question

MiniMa-9997 avatar image
0 Votes"
MiniMa-9997 asked emilyhua-msft commented

EXCEL 資料位置範圍運算

想請問有一欄具波峰形式的數值(非規律0遞增到一個峰值後又非規律遞減到0,
我以IF函數以及MAX函數寫一個尋找某接近值的對應值,
會求出可能在遞增數列裡或是遞增數列裡的值,
所以我想在尋找的範圍值裡就先寫下只搜尋0到遞增數列的峰值這範圍,
所以想請問如核對資料位置範圍作函數取值,
謝謝
如範例圖93785-1620193221871.jpg93805-1620193236067.jpg


office-excel-itpro
1620193221871.jpg (43.3 KiB)
1620193236067.jpg (31.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft edited

@MiniMa-9997

Please note, currently Q&A forum support language is English, to better help other forum uses who encounter similar issue and read this thread, could you please translate your post in English? Thanks for your understanding.

According to your description, I created a sample like yours.
93889-image.png

I used the following array formula =MAX((V1:V21=MAX(V1:V21))*ROW(V1:V21)) to get the row number of max value. The result of my sample is 11.

Then I use the following array formula to get the value that matches the condition and from continuously growing data area. please do not forget to press Ctrl+Shift+Enter.
=MAX(IF(V$1:INDIRECT("V$"&MAX((V1:V21=MAX(V1:V21))*ROW(V1:V21)))<35,V$1:INDIRECT("V$"&MAX((V1:V21=MAX(V1:V21))*ROW(V1:V21)))))
93899-9.png



If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (12.7 KiB)
9.png (13.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.