The application of WPS form function in the entry of invoice value

Financial personnel must be inseparable from the various amounts of input and conversion. Sometimes a data is called repeatedly in the same table, and the WPS function makes the input simpler.

For example, the following invoices, if not through function calculation, need to have input four times. Not only is the work efficiency extremely bad, the probability of the error will also be greatly improved.

Picture Side Invoice

Solution: Quantity and unit price is variable, need manual input, the other three use function direct calculate Total price, then convert the total price into required format.

Step one: "Amount" formula

1. Select the cell G4 enter the formula, and then pull right to Q4; Select G4:g4 to fill the g9:q9.

=left (Right TEXT ($E 4* $F 4/1%, "￥#;;"), COLUMNS (A1: $K 1))

2. Formula Analysis:

E4 and F4 units are first multiplied by cells. In order to make the formula fill, the cell address is set to absolute column, relative row form, so that the formula can adapt to the change of reference unit when pulling the right pull down.

Then the addition of 1%, that is, 100 times times the expansion of the role is to eliminate the decimal point. such as "1.01/1%" that becomes 101, the convenient surface of the formula calculation;

Then use the text function to remove the quantity and unit price of 0 of the situation. We know that the cell format definition is divided into four paragraphs: [positive, negative, 0, text], in this case the second parameter of text is "￥#; When the unit price is 0 or negative, the result of the formula returns blank;

Then use the right function to extract the characters. Right is the function to extract n characters from the right-hand side of the string, used here because the second parameter columns (A1: $K 1) is dynamic, and the result of columns (A1: $K 1) decreases from 11 to 1 when the formula is pulled to the right, and the extracted string is also decremented;

Finally, the left function is used to extract the final result. The left function is the opposite of the right function-the character is extracted from the left-hand side of the string, and the second argument is optional. In this case, the length of the formula is shortened, and the second parameter is omitted, which means that the extraction length is the default value of 1. The second parameter in the argument of the text function is "￥#;", and the emphasis is on the preceding space. When you use the left function to extract a character, if the E4 and F4 product has a character length that is less than the number of digits in the current unit plus 1 (the number of digits, such as hundreds of millions), left can only be extracted to a space, if equal to the number of digits in the current unit plus 1 o'clock returns the renminbi symbol "￥", and then returns the single character in the product result bitwise, That is exactly what is needed.

3. This formula Three points: a) text function of the second parameter of the preceding space magical; (B) Two of the second parameters ";" The magical; C the right and left apply the formula to a bit to extract the number.

Step Two: "Total" formula

1. Select cell M10 input array formula "=text (SUM (E4:E9*F4:F9)," 0.00 ")", and press Ctrl+shift+enter three key at the same time, otherwise the formula will be wrong to the fruit. After the input, if the formula before and after the system automatically add curly braces that means the input error, see figure II shows. <

Figure Two array formula

2 Formula Resolution: The value of the E4:e9 area is multiplied by the value of the F4:F9, and then the sum is added, and the formula automatically ignores the blank area. In this case, the formula is much longer and the efficiency is worse if the array formula is not used. such as: =sum (E4*F4+E5*F5+E6*F6+E7*F7+E8*F8+E9*F9). The advantage of array operation is to shorten the formula and improve the efficiency, which can simplify the merging of multi-step operations in conventional formulas. Finally, the text function is used to format the array results to two decimal places.

3. Select cell M10:q10, click Merge Across columns on the menu, so that the cell results are centered in the area.

Using Center across columns instead of merging center is a factor group formula that cannot exist in cells that are merged in multiple cells. You use a range-by-column merge to display the same effect as "Merge and center," but you can live in peace with an array formula because you do not merge cells.

Figure three spanned columns merge cells

Step three: "Uppercase" formula

1. Select cell C10,C10 You can merge the center first, and then enter the formula:

=text (M10, "[dbnum2][$RMB]g/General format")

2. Formula Analysis: This formula through the text function to change the reference source M10 cell number display mode, so that in renminbi capital Square. [DBNum2] modified to [DBNUM1] and "g/General format" modified to "0" has a different effect, the reader can try their own.

　　Computer Tutorials Figure four RMB capital

Summarize:

The 1.Text function is a format function that can change the way the reference source is displayed. The first parameter is the reference source, and the second parameter is the display mode. This is a widely used function. In this case, three formulas have been used.

2. The worksheet should use the formula to simplify the repetitive entry of the digital data, and also improve the accuracy.