­ã´£³¡ªL

¦^´_ 10# Airman


¥Î¢Ô¢¸¬Ý¤@¬Ý¤U¤è¤½¦¡ªº®t²§¡G
=OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),ROW($1:$7))=$R$5
=N(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),ROW($1:$7)))=$R$5
=N(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7))=$R$5
=COUNTIF(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5)

Airman

¦^´_ 6# ­ã´£³¡ªL
­ã¤j:
²Ä3¦¡¶]¤£¥XTrue
¬d¬Ý¥b¤Ñ~ı±o¤½¦¡¨S¦³¿ù~¬d¤£¥X°ÝÃD
TEST.rar (23.7 KB)

¤U¸ü¦¸¼Æ: 1

2015-11-20 14:28

Airman

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

­ã¤j:
¨þ ~¨þ ~±z¥¼»¡«e¡A¤p§Ì´N¸Õ¤F~§¹¥þÁA¸Ñ¤FN()ªº¥Îªk~±z¯u¬O­ÓÀu¨}¦Ñ®v~¹¡¾Ç¤§¤h¡A¤£¤@©w¯àªí¹F¦Ó·|±Ð¾Ç~Åý¾Ç¥Í§¹¥þ³e³q¡C

¦³±N²Ä3¦¡§ï¬°=
(COUNT(N(RANK($R$5,OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7)))=3))
´N·|Åã¥Ü1(TRUE)
¬O¤£¬O²Ä3¦¡¤Ö¤FN?
·q½Ð½ç±Ð!·P®¦

­ã´£³¡ªL

¦^´_ 13# Airman


³o­Ó¤½¦¡¬O¿ù»~ªº,
RANK ¥u·|¥X²{ [¼Æ­È]¤Î[¿ù»~­È], ¥Î N¦æ¤£³q(µLªk±Æ¿ù), ª½±µCOUNT§Y¥i,

Airman

TEST.rar (28.2 KB)

¤U¸ü¦¸¼Æ: 1

2015-11-20 16:44

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

­ã¤j:
¦ý¥u¦³³o¼Ë®É¡A²Ä3¦¡µª®×¤~¬O¥¿½Tªº
°t¤W¥¦¦¡(¥æ¶°)~1~3¦¡µª®×¤]¥þ¤@¼Ë
???

­ã´£³¡ªL

¦^´_ 15# Airman


¥HSheet3. T35¬°¨Ò¡G
=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1;2;3},$R35,$R35-T$3,$R35-T$3*2),{1,2,3,4,5,6,7})),0,0,0)*(COUNT(N(RANK($R$5,OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7)))=3)),$R35-T$3*2,""))¡@

®Ø¦í¬õ¦â¬q«ö¢Ô¢¸¬Ý¬Ý¡A­È¬°¢¯¡A±ø¥ó¬O¤£¦¨¥ßªº¡I¡]¦ýµª®×¬Ý¨Ó¨S¿ù¡AÀ³¬O»~¼²¡A¦h¤@¨Ç´ú¸Õ­È¤~·Ç¡^


=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1;2;3},$R35,$R35-T$3,$R35-T$3*2),{1,2,3,4,5,6,7})),0,0,0)*(COUNT(RANK($R$5,OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7)))=3),$R35-T$3*2,""))


ÂŦâ¬q­È¬°TRUE¡I
ª`·N¡G³o¤½¦¡À³¥H¡e°}¦C¡f¿é¤J¡I

Airman

¦^´_ 16# ­ã´£³¡ªL
­ã¤j:
¤£¦n·N«ä¡A­ì¨Ó²Ä3¦¡¬°°}¦C¤½¦¡~³o¼Ëµª®×´N²Å¦X¤F¡C
ÁÙ¦³¦hªø¤F¤@­Ó¸gÅç~µª®×¦³ºÃ°Ý®É¡AÀ³¸Ó«öF9À˵ø¡C
¦A¦¸·PÁ±zªº­@¤ß±Ð¾É~·P®¦

PS:¦]¥~¥X¦Ó¿ðÂÐ~·q½Ð¨£½Ì