ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó¦bmatch¤¤¤Þ¥Î2ºû°}¦C

[µo°Ý] ¦p¦ó¦bmatch¤¤¤Þ¥Î2ºû°}¦C

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-12-6 13:16 ½s¿è

¤U¦CVBA¤¤,¦³¤@¬qmatch¶·¤Þ¥Î1ºû°}¦C,
¬G¥ý±N2ºû°}¦C¥ýÂন1ºû°}¦C¦A¤Þ¥Î(¬õ¦â³¡¤À),
½Ð°Ý¬õ¦â³¡¤À¦p¦ó¥¿½T¤Þ¥Î(¦p¦óºë²)?
  1. Public arW, arL
  2. '¨ú±o寛«×»Pªø«×¬É­­ªº°}¦C, ¨ÑMatch¥Î
  3. Sub init()
  4.     ReDim arW(3) As Integer
  5.     ReDim arL(3, 3) As Integer
  6.     Dim W1 As Integer, L1 As Integer
  7.     arW(0) = Split(Cells(3, 3), "~")(1)    '寛«×ªº¤W­­
  8.     For W1 = 0 To 2
  9.         arW(W1 + 1) = Split(Cells(W1 * 3 + 3, 3), "~")(0) '寛«×«ö­°¾­±Æ
  10.         For L1 = 0 To 2
  11.             arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 3, 5), "~")(0)   'ªø«×«ö¤É¾­±Æ
  12.         Next
  13.         arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 2, 5), "~")(1)     'ªø«×ªº¤W­­
  14.     Next
  15. End Sub

  16. Private Sub CommandButton1_Click()
  17.     Dim I As Integer, J As Integer, arL2(3) As Integer
  18.     Dim MHW, MHL, IDW As String, IDL As String
  19.     init
  20.     For I = 4 To [G4].End(xlDown).Row
  21.         MHW = Application.Match(Cells(I, 8), arW, -1)
  22.         IDW = Application.Index([B1:B11], MHW * 3, 1)
  23. [color=Red]        For J = 0 To 3
  24.             arL2(J) = arL(MHW - 1, J)    '±N2ºû°}¦CÂର1ºû°}¦C
  25.         Next
  26.         MHL = Application.Match(Cells(I, 9), arL2, 1)[/color]
  27.         'ªø«×¥N¸¹¤À[D3:D5,D6:D9,D10:D12]¤T°Ï
  28.         IDL = Application.Index([D3:D5,D6:D8,D9:D11], MHL, 1, MHW)
  29.         Cells(I, 10) = IDW & "_" & IDL
  30.     Next
  31. End Sub
½Æ»s¥N½X
(µù:VBA¥i¥¿±`¹B§@)
       For J = 0 To 3
            arL2(J) = arL(MHW - 1, J)    '±N2ºû°}¦CÂର1ºû°}¦C
        Next
        MHL = Application.Match(Cells(I, 9), arL2, 1)

¦h±ø¥ó§PÂ_½s»s¦WºÙ1206.rar (11.7 KB)

Sorry, À³¸Ó©ñ¦bµ{¦¡°Ï¤~¹ï, ¥u¦n±N¿ù´N¿ù!!

TOP

Function GET_WL(xA, Va, Vb) As String
Dim Arr, AA, j&, Jm%, T$
Arr = xA.Value
For j = 1 To UBound(Arr)
¡@¡@If Arr(j, 2) <> "" Then
¡@¡@¡@¡@Jm = 0:  AA = Split(Arr(j, 2) & "~", "~")
¡@¡@¡@¡@If Va > Val(AA(0)) And Va <= Val(AA(1)) Then T = Arr(j, 1): Jm = 1
¡@¡@End If
¡@¡@If Jm = 1 Then
¡@¡@¡@¡@AA = Split(Arr(j, 4) & "~", "~")
¡@¡@¡@¡@If Vb > Val(AA(0)) And Vb <= Val(AA(1)) Then GET_WL = T & "_" & Arr(j, 3): Exit For
¡@¡@End If
Next j
End Function

¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
¥t¤@°Ñ¦Ò¡A¦Û­q¨ç¼Æ¡A¼u©Ê¸û¦n¨Ç¡G
¢Ø¢³¡D¤½¦¡¡G=get_wl(B$3:E$11,H4,I4)

TOP

¦^´_ 3# ­ã´£³¡ªL
ÁÂÁ­ã¤j, «D±`¦n¥Î¤Sºë²ªºVBA,
¥¿¦b¥Î­è¾Ç¨ÓªººÊ¬Ýµøµ¡¬ã¨s¤¤,
¹ï©ó¦p¦ó¨Ï¥Î2ºû°}¦C¤¤ªº¬Y¤@°}¦Cªºªí¹F¦¡
¤]¦b¤j¤jªº³o­ÓVBA¤¤±o¨ì¸Ñµª, ÁÂÁÂ!!

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-12-7 10:27 ½s¿è

¦Û¨¥¦Û»y¡G
ÁöµM¦bºÊ¬Ýµøµ¡¤¤, ¥i¥H¬Ý±o¨ì¤Gºû°}¦CarL()
ªº¤@ºûªí¥Ü¦¡¡GarL(0),arL(1),..
¦ý¦bVBA¤¤Óý¤£¯à¨Ï¥ÎarL(0),arL(1),..ªºªí¥Ü¦¡,
(·|¥X²{¯Á¤Þ¶W¥X½d³òªº¿ù»~!)
¬G,¤Gºû°}¦C­Y­nµ¹match¤Þ¥Î,ÁÙ¬O­nÂন¤@ºû°}¦C,
¦üµLªkºë².
(·íµM, ­ã¤jªºVBA«D·íºë²,¤S´£¨Ñ¥t¤@«ä¸ô, ¯uªº«Ü¤£¿ù)

