想請各位EXCEL高手幫幫忙,我有一個表格,需要在F欄位放入當天的資料,並與E欄進行比對:
條件一 : F欄的號碼如果在E欄沒有比對到一模一樣號碼,即為不吻合
例如:F欄第一個號碼為 0022-24163
而E欄之中沒有這個號碼,即將0022-24163 放到"比對結果"一欄位中並
註明"多的"
條件二 : F欄的號碼與E欄的號碼出現次數不一樣,即為不吻合
例如:F欄位中 0200-15120 (F10) 出現了8個,而E欄的0200-15120也是有
8個,即為吻合,但如果F欄位的號碼出現次數與E欄位不相同,則將其號碼
代到"比對結果" 一欄位中(如果可以顯示F欄比E欄同一個號碼多了幾個或
少了幾個更好)
條件三: 如果E欄位中的號碼,在F欄位中沒有出現,則將該號碼 代入"比對結果"
一欄位中,並註明"缺少"
不知道上述的條件會不會太複雜沒辦法比對? 總之以E欄位為標準欄,F欄位的號碼、與出現次數必需與E欄位相同, 如有差異則將其有差異的號碼顯現在"比對結果"欄位中,拜託大家幫忙了,感激不盡!!
由於我沒辦法直接回覆你們的問題(不知為什麼,新手有發文限制?),我只能在這邊說明:
回覆 blanksoul12 :
"需要在F欄位放入當天的資料" 這句意思是---我每天從系統down資料並copy下來到這個excel表與E欄比對
1.人手一條一條輸入?...copy的
2.從另一資訊源導入或 copy 過來? .....系統down下來並手動copy過來的
回覆ccenjor:
我用你幫我寫的函數貼到G欄後,帶出來的資料不太正確,不知道是不是我貼過來函數跑掉了?
這裏不知道能不能直接上傳EXCE檔案上來給你看?
回覆rogeryao:
您直接做巨集真是太好了,可是我不知道怎麼使用,能教教我嗎?
回覆海綿寶寶
我用你幫我寫的函數貼到G欄後,帶出來的資料不太正確,感覺函數跑掉了
這裏不知道能不能直接上傳EXCE檔案上來給你看?
已邀請的邦友 {{ invite_list.length }}/5
Private Sub CommandButton1_Click()
Worksheets("工作表2").Cells(1, 1) = "Part P/N"
Worksheets("工作表2").Cells(1, 2) = "缺少"
Worksheets("工作表2").Cells(1, 3) = "多的"
Worksheets("工作表2").Cells(1, 4) = "差異"
'工作表1的 E 欄位 copy 到工作表2 A 欄位
Range("E2", Range("E2").End(xlDown)).Copy Worksheets("工作表2").Range("A2")
Dim XValue As String
XValue = "A" & (Worksheets("工作表2").Range("A2").End(xlDown).Row + 1)
'工作表1的 F 欄位 copy 到工作表2 A 欄位(接續上面)
Range("F2", Range("F2").End(xlDown)).Copy Worksheets("工作表2").Range(XValue)
'工作表2 A 欄位去除重複
Worksheets("工作表2").Range("A2:A65536").RemoveDuplicates Columns:=1, Header:=xlNo
Dim YValue As String
YValue = "A" & (Worksheets("工作表2").Range("A2").End(xlDown).Row)
'排序
Worksheets("工作表2").Sort.SortFields.Clear
Worksheets("工作表2").Sort.SortFields.Add Key:=Range("A2:" & YValue), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets("工作表2").Sort
.SetRange Range("A2:" & YValue)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'
Dim U As Integer
Set RngHead = Worksheets("工作表2").Range("A2")
DataCunt = Worksheets("工作表2").Range("A65536").End(xlUp).Row
'用 CountIf 計算同一字串出現次數
Dim SearchStr As String
For U = RngHead.Row To DataCunt
SearchStr = Trim(Worksheets("工作表2").Cells(U, 1))
If (SearchStr <> "") Then
Worksheets("工作表2").Cells(U, 2) = WorksheetFunction.CountIf(Worksheets("工作表1").Range("E2:E65536"), SearchStr)
Worksheets("工作表2").Cells(U, 3) = WorksheetFunction.CountIf(Worksheets("工作表1").Range("F2:F65536"), SearchStr)
'
If Worksheets("工作表2").Cells(U, 3) > Worksheets("工作表2").Cells(U, 2) Then
Worksheets("工作表2").Cells(U, 4) = "多" & Str(Worksheets("工作表2").Cells(U, 3) - Worksheets("工作表2").Cells(U, 2))
ElseIf Worksheets("工作表2").Cells(U, 3) < Worksheets("工作表2").Cells(U, 2) Then
Worksheets("工作表2").Cells(U, 4) = "少" & Str(Worksheets("工作表2").Cells(U, 2) - Worksheets("工作表2").Cells(U, 3))
Else
Worksheets("工作表2").Cells(U, 4) = "吻合"
End If
End If
Next
End Sub
G2:
=IFERROR(IF(MATCH(F11,$E$2:$E$20,0)>0,IF(COUNTIF($E$2:$E$20,F11)=COUNTIF($F$2:$F$20,F11),"吻合",F11&"("&(COUNTIF($F$2:$F$20,F11)-COUNTIF($E$2:$E$20,F11)&")"))),F11&"(多的)")&IF(COUNTIF($F$2:$F$20,E11)=0,","&E11&"(缺少)","")
再將公式複製到G3:G20。
如果結果符合需求
公式如下
G2=IF(COUNTIF($E:$E,F2)=0,"多的( "&F2&")",IF(COUNTIF($E:$E,F2)<>COUNTIF($F:$F,F2),"F-E=" & COUNTIF($F:$F,F2)-COUNTIF($E:$E,F2) & "(" & F2 & ")","")) & IF(COUNTIF($F:$F,E2)=0,"缺少( "&E2&")",IF(COUNTIF($E:$E,E2)<>COUNTIF($F:$F,E2),"F-E=" & COUNTIF($F:$F,E2)-COUNTIF($E:$E,E2) & "(" & E2 & ")",""))
G3, G4... 以下複製貼上
如果公式OK，原理只有一句話
用 COUNTIF 計算該儲存格在 E,F 欄的出現次數(0表示缺少/多的)
若不為0則進行比較，不同就顯示差異(F-E=)
1.先對E欄及F欄"各"做一個樞紐分析表.並將這二個樞紐分析表放在同一個SHEET上
2.用VLOOKUP 去比對這二個樞紐分析表的"計數 PART P/N"的數量即可得到答案
E欄的樞紐 : 只會出現E欄的Part P/N, 就將Part P/N 拉二次: 一次拉到"列" , 一次拉到"值"
就可以看到每一個Part P/N"出現"幾次.
F欄也是同樣作法.
再將這二個樞紐做VLOOKUP的比對即可.
iThome鐵人賽
看影片追技術