文字列ごとの出現回数をカウントして頻出順に並び替える

最近ようやく Office 2016 から Microsoft 365 に切り替えた。そして、スピルが使えるようになった。素晴らしい機能だと思った。

例えば、トンボを見つける度に、その種類を A 列にメモしてゆくとする。そして、種類ごとに頻出順で並び替えたリストを作成したいとする。

• スピルを利用できない場合、COUNTIF 関数でカウントした後、「重複の削除」を実施する必要がある。
• スピルを利用できる場合、SORTBY 関数と UNIQUE 関数 と COUNTIF 関数を組み合わせることで、「重複の削除」が不要になる。

=LET(
    範囲, A:A,
    配列, UNIQUE(範囲),
    基準配列, COUNTIF(範囲, 配列),
    SORTBY(配列, 基準配列, -1)
)

トンボの種類を追記していっても自動的に頻出順が変わる。つまり、VBA マクロを作成せずとも、スピルを利用して多くの作業が自動化できるようになった。

TOCOL 関数を用いて、空白セルを除外することで０を消すことが出来る。

=LET(
  範囲, A:A,
  種類, UNIQUE(TOCOL(範囲, 1)),
  数, COUNTIF(範囲, 種類),
  SORTBY(種類, 数, -1)
)

HSTACK 関数を用いて、発見数も列記する。（分かりやすさのために列指定にしていたが、負荷を減らすために範囲指定に変更する）

=LET(
    範囲, A1:A999,
    種類, UNIQUE(TOCOL(範囲, 1)),
    数, COUNTIF(範囲, 種類),
    SORT(HSTACK(種類, 数), 2, -1)
)

COUNTIF 関数の第一引数に配列が使えないため、範囲を利用することに注意する。

以上