Office TANAKA - ExcelのAI活用「[作って]データ量が可変な数式」

【2025年8月】

AIは数式も作ってくれます。ひとつの関数だけを使う簡単な数式だったら、自分で考えることもできるでしょう。でも、ちょっと複雑になったら自作は難しいです。そんなときは、AIに数式を作ってもらいましょう。

A列には、4つの値(数値や文字列など)がハイフンで区切られています。このうち、ハイフンで区切られた3番目の値(セルA2では"田中")を抽出する数式を作ってもらいます。

A列に入力されている文字列をハイフンで区切り、3番目の値だけ抽出する数式を作って

'Sheet1' の A1:B5 を調べ、1 つの数式列を確認して 列 B に挿入します:
名前（抽出）
各行の「記号」からハイフンで区切られた3番目の要素を抽出し、担当者や関係者の名前を
自動的に取り出します。

=INDEX(TEXTSPLIT($A2,"-"),3)

"記号" 列の値をハイフンで分割し、3 番目の要素を抽出します。

TEXTSPLIT 関数を使い、"記号" の値を区切り文字 "-" (ハイフン記号) で分割します。
INDEX 関数を使い、分割されたリストの 3 (3 番目（左から数えて 3 番目）) の要素を
取得します。

おお、なんかすごい。驚いた。とりあえず私が作ったのと、まったく同じだ。上図のB列には説明のために名前を抽出していますが、実は同じ数式が入力されています。

一応、数式を削除してからAIに聞きましたので、まさかカンニングではないでしょうｗまぁ、これが普通ですね。INDEX関数の代わりにCHOOSECOLS関数やTAKE関数などを使う手もありますが、いずれにしてもポイントはTEXTSPLIT関数ですね。古典的な、FIND関数の3連コンボは、ちょっと今の時代に相応しくありませんからね。

なかなか、やりますね～ちょっと甘く見てました。じゃ次。少し難易度を上げましょう。

セル範囲A2:D6から、セルF1に入力したヘッダの列を表示する数式を作って。セルF1が「名前」だったらB列、「数値」だったらD列が表示されるようにして。

伝わるかな？関係ないけど、AIが考えてるとき、シート上のセルが、何やらカラフルになります。なんか、カッコイイですｗ

=INDEX(A2:D6, , MATCH(F1, A1:D1, 0))
この数式を使うと、F1に入力したヘッダ名に対応する列（A2:D6の中）を表示できます。

AIが作成した数式は、カンマの後ろにスペースが入っています。これはVBAのルールで、セルの数式ではスペースを入れません。もっとも、数式内のスペースは無視されますので、このままでも正常に動作します。にしても、なるほど～INDEX関数とMATCH関数ですか。ちょっと補足します。INDEX関数は「INDEX(範囲,行,列)」と指定しますが、ここでは引数「行」が省略されています。省略すると、引数「列」で指定した列全体が返ります。これは、大昔からの仕様ですが、スピル実装前のExcelでは配列数式にするしかなく、使い勝手が悪かったです。でも、スピルが実装されてからは、こうした使い方が簡単にできるようになりました。知らなかったという方は、ぜひ覚えといてください。

ちなみに今回は、私が想定した数式とは違いました。なので、続けて質問します。

XLOOKUP関数を使って

=XLOOKUP(F1, A1:D1, A2:D6)
この数式をF2セルに入力すると、F1に入力したヘッダ名に対応する列全体が表示されます。

私が使ったのは、この数式です。XLOOKUP関数は、指定したヘッダの列を、簡単に抽出できます。じゃ、さらに難易度アップ。

現在はデータが6行目まで入っていますが、データの行数が可変のときは？

=XLOOKUP(F1, A1:D1, CHOOSE({1}, INDEX(A:A, 2):INDEX(A:A, COUNTA(A:A)), INDEX(B:B, 2):INDEX(B:B, COUNTA(B:B)), INDEX(C:C, 2):INDEX(C:C, COUNTA(C:C)), INDEX(D:D, 2):INDEX(D:D, COUNTA(D:D))))
この数式なら、データの行数が増減しても自動で対応できます。

突然どした？悪いもんでも食ったか？意味不明な数式です。CHOOSE関数の第1引数に「{1}」ってｗまったく意味のない使い方ですね。CHOOSE関数の使い方として破綻しています。セルに入力すると、もちろんエラーです。

「このエラーを説明して(略)」を実行してみます。

だから！それじゃ行が固定されちゃうから、可変にしてって頼んだっすよ。しかたないので、ヒントを与えましょう。

=XLOOKUP(F1, A1:D1, A2:D6) にTRIMRANGE関数を組み合わせて、できませんか？

はい、可能です。TRIMRANGE関数でA2:D6のデータ範囲から外側の空白行や空白列を自動的に除外し、
その結果をXLOOKUPの検索範囲に使えます。下記のように組み合わせてください。


=XLOOKUP(F1, TRIMRANGE(A1:D1, 3, 3), TRIMRANGE(A2:D6, 3, 3))
この式で、ヘッダーやデータ範囲の両方にTRIMRANGEを適用しています。

う～む、話が伝わらない人との会話って、こんな感じですかね。データの行数が可変だから、セル範囲A2:D6ってアドレスを特定できないって話をしているんですよ。それに、セル範囲A2:D6には全セルに値が入っているんですよね、だったらTRIMRANGE(A2:D6, 3, 3)って、結局セル範囲A2:D6じゃないっすか。

これ、今回の発想だったら、こうします。

検索値(ここではセルF1の"名前")を探す1行目は、TRIMRANGE関数を使いません。ここはアドレス固定でいいです。もし1行目全体をTRIMRANGEしたら、セルF1まで抽出されちゃいます。どうしてもアドレスで指定したくないのでしたら、TAKE関数を使う手もありますが、ここは決め打ちで十分です。さて、問題はデータ範囲です。この行が不定なのですから、TRIMRANGE関数で抽出するのは"列全体"です。さらにこれ、トリム参照を使えば、こう書けます。

でも待ってください。結果をよくご覧ください。B列で"データが入っている範囲"を抽出したので、1行目の「名前」まで表示されちゃいました。これは、いただけません。なので、この抽出範囲から"先頭の1行目を削除"します。使うのはDROP関数です。

これなら、データが増えても大丈夫です。

何と言いますか、ちょっとガッカリです。ちなみに、私が入力したこの数式を説明してもらうと、

この数式 =DROP(XLOOKUP(F1,A1:D1,A:.D),1) は、Sheet2の表データを使って、指定した
「名前」に対応する行データを取得し、1行目（ヘッダー）を除いた結果を返します。

主な処理の流れは以下の通りです：

XLOOKUP(F1,A1:D1,A:.D)
F1セル（例:「田中」）の値を、A1:D1（「日付」「名前」「記号」「数値」）の中から検索し、
A:.D（A列～D列全体）から一致する列のデータを取得します。
ここでは、A1:D1の中で「名前」が一致する列（B列）を見つけ、A:.DのB列（「名前」列）全体を
返します。

DROP(...,1)
取得したデータの先頭1行（通常はヘッダー行）を除外し、実データのみを返します。

つまり、この数式は「名前」列のデータ（ヘッダーを除く）を抽出します。F1に「田中」と
入力されている場合でも、B2:B7の「名前」リスト（田中、小原、佐倉、花澤、雨宮、久保）が
返されます。

だそうです。何でしょうね？さっき、とんでもない失敗をしたので動揺してるんでしょうか？ちょっと言ってることが変です。「F1セル（例:「田中」）の値を」って、セルF1の値はヘッダの"名前"です。"田中"はデータです。「F1に「田中」と入力されている場合でも、B2:B7の「名前」リスト」って、いやいや、だからセルF1に"田中"って入力してもダメなんですよ。まぁまぁ、いったん落ち着いて深呼吸してくださいな。

なんか、圧迫面接の面接官になった気分です(知らんけど)。いきなり難易度を上げすぎましたかね？いつもこうして試していると、時間を忘れてキリがないです。なので今回は、このへんにしておきます。また、別のコンテンツで数式を作ってみましょう。