🧙‍♂️
Excelの新関数とスピルを使いこなす - "第1正規化する関数"の作成

Yasunari Uchijima
2025/07/25に公開
 はじめにOffice 2024でExcelに新しいテキスト関数と配列関数が追加されてから、間もなく1年になります。

Office 2024 および Office LTSC 2024 の新機能 - Microsoft サポート
私自身、日々の業務では何だかんだExcelをこねくり回す機会が多いのですが、追加された新関数で出来るようになった処理が増えた実感があります。

そこで今回は、これまで試してきたアイデアの備忘録的に、Excelの新関数を使ったちょっとした小技を書き留めていこうと思います。

 今回の目標「Excelは表計算ソフトである」という標語は巷に溢れていますが、私の身の回りではExcelを何かの管理簿やチェックシートとして使うことも多いように感じます。しかし、そういったExcelファイルで収集したデータを整理しよう、となった時に以下のようなセルに出くわして困ることがあります。


社員コード
氏名
管理備品


001
Alice
・ノートパソコン
・マウス
・キーボード

002
Bob
・PC1
・PC2

003
Carol
・特になし

このExcelファイルでは、1つのセルの中に複数の値が入っているのでデータを抽出したり加工したりするのが難しくなります (例えば、ある社員が管理している複数の備品の内、1つを他の人に移譲したりする場合) 。

今回はExcel 2024で追加された様々な関数を用いて、上のようなExcelファイルを1つのセルに1つだけの値が入った (第1正規化された) 以下のように変換する関数 (第1正規化する関数) を構成していきます。


通し番号
社員コード
氏名
管理備品


1
001
Alice
・ノートパソコン

2
001
Alice
・マウス

3
001
Alice
・キーボード

4
002
Bob
・PC1

5
002
Bob
・PC2

6
003
Carol
・特になし


 1つのセルの展開は簡単実は、Excel 2024で追加されたTEXTSPLIT関数を用いれば1つのセルの中の文字列を複数のセルに展開することは簡単にできます。

TEXTSPLIT 関数 - Microsoft サポート
TEXTSPLIT関数は、1つのセル内の文字列を区切り記号により区切られた一連の配列に展開 (スピル) する関数です。スピルとは、1つのセルに入力された数式で生成された複数の値が、一連の配列として隣接するセルに配置される動作を指します。スピルの最大の特徴は、スピルされた配列が動的であることで、数式やその引数が変更されると出力される範囲にも直ちに反映されます。これにより、TEXTSPLIT関数は出力する範囲に応じて都度数式をコピーする必要なく、1つのセルに数式を入力するだけで必要な範囲だけの配列を出力できるのです。

動的配列数式とスピル配列の動作 - Microsoft サポート
今回の場合では改行コードCHAR(10)を区切り記号として使えば良さそうなので、TEXTSPLIT関数を用いた文字列の展開は次のように行えます。[1] [2]
=TEXTSPLIT(D3,, CHAR(10))


１つのセルの展開は実は簡単
しかし、これを複数のセルに対して一気に適用するとなると途端に難しくなります。なぜなら、TEXTSPLIT関数の第1引数に範囲を指定しても上手く機能しないからです。
=TEXTSPLIT(D3:D5,, CHAR(10))


範囲を指定すると上手く機能しない
スピルにより展開されてほしい区切り記号より後のセルがどこにもなく、消えてしまいました。[3] どうやら第1正規化する関数は、単にTEXTSPLIT関数を使うだけでは実現できないようです。

 セルがどれだけ展開されるか数えるまずは第1正規化する関数が出力する配列の範囲を求めるため、それぞれのセルがいくつのセルに展開されるのか数えることから始めましょう。

これには色々な方法があると思いますが、そもそもTEXTSPLIT関数は区切り記号で展開していたことを思い出すと、各セルの区切り記号の数を数えるのが良い方針に思えます。セルの中の特定の文字列の個数を数える方法は、直感的ではないですが以下の方法が知られています。
=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))/LEN(CHAR(10))
この関数は、文字列の文字数を数えるLEN関数と、文字列の中の特定の文字列を全て置換するSUBSTITUTE関数から構成されています。関数の仕組みは以下の通りです。
元々の文字列の文字数を数える
区切り記号を全て空白文字に置換した文字列の文字数を数える
1.から2.を引いた文字数を、区切り記号の文字数で割ることで、区切り記号の個数を求める
元々の目的はTEXTSPLIT関数で1つのセルがいくつのセルに展開されるのか数えることでしたから、求めたい値は区切り記号の数より1大きい数になります。すなわち、以下の関数です。
=1+(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))/LEN(CHAR(10))
幸いなことに、この関数は参照先を範囲に変換しても上手く動作してくれます。[4] これで、元々の表中の各セルが、変換後いくつのセルに展開されるのかが分かりました。



展開後のセル数を求める関数を手に入れた！

 1つの関数で変換するには？ここまでで、今回の目標である第1正規化する関数の出力範囲の大きさ (第1正規化を施した後のセルの個数) は求めることができました。ここからTEXTSPLIT関数を使った実現方法を考えるのであれば、TEXTSPLIT関数を何行かおきに計算すれば良さそうです。しかし、まず1行目にA1セルのTEXTSPLIT関数を入れて、5行目にA2セルのTEXTSPLIT関数を、7行目にA3セルのTEXTSPLIT関数を、...という方法を突き詰めていくのは、あまり良い方法には思えません。

そこで、以下の情報を基にして変換後のセルの値を求める別の方法に舵を切りたいと思います。
変換前のどのセルに含まれているか (引数①)
区切り記号で区切られた後の何番目の要素であるか (引数②)
この方法であれば、上記の2つの引数を基に1つの結果を出力する関数をただ1つだけ作成して、引数だけを変換後の各セルで異なるように指定することで上手くいきそうな気がします。



イメージはこんな感じ
実はこの方法は、既に紹介したTEXTSPLIT関数と同じ、Excel 2024で登場したテキスト関数であるTEXTBEFORE関数とTEXTAFTER関数を使えば実現できます。

TEXTBEFORE 関数 - Microsoft サポート

TEXTAFTER 関数 - Microsoft サポート
=LET(
  tmp, TEXTBEFORE(A1,CHAR(10),B1,,,A1),
  TEXTAFTER(tmp,CHAR(10),-1,,,tmp)
)
ここでは、A1セルに変換前の文字列が、B1セルに何番目の要素を取り出すかを示す数値が入っている場合を考えています。また、分かりやすさのためにLET関数を用いました。

LET 関数 - Microsoft サポート

関数の仕組みは以下の通りです。

TEXTBEFORE関数を用いて、文字列A1からB1番目の区切り記号 (CHAR(10)) より前の文字列を取り出す

LET関数を用いて、1.の結果をtmpという変数に格納する

TEXTAFTER関数を用いて、tmpから最後の区切り記号 (CHAR(10)) より後の文字列を取り出す



例えば４番目の要素を取り出す場合、それぞれの関数の出力はこうなる
今後は、この新しい方法で第1正規化する関数の構成を目指していきましょう！

 それぞれ指定する回数だけ繰り返すここからは、第1正規化する関数の引数①
変換前のどのセルに含まれているか
を求める関数を作成していきます。
第1正規化する関数のイメージで見せたように、この引数①は、変換後のセルと1対1対応するように、変換前のデータをそれぞれ指定する回数だけ繰り返す関数から作ることができそうです。ただし、必ずしも変換前の文字列を直接繰り返す関数を作る必要はないことも示しておきます。なぜなら、変換前の文字列と1対1対応する別のセルXを繰り返す関数を作成すれば、セルXと変換前のセルの対応をもとに変換前のセルを検索できるからです。セルXは一意であればどんな値でも良いですが、扱いやすいように変換前の表の主キーにするのが良いと思います。
この関数はTEXTSPLIT関数に加えて、TEXTJOIN関数とREPT関数を組み合わせることで実現できます。

TEXTJOIN 関数 - Microsoft サポート

REPT 関数 - Microsoft サポート
=LET(
  reptKeys, REPT(B3:B5&",", F3:F5),
  joinKeys, TEXTJOIN("", TRUE, reptKeys),
  TEXTSPLIT(joinKeys,, ",", TRUE)
)


変換前の表の主キーをそれぞれ指定する回数だけ繰り返す
ここでは、B3:B5に変換前の表の主キーが、F3:F5に変換前のセルが変換後にいくつのセルに展開されるか (展開後のセル数) を示す数値が入っている場合を考えています。関数の仕組みは以下の通りです。

REPT関数を用いて、主キーB3に区切り記号,を付加した文字列をF3回だけ繰り返す

REPT関数の引数は範囲であるため、1.が入力された全ての行で実施され配列reptKeysとして出力される

TEXTJOIN関数を用いて、配列reptKeysを1つの文字列joinKeysに結合する

TEXTSPLIT関数を用いて、joinKeysを区切り記号,によりスピルされた配列を出力する
ここで、変換前の表の主キー以外のセルの値はXLOOKUP関数を用いて変換前の表を検索することで求められます。

XLOOKUP 関数 - Microsoft サポート
=XLOOKUP(G3#, $B$3:$B$5, $C$3:$C$5)
G3#はスピルされた配列全体を参照する場合の記法です。XLOOKUP関数では、検索値の引数として配列を入力できるので、複数の検索を一度に実行できて便利ですね。

 セクション毎の通し番号を振るここからは、第1正規化する関数の引数②
区切り記号で区切られた後の何番目の要素であるか
を求める関数を作成していきましょう。

基本的なアイデアは前の章と同じTEXTSPLIT関数とTEXTJOIN関数の組み合わせですが、今回は同じ値の繰り返しではなく、変換前のセル毎に通し番号を振らなければいけないのが厄介です。Excelで通し番号を振る方法としては真っ先にSEQUENCE関数が挙がりますが、SEQUENCE関数は全体を通しての採番しかできないため今回は使えません。そこで今回使うのがMAKEARRAY関数です。[5]

MAKEARRAY 関数 - Microsoft サポート
MAKEARRAY関数は、第1引数の行数、第2引数の列数の配列を作成し、各要素の値を第3引数のLAMBDA関数で指定する関数です。このLAMBDA関数は、第1引数に行番号を、第2引数に列番号を取ります。

このMAKEARRAY関数を用いることで、変換前のセル毎に通し番号を振るための第一歩が得られます。
=MAKEARRAY(ROWS(B3:B5), MAX(F3:F5), LAMBDA(r,c,c))


まずは列番号の配列を作る
ここでは、B3:B5に変換前の表の主キーが、F3:F5に変換前のセルが変換後にいくつのセルに展開されるか (展開後のセル数) を示す数値が入っている場合を考えています。

この関数では、行数が変換前の表の行数に等しく、列数が展開後のセル数の最大値となる配列を作成しています。そして、この配列の値は列ごとに1から始まる連番が振られています。

ここから目的の第1正規化を実現する関数の引数②を得るためには、余分なセルを消せば良さそうですね。大変便利なことに、これはIF関数を使うだけで実現できます。
=LET(
  arrCol, MAKEARRAY(ROWS(B3:B5), MAX(F3:F5), LAMBDA(r,c,c)),
  IF(arrCol>B1:B3, "", arrCol)
)


列番号の配列から余分なセルを消去する
見通しを良くするためLET関数を使っていますが、変数arrColは先ほどのMAKEARRAY関数を使った列番号の配列と同じです。実際に余分なセルを消す部分はIF関数を使っており、単にF3:F5 (展開後のセル数) との大小比較を行っているだけです。
長くなってしまいましたが、これで変換前のセル毎に通し番号を振る準備が整いました。最後は上の配列をTEXTSPLIT関数とTEXTJOIN関数を使って1列に統合すれば、目標達成です！
=LET(
  arrCol, MAKEARRAY(ROWS(B3:B5), MAX(F3:F5), LAMBDA(r,c,c)),
  arrSN, IF(arrCol>F3:F5, "", arrCol),
  joinSN, TEXTJOIN(",", TRUE, arrSN),
  TEXTSPLIT(joinSN,, ",", TRUE)
)


配列を１列に統合すれば、引数②の完成！

 完成！の前に...ちょっとした小技ここまでで第1正規化する関数に必要な関数は全て作成できました。早速それぞれの関数を組み合わせていきたいところですが、その前に配列関数を使ったデータ参照の小技を紹介します。

これまでセル範囲を参照するときはA1:A3のようにコロンを利用した書き方をしてきましたが、この書き方では行数が変わる度に参照範囲を超過しないか確認する必要があり不便です。そのため本筋からは逸れますが、TAKE関数とDROP関数を使って可変の範囲を参照する方法を導入しておきましょう。
TAKE関数とDROP関数は、どちらも配列の連続した一部分を取り出す関数です。TAKE関数は先頭又は末尾から指定した行数又は列数だけを取り出した配列を返し、反対にDROP関数は先頭又は末尾から指定した行数又は列数だけを取り除いた配列を返します。

TAKE 関数 - Microsoft サポート

DROP 関数 - Microsoft サポート
両者を組み合わせることで、指定する列の内、実際にデータが入っている部分だけを取り出すことが出来ます。
=LET(
  ref, B:B,
  rowZero, ROW(B2),
  numRows, MAX((ref<>"")*ROW(ref))-rowZero,
  TAKE(DROP(ref,rowZero),numRows),
)


見た目は全く同じだけど、使いやすさが全然違う
ここでは、取り出したいデータがB列に格納されており、B2セルは列名等のデータ以外の部分 (ヘッダ) である場合を考えています。関数の仕組みは以下の通りです。

B列全体に対して空白文字 ("") との大小比較を行う (セルが空でない場合にTRUEを返す)
1.の結果とROW関数で求めたB列の各セルの行数との積を取る。このときTRUE=1、FALSE=0として処理されるので、MAX関数で最大値を取るとB列の空でない最大の行数が得られる
2.の結果とROW関数で求めたヘッダの行数の差を取り、データの行数を求める

DROP関数を用いて、B列全体からヘッダより上側を取り除く

TAKE関数を用いて、4.の結果 (ヘッダの下側全体) から3.の結果 (データの行数) だけ配列を取り出す
これで、変換前の表のデータがどのように増減しても、問題なくデータを参照できるようになりました。この小技も活用しつつ、いよいよ第1正規化する関数を完成させていきましょう。

 いよいよ完成！最後に、これまで作成してきた関数を組み合わせて第1正規化する関数を完成させます！

本章では、以下の画像のように、変換前の表 (B列~D列)・作業列 (F列~H列)・変換後の表 (J列~M列) を並べる形で作成していきます。



この章では、このファイルの空列を埋めていく
まず、第1正規化する関数による展開後のセル数をF4セルに記載します。
展開後のセル数 (F4セル)
=LET(
  refTgts, $D:$D,
  rowZero, ROW($D$3),
  numRows, MAX((refTgts<>"")*ROW(refTgts))-rowZero,
  tgts, TAKE(DROP(refTgts,rowZero),numRows),

  N(tgts<>"")+(LEN(tgts)-LEN(SUBSTITUTE(tgts,CHAR(10),"")))/LEN(CHAR(10))
)
上記は変換前の表の列「管理備品」のデータを配列tgtsとして参照する関数と、変換により各セルが展開されるセル数 (展開後のセル数) を求める関数の組み合わせですね。ただし、もし空白のデータが紛れ込んだ場合は行を削除して欲しいので、最後に加算する1をデータが空白文字かどうかを数値化したものN(tgts<>"")に変更しています。これにより、もし空白のデータがあった場合 (当然区切り記号も0個なので) 関数の出力は0となります。
次に、第1正規化する関数の引数となる2つの関数をG4セルとH4セルに記載します。
引数① (G4セル)
=LET(
  refTgts, $D:$D,
  rowZero, ROW($D$3),
  numRows, MAX((refTgts<>"")*ROW(refTgts))-rowZero,
  tgts, TAKE(DROP(refTgts,rowZero),numRows),

  refKeys, $B:$B,
  keys, TAKE(DROP(refKeys,rowZero),numRows),

  repts, F4#,
  reptKeys, REPT(keys&",", repts),
  joinKeys, TEXTJOIN("", TRUE, reptKeys),
  TEXTSPLIT(joinKeys,, ",", TRUE)
)
引数② (H4セル)
=LET(
  refTgts, $D:$D,
  rowZero, ROW($D$3),
  numRows, MAX((refTgts<>"")*ROW(refTgts))-rowZero,
  tgts, TAKE(DROP(refTgts,rowZero),numRows),

  refKeys, $B:$B,
  keys, TAKE(DROP(refKeys,rowZero),numRows),

  repts, F4#,
  arrCol, MAKEARRAY(ROWS(keys), MAX(repts), LAMBDA(r,c,c)),
  arrSN, IF(arrCol>repts, "", arrCol),
  joinSN, TEXTJOIN(",", TRUE, arrSN),
  TEXTSPLIT(joinSN,, ",", TRUE)
)
かなり長くなってきたので初見で読み解くのは困難かもしれませんが、これらも変換前の表の列「管理備品」と「社員コード」のデータを配列tgtsとkeysとして参照する関数と、第1正規化する関数の2つの引数を出力する関数の組み合わせになっています。ここでは、既に求められている展開後のセル数を配列reptsとして参照しています。
そして、いよいよ第1正規化する関数をM4セルに記載します。
第1正規化する関数 (M4セル)
=LET(
  refTgts, $D:$D,
  rowZero, ROW($D$3),
  numRows, MAX((refTgts<>"")*ROW(refTgts))-rowZero,
  tgts, TAKE(DROP(refTgts,rowZero),numRows),

  refKeys, $B:$B,
  keys, TAKE(DROP(refKeys,rowZero),numRows),

  rawStr, XLOOKUP(G4#, keys, tgts),
  tmp, TEXTBEFORE(rawStr,CHAR(10),H4#,,,rawStr),
  TEXTAFTER(tmp,CHAR(10),-1,,,tmp)
)
冒頭のセクションは2つの引数を作成した関数と同じ、配列tgtsとkeysを生成する部分であり、最後のセクションは、既に説明したTEXTBEFORE関数とTEXTAFTER関数を使って要素を取り出す関数になっています！[6]　

これでようやく第1正規化する関数が完成しました！



今回の目標達成！
最後に残った部分ですが、変換後の表における新しい主キーの列「#」については、SEQUENCE関数を用いれば簡単に連番を振ることができます。
新しい主キー (J4セル)
=SEQUENCE(ROWS(G4#))
それ以外の列も変換前の表とデータ自体は同じなので、XLOOKUP関数を使えば問題ありません。
氏名 (L4セル)
=LET(
  refTgts, $D:$D,
  rowZero, ROW($D$3),
  numRows, MAX((refTgts<>"")*ROW(refTgts))-rowZero,
  tgts, TAKE(DROP(refTgts,rowZero),numRows),

  refKeys, $B:$B,
  keys, TAKE(DROP(refKeys,rowZero),numRows),

  refSrcs, $C:$C,
  srcs, TAKE(DROP(refSrcs,rowZero),numRows),
  XLOOKUP(G4#, keys, srcs)
)
変換前の表のデータを配列として参照するための部分があるため長く見えますが、本質的な部分はただのXLOOKUP関数ですね。

 最後にこれで今回の目標、第1正規化する関数の構成は完了です。もし手元に1つのセルに複数のデータが入力されていてどうにもならないExcelファイルがある場合、この記事で説明した内容を基に、よりデータ処理のしやすい新しいExcelファイルへの変換が可能になっていれば幸いです。
また、この記事で紹介した様々な関数やスピルのテクニックを使って、ぜひ皆さま自身で必要な処理を実現してみて下さい。

スピルを使えば入力データを自由に挿入・削除できて便利ですよ！！！ [7]



入力データに追記すれば、変換は自動的に行われる

脚注
スピルを利用しているため、数式が入力されているのはF3セルのみであり、F4セル以降には数式を入力していないことに注意してください。 ↩︎
TEXTSPLIT関数の2番目の引数が空であり、3番目の引数に区切り記号を入れているのは、セルを行方向に展開するためです。 ↩︎
列方向の展開を試みた場合も同じ結果になります。 ↩︎
 TEXTSPLIT関数で展開された行数又は列数を直接ROWS関数やCOLUMNS関数で数えられれば良かったのですが、前章で示した通りTEXTSPLIT関数は範囲を参照すると上手くいかないため使えませんでした。 ↩︎
MAKEARRAY関数は2022年に追加された関数ですが、残念ながらExcel for Microsoft 365でしか使えません。もし利用できない場合は、行毎にSEQUENCE関数を実行する方法が考えられます。 ↩︎
変換前の表の文字列rawStrは、主キーである引数①を基にXLOOKUP関数で求めています。 ↩︎
もちろんスピルにも欠点はあり、例えばフィルタが適用できません。フィルタを使いたい場合は変換後の表を数値化して別の場所に貼り付ける等の対処が必要です。 ↩︎