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

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

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

Book1.rar (2.38 KB)
B3 =IF(COUNTIF($B7:$Z7,B5)=0,B5,"")
¦pªG·Q­n²Ä3¦Cªºµª®×¦p¦P²Ä9¦Cªºµª®×¡F
½Ð°Ý¡GB3ªº¤½¦¡­n¦p¦ó¦A¼W½s¡H
ÁÂÁ¡I

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

¦^´_ 2# ML089
¤£¦n·N«ä¡A¥i¤£¥i¥H½Ð±z«ü¾É2003ª©ªº¤½¦¡?
¤]¤£¥Î±Æ§Ç¡F¥u­n¦V¥ª¶ñº¡´N¥i¥H¤F¡C
2007ª©ªºIFERROR§ï¦¨2003ª©ªº ISERROR
¤½¦¡·|¦nªø^^"
ÁÂÁ±z !

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

¦^´_ 4# ML089
ª©¥D :
·PÁ±zªº«ü¾É

TOP

¦^´_ 2# ML089
ªþ¥ó :    TEST_1.rar (2.51 KB)
ª©¤j : ±z¦n !
¤£¦n·N«ä~¦³2­Ó°ÝÃD·Q¦V±z½Ð±Ð :
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¨ç¼Æ?

2_¦pªG²Ä5¦C¦³Åã¥Ü¼Æ¦rªºÄæ¦ì¤£½T©w¡A¦p¦ó±N¤½¦¡¶W©ÔªºÄæ¦ìÅã¥Ü""¡C
EX : B10ªº¤½¦¡¡C

¥H¤W    Àµ½Ð½ç±Ð     ÁÂÁ±z !

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

        ÀR«ä¦Û¦b : §Ñ¥\¤£§Ñ¹L¡A§Ñ«è¤£§Ñ®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD