Excel-data-analysis-cleaning-data-with-text-functions

提供：Dev Guides
移動先:案内、検索

テキスト関数を使用したデータのクリーニング

さまざまなソースから取得したデータの多くは、すぐに分析できる形式ではありません。 この章では、分析用のテキスト形式のデータを準備する方法を理解します。

最初に、データを消去する必要があります。 データクリーニングには、テキストから不要な文字を削除することが含まれます。 次に、さらなる分析に必要な形式でデータを構造化する必要があります。 あなたは同じことをすることができます-

  • テキスト関数を使用して必要なテキストパターンを検索します。
  • テキストからデータ値を抽出します。
  • テキスト関数を使用してデータをフォーマットします。
  • テキスト関数を使用してデータ操作を実行します。

テキストから不要な文字を削除する

別のアプリケーションからデータをインポートすると、印刷できない文字や余分なスペースが含まれる場合があります。 余分なスペースは-

  • 先行スペース、および/または *単語間の余分なスペース。

そのようなデータをソートまたは分析すると、誤った結果が得られます。

次の例を考慮してください-

製品データ

これは、製品ID、製品説明、価格を含む製品情報で取得した生データです。 文字「|」は各行のフィールドを区切ります。

このデータをExcelワークシートにインポートすると、次のようになります-

データのインポート

ご覧のとおり、データ全体が単一の列にあります。 データ分析を実行するには、このデータを構造化する必要があります。 ただし、最初はデータを消去する必要があります。

データに存在する可能性のある印刷できない文字と余分なスペースを削除する必要があります。 この目的には、CLEAN関数とTRIM関数を使用できます。

S.No. Function & Description
1.
  • CLEAN*

テキストからすべての印刷不能文字を削除します

2.

TRIM

テキストからスペースを削除します

  • セルC3 – C11を選択します。 *「= TRIM（CLEAN（B3））」と入力し、CTRL＆plus;を押します入る。

数式はセルC3〜C11に入力されます。

満たされた式

結果は以下のようになります-

式が満たされた結果

テキスト関数を使用して必要なテキストパターンを見つける

データを構造化するには、データ値を抽出できる特定のテキストパターンマッチングを行う必要がある場合があります。 この目的に役立つテキスト関数の一部は次のとおりです-

S.No. Function & Description
1.
  • EXACT*

2つのテキスト値が同一であるかどうかを確認します

2.

FIND

あるテキスト値を別のテキスト値内で検索します（大文字と小文字が区別されます）

3.

SEARCH

1つのテキスト値を別のテキスト値内で検索します（大文字と小文字は区別されません）

テキストからデータ値を抽出する

同じ構造を作成するには、テキストから必要なデータを抽出する必要があります。 上記の例では、たとえば、ProductID、Product_Description、Priceの3つの列にデータを配置する必要があります。

次のいずれかの方法でデータを抽出できます-

  • テキストを列に変換ウィザードを使用したデータ値の抽出
  • テキスト関数を使用したデータ値の抽出
  • フラッシュフィルを使用したデータ値の抽出

テキストを列に変換ウィザードを使用したデータ値の抽出

フィールドがテキストの場合は、*テキストを列に変換ウィザード*を使用して、Excel列にデータ値を抽出できます-

  • 文字で区切られている、または
  • 各フィールドの間にスペースを入れて列に整列します。

上記の例では、フィールドは文字「|」で区切られています。 したがって、*テキストを列に変換*ウィザードを使用できます。

  • データを選択します。
  • 同じ場所に値をコピーして貼り付けます。 それ以外の場合、*テキストを列に変換*は、データ自体ではなく関数を入力として使用します。

テキストを列に変換

  • データを選択します。
  • リボンの[データ]タブの下にある[データツール]グループの[列へのテキスト]をクリックします。
  • ステップ1 *-テキストを列に変換ウィザード-ステップ1/3が表示されます。
  • 区切り記号を選択します。
  • Nextをクリックしてください。

テキストを列に変換Step1

  • ステップ2 *-テキストを列に変換ウィザード-ステップ2/3が表示されます。
  • Delimiters で、 Other を選択します。
  • その他*の横のボックスに、文字 *| を入力します
  • [次へ]をクリックします。

テキストを列に変換するステップ2

  • ステップ3 *-テキストを列に変換ウィザード-ステップ3/3が表示されます。

この画面では、ウィザードでデータの各列を選択し、その列の形式を設定できます。

  • Destination で、セルD3を選択します。
  • [詳細設定]をクリックして、表示される[詳細なテキストのインポート設定]ダイアログボックスで[10進数の区切り記号]と[数千の区切り記号]を設定できます。
  • *完了*をクリックします。

テキストを列に変換Step3

列に変換されるデータは、D、E、Fの3つの列に表示されます。 

*列ヘッダーにProductID、Product_Description、およびPriceという名前を付けます。

名前列ヘッダー

テキスト関数を使用したデータ値の抽出

データ内のフィールドが文字で区切られておらず、各フィールド間にスペースがある列に配置されていない場合、テキスト関数を使用してデータ値を抽出できます。 フィールドが区切られている場合でも、テキスト関数を使用してデータを抽出できます。

この目的に役立つテキスト関数のいくつかは次のとおりです-

S.No. Function & Description
1.
  • LEFT*

テキスト値から左端の文字を返します

2.

RIGHT

テキスト値から右端の文字を返します

3.

MID

指定した位置から始まるテキスト文字列から特定の文字数を返します

4.

LEN

テキスト文字列の文字数を返します

また、必要なデータ値を抽出するために、手元のデータに応じてこれらのテキスト関数を2つ以上組み合わせることができます。 たとえば、LEFT、RIGHTおよびVALUE関数の組み合わせを使用するか、FIND、LEFT、LENおよびMID関数の組み合わせを使用します。

上記の例では、

  • 最初の残りのすべての文字| ProductIDという名前を付けます。
  • 2番目の右にあるすべての文字| Priceという名前を付けます。
  • 最初の|の間にあるすべての文字と2番目| Product_Descriptionという名前を付けます。
  • 各|前後にスペースがあります。

この情報を観察すると、次の手順でデータ値を抽出できます-

  • 最初の位置を見つける| -最初|ポジション
  • FIND関数を使用できます

2番目の位置を見つける| -* 2番目|ポジション*

  • FIND機能を再び使用できます

開始（最初|位置 – 2）テキストの文字はProductIDを与えます

  • LEFT関数を使用できます

（最初|位置＆plus; 2）から（第二|位置-2）テキストの文字はProduct_Descriptionを与えます

  • MID機能を使用できます

（秒|位置＆plus; 2）テキストの終了文字で価格を指定 

*RIGHT関数を使用できます

データ値の抽出

結果は以下のようになります-

データ値の抽出結果

価格列の値はテキスト値であることがわかります。 これらの値で計算を実行するには、対応するセルをフォーマットする必要があります。 テキストの書式設定を理解するには、以下のセクションをご覧ください。

フラッシュフィルを使用したデータ値の抽出

Excel* Flash Fill *を使用することは、テキストからデータ値を抽出する別の方法です。 ただし、これは、Excelがデータ内のパターンを検出できる場合にのみ機能します。

  • ステップ1 *-データの横にProductID、Product_DescriptionおよびPriceの3つの列を作成します。

列の作成

  • ステップ2 *-B3からC3、D3、およびE3の値をコピーして貼り付けます。

値の貼り付け

ステップ3 *-セルC3を選択し、 *Data タブの Data Tools グループで Flash Fill をクリックします。 ProductIDのすべての値が入力されます。

Flash Fill

  • ステップ4 *-Product_DescriptionとPriceに対して上記のステップを繰り返します。 データが入力されます。

繰り返し手順

テキスト関数を使用したデータのフォーマット

Excelには、テキストを含むデータのフォーマットに使用できるいくつかの組み込みテキスト関数があります。 これらには-

必要に応じてテキストをフォーマットする機能-

S.No. Function & Description
1.

LOWER

テキストを小文字に変換します

S.No. Function & Description
1.

UPPER

テキストを大文字に変換します

2.

PROPER

テキスト値の各単語の最初の文字を大文字にします

数値をテキストとして変換および/またはフォーマットする関数-

S.No. Function & Description
1.

DOLLAR

$（ドル）通貨形式を使用して、数値をテキストに変換します

2.

FIXED

数値を固定小数点数のテキストとしてフォーマットします

3.

TEXT

数値をフォーマットし、テキストに変換します

テキストを数値に変換する関数-

S.No. Function & Description
1.

VALUE

テキスト引数を数値に変換します

  • テキスト関数を使用したデータ操作の実行 *

データに対して特定のテキスト操作を実行する必要がある場合があります。 たとえば、組織の従業員のログインIDが形式の変更に基づいて新しい形式に変更された場合、テキストの置換が必要になる場合があります。

次のテキスト関数は、テキストを含むデータに対してテキスト操作を実行するのに役立ちます-

S.No. Function & Description
1.
  • REPLACE*

テキスト内の文字を置き換えます

2.

SUBSTITUTE

テキスト文字列内の古いテキストを新しいテキストに置き換えます

3.

CONCATENATE

複数のテキスト項目を1つのテキスト項目に結合します

4.

CONCAT

複数の範囲や文字列のテキストを結合しますが、区切り文字またはIgnoreEmpty引数を提供しません。

5.

TEXTJOIN

複数の範囲や文字列からテキストを結合し、結合する各テキスト値の間に指定する区切り文字を含めます。 区切り文字が空のテキスト文字列の場合、この関数は範囲を効果的に連結します。

6.

REPT

テキストを指定された回数繰り返します

「https://www.finddevguides.com/index.php?title=Excel-data-analysis-cleaning-data-with-text-functions&oldid=15655」から取得
Powered by MediaWiki