[µo°Ý] ¨ç¼Æ_±N¬Û¤¬¹ïÀ³ªº¤G­Ó¡i¤ñ¹ï³sÄò´Á¼Æ¡j§ï¬°¤G­Ó¡i¤ñ¹ï°Ï°ì¡j¡C

Airman

2015-1209-Q-1.rar (19.65 KB)

¤U¸ü¦¸¼Æ: 1

2015-12-9 09:47

T7
=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1;2},T$5-1,$R7-1),{1,2,3,4,5,6,7})),0,0)*MODE(N(OFFSET($I$6,CHOOSE({1;2},T$5-2,$R7-2),{1,2,3,4,5,6,7})),0,0)*MODE(N(OFFSET($I$6,CHOOSE({1;2},T$5-3,$R7-3),{1,2,3,4,5,6,7})),0,0),$R7-3,""))
§Y¦bT5-1(=90´Á),T5-2(=89´Á),T5-3(=88´Á)¤T­Ó³sÄò´Á¼Æ¤ñ¹ïR7-1,R7-2,R7-3¤T­Ó³sÄò´Á¼Æ¡A¨ä¤T­Ó¬Û¤¬¹ïÀ³´Á¼Æ¡A³£¥²¶·§t¦³¤@­Ó(§t)¥H¤Wªº¥æ¶°­È¡C

»Ý¨D¡J
¥i§_¯à±N¤½¦¡¤¤ªºT5-1,T5-2,T5-3§ï¬°T5-1¡GT5-3(§Yµø¬°A°Ï°ì)ªí¹F?
¥t±N¤½¦¡¤¤ªºR7-1,R7-2,R7-3§ï¬°R7-1¡JR7-3(§Yµø¬°B°Ï°ì)ªí¹F?

»¡©ú¡J
¦]¬°¦pªG·Q±N¬Û¤¬¹ïÀ³ªº¤G­Ó¤ñ¹ï³sÄò´Á¼Æ¡AÂX¥R¨ì7´Á©ÎÂX¥R¨ì10´Á¡A´N¥²¶·¼g7¦¸©Î10¦¸ªº¤½¦¡¡A
©Ò¥H§Æ±æ¯à±N¬Û¤¬¹ïÀ³ªº¤G­Ó¤ñ¹ï³sÄò´Á¼Æ§ï¬°¤G­Ó¤ñ¹ï°Ï°ì¡C
¤£ª¾¨ç¼Æ¯à§_¹F¦¨»Ý¨D?

¸Ô²Ó»¡©ú¤Î¹Ï¥Ü¦pªþ¥ó¡C

¥H¤W  Àµ½Ð¦U¦ì¥ý¶i¡B«e½ú¤£§[½ç±Ð!  ÁÂÁÂ!

­ã´£³¡ªL

=IF(L_1,"",IF(AND(MMULT(1-ISNA(RANK(N(OFFSET($I$6,T$5-{1;2;3},{1,2,3,4,5,6,7})),OFFSET($I$6,$R7-{1;2;3},1,,7))),ROW($1:$7)^0)),$R7-3,""))

=IF(L_1,"",IF(AND(MMULT(SUMIF(OFFSET($I$6,$R7-{1;2;3},1,,7),N(OFFSET($I$6,T$5-{1;2;3},{1,2,3,4,5,6,7}))),ROW($1:$7)^0)),$R7-3,""))

=IF(L_1,"",IF(AND(MMULT(COUNTIF(OFFSET($I$6,$R7-{1;2;3},1,,7),N(OFFSET($I$6,T$5-{1;2;3},{1,2,3,4,5,6,7}))),ROW($1:$7)^0)),$R7-3,""))


ROW($1:$7)^0 ¥i¥Î {1;1;1;1;1;1;1}

Airman

¦^´_ 2# ­ã´£³¡ªL
­ã¤j:
ÁÂÁ±z¦A¦¸¼ö¤ß¨ó§U©M«ü¾É!3­Ó¶Q¸Ñ¤½¦¡´ú¸ÕOK~·P®¦

¥i§_¦A½Ð½ç±Ð~
±N¥æ¶°­Èªº±ø¥ó¦A¶i¶¥¬°¥²¶·¦PÄæ¦ì¡C
ÁÂÁ±z

­ã´£³¡ªL

¦^´_ 3# Airman


«ç¼Ëªº¦PÄæ¡H
¦C¹ï¦Cªº¦PÄæ¡H

¬Ý¤£À´¡ã¡ã

Airman

¦^´_ 4# ­ã´£³¡ªL
­ã¤j:
¬Oªº~­­©w¦C¹ï¦Cªº¦PÄæ¦ì¤§¬Û¦P­È¡C
·q½Ð¦A¦¸«ü¾É~·P®¦

2015-1209-Q-1-­­©w¦PÄæ.rar (9.85 KB)

¤U¸ü¦¸¼Æ: 1

2015-12-9 16:24

­ã´£³¡ªL

¦^´_ 5# Airman

=IF(L_1,"",IF(AND(MMULT(N(OFFSET($I$6,T$5-3,1,3,7)=OFFSET($I$6,$R7-3,1,3,7)),{1;1;1;1;1;1;1})),$R7-3,""))

Airman

¦^´_ 6# ­ã´£³¡ªL
­ã¤j:
¥þOK¤F~·PÁ±z