拖动公式并更改COLUMN引用而不是ROWS [英] Drag down formula and change COLUMN references instead of ROWS
问题描述
= SUM(Banco!H $ 5; Banco!H $ 6 ; Banco!H $ 8; Banco!H $ 9; Banco!H $ 10; Banco!H $ 11)
我需要它来更改COLUMN引用而不是ROWS,当我拖动它(基本上表现为我拖动它)...例如:
= SUM(Banco!I $ 5; Banco!I $ 6; Banco!I $ 8; Banco!I $ 9; Banco!I $ 10; Banco!I $ 11)
= SUM(Banco!J $ 5 ; Banco!J $ 6; Banco!J $ 8; Banco!J $ 9; Banco!J $ 10; Banco!J $ 11)
= SUM(Banco!K $ 5; Banco!K $ 6; Banco! K $ 9; Banco!K $ 10; Banco!K $ 11)
任何线索?
非常感谢!
...使用偏移函数。 >
例如 - 假设您在第1行中有一个公式,并且想要引用 Banco!H5
,则可以执行类似:
= OFFSET(Banco!$ G $ 5,0,ROW())
现在,当你拖动它,它会被n
所以你的新公式如下所示:
= SUM(OFFSET(银行!$ G $ 5,0,ROW()),OFFSET(银行!$ G $ 6,0,ROW()),OFFSET(银行!$ G 8,0 $,ROW( )),OFFSET(Banco!$ G $ 9,0,ROW()),OFFSET(Banco!$ G $ 10,0,ROW()),OFFSET(Banco!$ G $ 11,0,ROW()))
再次,这假设您将第1行中的此公式粘贴(我知道这很丑,但是您指定了特定的单元格,所以你必须分别指定每一个)
希望这是有道理的
I have this formula in Excel, in row E5:
=SUM(Banco!H$5;Banco!H$6;Banco!H$8;Banco!H$9;Banco!H$10;Banco!H$11)
I need it to change the COLUMN references instead of ROWS when I drag it down (basically behave like I was dragging it across)... For example:
=SUM(Banco!I$5;Banco!I$6;Banco!I$8;Banco!I$9;Banco!I$10;Banco!I$11)
=SUM(Banco!J$5;Banco!J$6;Banco!J$8;Banco!J$9;Banco!J$10;Banco!J$11)
=SUM(Banco!K$5;Banco!K$6;Banco!K$8;Banco!K$9;Banco!K$10;Banco!K$11)
Any clues?
Thanks a lot!
... Use the offset function.
For example - Suppose you had a formula in row 1 and you wanted to reference Banco!H5
, you could do something like:
=OFFSET(Banco!$G$5,0,ROW())
Now, as you drag it down, it will offset by the number of rows down you go.
So your new formula would look as follows:
=SUM(OFFSET(Banco!$G$5,0,ROW()),OFFSET(Banco!$G$6,0,ROW()),OFFSET(Banco!$G$8,0,ROW()),OFFSET(Banco!$G$9,0,ROW()),OFFSET(Banco!$G$10,0,ROW()),OFFSET(Banco!$G$11,0,ROW()))
Again, this assumes you are pasting this formula in row 1 (I know it's ugly, but you specified specific cells, so you have to specify each one separately)
Hope this makes sense
这篇关于拖动公式并更改COLUMN引用而不是ROWS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!