Shulou(Shulou.com)11/24 Report--

Hello, everyone. I'm Twelve.

As a worker in the workplace, I believe everyone is no stranger to the Vlookup function. She has saved tens of thousands of cousins from suffering, and it is not too much to say that she is a popular lover in the functional circle.

However, a new function was born, which can not only complete the query operation of Vlookup, but also further optimize it.

She is the new goddess we are going to introduce to you today: the Filter function.

As early as 2019, Office365 launched this function, which is the envy of our friends who use WPS.

Now, we can finally experience this powerful function! Thank you, CCTV! Thank you, WPS bully!

Now go to the WPS official website (https://platform.wps.cn) to download the latest WPS Windows version, and you can use the Filter function for free!

Next, let's take a look at the specific usage of the Filter function.

1. The most widely used function of the single-column conditional search Vlookup function is to find the required data through the single-column conditional search, and the Filter function can also be used.

Give me a chestnut!

We need to find the employee's "name" according to "gender".

Look at this wave of operations:

It can be done with only 2 parameters, isn't it convenient?

The Filter function, which can filter out the desired results according to custom criteria.

It consists of three parameters: array, including, and null:

= FILTER array, including, [null]) Parameter 1-Array: region or array to find

Parameter 2-including: search criteria

Parameter 3-Null: custom description of the null result (optional)

We enter the formula in the cell:

= FILTER (B2purl B30jec C2 C30roomI1) formula:

The region or array that ❶ is looking for: the last name column of the B2:B30 employee.

❷ search criteria: whether the C2:C30=I1 gender column is equal to "male".

The cell drop-down list function is referenced here. Friends can click on the link below to consolidate their knowledge.

Xiaobai can learn the multi-level drop-down list, so that you can do most of other people's work in half an hour!

It should be noted that WPS does not support dynamic arrays and overflow functions, so:

❶ will select the entire data storage area when selecting the data display range, and WPS will automatically mark the excess cells as the error value "# NA" (if you need to remove the error value, you can use the nesting of Index and Iferror functions); Office365 displays normally without the need for box selection.

WPS interface:

Office365 interface:

❷ completes the input of Filter function in WPS, can't press enter key directly, but needs to press and hold "Ctrl" + "Shift" + "Enter" three keys to complete formula input.

When the input is complete, look at the bulletin board and you will see that the function formula is selected by a pair of curly braces. This is how the WPS array formula is displayed.

If there is only one item in ❸ that meets the requirement, WPS will automatically fill the entire marquee area and Office365 will display normally.

WPS interface:

Office365 interface:

See here, you may have questions in mind: this search function and Vlookup function is not much different?

Then you underestimate her! Look down and continue to feel the charm of the new goddess!

2. Multi-column criteria search for the last case We looked up the names of all employees through one-to-one conditions.

So, is it possible to find multiple pieces of information through one condition?

Look at the following chestnut:

Use "gender" to find all employee information, the time saved can continue to mo (yu)!

Start the operation:

Take a look at the formula:

The region or array that ❶ is looking for: all the information columns of A2:F30 employees.

❷ search criteria: whether the C2:C30=I1 gender column is equal to "male".

Compared with the Vlookup function to write multiple formulas, using the Filter function to write one can be done!

Wuhu ~ simply don't be too good!

3. Keyword search is less than 10 minutes away from work! At this time, the boss asked us to find out in a large amount of data that all "regions" contain employee information in Hubei Province. What should we do?

The Filter function gives us a hand! Not working overtime is our last stubbornness.

Look at this wave of operation, it doesn't take 10 minutes, it can be completed in 2 minutes! (you can still touch the fish for 8 minutes. Jpg)

The formula is as follows:

Don't be frightened by this seemingly complicated function, let's disassemble Kangkang step by step, do not be frightened by this seemingly complex function (FIND ($FIND ($Hang1Grainecommensory Elev2VuIRAUBER 30)).

This is a nested function, and we use the Isnumber and Find functions to locate the search criteria:

❶ = FIND ($Humb1GradeElev2Find) is a lookup function that looks for the position of the desired character in a specified cell or string.

= FIND (string to be found, string to be found, [where to start])

We first use the Find function to find the location of "Hubei Province" in the "area", find the corresponding location, and return the error value "# VALUE!" if not found. ".

❷ = ISNUMBER (FIND ($Hang 1, FIND ($Hang 1, Magneto, etc.) Isnumber is a decision function that detects whether a value is a numeric value and returns True or False accordingly.

If the FIND function finds the location, Isnumber returns True, otherwise it returns False.

❸ = FILTER (A2Drex E30Magnesia isNUMBER (FIND ($Hang1recoveryEfficient2FIND)) finally, the result returned as True is filtered out with the Filter function.

Is this wave of operation very good?

4. the difficulty of finding any condition is upgraded! After receiving the form, the boss assigned a new task:

Look for information about employees who have been employed for more than 10 years or whose sales are more than 8000, and prepare to issue rewards! Sooner or later, I will have it, too.

)

Guess how many minutes will it take this time?

It can be done in 2 minutes, no more!

Formula disassembly:

= FILTER (A2:F30, (E2:E3010) + (F2:F308000)) the region or array that ❶ is looking for: all the information columns of A2:F30 employees.

❷ search criteria:

E2:E30 > 10: the seniority column is greater than "10"

F2:F30 > 8000: sales column is greater than "8000"

Either of the two conditions is connected with a "+" sign.

So, after learning the Filter function, is it still difficult to get off work on time?

5. Multi-conditional search besides any conditional search, the Filter function can also find employee information that meets two conditions at the same time.

Look at the last chestnut!

In the data below, find all the "female" employee information that the department is "selling one".

To operate a wave of lightning:

Formula disassembly:

= FILTER (A2:F30, (A2:A30=I1) * (C2:C30=J1)) the region or array that ❶ is looking for: all the information columns of A2:F30 employees.

❷ search criteria:

A2:A30=I1: Department column equals "sales one"

C2:C30=J1: gender column equals "female"

Both conditions are met by connecting with the "*" sign.

Wait a minute. When we inquire from another department, why does it show such a strange thing? You don't have to work overtime, do you? )

Don't worry! Take a look at the following knowledge point!

When the lookup zone does not have data that meets the criteria, WPS returns the error value "# CALC".

At this point, we can set the third parameter of FILTER, such as: no information.

The formula is as follows:

= FILTER (A2:F30, (A2:A30=I1) * (C2:C30=J1), "No Information") Note, you need to switch the input method to English and enter double quotation marks!

The ancients did not deceive me. After learning this method, they were finally able to xia (ban).

After reading this article, do you feel the charm of Filter function?

If you have different views, you are welcome to leave a positive message and let's exchange and discuss it together.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: 12, Review: Xiao Shuang, Editor: Xiaoyin, Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.