Not long ago, the leader assigned a node task, which required statistics on which application systems of the company owned by each employee of the company, and what the corresponding login accounts are. The company has five application systems, such as ERP, office automation, instant communication, report query, and part design. The above application systems are independent of each other in business and cross each other. For example, Zhang San only has two sets of system rights for ERP and part design. Li Si has the authority of all systems except the part design system. Through analysis of the account database of each system database, ERP has 100 users, office automation has 300 users, instant communication has 200 users, report query has 150 users, parts design has 500 users, and the whole factory has 1000 employees. . Faced with such huge and irregular data, how to easily and accurately count the “employee, system user mapping table” in the shortest time? It is obviously not practical to manually build a table, or to explore the powerful functions of the Execl function. First, create a workbook for the “Map Table” by Execl, design the structure of the “User Mapping Table”, and import all employee names (Figure 1). Secondly, the account table exported from each application system database is saved as an Execl form, and is imported into the “map table”, and the content is uniformly modified, and only the name and account fields in the account table are retained. Next, we start designing according to the VLOOKUP function syntax. In the B3 form of the “User Mapping Table”, enter the formula: “=VLOOKUP (user mapping table! A3, ERP account table! $A$3: $B$11, 2,0)", meaning, in the A3 to B11 area of the "ERP account table", look for the A3 data of the "user mapping table", if it matches, then the "ERP account table" The data in the second column (ie column B) is copied to the B3 table. Since the user mapping table !A3” in the formula uses relative values, the B3 column is selected, and the mouse is pulled down to drag the filling formula in the data area. According to the above formula, other application system accounts are automatically populated in the “User Mapping Table” (Figure 2). Finally, we only need to modify the "user mapping table" slightly. Copy the “User Mapping Table” data. In the newly created worksheet, right click and select “Select Paste”. In the pop-up window, select “Value”, and then leave only the value. , and remove all the formulas, then press the shortcut key Ctrl+F, pop-up "Find and replace" window, switch to the "Replace" tab, enter #N/in the "Find content" column. A, keep “ replace with & rdquo; column is empty (Figure 3), click & ldquo; replace all & rdquo;, get the final result (Figure 4). Tip: Open an Execl form arbitrarily, click the Insert Function icon in the toolbar, and in the pop-up "Insert Function" window, the wizard will guide you to find the function that meets the requirements. In the "Common Functions" box, select the VLOOKUP function, and immediately display the meaning of the function in the lower edge of the window: the first column of the search table area satisfies the conditional element, and determines the row number of the cell to be retrieved in the area, and then further Returns the value of the selected cell. This article comes from [System Home] www.xp85.com