Excel関数で重複と空白を除いてデータの件数を求める方法

Excel関数で重複と空白を除いてデータの件数を求める方法

今までは画像①にある通り、A列にある番号の重複を除いたデータ件数を参照していました。
※関数は {=SUMPRODUCT(1/COUNTIFS(A2:A12,A2:A12))}　を使用しています。

今回質問したいのは画像②のようにA列にところどころ空白がある場合、空白を無視して(空白以外)の重複を除いたデータ件数を出す方法が知りたいです。
また、範囲もA2～入力がある最終行まで、またはA2～A最終行までとしたいと思っています。

知識不足や説明が分かり辛い点が多々あるとは思いますが、皆様のお知恵を拝借出来たらと思います。
よろしくお願いします。

質問者からの補足コメント

• 分かり易くご回答頂き、ありがとうございます。
  出来ればですが、B列や他のセルを使用せずに完結出来たらと思いますが、
  やはり難しいのでしょうか・・・？

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/06/15 16:52

No.2 ベストアンサー 回答者： fujillin 回答日時： 2021/06/15 16:54

こんにちは

＞範囲もA2～入力がある最終行まで、またはA2～A最終行までとしたいと思っています。
関数で最終行を求めることはできます（後述）けれど、これを組み込めばその分だけ式が長くなりやすいです。
範囲を適当に（広めに）設定しておく方が式が複雑にならずに済みます。

＞空白を無視して(空白以外)の重複を除いたデータ件数を出す方法が知りたいです。
UNIQUE関数等を使える環境であれば、これを利用することで簡単に求められるはずです。
私の環境では利用できないので、以下は、ローテクによる多少面倒な方法です。

空白が本当の空白（無入力の状態）だけであるなら、以下の式で可能です。
=SUMPRODUCT((COUNTIF(OFFSET($A$2,,,ROW($A$1:$A$99)),$A$2:$A$100)=1)*1)

関数式で空白文字列を返しているようなものを含んでいる場合は、上式ではそれも（未入力でないとして）カウントしてしまいますので、そのような空白も除きたいような場合は、
=SUMPRODUCT((COUNTIF(OFFSET($A$2,,,ROW($A$1:$A$99)),$A$2:$A$100)=1)*($A$2:$A$100<>""))

※ 上式はセル範囲をA2:A100に設定してありますが、
＞範囲もA2～入力がある最終行まで～～
をその文言のまま実現なさりたいのなら、A列の最終行は
=AGGREGATE(14,6,ROW(A:A)/(A:A<>""),1)
で求めることができますので、これを式中に組み込むようにすれば実現できるでしょう。

この回答へのお礼

丁寧かつ分かり易くご説明頂き、ありがとうございます。
=SUMPRODUCT((COUNTIF(OFFSET($A$2,,,ROW($A$1:$A$99)),$A$2:$A$100)=1)*1)
こちらに=AGGREGATE(14,6,ROW(A:A)/(A:A<>""),1)を=INDIRECTで組み込むことで私の要望通りの処理が出来ました。
とても勉強になりました。本当にありがとうございます。

その他の方々も早急にご回答くださり、大変感謝しております。
本件は私の要望通りの式を教えて頂いたfujilin様をベストアンサーとさせて頂きます。

お礼日時：2021/06/16 11:10

No.5 回答者： mike_g 回答日時： 2021/06/16 10:57

あの～、その～、余計なお世話のお許しを。

m(_._)m

どなたからの受け売りで貴方は単に示されたのかも知れませんが、
=SUM(1/COUNTIFS(A2:A12,A2:A12))
の場合は、実体が
{=SUM(1/COUNTIFS(A2:A12,A2:A12))｝
というCSE数式になるように入力する必要がありますが、SUM の代わりに
SUMPRODUCT を使うなら、
=SUMPRODUCT(1/COUNTIFS(A2:A12,A2:A12))
のマンマ(CSE操作不要)でＯＫですよ。なぜなら、SUMPRODUCT 自体が配列数式として処理する関数だから。

No.4 回答者： banzaiA 回答日時： 2021/06/15 20:53

>出来ればですが、B列や他のセルを使用せずに完結出来たらと思いますが

そんな都合のよいことを･･･。

ではバージョンを365 にしましょう。

=COUNT(UNIQUE(A:A,FALSE))
これで、(空白以外)の重複を除いたデータ件数が出せます。

No.3 回答者： 銀鱗 回答日時： 2021/06/15 17:15

んー。

これはイレギュラーなやり方
　1/COUNTIFS(A2:A12,A2:A12)
をしている計算なので仕方のない事です。
空白は必ず「0」と値が返るので回避のしようがない。

素直に作業列を設けて計算しましょう。

No.1 回答者： tanapyondai 回答日時： 2021/06/15 16:49

この方法が嫌じゃなければ。

B列に、Aセルが空白じゃなかったら1を入れる。
B列を集計する(1の合計＝件数)

例えば、
B2に、「=if(A2="","",1)」