下面是小编为大家整理的wps公式（全文完整）,供大家参考。
wps 公式 =CONCATENATE (A2, "), B2) separates A2 B2 from intermediate content by spaces
=A2&B2 combines A2 B2
=left (a2,3) extracts the first three bits of data information from the A2 cell
=RANDBETWEEN (01000) randomly fills the value between 1 and 1000
=INT (RAND () *1000+1) randomly fills the value between 1 and 1000
=COUNTIF (A$1:A$24, A1), counting the number of duplicate data that is the same as the A1 cell
The following example illustrates how the VLOOKUP function is used.
Assume that in Sheet1 stored wheat, rice, corn, peanuts and other agricultural products sales unit price:
A B
1 unit price of agricultural products
2 wheat 0.56
3 rice 0.48
4 corn 0.39
5 peanuts 0.51
.......................................
100 soybean 0.45
Sheet2 is the list of sales, and each time the list is filled with different requirements: enter the name and quantity of the agricultural product in Sheet2, and automatically generate unit price and sales according to the data of Sheet1. Set the table to Sheet2:
A B C D
1 name, quantity, unit price and amount of agricultural products
2 rice 10000.48480
3 corn 20000.39780
.........................................................
Enter the formula in the D2 cell:
=C2*B2;
Enter the formula in the C2 cell:
=VLOOKUP (A2, Sheet1, A2:B100, 2, FALSE).
As in language, this is to find the value of the Sheet2 table cell A2 in the first column of the Sheet1 table A2:B100 region, and then return the value of the second column of the row after it is checked.
Thus, when the name entered in the Sheet2 table A2 cell changes, the unit price in the C2 changes automatically. Of course, as the single values in Sheet1 change, the corresponding values in Sheet2 change as well.
The formulas for other cells can be written in the form of fill.
VLOOKUP function usage notes
When it comes to the VLOOKUP function, I"m sure everyone will use it, and I"m using it very well. However, there are several details, we should pay attention to the use of good.
A.VLOOKUP syntax
The full syntax of the VLOOKUP function is this:
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
1., there are four arguments in parentheses, which are required. The last parameter, range_lookup, is a logical value. We often enter a 0 word, or False, and can actually enter a 1 word, or
true. What"s the difference between the two? The former represents a complete search, and the error value N/A is returned if it is not found; the latter first looks the same,
Can not find, and then find a very close value, but also can not find, also had to return error value N/A. It doesn"t really mean anything to us either, just to satisfy curiosity, and friends who are interested can experience it.
2.Lookup_value is an important parameter that can be numeric, literal, string, or referenced. We often use reference addresses. When you use this parameter, you have two important reminders:
(A) the cell format category of the reference address is consistent with the category of the cell format to be searched. Otherwise, sometimes the information is visible, but it cannot be caught. Especially when the value of the reference address is numeric, the most obvious is that if the cell format of the search is text, though it looks like 123, it won"t be able to catch anything.
And format categories in the data is not entered, we must first determine the good, if the data are entered, and found that the format does not match, it is too late, if you want to catch, you need to re-enter.
B) second point reminder is the use of a convenient and practical skills, I believe many people have long been aware of. We use the reference address, sometimes need to be fixed on a lattice in the value of lookup_value, and to use the
drop-down form (or copy) function will be added to the new cell, here will be used "$" this symbol, it is a fixed sign. For example, I always want to capture data in D5 format, so you can make D5 like this: $D$5, no matter how you pull and copy, the function always takes data with the value of D5.
3.Table_array is the scope of the search, and col_index_num is the number of columns within the range. Col_index_num can not be less than 1, in fact equal to 1, there is no practical use. If there is one such error value, #REF, it may be the total number of fields in which the value of col_index_num exceeds the range.
=VLOOKUP (H3, Sheet3, $B$1:B100, 2, FALSE)