事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

Excelの機能を最大限に生かして、業務の省力化や経営状況を分析できる資料の作成方法を解説します。

指定した条件に一致するデータだけを集計する（その１）

Excel ピボットテーブル 改訂版

リストの中から「条件を満たす」データだけを抽出し集計する方法

 　IF関数を使えば、一覧表の中から指定した条件を満たすデータだけを抽出して集計することができます。

　IF関数は「条件」を１つしか設定できませんでしたが、Excel2007で「複数の条件」が設定できる「IFS関数」が加わりました。

　それまでのIF関数では「入れ子（ネスト）」しなければならないような複数条件を設定する場合でも、この「IFS関数」を使うことによって簡単に複数の条件を設定できるようになりました。

 　　　

　　　f:id:k-ohmori9616:20190801083302p:plain

「IF関数」にはいろいろな種類があります

  • COUNTIF 関数　　　「条件に合うセルを数える」
  • SUMIF 関数　　　　 「条件に合うデータの合計を求める」
  • AVERAGEIF 関数　  「条件に合うデータの平均を求める」
  • COUNTIFS 関数  　  「複数の条件に合うデータを数える」
  • SUMIFS 関数         　「複数の条件に合うデータの合計を求める」
  • AVERAGEIFS 関数　「複数の条件に合うデータの平均を求める」

 

 COUNTIF関数を使えば条件に合う「データ」をカウントすることができます

　指定した条件に一致するデータの件数を表示することができます。

　　　f:id:k-ohmori9616:20190731111937p:plain

 

  1.  「男性」に販売した件数                    「 ＝COUNTIF(「性別」,"男") 」
  2. 「35歳以上」に販売した件数             「 =COUNTIF(「年齢」,>=35) 」
  3. 「広島市内」の顧客に販売した件数   「 =COUNTIF(「居住地」,"広島市*") 」
  4. 「広島市以外」の顧客に販売した件数「 =COUNTIF(「居住地」,"<>広島市*") 」　　　

　　　f:id:k-ohmori9616:20190731114057p:plain

※ 文字列を「 " " 」で囲むことによって、検索条件とすることができます。

※ ワイルドカードの「 * 」（0文字以上の任意の文字列）や「 ? 」（任意の1文字）が使えます。

 

その他にもいろいろな集計方法があります

　SUMIFS関数やAVERAGEIFS関数を使えば、「ネスト（入れ子）構造」にすることなく、複数の条件で抽出して集計することができます。

  1. 販売金額が「A」の商品の売上金額合計「 =SUMIF(「金額」,"A*",「商品ID」) 」
  2. 商品を買った「男性」の平均年齢 「 =AVERAGEIF(「年齢」,"男",「性別」) 」
  3. 広島市内在住の50歳未満の顧客数  「 =COUNTIFS(「居住地」,"広島市*",「年齢」,"<50") 」
  4. 発送日が「9日～12日」の商品の個数「 =COUNTIFS(「発送日」,">=2016/3/9",「発送日」,"<=2016/3/12")
  5. 商品IDに「B」が付く商品を購入した女性の売上金額合計「 =SUMIFS(「金額」「商品ID」,"B*",「性別」,"女")」
  6. 広島市内在住で購入金額が1万円以下の女性の平均年齢「 =AVERAGEIFS(「年齢」,「居住地」,"広島市*",「金額」,"<=10000",「性別」,"女") 」

 

　　　f:id:k-ohmori9616:20190731095059p:plain

 

複数の関数を組み合わせることによって、さらに複雑な集計ができます

※ SUMPRODUCT関数は配列の対応する要素間の積をまず計算し、さらにその和を返します。

  1. 「三原市・岩国市・岡山市」の販売件数「 =SUM(COUNTIF(「居住地」,{"*三原市","*岩国市","*岡山市"})) 」
  2. 「注文日から3日以内に発送」する商品の件数「 =SUMPRODUCT( (「発送日」-「注文日」<=3)＊1) 」
  3. 「注文日から3日以内に発送」する商品の販売金額「 =SUMPRODUCT( (「発送日」-「注文日」<=3)＊「金額」) 」
  4. 月曜日に売り上げた販売数量「 =SUMPRODUCT( (WEEKDAY(「注文日」)=2)*「数量」) 」
  5. 商品IDの「下1桁が50以上」の商品の販売数量「 =SUMPRODUCT( (RIGHT(「商品ID」,2)*1>=50)*「数量」) 」
  6. 「三原市・岩国市・岡山市」に販売した件数「 =SUM(COUNTIF(「居住地」,{"*三原市","*岩国市","*岡山市"})) 」

 

　　　f:id:k-ohmori9616:20190731114830p:plain



　このようにExcelの関数を使うことによって、様々な集計をすることができます。

　しかし関数を使って複雑な数式を入力する必要がある点や、「集計用の枠」を作成しなくてはならないなどかなり手数（工数）のかかる作業になってしまいます。

　ピボットテーブルを使えば関数を使った複雑な数式を入力したり、集計結果を表示するために表を作り直さずに複雑な集計作業を行うことができます。

 

　ピボットテーブルはExcelに従来からある機能ですが、最新のバージョンでは「視覚的」に操作できるようになり使いやすくなりました。

 

ピボッテーブルで地区別と男女別でクロス集計した集計表とグラフ

　複雑なクロス集計の集計表やグラフが、ドラッグ操作だけで作成できます。

 

　　　f:id:k-ohmori9616:20190731130838p:plain

 

　（その２）では「ピボットテーブル」の機能を使って今回紹介したExcel関数を使った集計と同様の内容を試してみます。

　ドラッグドロップで簡単に操作できるだけではなく、条件の変更や複数の条件の組み合わせ、グラフ化など様々なことができます。

 

 

k-ohmori9616.hatenablog.com