1. Excelの入力と計算の基本
1.6. 条件を付けて集計
日々の売上や仕入dataから目的に合わせて集計結果を表示したいなど，「条件付きの集計」の手順と目的に合った関数選びのポイントを押さえておこう。
Excelで表を作る場合，あらかじめ集計欄などを用意し，そこにひとつの結果だけが表示されるように想定してきれいに表を作ろうとする人も多いでしょう。しかし，Excelではdataが入力された単純な表さえあれば様々な集計を行えます。次の具体例で考えてみましょう。
次の表は，ある小売店における仕入商品の週間日計表のsample(account.csv)です。
練習1：
グループ(項目別)集計：取引先別の取引金額を集計する。
元になる取引日計表をworksheetごとcopyし，新しいsheetで集計する。sheet名を「集計1」とする。
- sheet「集計1」のdatabaseを「取引先」で並べ替えておく
- 「データ(D)」⇒「集計(B)」を選択
メモ：「データ(D)」機能を利用するときは，必ず「項目名」を含めた範囲を指定する。
Fig. 集計dialog
- 集計dialogで[OK]buttonを押すと下図のような集計結果が現れます。
- 「集計」機能を使うと，表の左側に"アウトライン(outline)"が表示されます。これで集計結果の表示をoutlineのlevelで切り替えることができます。
- Levelの"2"のbottunをcrickしてみる
上のoutlineのlevel"2"の集計結果が得られたら，その表を他のworksheetにcopyして集計の報告書に使う表を作成する。
ただし，取引先別に１行ずつ「形式を選択して貼り付け」という操作が必要になる。
- Levelの"1"や"3"のbottonをcrickしてみる
- 元の表示に戻すには，「集計の設定」dialogの窓で「すべて削除(R)」buttonを押す。
条件を１つ指定した集計
練習2：
項目別の集計を自分で条件を指定して行い，結果も目的に合わせた表にする。
条件に合うデータを合計する「SUMIF関数」と条件に合うデータの個数を求める「COUNTIF関数」，このを条件付き集計に使う関数使ってみよう。
上の取引日計表で，商品種別が「事務用品」のdataだけを集計したいというように，１つの条件
を満たすdataのみを集計するには，「SUMIF関数」（分類は数学/三角）を使う
- 条件をCellの値で指定する
文房具AをCell "H4" に求めるならSUMIF(D3:D34,"="&H5, F3:F34)とする。（=値1&値2：＆は文字列の結合）
- 文房具を一度で集計するなら，SUMIF(D3:D34,"文房具*", F3:F34)とする。
Cell "H7" に「文房具」を入力し，Cell "I7" に結果を表示する。
- 個数を数える場合は，「COUNTIF関数」（分類は統計）を使う。事務用品の仕入取引回数を数えてみる。Cell "J3" に集計結果を表示する。
- 以上，より大きいなどの条件も使える。例として，項目「数量」が36個以上の仕入回数を調べる。Cell "H9" に「36個以上の仕入回数」を入力し，Cell "J9" に結果を表示する。
COUNTIF(E3:E34, ">=36")
複数条件付きの集計
練習3：
商品Aに対する会社Bと会社Cの取引額を集計し，比較したいという場合，商品種別が「商品A」で
かつ取引先が「会社B」という2つの条件を満たすdataを探し，集計する必要がある。
こうした複数の条件を指定した集計は「DSUM関数」（分類はデータベース）を使う。
練習として，取引先（いちまる堂，みくに屋）と商品種別（専門書，雑誌）の集計表を作る。
- 検索条件を設定する。Cell "H11"に「取引先」を，Cell "I11"に「商品種別」をcopyする。
。Cell "H12"に「いちまる堂」を，Cell "I12"に「専門書」をcopyする。これで，いちまる堂の専門書を求める準備ができた。
- Cell "I15"と"J15"にそれぞれ「専門書」と「雑誌」を入力し，Cell "H16"と"H17"に「いちまる堂」と「みくに屋」を入力
- Cell "I16"にいちまる堂との専門書の取引金額の合計を求める。
- 他の結果を求めるために，"I16" の数式を修正し，他のCellにcopyする。
引数「検索条件」の指定方法
| 条件の意味 | 例 | 入力例の意味 |
|---|
| ある文字と等しい | "語句1" | 「語句1」と等しい |
| ある文字で始まる | "Windows*" | 「Windows」で始まる |
| ある数以上 | ">=2000" | 「2000」以上 |
| 指定セルと等しい | B5 または "="&B5 | 「B5」セルと等しい | 指定セルより小さい | "<"&B5 | 「B5」セルより小さい |
課題 3:
今日の練習結果を「レポートの様式」で提出する。
詳細は [Exercise]のページで確認すること。
--
参考文献
１） 日経PC21 2003年7月号 p.28 "エクセルの「疑問・難問」一挙解決!
２） 朝日ビジネスPASO 2003年5月号 p.12 "表設計から集計術まで究極の10ルール