フラッシュ フィル


Posted by Diego M. Oppenheimer
8 月 9 日

この記事は、Excel チームのプログラムマネージャーである Chad Rothschiller が担当しました。この新機能を実際にお試しになるには、ここから Office プレビューをダウンロードしてください。

Microsoft に入社する前、私はデータ処理を長年にわたって行ってきました。大量のテキストから、姓名やメールアドレスを抽出する仕事をよくしたものです。姓と名前を 1 列のテキストに組み合わせることをよく行いました。姓のイニシャルだけが必要で、これを名前と組み合わせることもしました。日付、金額などの書式や表示方式を変更することも行いました。データはデータベースから取得したものであるため、名前に大文字小文字を正しく設定する (すべてを大文字または小文字にするのではなく) 必要もよくありました。

今日は Excel 2013 の「フラッシュフィル」という新機能を自信をもってご説明したいと思います。これは、このような作業をほとんど初心者のユーザーでも簡単に行えるようにする機能です。数年前、私はこのような文字列操作の方法を紹介するブログ記事を書きました。問題は、この方法が関数の使用に大きく依存していたことです。これは極めて複雑で、Excel に慣れていない人には敷居の高い方法でしたが、現在ではフラッシュフィルを活用すれば関数を使用せずに実行できるようになりました。従来よりも実に簡単な方法です。それでは、実際に例を紹介しながら説明していきましょう。

フラッシュ フィルは次のように表示されます。

私の以前の記事で取り上げた例を、今度はフラッシュフィルを使って処理してみましょう。内容がさらに興味深いものになるように、元のデータ セットの値をいくつか更新しておきました。このデータはここから入手できます。.このデータを Excel に取り込むときには、別々の列に分割する必要がありますが、私は「カンマ」を区切り文字として使用する、区切り位置機能を使用しました。

メモ:ここでは、先頭の行に太字の書式設定を設定して、目立つようにしています。これは Excel が先頭行が見出しであると判断するのにも役立ちます。よって、フラッシュ フィルではパターン規則を作成するときに、これを例の一部としては使用しません。

 

ステップ 1: 社会保障番号を 123-45-6789 のように書式化する

SSN 列とLast Name 列との間に新しい列を挿入して、この列の名前を「SSN」とします。書式化した SSN を入力し始めると、前の行に入力した値に基づいて Excel が列の値の入力をサポートすることに注目してください。

  1. 挿入した列に、書式化した値を表示するのに十分な幅を持たせます (80 ピクセルあれば十分です。これはフラッシュ フィルの使用に必須の条件でなく、単に見やすくするための処置です)。
  2. 1 つ目の社会保障番号 (SSN) をハイフンを使って「413-66-4341」のように B2 に入力します。
  3. 2 つ目の SSN 値の入力を始めます。まず「2」と入力してください。

この時、皆さんが入力しようとしている内容をフラッシュ フィルが判断して、入力値を提案します。

その提案内容に問題がなければ (まさに入力しようとしている値であれば)、Enter キーを押して、その内容を受け入れます。

さて、もし皆さんが私のような疑り深い性格であれば、ステップ 2 で自分が SSN を正しく再入力できるかどうかが気になることでしょう。その場合は、A2 をダブルクリックして (「セルの編集モード」に入ります)、その値をコピーし、次にセル B2 をダブルクリックして (「編集モード」に戻ります)、その値を貼り付けてください。次に、カーソルを 3 つ目と 4 つ目の数字の間に挿入してハイフンを入力し、Enter キーを押して値を確定します。 同様に、5 つ目と 6 つ目の数字の間にもハイフンを挿入してください。Enter キーを押して、ステップ 2 に進んでください。

 

ステップ 2: 氏名を Rothschiller, Chad B (<名前>, <姓> <ミドルネームのイニシャル>) のように書式化する

First Name 列と DOB 列との間に新たに列を挿入して、この列に Name という見出しを付けます。

  1. 新しい Name 列に全員の名前を表示するのに十分な幅を持たせます (162 ピクセルあれば十分ですが、これは必須ではなく、単に見やすくするためです)。
  2. セル F2 に「Freehafer, Nancy D」と入力してください。
  3. 次のセルに Cencini に対する「C」を入力すると、Excel のフラッシュ フィルによって、その後のデータが提案されます。
  4. その結果がよければ (完全ではなくとも、一旦受け入れます)、Enter キーを押して値を受け入れます。

    この場合、ミドル ネームあるいはイニシャルの列がないと、フラッシュ フィルにはユーザーがしようとしていることの確証が得られません。そのため、私が用意したこの例ではミドル イニシャルの列を使用しています。
    次に、修正と呼ぶ例を示します。誤っている箇所について、私がしたいことをフラッシュ フィルに教えるのです。
  5. セル F5 (最初の空白セル) に移動します。
  6. 「Sergienko, Mariya」と入力して、Enter キーを押します。
    このセルの編集中に、フラッシュ フィルの枠が再び表示されることに注目してください。

Enter キーを押すと、フラッシュ フィルによって新たな例が追加され、新しい規則が作成されます。そしてフラッシュフィルの範囲全体にそのルールが再度適用されます (ユーザーが入力したセルは除きます)。ミドル ネーム/イニシャルがない他の列 (下から 2 列目の「Guissani, Laura」) もこの適用対象になっています。

元の名前の値に含まれていた余分なスペースが削除されたことにも注目してください。フラッシュ フィルでは余分なスペースは無視し、結果をクリーンアップします。

 

ステップ 3: 生年月日を 19610222 から 2/22/1961 (一般的な表現では YYYYMMDD から M/D/YYYY) に変換する

DOB (生年月日) 列を日付の書式に変換して読みやすくし、Excel がこの値を日付として認識できるようにします。DOB (生年月日)という新しい列を挿入します。

  1. これからする作業がわかりやすくなるよう、この列に十分な幅を持たせます (75 ピクセルが適切です)。
  2. 新しい日付を適用する範囲全体を選択して、これらの書式を「mm/dd/yyyy」とします ([セルの書式設定]、[表示形式]、[ユーザー定義] を選択して、[種類] のテキスト ボックスに「mm/dd/yyyy」と入力します)。
  3. 先頭から日付を 3 つ入力します。
  4. フラッシュ フィルがまだ動作していないことに注目してください。私たちは、数値データに対してはフラッシュ フィルが自動的に起動されないようにしました。つまり、数値を扱う処理では 10 種類の文字しかないため、意味をなさないランダムなパターンが現れやすいためです。ここでは、新たな例としてリボン ボタンを使用します。
  5. [データ] タブを開き、[フラッシュ フィル] ボタンをクリックします。

結果は次のようになります。

これで日付に変換できました。日付の書式は [表示形式] ダイアログボックスを再度使用すれば、「m/d/yyyy」などと自由に変更できます。

日付 (および数値一般) を処理するには、このような余分なステップがいくつかあることに注意してください。Excel で使用しているアルゴリズム エンジンでは、すべてをテキストとして処理します。そのため、Excel では数値に関係する特別な規則 (たとえば、数値として扱うときには先頭のゼロは省略できるが、郵便番号や ID などとしてを扱う際にはゼロは省略できないなど) を理解できません。もし私がこの新しい DOB (生年月日) 列に対して、最初に月として 2 桁の数字を、また日として 2 桁の数字を割り当てていなかった場合には、このアルゴリズムでは私が「02」を「2」(つまり「2 月」) と変換したのを見て、月の数値の 2 番目の桁だけを必要としているのだと考えるでしょう。そして、このアルゴリズムは「12」も「2」と変換します。これは、先頭のゼロは省略できるが、先頭の他の数値は省略できないという数値に関する特別な規則が理解できないためです。これは将来的には確実に改善を進めて、よりよい結果をより迅速に出せるようにしていく分野です。

 

ステップ 4: 電話番号を (206) 555-1212 という書式に変換する

Home Phone 列の後に新しい列を挿入して、この見出しを Phone とします (この列は 96 ピクセルの幅にするとフラッシュフィルの提案が見やすくなります)。

  1. それでは、「(206) 695-9457」と入力してください。
  2. 2 列目に対して「(」と入力すると、入力内容の提案が始まります (ここで私たちは再度数字を扱っていますが、この場合はテキストが混在しているため、Excel はこれを数値ではなくテキストとして扱います。よって、提案が自動的に行われるのです)。
  3. Enter キーを押して提案を受け入れれば完了です。

ステップ 5: 郵便番号の先頭のゼロを戻す (01001 など)

これは日付の場合のステップととてもよく似ています。対象の列を設定したら (これを Zip という見出しにします)、全体の範囲を選択して、これを文字列として書式設定します。これは Excel がこの数字を数値として扱わないようにして、先頭のゼロが再度省略されないようにするためです。

1. Zip 列の範囲を [文字列] として書式設定します。

2. 郵便番号を入力し、提案される内容に注目してください。

提示される内容には値が誤っているものがありますが、とにかくこれを受け入れます。その後、正しくないものを修正します。フラッシュフィルは、この値を「ハードコーディングされた」先頭のゼロの代わりとして考えたものと思われます。このデータのどこかからこの数字が抽出されてきたのでしょう。

3. 結果を受け入れた後、修正が必要なセルを選択して (この場合、セル S5)、これを正しい値に修正します。

4. Enter キーを押します。

1 箇所を修正すると、他の値も同様に更新されることに注目してください。

元のデータの他の列について

まずフラッシュ フィルを起動させる方法を理解して、望む結果を得ることの方がわかりやすいと考え、ここまで他の列については詳細なステップを説明しませんでした。

  • E-Mail Address 列の値は小文字に変換できます。新しい列を挿入して、その値の小文字のパターンを入力し始めると、フラッシュ フィルが他のデータの内容を提案します。
  • Address 列からは不要なスペースを削除できます。新しい列にスペースなしの値を入力し始めるだけで、フラッシュ フィルが残りの変換を実行します。
  • State 列の値は、新しい列に 2 文字の大文字の値を入力すると、大文字に変換されます。

 

フラッシュ フィルの使用についてのヒント

皆さんは、今回私が行ったデータの操作方法に、いくつかのパターンがあったことにお気づきになったかと思います。ここでは、フラッシュフィルから最大の効果を得るために知っておくべき知識について説明します。

  • パターンを認識するフラッシュ フィルのアルゴリズムでは、数値または数字をテキストと異なる方式で処理しません。
    • 一番左側にあるゼロでない数字の、さらに左側にあるゼロは重要ではなく、省略できるものであること (しかし郵便番号などの場合は省略してはならないこと) を理解できません。
    • 1 は「1 月」に、2 は「2 月」に対応する場合があることを理解できません。
    • このようにフラッシュ フィルは数値や日付も処理できるものの、場合によっては、望む結果を得るための方法を明確に考えて示す必要があります。
  • フラッシュ フィルは、元のデータと入力した出力例との関係が明確で、あいまいでない場合に、より正しく機能します。たとえば、元のデータが次のような場合を考えます。

この場合は、Suffix の値の例を 1 つ目に指定 (「AB」) するよりも、2 つ目か 3 つ目の行のデータに指定 (「EF」または「YZ」) した方が正しく機能します。この理由は、この例の「AB」という値は、元のデータのハイフンの前の AB と後の AB のどちらを示すのかが不明確であるためです。

  • 自動的に提案されるようにするには、関連するデータのすぐ隣のセルを編集する必要があります。
    • 編集するセルと、それに関連するデータとの間に 1 つ以上の空白の列を置くことはできません。
    • 疑問に思われる場合は、セルをクリックして、Ctrl + A キーを押してください (連続する範囲が選択されます)。その結果としてシート全体が表示される場合は、Excel がそのセルが何らかのデータの隣にあると認識していないことを意味します。関係するデータの集合が選択された場合は、そのセルは適切な位置にあることを意味します。
  • 自動的に提案されるようにするには、2 箇所を連続して編集する必要があります。1 箇所の編集のすぐ後にもう 1 箇所を編集します。「2 箇所を連続して編集」とは、1 つ目の編集と 2 つ目の編集の間に他の操作 (他のシートに切り替えるなど) を実行しないことを意味します。
  • データ テーブルの最上部に見出しがある場合、Excel はこれを見出しとして認識するため、フラッシュ フィルの規則を作成するときにこれらは除外されます。
    • 見出しがあることを Excel に認識させるのに私が推奨するのは、データの範囲内のセルを有効にして、Ctrl + T キーを押すことです (テーブルの作成)。表示されるダイアログに、[先頭行をテーブルの見出しとして使用する] というチェックボックスがあります。このボックスをチェックすると、Excel の自動見出し検出ロジックによって、見出しがあると見なされます。このような設定を行わないと、Excel はヘッダーがあることを認識しません。
    • Excel にヘッダーがあることを認識させる一番簡単な方法は、対象とする範囲をテーブルに変換することです (Ctrl + T キーを押した後にダイアログで [OK] をクリックするだけです)。

最後までお読みくださりありがとうございました。またコメントをお待ちしています。フラッシュフィルが正しく機能しないパターンが見つかった場合には、是非私たちにお知らせください。私たちは常に改善の機会を求めており、皆さんからの価値あるフィードバックを歓迎します。

添付資料: DataCleaningWithExcel2013.zip

Comments (0)

Skip to main content