[發問] 請問如何找出儲存格缺少的數字

a6566

請問我在A1,B2,C3,D4,E5,F6,G7,H8,I9的儲存格內各有數字0到9,javascript:;
但是這些儲存格中的數字會重覆,
我想在J1的儲存格，如何利用公式去抓出這9個儲存格中沒有出現缺少的數字請問要如何做到?

下載 (5.05 KB)

2016-6-2 11:35

,

懇請教導

p212

回復 1# a6566
1、新增輔助欄
(1) K欄，儲存格K1輸入公式 =INDIRECT(ADDRESS(ROW(1:1),ROW(1:1)))，向下複製公式。
(2) L欄，儲存格L1輸入陣列公式 =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))，向下複製公式。
      陣列公式請以 Ctrl+Shift+Enter 三鍵齊按輸入
2、儲存格J1輸入公式 =TRIM(SUBSTITUTE(CONCATENATE(L1," ",L2," ",L3," ",L4," ",L5," ",L6," ",L7," ",L8," ",L9),99,""))
以上拙見，請參考！

a6566

這位高手不好意思我想再請教以下2個問題
1.要將圖片的E5,I5,M5,Q5,I7,L7,K10的這幾個儲存格找出缺少的數字然後在H31顯示出來
2.要將圖片的D10,F10,O10,E12,P12,J15,L15,K18的這幾個儲存格找出缺少的數字然後在I20顯示出來

下載 (25.05 KB)

2016-6-2 14:58

hcm19522

(輔助)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)

rouber590324

DEAR  SIR
1.E5,I5,M5,Q5,I7,L7,K10 - 用圖一
2.D10,F10,O10,E12,P12,J15,L15,K18 - 同方式自行修改儲存格
圖一
=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,"")

rouber590324

DEAR  SIR
1.E5,I5,M5,Q5,I7,L7,K10 - 用圖一
2.D10,F10,O10,E12,P12,J15,L15,K18 - 同方式自行修改儲存格
圖一
=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,"")

a6566

回復 5# rouber590324
這個論壇的高手真是太厲害了
我想再請問一下2個問題
1.如何不把0列入，只找１－９缺少的數字
2.我套用公式後，會出現我已經有的９了

rouber590324

DEAR SIR
1取消 0 那段即可-圖一
=IF(ISERROR(SEARCH(0,E5&I5&M5&Q5&I7&L7&K10)),0,"")

其中之 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,"")

a6566

回復 8# rouber590324


謝謝您的教導，目前已成功了
謝謝
又學到了

a6566

回復 8# rouber590324


   我可以再請問一下我要如何把E5到K18這其中所有的數字，可以分別在B34(單數)及B35(偶數)分別在這2個儲存格統計單數跟偶數各有多少個?