☰

从特定表格格式检索数据

tag-icon • tag-icon microsoft-excel
从特定表格格式检索数据

我在使用 Excel 时遇到了一些问题。想知道是否有人可以帮我解答？

假设你有一个包含两张表的 Excel，sheetA 和 sheetB

在 sheetA 上，您有如下表格

item 1
a                         value
b                         value
c                         value
d                         value
item 2
a                         value
b                         value
c                         value
d                         value
item 3
a                         value
b                         value
c                         value
d                         value

.... 等等

另外，我们不能假设任何固定的位置，因为有时我们可能有一个没有任何子项目的项目，或者比其他项目有更多的子项目，例如：

item X
a                        value
c                        value
d                        value
item Y
a                        value
b                        value
c                        value
d                        value
e                        value
item Z
c                        value
d                        value
e                        value
f                        value

在 sheetB 上我想检索特定的子项值，例如

第 3 项

b（我想使用一个函数来查找并找到本例中的第 3 项，并返回 b 的值

最有效的方法是什么？你能帮助我吗？

例如

在 SheetA 上我们有

Item1 a 700 b 500 c 800 Item2 a 750 b 550 d 320（Item2 中没有“c”，而是“d”）Item3 b 1020（Item3 中没有“a”）c 740 d 120
e 470（Item3 中多了一个“e”）

现在在同一个工作簿但不同的工作表中，我想要一个公式来返回 b 的值，例如，不同的项目 1,2,3，如下所示：

Item2，b = 函数将返回 550 Item3，b = 函数将返回 1020 Item1，b = 函数将返回 500

这可以在 Excel 中完成吗？问候

答案1

首先，假设 Sheet1 的 A 列中的项目是唯一的。然后运行 ​​VLOOKUP：

=VLOOKUP("b", Sheet1!$A:$B, 2, FALSE)

这将在范围 Sheet1!$A:$B 的第一列中查找第一个完全匹配项，然后返回范围第 2 列中的相应值。VLOOKUP 的高级和较新的兄弟是 XLOOKUP，它提供了更多的自由度（搜索列不必是第 1 列，您可以从下往上搜索等）。您还可以将 VLOOKUP 分为两个步骤：

=MATCH("b", Sheet1!$A:$A, 0)

返回第一个匹配项发生的行号作为索引，然后：

=INDEX(Sheet1!$B:$B, MATCH("b", Sheet1!$A:$A, 0))

返回 B 列中匹配行号的值。

解释的原因很简单。普通的 VLOOKUP 不行。我们需要进行两次 MATCH 搜索。首先，我们需要找到“项目 3”的行号，然后使用第一个 MATCH 作为初始点。因此，第一次出现“项目 3”的行索引是

=MATCH("Item 3", Sheet1!$A:$A, 0)

搜索“b”的范围是

=INDEX(Sheet1!$A:$A, MATCH("Item 3", Sheet1!$A:$A, 0)+1):INDEX(Sheet1!$A:$A, MATCH("Item 3", Sheet1!$A:$A, 0)+10)

假设最多有 10 个子项目（您可以根据需要将 +10 设置得尽可能大）。

现在让 MATCH 在上述范围内搜索正确的“b”，它将返回行索引偏移量（关系到起始位置计算如下）：

=MATCH("b", INDEX(Sheet1!$A:$A, MATCH("Item 3", Sheet1!$A:$A, 0)+1):INDEX(Sheet1!$A:$A, MATCH("Item 3", Sheet1!$A:$A, 0)+10), 0)

最后，使用 INDEX 进入 B 列起始位置+行索引偏移量：

=INDEX(Sheet1!$B:$B, MATCH("Item 3", Sheet1!$A:$A, 0) + MATCH("b", INDEX(Sheet1!$A:$A, MATCH("Item 3", Sheet1!$A:$A, 0)+1):INDEX(Sheet1!$A:$A, MATCH("Item 3", Sheet1!$A:$A, 0)+10), 0))

Ps. Excel 365 版本使用 LET 变量：

=LET(item, "Item 3", subitem, "b", maxsubitems, 10, itemrow, MATCH(item, Sheet1!$A:$A, 0), itemrange, INDEX(Sheet1!$A:$A, itemrow+1):INDEX(Sheet1!$A:$A, itemrow+maxsubitems), rowoffset, MATCH(subitem, itemrange, 0), INDEX(Sheet1!$B:$B, itemrow + rowoffset))

相关内容