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

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

¦^´_ 10# olisun


6¤j = B4F/6
¦¨¥æ©ú²Ó¨S¦³~~

TOP

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


   

TOP

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


    ¤£¦n·N«ä¡A¦³Aªº¹ïÀ³¥X¨Ó³£¬OªÅ¥Õ

TOP

¦^´_ 13# olisun

1.¦]¬°­ã¤j¨Ì§Aªº»Ý¨D±N3A§ï¬°4
=VALUE(SUBSTITUTE(LEFT(±±ªí!A2,LEN(±±ªí!A2)-1),"3A",4))
¦ý¦b"¦¨¥æ©ú²Ó"¸ê®Æ¤´¬O3A,©Ò¥H§ä¤£¨ì
§â¤½¦¡¤ºªº©w¸qX§ï¬°©w¸qY§Y¥i
­ì¸ê®Æ©w¸qY=LEFT(±±ªí!A2,LEN(±±ªí!A2)-1)
=LOOKUP("z",IF({1,0},"",VLOOKUP("B"&MATCH(-X,-{999,39,25,12})&"F/"&Y,¦¨¥æ©ú²Ó!$E$2:$J$49,6,)&""))

2.§Ú´£¨Ñªº¨S¦³±N3A§ï¬°4,¦]¬°¨Ì§Aªº¸ê®Æ3 & 4ªº¨®¦ì¼Ó¼h¤@¼Ë(X<=12,X<=25,X<=39),©Ò¥Hª½±µ¥Î3¨Ó§PÂ_

TOP

¦^´_ 12# olisun


"¨®¦ì¸¹½X"¦³¨âÄæ, ¨Sª`·N~~
=IFERROR(LOOKUP(1,-FIND("B"&MATCH(-X,-{999,39,25,12})&"F/"&X&"_",¦¨¥æ©ú²Ó!$E$1:$E$49&"_"&¦¨¥æ©ú²Ó!$G$1:$G$49&"_"),¦¨¥æ©ú²Ó!$J$1:$J$49),"")

TOP

¥»©«³Ì«á¥Ñ olisun ©ó 2020-8-7 10:08 ½s¿è

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

ÁÂÁ¡A¤£¹L¦³Aªº¤@¼Ë¬OªÅ¥Õ¡A¥t¥~½Ð±Ð¤@¤UMATCH(-X,-{999,39,25,12})³o·í¤¤ªº-¬O¬Æ»ò§@¥Î?

TOP

¦^´_ 14# jcchiang


    ©ú¥Õ¡AÁÂÁÂ

TOP

©w¸q¦WºÙ:
X:=--SUBSTITUTE(LEFT(±±ªí!A2,LEN(±±ªí!A2)-1),"3A",4)
Y:'="B"&SUM(N(±±ªí!X<={0,12,25,39,999}))&"F/"&±±ªí!X

C2:
=LOOKUP("z",CHOOSE({1,2,3},"",VLOOKUP(Y,¦¨¥æ©ú²Ó!$E:$J,6,),VLOOKUP(Y,¦¨¥æ©ú²Ó!$G:$J,4,)))
©Î
=IFERROR(IFERROR(VLOOKUP(Y,¦¨¥æ©ú²Ó!$E:$J,6,),VLOOKUP(Y,¦¨¥æ©ú²Ó!$G:$J,4,)),"")

TOP

©w¸qY, §ï¦¨:
="B"&SUM(N(X<={0,12,25,39,999}))&"F/"&LEFT(A2,LEN(A2)-1)

Xl0000179-2.rar (19.53 KB)

¬Ý±o²´³£ªá~~

TOP

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


    ¦¬¨ì¡AÁÂÁ«ü¾É

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD