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

[µo°Ý] Index and Match VBA

[µo°Ý] Index and Match VBA

¦U¦ì¤j¤j

¦pªG§Ú"®w¦s"¨½¦bAÄæ©MAGÄæ ¨â­Ó¦X²Åªº±ø¥óªº±¡ªp¤U¦b´M§ä"WORKING" §ä¦^¬Û¹ïÀ³ªº¸ê®Æ,

§ÚÀ³ÂI¼gVBA CODE ©O???

ÁÂÁÂ

¬¡­¶Ã¯1.rar (129.99 KB)

Âù±ø¥ó--¥Îindex+match--¦æ¤£³q:
Sub ¸ü¤J®w¦s()
Dim R&, C&, i&, j%, Arr, Brr, Crr, xD, U&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([working!AF1], [working!A1].Cells(Rows.Count, 1).End(xlUp))
For i = 2 To UBound(Arr)
    xD(Arr(i, 1) & Arr(i, UBound(Arr, 2))) = i
Next i
'----------------------------------
Brr = Range([®w¦s!AG1], [®w¦s!B1].Cells(Rows.Count, 1).End(xlUp))
ReDim Crr(1 To UBound(Brr), 1 To UBound(Brr, 2) - 2)
For i = 5 To UBound(Brr)
    U = Val(xD(Brr(i, 1) & Brr(i, UBound(Brr, 2))))
    If U = 0 Then GoTo 101
    For j = 1 To UBound(Crr, 2)
        Crr(i - 4, j) = Arr(U, j + 1)
    Next j
101: Next i
'----------------------------------
[®w¦s!C5:AF5].Resize(UBound(Crr)) = Crr
End Sub


Xl0000210(®w¦sªí§ó·s).rar (85.48 KB)


========================

TOP

¥»©«³Ì«á¥Ñ mdr0465 ©ó 2020-6-25 21:39 ½s¿è

¦^´_ 2# ­ã´£³¡ªL

ª©¥D:­ã´£³¡ªL, ¯uªº¦n¦hÁ§AªºÀ°¦£,§A¹ê¦b¤ÓµR§Q¤F:handshake

§Ú¦³¹Á¸Õ¤F¸Ñ·í¤¤ªº¼gªk,¦ý¦]¬°§Ú¥u¬O½Æ¨î¤@³¡¥÷µ¹§A, ¦ý¦]¬°¹Bµ{¦¡®É·|¦³¤@¨Ç¸ê®Æ·|§R°£¤F,

¤p§Ì¤£¤~,¤£¯à¦Û¦æ­×§ï,¥Oµ{¦¡¹B¦æ¶¶§Q...

§ÚÁÙ¹J¨ìªº°ÝÃD¦³2ÂI

1. ¦p¦ó¸Ñ¨M¤£§R°£¨C¤@­Ó²£«~ªº¹w³]¥[Á`
2. ¦p¦ó¸Ñ¨MAH:AOÄæ¦ìªº½Æ¨î

ÁÂÁÂ

SAMPLE.rar (307.04 KB)

TOP

¦^´_ 3# mdr0465

§PÂ_"¦X­p"¦æ--¢äÄæ¤å¦r³Ì¥k¤@¦r¬O§_¬°":"©Î"¡G"---²Åé¦r¤S¥þ¥b«¬²V¦X---¥u¯à§ì³o³W«h¡G
Xl0000211(®w¦sªí§ó·s).rar (159.1 KB)

µ{¦¡½XÅܽÆÂø¤F¡A§Æ±æ¬Ý±oÀ´¡ã¡ã

TOP

¦^´_ 4# ­ã´£³¡ªL


­ã´£³¡ªLª©¥D

¯uªº¸U¤G¤À·PÁ§AªºÀ°¦£¡AÁÂÁ§A

§Ú·|§V¤O¤F¸Ñ·í¤¤ªºµ{¦¡

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD