【Googleスプレッドシート】"列ごと/行ごとに集計する(10種切替式)"名前付き関数

はじめに

(作った名前付き関数の紹介記事です。
スプレッドシートの名前付き関数についてはこちら で私見を語っています。)

今回は列ごとや行ごとの集計結果(合計や平均値など)を配列展開させる名前付き関数についてです。

「ん？それって単にBYCOLやBYROWで良いのでは？」というツッコミがあろうかと思います。

それについてはですね、
「はい、全くその通りです！」

〜完〜

……いや、「全くその通り」ではあるんですけれども、「〜完〜」はちょっと取下げさせて頂きまして……

私がそうだったのですが、多分BYCOLやBYROW…平たく言うと「LAMBDAヘルパー関数」ってかなり理解が難しいものだと思うのです。
「LAMBDAヘルパー関数が難しい」というより、そもそも「LAMBDA関数自体難しい」のですが…。
書き方も特殊ですし、説明を読んでも良く分からないというか…
(そう思ってるのが私だけだったらどうしよう……)

私の場合、「『LAMBDA』の最初に求められる『名前』って何なの？」というのがなかなか掴めなくて苦戦していたのですが、「BYCOL/BYROWからLAMBDAに渡される一列一列/一行一行 を何と呼ぶか」ということだと理解してから、どうにか扱えるようになりました。
(私はLAMBDA関数自体より先にLAMBDAヘルパー関数を知り、LAMBDAはお作法的な何かだと思ってました。LAMBDAを先に修めた方が理解はしやすそう……)

ここではこれ以上「LAMBDAヘルパー関数とは？」とか「LAMBDA関数とは？」については書きませんが、きっと既に他の方が分かりやすい説明をどこかで書いて下さっていることでしょう……

といったところで、以下本編、「BYCOL/BYROWで列ごと/行ごとに指定された集計を実行するだけ」の名前付き関数について解説していきます。
前回 長かったですし、サクッといきたいですね。

L_STAT

名前付き関数を作り始めた初期の頃に「BYCOL/BYROWが分からない人でも列ごと/行ごとの集計が一気に出来るようになるならそれは便利では？」と思って作ったものです。
特に周りで普及したりはしていません。ﾊﾊﾊ。

【概要】
指定した範囲のデータを、列ごと(縦)または行ごと(横)に一括集計します。合計や平均はもちろん、中央値、重複なし件数、テキスト結合まで10種類の統計処理に対応します。

【引数と引数の説明】

1. 集計範囲
   集計対象となるデータ範囲を指定します。(例: B2:Z)内部で自動的にデータが存在する範囲のみに最適化(クロップ)されるため、列全体のような広めの指定も可能です。

2. 集計の種類
   実行したい集計処理を指定します。以下に挙げた数字や文字列での入力が可能です。

• 1, "合計", "SUM"

• 2, "平均", "AVERAGE", "AVG"

• 3, "最大", "最大値", "MAX"

• 4, "最小", "最小値", "MIN"

• 5, "中央値", "MEDIAN"

• 6, "最頻値", "MODE", "MODE.SNGL"

• 7, "数値の件数", "COUNT"

• 8, "データの件数", "件数", "COUNTA"

• 9, "重複なし件数", "COUNTUNIQUE"

• 10, "テキスト結合", "TEXTJOIN"

1. 集計方向
   範囲をどの方向に集計するかを指定します。以下の数字や文字列での入力を受け付けます。

• 縦(列ごとの集計): 1, "縦", "列", "列ごと", "C", "V", "BYCOL"

• 横(行ごとの集計): 2, "横", "行", "行ごと", "R", "H", "BYROW"

【定義式】

