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

jcman

·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)

mapping
¤U¸ü¦¸¼Æ: 6

2016-10-3 22:53

jcman

¥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

rouber590324

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)))

­ã´£³¡ªL

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

rouber590324

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

­ã´£³¡ªL

¦^´_ 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

jcman

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