SUM在另一个工作表中的一行中的12个单元格的顺序组 [英] SUM sequential groups of 12 cells from a row in another worksheet

问题描述

我有两张表格：1份月费用，另一份是费用类型的年度夏季费用。

对于年度费用表，我想要每12个月把它放在一个单元格中，然后旁边的单元格是未来12个月，等等。

所以要介绍一下我的工作表设置方式：

标题为运营费用的工作表单位B4：M4是当年1月至12月的每月费用。

在题为资金来源和使用资金的表格中，我想打电话给D28是B4：M4的总和，然后单元格D29的总和N4：Y4等等，直到我停止拖动公式。

我尝试了Sum（offset（））函数，但似乎无法弄清楚。

解决方案

舍弃 SUM（OFFSET（...））方法，并坚持一对 INDEX函数定义第4行的范围。如果会更快，是一个非易失性的功能。

  = SUM（INDEX（'Operating Expense'！$ 4：$ 4， 1,2 +（ROW（1：1）-1）* 12）：INDEX（'Operating Expense'！$ 4：$ 4,1,13 +（ROW（1：1）-1）* 12））$ b $在D28中，您可以填写B4：M4，N4：Y4等。 

工作原理

与其他查找功能不同（例如 VLOOKUP功能， HLOOKUP功能等） INDEX函数可以返回可以放到的单元格地址引用直接使用额外的开销，如 INDIRECT函数。如果事实，你可以把它们中的两个连在一起，而不是一个冒号，以创建一个可用的单元格范围。

语法：

INDEX（< array>，< row_num>< column_num>）

示例:(这些都是相同的东西）

  = SUM（INDEX（A：A，1，1） ：INDEX（B：B，5，1））
 = SUM（A1：INDEX（B：B，5，1））
 = SUM（INDEX（A：A，1，1） B5）
 = SUM（A1：B5）
  

一旦我们控制了这一行，单元格范围的列（也称为数组），我们可以用一点数学来操纵它们。

= ROW（1：1）在任何单元格中，填写您将获得<1> 2，3等等。这是我们填写的错误的顺序。

你想要第一个12个月的总和B4：M4，所以第一部分为< array>使用<4> 4 。这表示第4行的全部。< row_num>是 1 ，因为只有一行要考虑。

您想要在列B开始总和范围，以便在数字上列2。我们不想第一次添加任何东西，但是我们希望在填写时添加 12 ，以便匹配是 2 +（ROW（1：1）-1） ×12 或 2+（ROW（1：1）-1）* 12 开始< column_num> 2，14，26等。

同样，你想结束列M处的总和范围，这样数字列为13。我们不想添加任何事情都是第一次，但是我们要添加 12 ，因为我们填写，所以匹配是 13 +（ROW（1：1）-1）×12 或 13+（ROW（1：1）-1）* 12 结束< column_num> 13，25，37等。

填写的结果单元格范围将为B4：M4，N4：Y4，Z4：AK4等将其填充到 SUM功能将给你各自的总额。

如上所述，这是INDEX的特质。 VLOOKUP，HLOOKUP等只返回值，而不是可用的单元格引用。

I have two excel sheets: 1 for expenses that is monthly and another that is a yearly summer of expenses by expense type.

For the yearly expense sheet, I would like to sum every 12 months an put it in a cell, then the cell next to it is the next 12 months, and so on.

So to give an idea of how my sheet is set up:

Sheet titled 'Operating Expense' has cells B4:M4 that are Jan-Dec of that year for the monthly expenses.

On The sheet titled "sources & uses of funds", i would like to have call D28 be the sum of B4:M4, and then cell D29 the sum of N4:Y4 and so on until I stop dragging the formula across.

I tried the Sum(offset()) function but couldn't seem to figure it out.

解决方案

Discard the SUM(OFFSET(...)) method and stick with summing a pair of INDEX function's defining a range on the 4th row. If will be faster and is a non-volatile function.

=SUM(INDEX('Operating Expense'!$4:$4,1,2+(ROW(1:1)-1)*12):INDEX('Operating Expense'!$4:$4,1,13+(ROW(1:1)-1)*12))

With that in D28, you can fill down for B4:M4, N4:Y4, etc.

How It Works:

Unlike other lookup functions (e.g. VLOOKUP function, HLOOKUP function, etc) the INDEX function can return a cell address reference that can be put to use directly withot resorting to additional overhead like the INDIRECT function. If fact, you can stitch two of them together with nothing more than a colon to create a usable cell range.

Syntax:

INDEX(<array>, <row_num>, <column_num>)

Example: (these all amount to the same thing)

=SUM(INDEX(A:A, 1, 1):INDEX(B:B, 5, 1))
=SUM(A1:INDEX(B:B, 5, 1))
=SUM(INDEX(A:A, 1, 1):B5)
=SUM(A1:B5)

Once we have control over the row and column of a cell range (aka array) we can manipulate them with a little math.

If you put =ROW(1:1) in any cell and fill down you will get 1, 2, 3, etc. This is how we sequence the stagger as we fill down.

You want the first set of 12 month to sum B4:M4 so the first part uses 4:4 for the <array>. This represents all of row 4. The <row_num> is 1 because there is only one row to consider.

You want to start the sum range at column B so that is numerically column 2. We don't want to add anything the first time but we want to add 12 as we fill down so the match is 2 + (ROW(1:1)-1) × 12 or 2+(ROW(1:1)-1)*12 for starting <column_num> of 2, 14, 26, etc.

Similarly, you want to end the sum range at column M so that is numerically column 13. We don't want to add anything the first time but we want to add 12 as we fill down so the match is 13 + (ROW(1:1)-1) × 12 or 13+(ROW(1:1)-1)*12 for ending <column_num> of 13, 25, 37, etc.

The resulting cell range(s) as you fill down will be B4:M4, N4:Y4, Z4:AK4, etc. Stuffing these into a SUM function will give you the respective totals.

As mentioned, this is a trait of INDEX. VLOOKUP, HLOOKUP, etc only return the values, not a usable cell reference.

这篇关于SUM在另一个工作表中的一行中的12个单元格的顺序组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！