°}¦C¯Á¤Þ.jpg

TOP

¦^´_ 5# yen956



Index ¨ç¼Æ¥i¥H©¿²¤²Ä¤T°Ñ¼Æ¡A¥u¨ú¥X¬Y row ¦¨¬°¤@ºû°}¦C
¦ý¦]­ì¥»¸Ó¨ç¼Æ¬O³]­pµ¹¤u§@ªí Range ¥Î , ¬G¶·ª`·N :
1. ¤Gºû°}¦C row ¼Æ¤£¥i¶W¹L65536
2. ¨ú¥Xªº¤@ºû°}¦C¡Aindex¬O¥Ñ1¶}©l¦Ó¤£¬O0
3. ¤Gºû°}¦C¹L¤j®É¡A¸Ó¨ç¼Æ®Ä²v«Ü®t¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 5# yen956
§Ú±N­ã´£³¡ªLª©¤jªº¦Û­q¨ç¼Æµyµy­×§ï¤F¤@¨Ç¡A
§A¥Î [F8] ¥h Debug ¤@¦¸¡A«K·|¥þ½L©úÁA¤F¡C
§Ú±Nµ²ªG§ï¼g¨ì K Äæ¡A¥H«K§A¶i¦æ¤ñ¹ï¼g¤J­È¡C
  1. '  ¦Û­q¨ç¼Æ¡A¼u©Ê¸û¦n¨Ç¡G  By ­ã´£³¡ªL
  2. '  ¢Ø¢³¡D¤½¦¡¡G=get_wl(B$3:E$11,H4,I4)
  3. Function GET_WL(xA As Variant, Va As Range, Vb As Range) As String
  4.     Dim Arr, AA, j&, Jm%, T$
  5.    
  6.     Arr = xA
  7.     For j = 1 To UBound(Arr)
  8.         If Arr(j, 2) <> "" Then
  9.             Jm = 0:  AA = Split(Arr(j, 2) & "~", "~")
  10.             If Va > Val(AA(0)) And Va <= Val(AA(1)) Then T = Arr(j, 1): Jm = 1
  11.         End If
  12.         If Jm = 1 Then
  13.             AA = Split(Arr(j, 4) & "~", "~")
  14.             If Vb > Val(AA(0)) And Vb <= Val(AA(1)) Then GET_WL = T & "_" & Arr(j, 3): Exit For
  15.         End If
  16.     Next j
  17. End Function

  18. Sub Ex()
  19.     Dim cts As Integer
  20.    
  21.     For cts = 0 To 5
  22.        [K4].Offset(cts) = GET_WL(Range("B$3:E$11"), [H4].Offset(cts), [I4].Offset(cts))
  23.     Next cts
  24. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2015-12-8 10:24 ½s¿è

¦^´_ 3# ­ã´£³¡ªL
½Ð±Ðª©¤j¡A
½Ð°Ý§Ú¦bK11Ä椺¿é¤J  =get_wl(B$3:E$11,H4,I4)
¬°¦ó·|¥X²{ #REF! ¦r¼Ë¡H
¦Û­q¨ç¼Æ¦b Excel ­n¦p¦ó³]©w¨ä©w¸qÅý¥¦¯à³Q¤Þ¥Î¡H
P.S. ¯u¤£¦n·N«ä§Ú¹ï Excel  ¦p¦ó¨Ï¥Î¤º¦b¨ç¼Æ¡A¥H¤Î
       ¦Û­q¨ç¼Æ (Function) ¦p¦ó¦bÄ椺À³¥Î¤è­±©Òª¾¦³­­¡C
ÁÂÁ±z«ü±Ð¡I

TOP

¦^´_ 8# c_c_lai


#REF»PÀx¦s®æ°Ñ·Ó¦³Ãö, À³»P¦Û­q¨ç¼ÆµLÃö, ­YK4¤]¬O¦P¤½¦¡«o¥i¥¿±`Åã¥Ü, ´N­n¬d¬Ý¬Ý­ì¦]?
¦Û­q¨ç¼Æ¤j­P»P¤@¯ë¤½¦¡Ãþ¦ü, ¥u¬O´£¨Ñ°Ñ¼Æ©ÎÀx¦s½d³òÅýµ{¦¡¥h¹Bºâ~~

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2015-12-8 13:21 ½s¿è

¦^´_ 9# ­ã´£³¡ªL
ÁÂÁ±z¦^ÂСA§Ú¨Ï¥Î±z#3¼Óªºµ{¦¡
´úªº¤â³£³n¤F¡AÁÙ¬O¤@¼Ë (µL½×¬O¦b¥ô¦óÄæ¦ì¡A¥]§tJ4)
¨ÌµM¬O #REF!¡C¬Æ¦Ü¼g¤F¨â¤ä´ú¸Õµ{¦¡¡A³£ PASS¡G
  1. Function AddCol(h, i) As String
  2.     AddCol = h + i
  3. End Function

  4. Function AddCol2(w, h, i) As String  '  ¬G·N±a¤J w °Ñ¼Æ
  5.     AddCol2 = h + i
  6. End Function
½Æ»s¥N½X

°ß¿W =get_wl(B$3:E$11,H4,J4) ¤£¦æ¡G

°ÝÃDÀ³¸Ó¥X¦bµ{¦¡½X¤º³¡µLªk°Ñ·Ó¨ì B$3:E$11¡A
¦ý¥Î VBA ÀË´ú¤]§ä¤£¥X°ÝÃDÂI¡C

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD