Board logo

標題: [發問] 找出各群組的眾數(不含0) [打印本頁]

作者: PJChen    時間: 2013-4-1 23:17     標題: 找出各群組的眾數(不含0)

大家好,

我想找出各群組的眾數(不含0),如下解說,請問公式可以怎麼下呢?
A欄代表群組
B欄是各個數值
C欄要以公式標示出 以A欄群組為主,將B欄出現最多次的眾數找出來(不含0)

P.S. 數據會一直增加,C欄的公式要從C2開始SHOW,找出各群組的眾數(不含0),並在C欄顯示每個群組第一個出現的眾數,非眾數則掛0

[attach]14510[/attach]
作者: Hsieh    時間: 2013-4-2 10:52

回復 1# PJChen

試試看附件
[attach]14511[/attach]
作者: PJChen    時間: 2013-4-2 15:33

回復 2# Hsieh

大大,

這樣的結果看來是正確的,不過請問大大,可否幫忙想個不用定義名稱的公式?
因為我有幾個檔,到最後還要合併,太多的定義名稱公式,目前對我來說實在是消化不良....
作者: PJChen    時間: 2013-4-2 18:01

回復 2# Hsieh

大大,

完了....我要合併這個資料及之前標題"求公式,不知如何下標題?"完全跑不動.

請問定義名稱會讓檔案跑的速度變慢嗎?我昨天打開"求公式,不知如何下標題?"中的檔案,跑了一個小時一直無法存檔,連帶電腦其他已經打開的軟體(如:OUTLOOK),
全都慢得要當機了.....

好苦惱!!
作者: Hsieh    時間: 2013-4-2 18:31

回復 4# PJChen
這兩個檔案其實都是陣列公式的運用
尤其使用2007以上版本,如果記憶體不足,資料量又大時更會造成當機可能
這應該從整體流程上來檢討
就如同"不知如何下標題"中已經很多是用已算出的結果當作輔助欄來計算
作者: PJChen    時間: 2013-4-2 20:52

回復 5# Hsieh
大大,
公司的電腦開這個檔案要一個小時以上!!我已經不敢貿然開啟了,若我把公司的電腦依以下方式,將「值」設為「2」,會不會更好?
Excel會出現 「"系統資源不足,無法完全顯示"」
方法:
記憶體系統資源不足
1、執行 regedit,進入登錄編輯。
2、找
「HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Current
Version\Explorer」
3、在右方窗格新增一個「AlwaysUnloadDll」「字串值」。
4、在「AlwaysUnloadDll」上按下滑鼠右鍵選擇「修改」。
5、將「值」設為「1」。
作者: Hsieh    時間: 2013-4-2 22:10

回復 6# PJChen

更改登錄值有用嗎?
電腦記憶體多大?要使用2007以上版本最好有大於2G的記憶體
我的筆電雙核心處理器+2G記憶體跑起來是OK的
作者: PJChen    時間: 2013-4-3 00:37

回復 2# Hsieh
大大,

有沒有辦法幫我將檔案改為不要自訂義的公式呢?
我改了一晚,一直做不出來,越作越錯!!
作者: PJChen    時間: 2013-4-3 14:07

回復 8# PJChen

求...熟悉函數公式的能人,可以幫助修改,將檔案中的"自定義公式"全部改掉??這只是一個範例檔,資料會一直增加,最好不要使用陣列公式.....

[attach]14525[/attach]
作者: Bodhidharma    時間: 2013-4-3 16:47

回復  PJChen

求...熟悉函數公式的能人,可以幫助修改,將檔案中的"自定義公式"全部改掉??這只是一個範例 ...
PJChen 發表於 2013-4-3 14:07


定義名稱看起來很好用啊,實在搞不懂為什麼一定要改掉
又不要定義名稱又不要用陣列,這個有也強人所難吧…


一個疑問:你要的是該群組「第一次出現」的眾數,還是該群組「最大」的眾數?
Hsieh版的公式似乎是顯示「最大」的眾數(以A組資料來看,是3.3,但是第一次出現的眾數是3.1)

如果硬是要的話大概就像附檔這樣吧(僅處理到「第一次出現」的眾數,因此沒有把原定義名稱中的a拿來用)
作者: PJChen    時間: 2013-4-3 17:16

回復 10# Bodhidharma

