這里是新手用戶，請多多包涵。我對 VLOOKUP 很滿意，但這就是我的專業知識的終點。
Sheet1 是我正在處理的活動作業表。
Sheet2 是我的源資料。
- 此表包含過去 6 個月內的數千行客戶資料。
- 每個客戶當前在串列中將有 1 到 6 個唯一的資料行（并且還在增長），每個客戶行的主要區別是“日期”（每個客戶每個月 1 個日期）、“型別”（即拖欠、欠款）和“$ 金額”。
列比我上面提到的要多得多，所以在我的活動作業表中，我已經使用 VLOOKUP 來傳輸我正在使用的 3 個主要資料列，它們是；
“貸款編號”、“客戶名稱”、“貸方”
基于“貸款編號”的重復行也已被洗掉。所以，現在每個客戶端就像 1 行一樣。
我現在嘗試做的是，在每個客戶唯一的行中，使用原始源資料，將“$ 金額”填充到相應的列中，帶有月份標題，也基于“型別”值
所以基本上
- 在“Sheet1”中，在“Sheet2”中查找貸款編號（單元格 A1）
- 在“Sheet2”中，找到匹配“Loan Number”和匹配“Date”的行（記住“Sheet1”只有月份，而“Sheet2”有完整的 DD/MM/YYY）
- 如果該行的“型別”為“跟蹤”或“欠款”（還有其他型別欄位我想省略），那么
- 將“金額”回傳到輸入公式的單元格。
我知道可能有多種方法可以做到這一點，但我不想搞亂 Pivots 或類似的東西，我也不想更改源資料的格式。首選基本公式，因為我正在使用書面公式等集成一些 VBA 函式......
任何幫助將不勝感激！
西蒙
uj5u.com熱心網友回復：
你需要在你的作業表中調整它，但SUMIFS應該為你做......
這是單元格K2中的公式...
=SUMIFS($F$2:$F$9,$A$2:$A$9,$H2,$D$2:$D$9,">=" & K$1,$D$2:$D$9,"<=" & EOMONTH(K$1,0))
從本質上講，它是用多個標準總結您的資料。您可以根據需要添加其他條件。這里要注意的重要一點是日期...
K1 = 1/07/2021, 格式為 ... mmmm...它必須是一個日期。
L1 = =EDATE(K1,1), 格式化為 ...mmmm
確保從K1到N1填寫。
要過濾日期（即使用 >= 和 <=），所有欄位都必須是有效日期。
日期很重要，如果它們不是日期，那么將日期 (ie 1/01/2021) 與單詞 (ie January) 進行比較將不起作用。
uj5u.com熱心網友回復：
請嘗試下一個代碼。它應該很快，使用陣列并且大部分處理都在記憶體中完成。它在另一張紙（shDest）中回傳，我測驗它在下一張中回傳：
Sub TransposeDataByLenderMonth()
Dim sh As Worksheet, shDest As Worksheet, lastR As Long, minD As Date, maxD As Date, arrD
Dim dict As Object, El, arr, arrFin, i As Long, k As Long, mtch, strD As String
Set sh = ActiveSheet 'use here the sheet you need
Set shDest = sh.Next 'use here the destination sheet (where the processing result to be returned)
'if next one is empty, the code can be used as it is
lastR = sh.Range("A" & sh.rows.Count).End(xlUp).row
minD = WorksheetFunction.min(sh.Range("D2:D" & lastR)) 'find first Date
maxD = WorksheetFunction.Max(sh.Range("D2:D" & lastR)) 'find last date
'create months arrays (one for months header and second to match the date):
arrD = GetMonthsIntArraysl(minD, maxD)
arr = sh.Range("A2:F" & lastR).Value 'place the whole range in an array, for faster iteration
'Extract unique keys by LoanNo:
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr)
dict(arr(i, 1)) = Empty
Next i
ReDim arrFin(1 To dict.Count 1, 1 To UBound(arrD(1)) 4): k = 1
'Place the header in the final array:
arrFin(1, 1) = "LoanNo": arrFin(1, 2) = "Client Name": arrFin(1, 3) = "Lender"
For i = 1 To UBound(arrD(0))
arrFin(1, 4 i) = CStr(arrD(0)(i, 1))
Next i
k = 2 'reinitialize k to load values after the header
For Each El In dict.Keys 'iterate between the unique elements:
For i = 1 To UBound(arr) 'iterate between the main array rows:
If arr(i, 1) = El Then 'when a match is found:
arrFin(k, 1) = arr(i, 1): arrFin(k, 2) = arr(i, 2): arrFin(k, 3) = arr(i, 3)
strD = CStr(Format(DateSerial(Year(arr(i, 4)), month(arr(i, 4)), 1), "dd/mm/yyyy"))
mtch = Application.match(Replace(strD, ".", "/"), arrD(1), True)
arrFin(k, 4 mtch) = arr(i, 6) arrFin(k, 4 mtch) 'add the value of the appropriate month
End If
Next i
k = k 1
Next El
'Format a little and drop the processed array content:
With sh.Range("J1").Resize(1, UBound(arrFin, 2))
.NumberFormat = "@"
.BorderAround 1, xlMedium
End With
With sh.Range("J1").Resize(UBound(arrFin), UBound(arrFin, 2))
.Value = arrFin
.EntireColumn.AutoFit
.BorderAround 1, xlMedium
.Borders(xlInsideVertical).Weight = xlThin
End With
MsgBox "Job done..."
End Sub
'The following function returns an array of two arrays.
'One for header and the other for matching the date columns (in the final array)
Private Function GetMonthsIntArraysl(startDate As Date, endDate As Date) As Variant
Dim monthsNo As Long, rows As String, monthsInt, dd As Long, arrStr, arrD
monthsNo = DateDiff("m", startDate, endDate, vbMonday)
rows = month(startDate) & ":" & monthsNo month(startDate)
arrStr = Evaluate("Text(Date(" & Year(startDate) & ",row(" & rows & "),1),""mmmm YYYY"")")
arrD = Evaluate("Text(Date(" & Year(startDate) & ",row(" & rows & "),1),""dd/mm/yyyy"")")
GetMonthsIntArraysl = Array(arrStr, arrD)
End Function
上面的代碼添加了屬于同一月份/客戶的日期的所有值，如果是這樣的話......
我把你的問題當作一個挑戰，但你必須知道，為了獲得解決方案，你必須自己進行一些研究并向我們展示一段代碼。請把它當作一個人情，下次從上述角度提出一個更好的問題，遵守社區規則。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/411977.html
標籤：