Office software is closely linked to the PDF,the PDF is also must have to control!
Set home Page Add to Favorites
|
The following details about the application of VLOOKUP function. In short, VLOOKUP function to search the region in accordance with the value of the left column to find out other areas of the data and return to the disaggregated data, for example letters, are not case-sensitive search. Therefore, to find the VLOOKUP function can be achieved two purposes: First, to find accurate. The second is to find approximate. Respectively below. (1) precise search - based on the value of the left column region, out of other data to find precise Example: Create a payroll and wage Article First of all, the establishment of employee payroll
A first step, copy the title bar The second step in the Department ID (A21) into the A001 The third step, in the name of (B21) to create the formula = VLOOKUP ($ A21, $ A $ 3: $ H $ 12,2, FALSE) Syntax explanation: the $ A $ 3: $ H $ 12 range (that is, the wage table) to find out precisely in line with the A21 cell line and the line of the contents of the second column into the cell. The fourth step, and so on, in the next cell to write the corresponding formula.
Example: In accordance with the corresponding total extraction ratio of different bonuses The first step in a project to establish the proportion of the total with the prize table, as shown in Figure 6. Projects are greater than the total number of cases. That the total range of 0 ~ 5,000 yuan, the ratio of 1 percent bonus, and so on.
= VLOOKUP (A11, $ A $ 4: $ B $ 8,2, TRUE) To achieve a specific ratio of 5% bonuses, in Figure 7.
MATCH function has two functions, the two operations return the value of a location. First, determine the area of a value in one of the exact location, the precise list of inquiries and had nothing to do whether or not to sort. The second is to determine the value of a given sorted list at the location, which does not require accurate matching. Grammatical structure: MATCH (lookup_value, lookup_array, match_type) In order to search lookup_value value. lookup_array: To find the area (must be a party or a). match_type: match the form of 0,1 and -1 have three choices: "0" that an accurate search. "1" that the search is less than or equal to the maximum value of search for, find the region for ascending order. "-1" Means search to find the value of greater than or equal to the minimum value, find the region must be arranged in descending order. Search more than if there is no matching value, then return # N / A. |
