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

Our company is also recruiting new employees recently, and I also went for a walk on the Internet.

See that the recruitment requirements of many positions are written: "proficient in the use of Excel and other office software", and there are computer operation questions during the interview.

Then went to the Internet to search to see what the Excel interview questions are, what level of questions, test whether they can handle it or not.

In the process, I found an interview question, which is very interesting, which I share with you today.

The following table records the date of each customer's visit to the store, requiring: use the function to extract the date of the customer's last visit.

At first glance, it doesn't seem difficult, but for rookies, it may still take some work.

It is estimated that a considerable number of people who are proficient or proficient in Excel on their resume will not be able to do it.

Analyze the problem. Let's analyze this problem first!

This topic is a double conditional lookup reference, and it is the last [date of arrival] found.

For example: the customer [Zhang San] has many duplicate names, and the [customer number] is also duplicated.

Requirements: extract the last [date of arrival] whose name is [Zhang San] and number is [MD003].

In the picture: [Zhang San] [MD003] [date of arrival] has a total of two times.

First time: 2022-1-4

Second (and last): 2022-1-8

Think about it, if it were you, what would you do?

When it comes to searching, the first thing I think of is to use the Vlookup function (which may be the first reaction of many people), because anyone learning Excel will come into contact with it, and it is also the most commonly used lookup function.

This is the right way of thinking. Vlookup can really solve this problem. Let's take a look.

The problem-solving ▋ method 1Vlookup is usually used for single-conditional lookup, but for double-conditional or more conditional lookups, it cannot be done alone.

What are we going to do?

We can combine the IF function to achieve double conditional search.

As shown below:

The formula is as follows:

= VLOOKUP (1) if (1) ({1) 0}, 0 / (E2&F2=$A$2:$A$15&$B$2:$B$15), $C$2:$C$15), 2) the formula probably means:

The principle of dichotomy search is used to match the last qualified value.

A search area and a return area are formed with if {1DO}.

The conditional area is:

$A$2:$A$15&$B$2:$B$15

Connect two conditions E2 and F2 together with a connector (&), and then compare them with the join condition regions of A2:A15 and B2:B15.

E2&F2=$A$2:$A$15&$B$2:$B$15 returns TRUE if it is the same, or FALSE if not

The results are as follows:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Then divide the array by 0 to get a memory array of 0 and error values.

{# DIVAccording to DIVUniverse 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 /

Among them: there are two zeros are the same as the search criteria, and finally use any value greater than 0, here use 1 to find the location of the last zero, and return the contents of the cell corresponding to $C$2:$C$15.

It is important to note that:

This formula is an array formula. You need to press the three keys [Ctrl+Shift+Enter] to end the formula input.

For Office 365, you can press [Enter] directly.

▋ method 2 for most friends, such a complex formula may not necessarily be used.

What are we going to do?

No rush!

Be sure to be steady during the interview!

If we only look at the requirements of the topic, we can modify it to make the V function easier to use.

Here we can change the two conditions into one condition and return the last value to the first value.

What needs to be done is to add a secondary column and sort the data source.

What does it mean exactly? Look down, ↓↓↓.

❶ add secondary column

The purpose of this step is to turn the two conditions into one.

Add a secondary column to the [A] column:

Enter the following formula in the A2 cell:

= B2&C2 concatenates the customer name of B2 and the customer number of C2 with a connector (&) to form a condition.

And fill the formula drop-down to [A15].

❷ sorting

The purpose of this step is to put the maximum date at the top. So that the V function looks for the first value.

In the data tab, call up the sort dialog box:

And set three sorts in the sort dialog box.

First keyword: customer name, ascending order

Second keyword: customer number, ascending order

The third keyword: date of arrival, descending order

Finally, click OK, and the sort result will come out.

As shown below:

❸ input formula

In this step, you can do the same operation as usual with the V function!

As shown below:

The formula is as follows:

= VLOOKUP (F2, G2, grammar, 1, 4, 4, 0) the formula means:

Join the contents of cells F2 and G2 together to form a condition, then look for the date of the first occurrence (that is, the date of the last visit) in the [A1:D15] area that starts with the auxiliary column, and return the corresponding value of column 4.

PS: this approach takes a flexible approach. Sometimes it's a good idea to use auxiliary columns to solve problems.

▋ method 3 in addition, this problem is to find the date of the last visit to the store.

Then the last date of coming to the store is the maximum date.

So we can also use the MAX+IF combination to do it.

So we can write it like this:

= MAX (IF ($Aph2Rose / 15BLV / B15E2Mill / C2MIT / CJ15) Formula Analysis:

First, the two conditional regions of column An and column B are connected into a conditional region, and then compared with the conditions in column E and column F, if the conditions are the same, the region of column C is returned. Finally, use MAX to return one of the largest date values (that is, the date of the last visit).

If your version is Office 2016 or later, you can also use the MAXIFS function.

The usage of the Maxifs function is the same as that of Sumifs.

The first parameter is the returned area

The second parameter is: conditional region

The third parameter is: condition

The basic routines are:

= region returned by MAXIFS, condition area 1, condition 1, condition area 2, condition 2...) For conditional areas and conditional pairs, a maximum of 126 pairs can be entered.

If the problem does not necessarily require a functional solution, it may be easier to use a PivotTable.

Click PivotTable in the insert tab to bring up the PivotTable dialog box.

Select [A1:C5], select the [E7] cell in the [existing worksheet], and finally click [OK].

Then drag [customer name] and [customer number] to [Line area], and [date of arrival] to [value area]

Filter the customer number

Select [count item: date of arrival] and right-click to set to: maximum.

The final effect is as follows:

All right, we're done with our interview questions!

But the question is, how do you know if the result is correct?

When making watches, we must keep one thing in mind, that is, there must be a checking mechanism.

For example, we use the V function to get the following results:

Some candidates may check again with other functions (such as MAX+IF function) in H column.

Finally, the result values obtained by two different functions are compared, and the comparison results are put in column I.

If it is true, the result is the same twice.

If it is false, it is different, and the problem should be further identified.

Of course, you can also use other methods to check, such as PivotTable. The method can be chosen by yourself.

To sum up, the following methods are introduced today for multi-conditional lookup references:

❶ VLOOKUP function

When searching for multiple conditions, this function needs to combine the IF function to reconstruct the data, which is more complex.

❷ VLOOKUP function + auxiliary column

This method is adaptable, and rookies are easy to use.

❸ MAX+IF function combination

The array judgment method is suitable for people with a certain array foundation.

❹ MAXIFS function

It is easy to use, but can only be used in versions above OFFICE 2016.

❺ PivotTable

The method is the simplest and has a wide range of application.

In addition, in the workplace, be sure to remember that after you have done something, there must be a checking mechanism.

And have a good record!

Sometimes what you spell in the workplace is not only skills, but also experience.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel, Review: Xiao Shuang, Editor: 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.