Excelテーブルの集計行｜金額と数量を列ごとに確認する

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

Excelのテーブルにある「集計行」は、明細の下に列ごとの合計などを表示できる機能です。ただ、集計行を表示しただけで、必要な列がすべて期待どおりの合計になるとは限りません。金額と数量を並べた表なら、それぞれの集計セルで何を計算するかを確かめる必要があります。

この記事では、架空の4明細から始め、金額と数量を別々に合計します。その後、分類Aへの絞り込み、5件目の追加、手動で隠した行、隣の列への式の移し方を、一つの表でたどります。数字が変わったら、計算式だけでなく「どのIDが対象か」「どの列を足しているか」を分けて確認しましょう。

操作の説明はWindowsデスクトップ版Excelを想定し、Microsoftの公式資料を参照しています。Mac版やWeb版では入口や表示が異なることがあります。この練習ブックを実機で操作した結果ではなく、明細から別に計算した期待値と公式資料に基づく手順です。画像は操作画面ではなく説明用の生成イメージです。

見出し・明細・集計行を分け、二つの列の合計を準備する

4行の明細と二つの単位を用意する

まず、空のワークシートでA1:D1に「ID」「分類」「金額」「数量」と入力し、A2:D5に次の4行を入れます。IDと分類は文字、金額と数量は数値として入力します。金額の単位は円、数量の単位は個とし、セルの値には単位の文字を直接付けません。これは説明用の架空データであり、実際の売上や取引の記録ではありません。

ID	分類	金額（円）	数量（個）
R01	A	700	1
R02	B	1300	2
R03	A	500	3
R04	B	900	4

金額は700＋1300＋500＋900で3,400円、数量は1＋2＋3＋4で10個です。明細は4件なので、数量の10個を「10件」と読まないでください。R04は1件の明細ですが、数量の値は4個あります。同じ行を数える場合と、数量の値を足す場合は違う作業です。

分類Aに当たるIDはR01とR03です。この2行の金額は700＋500で1,200円、数量は1＋3で4個です。BのR02とR04は絞り込みでは対象外にします。まずこの二つの状態を紙などに書いておくと、後の確認で「合計が合わない」と気付いたときに、どの行を足すべきかへ戻れます。

表に含める範囲を指定する

A1:D5を選択して［挿入］から［テーブル］を選び、作成画面で範囲がA1:D5になっていることと、先頭行を見出しとして扱う設定を確認して［OK］を押します。先頭の「金額」「数量」は列名で、足す値ではありません。選択範囲に見出しと4行の明細が含まれているかを確認します。

見出しを含め忘れると、意図した文字が明細として扱われたり、別の列名が付いたりします。見出しの文字が各列の上にあるだけでなく、テーブルの列名として使われているかを確認しましょう。操作前に選択範囲を決め、ダイアログの範囲も見直すと、別の表まで含めてしまう間違いを減らせます。

この練習では、表の色やデザインは説明の中心ではありません。重要なのは、列名があり、その下にR01からR04までの4つの明細があることです。表の下に集計行を追加しても、その行は明細ではありません。集計行の3,400円を明細の金額へ再入力して足すと、同じ値を二重に含めることになります。

合計の目的を言葉にする

この表で知りたいのは、対象行の「金額の合計」と「数量の合計」です。全行を見る状態ならR01〜R04、分類Aに絞る状態ならR01とR03を対象とします。どちらの状態でも、金額の列と数量の列は別々に足します。対象行を決める軸と、足す列を選ぶ軸を混ぜないことが出発点です。

たとえば、3,400という表示だけをメモしても、その数字が円なのか個なのか、どのIDを含むのかは分かりません。集計結果の見出しと条件を残し、「全4件の金額3,400円」と読める状態にすると、後で分類Aに絞った結果と比較できます。実際の仕事では、各行に何を記録するかも先にそろえてください。

集計行を表示し、金額と数量でそれぞれ合計を選ぶ

入口はテーブル内を選択してから

作成したテーブルの中のセルを一つ選び、リボンの［テーブル デザイン］または［デザイン］にある［集計行］をオンにします。表示名や配置はExcelの版や画面幅によって異なる場合があります。テーブルの外を選んでいて該当するタブが見えないときは、先にテーブル内のセルを選び直します。

集計行は表の末尾に表示されます。ただし、スイッチをオンにしただけで金額と数量の両方が目的どおりの合計になると決めつけないでください。どの列にどんな計算が入るかは一列ずつ確認します。表に現れた数字が一つでも、もう一つの列が空欄や別の集計方法である可能性を切り分けましょう。

