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

標題: [發問] 請問關於統計不重複項目的個數"指定條件" [打印本頁]

作者: starry1314 時間: 2015-4-25 13:50 標題: 請問關於統計不重複項目的個數"指定條件"

請問要如何統計呢?

SUM(IF(A1:A1000<>"",1/COUNTIF(A1:A1000,A1:A1000)))

知道這樣的寫法可以統計不重複個數

但不知該如何寫法可以提取 A欄裡面的不重複項目的數量且指定條件

最後可顯示出
4A的不重複數量 4B不重複數量 4C的不重複數量

範例如下
[attach]20794[/attach]

作者: starry1314 時間: 2015-4-25 14:11

阿...頭腦撞到牆了
突然靈機一動
=COUNTIF(A2:A7,"*a**")
即完成我要的目的

作者: starry1314 時間: 2015-4-25 15:08

突然發現此函數要的不對....
[attach]20795[/attach]

需要出現的是這個才對
,上一篇是出現的次數(包含重複)

我想要的不重複的指定個數

作者: starry1314 時間: 2015-4-25 15:41

附上範例檔....但指定條件那邊如果設定"**A****" 就無法執行
*是不固定的文字
[attach]20796[/attach]

作者: ML089 時間: 2015-4-25 16:58

回復 4# starry1314

[attach]20797[/attach]

G2 =SUM(IF(MID($A$1:$A$49,2,1)=RIGHT(G1),1/COUNTIF($A$1:$A$49,$A$1:$A$49)))
陣列公式

作者: starry1314 時間: 2015-4-25 17:06

回復 starry1314

G2 =SUM(IF(MID($A$1A$49,2,1)=RIGHT(G1),1/COUNTIF($A$1A$49,$A$1A$49))) ...
ML089 發表於 2015-4-25 16:58

請問right=(G1) 的涵義是??
因我儲存格G1內容為台中=A
有點不懂意思~

作者: starry1314 時間: 2015-4-25 17:10

回復 5# ML089

不好意思~已了解意思
但如果設在 right設在H1
實際個數為10,會顯示成9

作者: starry1314 時間: 2015-4-25 17:15

回復 7# starry1314

不好意思~沒事,是我眼殘看錯了....

作者: ML089 時間: 2015-4-25 18:15

請問right=(G1) 的涵義是??
因我儲存格G1內容為台中=A
有點不懂意思~
starry1314 發表於 2015-4-25 17:06

right(G1) 就是 right(G1,1) 的省略寫法

台中=A 建議分為上下2列台中 / A，可以簡化程式及計算時間

作者: starry1314 時間: 2015-4-25 20:17

right(G1) 就是 right(G1,1) 的省略寫法

台中=A 建議分為上下2列台中 / A，可以簡化程式及計算時 ...
ML089 發表於 2015-4-25 18:15

那也是一樣的寫法嗎?

作者: starry1314 時間: 2015-4-25 20:26

right(G1) 就是 right(G1,1) 的省略寫法

台中=A 建議分為上下2列台中 / A，可以簡化程式及計算時 ...
ML089 發表於 2015-4-25 18:15

了解~感謝指點

作者: ML089 時間: 2015-4-25 20:27

回復 10# starry1314

就是 MID($A$1A$49,2,1)=RIGHT(G1) 變成 MID($A$1A$49,2,1)=G1，少一個函數運算

作者: starry1314 時間: 2015-4-26 14:15

本帖最後由 starry1314 於 2015-4-26 14:17 編輯

回復 12# ML089

不好意思~可再請問一下可以做多條件嗎?

原本只要讀取第二個字母的即可,
那如果像這樣
4B2CV
4C20BV
4B3B
4B4C

V的位置統一在最後一個值,和V的代號是統一固定的
可以讀取到有B和V的=1
                     有C和V的=1
                     第二個為B的=2

可用萬用字元嗎?  如>? >*

作者: ML089 時間: 2015-4-26 18:40

回復 ML089

不好意思~可再請問一下可以做多條件嗎?

原本只要讀取第二個字母的即可,
那如果 ...
starry1314 發表於 2015-4-26 14:15

   不好意思~可再請問一下可以做多條件嗎?

原本只要讀取第二個字母的即可,
那如果像這樣
4B2CV
4C20BV
4B3B
4B4C

V的位置統一在最後一個值,和V的代號是統一固定的
可以讀取到有B和V的=1
                     有C和V的=1
                     第二個為B的=2

可用萬用字元嗎?  如>? >*

固定字可以用 MID、RIGHT、LEFT，不固定字可以用FIND
例如
有B和V的=1， (RIGHT(A1:A4)="V")*COUNT(FIND("B",A1:A4))
有C和V的=1， (RIGHT(A1:A4)="V")*COUNT(FIND("C",A1:A4))
第二個為B的=2， (MID(A1:A4， 4,1)="B")*2

作者: starry1314 時間: 2015-4-26 20:17

不好意思~可再請問一下可以做多條件嗎?

原本只要讀取第二個字母的即可,
那如果像這樣
4 ...
ML089 發表於 2015-4-26 18:40

那要怎麼加入進去在SUM(IF(MID($A$1:$A$49,2,1)=RIGHT(G1),1/COUNTIF($A$1:$A$49,$A$1:$A$49)))
這裡面呢?

目的是:讓有A代號裡面又有V的可以統計有幾個如下圖所示,不好意思麻煩妳了~如果可以的話可以幫我註解功能是什麼嗎? 麻煩您了
[attach]20800[/attach]

作者: ML089 時間: 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

之前的公式大致是某城市的不重複數，你目前要改為什麼? 沒有目地沒有辨法應套。

作者: starry1314 時間: 2015-4-26 23:13

本帖最後由 starry1314 於 2015-4-26 23:15 編輯

回復 16# ML089
如下圖所示,原本是計算出第2個位置帶A的不重複數,
現在想在屬於這個條件計算出的不重複數再從中找出帶著V的數量
[attach]20803[/attach]

作者: ML089 時間: 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))

作者: starry1314 時間: 2015-4-27 00:15

回復 18# ML089

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

作者: ML089 時間: 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啟動計算，然後...去喝杯咖啡...上上廁所...休息一下，應該會跑很久不要以為是當掉。

作者: starry1314 時間: 2015-4-27 10:59

不好意思~如果再A12欄輸入數據也是會出現錯誤,
我再將(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3

A$9999))*1,{1;1})=2)
改為更前方範圍一樣大小也是會出錯呢,
如果說使用COUNTIF很耗資源的話,還是說有遇到空白就自動停止繼續計算下去呢,
因我資料如果有1000筆就1~1000都有資料,1000就等於結束不會再有資料可以計算,有辦法偵測空白就停止嗎

作者: starry1314 時間: 2015-4-27 11:02

(MMULT(ISNUMBER(FIND(MID(D$2,{1,2},1),$A$3

A$5000))*1,{1;1})=2))-E3
還是會導致有空白就計算錯誤呢,
我再將紅字範圍更改為較大也是會出錯

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

作者: starry1314 時間: 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))

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

作者: ML089 時間: 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))

作者: starry1314 時間: 2015-4-27 14:47

回復 24# ML089

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

作者: ML089 時間: 2015-4-27 14:50

回復 25# starry1314

COUNTIF(Rng,Rng)

Rng 資料範圍超過3000筆就會很慢

作者: starry1314 時間: 2015-4-27 15:19

回復 26# ML089

了解～目前約在１０００～１５００之間！要到３０００還要有點時間～但到那時資料庫應該已經建置完成，就沒關係了!感謝幫忙囉

作者: starry1314 時間: 2015-4-28 14:53

回復 26# ML089

不好意思~遇到個問題！！　如果　４Ｐ５ＢＶ（含有ＰＢＶ）　　　４Ｂ５Ｖ（含有ＢＶ）　會導致重複計算　請問可指定最右邊數來是Ｖ且在第２欄位是Ｂ的嗎
[attach]20814[/attach]

[attach]20815[/attach]

作者: ML089 時間: 2015-4-28 15:29

回復 ML089

不好意思~遇到個問題！！　如果　４Ｐ５ＢＶ（含有ＰＢＶ）　　　４Ｂ５Ｖ（含有ＢＶ ...
starry1314 發表於 2015-4-28 14:53

編碼規則及取碼規則不是講得很清楚，
若沒有辦法講不是很清楚時，請作示範例比較知道你要如何取?

編碼規則目前是前4碼固定有，第5碼 V 不一定有
４Ｐ５ＢＶ你要比較的碼有字元第2、4、5字，這與上方的 BV 、PV要如何對應? 有時對第2碼、有時對第4碼，這樣是看不出規則

作者: starry1314 時間: 2015-4-28 18:11

回復 29# ML089
不好意思~已附上檔案並加說明,

[attach]20818[/attach]
[attach]20817[/attach]

作者: ML089 時間: 2015-4-28 20:39

回復 30# starry1314

是否是這樣，取第2碼與第5碼比較
D3 =SUMPRODUCT(1/COUNTIF(rng,rng)*(MMULT((MID(rng,{2,5},1)=MID(D$2,{1,2},1))*1,{1;1})=2))
右拉

J3 =SUM(D3:I3)
K3 =SUMIF(D2:I2,"?V",D3:I3)

作者: starry1314 時間: 2015-4-28 23:54

回復 29# ML089

