gpt4 book ai didi

How to write formula that references specified number of cells relative to the formula' location in excel?(如何编写相对于公式在EXCEL中的位置引用指定数量的单元格的公式?)

转载 作者:bug小助手 更新时间:2023-10-25 19:45:50 34 4
gpt4 key购买 nike



Example: Sum 7 cells directly to the left of the formula.

例如:将公式左侧的7个单元格相加。


Notably, the formula should work when inserting new column to the left of it or somewhere inside the range. Not extending the range, not skipping newly inserted column.

值得注意的是,当在其左侧或范围内的某个位置插入新列时,该公式应该起作用。不扩展范围,不跳过新插入的列。


更多回答

OFFSET() function may help.

Offset()函数可能会有所帮助。

优秀答案推荐

The easiest way to accomplish this is by defining a relative named range. Start by selecting cell H1. Then, on the ribbon, go to the Formulas Tab > Defined Names Group and click Define Name. In the Name: field, type RangeToLeft, then in the Refers to: field, type =!A1:G1 and click Ok (note the omission of the sheet name, as well as the $ signs).

实现这一点的最简单方法是定义相对命名范围。首先选择单元格H1。然后,在功能区上,转到公式选项卡>定义的名称组并单击定义名称。在名称:字段中,键入RangeToLeft,然后在引用:字段中,键入=!A1:G1并单击确定(请注意省略了工作表名称以及$符号)。


The name “RangeToLeft” can now be used in any worksheet formula from column H through to column XFD, and will always refer to the seven cells immediately to the left of the formula, no matter how many columns are inserted or deleted. For example, in cell H1 you can use the following formula:

名称“RangeToLeft”现在可以在从H列到XFD列的任何工作表公式中使用,并且将始终引用公式左侧紧邻的七个单元格,无论插入或删除多少列。例如,在单元格H1中,您可以使用以下公式:


=SUM(RangeToLeft)

The only time this will fail is if it’s used in columns A to G, as there are less than seven cells to the left remaining. Cheers!

唯一失败的情况是在A到G列中使用它,因为左边剩下的单元格不到七个。干杯!



I had previously come up with this formula but the accepted answer is so simple and elegant that I'm not sure if it's worth posting any more. Still, for the record a regular formula not using offset might be:

我之前已经想出了这个公式,但公认的答案是如此简单而优雅,以至于我不确定是否值得再发一篇。不过,对于记录,不使用偏移量的常规公式可能是:


=SUM(INDEX(A1:H1,COLUMN()-6-COLUMN(A1)):INDEX(A1:H1,COLUMN()-COLUMN(A1)))

enter image description here


With insertion to left:

插页向左:


enter image description here


With insertion in range:

在范围内插入:


enter image description here



"the formula should work when inserting new column", but how it should work?

“当插入新列时,公式应该起作用”,但它应该如何起作用呢?


[H1]=SUM(A1:G1)

The default behavior is to keep both references with dedicated cell. It means if A1:G1 will be extended inside it, e. g. new column before B, it will be changed to A1:H1.

默认行为是使用专用单元格保留这两个引用。这意味着如果A1:G1将扩展到它的内部,例如B之前的新列,它将被更改为A1:H1。


You can anchor any end with the length:

您可以锚定具有以下长度的任何末端:


=SUM(A1:OFFSET(A1;;6)) fixed left end
or
=SUM(OFFSET(G1;;-6):G1) fixed rigth end

Or you can fix the range related to the current cell:

或者,您可以固定与当前单元格相关的范围:


[H1]=SUM(OFFSET(H1;;-7):OFFSET(H1;;-1))

Replace ; by , following your regional settings.

替换为,根据您的地区设置。


更多回答

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com