最新评论 (0)


2 Ways to Filter for a List of Items in Excel

欢迎来到ExcelCampus 我是约翰
Welcome to ExcelCampus. My name is John.
视频来自Filters 入门课程
And the following video is from the Filters 101 Course.
这是一门在线课程 可以帮助你更快地准备并分析数据
This is an online course that we will help you prepare and analyze your data faster
to get the most out of the filter features in excel.
请访问网站“ExcelCampus.com/filters” 学习更多课程视频
Please visit ExcelCampus.com/Filters to learn more about the course.
Now let’s check out the video.
视频中 我将介绍从项目列表中进行筛选的两种方法
In this video, I’m gonna explain two different ways to filter for a list of items.
So here I have a table with a list of transactions,
在国家这一列 包含大量不同的国家
and I have this Country column here that contains a lot of different countries.
那么我想筛选这个 列出仅仅几个国家
So I wanna filter this, eh, column down for just a few countries.
首先来看第一种技巧 “将当前所选内容添加到筛选器”
So we first gonna look at a technique using the “Add current selection to filter” method.
Er, to filter down for this list of items.
And then we’re going to look at a formula based approach
为在表格这里 筛选一个稍长项目列表
to filter down for a longer list of items in this table here.
Er, using formulas.
So let’s jump back over to our table here.
首先 我想筛选列表中的仅仅这三个国家
So the first thing I wanna do is filter this list here for just these three countries.
So I wanna select and click the filter drop-down menu here.
找到搜索框 我从输入第一个国家“Costa Rica”开始
And go to the Search box and I’ll start with the first item which is “Costa Rica”.
开始输入 就看到列表被筛选出来
So I’m gonna start typing that. I’ll see the list that is filtered for that.
单击“OK” 仅筛选“Costa Rica”
Then I’ll click OK. So that’s just filtered for “Costa Rica”.
现在我想增添筛选器 添加“Poland”
So now I wanna add to this filter and I wanna add “Poland”.
So I’m gonna again click the filter drop-down menu here,
找到搜索框 并开始输入“Poland”
go to the Search box and I’m gonna start typing “Poland”.
那么我开始输入“Poland” 就再次筛选出这个列表
So I start typing “Poland”. That will again filter down this list.
这有个选项 “将当前所选内容添加到筛选器”
And we have this option here and it is “Add current selection to filter”.
我们可以选中它 勾选 然后按“OK”
We can click that. And click that and then click OK.
That’s going to then add that filter criteria to the existing filter criteria.
因此看到列表中筛选后兼有“Costa Rica”和“Poland”的数据在这里
So we can see now that the list is filtered down for both “Costa Rica” and “Poland” right here.
And we can do the same thing for “Venezuela” as well.
So I’ll click the filter drop down.
用键盘快捷键“E” 跳转至搜索框
We can use the keyboard shortcut which is the letter “e” to jump down to our Search box here.
We can start typing “Venezuela”
and we will see we get the same thing here “Add current selection to filter”.
勾选 并点击“OK”或按“Enter”键
We’ll check that box and click OK or hit Enter.
And now our list has been filtered for all three of those items.
在筛选下拉菜单这里 我们可以看到筛选条件
Now we can really see that filter criteria, eh, within the filter drop-down menu here.
Anywhere we have to scroll to see all the boxes that have been checked.
但是这里如果我们点击“取消” 能怎么做呢
Eh, but what we can do if we hit cancel here.
So when we hover over the filter drop-down menu icon,
屏幕注释出现 显示这里的筛选条件
we will see that screentip appears and it shows there the filter criteria.
“Costa Rica” “Poland” “Venezuela”作为筛选条件 这列被筛选得出
So “Costa Rica”, “Poland”, and “Venezuela” is the filter criteria that this column has been filtered for.
因此 当我有一个小项目列表 想筛选的是短列表时
So, this technique using the “Add current selection to filter” option is great,
if we have a small list of items, a short list of items we wanna filter for.
但是如果我有一个长列表 像这个有十个项目的列表
But if we have a long list of items, like this table here with ten items,
输入所有这些搜索词 并勾选框等操作 会花费大量时间
that can take a long time to type all those searches and click the box and all of that.
例行公事般 一遍又一遍重复做
Eh, that routine. Doing that over and over again.
对这种情况的处理 我们可以用“基于公式的解决方案”
So for this type of process, we can use a formula based approach.
So I’m gonna jump back over to my sheet that contains my table.
I’m gonna go ahead just clear this filter for now.
在这里 单元格就在表格右侧
And in this, eh, cell right here to the right of the table,
we’re going to add a COUNTIF formula.
输入“=COUNTIF” 可以直接点击这里输入函数
So I’m gonna type “=” and then “COUNTIF”. We can tab into the function here.
COUNTIF contains two criteria.
First is the range that we wanna evaluate.
然后是条件 两个不同参数
And then the criteria. So two different arguments here.
先选区域 就是我们的筛选列表
So we’ll first select the ” range” and now we’ll be our filter list.
So we’re gonna go over to this table here,
这个工作表包含 筛选列表 并且其亦在Excel表格中
this sheet that contains the, eh, filter list. And this is in excel table.
你不是必须用Excel表格格式 但这里需要
You do not have to use the excel tables for this, but it does, eh, have some advantages here,
which all explain in just a second.
鼠标悬停在顶部 列头这里
So we’re first just going to hover our mouse over the top, the header here
左键单击 选择整列的字段值区域
and left-click and now we will select the data value range here of the column
that we want to evaluate.
That’s our range for our COUNTIF formula.
And then we will type a comma.
And then we will go back over to our other sheet.
And we are going to just select the cell here that contains the country.
I can see my formula here is using the excel table notation
which is called structured references.
再者 不需要引用表格
Again, you do not need to use tables.
Eh, you could just use regular range references here.
这样做应该没问题 不管怎样 有其优点
And this should work just fine. However, there are some advantages.
Eh, so I’m going to head and go ahead close parentheses.
And then I’ll hit Enter.
由于我使用excel表格 公式向下自动填充
Since I’m using the excel table, the formula’s automatically copy down for me.
So we now get this column with ones and zeros.
And any row that contains a one means that that value is in my filter list.
So the COUNTIF formula is going to just count the number of time
that this value appears in the filter list table which is right here.
那么 当然 “Costa Rica”就在筛选列表这里
So, of course, “Costa Rica” is right here in the filter list table.
So we jump back over to our, eh, data table here.
看到“Costa Rica”这里
We’ll see “Costa Rica” here.
So this is returning a one
因为“Costa Rica”在表格中被发现一次
because it’s found the value “Costa Rica” in that table one time.
现在我们可以做的是 只要筛选出此列的“1”
So now what we can do is just to filter this column for ones.
所以我只筛选“1” 并点击“ok”
So I’ll just filter here for ones only and click “OK”
and now we filter down our table for all of the items in our filter list.
再次返回筛选列表 这是筛选列表
So if we jump back over to our filter list again, here is our filter list,
and because we’re filtering for all of the ones of the times
这里 在表格中被发现的这个(次数)值
this, eh, this value has been found in the table,
we’ve applied a filter for all of those values.
如果不是“0”或“1” 想此处显示“TRUE”和“FALSE”
Now instead of ones and zeros, so if I might wanted to display trues and falses here
that might make more sense for using this, eh, as a source data of
a pivot table or something like that.
清除 再次筛选
So I’m gonna go ahead clear are, eh, filter again.
在公式中 有COUNTIF公式
And here within our formula, what we have this COUNTIF formula.
We can just set this “=” to one
在最末尾处 输入“=1”
so at the very end of the item, type the equal sign and then put a one.
And that’s going to evaluate whether the return value from the COUNTIF function equals one.
如果为“1” 返回“TRUE” 否则的话 将返回“FALSE”
And if it does, it’ll return a true. If not, it’ll return a false.
我们筛选出“TRUE”和”FALSE” 代替“1”和“0”
So now we can just filter for trues and falses instead of ones and zeros.
And that just might make a little more sense again for using this in a pivot table.
We can also rename this column to something that’s more descriptive,
比如说“Filter countries”或类似的词
eh, like “filter countries” or something like that,
to help us, eh, describe what we’re doing with this column.
Now, as I mentioned before,
there are some advantages to using an excel table for this filter list here.
And the main advantage is that we can easily add items to the bottom of this list,
and not have to worry about updating our formula.
举个例子 在此处 如果我想在列表底部再添加几个国家
So for example, here, if I wanted to add maybe a few more countries to the bottom of my list here,
I’ll add a “Brazil” and “Portugal” to the bottom.
当然 表格自动扩展到我们添加项目的这里
Of course, our table is automatically extended here as we add items to it.
And now if we go back over to our, eh, data table,
all we need to do now is reapplied the filters.
And that’s because this formula is referencing that entire column.
在筛选列表中 这是整列
This is the entire column in our filter list table.
So we don’t need to change any references to any cell addresses here to extend the table.
That’s automatically done for us.
So all we really need to do now is just reapplied the filter.
We can do that on the “Data” tab of the Ribbon.
I just clicking the Re-apply button right here,
that will reapply our filter for all the trues or all the ones.
And we can see now that we have these new items
that are in our filter list applied here in our data table.
再一次强调 你不是必须使用Excel表做这个
So again, you don’t have to use excel tables for this,
但它可使生活更简单 并减少
but it’ll make your life a little easier and reduce the amount of maintenance
that you have to do on these formulas in the future,
as you add and delete items from the filter list.
And one other thing I wanted to point out real quickly is
that we can also use this technique to filter for items that are not in this list.
那么当然 最近的筛选项目在筛选列表这里
So, of course, recurrently filtering for items that are in our filter list here.
返回数据表 只改变筛选为“FALSE”
But if we go back to our data table and just change this filter to either false
如果使用“1”或“0” 改变为“0” 点击“OK”
or if using ones and zeros, change this to zero, and click OK.
We are now filtering for all the items that are not in our filter list table here.
So everything that’s not in that table is displayed here.
And this might be useful for a filter criteria as well.
So in the next video,
we’ll take a look at how to filter for duplicates with conditional formatting.
So that was a sample video from the Filters 101 Course.
这是一门在线课程 包含类似的40多个视频教程
This is an online course that contains over 40 video lessons just like that one.
This will help you prepare and analyze your data faster,
to help you get the most out of the excel sort and filter features.
If you’d like to learn more about the course,
please visit ExcelCampus.com/Filters.
有任何问题 请在下方评论区留言
If you have any questions about this video, please leave a comment below.
感谢收看 下次再见
Thanks again for watching and I’ll see you soon.