誰能告訴我如何撰寫一個公式來識別下表中具有不同產品或產品基礎更改的所有 CustomerID？
結果：在 C 列上顯示具有不同產品的 CustomerID 1 和 3
uj5u.com熱心網友回復：
我在 C 列中的公式是：
=COUNTIFS($A$2:$A$11;A2;$B$2:$B$11;B2)=COUNTIF$A$2:$A$11;A2)
如果客戶是否總是購買相同的產品，此公式將回傳 True/False。您對購買不同產品的客戶感興趣，因此在這種情況下您希望按 FALSE 選項過濾：
如果你有 E365，你有 UNIQUE 和 FILTER 之類的函式，所以你可以使用公式而不是手動過濾范圍。
uj5u.com熱心網友回復：
正如@Foxfire And Burns And Burns 所提到的，如果你有 O365，你可以使用 Filter 和 Unique。它看起來像這樣：
=UNIQUE(FILTER(A2:A11,COUNTIFS(A2:A11,A2:A11,B2:B11,"<>"&B2:B11)<>0))
如果您沒有 O365，則可以恢復為下拉公式。如果你在 D2 中輸入它，比如說，它將是：
=IFERROR(INDEX(A$2:A$11,MATCH(1,(COUNTIFS(A$2:A$11,A$2:A$11,B$2:B$11,"<>"&B$2:B$11)<>0)*(COUNTIF(D1:D$1,A$2:A$11)=0),0)),"")
作為陣列公式輸入。
uj5u.com熱心網友回復：
我不認為 excel 函式可以給你一個非單一結果的串聯答案。VBA 可以解決這個問題。
假設您的資料將是：
- 資料范圍 A2:B11（不包括 A1:B1 中的標題），假定為非空白單元格
- 按 CustomerID 排序（按任何型別的訂單），VBA 腳本將沿著您排序的 CustomerID 列運行
- 結果（CustomerID 有超過 1 個產品）將從 C2 沿 C 列列印。因此，請確保清理 C 列中的現有資料。
VBA 腳本應該是：
Sub list()
Dim i As Integer
Dim cell As Range
Dim d_cust() As Variant
Set customerID = ActiveSheet.Range("$A$2:" & Range("A2").End(xlDown).Address)
Set Product = ActiveSheet.Range("$B$2:" & Range("B2").End(xlDown).Address)
i = 0
For Each cell In customerID
On Error Resume Next
If cell.Value <> d_cust(i - 1) And Evaluate("=SUMPRODUCT((" & customerID.Address & "=" & cell.Value & ")/(COUNTIFS(" & Product.Address & "," & Product.Address & "," & customerID.Address & "," & Chr(34) & cell.Value & Chr(34) & ") (" & customerID.Address & "<>" & cell.Value & ")))") > 1 Then
ReDim Preserve d_cust(0 To i) As Variant
d_cust(i) = cell.Value
ActiveSheet.Cells((2 i), 3) = cell.Value
i = i 1
End If
Next cell
End Sub
這是 C2 的 C 列中的結果，每個單元格 1 的結果。您可以通過在此處更改起始單元格和方向來根據需要更改結果行/列：ActiveSheet.Cells((2 i), 3) = cell.Value
uj5u.com熱心網友回復：
檢索具有多個專案的唯一性（字典）
- 調整常量部分中的值。
Option Explicit
Sub RetrieveMultiProductIds()
' Source
Const sName As String = "Sheet1"
Const sFirstCellAddress As String = "A1"
' Destination
Const dName As String = "Sheet1"
Const dFirstCellAddress As String = "C1"
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets(sName)
' Reference the source data range and write its values to an array.
Dim srg As Range
Dim srCount As Long
Dim fCell As Range: Set fCell = ws.Range(sFirstCellAddress)
With fCell.CurrentRegion
Set srg = fCell.Resize(.Row .Rows.Count _
- fCell.Row, .Column .Columns.Count - fCell.Column)
srCount = .Rows.Count - 1
End With
If srCount < 1 Then Exit Sub ' too few rows
Set srg = srg.Resize(srCount, 2).Offset(1)
Dim sData As Variant: sData = srg.Value
' Store the unique ids in the keys and their corresponding values
' in the items of a dictionary. If for the same key a different value
' is encountered, flag the item with "@".
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
Dim sKey As Variant
Dim r As Long
Dim drCount As Long
For r = 1 To srCount
sKey = sData(r, 1)
If dict.Exists(sKey) Then
If dict(sKey) <> "@" Then
If dict(sKey) <> sData(r, 2) Then
dict(sKey) = "@"
drCount = drCount 1
End If
End If
Else
dict(sKey) = sData(r, 2)
End If
Next r
If drCount = 0 Then Exit Sub
' Write the flagged keys to a 2D one-based one-column array.
Dim dData As Variant: ReDim dData(1 To drCount, 1 To 1)
r = 0
For Each sKey In dict.Keys
If dict(sKey) = "@" Then
r = r 1
dData(r, 1) = sKey
End If
Next sKey
' Write the values from the array to the destination range and clear below.
With wb.Worksheets(dName).Range(dFirstCellAddress)
.Resize(r).Value = dData
.Resize(.Worksheet.Rows.Count - .Row - r 1).Offset(r).Clear
End With
MsgBox "Number of found multi-product ids: " & r, vbInformation
End Sub
uj5u.com熱心網友回復：
我認為 COUNTIFS 有效地解決了這個問題
=COUNTIFS($A$2:$A$8,"="&A2,$B$2:$B$8,"="&B2)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/435203.html