エクセルでよく目にする住所リスト。ひとつのセルに姓名が入力している場合もあれば、姓と名を分けて入力されている場合もあったりとさまざまです。エクセルにはこれらのデータを一瞬で分けたり結合したりするとても便利な機能があります。同じ機能を関数を使って行う方法と合わせてご紹介します。

 

 

一瞬で姓と名を分けたりつなげたりできるフラッシュフィル機能の使い方

Excel2013バージョンからフラッシュフィル機能が追加されました。データベースにあるセルの情報の加工をひとつ示すだけで、あとに並ぶセル情報も同様に加工してくれるというスグレモノ機能です。

入力されている姓名をフラッシュフィル機能で姓と名に分ける

  • セルには姓名が入力されています。

 

  • 最初に並ぶデータを姓と名に分けます。

 

  • 「新垣」姓のすぐ下のセルを選択します。
  • リボンの「データ」タブをクリックします。
  • 「データツール」グループにある「フラッシュフィル」をクリックします。

 

  • 「新垣」さんの書式に習って、その下に並ぶセルに姓だけが一瞬で入力されます。

 

  • 名前も同様です。
  • D2セルに新垣結衣さんのお名前だけを入力します。
  • 「フラッシュフィル」アイコンをクリックします。
  • 一瞬で、姓名の名だけが抽出されました。

フラッシュフィル機能で姓と名を結合する方法

  • 姓と名が分かれて入力されています。
  • D2セルに「新垣結衣」とフルネームで入力します。

 

  • リボンの「データ」タブをクリックし、「データツール」グループにある「フラッシュフィル」アイコンをクリックします。

 

 

  •  一瞬で分かれて入力されていた姓と名が結合されて表示されました。
  • 「新垣結衣」さんのお名前を入力する際、「新垣　結衣」と姓と名の間にスペースを入れて「フラッシュフィル」機能を使うと、すべて姓と名の間にスペースが入って結合された姓名が表示されます。

関数を使って姓と名を分ける方法

文字列関数のLEFT関数とFIND関数の組合せで姓名の姓と名を分けます。姓名の間にある不揃いのスペースを全角に統一するなどの準備も必要です。

関数の組合せをシンプルにするために姓名の姓と名の間のスペースを全角に揃える

  • 統一するスペースは全角でも半角でも構いません、どちらかに統一することがポイントです。
  • ここでは、全角スペースに統一します。
  • SUBSTITUTE関数を使います。SUBSTITUTE関数は、「=SUBSTITUTE(文字列,検索文字,置換え文字,[置換え対象])」の構成です。「置換え対象」は省略可です。
  • C2セルに「=SUBSTITUTE(B2,” “,”　”)」と入力し、「Enter」キーを押します
  • 「” “」半角スペースを「”　”」全角スペースに変更するとなります。

 

  • 半角スペースが全角スペースに変わりました。

 

  • オートフィル機能を使ってコピペします。
  • B列の姓名の間にあるスペースがC列で全角スペースに統一となりました。

関数を使って姓名を分けて文字列を抽出する方法

  • C2セルの姓名から姓を抽出します。
  • LEFT関数とFIND関数の組合せで抽出します。
  • FIND関数は、「＝FIND（検索文字,対象,[開始位置]」の構成です、開始位置は省略できます。
  • LEFT関数は、「=LEFT（文字列,[文字数]）」の構成で、文字数は省略できます。
  • FIND関数で全角スペースの位置「3」を左から返しますので1を引いて「2」を返すようにします。
  • 結果、LEFT関数は「=LEFT(C2,2)」となりますので、姓の「新垣」が返します。
  • 「Enter」キーを押します。

 

  • 「新垣」さんの姓が表示されました。

 

  • 次に、C2セルの姓名から名前を抽出します。
  • RIGHT関数を利用します。
  • 文字列の右（末尾）からスペースまでの位置を求める必要があるのでFIND関数で全角スペースの位置を求めます。
  • LEN関数で文字列全体の文字数を求め、FIND関数の数を引くことで名前を抽出します。
  • E2に「=RIGHT(C2,LEN(C2)-FIND(“　”,C2))」と入力し、「Enter」キーを押します

 

  • オートフィルを使ってC2の関数式をコピーします。

 

  • 姓名を分けたセルを2つ範囲指定します。
  • 右下隅の「■」（ハンドル）をダブルクリックして、姓と名の関数式をコピーします。

 

  • C列に並ぶ姓名が、それぞれ姓と名に分けて表示することができました。

半角スペース全角スペース修正せずに関数を使って姓名を分け抽出する方法

  • 上記は半角スペースを一旦全角スペースに修正してから、関数を使って姓名を分けて抽出しました。
  • そこで今回は、IFERROE関数を使って、修正せず一度に姓名を分けて抽出します。
  • IFERROR関数は、「=IFERROR(値,エラーの場合の値)」が構成ですので、全角スペースでエラーになった場合は半角で計算という式を作成します。
  • 「=IFERROR(LEFT(B2,FIND(“　”,B2)-1),LEFT(B2,FIND(” “,B2)-1))」と入力し、「Enter」キーを押します。（先のFIND関数の検索値は「”　”」全角スペース、後のは半角スペースで入力します）

 

  • 半角スペースのままでも、姓を分けて表示することができました。

 

  • 同様に名前の抽出する関数もIFERROR関数を使います。

 

 

  • 関数式をドラッグしてコピーします。
  • B列の姓名の間にあるスペースが全角でも半角でも関数を使って姓と名を分けて表示することができました。

関数を使って姓と名を結合する方法

文字列関数のLEFT関数とFIND関数の組合せで姓名の姓と名を分けます。姓名の間にある不揃いのスペースを全角に統一するなどの準備も必要です。

CONCATENATE関数を使って姓と名を結合する

  • CONCATENATE関数は、「=CONCATENATE(文字列１,文字列2,…）」と構成されます。
  • D2セルに「=CONCATENATE(B2,C2)」と入力します。
  • 姓と名の間に全角スペースを入れる場合は、「=CONCATENATE(B2,”　”,C2)」と入力します。

 

  • 姓「新垣」と名「結衣」が「新垣結衣」に結合して表示されました。

  • オートフィル機能を使って下のセルにコピペします。

  • コピペの結果、すべての姓と名が姓名と結合して表示されました。

 

私の感想

Excel2013バージョンから追加された「フラッシュフィル」機能は、関数を使わずとも、修正データを手本に列に並ぶデータを修正データの入力ルールを自動的にコピペしてくれるスグレモノです。

LEFT関数やRIGHT関数のそれぞれ左から何番目、右から何番目の文字を返すのかという文字数を、FIND関数やLEN関数で求めた数値で自動化しています。関数初心者でしたら難解に感じるかもしれませんが、分解して考えると次第に慣れてくるかと思います。

今回登場の関数は、LEFT関数、RIGHT関数、LEN関数、FIND関数などの文字関数の基本に、IFERROR関数の組合せやCONCATENATE関数をご紹介しました。場合によっては、今回の関数の組合せを方程式と考えてしまうのもいいかも知れません。

 

今回ご紹介の関数の使い方と「フラッシュフィル」機能は何かと利用する頻度も高いのでおススメです。

 

（marusblog記事紹介）

http://marus.info/excel-space-dekete/

http://marus.info/excel-ifs-marusblog/

http://marus.info/excel-pull-doen-menu/

 

 

今回のまとめ

姓と名を分けたり結合したりして、データを修正、統一する必要は少なからず生じます。関数を組合わせて行うのを基本として、Excel2013で新たに追加された「フラッシュフィル」機能は、それら関数を使わずして修正加工ができる点はおおいに効率的で役に立ちます。

今回ご紹介のいくつかの文字関数やFIND関数、LEN関数、そしてIFERROR関数の組合せをご紹介。馴染みやすい文字関数ですのでこの機会に関数の組合せも馴染め安くなるはずです。

 

フラッシュフィル機能やこまかな文字修正を施す関数の組合せはおおいに効率アップにつながります。

 

ぜひお試しください。

 

いかがでしたでしょうか。

最後まで読んでくださりありがとうございます。人気ブログランキングに参加中。こちらクリックして頂けましたらうれしく思います。
↓↓↓

Microsoft Officeランキング