我有一本包含多张工作表的工作簿,用于跟踪人员及其帐户余额。每个人的第一列都有一个唯一的字母数字 ID,第二列中则显示余额。我希望能够比较两张工作表之间的 ID 号列,并在当前工作表的单元格中将上一张工作表中的相应余额相加,以便跟踪未结余额。
所附图片是我正在使用的简单模型。“测试”表是去年的数字,“结果”表(见此评论) 是我当前的工作表,我试图使用 SUMIF 将“测试”上 A 列中的 ID 与“结果”上 A 列中的 ID 进行比较,以确定“结果”中缺少哪些 ID,然后将“测试”上 B 列中的相应值相加。
我正在寻找“余额”值列的总和,不包括与当前工作表上使用过的 ID 相关联的值。在所附示例中,两个 ID 已用红色框出。从“2016 年 9 月”到“2016 年 10 月”,我与这些 ID 开展了新业务,因此可以假设他们 9 月份的余额已付清。但是,其他三个 ID 仍欠款,因此我希望“2016 年 10 月”的单元格 B8 能够 1) 检查“2016 年 9 月”中列出的 ID 号是否也列在“2016 年 10 月”中,2) 如果不是,则将相应的“余额”相加并显示总数。(在我发布的示例中为 1350)我希望这能让事情更清楚。
表格形式的数据:
2016 年 9 月工作表:
ID Balance
TV14 300
TY44 275
TU25 6690
TU30 650
TN41 775
2016 年 10 月工作表:
ID Balance
TT61
TA82
TU30
TG30
TU25
September Balance 1350
答案1
编辑:按照 OP 的思路
我正在寻找“余额”值列的总和,不包括与当前工作表上使用过的 ID 相关联的值。在所附示例中,两个 ID 已用红色框出。从“2016 年 9 月”到“2016 年 10 月”,我与这些 ID 开展了新业务,因此可以假设他们 9 月份的余额已付清。但是,其他三个 ID 仍欠款,因此我希望“2016 年 10 月”的单元格 B8 能够 1) 检查“2016 年 9 月”中列出的 ID 号是否也列在“2016 年 10 月”中,2) 如果不是,则将相应的“余额”相加并显示总数。(在我发布的示例中为 1350)我希望这能让事情更清楚。
上述评论+OP 的新屏幕截图让位于这一点:
解决方案:
=SUM('September 2016'!$C$2:$C$6)-SUM(SUMIF('September 2016'!$A$2:$A$6,A2:A6,'September 2016'!$C$2:$C$6))
- 剪切/粘贴,更正/修改表格(例如 $A$1:$A$7)以指向
- 完成后不要简单地按回车键,而是
CTRL+SHIFT+ENTER
“告诉 Excel”,“我正在对数据数组求和”。Excel 将自动输入括号(例如 { },参见最后一张图片)。 - 完毕。
解释其工作原理
如果您需要帮助来理解最终解决方案中的程序和思维过程,请继续。
公式是什么?
A:
SUM('September 2016'!$C$2:$C$6)
对 C2 到 C6 的值进行连续求和(例如,单列表格);工作表:2016 年 9 月
乙:
SUMIF('September 2016'!$A$2:$A$6,A2:A6,'September 2016'!$C$2:$C$6)
如果“此”页面 A2 到 A6(ID)上的值出现在工作表 9 月 X 日,则对 9 月 X 日表格 C2 到 C6 中的值求和。SUMIF(范围核实,标准,范围和)。
- 范围:'2016 年 9 月'!$A$2:$A$6
- 标准:A2:A6 ---- 此公式所在的本地页面
- 范围总和:'2016 年 9 月'!$C$2:$C$6
C:
SUM(SUMIF('September 2016'!$A$2:$A$6,A2:A6,'September 2016'!$C$2:$C$6))
总结上面解释B中解释的内容。
德:
SUM('September 2016'!$C$2:$C$6)-SUM(SUMIF('September 2016'!$A$2:$A$6,A2:A6,'September 2016'!$C$2:$C$6))
通过输入此公式,它将通过遍历数组(A2:A6)对 C 部分求和,并将结果的值作为向量和进行求和;这就是为什么您必须按下CNTRL + SHIFT + ENTER
,否则预期的公式将产生不想要的结果,如本解决方案中的 OP 所示。
虽然图像中揭示了多个公式,但这只是思维过程向最终结论的揭示。最终的图像只揭示了一个公式,可以得到所需的结果。
答案2
使用以下公式:
=SUMIF (Account! A2, VLOOKUP (Test! A2, Test! A1:B5, 2,FALSE), Account! B2:B5)