ツイッター出題回答
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




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

オブジェクトのByRef、ByVal、Variant
「マクロの登録」で登録できないプロシージャーは?
抜けている数値を探せ
15桁を超える数値の足し算
ジャグ配列から順列を作成する
m/d/yyyy形式文字列を日付シリアル値に変換
成績表(ネ申エクセル)を別表に集計
シート内の全テーブルを1つに統合
VBA穴埋め問題「On Error GoToの挙動」
年月に対して有効な日だけの入力規則のリスト作成
8桁数値が日付として不適切なら赤にする条件付き書式


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

8桁数値が日付として不適切なら赤にする条件付き書式|ツイッター出題回答 (2022-08-10)
年月に対して有効な日だけの入力規則のリスト作成|ツイッター出題回答 (2022-08-10)
VBA穴埋め問題「On Error GoToの挙動」|ツイッター出題回答 (2022-08-09)
シート内の全テーブルを1つに統合|ツイッター出題回答 (2022-08-01)
VBAで漢数字を算用数字に変換|ツイッター出題回答 (2022-07-12)
成績表(ネ申エクセル)を別表に集計|ツイッター出題回答 (2022-07-09)
m/d/yyyy形式文字列を日付シリアル値に変換|ツイッター出題回答 (2022-07-07)
ジャグ配列から順列を作成する|ツイッター出題回答 (2022-07-05)
15桁を超える数値の足し算|ツイッター出題回答 (2022-07-01)
抜けている数値を探せ|ツイッター出題回答 (2022-07-01)


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

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




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


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



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