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

[µo°Ý] ½Ð±Ð Countif ,Vlookup, COLUMN ¨ç¼Æ¹B¥Î°ÝÃD

[µo°Ý] ½Ð±Ð Countif ,Vlookup, COLUMN ¨ç¼Æ¹B¥Î°ÝÃD

±z¦n
ªþ¥óÀɮפ¤¡A§Ú¦pªG¦b¤À­¶(¹L±bÀÉ)´¡¤JÄæ¦ì¡A¸ê®Æ·|¶]±¼¡C
Á|¨Ò¡G
1. IÄæ´¡¤JÄæ¦ì¡A¬°¦ó­ì¥»HÄæ¤ÎKÄæ¸ê®Æ·|¶]±¼¡A¦ý­ì¥»B¡BC¤ÎGÄæ¸ê®Æ¤£·|¶]±¼¡C
2. ¥t¥~§Ú¦bDÄæ´¡¤JÄæ¦ì¡A¬°¦ó­ì¥»GÄæ¡BHÄæ¤ÎKÄæ¸ê®Æ·|¶]±¼¡A¦ý­ì¥»B¡BCÄæ¸ê®Æ¤£·|¶]±¼
§Ú·Q½Ð±Ð¦³¤°»ò¤èªk¡A¥i¥HÅý§ÚÀH·N´¡¤JÄæ¦ì¡A¸ê®Æ¤]¤£·|¶]±¼¡C
ÁÂÁÂ!!

TEST.rar (499.58 KB)

¥»©«³Ì«á¥Ñ p212 ©ó 2014-5-27 11:17 ½s¿è

¦^´_ 1# pupai
1¡BÀx¦s®æH2­ì¤½¦¡ =IF(COUNTIF(¸ê®Æ!$A:$A,$A2),VLOOKUP($A2, ¸ê®Æ!$A:$AT,COLUMN(I2),),"")
IÄæ´¡¤JÄæ¦ì«á·|Åܦ¨=IF(COUNTIF(¸ê®Æ!$A:$A,$A2),VLOOKUP($A2, ¸ê®Æ!$A:$AT,COLUMN(J2),),"")
HÄæ¸ê®Æ¤@©w·|¶]±¼
2¡B¡u¹L±b¡v¤u§@ªí¤§Àx¦s®æB2¿é¤J
=IFERROR(INDEX(¸ê®Æ!Print_Area,MATCH($A2,¸ê®Æ!$A:$A,0),MATCH(B$1,¸ê®Æ!$1:$1,0)),"")
¦V¤U¦V¥k½Æ»s¤½¦¡
3¡B¡u¹L±b¡v¤u§@ªí¤§¡u(·s)³æ»ù¡vÄæ¦ì¤£¦b¡u¸ê®Æ¡v¤u§@ªí¤º¡A¬GµL¸ê®Æ¥i¹ïÀ³¡C
4¡B¡u¹L±b¡v¤u§@ªí»P¡u¸ê®Æ¡v¤u§@ªí¤ºªºÄæ¦ì¦WºÙ§¹¥þ¬Û¦PªÌ¡A«h¥i¹ïÀ³¥X¸ê®Æ¡A¥B¤£¨ü´¡¤JÄæ¦ì¼vÅT¡C
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# p212


    ½Ð±Ð¦pªG¥Î¦¹ºØ¤è¦¡¹ïÀ³
¸ê®Æ¦pªG¤W¤dµ§¥H¤W(©Î§ó¦h)
±N¨Ó¯Á¤Þ¹ï·Ó·|¤£·|«Üªá®É¶¡
ÁÂÁÂ!!

TOP

¦^´_ 1# pupai

¦pªG¸ê®Æ»P¹L±b¸ê®Æªº¼ÐÃD¯à°÷¤@­P¨º´N¤ñ¸û®e©ö

    TEST.zip (962.86 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-5-27 17:25 ½s¿è

¦^´_ 3# pupai
1¡B©Ó¦p4#¶Wª©Hsieh«ü¾Éªº¡G¡u¦pªG¸ê®Æ»P¹L±b¸ê®Æªº¼ÐÃD¯à°÷¤@­P¨º´N¤ñ¸û®e©ö¡v¡A¬O§_½Õ¾ã¤@¤U¨Ï¡u¹L±b¡v¤u§@ªí»P¡u¸ê®Æ¡v¤u§@ªí¤ºªºÄæ¦ì¦WºÙ§¹¥þ¬Û¦P¡C
2¡B2#´£¤Îªº¡u¹L±b¡v¤u§@ªí¤§Àx¦s®æB2¿é¤J=IFERROR(INDEX(¸ê®Æ!Print_Area,MATCH($A2,¸ê®Æ!$A:$A,0),MATCH(B$1,¸ê®Æ!$1:$1,0)),"")³¡¥÷¡A¨ä¡u¸ê®Æ!Print_Area¡v±z¥i¦Û¦æ½Õ¾ã½d³ò¡A©Î¥HOFFSET¤è¦¡©w¸q¦¨°ÊºA½d³ò¡C
3¡B­Ó¤H»{¬°¥Î2#ªº¤èªk¹ï«Ý¤W¤dµ§¥H¤Wªº¸ê®Æ¡A¨ä©Òªá¶Oªº®É¶¡À³ÁÙºâOK¡I(¥Î¡u¹L±b¡v¤u§@ªíªºÄæ¦ì¦WºÙ¥h¹ïÀ³¡u¸ê®Æ¡v¤u§@ªíÄæ¦ì¡A§Y¥iÁקK¨ü´¡¤JÄæ¦ìªº¼vÅT¡C)
¥H¤W»¡©ú¤£ª¾¹ï±z¦³µLÀ°§U¡HÁÙ¬O»~·|±zªº·Qªk(¦p½Õ¾ã¡u¹L±b¡v»P¡u¸ê®Æ¡v¨â¤u§@ªíªºÄæ¦ì¦WºÙ)¡H

TOP

¦^´_ 5# p212

   ¤F¸Ñ
    ÁÂÁ±zªº«ü¾É
   ·P®¦

TOP

¦^´_ 4# Hsieh


    ÁÂÁª©¥D
   ·P®¦!!

TOP

¦^´_ 4# Hsieh


    ½Ð±Ð¶Wª©
¨Ì·Ó§Aªº¤è¦¡
§Ú¦b¡u¸ê®Æ¡v¤u§@ªíªºAÄæ¦ì´¡¤JÄæ¦ì
¡u¹L±b¡v¤u§@ªíªº¼Æ­È´N¶]±¼¤£¹ï
½Ð±Ð¸Ó¦p¦ó­×¹¢

TOP

¦^´_ 8# pupai
±Nªí®æªº­ìÂI©w¸q¦WºÙ
¥u­n¸ÓÀx¦s®æ¤£³Q§R°£À³¸Ó³£¦³®Ä
TEST.zip (963.41 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD