請問關於統計不重複項目的個數"指定條件" - Excelㄧ般區

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

1^# 跳轉到 »

發表於 2015-4-25 16:58 | 顯示全部帖子

回復 4# starry1314

指定條件不重複_ML089.rar (6.75 KB)

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2015-4-25 18:15 | 顯示全部帖子

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

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

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2015-4-25 20:27 | 顯示全部帖子

回復 10# starry1314

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 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

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 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

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 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

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

8^#

發表於 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

ML089