尋找最近的數字 - 我附近的健康食品

內容

在實踐中，很多時候你和我需要在一組（表）中找到與給定數字相關的最接近的值。例如，它可能是：

根據數量計算折扣。
獎金數額的計算取決於計劃的執行情況。
根據距離計算運費。
為貨物等選擇合適的容器

此外，根據情況，可能需要上下四捨五入。

有幾種方法——明顯的和不那麼明顯的——來解決這樣的問題。讓我們依次看看它們。

首先，讓我們假設一個供應商提供批發折扣，折扣的百分比取決於購買的商品數量。例如，購買超過 5 件時，給予 2% 的折扣，而從 20 件起購買時 - 已經 6% 等。

輸入所購商品數量時如何快速漂亮地計算折扣百分比？

方法 1：嵌套 IF

系列中的一種方法“有什麼想法 - 你需要跳！”。使用嵌套函數 IF （如果） 依次檢查單元格值是否落入每個區間並顯示相應範圍的折扣。但是這種情況下的公式可能會變得非常麻煩：

尋找最近的號碼

我認為很明顯，調試這樣一個“怪物娃娃”或嘗試在一段時間後為其添加一些新條件是很有趣的。

此外，Microsoft Excel 對 IF 函數有嵌套限制——舊版本為 7 次，新版本為 64 次。如果你需要更多怎麼辦？

方法 2. 帶區間視圖的 VLOOKUP

這種方法要緊湊得多。計算折扣百分比，使用傳說中的函數 VPR （VLOOKUP） 在近似搜索模式下：

尋找最近的號碼

哪裡

B4 – 我們正在尋找折扣的第一筆交易中商品數量的價值
$G$4:$H$8 – 折扣表的鏈接 – 沒有“標題”，地址用 $ 符號固定。
2 — 折扣表中我們要從中獲取折扣值的列的序號
TRUE ——這就是“狗”被埋葬的地方。如果作為最後一個函數參數 VPR 指定 說謊（錯誤的） 或0, 然後函數會尋找 嚴格匹配 在數量列中（在我們的例子中，它會給出 #N/A 錯誤，因為折扣表中沒有值 49）。但如果相反說謊寫 TRUE （真的） 或1, 那麼該函數將不會尋找精確的，而是 最近最小的 價值並將給我們所需的折扣百分比。

這種方法的缺點是需要按第一列對折扣表進行升序排序。如果沒有這樣的排序（或以相反的順序進行），那麼我們的公式將不起作用：

尋找最近的號碼

因此，這種方法只能用於找到最接近的最小值。如果您需要找到最近的最大的，那麼您必須使用不同的方法。

方法 3. 使用 INDEX 和 MATCH 函數查找最近的最大值

現在讓我們從另一面來看我們的問題。假設我們銷售幾種不同容量的工業泵型號。左側的銷售表顯示了客戶所需的功率。我們需要選擇最接近的最大或相等功率的泵，但不能低於項目要求。

VLOOKUP 函數在這裡沒有幫助，所以你將不得不使用它的模擬——一堆 INDEX 函數（指數）和更多暴露（匹配）:

尋找最近的號碼

在這裡，最後一個參數為 -1 的 MATCH 函數以查找最接近的最大值的模式工作，然後 INDEX 函數從相鄰列中提取我們需要的模型名稱。

方法 4. 新增功能 VIEW (XLOOKUP)

如果您有安裝了所有更新的 Office 365 版本，則不是 VLOOKUP （VLOOKUP）您可以使用它的模擬 - VIEW 功能（X查找），我已經詳細分析過：

尋找最近的號碼

這裡：

B4 – 我們正在尋找折扣的產品數量的初始值
$G$4:$G$8 – 我們正在尋找匹配的範圍
$H$4:$H$8 – 您要從中返回折扣的結果範圍
第四個參數（-1) 包括搜索我們想要的最接近的最小數字，而不是精確匹配。

這種方法的優點是不需要對折扣表進行排序，並且可以搜索，如果需要，不僅可以搜索最近的最小值，還可以搜索最近的最大值。在這種情況下，最後一個參數將是 1。

但是，不幸的是，並不是每個人都擁有這個功能——只有 Office 365 的快樂所有者。

方法 5. 電源查詢

如果您還不熟悉用於 Excel 的強大且完全免費的 Power Query 加載項，那麼您就在這裡。如果您已經熟悉，那麼讓我們嘗試使用它來解決我們的問題。

我們先做一些準備工作：

讓我們使用鍵盤快捷鍵將源表轉換為動態（智能）按Ctrl+T 或團隊 主頁 – 格式為表格（首頁——表格格式）.
為了清楚起見，讓我們給他們起個名字。 銷售額 и 折扣選項卡 構造函數（設計）.
使用按鈕將每個表依次加載到 Power Query 從表/範圍 選項卡 數據（數據——來自表格/範圍）. 在最新版本的 Excel 中，此按鈕已重命名為 有葉子（來自工作表）.
如果表具有不同的列名稱和數量，如我們的示例中（“貨物數量”和“來自...的數量”），那麼它們必須在 Power Query 中重命名並命名相同。
之後，您可以通過在 Power Query 編輯器窗口中選擇命令返回 Excel 主頁 — 關閉並加載 — 關閉並加載... （首頁 — 關閉並加載 — 關閉並加載到…） 然後選項 只需創建一個連接（僅創建連接）.
然後最有趣的開始了。如果您有 Power Query 方面的經驗，那麼我認為進一步的思路應該是使用連接查詢（合併）a la VLOOKUP 合併這兩個表，就像之前的方法一樣。事實上，我們需要在添加模式下進行合併，這乍一看並不明顯。在 Excel 選項卡中選擇 數據——獲取數據——合併請求——添加（數據——獲取數據——組合查詢——追加） 然後是我們的桌子 銷售額 и 折扣在出現的窗口中：
點擊後 OK 我們的桌子將被粘成一個整體——在彼此下方。請注意，這些表中的商品數量列在彼此之下，因為。他們有相同的名字：
如果 sales 表中的原始行序列對您很重要，那麼在所有後續轉換之後您可以恢復它，使用以下命令將編號列添加到我們的表中 添加列 - 索引列（添加列 - 索引列）. 如果行的順序對您來說不重要，那麼您可以跳過這一步。
現在，使用表格標題中的下拉列表，按列排序數量上升：
主要技巧：右鍵單擊列標題折扣選擇一個團隊 填充 - 向下（填充 - 向下）. 空單元格空自動填寫以前的折扣值：
仍然通過按列排序來恢復原始的行序列 Index 指數 （您可以稍後安全地刪除它）並使用過濾器刪除不必要的行空按列 交易代碼: