时间戳

首页 > 解决方案 > Google 表格数组公式运行余额按类别列分层并按日期列排序

google-sheets - Google 表格数组公式运行余额按类别列分层并按日期列排序

问题描述

我试图找出一个数组公式来计算另一列中列出的类别中的运行余额。

这是一个示例表供参考 https://docs.google.com/spreadsheets/d/1kmitZF6YtARAi2EB0NggbIhKgsb7tLCknsKBTEKycrw/edit?usp=sharing

要求是：

任何帮助将非常感激！！

标签： google-sheetsarray-formulascumulative-sumbalance

解决方案

最短路径

使用SUMIFS函数根据多个条件获取范围的总和。在您的情况下，您有两个：

  • 根据类别计算
  • 根据日期计算

结果：cell = sumifs(C$2:C,B$2:B,"="&B2,A$2:A,"<="&A2)

语法解释

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

  • sum_range - 要求和的范围。

  • criteria_range1 - 对照标准 1 检查的范围。

  • 标准 1 - 应用于标准范围 1 的模式或测试。

  • criteria_range2，criteria2，... - [可选] - 要检查的其他范围和标准。

编辑

如果您需要自动填充新行，您可以使用电子表格服务用于Google Apps 脚本和简单触发器。

当用户更改电子表格中任何单元格的值时，有一个称为onEdit(e)的触发器会自动运行。

您可以使用以下代码在Running balance每次更改值时更新列。

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var last_row = sh.getLastRow()
  console.log(last_row)
  var formula = '=sumifs(C2:C,B2:B,B2,A2:A,"<="&A2)'
  sh.getRange('D2:D'+last_row).setFormula(formula)
}

但是，此代码会更新整个列，覆盖旧条目的公式。如果您只想更新新条目以获得更高效的代码，您可以使用以下代码。

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var last_row = sh.getLastRow()
  var last_formula_row = sh.getRange('D1:D').getValues().filter(String).length
  var formula = '=sumifs(C$2:C,B$2:B,B' + last_formula_row + ',A$2:A,"<="&A'+ last_formula_row + ')'
  sh.getRange('D'+last_formula_row+':D'+last_row).setFormula(formula)
}

我附上你们两个，因为如果没有第一个，第二个可能很难理解。

参考

  • SUMIFS：根据多个条件返回范围的总和。
  • 电子表格服务：允许脚本创建、访问和修改 Google 表格文件。
  • Google Apps Script : 由 Google 开发的脚本平台，用于在 Google Workspace 平台上进行轻量级应用程序开发
  • 简单触发器：当某个事件发生时自动运行一个函数
  • onEdit(e)触发器，当用户更改电子表格中任何单元格的值时自动运行

推荐阅读