Microsoftのテーブルのデータを集計する公式説明には、集計行の表示と、セルで集計関数を選ぶ方法が掲載されています。以下の値は上の明細を手計算した期待値であり、画面を実際に操作して得た観測値ではありません。

金額のセルで「合計」を選ぶ

集計行の金額列にあるセルを選択し、表示される選択肢から［合計］を選びます。R01〜R04が対象なら、700円、1,300円、500円、900円の合計は3,400円です。表示が異なるときは、式を直す前に4行の金額が数値として入っているか、列の選択が金額かを見ます。

Excelの公式資料は、集計行で選んだ集計関数がSUBTOTALを使う例を示しています。式を見るときは、関数だけでなく、対象が金額列であることも確認します。テーブルの列名を使う参照を構造化参照と呼びますが、公式説明にある別の列名の例を、この教材の実際の式として書き写す必要はありません。自分のブックの数式バーに表示される式を見て、対象列を確認してください。

同じ「合計」でも、意図した明細が表の外にある場合や数値が文字として入っている場合には、期待値と表示が合わないことがあります。値を強引に合わせるよりも、まずR01からR04の金額、テーブルの境界、選んだ列の順に確認します。セルの表示形式で円を付ける場合も、値自体が数値であることとは別に考えましょう。

数量のセルでも、もう一度選ぶ

次に集計行の数量列のセルを選び、こちらでも［合計］を指定します。対象が全4件なら期待値は10個です。金額の3,400円と同じ操作名を選びますが、参照先は数量列です。金額のセルに出た3,400を数量へそのままコピーして正解にすることはできません。

数量セルが空欄のままであれば、金額の設定が失敗したのではなく、数量側の関数が未選択かもしれません。逆に数量が4と表示されたときは、「数量の合計」ではなく「明細件数」を数える設定になっていないか、選択した集計方法を見ます。4件と10個はいずれもこの表から説明できる値ですが、答えている問いが違います。

テーブルの計算列では、明細側に入力した式が他の行へ広がる場面があります。一方、ここで設定しているのは最下部の集計行で、各列の結果を選ぶ操作です。計算列の自動入力と、集計行の列ごとの選択を同じ手順として扱わないようにしましょう。確認したいのは、金額側が金額の合計、数量側が数量の合計を指しているかです。

二列を同じ対象行で読み合わせる

金額3,400円、数量10個という二つの期待値を、全4件という条件とセットにします。金額だけ正しくても、数量の参照が別の列であれば二列そろった集計にはなりません。逆に数量だけ合っている場合も、金額側を省略せず確認します。小さな練習表では、行ごとに足して一致を確かめられます。

表の下に複数の数字が並ぶと、左から順に読むだけで正しいと思いがちです。集計行は結果、上の行は元の明細、見出しは列の意味を示すものです。3つの役割を縦にたどると、どの値が計算の入力で、どの値が結果かを混同しにくくなります。

集計方法が異なるときの読み方

金額セルを選んだときに一覧で［平均］を選べば、足し算の合計ではなく対象値の平均を求めることになります。数量側で［個数］を選べば、数量の値の合計とは別の問いになります。選択肢が並んでいることは便利ですが、最初に書いた「何を知りたいか」と一致しているかが大切です。今回の問いは、円と個をそれぞれ足すことなので、二列とも［合計］を選びます。

金額と数量のいずれかを選び直した後は、もう一方のセルも確認してください。金額だけを合計にして数量を平均のままにすれば、二つの結果は違う計算方法の組み合わせになります。画面にエラーがなくても、見た人が両方とも合計だと受け取ると判断を誤ります。列名・集計方法・単位を一組として読む習慣が役立ちます。

元データの入力ミスを直したいときも、集計行に期待する数字を書き込んで合わせるのではなく、R01などの明細側を確認します。例えばR03の数量が3ではなく30と入力されていれば、全件数量の期待値10と合わなくなります。その場合はR03の入力値を調べ、意図を確かめてから明細を修正します。集計結果を見えなくする前に原因へ戻るための順番です。

絞り込みと手動非表示で、対象となるIDを確認する

追加前はAの二件を対象にする

4件のまま、分類のフィルターでAだけを表示する場合を考えます。表示対象はR01とR03、金額の期待値は1,200円、数量は4個です。R02とR04は削除したのではなく、フィルターの条件から外れています。元のデータから行がなくなったと判断しないでください。

この場面で変わるのは対象行です。金額を足す列が数量に変わるわけではありません。逆に、数量の集計セルが誤って金額を参照していた場合は、同じAの行だけを対象にしても、求めたい個数と違う値が出ます。対象行の切替と列参照の確認は別々に行います。

