在Google表格中容纳新条目的技术 [英] Techniques to accommodate new entries in google sheets

问题描述

如您所见，我将代码转换为唯一的列标题，以便对借方和贷方进行分析和汇总.汇总会在另一张工作表中转置以创建汇总损益科目.我需要帮助，如何在第一列中复制求和公式以提供任何扩展的转置后的唯一代码，以及是否/如何为单个单元格输出使用arrayformula.

As you can see I transpose codes into unique column headings so that debits and credits are analysed and summated. Summations are transposed in another sheet to create summary profit/loss account. I need help how to replicate the sum formula in column I to serve any expanded transposed unique codes and whether/how I should use arrayformula for the individual cell output.

编辑

实际输出如下:

我的问题是如何自动在汇总行和单元格主体中容纳新的条目/代码.该数据属于居民委员会，因此我只能将匿名数据显示为图像.

My problem is to how to automatically accommodate new entries/codes in the totals row and main body of cells. The data belongs to a residents' committee so I can only show anonymous data as image.

编辑2

实际输入是从银行记录中导入的，然后进行编码:

Actual input is imported from bank records, then coded:

推荐答案

Query 对于SUM部分非常有用.

Query is pretty good for the SUM part.

从列 I 开始，您可以执行以下操作:

Starting in column I, you can do:

=ArrayFormula(INDEX(QUERY(
  0+OFFSET(I4,0,0,ROWS(F6:F),COUNTA(UNIQUE(F4:F))),
  "select "&
  JOIN(
    ",",
    "sum(Col"&SEQUENCE(COUNTA(UNIQUE(F4:F)))&")"
  )
),2))

第二个代码中的 0 + 或 VALUE (它们在此处做同样的事情)将数据单元格转换为默认值，如果为空，则为0，否则查询失败.这也使我们可以按序列号引用列，这是我们在第二个参数中所做的.我们将查询构建为类似于 select sum(Col1)，sum(Col2)，...，sum(ColN)的对象.由于这默认情况下为我们提供了标题，因此我们可以重新标记查询语句中的所有内容，但这会提供过多的额外代码，因此更容易做到的是使用 INDEX 选择总和.

The 0+ or the VALUE in the second one (they both do the same thing here) transforms the data cells to default to 0 if blank, otherwise the query fails. This also lets us refer to the columns by sequence number, which is what we do in the second argument. We build the query into something that looks like select sum(Col1),sum(Col2),...,sum(ColN). Since this gives us a header by default, we could relabel everything in the query statement, but that gives too much extra code, so the easier thing to do is use INDEX to select the sums.

EQ 部分对于Arrayify非常简单.从 I4 :

The EQ part is fairly straightforward to Arrayify. Starting in I4:

=ArrayFormula(
  (FILTER(F4:F,F4:F<>"")=FILTER(I2:2,I2:2<>""))*
  IF(
    Array_constrain(G4:G,COUNTA(FILTER(F4:F,F4:F<>"")),1),
    G4:G,
    -H4:H
  )
)

FILTER 仅过滤掉空白单元格，而 Array_Constrain 将 G 列的大小调整为与过滤后的 F 列.

The FILTERs just filter out the blank cells, and the Array_Constrain sizes the G column to the same size as the filtered F column.

这篇关于在Google表格中容纳新条目的技术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！