找出各群組的眾數(不含0) - Excelㄧ般區

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

12^#

發表於 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的方式來處理

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

13^#

發表於 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的需求了

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

14^#

發表於 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非遞增時候該函數的運算過程？

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

15^#

發表於 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)的值的數量

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

16^#

發表於 2013-4-8 16:52 | 顯示全部帖子

回復 43# Bodhidharma

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

frequency函數模擬.rar (10.37 KB)

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

17^#

發表於 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的列後一列

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

18^#

發表於 2013-4-8 21:19 | 顯示全部帖子

回復 46# Bodhidharma

把整個函數寫在一起，基本上應該就滿清楚的
請大家幫我看一下這樣的概念正不正確

frequency函數說明.rar (10.83 KB)

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

19^#

發表於 2013-4-9 19:47 | 顯示全部帖子

回復 48# ML089

感謝說明

我試圖把公式寫在同一行，不過發現功力不足
直好另外加一行，把原本的結果當成輔助列

直覺上應該是要用定義名稱，不過我定義名稱還不夠熟
不會處理這種有動態範圍的公式：IF(COUNTIF(OFFSET($B$2,,,ROW()-1,),B2)>1,0,......)

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

frequency函數說明(處理非數字).rar (12.37 KB)

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

20^#

發表於 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與定義名稱"，應該有把問題講的比較清楚一點