☰

这个公式如何起作用？

tag-icon • tag-icon microsoft-excel
这个公式如何起作用？

我有一个 Excel 文件（Cartel1），其中包含有关一组工人的信息（ID、姓名、姓氏、职位和工资单）。

有关工资单的信息缺失，但在第二个文件（Cartel 2）中可用，其中有更多的个人和相同的变量集。我想将有关工资单的信息从 Cartel 2 导入到 Cartel 1。当然，我只想导入有关 Cartel 1 中个人子集的信息（而不是整个 Cartel 2）。

我被难住了，因为我不知道该怎么做。任何建议我都会非常感激。

卡特尔 1

卡特尔2

答案1

首先，您需要在第一个工作簿中访问第二个工作簿的数据。您可以使用外部引用（如下所述）或将工作簿“Cartel 2”中的数据复制到工作簿“Cartel 1”中名为“来源”的单独工作表中。

现在，要将工资单导入单元格 E2，请使用以下公式：

=INDEX(Source!$E$2:$E$21, MATCH(1, (B2=Source!$B$2:$B$21) * (C2=Source!$C$2:$C$21) * (D2=Source!$D$2:$D$21), 0))

请注意，这是一个 CSE 公式！因此，您需要通过“Control+Shift+Enter”组合键输入（仅“Enter”键不起作用）。

现在，您可以拖动 E2 的公式并填充单元格 E3:E11。

外部引用

如果复制数据不方便，您可能需要使用外部引用。它们用于引用位于其他工作簿中的单元格。引用位于文件夹中工作簿的工作表上的Path[Filename]Sheet-name!Cell坐标单元格。例如，您必须写成而不是。CellSheet-nameFilenamePathSource!$E$2:$E$21/path/to/the/second/workbook[Cartel 2]Foglio1!$E$2:$E$21

这个公式如何起作用？

  • 在 Excel 中（几乎所有地方都是如此），真条件用数字 1 表示，假条件用数字 0 表示。乘法（用星号表示）可用于检查多个条件。例如，=(A1=A1)*(1>0)将导致1因为两个条件都为真且1*1=1，而(A1=A1)*(1<0)将导致0因为第二个条件为假且1*0=0。
  • $用于固定一个值，这样当公式被拖到相邻单元格时，该值不会改变。例如，如果公式被拖到下方的相邻单元格，它将=B2变成。将保持不变。=B3=B$2
  • 外部参考如上所述。
  • 现在，(name=B2:B21)是一个数组公式。它对大小为 20 的数组进行操作（B2:B21）。它将产生一个大小相同的数组：{name=B2, name=B3, ..., name=B21}。
  • 因此name=B2:B21，surname=C2:C21和position=D2:D21将产生三个大小相同的数组 (20)。 乘法将产生一个大小相同的数组，其中包含相应元素的乘积。
  • 现在请注意，此数组将包含1匹配的位置(name, surname, position)，这正是我们所需要的！MATCH可以使用函数来找到该位置。它的一般语法是：
  • MATCH(niddle, haystack, mode)查找具有匹配模式niddle的范围中的值并返回位置。模式表示精确匹配，因此我们的公式如下所示：haystackmode0
  • MATCH(1, (name=B2:B21) * (surname=C2:C21) * (position=D2:D21), 0)
  • 最后，INDEX可以使用函数来检索该职位的工资单。一般语法是：
  • INDEX(range, position)
  • 加起来：
  • INDEX(Source!$E$2:$E$21, MATCH(1, (B2=Source!$B$2:$B$21) * (C2=Source!$C$2:$C$21) * (D2=Source!$D$2:$D$21), 0))

答案2

尝试这个公式：

=SUMPRODUCT((Sheet2!$B$2:$B$21=Sheet1!B2)*(Sheet2!$C$2:$C$21=Sheet1!C2)*(Sheet2!$D$2:$D$21=Sheet1!D2),Sheet2!$E$2:$E$21)

在此处输入图片描述

相关内容