Excel でユニークな件数を得る
数値が格納されている場合、検索すれば統計関数を利用した式がすぐに出てくるが、文字列の例がないので自作したメモ
{=SUM(IF(MATCH([範囲],[範囲],0)=ROW([範囲])-ROW(INDEX([範囲],1))+1,1,0))}
縦一列の範囲用なので、横一列の場合は ROW() のかわりに COLUMN() を使用し、矩形領域の場合には IF() の中で COLUMN(), ROW() を両方比較するようにする必要がある。
- 対象セルと同じ値を、対象範囲の先頭から探す (IF 左値)
- 対象セルの行番号から、対象先頭セルの行番号を引くことで、対象セルの相対位置を探す (IF 右値)
- この値が一致するレコードは、先頭から見て最初に発見されたレコードであるため 1 とする (IF 真値)
- この値が一致しないレコードは、先頭から見て最初に発見されたレコードであるため 0 とする (IF 偽値)
- 合計を得ることで、最初に発見されたレコードの数＝ユニークな件数が得られる
| 範囲 | 右値 | 左値 | 真偽 |
|---|---|---|---|
| あ | 1 | 1 | 1 |
| え | 2 | 2 | 1 |
| い | 3 | 3 | 1 |
| う | 4 | 4 | 1 |
| え | 5 | 2 | 0 |
| お | 6 | 6 | 1 |
| あ | 7 | 1 | 0 |
| お | 8 | 6 | 0 |
右値は、ROW() 関数を使って作った範囲配列のインデックス…つまり、右値は行番号です。Excel 上で１行目から開始しているなら、右値は ROW([範囲]) だけでも良いのですが、任意の範囲に使える式にするために多少の加工があります。
左値は、検索結果ですので重複しているデータがある場合は、もっとも若い行番号が入ることになります。そして、左値と右値が同じであるものを数えているので、結果としてユニークなデータ件数が手に入ります。
