他のソースからExcelにデータをインポートするのは、特にインターネット上のソースからコピー＆ペーストする場合は、本当に面倒な作業です。メインフレーム、Microsoft Access、Lotus、Word、Word Perfect、Adobe Acrobatなどの他のプログラム、あるいはその他のテキストベースのソースからエクスポートされたデータは、一般的には簡単なプロセスです。なぜなら、すべてを単純なASCIIテキストファイルに圧縮できるからです。
Excelのインポートと解析オプションでは、ウィザードがこれらのプロセスをガイドします。画面の指示に従うだけです。データをインポートしたら、次に問題となるのは、データを適切に解析する方法です。特に、解析対象の各フィールドの情報に複数の単語、多数の句読点、特殊文字、その他の複雑な要素が含まれている場合はなおさらです。
1. 空のExcelスプレッドシートを開き、「Excelプロのヒント：データのインポートと解析」の手順に従います。すべての情報が1つのフィールドにインポートされるため、この文字列を個別のフィールドに分割するには、データを解析する必要があります。参考記事で使用したデータの解析は、各フィールドに類似したレコードが含まれていたため、非常に簡単でした。
2. この例では、12件のレコードを入力します。これらは手動で数分で編集できますが、ここで紹介する手法は数千件のレコードを編集する場合にも有効です。注：この記事の残りの部分では、スプレッドシートに1,000件のレコードがあると仮定します。このデータベースのフィールドは、名前、役職、支店所在地、市区町村、都道府県の5つの列（またはフィールド）です。
PCワールド / JDサルテイン 6つのフィールドすべてを1つの列にインポート
データベースを解析する前に
現状でデータベースを解析すると、結果が混乱するでしょう。なぜなら、名前の中には敬称や敬称が含まれているもの、ミドルネームやイニシャルが含まれているもの、接尾辞や資格が含まれているものなどがあるからです。さらに、他のフィールドにはレコードごとに1～5語が含まれています。また、区切り文字がスペースのみであるため、Excelは単語ごとに新しいフィールドまたは列を作成します。この混乱を解消するには、いくつかの手順が必要です。
PCワールド / JDサルテイン 再編成前に解析されたデータベース。
A. まず最後の単語を抽出します
1. 各テキスト文字列の最後の単語を抽出し、元のデータベースからその単語を削除します。今回の場合は、州名です。
2. カーソルをセル P5 に移動し、次の数式を入力します。
=TRIM(RIGHT(SUBSTITUTE(A5, “,REPT(“ “,100)),100))
3. P5 から P6 まで、そして P1000 まで数式をコピーします。
4. 次に、範囲をハイライトし、「コピー」を選択して1列右に移動し、「値の貼り付け」を選択します。これを行わないと、抽出した「状態」情報は、A5の新しい解析情報に基づいて変更されてしまいます。
5. 列Pを削除します。
PCワールド / JDサルテイン 各テキスト文字列の最後の単語を抽出します。
B. 各レコード/テキスト文字列の最後の単語を削除します
1. カーソルを列 B に移動し、次の数式を入力します。
=LEFT(TRIM(A5),FIND(“~”,SUBSTITUTE(A5,” “,”~”,LEN(TRIM(A5))-LEN(SUBSTITUTE(TRIM(A5),” “,””))))-1)
州を除くA5 のすべてのデータが B5 にあることに注意してください。
2. この数式を B5 から B6、B1000 までコピーします。
3. 列 B が数式になっているため、テキストに変換する必要があることに注意してください。
4. 次に、範囲 (B5:B1000) を強調表示し、[コピー]を選択して、1 列右 (列 C) に移動し、[値の貼り付け]を選択します。
5. 列 A と列 B を削除します。すべてが左に移動し、データベースは再び列 A に戻ります。
PCワールド – JDサルテイン 各テキスト文字列の最後の単語を削除します。
C. 繰り返しデータを使用して都市を抽出する
このデータベースで唯一一貫性のある情報は「Branch」という単語です。この情報を使って都市を抽出してみましょう。この機能を実行する方法はいくつかありますが、これはほんの一例です。
1. まず、検索と置換の手順を簡略化するために、数式にプラス記号 (+) などの文字を挿入します。
2. カーソルをホーム位置に移動します。
3. 「ホーム」タブの「検索と選択」ボタンをクリックします。リストから「置換」を選択します。
4. [検索と置換]ダイアログの[検索する文字列]フィールド ボックスに、 「Branch」という単語とスペースを入力します。
5. [置換後の文字列] フィールド ボックスに、「Branch」という単語の後にスペースを入力し、最後に+記号を入力します。
6. 結果をテストするには、「次を検索」ボタンをクリックし、「置換」ボタンをクリックします。結果が正確であると確信できる場合は、「すべて置換」ボタンをクリックします。
7. Excel によって各都市と Branch という単語の間に+記号が配置されていることに注意してください。
8. 次に、セルM5に次の数式を入力します: =RIGHT(A5,LEN(A5)-FIND(“+”,A5))。
9. 範囲を強調表示し、M5 の数式を M6 から M1000 までコピーします。
10. 列 M は数式になっているため、テキストに変換する必要があることに注意してください。
11. 次に、範囲 (M5:M1000) を強調表示し、[コピー]を選択して、1 列左 (列 L) に移動し、[値の貼り付け]を選択します。
12. 列 M を削除し (すべてが左に移動します)、範囲内の最も長い都市を収容できるように列 L を拡張します。
PCワールド / JDサルテイン 繰り返しデータを使用して都市を抽出します。
D. データベースのテキスト文字列から都市を削除する
1. カーソルをセル B5 に移動し、次の数式を入力します。
=LEFT(A5, FIND(“+”, A5&”+”)-1)
都市を除くA5のすべてのデータがB5にあることに注意してください。
2. この数式を B5 から B6、B1000 までコピーします。
3. 列 B が数式になっているため、テキストに変換する必要があることに注意してください。
4. 範囲 (B5:B1000) を強調表示し、[コピー]を選択して、1 列右 (列 C) に移動し、[値の貼り付け]を選択します。
5. 列 A と列 B を削除します。すべてが左に移動し、データベースは再び列 A に戻ります。
市が列 J に移動し、州が列 K にあることに注意してください。
PCワールド / JDサルテイン データベース文字列から都市を削除します。
E. 解析する前に列数を数える
この数式を使用して、Excel がデータを解析するために必要な列/フィールドの数をカウントします。
注: スプレッドシートにデータ操作のための十分な作業スペースがあることを確認してください。列や行は後からいつでも挿入または削除できます。
1. データを解析する前に、カーソルをセル M5 に移動して次の数式を入力します。
=IF(LEN(TRIM(A5))=0,0,LEN(TRIM(A5))-LEN(SUBSTITUTE(A5,” “,””))+1)。
2. 数式を M5 (データベースの最初のセル) から M6 にコピーし、データベースの最後のセル (例: M1000) までコピーします。
3. 範囲を強調表示し、[コピー]を選択して1 列右に移動し、[値の貼り付け]を選択します。
4. 次に、列 M (数式) を削除すると、すべてが 1 列左に移動します。
5. 新しい列Mの値を降順（最大から最小）で並べ替えます。この範囲の最大の数字は、データを解析する際に必要な列数（処理対象）を示します。この場合、5行目からA列からK列までの11列になります。
注: 列 J と K はすでに使用されており、作業する列がいくつか必要なため、B と K の間に 6 つの新しい列を挿入し、City と State を列 P と Q に押し出します。
PCワールド / Jd Sartain 数式を使用して、データを解析した後の列の数を調べます。
そしてデータを解析します
次に、テキストを列に分割ウィザードを使用してデータを解析します。
1. データベース (この場合は列 A のみ) を強調表示します。
2. [データ] > [テキストを区切り]を選択します。
3. テキスト区切りウィザード (手順 1) で、[区切り]ボタンを選択し、[次へ] をクリックします。
PCワールド / JDサルテイン テキスト区切りウィザードのステップ1から「区切り」を選択します。
4.ウィザード (手順 2) で[スペース]ボックスをオンにし、 [次へ]をクリックします。
5. 手順3で、最初の列をクリックし、「列データ形式」パネルから「テキスト」ボタンをクリックします。次の列をクリックし、再び「テキスト」ボタンを選択します。これを最後まで繰り返し、「完了」をクリックします。
注: 列に日付が含まれている場合は、「日付」ボタンをクリックします。テキストの場合は「テキスト」ボタン、数値の場合は「 一般」ボタンをクリックします。不明な場合は、すべてのフィールドで「一般」ボタンをクリックしてください。このボタンは、日付フィールドを日付に、数値を数値に、その他すべてをテキストに変換します。また、「列をインポートしない（スキップ）」ボタンを選択すると、不要なフィールドを省略できます。
また、「保存先フィールド」ボックスのセルアドレスにも注意してください。デフォルトは、ハイライト表示した範囲の最初の列の最初のセル（例：$A$5）です。デフォルトは通常のセルです。ただし、例えばデータベースをこのシート内の別の場所、別のシート、または別のワークブックに保存したい場合は、別のセルアドレスを選択することもできます。
PCワールド / JDサルテイン 列データ形式として、スペース区切り文字とテキスト（または全般）を選択します。
次にデータを整理します
まだごちゃごちゃしていますが、以前よりはずっと良くなりました！これで6つのフィールドが11列にまたがっています。なぜでしょう？論理的な区切り文字はスペースだけだったので、Excelはスペースを見つけるたびに新しい列を作成していたのです。さて、これを論理的な順序で元に戻す必要があります。
A. ソートと連結
1. データベースを列 A で並べ替えます。名前フィールドは 4 列を超えないことに注意してください。
2. カーソルをL5に移動します。「数式」 > 「テキスト」 > 「連結」を選択します。 「関数の引数」ダイアログウィンドウの「連結」パネルの下に、複数のテキストフィールドボックスがあります。以下のように入力します。
- Text1のA5をクリックします。
- Text2に引用符で囲まれたスペースを入力します。
- 次に、 Text3のB5をクリックします。
- Text4 に引用符で囲まれた別のスペースを入力します。
- Text5のC5をクリックします。
- Text6に引用符で囲まれた別のスペースを入力します。
- 最後に、Text7のD5をクリックし、[OK] をクリックします。
- または、L5 に次の数式を入力します: =CONCATENATE(A5,” “,B5,” “,C5,” “,D5)。
結果の一部には、名前フィールド全体が含まれます。また、名前と敬称の一部が含まれる場合もあります。
4. 新しいスプレッドシート (新しい WS) を挿入し、列 L をコピーします。新しいシートに移動し、値を A5 に貼り付けます。
5. また、列 P (市) と列 Q (州) を新しい WS の列 D と列 E にコピーします。
PCワールド / JDサルテイン 解析されたフィールドを結合するには、連結関数を使用します。
役職はCEO、CTO、CFO、COO、社長、シニアマネージャー、マネージャー、ディレクター、ヘッドの9つしかないことに注意してください。一部の役職は、マーケティング責任者、広報責任者、情報科学担当CTOなど、部門に関連付けられています。そのため、安全のため、該当する部門も数式に追加します。
注：数式ごとに使用できるSubstitute関数の最大数はわかりません。2003では7個までに制限されていましたが、Office 2007、2010、2016ではそれ以上になっています。私の場合は10個までに制限しています。それ以上になると編集が複雑になりすぎるからです。
B. SUBSTITUTE関数を使用してタイトルと記事を抽出する
つまり、新しい WS スプレッドシートのセル D5 に次の数式を入力します。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,”COO”,””),”CFO”,””),”CTO”,””),”CEO”,””),”社長”,””),”シニア”,””),”社長”,””),”ヘッド”,””),”マネージャー”,””),”マーケティング”,””)
6. セル G5 に次の数式を入力します。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D5,”社内業務”,””),”広報”,””),”ビジネス”,””),”財務”,””),”広告”,””),”情報”,””),”科学”,””),”そして”,””),”の”,””)
7. 最初の数式（A5を参照）はD5に入力され、タイトルを削除します。2番目の数式（D5を参照）はG5に入力され、部門と記事（and、ofなど）を削除します。
8. 数式を G5 から G1000 を通じて G6 にコピーします。
9. 範囲をハイライトし、「コピー」を選択してセルA5に移動し、「値の貼り付け」を選択します。これで、名前とタイトルが分離されました。
10. 列DとGを削除します。
PCワールド / JDサルテイン SUBSTITUTE関数を使用してタイトルと記事を削除します
C. 残りのフィールドを連結する
1. 元の解析済みデータベースに戻ります。フィールドはA5からK5まで伸びています。
2. カーソルをL5に移動し、次の数式を入力します：=CONCATENATE(C5,” “,D5,” “,E5,” “,F5,” “,G5,” “,H5,” “,I5,” “,J5,” “,K5)。この関数は、タイトルとブランチを再結合します。
3. L5 の数式を L6 から L1000 までコピーします。
4. 次に、カーソルをセルR5に移動し、次の数式を入力します: =TRIM(RIGHT(SUBSTITUTE(TRIM(L5),” “,REPT(“ “,60)),180))
重要な注意: 次の 2 つの数式もこのステップで使用できます。
=MID(L5,(LEN(R5)+2),99)
=IF(ISERROR(SEARCH(R5,L5,1)),L5,RIGHT(L5,LEN(L5)-LEN(R5)))
5. R5のこの数式をR6からR1000までコピーします。これで支店情報が得られます。
6. R5からR1000までをハイライトし、「コピー」を選択します。新しいWSスプレッドシートに移動し、列Cに値を貼り付けます。
PCワールド / JDサルテイン データベースから支店情報を抽出する
D. タイトルを抽出して完了
残っているのは、タイトルを抽出して新しい WS にコピーすることだけです。
1. カーソルをセル T5 に移動します。(セルの位置は任意であることに注意してください)。
2. 次の数式を入力します: =SUBSTITUTE(L5,R5,””)
3. T5 から T6 まで、そして T1000 まで数式をコピーします。
4. タイトルは、列 L のタイトルと支店名の組み合わせから抽出されていることに注意してください。
PCワールド / JDサルテイン L列の結合フィールドからタイトルを抽出します
5. T列のタイトルを新しいワークシートのB列にコピーします。「貼り付け」 > 「特殊」 > 「値」を使用してください。
これで、複数の単語を含むすべての列が適切なフィールドに分割され、フィールドごとにデータを操作できるようになりました。差し込み印刷用のメーリング リストを作成したり、封筒やラベルを印刷したり、Outlook に差し込んだり、別のデータベースの情報を使用したりすることができます。
PCワールド / JDサルテイン 完全に解析されたデータベース
