Board logo

標題: [發問] 多條件的VLOOPUP [打印本頁]
作者: missbb    時間: 2013-11-23 23:04     標題: 多條件的VLOOPUP

各大大:

請問如何將表1的資料, 以函數放置在表2, 因為要以兩個條件使用VLOOKUP (第一組條件時段及薪金, 第二組是條件時段及加班)                       
抱歉因權限不足未能上傳檔案:(
               
表1       
A                      B                                     C
                        時段                                金額       
薪金                 2013年1月-2月        10000       
                         2013年3月-5月        20000       
                         2013年5月                        25000       
加班                 2013年1月-5月        1000       
                       
                       
表2                       
A                      B             C
時段                 薪金         加班       
2013年1月        10000        1000       
2013年2月        10000        1000       
2013年3月        20000        1000       
2013年4月        20000        1000       
2013年5月        25000        1000
作者: sunnyso    時間: 2013-11-23 23:53

回復 1# missbb

不能直接上載excel檔, 要壓成ZIP再上載.
作者: Hsieh    時間: 2013-11-24 09:36

回復 1# missbb

表1的資料中，時段用區段方式記錄，不合乎資料庫準則
要做分月查詢是不能正確得到
作者: ML089    時間: 2013-11-24 12:21

表1        
A                      B                                     C
                        時段                                金額        
薪金                 2013年1月-2月        10000        
                         2013年3月-5月        20000        
                         2013年5月                        25000        
加班                 2013年1月-5月        1000     

紅色字 5月是否應改為 4月
作者: missbb    時間: 2013-11-24 20:08

回復 4# ML089

:lol 是呢, 打錯了.
作者: nissin    時間: 2013-11-25 01:45

這個用VLOOKUP太複雜了
用VBA處理可能會比較簡單
作者: missbb    時間: 2013-11-25 21:25

回復 6# nissin


   明白, 已在學習中!:P
作者: nissin    時間: 2013-11-26 18:41

試試這個
  1. Sub copydata()
  2. Dim s1 As Long, s2 As Long, s3 As Long, d1 As Long, d2 As Long
  3. Dim wages As Long, OT As Long

  5. i = 1
  6. While Sheets(1).Cells(i, 1) <> "薪金"
  7.     i = i + 1
  8. Wend
  9. wages = i

  11. While Sheets(1).Cells(i, 1) <> "加班"
  12.     i = i + 1
  13. Wend
  14. OT = i

  16. For i = wages To Sheets(1).UsedRange.Rows.Count
  17.     s1 = Left(Sheets(1).Cells(i, 2), 4)
  18.     s2 = Mid(Sheets(1).Cells(i, 2), 6, InStr(1, Sheets(1).Cells(i, 2), "月") - 5 - 1)
  19.     If InStr(1, Sheets(1).Cells(i, 2), "-") <> 0 Then
  20.         s3 = Mid(Sheets(1).Cells(i, 2), InStr(1, Sheets(1).Cells(i, 2), "-") + 1, Len(Sheets(1).Cells(i, 2)) - (InStr(1, Sheets(1).Cells(i, 2), "-") + 1))
  21.     Else
  22.         s3 = 0
  23.     End If
  24.     For j = 2 To Sheets(2).UsedRange.Rows.Count
  25.         d1 = Left(Sheets(2).Cells(j, 1), 4)
  26.         d2 = Mid(Sheets(2).Cells(j, 1), 6, InStr(1, Sheets(2).Cells(j, 1), "月") - 1 - 5)
  27.         
  28.         If d1 * 100 + d2 >= s1 * 100 + s2 Then
  29.             If s3 <> 0 Then
  30.                 If d1 * 100 + d2 <= s1 * 100 + s3 Then
  31.                     If i >= OT Then
  32.                         Sheets(2).Cells(j, 3) = Sheets(1).Cells(i, 3)
  33.                     Else
  34.                         Sheets(2).Cells(j, 2) = Sheets(1).Cells(i, 3)
  35.                     End If
  36.                 End If
  37.             Else
  38.                 If i >= OT Then
  39.                     Sheets(2).Cells(j, 3) = Sheets(1).Cells(i, 3)
  40.                 Else
  41.                     Sheets(2).Cells(j, 2) = Sheets(1).Cells(i, 3)
  42.                 End If
  43.             End If
  44.         End If
  45.     Next j
  46. Next i
  47. End Sub
複製代碼
作者: missbb    時間: 2013-11-26 22:35

回復 8# nissin
多謝您呀 !:D
作者: Hsieh    時間: 2013-11-26 23:30

本帖最後由 Hsieh 於 2013-11-26 23:43 編輯

回復 1# missbb
你是要整理資料成為資料庫型態吧
  1. Sub ex()
  2. Dim OT$, Ary(), r&, y$, a$, i%, s&
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. Set dic1 = CreateObject("Scripting.Dictionary")
  5. r = 2
  6. With Sheets(1)
  7. Do Until .Cells(r, 2) = ""
  8. OT = IIf(.Cells(r, 1) <> "", .Cells(r, 1), OT)
  9. y = Split(.Cells(r, 2), "年")(0)
  10. a = Split(.Cells(r, 2), "年")(1)
  11.    If InStr(a, "-") > 0 Then
  12.    ar = Split(a, "-")
  13.    For i = Val(ar(0)) To Val(ar(1))
  14.       dic(y & "年" & i & "月" & OT) = .Cells(r, 3)
  15.       dic1(y & "年" & i & "月") = ""
  16.    Next
  17.    Else
  18.    dic(y & "年" & a & OT) = .Cells(r, 3)
  19.    n = .Cells(r, 2)
  20.    dic1(.Cells(r, 2) & "") = ""
  21.    End If
  22.    r = r + 1
  23. Loop
  24. ay = Array("時段", "薪金", "加班")
  25. ReDim Preserve Ary(s)
  26. Ary(s) = ay
  27. s = s + 1
  28. For Each ky In dic1.keys
  29. ReDim Preserve Ary(s)
  30. Ary(s) = Array(ky, dic(ky & ay(1)), dic(ky & ay(2)))
  31. s = s + 1
  32. Next
  33. With Sheets(2)
  34. .Columns("A:C") = ""
  35. .[A1].Resize(s, 3) = Application.Transpose(Application.Transpose(Ary))
  36. End With
  37. End With
  38. End Sub
複製代碼
作者: sunnyso    時間: 2013-11-29 21:39

不用VBA也可以, H~O欄爲輔助欄, 答案在Q~S欄
[attach]16902[/attach]

附件需要用WinRAR V5.0才能開啓
[attach]16901[/attach]














回復 4# ML089
回復 5# missbb
回復 6# nissin
回復 10# Hsieh
作者: sunnyso    時間: 2013-11-29 21:54

回復 11# sunnyso

[attach]16903[/attach]
作者: ML089    時間: 2013-12-2 09:08

回復 1# missbb
回復 11# sunnyso

非規則資料會讓公式複雜化，
有時非規則資料是讓資料美觀，可增加輔助欄整理成標準資料塊，也可用簡單公式完成。

[attach]16924[/attach]

F2
=SUMPRODUCT((F$1=LOOKUP(ROW($2:$7),ROW($2:$7)/($A$2:$A$7<>""),$A$2:$A$7))*
(--($E2&"1日")>=--(LEFT($B$2:$B$7,FIND("-",$B$2:$B$7&-1)-1)&"1日"))*
(--($E2&"1日")<=--(LEFT($B$2:$B$7,5)&SUBSTITUTE(SUBSTITUTE(RIGHT($B$2:$B$7,3),"-",),"年",)&"1日")),$C$2:$C$7)

右拉下拉複製公式(黃色區)
作者: missbb    時間: 2013-12-2 21:08

回復 13# ML089


EXCEL真係變化多端, 會試試看!多謝各位大大!:D
作者: tccivy    時間: 2013-12-7 01:25

EXCEL千變萬化再加上VBA有得學囉



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)