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

[µo°Ý] ¦WºÙ¨Ï¥Î°ÊºA½d³òªº°ÝÃD

[µo°Ý] ¦WºÙ¨Ï¥Î°ÊºA½d³òªº°ÝÃD

¦U¦ì«e½ú¦n¡G
·Q½Ð°Ý¤j®a¦WºÙ¨Ï¥Î°ÊºA½d³ò®É¹J¨ìªº°ÝÃD¡A
¦]¬°A¼Æ¾Ú©¹«á¦³»Ý­n·s¼W¥[¥Ò9¡B¥Ò10µ¥µ¥¤£½T©w¦WºÙ­Ó¼Æ¡A©Ò¥H·Q¥Î°ÊºA½d³òªº¦WºÙ¡A
¥Ø«e°ÊºA½d³ò¼gªk=OFFSET(¤u§@ªí1!$A$3,0,0,COUNTA(¤u§@ªí1!$A$3:$A$10),1)
¦ý¦]¬°¤S¦³¥t¥~ªºB¼Æ¾Ú±µ¦bA¼Æ¾Ú¤U¤è¡A
©Ò¥H·í§Ú¦bA11´¡¤J¥Ò9®É¡A°ÊºA½d³ò¦]¬°§Ú¥Î¤FCOUNTA(¤u§@ªí1!$A$3:$A$10)¡A
¤]´N¤£·|¯Ç¤J¥Ò9¤F.......
§Úª¾¹D¥i¥H§âB¼Æ¾Ú·h¨ì¤u§@ªí2¡AµM«á§ï¦¨COUNTA(¤u§@ªí2!$A$A)¡A
¦ý¦pªG¤£§Æ±æÅÜ°ÊABC¼Æ¾Úªº¥Ø«eÂ\©ñªº®æ¦¡¡A
¦³¥i¯à¨Ï¥Î°ÊºA½d³ò¨Ó¹F¦¨¥i¥H¥ô·N·s¼W¥Ò9¡B¥Ò10¡AµM«áÅý¦b·J¾ãªíªºSUMPRODUCT¤½¦¡¤£¥Î¤@ª½§ï¡C
¥ýÁÂÁ¦U¦ì«e½úªºÀ°¦£¡I¡I

°ÝÃD.zip (10.14 KB)

C39 °}¦C¤½¦¡
C39 =INDEX(B$1:B$33,MATCH($B39&$A39,LOOKUP(ROW($1:$33),ROW($1:$33)/(RIGHT($A$1:$A$33,2)="¼Æ¾Ú"),$A$1:$A$33)&$A$1:$A$33,))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 1# maxchou

°ÊºA½d³ò©w¸q¦WºÙA¼Æ¾Úªº¤½¦¡
=OFFSET(¤u§@ªí1!$A$3,,1,COUNTA(OFFSET(¤u§@ªí1!$A$3,,,MATCH(1,1/ISNUMBER(FIND("¼Æ¾Ú",¤u§@ªí1!$A$3:$A$44)),0)-1,1)),8)
   
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh
·PÁÂHsieh¥S´£¨Ñ¸Ñªk¡A·Q¦b½Ð°Ý¤@¨Ç°ÝÃD¡A¤£ª¾¹DHsieh¥S¥i§_¼·ªÅ¦^ÂСAÁÂÁ¡I
1.FIND¬°¤°»ò¬O·j´M¡y¼Æ¾Ú¡z
2.·j´M¡y¼Æ¾Ú¡z¬O¨S¦³µ²ªG#VALUE!¡A¦A¥ÎISNUMBER¡A¥X²{ªº¬OFALSE¡AµM«á¦A¥Î1/ISNUMBER¡A¨ä¹ê¨ì³o¸Ì§Ú¤w¸g¬Ý¤£À´¤F.........
¤£¦n·N«ä¡AÀ³¸Ó¬O°Ý¤F«Ü¥Õ誺°ÝÃD¡A§Æ±æHsieh¥S¨£½Ì¡I

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD