請教如何取得特定儲存格上(下)方最接近的五個數值裡其中最大銷量的數值

Lin sunny3538
個人積分：0分
文章編號：84937220

0分

樓主

2022-05-24 23:09

想請教各位高手

如何利用公式找出特定儲存格上方
及下方最接近的五個售價中
"銷量最大的產品"及帶入該售價與銷量

如圖一的資料
可以利用公式帶入如圖二的結果

自己試了很多方法都沒用
爬很多文也也沒找到解答
希望有好心人可以教我
感謝

請教如何取得特定儲存格上(下)方最接近的五個數值裡其中最大銷量的數值

2022-05-24 23:09 發佈

文章關鍵字儲存格數值銷量

jaredsam

jaredsam
個人積分：1505分
文章編號：84937721

1505分

2樓

2022-05-25 1:57

看有沒有高手
只發現笨方法

E4=INDEX(A:A,MATCH(MAXIFS(C:C,B:B,"<210"),C:C,0))
F4=VLOOKUP(E4,A:C,2,0) <---F4複製貼到G4/F2/G2

E2=INDEX(A:A,MATCH(MAXIFS(C:C,B:B,">210"),C:C,0))

因為max這個函數是字串
比大小要直接數值
因此210不能用F3代替

Lin sunny3538 樓主

謝謝，像我只會用index+match的人來說任何公式都幫助很大^^

2022-05-26 23:47

mk2-paul

mk2-paul
個人積分：345分
文章編號：84940577

345分

3樓

2022-05-25 12:54

E2
=OFFSET(A1,MATCH(MAX(OFFSET(OFFSET(A1,MATCH(E3,A:A,0)-1,0),-1,2,-IF(MATCH(E3,A:A,0)>6,5,MATCH(E3,A:A,0)-2))),OFFSET(OFFSET(A1,MATCH(E3,A:A,0)-1,0),-1,2,-IF(MATCH(E3,A:A,0)>6,5,MATCH(E3,A:A,0)-2)),0)+MATCH(E3,A:A,0)-2-IF(MATCH(E3,A:A,0)>6,5,MATCH(E3,A:A,0)-2),0)

E4
=OFFSET(A1,MATCH(MAX(OFFSET(OFFSET(A1,MATCH(E3,A:A,0)-1,0),1,2,5)),OFFSET(OFFSET(A1,MATCH(E3,A:A,0)-1,0),1,2,5),0)+MATCH(E3,A:A,0)-1,0)

F2
=VLOOKUP(E2,A:C,2,0)
下拉套用

G2
=VLOOKUP(E2,A:C,3,0)
下拉套用

Lin sunny3538 樓主

感謝！立馬來試試看

2022-05-26 23:46

Lin sunny3538 樓主

謝謝mk2-paul大大幫忙解決我長期的困擾^^這公式真的超級符合需求另外想請問一下如果要跨工作表輸入公式的話是在E2跟E4的公式的最前面加上=INDIRECT嗎?很複雜想再請教一下,謝謝

2022-05-27 12:00

mk2-paul

mk2-paul
個人積分：345分
文章編號：84957515

345分

4樓

2022-05-27 12:53

跨工作表還是跨檔案
以下是跨工作表
例如
正常同一個工作表
=A1:A10
將A1到A10框選起來

跨工作表
=工作表2!A1:A10
先點你要的工作表(下面的工作表標籤)，再將A1到A10框選起來

誇檔案
=[活頁簿1.xlsx]工作表1!$A$1:$A$10
='D:\下載資料夾\[活頁簿1.xlsx]工作表1'!$A$1:$A$10
兩個檔案都打開，再框選，檔案關掉會變下面那個
舊版的會不會自己變動我就不清楚了

Lin sunny3538 樓主

mk2-paul大大你好因為回覆留言無法夾帶圖片我已在底下留言中附上圖片說明我的問題還麻煩你教教我如何解決這個問題謝謝^^|||

2022-05-28 0:03

Lin sunny3538

Lin sunny3538
個人積分：0分
文章編號：84962743

0分

樓主

2022-05-28 0:01

mk2-paul大大你好
我遇到的問題是同一個檔案中跨工作頁無法帶入公式,
已做成圖片說明,請你教我如何解決,謝謝

mk2-paul

mk2-paul
個人積分：345分
文章編號：84963986

345分

6樓

2022-05-28 9:25

工作表2
A2
=OFFSET(工作表1!A1,MATCH(MAX(OFFSET(OFFSET(工作表1!A1,MATCH(A3,工作表1!A1:A20,0)-1,0),-1,2,-IF(MATCH(A3,工作表1!A1:A20,0)>6,5,MATCH(A3,工作表1!A1:A20,0)-2))),OFFSET(OFFSET(工作表1!A1,MATCH(A3,工作表1!A1:A20,0)-1,0),-1,2,-IF(MATCH(A3,工作表1!A1:A20,0)>6,5,MATCH(A3,工作表1!A1:A20,0)-2)),0)+MATCH(A3,工作表1!A1:A20,0)-2-IF(MATCH(A3,工作表1!A1:A20,0)>6,5,MATCH(A3,工作表1!A1:A20,0)-2),0)

A4
=OFFSET(工作表1!A1,MATCH(MAX(OFFSET(OFFSET(工作表1!A1,MATCH(A3,工作表1!A1:A20,0)-1,0),1,2,5)),OFFSET(OFFSET(工作表1!A1,MATCH(A3,工作表1!A1:A20,0)-1,0),1,2,5),0)+MATCH(A3,工作表1!A1:A20,0)-1,0)

是樓主打錯了吧，跨工作表確實不會變色
還有為什麼前後要加 " 那是文字化用的

jaredsam

樓主應該是看不懂公式，生硬複製貼上才會不行吧@@，其實還是需要看懂公式才有用，不然下次改個條件就不會了>

2022-05-28 11:32

Lin sunny3538 樓主

真的是需要練習,第一次遇到這麼長的公式,才知道要帶到新報表中要確實檢查公式中每一個標題抬頭跟圈選範圍,如果公式中的對應資料範圍有一個沒改到,資料就不會回傳,至少要花個兩天才能把想要的資料表做好^^||

2022-05-28 12:15

錦子

錦子
個人積分：2074分
文章編號：85121529

2074分

7樓

2022-06-17 21:57

在F3儲存格輸入公式：
「=VLOOKUP($E3,$A$2:$C$19,COLUMN()-4,0)」
，再複製到G3儲存格。
在E2儲存格輸入公式：
「=OFFSET($A$1,MATCH($E3,$A$2:$A$19,0)-6+MATCH(MAX (OFFSET($A$1,MATCH($E3,$A$2:$A$19,0)-5,2,5,1)),OFFSET($A$1,MATCH($E3, $A$2:$A$19,0)-5,2,5,1),0),COLUMN()-5,1,1)」
，再複製到F2:G2儲存格。
在E4儲存格輸入公式：
「=OFFSET($A$1,MATCH($E3,$A$2:$A$19,0)+MATCH(MAX (OFFSET($A$1,MATCH($E3,$A$2:$A$19,0)+1,2,5,1)),OFFSET($A$1,MATCH($E3,$A$2:$A$19,0)+1,2,5,1),0),COLUMN()-5,1,1)」
，再複製到F4:G4儲存格。

錦色如月，子耀光芒。