Excelでフィルター後も合計が変わらない?表示中の行を足すSUBTOTALの9と109

「見えている行だけ、合計したい。ExcelでAIと確認」の文字。青緑で強調した架空の表の行と電卓を、小さなロボットが確認しているAI生成イラスト。実際のExcel画面ではありません。 AIで仕事をラクに

「未処理だけに絞ったのに、合計がさっきと同じ。いま見えている行だけ、足せないのかな。」

表の行は減ったのに、合計欄の数字が動かない。見えている数を手で足してみると違う数字が出て、どちらを報告すればいいか迷う。そんな表を目の前にしているあなたに向けて書きました。

私は月末の事務にExcelを使っていました。以下の表と数字は、AIがこの疑問を確かめるために用意した架空の例です。実際の帳票や取引データは使わず、Windows版Excelの新しいブックで計算して、保存せずに閉じました。

先に答えると、表示中の行の合計には =SUBTOTAL(109,B2:B6) を使えます。SUBTOTALは、行が隠れているかどうかを見ながら集計できる式です。B2:B6 はB列の2行目から6行目までを指します。式を入れても数字が合わないときは、行の隠し方、式が見る範囲、金額が数値として入っているかを分けると、どこで食い違ったか追えます。

同じ表に、役割の違う合計を3つ置く

◇(1) まず5行だけ用意します

A列を「状態」、B列を「金額」にします。1行目は見出しです。AIの検証では入力用の状態名に Open と Closed を使いました。以下では「未処理」「処理済み」と呼びます。手元で作るなら、状態名はその日本語のままで大丈夫です。

AI検証の初期データ。金額はすべて架空
Excelの行 A列 状態 B列 金額
2行目 未処理 120
3行目 未処理 240
4行目 未処理 60
5行目 処理済み 350
6行目 処理済み 80

◇(2) 式は表の下の空いた行に置きます

E9に =SUM(B2:B6)、E10に =SUBTOTAL(9,B2:B6)、E11に =SUBTOTAL(109,B2:B6) を入れます。SUMは指定した範囲の数値を足す式です。SUBTOTALの最初に入れる9と109は、どちらも合計を選ぶ番号です。AIの実験では、この3つの式を置いてから行の見え方を変えました。

合計と件数を出す場所は、表の下のE9からE13までです。後で7行目に明細を足しても、結果欄は絞り込みの範囲より下にあります。行を隠すと、その行にある右側のセルも一緒に隠れるので、結果は明細の行から離して置きます。左隣のD列に「全行の合計」など、式の役割を書いておくと見比べやすくなります。

3つとも、最初は850です。

比較を始める前にこの850を見ておくと、式に同じ5行を指定したことを手計算で確かめられます。実験では行の表示を変えたときに式の差を読めるように、金額は入力したままにしました。自分で試すときも、この最初の一致を確認してから行を絞ると、範囲の指定間違いを先に見つけられます。

未処理に絞ると850と420に分かれる

◇(1) 状態の列だけで絞ります

A1からB6までを選び、[データ]の[フィルター]を使います。フィルターは条件に合う行を残して見せる機能です。「状態」の見出しの矢印を開き、「未処理」だけを選びます。これで表の中では、120、240、60の3行が見えます。

表示された3つを足すと、420です。

AIが値を読み取ると、E9のSUMは850のままでした。B5とB6の350と80が画面から隠れても、SUMが見るB2からB6には残っています。E10とE11のSUBTOTALはどちらも420でした。フィルターの条件で外れた行を、9も109も集計から外した結果です。

◇(2) 850と420に名前を付けます

この表なら、850の横に「全5行の合計」、420の横に「表示中の合計」と書けます。確認する人が見ているのは、同じ金額欄でも答えの違う2つの数字なんです。絞り込みを変えながら確認する用途なら、数字のラベルに「表示中」と残しておくと、後から表を開いた人にも計算の条件が伝わります。

一方、未処理の金額を常に見たい場合は、「表示中」という条件自体が目的とずれます。別の人が処理済みに絞れば、表示中の合計も変わるからです。提出用に数字を写すときは、数字と一緒に「状態=未処理」のように選んだ条件も控えておくと、翌日に数字が違って見えたときに同じ条件を作り直せます。

行を隠す操作で9と109を比べる

◇(1) 絞り込みを解除し、3行目だけを隠します

