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

[µo°Ý] ½Ð±Ð¸Ó¨Ï¥Î¦óºØ¤½¦¡

[µo°Ý] ½Ð±Ð¸Ó¨Ï¥Î¦óºØ¤½¦¡

§Ú¦³¤@­Ó EXCEL ¤½¦¡ ·Q­n½Ð±Ð¤@¤U¤j®a¸Ó¦p¦ó­pºâ¡A¦³¸ß°Ý¹L§O¤H¦ý¬O¥Lµ¹§Úªº¦^ÂЦn¹³¤£¥¿½T¡A¥Lªº«Øij¬O¥s§Ú¨Ï¥ÎLOOKUP ¤½¦¡¡C
¦ý¬O§Ú¹Á¸Õ¤F¤@¬q®É¶¡ºâ¸Õ¤£¥X¨Ó¡A¬O§_¦³¤H¦³¹L³o¼Ëªº¸gÅç¥i¥H´£¨Ñ¤½¦¡¡C

§Ú±NEXCEL ©ñ¸mªþ¥óÀɮפ¤¡C

½d¨Ò
¾P³f©ú²Óªí G5 ¤½¦¡ : E5 ¼Æ¶q ¹ïÀ³"³f«~¤Î¼Æ¶q§é¦©ªí" Table ¼Æ¶q ¡A B5 «È¤á½s¸¹ ¹ïÀ³"³f«~¤Î¼Æ¶q§é¦©ªí" Table ³f«~ * F5

Excel.zip (36.12 KB)

Excel

                 

¦^´_ 1# deepseacastle

G5=LOOKUP($E5,³f«~¤Î¼Æ¶q§é¦©ªí!$C$4:$F$4,OFFSET(³f«~¤Î¼Æ¶q§é¦©ªí!$B$4,MATCH("³f«~"&LEFT(¾P³f©ú²Óªí!$D5),³f«~¤Î¼Æ¶q§é¦©ªí!$B$5:$B$7,0),1,,4))*F5
H5=LOOKUP($E5,«È¤á¤Îª÷ÃB§é¦©ªí!$B$5:$B$8,OFFSET(«È¤á¤Îª÷ÃB§é¦©ªí!$B$4,1,MATCH("ºØÃþ"&LEFT(¾P³f©ú²Óªí!$B5),«È¤á¤Îª÷ÃB§é¦©ªí!$C$4:$F$4,0),4,1))*F5
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# deepseacastle

G5=VLOOKUP("³f«~"&LEFT(D5),³f«~¤Î¼Æ¶q§é¦©ªí!B$5:F$7,MATCH(E5,³f«~¤Î¼Æ¶q§é¦©ªí!B$4:F$4),)*F5
H5=HLOOKUP("ºØÃþ"&LEFT(B5),«È¤á¤Îª÷ÃB§é¦©ªí!$C$4:$F$8,MATCH(F5,«È¤á¤Îª÷ÃB§é¦©ªí!B$4:B$8),)*F5
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¦U¦ì! §Ú©ú¥Õ¤F¡C
                 

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD