ADM-201 dump PMP dumps pdf SSCP exam materials CBAP exam sample questions

用列表搜索工具来搜索数据有效性列表 – 译学馆
未登录,请登录后再发表信息
最新评论 (0)
播放视频

用列表搜索工具来搜索数据有效性列表

How to Search Data Validation Lists in Excel with the List Search Add in

欢迎来到ExcelCampus
Welcome to ExcelCampus.
我是乔恩 在这个视频中 我将会向你们展示怎样搜索数据有效性列表
My name is Jon, and in this video I’mgoing to show you how to search a data validation list
或是单元格内下拉列表
or an in cell drop-down list.
在这个单元格中 有一个数据有效性列表 里面有长长一列的有效值
So in this cell right here I hava a data validation list with a long list of values here.
列表里有许多的水果和蔬菜
There’s a lot of fruits and vegetables in this list here,
有时候一条条地寻找我们想要的词条很难
and sometimes it’s hard to scroll through these long lists to find the item we’re looking for,
不幸的是 我们没有办法直接在Excel中搜索列表
and unfortunately there’s no way to directly search this list in Excel.
没有这样的内置方法
There’s no built-in way to do this.
虽然有一些很棒的工作区 你可以使用它们来创建
There are a few fancy workarounds you can use to create
基于公式的有效性列表来便于搜索
formula-based data validation lists that will help with this search,
但你需要对每一个列表做大量的设置工作
but you have to do a lot of setup work in each list that you want to create that for.
所以我建立了一个内插式附件 这是一个完全可以免费使用的内插式附件
So I’ve created an add-in it’s a absolutely free add-in that you can use,
一但安装 你会在这里看到Execelcampus功能区
and once install here you’ll see the ExcelCampus tab of the ribbon appear right here,
并且在这里你会看到列表搜索键
and then you’ll see this List Search button appear right here,
点击那个列表搜索按钮 就能打开这个非常简单的表格
and clicking that List Search button will open this very simple form right here.
里面有一个下拉菜单 点击时 你就会看到这个列表
Has a drop-down menu in it and when you click that you’ll see the list,
这个单元格中的数据有效性列表
has been loaded into this list right here, so the list, the data validation list is in the cell,
将会加载到这个表格中 然后我们就可以用这个表格来进行搜索了
has been loaded right here into the form and we can use this box here to search.
桃子是我最喜欢的水果之一 如果我开始打桃子(peach)这个单词
So one of my favorite fruits are peaches, so if I start typing the word peach,
你能看到列表中包含的词语范围缩小了
you can see that the list is narrowed down right here for any words
只有含PEA的单词可以看到了 所以不需要打出整个单词了
that contain the phrase PEA right there, so it doesn’t need to start with the word.
桃子就在这里了 你可以用键盘或鼠标选择你想要的单词
Here is Peach right here so you can use the keyboard or the mouse to select the item you want.
我选了桃子 我可以按下键盘上的回车键
I’ll select Peach there and then I can hit the Enter key on the keyboard
或我可以按下这里的按钮 将词组放入单元格中
or I can press this botton right here to input that value in the cell right there,
你可以看到它自动选择了下一个单元格
and then you can see that it also selects the cell right below that,
让你可以继续搜索并填上想填的
as you can continue to search here and add items.
现在我可以继续搜索 我还喜欢番薯(sweet potato)
So now I can continue with my search.Another vegetable I Iike are sweet potatoes.
如果我开始打番薯这个单词 在列表中就可以看到番薯了
If I start typing the word sweet potato you can see I don’t have, oh, I have sweet potato here in the list.
我不需要滚动翻找列表就可以找到古巴甘薯了
I never would’ve found Cuban Sweet Potato scrolling through the list there,
这个工具让你能很简单地找到你想要的词条
so again this makes it very easy to find items you’re looking for,
就像这个词条 你可以按回车键 它就放到了单元格中
just like that item and you can hit Enterand that’ll enter right in the cell there,
列表搜索还有一些可选功能
and List Search also has some optional features here.
当你点击这个菜单按钮 你可以将列表中的词按A-Z的升序排列
So if you click this menu botton you can see that you can sort the list from A to Z Ascending Order.
你也可以按降序或原始顺序排列
You can also sort it in Decending Order or this is the Original Order here.
我们还可以得到列表的简要信息
We can also get some info about the list,
这个表有164个词条
so this particular list here has 164 items in it,
你还可以改变下一个单元格的选中顺序 当你按下回车键后
and then you can also change the order so after you hit Enter
或者按下这个键入键 会自动选中下一个单元格 你可以改变这个顺序
or press the Input button right here you could change the order of the next cell that’s selected,
你可以往下选或者往右选
so you can select down one cell you can go right one cell.
也可以什么都不做或者关闭这个表格
I do nothing at all or just close the form.
以上这些就是列表搜索的基本功能
So that’s a basic overview of how list search works,
这个工具可以用于任何工作簿的有效性列表中
and it will work on any data validation list in any workbook that you already have,
你不需要对你的工作簿做任何的改变
so you don’t have to make any change to your workbooks.
你只需要打开表格 在列表中搜索
You can just open this form and start searching the list,
甚至你不一定需要有数据有效性列表
and you don’t even have to have data validation cells.
刚刚示范的表格中已经有了数据有效性列表
So these cells here that we opened the form on have this data validation.
如果我跳到另一个表格 可以看到我有一列数据
If I jump over to this other sheet you can see I just have a sheet here with a list of data,
这些单元格没有数据有效性列表
and there aren’t any data validation cells anywhere in this list.
选择一个单元格 打开列表搜索
So if I was to just select a cell here and open list search.
它会自动加载在这一列中的所有数据
It’s going to automatically find all the unique values
在D列中的数据
in this particular column here, in column D,
然后把他们在列表中列出 当然在这里也可以进行搜索
and then list them right here in the box and of course the search works here as well.
假设查找姓名Ann
So if we were looking for the name Ann,
我们开始打单词Ann
we can just start typing the word Ann,
你可以看到所有包含Ann的单词
and you can see all the names appear right here that have the word Ann in them.
所以数据有效性列表不是必须的
So you don’t even need to have data validation in your cells.
列表搜索可用于你工作簿的任何地方
List Search can work just about anywhere in your worksheet,
当然其他功能也可以正常使用
and of course the options work here as well.
如果你想让列表按升序或者降序排列
If you want to sort the list here in Ascending Order,
列表就可以按升序或者降序排列
we could see the list in ascending order decending order,
这些都基于我们现有的搜索列表 如果你想要清空关键词限制 也可以清空
and this is just based on the search we have, so if we want to clear the search we can do that,
再一次看到原始的列表按升序或降序排列
and then again see the list in Ascending Order or Decending Order,
同时也可以改变选中下个单元格的方向
and change the direction as well.
让我们用这个便捷有用的小工具搜索数据有效性列表吧
So let’s search again just a great handy little tool to help you search data validation list
或者是任何Excel中的列表数据
or any list of data in Excel,
在www.ExcelCampus.com上即可免费下载
and it’s a free download from ExcelCampus you can get it at excelcampus.com,
我会把链接放在就视频下方的说明栏里
and I’ll put the link to that download page below this video in the description section.
如果你有任何疑问 请在视频下评论留言
So if you have any questions at all please leave a comment below,
我很乐意能帮助解答
and I’ll be happy to help answer them.
再次感谢观看 下次再见
Thanks again for watching and I’ll see you soon.

发表评论

译制信息
视频概述

视频演示了如何用添加列表搜索工具,并且如何使用工具来搜索数据有效性列表

听录译者

真皮沙发

翻译译者

真皮沙发

审核员

审核团MG

视频来源

https://www.youtube.com/watch?v=kntHn2J7pdk

相关推荐