Bodhidharma

回復  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

回復 31# Bodhidharma

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

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

感謝你們熱心的幫忙.....感激不盡!!

Bodhidharma

回復  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

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.
範列檔案
眾數_ML089.rar (6.71 KB)

下載次數: 3

2013-4-7 00:59

Bodhidharma

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

另外我也有思考是否能增加組名與"眾數"的對照欄
不過因為原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

回復 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欄的值，就能得到答案
眾數.rar (12.04 KB)

下載次數: 5

2013-4-7 09:41

ML089

回復 36# Bodhidharma

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

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

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

台端上面的分析說明寫的很棒，應該給你一個讚。

ML089

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

第一個最高次數的值
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(),"")

眾數0407.rar (23.43 KB)

下載次數: 2

2013-4-7 11:02

Bodhidharma

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

第一個最高次數的值
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非遞增時候該函數的運算過程？