☰

根据项目名称在两个电子表格之间复制数据

tag-icon • tag-icon microsoft-excel
根据项目名称在两个电子表格之间复制数据

我有两个电子表格。其中一个叫做列表_工作表A，其中包含产品及其营养价值的列表。列包括蛋白质，碳水化合物，胖的，物品。这三个值均以每 100 克列出，最后一个是产品名称。反过来，第二张表计算表 B包含饮食计算，其中包含相同的列，此外还包含数量。第一个电子表格中的字段由我填写。

我的问题是：

  1. 能否查找表“ 卢布 ”查找“该解决方案中的函数是否正确？

  2. 我如何自动或通过使用 Python 中的宏或脚本来计算蛋白质，碳水化合物和胖的只需在物品和数量在第二个电子表格中？

  3. 我能否获得有关在哪里寻找类似信息或解决方案示例的提示或建议？

编辑

阅读完所有评论后，我调整了以下公式C2：

=IFERROR(VLOOKUP($I2;$Products.$A$2:$G$100;MATCH(C$1;$Products.$B$1:$G$1;0)+1;0)*$J2/100;0)

我将其用作参数，如附图所示床单。

答案1

您可以使用VLOOKUP()（HLOOKUP()此处不适用，因为您的数据是上/下，而不是左/右）。但要这样做，您必须移动查找值的列，因为VLOOKUP()只查找查找值的右侧（Item），并且正如您描述的设置，查找值（Item）当前位于最右侧。

我猜你会很乐意这么做。如果不是，那么你必须使用不同的方法。新XLOOKUP()功能是理想的，但如果你的 Excel 版本中没有它，那么这种INDEX/MATCH方法在很长一段时间内一直是互联网上最流行的方法。在很多网站和 YouTube 上的很多视频中很容易找到对它的解释。

要计算您询问的三个字段中的每一个，只需使用Item（VLOOKUP()如果您将Item列移动到 A 列）或进行查找XLOOKUP()即可。在同一个公式中，只需将每个结果乘以这些字段右侧的总克数（这里有一个拼写错误：顺便说一下，应该是“数量”），然后除以每个输入值所基于的 100 克。因此，通用公式将是“

=VLOOKUP result * Quantity / 100

我保证，这很容易做到。当您VLOOKUP()为第一个写上时，只需将其复制到第二个和第三个，然后根据需要进行编辑。为了使操作更简单，请注意查找值将始终位于 D 列，仅在公式所在的行中发生变化，因此请将 a 添加$到列而不是行：$D2例如。这样做的好处是它将复制到右侧保持不变，因此不需要编辑。当您将第一个公式复制到右侧时，第二个参数的列范围（VLOOKUP 语音中的“查找表”）确实需要更改，但仅复制范围的第二部分，而不是第一列，因此请$在范围的第一部分使用 a，而不要$在第二部分使用 a：例如 $A2:B3001。然后复制到右侧两个单元格将看到每个查找范围的后半部分为您更改。请确保对第四个参数使用“FALSE”或“0”，否则您将回到这里报告它并不总是正常工作。因此，我提到的编辑可以简化为将第二和第三个查找的查找列从 2 更改为 3 和 4。甚至这也可以“自动化”，因为您可以使用表达式，COLUMN()+1该表达式会在 A 列中为您提供所需的“2”，依此类推。然后，您只需向右复制并向下复制您必须编写的单个公式。

就是这样。

现在，在上一段中，我告诉了您一两种“巧妙”编写公式的方法，这样您只需编写一个公式，就可以将其复制到右侧和下方，而不必编写三个公式。这样做很好，因为每次您（人类）编写公式而不是让 Excel 编写公式时，您都有可能犯错。但是，您可能很容易想添加另一种巧妙的方法，尽管您似乎不太可能需要它。不过，如果您愿意，这是一个很好的练习，可以在其他时候使用。

基本思想是VLOOKUP()通过使用匹配输出页面列的标题和数据表上这些数据列的标题来选择返回列（第三个参数，表示“返回此列的数据”）MATCH()。它将返回您检查的范围内匹配的序数，但作为基数，您可以在中使用VLOOKUP()。因此，它会查找“Carbs”并发现它与数据表上查找列表中的第一个项目匹配，您可以为该列提供该数字，该列VLOOKUP()应该从中返回一个值...好吧，您必须考虑它与查找相比可能存在怎样的偏移（这意味着在本例中将 1 添加到其结果中），但是如果您变得更聪明，您可以为其指定一个查找范围，该范围以查找表所在的同一列开始，因此它完全匹配并且不需要偏移。因此，您可以使用MATCH()下面的函数来执行此操作：

=MATCH( A1, 'list_SheetA'!A1:D1, 0 )

（这就像以HLOOKUP()某种方式执行某个操作，也许您之前已经在使用它的应用程序中接触过它HLOOKUP()。有些用途确实如此，所以......但是，MATCH()这里仍然需要，因为您只需要一个数字，而不是返回值。）

如果你愿意的话，你可以将之前的各种“光滑度”应用到此，但是我现在已经用完了，所以我把这个留给你了。

请注意： 如果您执行此匹配操作，则可以防止在数据表中添加或删除列，至少在现有列之间（并且可以通过简单地将范围扩展到 E1 来覆盖“它们之后”，因此即使在 D 和 E 之间添加一列仍然可以工作）因为在发生列混乱之后仍会查找标题名称，并且始终返回它实际所在的列。然而，这些列名必须始终匹配，对吧？因此，建议您不要输入一组列名，而是使用公式来显示它们。这样，无论如何，它们都会始终匹配。并且处理列添加/删除，因为 Excel 会更改它们的列引用以保持正确。因此，在输出表上，使用“=B1”、“=C1”和“=D1”……更好的方法是，创建命名范围，命名这三个名称，然后为两组标题输入“=Proteins”等。这在几个方面增加了价值，但显然，虽然 3 列很酷，但 23 列需要 23 个命名范围可能会开始……让您恼火。您可以使用自动命名，但如果您的标题采用复杂的名称（空格、Alt-Enter 等），这会变得复杂。

好吧，除非我严重误解了，否则你真正想要的只是事情在这里遥远的地方结束，所以我将结束。

答案2

如果您使用的 Excel 版本不支持 XLOOKUP，那么您可以使用以下公式：

=VLOOKUP($G2,$A$2:$D$5,2,FALSE)*$H2/100

已修改您的工作表名称和表格位置。

要使用 VLOOKUP，请将输入的“项目”移动到 Sheet1 上要查找的值的左侧。

在此处输入图片描述

在您的公式中，A2:D5 部分将是 Sheet1 的范围。

这个公式是有效的，我们可以快速检查出，由于每 100 克李子含有 3 克蛋白质，因此 160 克李子含有3 x 1.6 = 4.83 克蛋白质。

如果您使用的是最新版本的 Excel，并且可以访问 XLOOKUP 公式，则只需将此公式放在每行的第一列中即可获得相同的结果：

=XLOOKUP($G2,$A$2:$A$5,$B$2:$D$5)*$H2/100

XLOOKUP 很棒，因为它可以返回多列数据，所以实际上它只是查找包含您要查找的项目的行，然后返回该行的所有营养信息。

在此处输入图片描述

答案3

您可以尝试这种方法，因为它是一种计算每种水果营养成分的智能方法。

在此处输入图片描述

怎么运行的：

  • 工作表 2 中单元格 N41 中的公式：

    =SUMPRODUCT(((Sheet1!$M$33:$M$36=$M41)*(Sheet1!$N$32:$P$32=N$40)*Sheet1!$N$33:$P$36))*N$39/100

注意：

  • 单元格 N39、O39 和 P39 具有可变的权重，并且也可编辑。

  • 您可以根据需要调整公式中的单元格引用。

:编辑:

我发布这个方法是因为 OP 想要计算每种水果不同数量的营养成分，其中每种水果都显示为重复数据。

在此处输入图片描述

怎么运行的

  • F、G 和 H 列是辅助列。

  • 如图所示，在 F 列中输入水果的名称。

  • 在 G 列中重复值，在 H 列中变量数量。

  • 工作表 2 单元格 J2 中的公式：

    =IFERROR(IF(COUNTIF(J$1:J1,J1)<IFERROR(VLOOKUP(J1,$F$2:$G$5,2,FALSE),0),J1,INDEX($F$2:$F$5,MATCH(0,INDEX(COUNTIF(J1:J$1,$F$2:$F$5&""),0,0),0))&""),"")

  • 在 Sheet 2 的单元格 K2 中输入以下公式：

    =IF(ROW()>COUNTA(H:H)*12,"",INDEX(H:H,MOD(ROW()-2,COUNTA(H:H))+1))

  • 工作表 2 单元格 L2 中的最终公式：

    =SUMPRODUCT(((Sheet1!$A$2:$A$5=$J2)*(Sheet1!$B$1:$D$1=L$1)*Sheet1!$B$2:$D$5))*$K2/100

注意：

  • 在整个范围内填充公式。
  • 根据需要调整公式中的单元格引用。

相关内容