excel関数で教えてください。

質問

Ａ　　　Ｂ　　
１　　12345
２　　23456
３　　12345
４　　44556
６　　44556
７　　55667
８　　88990
９　　12345　
１０　　　　　　
１１　　5件

----------------------------------------------
おはようございます。
上記のように数値が入力されているシートがあります。
１１行の「5件」を求めたいのですが、うまくできません。

※条件※
(1)．同じ数値（または文字列）の場合は、重複を求めて１件とします。
(2)．１０行のように空白セルはカウントしない。

個別に求める事は出来たのですが、なんとか複合系で算出したく
投稿いたしました。
お知恵を貸してください。お願いします。

回答No.10

Wendy02 · ベストアンサー

こんにちは。

こんな方法もあります。

=SUMPRODUCT((MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0)=ROW(A1:A10))*NOT(ISBLANK(A1:A10)))

-------------------------------------------
ここでは空白判定は、A列だけにします。A列B列判定するには、NOT(ISBLANK(A1:A10)))を　((A1:A10&B1:B10)<>"")に置き換えればよいです。
-------------------------------------------
配列数式は、トリッキーで、掲示板の解答特有のものだと思います。

解くのは面白いけれども、これらは、いくつかの解法のパターンがあります。それを覚えれば可能なものの、むつかしいことを考えないで、補助列・作業列を使って、C列に作った数式　=A1 & B1 を、=(COUNTIF($C$1:C1,C1)=1)*(C1<>"")で、オートフィルでコピーして、それを合計すればよいと思います。

ただし、単純なVBAのコードでは解けないもので、配列数式では可能なものもあります。

回答No.9

MackyNo1

補助列を使わずにA列とB列の組み合わせデータで重複のないデータ数を表示するなら、たとえば以下のような関数を使用します。

=COUNT(1/(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0)=ROW(A1:A10)))-(SUM((A1:A10&B1:B10="")*1)>0)

配列数式にするため、入力後Ctrl+Shift+Enterで確定してください。

お礼

MackyNo1様

満足できる結果が得られました。

色々とご教授頂きありがとうございました。

回答No.8

KURUMITO

Ａ11セルには次の式を
=SUMPRODUCT((C1:C10<>"")/COUNTIF(C1:C10,C1:C10&""))
Ｃ11セルには次の式を入力します。
=COUNTIF(C1:C10,"?*")-SUMPRODUCT((C1:C10<>"")/COUNTIF(C1:C10,C1:C10&""))

回答No.7

Wendy02

こんにちは。

条件的には、
-------------------------------------------
C列を集計データとする　=A1 & B1
A列 には、日付データが入る。
空白値は、B列で判定させる。（A列は、日付が入るという条件から）
B列は空白か文字列か数字。空白は、文字空白("")を用いない。
エラー値は入れない。
----------------------------------------

COUNTIF 関数
=SUMPRODUCT((COUNTIF(OFFSET($C$1,,,ROW($A$1:$A$10),),C1:C10)=1)*($B$1:$B$10<>""))

MATCH 関数
=SUMPRODUCT((MATCH($C$1:$C$10&"",$C$1:$C$10,0)=ROW($C$1:$C$10))*($B$1:$B$10<>""))

移動のときの注意
*ROW($C$1:$C$10)の部分は、高さを示すから、必ず、1から始まる。

失敗例：
重複の合計から、COUNTBLANK関数で引く場合は、1個の場合は良いけれども、2個以上の場合は数がずれてしまいます。

×=SUMPRODUCT((MATCH(C1:C10&"",C1:C10,0)=ROW(C1:C10))*1)-COUNTBLANK(B1:B10)

お礼

Wendy02様
ありがとうございました。
ＭＡＴＣＨ関数があるなんて知りませんでした。

多数の方から、教えていただいたので一番合ってる関数
を使わせていただきます。

回答No.6

MackyNo1

参考までに

wisemac21さんの数式は、数字以外のデータが混ざっている場合にも対応できますが、今回の条件の空白セル含まれる場合に対応できません。

=SUMPRODUCT(1/COUNTIF($A$1:$A$8,A1:A8))
　　　　　　　↓
=SUM(IF(A1:A10="","",1/COUNTIF(A1:A10,A1:A10)))

上記のように変更してCtrl+Shift+Enterで確定するのがよいと思います。

Ctrl+Shift+Enterで確定しないでも計算できる配列数式にするならたとえば以下のような数式になります。

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""))-(COUNTBLANK(A1:A10)>0)

ちなみにedomin7777さんの数式で該当しないデータをエラーにする方法の場合は以下のように単純にFREQUENCYで求められる配列を０や１の数字で割り算したほうがわかりよいような気がします。

=COUNT(1/FREQUENCY(A1:A10,A1:A10))

お礼

↑間違いです。
－－－－－－－－－
｜１１　｜　　６件
｜　　　｜　　　　　

でした。

お礼のフォームを使わせてもらいました。
ごめんなさい。

補足

MackyNo1様　有難うございます。
難しすぎて僕にはサッパリです(^^;

どうせ考えても理解できないので、コピーして使わせていただきます。
ついでに、お聞きしたいのですが　↓

　　　　Ａ　　　　　　Ｂ　　　　Ｃ　　　　　
１　　12345　　　田中　　12345田中
２　　23456　　　山本　　23456山本
３　　12345　　　木村　　12345木村
４　　44556　　　山本　　44556山本
６　　44556　　　山本　　44556山本
７　　55667　　　鈴木　　55667鈴木
８　　88990　　　木村　　88990木村
９　　12345　　　田中　　12345田中
１０　　　　　　
１１　　　　　　　　　　重複2件　　

上記のようにできるでしょうか？
(1)．ＡとＢの範囲で重複をカウント。
(2)．空白はカウントしない。
です。
Ｃのセルは作業用でＡ&Ｂで結合しました。そのほうが
やりやすいと思ったのですが、
１０行目の空白列で重複扱いになってしまいます。
（Ａのセルは、「日付型」です。）
よろしくお願いします。

回答No.5

KURUMITO

次のようにすることで作業列も使わずに空白行があっても対応できます。
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

お礼

KURUMITO様
有難うございました。

また、困ったらご教授くださいませ。

回答No.4

wisemac21

作業列を使わず
=SUMPRODUCT(1/COUNTIF($A$1:$A$8,A1:A8))

お礼

wisemac21様
返事遅くなってごめんなさい。

勉強になりました。またよろしくお願いします。

回答No.3

st_comp

1.
B列にIF(COUNTIF($A$4:A9,A9)>1,"重複","")
などの数式を入れて、一度列に出た数値に重複マーカーをつける。
2.
A列をカウント
3.
B列から重複マーカーをカウント
4.
A列のカウント値からB列の重複マーカー値を引く

以上でいかがでしょう。

実際運用時はマーカーは計算エリアにでも入れて非表示にすればいいかと。

お礼

st_comp様
ありがとうございます。
助かりました。正月明けにすぐ使えないと部長に
かみなりでした。^^;

お礼の場所を間違っておりました。

補足

st_comp様
ありがとうございます。
助かりました。正月明けにすぐ使えないと部長に
かみなりでした。^^;

回答No.2

tom04

こんにちは！
色々方法はあるかと思いますが・・・
一例です。

↓の画像のように作業用の列を使わせてもらいます。

B2セルに
=IF(COUNTIF($A$2:A2,A2)=1,1,"")
という数式を入れ、オートフィルで下へずぃ～～！っとコピーします。

そして、A11セルは単純に
=SUM(B2:B10)
としています。

（数式の範囲指定領域はデータ量によってアレンジしてください。）

以上、参考になれば幸いですが、
他に良い方法があれば
読み流してくださいね。m(__)m

補足

tom04様　ありがとうございます。

上手く処理できそうです。

excelって奥深いですね
また、よろしくお願いします。

回答No.1

edomin7777

=COUNT(FREQUENCY(A1:A10,A1:A10)^0)
で算出できます。

補足

edomin7777様

こんな関数でも出来るんですね。
試してみます。

有難うございました。