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

[µo°Ý] ªÅ¥Õ<>0­Èªº¨ç¼Æ¤½¦¡

[µo°Ý] ªÅ¥Õ<>0­Èªº¨ç¼Æ¤½¦¡

ªÅ¥Õ¤£µø¬°0­Èªº¨ç¼Æ¤½¦¡.rar (5.74 KB)
¥Ø«e¡G
C19

=IF(C18="","",INDEX($1:$1,LARGE(IF($C14:$AY14=C18,COLUMN($C:$AY)),COUNTIF($C18:C18,C18))))
§Y·í²Ä14¦Cªº¬YÄæ­È=C18=0®É¡A«hC19Åã¥Ü²Ä14¦Cªº¸ÓÄæ­È¤§²Ä1¦Cªº³Ì¤j¦PÄæ­È¡C

°ÝÃD¡G
¦]¬°¥Ø«eªºC19¤½¦¡·|±N$C14:$AY14=""ªºÀx¦s®æ~³£§PÂ_¬°0­È~©Ò¥HC19=49¡F
¦ýµª®×¬O¿ù»~ªº¡C

»Ý¨D¡G
±NC19¤½¦¡ªº§PÂ_±ø¥ó­×¥¿¬°~
·í$C14:$AY14ªº¬YÀx¦s®æ=""®É¡A«h§PÂ_¬°"ªÅ¥Õ®æ"¡A¤£µø¬°0­È~
·í$C14:$AY14ªº¬YÀx¦s®æ=0®É¡A¤~§PÂ_¬°0­È¡C
§YC19=05

¥H¤W C19ªº¨ç¼Æ¤½¦¡~Àµ½Ð¦U¦ì¤j¤j½ç¥¿¡C
ÁÂÁÂ!

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2021-10-8 10:51 ½s¿è

{=IF(C18="","",INDEX(1:1,MAX(IF(($C14:$AY14=C18)*($C14:$AY14<>""),COLUMN($C:$AY)))))

=IF(C18="","",LOOKUP(1,($C14:$AY14=C18)/($C14:$AY14<>""),$C1:$AY1))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 1# ziv976688


    {=IF(C18="","",INDEX($1:$1,LARGE(IF($C14:$AY14<>"",  IF($C14:$AY14=C18,COLUMN($C:$AY)),),1)))}

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-8 11:50 ½s¿è

¦^´_ 2# hcm19522
¦^´_ 3# samwang
¤G¦ì¤j¤j :
´ú¸Õ¦¨¥\
©Ó±Ð¤F~·P®¦

TOP

C19 =INDEX(1:1,MATCH(C18,14:14,))

¨ú¥©
C19 =MATCH(C18,14:14,)-2
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-10-8 17:25 ½s¿è

¦^´_ 5# ML089
¤£¶È¬O«D°}¦Cªº¤@¯ë¤½¦¡¸Ñ(§Q©ó®M¥Î¦bµ{¦¡½X)¡A¥Bºë©ú(§Q¥Î©T©wA&Bªº-2Äæ®t)¦Ó²µuªº¸Ñªk~
©Ó±Ð¤F~·P®¦

¥t¥~¦A©µ¦ù½Ð±Ð¡G
C19
=IF(C18="","",INDEX($1¡G$1,LARGE(IF(($C14¡G$AY14=C18)*($C14¡G$AY14<>""),COLUMN($C¡G$AY)),COUNTIF($C18¡GC18,C18))))
°}¦C¤½¦¡  ¥k©Ô¨ìAY19¶ñº¡(¦]¬°¤½¦¡¦³®M¥Î¦bµ{¦¡½X¤º¡A¨Æ«e¤£ª¾²Ä18¦C¼Æ¦rªºÅã¥Ü¨´¤î®æ¡A©Ò¥H¹w©Ô¨ìAY19)¡A
§YC19=
·í$C14¡G$AY14ªº¬YÀx¦s®æ<>""¥B$C14¡G$AY14=C18®É¡A
«hC19Åã¥Ü²Ä14¦C¸ÓÄ檺²Ä1¦C¦PÄæ­È¡F
¥t·í²Ä18¦C¦³2®æ¥H¤Wªº¬Û¦P­È®É¡A«h¸Ó²Ä19¦Cªº­È¥H¥Ñ¤j¦Ó¤p±Æ§Ç¡C
¸Ô¦pªþ¥ó : TEST_1008.rar (3.54 KB)

½Ð°Ý¡G±zªºC19¤½¦¡·|¦p¦ó§@¸Ñ¡H
ÁÂÁ±z

TOP

C18:AB19 °Ï°ì°}¦C¤½¦¡¡A±Æ¦C 14¦C¼Æ­È¥Ñ¤p¦Ü¤j¤Î1¦C¼Æ­È¥Ñ¤j¦Ü¤p
=IFERROR(INDEX(A:AY,{14;1},1/MOD(SMALL(IF(C14:AY14<>"",C14:AY14*10^4+1/COLUMN(C:AY)),COLUMN(A:Z)),10^4)),"")

¿é¤J¤½¦¡ (¥H«e°}¦C¤½¦¡­n¥ý§R°£)
1 ¥ý½Æ»s¤½¦¡
2 ¿ï¾Ü C18:AB19 Àx¦s®æ
3 ½s¿è¦C¶K¤W¤½¦¡
4 ¹«¼Ð¦ì©ó½s¿è¦C¡A¥Î¤TÁä(CTRL+SHIFT+ENTER)¿é¤J¤½¦¡


COLUMN(A:Z) ¨ú¥X´X­Ó¡A³Ì¤jÀ³¸Ó¬O¸ê®Æ¦³´XÄæ´N³]´XÄæ¡A¦ý¶K¤W(Åã¥Ü)Äæ¦ì¥i¥HÁYµu¦ÜªÅ®æ¥X²{1®æ¦ì¸m¡Aªí¥Ü¸ê®Æ§¹¥þ¥X²{¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089
ª©¤j :
°Ï°ì°}¦C¤½¦¡ªº®Ä²v«Ü°ª¡C
ÁÂÁ±zªº­@¤ß«ü¾É~·P®¦

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD