帖子: 32
主題: 10
精華: 0
積分: 68
點名: 0
作業系統: win xp
軟體版本: Office 2003
閱讀權限: 20
性別: 男
來自: 南投縣
註冊時間: 2010-9-28
最後登錄: 2019-10-4

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

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2013-10-14 11:39 | 只看該作者

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

TEST.rar (7.74 KB) 煩請各位前輩解惑，題目及問題於附加檔案內，謝謝

樓主熱帖

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

2^#

發表於 2013-10-14 14:38 | 只看該作者

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

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

TOP

sptakung1

中學生 Rank: 2

帖子: 32
主題: 10
精華: 0
積分: 68
點名: 0
作業系統: win xp
軟體版本: Office 2003
閱讀權限: 20
性別: 男
來自: 南投縣
註冊時間: 2010-9-28
最後登錄: 2019-10-4

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

3^#

發表於 2013-10-14 16:58 | 只看該作者

回復 2# ML089

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

TOP

owen06

　暱稱: 阿軒

中學生 Rank: 2

帖子: 396
主題: 12
精華: 0
積分: 425
點名: 1
作業系統: Win10
軟體版本: Office 2016
閱讀權限: 20
性別: 男
來自: 台中
註冊時間: 2013-7-2
最後登錄: 2024-8-23

2015羊年全勤獎章 2014馬年全勤獎章熱烈參與勳章

4^#

發表於 2013-10-14 23:43 | 只看該作者

回復 3# sptakung1

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

TEST.rar (8.55 KB)

TOP

owen06

　暱稱: 阿軒

中學生 Rank: 2

帖子: 396
主題: 12
精華: 0
積分: 425
點名: 1
作業系統: Win10
軟體版本: Office 2016
閱讀權限: 20
性別: 男
來自: 台中
註冊時間: 2013-7-2
最後登錄: 2024-8-23

2015羊年全勤獎章 2014馬年全勤獎章熱烈參與勳章

5^#

發表於 2013-10-15 00:05 | 只看該作者

回復 3# sptakung1

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

6^#

發表於 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 三鍵齊按輸入公式

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

TOP

owen06

　暱稱: 阿軒

中學生 Rank: 2

帖子: 396
主題: 12
精華: 0
積分: 425
點名: 1
作業系統: Win10
軟體版本: Office 2016
閱讀權限: 20
性別: 男
來自: 台中
註冊時間: 2013-7-2
最後登錄: 2024-8-23

2015羊年全勤獎章 2014馬年全勤獎章熱烈參與勳章

7^#

發表於 2013-10-15 10:43 | 只看該作者

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

TOP

sptakung1

中學生 Rank: 2

帖子: 32
主題: 10
精華: 0
積分: 68
點名: 0
作業系統: win xp
軟體版本: Office 2003
閱讀權限: 20
性別: 男
來自: 南投縣
註冊時間: 2010-9-28
最後登錄: 2019-10-4

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

8^#

發表於 2013-10-15 12:48 | 只看該作者

回復 7# owen06

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

TOP

sptakung1

中學生 Rank: 2

帖子: 32
主題: 10
精華: 0
積分: 68
點名: 0
作業系統: win xp
軟體版本: Office 2003
閱讀權限: 20
性別: 男
來自: 南投縣
註冊時間: 2010-9-28
最後登錄: 2019-10-4

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

9^#

發表於 2013-10-15 12:53 | 只看該作者

回復 6# ML089

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

10^#

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

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

TOP