何種類の商品があるか数える関数

質問

A,C,D列に商品の製造番号が入っています。中には重複しているものがいくつかあるのですが、E列に全部で何種類の製造番号があるか調べる関数はありますか？

回答No.4

imogasi

VBAで少し短くならないかと。J列をワークに使います。
下記のMsgBox nはCells(i,j)=nに変えれば、セルにセットできます。
Sub test01()
h = 1
retu = Array(1, 3, 4) '列をA、C、D列指定
For Each i In retu
d = Cells(65536, i).End(xlUp).Row
Range(Cells(1, i), Cells(d, i)).Copy '以下J列へ縦に並べる
Cells(h, "j").Select
h = h + d
Selection.PasteSpecial Paste:=xlValues
Next i
Range(Cells(1, "j"), Cells(h, "j")).Sort Key1:=Range("J1") 'ソート
n = 0
For i = 1 To h
If Cells(i, "j") = Cells(i + 1, "j") Then
Else
n = n + 1 'カウント
End If
Next i
MsgBox n
End Sub

回答No.3

imogasi

列を寄せ集めます。A、C、D列をA、B、C列にまとめます。
（例）A1:C7に
112 115 112
223 224 112
113 224 113
44 45 444
123 127 129
112 154
222 118 118
（式）
A9に（どこでも空き範囲のセルで良い）
=COUNTIF($A$1:$C$7,A1)といれ、A9セルをA15まで複写する。次にA9:A15を範囲指定してC9:c15まで＋ハンドルを引っ張る。
4 1 4
1 2 4
2 2 2
1 1 1
1 1 1
0 4 1
1 2 2
となる。
次にD1セルに式=COUNTIF($A$9:$C$15,ROW(A1))/ROW(A1)
をいれ、A9:C15の中に出ている最大数字行まで（上例では４なのでD4まで）複写します。
10
3
0
1
0
0
0
この和の１４が種類数です。
))/ROW(A1)の部分の意味は、考えている範囲に、２個同じ数字があれば２ヶ所で２が出る、３個同じ数字があれば３ヶ所３が出るからです。だから、１の個数＋２の個数／２＋３の個数／３＋・・で種類数が数えられます。
本回答は倍以上の余分なセル範囲が必要だったりしますが、関数でと言うことであるので、ない知恵を絞って考えました。

お礼

丁寧な回答ありがとうございます。
関数だと大変なのですね。。。

回答No.2

comv

こんばんは

>A,C,D列
Ａ：Ｄ列の特定範囲でＢ列及び空白セルはカウントしない条件で
Ａ：Ｄのデータ範囲　例えば　A1:D50　など

=ROUND(SUM(IF(A1:D50="",0,1/COUNTIF(A1:D50,A1:D50))*(COLUMN(A1:D50)<>2)),0)

と入力して　[Ctrl]+[Shift]を押したまま[Enter]で入力確定
成功すると確定後　数式は　{=式}　と大括弧で括られます（配列数式）

で試してみて下さい

補足

大変すいません。質問のA,C,D列は間違いでA,C,E列でした。また初心者の為、回答いただいた関数の意味を教えていただけないでしょうか？今後他に応用出来るようにする為、なぜこういう組み合わせになったのか知りたいのですが、考えても良くわからないのです。お手数ですがお願いします

回答No.1

ja7awu

直接目的の数値を算出する関数は無いと思います。
ワークシート関数だけで算出する場合は、複数の関数を組み合わせ、作業用のセルを
使うなど、かなり面倒になると思います。

もし、マクロでの処理で良ければ、次の手順でテストしてみてください。

下記マクロを実行すると、A,C,D列にある商品の製造番号を同じものは１つと数えて、
その種類をカウントします。

１．Alt + F11 で VBE(Visual Basic Editor)を開きます。
２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。
３．モジュールウィンドウに下記コードをコピーして貼り付けます。
　　コードの２～３行目を実情に合わせて設定します。
４．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。
５．メニューから[ツール]-->[マクロ]-->[セキュリティ]で「セキュリティレベル」を
　「中」にして[OK]します。
６．実行するときは、Alt ＋ F8 (メニューから[ツール]-->[マクロ]-->[マクロ])で
　「製造番号種類カウント」を指定し、[実行]ボタンを押します。

これで、データがあるシートをアクティブにして、このマクロを実行すると、指定した
セルに製造番号の種類をカウントし、その数値を表示します。

これで如何でしょうか。

Sub 製造番号種類カウント()
Const TopRow = 2 '　　　<---- データの先頭行を指定(見出しを除く)
Const OutCell = "E2" '　<--- 種類の数を表示するセルを指定
Dim Rng As Range
Dim S As Range
Dim Cnt As Long
Columns(1).Insert
If Range("B65536").End(xlUp).Row >= TopRow Then
　　Range("B" & TopRow, Range("B65536").End(xlUp)).Copy _
　　　　Destination:=Range("A2")
End If
If Range("D65536").End(xlUp).Row >= TopRow Then
　　Range("D" & TopRow, Range("D65536").End(xlUp)).Copy _
　　　　Destination:=Range("A65536").End(xlUp).Offset(1)
End If
If Range("E65536").End(xlUp).Row >= TopRow Then
　　Range("E" & TopRow, Range("E65536").End(xlUp)).Copy _
　　　　Destination:=Range("A65536").End(xlUp).Offset(1)
End If
If Range("A65536").End(xlUp).Row = 1 Then
　　Columns(1).Delete
　　Range(OutCell).Value = 0
　　Exit Sub
End If
For Each Rng In Range("A2", Range("A65536").End(xlUp))
　　If Rng.Text <> "" Then
　　　　Cnt = Cnt + 1
　　　　Set S = Range("A2", Range("A65536").End(xlUp)).Find(Rng.Text, lookat:=xlWhole)
　　　　If Not S Is Nothing Then
　　　　　　Do
　　　　　　　　S.Value = ""
　　　　　　　　If Range("A65536").End(xlUp).Row = 1 Then Exit For
　　　　　　　　Set S = Range("A2", Range("A65536").End(xlUp)).FindNext
　　　　　　Loop Until S Is Nothing
　　　　End If
　　End If
Next Rng
Columns(1).Delete
Range(OutCell).Value = Cnt
Beep: Beep: Beep
MsgBox "製造番号種類カウントを終了しました。!!" & vbLf & vbLf & _
　　Cnt & " 個です。", vbInformation
Set S = Nothing
End Sub