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

[µo°Ý] ­×¥¿¤½¦¡¡A¥H§Q¾A¥ÎÅܰʪº¤º®e¡C

¦^´_ 17# ML089
ML089ª©¤j¡J
½Ð°Ý¡J¶Q¤½¦¡¦p¦ó§ï¬°«e¤T¤p?
±NLARGE§ï¬°SMALLµª®×¤£¹ï^^"

TOP

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

¦^´_ 13# hcm19522

AZ7=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))

hcm19522¤j¤j¡J
½Ð°Ý¡J¶Q¤½¦¡¦p¦ó§ï¬°³Ì¤p,¦¸¤p,²Ä¤T¤p?
±NMAX§ï¬°MIN¡FLARGE§ï¬°SMALLµª®×¤£¹ï^^"
±zÁÂÁÂ^^

TOP

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

¦^´_ 17# ML089
=TEXT(SMALL(IF(LARGE($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),{1;2;3;4;5;6}),"[<>99];;")

ML089ª©¤j¡J
½Ð°Ý¡J¶Q¤½¦¡¦p¦ó§ï¬°³Ì¤p,¦¸¤p,²Ä¤T¤p?
±NLARGE§ï¬°SMALLµª®×¤£¥þ¹ï^^"
±zÁÂÁÂ^^

¥t¥~~
¼Æ­È«¬ºAªº³Ì¤j,¦¸¤j,²Ä¤T¤j,³Ì¤p,¦¸¤p,²Ä¤T¤p¤½¦¡­n«ç»ò§ï?
±zÁÂÁÂ^^

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2019-4-18 09:45 ½s¿è

¦^´_ 23# Airman

¿ï BH7:BH12¡A¶K¤W¤½¦¡¡A°}¦C¿é¤J(¤TÁä¿é¤J)
=TEXT(SMALL(IF(SMALL($B7:$AX7+1/$B$71:$AX$71,1+COUNTIF($B7:$AX7,"<="&INDEX(7:7,MATCH(--BG7,$1:$1,))) )=$B7:$AX7+1/$B$71:$AX$71,$B$1:$AX$1,99),{1;2;3;4;5;6}),"[<>99];;")
¿ï BH7:BH12 ¥k©Ô
¿ï BH7:BJ13 ¤U©Ô

¶È´ú¸Õ¤@²Õ¡A¨ä¥L§A¹ï¤@¹ï§a!
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 23# Airman

°Ï°ì°}¦C­n§ï¬°¼Æ­È«¬¡A§ÚÁÙ¨S¦³·s·Qªk¡A¥i¥H¥Î§A¤W¦¸ªº¤èªk

§â¥D­nªº¤½¦¡ SMALL(.....) ©w¸qµ{¦WºÙ¡A¤ñ¸û¦nºûÅ@¤½¦¡¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 25# ML089
«e3¤p.rar (10.35 KB)
ML089ª©¤j¡J
¤£¦n·N«ä¡A¨S¦³²Ó¬d¨ì">="¡A©Ò¥H¨S§ï¨ì^^"
¦ý§ï¤F¤§«á(¥H±zPOªº¤½¦¡)¡Aµª®×ÁÙ¬O¥u¦³AZÄ檺µª®×¥¿½T¡A
¥k©Ô«á~BA©MBB¤GÄ檺µª®×¤£¹ï(»PAZÄæ¬Û¦P)
¬O¤p§Ì¦³­þ¸Ì¿ù¤F?
½Ð½ç¥¿!ÁÂÁ±z^^
=========================
=IFERROR(SMALL(IF(MAX(IF(COUNTIF($BC9:BC11,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1),{1;2;3}),"")
¥i§_¯à³Â·Ð±z±N¤W­z¤½¦¡¦A+$B71¡J$AX71ªº¤ñ¹ï±ø¥ó¤§¤½¦¡PO¤W¶Ü?
¦WºÙ©w¸q¤p§Ì¦Û¦æ³B²z¡C
ÁÂÁ±z^^

TOP

¦^´_ 26# Airman

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),{1;2;3;4;5;6}),"[<>99];;")
¿ïAZ7:AZ12¥k©Ô


­pºâSMALL¨ú¼Æ¦ì¸m
        ª`·N --AY7 °t¦XAZ7:AZ12¤½¦¡ªº¥ª¤@®æ¦ì¸m¡A§Q¥Î INDEX(MATCH( ¬d¸ß¤W­Ó¤j¤p¼Æ­È¡A§Q¥ÎCOUNTIFºâ¥X <= ¸Ó¼Æ­È¦³´X­Ó¡C


71¦C¸ê®Æªº¤ñ¸û
        ¨ú¤j $B$71:$AX$71%%
        ¨ú¤p 1/$B$71:$AX$71
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 27# ML089
ML089ª©¤j¡J¤È¦w!
¤@®ÉÁÙ¨S¿ìªk®ø¤Æ~¦³«Ý¤p§Ì¦A¦h¥[¬ã²ß¡C
ÁÂÁ±zªº­@¤ß±Ð¾É~·P®¦^^

TOP

¦^´_ 27# ML089
ML089ª©¤j¡J±z¦n!
­pºâSMALL¨ú¼Æ¦ì¸m==>¤wÁA¸Ñ~ÁÂÁ±z!
¦ý~
¨ú¤j $B$71:$AX$71%%
¨ú¤p 1/$B$71:$AX$71
ÁÙ¬O¤£¤ÓÁA¸Ñ(²Ä1¦¸±µÄ²¨ì%%)^^"
¥H¶Q¸Ñ¤½¦¡¬°¨Ò¡J
«e3¤j
=TEXT(SMALL(IF(LARGE($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),{1;2;3;4;5;6}),"[<>99];;")
«e3¤p
=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),{1;2;3;4;5;6}),"[<>99];;")

¬°¦ó«e3¤j©M«e3¤p³£¬O$B$71:$AX$71%%?

¤S¥Hh¤jªº¤½¦¡¬°¨Ò¡J
=SMALL(IF(MIN((SMALL((MATCH(OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49),OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49),)=COLUMN($B:$AX)-1)*OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49),COLUMN(A64))=OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49))*(OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49)/1%%+$B$71:$AX$71))=OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49)/1%%+$B$71:$AX$71,$B$1:$AX$1),MOD(ROW(A64),7))
¨ä%%ªº¦ì¸m¤S­n¦p¦ó­×§ï¬°"¨ú¤p"?

¥H¤W ·q½Ð½ç±Ð!ÁÂÁ±z^^

TOP

¦^´_ 29# Airman


   % = 1/100
%% = 1/10000
123% = 1.23
123%% = 0.0123

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

·í¨ú¤j©Î¨ú¤p®É¡A§Aªº°Æ±ø¥ó1¨S¦³§ïÅÜ "$B71:$AX71ªº¦PÄæ¹ïÀ³¼Æ¦r¸û¤j¤§±Æ¦WªÌ"¡A
$B7:$AX7      = ,,,,,81,,,,,71,,,,,63,,,,,,81,,,,,
$B71:$AX71 = ,,,,,412,,362,,,319,,,,412,,
$B7:$AX7 + $B71:$AX71%% = ,,,,,81.0412,,,,71.0362,,,,63.0319,,,,,81.0412
¨Ï¥Î MAX ©Î LARGE(...., 1) ³£¥i¥H¨ú¥X 81.0412

¦A¥h¤ñ¹ï if( LARGE(...,1) = $B7:$AX7 + $B$71:$AX$71%% , ¬O¨ú¥X $B$1:$AX$1,  §_ ....)

­Y¡A°Æ±ø¥ó1 §ïÅÜ "$B71:$AX71ªº¦PÄæ¹ïÀ³¼Æ¦r¸û "¤p" ¤§±Æ¦WªÌ"
´N¨Ï¥Î $B7:$AX7 + 1/$B71:$AX71

1/100     = 0.01   ¤p¼Æ¤Ï¼Æ¬°¤j ªº¤p¼Æ
1/1000  =0.001  ¤j¼Æ¤Ï¼Æ¬°¤p ªº¤p¼Æ

¨ä¥L¬Û¦P¤£¦AÂب¥¡A¦³¤£À´¦A°Ý§a
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD