3871Excel 如何取得特定儲存格下方最接近的五個數值裡其中最大銷量的數值 @ 錦子老師

3871

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

徐小鳯：錦子老師您好，請問如何利用公式找出特定儲存格下方最接近的五個售價中"銷量最大的水果"及帶入該單價與銷量，如下圖的資料。例如：E3輸入水果名，自動帶入其單價與銷量，並在下一列E4:G4利用公式帶入左邊表格中其上方最接近的五個售價中"銷量最大的水果"及帶入該單價與銷量，己試了很多方法都沒用，希望可以教我，感謝！

錦子老師：徐小鳯，這個問題，可以借助MATCH、OFFSET、VLOOKUP函數來完成。

在F3及G3儲存格，我們可以利用VLOOKUP函數來完成。

在F3儲存格輸入公式：「=VLOOKUP($E3,$A$2:$C$19,COLUMN()-4,0)」，再複製到G3儲存格。

由於在F3儲存格的公式是透過$E3儲存格去和A欄比對後抓取符合條件的B欄資料($A$2:$C$19)，而G3儲存格公式是要抓取符合條件的C欄資料，故要將公式所在欄的欄編號減4(COLUMN()-4)。

在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儲存格。

首先我們要抓取E4儲存格位於A2:A19的第幾列(MATCH($E3,$A$2:$A$19,0))。

然後從A1儲存格為起點，抓取其所列下方5列(5)的C欄(2)資料，所以要將MATCH 函數傳回值加1，(OFFSET($A$1,MATCH($E3,$A$2:$A$19,0)+1,2,5,1))。

接著透過MAX傳回這5列中的最大值(MAX(OFFSET($A$1,MATCH($E3,$A$2:$A$19, 0)+1,2,5,1)))。

接著利用MATCH函數傳回最大值位於5列資料中的第幾列(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))。

最後在E4儲存格利用OFFSET函數從A1儲存格，抓取A欄資料，所以要將目前公式所在欄編號減5(COLUMN()-5)，F4抓取B欄資料、G4抓取C欄資料，再將E3儲存格位於A2:A19的第幾列的值加上最大值位於選範圍的第幾列值(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))。