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

[µo°Ý] ½Ð°Ý¦p¦ó§ä¥XÀx¦s®æ¯Ê¤Öªº¼Æ¦r

[µo°Ý] ½Ð°Ý¦p¦ó§ä¥XÀx¦s®æ¯Ê¤Öªº¼Æ¦r

½Ð°Ý§Ú¦bA1,B2,C3,D4,E5,F6,G7,H8,I9ªºÀx¦s®æ¤º¦U¦³¼Æ¦r0¨ì9,javascript:;
¦ý¬O³o¨ÇÀx¦s®æ¤¤ªº¼Æ¦r·|­«ÂÐ,
§Ú·Q¦bJ1ªºÀx¦s®æ¡A¦p¦ó§Q¥Î¤½¦¡¥h§ì¥X³o9­ÓÀx¦s®æ¤¤¨S¦³¥X²{¯Ê¤Öªº¼Æ¦r½Ð°Ý­n¦p¦ó°µ¨ì?

,

Àµ½Ð±Ð¾É

¦^´_ 1# a6566
1¡B·s¼W»²§UÄæ
(1) KÄæ¡AÀx¦s®æK1¿é¤J¤½¦¡ =INDIRECT(ADDRESS(ROW(1:1),ROW(1:1)))¡A¦V¤U½Æ»s¤½¦¡¡C
(2) LÄæ¡AÀx¦s®æL1¿é¤J°}¦C¤½¦¡ =SMALL(IF(COUNTIF($K$1:$K$9,{0,1,2,3,4,5,6,7,8,9}),99,{0,1,2,3,4,5,6,7,8,9}),ROW(1:1))¡A¦V¤U½Æ»s¤½¦¡¡C
      °}¦C¤½¦¡½Ð¥H Ctrl+Shift+Enter ¤TÁä»ô«ö¿é¤J
2¡BÀx¦s®æJ1¿é¤J¤½¦¡ =TRIM(SUBSTITUTE(CONCATENATE(L1," ",L2," ",L3," ",L4," ",L5," ",L6," ",L7," ",L8," ",L9),99,""))
¥H¤W©å¨£¡A½Ð°Ñ¦Ò¡I

fig.jpg (124.22 KB)

fig.jpg

TOP

³o¦ì°ª¤â¤£¦n·N«ä§Ú·Q¦A½Ð±Ð¥H¤U2­Ó°ÝÃD
1.­n±N¹Ï¤ùªºE5,I5,M5,Q5,I7,L7,K10ªº³o´X­ÓÀx¦s®æ§ä¥X¯Ê¤Öªº¼Æ¦rµM«á¦bH31Åã¥Ü¥X¨Ó
2.­n±N¹Ï¤ùªºD10,F10,O10,E12,P12,J15,L15,K18ªº³o´X­ÓÀx¦s®æ§ä¥X¯Ê¤Öªº¼Æ¦rµM«á¦bI20Åã¥Ü¥X¨Ó

TOP

(»²§U)K1:K9{=IFERROR(","&SMALL(IF(COUNTIF(A$1:I$9,ROW($1:$10)-1)=0,ROW($1:$10)-1),ROW(A1)),"")&K2
J1=MID(K1,2,99)

TOP

DEAR  SIR
1.E5,I5,M5,Q5,I7,L7,K10 - ¥Î¹Ï¤@
2.D10,F10,O10,E12,P12,J15,L15,K18 - ¦P¤è¦¡¦Û¦æ­×§ïÀx¦s®æ
¹Ï¤@
=IF(ISERROR(SEARCH(0,E5&I5&M5&Q5&I7&L7&K10)),0,"")&IF(ISERROR(SEARCH(1,E5&I5&M5&Q5&I7&L7&K10)),1,"")&IF(ISERROR(SEARCH(2,E5&I5&M5&Q5&I7&L7&K10)),2,"")&IF(ISERROR(SEARCH(3,E5&I5&M5&Q5&I7&L7&K10)),3,"")&IF(ISERROR(SEARCH(4,E5&I5&M5&Q5&I7&L7&K10)),4,"")&IF(ISERROR(SEARCH(5,E5&I5&M5&Q5&I7&L7&K10)),5,"")&IF(ISERROR(SEARCH(6,E5&I5&M5&Q5&I7&L7&K10)),6,"")&IF(ISERROR(SEARCH(7,E5&I5&M5&Q5&I7&L7&K10)),7,"")&IF(ISERROR(SEARCH(8,E5&I5&M5&Q5&I7&L7&K10)),8,"")&IF(ISERROR(SEARCH(9,E5&I5&M5&Q5&I7&L7&K10)),9,"")
ù

TOP

DEAR  SIR
1.E5,I5,M5,Q5,I7,L7,K10 - ¥Î¹Ï¤@
2.D10,F10,O10,E12,P12,J15,L15,K18 - ¦P¤è¦¡¦Û¦æ­×§ïÀx¦s®æ
¹Ï¤@
=IF(ISERROR(SEARCH(0,E5&I5&M5&Q5&I7&L7&K10)),0,"")&IF(ISERROR(SEARCH(1,E5&I5&M5&Q5&I7&L7&K10)),1,"")&IF(ISERROR(SEARCH(2,E5&I5&M5&Q5&I7&L7&K10)),2,"")&IF(ISERROR(SEARCH(3,E5&I5&M5&Q5&I7&L7&K10)),3,"")&IF(ISERROR(SEARCH(4,E5&I5&M5&Q5&I7&L7&K10)),4,"")&IF(ISERROR(SEARCH(5,E5&I5&M5&Q5&I7&L7&K10)),5,"")&IF(ISERROR(SEARCH(6,E5&I5&M5&Q5&I7&L7&K10)),6,"")&IF(ISERROR(SEARCH(7,E5&I5&M5&Q5&I7&L7&K10)),7,"")&IF(ISERROR(SEARCH(8,E5&I5&M5&Q5&I7&L7&K10)),8,"")&IF(ISERROR(SEARCH(9,E5&I5&M5&Q5&I7&L7&K10)),9,"")
ù

TOP

¦^´_ 5# rouber590324
³o­Ó½×¾Âªº°ª¤â¯u¬O¤Ó¼F®`¤F
§Ú·Q¦A½Ð°Ý¤@¤U2­Ó°ÝÃD
1.¦p¦ó¤£§â0¦C¤J¡A¥u§ä¢°¡Ð¢¸¯Ê¤Öªº¼Æ¦r
2.§Ú®M¥Î¤½¦¡«á¡A·|¥X²{§Ú¤w¸g¦³ªº¢¸¤F

TOP


DEAR SIR
1¨ú®ø 0 ¨º¬q§Y¥i-¹Ï¤@
=IF(ISERROR(SEARCH(0,E5&I5&M5&Q5&I7&L7&K10)),0,"")

¨ä¤¤¤§ 0 ¥i¨Ì»Ý¨DÅܧó.¦A¥H  & ¦ê¦A¤@°_  

¹Ï¤@
=IF(ISERROR(SEARCH(1,E5&I5&M5&Q5&I7&L7&K10)),1,"")&IF(ISERROR(SEARCH(2,E5&I5&M5&Q5&I7&L7&K10)),2,"")&IF(ISERROR(SEARCH(3,E5&I5&M5&Q5&I7&L7&K10)),3,"")&IF(ISERROR(SEARCH(4,E5&I5&M5&Q5&I7&L7&K10)),4,"")&IF(ISERROR(SEARCH(5,E5&I5&M5&Q5&I7&L7&K10)),5,"")&IF(ISERROR(SEARCH(6,E5&I5&M5&Q5&I7&L7&K10)),6,"")&IF(ISERROR(SEARCH(7,E5&I5&M5&Q5&I7&L7&K10)),7,"")&IF(ISERROR(SEARCH(8,E5&I5&M5&Q5&I7&L7&K10)),8,"")&IF(ISERROR(SEARCH(9,E5&I5&M5&Q5&I7&L7&K10)),9,"")
ù

TOP

¦^´_ 8# rouber590324


ÁÂÁ±zªº±Ð¾É¡A¥Ø«e¤w¦¨¥\¤F
ÁÂÁÂ
¤S¾Ç¨ì¤F

TOP

¦^´_ 8# rouber590324


   §Ú¥i¥H¦A½Ð°Ý¤@¤U§Ú­n¦p¦ó§âE5¨ìK18³o¨ä¤¤©Ò¦³ªº¼Æ¦r¡A¥i¥H¤À§O¦bB34(³æ¼Æ)¤ÎB35(°¸¼Æ)¤À§O¦b³o2­ÓÀx¦s®æ²Î­p³æ¼Æ¸ò°¸¼Æ¦U¦³¦h¤Ö­Ó?

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD