(コピペ用):範囲から値リストとその出現回数を一度に示すExcelの計算式

Excelで一意の値を取得し並べ替え、出現回数をカウントして表示する計算式

Excelでデータの整理や分析を行う際に、特定の範囲から一意の値を取得し、それを並べ替えて出現回数をカウントすることはよくあります。

つまりアイテムリスト(値リスト)とそれぞれのカウント値のリスト化です。

この記事では、LET関数とスピル機能を使用してこれを簡単に実現する方法を紹介します。※Excel 365用の計算式です。

アイテムリスト生成とそれを数える計算式

コピペ用計算式

アイテムリスト(値リスト)とそれぞれのカウント値のリスト化をするコピペ用計算式は以下となります。

=LET(_Range,配列,HSTACK(SORT(UNIQUE(_Range)),COUNTIF(_Range,SORT(UNIQUE(_Range)))))

この計算式を使用すると、指定した範囲から一意の値を取得し、それを並べ替えて、各値の出現回数を表示することができます。具体的には、次のような表を対象とします。「配列」の部分を「B2:B27」などの範囲に置き換えれば完成します。

アイテムリスト(値リスト)とそれぞれのカウント値のリスト化をするコピペ用計算式

アイテムリスト(値リスト)とそれぞれのカウント値のリスト化をするコピペ用計算式

完成サンプルとテーマ表

サンプルを見てみましょう。以下の表(A1:C27)におけるB2:B27のアイテムリストと出現回数を数える式は

=LET(_Range,B2:B27,HSTACK(SORT(UNIQUE(_Range)),COUNTIF(_Range,SORT(UNIQUE(_Range)))))

になります。

開催日会場来客数
2020/03/05上野840
2020/03/06上野1000
2020/03/12上野760
2020/03/13町田750
2020/03/19町田840
2020/03/20町田560
2020/03/26川崎730
2020/03/27品川590
2020/04/02町田610
2020/04/03川崎980
2020/04/09川崎1080
2020/04/10品川840
2020/04/16上野930
2020/04/17川崎720
2020/04/23町田830
2020/04/24品川760
2020/04/30川崎970
2020/05/01上野710
2020/05/07町田920
2020/05/08品川790
2020/05/14品川1100
2020/05/15品川930
2020/05/21上野730
2020/05/22町田970
2020/05/28町田760
2020/05/29上野730

計算式の構造

基本構造(LET関数は未セット)

重複しないアイテムリスト(値リスト)とそれを数える式は以下のようになります。これは理解のためにコピペ用の式からLET関数を取り外したものです。このままでも使用可能です。

=HSTACK(SORT(UNIQUE(配列)),COUNTIF(配列,SORT(UNIQUE(配列))))

調査対象の「配列」が「B2:B27」ならば

=HSTACK(SORT(UNIQUE(B2:B27)),COUNTIF(B2:B27,SORT(UNIQUE(B2:B27))))

となります。

この計算式の各部分の詳細な説明は次の通りです。

  • UNIQUE(配列): 範囲配列内の一意の値を取得します。UNIQUE関数は、配列内の重複を除外し、一意の要素だけを返します。
  • SORT(UNIQUE(配列)): UNIQUE関数で取得した一意の値を昇順に並べ替えます。SORT関数を使用することで、結果をアルファベット順や数値順に整列することができます。
  • COUNTIF(配列, SORT(UNIQUE(配列))): SORT(UNIQUE(配列))で得られた一意の値に対して、元の配列内でそれぞれの値が何回出現するかをカウントします。COUNTIF関数は、指定された条件に一致するセルの数を返します。
  • HSTACK(SORT(UNIQUE(配列)), COUNTIF(配列, SORT(UNIQUE(配列)))): SORT(UNIQUE(配列))で得られた並べ替えられた一意の値と、その出現回数をHSTACK関数で水平に結合します。これにより、結果が2列の配列として表示されます。

しかしながら実際に使う際は「配列」の指定を一度で済ませるためにLET関数を使うのがよいでしょう。

LET関数を使用時の構造

次の式では「配列」の指定が一度で済むようになります。

この計算式はLET関数を使用しており、計算を分かりやすく整理しています。LET関数を使用すると、計算式内で名前付き変数を定義できるため、読みやすさと再利用性が向上します。

=LET(_Range, B2:B27, HSTACK(SORT(UNIQUE(_Range)), COUNTIF(_Range, SORT(UNIQUE(_Range)))))

以下は、この計算式の各部分の詳細な説明です。

  • LET(_Range, B2:B27, ...): 範囲B2:B27_Rangeとして定義します。
  • UNIQUE(_Range): _Range内の一意の値を取得します。
  • SORT(UNIQUE(_Range)): 一意の値を昇順に並べ替えます。
  • COUNTIF(_Range, SORT(UNIQUE(_Range))): _Range内の各一意の値の出現回数をカウントします。
  • HSTACK(SORT(UNIQUE(_Range)), COUNTIF(_Range, SORT(UNIQUE(_Range)))): 並べ替えられた一意の値とその出現回数を水平に結合します。

実際の使用例

上記の表を基に、範囲B2:B27の会場リストとその出現回数を表示するには、以下の式を使用します。

=LET(_Range, B2:B27, HSTACK(SORT(UNIQUE(_Range)), COUNTIF(_Range, SORT(UNIQUE(_Range)))))

この計算式を入力すると、次のような結果が得られます。

会場出現回数
上野7
川崎5
町田8
品川6

このように、LET関数とスピル機能を活用することで、Excelでのデータ集計が非常に簡単になります。ぜひ試してみてください。

コメント