いきなり答える備忘録

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

(Excel)グループ別の最大値・最小値を求める

 Excelで、多数の数値を個々のグループごとに区別して、それぞれの最大値・最小値を求める方法についてです。

  • FILTER関数とCOUNTIFS関数を組み合わせて、グループ別の最大値・最小値を求めることができます。

手順

 画像では、B列に氏名が、C列にスコアの数値が並んでいます。
 そこでE3セルに式を入力し、それぞれの氏名ごとのスコアの最大値を求めています。

f:id:accs2014:20201112095305p:plain:w700

 E3セル

=UNIQUE(FILTER(B3:C11,COUNTIFS(B3:B11,B3:B11,C3:C11,">"&C3:C11)=0))

 FILTER関数とCOUNTIFS関数を組み合わせて、「同じ氏名を持つ行のうち、より大きいスコアが存在しない行」だけを抽出しています。不等号の向きを逆にすると最小値が求まります。
 UNIQUE関数があるのは、同一人物が同じスコアを2回以上出しているときに1つの行にまとめるためです。

 

 次の画像もほとんど同じ例ですが、D列として備考欄の列が加わっています。
 SQL的なグループ化集計だと「それぞれの氏名ごとのスコアの最大値を求め、D列を含めて出力する」というのはちょっと苦労しますが、上記と同じような式で求めることができます。

f:id:accs2014:20201112095309p:plain:w700

 F3セル

=UNIQUE(FILTER(B3:D11,COUNTIFS(B3:B11,B3:B11,C3:C11,">"&C3:C11)=0))

 同一人物同一スコア(鈴木三郎、80点)の行に対してD列の値が複数存在する(井上、田中)場合にはUNIQUE関数で1つにまとめることはできませんので、複数の列が出力されているのがわかります。