用自定義公式其實是很簡潔,但這個檔案與我另一個檔有關聯(求公式,不知如何下標題?),另一個檔案因為在公司一直快當掉,導致開了檔就無法工作了,所以我才想將自定義名稱改掉.
在我來說,一個再好用的產品,如果我連打開都有問題,怎麼使用它呢??這不是強人所難,是真的有困難.
當然,我知道大家或許很難理解我對檔案的諸多要求,但不管要求是什麼,我都要想辦法去達成它,工作就是工作啊....
事實上,我使用office 2010差不多二年了,也只有最近的這個檔案才有這個困擾,其他檔案比這個公式更多且一個檔就超過1GB也沒有這種問題....
好了,其實我花在解釋這件事的字數實在是太多了,只是希望各位不要再問我為什麼不要用"自定義公式"....

還是談這個檔案關於公式:眾數
我想找出各群組的眾數(不含0),如下解說,請問公式可以怎麼下呢?
A欄代表群組(每個群組的個數都不一定,數據也會增加)
B欄是各個數值
C欄要以公式標示出 以A欄群組為主,將B欄出現最多次的眾數找出來(不含0)...例:群組A出現最多次的數值是3.3,則將3.3標示在第一次出現的C5儲存格.
作者: Bodhidharma    時間: 2013-4-3 17:27

回復  Bodhidharma

用自定義公式其實是很簡潔,但這個檔案與我另一個檔有關聯(求公式,不知如何下標題?), ...
PJChen 發表於 2013-4-3 17:16


我覺得開啟有困難應該不一是定義名稱的問題吧(不過你另一個資料有點複雜,我也要研究一下為什麼我開會出問題)

然後A組資料中3.1和3.3都出現4次,為什麼是選3.3呢?
作者: PJChen    時間: 2013-4-3 17:33

回復 12# Bodhidharma

不好意思,因為我要的眾數是連續型的,A組的3.1不是連續的可以忽略它.
至於自定義的問題,我自行小試了一下,不要用自定義的時候,檔案就正常,所以我假設是這個問題...
作者: Bodhidharma    時間: 2013-4-3 18:29

回復  Bodhidharma

不好意思,因為我要的眾數是連續型的,A組的3.1不是連續的可以忽略它.
至於自定義的問 ...
PJChen 發表於 2013-4-3 17:33


hmmm...所謂的連續是什麼意思呢?假設(同組)資料如下:
1
1
2
2
1
1
1
3
3
3
2
這樣子到底要標哪一個呢?是第5列的1嗎?
作者: Hsieh    時間: 2013-4-3 21:31

回復 13# PJChen

既然你試著不使用定義名稱就可以使用,那還有甚麼問題?
作者: PJChen    時間: 2013-4-3 23:06

回復 15# Hsieh

大大,

我的作法是暫時的,只是為了可以打開檔案,然後將公式改手動後存檔(否則一打開還沒使用就當了),將原來使用自定義名稱的所有公式全部值化,等於都沒有公式,所以當然可以很順暢,當然我是針對會當機的那個檔: 求公式,不知如何下標題?
不是指眾數的這個!!
作者: Bodhidharma    時間: 2013-4-3 23:13

本帖最後由 Bodhidharma 於 2013-4-3 23:15 編輯

回復 16# PJChen

試寫了一個,加了一個輔助欄,其實邏輯幾乎跟Hsieh板主一樣

定義名稱純粹只是讓公式不要太醜,如果真的那麼討厭的話,直接代入即可
作者: PJChen    時間: 2013-4-3 23:14

回復 14# Bodhidharma

數值是由一個機器所讀出的,要拿來作分析用的,一連串的數值,頭尾為剛開機及關機的數值,所以都是由0慢慢變大,中間較相近的值,是機器穩定時的值.
如下的數值,是一小段區間的值,眾數是3.8126,所以不會有你所說的情形...

0.0000
0.9533
3.8369
3.7636
3.8126
3.8126
3.8126
3.8126
3.7636
3.7636
3.7636
3.7882
0.0000
作者: Hsieh    時間: 2013-4-3 23:33

回復 18# PJChen

Bodhidharm的檔案再加一輔助欄位不用定義

   [attach]14530[/attach]
作者: PJChen    時間: 2013-4-3 23:35

回復 15# Hsieh

大大,

另一個檔已獲得美滿的解決,只剩眾數這個檔了,不知您還有時間也幫我將這個資料改為一般公式,不要用自定義公式嗎?
作者: Bodhidharma    時間: 2013-4-3 23:35

回復  Bodhidharma

數值是由一個機器所讀出的,要拿來作分析用的,一連串的數值,頭尾為剛開機及關機的數值 ...
PJChen 發表於 2013-4-3 23:14


0
1
1
2
2
2
2
3
3
1
1
1
1
0
這樣是要標2(最早出現的"連續出現最大值")還是要標後面的1(總共出現最多)?
作者: PJChen    時間: 2013-4-3 23:40

回復 19# Hsieh
回復 21# Bodhidharma

感謝大大,我才剛回另一篇,您們已經作完檔案又回覆了,請忽略我前面的回覆,說不出的感謝,二位....
作者: Bodhidharma    時間: 2013-4-3 23:44

回復 19# Hsieh


這樣子寫的話如果有兩個眾數的話會出問題
作者: Bodhidharma    時間: 2013-4-4 00:01

回復  Hsieh


這樣子寫的話如果有兩個眾數的話會出問題
Bodhidharma 發表於 2013-4-3 23:44



仔細看了一下,有點看不懂板主的邏輯
「該組眾數」會回傳該組"最後一個出現的眾數"
然後「眾數」那欄會回傳:"如果該列的值是眾數,就回傳該組最後一個出現的眾數
所以會變成這樣子:
數值 眾數
1 0
2 3
2 0
3 3
3 0
4 0
1 0
0 0
好像怪怪的
作者: Hsieh    時間: 2013-4-4 00:07

回復 23# Bodhidharma

條件越多就越麻煩,輔助欄位就要增加

    [attach]14531[/attach]
作者: PJChen    時間: 2013-4-4 00:26

回復 25# Hsieh
回復 24# Bodhidharma

謝謝啦!!幫我發現問題...
我再拿新的檔查看一下數據,至少以我個人來說,比較能看懂,多幾行公式沒關係的!!
作者: Bodhidharma    時間: 2013-4-4 00:30

回復  Bodhidharma

條件越多就越麻煩,輔助欄位就要增加
Hsieh 發表於 2013-4-4 00:07



越看越不懂…雖然多了幾個輔助欄,但是結果似乎和你原本的版本不會有差異
1 0
2 3
2 0
3 3
3 0
4 0
1 0
0 0
的現象還是存在啊

不過我覺得跟本的問題是出現原po沒有講清楚如果有重複的"眾數"要怎麼處理
只顯示第一個:用我原始的檔案
兩個都顯示:C2儲存格=IF(D2=MAX(OFFSET(INDEX(A:A,MATCH(A2,A:A,0),),,3,COUNTIF(A:A,A2),)),B2,0)
只顯示最後一個:Hsieh的第二個輔助欄加我原始的檔案

以及我前面問的:
0
1
1
2
2
2
2
3
3
1
1
1
1
0
這樣是要標2(最早出現的"連續出現最大值")還是要標後面的1(總共出現最多)?
作者: PJChen    時間: 2013-4-4 00:51

回復 27# Bodhidharma
回復 19# Hsieh

請問,當眾數不連續時,如何讓C欄的眾數只出現一次?  [attach]14532[/attach]
群組        數值        眾數        往下數值重複個數        數量        該組眾數        DE欄相乘        G欄最大
C        0        0                1        3.7882        0        8
C        0.44        0        1        1        3.7882        1        8
C        3.9105        0        1        1        3.7882        1        8
C        3.8372        0        1        3        3.7882        3        8
C        3.7882        0        1        4        3.7882        4        8
C        3.7638        0        1        1        3.7882        1        8
C        3.7882        3.7882        2        4        3.7882        8        8
C        3.7882        0        1        4        3.7882        4        8
C        3.8372        0        2        3        3.7882        6        8
C        3.8372        0        1        3        3.7882        3        8
C        3.7882        0        1        4        3.7882        4        8
C        3.0062        0                1        3.7882        0        8
作者: Bodhidharma    時間: 2013-4-4 01:09

回復  Bodhidharma
回復  Hsieh

請問,當眾數不連續時,如何讓C欄的眾數只出現一次?  
群組        數值        眾數 ...
PJChen 發表於 2013-4-4 00:51



所以當有多個眾數的時候,你希望只顯示第一個,
然後只考慮"連續出現最多次",如果分開兩個不同地方出現,就視為不同值?

這樣的話,我原始的檔案(眾數_3)應該就是你要的吧?
作者: PJChen    時間: 2013-4-4 01:16

回復 29# Bodhidharma

所以當有多個眾數的時候,你希望只顯示第一個....yes
然後只考慮"連續出現最多次"....yes
如果分開兩個不同地方出現,就視為不同值?....這個倒不一定,應該是同一組若有二個相同的眾數,以連續值的眾數為優先考量
作者: Bodhidharma    時間: 2013-4-4 01:24

本帖最後由 Bodhidharma 於 2013-4-4 01:27 編輯
回復  Bodhidharma

所以當有多個眾數的時候,你希望只顯示第一個....yes
然後只考慮"連續出現最多次". ...
PJChen 發表於 2013-4-4 01:16



"如果分開兩個不同地方出現,就視為不同值?....這個倒不一定,應該是同一組若有二個相同的眾數,以連續值的眾數為優先考量"
耶…我問題的意思是說
x
x
y
y
y
z
z
z
z
y
y
x
x
x
是否在算數量的時候,即便x跟y的總數有5個,但是因為在不同地方出現,所以被看成"2個x,3個y,4個z,2個y',3個x',所以還是要顯示第一個z?
然後"以連續值的眾數為優先考量"我看不太懂
最後,我的前面那個"眾數_3"的檔案,到底符不符合你的需求啊啊啊?
作者: PJChen    時間: 2013-4-4 01:33

回復 31# Bodhidharma

"眾數_3"的檔案,目前看來符合我的需求,不過我要進一步套進正常的報表中試試看!!

明天開始連續假期,我要4點多要起床,現在我已經腦袋空空了....要改天才能試了!!我會再回覆最終結果....

感謝你們熱心的幫忙.....感激不盡!!
作者: Bodhidharma    時間: 2013-4-4 01:51

本帖最後由 Bodhidharma 於 2013-4-4 01:53 編輯
回復  PJChen

試寫了一個,加了一個輔助欄,其實邏輯幾乎跟Hsieh板主一樣

定義名稱純粹只是讓公式不 ...
Bodhidharma 發表於 2013-4-3 23:13


突然發現我「往下數值重複個數」這欄有點問題,每組的最後一個資料一定會是空格
D2儲存格=IF(($A2=$A3)*($B2>0),IF($B2=$B3,D3+1,1),"")
應該修正為
=IF($B2>0,IF((A2=A3)*($B2=$B3),D3+1,1),"")
作者: ML089    時間: 2013-4-7 01:00

1.
眾數讓我以為是要用 MODE() 函數找出現頻率最高的數,
應該是各組中找出最高連續重複數之第一位數位置於C欄標示其值,其他位置標示為 0

2.
提供一個沒有名稱定義的範例,為加速計算增加輔助欄將每一組最多連續重複的第一數位置標訂出來。
=INT(1%+1/MOD(MAX(INDEX(FREQUENCY(-ROW($1:$88),-ROW($1:$88)*(((A$1:A$88<>G2)+(B$1:B$88<>B$2:B$89)+(B$1:B$88=0))>0))+1/ROW($2:$90),)),1))

3.
C欄的公式就能大幅簡化計算
=IF(A2="","",(VLOOKUP(A2,G:H,2,)=ROW())*B2)

4.
範列檔案
[attach]14549[/attach]
作者: Bodhidharma    時間: 2013-4-7 03:43

本帖最後由 Bodhidharma 於 2013-4-7 03:45 編輯
1.
眾數讓我以為是要用 MODE() 函數找出現頻率最高的數,
應該是各組中找出最高連續重複數之第一位數位置 ...
ML089 發表於 2013-4-7 01:00


=INT(1%+1/MOD(MAX(INDEX(FREQUENCY(-ROW($1:$88),-ROW($1:$88)*(((A$1:A$88<>G2)+(B$1:B$88<>B$2:B$89)+(B$1:B$88=0))>0))+1/ROW($2:$90),)),1))
這個式子真是博大精深!
利用int(1%+1/mod(max(重複次數+1/所在列數),1)
來回傳"重複次數最多,在最前面出現的資料列數"這個用法很有意思
之前有寫過「重複人名表」之類的東西,我使用的方法是將重複次數*10000+row(),(假設資料小於10000筆)
沒想到還有這種處理方式!

另外frequency函數的用法還滿特別的
frequency(負1到負最後一個數字,負1到負最後一個數字,不過同組非零且與前一列相同的話就是0)
不太了解這種用法為什麼能夠做到"眾數"的要求
frequency我看說明一般好像沒有這種非遞增的bin_array用法,不知道原理是什麼?

我原本也有思考是否能夠完全不用輔助欄,不過想不出來
這個公式可以完全達到我輔助欄「往下數值重複個數」的效果!
(不過在公式效率上因為有用到陣列相乘,應該會略遜於用輔助欄的方式)

另外我也有思考是否能增加組名與"眾數"的對照欄
不過因為原po的資料會一直增加,所以很難用動態的方式設定輔助欄
因此後來我就放棄了,還是回到offset的方式來處理
作者: Bodhidharma    時間: 2013-4-7 04:13

另外我也有思考是否能增加組名與"眾數"的對照欄
不過因為原po的資料會一直增加,所以很難用動態的方式設定輔助欄
因此後來我就放棄了,還是回到offset的方式來處理
Bodhidharma 發表於 2013-4-7 03:43


其實也還好,用
{=IFERROR(INDEX(A:A,SMALL(IF(OFFSET($A$2,,,COUNTA(A:A),)=OFFSET($A$3,,,COUNTA(A:A),),"",ROW(OFFSET($A$2,,,COUNTA(A:A),))),ROW(A1))),"")}
之類的公式就可以抓出所有的組名
然後眾數位置那欄再弄個動態範圍基本上就符合原po的需求了
作者: Hsieh    時間: 2013-4-7 09:42

回復 36# Bodhidharma
1.這樣可傳回同群組內B欄各數值的第一次出現的位置,傳回該數值出現次數  
=FREQUENCY(OFFSET($B$1,MATCH($A2,$A:$A,0)-1,,COUNTIF($A:$A,$A2),)*10^0,OFFSET($B$1,MATCH($A2,$A:$A,0)-1,,COUNTIF($A:$A,$A2),)*10^0)
2.這樣判斷是否B欄數值為連續出現
=(FREQUENCY(INDIRECT("RC2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0,INDIRECT("RC2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0)=FREQUENCY(INDIRECT("R[1]C2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0,INDIRECT("R[1]C2:R"&MATCH($A2,$A:$A,0)+COUNTIF($A:$A,$A2)-1&"C2",0)*10^0)+1)
3.C欄只要是以上兩條件吻合就是該群組連續最多的眾數
兩個判斷式相乘再乘上B欄的值,就能得到答案
[attach]14551[/attach]
作者: ML089    時間: 2013-4-7 10:07

回復 36# Bodhidharma

公式速度要快,需要將每一項都會 重複計算的計算數 讓想辦法只計算一次,就需要用 輔助欄 或 定義名稱。

我提供的 輔助欄 公式是採用 擴大範圍 來計算,如果 組項 非常多時可以採用 動態範圍 來替代,可以縮短更多時間。

定義名稱 其實也可以加快計算,並非使用 定義名稱 就會變慢,例如一個公式內會使用好幾次 動態範圍,用 定義名稱 來設 動態範圍 就能節省不少時間。
若 定義名稱 相對於每一個儲存格都是 "引數" 都需要根據這些 "引數s"此做一遍計算,這時反而會變得更慢。

台端上面的分析說明寫的很棒,應該給你一個讚。
作者: ML089    時間: 2013-4-7 11:03

此題採用垂直輔助欄 可以用下列公式較為簡單

第一個最高次數的值
D2 =IF(ROW()=INT(1%+1/MOD(MAX(OFFSET(E$1,MATCH(A2,A:A,)-1,,COUNTIF(A:A,A2))),1)),B2,"")

輔助欄 : 是否連續出現,格式設為整數型態較為美觀
E2 =IF(B2=B3,INT(N(E3))+1+1/ROW(),"")

[attach]14552[/attach]
作者: Bodhidharma    時間: 2013-4-7 13:15

此題採用垂直輔助欄 可以用下列公式較為簡單

第一個最高次數的值
D2 =IF(ROW()=INT(1%+1/MOD(MAX(OFFSE ...
ML089 發表於 2013-4-7 11:03


嗯,一樣始用 int(1%+1/mod(max(重複次數+1/所在列數),1) 的原理
保留了"重複次數"以及"該列在整個工作表的第幾個位置"的資訊
因此在對應的時候,就不用像我的方式offset半天,找「該列在該組的第幾個位置」,可以直接對應「該列在整個工作表的第幾個位置」

不過E2公式
=IF(B2=B3,INT(N(E3))+1+1/ROW(),"")
並沒有處理"不同組"或是"數值為零"的情況,是否應改為
=IF(B2>0,IF((A2=A3)*(B2=B3),INT(N(E3))+1+1/ROW(),1+1/ROW()),"") ?

另外frequency函數我不是很熟
可否解釋一下,當bin_array非遞增時候該函數的運算過程?
作者: Hsieh    時間: 2013-4-7 14:52

回復 40# Bodhidharma
bin_array非遞增數值陣列時
會以小於等於bin_array陣列元素,並扣除其餘元素以計算過之元素作為計算
例如:陣列元素中有重複的值,因為在第一次出現時已經計算過該值以下的出現頻率
所以第二次出現時就會扣除已計算過的結果

[attach]14561[/attach]
作者: ML089    時間: 2013-4-7 18:05

回復 40# Bodhidharma

1.
對,
前E2公式
=IF(B2=B3,INT(N(E3))+1+1/ROW(),"")
並沒有處理"不同組"或是"數值為零"的情況,可以改為
=IF(AND(A2=A3,B2=B3,B2<>0),INT(N(E3))+1+1/ROW(),"")

2.
FREQUENCY看說明確實不容易懂,記得剛開始學此函數時也在網路上找很多實例來了解,仍需做做小模型反覆比較差異才能深入。

FREQUENCY並無 遞增或遞減 的規定
是計算小於或等於bins_array間的data_array數量。

因為題目要求在連續重複數的第一個顯示其值,因標記數要大於等於data_array(該重複數區內)。

例如 A1:A9中A4:A6是重複數 {1,2,3,4,4,4,7,8,9}

一式
=FREQUENCY(ROW(1:9),ROW(1:9)*(L1:L9<>L2:L10))
={1;1;1;0;0;3;1;1;1;0}

二式
=FREQUENCY(-ROW(1:9),-ROW(1:9)*(L1:L9<>L2:L10))
={1;1;3;0;0;1;1;1;1;0}

兩式都可以得最大重複數 3,但位置卻不同。這就是 ROW(1:9) 與 -ROW(1:9) 差一個負號的用意。
作者: Bodhidharma    時間: 2013-4-8 16:04

回復 42# ML089

剛剛測試了一下,不知道這樣的理解正不正確:
frequency(data_array,bin_array)
若data_array的值為d[1],d[2]...d[n],bin_array的值為b[1],b[2]...b[x],回傳result_array的值為r[1],r[2]...r[x+1]
會先搜尋bin_array中,第一個出現最小的值min(bin_array),假設第一個出現的為b[y]
則r[y]=data_array中小於等於b[y]的值的數量
至於不是第一個出現的b[y']=min(bin_array),r[y']的值會是零
然後再搜尋bin_array中第一個出現,第二小的值b[z]
r[z]=data_array中,小於等於b[z]且大於b[y]的值的數量,其它的r[z']的值一樣是零
以此類推,然後r[x+1]=data_array中,大於max(bin_array)的值的數量
作者: Bodhidharma    時間: 2013-4-8 16:52

回復 43# Bodhidharma

稍微模擬了一下,試了一些數字,看起來應該是正確的
(懶得用動態範圍,因此把可輸入的資料限定在100列以內)

[attach]14584[/attach]
作者: ML089    時間: 2013-4-8 17:28

回復 43# Bodhidharma


bin_array 不用排序,若有重複數時,計算值會標註於第一次出現之數位置其他為0
作者: Bodhidharma    時間: 2013-4-8 17:44

回復  Bodhidharma


bin_array 不用排序,若有重複數時,計算值會標註於第一次出現之數位置其他為0
ML089 發表於 2013-4-8 17:28


嗯,反正result_array[x]就是回傳「小於等於bin_array[x],且大於"bin_array中,次小於bin_array[x]"」這個區間的數值個數
如果已經是bin_array中最小的,則回傳所有小於等於該值的個數
若有重複的話就標註第一個,其它為零
然後大於bin_array中最大數值個數的,就放在result_array的列後一列
作者: Bodhidharma    時間: 2013-4-8 21:19

回復 46# Bodhidharma

[attach]14591[/attach]
把整個函數寫在一起,基本上應該就滿清楚的
請大家幫我看一下這樣的概念正不正確
[attach]14592[/attach]
作者: ML089    時間: 2013-4-9 11:24

回復 47# Bodhidharma


如果能模擬出正確答案,對FREQUENCY函數已經充分了解
另外加一點,非數字自動排除,計算值會往前排列
作者: Bodhidharma    時間: 2013-4-9 19:47

回復 48# ML089


感謝說明

我試圖把公式寫在同一行,不過發現功力不足
直好另外加一行,把原本的結果當成輔助列
[attach]14601[/attach]
直覺上應該是要用定義名稱,不過我定義名稱還不夠熟
不會處理這種有動態範圍的公式:IF(COUNTIF(OFFSET($B$2,,,ROW()-1,),B2)>1,0,......)

想請教這種有動態範圍的公式,是否可以用定義名稱處理?
(還是說這跟本不是定義名稱的問題?)

[attach]14602[/attach]
作者: ML089    時間: 2013-4-9 23:05

回復 49# Bodhidharma

I2
=IF(ROW()=COUNTA(B:B)+1,COUNTIF(A:A,">"&MAX(B:B)),IF(OR(ISERR(-B2),MATCH(B2,B:B,)<>ROW()),0,COUNTIF(A:A,"<="&B2)-IF(B2=MIN(B:B),0,COUNTIF(A:A,"<="&SMALL(B:B,COUNTIF(B:B,"<"&B2))))))

一般公式

此公式模擬 FREQUENCY 但沒有處理 bin_array 有非數值之情況,
作者: Bodhidharma    時間: 2013-4-10 02:17

回復 50# ML089


=IF(ROW()=COUNTA(B:B)+1,COUNTIF(A:A,">"&MAX(B:B)),IF(OR(ISERR(-B2),MATCH(B2,B:B,)<>ROW()),0,COUNTIF(A:A,"<="&B2)-IF(B2=MIN(B:B),0,COUNTIF(A:A,"<="&SMALL(B:B,COUNTIF(B:B,"<"&B2))))))

受教了!
用match(B2,B:B,)<>row()來判斷重複,比IF(COUNTIF(OFFSET($B$2,,,ROW()-1,),B2)>1,0,......)漂亮多了
不過想請教一下ISERR(-B2)是要處理什麼狀況?
另外用SMALL(B:B,COUNTIF(B:B,"<"&B2))也很漂亮,不需使用陣列公式就能達到「次小值」MAX(IF(bin_array<B2,bin_array,""))的效果
然後用減的來取代countifs也是個好主義
雖然結構會沒那麼清楚(把我原本4,5結合在一起),但是就公式而言,真的用的很漂亮!

不過我原本的疑問比較是offset和定義名稱的問題
我另外發了一篇"offset與定義名稱",應該有把問題講的比較清楚一點
作者: ML089    時間: 2013-4-10 09:08

ISERR(-B2) 判斷是否為文字,若是文字 -B2 會產生錯誤

COUNIFS 功能強大可是我不太喜歡用,因為很多題目仍需符合 2003版才能適用
作者: PJChen    時間: 2013-4-10 18:05

回復 34# ML089

感謝您有興趣解答這一題,我將公式套入正式的數值中,結果有的眾數不會在第一次出現的位置顯示出來?不知哪兒出錯?

[attach]14619[/attach]
作者: ML089    時間: 2013-4-10 18:25

回復  ML089

感謝您有興趣解答這一題,我將公式套入正式的數值中,結果有的眾數不會在第一次出現的位置顯 ...
PJChen 發表於 2013-4-10 18:05



    我看一半數據了都是在每組眾數第一位置出現,你把看到有問題的位置再告訴我
作者: PJChen    時間: 2013-4-10 21:38

回復 54# ML089

我用黃底標示有問題的部份.

D76
D289
D1637
D1708

[attach]14622[/attach]
作者: PJChen    時間: 2013-4-10 21:47

回復 33# Bodhidharma

抱歉現在才回覆,連休後一上班,每天都忙個半死..
不過我還是要說明,檔案中雖然只有二個自定義,我還是偏愛看得見的公式.
我將公式套入正式的數值中,結果有的眾數不會在第一次出現的位置顯示...
C289
C1708

[attach]14623[/attach]
作者: PJChen    時間: 2013-4-10 21:56

回復 25# Hsieh

版大,我還是要再說一次,感謝你幫忙將自定義改為輔助公式.
我將公式套入正式的數值中,結果有的眾數不會在第一次出現的位置顯示,我可能標得不太好,因為有點錯綜複雜...    [attach]14624[/attach]

C289
C392
C395
C548
C553
C714
C1047
C1052
C1225
C1228
C1708
C1710
C1712
作者: Bodhidharma    時間: 2013-4-10 23:55

回復  Bodhidharma

抱歉現在才回覆,連休後一上班,每天都忙個半死..
不過我還是要說明,檔案中雖然只有二 ...
PJChen 發表於 2013-4-10 21:47



請你把你的需求開清楚
所謂的"眾數"到底是什麼意思?
我前面一直在問,但仍然得不到一個明確的答案

你的測資已經開始讓我一頭霧水了……

[attach]14632[/attach]

(話說我的公式和ML089的結果,唯一的差別是「當所有數都只出現一次時,ML089的公式會漏掉,全部不標」,其它的結果都會一樣…)
作者: ML089    時間: 2013-4-11 00:25

回復  ML089

我用黃底標示有問題的部份.

D76
D289
D1637
D1708
PJChen 發表於 2013-4-10 21:38


目前公式是標示在最大連續數的第一個連續數位置,並不是第一個出現的位置,這也比較合理,同時指出最大連續數的起始位置。
請在思考看看
作者: PJChen    時間: 2013-4-12 01:23

回復 59# ML089
回復 58# Bodhidharma
回復 25# Hsieh

各位,

我解釋為什麼抓不出一個準則的原因:
所有的數值是由一個電表所抓取出來的數據,數據是用來分析一些電方面的耗電、度數、及很多我不懂的專有名詞.
電表是斷續啟動的,約啟動個幾天,就會拿這些數據來作分析,由於它跑出的數值大小都是未知數,所以才要分析,
但各位也看到了數據的一些茫點...抓不出準則不知如何定公式?
我已跟我的user討論過,因為在眾數方面真的很難抓出一個準確的方向,所以我說服他放棄眾數的需求,改用其他方向去抓所需數值,
其實我要求他給我不同天的電表數據,想做"求公式,不知如何下標題?"的驗算,但一拿到手發現,第二份數據套用在前面討論很多天的"求公式,不知如何下標題?",已出現了很多錯誤地方,更別談"眾數"這個議題.

很抱歉提出一個無法解決的問題讓大家傷腦筋! 我自己看到第2份報表也是快腦充血了,還不知道要如何去進行....
作者: Hsieh    時間: 2013-4-14 23:15

回復 57# PJChen

寫的頭有點昏了,看看結果如何?

    [attach]14673[/attach]
作者: PJChen    時間: 2013-4-16 22:08

回復 61# Hsieh

大大好,

我已經告知我的user放棄眾的數值分析方式,因為我想大家已經都被這個議題搞瘋了!想不到您還是繼續研究中,真令人感動...
我想即使我的USER不再使用眾數,但對我來說,它還是一個珍貴的資料,可以當成公式的參考資料.

我驗證了眾數的內容,以A欄的每個群組,取B欄數值的眾數(若有出現同次數者,則取最大值的眾數),
發現第4個群組只有二個數值,但二個都以眾數的形態出現(C288/ C289),其餘看起來都沒有問題.
作者: Hsieh    時間: 2013-4-17 08:46

回復 62# PJChen
再把結果取出最大值
[attach]14685[/attach]
作者: PJChen    時間: 2013-4-17 15:09

回復 63# Hsieh

太好了,果然ok.
這篇真是我的好教材..
TKS.




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