重複を除いて何人いるか数える式!空白や表記ゆれで数が1つずれる理由も

重複を除いて何人か数える。UNIQUEで数がずれる理由と直し方 関数

「今月来てくれたお客さんって、結局何人?」「取引先って全部で何社あるんだっけ?」…同じ名前が何度も出てくる一覧から、重複を除いた数を出したいとき、ありますよね。

結論から書くと、この式で数えられます!

=COUNTA(UNIQUE(FILTER(範囲,範囲<>"")))

「=COUNTA(UNIQUE(範囲)) だけでいいのでは?」と思いますよね。私も最初はそう思ったんですが、実際に試してみたら、範囲に空白のセルが1つでもあると、答えが1つ多くなりました(^^;

この記事では、来店記録から「お客さんが何人いるか」を数える例で、空白で数がずれるしくみ、担当ごとの人数、1回しか来ていない人の出し方を紹介します。それから、試してみてわかった「表記ゆれ」の意外な扱いについても書いています✨

🎬 実際の Excel 画面で、式を入れるところから動かしている動画もあります(3分くらい)。空白で1つ多くなるところも映しているので、こちらからどうぞ♪

動画で見る:重複を除いて数える式と、空白・表記ゆれの落とし穴(音声あり・章ごとに見られます)

できあがり:15件の来店記録から「6人」

来店記録は15行ありますが、同じ人が何度も来ているので、お客さんは6人です。11/9 の行は、顧客名が入っていません。

来店記録の右にG1の式で顧客は何人?の答え6が表示された完成形
G1 の式で「6」と出た
=COUNTA(UNIQUE(FILTER(C2:C16,C2:C16<>"")))

中でやっていることは3つだけです。

  • FILTER(C2:C16,C2:C16<>""):顧客名の列から、空白じゃないセルだけを取り出す
  • UNIQUE(…):同じ名前を1つにまとめる
  • COUNTA(…):残った名前を数える

空白を除かないと「7」になってしまう理由

FILTER を入れずに =COUNTA(UNIQUE(C2:C16)) にすると、答えは 7 になりました。UNIQUE の中身をそのまま表示してみると、6人の名前のあいだに 0 がひとつ混ざっていたんです。

空白を除かない式では7になり、UNIQUEの中身に0が1つ混ざっている
空白を除かないと 7。UNIQUE の中身に 0 が混ざる

空白のセルも「1種類」としてまとめられて、0 として返ってきます。COUNTA はその 0 も1件と数えるので、1つ多くなるというわけです。

空白がない表なら =COUNTA(UNIQUE(C2:C16)) でも同じ答えになります。でも、あとから未入力の行が入ってくることもあるので、最初から FILTER を付けておくほうが安心です。

担当ごとに、重複を除いた人数を出す

「担当ごとに何人のお客さんを持っているか」も、式2つで出せます。F4 に担当の一覧、G4 に担当ごとの人数です。担当が増えても、行は勝手に増えてくれます♪

F4: =UNIQUE(B2:B16)
G4: =MAP(F4#,LAMBDA(n,COUNTA(UNIQUE(FILTER(C2:C16,(B2:B16=n)*(C2:C16<>""))))))
担当ごとの顧客数。佐藤3、鈴木4、高橋3
佐藤 3人・鈴木 4人・高橋 3人

F4# は「F4 から広がった範囲ぜんぶ」という意味です。MAP と LAMBDA は、F4# の名前を1つずつ n に入れて、同じ計算をくり返してくれます。鈴木さんは遠藤さんを2回担当していますが、ちゃんと1人として数えられて4人になりました。

MAP はちょっと難しい…という場合は、G4 にこの式を入れて、担当の数だけ下にコピーしても大丈夫です。同じ結果(3・4・3)になりました。

=COUNTA(UNIQUE(FILTER($C$2:$C$16,($B$2:$B$16=F4)*($C$2:$C$16<>""))))

「1回だけ来た人」と「何回来たか」

UNIQUE の3つ目の引数を TRUE にすると、「重複を除く」ではなく「1回しか出てこないものだけ」になります。

名前は似ていますが、結果はまったく違うので要注意です。「重複を除いた人数」を出したいときに TRUE を付けてしまうと、何度も来ている常連さんが全員消えてしまいます。

1回だけ来た顧客: =UNIQUE(FILTER(C2:C16,C2:C16<>""),,TRUE)
顧客の一覧:     =UNIQUE(FILTER(C2:C16,C2:C16<>""))
来店回数:       =COUNTIF(C2:C16,F12#)
1回だけ来た顧客は太田だけ。顧客ごとの来店回数の一覧
1回だけ来たのは太田さんだけ。下は顧客ごとの来店回数

来店回数は、顧客の一覧(F12 から広がった範囲)を COUNTIF の条件に入れると、1つの式で全員分が出ます。青木さん4回、石井さん3回…と、元の表を手で数えた回数とぴったり合いました✅

表記ゆれ:UNIQUE が「同じ」と見なすもの・見なさないもの

ここからが、この記事でいちばん伝えたいところです!

人の手で入力した表だと、同じ取引先が少しずつ違う書き方で入っていること、よくありますよね。そこで次の7件を UNIQUE でまとめて、どれが「同じ」と見なされるかを試してみました。

表記ゆれのある取引先7件を、そのまま数えると4、空白をそろえてから数えると2
そのまま数えると 4、空白をそろえてから数えると 2
違い例UNIQUE の扱い
大文字・小文字ABC商事 / abc商事同じ(1つにまとまった)
全角・半角の英字ABC商事 / ＡＢＣ商事同じ(1つにまとまった)
途中の空白の全角・半角みなと 物産 / みなと　物産同じ(1つにまとまった)
後ろの空白ABC商事 / ABC商事(後ろに空白)別のもの
空白があるか、ないかみなと 物産 / みなと物産別のもの

大文字・小文字や、全角・半角の違いは、UNIQUE が自動で同じものとして扱ってくれました(EXACT 関数で比べると「違う」と出るものでも、UNIQUE では1つになるんです)。数がずれる原因になるのは「空白」のほうで、7件をそのまま数えると 4 になりました。

空白を全部取り除いてから数えると、ちゃんと2社になりました🙌 半角の空白と全角の空白の両方を SUBSTITUTE で消して、ASC で英数字を半角にそろえています。

=COUNTA(UNIQUE(ASC(SUBSTITUTE(SUBSTITUTE(A2:A8," ",""),"　",""))))

この式は、名前の途中の空白も消します。「山田 太郎」と「山田太郎」を同じ人として数えたいときには便利ですが、空白に意味がある表では使わないでくださいね。

UNIQUE や FILTER がない Excel の場合

UNIQUE と FILTER は Microsoft 365 と Excel 2021 以降の関数です。それより前の Excel では、SUMPRODUCT と COUNTIF を使う式がよく紹介されています。ただ、これが空白があると答えが出ないんです(>_<)

古い版向けの式。空白を考えない式は#DIV/0!、空白対応の式は6
空白があると #DIV/0!。空白に対応した式は 6
空白があると #DIV/0! になる式: =SUMPRODUCT(1/COUNTIF(C2:C16,C2:C16))
空白があっても数えられる式:     =SUMPRODUCT((C2:C16<>"")/COUNTIF(C2:C16,C2:C16&""))

1つ目の式は、空白のセルについて COUNTIF が 0 を返して 1÷0 になるので、#DIV/0! になりました。2つ目の式は、C2:C16&"" で空白も「空の文字」として数えさせて、さらに (C2:C16<>"") で空白の分を 0 にしています。

Microsoft 365 の Excel で、答えが 6 になることを確かめました(古い版の Excel そのものでは試せていません m(_ _)m)。

サンプルファイルはこちら

「来店記録」シートに、この記事の式がすべて入っています。「表記ゆれ」シートは、上の7件の比べ方を試せるようにしたものです。データはすべて架空です。

unique-count-sample.xlsxダウンロード

マクロは入っていません。ダウンロードしたファイルが「保護ビュー」で開いたら、「編集を有効にする」を押すと式が動きます。

「数えたはずなのに1つ多い…」のモヤモヤが、これで解消できたらうれしいです(*^^*)

この記事の式は、Microsoft 365 の Excel(ビルド 16.0.20527.20150、Windows 11)で、2026年11月8日にすべて試しました。

毎月の集計表づくりをまとめて自動化したい場合は、ココナラで Excel の自動化のご依頼も受けています。

タイトルとURLをコピーしました