鈽?Lookup - ratio of the number and ranks of
Lookup job duties what is it? With a number and a line or a comparison of data in turn, found that match the value, the will of another set of data extracted from the corresponding value.
* Wage scale: numerical comparison
According to different tax rates for different wages is a common application. We look at this "wage rate query" table (see Figure 1). Now on the right side under the "income" (F series), directly corresponds to the "rate" (G column). No. 1 in the calculation of "tax", the input function the formula "= LOOKUP (F4, $ B $ 3: $ B $ 8, $ D $ 3: $ D $ 8)", carriage return, will be "36.00%."
Figure 1 Numerical comparison
The result is that how come? With F4 in the first one income was "$ 123,409", and left the table, "the lowest income," the file data ("$ B $ 3: $ B $ 8") were compared, although " $ 123,409 "in the" lowest income "number of the file does not match exactly the same data, but with one less than its maximum number of" $ 58,501 "match. This corresponds to the same line, "36.00%" to extract the.
* Book sales table: Comparison with text
Comparison of the number of Lookup functions can also be text. Query book sales in this table (see Figure 2), enter the following table of "number" (A15 cells) text as the query number, and the table of "number" 1 ($ A $ 3: $ A $ 11) comparing the query to match the text, the "material name" 1 ($ B $ 3: $ B $ 11) corresponding to the data extracted. The formula is "= LOOKUP (A15, $ A $ 3: $ A $ 11, $ B $ 3: $ B $ 11)".
Figure 2 Comparison of the text
鈽?Vlookup - ratio of the number and form
Lookup a big brother - Vlookup function. There are many similarities between the two brothers, but the ability to more Big Brother. Vlookup with comparative figures with a "table" in contrast, not the Lookup function of a one or a row, and Vlookup can choose Exact or fuzzy query method, and Lookup only vague query.
* Fuzzy matching
Fuzzy query with Vlookup function, almost exactly the same role with the Lookup. We use the Vlookup function to extract the first one in the example results of the wage rate. Function formula "= VLOOKUP (F4, $ B $ 3: $ D $ 8,3, TRUE)".
In this function, use the first one income "$ 123,409" (F4 cells) as a comparison number, use it with the left side of the table ("$ B $ 3: $ D $ 8") 1 of the number of columns compared Although the "$ 123,409" in the "lowest income" number of the file does not match exactly the same data, but the function of the last parameter is "TURE" ("TURE" is fuzzy query), so it will work with which less than it The maximum number of "$ 58,501" match. And table 3, column (the first three parameters function as a "3") corresponding to the data extracted, so the result is also a "36.00%."
* Order Schedule: Exact Match
Sometimes, we need to be upgraded. In the following "Order List" (see Figure 3), the last a "freight charges" in the data through "delivery methods" from the left, "Distribution Company Charges" in the match queries. This is a typical example of the exact query, first a data calculation function formula is "= VLOOKUP (H3, $ B $ 2: $ D $ 6,3, FALSE)".
Figure 3 exact matches
Tip:
The last argument from the "TRUE" changed into "FLASE", is the exact match. The exact query that queries the table with the query in column 1 in order to match the data exactly the same extraction, otherwise the result returned error value "# N / A".
Teacher commented:
Excel provides us with nearly 20 on "Find and quote" function, but the most common Lookup, Vlookup, there Choos, Row, Colum, Index and Match, etc., we can help see through the function of specific features. These functions are not alone, with other functions and Excel, with some functions. Hope that the data later, "find a friend", the no longer "People find it in thousands of Baidu," the sense of direct "Mu natural look back, that number has in the dim light," the.
相关链接:
MKV To VOB
Real to MP4
3GP to FLV
Career 12 Fatal Flaws
"Warehouse Assistant" Hot Promotions, Invites You To Try
X-window on the summary notes
Using Filter Effects Create Fantastic Images
"Dream Zhu Xian," Common Sense To Make Money: What Equipment The Most Valuable?
New Seasonal - Screen Savers
CUSTOMIZE the "Send to" menu
Compare Adventure And Roleplay
Youtube Watch 4
Switch to a three-year-old can not afford to skip how should I do?
FTP Clients Directory
No comments:
Post a Comment