エクセルの配列数式

同窓会名簿を便宜上番地の数字の手前までを抽出する必要がありました。

Excelにはある特定の記号や符号を境目にデータを分ける機能が備わっています、例えば「,」「;」「:」「.」等、しかし冒頭の機能はないのです、そこでいろいろな関数を組み合わせて実現する必要があります。

調べてみたら

=LEFT("文字列",MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC("文字列")&1234567890))-1)

というのがありました、やはり同じことで悩んでいる方も多いようです。

これはまずセルA1に入力してある

東京都渋谷区神南２丁目２-１を例に

FIND(検索文字列, 対象, 開始位置[省略可])で最初の数字を見つけます。

ASC関数は全角を半角に直すものですべての数字を半角に揃えます、そして「&1234567890」はもし住所に数字が含まれていない場合FIND関数がエラーになるのを防ぐために付加してあります。

ASC(A1)&1234567890は

東京都渋谷区神南2丁目2-11234567890

となります、そして

FIND({0,1,2,3,4,5,6,7,8,9},"東京都渋谷区神南2丁目2-11234567890")

となりますがFIND({の"{}"は配列数式で[0-9]までの文字をそれぞれ「"東京都渋谷区神南2丁目2-11234567890"」の中から探し、その位置を調べます、その結果はそれぞれ、、

0=24
1=14
2=9
3=17
4=18
5=19
6=20
7=21
8=22
9=23

となります、重複数字は最初のみ有効です、この結果をMIN関数で抽出します。

上記の抽出結果の右辺から一番小さな値を探します、その式が

MIN(FIND({0,1,2,3,4,5,6,7,8,9},"東京都渋谷区神南2丁目2-11234567890"))-1

それが「2=9」なので左辺の「2」が最初に現れる数字で9文字目です。

そしてその1文字前までを先頭から抽出するために「-1」します、「9-1=8」になり、左から8文字を取り出せということになり、結果として

=LEFT("東京都渋谷区神南２丁目２-１",8)

という非常にわかりやすいものになります、この「8」を求めるのに

MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC("文字列")&1234567890))-1

という式が必要だったんですね、ああめんどくさい。

そして結果は下記のようになります。

東京都渋谷区神南

これを

SUBSTITUTE("東京都渋谷区神南２丁目２-１","東京都渋谷区神南,"")

とすることで「神南」より後ろと分けることが出きます、ただし

神南二丁目2-1などのように漢数字の場合はアウトです、その場合は前もって書き換えなければだめです。

書いている自分がこんがらがってしまいます、説明がとても難しいですね。