ツイッター出題回答
15桁を超える数値の足し算

「エクセル頭の体操」
たかし君は何を思ったか、過去の国家予算を合計することにした。
あれ、このエクセルおかしいぞ、最後が0になっちゃう・・・
1円単位まで正しく集計してください。
※数値はデタラメです、1円単位で公開されているかは知らん😇

https://twitter.com/yamaoka_ss/status/1542786842554433536

引用リツイート

出題時に用意した数式
=TEXT(SUM(INT(A1:A10/10^9))+INT(SUM(MOD(A1:A10,10^9)/10^9)),"#,###")&","&TEXT(MOD(SUM(MOD(A1:A10,10^9)),10^9),"000,000,000")
長いし、何の工夫もない・・・
3桁単位なら3,6,9,12のどこでも良いので、そこで分割して別々に合計。
分割下側からの繰り上がりの処理が入るので、どうしても長くなってしまいました。
しかしこの数式では、結果が9桁以下の場合の表示がおかしくなってしまいます。
,010,000,000
これはさすがに何とかしないと・・・
そこで9桁以下の場合で分岐させると、
=TEXT(SUM(INT(A1:A10/10^9))+INT(SUM(MOD(A1:A10,10^9)/10^9)),"#,###")&IF(SUM(A1:A10)>10^9,","&TEXT(MOD(SUM(MOD(A1:A10,10^9)),10^9),"000,000,000"),TEXT(MOD(SUM(MOD(A1:A10,10^9)),10^9),"#,###"))
さらに長くて、もう・・・
これでもマイナスの場合が正しくないのです・・・
マイナスの場合は、
INT関数とMOD関数が期待した結果になってくれません。
マイナスに対応するには、これらの関数を別の方法でやる必要があります。
INTはROUNDDOWNが使えます。
MODは自力でやるしかなさそう、数式の書き方は色々考えられますね。
=LET(
範囲,A1:A10,
桁,10^9,
商,範囲-ROUNDDOWN(範囲/桁,0)*桁,
余,SUM(商)-ROUNDDOWN(SUM(商)/桁,0)*桁,
TEXT(SUM(ROUNDDOWN(範囲/桁,0))+ROUNDDOWN(SUM(商/桁),0),"#,###")&
IF(OR(SUM(範囲)>桁,SUM(範囲)<-桁),","&TEXT(ABS(余),"000,000,000"),
TEXT(余,"#,###")))
もう訳わからない・・・
3桁で区切って計算を繰り返す場合も、繰り上がりと符号の扱いが大変そうですよね。
ということで、
問題を出しておいてなんですが、エクセルで15桁超の数値なんて扱うもんじゃないと思います。

https://twitter.com/yamaoka_ss/status/1543165284483477504

同じテーマの記事
新着記事
アクセスランク