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

Ãö©ó¤j­±¿nªºVlookup½wºC§ïµ½ªº¥i¯à?

Ãö©ó¤j­±¿nªºVlookup½wºC§ïµ½ªº¥i¯à?

°²³]
A1~A65535 ©ñ°Ó«~¥N½X(­^¼Æ²V¦X¦@¤Q¦ì¼Æ)
B1~B65535 ©ñ»ù®æ (¦ý»ù®æ¥i¯à¯Êº| ©Î ¥X²{¿ù»~­È)
C1~C9999 ÀH¾÷©ñ¤J°Ó«~¥N½X(©Î«È¤H¦Û¦æ¿é¤J)
D1~D9999 Åã¥Ü¹ïÀ³CÄ檺°Ó«~»ù®æ

¥Ø«eªº§@ªk¬O±NA1~B65536©w¸q¬°¦WºÙ©w¸q"°Ó«~»ù®æªí"
¦A§âDÄæ¥ÎVlookupªº¤½¦¡°Ñ·ÓCÄæ¥h§ì»ù®æ

¦ý¦]¬°¸ê®Æ¶q¤jªºÃö«Y? ¤ñ¸û®z¤pªº¹q¸£·|¶]«Ü¤[
½Ð°Ý¦³§ó¦nªº¨ç¼Æ©Î¦Û­q¨ç¼Æ©Î°}¦C¨ç¼Æªº¼gªk¶Ü?

ÁÂÁÂ

¤S
ps.¤p§Ì¤£¬O¬ì¯Z¥X¥Í ¦³¤U­±·Qªk ¤£ª¾¹D¹ï§_ ¦pªGÆ[©À¤£¹ï¤U¥i¯à·|¬Ý¤£À´½Ð©¿²¤ ©Î «ü¥¿ ·P®¦!
VBA¥i¥HÁקK­«½Æ¤j¶qªº¹Bºâ¶Ü?¨Ò¦p¥ý§â¦WºÙ©w¸q"°Ó«~»ù®æªí"ªº¸ê®ÆŪ¨ì°O¾ÐÅ餺µ¥¤è¦¡?
¤£­nÅýDÄæ¨C¤@®æVlookup³£¥h§ì¸ê¤@¦¸¦WºÙ©w¸q"°Ó«~»ù®æªí"ªº¸ê®Æ?
Åý¾ã­ÓDÄæ¨C¤@®æVlookup(©Î¦Û­q¨ç¼Æ)¦@¥Î°O¾ÐÅ餺ªº"°Ó«~»ù®æªí"ªº¸ê®Æ
ÂǦ¹ÅýVlookup(©Î¦Û­q¨ç¼Æ)¥i¥H§ó§Ö³t¹Bºâ?

ÁÂÁÂ

±NA1~B65536©w¸q¬°¦WºÙ©w¸q"°Ó«~»ù®æªí"
­Y¸ê®Æ¨S¦³¨º»ò¦h¡A¸ê®Æªí©w¸q¨º»ò¤j¡A°£®ö¶O°O¾ÐÅé¤]Åý¬d¸ß½d³òÅܤj³t«×ÅܺC¡C

©w¸q"°Ó«~»ù®æªí"¡A±Ä¥Î°ÊºA½d³ò¨Ó©w¸q
=OFFSET($A$2,0,0,counta($A:$A)-1,2)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ jazzcarr ©ó 2017-7-9 22:19 ½s¿è

¨ä¹ê¸ê®Æ¶q ÁÙ¶W¹L655536
»ù®æ¥i¯à¯Êº| ©Î ¥X²{¿ù»~­Èªº¤ñ²v¬ù0.1% (¦]¬°°Ó«~¥i¯à¤U¬[©Î¼È®É°±°â)
¦³¤ñvlookup§ó¦nªº¨ç¼Æ©ÎVBA¨ç¼Æ¶Ü?

TOP

¥Î index(match)
§A¥i¥H¥hgoogle ¤@¤U¥Îªk, §Ú¹ê»Ú¥Î¹Lªº¸gÅç, ¦b¸ê®Æ«Ü¦h®É, ªº½T¤ñ vlookup §Ö

TOP

http://www.exceluser.com/formulas/how-to-use-index-match-part-3.html

You can refer to this page.

TOP

¦n³á ÁÂÁÂ
¥t¥~½Ð°Ý¥i¥H¼g¦¨°}¦C¤½¦¡¶Ü?
·|¤ñ¸û§Ö¶Ü?

TOP

ABCD.rar (342.39 KB) ¦^´_ 1# jazzcarr
[d1:d65536]¥Î¦r¨å­pºâ0.3¬í,¥Îvlookup¨ç¼Æ­pºâ5¬í,¥Îvlookup¨ç¼Æªº¦n³B¬O«O¯d¤½¦¡,cÄæÀH¶ñdÄæ°¨¤W±o¨ìµ²ªG
lmh

TOP

ÁÂÁ mhl9mhl9 ¤j¤j §Úªº¹q¸£¤ñ¸ûºC ¦ý¨â­Ó¤èªkªº®É¶¡ ®t¤F±Nªñ20­¿!!!
¥i¥H½Ð±zµù¸Ñ¤@¤Uµ{¦¡½X¶Ü?(¦r¨å¨º­Ó´N¥i¥H)
²Ä¤@¦¸¬Ý¨ì³o¼Ëªº¤è¦¡
¦ý§ÚÁٻݭnµy§@­×§ï
¤~¯à§@¬°¹ê°È¤Wªº¹B¥Î
·P®¦

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-7-20 03:14 ½s¿è

¦^´_ 7# mhl9mhl9


«D±`·PÁÂmhl9mhl9¤j
­è¦n¦³¦P¼Ë»Ý¨D
´ú¸Õ¤F¤@¤U±zªºÀÉ®×
³o©Û¯uªº¦n¥Î!
¦¬¤U¤F
ÁÂÁÂ:lol
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

¦^´_ 8# jazzcarr
    Set d = CreateObject("scripting.dictionary")''''''«Ø¥ß¦r¨åd(¤£¥Î¤Þ¥Î)
    a = [A1].CurrentRegion.Resize(, 4)''''''''''''''''''§âÀx¦s®æ¸ê®Æ©ñ¶iÅܶqa(¸ê®Æ¥u¦³3Äæ,³]©w4Äæ,³Ì«áÄæ±N¦s©ñµ²ªG)
   For i = 1 To UBound(a, 1)
    d(a(i, 1)) = a(i, 2)''''''''''d(­n§ä¬O°Ñ·Ó)=§ä¨ìªºµ²ªG'''''§â©Ò¦³°Ñ·Ó©Mµ²ªG³£¸Ë¶i¦r¨åd,¤@¤@¹ïÀ³
    For i = 1 To UBound(a, 1)
    a(i, 4) = d(a(i, 3))''''''²Ä4Äæ¬Oµ²ªG,²Ä4Äæ=d(²Ä¤TÄæ)
    Next i
    [d1].Resize(UBound(a, 1)) = WorksheetFunction.Index(a, 0, 4)'''''¥kÃä¬O²Ä4Äæ¥þ³¡¸ê®Æ.§â¥þ³¡¸ê®Æ¿é¤JDÄæ   
    MsgBox Format(Timer - pp, "0.0000")''''''Åã¥Üµ{¦¡°õ¦æ¯Ó®É¦h¤Ö¬í
lmh

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD