如何在Excel/Sheets中根据账单项汇总对应Debt金额计算Total Bills

一、Excel/Google Sheets 公式实现（新手友好） #

直接用内置公式就能搞定，不需要复杂操作：

• SUMIFS分表求和：假设Bills和Loans表的Pay Date列是第A列，Debt列是第C列，输出表中Pay Date在A2单元格，对应Total Bills单元格输入：

  =SUMIFS(Bills!$C:$C, Bills!$A:$A, $A2) + SUMIFS(Loans!$C:$C, Loans!$A:$A, $A2)
  

  下拉填充就能自动计算每一行的Total Bills。
• QUERY合并求和：如果两个表结构完全一致，也可以合并后筛选求和：

  =SUM(QUERY({Bills!A:C; Loans!A:C}, "select Col3 where Col1 = date '"&TEXT($A2, "yyyy-mm-dd")&"'", 0))
  

二、Python 实现（适合正在学习Python的你） #

用pandas库快速处理数据，步骤清晰：

import pandas as pd

# 读取两个数据源文件（根据实际路径修改）
bills_df = pd.read_csv('bills.csv')
loans_df = pd.read_csv('loans.csv')

# 合并两个表并按Pay Date分组求和Debt
combined_df = pd.concat([bills_df, loans_df])
total_bills = combined_df.groupby('Pay date')['Debt'].sum().reset_index()

# 读取输出表并合并Total Bills列
output_df = pd.read_csv('output.csv')
output_df = output_df.merge(total_bills, on='Pay date', how='left').fillna(0)
output_df.rename(columns={'Debt': 'Total Bills'}, inplace=True)

# 自动计算Net income
output_df['Net income'] = output_df['Expected Pay'] - (output_df['Total Bills'] + output_df['Savings'])

# 保存更新后的表格
output_df.to_csv('updated_finance.csv', index=False)

三、Google Apps Script 实现（自动化同步） #

适合用Google Sheets的场景，写个脚本自动计算：

1. 打开你的Google Sheets，点击「扩展程序」→「Apps Script」
2. 替换默认代码为以下内容（注意修改表名和列索引匹配你的表格）：

function calculateTotalBills() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const billsSheet = ss.getSheetByName('Bills');
  const loansSheet = ss.getSheetByName('Loans');
  const outputSheet = ss.getSheetByName('Output');

  // 创建日期与Debt总和的映射表
  const debtMap = new Map();

  // 处理Bills表数据
  const billsData = billsSheet.getDataRange().getValues();
  for (let i = 1; i < billsData.length; i++) {
    const dateStr = billsData[i][0].toISOString().split('T')[0];
    const debt = billsData[i][2]; // 假设Debt在第3列（索引从0开始）
    debtMap.set(dateStr, (debtMap.get(dateStr) || 0) + debt);
  }

  // 处理Loans表数据
  const loansData = loansSheet.getDataRange().getValues();
  for (let i = 1; i < loansData.length; i++) {
    const dateStr = loansData[i][0].toISOString().split('T')[0];
    const debt = loansData[i][2];
    debtMap.set(dateStr, (debtMap.get(dateStr) || 0) + debt);
  }

  // 更新Output表的Total Bills列
  const outputRows = outputSheet.getLastRow() - 1;
  for (let i = 2; i <= outputRows + 1; i++) {
    const dateStr = outputSheet.getRange(i, 5).getValue().toISOString().split('T')[0]; // 假设Pay date在第5列
    outputSheet.getRange(i, 2).setValue(debtMap.get(dateStr) || 0); // 假设Total Bills在第2列
  }

  // 自动计算Net income
  for (let i = 2; i <= outputRows + 1; i++) {
    const expectedPay = outputSheet.getRange(i, 1).getValue();
    const totalBills = outputSheet.getRange(i, 2).getValue();
    const savings = outputSheet.getRange(i, 3).getValue();
    outputSheet.getRange(i, 4).setValue(expectedPay - (totalBills + savings)); // 假设Net income在第4列
  }
}

3. 点击运行按钮授权，之后可以设置触发器（「编辑」→「当前项目的触发器」），让脚本定期自动更新数据。

内容的提问来源于stack exchange，提问作者cronobear