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

標題: 請問這12萬筆資料裡面我要如何統計 "TRUE"連續出現的次數 [打印本頁]

作者: eric7765 時間: 2017-4-28 14:55 標題: 請問這12萬筆資料裡面我要如何統計 "TRUE"連續出現的次數

[attach]27100[/attach][attach]27100[/attach]
[attach]27101[/attach]
請各位幫幫忙謝謝

作者: ML089 時間: 2017-4-28 16:30

本帖最後由 ML089 於 2017-4-28 16:32 編輯

用名稱定義公式
xT =FREQUENCY(ROW($1:$120000),ROW($1:$120000)*($A$1:$A$120000=FALSE))

最多出現連續幾個
=MAX(xT)-1

連續出現
D3:D32 =FREQUENCY(xT,MOD(ROW(2:30),29))
範圍陣列公式
PS: 複製上述公式，選D3:D32後將公式貼上編輯列，先按CTRL+SHIFT不放，再按ENTER輸入公式，若成功公式前後會有 {.....}

"幾個" C3下拉30個

作者: eric7765 時間: 2017-4-28 17:12

回復 2# ML089
大神抱歉後輩不材 xt的意思是還有最大值的意思我看不太懂

作者: ML089 時間: 2017-4-28 17:20

回復 3# eric7765

[attach]27103[/attach]
看看檔案裡的公式(黃色區)

工具列裡公式 - 名稱管理員，查看 xT 名稱

作者: eric7765 時間: 2017-4-28 17:28

回復 2# ML089 [attach]27104[/attach]

作者: eric7765 時間: 2017-4-28 17:30

回復 4# ML089
非常感謝正在研究中了

作者: eric7765 時間: 2017-4-28 17:36

回復 2# ML089
大神抱歉再打擾您一下黃色區塊裡面的公式看起來都一樣為甚麼顯示出來的值會不一樣呢?
因為我還需要套道別的數據裡所以需要理解清楚一點再麻煩大神了[attach]27105[/attach][attach]27105[/attach]

作者: ML089 時間: 2017-4-28 17:38

回復 7# eric7765

範圍陣列公式
PS: 複製上述公式，選D3:D32後將公式貼上編輯列，先按CTRL+SHIFT不放，再按ENTER輸入公式，若成功公式前後會有 {.....}

一般公式用 ENTER輸入
陣列公式用 CTRL+SHIFT+ENTER 三鍵齊按輸入

作者: eric7765 時間: 2017-4-28 17:45

回復 8# ML089
有成功了我再多練習幾次感激
還有想請教第 28個跟29個為甚麼會有數字呢? 因為最高不是21個嗎?

作者: ML089 時間: 2017-4-28 18:46

回復 9# eric7765

[attach]27106[/attach]
小資料練習，看看公式中FREQUENCY函數每個參數變化，先自行體會，不懂再問。

第一個(內)FREQUENCY統計的數要減1才是連續數
因為陣列有120000個不想先減1，這會增加計算時間，所以用取2代替1，取3代替2，來統計連續數數量
FREQUENCY 第二的陣列數值第一個應該為2開始(2,3,4,5,......,0,1)，最後應該用1收尾，所以使用MOD函數來處理

作者: ML089 時間: 2017-4-28 18:49

本帖最後由 ML089 於 2017-4-28 23:33 編輯

統計每個連續數+1
xT =FREQUENCY(ROW($1:$120000),ROW($1:$120000)*($A$1:$A$120000=FALSE))

統計相同的連續數數量
=FREQUENCY(xT,MOD(ROW(2:30),29))

作者: eric7765 時間: 2017-4-28 19:58

回復 11# ML089
大神版主因為有好幾個欄位需要計算您的方法很好但是變成我不會轉換到別欄因為公式看起來一模一樣不材的我無法推算邏輯
您能教導我如何計算 a欄所有的 true 連續3次的有幾個連續5次的有幾個單純公式這樣我就可以徒法煉鋼慢慢用了

作者: eric7765 時間: 2017-4-28 20:43

回復 11# ML089
大神版主我傳我最後想要做成的表格給您看看
用陣列的方式好像沒辦法改變一部分的公式
您可以看看這個檔案教導後輩一下嗎我可以自己慢慢套進去
真的非常感謝您

[attach]27107[/attach]

作者: ML089 時間: 2017-4-28 21:51

回復 13# eric7765

A欄統計
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>FALSE)),MOD(ROW(2:33),32))

B欄統計
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>FALSE)),MOD(ROW(2:33),32))

C欄統計
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>FALSE)),MOD(ROW(2:33),32))

作者: eric7765 時間: 2017-4-28 21:51

回復 11# ML089
目前 TRUE的部分已經可以以此類推成功了

想請問 FALSE的計算方式不一樣嗎?
=FREQUENCY(ROW($1:$120000),ROW($1:$1000000)*($A$1:$A$1000000=FALSE))
我把上面的 FALSE換成TRUE 以為可以算出FALSE 但是數字算出來是錯誤的

