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

The original title: "Let the boss look dumbfounded, this Excel form can be updated automatically!" "

Hi, I'm little Em.

When I passed by my colleague's station yesterday, I saw that he had made an entry form:

Click, Excel can automatically enter the corresponding information!

Wow! Doesn't it mean that you no longer have to worry about entering data so tired that your hands get cramped?

First of all, if you want to achieve "table automatic data entry", you need to use three functions and one function.

Three functions: VLOOKUP, IFERROR, COLUMN

One function: drop-down list

Although there seem to be a lot of functions used, it is actually very simple. You can collect formulas if you really can't learn them, and use them directly.

01. Prepare the parameter table, that is, the data that needs to be entered into the "input table". Remember, it must be an one-dimensional table format.

02. Set the drop-down list ❶. Select the B5:B16 area of the input table and open the "data Verification" window.

Select [sequence] for ❷ verification condition.

❸ chooses to add data sources.

Demonstration of the effect:

03. Input function formula enter the formula in the C5 cell of the input table:

= IFERROR (VLOOKUP ($B5 Parametric Table! $Agg1VRank Found14Com (input Table! B5), 0),)

Then drag the formula right and down to copy it to another cell.

It's done! You see, is it possible to enter it automatically by clicking on it now?

Finally, let me briefly explain the functions mentioned above.

= IFERROR (VLOOKUP ($B5 Parametric Table! $Agg1VOB5 Parameter Table! $Abeli1VOB5 FrequencyCOLUMN (input Table! B5), 0),) ❶ COLUMN (input Table! B5)

Is so that when you drag the fill formula to the right, you do not have to change the column number returned.

❷ VLOOKUP ($B5, parameter table! $A$1:$F$14, ❶, 0)

Parameter-$B5: indicates the value to look for

Parameter two parameter table! $A$1:$F$14: indicates the scope of the search

Parameter 3 COLUMN (input table! B5): indicates the column relative to B5 cell

Parameter 40: indicates an exact match.

❸ = IFERROR (❷, "")

When the formula is calculated correctly, the result of the formula is returned; when there is an error in the calculation, the cell is displayed as empty.

How's it going? Do you know what to do with this automatic data entry form?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: witty Qiu Xiao E, 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.