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

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

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

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

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

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¤©½ç±Ð!ÁÂÁÂ!

¿ï¾Ü 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¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

¥ý¤W¯Z¥h¤F¡Aµ¥¤¤¤È§Ú¦A¬Ý¬Ý
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

1¼Ó¤½¦¡¸É¥R»¡©ú¡A{1;2;3} ¬O·§¦ô¦P¼Æ³Ì¦h3­Ó¡A¨Ì¥Ø«eªí®æ¥iÂX¥R¦Ü 1~7
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦A¸É¥R
©¹¤Uªº½Æ»s­n¿ï¾Ü AZ7:BB9¡A¤@¦¸¤@­Ó°Ï°ì½Æ»s
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

­è­èÀˬ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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 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^^

TOP

¦^´_ 6# ML089

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

TOP

=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),"")
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 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];;")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD