いきなり答える備忘録

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

(Gスプレッドシート)グループごとに一番下(一番上)の行を取得する

 Googleスプレッドシートで、ある列の値によりグループを区別し、それぞれの最も下(最も上)にある行を取得する方法についてです。
 QUERY関数によるアプローチも有力ですが、ここではCOUNTIFS関数を使った方法を試してみます。

  • FILTER関数とCOUNTIFS関数を使って、グループごとに一番下(一番上)の行を取得することができます。

手順

グループごとに一番下の行を取得する例

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

 B列に果物の名前が、C列に産地が並んでいます。
 果物の名前ごとに、それらが出現する一番下の行のデータを取得するものとします。

 早速ですが、画像ではE3セルに式を入力して結果を得ています。

f:id:accs2014:20200726174449p:plain:w700

 E3セルの式は次のとおりです。

=FILTER(B3:C13,ARRAYFORMULA(COUNTIFS(B3:B13,B3:B13,ROW(B3:B13),">"&ROW(B3:B13)))=0)

 なかなか難解です。
 キモとなるのは「ARRAYFORMULA(COUNTIFS(B3:B13,B3:B13,ROW(B3:B13),">"&ROW(B3:B13)))」の部分で、これにより「各行に記されている果物の名前がその行より下にいくつあるかをカウント」しています。
 この部分は次の式を一度に実行していると考えれば理解しやすいかと思います。

COUNTIFS(B3:B13,B3,ROW(B3:B13),">"&ROW(B3))
COUNTIFS(B3:B13,B4,ROW(B3:B13),">"&ROW(B4))
 ・
 ・
 ・
COUNTIFS(B3:B13,B13,ROW(B3:B13),">"&ROW(B13))

 あとはFILTER関数により各行と紐づけて、カウントが0である行を抽出している(カウントそのものは条件として機能するだけで抽出されません)というわけです。

グループごとに一番上の行を取得する例

 設定は上記の例と同じで、画像はE3セルに式を記して結果を取得した様子です。

f:id:accs2014:20200726174454p:plain:w700

 E3セルの式は次のとおりです。

=FILTER(B3:C13,ARRAYFORMULA(COUNTIFS(B3:B13,B3:B13,ROW(B3:B13),"<"&ROW(B3:B13)))=0)

 不等号の左右が逆なだけで、あとは上記の例と全く同じです。