エクセル（excel-xls-xlsx）
ChatGPT

エクセルで住所一覧から都道府県名だけを抽出する数式は=LEFT(A2,3+(MID(A2,4,1)=”県”))でした

2024.03.18
この記事は約7分で読めます。

=LEFT(A2,3+(MID(A2,4,1)=SNSでちょっとした話題になっていたのがエクセルで都道府県名だけを抽出する数式です。初めて見た式だったのですが調べてみたら正しく抽出できました。こんなこと考える人いるんですね、すごいです。
その数式は =LEFT(A2,3+(MID(A2,4,1)=”県”)) です。A2のセル内になる住所情報から都道府県名だけを抽出する式で２つの関数をうまく使っています。

エクセルで住所一覧から都道府県名だけを抽出する数式

=LEFT(A2,3+(MID(A2,4,1)=”県”))

この=LEFT(A2,3+(MID(A2,4,1)=”県”))がうまく動作するのかどうか気になり試してみることにしました。

=LEFT(A2,3+(MID(A2,4,1)=”県”))

このためだけにエクセルを立ち上げるのは面倒だったので、スマホのChatGPTアプリで調べました。GPTsの「ExcelGPT」を使ってみました。

308 Permanent Redirect
chat.openai.com

その結果は以下のとおりでした。

この結果だけ見ると「有効」であることはわかりました。しかし、本当に検証したかどうかがわからないですね。

GPTsの「ExcelGPT」で経緯を確認

そこで、GPTsの「ExcelGPT」が実証した経緯と結果を表示してもらいました。その内容は以下のとおりです。

python
Copy code
# セルA2の住所から県名を抜き出す数式のテスト
# 数式: =LEFT(A2,3+(MID(A2,4,1)=”県”))
# 47都道府県の各名前を処理して、数式の結果を確認する。

# 47都道府県のリスト
prefectures = [
“北海道”, “青森県”, “岩手県”, “宮城県”, “秋田県”,
“山形県”, “福島県”, “茨城県”, “栃木県”, “群馬県”,
“埼玉県”, “千葉県”, “東京都”, “神奈川県”, “新潟県”,
“富山県”, “石川県”, “福井県”, “山梨県”, “長野県”,
“岐阜県”, “静岡県”, “愛知県”, “三重県”, “滋賀県”,
“京都府”, “大阪府”, “兵庫県”, “奈良県”, “和歌山県”,
“鳥取県”, “島根県”, “岡山県”, “広島県”, “山口県”,
“徳島県”, “香川県”, “愛媛県”, “高知県”, “福岡県”,
“佐賀県”, “長崎県”, “熊本県”, “大分県”, “宮崎県”,
“鹿児島県”, “沖縄県”
]

# 数式を模倣する関数
def extract_prefecture(address):
# 最初の3文字を抽出
result = address[:3] # 4文字目が”県”の場合、最初の4文字を抽出
if len(address) > 3 and address[3] == “県”:
result = address[:4] return result

# 各都道府県に対して数式を実行し、結果をリストに格納
results = [extract_prefecture(pref) for pref in prefectures] results
Result
[‘北海道’,
‘青森県’,
‘岩手県’,
‘宮城県’,
‘秋田県’,
‘山形県’,
‘福島県’,
‘茨城県’,
‘栃木県’,
‘群馬県’,
‘埼玉県’,
‘千葉県’,
‘東京都’,
‘神奈川県’,
‘新潟県’,
‘富山県’,
‘石川県’,
‘福井県’,
‘山梨県’,
‘長野県’,
‘岐阜県’,
‘静岡県’,
‘愛知県’,
‘三重県’,
‘滋賀県’,
‘京都府’,
‘大阪府’,
‘兵庫県’,
‘奈良県’,
‘和歌山県’,
‘鳥取県’,
‘島根県’,
‘岡山県’,
‘広島県’,
‘山口県’,
‘徳島県’,
‘香川県’,
‘愛媛県’,
‘高知県’,
‘福岡県’,
‘佐賀県’,
‘長崎県’,
‘熊本県’,
‘大分県’,
‘宮崎県’,
‘鹿児島県’,
‘沖縄県’]

検証はエクセルの関数そのものを実施するのではなく、パイソン（python）で実行しているようです。

使っているエクセルの数式と関数の確認

まずは数式のチェック

この数式は、セルA2に入力された住所から県名を抜き出す際に特定のケースでうまく機能しますが、すべての住所形式や県名に対応しているわけではありません。具体的には、この数式は以下のような条件を前提としています。

  1. 都道府県名は3文字（例: 青森県、石川県、沖縄県）か、4文字（例: 神奈川県、和歌山県、鹿児島県）のどちらかである。
  2. 4文字の場合は必ず県名であるため4文字目が「県」である。

解説を追加すると、47ある「都道府県」のうち「都道府」は、東京都、京都府、大阪府、北海道の４つで、この４つはすべて３文字です。また「県」は43あり、ほとんど３文字ですが、神奈川県、和歌山県、鹿児島県の３つだけが４文字です。
そこで、左から３文字は共通なのでLEFT関数で切り出し、４文字目が県のときだけさらにもう１文字追加するということなんですね。

数式の動作は以下の通りです。

  • LEFT(A2,3)はA2セルの文字列から左から3文字を取り出します。
  • MID(A2,4,1)="県"はA2セルの文字列の4文字目が「県」であるかどうかを判断します。この結果が真（TRUE）であれば、4文字目が「県」であると認識し、偽（FALSE）であれば3文字の県名であるとみなします。
  • 真（TRUE）の場合は、LEFT(A2,3+1)、つまりLEFT(A2,4)となり、左から4文字を取り出します。偽（FALSE）の場合は、LEFT(A2,3)のままです。

関数の確認

LEFT関数

ExcelにおけるLEFT関数は、指定した文字列の左端から特定の数の文字を抽出するために使用されます。この関数はテキストデータの加工や特定の情報の抽出に便利です。LEFT関数の基本的な構文は次の通りです：

LEFT(テキスト, [文字数])
  • テキストは、抽出の対象となる文字列を指定します。
  • 文字数はオプショナル（省略可能）で、テキストの左端から抽出する文字の数を指定します。この引数を省略した場合、デフォルトで1文字が抽出されます。

例えば、LEFT("Excel", 3)という式は”Exc”を返します。これは”Excel”という文字列の左端から3文字を抽出するためです。同様に、LEFT("データ分析", 2)は”デー”を返します。

LEFT関数は、電話番号や郵便番号などの特定のフォーマットから国コードや地域コードのような部分を抽出する場合に特に有用です。また、テキストデータの前処理やデータの標準化にも役立ちます。

MID関数

ExcelのMID関数は、テキスト文字列から指定された位置から始まる特定の数の文字を抽出するために使用されます。この関数は非常に便利で、データの特定部分を切り出したい場合によく利用されます。MID関数の基本的な使い方は次のとおりです：

MID(テキスト, 開始位置, 文字数)
  • テキストは、抽出する元となる文字列です。
  • 開始位置は、テキスト内で文字を抽出し始める位置を指定します。1からカウントが始まります。
  • 文字数は、開始位置から抽出する文字の数を指定します。

例えば、MID("Excelは便利です", 6, 2)という関数を使用した場合、”は便”という結果が返されます。この例では、文字列”Excelは便利です”から6文字目の位置から始まり、そこから2文字を抽出しています。

MID関数は、データ整理や分析作業で特に有用です。例えば、固定フォーマットのテキストデータから特定の情報を抽出する際に役立ちます。また、住所や電話番号といった情報から特定の部分を取り出す場合にも使用できます。

エクセルは奥が深いしChatGPT活用は便利

今回の事例で感じたことは「エクセルは奥が深いな」ということです。

よくエクセル職人と言われるような人がマニアックなエクセルの使い方をしていることがありますが、まだまだ知らないエクセルの世界がありそうです。

また、ChatGPTでエクセルの式や関数を調べるのがとても便利だということがわかりました。

これからはエクセルで不明点があったら、エクセルのヘルプよりもChatGPTに回答を求めたほうがよさそうです。