作者: eric7765 時間: 2017-4-28 21:54

回復 14# ML089
謝謝版主已經成功了現在剩下的問題是為什麼 28 29個那邊會出現數字??

作者: eric7765 時間: 2017-4-28 21:57

回復 14# ML089

謝謝版主已經知道問題出在哪裡了也已經解決我的問題非常感謝周末愉快

作者: eric7765 時間: 2017-4-28 22:20

回復 14# ML089
再請教一問題如果今天我統計的資料有10000筆那我應該要更改那些數字? 這個檔案只有 4835筆資料

作者: handmuch 時間: 2017-4-28 22:45

回復 1# eric7765

[attach]27108[/attach]

[attach]27109[/attach]

作者: ML089 時間: 2017-4-28 23:32

回復 18# eric7765

以下資料位於 2:4835列，若有 10000筆表示資料位於 2:10001，將
ROW(2:4835)改為ROW(2:1001)
A2:4835改為A2:A1001

A欄統計
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>FALSE)),MOD(ROW(2:33),32))

B欄統計
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>FALSE)),MOD(ROW(2:33),32))

C欄統計
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>FALSE)),MOD(ROW(2:33),32))

作者: ML089 時間: 2017-4-28 23:36

回復 16# eric7765

28 29個那邊會出現數字?

這是不要的數字(為1及0的統計數)，只是秀出來了解

作者: ML089 時間: 2017-4-28 23:39

回復 15# eric7765

A欄統計
TRUE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>FALSE)),MOD(ROW(2:33),32))

參考上兩式TRUE與FALSE使用差別，因為有空白格時，用 = 容易錯誤，改用 <> 比較好。

作者: eric7765 時間: 2017-4-29 08:12

回復 19# handmuch
謝謝前輩的方法適合我這個新手

作者: eric7765 時間: 2017-4-29 08:24

回復 22# ML089
大神版主真的很感謝你的教導包括之前都習慣用=號的習慣現在又有了新方法
後輩也研究出心得來了
也又想到一個問題想發問不知道這問題是否用EXCEL 能解的出來
[attach]27111[/attach]

[attach]27112[/attach]

再麻煩前輩們指導

作者: ML089 時間: 2017-4-29 09:07

回復 24# eric7765

可以參考19樓方式比較簡單
B1 {=IF(AND(D$2:D$17=A1:A16),1,0)}
下拉
I6 =COUNTIF(B:B,1)

OR

I6 =COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW(1:16)^0)=16))

作者: eric7765 時間: 2017-4-29 17:37

回復 25# ML089
抱歉我試著研究好久但還是得不出結果不知道是我哪裡理解錯誤了
[attach]27114[/attach]

請問是否有跟D2:D7一樣的組合應該要輸入哪個公式?
跟D2:D7一樣的組合總共有幾組應該要用哪種公式?

作者: ML089 時間: 2017-4-29 17:43

[attach]27115[/attach]

作者: eric7765 時間: 2017-4-29 18:24

回復 27# ML089

第一個方法已經了解

想請問如果今天一樣是12萬筆資料  要搜索"B"欄
=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:R)-2,))=N(OFFSET(F2,COLUMN(A:R)-1,))),ROW(1:16)^0)=16))

如果是要搜索"B"欄是否等於 F2:F11的話以上公式應該更改那些地方??
         要搜索"B"欄是否等於 E2:E8 的話??
         假如今天只要搜尋5萬筆資料是否只要將  =COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:R)-2,))=N(OFFSET(F2,COLUMN(A:R)-1,))),ROW(1:16)^0)=16))
         裡面的120000改成50000即可

再麻煩大神版主了  這幾天的問題夠我練習好一陣子了想了解以上這公式的用法之後如果同時很多欄位的話使用上會比較方便

作者: ML089 時間: 2017-4-29 19:50

回復 28# eric7765

A1:A12000 裡等於 D2:D17 數量
=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW(1:16)^0)=16))

B1:B5000 裡等於 F2:F11 數量
=COUNT(0/(MMULT(N(N(OFFSET(B1,ROW(1:5000)+COLUMN(A:J)-2,))=N(OFFSET(F2,COLUMN(A:J)-1,))),ROW(1:10)^0)=10))

A1:A120000改為B:B5000修改
A1改為B1
12000改為5000

D2:D17改為F2:F11修改
D2改為F2
D2:D17為16個，F2:F11為10個，將16改為10，P(=16)改為J(=10)

作者: eric7765 時間: 2017-5-5 10:14

回復 29# ML089
謝謝版主教導
最近發現一個小問題想請教前輩們有沒有方法可以修正
就是如果有增加或是刪除列的話就會出現錯誤有辦法把公式固定住嗎?
如圖我把A1-A12 刪除然後選取下方儲存格上移公式就會跑掉了如何讓公式都是計算最新的資料
[attach]27144[/attach]

[attach]27145[/attach]

作者: eric7765 時間: 2017-5-5 10:15

回復 29# ML089
謝謝版主教導
最近發現一個小問題想請教前輩們有沒有方法可以修正
就是如果有增加或是刪除列的話就會出現錯誤有辦法把公式固定住嗎?
如圖我把A1-A12 刪除然後選取下方儲存格上移公式就會跑掉了如何讓公式都是計算最新的資料
[attach]27144[/attach]

[attach]27145[/attach]

作者: eric7765 時間: 2017-5-5 10:16

回復 29# ML089
謝謝版主教導
最近發現一個小問題想請教前輩們有沒有方法可以修正
就是如果有增加或是刪除列的話就會出現錯誤有辦法把公式固定住嗎?
如圖我把A1-A12 刪除然後選取下方儲存格上移公式就會跑掉了如何讓公式都是計算最新的資料
[attach]27144[/attach]

[attach]27145[/attach]

作者: ML089 時間: 2017-5-8 08:47

回復 30# eric7765

刪除資料會影響公式，公式中的範圍可以用固定範圍 $
A1:A12000 裡等於 D2:D17 數量
=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW(1:16)^0)=16))
改為
=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW($1:$120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW($1:$16)^0)=16))

作者: eric7765 時間: 2017-5-8 10:09

回復 30# eric7765
測試一下好像看不到回覆

作者: eric7765 時間: 2017-5-8 10:16

回復 37# eric7765

=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW($1:$120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW($1:$16)^0)=16))

版主抱歉修改後變成前面紅色這個A1 會變成#REF!
對了謝謝版主上週的幫忙解決了很多問題後輩有幫忙捐3000給財團法人高雄市私立小天使家園替版主增加點福份謝謝您熱心的替大家解決問題

作者: ML089 時間: 2017-5-8 12:47

回復 eric7765

=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW($1120000)+COLUMN(A)-2,))=N(OFFSET(D2,COLUMN ...
eric7765 發表於 2017-5-8 10:16

若連A1都會刪除，需要將A1改為A$1才不會被影響。
=COUNT(0/(MMULT(N(N(OFFSET(A$1,ROW($1:$120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW($1:$16)^0)=16))

謝謝你的善舉，讚! 讚! 讚! 讚! 讚! ...
祝你施比受更有福

作者: eric7765 時間: 2017-5-8 13:27

回復 39# ML089
最近論壇好像怪怪的
我是因為把A1:A12 刪除然後選除下方儲存格上移所以才會出現錯誤鎖定後還是會跑掉還是謝謝版主我已經用巨集的方法解決掉了
[attach]27162[/attach]

作者: eric7765 時間: 2017-5-8 13:41

回復 39# ML089
最近論壇好像傳送圖片跟檔案都會錯誤
有個問題還煩請版主有空時能幫忙想看看是否可以解答  因為無法上傳圖片使用文字敘述想了快一個禮拜還是沒有頭緒
想請問 A欄裡面有 1-10 的數字如何算出連續的 1跟0之間有幾個0    舉例  4-7 中間連續的1跟0 有 "1""0"  所以答案是一個0    13-21之間 1 0 0 1 1 1 0 所以答案是 3個0
            要如何算出整個A欄裡面  連續的1跟0的中間出現 1個0的有幾次一直到出現 30個0 的公式

EX:
      A欄
1 1
2 1
3 2
4 2
5 1
6 0
7 3
8 2
9 1
10 1
11 1
12 2
13 3
14 1
15 0
16 0
17 1
18 1
19 1
20 0
21 2
22 2
23 0
24 0
25 1
26 0
27 0
28 0
29 1
30 0

作者: eric7765 時間: 2017-5-8 13:44

回復 40# eric7765
測試

作者: eric7765 時間: 2017-5-8 13:45

回復 40# eric7765
測試

作者: eric7765 時間: 2017-5-8 13:50

回復 37# eric7765
測試一下好像看不到回覆

作者: eric7765 時間: 2017-5-8 13:56

回復 37# eric7765
再測試一下一直看不到自己回覆的文字

作者: eric7765 時間: 2017-5-8 13:57

回復 40# eric7765
再測試一下一直看不到自己回覆的文字

作者: eric7765 時間: 2017-5-8 14:05

回復 39# ML089
版主這邊的回覆好像都傳不出去不知道您是否有看到我有發一個新帖名叫如何計算出A欄"連續"的0跟1之間有幾個0
再麻煩您幫忙看看

作者: ML089 時間: 2017-5-8 14:38

回復 40# eric7765

修正，若連A1都會刪除，需要將A1改到不會被刪的位置，例如B1才不會被影響，再用OFFSET的 cols偏移-1。
=COUNT(0/(MMULT(N(N(OFFSET(B$1,ROW($1:$120000)+COLUMN(A:P)-2,-1))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW($1:$16)^0)=16))

作者: eric7765 時間: 2017-5-8 15:07

回復 48# ML089
版主抱歉看不到這邊的回覆我有發一個新帖能請您看看嗎?

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