Using Excel's VLOOKUP function to adjust salary scale in batch

This article mainly describes how to use the vlookup function in Excel batch digital adjustment, in order to quickly deal with a large number of changes in data, such as the batch adjustment of payroll.

Now there is a list, which lists only the payroll and the specific Shang amount, and requires that the salary be changed by looking up the corresponding personnel records from the payroll list. If you look for changes in the usual way, it's not easy for these dozens of people to change each one individually. In fact, borrow the vlookup function in Excel, a few seconds can be easily done. Don't believe it? Let's see how I realized it in Excel 2007.

　　New Shang Record table

Start with Excel 2007 to open the Payroll worksheet that holds the worker's payroll records. Create a new worksheet and double-click the Sheet tab to rename it to "Shang list." In columns A and b respectively, the names and Shang of Shang personnel are entered, and a positive salary for a pay increase is expressed in negative numbers (Figure 1). If you get a Shang list of computer documents is easier, you can copy it directly to use.

　　Show Shang amount in salary table

Switch to the payroll sheet, add a column (M column) to the right of the original table, and enter the formula in the M4 cell =iferror (B8, Shang list!). A:b,2,false), 0, and then select M4 Double-click the Black Small square (fill handle) in its lower right corner to copy the formula down to the cells in the M column.

Now Shang the person appearing in the list, the M column cell displays the amount of the payroll that the person is adjusting, and the person who does not need Shang displays 0 (Figure 2). The formula uses the VLOOKUP function to find and return the Shang amount by name from the Shang list worksheet, false to indicate an exact match. When a return #n/a error is not found, the Iferror function makes it appear as 0.

　　Quickly complete batch Adjustment

OK, now it's simple, select the M column in the Shang amount on the Payroll worksheet to copy, select the D column of the original payroll you want to adjust, and right-click to select Paste Selective. In the Paste Selective window that pops up, click to select the Value single option under Paste and the Add single option under Operations (Figure 3), click the OK button to paste, and immediately see that the salary in column D has been Shang according to the amount of Shang in the list.

The paste-only calculation function is valid for numbers and has no effect on the text in the header, so you can select the entire column directly to copy and paste. Note The value single option must be selected at the same time, or the D column cell format will be formatted like the M column Without Borders, fonts, and so on.

Do not delete the M column content after completing the Shang, you can right-click the M column to select "Hide" or by specifying the print area to let M column not be printed. The next time you Shang, just press the new Shang list to modify the Shang record in the Shang list, and then repeat it by selecting the M column, copying, pasting, and adding to column D to quickly complete the Shang.

Regular units also frequently need to remove the turnover record from the payroll according to the separation list. The same can be done quickly. All you have to do is type the list into the Shang list sheet, and the adjusted salary will be entered in 10. Return to the payroll worksheet to see that the M column for all outgoing people shows 10. In the M column, look for a cell that has a value of 10 right-click, select Filter/Filter by selected cells from the pop-up menu, and immediately see the record of the left person in the table, and all other records disappear. Now you can easily select all of the Employee records right-click Select Delete Row to delete. Finally click the clear icon in the sort and filter area of the Data tab to clear the filter settings to restore all payroll records.