スプレッドシートのCOUNTIFS関数・SUMIFS関数でARRAYFORMULAっぽい事をしてみる。（1回目）
こんにちは。
今回はGoogleSpreadsheetでARRAYFORMULA関数が使えないCOUNTIFS関数・SUMIFS関数でそれっぽい事をしてみる方法の紹介です。
GoogleSpreadsheetではエクセルのようなスピル機能が無い代わりに、同じような役割をするARRAYFORMULA関数というものがあります。
しかし残念ながらSUM関数、AVERAGE関数、Index関数、Match関数、COUNTIFS関数、SUMIFS関数等にはARRAYFORMULA関数は使えません。
ですが、名前付き関数・MAP関数・LAMBDA関数を組み合わせることで同じような事ができます。
上の画像はMリーグ（麻雀でのチーム制プロリーグ）2022-23シーズンの対局結果データです。
このデータからチーム別に解説者ごとの対局数とトータルスコアを集計したいとします。
普通にやるのであれば、O4セル・R4セルに上の画像のような数式を入力し、それぞれQ11セル・T11セルまで数式コピーすれば出来ますが、この下まで数式コピーする一手間がなんとも煩わしいw。
今回くらいの行数であれば大した事ないですが、これが何百・何千行となれば結構な手間です。
ですので普段私は、名前付き関数を作り少しでも手間を省けるようにしています。
ではやってみましょう。
上の画像のようにO4セルに
=COUNTIFS条件2つ($I:$I,$D:$D,$N$4:$N$11,O$3)
数式を入力しEnterキーを押すとO列の日吉さん解説の時の各チームの対局数が表示されます。
あとはO4セルの数式をQ4セルまで数式コピーすれば対局数の方は出来上がりです。
このCOUNTIFS条件2つという関数は私が自作した関数で引数の$I:$Iは検索する条件の1つ目の範囲であるチームのI列を、$D:$Dは2つ目の条件である実況のD列を、$N$4:$N$11は検索する条件1つ目のキーとなる範囲を、O$3は検索する条件2つ目のキーとなるセルを指定しています。
ではこの関数の作り方を説明していきます。
まず一番初めに紹介した従来のやり方である=COUNTIFS($I:$I,$N4,$D:$D,$O$3)
と
数式を入力し、COUNTIFS($I:$I,$N4,$D:$D,$O$3)の部分をコピーしてDeleteし、=LAMBDA(aa,と入力し後ろに貼り付けカッコで閉じて
=LAMBDA(aa,COUNTIFS($I:$I,$N4,$D:$D,$O$3))
とします。
第1引数のaaは何でもよく任意の文字列、たとえばABCとかあいうとかでも大丈夫なのでお好きな文字を入力してください。
次に検索条件1つ目のキー範囲$N4の部分を第1引数と同じ文字列、ここではaaと書き換えます。
そしてまた=より後ろのLAMBDA(aa,COUNTIFS($I:$I,aa,$D:$D,$O$3)
の部分をコピーしDelete、今度は=MAP($N$4:$N$11,と入力し、その後ろに貼り付けカッコで閉じ、=MAP($N$4:$N$11,LAMBDA(aa,COUNTIFS($I:$I,aa,$D:$D,$O$3))
としてEnterキーを押すと結果が表示されます。
今度の第1引数である$N$4:$N$11は検索条件1つ目のキーとなるセル範囲を指定しています。
このセル範囲が先程入力した文字列aaの部分に入るとイメージすれば良いでしょう。
これで数式は完成なのですが、次にこの数式に名前を付けて使いやすいようにカスタマイズしていきます。
先程数式を入力したO4セルで右クリック
セルでの他の操作項目を表示→名前付き関数を定義
をクリックします。
すると上の画像のような入力フォームが出てきますので、まず一番上の関数名の所に任意の名前を付けます。
今回はCOUNTIFS条件2つと名付けます。
その下の関数の説明は省略可、お好みで入力してください。
次に引数のプレースホルダには関数の引数となる部分の名称を入力していきます。数式入力時に下の画像の緑色の部分になるところですね。
今回は
条件範囲1 → Enterキー
条件範囲2 → Enterキー
検索キー1 → Enterキー
検索キー2 → Enterキー
としていきますと、下の画像のようにタブが4つ現れてきます。
続いてこのタブに今回入力した数式の中の引数を当てはめていきます。
一番下の引数の候補のどれかをクリックすると
画像のような画面になります。
今回は検索条件1をチーム名、検索条件2を実況名としたいので
$N$4:$N$11 → 検索キー1
はとなります。
検索キー1のタブをクリックすると枠内に検索キー1の文字が現れますので、緑色の定義をクリック。
続けて
$I:$I → 条件範囲1
$D:$D → 条件範囲2
$O$3 → 検索キー2
と定義付けしていきます。
4つの引数を定義付けすると下の画像のような画面になります。
ちなみに表示されている条件範囲1・条件範囲2・検索キー1・検索キー2の4つのタブは作った関数で数式を入力する際に、このタブの並び順で入力することになりますので入力し易い順番にしておくといいと思います。
（タブにカーソルを合わせると十字マークが出るのでそのままドラッグすると順番を入れ替えられます）
今回ように同じシートで集計するならいいですが、大体の場合は参照シートと集計シートは別になると思いますので、入力時にシートを行き来しなくてすむような順番にしておくといいと思います。
並び替えが終わりましたら右下の次へをクリックすると下の画像のような画面になりますが、ここは省略してかまいません。お好みで入力してください。
右下の作成をクリックしたら出来上がりです。
では今作った関数を実際に使ってみましょう。
O4セルに=countと入力すると下の画像ようにCOUNTIFS条件2つというのが候補に出てきますのでこれを選択。
後は普通の数式入力時のように濃い緑色に該当する範囲を選択していけばOKです。
条件範囲1 → I列（チーム）全体を選択 → F4キーで絶対参照 → ,
条件範囲2 → D列（実況）全体を選択 → F4キーで絶対参照 → ,
検索キー1 → N4:N11を選択 → F4キーで絶対参照 → ,
検索キー2 → O3セルを選択 → 絶対参照なし → Enterキー
はい出来ました。
続いて、O4セルをクリックしCtrl+Cでコピーしshiftキーを押しながらQ3セルをクリック、右クリックして特殊貼付け→数式のみ貼付け
以上で対局数の方は出来ました。
今回はこのへんで失礼しようと思います。次回はトータルスコアの方をSUMIFS関数をもとに数式を作って求める方法と、作った関数を別のスプレッドシートで使用する方法等を紹介したいと思います。
ではまた。