问用于会计的Google Sheets脚本
EN

Stack Overflow用户

提问于 2019-04-05 06:49:00

回答 1查看 439关注 0票数 0

我是谷歌应用程序脚本的新手，所以请耐心等待。我正在尝试在Google Sheets中创建自定义函数来监控每月和每周的销售和费用。我可以使用内置公式来实现，但每次编辑每个单元格都太耗时了。Here is what the Google Sheet looks like。

对于月度会计，我使用下面的公式(这里是“销售”)。我使用多个SUMIFS的总和作为费用，因为有多个标准(“供应”、“员工”或“运营”)。

=ARRAYFORMULA(SUMIFS('2019'!$F$2:$F,text('2019'!$A$2:$A,"MMM"),A3,'2019'!$E$2:$E,"Sales"))

对于每周会计，我使用下面的另一个公式。在这里，我汇总了给定日期范围(这里是从Mar 31 2019到Apr 6 2019)内的所有交易(这里是"Sales")。与上面一样，我使用相同的原则将多个SUMIFS函数汇总为“费用”。

费用：=SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Supplies")+SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Staff")+SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Operations")

如何将上面显示的公式转换为自定义函数？我正在试着写一些像这样的东西：

javascript

google-apps-script

google-sheets

accounting

回答 1

Stack Overflow用户

发布于 2019-04-10 08:27:58

我建议一种不同的策略，避免自定义函数，允许您使用没有复杂参数的标准函数，并在此过程中实现更合理的数据布局。

2019 Sheet: Category (E列)：将这些设置为下拉选择，以避免输入错误和其他借记Sheet: Column F errors.
2019 -不清楚是销售应为负(贷方)，还是费用应为正数(借方)。无论哪种方式，这一列的总和都会在某个阶段与某些东西相一致，因此交易的“符号”很重要。这还意味着，汇总工作表上的销售公式应以负数为前缀(以便它们显示为正数工作表-Monthly Sales -前缀为“-”，以便销售额显示为正数。=-ARRAYFORMULA(SUMIFS('2019'!$F$2:$F,text('2019'!$A$2:$A,"MMM"),A3,'2019'!$E$2:$E,"Sales"))
**Summary Sheet:每月费用“”-简化公式:只需将不是"Sales“=ARRAYFORMULA(SUMIFS('2019'!$F$2:$F,text('2019'!$A$2:$A,"MMM"),A3,'2019'!$E$2:$E,"<>Sales"))
Summary Sheet的值相加: Expenses)
Summary - =B3-C3 (Sales减去Expenses)
Summary：Weekly reporting -很难在每月结果与每周结果之间取得平衡。因此，许多企业采用4/4/5周的季度周期来满足每月天数的变化。在任何情况下，您使用的公式都非常复杂，并且容易因输入错误而出错(如果没有其他错误的话)。我为每周报告中相应的每周"From"/" to“日期创建suggest:
create列，并使用date算法来计算每个新的周/月的相应日期。
使用引用“自/至”日期的“每周销售额和费用”公式。这使得你的公式完全通用，使你能够复制公式与传统的“复制/粘贴”命令，和不需要详细的公式editing.
Consider是否你想每周结果对齐每月结果。如果您这样做了，那么
- 可以通过在week#4中的" to“日期中添加或删除天数来微调月末。下个月的Week#1 "From”日期将自动调整，随后月份中其他几周的"From"/"To“日期也会自动调整。这将有助于消除意外错误，例如将3月31日同时包含在3月week#4和4月week#1中。
- 您还可以在右侧额外添加三列，以求和四周的总销售额、总费用和净利润。这个数字将与每月的result.

一致

公式简化

通过公开From和To日期，您可以在SumIFS公式中使用这些日期。例如，三月Week#1：

销售：=-SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&G3,'2019'!$A$2:$A,"<="&H3,'2019'!$E$2:$E,"Sales")费用：=SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&G3,'2019'!$A$2:$A,"<="&H3,'2019'!$E$2:$E,"<>Sales")

月度结果