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

[µo°Ý] ½ÐÀ°§Ú»¡©ú³o­ÓMATCH¨ç¼Æªº·N¸q

[µo°Ý] ½ÐÀ°§Ú»¡©ú³o­ÓMATCH¨ç¼Æªº·N¸q

¥»©«³Ì«á¥Ñ CyaTu ©ó 2012-6-29 11:27 ½s¿è

§Ú¦b³o­Ó³¡¸¨®æ¤W¬Ý¨ì¥Lªº±Ð¾Çªþ¥ó¡A«K·Q¾ÇµÛ°µ
http://www.wretch.cc/blog/HUNGCHILIN/10258332

200938201035471.rar (4.97 KB)

¦ý½Ð°Ý©w¸q¦WºÙªºNO¡A¨ä¤¤
MATCH(2,1/(DATABASE!$A$1:$A$8<>"")) -1
¬O¤°»ò·N«ä©O?
¬°¦ólookup_value­n´M§ä2
lookup_arrayªº³¡¤ÀÅܦ¨­Ë¼Æªº­ì¦]§Ú¤]¤£©ú¥Õ¡A¯à§_«üÂI¤@¤U¡A·PÁ·PÁÂ!

¥»©«³Ì«á¥Ñ CyaTu ©ó 2012-7-3 11:47 ½s¿è

«z! ·PÁÂHUNGCHILINªº»¡©ú¡A
±zªº³¡¸¨®æÅý§Ú¨ü¯q¨}¦h¡Aª½¦¡ªºÅçÃÒ§Ú¤]¦³¤U¸ü¨Ó¬Ý¹L¡A¦ý¦]¬°¥LªºDATA¤ñ¸û²³æ¡A¥u¦³¤@¦C¡A
¾î¦¡ªºDATA¬OX-Yªí®æªº«¬ºA¡A¤ñ¸û²Å¦X§Ú¥Ø«eªº»Ý¨D¡A¥i¼W¥[¦C¡B¤]¥i¼W¥[Äæ...

§ÚÀˬd¹L¡A³o­ÓÅçÃÒªº³¡¤ÀÀ³¸Ó¨S¦³¥Î¨ìVBA¡A§Ú¤]¤@­Ó­Ó¤½¦¡¥á¨ìÀx¦s®æ¥h¸Ñ¶}¡Aµo²{°ß¦³§ÚªºÀɮפ¤¦WºÙ©w¸q¬°Heighªº¡A¶]¤£¥X­È¡C
¦b±zªº­ìÀɤ¤¡A©R¦W¬°MaxA

¦ý¦]¬°ª½¾î¤£¦P
©Ò¥H±z­ì¥ýªºMaxA¼gªº¬O
=MATCH(2,1/(OFFSET(DATABASE!$1¡G$1,DATA,0)<>""))

§ÚªºHeigh«h§ï¦¨
=MATCH(2,1/(OFFSET(DATA!$A¡G$A,0,toClass)<>""))

¦ýOFFSETªº¨º¤@¬q¨ç¼Æ¡A«ç»ò¤]¶]¤£¥X¡C
§Ú¬Æ¦Ü§âtoClassªº©w¸qª½±µ§ï¬°¼Æ¦r´À¥N¬Ý¬Ý¡Aµ²ªGÁÙ¬O¤@¼Ë¡C
¯uªº»Ý¦U¦ì«e½úÀ°§Ú¸Ñ´b¡AÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ HUNGCHILIN ©ó 2012-7-3 11:00 ½s¿è

