未登录,请登录后再发表信息
最新评论 (0)
播放视频

Excel筛选信息的两个技巧

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
以在Excel中实现突出的筛选功能
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.
对“Venezuela”我们也同样做
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.
输入“Venezuela”
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,
我们打算添加一个“COUNTIF”公式
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包含两个参数
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.
就是“COUNTIF”公式的区域
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.
看到公式这里使用Excel表格名称
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.
得出这列为“0”和“1”
So we now get this column with ones and zeros.
包含“1”的每一行的值在我的筛选列表中
And any row that contains a one means that that value is in my filter list.
COUNTIF公式只用于计算次数的数量
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.
这里返回值为“1”
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,
因为我们筛选的是所有次数为“1”的
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.
设置其为“1”
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.
将计算COUNTIF函数的返回值是否为1
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,
使用Excel表格筛选列表有许多优点
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,
添加“Brazil”和“Portugal”到底部
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.
点击“Data”功能区选项卡
We can do that on the “Data” tab of the Ribbon.
点击这里的“Reapply”
I just clicking the Re-apply button right here,
将再次筛选得出所有“TRUE”或“1”
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.
示例视频来自Filters入门课程
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,
以助你从Excel排序和筛选功能中获益良多
to help you get the most out of the excel sort and filter features.
想学习更多课程
If you’d like to learn more about the course,
请访问网站ExcelCampus.com/filters
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.

发表评论

译制信息
视频概述

在Excel中,筛选信息的两个技巧:1、将当前所选内容添加到筛选器;2、基于公式的解决方案。

听录译者

收集自网络

翻译译者

GreenT

审核员

HZ

视频来源

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

相关推荐