Excel should be like this-3. Name the table: a complete transformation formula

In the previous article, we introduced removing the address reference in the formula by using the name column to make the formula easier to understand. However, the previously named columns are only for a single table. How to reference columns in other tables is the main content of this article. 1. Create a name table Create a table on the premise of naming a table. As described in the previous article, select a cell in the data area and press Ctrl + T. This document uses product sales as an example. First, create a product table, as shown in the following figure: Go to the menu formula> name MANAGER: In the pop-up name Manager dialog box, select the corresponding items of the table you just created, click the edit button above to modify the table name. In the edit name dialog box, you can see that the reference position of the table is from A2 to C5. If a new row or column is inserted, the reference location is automatically expanded. Of course, when deleting rows or columns, the reference position will also be reduced. For example, a blue mark is displayed in the bottom right corner of the table to identify the valid range of the table. If, for some reason, the table does not cover all the data (for example, empty rows, it is best to avoid empty rows), you can drag the blue mark with the mouse to expand the table range, such: 2. Create a sales table The process of creating a sales table is omitted (it can be different from the product table in sheet). You must enter the name and price when entering the sales table, and then enter the quantity to obtain the subtotal. The table is as follows: 3. Compile Formulas The most common method for finding a name through sheet is: the first parameter of Vlookup is the cell to be searched; the second parameter is the search area, which is referenced by the sheet name and column; the third parameter is the column to be taken; the fourth parameter indicates whether to perform fuzzy match. Modify the formula using the name column and name table method, change the first parameter of Vlookup to the name column, and change the second parameter to the name table. The formula becomes easier to understand immediately: 4. Optimization Formula The above formula contains a number 2, which indicates to search for the 2nd column in the product table. After you see this formula, you must first check the meaning of the 2nd column in the product table. If you insert a column before Column 2nd, this formula will produce an error. How to make the formula easier to understand and avoid errors after column adjustment, the solution is to use the named column. Here, we replace Number 2 with column (product [name]). This formula is used to find the column number of the "name" column of the "product" table. Because Name Reference is used, it is easier to understand even if the column is adjusted. 5. Summary The meaning of the first three articles must be consistent, that is, to change numbers and addresses that are not easily understood to be referenced by names to improve the readability of formulas. For example, if you visit the Guangzhou Institute, you can give him latitude and longitude, and you cannot find a place to death. However, he was told that the address was shop 35, building Mu, Taikoo Hui, No. 383, Tianhe Road, Guangzhou, so that he could find it. Remember that formulas are for people to understand, as long as the syntax is not correct, the computer can understand, but the logic of the formula needs to be determined by people. Tips When you enter a formula, Excel will list several candidate items. If you have not entered a complete formula and only one candidate item is left, press the "tab" key and the remaining letters will be automatically filled. When you enter the formula name, press Ctrl + A to bring up the function parameter dialog box. For example, the comment given here is incorrect for the last Vlookup parameter. If it is set to false, exact match is performed. If it is set to true, fuzzy match is performed. Download the sample Excel file: http://files.cnblogs.com/conexpress/name table example. Zip

 

From Weizhi note (wiz)

Excel should be like this-3. Name the table: a complete transformation formula