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でのデータ集計が非常に簡単になります。ぜひ試してみてください。
コメント