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

[µo°Ý] ·j´M49Äæ*10¦C¤ñ¤j¤p

[µo°Ý] ·j´M49Äæ*10¦C¤ñ¤j¤p

¥»©«³Ì«á¥Ñ Airman ©ó 2019-4-20 09:04 ½s¿è

´ú¸ÕÀÉ~ «e3¤p_0420.rar (27.22 KB)

³Æµù¡J
1_½Ð¥HMS2003ª©ªº¨ç¼Æ¸ÑÃD¡C
2_¤£¼W¥[»²§UÄ欰¸ÑÃD­ì«h¡C
3_¦USheetªº¤º®e¬Ò¬°¼ÒÀÀ¡C


»Ý¨D¡J
¥D±ø¥ó=·j´M$B7:$AX7ªº«e¤T¤p¼Æ¦r(¤¤¦¡±Æ¦W)¡F
¦pªG¥D±ø¥óªº«e¤T¤p¼Æ¦r¬O¬°³æ1­Ó®É¡A«h±N¨ä¦b$B1:$AX1ªº¦PÄæ¹ïÀ³¼Æ¦r¨Ì§Ç¶ñ¤JAZ7¡JBB7¡C

°Æ±ø¥ó1=¦pªG¥D±ø¥ó¨ä¤¤ªº¬Y±Æ¦W¼Æ¡A¦³2­Ó(§t)¥H¤W®É¡A«h¿ï¨ú¨ä¦b$B71:$AX71ªº¦PÄæ¹ïÀ³¼Æ¦r¸û¤p¤§±Æ¦WªÌ¡A
¨Ã±N¿ï¨ú«áªº«e¤T¤p¼Æ¦r¤§¦b$B1:$AX1ªº¦PÄæ¹ïÀ³¼Æ¦r¨Ì§Ç¶ñ¤JAZ7¡JBB7¡C

°Æ±ø¥ó2=¦pªG¥D±ø¥óªº¨ä¤¤¬Y±Æ¦W¼Æ¡A¦³2­Ó(§t)¥H¤W¡A¥B¨ä¦b$B71:$AX71ªº¦PÄæ¹ïÀ³¼Æ¦r¥ç¬Û¦P®É¡A«h±N¸Ó¬Û¦P±Æ¦WªÌ¥þ¿ï¨ú¡A
¨Ã±N¿ï¨ú«áªº«e¤T¤p¼Æ¦r¤§¦b$B1:$AX1ªº¦PÄæ¹ïÀ³¼Æ¦r¨Ì§Ç¶ñ¤JAZ7¡JBB7¡C

¥H¤W   Àµ½Ð¦U¦ì¥ý¶i´f¤©½ç±Ð!ÁÂÁÂ!

°Æ±ø¥ó2, ¤½¦¡Ãø???

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2019-4-20 12:06 ½s¿è

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

­ã¤j:
³o¬O«e3¤j(§t71¦C¤]¬O¨ú¦PÄæ¹ïÀ³¼Æªº¸û¤jªÌ)ªº°}¦C¤½¦¡~
=SMALL(IF(MAX((LARGE((MATCH(OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49),OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49),)=COLUMN($B:$AX)-1)*OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49),COLUMN(A1))=OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49))*(OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49)/1%%+$B$71:$AX$71))=OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49)/1%%+$B$71:$AX$71,$B$1:$AX$1),MOD(ROW(A1),7))
¦p¦ó§ï¦¨«e3¤p(§t71¦C¤]¬O¨ú¦PÄæ¹ïÀ³¼Æªº¸û¤pªÌ)ªº¤½¦¡
¤p§Ì¦³±NMAX§ï¬°MIN¡FLARGE§ï¬°SMALL¡A¦ýµª®×Åܦ¨ªÅ¥Õ^^"
¤£ª¾¹DÁÙ­n­×§ï­þ¸Ì?
·q½Ð«ü±Ð©M½ç¥¿!ÁÂÁ±z^^

TOP

AZ7:AZ12 ¤»®æ°Ï°ì°}¦C:
=TEXT(SUBSTITUTE(-SMALL(IF(MIN(IF($B7:$AX7>SUMIF($B$1:$AX$1,AY7,$B7:$AX7),$B7:$AX7))=$B7:$AX7,$B$71:$AX$71,4^8)/1%+$B$1:$AX$1,ROW($1:$6)),-MIN(IF(MIN(IF($B7:$AX7>SUMIF($B$1:$AX$1,AY7,$B7:$AX7),$B7:$AX7))=$B7:$AX7,$B$71:$AX$71)),),"[>]0;")

³Ì¦h¨ú¤»­Ó:
«e3¤p_0420-1.rar (31.06 KB)

TOP

AZ7:AZ12 ¤»®æ°Ï°ì°}¦C:
=TEXT(SMALL(IF(SMALL($B7:$AX7+$B$71:$AX$71%%,1+COUNTIF($B7:$AX7,"<="&INDEX(7:7,MATCH(--AY7,$1:$1,))) )=$B7:$AX7+$B$71:$AX$71%%,$B$1:$AX$1,99),ROW($1:$6)),"[<>99];;")

¥ý¿ï AZ7:AZ12 ¡A½s¿èÄd¶K¤W¤½¦¡¡A°}¦C¿é¤J¤½¦¡

¿ï AZ7:AZ12¡A¥k©Ô
¿ï AZ7:BB13¡A¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2019-4-20 16:28 ½s¿è

¦^´_ 4# ­ã´£³¡ªL
¦^´_ 5# ML089
·PÁ¤G¦ìª©¥D½ç±Ðªº°Ï°ì°}¦C¤½¦¡^^"
¥i¤£¥i¥H½Ð¤G¦ì±Ð¤p§Ì¦p¦ó­×§ï3#ªº¤@¯ë°}¦C¤½¦¡?
¦]¬°¤p§Ì¦h¦¸¦³½Ð±Ð¹L¸Ó¤½¦¡ªº­ì§@ªÌ¦p¦ó­×¥¿?¤]¤£ª¾¹D¬O¦³¤°»ò­ì¦]¡A¦³¦^ÂСA«oÁ`¬O±o¤£¨ì"¥¿½Tµª®×"ªº¦^ÂСC

¤£¬O¤p§Ì°õµÛ¡A¦Ó¬O¥u¾Ç·|"¤ñ¤j"¡A«o¨S¾Ç·|"¤ñ¤p"~¤ß¸Ì«Ü¤£¬O´þ¨ý«Ü¤£½ñ¹ê^^"
´N¦p°Ï°ì°}¦C¤ñ¤j¤ñ¤pªº¤½¦¡¡A¦]¸g¹LMª©¥Dªº­@¤ß±Ð¾É¡A¤p§Ì¤~¾Ç·|¤F¡C
ÁÂÁ±z­Ì¤G¦ì^^

TOP

AZ7 °}¦C¤½¦¡
=TEXT(SMALL((SMALL(OFFSET($B7,-LOOKUP(9,0/("¤p­p"=$A1:$A7),{6,5,4,3,2,1,0}),,,49)+$B$71:$AX$71%%,1+COUNTIF(OFFSET($B7,-LOOKUP(9,0/("¤p­p"=$A1:$A7),{6,5,4,3,2,1,0}),,,49),"<="&INDEX(OFFSET($A7,-LOOKUP(9,0/("¤p­p"=$A1:$A7),{6,5,4,3,2,1,0}),,,50),MATCH(--OFFSET(AY7,-LOOKUP(9,0/("¤p­p"=$A1:$A7),{6,5,4,3,2,1,0}),),$1:$1,))))<>OFFSET($B7,-LOOKUP(9,0/("¤p­p"=$A1:$A7),{6,5,4,3,2,1,0}),,,49)+$B$71:$AX$71%%)*99+$B$1:$AX$1,LOOKUP(9,0/("¤p­p"=$A1:$A7),{7,6,5,4,3,2,1})),"[<99];;")

¥k©Ô¤U©Ô

³æ³æ©w¦ì´Nªá¶O«Üªøªº¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089
ª©¥D:
ÁÂÁ±zªº¦A¦¸½ç±Ð!ÁÂÁ±z^^

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD