寻找最近的数字 - 我附近的健康食品

内容

在实践中，很多时候你和我需要在一组（表）中找到与给定数字相关的最接近的值。例如，它可能是：

根据数量计算折扣。
奖金数额的计算取决于计划的执行情况。
根据距离计算运费。
为货物等选择合适的容器

此外，根据情况，可能需要上下四舍五入。

有几种方法——明显的和不那么明显的——来解决这样的问题。让我们依次看看它们。

首先，让我们假设一个供应商提供批发折扣，折扣的百分比取决于购买的商品数量。例如，购买超过 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 功能（XLOOKUP），我已经详细分析过：

寻找最近的号码

这里：

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 （您可以稍后安全地删除它）并使用过滤器删除不必要的行空按列 交易代码: