エクセル関数だけで都道府県名を効率よく取り出す方法

本稿は、ウェブ主自身がかつて運営していた『エク達』というウェブサイトからの転載コンテンツです。エクセルの醍醐味のひとつは、シンプルな計算式を知っておけば、複雑な文字列もスイスイ操作できる、という点にあります。本稿では実務上問題になることが多い、「都道府県名」に関する基本的な文字列操作について解説します。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

エクセルの醍醐味のひとつは、文字列操作が容易である、という点にある。本稿では、北海道を除く都道府県名から「都・府・県」を取り除く、非常にスマートなやり方を紹介したい。

都道府県名の抽出

「エクセル評論家」であるウェブ主自身がときどきお問い合わせをいただくのが、「都道府県」のデータから「都・道・府・県」を取り除く方法だ。さっそく、例を挙げよう。

日本には都道府県が47個ある。

「青森県」、「岩手県」…、といった具合に、「都道府県」を付す場合と、「青森」、「岩手」…、といった具合に、都府県名のみを付す場合があるだろう。ただし、例外として、北海道の場合は「道」を除いた「北海」だけでは道名として成り立たない。

仮に、こんなエクセルシートがあったとしよう(図表1)。

図表1 都道府県名

ここではシンプルに、A列に都道府県名のみを入力したシートを準備した。

都道府県名のみを切り出すわかりやすいやりかた

このA列から、図表2のように、「都道府県名」のみを取り出してみたい。

図表2 北海道以外については「都府県」を除いたデータ

いったいどうやるのが良いか。

この点、類似サイトを眺めると、こんなやり方が書かれていることが一般的だ。

  • 都道府県の名称は、神奈川県、和歌山県、鹿児島県を除くと、すべて3文字です。
  • ですので、計算式でIF文を使い、まずは北海道を除外しましょう。
  • 次に、神奈川県、和歌山県、鹿児島県の3つの県のどれかに該当していたら、その3つの県を除外しましょう。
  • 残りの都府県について、LEFT関数(左からの文字数を切り取る関数)で、2番目まで切り取りましょう。

…。

これは、一見するとそのとおりであり、初心者にとってはこれが一番わかりやすい。

すなわち、先ほどの図表1に関していえば、B2~C2セルあたりにこんな関数を入れてやれば良い、ということだ。

【B2セル】 =IF(A2=”北海道”,A2,0)

【C2セル】 =IF(or(A2=”神奈川県”,A2=”和歌山県”,A2=”鹿児島県”),left(A2,3),left(A2,2))

B2セルの意味は、「もしA2の値が “北海道” ならば、A2の値をそのまま採用し、そうでない場合は0を返せ」、C2セルの意味は、「もしA2の値が “神奈川県” “和歌山県” “鹿児島県” のいずれかであるならば、A2の左から3文字目までを、そうでない場合は左から2文字目までを切り取って返せ」、という意味だ。

いちおうやろうと思えばできるが…

これを入力した結果が、図表3のとおりだ。

図表3 入力結果

設例のケースだと、C2セルに「北海」と表示されてしまっているのはご愛嬌だ。

図表3はわかりやすく、IF文を2つに分けたが、実務的にはB2とC2をひとつのセルにまとめ、次のように記述してやると良い。

【D2セル】 =IF(A2=”北海道”,A2,IF(or(A2=”神奈川県”,A2=”和歌山県”,A2=”鹿児島県”),left(A2,3),left(A2,2)))

その結果が、図表4のとおりである。

図表4 入力結果

ただ、この方式だと、いかにもまどろっこしい。文字数で76文字にも達するからだ。

図表4のようなケースだと、タイトルも含めてせいぜい48行しかないが、これが何万行にも及ぶ顧客名簿等であれば、それだけで大変に多くの目盛を食うし、意外と脆弱なエクセルがあっけなくハングアップし、作業結果が失われてしまうという悲劇にも見舞われかねない。

計算式はもっと簡単にできる!

もっと、シンプルにやろう。

【E2セル】 =IF(A2=”北海道”,A2,LEFT(A2,LEN(A2)-1))

この結果が、図表5である。

図表5 入力結果

いかがだろうか。

この計算式は、A2セルの値が「北海道」だったときだけA2セルの値をそのまま採用し、それ以外の場合は左側から「文字長-1」を引く、というものである。

「東京都」「大阪府」「兵庫県」といった2文字の都府県名だと、文字長が3文字となるため、「文字長-1」は2文字であり、したがって「東京」「大阪」「兵庫」といった文字を抽出することができる。

一方、「神奈川県」「和歌山県」「鹿児島県」だったら文字長が4文字となるため、「文字長-1」は3文字であり、この場合もやはり「神奈川」「和歌山」「鹿児島」といった文字を抽出することができるのだ。

いずれにせよ、この方法だと、セルの計算式の文字数を34文字に圧縮できる。先ほどの図表4と比べてデータ量は約45%だ。

計算式を短くすれば、それだけエクセルがハングアップしてしまうリスクも減少する。データの加工はスマートにやりたいものだ。

エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

 

本文は以上です。

読者コメント欄はこのあとに続きます。当ウェブサイトは読者コメントも読みごたえがありますので、ぜひ、ご一読ください。なお、現在、「ランキング」に参加しています。「知的好奇心を刺激される記事だ」と思った方はランキングバナーをクリックしてください。

にほんブログ村 政治ブログ 政治・社会問題へ

このエントリーをはてなブックマークに追加    

読者コメント一覧

  1. Sky より:

    エクセル関数は必要に迫られて、やっつけ仕事で片付けるケースがほとんどなので、珠に達人の技を拝見すると、染み込ります。ありがとうございます。以後、機会があれば多変量解析、回帰分析、モンテカルロ解析などについても取り上げていただければ幸いです。

  2. ネリチャリ より:

    関数は使わないかな。A列を範囲指定して、Ctrl+Hです。上の窓に「県」を入れ、したの窓は空欄のままで「すべて置換」。都と府は手作業で削除します。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

ネリチャリ へ返信する コメントをキャンセル

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

【おしらせ】人生で10冊目の出版をしました

自称元徴用工問題、自称元慰安婦問題、火器管制レーダー照射、天皇陛下侮辱、旭日旗侮辱…。韓国によるわが国に対する不法行為は留まるところを知りませんが、こうしたなか、「韓国の不法行為に基づく責任を、法的・経済的・政治的に追及する手段」を真面目に考察してみました。類書のない議論をお楽しみください。

【おしらせ】人生で9冊目の出版をしました

日本経済の姿について、客観的な数字で読んでみました。結論からいえば、日本は財政危機の状況にはありません。むしろ日本が必要としているのは大幅な減税と財政出動、そして国債の大幅な増発です。日本経済復活を考えるうえでの議論のたたき台として、ぜひとも本書をご活用賜りますと幸いです。
関連記事・スポンサーリンク・広告