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

[µo°Ý] ¦h±ø¥ó¤§§PÂ_

[µo°Ý] ¦h±ø¥ó¤§§PÂ_

¦h±ø¥ó¤§§PÂ_.rar (4.85 KB)

¦h±ø¥ó¤§§PÂ_¡A·q½Ð¸Ô¨£À£ÁYÀÉ¡C

·PÁ±zªº«ü¾É¡AÁÂÁ¡I

¦^´_ 1# andyhang


    E24=INDEX($B$5:$F$5,,IF(ISERROR(MATCH($C24,OFFSET($A$1,MATCH($B24,$A$2:$A$4,0),1,,4),0)),5,MATCH($C24,OFFSET($A$1,MATCH($B24,$A$2:$A$4,0),1,,4),0)))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

C9=IF(ISERROR(OFFSET($A$1,4,MATCH($C$7,OFFSET($A$1,MATCH(B7,A2:A4,0),1,1,4),0))),F5,OFFSET($A$1,4,MATCH($C$7,OFFSET($A$1,MATCH(B7,A2:A4,0),1,1,4),0)))
YOYO

TOP

¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-4-12 13:04 ½s¿è

c9=INDEX($B$5:$F$5,IF(SUMPRODUCT((B7=$A$2:$A$4)*(C7=$B$2:$E$4)*({1,2,3,4})),SUMPRODUCT((B7=$A$2:$A$4)*(C7=$B$2:$E$4)*({1,2,3,4})),5))
¦pªG§âF5²¾¨ìA5
c9=INDEX($A$5:$E$5,SUMPRODUCT((B7=$A$2:$A$4)*(C7=$B$2:$E$4)*{1,2,3,4})+1)

TOP

³£¬O«Ü¦³¾Ç°Ýªº¸Ñªk¡A¦¬¤U¡A¦n¦n¬ã¨s¥h¡A·PÁÂHsieh ¡Byanto913 ¡BANGELA ¤T¦ì¤j¤jªº«ü¾É¡AÁÂÁ¡I

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST() '¡ô
Dim Brr, Crr, Y, i&, j&, T$, K%
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = [A2:E5]
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¤¤
K = UBound(Brr)
'¡ô¥OKÅܼƬOBrr°}¦C³Ì¤j¯Á¤Þ¦C¸¹
For i = 1 To K
   For j = 2 To UBound(Brr, 2)
      T = Brr(i, 1) & "/" & Brr(i, j)
      Y(T) = Brr(K, j)
   Next
Next
'¡ô³]¶¶°j°é±N¸ê®Æ°Ï²Õ¦X¦r¦ê·íkey,item¬OÃþ§O,¯Ç¤JY¦r¨å¤¤
Brr = Range([C24], [B65536].End(xlUp))
'¡ô¥OBrrÅܼƭ«·s©w¸q¬O ¤Gºû°}¦C,´«¸Ë·sÀx¦s®æ­È
ReDim Crr(1 To UBound(Brr), 1 To 1)
'¡ô«Å§iCrr°}¦C¬O ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr,¾î¦V½d³ò1~1¯Á¤Þ¸¹
For i = 1 To UBound(Brr)
   T = Brr(i, 1) & "/" & Brr(i, 2)
   If Y(T) = "" Then
      Crr(i, 1) = "EÃþ"
      Else
         Crr(i, 1) = Y(T)
   End If
Next
'¡ô³]¶¶°j°é±N¥Ø¼Ð°Ï²Õ¦X¦r¦ê¬dY¦r¨å±o¨ìitem­È±a¤JCrr°}¦C¤¤,
'¦pªG¬dY¦r¨å¨äitem­È¬O"",Crr°}¦C«h¼g¤J"EÃþ"

[E24].Resize(UBound(Crr)) = Crr
'¡ô¥OCrr°}¦C­È¼g¦pµ²ªGÀx¦s®æ¤¤
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD