ziv976688

¦^´_ 10# ML089
ÁA¸Ñ¤F~ÁÂÁ±z

­ã´£³¡ªL

=LOOKUP(1,IF({1;0},{0,""},INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C$215:$AY$298,ROW(A1)*12,),INDEX($C$210:$AY$293,ROW(A1)*12,))=0,COLUMN($C:$AY),99),COLUMN(A1)))^{0,1}))

­ã´£³¡ªL

2003¨S¦³IFERROR¨ç¼Æ, ¼g¤@­Ó¦Û­q¨ç¼Æ, ¥[¨ì»Ý­nªºÀɮפ¤, §Y¥i¨Ï¥Î:
²Õ¦X¸õ¦C¿z¿ï+IFERROR.rar (18.51 KB)

¤U¸ü¦¸¼Æ: 3

2021-10-31 11:40

ziv976688

¦^´_ 12# ­ã´£³¡ªL
·PÁ­ã¤j¥X¤â±Ï´©
´ú¸Õ«á~§¹¥þ²Å¦X»Ý¨D~·P®¦

PS :
«á¨Óµo²{¥½¾Çªº#7¤½¦¡¤]¬O´«¤F°_©l¦C~µª®×´N¤£¹ï¤F
¦]¬°¥½¾Ç§Ë¤F2¤p®É¡AÁÙ¬OµLªk¹F¦¨¥ô¤@¦C°_©l¥þ³¡³£¬O¥¿¸Ñ¡A
©Ò¥H´N·Q¦A¤W¨Ó½Ð±ÐMª©¤j~
¬Ý¨ì­ã¤j¤w¸g§¹¦¨¥¿¸Ñ~
¥½¾Ç¸U¤À·P¿E

­ã´£³¡ªL

¦A´î´X¦r
=iferror(INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C:$AY,ROW(A1)*12+214,),""&INDEX($C:$AY,ROW(A1)*12+209,))=0,COLUMN($C:$AY)),COLUMN(A$1))),"")

ziv976688

¦^´_ 15# ­ã´£³¡ªL
¥¿¦b¯Ç´e¬°¦ó·|#12¸õ¨ì#14~­ì¨Ó¬O#13¨S¦³§Y®ÉÅã¥Ü¡C
·PÁ±zªº«ü¾É~IsErrorÂàIFERROR¨ç¼Æªº¥¨¶°

ÁÂÁ«ü¾É¦h¼Ë¤½¦¡~Åý¥½¾Ç¦³¾Ç²ßªº¾÷·|~·P®¦

­ã´£³¡ªL

¦^´_ 17# ziv976688

¬Ý15¼Ó¤è¦¡:
ROW(A1)*12+209 = 221 --- ¤U©Ô»¼¼W12
ROW(A1)*12+214 = 226

¯Â¼Æ¦r­pºâ, ¦Û¤v±ÀºV±ÀºV~~

ziv976688

¦^´_ 17# ­ã´£³¡ªL
¤£¦n·N«ä~¦]¬°­è­è·Q¨ì~·Q¦Û¦æ´ú¸Õ¬Ý¬Ý~©Ò¥H§R¤F´£°Ý~¦ý«o¦A¤@¦¸»P±z¦P¨B~¯u¥©¡C
¸É¤W´£°Ý~´ú¸Õ«á~¦A¦V±z³ø§i~ÁÂÁ±z^_^
­ã¤j :
½Ð±Ð±z~
=LOOKUP(1,IF({1;0},{0,""},INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C$215:$AY$298,ROW(A1)*12,),INDEX($C$210:$AY$293,ROW(A1)*12,))=0,COLUMN($C:$AY),99),COLUMN(A1)))^{0,1}))

¥HC221¡GAY221¤ñ¹ïC226¡GAY226¦pªG¬O¥H$C$215:$AY$298¤ñ¹ï$C$210:$AY$293¨Óªí¹Fªº¸Ü~
°²³]¬O§ï¬°C221¡GAY221¤ñ¹ïC224¡GAY224
¨º¤½¦¡­n¦p¦ó­×§ï?

Àµ½Ð½ç±Ð !     ÁÂÁ±z !

ziv976688

¦^´_ 17# ­ã´£³¡ªL
­ã¤j :
221¤ñ¹ï224
=LOOKUP(1,IF({1;0},{0,""},INDEX($A:$AY,ROW(A1)*12+209,SMALL(IF(COUNTIF(INDEX($C$213:$AY$296,ROW(A1)*12,),INDEX($C$210:$AY$293,ROW(A1)*12,))=0,COLUMN($C:$AY),99),COLUMN(A1)))^{0,1}))
¸Ô²Ó¤ñ¹ïµª®×µL»~¡C
¸U¤À·PÁ±zªº«ü¾É~¥½¾Ç¨ü¯q¨}¦h~·P®¦^_^

PS :
­ì¨Ó¬O¥½¾Ç±N¤ñ¹ïªº¦C¼Æ§Ë¤Ï¤F~210+2©M293+2~©Ò¥H¶]¥Xªºµª®×¤£¹ï~
¥u·Q¨ì¦C¼Æ®t¼Æ¡A§Ñ¤F¦Ò¼{¨ì¬OCOUNTIF¨ç¼Æ^^*