☰

EXCEL - 嵌套范围以供参考

tag-icon • tag-icon microsoft-excel worksheet-function
EXCEL - 嵌套范围以供参考

感谢您阅读我的问题。

我正在尝试查找各种​​尺寸的产品包装的价格点。我在 Sheet1 上有一个数据范围，A1:Z102，其中顶行是包装质量（B1:Z1），第一列（A2:A102）是销售价格。在数组中，我计算了利润率（B2:Z102）

我正在尝试在工作表上自动设置“假设”，以便我输入各种重量“Sheet2！B1：B10” - 这将从“Sheet1！A2：Z2”中选择相应的包装重量列，然后让函数将该列中最接近我的“假设”利润率“Sheet2！A1”的行匹配，最后在 Sheet2、C1：C10 上返回最接近该利润率的销售价格。

SHEET1
       1lbs    2lbs    3lbs    4lbs
$1     $0.10  -$0.80  -$1.80% -$2.80
$2     $1.74   $0.74  -$0.26  -$1.26
$3     $2.61   $1.61   $0.61  -$0.39
$4     ...     ...     ...     $0.47
...

SHEET2
$0.20  2lbs    X
       4lbs    X

[Edit1] 因此该函数将在三个迭代步骤中返回 C1=$2 和 C2=$4（我认为）。

  1. Cell.Sheet2!B1.Value 与 Sheet1 Row1 中的相同列匹配：返回 Y 列
  2. 对于 Sheet1 中的 Y 列，找到最接近单元格的正值。Sheet2A1：返回 Y 列、Z 行
  3. 对于 Sheet1 的 Y 列、Z 行，返回 Sheet1 的第 1 列 Z 行中的值。在 Sheet2 的 C1 中重复该值
  4. 对 B2...Bn 重复 1-3

我很难解决每个步骤：

  1. MATCH 可以识别正确的列
  2. 无法弄清楚如何在“OFFSET”中嵌套 MATCH 以仅搜索 Y 列。我尝试过 INDEX MATCH、INDEX MATCH MATCH、LOOKUPs（V&H）。
  3. 返回 RowZ 中的 Column1 值。

我意识到这可能是一个宏解决方案，但我只是不知道如何使用 VBA 来实现它：

Dim profit, lbs, cost, reflbs, refprofit As Range

Set cost = Worksheets("Sheet1").Range("$A$2", "$A$5")
Set profit = Worksheets("Sheet1").Range("$B$2", "$E$5")
Set lbs = Worksheets("Sheet1").Range("$B$1", "$E$1")
Set reflbs = Worksheets("Sheet2").Range("$B1")
Set refprofit = Worksheets("zTest").Range("$H$39")

For Each profitCell In profit
    For Each costCell In cost
        For Each lbsCell In lbs
            If lbsCell.Value = reflbsCell.Value Then

然后我不确定如何在标题 lbs 与“reflbs”匹配的列中直接进行搜索，然后我输入：

If profitCell.Value = refprofitCell.Value Then

然后返回匹配的行 Z，然后最终返回 Sheet2!C1 中的行 Z、列 A Sheet1!。

非常感谢！

答案1

假设您的源数据布局与您的示例类似，如下所示：

在此处输入图片描述

然后在 sheet2 上，如果您的数据布局如下：

在此处输入图片描述

然后您可以使用以下公式来获取红色的数字：

=INDEX(Sheet1!$B$2:$E$5,IFERROR(MATCH(Sheet2!$A$1,Sheet1!$A$2:$A$5,1),1),MATCH(Sheet2!$B1,Sheet1!$B$1:$E$1,0))

在此处输入图片描述

当您要查找的美元值小于列中的第一个值时，您可能会收到错误。vlookup 和 match 的工作方式是它们不查找最接近的数字值。它们查找小于所查找值的最后一个数字。这就是为什么您的列需要按升序排序的原因。为了处理小于列表中第一个值的合法值，将引发错误的 match 函数包装在 iferror 函数中，该函数返回 1 以表示第一行。请注意，如果在 sheet2!A1 中输入了无效的价格条目，它仍将为第一行返回 1。

对于您的体重，它们实际上很可能是字符串值而不是数字。将您的单位列在单独的单元格中，然后将值列在自己的单元格中，这样就更容易处理这些值了。在这种情况下，我假设您的体重始终与您的某个列标题完全匹配。2.5 磅的体重会引发错误，因为在您的标题列表中找不到它。

相关内容