いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Excel)関数を使ったグループ化と集計

 Excelにはメニュー操作によるグループ化と集計の機能がありますが、ここではより簡単に関数を使って値をグループ化し、その値ごとに数値を集計する例を試してみます。

  • UNIQUE関数を使って値をグループ化した結果を得ることができます。
  • さらにSUMIF関数などを使ってグループごとに数値を集計することができます。

手順

グループ化の対象が1列だけの場合

f:id:accs2014:20200809120702p:plain:right:w400

 画像ではB列に果物の種類が、C列に個数が記録されています。
 同じ果物が何回かずつ現れますので、その種類ごとに個数を集計するものとします。

 

f:id:accs2014:20200809120706p:plain:right:w450

 まずはE3セルに「=UNIQUE(B3:B9)」と入力します。
 これで果物の種類について、重複を除いた結果を得ることができます。

 

f:id:accs2014:20200809120711p:plain:right:w500

 さらにF3セルに次のように入力します。
 これで果物の種類ごとに個数を集計した結果が得られました。

 F3セル

=SUMIF(B3:B9,E3:E5,C3:C9)

 見慣れない感じの式ですが、次の3つの式を同時に実行していると考えればわかりやすいと思います。

=SUMIF(B3:B9,E3,C3:C9)
=SUMIF(B3:B9,E4,C3:C9)
=SUMIF(B3:B9,E5,C3:C9)

 同様にしてCOUNTIF関数やAVERAGEIF関数を使えばグループごとの行の数や平均個数などを求めることができます。
 

f:id:accs2014:20200809120715p:plain:right:w500

 ちょっとした注意点です。
 上記の式の場合、元の表の値が変更されて果物の種類が増えた場合に、すべての果物を集計対象にすることができません(画像参照)。
 これは、SUMIF関数の第2引数(検索条件)としてE3からE5までの3つのセルしか参照していないからです。

 

f:id:accs2014:20200809120720p:plain:right:w500

 表の値の変更に対応するには、式を次のようにするのが1つの方法です(画像参照)。

=SUMIF(B3:B9,E3#,C3:C9)

 第2引数として「E3#」と指定しています。「#」はスピル範囲演算子と呼ばれ、「E3#」とすることでE3セルに入力された式(=UNIQUE(B3:B9))の結果全体を参照することができます。
 

f:id:accs2014:20200809120725p:plain:right:w550

 これにより、UNIQUE関数の結果(行数)が増減しても、SUMIF関数はそれに対応した結果を出力することができます。

 ただし、この例では元の表の行数の増減には対応していません(E3セルのUNIQUE関数は常にB3:B9を参照するため)ので注意してください。UNIQUE関数が参照する範囲を動的に変更するには別の対応が必要です。

グループ化の対象が2列以上の場合

f:id:accs2014:20200809131352p:plain:right:w450

 画像では、B列に果物の種類が、C列に産地が、D列には個数が記録されています。
 そこで、果物の種類と産地ごとに個数を集計するものとします(果物と産地の両方が一致するものを1つのグループとします)。

 

f:id:accs2014:20200809131357p:plain:right:w500

 まずはF3セルに「=UNIQUE(B3:C13)」と入力します。
 これで果物と産地について、重複を除いた結果が得られます。

 

f:id:accs2014:20200809131401p:plain:right:w550

 次にG3セルに次のように入力します。
 これで果物の種類と産地ごとに、個数を集計した結果が得られました。

 G3セル

=SUMIFS(D3:D13,B3:B13,F3:F9,C3:C13,G3:G9)

 ちょっと長いですが、これも次のような式を一度に実行していると考えればわかりやすいと思います。

=SUMIFS(D3:D13,B3:B13,F3,C3:C13,G3)
=SUMIFS(D3:D13,B3:B13,F4,C3:C13,G4)
 ・
 ・
 ・
=SUMIFS(D3:D13,B3:B13,F9,C3:C13,G9)

 同様にしてCOUNTIFS関数やAVERAGEIFS関数を使えばグループごとの行の数や個数の平均値などを求めることができます。
 

 さて、最初の例のようにスピル範囲演算子(#)を使って簡単に参照をしたいところですが、UNIQUE関数の戻り値の1列目や2列目だけを参照する適当な方法がありません。
 あえてやれば次のようになります。完全に芋筋ですが参考まで。

f:id:accs2014:20200809131406p:plain:w780

 G3セル

=SUMIFS(D3:D13,B3:B13,OFFSET(F3,0,0,ROWS(F3#)),C3:C13,OFFSET(F3,0,1,ROWS(F3#)))

 もちろんこれでも元の表の行の増減には対応できません(F3セルのUNIQUE関数は常にB3:C13を参照しているため)。UNIQUE関数が参照する範囲を動的に変更するには別の対応が必要です。