エクセル 重複を除いた個数を出す関数（空白セルあり）

エクセル2013、任意の範囲の中から重複を除いた個数を出す関数を探しています。
検索するといくつか出てくるのですが、いずれも範囲内に空白セルがないことを前提としているもので、今回関数を使いたい範囲の中には空白セルがあるので困っています。

どうぞよろしくお願い致します。

No.13 回答者： d-q-t-p 回答日時： 2019/12/03 14:46

fujillinさんの式だと 空白セルが混じると「#DIV/0!」になるでしょう。

逆に範囲に「0」が混じると計算結果がおかしくなります。「0」なん
て品目を考える必要があるかどうかは別として不確定要素を残すと碌
なことにならないと思います。

=SUMPRODUCT(1/COUNTIF(範囲,範囲&"")-(COUNTBLANK(範囲)>0)

「できれば配列数式を使いたくない」とのことなので 言葉通りに捉え
るならこれもダメってことになるんでしょうけどね。

因みに Office365なら COUNTAと UNIQUEの組み合わせであっさり
解決します。

No.12 回答者： fujillin 回答日時： 2019/12/02 17:38

No7です。

再度の訂正、面目ないです。（汗）

＞今回関数を使いたい範囲の中には空白セルがあるので困っています。
というのを忘れてました。
空白セルが含まれる場合は種類数から1引けば良いでしょう。

　=SUM(INDEX(1/COUNTIF(範囲,範囲),,))-IF(COUNTBLANK(範囲),1,0)

No.11 回答者： tom04 回答日時： 2019/12/02 17:02

こんにちは！

一案です。
この質問限定の関数（ユーザー定義関数）を作ってみてはどうでしょうか？

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　カーソルが点滅しているところに
↓のコードをコピー＆ペーストし　→　Excel画面に戻り（VBE画面を閉じて）
通常のワークシート関数と同じ使い方をします。

Function kosuu(myRng As Range) '//この行から//
　Dim myDic As Object
　Dim myStr As String
　Dim c As Range
　　Set myDic = CreateObject("Scripting.Dictionary")
　　　For Each c In myRng
　　　　If c <> "" Then
　　　　　myStr = c
　　　　　　If Not myDic.exists(myStr) Then
　　　　　　　myDic.Add myStr, ""
　　　　　　End If
　　　　End If
　　　Next c
　　　　kosuu = UBound(myDic.keys) + 1
　　　　Set myDic = Nothing
End Function '//この行まで//

そして表示したいセルに
=kosuu(A1:A10)

のように数式を入れてみてください。

※　極端に広い範囲（行全体など）は指定しないでください。
すべてのセルをループさせていますので、計算速度が極端に落ちます。

尚、ファイル保存時は「マクロ有効ブック」として保存してください。m(_ _)m

No.10 回答者： d-q-t-p 回答日時： 2019/12/02 16:58

ああ ぐーるぐるさんはテーブル書式にすることが前提なんですね。

失礼しました。

No.9 回答者： d-q-t-p 回答日時： 2019/12/02 16:47

Excel2013なのに関数で計算するのですか？

クエリを作っておけば 更新するだけですけど。

ぐーるぐるさんの方法だと空白セルが混じるとダメでしょうね。
あと場合によって演算誤差が発生します。見るだけなら結構ですが
計算結果を何かに使う場合は注意が必要です。

配列数式を使いたくないとのことですが その拘りには何か意味が
あるのですか？

No.8 回答者： fujillin 回答日時： 2019/12/02 16:45

No7です

訂正です。失礼。　m(__)m

　誤：一部の範囲（A1:A13)
　　　↓　↓　↓
　正：一部の範囲（A8:A13)

No.7 回答者： fujillin 回答日時： 2019/12/02 16:34

こんにちは

ググれば方法は普通に見つかるはずなので、あとは一つの式にまとめれば良いだけです。
　=SUM(INDEX(1/COUNTIF(範囲,範囲),,))
とか。

添付図は、A1:C4を全体のデータとしたときに、その中の一部の範囲（A1:A13)に対して上の式で算出した例です。

No.6 回答者： ぐーるぐる 回答日時： 2019/12/02 16:24

=SUMPRODUCT(1/COUNTIF([品目],[品目]))

じゃあこれで。品目をテーブル化しています。

No.5 回答者： ぐーるぐる 回答日時： 2019/12/02 15:38

ん？じゃあ、どういう風に作業場使うのかな？そこの説明が不足しているね。

例えば、同じところにデータを追加して行って、今搾れているもの以外にもどんどん新しい品目が追加されるのか？
それとも、毎度、新しいファイルなのか？とかは？

この回答へのお礼

何度もありがとうございます。

＞同じところにデータを追加して行って、今搾れているもの以外にもどんどん新しい品目が追加されるのか？
絞るというよりは、データをどんどん追加していく過程で、現在重複を除いた入力件数がどれだけあるかをリアルタイムで知りたいのです。

お礼日時：2019/12/02 16:08

No.4 回答者： ぐーるぐる 回答日時： 2019/12/02 15:05

これじゃダメなの？一番簡単だけど？

この回答へのお礼

ご回答ありがとうございます。
フィルタのこの機能は存じておりますが、事情によりデータを入力すると自動的にデータの種類の個数が表示される関数を探しています。
私が使うぶんにはこれで済むのですが、作るファイルがスキルがまちまちの複数の人が使うことを想定しているため、なるべく手数をかけずに数字が求められる関数を第一希望としております。

お礼日時：2019/12/02 15:32