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

excel ´M§ä¹ïÀ³mapping¥N¸¹

excel ´M§ä¹ïÀ³mapping¥N¸¹

·Q½Ð°Ý¦U¦ì°ª¤â¡A§Ú·Q±Nªþ¥ó¤¤¢ÏÄ椧security type ¹ïÀ³¦Ümapping table¡A¦A±N¹ïÀ³¨ì¤§¢ÓÄ檺¥N¸¹¶ñ¤J¢ÑÄæ¡]type)¡CÁ|¨Ò¨Ó»¡¡A¢ÏÄæ¥ô¦óµ¥©óTWD-CB©ÎTWD-COR©ÎTWD-FIN©ÎTWD-GOVªºsecurity type¡A¦b¢ÑÄæ·|§e²{DB¡A¦Ó¢ÏÄ檺TWD-PS¡A¦b¢ÑÄæ«h·|§e²{PS¡C½Ð°Ý­n¦p¦ó§¹¦¨©O¡H
Book3.zip (54.27 KB)
Jeffrey Chen

¥HDB¬°¨Ò¡A§Ú¼gªº¤½¦¡¬°
IF(A:A={"TWD-GOV","TWD-FIN","TWD-CB","TWD-COR"},"DB","")
¦ý¦p¦¹¥u·|Åã¥Ü SECURITY TYPE ¬° TWD-GOV ¬°DB,¨ä¾lTWD-FIN, TWD-CB ©ÎTWD-COR§¡§ì¤£¨ì¡A½Ð°Ý¸Ó¦p¦ó°µ©O? «ô°U«ô°U
Jeffrey Chen

TOP

DEAR SIR
§Ú¥u·|¥Î¦p¤U¤è¦¡
C2©ñ¦p¤U¨ç¼Æ«á©¹¤UCOPY¨ç¼Æ§Y¥i

=INDIRECT("E"&IF(ISERROR(MATCH(A2,$F$1F$11,0)),0,MATCH(A2,$F$1F$11,0))+IF(ISERROR(MATCH(A2,$G$1G$11,0)),0,MATCH(A2,$G$1G$11,0))+IF(ISERROR(MATCH(A2,$H$1H$11,0)),0,MATCH(A2,$H$1H$11,0))+IF(ISERROR(MATCH(A2,$I$1I$11,0)),0,MATCH(A2,$I$1I$11,0))+IF(ISERROR(MATCH(A2,$J$1J$11,0)),0,MATCH(A2,$J$1J$11,0))+IF(ISERROR(MATCH(A2,$K$1:$K$11,0)),0,MATCH(A2,$K$1:$K$11,0))+IF(ISERROR(MATCH(A2,$L$1:$L$11,0)),0,MATCH(A2,$L$1:$L$11,0))+IF(ISERROR(MATCH(A2,$M$1:$M$11,0)),0,MATCH(A2,$M$1:$M$11,0))+IF(ISERROR(MATCH(A2,$N$1:$N$11,0)),0,MATCH(A2,$N$1:$N$11,0)))
ù

TOP

¢Ñ¢±¡G
=IF(COUNTIF(F$2:N$11,A2),INDEX(E:E,SUMPRODUCT(MAX((F$2:N$11=A2)*ROW(E$2:E$11)))),"")

TOP

SUMPRODUCT¥i°µ¦¹À³¥Î.¨ü¯q¨}¦h·PÁ­㴣³¡ªL¤j¤j
ù

TOP

¦^´_ 5# rouber590324


­ì¤½¦¡¡G
=IF(COUNTIF(F$2:N$11,A2),INDEX(E:E,MAX((F$2:N$11=A2)*ROW(E$2:E$11))),"")
¬°¡e°}¦C¤½¦¡¡f¡A¶·¤TÁä¿é¤J¡A

¥[¤J SUMPRODUCT ¡A´N¤£¥Î¤TÁä¿é¤J¡A¤£¹LÁÙ¬O¤@­Ó°}¦C¹Bºâªº¡I

TOP

¥»©«³Ì«á¥Ñ jcman ©ó 2016-10-7 14:28 ½s¿è

³o¨â¤Ñ¤Ó¦£¡AµLªk¤W¨Ó¬Ý¡CÁÂÁ¦U¦ì¤j¤jªº¦^´_¡A¨ü¥Î¨}¦h¡A§Ú·|¦A¦n¦n¬ã¨s¤@¤U
Jeffrey Chen

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD