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

[µo°Ý] ¼W¼g¦V¥ª¶ñº¡ªº¤½¦¡

B10
=IFERROR(MOD(SMALL(IF(COUNTIF(7:7,$B5:$I5)=0,$B5:$I5+COLUMN($B:$I)*10^5),COLUMN(A1)),10^5),"")
¤TÁä(CTRL+SHIFT+ENTER)¿é¤J¤½¦¡

¥k©Ô½Æ»s
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# johnny1680

=INDEX(5:5,SMALL(IF(COUNTIF(7:7,$B5:$I5)=0,COLUMN($B:$I),99),COLUMN(A1)))&""
°}¦C¤½¦¡¡A¤TÁä¿é¤J

¤å¦r®æ¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ziv976688

B10
=IF(ISERROR(MOD(SMALL(IF(COUNTIF(7:7,$B5:$Z5)=0,$B5:$Z5+COLUMN($B:$Z)*10^5),COLUMN(A1)),10^5)),"",MOD(SMALL(IF(COUNTIF(7:7,$B5:$Z5)=0,$B5:$Z5+COLUMN($B:$Z)*10^5),COLUMN(A1)),10^5))
1_¥½¾Ç¤£¤Ó²z¸Ñ~¤½¦¡¤¤¬°¦ó·|»Ý­n¥Î¨ì¨ú¾l¼ÆªºMOD¨ç¼Æ?
»¡©ú¡G¬°¤F±Æ¦C¦ì¸m¤£ÅÜ¡A­n¥HCOLUMN NO §@¬°±Æ§Ç¥D¾É¡A$B5:$Z5 + COLUMN($B:$Z)*10^5 = {200017, ....} ¨Ï¥ÎSMALL±Æ§Ç¡A¨ú¥X­È¥Î MOD( 200017, 10^5) ¨ú¥X 17 ­È

2_¦pªG²Ä5¦C¦³Åã¥Ü¼Æ¦rªºÄæ¦ì¤£½T©w¡A¦p¦ó±N¤½¦¡¶W©ÔªºÄæ¦ìÅã¥Ü""¡C
¶W©ÔÅܦ¨ "" ¤è¦¡«Ü¦h
1 »s³y¿ù»~¡A¥ÎIFERROR( ..., "") ¨ÓÅܦ¨ ""
2 »s³yªÅ®æ¦ì¸m¡A ªÅ®æ¥Î &"" ·|Åܦ¨ ""¡A¤£µM·|¦¨¬° 0 ¡A¦ý¨ä¥L¼Æ¦r³£·|Åܦ¨ ¤å¦r«¬¼Æ¦r
3 ­pºâ¦³¦h¤Ö¶q­nÅã¥Ü¡A¶W¹L¼Æ¶q®É¬° ""¡AIF( ¶W¶q§PÂ_, "", ­pºâ¦¡)
4 ¨ä¥L¤è¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ziv976688


B11 =IF(COLUMN(A1)>COUNT(0/(COUNTIF($B7:$K7,$B5:$I5)=0)),"",SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,$B5:$I5),COLUMN(A1)))
¤TÁä¿é¤J¡A¥k©Ô

¥H¼Æ¦r¤j¤p±Æ¦C¤è¦¡¡A¥Î IF(¶W¹L¼Æ¶q®É, "", ­pºâ)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ziv976688

B12 =INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5),99),COLUMN(A1)))&""

³o¬O±Ä¥Î column 99 ¦ì¸m¬°ªÅ®æ & "" ¨Ó»s³yªÅ®æ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ziv976688


B13 =IFERROR(INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5)),COLUMN(A1))),"")
¥H¿ù»~¨Ó»s³yªÅ®æ IFERROR( ¡K, "")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ziv976688

­Y¬O 2003ª©¨S¦³ IFERROR ¨ç¼Æ¥i¥H¨Ï¥Î¡A¤]¥i¥Î¨ä¥L¤è¦¡¡A¨Ò¦plookup

B14 =LOOKUP(1,IF({1;0},{0,""},INDEX(5:5,SMALL(IF(COUNTIF($B7:$K7,$B5:$I5)=0,COLUMN($B5:$I5)),COLUMN(A1)))^{0,1}))
¤TÁä¿é¤J ¥k©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD