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

hcm19522

°Ñ¦Òhttp://forum.twbts.com/viewthrea ... a=pageD1&page=2

Airman

¦^´_ 41# hcm19522
h¤j:
·PÁ±zªº¦A¦¸¦^À³^^
¤£¦n·N«ä¡AÁÙ¬O¨S¦³²Å¦X»Ý¨D^^"
²³æ»¡´N¬O±N9#ªº¶Q«e3¤jªº¸Ñµª¤½¦¡¡A§ï¬°«e3¤p(§t71¦C¤]¬O¨ú¦PÄæ¹ïÀ³¼Æªº¸û¤pªÌ)¡C
©Î½Ð¸Ô¨£~
http://forum.twbts.com/thread-21687-1-1.html
ÁÂÁ±z^^

hcm19522

¦^´_ 40# Airman

­×¥¿ {=RIGHT(MIN(IF(SMALL(IF(MATCH($B7:$AX7,$B7:$AX7,)=COLUMN($B:$AX)-1,$B7:$AX7),COLUMN(A1))=$B7:$AX7,$B7:$AX7/1%+$B$1:$AX$1)),2)

Airman

¦^´_ 39# hcm19522
h¤j:
¨þ~¨þ~¤½¦¡³ºµMÅܦ¨³o»ò²µu~~¼F®`~~
²Ê¬ÝÀ³¸Ó¬O¯Ê¤Ö¦W¦¸¦³¨Ã±ÆªÌ¡A¨ä71¦C¦PÄæ¼Æ¦r¤]¬Û¦P®É¡A«h¸Ó¨Ã±ÆªÌ¥þ¨úªº±ø¥ó¡C
¤p§Ì¥ý¬ã¨s¦A»¡¡C
ÁÂÁ±zªº¤£§[«ü¾É~·P®¦^^

hcm19522

AZ7:BB7{=RIGHT(SMALL(IF(MATCH($B7:$AX7,$B7:$AX7,)=COLUMN($B:$AX)-1,$B7:$AX7/1%+$B$1:$AX$1),COLUMN(A1)),2)
½Æ»s¨ì¦U¦æ

Airman

¦^´_ 37# hcm19522
h¤j:
¶Q5Äæ¤@°Ï¬qªº¸ÑÃD¤j§@¡A¤p§Ì¤w«ôŪ~¨ØªA^^
¥t¥~~
¯à§_½Ð±z¦A¥H35#ªº­ì¤½¦¡§@­×§ï?
¦]¬°Áö¸g¹LM¤jªº½Ñ¦hÁ¿¸Ñ¡A¹ï¨ä¤½¦¡¬O¤wª¾«e3¤j©M«e3¤pªºÅܤơF
¦ýÀ³¥Î¨ì¶Q¤½¦¡«o¤@ª½¶]¤£¥X¥¿½Tµª®×^^"
¤£¦n·N«ä¡Aµ¹±z¼W²K³Â·Ð¡AÁÂÁ±z^^
PS¦]¬°²Ä¤@¦¸¹J¨ì³oºØÃþ«¬ªº¸ÑÃD¤½¦¡¡A©Ò¥H·Q¦h¾Ç²ß¡C

hcm19522

¦^´_ 35# Airman


   °Ñ¦Ò
https://blog.xuite.net/hcm19522/twblog/587392713

Airman

¦^´_ 9# hcm19522
ªþ¤W½d¨ÒÀɨѰÑ~
«e3¤p_EX-1.rar (6.87 KB)

¤U¸ü¦¸¼Æ: 3

2019-4-19 16:20

Airman

¦^´_ 9# hcm19522
h¤j:
½Ð±Ð±z~¤U¦C¶Q¸Ñµª¤½¦¡­n¦p¦ó§ï¬°«e3¤p¦W¦¸¦³¨Ã±Æ®É¡A«h$B$71:$AX$71¨ú¸û¤p­È~
=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))
¥H¤W  ·q½Ð½ç±Ð !ÁÂÁ±z^^