これなんて無理ゲー？ 都道府県名の有無が不統一な住所から都道府県名を取り出す処理

Excelのアドイン「Excel Labs」から「LAMBDA関数」を利用する

　Microsoft 365とWeb版のExcelで使える「LAMBDA関数」をご存じですか？ 「ラムダ」と読みます。2022年の発表当時は、変数と計算式を指定してオリジナルの関数を作成できる！ と話題になりました。「ヘルパー関数」を組み合わせて従来の関数では実現できない処理も実現可能です。

Microsoft 365とWeb版のExcelで利用可能な「LAMBDA関数」は難易度高すぎ

　ただ、変数と計算式を自由に指定できると言われても困りますし、「名前の定義」とセットで利用する必要もあります。しかもヘルパー関数とは……？　実際のところ、あまり利用されていないようです。

　そんな難易度高すぎなLAMBDA関数ですが、以前に紹介したChatGPTも使える実験アドイン「Excel Labs」からは便利に使えることもあるので紹介したいと思います。

住所から都道府県名を取り出すには？

　以下の表では、会員リストに入力された住所から都道府県名を取り出しています。しかし、よく見ると「広島市」「奈良市」「葛飾区」など、都道府県名ではない文字列が抽出されています。

都道府県名ではない「広島市」「奈良市」「葛飾区」などの文字列が抽出されている

　セルE2の数式「=IF(MID(D2,4,1)="県",LEFT(D2,4),LEFT(D2,3))」の意味は『住所の4文字目を判定して「県」なら先頭から4文字、そうでなければ先頭から3文字取り出す』です。これは［住所］列に都道府県名のない住所が入力されていることが問題ですね。目視ではチェック漏れしそうなので、数式で判定したいところです。

都道府県名が含まれていない住所を判定する

　都道府県名のパターンは、3文字目が「都」「道」「府」「県」のいずれか、もしくは4文字目が「県」です。すべてOR条件でつなげることができます。

　都道府県名以降の3，4文字目が「都」「道」「府」「県」の可能性を考慮するなら、VLOOKUP/XLOOKUP関数を利用して、都道府県名と突合する必要がありますが、ここでは上記の条件で数式を考えてみます。

=OR(MID(E2,3,1)="都", MID(E2,3,1)="道", MID(E2,3,1)="府", MID(E2,3,1)="県", MID(E2,4,1)="県")

　数式の結果は「TRUE」か「FALSE」で返され、「TRUE」なら都道府県名が入力されていると判断できます。

都道府県名が含まれていれば「TRUE」、含まれていなければ「FALSE」と表示される

　この数式をセルE2にネストして、都道府県名が含まれていない場合に「■■■」と表示させるとしましょう。以下のような数式になりますが、長すぎて気が遠くなりますね。

=IF(OR(MID(E2,3,1)="都", MID(E2,3,1)="道", MID(E2,3,1)="府", MID(E2,3,1)="県", MID(E2,4,1)="県")=TRUE,IF(MID(E2,4,1)="県",LEFT(E2,4),LEFT(E2,3)),"■■■")

オリジナルの関数を定義する

　そこで、都道府県名を判定する数式をオリジナルの関数として定義してみましょう。「Excel Labs」の出番です。ここでは関数名を「TDFK」としました。参照する対象は住所なので、変数名は「住所」としました。

［ホーム］タブに追加されている［Excel Labs］をクリックして「Excel Labs」のパネルを表示する。［Advanced formula environment］の［Open］をクリックする

［Grid］タブをクリックして、セルF2を選択すると数式が表示される

数式をコピーする

［Names］タブに切り替えて［Function］を選択し、［＋］をクリックする

［Function name］に「TDFK」、［Argument］に「住所」と入力した。どちらも任意の名称で構わない。［Function definition］に先ほどコピーした数式を貼り付ける

セルF2の数式が貼り付けられているので、セル参照の「D2」の部分を変数名の「住所」に修正する。［Done］をクリックする

オリジナルの関数「TDFK」が登録された

作成した関数をネストする

　定義した「TDFK関数」をセルE2の数式「=IF(MID(D2,4,1)="県",LEFT(D2,4),LEFT(D2,3))」にネストします。『TDFK関数の結果が「TRUE」なら、都道府県名を取り出す。そうでなければ「■■■」を表示する』の処理を記述します。

　既存の数式の修正にも「Excel Labs」が便利。セルを選択して［Grid］タブに切り替えて入力済みの数式を表示します。

　なお、［Shift］＋［Ctrl］＋［F］キーでインデントを自動調整すると、数式の構造を把握しやすくなります。いつもの数式を修正する際のエディターとして活用してもいいでしょう。

セルE2を選択して［Grid］タブに切り替えると、セルE2の数式が表示される

1行目にTDFK関数の条件式「IF(TDFK(D2) = TRUE,」、6行目に「,"■■■")」と追記する

［Shift］＋［Ctrl］＋［F］キーを押すとインデントが自動調整される。

［Save］をクリックすると、修正した数式がセルに反映される

セルの数式をコピーした。都道府県名以外に「■■■」が表示された

LAMBDA関数は「名前の定義」で確認できる

　これまでの操作で、一度も「=LAMBDA」のような数式を入力しませんでしたが、「Excel Labs」での操作の結果としてLAMBDA関数は登録されています。［名前の管理］ダイアログボックスから確認可能です。

［数式］タブの［名前の管理］をクリックする

［名前の管理］ダイアログボックスが表示された。定義したTDFK関数を選択すると［参照範囲］にLAMBDA関数が使われていることを確認できる

　LAMBDA関数はセルに直接入力するものではなく、指定した処理を呼び出す役割があると考えていいでしょう。利用する機会は少ないかもしれませんが、「Excel Labs」を利用して数式を簡略化するアイデアでした。