EXCEL 怎么用函数公式计数(去除重复项)
要好好学习,在用函数计算公式来计算。
如何在EXCLE中定义公式求的不同类别数组中的最大值或最小值?
做透视图就可以,需要时点右键刷新一下,公式也可以,但没必要一步步编,因同类数据不等长,不规则。
excle函数公式,不用数组
使用VBA,不用数组;
Dim MyMaxRow As Integer
Dim ThisRowN As Integer
Dim DRowN As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Temp As Integer
With Excel.Application.ActiveWorkbook
MyMaxRow = .Sheets("Sheet1").Range("D65535").End(xlUp).Row
'把数据去除元格从D迁移到G列
ThisRowN = 1
Do While .Sheets("Sheet1").Cells(ThisRowN, 4).Value = ""
ThisRowN = ThisRowN 1
Loop
DRowN = ThisRowN - 1
For i = ThisRowN To MyMaxRow
If .Sheets("Sheet1").Cells(i, 4).Value "" Then
DRowN = DRowN 1
.Sheets("Sheet1").Cells(DRowN, 7).Value = .Sheets("Sheet1").Cells(i, 4).Value
End If
Next i
'把数据按照排列写到H列
'先把数据从G列复制到H列
For i = ThisRowN To DRowN
.Sheets("Sheet1").Cells(i, 8).Value = .Sheets("Sheet1").Cells(i, 7).Value
Next i
'然后开始排序
For i = ThisRowN To DRowN - 1
k = i
For j = i 1 To DRowN
If .Sheets("Sheet1").Cells(k, 8).Value > .Sheets("Sheet1").Cells(j, 8).Value Then
k = j
End If
Next j
If k i Then
Temp = .Sheets("Sheet1").Cells(i, 8).Value
.Sheets("Sheet1").Cells(i, 8).Value = .Sheets("Sheet1").Cells(k, 8).Value
.Sheets("Sheet1").Cells(k, 8).Value = Temp
End If
Next i
End With
在excel中如何用公式统计不重复的值的个数
假设你的数据在A2:A21,
统计数据个数(重复数据只计算1次)
=SUMPRODUCT(1/COUNTIF(A2:A21,A2:A21))