如何在Excel中创建搜索功能
有人可以帮助我在Excel中创建搜索功能，以作为联系人查找？直到2016年，我在Excel中都非常舒服，但是没有VBA知识，所以我希望没有它可以做到。使用Excel 365，并将其发布给特定用户。我知道在这里依靠样本数据是不好的做法，所以我会尽力使用几个屏幕截图来解释。
情况： 我有一个原始数据表，其中包含50,000行（自定义，ID，CITY，COUNTRY，CHAILADDRESS等）。我们的销售团队需要查找联系方式，但只有知道客户的名字或部分。 我需要隐藏基础数据，但是基本系统是可以接受的，我们只是不想将其提供给盘子。通过使其所有白色文本的基本推荐的裁员方法；锁定所有细胞；隐藏所有行和列；密码保护和隐藏纸牌目前还不错 - 没有人会决定不足以开始将其分开。
我在这里搜索了，虽然有类似的结果，但没有什么真正适合的。到目前为止，最合适的是在扩展上，这还不够足够（将在附件中放置链接）。它利用搜索，排名，行和Vlookup功能，我不完全理解其交互，但有效。
” /strong>
- 结果没有可分辨的顺序。排名和行函数似乎是随机分配等级的，当您获得50多个结果而无法分类时，它会令人沮丧。
- 每当一个人询问时，它都会消除最后一个人的搜索。期待，但不是很好
partial string search with in n
ofst
- 该文档将在SharePoint上，直接可直接访问约20人。理想情况下，当一个人搜索时，它不会在别人的屏幕
- 结果上改变，应按客户名称
noce to Haves出现字母：
- 添加另一个标准以搜索。例如，城市或客户经理 - 不必堆叠
- 选择并复制单元格的内容（例如电子邮件地址），而不允许编辑值或公式。如果不可能，我将完全锁定细胞，因为意外覆盖公式的权衡是不值得的。
任何帮助都将不胜感激
Could somebody please help me create a search feature in Excel, intended as a contact lookup? I'm reasonably comfortable in Excel up to 2016 but have no VBA knowledge so I hope this can be done without it. Using Excel 365 and it will be published to Sharepoint to specific users. I know it's poor practice on here to rely on sample data so I'll do my best to explain with a couple of screenshots.
Situation:
I have a raw data sheet containing 50,000 rows (CustomerName, ID, City, Country, EmailAddress etc). Our sales team need to look up contact details but only if they know the customer's name, or part thereof.
I need to hide the underlying data but a rudimentary system is acceptable, we just don't want to serve it up on a platter. The basic shove-it-under-the-mattress method by making it all white text; lock all cells; hide all rows and columns; password protect and hide the sheet is fine for now - no one will be determined enough to start picking that apart.
I've searched here and while there are similar results, nothing really fits perfectly. The most suitable so far was on extendoffice which doesn't quite suffice (will put link in attachment). It utilises SEARCH, RANK, ROW and VLOOKUP functions, the interaction of which I don't entirely understand, but it works.
Interim steps for the lookup function on sheet "Search"
Issues with current concept:
- There's no discernable order to the results. The RANK and ROW functions seem to randomly assign ranks, and when you get 50+ results with no way of sorting it will be frustrating.
- Whenever one person queries it undoes the last person's search. Expected, but not nice
Partial string search with results unsorted
Additionally:
- This doc will be on Sharepoint, directly accessible by ~20 people. Ideally, when one person searches it won't change on someone else's screen
- Results should appear alphabetically by customer name
Nice to haves:
- Add another criteria to search by. Eg City or Account Manager - doesn't have to stack
- Select and copy the contents of a cell (eg email address), but not allow editing of the value or formula. If not possible I'll leave the cells completely locked as the trade-off of accidentally overriding a formula is not worth it.
Any help would be super appreciated
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论