Excel2007からの新しい関数「MAXIFS」と「AVERAGEIFS」を使って、複数の条件から最大値を求めてみます。
表からある条件での「最大」や「平均」を求める際に使う関数が「MAX関数」と「AVERAGE関数」です。
従来の「MAXIF関数」や「ARERAGEIF関数」を使って20歳台の「最大」と「平均」を求める場合、「入れ子（ネスト）」を使う方法で複数の条件「 >=20 」と「 <30 」の２つの条件を設定していていました。
新しい「MAXIFS関数」と「AVERAGEIFS関数」では、複数条件を設定できるようになりました。
そして、このように複数条件から「最大」や「平均」を集計する際にも「ピボットテーブル」はとても便利に使うことができます。
Excel2007からの新機能「MAXIFS関数」や「AVERAGEIFS関数」を使えば、複数の条件から「最大値」や「平均値」を求めることができます
「MAXIFS関数」や「AVERAGEIFS関数」はネスト（入れ子）構造にすることなく、「20歳以下」と「30歳未満」という複数の条件を設定することができます。
- 数式「 =MAXIFS（参照範囲 , 年齢 , ">=20" , 年齢 , "<30") 」
- 数式「 =AVERAGEIFS（参照範囲 , ">=20" , 年齢 , "<30") 」
※ 他にも「SUMIFS関数」「IFS関数」なども、同様に複数条件が設定できる。
同じことを「ピボットテーブル」を使ってやってみます
ピボットテーブルを使えば「関数」を使うことなく最大値を求めることができます。
グラフと「年齢」のスライダーも加えてみます
「スライサー」を使えば、任意の年齢で絞り込むことができます。
スライサーを使って２０歳台を表示します
スライサーを使えば、簡単に条件を変えて集計をすることができます。
- 「誰が１位なのか」の情報
- ２位・３位がだれなのか
- １位とそれ以外にどれくらい差があるのか（分布状況）
「平均」もコマンド1つで簡単に集計できます
ピボットテーブルに用意されている集計方法の「平均」を選択するだけで平均値を求めることができます。
具体的な操作方法
MAXIFS関数の設定方法
「MAXIFS関数」は「条件１」「条件２」「条件３」のように複数の条件を追加することができます
ピボットテーブルを使えば、関数を使わずに集計することができます
- 集計する「表」のどこか1カ所を選択します
- 「挿入」タブを選択
- 「ピボットテーブル」をクリック
ピボットテーブルが作成されました
集計の条件にするフィールドをボックスにドラッグするだけで設定できます。
- 「名前」を行のボックスへドラッグ
- 「年齢」を列のボックスへドラッグ
- 「スコア」を∑値へドラッグ
「年齢」を１０歳ごとに表示することもできます
ピボットテーブルに用意されている「グループ化」で設定することで、集計結果を「10歳ごと」に表示することができます。
- 行ラベルの「年齢」のどこか１カ所を選択して「右クリック」します
- ボックスで「グループ化」をクリックします
- 先頭の値を「20」に設定
- 末尾の値を「60」に設定
- 単位を「10」に設定
行ラベルの「年齢」が10歳ごとにグループ化されました
グラフを表示します
- ピボットテーブルのどこか1カ所を選択します
- 「ピボットテーブルツール」を選択
- 「分析」タブを選択
- 「集合縦棒」を選択
集合縦棒グラフが表示されました
「年齢」のスライサーを作成します
- ピボットテーブルのどこか１カ所を選択します
- 「ピボットテーブルツール」を選択
- 「分析」タブを選択
- 「スライサーの挿入」をクリック
- スライサーの挿入ボックスで「年齢」にチェックを入れる
スライサーが作成されました
集計方法を「平均」に変更します
- 「年齢」を行のボックスへ
- 「スコア」を∑値のボックスへ
- 「値」のラジオボタン「▼」をクリック
- 「値フィールドの設定」をクリック
- 値フィールドの設定で「合計」を選択
集計方法が「平均」に変更されました
グラフに「データラベル」を加えます
データラベルを「外側」に設定します
- 「グラフ」をクリックして選択状態にします
- 「ピボットグラフツール」を選択
- 「デザイン」タブを選択
- 「グラフ要素を追加」を選択
- 「データラベル」を選択
- 「外側」を選択
データラベルが追加されました
このように「最大値」や「平均値」を知りたいときに、ピボットテーブルを作成することにより簡単にいろいろな情報を知ることができます。
１０歳刻みを、５歳刻みに変えるなど色々な変更が簡単にできます。
又、グラフも簡単に作成可能なので「視覚的」に情報を把握することもできます。
関数を使わずにピボットテーブルを使う最大のメリットは「条件を簡単に変更できる」ことです。
関数を使う場合は条件を変えるためには数式を書き換える必要があり、大変な作業になります。
ピボットテーブルを使えば、ダイス分析などの複雑な集計方法も簡単に設定することができます。
職員の年齢分布など職員台帳を「ピボットテーブル」化することで簡単に調べることができます。
- Excel2007からの新しい関数「MAXIFS」と「AVERAGEIFS」を使って、複数の条件から最大値を求めてみます。
