Google Sheets中基于数据库创建多物业P&L报表的最优方案问询

问题背景 #

需在Google Sheets中构建覆盖多物业的损益（P&L）报表：现有数据库表DB1存储各物业的收入、支出、债务偿还等核心数据，但直接基于DB1生成的数据透视表无法插入自定义计算行（如净营业收入NOI=收入-运营支出OpEx、现金流CF=NOI-其他支出-债务偿还）。

当前采用临时方案：新增数据库表DB2，通过命名函数（如CY_PROPERTY()、基于SUMIFS计算DB1对应物业收支差的NOI）生成计算行数据，再合并DB1与DB2为MasterDB，最终基于MasterDB生成含计算项的数据透视表，该方案支持新增账户、跨年对比报表等操作。

1. 当前方案是否为实现目标的最优方案？ #

不是最优方案。额外维护DB2会带来数据冗余，且手动同步数据存在出错风险。更高效的方案是直接利用Google Sheets数据透视表的「计算字段」功能，无需额外创建DB2：

• 打开数据透视表编辑器，点击「添加字段」→「计算字段」
• 自定义计算逻辑：例如输入收入 - 运营支出OpEx定义NOI，输入NOI - 其他支出 - 债务偿还定义CF
• 将计算字段拖入透视表的「行」或「值」区域，即可直接在透视表内展示计算结果

该方案无需维护额外数据表，减少数据同步成本，且原生支持按物业、时间维度的分组计算，完全覆盖现有方案的功能（新增账户、跨年对比）。

2. 新增物业时的自动化方案，及Google Sheets是否支持单元格直接调用函数？ #

自动化生成DB2对应行的方案

手动复制模板效率低下，推荐两种自动化方式：

• 动态生成方案：用QUERY函数自动同步
  无需手动维护DB2，通过QUERY+UNIQUE自动提取DB1中的唯一物业列表，并调用命名函数计算对应值：

  =QUERY(UNIQUE(DB1!A:A), "SELECT Col1, '"&NOI(Col1)&"', '"&CF(Col1)&"' WHERE Col1 IS NOT NULL", 1)
  

  当DB1新增物业时，DB2会自动更新对应行数据。
• 脚本自动化方案：Google Apps Script监听编辑事件
  编写简单脚本，监听DB1的编辑操作，自动将新增物业同步至DB2并填充公式：

  function onEdit(e) {
    const db1Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB1");
    const db2Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB2");
    const editedRow = e.range.getRow();
    const propertyName = db1Sheet.getRange(editedRow, 1).getValue();
    
    const existingProperties = db2Sheet.getRange(2, 1, db2Sheet.getLastRow()-1, 1).getValues().flat();
    if (!existingProperties.includes(propertyName) && propertyName !== "") {
      const newRow = db2Sheet.getLastRow() + 1;
      db2Sheet.getRange(newRow, 1).setValue(propertyName);
      db2Sheet.getRange(newRow, 2).setFormula(`=NOI("${propertyName}")`);
      db2Sheet.getRange(newRow, 3).setFormula(`=CF("${propertyName}")`);
    }
  }
  

单元格直接调用函数的支持情况

Google Sheets完全支持在目标单元格直接调用自定义命名函数。只要已完成命名函数的定义，直接在单元格输入=NOI("Property C")即可执行函数并返回计算结果，无需额外操作。

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