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

[­ì³Ð] ¤À¨É - °ÊºAÅã¥Ü¹Ï¤ù(¤£¥ÎVBA)

[­ì³Ð] ¤À¨É - °ÊºAÅã¥Ü¹Ï¤ù(¤£¥ÎVBA)

1. ¦b [Sheet1]  A2 ¿é¤J¹Ï¤ù¦WºÙ; ½Õ¾ãB2ªº¤j¤p©M¹Ï¤ù¤j¤p, ¤£Åý¹Ï¤ù¶W¥X¸Ó®æ;


2. ­«½Æ¨BÆJ 1 ¦b A3, A4, ....; B3, B4, .... ¿é¤J¦WºÙ©M´¡¤J¹Ï¤ù

3. ¿ï©wÀx¦s®æ[B2], «ö¤U½Æ»s«ö¶s(or ctrl+c), ¤Á´«¨ì[Sheet2], «ö¤U"¶K¤W"«ö¶s¤Uªº½bÀY, ¿ï¾Ü Linked Picture (¤¤¤åÀ³爲"³sµ²ªº¹Ï¤ù")


4. ¦b [sheet2]¤¤¿é¤J¤½¦¡, B1 =MATCH(A1, Sheet1!$A$2:$A$5,0); C1 =OFFSET(Sheet1!$B$1,Sheet2!$B$1,0)
<½Ðª`·N¤£­n¼g¿ù¤½¦¡¤¤ªºSheet1! ©MSheet2!>

5. ¤Á´«¨ìFormulas "¤½¦¡"­¶¦¸¤U, §ä¨ìDefine Name "©w¸q¦WºÙ" (¤¤¤å¦WºÙ¤£½T©w), ¦p¤U¹Ï
¦b c ¤¤¿é¤J¦WºÙ: "dyn_pick_pic" (¤£¥Î¿é¤J¤Þ¸¹)
¦b d ¤¤¿é¤J "=OFFSET(Sheet1!$B$1,Sheet2!$B$1,0)"  (¤£¥Î¿é¤J¤Þ¸¹, ¥i¥H±qC1 ¤¤½Æ»s¤å¦r)


6. ¿ï©w¨BÆJ3¤¤¶K¤Wªº¹Ï¤ù, ¦b Formula Bar ¤¤¿é¤J ¨BÆJ5©w¸qªº¦WºÙ dyn_pick_pic


7. ¹Ï¤ù±N·|¨Ì[A1] ¦ÓÅܤÆ
7.JPG

ªþ¥ó
dyn_pick_pic_Sonny_Su.rar (326.88 KB)

¶È¦b Excel 2010 ´ú¸Õ¹L, ¨ä¥Lª©¥»¥¼ª¾. ½Ð´ú¸Õ¨ä¥Lª©¥»,¨Ã§iª¾.
ss

¦^´_ 1# sunnyso

¤j¤j~
½Ð°Ý¤@¤U²Ä¥|¨BÆJ
4. ¦b [sheet2]¤¤¿é¤J¤½¦¡, B1 =MATCH(A1, Sheet1!$A$2:$A$5,0); C1 =OFFSET(Sheet1!$B$1,Sheet2!$B$1,0)
<½Ðª`·N¤£­n¼g¿ù¤½¦¡¤¤ªºSheet1! ©MSheet2!>

¬O­n¦bB1¿é¤J¶Ü?

¥t¥~²Ä¤­¶µ
­n¦b­þ¤@®æ¸Ì­±¿é¤J?

¤£¦n·N«ä~²Ä¥|¶µ«á´N¦³ÂI¦Ç¤F~
³Â·Ð½Ð«ü±Ð¤@¤U~ÁÂÁÂ!

TOP

¦^´_ 2# kengo

¬Oªº
ss

TOP

¦^´_ 3# sunnyso


¤j¤j~
¤£¦n·N«ä~¦A¥´ÂZ¤@¤U

§Ú¦b²Ä¥|¨BÆJ¿é¤J


Åã¥Ü»yªk¿ù»~

¤U¹Ï¬O§Ú¦bSheet1¿é¤Jªº¸ê®Æ


²Ä¥|¨BÆJ¬O­þÃä¥X¿ù¤F?

TOP

¦^´_ 4# kengo

C1 =OFFSET(Sheet1!$B$1,Sheet2!$B$1,0)
¬O«ü¦bC1³oÄæ¿é¤J=OFFSET(Sheet1!$B$1,Sheet2!$B$1,0)

±µµÛ°µ²Ä5¨BÆJ°µ©w¸q¦WºÙ
°Ê©À¦¨¦]¿n¦]¦¨ªG by huijuang

TOP

¦^´_ 5# huijuang


:P ­ì¨Ó~
«D±`·PÁÂ~
¤U¯Z«á¦b¦^®a¸Õ¸Õ

ÁÂÁ¤j¤j

TOP

¦^´_ 1# sunnyso

¤j¤j
½Ð°Ý²Ä¤­­Ó¨BÆJ
­n¦b­þ¤@®æ³]©w?
ÁÙ¬O­nÂI¹Ï¤ù³]©w?

¨D¸Ñ~ÁÂÁÂ!

TOP

¦^´_ 1# sunnyso

ÁÂÁ¤À¨É,,¥¿¦n»Ý­n..¨Ó¬ã¨s¬Ý¬Ý..^^

TOP

¤£¥Îvba¡A¦Ó¥Î³o¼Ëªº¤è¦¡¡A´N¥i¥H§K°£±¼«Ü¦h¤£¥²­nªº³Â·Ð¤F~

TOP

ªO¥D¤j¤j

ÁÂÁ§Aªº¤À¨É «Ü¦n¥Î ¦ý¦³¤@­Ó°ÝÃD·QÄ~Äò½Ð±Ð½Ð±Ð~

¦pªG¬O¦³4²Õ¹Ï¤ù¦b¦P­Ó¬¡­¶¤¤­n®Ú¾Ú¤£¦Pªº«ü¥O°µÅܤƠ ¤½¦¡"©w¸q¦WºÙ"¥u¯à¥´dyn_pick_pic¶Ü??

¤w¸g¸Õ¥Î¹L¥Î¨ä¥Lªº¦WºÙ¨Ó©w¸q¦ý¦n¹³¨S¦³¿ìªk  ¦pªG¥u¥Î³o²Õ¦WºÙ¹Ï¤ùÅܤƤS¥u·|Åã¥Ü¥X²Ä¤@²Õ¹Ï¤ùªº±¡ªp

¤£ª¾¹D¦³¤°»ò¿ìªk¥i¥H¸Ñ¨M  

¨D¸Ñ~ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD