若次大值的個數大於1個以上時該如何設定公式

sptakung1

TEST.rar (7.74 KB)

下載次數: 9

2013-10-14 11:38

煩請各位前輩解惑，題目及問題於附加檔案內，謝謝

ML089

文字描述不如將解答範本，這樣配合你的說明才能快速理解

sptakung1

回復 2# ML089
TEST1.zip (9.21 KB)

下載次數: 8

2013-10-14 16:57

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

owen06

回復 3# sptakung1


小弟不是很厲害，不太懂陣列，只會這樣改，你看看這樣可以嗎？

owen06

回復 3# sptakung1


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

ML089

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

天呀，我從頭到尾都搞錯了原PO的意思了，真是不好意思@@

sptakung1

回復 7# owen06

owen06大大：謝謝您精彩的解說，因為我的語文表達能力太差，造成您誤解題意，甚感抱歉，再次感謝您的熱情答覆。

sptakung1

回復 6# ML089

ML089大大您好：您的答案完全滿足我的需求，謝謝您。不過此答案的邏輯還真夠我瞧的，在此先收下再慢慢研究了。
再次感謝您。

ML089

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