Format numbers in a spreadsheet
Custom number formatting
숫자를 한자로 표시하는 방법으로 엑셀에서는 간단히 사용자정의 표시형식에 [DBNum2][$-412]를 사용했는데, Google Spreadsheet엔 없는것 같다.(찾질 못했나?)
암튼,
MOD함수, QUOTIENT함수로 자릿수에 맞게 숫자를 뽑아내고, CHOOSE함수로 해당하는 한자(서수)와 매칭시킨 후, CONCATENATE함수로 정리하는 수순. (조악하지만 어지러운 IF문은 없다)
주의! 仟兆(10의 15승, PETA)단위에서는 에러!
 |
| 숫자를 한자(서수)로 |
=IF(ABS(A1)>=POWER(10,15),"Error! Greater than or equal to peta(P)",
CONCATENATE(
IF(A1<0,"(陰) ","")
,CHOOSE(ADD(1,QUOTIENT(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,16)),POWER(10,12)),POWER(10,3)),POWER(10,2))), "","壹百","貳百","參百","四百","伍百","六百","七百","八百","九百")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,16)),POWER(10,12)),POWER(10,3)),POWER(10,2)),POWER(10,1))), "","壹拾","貳拾","參拾","四拾","伍拾","六拾","七拾","八拾","九拾")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,16)),POWER(10,12)),POWER(10,3)),POWER(10,2)),POWER(10,1)),POWER(10,0))), "","壹","貳","參","四","伍","六","七","八","九")
,IF(QUOTIENT(MOD(ABS(A1),POWER(10,16)),POWER(10,12))>0,"兆","")
,CHOOSE(ADD(1,QUOTIENT(QUOTIENT(MOD(ABS(A1),POWER(10,12)),POWER(10,8)),POWER(10,3))), "","壹阡","貳阡","參阡","四阡","伍阡","六阡","七阡","八阡","九阡")
,CHOOSE(ADD(1,QUOTIENT(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,12)),POWER(10,8)),POWER(10,3)),POWER(10,2))), "","壹百","貳百","參百","四百","伍百","六百","七百","八百","九百")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,12)),POWER(10,8)),POWER(10,3)),POWER(10,2)),POWER(10,1))), "","壹拾","貳拾","參拾","四拾","伍拾","六拾","七拾","八拾","九拾")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,12)),POWER(10,8)),POWER(10,3)),POWER(10,2)),POWER(10,1)),POWER(10,0))), "","壹","貳","參","四","伍","六","七","八","九")
,IF(QUOTIENT(MOD(ABS(A1),POWER(10,12)),POWER(10,8))>0,"億","")
,CHOOSE(ADD(1,QUOTIENT(QUOTIENT(MOD(ABS(A1),POWER(10,8)),POWER(10,4)),POWER(10,3))), "","壹阡","貳阡","參阡","四阡","伍阡","六阡","七阡","八阡","九阡")
,CHOOSE(ADD(1,QUOTIENT(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,8)),POWER(10,4)),POWER(10,3)),POWER(10,2))), "","壹百","貳百","參百","四百","伍百","六百","七百","八百","九百")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,8)),POWER(10,4)),POWER(10,3)),POWER(10,2)),POWER(10,1))), "","壹拾","貳拾","參拾","四拾","伍拾","六拾","七拾","八拾","九拾")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(MOD(QUOTIENT(MOD(ABS(A1),POWER(10,8)),POWER(10,4)),POWER(10,3)),POWER(10,2)),POWER(10,1)),POWER(10,0))), "","壹","貳","參","四","伍","六","七","八","九")
,IF(QUOTIENT(MOD(ABS(A1),POWER(10,8)),POWER(10,4))>0,"萬","")
,CHOOSE(ADD(1,QUOTIENT(MOD(ABS(A1),POWER(10,4)),POWER(10,3))), "","壹阡","貳阡","參阡","四阡","伍阡","六阡","七阡","八阡","九阡")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(ABS(A1),POWER(10,4)),POWER(10,3)),POWER(10,2))), "","壹百","貳百","參百","四百","伍百","六百","七百","八百","九百")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(MOD(ABS(A1),POWER(10,4)),POWER(10,3)),POWER(10,2)),POWER(10,1))), "","壹拾","貳拾","參拾","四拾","伍拾","六拾","七拾","八拾","九拾")
,CHOOSE(ADD(1,QUOTIENT(MOD(MOD(MOD(MOD(ABS(A1),POWER(10,4)),POWER(10,3)),POWER(10,2)),POWER(10,1)),POWER(10,0))), "","壹","貳","參","四","伍","六","七","八","九")
)
)
