COUNTIF 関数の第一引数に配列が使えない

Excel で配列数式を使うようになって、たびたび改善してほしいと思うのは、COUNTIF 関数及び COUNTIFS 関数の第一引数に、範囲は使えるが、配列を使えないことである。

• OK
  • COUNTIF(A1:A5, {“あ”})
  • COUNTIF(A1:A5, {“あ”,”い”})
• ERROR
  • COUNTIF({“あ”,”あ”,”い”,”い”,”い”}, “あ”)
  • COUNTIF({“あ”,”あ”,”い”,”い”,”い”}, {“あ”,”い”})

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

数式１（エラー）

COUNTIF 関数の第一引数に配列を利用できないためエラーになる。

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

数式２

COUNTIF 関数の第一引数を範囲に変更するとエラーは発生しない。

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

しかし、計算量が減ることを期待して TOCOL 関数で空白を除外しているため、出来れば TOCOL 関数を適用した配列を利用したい。

数式３

BYROW（MAP）関数を利用する方法を考える。

=LET(
  範囲, A1:A999,
  全て, TOCOL(範囲, 1),
  種類, UNIQUE(全て),
  数, BYROW(種類, LAMBDA(x, SUM(N(全て=x)))),
  SORT(HSTACK(種類, 数), 2, -1)
)

追記：
𝕏 にて、光希桃さん（mikimomo_as）から COUNT(1/ 論理式) を使う方法と MMULT 関数（行列積）を利用する方法を教えていただいた。

数式４

=LET(
  範囲, A1:A999,
  全て, TOCOL(範囲, 1),
  種類, UNIQUE(全て),
  数, MAP(種類, LAMBDA(x, COUNT(1/(全て=x)))),
  SORT(HSTACK(種類, 数), 2, -1)
)

数式５　

=LET(
  範囲, A1:A999,
  全て, TOCOL(範囲, 1),
  種類, UNIQUE(全て),
  UxN, N(TRANSPOSE(全て) = 種類),
  UxI, SEQUENCE(ROWS(全て), 1, 1, 0),
  数, MMULT(UxN, UxI),
  SORT(HSTACK(種類, 数), 2, -1)
)

MMULT 関数を使う時は、分かりやすいように行列のサイズを変数名にしている。

• U：Unique の頭文字。
• N：Number の頭文字。
• x ：×（\times）の代わり。
• I ：１の代わり。※ 変数名に数字は使えない。

数式５が最も計算が速そうだ。ただし、メモリ不足になるかもしれない。