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

The original title: "after using Excel for more than ten years, I unexpectedly know this high-energy function. Don't be too easy to use!" "

The INDIRECT function is a very high-energy function in Excel. At the same time, its syntax is very simple, with only two parameters, and the second parameter can be defaulted.

With regard to the INDIRECT function, teacher Green Water Zero is secretly learning this minority high-energy function. I have never worked overtime again. It has been interpreted in detail in this article.

But even so, once practiced, the INDIRECT function is still a nightmare for many friends!

In this article, Xiaohua will analyze the common misunderstandings of INDIRECT function for you. I believe it will sweep away the haze for you!

1. A message from a fan about the citation style.

The formula of the message is as follows:

= INDIRECT (R & MATCH) & C & MATCH, FALSE) to be honest, not only this little friend, but almost all of us are used to the way the INDIRECT function omits the second parameter A1, so that when it uses the R1C1 reference style, the opposite side doesn't know it!

There are two reference styles for Excel cell addresses:

❶ A1 reference style: use letters to indicate column numbers, numbers for line numbers, default for relative references, and "$" for absolute references.

❷ R1C1 reference style: line numbers are represented by R + numbers, column numbers by C + numbers, absolute references by default, and relative references by "[]".

The comparison between the two is as follows:

In the formula referred to in the message, the parameter A1 is FALSE, indicating that the R1C1 reference style is adopted. The two MATCH connect R and C to form a complete R1C1 reference style by matching the position order values that appear in the condition value. INDIRECT then evaluates the reference according to this address, and finally completes the cross query!

The formula is as follows:

= INDIRECT ("R" & MATCH) & "C" & MATCH (I2 magnificent A1 MATCH F1L), FALSE)

So, even though the second parameter A1 of INDIRECT is omitted most of the time, we still can't forget it easily!

2. With regard to the reference address INDIRECT can correctly handle, only the text that represents the reference address, the vast majority of INDIRECT function application errors focus on the construction of the reference address text.

The following figure lists several ways to build reference address text.

To put it simply, the first four, whether the first parameter Ref_text of the INDIRECT function is directly set to text, cell reference, or formula, as long as the final Ref_text can return a complete text representing the reference address, the INDIRECT function can operate correctly.

And the ⑤ will need to reference the cell B1 directly as a parameter Ref_text, the formula will first reference the value of B1, get 2, and the number "2" is not a complete reference address, resulting in the INDIRECT function can not be calculated.

This is different from the case in ②, which directly takes the text "B2" as the parameter Ref_text, which does not further calculate the text "B2", and the text "B2" is the reference address.

In the case of ①, the A1 cell is referenced as the parameter Ref_text, and the formula first refers to the value of A1 to get "B2", which can also be calculated correctly.

The ⑤ case is a common mistake in the application of the INDIRECT function. Have you ever stepped on a mine?

3, about single quotation marks using the INDIRECT function for cross-table reference, is another error hardest hit area!

Even if the reference address is clear and accurate, the INDIRECT function still cannot be calculated!

Why is that?

The error formula is as follows:

= INDIRECT ("January Guangzhou B2")

This is because some worksheet names contain some special characters, such as spaces, asterisks, etc., so that the INDIRECT function can not recognize the table name. At this time, you need to circle the worksheet name with single quotation marks "'" before the INDIRECT function can recognize it correctly.

The revised formula is as follows:

= INDIRECT ("'January Guangzhou'! B2")

So how do you tell if you need to add single quotation marks? Simply use the equal sign to refer to any cell of the target worksheet and see if the formula contains single quotation marks.

In fact, single quotation marks can be calculated correctly without the need for single quotes.

Therefore, when you need to reference multiple worksheets, it is safe to add single quotation marks!

4, for cross-table reference to see the following example, Xiao Zhang needs to calculate the maximum sales of the day in a specific three cities, that is, to reference the same cell of discontiguous tables, and then find the maximum value.

Xiao Zhang painstakingly set up the cross-table reference formula, but the result was wrong. Let's see what went wrong.

The error formula for referencing across multiple tables is as follows:

{= MAX (INDIRECT ($Daw2VOV DUBE4 & "! B2"))}

Through the array operation, it seems that there is nothing wrong with the formula that makes INDIRECT ($Demp2VOULDUB4 & "! B2") quote the B2 cells of Huizhou, Foshan and Dongguan three tables respectively, and then use the MAX function to get the maximum value.

The result of BUG's cross-table reference in INDIRECT is a multi-dimensional reference. The MAX function cannot operate on this multi-dimensional reference and can only return the first value, that is, Huizhou! B2, resulting in an error.

In general, multidimensional references cannot be operated directly without using specific functions. These specific functions include T function, N function, SUMIF function, SUBTOTAL function and so on.

In this case, we only need to use the N function to convert the multidimensional reference result of the INDIRECT function to a numeric form, and the MAX function can operate correctly.

The modified formula for cross-table reference is as follows:

{= MAX (N (INDIRECT ($Daw2)}

INDIRECT function across multiple table references in the door is very deep, interested friends can look forward to Xiaohua follow-up article!

These are the common misunderstandings of the INDIRECT function disassembled by Xiaohua, including:

❶ ignores parameter A1 and makes it impossible to understand the INDIRECT function under the R1C1 reference style.

Incorrect construction of ❷ causes the parameter Ref_text to be incomplete, representing the text of the reference address

❸ did not add single quotation marks so that the INDIRECT function could not correctly recognize the table name

❹ does not use specific functions to handle multidimensional reference results, making it impossible for other nested functions to operate.

After reading this article, you can no longer step on the INDIRECT function! If there are other common types of errors in INDIRECT functions that Xiaohua has not mentioned, please feel free to leave a message and communicate with us.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua

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.