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

ExcelマクロVBAとエクセル関数についての私的雑感
最終更新日:2022-07-02

15桁を超える数値の足し算


ツイッターで出題した問題です。


エクセルでは整数値は15桁までの数値しか正しく扱えません。
15桁を超える数値は16桁目以下は0となってしまいます。
15桁の数値を合計して16桁になると、1の位が0になってしまいます。
そこで、16桁目も正しく1の桁まで計算する問題です。


出題ツイート

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

Excel エクセル クイズ
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桁超の数値なんて扱うもんじゃないと思います。


Excel エクセル クイズ
https://twitter.com/yamaoka_ss/status/1543165284483477504




同じテーマ「ツイッター出題回答 」の記事

連番を折り返して出力
コメントから特定形式の年月を取り出す
抜けている数値を探せ
15桁を超える数値の足し算
m/d/yyyy形式文字列を日付シリアル値に変換
成績表(ネ申エクセル)を別表に集計
年月に対して有効な日だけの入力規則のリスト作成
8桁数値が日付として不適切なら赤にする条件付き書式
条件付きの最大値と中央値("A"が2文字の条件)
日付時刻のマイナス表示に対応する方法
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する


新着記事NEW ・・・新着記事一覧を見る

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.エクセルVBAでのシート指定方法|VBA技術解説
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