=LET(
  範囲, L_CROP_A(集計範囲),
  x, UPPER(TO_TEXT(集計の種類)),
  y, UPPER(TO_TEXT(集計方向)),
  
  isX, LAMBDA(リスト, ISNUMBER(XMATCH(x, リスト))),
  isY, LAMBDA(リスト, ISNUMBER(XMATCH(y, リスト))),
  
  集計関数,
    IF(isX({"1","SUM","合計"}),                  LAMBDA(d, SUM(d)),
    IF(isX({"2","AVERAGE","AVG","平均"}),        LAMBDA(d, AVERAGE(d)),
    IF(isX({"3","MAX","最大","最大値"}),         LAMBDA(d, MAX(d)),
    IF(isX({"4","MIN","最小","最小値"}),         LAMBDA(d, MIN(d)),
    IF(isX({"5","MEDIAN","中央値"}),             LAMBDA(d, MEDIAN(d)),
    IF(isX({"6","MODE","MODE.SNGL","最頻値"}),   LAMBDA(d, IFERROR(MODE.SNGL(d), "#N/A")),
    IF(isX({"7","COUNT","数値の件数"}),          LAMBDA(d, COUNT(d)),
    IF(isX({"8","COUNTA","データの件数","件数"}), LAMBDA(d, COUNTA(d)),
    IF(isX({"9","COUNTUNIQUE","重複なし件数"}),  LAMBDA(d, COUNTUNIQUE(d)),
    IF(isX({"10","TEXTJOIN","テキスト結合"}),    LAMBDA(d, TEXTJOIN(", ", TRUE, d)),
    "エラー: 不正な集計の種類です"
    )))))))))),

  IF(ISTEXT(集計関数), 集計関数,
    IF(isY({"1","縦","列","列ごと","C","V","BYCOL"}), BYCOL(範囲, 集計関数),
    IF(isY({"2","横","行","行ごと","R","H","BYROW"}), BYROW(範囲, 集計関数),
      "エラー: 集計方向を正しく指定して下さい (1/2/縦/横/列/行等)"
      )
    )
  )
)

【解説】
10種類詰め込んでいるのと入力の柔軟性を確保するために定義式自体はやや長めになっていますが、やっていることは特に難しくはありません。

=LET(
  範囲, L_CROP_A(集計範囲),
  x, UPPER(TO_TEXT(集計の種類)),
  y, UPPER(TO_TEXT(集計方向)),

「L_CROP_A」についてはこちらの記事をご参照頂ければと思いますが、データが入っている最終行・最終列を特定して、有効範囲を切り抜く役割の関数です。

第二引数・第三引数の「集計の種類」「集計方向」では様々な入力に対応するため、UPPERとTO_TEXTで表記揺れ等をある程度吸収しています。

  isX, LAMBDA(リスト, ISNUMBER(XMATCH(x, リスト))),
  isY, LAMBDA(リスト, ISNUMBER(XMATCH(y, リスト))),

「どの入力だったらどの集計を実行するか」の判定式を作成するための事前準備です。
「どの入力だったら」を判定するための式を簡略化するためにLAMBDA式を用意しました。
(上のUPPER/TOTEXTする際に名前を「x」「y」と極端に簡略化したのは、本来は判定式で何度も書くことになるのが面倒だったので…というところだったのですが、ここにきてそれもLAMBDAにまとめてしまったので、「x」「y」 である必然性はもうないのですが、逆に不都合も特にないのでそのままになっています。)

この式を定義することによって、次の判定式を作成する段では「リスト」に項目を配列の形で列挙するだけで良いということになります。

式の中身の解説は特に必要ないとは思いますが、XMATCHを使うことで「xがリストに含まれていれば数字が返り、なければエラーになる」ので、「リストにxが含まれていればISNUMBERがTRUEになり、含まれてなければFALSEになる」というよくある判定式ですね。

 集計関数,
    IF(isX({"1","SUM","合計"}),                  LAMBDA(d, SUM(d)),
    IF(isX({"2","AVERAGE","AVG","平均"}),        LAMBDA(d, AVERAGE(d)),
    IF(isX({"3","MAX","最大","最大値"}),         LAMBDA(d, MAX(d)),
    IF(isX({"4","MIN","最小","最小値"}),         LAMBDA(d, MIN(d)),
    IF(isX({"5","MEDIAN","中央値"}),             LAMBDA(d, MEDIAN(d)),
    IF(isX({"6","MODE","MODE.SNGL","最頻値"}),   LAMBDA(d, IFERROR(MODE.SNGL(d), "#N/A")),
    IF(isX({"7","COUNT","数値の件数"}),          LAMBDA(d, COUNT(d)),
    IF(isX({"8","COUNTA","データの件数","件数"}), LAMBDA(d, COUNTA(d)),
    IF(isX({"9","COUNTUNIQUE","重複なし件数"}),  LAMBDA(d, COUNTUNIQUE(d)),
    IF(isX({"10","TEXTJOIN","テキスト結合"}),    LAMBDA(d, TEXTJOIN(", ", TRUE, d)),
    "エラー: 不正な集計の種類です"
    )))))))))),

先ほど定義した「isX」の式を用いて、「x(集計の種類)がこのリストに含まれていればこの式を使用する」の長大なIFネストです。

IFのネストは「素人業」というイメージがありますが、IFSやSWITCHでは数式や配列を返すことができないのですよね…。
何だかんだ「ネストが深くなると可読性が下がる」という以外はかなり柔軟で強力で便利です。

長大なネストは閉じ括弧の数に気を付ける必要がありますが、名前付き関数の中身の話ですから、一度きちんと設定できていればそれで使用する際は気にせずに使えますので…良いかな…と。

最後は、どの項目にも当てはまらなかった場合のエラーメッセージを設定しています。

  IF(ISTEXT(集計関数), 集計関数,
    IF(isY({"1","縦","列","列ごと","C","V","BYCOL"}), BYCOL(範囲, 集計関数),
    IF(isY({"2","横","行","行ごと","R","H","BYROW"}), BYROW(範囲, 集計関数),
      "エラー: 集計方向を正しく指定して下さい (1/2/縦/横/列/行等)"
      )
    )
  )
)

ラストです。
上の「集計関数」が数式ではなくテキストを返している場合は「エラー」になっていますので、そのままエラーメッセージを出力します。

「isY」は先ほどの「isX」と仕組みは同じですね。
「y(集計方向)がどちらのリストに含まれているか」を判定して、BYCOLを使うかBYROWを使うかの分岐となります。
指定が正しくない場合はエラーメッセージとなります。

BYCOL/BYROW自体は、クロップした「範囲」に対して列ごともしくは行ごとに「集計関数」を実行するだけ…と、核の部分は本当にシンプルですね。

冒頭で書いた通り、集計すべき内容が一定でLAMBDAヘルパー関数を使える人であればこの「L_STAT」を使う必要はほぼないかなとは思います。

ただ、「『集計の種類』をプルダウンに設定しておいて切り替える」のような使い方は割と便利だと思いますので、全く活用場面がないわけでもないですね。
いや、「ないわけでもない」どころか「これこそが最大の強みなのです！」
(…とドヤ顔でアピールしても良いとGeminiが言ってました。)

使用例と導入方法

【使用例】
最初の記事(アンピボットの名前付き関数)で少し書いたのですが、もともと記事を書き始めた動機というのが「定義式(コード)を有識者に見てもらいたい」というところだったので、これまで特に「使用例」みたいなものは載せずにコードの中身中心で書いてきたのですが、「名前付き関数の紹介」というならやっぱりあった方が良いか…と思い直したので、軽くですがスクリーンショットだけ載せていこうと思います。

【導入方法】
「導入方法」については、この度「リンクを知っている全員」に閲覧権限を付与した共有用のスプレッドシート を作成いたしました。

閲覧権限さえあれば名前付き関数をインポートしてもらうことができますので、「試してみてやっても良いか」という方は、こちらの記事の後半を参考にインポートしてみて頂ければと思います。
(もし「すべてインポート」しない場合は「L_STAT」と合わせて「L_CROP_A」も一緒にインポートが必要になります。)

これまで、自分のアカウント名みたいなものが見えてしまう懸念もあるかなというところで作っていなかったのですが、「ゲーム用」として持っていたアカウントを使えば良いか…と思い、用意してみました。

この「共有用のシート」の説明や、導入方法の部分だけ改めて説明する感じの軽い記事を別途一つ書こうかなと思っています。

おわりに

ここまで読んで頂きありがとうございました。

今回は「BYCOL/BYROWで集計するだけ」という名前付き関数でした。
それだけと言えばそれだけなのですが、「それだけ」が難しい時期が自身にもあったので、「使ってもらえたら良いなぁ…」という思いではあります。
(皆様はすんなりと「LAMBDA」理解出来ましたでしょうか…？)

一応「本当にそれだけ」で終わらないように10種の集計に対応できるようにして、結果的にプルダウンでの切替など便利な使い方もできるものにはなりましたので、興味がありましたらインポートして試してみて頂ければと思います。