エクセル実践塾 > エクセル実践塾2016

住所から都道府県名を取り出して表示したい

 

あらかじめ都道府県の一覧を用意しておけば、 それを使った配列数式で、 住所から都道府県名を取り出して表示できます

用意した都道府県の一覧は、 配列数式で住所の頭3文字と比較します。

該当する都道府県の位置は、 MATCH関数で確認できます。

確認した位置を使って、 INDEX関数で選択したセル範囲から、 該当する都道府県名を取り出します。


 

 

@都道府県の一覧を用意しておきます

 

ALEFT関数を使えば、対象セルのデータから頭3文字を取り出すことができます

配列数式で都道府県一覧を比較するので、数式全体をカッコで囲みます。

B作例では表がテーブル化されているので、セルの指定は項目名で行われます

セル番地で指定する際には、相対参照、もしくは列のみを固定した複合参照にします。

 

C都道府県名は頭3文字で区別できるので、LEFT関数を使って頭3文字を取り出します

D比較するのは、都道府県一覧のセル範囲です

絶対参照で指定します。

E住所から取り出した頭3文字と、都道府県一覧の頭3文字を、配列数式で比較します

配列数式で比較するので、数式全体をカッコで囲んで、Ctrl+Shift+Enterで確定します。

配列数式になると、全体が{}が囲まれます。

作例では、表がテーブル化されているので、自動的に同列のすべてのセルに同じ配列数式が設定されます。通常の表では、確定した配列数式をフィルハンドルでコピーして使います。

F配列数式の結果は、左図のような配列になります

住所の頭3文字と、都道府県すべての頭3文字が比較され、その結果が真(TRUE)か偽(FALSE)の配列で得られます。

配列数式の結果は、比較対象のセルと同数のセルを選択してから、Ctrl+Shift+Enterで確定にすることで展開できます。作例では、「栃木県」に相当する配列がTRUE(真)となっていることを確認できます。

GMATCH関数を使って、TRUE(真)の位置を調べます

H「検査値」として、TRUEを指定します

I「照合の種類」は「完全一致」にします

JCtrl+Shift+Enterで配列数式にすると、TRUEの位置が確認できます

KINDEX関数を使えば、MATCH関数で得たTRUE位置に該当する値を取り出せます

L「配列」の都道府県一覧は絶対参照にします

都道府県一覧は一列の配列なので、「列番号」の指定は省略できます。

MCtrl+Shift+Enterで配列数式にすると、住所の頭3文字に該当する都道府県名が表示されます

作例のように、「和歌山県」などの4文字の都道府県名も取り出せます。

   

   
 

 

●住所から都道府県名を取り出す動画です

あらかじめ用意した都道府県の一覧と、 配列数式を使います。

関連する他のページ

名前にフリガナを登録したい
Alt+Shift+↑キーを使うと、フリガナの登録を行えます

名前を読み順で並べ替えたい
ふりがなの登録されている名前なら、通常の並べ替えでその読み順に並べ替えられます

名前にフリガナを表示させたい
「ふりがなの表示/非表示」を使うと、 名前を入力したときの読み仮名をフリガナとして表示できます

住所から都道府県を取り出したい
あらかじめ都道府県の頭3文字を抜き出したリストを用意して、 それを使ってVLOOKUP関数による表引きを行います

住所から都道府県名を取り出したい
都道府県名の一覧を用意すれば、 VLOOKUP関数で住所から都道府県名を取り出せます

住所から都道府県名を取り出したい
都道府県名の頭3文字を使って、住所から該当する都道府県を探して表示します

テーブル化されている表から表引きしたい
F5キーでテーブルおよび列の指定を簡単に行えます

住所データから抜き出した都道府県名を完全なものにしたい
VLOOKUP関数を使って、都道府県名リストと照合します

テーブル化した表を使って表引きしたい
あらかじめ適当なテーブル名をつけておけば、その名前で表引きを行えます

都道府県名から送料を求めたい
VLOOKUP関数を使って、都道府県名に該当する送料を検索します

例外の市を含む 都道府県から、送料を求めたい
IFERRORを使って、2つのVLOOKUP関数で段階的に送料を検索します

入力済みの住所を、都道府県名と、それ以降の住所に分けたい
郵政事業株式会社の郵便番号データで都道府県名を表引きすることで、 それ以降の住所と分けて表示することが可能です

郵便番号から、都道府県名と、それ以降の住所を、別々に入力したい
郵政事業株式会社のWebで公開されている郵便番号データを使えば、 郵便番号から都道府県名などを表引きできます

住所から都道府県名を取り出したい
都道府県名の頭3文字を使って、住所から該当する都道府県を探して表示します

住所データから都道府県名を抜き出したい
LEFT関数を使えば、住所の先頭に入力されている都道府県名を抜き出せます

名字と住所の重複を調べて、名前変換ミスの疑いのある人を強調する
便宜上、LEN関数で名前の頭2文字を抜き出し、 「&」で郵便番号とつないだもので、各行と全体を比較します

名字と住所の重複を調べて、名前変換ミスの疑いをメッセージ表示で注意する
便宜上、LEN関数で名前の頭2文字を抜き出し、 「&」で郵便番号とつないだもので、各行と全体を比較します

結合セルを含む表からの集計結果を、 別表に反映させたい
MATCH関数で対象を検索し、 ADDRESS関数とINDIRECT関数の組み合わせで、 SUM関数による集計を行う方法があります

時間帯ごとの予約状況を参照しながら、 新たな予約を追加していきたい
該当する時間帯から最大値を取得することで、 予約の詰まった時間帯を条件付き書式で警告できます

最大値の左側に記されている番号を知りたい
連番になっているなら、MATCH関数だけで該当番号がわかります

プランに応じた規定料金を求めたい
MATCH関数を利用すれば、プラン別の料金をVLOOKUP関数で求めることができます

A列の同一データ間で、B列で前後30日間に該当する日付を調べる
シリアル値で使われていない桁で、A列のデータを識別し、2つのデータをまとめたもので前後30日を判定します

すべての月で目標を達成した者だけを強調表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

すべての月で目標を達成した者だけにメッセージを表示する
PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます

都道府県順にデータを並べ替えたい
都道府県の一覧が入力されているセル範囲から、 ユーザー設定リストへの登録を行っておくと、 それを使った並べ替えが可能となります

住所から都道府県を取り出したい
あらかじめ都道府県の頭3文字を抜き出したリストを用意して、 それを使ってVLOOKUP関数による表引きを行います

顧客別の売上総額を並べ替えて、 累積構成比を求めたい
売上の累積構成比は、 売上と同じフィールドを使って集計できます

名前を読み順で並べ替えたい
ふりがなの登録されている名前なら、 通常の並べ替えでその読み順に並べ替えられます

名前にフリガナを表示させたい
「ふりがなの表示/非表示」を使うと、 名前を入力したときの読み仮名をフリガナとして表示できます

ABC分析したい
降順に並べ替えてから、比率の累計を求めて、適当な条件付き書式を適用します

一部のデータに絞り込んで順位を確認したい
フィルターを使えば、データの絞り込みや並べ替えを簡単に行えます

逆順の並べ替えを自動的に行いたい
INDIRECT関数を使って、逆順の並べ替えを行う方法もあります

隣接する列のデータを並べ替えたい
セル範囲を選択してから、並べ替えを実行します

No.0〜No.30の連番を正しく並べ替えたい
表示形式で「No.」を付加した、 数値の連番にしておけば、正しく並べ替えることができます

表の一部だけを並べ替えたい
表の一部だけを並べ替えたいときには、 そのセル範囲をあらかじめ選択しておいてから、並べ替えのボタンを使います

並べ替えないで、勝ち数による順位を知りたい
RANK関数を使えば、並べ替えることなく順位がわかります

勝ち数と得失点差で並べ替えたい
複数の条件を指定すれば、同じ勝ち数でも得失点差で並べ替えが実行されます

表を簡単に見やすくしたい
Excel 2010/2007なら、表をテーブル化すれば、 簡単に見やすくできるとともに、表の拡張なども容易に行えるようにできます

 

 

 

 

 

出前館

アウトドア&フィッシング ナチュラム