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

¦p¦ó±qºô­¶¤U¸ü¸ê®Æ¦Û°ÊÂà´«¦¨©Ò»Ý­n¸ê®Æ

¦p¦ó±qºô­¶¤U¸ü¸ê®Æ¦Û°ÊÂà´«¦¨©Ò»Ý­n¸ê®Æ

¥»©«³Ì«á¥Ñ asky2346 ©ó 2011-6-24 15:22 ½s¿è

¦U¦ì¦n
¤p§Ì³Ìªñ¤~±µÄ²EXCEL µo²{¦³«Ü¦hªF¦è³£¤£·|¥Î
±æ¦U¦ì¯à«ü±Ð¤@¤U¤p§Ì


¬õ°é¤¤¬O±qºô­¶¤U¸ü¸ê®Æ
§Ú·Q§âY¦Cªº¤¤¥æ¦r §Y¤@¤G¤T  ¦Û°ÊÂà´«¦¨ B2,C3µ¥ªº­^¤å¦r¥i¥H¶Ü

ÁÙ¦³¤@­Ó°ÝÃD ±qºô­¶±o¨Óªº¸ê®Æ ·|¦Û°Ê§â³Ì·sªº¸ê®Æ©ñ¨ì³Ì³»
³o¼Ë¦³¤°»ò¤èªk¥i¥H§â·s±o¨Óªº¸ê®Æ ¦Û°ÊÂà¤J¨ì¹ïÀ³ªº®æ¼Æ¤¤

½Ð¦U¦ì¦h¦hÀ°¦£

¬O®Ú¾Ú¤°»òªº±ø¥ó©ñ¶i¥hªº©O?¦Ó"A,B,C"¤S¥Nªí¤°»ò·N«ä©O?
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

"¤@" = "A"
"¤G" = "B"
"¤T" = "C"
¥H¤WA,B,C ¸ò¤@,¤G,¤T ¥u¬O¨Ò¤l

TOP

B2°}¦C¤½¦¡
=INDEX(CHAR(ROW($65:$73)),MATCH(INDEX($Y:$Y,B1+1),TEXT(ROW($A$1:$A$9),"[DBNum1][$-404]G/³q¥Î®æ¦¡"),0))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

­º¥ýÁÂÁÂHsieh¤j¤jªº¦^ÂÐ

§Ú«ö§Aªº°µªk¦bB2¿é¤J¤F¬ÛÃöªº¤½¦¡«á
ª½±µ¿é¤J¥X²{¤F¥H¤U±¡ªp
¥[¤F¤¤¬A¸¹«á¥X­±¤F#N/A
ÁÙ¦³·Q°Ýªº¬O³o¥y[DBNum1][$-404]G/³q¥Î®æ¦¡"),0))¤j·§¬O¤°»ò·N«ä


ÁÂÁÂ

TOP

¦^´_ 5# asky2346
1.  ¥H°}¦C¤½¦¡¿é¤Jªkctrl+shift+enter§¹¦¨¿é¤J
2.  YÄæ¸ê®Æ¥²¶·¬O°ê¦r¤j¼g¼Æ¦r¤~¦æ
=INDEX(CHAR(ROW($65:$73)),MATCH(INDEX($Y:$Y,B1+1),TEXT(ROW($A$1:$A$9),"[DBNum1][$-404]G/³q¥Î®æ¦¡"),0))

  ROW($A$1:$A$9)
±N¶Ç¦^1,2,3,....9³o¤E­Ó¼Æ¦rªº¼Æ¦C  

TEXT(ROW($A$1:$A$9),"[DBNum1][$-404]G/³q¥Î®æ¦¡")
¥ÎTEXT¨ç¼Æ±N1~9³o¨Ç¼Æ¦rÂন°ê¦r

INDEX($Y:$Y,B1+1)
°Ñ·Ó¨ìYÄ檺²Ä?¦C¦ì¸m¡AB1+1=2¡A©Ò¥H·|±o¨ìY2ªº¤º®e

MATCH(INDEX($Y:$Y,B1+1),TEXT(ROW($A$1:$A$9),"[DBNum1][$-404]G/³q¥Î®æ¦¡"),0)
¶Ç¦^¦b°ê¦r¼Æ¦C¤¤§ä¨ìY2ªº¦ì¸m¡A¦p:¤@´N·|¶Ç¦^1¡A¤G´N¶Ç¦^2

CHAR(ROW($65:$73))
±o¨ì65~73³o¨Ç¦r¤¸½Xªº¹ïÀ³­^¤å¦r¥À

³Ì«á¥ÑINDEX§ä¨ì­^¤å»P°ê¼Æ¦rªº¹ïÀ³
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÙ¦³ÂI°ÝÃD °²¦p§Ú¤U¸üªº¸ê®Æ¤£¬O"¤@","¤G","¤T" ¦Ó¬O¨ä¥L¤¤¤å¦r
¥i¥H¹ïÀ³¨ì¬ÛÃöªº­^¤å¦r¶Ü
¨Ò¦p¦bY¦Cªº¬O"¿é","¾Æ" ¥i¥H¹ïÀ³"W"©M"L"ªº¶Ü

TOP

B2=IF(INDEX($Y:$Y,COLUMN())="¿é","L",IF(INDEX($Y:$Y,COLUMN())="Ĺ","W",""))
¦V¥k½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh
ÁÂÁ¤j¤j¸Ñ¨M¤F§Úªº°ÝÃD
¥i¬O§ÚÁÙ¹J¨ìÂI§xÃø¤£·|¸Ñ¨M

¥i¥H§â¸ê®Æ¹ïÀ³¨ì¬ÛÃöªº®æ¼Æ¶Ü
¦p¤U¹Ï
B1,C1,D.....1 ¸òW8,W7,W6....ªº¼Æ­È¬O¤@¼Ë®É
¤~¹B¦æ§A¦b¤W­±©Ò»¡ªººâ¦¡ ¥i¥H¶Ü

TOP

=LEFT(INDEX($Y:$Y,MATCH(B1,$W:$W,0),),1)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD