計算次數

s10002096

下載 (147.39 KB)

2015-12-8 11:49

請問新客戶數的公式如何代入呢？以業務人員區分，看是否為新客戶，不重複計算客戶編號，麻煩大家幫幫忙
出現結果：
s001：0
s002：1
s003：3

rouber590324

執行如下試試

Sub 業代客戶編號新客戶()
Application.Run "不重覆"
Application.Run "業代客戶編號新客戶1"
Application.Run "業代客戶編號新客戶2"
Application.Run "業代客戶編號新客戶3"
End Sub
Sub 不重覆()
Sheet1.[P265536].ClearContents
Columns("B:B").AdvancedFilter xlFilterCopy, CopyToRange:=[P1], Unique:=True
End Sub
Sub 業代客戶編號新客戶1()
Sheet1.[X2:Y65536].ClearContents
X = Sheet1.[B65536].End(xlUp).Row
For M = 2 To X
Sheet1.Cells(M, 24) = Sheet1.Cells(M, 2) & Sheet1.Cells(M, 3) & Sheet1.Cells(M, 12)
Next
Columns("X:X").AdvancedFilter xlFilterCopy, CopyToRange:=[Y1], Unique:=True
End Sub

Sub 業代客戶編號新客戶2()
Sheet1.[Z2:Z65536].ClearContents
X = Sheet1.[Y65536].End(xlUp).Row
For M = 2 To X
If Sheet1.Cells(M, 25) Like "*新客戶*" Then
Sheet1.Cells(M, 26) = Mid(Sheet1.Cells(M, 25), 1, 4)
End If
Next
End Sub

Sub 業代客戶編號新客戶3()
Sheet1.[Q265536].ClearContents
X = Sheet1.[P65536].End(xlUp).Row
For M = 2 To X
Sheet1.Cells(M, 17) = Application.CountIf(Sheet1.Range("Z:Z"), Sheet1.Cells(M, 16))
Next
End Sub

hcm19522

=SUMPRODUCT((MATCH($C$2:$C$20,$C$2:$C$20,)=ROW(C$2:C$20)-1)*(B$2:B$20=P2)*(L$2:L$20="新客戶"))

准提部林

Ｅ大，
Ｃ欄若有空白，MATCH會有#N/A，
相對無法使用SUMPRODUCT～～用1/COUNTIF(~~)也同理～～
可用COUNT計數，但資料一多，很卡～～

沒有檔案，伸不了手～～
試試．陣列公式：
=COUNT(0/IF(B$2:B$120&L$2:L$120=P2&"新客戶",MATCH(C$2:C$120,C$2:C$120,)=ROW(C$2:C$120)-1))

B$2:B$120&L$2:L$120=P2&"新客戶"　　條件成立，再去MATCH陣列，效率可能好些～～