フィルターの条件をクリアし、表をすべて表示します。左端の行番号3を右クリックして[非表示]を選びます。240の行が隠れますが、未処理の120と60、処理済みの350と80は表示されたままです。この4つを手で足すと610になります。

AIがExcelで読み取った3つの式の結果
行を隠した操作 SUM SUBTOTALの9 SUBTOTALの109
すべて表示 850 850 850
未処理だけに絞る 850 420 420
絞り込みを解除し、3行目を手動で隠す 850 850 610
未処理に絞った状態で、3行目も手動で隠す 850 180 180

9と109の差は、手動で隠した240です。

◇(2) 絞り込み中にも隠してみます

AIは別の操作順も試しました。未処理に絞り込んだ後、その状態で3行目を手動で隠したところ、実験したWindows版Excelでは9も109も180になりました。新しいブックで同じ操作を繰り返しても同じ結果です。この順番を試した範囲では、9にすると240が含まれると見込んで式を選ぶと、結果が食い違います。

Microsoftの仕様では、フィルターの結果から外れた行は両方の番号が除外します。手動で非表示にした行については、9が含み、109が除外する使い分けが示されています。式が入っている既存の表で両方の操作が混ざっていたら、確認用のコピーで一度フィルターをクリアし、行を再表示してから、必要な条件だけを選び直すと調べやすくなります。今回のように見えている行を足す用途には、109を使います。

合計が0なら、表示中の行数も見る

◇(1) 該当行がない0を作ります

AIはどの行にも当てはまらない条件で絞りました。このとき109の合計は0でした。次に3行目の240を数値のマイナス180に置き換え、未処理に絞ります。表示された120、マイナス180、60を足した結果も0でした。状態欄で未処理を選んだ場合は、実際に3行が見えています。

0だけでは、何行を足したか分かりません。

◇(2) 必ず埋まる列を使って行数を添えます

E12に =SUBTOTAL(103,A2:A6) を入れます。103は、表示中で値が入っているセルを数える番号です。この表では状態欄が全行にあるので、行数の目安として使えます。AIが測った結果は、該当なしなら0件、3つの数値が打ち消し合ったときは3件でした。

同じ0でも「該当する行がなかった」と「3行を足したら0だった」では、報告を受ける側が確認する先は変わります。自分の表で使うなら、空欄になり得る備考欄より、各行で必ず埋める欄を指定します。未入力の行がある列を数えると、表示中の行が残っていても少なく出るからです。合計欄のそばに件数を置いておくと、0が出た段階で次に何を確かめるか決められます。

表示が3行でも、数値として数えられる金額は2つ

◇(1) 240を文字として入れます

元の5行に戻し、AIはB3の240だけを文字列(入力した数字の形を、文字のまま保存したもの)にして、未処理に絞りました。セルには240と見えます。状態欄を数えるE12は3件のままでした。ところが9と109の合計は180で、120と60だけが足されています。

ここで見るのは、金額セルの中身です。240のセルが、合計に使える数値として入っていなかったんです。結果欄の3件と180を明細の金額と照らし合わせると、どの金額が落ちたか分かります。

◇(2) 数値が入ったセルだけの件数を出します

E13に =SUBTOTAL(102,B2:B6) を入れます。102は表示中のセルから数値を数える番号です。文字の240が混ざったとき、AIが読んだ件数は2でした。103で数えた状態欄が3件、102で数えた金額欄が2件なら、表示された行のどこかに、数値として数えられない金額欄があると絞れます。

セルを1つ確かめるなら、空いた場所に =ISNUMBER(B3) を入れる方法があります。ISNUMBERは指定したセルが数値かどうかを判定する式で、数値ならTRUE、文字ならFALSEです。空欄でも数値とは判定されないため、件数の差だけを見て文字の金額だと決めつけず、元の欄を確認します。訂正する場合は、控えの表で該当する金額を元の資料と照らし合わせてから、合計と件数を読み直します。

行を1つ足したときは、絞り込みと式の範囲を別々に見る

◇(1) 表で見える追加行を、式が見ているか調べます

AIは元の5行の下に、未処理90の行を追加しました。A1からB7までを対象に、未処理だけが見えるように絞り直します。画面には120、240、60、90が残りました。4つを手で足すと510です。それでも、E11の式が =SUBTOTAL(109,B2:B6) のままだと結果は420でした。

