メインコンテンツへスキップ
見出し画像

【Googleスプレッドシート】「動的クロップ(有効範囲切り抜き)」の名前付き関数

    イネ

    はじめに

    作った名前付き関数の紹介記事です。

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

    以前アンピボットの記事中で「L_CROP_A」という名前付き関数について定義式だけ載せていましたが、
    そちらも含めて、今回は「ガバっと選択した範囲から、実際にデータが入っている”有効範囲”を動的に切り抜く(クロップする)関数」について紹介させて頂きます。

    「L_CROPシリーズ」として以下4つを作成しました。

    •  L_CROP (基準列・基準行の最終位置を特定して切り抜く)

    • L_CROP_C (指定した1列を切り抜く)

    • L_CROP_R (指定した1行を切り抜く)

    • L_CROP_A (範囲全体からデータが入っている最終行・最終列を特定して切り抜く)

    「わざわざ分ける必要があるのか」については賛否あるところだろうとは思いますが……
    一旦全てを見て頂いた上でご意見を頂けると嬉しく思います。

    これらは単体で使用するというよりは、主に「引数に範囲(配列)を要求する関数の中で利用する」という補助的な用途を想定して作っています。

    これが地味に便利なのです…(と私は思っています)

    • 広めに選択しておけばデータの増減に自動的に対応できる

    • データ範囲外の空白への対処が不要(「IF(〇〇="",""...)」等)

    • ↑このため、データ範囲内の空白への対処を気兼ねなく書ける(「IF(〇〇="","該当なし",...)等」)

    • 計算負荷の軽減(必要な範囲だけ計算)

    • 認知負荷の軽減(「A:Z」や「1:1000」など雑な選択が許容される)

    (個人的にはとにかく「雑に選択できる」が一番助かってます…。存在が雑なので……)

    L_CROP

    【概要】
    範囲の最終行・最終列を特定する際、範囲全体を検索することはちょっと大変なので「この列・行を見れば最終行・最終列を特定できますよ」を使用者側で指定する作りにしました。

    上段で書いた通り「補助的な利用」を主な用途と想定しているため、これ自体は「出来るだけ負荷を軽く・処理を速く」を意識して設計しています。
    (もっと効率化できるところがあったらご教示お願いいたします。)

    【引数と引数の説明】
    引数:範囲,基準列番号,基準行番号

    • 範囲:切り抜き元の範囲です。

    • 基準列番号:データが密に詰まっていることが期待できる列(ID列等)の列番号を数字で指定します。この列の最終行を全体の最終行として扱います。

    • 基準行番号:データが密に詰まっていることが期待できる行(項目行等)の行番号を数字で指定します。この行の最終列を全体の最終列として扱います。

    【定義式】

    =LET(
 行範囲, ARRAYFORMULA(TO_TEXT(INDEX(範囲, 0, 基準列番号))),
 列範囲, ARRAYFORMULA(TO_TEXT(INDEX(範囲, 基準行番号, 0))), 

 行数, IFERROR(XMATCH("?*", 行範囲, 2, -1), 1),
 列数, IFERROR(XMATCH("?*", 列範囲, 2, -1), 1),

 ARRAY_CONSTRAIN(範囲,行数,列数)
)

    TO_TEXTはXMATCHの「ワイルドカード検索」を確実にするための準備です。
    (ワイルドカードは文字列にのみ反応し数値はヒットしない仕様のため)

    XMATCHでは第三引数に2を指定することで「ワイルドカード検索」ができます。
    また、第四引数に-1を指定することで末尾からの検索が可能です。

    今回は「空白ではないセル」を末尾から検索することで、最終行/最終列を特定する使い方をしています。

    "?*"は、次のような仕組みで「空白ではないセル」を意味します。

    • ?：何でもいいから1文字が存在す

    • *：0文字以上の文字列

    • ?*：何らかの1文字のあとに0文字以上の文字列が続く

    =「1文字以上の何かが入力されているセル」

    最後にARRAY_CONSTRAINで特定した行数・列数分を切り抜いて完了です。

    ARRAY_CONSTRAINは指定した範囲を左上から指定行数・指定列数分抜き出す関数です。

    (余談ですが、もともとはARRAY_CONSTRAINではなくCHOOSECOLS/CHOOSEROWSを使用していました。
    つい最近、たまたまこの「ARRAY_CONSTRAIN」という関数の存在を知り、あまりに用途がぴったりハマっていたので差し替えました。)

    「始点は必ず一番左上」「引数の省略不可」とやや融通が利かず使い勝手がイマイチな関数ということでAIとしては優先度が下がるらしく、Geminiからは全く提案されなかったものなのですが、今回の用途にはこれで必要十分ですね。

    ちなみにExcelには上位互換と言える「TAKE関数」なるものがあるらしいです。

    スプレッドシートにも輸入されて欲しい…

    L_CROP_C

    【概要】
    1列を抜き出す用です。

    出発点は、ARRAYFORMULA-VLOOKUPで検索値を1列選択するような場合に「L_CROP」で「1,1」と書くのが何だか無駄なように感じて「1列用のものを作成してみよう」というところからでした。

    そこから、もう少し広い用途で使えるように「"範囲"から抜き出す列を指定できる作り」にしたのですが、第一引数に1列のみを指定すると自分でも第二引数の"1"を忘れがちになります…。

    けれど、広めの範囲から特定の列を指定して抜き出す使い方(例えば「L_CROP_C(範囲,MATCH(...))」のような使い方)は、XLOOKUPの第三引数(戻り範囲)に入れるなど使いどころが色々考えられるので、とりあえずこの仕様で良いかなと思っています。

    【引数と引数の説明】
    引数:範囲,列番号

    • 範囲:切り抜き元の範囲です。1列でも複数列でも。結果は1列で返します。

    • 列番号:複数列の範囲を選択した場合、何列目を抜き出すのか指定します。1列を選択した場合は1を指定します。

    【定義式】

    =LET(
 対象列,INDEX(範囲,0,列番号),
 最終行,IFERROR(XMATCH("?*",ARRAYFORMULA(TO_TEXT(対象列)),2,-1),1), 

 ARRAY_CONSTRAIN(対象列,最終行,1)
)

    私自身がAIに教えてもらうまで知らなかったので解説させて頂くのですが、「INDEX(参照,0,列)」のように第二引数(行)に「0」を指定すると全行=1列を取り出すことができます。

    (これも余談ですが、それまでずっと独学だった関数というものについて、AIと協議するようになってこのような知識を教えてもらえたり、飛躍的にスキルが向上したように思います。AIって凄い……。)

    あとは「L_CROP」と大体同じですね。

    IFERRORについて前段の「L_CROP」で説明できていませんでしたが、「指定した範囲が全て空白だった場合1行・1列とみなす」という役割を果たしています。

    これによって、最終的に空白の1セルが出力される形になります。

    「指定した範囲が全て空白の場合エラーを返す」という選択肢もありますが、「L_CROPシリーズ」は補助的な利用が主なのでここでは「必ず範囲を返す」という整理にして、空白の場合どうするかはこれを受け取る各関数側の方で処理してもらうという設計思想です。

    L_CROP_R

    【概要】
    1行を抜き出します。

    L_CROP_Cの行版ですね。
    中身もほぼ変わりません。
    使いどころはやや少ないかもしれません…。

    【引数と引数の説明】
    引数:範囲,行番号

    • 範囲:切り抜き元の範囲です。1行でも複数行でも。結果は1行で返します。

    • 行番号:複数行の範囲を選択した場合、何行目を抜き出すのか指定します。1行を選択した場合は1を指定します。

    【定義式】

    =LET(
 対象行,INDEX(範囲,行番号,0),
 最終列,IFERROR(XMATCH("?*",ARRAYFORMULA(TO_TEXT(対象行)),2,-1),1), 

 ARRAY_CONSTRAIN(対象行,1,最終列)
)

    「L_CROP_C」と行と列が入れ替わっただけという感じです。

    INDEXについても、第三引数(列)を「0」にすると行の時と同様に全列=1行を返してくれますので、ここが入れ替わっただけですね。

    L_CROP_A

    ※2026/04/20 定義式微修正
    【概要】
    アンピボットの方で定義式だけ載せていましたが、「ARRAY_CONSTRAIN」を差し替え採用したため、そこの部分だけ差異があります。

    あちらの記事の記述を書き換えても良いのですが、せっかくなので見比べられるように残しておきます。

    こちらは主に「他の名前付き関数の中で使う」ために作成しました。

    実は少し前まで他の名前付き関数の中で「L_CROP」を「第二引数・第三引数ともに1」の決め打ちで使用していました…。(「1列目・1行目の末尾を見る」という使い方)

    1行目や1列目がスカスカだった場合問題があることは分かっていたのですが、「動的クロップ」がとにかく便利で"とりあえず"入れてしまっていたのですよね…。

    名前付き関数が増えてきて、"とりあえず"の「L_CROP」使用も増えてくる中で「さすがにちょっと問題か……これ以上先延ばしすると全てを修正するのも大変になる…」と思い、全自動で最終行・列を特定して切り抜くものを作成したという次第です。

    ちなみに「A」は「ALL」「AUTO」「ARRAY」等の頭文字として決めました。

    【引数と引数の説明】
    引数:範囲

    • 範囲:切り抜き元の範囲です。範囲だけ渡せばあとは何もいりません。

    【定義式】

    =LET(
  最終行, CHOOSEROWS(範囲, -1),
  行クロップ不要, IFERROR(OR(INDEX(最終行<>"")),TRUE),  

  行クロップ後, IF(行クロップ不要, 範囲,
    LET(
      範囲行数, ROWS(範囲),
      最大行, MAX(INDEX(IF(IFERROR(範囲<>"",TRUE), SEQUENCE(範囲行数), 1))),
      ARRAY_CONSTRAIN(範囲,最大行,COLUMNS(範囲))
    )
  ),  

  最終列, CHOOSECOLS(行クロップ後, -1),
  列クロップ不要, IFERROR(OR(INDEX(最終列<>"")),TRUE),  

  IF(列クロップ不要, 行クロップ後,
    LET(
      範囲列数, COLUMNS(行クロップ後),
      最大列, MAX(INDEX(IF(IFERROR(行クロップ後<>"",TRUE), SEQUENCE(1, 範囲列数), 1))),
      ARRAY_CONSTRAIN(行クロップ後,ROWS(行クロップ後),最大列)
    )
  )
)

    これまでより少し長いので、こちらは少し分解しながら解説いたします。

    一旦全体としては、元の"範囲"のまま最終行と最終列を特定しにいくのではなく、まず行を処理してから列を処理する…と順番に処理することで、最終列を特定する際に検索すべきセル数を減らす…というささやかな工夫をしています。

    定義式自体はこの方が少し長くなるかと思いますが、処理としては少し軽くなります。

    ※2026/04/20 修正
    修正箇所は以下の分解解説中に記します。

    =LET(
  最終行, CHOOSEROWS(範囲, -1),
  行クロップ不要, IFERROR(OR(INDEX(最終行<>"")),TRUE),

    まず、そもそも指定された範囲にデータ範囲外の余白が含まれているか否かを確認しています。

    「L_CROP」の方で書いた通り全件検索して最終行・最終列を特定することは少し処理が大変なため、しなくて良い処理をしないで済むようにするための工程です。

    "範囲"の最終行に「空白以外」のセルが1つでもあれば「行クロップ不要」となります。

    仕組みとしては、
    まずCHOOSEROWSの「-1」で後ろから1行目を取得します。
    INDEXは第一引数のみを指定すると配列を作成するので、「最終行が空白以外である」のTRUE/FALSEの配列ができます。
    OR関数は中身が1つでもTRUEであればTRUEを返しますので、INDEXの中身が1つでもTRUE=空白以外であればTRUEとなり、「行クロップ不要」と判定できるという具合です。

    ※2026/04/20 修正
    OR関数は中身にエラーが1つでも含まれている場合エラーとなってしまうため、IFERRORで対策しました。
    エラー=空白ではないため、最終行にエラーがある場合にTRUEとなるように修正しました。

     行クロップ後, IF(行クロップ不要, 範囲,
    LET(
      範囲行数, ROWS(範囲),
      最大行, MAX(INDEX(IF(IFERROR(範囲<>"",TRUE), SEQUENCE(範囲行数), 1))),
      ARRAY_CONSTRAIN(範囲,最大行,COLUMNS(範囲))
    )
  ),

    繰り返しになりますが、まず行を処理してから列を処理するという順番での処理になっています。

    行クロップが不要であれば、当然元の"範囲"がそのまま「行クロップ後」となります。

    そうでなければ全件検索となり、以下のように処理します。

    INDEXは先の通り配列を作成する役割です。

    「範囲<>""」は"範囲"全体に対して空白以外であるかどうかのTRUE/FALSEの配列となります。

    「SEQUENCE(範囲行数)」は行数分の連番の1列の配列です。

    「IF(範囲<>"", SEQUENCE(範囲行数), 1)」と書くと、TRUEの部分には「SEQUENCE(範囲行数)」の結果が入り、FALSEの部分には「1」が入った配列が出力されます。

    「SEQUENCE(範囲行数)」は1列の配列ですが、ありがたいことにIFでこのように記述した場合大きい配列に合わせて拡張されるという仕様があり、全列に同じように反映されます。

    「SEQUENCE(範囲行数)」の数字は行数を表し、FALSE=空白のセルは1ですので、「この配列の中で最も大きい値(MAX)」が最大行数ということになります。

    これが特定できればあとはARRAY_CONSTRAINで切り抜いて行クロップ完了です。

    また、こちらも「指定された範囲が全て空白の場合」1を返す仕様になっていますので、その場合は最終的に1セルの空白を返すということになります。

    ※2026/04/20 修正
    エラー=空白ではないため、IFERRORでTRUEに変換しSEQUENCEの値が入るように修正しました。
    MAXの中身が1つでもエラーの場合結果がエラーになってしまうため、その対策も兼ねます。

      最終列, CHOOSECOLS(行クロップ後, -1),
  列クロップ不要, IFERROR(OR(INDEX(最終列<>"")),TRUE),

  IF(列クロップ不要, 行クロップ後,
    LET(
      範囲列数, COLUMNS(行クロップ後),
      最大列, MAX(INDEX(IF(IFERROR(行クロップ後<>"",TRUE), SEQUENCE(1, 範囲列数), 1))),
      ARRAY_CONSTRAIN(行クロップ後,ROWS(行クロップ後),最大列)
    )
  )
)

    列について、行と同様の処理を実行します。

    行クロップが終了しているので、元の"範囲"ではなく"行クロップ後"を対象にして処理します。

    まずは「列クロップ不要」の判定、列クロップが不要であればここで処理終了です。

    行も列もクロップ不要の場合についてまとめると、
    「最終行取得→1つでも値があるか判定→あるので処理不要→最終列取得→1つでも値があるか判定→あるので処理不要」という処理だけで済むようになっています。

    列クロップが必要な場合はSEQUENCEで列番号の1行の配列を作成して、行と同様の処理で最大列数を特定し、ARRAY_CONSTRAINで処理完了です。

    おつかれさまでした…！

    ※2026/04/20 修正
    行の修正箇所と同様にIFERRORを追加しています。

    おわりに

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

    これまでに記事にしたアンピボットや半角カタカナの全角化については、自分で思いついた名前付き関数を一通り作り終えたあとに、Geminiからの提案や他の方の記事を見て作成したものでした。

    今回からは私が「作ろうと思って作ったもの」になります。

    基本的には「あったら便利だろう」と思って作りましたので「便利そうだ」と思ってもらえたら嬉しいのですが…どうでしょうか。
    (これから紹介していくものの中には一部「作れそうだったから作った」といったものもあります…)

    ご意見・ご感想を頂けると嬉しく思います。

     
     

    イネ

     
     
    しがないバックオフィス系社員。 Googleスプレッドシートで関数を書き名前付き関数を作りながらどうにか生存しています。

    あなたへのおすすめ