重複の数を数え、選択したセルの値を返してから重複を削除する数式を作成するにはどうすればよいですか?

Question 1

私の提案は最初の部分（製品の並べ替えリスト）のみに関するものです。

リストを Excel テーブル ( ) に配置し、Table1このリストをとして参照することができますTable1[List]。
列とにはC、D個数を含むユニケートのリストが表示されます。の
数式C2:の=UNIQUE(Table1[List])
数式D2:次の手順として、列と=COUNTIF(Table1[List],C2#)
に示すように、これら 2 つの列を並べ替えることができます。の数式:FG
F2=SORT(HSTACK(C2#,D2#),2,-1)

Answer

私の提案は最初の部分（製品の並べ替えリスト）のみに関するものです。

リストを Excel テーブル ( ) に配置し、Table1このリストをとして参照することができますTable1[List]。
列とにはC、D個数を含むユニケートのリストが表示されます。の
数式C2:の=UNIQUE(Table1[List])
数式D2:次の手順として、列と=COUNTIF(Table1[List],C2#)
に示すように、これら 2 つの列を並べ替えることができます。の数式:FG
F2=SORT(HSTACK(C2#,D2#),2,-1)

Question 2

以下のデータがあり、MS365それを使用している場合は、次の方法を実行できます。

ファイバ	カルシウム	鉄	マグネシウム	亜鉛	銅	マンガン	ヨウ素	セレン	フッ化物
ネイビービーンズ	牛乳	強化シリアル	かぼちゃの種	牛肉	カニ	小麦胚芽	ベークドポテト	ブラジルナッツ	紅茶
レンズ豆	ヨーグルト	小麦ふすまフレーク	アーモンド	かぼちゃの種	ロブスター	サツマイモ	牛乳	ヒマワリの種	エビ
ピント豆	パルメザン	グリッツ	ほうれん草	ヒマワリの種	椎茸	コラードグリーン	エビ	マグロ	レーズン
ヒヨコマメ	スイス	グラノーラ	黒豆	レンズ豆	白いボタン	豆	七面鳥	鮭	ラズベリー
リママメ	カッテージチーズ	シュレッドウィート	鮭	七面鳥	ポルタベラ	松の実	ネイビービーンズ	ロブスター	調理したオートミール
アボカド	ほうれん草	ライスクリーム	ヨーグルト	卵	クレミニス	ヘーゼルナッツ	卵	エビ	ビール
かぼちゃの種	コラードグリーン	牛肉	牛乳	椎茸	サツマイモ	ピーカン		豚ヒレ肉	赤ワイン
ゴマ	カブの葉	牛ひき肉	アボカド		カブの葉	オーツ麦		牛肉	ハードサイダー
アーモンド	ブロッコリー	ビーフテンダーロイン	バナナ		ベークドポテト	リママメ		子羊	グリッツ
ピスタチオ	ブラック·アイド·ピーズ	バッファローサーロイン			ほうれん草	ヒヨコマメ		チキン	黒豆のスープ
ピーカン	ネイビービーンズ	地上のバイソン			ヒマワリの種	ネイビービーンズ		七面鳥	ベークドポテト
ヒマワリの種	鮭	ポーターハウス			かぼちゃの種	レンズ豆		ネイビービーンズ	人参
どんぐりかぼちゃ	どんぐりかぼちゃ	ビーフシチュー肉			ブラジルナッツ	ほうれん草		ピント豆	ほうれん草
ハバードスカッシュ	ロブスター	豚ヒレ肉			クルミ	パイナップル		リママメ	マスクメロン

• -->GROUPBY()にのみ適用可能な関数を使用します。MS365Office Insiders

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     GROUPBY(_SingleCol,_SingleCol,ROWS,,0,-2))

または、BYROW()またはを使用してMMULT()

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, MMULT(N(_Uniq=TOROW(_SingleCol)),SEQUENCE(ROWS(_SingleCol))^0),
     HSTACK(_Uniq,_Counts))

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, BYROW(_Uniq,LAMBDA(x,SUM(--(x=_SingleCol)))),
     HSTACK(_Uniq,_Counts))

上記のすべての式は、各一意のレコードのカウント数を示します。
関数を使用して、TOCOL()重複を除いた複数の範囲のデータを単一の列配列に変換します。
方法1--> 最初の方法では、指定されたフィールドに従って、に設計された関数を使用します。ただしGROUPBY()、その関数はであるため、オプションを有効にしない限り、機能せず、エラーとして表示されます。groupaggregatesortOffice-Insiders#NAME!
方法2--> またLET()、関数から始まるため、読みやすくなり、冗長な範囲/数式の使用が減ります。
最初の方法と同様に、最初にTOCOL()関数を適用し、次にUNIQUE()関数を使用して一意の値を返し、最後にMMULT()関数を使用して、指定された 2 つの配列の行列積であるカウントを返します。
最後に、を使用して、およびHSTACK()関数を使用して返された両方の配列を結合し、目的の出力を返します。UNIQUE()MMULT()
方法3BYROW()-->各項目の一致するカウントを取得する関数を使用することを除いて、上記と同じ手順で開始します。

ただし、これらの方法が複雑すぎると思われる場合は、以下に示す最も単純な方法も使用できます。

まず、TOCOL()&UNIQUE()関数を使用して、空と重複を除いた複数の範囲を1つの配列に返します。そして、セルに配置します。L2

=UNIQUE(TOCOL(A3:J16,1))

最後に、次の関数を入力してカウントを取得します。データ範囲は、A3:A16スーツに応じて変更する必要があることに注意してください。次の部分は、あふれてしまうため、下まで埋める必要はありません。L2上記の式は、およびを使用して返されますTOCOL()。UNIQUE()

=COUNTIFS(A3:J16,L2#)

それでも上記に従うことに抵抗がある場合は、以降POWER QUERYで利用可能なExcel 2010+、およびで利用可能なをWindows Excel 2010+使用できます。Excel 365 (Windows or Mac)

Power Query を使用するには、次の手順に従います。

まずソース範囲をテーブルに変換し、それに応じた名前を付けます。この例では次のように名前を付けました。Table1

Data次に、タブ --> Get & Transform Data--> Get Data--> From Other Sources-->から空のクエリを開きます。Blank Query

上記のようにPower Queryウィンドウが開きます。TabからHome--> Advanced Editor-->そして、表示されているものを削除して次の内容を貼り付けM-Code、を押します。Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

最後に、それを再度インポートするには、Excel-->Close & LoadまたはをクリックしますClose & Load To--> 最初にクリックすると、New Sheet必要な出力を含むが作成され、後者をクリックすると、結果を配置する場所を尋ねるウィンドウが表示されます。

^{注: 数式は重複を削除することはできませんが、特定の条件に従ってセルに配置されたときに値を抽出します。上記に示したように。基本的に、目的の出力を得るにはUNIQUE()、、TOCOL()&COUNTIF()またはCOUNTIFS()関数が必要です。}

Rough Listこの数式を任意の空白セルに入力するだけです。Listの名前は次のようになりますTable。

=LET(
     _Uniq, UNIQUE(List),
     _Output, SORT(HSTACK(_Uniq, COUNTIF(List,_Uniq)),2,-1),
     VSTACK({"Nutrients","Counts"},_Output))

該当する場合は関数を使用すると簡単になりますGROUPBY():

=GROUPBY(List,List,ROWS,,0,-2)

Answer