在 Excel 中查询另一个表

Question

我想从另一个表中的特定列中提取所有值。我的目标是采用几个不同的表，并将每个表中的特定列放入一个经过整理的表中。

例如，假设我有关于不同类型对象的表。

FRUITS
name         flavor
banana       savory
orange       sweet
peach        sweet

PETS
name         lifespan
dog          long
fish         short
cat          long

想象一下，我现在想制作第三个表，其中包含水果和宠物的名称列。

COLLATED
name      source 
banana    fruits
orange    fruits
peach     fruits
dog       pets
fish      pets
cat       pets

我尝试安装 powerpivot 加载项来执行此操作，但我不确定如何使用 Mac 执行此操作。如果可能的话，我更喜欢使用 Excel 提供的任何"表连接"功能。

Answer 1

来自@Ike和@JosWoolley的想法的组合将是这样的：

=LET(
n,{"Fruits","Pets","Cars"},
w,(tblFruits[Name],tblPets[Name],tblCars[Name]),
y,COUNTA(w),
s,SEQUENCE(AREAS(w)*y,,0),
q,1+QUOTIENT(s,y),
z,CHOOSE({1,2},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q)),
FILTER(z,INDEX(z,0,1)<>""))

对于新表，表名称将添加到n变量中，列/范围将添加到w变量中，而无需编辑公式的其余部分。

编辑 #1

使用此方法添加更多列可能会变得棘手，但可以做到。例如，在所有表格中都有一个额外的"价格"列将需要这样的东西：

=LET(
n,{"Fruits","Pets","Cars"},
w,(tblFruits[Name],tblPets[Name],tblCars[Name]),
p,(tblFruits[Price],tblPets[Price],tblCars[Price]),
y,COUNTA(w),
s,SEQUENCE(AREAS(w)*y,,0),
q,1+QUOTIENT(s,y),
z,CHOOSE({1,2,3},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q),IFERROR(INDEX(p,1+MOD(s,y),,q),"")),
FILTER(z,INDEX(z,0,1)<>""))

其中，您有一个额外的p变量，并且CHOOSE已更新以反映新值。当然，您可以通过更改 3 个部分的顺序或简单地更改{1,2,3}数组中的数字来更改CHOOSE中列的顺序(例如{1,3,2})。

Answer 2

=LET(w,(tblFruits[name],tblPets[name],tblCars[name]),x,AREAS(w),y,COUNTA(w),z,IFERROR(INDEX(w,1+MOD(SEQUENCE(x*y,,0),y),,1+INT(SEQUENCE(x*y,,0)/y)),""),FILTER(z,z<>""))

根据需要修改表列名称，根据需要添加尽可能多的名称。

这应该适用于相当小的范围，尽管x*y当然可以作为下限进行改进。

同意 Ike 的观点，递归 lambda 可能会在这里有所帮助。

Answer 3

我在工作表中添加了两个表：tblFruits和tblPets。

然后，您可以将以下公式放在同一张纸或另一张纸上的任何单元格中。

=LET(
a,CHOOSE({1,2},tblFruits[name],"Fruits"),
b,CHOOSE({1,2},tblPets[name],"Pets"),
rowIndex,SEQUENCE(ROWS(a) + ROWS(b)),
colIndex,SEQUENCE(1,COLUMNS(a)),
IF(rowIndex<=ROWS(a),
INDEX(a,rowindex,colIndex),
INDEX(b,rowindex-ROWS(a),colIndex)
)
)

公式的前四行用于检索变量，然后在最终的 if 函数中使用：

a和b将返回每个名称列的"虚拟"数组以及给出类型的"new"列。

rowIndex返回单个数组 {1,2,...(两个表的行数)}

colIndex返回一个数组，该数组由列数构建 - 在本例中为 2(名称和类型)

这些变量用于IF-formula： 可以将其视为For i = 1 to Ubound(rowIndex)循环。

如果 -ArrayrowIndex中的第一个值小于tblFruits的行数，

• 则INDEX-result 基于虚拟阵列a，
• 如果不是，则计算b的行索引INDEX并且 -result 基于虚拟数组b。

结果是一个溢出数组- 您可以在其上使用过滤器。只需添加一个标题行并添加过滤器。

但是，您将无法基于它创建表。 因此，您必须使用VBA来创建组合数据。

这将是带有第三个表的公式：

=LET(
a,CHOOSE({1,2},tblFruits[Name],"Fruits"),
b,CHOOSE({1,2},tblPets[name],"Pets"),
c,CHOOSE({1,2},tblRooms[name],"Rooms"),
rowIndex,SEQUENCE(ROWS(a)+ROWS(b)+ROWS(c)),
colIndex,SEQUENCE(1,COLUMNS(a)),
IF(rowIndex<=ROWS(a),
INDEX(a,rowIndex,colIndex),
IF(rowIndex<=ROWS(a) + ROWS(b),
INDEX(b,rowIndex-ROWS(a),colIndex),
INDEX(c,rowIndex-(ROWS(a)+ROWS(b)),colIndex))))