如何在Excel中一次用一个单元格引用增加4行公式

Question

我正在构建一个脚本，其中来自Sheet1 B2:B 3000的每个单元格值在Sheet2上被引用了4次。

期望输出实例

=concatenation "Text & Sheet1!B2"
=concatenation "Text & Sheet1!B2"
=concatenation "Text & Sheet1!B2"
=concatenation "Text & Sheet1!B2"

=concatenation "Text & Sheet1!B3"
=concatenation "Text & Sheet1!B3"
=concatenation "Text & Sheet1!B3"
=concatenation "Text & Sheet1!B3"

但是，如果我使用这个模式并填充它，它总是跳错了4行，就像这样

=concatenation "Text & Sheet1!B2"
=concatenation "Text & Sheet1!B2"
=concatenation "Text & Sheet1!B2"
=concatenation "Text & Sheet1!B2"

=concatenation "Text & Sheet1!B6"
=concatenation "Text & Sheet1!B6"
=concatenation "Text & Sheet1!B6"
=concatenation "Text & Sheet1!B6"

你知道我怎么能强迫每块4个命令的一个增量吗？

Answer 1

试试INDIRECT()函数-

="Hello " & INDIRECT("B" & ROUNDUP(ROW()/4,0))

​

​