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

[µo°Ý] ¦h±ø¥ó¬d§ä

[µo°Ý] ¦h±ø¥ó¬d§ä

¬d§ä.zip (6.03 KB)

¬d¤u§@ªí

¬d.jpg
2015-8-12 12:50

®w¦s¤u§@ªí

®w¦s.jpg
2015-8-12 12:50
·Q«Ø¥ß®w¦s¦©±b§@·~
1.¦b¬d¤u§@ªí¤¤¡A¤Ä¿ï¤è¶ô«á¡A
    ©ó®w¦s¤u§@ªí¤¤ªºB¦æ¬d§ä¸Ó¤Ä¿ï¤è¶ô¥kÃ䪺Àx¦ì
                                   A¦æ¤¤¬d§ä¸Ó¤Ä¿ï¤è¶ô¦P¤@¦Cªº«~¦W
2.§ä¨ì²Å¦X±ø¥ó«á¡A¦b®w¦s¤u§@ªí¤¤ªºD¦æ
   ¶ñ¤J¸Ó¤Ä¿ï¤è¶ôªº»Ý¨D¶q

¹ê»Ú¤W¥ÎIF¤]¥i°µ¨ì¡A¦ý¬O¦]¬°®w¦sªí¹ê»Ú¤W4¤d¦hµ§¡A®É¶¡ªá«Ü¤[
¬O§_¦³§ó§Öªº¤è¦¡¡HÁÂÁ¡I

¥»©«³Ì«á¥Ñ p212 ©ó 2015-8-12 15:45 ½s¿è

¦^´_ 1# dnadark
1.¦b¡u®w¦s¡v¤u§@ªí¤¤¡A¿ï¨ú¤Ï¥Õ¸ê®Æ½d³ò(¨Ò¦p½d¨ÒÀɪºA1:D12)¡A«ö¡uCtrl+Shift+F3¡vÁä¡A¿ï¾Ü¥H¡u³»ºÝ¦C¡v¬°¦WºÙ¡A¶i¦æ©w¸q¦WºÙ¡C
2.¦b¡u¬d¸ß¡v¤u§@ªí¤¤ªºÀx¦s®æB2¿é¤J
=IFERROR(SUMPRODUCT(($A2=«~¸¹)*(INDEX($C2:$M2,MATCH(TRUE,$C2:$M2,0)+1)=Àx¦ì)*¥X®w),"")
¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# p212


    ÁÂÁÂp212ªº¦^ÂСA¤£¹L¦]¬°¸ê®Æ¹L¦h¡A¥Î¨ç¼Æ·|¶]«Ü¤[¡A¥H¥¨¶°°õ¦æ
    ¥¨¶°¤]¥i°µ¨ì¡A¦ý¥Ø«e¼gªº¥¨¶°¤]¬O­n¶]«Ü¤[
     ²{¦b¥¨¶°¦p¤U¡A¬O§_¦³Â²¤Æ®É¶¡ªº¼gªk¡H  ÁÂÁ¡I

lastrow_a = Sheets("®w¦s").Cells(Rows.Count, 1).End(xlUp).Row
lastrow_b = Sheets("¬d").Cells(Rows.Count, 1).End(xlUp).Row
For ax = 2 To lastrow_b '¬d¤u§@ªíªº«~¸¹¦C¼Æ
  For ay = 3 To 12 Step 3 '®Ö¨ú¤è¶ôÄæ
    For  ao = 2 To lastrow_a '®w¦s¸ê®Æ®w
If Sheets("¬d").Cells(ax, ay) = True And Sheets("¬d").Cells(ax, 1) = Sheets("®w¦s").Cells(ao, 1) And Sheets("¬d").Cells(ax, ay + 1) = Sheets("®w¦s").Cells(ao, 2) Then
   Sheets("®w¦s").Cells(ao, 4)=  Sheets("¬d").Cells(ax, 2)
end if
next
next
next

TOP

¦^´_ 3# dnadark


    Ãö³¬¦Û°Ê­pºâ¸Õ¸Õ
Application.Calculation = xlManual ' µ{¦¡½X«e
Application.Calculation = xlAutoma tic ' µ{¦¡½X«á

TOP

¦^´_ 4# starry1314


    starry1314 ¤j~ÁÂÁ§Aªº«Øij¡A¦ý¬O¬d¤u§@ªíªº¨ç¼Æ¡A¤w¸g³£§ï¥Î¥¨¶°¼g¤F¡A
    ¨ÃµL¨ç¼Æ¡A©Ò¥H¹ê»Ú¥[¤W°±¤î¹Bºâªºµ{¦¡½X¤´Âªá«Ü¤[®É¡K¡K

TOP

¦^´_ 5# dnadark

sheet ¬d¸ß ³o±iªí¨ç¼Æ¦p¦³¥|¤d¦hµ§ ·|«D±`ªº¦h..¥Bvlookupµ¥§t¼Æ¤@¦h·|ºC
Àx¦ì,®w¦s ¨âÄ檺¨ç¼Æ±N·|¦³¦Ü¤Ö¤@¸U¤»¤d¦h­Ólookupµ¥¨ç¼Æ

TOP

¦b¼Æ²Õ¤¤°õ¦æÀ³¯à§ÖÂI.

Sub zz()
a = Sheets("®w¦s").[a1].CurrentRegion
b = Sheets("¬d").[a1].CurrentRegion
For ax = 2 To UBound(b)  'lastrow_b '¬d¤u§@ªíªº«~¸¹¦C¼Æ
  For ay = 3 To 12 Step 3 '®Ö¨ú¤è¶ôÄæ
    For ao = 2 To UBound(a)  'lastrow_a '®w¦s¸ê®Æ®w
      If b(ax, ay) = True And b(ax, 1) = a(ao, 1) And b(ax, ay + 1) = a(ao, 2) Then
        a(ao, 4) = b(ax, 2)
      End If
    Next
  Next
Next
Sheets("®w¦s").[a1].Resize(UBound(a), UBound(a, 2)) = a
End Sub

TOP

¦^´_ 7# ikboy


    ÁÂÁÂikboy¤j~~Àþ¶¡´N¶]§¹¤F¡I¡I
    ÁÂÁ¡I

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD