Summarize and organize several patterned formula usages in Excel

This article brings you relevant knowledge about excel. It mainly organizes several issues related to the usage of standardized formulas, including filling in voucher amounts in columns and splitting accounting accounts. , convert Chinese uppercase amounts, etc. Let’s take a look at them together. I hope it will be helpful to everyone.

Relevant learning recommendations: excel tutorial

1. Fill in the voucher amount in columns

as shown below , is a simulated payment voucher, in which column F is the total amount of the goods. It is necessary to display the amount in columns G~P, and add the RMB symbol (￥) before the first digit

Pattern solution: Enter the following formula in cell G5 and drag it right and down.

=IF($F5,LEFT(RIGHT(" ￥"&$F5/1%,COLUMNS(G:$P))),"")

Note that there is before the RMB symbol One space, don't miss it.

2. Split accounting accounts

As shown in the figure below, column A is some accounting accounts, and "/" is used to separate accounts at all levels. It needs to be extracted from columns B~D. Names of subjects at all levels.

Enter the following formula in cell B2 and copy the formula to the right and down.

=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMN(A1)*99-98,99))

Note the REPT function The first parameter is enclosed in double quotes with spaces, don't miss it.

3. Convert the amount in Chinese uppercase letters

As shown in the figure below, column B is the amount in lowercase, which needs to be converted into Chinese uppercase amounts.

#Enter the following formula in cell C3 and copy the formula downwards.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(B3<0,"负","")&TEXT(INT(ABS(B3)),"[dbnum2];; ")&TEXT(MOD(ABS(B3)*100,100),"[>9][dbnum2]元0角0分;[=0]元整;[dbnum2]元零0分"),"零分","整")," 元零",)," 元",)

Some friends may have said, what should I do if I can’t understand such a complicated formula?

It doesn’t matter. Since it is patterned, it means that the usage is already fixed. When necessary, just replace the cell address in the formula with the actual cell where you store the data. No need to do anything else. consider.

Related learning recommendations: excel tutorial

The above is the detailed content of Summarize and organize several patterned formula usages in Excel. For more information, please follow other related articles on the PHP Chinese website!