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

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

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

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

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

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

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

このようなデータを並べ替えたり分析したりすると、誤った結果が得られます。

次の例を考えてみましょう-

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

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

ご覧のとおり、データ全体が1つの列にあります。データ分析を実行するには、このデータを構造化する必要があります。ただし、最初にデータをクリーンアップする必要があります。

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

S.No.	機能と説明
1.1。	CLEAN 印刷できない文字をすべてテキストから削除します
2.2。	TRIM テキストからスペースを削除します

セルC3〜C11を選択します。
= TRIM（CLEAN（B3））と入力し、CTRL + Enterを押します。

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

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

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

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

S.No.	機能と説明
1.1。	EXACT 2つのテキスト値が同一であるかどうかを確認します
2.2。	FIND あるテキスト値を別のテキスト値から検索します（大文字と小文字を区別）
3.3。	SEARCH あるテキスト値を別のテキスト値から検索します（大文字と小文字は区別されません）

テキストからのデータ値の抽出

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

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

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

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

あなたは使用することができます Convert Text to Columns Wizard フィールドが-の場合、データ値をExcel列に抽出します

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

上記の例では、フィールドは文字「|」で区切られています。したがって、あなたは使用することができますConvert Text to Columns ウィザード。

データを選択します。
値をコピーして同じ場所に貼り付けます。そうでなければ、Convert Text to Columns データ自体ではなく関数を入力として受け取ります。

データを選択します。
クリック Text to Columns の中に Data Tools 下のグループ Data リボンのタブ。

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

[区切り]を選択します。
[次へ]をクリックします。

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

下 Delimiters、選択する Other。
隣のボックスに Other、文字を入力します |
クリック Next。

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

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

にとって Destination、セルD3を選択します。
クリックできます Advanced、およびセット Decimal Separator そして Thousands Separator の中に Advanced Text Import Settings 表示されるダイアログボックス。
クリック Finish。

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

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

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

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

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

S.No.	機能と説明
1.1。	LEFT テキスト値から左端の文字を返します
2.2。	RIGHT テキスト値から右端の文字を返します
3.3。	MID 指定した位置から始まるテキスト文字列から特定の文字数を返します
4.4。	LEN テキスト文字列の文字数を返します

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

上記の例では、

最初に残されたすべての文字| ProductIDという名前を付けます。
2番目までのすべての文字| 価格という名前を付けます。
最初の|の間にあるすべての文字そして2番目| Product_Descriptionという名前を付けます。
それぞれ| 前後にスペースがあります。

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

最初の位置を見つける| -First | Position
- FIND機能を利用できます
2番目の位置を見つける| -Second | Position
- FIND機能を再度使用できます
始まり（First | Position – 2）テキストの文字はProductIDを示します
- LEFT関数を使用できます
（（First | Position + 2）から（Second | Position -2）テキストの文字はProduct_Descriptionを与えます
- MID機能を利用できます
（（Second | Position + 2）テキストの最後の文字に価格を与える
- RIGHT関数を使用できます

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

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

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

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

Step 1 −データの横にProductID、Product_Description、Priceの3つの列を作成します。

Step 2 − B3からC3、D3、およびE3の値をコピーして貼り付けます。

Step 3 −セルC3を選択し、をクリックします Flash Fill の中に Data Tools 上のグループ Dataタブ。ProductIDのすべての値が入力されます。

Step 4−Product_DescriptionとPriceについて上記の手順を繰り返します。データが入力されます。

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

Excelには、テキストを含むデータの書式設定に使用できるいくつかの組み込みテキスト関数があります。これらには以下が含まれます-

Functions that format the Text as per your need −

S.No.	機能と説明
1.1。	LOWER テキストを小文字に変換します

S.No.	機能と説明
1.1。	UPPER テキストを大文字に変換します
2.2。	PROPER テキスト値の各単語の最初の文字を大文字にします

Functions that convert and/or format the Numbers as Text −

S.No.	機能と説明
1.1。	DOLLAR $（ドル）通貨形式を使用して、数値をテキストに変換します
2.2。	FIXED 数値を固定小数点以下のテキストとしてフォーマットします
3.3。	TEXT 数値をフォーマットしてテキストに変換します

Functions that convert the Text to Numbers −

S.No.	機能と説明
1.1。	VALUE テキスト引数を数値に変換します

Executing Data Operations with the Text Functions

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

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

S.No.	機能と説明
1.1。	REPLACE テキスト内の文字を置き換えます
2.2。	SUBSTITUTE テキスト文字列内の古いテキストを新しいテキストに置き換えます
3.3。	CONCATENATE 複数のテキストアイテムを1つのテキストアイテムに結合します
4.4。	CONCAT 複数の範囲や文字列からのテキストを結合しますが、区切り文字やIgnoreEmpty引数は提供しません。
5.5。	TEXTJOIN 複数の範囲や文字列からのテキストを結合し、結合される各テキスト値の間に指定する区切り文字を含めます。区切り文字が空のテキスト文字列の場合、この関数は範囲を効果的に連結します。
6.6。	REPT 指定された回数だけテキストを繰り返します