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

[µo°Ý] ¦p¦ó¦b¬d§ä¹ïÀ³¤¤¥[¤J¹J¨ì3A¡B13A¡B23A...¿ëÃѬ°4¡B14¡B24...

[µo°Ý] ¦p¦ó¦b¬d§ä¹ïÀ³¤¤¥[¤J¹J¨ì3A¡B13A¡B23A...¿ëÃѬ°4¡B14¡B24...

¥»©«³Ì«á¥Ñ olisun ©ó 2020-8-5 13:22 ½s¿è

¦b¦¨¥æ©ú²Ó¤¤¡A¦pªG¦¨¥æ©Ð«Î¦³ÁʶR¨®¦ì¡A«h·|¦b¦¨¥æ©ú²Ó¦Û°Ê§ì¨ú¸Ó¨®¦ì©Ò¹ïÀ³ªº©Ð«Î¤á§O¡C §Ú³]©w=VLOOKUP(IF(VALUE(LEFT(±±ªí!B19,LEN(±±ªí!B19)-1))<=12,"B4F/"&LEFT(±±ªí!B20,LEN(±±ªí!B20)-1),IF(VALUE(LEFT(±±ªí!B19,LEN(±±ªí!B19)-1))<=25,"B3F/"&LEFT(±±ªí!B20,LEN(±±ªí!B20)-1),IF(VALUE(LEFT(±±ªí!B19,LEN(±±ªí!B19)-1))<=39,"B2F/"&LEFT(±±ªí!B20,LEN(±±ªí!B20)-1),"B1F/"&LEFT(±±ªí!B20,LEN(±±ªí!B20)-1)))),¦¨¥æ©ú²Ó!$E$2:$AF$51,28,FALSE)¡C
¦]¬°®ø¶OªÌ¹ï4¦³§Ò¿Ð¡A¦]¦¹¹J4´N·|¥H3A¨Ó¥N´À(3A=4¡B23A=24¡B33A=34¡B43A=44)¡A¦ý¬O¹J¨ì3A¡B23A¡B33A¡B43A¡A´N·|¦³°ÝÃD¡C½Ð°Ý­n¦p¦ó³]©w·í¹J¨ì3A...µ¥¡A¦Û°Ê¿ëÃѬ°4...
test-2.rar (19.52 KB)

X©w¸q¦WºÙ§ï:
=VALUE(SUBSTITUTE(LEFT(±±ªí!A2,LEN(±±ªí!A2)-1),"3A",4))

C2¤½¦¡:
=LOOKUP("z",IF({1,0},"",VLOOKUP("B"&MATCH(-X,-{999,39,25,12})&"F/"&X,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&""))
©Î
=LOOKUP("z",IF({1,0},"",VLOOKUP("B"&5-MATCH(X,{0,13,26,40})&"F/"&X,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&""))

TOP

¦^´_ 1# olisun


§â¦WºÙX=VALUE(LEFT(±±ªí!G5,LEN(±±ªí!G5)-1))¼W¥[§PÂ_
X=IF(RIGHT(LEFT(±±ªí!G5,LEN(±±ªí!G5)-1),1)="A",VALUE(LEFT(±±ªí!G5,LEN(±±ªí!G5)-2)),VALUE(LEFT(±±ªí!G5,LEN(±±ªí!G5)-1)))
¦ì¸m½Ð¦Û¦æ½Õ¾ã

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-8-5 15:33 ½s¿è

43A = 44
²Ä¤@­Ó4ÁÙ¬O¦P¼Ë¦³§Ò~~
¥Î3A3A ¤S¦³ÂI©Ç

¦ó¤£±N4¥þ¥Î¥t¤@­^¤å¦r»PABFÁ׶}¨ú¥N,¨Ò¦p:V
4=V
14=1V
44=VV

TOP

¥»©«³Ì«á¥Ñ olisun ©ó 2020-8-5 17:20 ½s¿è

¦^´_ 2# ­ã´£³¡ªL

¨ü±Ð¤F¡A­ì¨ÓÁÙ¥i¥H¨Ï¥Î¨ú¥N³o­Ó¤èªk¡A¥t¥~½Ð°Ý·í¤¤ªºZ©MB­Ó¥Nªí¬Æ»ò?

TOP

¥»©«³Ì«á¥Ñ olisun ©ó 2020-8-5 17:21 ½s¿è

¦^´_ 3# jcchiang

·PÁ¡A§Ú´N¬O³o­ÓÂI·d¤£²M·¡

TOP

¦^´_ 4# ­ã´£³¡ªL


    ÁÂÁ¡A¦]¬°«Ø¿v®vªº½Ð·Ó¹Ï½s¸¹´N¬O³o¼Ë½s¡A¦pªG¥ÎVV¡A¤j®a·|·d²V

TOP

¦^´_ 5# olisun

"z" µ¹lookup·í¯Á¤Þ, °£¿ù¥Î, ¤£¦n¸ÑÄÀ,
"B"  >> B3F/13

¤£µM¥Î³o­Ó:
=IFERROR(VLOOKUP("B"&MATCH(-X,-{999,39,25,12})&"F/"&X,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&"","")

TOP

¦^´_ 8# ­ã´£³¡ªL

¤£¦n·N«ä¡A¤GºØ¦¡¤l®M¶i¥h¡AC2Åܦ¨ªÅ¥Õ

TOP

¥»©«³Ì«á¥Ñ olisun ©ó 2020-8-5 18:26 ½s¿è

¦^´_ 8# ­ã´£³¡ªL

©êºp¡A¦¡¤l¨S°ÝÃD¡A¬O§Ú§Ë¿ù¤F¡C¤£¹L¦¨¥æ©ú²Ó6¨®¡B23A¬O¦³½æ±¼ªº¡A¦ý¬O¹ïÀ³µ²ªG¬OªÅ¥Õ

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD