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

[µo°Ý] ¨ç¼Æ ½Ð±N10¬q¹ï¨¤¤½¦¡¶i¶¥§ï¬°¤@¦¡¡C

[µo°Ý] ¨ç¼Æ ½Ð±N10¬q¹ï¨¤¤½¦¡¶i¶¥§ï¬°¤@¦¡¡C


10¬q±×¹ï¨¤¤½¦¡§ï¬°¤@¦¡¡C.rar (7.65 KB)
B74:AX74ªº10°Ï¬q(5Äæ*9+4Äæ*1)¤½¦¡=
¨ú¦U¤p­p¦C¨C5Äæ¤Î¦A¨C¶¡¹j6¦Cªº5­Ó¹ï¨¤Àx¦s®æ(²Ä10¬q¬°²Ä9¬q¦A¶¡¹j5¦Cªº4­Ó¹ï¨¤Àx¦s®æ)ªº³Ì¤j¼Æ(¥i­«½Æ)¡A
¨Ã±N²Ä2¦C¦PÄ檺¹ïÀ³­È¡AÅã¥Ü©ó¦PÄ檺74¦CÀx¦s®æ¡C
EX¡J
B74 =IF((B$8=MAX($B$8:$F$8)),B$2,"")       ¥k©Ô¨ìF74(5Äæ)
G74 =IF((G$15=MAX($G$15:$K$15)),G$2,"")   ¥k©Ô¨ìK74(5Äæ)
¡J
¡J
¡J
AK74 =IF((AK$57=MAX($AK$57:$AO$57)),AK$2,"")  ¥k©Ô¨ìAO74(5Äæ)
AP74 =IF((AP$64=MAX($AP$64:$AT$64)),AP$2,"")   ¥k©Ô¨ìAT74(5Äæ)
AU74 =IF((AU$70=MAX($AU$70:$AX$70)),AU$2,"")  ¥k©Ô¨ìAX74(4Äæ)

½Ð¦U¦ì¤j¤jÀ°¦£±NB74:AX74ªº10¬q¤½¦¡©Î«e9¬q(B74:AT74)¶i¶¥§ï¬°¤@¦¡¥k©Ô§¹¦¨¡C
ÁÂÁ¡I

=IF(MAX(OFFSET($B1,INT(COLUMN(E1)/5)*7-(B2>45),INT(COLUMN(E1)/5)*5-5,,5-(B2>45)))=OFFSET($B1,INT(COLUMN(E1)/5)*7-(B2>45),B2-1),B2,"")
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-4-5 18:42 ½s¿è

¦^´_ 2# hcm19522
h¤j¤j : ±z¤Ó¼F®`¤F~
ÁÂÁ±zªº½ç±Ð©MÀ°¦£!·P®¦

TOP

¦^´_ 2# hcm19522
h¤j¤j : ±z¦n !
¤U¤è°ÝÃD¥i¯à³Q»~·|¬°­«½ÆÃD¦Ó³Q§RÃD¤F^^"
¥i§_³Â·Ð±z½ç±Ð©MÀ°¦£?
ÁÂÁ±z^^
¨ç¼Æ  ½Ð±N10¬q¤½¦¡¶i¶¥§ï¬°¤@¦¡¡C

10¬q¤½¦¡§ï¬°¤@¦¡.rar (3.51 KB)
B6:AX6µ¥10°Ï¬q¤½¦¡=¨ú²Ä4¦C10°Ï¬q(5Äæ*9+4Äæ*1)ªº³Ì¤j¼Æ(¥i­«½Æ)¤§²Ä2¦C¦PÄæ¹ïÀ³­È¡C
EX¡J       
B6=IF((B$4=MAX($B$4:$F$4)),B$2,"")       ¥k©Ô¨ìF6(5Äæ)
G6 =IF((G$4=MAX($G$4:$K$4)),G$2,"")      ¥k©Ô¨ìK6(5Äæ)
¡J
¡J
¡J
AP6=IF((AP$4=MAX($AP$4:$AT$4)),AP$2,"")   ¥k©Ô¨ìAT6(5Äæ)
AU6 =IF((AU$4=MAX($AU$4:$AX$4)),AU$2,"")  ¥k©Ô¨ìAX6(4Äæ)

TOP

=IF(B4=MAX(OFFSET($B4,,INT((B2-1)/5)*5,,5)),B2,"")
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 5# hcm19522
h¤j¤j : ±z¦n!
­ì¨Ó¬O§Q¥Î(B2-1)·í°ò¼Æ¡C
ÁÂÁ±zªº¼ö¤ßÀ°¦£~·P®¦^^

TOP

¦^´_ 5# hcm19522
h¤j¤j : ±z¦n!
Ãþ¦ü5¼Óªº»Ý¨D¡Aµª®×¯à§_©¹¥ª¶ñº¡~
½Ð¸Ô¨£¦p¤Uªº¹Ï¥Ü©M¤å¦r»¡©ú :

¿ï¨ú¦U«ü©w°Ï°ìªº³Ì¤j­È.rar (2.05 KB)

¿ï¨ú¦U«ü©w°Ï°ìªº³Ì¤j­È

B4¡JAX4¤À¹j¬°10­Ó°Ï¬q(5Äæ*9+4Äæ*1)¡A
±N¦U°Ï¬qªº³Ì¤j¼Æ(¦³­«½Æ®É¡A¥þ³¡¦C¥X)¶ñ¤JB5¡JAX5(©¹¥ª¶ñº¡)¡C
»Ý¨D¡J
½Ð°Ý¡JB5©¹¥ª¶ñº¡ªºExcel 2003ª©ªº¨ç¼Æ¤½¦¡¡H
ÁÂÁ±z^^

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 8# hcm19522
h¤j¤j : ±z¦n!
ÁÂÁ±zªº¼ö¤ßÀ°¦£©M«ü¾É~·P®¦

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-4-21 17:18 ½s¿è

¦^´_ 8# hcm19522
h¤j¤j : ±z¦n!
¤£¦n·N«ä¡A¦pªG±N­ì¶Q¸Ñ¤½¦¡ªº¤ñ¹ï²Ä4¦C(B4:AX4)ªº10°Ï¬q³Ì¤j¼Æ
=INDEX(4:4,SMALL(IF(SUBTOTAL(4,OFFSET($A4,,ROW(1:10)*5-4,,5))-N(OFFSET($A4,,ROW(1:10)*5-5+COLUMN($A:$E)))=0,ROW(1:10)*5-4+COLUMN($A:$E),99),COLUMN(A1)))&""
   °}¦C
§ï¬°¤ñ¹ï²Ä72¦C(B72:AX72)ªº10°Ï¬q³Ì¤j¼Æ
=INDEX(72:72,SMALL(IF(SUBTOTAL(72,OFFSET($A72,,ROW(1:10)*5-4,,5))-N(OFFSET($A72,,ROW(1:10)*5-5+COLUMN($A:$E)))=0,ROW(1:10)*5-4+COLUMN($A:$E),99),COLUMN(A1)))&""
°}¦C

¥HF8¤@¬q¤@¬qÀ˵ø­×§ï«áªº¤½¦¡¡AÅã¥Ü­È³£¥¿½T¡A¦ý´N¬O¶]¤£¥X¥¿½Tµª®×~³£¬O#VALUE!^^///
½Ð±Ð :ÁÙ¦³­þ­Ó°Ñ¼Æ¥²¶·­×¥¿?
ÁÂÁ±z^^

TOP

        ÀR«ä¦Û¦b : ¤Ñ¤W³Ì¬ü¬O¬P¬P¡A¤H¥Í³Ì¬ü¬O·Å±¡¡C
ªð¦^¦Cªí ¤W¤@¥DÃD