Excelのキーワードで分類文字列ベースの
いくつかの単語に基づいて文字列を分類することは、データ分析における私の基本的なタスクの1つでした。たとえば、ある調査で、特定のスマートフォンについて何が好きかを人々に尋ねると、同じ回答にさまざまな言葉が含まれます。カメラの場合、写真、ビデオ、自撮り写真などの単語を使用する場合があります。これらはすべてカメラを意味します。したがって、意味のある情報を得るために、前に文を分類することが非常に重要です。
この記事では、キーワードを使用してExcelで分類する方法を学習します。
私たちが話し合った調査の例を見てみましょう。
例:Excelの調査から収集されたデータを分類する
そこで、新しいスマートフォンxyzについて調査しました。 xyz電話の何が好きかをお客様に尋ね、Excelで回答を収集しました。次に、LEDスクリーン、スピーカー、カメラが誰に気に入ったかを知る必要があります。
このために、下の画像に示すように、カテゴリを参照する可能性のあるキーワードのリストを用意しました。理解するために、私はそれを小さく保ちました。
フィードバックの範囲はA2:A9、キーワードの範囲はE2:E10、カテゴリの範囲はF2:F10です。
カテゴリを作成するための一般的な式は次のようになります:
{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(words,sentence)),0))}
中括弧に注意してください。これは配列数式です。 Ctrl + Shift + Enterを使用して入力する必要があります。
カテゴリ:カテゴリのリストを含む範囲です。文またはフィードバックは、これらの値によって分類されます。私たちの場合はF2:F10です。
単語:キーワードまたはフレーズのリストを含む範囲です。
これらは文章で検索されます。これがE2:E10です。
文:分類されるのは文です。これは単一の相対セルです。
これで、Excelでの分類に使用される各変数と関数がわかったので、この例で実装してみましょう。
セルB2にこの数式を入力し、CTRL + SHIFT + ENTERを押します。
{=INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0))}
式をコピーして、各フィードバックのカテゴリを確認します。
単語とカテゴリのリストを修正する必要があります。数式をコピーしても変更されないため、キーワードとカテゴリの絶対的な参照を示しました。数式をコピーするときに文を変更したいのですが、それがA2としての相対参照を使用した理由です。
`link:/ excel-generals-relative-and-absolute-reference-in-excel [ここでExcelを参照]`について理解することができます
これで、LEDスクリーン、スピーカー、カメラを好むユーザーの数に関するレポートを作成できます。
どのように機能しますか?
数式の中核は link:/ logical-formulas-the-isnumber-function-in-excel [ISNUMBER]
( link:/ text-excel-search-function [SEARCH]
($ E $ 2:$ E $ 10、A2))*:
`link:/ counting-countifs-with-or-for-multiple-criteria [here]`について詳しく説明しました。
SEARCH関数は、A2の文のキーワードの各値($ E $ 2:$ E $ 10)を検索します。単語または#VALUE(単語が見つからない場合)の見つかった場所の配列を返します。最後に、この例では9つの要素の配列があります。
\ {#VALUE !; 5; #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。
次に、ISNUMBER関数を使用して、この配列を有用なデータに変換します。それをTRUEとFALSEの配列に変換します。
\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}。
これで、すべてが単純な `link:/ lookup-formulas-use-index-and-match-to-lookup-value [indexmatch]`になります。
link:/ lookup-formulas-excel-match-function [MATCH]
(TRUE、 link:/ logical-formulas-the-isnumber-function-in-excel [ISNUMBER]
( `link:/ text-excel-検索関数[検索] `($ E $ 2:$ E $ 10、A2))、0):
MATCH関数は、結果の配列でTRUEを探し、最初に見つかったTRUEのインデックスを返します。この場合は2です。
INDEX($ F $ 2:$ F $ 10、MATCH(TRUE、ISNUMBER(SEARCH($ E $ 2:$ E $ 10、A2))、0)):次に、INDEX関数はカテゴリの2番目の位置を調べます($ F $ 2: LEDスクリーンである$ F $ 10)。最後に、この式はこのテキストまたはフィードバックをLEDスクリーンとして分類します。
大文字と小文字を区別する:
この関数で大文字と小文字を区別するには、SEARCH関数の代わりにFIND関数を使用します。 FIND関数は、デフォルトで大文字と小文字を区別します。
{=INDEX(Category,MATCH(TRUE,ISNUMBER(FIND(words,sentence)),0))}
弱点:
1.同じ文に2つのキーワードが見つかった場合、リストの最初のキーワードに従って文が分類されます。
別の単語内のテキストをキャプチャします。ある範囲でLADを検索していると仮定します。次に、LADを含む単語がカウントされます。たとえば、ラダーにはLADが含まれているため、ラダーはLADとしてカウントされます。だから注意してください。ベストプラクティスは、データを可能な限り正規化することです。
したがって、これは、Excelでデータを分類する方法についての簡単なチュートリアルでした。私はそれをできるだけ簡単に説明しようとしました。この記事またはExcel関連の記事について疑問がある場合はお知らせください。
ファイルのダウンロード:
:これは、ダッシュボードのもう1つの重要な機能です。これは、特定の条件で値を合計するのに役立ちます。