☰

リストを分類するにはどうすればいいですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007 microsoft-excel-2013
リストを分類するにはどうすればいいですか?

特定の文字列を一致させてリストを分類/並べ替えたいと考えています。以前は解決策が見つからなかったため、この方法の方が簡単だと思います。

例のリスト:

[A]
The apple is the pomaceous fruit of the apple tree
Apples grow on deciduous trees which are large if grown from seed
Apples are an important ingredient in many desserts, such as apple pie
Puréed apples are generally known as apple sauce
A banana is an edible fruit produced by several kinds of large plants
Worldwide, there is no sharp distinction between "bananas" and "plantains"
The term "banana" is also used as the common name for the plants
Orange is the colour of saffron, pumpkins and apricots
The colour orange is named after the appearance of the ripe orange fruit
In ancient Egypt, artists used an orange mineral pigment called realgar
Apple, orange and banana smoothie
Eating an orange and banana exceed allowable sugar intake
Kale or borecole (Brassica oleracea Acephala Group) is a vegetable
Until the end of the Middle Ages, kale was one of the most common green vegetables

検索される文字列とその分類方法 (大文字と小文字は区別されません):

Apple = Apple
Apple Pie = Dessert
Banana = Banana
Orange = Orange
(anything not categorized) = Vegetables
(multiple found strings) = Multiple --> if this isn't possible it's fine

リストの横の列には次のような内容が表示されます。

[B]
Apple
Apple
Pie
Apple
Banana
Banana
Banana
Orange
Orange
Orange
Multiple
Multiple
Vegetables
Vegetables

そして、並べ替え/フィルターを使用します。ありがとうございます!

答え1

私は Power Query アドインを使用してこの問題を解決します。そこに到達するにはいくつかの手順が必要ですが、コードや入力データ構造の変更は必要ありません。

表示またはダウンロードできるプロトタイプを作成しました。これは、One Drive の「Power Query デモ - キーワードのリストの検索と分類」です。

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

基本的に、私のテクニックは、カテゴリのリストをロードしてダミーのマージ キーを割り当てるための予備クエリを作成し、ダミーのマージ キーを使用して検索するテキストに対してそれをマージすることでした。これにより、各入力行 x 各カテゴリの行が生成されます。次に、Text.Contains 関数を使用してカテゴリを計算し、最後に Group By を使用して元の行セットに戻しました。

その時点で、フィルタリングやピボット テーブルやピボット グラフの使用による調査に最適な正規化されたテーブルが作成されます。

答え2

動的かつ階層的 (アップルパイがアップルよりも優先される) にしたいという事実は、少し難しいですが、静的にプログラムするのであれば、次のようにすることができます。

Row 1 - Your search text
Row 2 - Your result text

B1=Apple
B2=Apple
B3=If(Len($A2)>LEN(SUBTITUTE(LOWER($A2),LOWER(B$1),"")),B$2,"")
C1=Apple Pie
C2=Deserts
D1=Orange
D2=Orange
Drag B3 across and down

ここで行っているのは、「apple」のインスタンスを何もないものに置き換え、文字数を数えて元の文字数より少ないかどうかを調べることです。通常、これは大文字と小文字を区別する操作ですが、最初に比較するテキストの両方を小文字にしました。これにより、その列の検索テキストにヒットがあった場合、各列に結果テキストが出力されます。

階層を組み込むには、列 B を IF(LEN(C2)>0,"",NORMAL FORMULA) に変更して、列 C にすでに値がある場合に列に APPLE が表示されないようにします。NORMAL FORMULA は、上記の B3 の数式です。

次に、countaを使用してヒット数を測定できます

=IF(COUNTA(B2:D2)=0,"Vegitables",IF(COUNTA(B2:D2)>1,"Multiple",B2&C2&D2))

一致が 0 個の場合は野菜、複数の場合は複数、それ以外の場合は 1 つのフィールドのみが入力されるため、結果を連結して最終的な回答を得ることができます。

また、動的リストを取得してヒット数を計算する式も思いつきました。これは配列関数なので、{}なしで入力し、Enterキーを押す代わりにCtrl+Shift+Enterキーを押します。

{=SUM(--(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($F$1:$F$6),""))>0))}

ただし、これは失敗します。なぜなら、リンゴとパイの両方が含まれているため、「アップルパイ」が含まれるものすべてに対して 2 になる可能性があるからです。ただし、列 F に提供されている動的リストから処理できるため、成功します。

関連情報