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

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

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

54^#

發表於 2013-4-10 18:25 | 只看該作者

回復 ML089

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

我看一半數據了都是在每組眾數第一位置出現，你把看到有問題的位置再告訴我

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

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

53^#

發表於 2013-4-10 18:05 | 只看該作者

回復 34# ML089

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

眾數_另一解_ML089_MOD_MAX_INDEX_FREQUENCY_ROW.zip (41.71 KB)

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

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

52^#

發表於 2013-4-10 09:08 | 只看該作者

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

COUNIFS 功能強大可是我不太喜歡用，因為很多題目仍需符合 2003版才能適用

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

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

51^#

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

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

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

50^#

發表於 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 有非數值之情況，

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

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

49^#

發表於 2013-4-9 19:47 | 只看該作者

回復 48# ML089

感謝說明

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

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

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

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

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

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

48^#

發表於 2013-4-9 11:24 | 只看該作者

回復 47# Bodhidharma

如果能模擬出正確答案，對FREQUENCY函數已經充分了解
另外加一點，非數字自動排除，計算值會往前排列

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

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

47^#

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

46^#

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