版主~不好意思在提醒內有看到妳回復~但文章沒有出現....沒有看到內容

作者: ML089 時間: 2015-4-29 09:02

回復 32# starry1314

取第2碼與第5碼比較
D3 =SUMPRODUCT(1/COUNTIF(rng,rng)*(MMULT((MID(rng,{2,5},1)=MID(D$2,{1,2},1))*1,{1;1})=2))
右拉

J3 =SUM(D3:I3)
K3 =SUMIF(D2:I2,"?V",D3:I3)

作者: starry1314 時間: 2015-4-29 15:36

回復 33# ML089

感謝版大辛苦解決,剛發現如果數量做到10位數的話,第5位元就被移到第6了,

我還是向主管建議將代號換掉好了, 感謝熱心幫忙

作者: starry1314 時間: 2015-6-19 17:32

回復 33# ML089

板大~不好意思遇到個問題

rag=定義名稱
=SUMPRODUCT(1/COUNTIF(rng,rng))

如果rag範圍內有空白的話他也會算為一個數量,請問有辦法解決嗎?

如再3~300列之間
10~15列是空白的話~ <<會當為一個數量

作者: ML089 時間: 2015-6-19 23:17

回復 35# starry1314

=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ")

作者: starry1314 時間: 2015-6-19 23:43

回復 36# ML089

感謝幫了大忙~減去空白！!!

作者: starry1314 時間: 2015-6-21 10:58

回復 36# ML089

大大~
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ")
如果是使用連結出現的值呢? 空白它變成0
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng,"0") 但是他會計算到重複數量  如有十個空白格他就會扣除總數10 其實只需要扣除1

改用
=SUMPRODUCT(1/COUNTIF(rng,rng)) - SUMPRODUCT(1/COUNTIF(rng,"0")) 會無法計算...
目前使用
因目前10個人內只要有一個人是不要計算的話就會固定出現十個空白值
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ")  /10  我目前是這樣使用可正確計算,但想請問如果遇到不固定空白格呢?

想法是用SUMPRODUCT(1/COUNTIF(rng,rng))  取出不重複值的數量方式使用在空白連結儲存格內,
也就是 SUMPRODUCT(1/COUNTIF(rng,"0"))
但好像不能這樣寫?因沒有計算值出來

作者: starry1314 時間: 2015-6-21 14:16

本帖最後由 starry1314 於 2015-6-21 14:17 編輯

回復 36# ML089

改用
=SUMPRODUCT(1/COUNTIF(rng早,rng早)) -SUMPRODUCT(1/COUNTIF(rng早,"0"))
會比實際數量多0.85
加上
=SUMPRODUCT(1/COUNTIF(rng早,rng早)) -SUMPRODUCT(1/COUNTIF(rng早,"0")) -0.85

再把格式調整不顯示剩餘位數也是可達到目標

作者: starry1314 時間: 2015-6-22 11:10

回復 36# ML089

遇到新問題...
-SUMPRODUCT(1/COUNTIF(rng早,"0")) 如果沒有空白的連結儲存格的話 ,就會無法計算...
會出現
#DIV/0

作者: starry1314 時間: 2015-6-22 12:03

回復 36# ML089

應該算是解決了吧...
=SUMPRODUCT(1/COUNTIF(rng早,rng早)*(rng早>0))
只統計大於0個數

作者: ML089 時間: 2015-6-22 21:14

因目前10個人內只要有一個人是不要計算的話就會固定出現十個空白值
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ") /10 我目前是這樣使用可正確計算,但想請問如果遇到不固定空白格呢?

改為
=SUMPRODUCT(1/COUNTIF(rng,rng)) - (COUNTIF(rng," ")>0)

作者: starry1314 時間: 2015-6-26 15:21

本帖最後由 starry1314 於 2015-6-26 15:23 編輯

回復  starry1314

取第2碼與第5碼比較
D3 =SUMPRODUCT(1/COUNTIF(rng,rng)*(MMULT((MID(rng,{2,5},1)=MID(D$2,{1,2},1))*1,{1;1})=2))
右拉

J3 =SUM(D3:I3)
K3 =SUMIF(D2:I2,"?V",D3:I3)
ML089 發表於 2015-4-29 09:02

[/quote]
大大~不好意思
想請問 01Q001AV  這樣的格式要如何改呢?  第8碼不一定有
         12A002BV
         01Q002A
D3 =SUMPRODUCT(1/COUNTIF(rng,rng)*(MMULT((MID(rng,{3,7},1)=MID(D$2,{1,2},1))*1,{1;1})=2))

但無法計算呢?

[attach]21266[/attach][quote]

作者: starry1314 時間: 2015-6-26 22:55

回復 42# ML089

已解決...原來是我少算一格

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