- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 257
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-11-27
              
|
2#
發表於 2010-12-20 23:25
| 只看該作者
本帖最後由 Hsieh 於 2010-12-20 23:35 編輯
E2=SUMPRODUCT((Sheet1!$A$2:$A$9=Sheet2!$A2)*(Sheet1!$B$2:$B$9=Sheet2!$B2)*(Sheet1!$C$2:$C$9=Sheet2!$C2)*Sheet1!D$2:D$9)
向下向右複製
VBA方式- Sub Ex()
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Dim A As Range
- With Sheet1
- For Each A In .Range(.[A2], .[A2].End(xlDown))
- mystr = A & A.Offset(, 1) & A.Offset(, 2)
- d(mystr) = A.Offset(, 3).Value
- d1(mystr) = A.Offset(, 4).Value
- Next
- End With
- With Sheet2
- For Each A In .Range(.[A2], .[A2].End(xlDown))
- mystr = A & A.Offset(, 1) & A.Offset(, 2)
- A.Offset(, 4) = d(mystr)
- A.Offset(, 5) = d1(mystr)
- Next
- End With
- End Sub
|
|
發表於 2010-12-20 23:10
| 
，感謝Hsieh大大再一次的幫忙!
