12345
返回列表 上一主題 發帖

[發問] 請問關於統計不重複項目的個數"指定條件"

starry1314
　暱稱: 豪

高中生Rank: 3Rank: 3

帖子
471 
主題
121 
精華
0 
積分
579 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE2019 
閱讀權限
50 
性別
男 
註冊時間
2015-4-16 
最後登錄
2023-1-17 
25# 跳轉到 »
發表於 2015-4-27 14:47 | 只看該作者
回復 24# ML089


   感謝指導這樣指令又縮短了！這樣應該不會發生之前說的按下指令後去跑杯咖啡吧！！！　　:D

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
24#
發表於 2015-4-27 13:58 | 只看該作者
本帖最後由 ML089 於 2015-4-27 14:17 編輯

回復 23# starry1314

我有找到版主之前回覆的文章
【SUMPRODUCT 遇到空白儲存格顯示錯誤解決方法】
我將編號定義為名稱後,
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(數量)),OFFSET($A$3,,,COUNTA(數量)))*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))-E3
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(AV)),OFFSET($A$3,,,COUNTA(AV)))*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))

可自動計算了,想請問這樣的寫法有什麼問題嗎|?或是有較好的建議

可用名稱定義 Rng 為 OFFSET($A$3,,,COUNTA($A$3:$A$9999))

D3 =SUMPRODUCT(1/COUNTIF(Rng,Rng)*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),Rng))*1,{1;1})=2))-E3
E3 =SUMPRODUCT(1/COUNTIF(Rng,Rng)*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),Rng))*1,{1;1})=2))
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

starry1314
　暱稱: 豪

高中生Rank: 3Rank: 3

帖子
471 
主題
121 
精華
0 
積分
579 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE2019 
閱讀權限
50 
性別
男 
註冊時間
2015-4-16 
最後登錄
2023-1-17 
23#
發表於 2015-4-27 11:19 | 只看該作者
我有找到版主之前回覆的文章
【SUMPRODUCT 遇到空白儲存格顯示錯誤解決方法】
我將編號定義為名稱後,
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(數量)),OFFSET($A$3,,,COUNTA(數量)))*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))-E3
=SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA(AV)),OFFSET($A$3,,,COUNTA(AV)))*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),$A$3:$A$5000))*1,{1;1})=2))

可自動計算了,想請問這樣的寫法有什麼問題嗎|?或是有較好的建議

TOP

starry1314
　暱稱: 豪

高中生Rank: 3Rank: 3

帖子
471 
主題
121 
精華
0 
積分
579 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE2019 
閱讀權限
50 
性別
男 
註冊時間
2015-4-16 
最後登錄
2023-1-17 
22#
發表於 2015-4-27 11:02 | 只看該作者
(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3A$5000))*1,{1;1})=2))-E3
還是會導致有空白就計算錯誤呢,
我再將紅字範圍更改為較大也是會出錯

如果說COUNTIF很耗資源,有辦法偵測到空白即停止計算嗎?

TOP

starry1314
　暱稱: 豪

高中生Rank: 3Rank: 3

帖子
471 
主題
121 
精華
0 
積分
579 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE2019 
閱讀權限
50 
性別
男 
註冊時間
2015-4-16 
最後登錄
2023-1-17 
21#
發表於 2015-4-27 10:59 | 只看該作者
不好意思~如果再A12欄輸入數據也是會出現錯誤,
我再將(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3A$9999))*1,{1;1})=2)
改為更前方範圍一樣大小也是會出錯呢,
如果說使用COUNTIF很耗資源的話,還是說有遇到空白就自動停止繼續計算下去呢,
因我資料如果有1000筆就1~1000都有資料,1000就等於結束不會再有資料可以計算,有辦法偵測空白就停止嗎

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
20#
發表於 2015-4-27 09:11 | 只看該作者
本帖最後由 ML089 於 2015-4-27 14:16 編輯

回復 19# starry1314

可以採用動態範圍

D3 =SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA($A$3:$A$9999)),OFFSET($A$3,,,COUNTA($A$3:$A$9999)))*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),OFFSET($A$3,,,COUNTA($A$3:$A$9999))))*1,{1;1})=2))-E3
E3 =SUMPRODUCT(1/COUNTIF(OFFSET($A$3,,,COUNTA($A$3:$A$9999)),OFFSET($A$3,,,COUNTA($A$3:$A$9999)))*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),OFFSET($A$3,,,COUNTA($A$3:$A$9999))))*1,{1;1})=2))

注意若有錯誤時，空格要清除內容不能有空白字

使用 COUNTIF 很耗資源，若有 5000筆時會跑很久，建議關閉自動計算，填完資料後再按F9啟動計算，然後...去喝杯咖啡...上上廁所...休息一下，應該會跑很久不要以為是當掉。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

starry1314
　暱稱: 豪

高中生Rank: 3Rank: 3

帖子
471 
主題
121 
精華
0 
積分
579 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE2019 
閱讀權限
50 
性別
男 
註冊時間
2015-4-16 
最後登錄
2023-1-17 
19#
發表於 2015-4-27 00:15 | 只看該作者
回復 18# ML089


    神人.....真的太感謝了！！
但遇到空白欄位,會導致計算失敗....因我資料的數量每天都不同所以有遇到空白有可略過不計算的嗎?
這樣我可將範圍設到A5000,就不用每次抓取資料後每次都手動再更改

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
18#
發表於 2015-4-26 23:32 | 只看該作者
回復  ML089
如下圖所示,原本是計算出第2個位置帶A的不重複數,
現在想在屬於這個條件計算出的不重複數再 ...
starry1314 發表於 2015-4-26 23:13



D3 =SUMPRODUCT(1/COUNTIF($A$3:$A$11,$A$3:$A$11)*(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3:$A$11))*1,{1;1})=2))-E3
E3 =SUMPRODUCT(1/COUNTIF($A$3:$A$11,$A$3:$A$11)*(MMULT(ISNUMBER(FIND(MID(E$2,{1,2},1),$A$3:$A$11))*1,{1;1})=2))
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

starry1314
　暱稱: 豪

高中生Rank: 3Rank: 3

帖子
471 
主題
121 
精華
0 
積分
579 
點名
0  
作業系統
WIN10 
軟體版本
OFFICE2019 
閱讀權限
50 
性別
男 
註冊時間
2015-4-16 
最後登錄
2023-1-17 
17#
發表於 2015-4-26 23:13 | 只看該作者
本帖最後由 starry1314 於 2015-4-26 23:15 編輯

回復 16# ML089
如下圖所示,原本是計算出第2個位置帶A的不重複數,
現在想在屬於這個條件計算出的不重複數再從中找出帶著V的數量
指定條件-不重複-多重條件.zip (7.29 KB)

未命名.png (11.97 KB)

未命名.png

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
16#
發表於 2015-4-26 20:52 | 只看該作者
那要怎麼加入進去在SUM(IF(MID($A$1A$49,2,1)=RIGHT(G1),1/COUNTIF($A$1A$49,$A$1A$49)))
這裡面 ...
starry1314 發表於 2015-4-26 20:17



   之前的公式大致是某城市的不重複數，你目前要改為什麼? 沒有目地沒有辨法應套。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 不怕事多，只怕多事。
12345
返回列表 上一主題