【分かりやすいExcel】データ整形で使う手法教えます！置換/区切り/結合！

2024年4月15日2024年9月9日

この記事ではデータ整形でよく使われる手法についてついて書いていきたいと思います。

データ整形においては、集めたデータも様々で一概に方法を述べることはできませんが、手法は基本的に共通していると思いますので、そのあたりご説明していきます！

データの整形について

データの整形が必要な状況としては、何らかのデータベースを作りたいときに直面することが多いのではないでしょうか。

例えば、インターネットの様々なサイトから店舗情報（店舗名、電話番号、FAX番号、住所、営業時間など）を引っ張ってきてデータベースを作ろうと思っても、サイトによって表示がバラバラで使い物にならないということがあると思います。

データベース作成のために集めたデータの一部を削除したり、区切ったり、結合したり様々な手法を用いる必要が出てくると思いますので、もちろん全てではないですが、そんな時に記事が少しでも役に立てばと思います。

これから各方法を複数の記事で書いていきますが、そこでは下記のようなデータを整形していきたいと思います（章によっては少し説明しやすいようにデータを変えていきます）。郵便番号にハイフンがないところや、住所がカンマで区切られていたり、フリガナが半角と全角という状況でバラバラです。

ここでは

ある文字を別の文字に変換する置換方法、

セル内の文字を分ける区切り方法

文字と文字をつなげる結合方法

を書いていきたいと思います。

置換方法：検索と置換（【ctrl】+【H】）

おそらく一番ベーシックな方法を書いていきます。

住所のカンマ（,）をほかと同様にハイフン（-）に置換したい場合、

【ctrl】+【H】（【ctrl】+【F】でも可）

を押すと「検索と置換」というウインドウが開きます。

①検索する文字列に「,」を入力
②置換後の文字列に「-」を入力
③すべて置換を押す

こちらを実行すると6件の置換（「,」⇒「-」が6個置換）が完了したウインドウが開きます。

ここで注意が必要なのが、特にセルや範囲を指定しない場合は「すべて置換」を押すと同じシート内（上記画像をみると検索場所がシートに指定されている）の該当する箇所がすべて置換されてしまい、意図しない箇所までも変わってしまいます。そのため、同じ文字列で置換したくないものが同じシート内にある場合は、置換するセルや範囲をドラッグ等で指定することをお勧めします。

また、「検索と置換」のウインドウの中に「セル内容が完全に同一であるものを検索する」というオプションがありますので、「,」でもセル内に「,」のみ記入されたセルを探し出してそれだけを置換することも可能です（文章の途中にある「,」は置換されない）。

置換方法：関数での置換

関数を使った置換方法もありますのでそちらもご説明していきます。状況や好みで使い分けをしてください。

=REPLACE

REPLACEは指定位置の文字を置換します。

先ほどの【ctrl】+【H】は文字を検索して、その文字を置換するものですので、位置を指定して置換するREPLACEとは異なります。

例えば、郵便番号のリスト（下図C列）が「-」ではなく「,」で区切られていた場合にREPLACEを使って「-」に置換してみたいと思います。

郵便番号の横に列を1列挿入し、下記の関数を入れます。

=REPLACE(C1,4,1,“-“)

文字列：C1　（どの文字列を設定するかというものです）
開始位置：４　（左から何文字目から変更するかというもの）
文字数：1　（何文字分まで変更するか。「,86」をまるまる「-」にしたい場合は3となります）
置換文字列：“-“　（置換したい文字を入れます。文字のため””で囲む必要があります）

上記を記入しますと「102-8688」になると思います。

あとはオートフィル機能で3行目まで引っ張るとすべて「,」が「-」になります。

REPLACEの利点としては、やはりセル内に置換したい同じ文字が2個以上あっても位置を指定できるため、片方のみ置換することができるという点だと思います。【ctrl】+【H】ですと同じセルに2個以上あるとすべて置換されてしまうため、片方のみということができません。

ただ一方で、REPLACEは複数のセルで同じようにやりたい場合、すべてのセルで置換したい文字が同じ位置にないとオートフィル機能で汎用性がないというところはやや使いにくいかなと思います。
（このセルでは4文字目に「,」があって、こっちのセルでは3列目にあるというような時は不向きです）

=SUBSTITUTE

SUBSTITUTEは【ctrl】+【H】とほとんど同じような感じになりますが、強いて違いを言えば、

SUBSTITUTEは関数のため、
一気に複数（例えば、「,」と「;」の両方を一度に「-」に置き換える）の種類の文字列を置換することができますが、
空欄の一列を挿入してそこに関数を入れるため、元データと置換後のデータが2個できます。
（【ctrl】+【H】は元データ上で置換を行うので無駄な列を挿入する必要はなし）

=SUBSTITUTE(C1,“,”,“-“)

文字列：C1　（どの文字列を設定するかというものです）
検索文字列：“,”　（文字のため””で囲む必要があります）
置換文字列：“-“　（置換したい文字を入れます。文字のため””で囲む必要があります）

上記を記入しますと「102-8688」になると思います。

先ほど複数の種類の文字列を置換できると記載しましたが、そちらもちょっとご説明できればと思います。

例えば、電話番号に記載が「03)3264,2111」のような記載になっており、「)」と「,」を「-」に変更いしたい場合、【ctrl】+【H】で2段階で実施してもいいのですが、SUBSTITUTEでは一気に可能となります。

=SUBSTITUTE((SUBSTITUTE(F1,”)”,”-“)),”,”,”-“)

1個目の関数が(SUBSTITUTE(F1,”)”,”-“))となり、こちらは「)」を「-」に変換する関数となります。
2個目の関数は、(SUBSTITUTE(F1,”)”,”-“))をXとした場合、=SUBSTITUTE(X,”,”,”-“)となり、「,」を「-」に変更する形となっております。

上記を記入しますと「03-3264-2111」になると思います。

関数の中に関数がある状況ですので少しややこしいですが、このパターンであれば3個目や4個目の文字列を置換することが可能となります。

区切り方法：区切り位置の設定

区切りのベーシックな方法としては、エクセルの上部に配置してある「区切り位置」の設定だと思います。（赤枠内）

例えば下記の様に郵便番号と住所が同じセルに入ってたとします。
このデータを郵便番号と住所に分けたい（区切る）と考えた時を想定します。

セルを選択した状態で、先ほどの「区切り位置」を選択するとウインドウが立ち上がります。

区切り位置の選択方法としては2種類になっています。

①コンマやタブなどの文字（空白スペースでもOK）を指定して区切る
②決められた長さ（何文字目の様なイメージ）を指定して区切る

コンマやタブなどの文字を指定して区切る

文字を指定して区切る場合、データの形式として一つ目を選択し、「次へ」ボタンを押します。

次の画面では、どの文字で区切るかを選択することができます。
ここでは郵便番号と住所の間にスペース（空白）がありますので、スペースを選んでみます。
そうすると、実際のデータの区切り方のプレビューが表示されます。

プレビューを見てみるとデータが3つに分かれていることが分かります。
これは住所の中にもスペース（空白）が存在しており、そこも区切る場所として認識されたからとなります。

ただ、今回はあくまでも郵便番号と住所の2つに分けたいので、「②決められた長さ（何文字目の様なイメージ）を指定して区切る」で進めていきたいと思います。

※今回はスペース（空白）で区切る例となりますが、「その他」の欄に、指定の文字を入力することで、その文字でも区切ることができます。例えば、「○○市△△町」を「市」で区切りたい場合は、その他の欄に「市」を入力してください。

決められた長さを指定して区切る

長さを指定して区切る場合、データの形式として二つ目を選択し、「次へ」ボタンを押します。

次の画面では、プレビューでどの位置で区切るかマウスで指定することができます。
決められた位置ということで、選択したセルにすべて適応されますので、そこはご注意ください！
今回は初期から意図している場所に区切りラインが来ていますので、このまま右下の「完了」ボタンを選択します。
（「次へ」ボタンを押すと、区切ったデータの形式を選択することができますが、特に設定しなくても問題ありません。）