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

Airman

²Ä¤@¦Cªº¹ïÀ³¼Æ.rar (21.04 KB)

¤U¸ü¦¸¼Æ: 9

2019-4-16 18:10

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,COLUMN($B:$AX)-1),MOD(ROW(A1),7))
°}¦C¤½¦¡ ~~ ¥k©Ô¨ìBB7¦A¤U©Ô¶ñº¡

¥Ø«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
·í$B1:$AX1ªº49­Ó¼Æ¦r«D¥Ñ¤p¦Ó¤j±Æ¦C(EX:Sheet1)®É¡A¨ä¤½¦¡ªºµª®×µLªkÀH$B1:$AX1ªº¼Æ¦r±Æ¦CÅÜ°Ê(EX:Sheet2&Sheet3)¦ÓÅܧó¡C

PS¡J³o¬O¤p§Ì¦b¥»½×¾ÂÂ^¨úªº¨ç¼Æ¤½¦¡¡A¦Û¤v¸Õ¤F¤@¤Ñ¡AµLªkÅýAZ7¤½¦¡³£¯à¾A¥Î©óSheet1~ Sheet3¡C

½Ð°Ý¡JÀ³¸Ó¦p¦ó­×¥¿AZ7¤½¦¡¡A¥H§Q¤½¦¡³£¯à¾A¥Î©óSheet1~ Sheet3
Àµ½Ð¦U¦ì¥ý¶i´f¤©½ç±Ð!ÁÂÁÂ!

ML089

¿ï¾Ü AZ7:AZ9 ¤T®æ
½s¿èÄæ¶K¤W¤½¦¡
=IFERROR(SMALL(IF(MAX(IF(COUNTIF($BC9:BC11,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1),{1;2;3}),"")
°}¦C¤½¦¡¡A¤TÁä¿é¤J

¿ï¾Ü AZ7:AZ9 ¤T®æ¡A¥k©Ô½Æ»s¤½¦¡

ML089

¤W¤½¦¡¶È¦b Sheet2 ´ú¸Õ

¥ý¤W¯Z¥h¤F¡Aµ¥¤¤¤È§Ú¦A¬Ý¬Ý

ML089

1¼Ó¤½¦¡¸É¥R»¡©ú¡A{1;2;3} ¬O·§¦ô¦P¼Æ³Ì¦h3­Ó¡A¨Ì¥Ø«eªí®æ¥iÂX¥R¦Ü 1~7

ML089

¦A¸É¥R
©¹¤Uªº½Æ»s­n¿ï¾Ü AZ7:BB9¡A¤@¦¸¤@­Ó°Ï°ì½Æ»s

ML089

­è­èÀˬd«e­±¤½¦¡¦³¿ù»~

¿ï¾Ü AZ7:AZ12 ¤»®æÀx¦s®æ ¡A¶K¤W¤U¦C¤½¦¡¡A¥H¤TÁä¿é¤J¤½¦¡                       
=IFERROR(SMALL(IF(MAX(IF(COUNTIF($AY7:AY12,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1),{1;2;3;4;5;6}),"")                       
                       
¿ï¾Ü AZ7:BB13 ¤C®æÀx¦s®æ ¡A¤U©Ô¦Ü©³½Æ»s¤½¦¡                       
                       
³Ì«á¤@²Õ¦³²§¡A¬O¤p­p¦ì¸mÀ³¸Ó¦b70¦C¦ì¸m¡A¦Û¦æ½Õ¾ã¸ê®Æ¦ì¸m§Y¥i

Airman

¦^´_ 6# ML089

ML089ª©¥D¡J¤È¦w!
¨þ~¨þ~¦n¤[¨S¦³¨£ÃѨì"°Ï°ì°}¦C"¤F~³oÅý¤p§Ì·Q°_"ª×Às"¤j®v^^

¤½¦¡OK~¦ý³o¥u¼g¨ì¥D±ø¥ó=$B7:$AX7ªº«e¤T¤jªº¼Æ¦r(¤¤¦¡±Æ¦W)ªº¤½¦¡~
¯Ê¤Ö¦A¤ñ¹ï$B71:$AX71ªº°Æ±ø¥ó¤½¦¡¡J
°Æ±ø¥ó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

¥H¤W  ÂԽн祿!ÁÂÁ±z^^

Airman

¦^´_ 6# ML089

¤£¦n·N«ä¡A§Ñ¤Fªþ¤W½d¨Ò~¸É¤W~
²Ä¤@¦Cªº¹ïÀ³¼Æ_A.rar (18.82 KB)

¤U¸ü¦¸¼Æ: 2

2019-4-17 12:53

ÁÂÁ±z!

hcm19522

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


OR
BD7:BF7 ¤U©Ô=IFERROR(INDEX($1:$1,AZ7+1),"")

ML089

¦^´_ 8# Airman

§Ú¬O¬Ý§Aªºªºµª®×°µªº¡A¤£¬O¤j³¡¤À¬Û¦P¶Ü?

§Ú¦³ÂI¾\Ū»Ùê¡A¤å¦r±Ô­z¤£¬O¬Ý±o«ÜÀ´¡C

§Aªº¬O2003ª©EXCEL¡A¨S¦³IFERROR¨ç¼Æ¡A­Y¥i¥H±µ¨ü¼Æ¦r¤å¦r«¬ºA®É¡A¤½¦¡¥i¥H§ï¬°
AZ7:AZ12 =TEXT(SMALL(IF(MAX(IF(COUNTIF($AY7:AY12,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1,99),{1;2;3;4;5;6}),"[<>99];;")