メインコンテンツへスキップ
見出し画像

合計すると金額が倍になる表を、AIに直してもらう

    420,000円の売上が、合計を押した瞬間に840,000円になった。

    入力を間違えたわけではなく、金額の列をぜんぶ足しただけで、きれいに2倍の数字が出た。

    エラーは出ていない。赤いセルも警告の三角もどこにもなく、ただ金額だけがちょうど倍に膨らんでいた。

    原因は、表に紛れていた「小計」の行だった。

    部門ごとの売上を1枚にまとめるとき、見やすさのために「東京 小計」「大阪 小計」と、途中に小計の行を入れる。よくある作り方だ。

    その表が、これだ。

    画像

    東京・大阪・名古屋。それぞれの明細の下に黄色い小計の行があって、見るぶんにはたしかに分かりやすい。

    問題は、合計のときに出る。金額の列をまるごと足すと、明細に加えて小計まで一緒に数えてしまう。

    小計は、その上にある明細を足したものだ。つまり明細と小計を全部足すと同じ金額を2回数えることになり、ちょうど倍になる。

    合計してみたのが、これだ。

    画像

    840,000円。本来の420,000円のぴったり2倍で、小計の420,000円をまるごと二重に足した数字になっている。

    この手の間違いをどう直すか、AIに相談してみた。

    こう聞いてみた。

    画像

    📊 なぜ、赤いエラーにならないのか

    返ってきた式の前に、まず「なぜ気づけないのか」を押さえておきたい。

    SUM は、数値を足すだけの関数だ。その数値が明細なのか小計なのかは、見ていない。

    小計のセルも、Excelから見ればただの数値だ。足して何が悪い、という顔で足される。

    式の書き方は正しく、参照先もあり、計算もできる。エラーとして拾える問題は、何ひとつ起きていない。

    エラーが出るのは「計算できない」ときだ。「計算はできるが、意味が違う」ときには、Excelは黙っている。

    だから、それらしく大きい数字が、何事もなかったように出てくる。ここが一番こわいところだ。

    🔧 小計を除いて、明細だけ足す

    AIが最初に出してきたのは、短い式だった。

    =SUMIF(B:B,"<>小計",C:C)
    

    意味はこうだ。B列(項目)を1行ずつ見て、「小計」ではない行の、C列(金額)を足す。<> は「等しくない」の記号になる。

    小計を除いて、足し直した。

    画像

    420,000円。今度はきちんと合っている。

    列ぜんぶを指定しているので、明細が増えても式を直す必要はない。見出しの「金額」は文字なので、合計には混ざらない。

    AIは、答え合わせの式も添えてくれた。=SUMIF(B:B,"小計",C:C) で小計の行だけを足し、これが明細の合計と同じ額なら、小計に抜けがない証拠になる。

    ⚠️ でも「小計」の文字で除くのは、危うい

    ここで、こちらが聞く前にAIが釘を刺してきた。「小計」という文字で行を見分ける方法は、2つの向きで黙って間違える、という。

    画像

    ⚠️ 明細なのに除かれる … 「小計用メモ」のように、項目にたまたま「小計」が入っていると、その明細まで外れてしまう。

    ⚠️ 小計なのに除かれない … 「小 計」「部門計」のように書き方が揺れると、小計の行がそのまま残ってしまう。

    どちらもエラーは出ないので、文字の見た目から行の役目を当てること自体に、そもそも無理がある。

    ✅ いちばん確実なのは、区分の列を1つ足すこと

    ではどうするか。AIの答えははっきりしていて、表の作りを知っている人が「区分」の列を作り、明細か小計かを自分で書く、というものだった。

    区分の列を、足してみた。

    画像

    D列に「明細」「小計」と入れて、=SUMIFS(C:C,D:D,"明細") で足す。文字「小計」頼みを、やめる形だ。

    この区分は「データの入力規則」でリストから選ぶ形にしておくと、打ち間違いも防げる。

    小計を最初から SUBTOTAL で作る手もあって、=SUBTOTAL(9,C:C) は範囲の中にある別の SUBTOTAL を自動で除くので、二重にはならない。

    ただしこれは「式の種類」で見分ける仕組みなので、誰かが小計を手入力の数値に書き換えたら、また倍に戻る。区分の列ほど、頑丈ではない。

    どの行が明細で、どの行が小計か。これはExcelには分からない。表を作った人だけが知っている。だから、そこは人が書く。

    🔍 二重に足していないか、確かめる

    直したあと、ちゃんと直ったかを見る式も聞いた。確かめ方は、3つだった。

    画像

    ✅ 明細だけの合計と、小計の合計が同じか(同じなら、小計に抜けがない)

    ✅ 全部そのまま足した額が、明細のちょうど2倍か(2倍なら、小計が二重に入っていた)

    ✅ 小計の行数が、部門の数と同じか(=COUNTIF(B:B,"小計") で数える)

    これを1枚にまとめて、計算させた。

    画像

    明細も小計も420,000円で一致し、差は0。全部そのまま足すと840,000円で、ちょうど明細の2倍になっている。小計の行は3つで、部門の数と合い、判定は「一致 OK」と出た。

    下の赤い3行が、倍になった正体だ。840,000円から明細の420,000円を引くと、残りはまた420,000円。この分が、まるごと二重に足されていた小計になる。

    🧮 明細が増えても、ついてくる

    式は列ぜんぶを指定してあるので、あとから明細を足しても、そのまま追いかけてくれる。

    福岡の明細を1行、追加してみた。

    画像

    明細だけの合計が、420,000円から470,000円に、ひとりでに増えた。式を触る手間はない。

    📝 直すより、設計で起きなくする

    今日の話は、式そのものは難しくない。小計を外すだけなら SUMIF ひとつで直る。

    やっかいなのは、間違いが赤くならないことだ。SUMは意味を知らないまま、小計も明細も平らに足し、倍になっても、それらしい大きな数字として何も言わずに出してくる。

    式に任せていいのは「決めたルールを当てはめる」ところまで。どの行が明細でどの行が小計か、というルールそのものは、表を作った人が決める。

    見やすさのために入れた小計が、合計を狂わせる。それなら最初から、区分の列を1本だけ持たせておく。直すより、起きないようにするほうが、ずっと早い。


    🛠️ 自分の表でも同じことをしたいけれど、作る時間が無い。そういう時は、表をそのまま預かって組んでいる。

    作ったものは全部ここにまとめてある。

    Excelの「あるある」を、AIと一緒に片づける小ネタを、LINEでも配っている。気が向いたら、📩 のぞいてみてほしい。

    画像

    https://lin.ee/UqFmzfB

     
     
    \初心者OK!自動化・効率化できるExcel教室/ ◇AI×Excelの仕事時短術で自分の時間爆増! ◇自分だけの自動化ツールがつくれる! ◆元PC平凡▶半年で職場のExcel先生 ◆AIを使った自動化ツール作成数100超

    あなたへのおすすめ