Re: [算表] 抓取各年度各機構之溫塞平均????

看板Office作者JieJuen (David)時間17年前 (2009/02/16 02:11)推噓1(1推 0噓 1→)

留言2則, 2人參與討論串2/2 (看更多)

把要處理的數列先定義起來即IF((.>=.)*(.<=.)*(.=.),.)這部分定為x 如此之前的算式成為=AVERAGE(x) 與 =GEOMEAN(x) =AVERAGE(IF(x<9E+307, CHOOSE(MATCH(x,QUARTILE(x,{0,1,3})),QUARTILE(x,1),x,QUARTILE(x,3)))) =AVERAGE(IF(ISNUMBER(x), IF(x<QUARTILE(x,1),QUARTILE(x,1),IF(x>QUARTILE(x,3),QUARTILE(x,3),x)))) x是沒辦法少用的，光組成新數列就會用到 QUARTILE(x,1), x, QUARTILE(x,3) 這3個再加上判斷上式用到2個下式用4個x 最外面要篩選出數字(上下式兩種方法都可)才丟給AVERAGE 因為當x = QUARTILE(x,1)時丟x或QUARTILE(x,1)都一樣所以等號的位置沒有關係不過確實有點複雜，不放心的話記得與您原本的算法比較看看，不一樣的話要再檢討~ 最保險的算法(對答案時用)：取出單一機構單一年度數據每個數字旁邊都判斷替換成Q1 Q3或原值，求平均當然，單格替換的方法其實也就是上面兩式包在AVERAGE(IF(.,這裡))中的內容 ※ 引述《ljuber (給你吃膨餅)》之銘言： : ※ 引述《ljuber (給你吃膨餅)》之銘言： : : 推 JieJuen:陣列公式 GEOMEAN(IF((.>=.)*(.<=.)*(.=.),.)) 02/13 14:58 : : → ljuber:感謝那如果是quartile函數能否也用陣列寫法? 02/13 17:20 : : → JieJuen:可以~ 目前還沒想到不行的 XD 02/13 19:39 : 最近在想溫塞平均數的寫法 : 定義: : 溫塞平均數（Winsorized mean）; Q1以下的數值和Q3以上的數值，並不是被排除，而是 : 分別用Q1或Q3取代，然後計算平均數，就是溫塞平均數。 : 其中excel的Q1就是用quartile(範圍,1) Q3就是用quartile(範圍,3) : 然後我的想法是 : 把下列幾個公式合起來寫： : COUNTIF(範圍,"=<"&QUARTILE(範圍,1))*(QUARTILE(範圍,1)) : =>計算範圍有多少個小於Q1的數量然後再乘起來 ->A : COUNTIF(範圍,">="&QUARTILE(範圍,3))*(QUARTILE(範圍,3)) : =>計算範圍有多少個大於Q3的數量然後乘起來 ->B : =SUMIFS(範圍,範圍,">"&QUARTILE(範圍,1),範圍,"<"&QUARTILE(範圍,3)) : =>計算範圍介於Q1和Q3中間的總和 ->C : 然後COUNTIF(範圍,">-9E307") 計算範圍有多少個數 ->D : 將上列幾個函數合併：(A+B+C)/D 得出個溫塞平均數 : 不過如果要用陣列函數符合我這個檔案的話 : 有什麼比較好的寫法可以參考一下？ : 希望一個函式可以寫出來不知道各位大大有什麼建議？ : 因為我這樣想出來的公式配合J大的陣列公式下去 : 我覺得公式好長啊....XDXD : (星期天加班在家中想問題真不好玩XD) -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 218.164.52.213 ※ 編輯: JieJuen 來自: 218.164.63.22 (02/16 04:22)