麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 請問如何找出儲存格缺少的數字 [打印本頁]

作者: a6566 時間: 2016-6-2 11:36 標題: 請問如何找出儲存格缺少的數字

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

[attach]24384[/attach],

懇請教導

作者: p212 時間: 2016-6-2 13:02

回復 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 時間: 2016-6-2 14:58

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

作者: hcm19522 時間: 2016-6-2 15:47

(輔助)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 時間: 2016-6-2 15:58

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 時間: 2016-6-2 17:47

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

作者: rouber590324 時間: 2016-6-2 18:53

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 時間: 2016-6-2 20:11

回復 8# rouber590324

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

作者: a6566 時間: 2016-6-2 20:22

回復 8# rouber590324

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

作者: rouber590324 時間: 2016-6-3 09:44

試試
B34(單數)=IF(MOD(E5,2)=1,1,0)+.........+IF(MOD(K18,2)=1,1,0)
B35(偶數)=IF(MOD(E5,2)=1,0,1)+.........+IF(MOD(K18,2)=1,0,1)

作者: p212 時間: 2016-6-3 09:57

本帖最後由 p212 於 2016-6-3 10:07 編輯

回復 10# a6566
採用4# hcm19522大大簡潔的公式 (謝謝hcm19522大大的分享)
1.依#3的資料分布且排除「0」(即「1-9」之數字)
(1)於U欄新增紅色區塊的資料欄(E5,I5,M5,Q5,I7,L7,K10)，V欄為其對應的輔助欄
儲存格V2輸入陣列公式 (以Ctrl+Shift+Enter輸入)
=IFERROR(","&SMALL(IF(COUNTIF($U$2:$U$8,ROW($1:$9))=0,ROW($1:$9)),ROW(A1)),"")&V3
向下複製公式
(2)於W欄新增藍色區塊的資料欄(D10,F10,O10,E12,P12,J15,L15,K18)，X欄為其對應的輔助欄
儲存格X2輸入陣列公式
=IFERROR(","&SMALL(IF(COUNTIF($W$2:$W$10,ROW($1:$9))=0,ROW($1:$9)),ROW(A1)),"")&X3
向下複製公式
(3)儲存格I20輸入公式 =MID($X$2,2,99)
儲存格H31輸入公式 =MID($V$2,2,99)
2.E5到K18所有的數字之奇偶計算
儲存格B34輸入計算「奇數」個數公式
=SUMPRODUCT((MOD($U$2:$U$8,2)=1)*1)+SUMPRODUCT((MOD($W$2:$W$10,2)=1)*1)
儲存格B35輸入計算「偶數」個數公式
=SUMPRODUCT((MOD($U$2:$U$8,2)=0)*1)+SUMPRODUCT((MOD($W$2:$W$10,2)=0)*1)
請參考！

作者: hcm19522 時間: 2016-6-3 10:07

http://blog.xuite.net/hcm19522/twblog/419051828

作者: 准提部林 時間: 2016-6-3 12:12

本帖最後由准提部林於 2016-6-3 12:15 編輯

１～９未出現：
=MID(TRIM(SUBSTITUTE(TEXT(SUMPRODUCT(ISERR(FIND(ROW(1:9),E5&I5&M5&Q5&I7&L7&K10))*ROW(1:9)*10^(9-ROW(1:9))),REPT("!,0",9)),",0",)),2,19)

或．以空格分隔：
=TRIM(SUBSTITUTE(TEXT(SUMPRODUCT(ISERR(FIND(ROW(1:9),E5&I5&M5&Q5&I7&L7&K10))*ROW(1:9)*10^(9-ROW(1:9))),REPT("0 ",9)),0,))

作者: p212 時間: 2016-6-3 15:56

本帖最後由 p212 於 2016-6-3 16:00 編輯

回復 14# 准提部林
請教准提部林版主
以3#的資料計算
套上14#的公式其所得結果好像會多出「9」的答案 (在E5&I5&M5&Q5&I7&L7&K10中缺2,3,4,7)
請問如何修正？

作者: 准提部林 時間: 2016-6-3 16:05

回復 15# p212

沒問題的, 看附件：
[attach]24407[/attach]

作者: p212 時間: 2016-6-3 16:12

回復 16# 准提部林
3#的附圖資料在J7而不位於I7儲存格，所以我誤解了。
感謝版主賜教！

作者: a6566 時間: 2016-6-3 16:46

謝謝各位高手的指導與不吝指教，真的學到很多
我還有一個問題想請教,如圖
儲存格中有Ａ到Ｊ不等的編號，每個編號上都有一個１到９的數字
我想請問
１。如何在右側的小表格中自動帶入一個一條直槓（像ｌ的符號）的符號
　　例如Ａ是８則在右側的小表格中的W9的地方出現一條直槓的符號（像ｌ的符號）
　　　　Ｄ是４則在右側的小表格中的W10的地方出現一條直槓的符號（像ｌ的符號）
　　如果ＡＤ同時是８的話則W9會出現二條直槓符號
２。同問題１一樣當ＥＦＧＫＬ上的數字是多少，則會在右側的小表格中的Z7到Z11分別
出現一條直槓的符號，若同時出現到5個相同的數字例如6，就在會在Z7出現五條直槓
[attach]24409[/attach]

作者: a6566 時間: 2016-6-7 23:03

請問各位高手,第18層的問題有辦法解決嗎

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)