思路和公式解释:
=LEFT(RIGHT(TEXT($A$2*$B$2/1%," ¥0; ¥-0;"),14-COLUMN()))
第一:$A$2*$B$2/1%,得到40000。目的是将A列所有数字由小数变为整数。
第二,=TEXT(数字," ¥0; ¥-0; "),根据text函数的正、负类型将数字前面添加人民币¥符号。
第三,left(right部分的结果),逐一提取第一个字符。
亿仟佰拾万仟佰拾元角分,共11位。14-COLUMN(A1),得到11,left和right结合提取数字。注意14是如何确定的,亿位是在c列,及列数为3,那么要保证从共11,则确定了14-column
公式依次如下(因具体数据而改变数据区域):
=MID(REPT(" ",COLUMNS($E$2:$M$2)-LEN($C2*$D2*100))&$C2*$D2*100,COLUMN(A1),1)
上述公式输入完后回车,向右,向下拉
=IF(SUMPRODUCT($C$2:$C$7,$D$2:$D$7)<0,"负","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(SUMPRODUCT($C$2:$C$7,$D$2:$D$7))),"[dbnum2]")&"元"&TEXT(RIGHT(INT(ABS(SUMPRODUCT($C$2:$C$7,$D$2:$D$7)*100)),2),"[dbnum2]0角0分")&"整","零角零分",""),"零分",""),"分整","分")
为了避免公式太长N1=SUM($E$4:$E$22)*1000000+SUM($F$4:$F$22)*100000+SUM($G$4:$G$22)*10000+SUM($H$4:$H$22)*1000+SUM($I$4:$I$22)*100+SUM($J$4:$J$22)*10+SUM($K$4:$K$22)+SUM($L$4:$L$22)*0.1+SUM($M$4:$M$22)*0.01)
E4=LEFT(RIGHT(" "&$C4*$D4/1%,10-COLUMN(A1)))
公式右拉,下拉M22
E23=LEFT(RIGHT(" "&$N$1/1%,10-COLUMN(A1)))
右拉
B23=SUBSTITUTE(SUBSTITUTE(IF(-RMB($N$1),IF($N$1>0,,$N$1)&TEXT(INT(ABS($N$1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB($N$1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF($N$1^2<1,,"零")),"零分","整")