BYROW関数などを用いて条件に合ったデータ数を数える[Googleスプレッドシート]

2024年4月18日 06:52

先日、このような記事を書きました。

この件に関してはもう一つ実現したいことがありました。
それは「複数の部署にまたがっている案件数を数えたい」です。

データはこちら。（前回と同じデータです）

数えたいのは「複数の部署にまたがっている案件」であり、「同一部署で課がまたがっている案件」は数えません。

今回のお題も自分では解決できず、所属しているコミュニティのメンバーに教えて頂きました。ありがとうございます！
また先日こちらの記事で教えて頂いた「COUNTUNIQUEIFS関数」も取り入れて、今後も応用できるよう自分なりにまとめてみます。

結論を先に言うと、今回はこちらの関数式で実現できました。

=COUNTIF(BYROW(UNIQUE(B4:B),LAMBDA(案件名,COUNTUNIQUEIFS(C4:C,B4:B,案件名))),">=2")

BYROW関数…苦手でして一向に習得できそうにありません…
「このケースならBYROWを使おう！」と思いつくことが出来るようなレベルに是非到達したいです。

今回も分解しながら理解していきます。

BYROW関数ではまず「行単位でグループ化する配列または範囲」を指定します。今回は「UNIQUE(B2:B)」とし、列Bにある案件名の一意の値を指定します。

【1】
=UNIQUE(B2:B)

関数がどう作用しているかを可視化します。

次に視点を関数式の後半に移し、
「COUNTUNIQUEIFS(C4:C,B4:B,案件名）」の部分を確認します。

ここでは第三引数「案件名」を、先程抽出した一意の案件名に一つずつ置き換えるとどのような値が返るのかを確認します。

【2】
=COUNTUNIQUEIFS(C4:C,B4:B,"AAA")
=COUNTUNIQUEIFS(C4:C,B4:B,"BBBB")
=COUNTUNIQUEIFS(C4:C,B4:B,"CCC")
…

この部分では、第三引数で指定した案件名を条件とし、列Bで条件に合致するレコードを絞り、その中で列Cの一意の値の個数を返します。
（…皆さんついてきていますかー？かくいう私もあやしいです...）

確かに、列C「担当部署」が2つにまたがる案件名では「2」が返されていることがわかります。

以上の2つの関数式を、BYROW関数とLAMBDA関数を用いてつなげます。

【3】
=BYROW(UNIQUE(B4:B),LAMBDA(案件名,COUNTUNIQUEIFS(C4:C,B4:B,案件名)))

これにより、列B「案件名」の一意の値を一つずつ後半の式に渡すことが出来ます。

図式にするとこのような感じでしょうか。

「名前」に代入するものとして、「UNIQUE(B2:B)＝一意の案件名」を指定します。
その名前を「案件名」とし、LAMBDA関数式の中で用います。

…難しいですよね…
BYROW関数の簡単な使い方も掲載しておきます。（これがないと私も思い出せない）

以下の例では「範囲B2:E4の各行を1行ずつ「x」に渡し計算する（この場合はSUM関数で総和を返す）」ということが実現できています。

話を本題に戻し、前出の以下の関数式を可視化してみます。

【3】
=BYROW(UNIQUE(B4:B),LAMBDA(案件名,COUNTUNIQUEIFS(C4:C,B4:B,案件名)))

上から順に、「またがっている部署の数」が返されました。

※なお最後に「0」が返ってきていますが、これは範囲の末尾を指定しなかったために返ったようです。
「BYROW(UNIQUE(B4:B)」の範囲を「(B4:B18)」と指定すると「0」は表示されませんでした。
但しここではデータベースが追加される実務を想定して末尾の範囲を指定しないこととします。

最後にCOUNTIF関数でくくり、「部署が複数にまたがる」を判定するために条件を「>=2」としカウントします。

【4】
=COUNTIF(BYROW(UNIQUE(B4:B),LAMBDA(案件名,COUNTUNIQUEIFS(C4:C,B4:B,案件名))),">=2")

いいなと思ったら応援しよう！