90が見えていても、式の終わりは6行目です。

E11の式を =SUBTOTAL(109,B2:B7) に広げると、510になりました。件数の式も7行目まで広げて4件を確認しています。90を加えた直後に式の数字が動かなかったときは、合計欄を選んで上の数式バーを見ます。どこまで計算する式なのかが書かれている場所です。

◇(2) 行が増える表では、列名で指定する方法もあります

明細を継ぎ足すたびに式の終わりを確認する表なら、Excelのテーブル(ひとまとまりのデータとして扱う表)にし、列名で計算対象を指定する方法があります。たとえばテーブル名が「明細」で、列名が「金額」なら、表の外に =SUBTOTAL(109,明細[金額]) と書けます。Microsoftによれば、テーブルに行を追加したり削除したりすると、この書き方の参照先も調整されます。

今回、AIが動かして確認したのはB2からB6をB2からB7へ広げる例です。テーブル方式を自分の表で使うときは、試しに1件だけ足して、追加したセルがテーブルの中に入り、合計と件数に反映されたところまで確かめると、次回も同じ場所へ追記できます。

小計が途中にある表では、二重に足していないか見る

◇(1) 明細と途中の小計を一緒に足します

AIは同じブックに別のシート「NestedSubtotals」を作り、A12に120、A13に240、A14にその2つの小計360、A15に60、A16に350を置きました。A14の式は =SUBTOTAL(109,A12:A13) です。そのシートのE12に =SUM(A12:A16) を入れると、結果は1,130でした。

120と240は、小計360の中にも入っています。

◇(2) 外側もSUBTOTALに変えて比べます

別シートのE13に =SUBTOTAL(109,A12:A16) を入れると、同じ範囲の集計結果は770でした。指定した範囲の中に別のSUBTOTALがあると、外側のSUBTOTALはその内側の式を飛ばします。今回足されたのは120、240、60、350です。

見た目に「小計」と書かれていても、式にとっては金額欄の中身がどう作られたかが重要です。途中の小計が数字の手入力や別の足し算なら、同じ結果を期待せず、確認用の表で明細だけを足した値と照らし合わせます。表を引き継いだときは、「小計」と書かれた行の数字をクリックして、何の式が入っているかを見てから最後の合計を扱うと、二重に足した分を見つけやすくなります。

数字を渡す前に、表示と範囲と件数を確かめる

◇(1) まず1件、結果がどう動くか予想します

「見えている行だけ、足せないのかな。」というとき、まず試しやすいのは、コピーした表で表示を2〜3行に絞ることです。紙や電卓で足した値と、109の値を見比べます。次に、金額が分かっている1行を手で隠してみます。その金額だけ差し引かれるかを見ると、画面の行と合計が同じ条件で動いているかを一度に確かめられます。

見た目の合計と式の結果が合わない場合は、先に式の末尾を確認します。表示は4行なのに数える式まで3行のままなら、両方とも追加行を範囲に入れます。範囲が合っていて状態の件数より金額の件数が少なければ、数字が文字になっているセルや空欄を調べます。この順なら、すぐに表全体の式を書き直さずに、違いが出たところを1つずつ切り分けられます。

◇(2) 合計と一緒に、何を選んだ数字かを残します

数字を渡すなら「未処理・表示3行・合計420」のように書けます。選んだ状態と件数があれば、相手も元の表で条件を再現できます。合計が0だったときも「該当0行」と「表示3行で合計0」を区別できます。

AIの確認は、Windows版Excelの1つの環境と、ここに載せた縦長の架空表で行いました。行が縦に並び、状態で絞り込む表での結果です。まずは同じ表で全行の合計と表示中の合計に名前を付けるところからで十分です。相手に渡す数字が、どの行から出たかを手元で説明できるようになります。

式の番号と対象範囲の仕様は、Microsoftの公式資料で確認しました。SUBTOTAL関数、フィルター処理、IS関数、テーブルの列名を使う書き方。

※架空表の検証と記事の作成はAIが行いました。画像はAI生成のイメージです。

この記事を書いた人
こむろ

製造業のひとり社長(合同会社6期目)。副業を5つ試して4敗1勝。FX・ブログ・コンサル・AI活用の実体験を、金額も時間もそのまま書いています。

こむろをフォローする
AIで仕事をラクに
シェアする
こむろをフォローする

コメント

タイトルとURLをコピーしました