分類Aの合計を最初から式で条件指定したい場合は、SUMIFSで複数条件を集計する記事も参考になります。この記事で追っているのは、表示する明細を切り替えたとき、テーブル末尾の集計行がどの列をどう集計しているかです。用途が違うため、どちらか一方を常に正解と決めず、何を見たいかから選びます。

状態別の期待値を表にして比べる

次の表は、宣言したIDに含まれる数値を足した結果です。Excelで実際に表示された結果ではありません。各行の状態を別の分岐として読み、前の行のフィルターや手動非表示を無言のまま引き継がないようにします。

状態	対象ID	金額（円）	数量（個）
追加前・全件	R01、R02、R03、R04	3400	10
追加前・A	R01、R03	1200	4
追加後・全件	R01、R02、R03、R04、R05	4000	12
追加後・A	R01、R03、R05	1800	6
追加後・A、R03手動非表示を除外	R01、R05	1300	3

この表は、金額と数量を同じIDの集合から別々に合計したものです。数量の10個や12個は明細の件数を示していません。実際のブックで数値が一致しないときは、対象ID・数値の入力・集計行の選択・参照列を順に確認します。表示結果だけから原因を一つに決めつけることはできません。

手動で隠す行は、追加後の別の状態で比べる

ここからは後の章でR05を追加した状態を先取りして、考え方を比べます。R05は分類A、金額600円、数量2個です。追加後に分類Aだけを表示すると、対象候補はR01・R03・R05で、金額は1,800円、数量は6個です。まだR05を表へ入れていない段階で、この1,800円や次の1,300円を期待値として使わないでください。

追加後のA抽出から、R03だけを手動で非表示にしたと仮定します。手動非表示を除くSUBTOTALの109を使う場合、対象はR01とR05となり、金額は700＋600で1,300円、数量は1＋2で3個です。R03の500円と3個を除いているためです。これは示した条件に基づく計算例であり、今回のブックで操作結果を観測したものではありません。

一方、手動で隠したR03を含める設定なら、Aの3件を対象として1,800円と6個になります。また、フィルターや手動非表示にかかわらず全5件の明細を単純に合計する目的なら、4,000円と12個です。1,300円、1,800円、4,000円のどれが正しいかは、質問と対象範囲で決まります。表示されている数字だけでは判断できません。

一般にSUBTOTALの9と109は、手動非表示行を含むか除くかで使い分けます。フィルターで除外された行と手動で隠した行を区別し、実際に使う関数番号は数式バーで確認してください。Excel上の表示ID、手動非表示の状態や実際の式は、この練習では未観測です。

見えている行と、集計の対象を照らす

迷ったら、対象にしたいIDを先に書き出します。追加前のAならR01・R03、追加後のAならR01・R03・R05、追加後のAからR03を手動非表示で除くならR01・R05です。各IDの金額と数量をそれぞれ足すと、どの状態を話しているのか明確になります。

もし予想と結果が異なっても、すぐに式を別の関数へ置き換えないでください。まずフィルター条件がAか、手動で隠している行があるか、R05が表の明細に入っているかを順に調べます。そのうえで集計行の式と対象列を確認します。条件と参照を同時に変えると、何が原因だったのか分からなくなります。

集計行を残したまま明細を追加し、二列の変化を確かめる

まず全件表示の基準に戻す

ここでは元のR01〜R04がすべて表示され、手動で隠した明細がない基準状態に戻します。追加前の期待値は金額3,400円、数量10個です。前章のA抽出や手動非表示が残っていると、行追加による変化と表示条件による変化が混ざります。フィルターと行の表示状態を確認してから進めましょう。

追加するのはR05・分類A・金額600円・数量2個です。これは1件の明細であり、集計行の金額セルや数量セルに600や2を上書きする操作ではありません。集計行に値を書き込めば、設定した集計式を置き換えてしまうおそれがあります。新しい値は明細として表の中へ入れます。

明細の位置へ行を挿入する

集計行を表示したまま追加する場合は、表の最後の明細であるR04の行を選び、テーブル内の行を挿入できる操作から新しい明細行を作ります。例えばR04のテーブル内セルを右クリックし、挿入メニューにテーブル行の項目が表示される場合は、R04の上へ1行挿入して、その行にR05の4項目を入力します。これならR05がR04より上に並んでも、IDと各列の値は同じです。

メニューの表示はExcelの版などで異なります。挿入後は新しい行が表の見出しから集計行までの領域内にあり、ID・分類・金額・数量が同じ行に並ぶことを確認します。Microsoftのテーブルに行や列を追加・削除する説明では、テーブル行の挿入やサイズ変更の一般的な方法を案内しています。集計行をオンにした今回のブックで、ここに示した操作の成功を実機確認したわけではありません。

追加後にR05が表の外へ置かれたままなら、集計行がその値を含むとは限りません。特に表から離れたセルに書いた行まで常に自動拡張されると考えないでください。表の範囲を選択し直す必要がある場合は、公式のサイズ変更方法も参照します。既存データを上書きしないよう、練習用のコピーで操作してください。

5件の明細と二つの合計を別々に照合する

R05が明細として加わった後、全5件の金額の期待値は3,400＋600で4,000円です。数量は10＋2で12個です。件数は4件から5件へ増えますが、数量は10個から12個へ増えます。増えた明細の数と、追加した行の数量を同じ数字として扱わないでください。

照合のため、IDの集合がR01・R02・R03・R04・R05であることを確かめます。並び順がR01・R02・R03・R05・R04になっても、各IDの値が同じなら全件の整数和は変わりません。ただしIDや数値の入力先を1列ずらせば、正しく並べたつもりでも集計の対象が変わります。新しい行だけでなく、各列の見出しと値の対応まで見ます。

金額が3,400円のままなら、R05が表の明細に含まれているか、金額600が数値か、集計行の参照先が何を指しているかを調べます。数量が10個のままなら、数量2と数量側の集計セルを同じように見ます。二列とも変わらない場合と、一列だけ変わらない場合では、探す場所が違います。

追加後のAだけをもう一度確かめる

追加後に分類Aで絞ると、対象はR01・R03・R05の3件です。金額は700＋500＋600で1,800円、数量は1＋3＋2で6個です。追加前のAでは1,200円と4個だったため、600円と2個だけ増えます。BのR02とR04はこの条件では対象外です。

全件4,000円・12個と、A抽出1,800円・6個は、同じ5件の元データから異なる対象行を選んだ結果です。期待値を比較するときは、フィルター状態と手動非表示の有無も一緒に記録してください。前章の1,300円・3個は、さらにR03を手動非表示として除外する別の枝です。ここではR03を隠していないので使いません。

表に行を追加できたかと、追加した値が今回の集計目的に合っているかは別です。R05が表の一部でも、誤ってBと入力すればA抽出の期待値には入りません。金額や数量を訂正する際も、最初に分類・IDを確認し、元の明細を直してから結果を見直すと、集計行の数字だけを調整する誤りを防げます。

追加行が集計されないときに調べる順番

R05を書いたのに金額も数量も変わらない場合、まず入力した場所を見ます。R05のIDが表示されているだけでは、テーブルの明細に入ったことまでは分かりません。テーブルを選択したときの範囲や、表の末尾にある集計行との位置関係を確認します。R05が表外にあれば、表のサイズを調整するか、明細行を作って値を入力し直す方法を検討します。

次に分類を確認します。R05をAとして追加しても、Bだけのフィルターを残したままではAの行は表示対象になりません。R05が見えない理由をすぐ「追加されなかった」と決めず、フィルターを解除して元の明細を確かめます。全件表示でR05を確認してから、再びAに絞り、対象がR01・R03・R05になるかを見てください。

金額だけが変わり数量が変わらない場合は、R05の数量入力、数量セルの集計方法と参照先を確認します。数量だけが変わり金額が変わらない場合は、金額600と金額セル側を確認します。一度に両方の式と元データを変えてしまうと、どちらの変更で結果が変わったのか追えません。一つずつ調べ、変更前後の条件を残しましょう。

R05の行を誤って二回入力すると、単位が正しくても明細が重複します。IDを見てR05が一度だけ現れることを確認します。逆にR01〜R04のいずれかを上書きすれば、追加の600円だけが増えるとは限りません。集計行は元データの誤りを自動で判別するものではなく、表に含まれる値を指定された方法で集計する場所です。

隣列への移し方を比べ、金額参照が数量に残っていないか調べる

二つのコピーを同じ基準状態から始める

最後に、集計行の式を隣の列へ移す場合を比較します。まずR05までを含む表を用意し、全5件が表示され、手動で隠した行がない状態を基準にします。金額の期待値は4,000円、数量は12個です。二つの比較用コピーを別々に作り、同じ状態から操作を始めてください。

一方のコピーでは、金額列の集計セルを選んでフィルハンドルを隣の数量列へドラッグします。もう一方では、同じ金額列の集計セルをコピーし、隣の数量列へ貼り付けます。比較の目的は、同じように見える二つの操作で、式の参照先がどう扱われるかを確かめることです。元のファイルを壊さないよう、必ず練習用コピーで試します。

参照先が変わるかを数式バーで見る

Microsoftの集計行の公式資料は、構造化参照を含む式を隣接する集計セルへ移す場合、フィルハンドルでドラッグすると列参照が更新され、コピーして貼り付けると更新されないことを説明しています。ここでの比較はその公式説明に基づく予想です。今回の練習ブックで操作して確認した結果ではありません。

ドラッグした数量セルでは、数量列を対象にした参照へ変わるかを確認します。コピー貼付した数量セルでは、金額列を指す参照が残っていないかを確認します。式に出るテーブル名や列名は実際のブックの設定を読み、見本の名称をそのまま「観測された式」として扱わないでください。構造化参照の仕組みはMicrosoftの構造化参照の説明でも確認できます。

参照が金額列のままであれば、数量の位置に4,000という数字が出ることがあります。数量として求めたいのは12個なので、4,000円と12個を見比べて参照列と単位の不一致に気付きます。分類Aのみの状態なら、金額1,800円と数量6個を比べられます。ただし、ここで記した数は対象IDから計算した期待値であり、コピー操作で実際に表示された値ではありません。

貼り付けが常に悪いわけではない

コピーした式が元の金額列を参照し続けることが、別の目的では必要な場合もあります。問題は、数量列を集計したいのに、参照先を確かめないまま金額の結果を数量として読むことです。コピー貼付をすべての用途で誤りと決めつけず、そのセルが何を計算すべきかに照らします。

確実に各列へ目的の集計を設定したい場合は、第2章のように数量側の集計セルで［合計］を選び、式と結果の対象を確認する方法もあります。ドラッグ後に期待値が表示されたとしても、参照先と対象行の状態は一度確認してください。たまたま同じ数字になるデータでは、間違った列を参照していても見た目だけでは気付けません。

二つの操作を混ぜずに比較する

ドラッグ後に同じブックでさらにコピー貼付を行うと、先ほどの式が上書きされ、どちらの操作による参照か分からなくなります。別コピーを使い、操作する前の金額セル、操作後の数量セル、それぞれの対象列を記録します。実際に試せない場合は空欄のままにして、公式資料の記述と手計算の期待値を区別してください。

見た目の数字が合っているかだけではなく、式の参照先が数量かも見ます。この教材では金額4,000と数量12が大きく違うため誤参照に気付きやすくなっています。もし別の表で金額と数量の合計が偶然同じなら、数値の一致だけでは誤参照を見抜けません。列名、単位、参照という三つの確認を残す理由はそこにあります。

式を直した後も、Aに絞って1,800円・6個という別の条件で確認できます。最初の全件4,000円・12個に戻すときは、フィルターを解除したことを明記します。二つの基準を行き来するときに、前の条件が残っていないかを意識すれば、式とフィルターのどちらが結果を変えたかを追いやすくなります。

食い違いを三つの質問で切り分ける

結果が異なるときは、まず「どのIDが対象か」を聞きます。全5件か、分類Aの3件か、手動非表示を除く2件かで期待値が変わります。次に「足す列はどれか」を確認します。金額と数量が別の列を指し、円と個が混ざっていないかを見ます。最後に「表の明細に入っているか」を調べます。特に新規のR05が表の外にある場合は、行追加の確認から戻ります。

例として数量欄が4,000だった場合、全5件の金額の期待値と一致します。そこで数量セルの式を開き、金額の参照が残っていないか調べます。数量欄が10だった場合は、追加前の全件の数量と一致するため、R05が表の対象に入っているかと、その数量が数値で2になっているかを調べるきっかけになります。どちらも値だけで原因を断定せず、条件と式を確認します。

金額が1,800円ならAの3件の期待値と一致しますが、全件のつもりだった場合は分類のフィルターが残っていないかを見ます。1,300円なら、AのR01とR05を対象にしR03を手動非表示から除くモデルと一致します。それでも実際の手動非表示や式は未確認なので、実ブックの表示と数式バーを確かめる必要があります。

集計行を使うときは、列ごとに関数を選ぶこと、行を追加した後に表の範囲を確かめること、式を移した後に参照列を見ることが大切です。まずは4件の3,400円・10個から始め、5件の4,000円・12個、分類Aの1,800円・6個へ進めます。対象IDと単位を一緒に追うことで、合計の数字が何を表すのかを確認できます。