「StiLL」Excel情報493　データ--リストから選択した集計方法で計算する表の作成

【テーマ】
今回はリストから集計方法を選択し、選択した集計方法で自動で計算する表の作成についてご紹介します。この方法で表を作成すれば、集計列を1列にまとめることができ、集計列を複数作成する必要がなくなります。

【方法】
集計項目に、「データの入力規則」の「リスト」から集計の種類を設定し、集計項目の列には「IF関数」で集計方法を分岐する数式を入力します。

【参考】
同じようなことは「SUBTOTAL関数」を使用して作成することも可能です。詳細は｢バックナンバー380　関数--SUBTOTAL関数とリストで集計方法を可変させる」をご参照ください。

今回はリストから集計方法を選択し、選択した集計方法で自動で計算する表の作成についてご紹介します。

この方法で表を作成すれば、集計列を1列にまとめることができ、集計列を複数作成する必要がなくなります。

「集計方法」を選択して、選択した「集計方法」で計算した結果を表示するようにします。

■リストの設定

　　　下図を例として、 F5 セルに「集計方法」を選択できるリストを設定します。

　　　今回は「集計方法」として、「合計」「平均」「最大」「最小」の4種類を設定します。

※

リストについては、以下のバックナンバーをご参照ください。

■IF関数の設定

下図の F6 セルに「IF関数」の設定をします。

　　　F5 セルに「合計」が選択された場合は、1~3月の合計を求める「SUM関数」、

　　　「平均」が選択された場合は、1~3月の平均値を求める「AVERAGE関数」、

　　　「最大」が選択された場合は、1~3月の最大値を求める「MAX関数」、

　　　「最小」が選択された場合は、1~3月の最小値を求める「MIN関数」で処理する数式を設定します。

　　　F6 セルに数式を入力後、 F13 セルまでコピーします。

※「IFS関数」および「SWITCH関数」を使用すると、数式を簡略化できます。

ただし、上記関数は、Excel2019以降のみ対応しています。

(Office365ユーザーの場合は、Excel2016も対応)

・　「IFS関数」の場合

=IFS($F$5="合計",SUM(C6:E6),$F$5="平均",AVERAGE(C6:E6),

$F$5="最大",MAX(C6:E6),$F$5="最小",MIN(C6:E6))

※「IFS関数」については、以下のバックナンバーをご参照ください。

・　「SWITCH関数」の場合

=SWITCH($F$5,"合計",SUM(C6:E6),"平均",AVERAGE(C6:E6),

"最大",MAX(C6:E6),"最小",MIN(C6:E6))

■集計方法の選択・結果の表示

下図の F5 セルのプルダウンで「合計」を選択すると、1~3が月の合計値を計算します。

　　　同様に「平均」を選択すると、1~3月の平均値を計算します。

　　　「最大」「最小」を選択した場合は、1~3月の最大値、最小値を算出します。

■ご参考までに

　　　上記と同様なことは、「SUBTOTAL関数」を使用して作成することも可能ですが、

　　　集計方法に設定できる種類が11種類のみです。

※

詳しくは、以下のバックナンバーをご参照ください。