在excel中，怎么根据产品类别、合同编号，计算“设备”和”服务“合同的个数，详见图片？

发布网友

我来回答

共3个回答

热心网友

根据已经条件的数据规律，合同尾号为1的为“设备”，合同尾号为2的为“服务”，合同中存在相同的合同不能重复计数，分别输入以下公式

=SUMPRODUCT((--RIGHT(C$2:C$16)=1)/COUNTIF(C$2:C$16,C$2:C$16))

=SUMPRODUCT((--RIGHT(C$2:C$16)=2)/COUNTIF(C$2:C$16,C$2:C$16))

详见附图示例

追答

识别设备及服务类型时，“设备续保服务”不在右侧“服务”列表内。

添加D列为辅助列，在D2单元格输入以下公式，然后向下填充公式，得到合同分类

=INDEX($E$1:$F$1,SUMPRODUCT((B2=E$2:F$10)*COLUMN(A:B)))

在C20单元格输入以下公式，然后向下填充公式

=SUMPRODUCT((D$2:D$16=B20)/COUNTIF(C$2:C$16,C$2:C$16))

热心网友

基于你提供的截图，
D21单元格写公式：
=SUMPRODUCT((1/COUNTIF(C2:C15,C2:C15))*(IFERROR(1/COUNTIF(E2:E10,B2:B15),0)))
然后鼠标点击进去上面的公式编辑栏，同时按下 Ctrl+Shift+Enter（回车）三键，直至公式前后出现{ }（注意：这个 { }不是手工输入进去的）。

D22单元格写公式：
=SUMPRODUCT((1/COUNTIF(C2:C15,C2:C15))*(IFERROR(1/COUNTIF(F2:F6,B2:B15),0)))
然后鼠标点击进去上面的公式编辑栏，同时按下 Ctrl+Shift+Enter（回车）三键，直至公式前后出现{ }（注意：这个 { }不是手工输入进去的）。追问能否告知下，这个Ctrl+Shift+Enter的原理是什么？或者说这个三键和直接输入enter，有什么不一样？谢谢！

追答Ctrl+Shift+Enter是用于Excel公式的数组公式的。输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl+Shift+Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”

热心网友

用VBA吧，一劳永逸。
Sub 合同分类统计()
ThisWorkbook.Activate
Sheets("合同").Activate '“合同”换为你实际的工作表名称
Dim rngCL As Range, rngCLB As Range
Dim i&, j&, k&
For Each rngCL In Range(Cells(2, "C"), Cells(Cells(2, "C").End(xlDown).Row, "C"))
If rngCL.Value <> Cells(rngCL.Row - 1, "C").Value Then
For Each rngCLB In Range(Cells(2, "E"), Cells(10, "F"))
If Cells(rngCL.Row, "B").Value = rngCLB.Value Then
If rngCLB.Column = 5 Then i = i + 1 Else j = j + 1
Exit For
End If
Next
End If
Next
Set rngCL = Nothing
Set rngCLB = Nothing
k = Cells(Rows.Count, "C").End(xlUp).Row
Cells(k - 1, "D").Value = i
Cells(k, "D").Value = j
End Sub