Board logo

標題: 若次大值的個數大於1個以上時該如何設定公式 [打印本頁]
作者: sptakung1    時間: 2013-10-14 11:39     標題: 若次大值的個數大於1個以上時該如何設定公式

[attach]16329[/attach]煩請各位前輩解惑，題目及問題於附加檔案內，謝謝
作者: ML089    時間: 2013-10-14 14:38

文字描述不如將解答範本，這樣配合你的說明才能快速理解
作者: sptakung1    時間: 2013-10-14 16:58

回復 2# ML089
[attach]16332[/attach]
1.首先感謝ML089大大您的回覆及指教。
2.針對ML089大大的指教予以修正說明：
如附件1的答案如若2013/10/1可得到需求，
但若是如2013/10/2時次大值的個數大於1個
以上時將無法得到需求，因為以目前我的公式
會使得每個次大值皆會去扣減某值，我的需求是
從任一次大值儲存格扣除某值。如此說明不知是否
有改善ML089大大所提的缺失。
3.因為自己的魯莽而造成發信在不對的區域，若是版主
同意是否可將本主題移至一般區，在此為自己不慎的態度
致歉。(原想在一般區重發主題，但如此做有灌水之疑，所以放棄此想法)
作者: owen06    時間: 2013-10-14 23:43

回復 3# sptakung1


小弟不是很厲害，不太懂陣列，只會這樣改，你看看這樣可以嗎？
作者: owen06    時間: 2013-10-15 00:05

回復 3# sptakung1


順便跟你說一下公式函意，
因為你是要算人腦判斷的第二大值，你可以用腦袋輕鬆判斷出同時好多個最大值之外的第二大值，
可是電腦在同時有好幾個最大值的時候(假設有六個8)，他的第一~六大值，都是判斷為8，直到第七大值，才會是實際上我們人腦認定的第二大值。
因此我們就可以先算出共有幾個最大值：=COUNTIF(octor2,MAX(octor2))      答案為6
所以我們了解到在octor2範圍裡，我們所要的第二大值，其實在電腦的判斷裡實際是第七大值。
那我們就可以運用技巧設定出我們實際上所要的第二大值：=LARGE(octor2,COUNTIF(octor2,MAX(octor2))+1)   
以上，希望對你有幫助。
作者: ML089    時間: 2013-10-15 10:04

回復 3# sptakung1


R3:AC4 範圍陣列公式
=B3:M4-IF(MOD(LARGE(B3:M4*10^9+ROW(B3:M4)*1000+COLUMN(B3:M4),2),10^9)=ROW(B3:M4)*1000+COLUMN(B3:M4),N4,0)

先選擇 R3:AC4 範圍
輸入公式以 CTRL+SHIFT+ENTER 三鍵齊按輸入公式
作者: owen06    時間: 2013-10-15 10:43

天呀，我從頭到尾都搞錯了原PO的意思了，真是不好意思@@
作者: sptakung1    時間: 2013-10-15 12:48

回復 7# owen06

owen06大大：謝謝您精彩的解說，因為我的語文表達能力太差，造成您誤解題意，甚感抱歉，再次感謝您的熱情答覆。
作者: sptakung1    時間: 2013-10-15 12:53

回復 6# ML089

ML089大大您好：您的答案完全滿足我的需求，謝謝您。不過此答案的邏輯還真夠我瞧的，在此先收下再慢慢研究了。
再次感謝您。
作者: ML089    時間: 2013-10-15 17:27

回復 9# sptakung1

這是借腹生子的技巧
B3:M4*10^9+ROW(B3:M4)*1000+COLUMN(B3:M4) 將原儲存格數值與該儲存格 ROW()、COLUMN()的位置封裝為一個數值再用 LARGE()取出第二大數值，
用 LARGE()取出第二大數值後，再用MOD()函數將去掉原儲存格數值(前面公式有些小錯誤要修正，詳後)，進行ROW與COLUMN位置的比對，成功位置就需要減掉N4之值。


公式修正如下(將MOD(... , 10^9)改為MOD( ... , 10^6)
=B3:M4-IF(MOD(LARGE(B3:M4*10^9+ROW(B3:M4)*1000+COLUMN(B3:M4),2),10^6)=ROW(B3:M4)*1000+COLUMN(B3:M4),N4,0)

公式限制1 :儲存格範圍 ROW()、COLUMN() <= 999
公式限制2 :儲存格數值小數位 <= 3
作者: sptakung1    時間: 2013-10-16 11:06

回復 10# ML089

謝謝ML089大大，經過您的解釋已清楚，真是開眼界了好一個"借腹生子"的方法
，感謝您熱心的教學。這問題已困惑我一段時間，總想不出好的解決方式今已豁然開朗
再次感恩。



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