Ãö©ó³o­ÓHUNGCHILINªº³¡¸¨®æ ¥u¬O­Ó­Ü®w
¸Ì­±ÁÙ¦³«Ü¦h¤£¦PªºÅçÃÒ½d¨Ò¡A¬Ý³sµ²
http://www.wretch.cc/blog/HUNGCHILIN&category_id=12188609
À³¸Ó¦³ª½ªºDATAªºÅçÃÒ½d¨Ò
½d¨Ò³£¤Ó¤[¤F...«Ü¦h§Ú³£§Ñ¤F·íªìªºÅÞ¿è^^...
ªñ´Á«D±`¦£¸L...­n³Â·Ð¦U¦ì...
¤âÀYÁÙ¦³«Ü¦h¯S®íÅçÃÒ§Þ¥©ÀÉ...­n§ä®É¶¡©ñ...§ä®É¶¡¤U³J...¦³½tªº¤H§ä®É¶¡§â¥¦¥´¯}
ÅçÃÒ½d¨Ò¸Ì­±À³¸Ó¦³´X­ÓÀÉ®×...©w¸q¦³ÁôÂð_¨Ó...¦pªG§ä¤£¨ì«h»Ý­n¥ÎVBAÅã¥Ü

TOP

TEST.zip (3.84 KB)

§Ú§â§ÚªºÀɮ׶ǤW¨Ó¡A½ÐÀ°§Ú¬Ý¬Ý¡C
³o­ÓÂù­«ÅçÃÒ²M³æªº¥\¯à¤¤¡A¦³¥|­Ó¦WºÙ©w¸q¡A
±Ð¾Çªº½d¨Ò¡A¥Lªº¸ê®Æ¬O¾î¦Cªº¡A¦ý§Úªº¸ê®Æ¬Oª½¦Cªº¡C
©ó¬O§Ú¥²¶·§ó§ï¥Lªº¤½¦¡¡C

LIST¤u§@ªí¬O³Ì«á·Q§e²{ªº¼Ë¤l¡F
DATA¤u§@ªí«h¬O§â¸ê®Æ¾ã²z¦C¥Xªº¦a¤è¡C

¥|­Ó¦WºÙ©w¸qªº¤½¦¡¡A§Ú³£¤@­Ó­ÓÀˬd¤F¡A
³Ì«áµo²{©R¦W¬°Heighªº¤½¦¡¶]¥X¨Óªº­È¤£¹ï¡A¦ý§Ú§ä¤£¨ì­ì¦]¡C

½ÐÀ°À°¦£¡A·PÁ¡I
¦]¬°§ÚÁÙ¨SÅv­­¥i¥H¤U¸üªþ¥ó¡A½ÐÅý§Úª¾¹D¤½¦¡ªº°ÝÃD´N¦n¡A¥Î¶K¹Ï©Î¬O¤å¦r³£¦n¡A·PÁ¡I¡I

TOP

¤]¬OMATCH¨ç¼Æªº°ÝÃD¡A

§Ú¥é·Ó¤@¼Óªº±Ð¾ÇÀɮסA§âÄæ¦ì¾îÂઽ¡A¼g¤F¤@­Ó¦¡¤l¡A¦ý«o§ä¤£¨ì¿ù»~¦b­þ¡C



§Úªº»Ý¨D¬O¡Aºâ¥X±qA¦C¦V¥k²¾°Ê3Äæ¡÷¢Ò¦C¤¤¦³¦h¤Ö«DªÅ¥ÕªºÄæ¦ì

(²¾°Ê"¢²"Äæ¡A¬O¤£¤@©wªº¡A¨ì®É­Ô·|¨Ï¥Î©w¸q¥h«ü©w)

TOP

¥i¨Ï¥Î¤u¨ã>¤½¦¡½]®Ö>µû¦ô­È¤½¦¡,¬d¬Ý¾ã­Ó°}¦C¸Õºâ¹Lµ{ªºÅܤơC

TOP

³o¬O°}¦C¤½¦¡­Y¤£¬O¥Î¦b¦WºÙ¤¤,¦bÀx¦s®æ¤¤¤½¦¡­n¥Îctrl+shift+enter¿é¤J

TOP



ª©¥D¡A§R°£0¤FÁÙ¬ONA­C¡C
§Úªº¤½¦¡¬O§_­þ¸Ì¦³¿ù»~?

¥t¥~¤½¦¡¦Û°Ê§ì³Ì«á¤@Ä檺°ÝÃD¡A§Úµo²{
¦pªG¦bHÄ椧«e¥Î´¡¤JÄæ¦ì¡A¤½¦¡´N¥i¥H§PÂ_
¦ý¦pªG¬O§â²M³æ¨º­ÓÂÅÂŪº®Ø¡A©¹¥k©Ô¦Û°Ê¦h¥X¤@Äæ¡A¤½¦¡´NµLªkÀH¤§§ó·s¤F¡C

¦ý­ì¥»ªº±Ð¾ÇÀɮצü¥G³£¬OOKªº¡C

TOP

1,³o¸Ì¤£¥i¥H¥Îºë½T¬d§ä¡A°Ñ¼Æ,0­n¥h±¼¡C
2=MATCH(2,1/(DATA!$C$2:$AV$2<>""))¤w¥i¾A¥Î¨ìAV2,

TOP

·PÁª©¥Dªº»¡©ú¡A
­ì¨Ó´N¬O­n§ä¤£¨ì¡A³o¼Ë§ÚÀ´¤F¡C
§Ú­ì¥ý¤å³¹ªºªþ¥ó¦ü¥G¨S¦³§¨±a¦n¡A©Ò¥H¤S¦A¶Ç¤F¤@¦¸¡A
¦]¬°½d¨Ò¤¤ªºMATCH¨ç¼Æ«á­±¡A¦ü¥G¦³³]¤@­Ó¤£µ¥©óªÅ¥Õªº±ø¥ó¡A
§Ú²q¥Øªº¬OÅý²M³æ¼W¥[ªº®É­Ô¡A¥i¥H¦Û°Ê¤@ª½§ì³Ì«á¤@®æ¡C

¦ý§Ú±N³o­Ó¥\¯àÂର¾î¦¡ªº®É­Ô«o¤@ª½¤£¦¨¥\¡A³s­È³£¦^¶Ç¤£¤F¡A·|¥X²{¢Ü¡þ¢Ï¡C
¯à§_À°§Ú¬Ý¬Ý­þ¥X¿ù¤F©O¡H
ÀɮסG
TEST.zip (3.5 KB)

§Úªº»Ý¨D¦³¨â­Ó
¢°¡DH25¥i¥H¥¿½Tªººâ¥X±qD2¡÷H2¡A¦³¦h¤Ö®æªº­È
¢±¡D·í²M³æ¦V¥k©µ¦ùªº®É­Ô¡A¨Ò¦p¼W¥[¤@Äæ¡A¤½¦¡¥i¥H¦Û°Ê§âH2Åܬ°I2

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