Chiquilin Site■05.02.08_Excel:複数条件での合計

その1:作業列を使って SUMIF関数で集計
その2:配列を用いて複数条件を集計
その3:データベース関数を使った集計
その4:ピボットテーブルを使った集計

その1:作業列を使って SUMIF関数で集計

まず先に。 Excel2007以降は COUNTIFSや SUMIFSといった複数条件での集計用関数が追加されてますので それを使えば すぐに計算できます。正直もうこのページは不要だと思っていますが一応残しておきます。(07.02.14 追加)
001
参考表001(Sheet1):画像はクリックすると初期状態に戻せます
ページの一番上へ
複数条件の値や個数を数える方法です。条件がひとつの合計/カウントなら SUMIF関数や COUNTIF関数で簡単に求まります。また 名前が「A」もしくは「B」の数量の合計 のようなOR条件での個数であれば これも SUMIF関数と SUM関数を組み合わせることで作業列がなくても一式で計算できます。
■任意セル
 =SUM(SUMIF(C3:C12,{"A","B"},E3:E12))
こちらは 今回は関係ありません。問題はAND条件及び別項目での条件が加わった場合です。名前が「A」でかつ商品が「あ」の数量の合計 の場合は別途作業列が必要です。
■F3セル:=C3="A"
■G3セル:=D3="あ"
■H3セル:=E3*F3*G3
それぞれ入力してフィルハンドルをダブルクリック
→変更を確認(クリックすると上の画像が切り替わります)
F・G列のような真偽を問う数式を論理式といいます。これらの計算結果は H列のような四則演算に利用することで「TRUE」は「1」 「FALSE」は「0」と同じ扱いになります。H列の合計が 名前「A」・商品「あ」の数量ということになります)。後は SUM関数で下記のように足し算するだけです。
■任意セル
 =SUM(H2:H12)
お互いを乗算しているのは どちらとも条件を満たしている時(AND条件)のみ「1」にする為で どちらか一方が条件を見たいしていればいい場合(OR条件)は 加算にします。「=(F3+G3)*E3」のような形です。またF・G列は省略してH列の作業列ひとつにまとめることもできます。
■ AND条件セル
 =(C3="A")*(D3="あ")*E3
■OR条件セル
 =((C3="A")+(D3="あ")>0)*E3
このような作業列を用いる方法の最大の利点は計算の負担が少ないことです。また余分なスペースが必要になる為 表の体裁を気にして利用を控える人もいますが 作業工程が確認しやすくなるので 協同で利用する表などには特に有効です。 Excel2007以降で追加されたテーブル書式との相性も良いです。

その2:配列を用いて複数条件を集計

002
参考表002(Sheet1):画像はクリックすると初期状態に戻せます
ページの一番上へ
続いて配列数式を用いた方法です。数式として一番すっきりします。
■任意セル
 =SUM(IF((C3:C12="A")*(D3:D12="あ"),E3:E12))
 Ctrl + Shift + Enter で確定
→変更を確認(クリックすると上の画像が切り替わります)
「Ctrl + Shift + Enter で確定」の説明はひとまず後回しにします。
それよりも「(C3:C12="A")*(D3:D12="あ")」この部分に首をかしげた方が多いのではないでしょうか。この部分が配列の計算になります。意味を理解するには まず最初の作業列を使った方法を先にご覧頂く必要があります。上の作業列で行っていた計算を一つの数式内でまとめて行うのが配列計算になります。つまり各行の計算結果を IF関数を使ってひとつずつ計算した結果をSUM関数で足し算しているということです。
初めの内は 配列の計算結果を理解しにくいかもしれませんので 計算結果の配列を確認する方法も提示しておきます。数式バーの「C3:C12="A"」この部分を選択した状態で F9キーを押してみて下さい。「{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}」という配列に変更されると思います。これが配列の計算結果です。Esc キーを押すと元に戻りますので 計算結果が想像できない場合は この操作で確認して下さい。
さて はじめに後回しにした「Ctrl + Shift + Enter で確定」の説明です。
この操作は「配列を使った計算をします」という宣言を表しています。普通には配列を計算できない関数を使う時に必要となります。今回は SUM関数を使ったので必要になります。逆に配列を利用することが前提の数式の場合 内部に配列があっても上記の操作は必要ありません。
■ 任意セル
 =SUMPRODUCT((C3:C12="A")*(D3:D12="あ"),E3:E12)
 =SUM(MMULT((C3:C12="A")*(D3:D12="あ")*E3:E12,1))
 =MINVERSE(1/SUM((C3:C12="A")*(D3:D12="あ")*E3:E12))
 =SUM(INDEX((C3:C12="A")*(D3:D12="あ")*E3:E12,0))
このような計算には「Ctrl + Shift + Enter で確定」は必要ありません。SUMPRODUCT・MMULT・MINVERSE・FREQUENCY・INDEXなどはそもそも配列を扱える関数だからです。また名前の定義の参照範囲に配列数式を入力した場合も「Ctrl + Shift + Enter で確定」は必要ありません。一方 AND・ORなど 配列の結果を個々に認識できない関数もあります。これらの関数は配列数式には利用することができませんのでご注意下さい。
 
最後に 配列数式のデメリットについて。配列数式は非常に数式が洗練されており 見た目がすっきりするので 慣れた人にとっては この上なく便利なものです。質問掲示板でもこの手の回答をたくさん目にします。しかし一方で配列の計算は一般的にはあまり浸透していませんし 計算が複雑になりがちです。他の人が見た時に理解し難いという問題もあります。また配列の計算全てを一式にまとめている以上 その計算には時間がかかります。量の多いデータには利用しない方が良いでしょう。

その3:データベース関数を使った集計

003
参考表003(Sheet1):画像はクリックすると初期状態に戻せます
ページの一番上へ
計算速度が速くて 且つ分かりやすいのが データベース関数です。フィルタオプションの設定のように条件を別セルに入力する必要はあるものの 複数条件などにも対応できます。また条件を書き出している為 見る人が理解し易いという利点もあります。
■C15セル:A
■D15セル:あ
■E15セル
 =DSUM(B2:E12,4,C14:D15)
 →変更を確認(クリックすると上の画像が切り替わります)
第二引数の「4」というのは テーブルの 4列目という意味で 上記の表でいう「数量」列のことです。データベース関数は 他にも DCOUNT・DAVERAGE・DGET など種類がたくさんあるので 状況に応じて使い分けることができます。ただしデータのテーブル機能と組み合わせると処理が重くなりますので注意して下さい。抽出したいものがたくさんある時に 使いづらいのが欠点ですね。
 
ただ融通が利くので 使い方次第では非常に有効です。例えば条件式をまとめることができます。
■B15セル
 =AND(C3="A",D3="あ")
■E15セル
 =DSUM(B2:E12,4,B14:B15)
これだけで済みます。活用方法については データベース関数で検索する を参考にして下さい。

その4:ピボットテーブルを使った集計(2005/02/16 追加)

003
参考表004(Sheet1):画像はクリックすると初期状態に戻せます
ページの一番上へ
もう一つピボットテーブルを使った方法があります。初心者の内は「自動」で処理することが素晴らしく思えるかもしれませんが 段々「自分が集計したい時に集計してくれる」方が処理の無駄がないことに気付いてくると思います。ピボットテーブルはそういう意味で非常に効率がいい方法です。 Excel を使うなら必ず覚えた方が良い機能だと思います。ピボットテーブルは本を読んで覚えるよりは 自分で感覚的に何度か触りながら習得した方がいいと思います。動画GIFでは限界がありますが 一応 操作手順を画像で表現してみました。
 →変更を確認(クリックすると上の画像が切り替わります)
ピボットの厄介なところは 元データが追加された時にいちいち範囲を指定し直さなければならないことです。追加がなければ 右クリックして[データの更新] だけで充分です。更新だけマクロを使う方法もありますが データ範囲を名前の定義で
[挿入]→[名前]→[定義]
[名__前]:範囲
[参照範囲]:
 =OFFSET($B$2:$E$2,,,MATCH(,$B:$B,-1)-ROW($B$1))
こんな感じで 最下行を可変にしておくという手もあります。無用に重くなるので あまりお勧めではありませんが。

他にも オートフィルタで抽出したデータを SUBTOTALで集計する方法やフィルタオプションの設定で別範囲に抽出する方法があります。あとセル範囲を多めに名前定義しておいて それを自参照する形で[外部データの取り込み]から データベースクエリ を指定して取り込む方法などもあります。結局やり方は千差万別なので データ量や利用頻度などを鑑みて ご自身に一番あった方法を選んで下さい。
関連:Excel 複数条件での検索方法

ChiquilinSite トップページへ Copyright(C) Chiquilin_site. All Rights Reserved.
inserted by FC2 system