• #### 科普

SCIENCE

#### 英语

ENGLISH

#### 科技

TECHNOLOGY

MOVIE

FOOD

#### 励志

INSPIRATIONS

#### 社会

SOCIETY

TRAVEL

#### 动物

ANIMALS

KIDS

#### 卡通

CARTOON

#### 计算机

COMPUTER

#### 心理

PSYCHOLOGY

#### 教育

EDUCATION

#### 手工

HANDCRAFTS

#### 趣闻

MYSTERIES

CAREER

GEEKS

#### 时尚

FASHION

• 精品课
• 公开课
• 欢迎下载我们在各应用市场备受好评的APP

点击下载Android最新版本

点击下载iOS最新版本

扫码下载译学馆APP

#### Excel筛选信息的两个技巧

2 Ways to Filter for a List of Items in Excel

Welcome to ExcelCampus. My name is John.

And the following video is from the Filters 101 Course.

to get the most out of the filter features in excel.

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.

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.

Then I’ll click OK. So that’s just filtered for “Costa Rica”.

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,

go to the Search box and I’m gonna start typing “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”.

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.

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.

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”.

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.

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,

this sheet that contains the, eh, filter list. And this is in excel table.

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.

And then I’ll hit Enter.

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.
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.

So, of course, “Costa Rica” is right here in the filter list table.

So we jump back over to our, eh, data table here.

We’ll see “Costa Rica” here.

So this is returning a one

because it’s found the value “Costa Rica” in that table one time.

So now what we can do is just to filter this column for ones.

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.

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.

And here within our formula, what we have this COUNTIF formula.

We can just set this “=” to one

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.

And if it does, it’ll return a true. If not, it’ll return a false.

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,

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.

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.

But if we go back to our data table and just change this filter to either false

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.

This is an online course that contains over 40 video lessons just like that one.

to help you get the most out of the excel sort and filter features.

Thanks again for watching and I’ll see you soon.

GreenT

HZ