Excel:Flashfill 偏移水平 + 垂直

Question

所以我不是 VBA 的粉丝，我最近了解到 OFFSET 可以与 COUNTA 一起使用来快速填充一个范围，只要你的目标范围比你拥有的数据更长。现在我希望能够同时对列和行实现这一点，其中行是平均的。这可以做到吗？我正在用头撞墙寻找一些逻辑来做到这一点，但只能设法将它以一种将行与列数相乘的方式组合起来……这当然是不希望的。

我在 Excel Online 中发布了一个最小可重现示例: https://onedrive.live.com/view.aspx?resid=63EC0594BD919535!1491&ithint=file%2cxlsx&authkey=!ALmV0VtFb7QZCvI

如果您看到单元格 J9 和 J11，您就会看到我想要合并的内容。 J11 及以下的三行，我想在 J10 中取平均值，然后溢出/快速填充(就像 J9 和 11 一样自动执行，因为公式已经存在)它们从右边开始，对于与 A1- 范围内的数据一样多的列G4..

所以我有 numbers 的原始数据，标题在 A1-G4 中，并通过编写 =OFFSET($A$1:$A$1,0,0,1,COUNTA($ A$1:$EV$1)-1) 在 J9 中，我得到了从左到右填充的所有列的标题，并通过编写 =OFFSET($A$1,1,0,COUNTA($ A:$A)-1) 在 J11 中，我从上到下填充了第一列的行。它们也可以通过编写 OFFSET(Days,1,0,COUNTA($A:$A)-1,COUNTA(Days)) 来组合，其中“Days”是 =OFFSET ($A$1:$A$1,0,0,1,COUNTA($A$1:$EV$1)-1)(在命名范围内以提高可读性)或 OFFSET($A$1: $A$1,0,0,1,COUNTA($A$1:$EV$1)-1) 不使用命名范围

作为一个想法，虽然我不确定如何实现它，但也许可以以某种形式使用它来获取水平部分的列引用并结合 =AVERAGE(OFFSET($A$1 ,1,0,COUNTA($A:$A)-1))

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)

..发现于https://superuser.com/questions/1259506/formula-to-return-just-the-column-letter-in-excel/1259507

Answer 1

现在，根据您的解释，这是我的测试截图:

A1 部分:Exxx

我已经将该部分转换为一个名为 «TblData» 的表格，它具有许多优点:

1. 它会自动扩展，无需任何额外的努力/公式
2. 我们可以通过表 [@1]、[@2]、[@3]、[@4]、[@5] 自动归因的列来识别数据

J9:N9 部分

作为表名的副本，我使用了以下公式来检索它:

=INDEX(TblData[#Headers],1,COLUMN(A1))    '<--- This is for J9
=INDEX(TblData[#Headers],1,COLUMN(E1))    '<--- This is for N9

J11 节:Nxx

作为表内容的副本，我使用了以下公式来填充内容:

=INDEX(TblData,ROW($A1),MATCH(J$9,TblData[#Headers],0))   '<--- This is on J11
=INDEX(TblData,ROW($A3),MATCH(N$9,TblData[#Headers],0))   '<--- This is on N13

J10:N10 部分

现在这是平均值的有趣部分，所以这是我用于它的公式:

=AVERAGE(TblData[1])  '<--- This is on J10
=AVERAGE(TblData[5])  '<--- This is on N10

NB: (1) Instead of using the Content below J10:N10, I prefer to reuse the Table as it expands automatically as more rows are added. (2) Unless it is really necessary, I feel it is a double work as well to replicate again A1:Exxx from J9:Nxxx, because you can use the Table for whatever you need, with less maintenance.

请在我更新这些项目后找到附件:

File Link: https://drive.google.com/open?id=1wRbpUxg0XLpfGqdvMF4fNKXDrL7xPPWs

我们可以在下面进行更多通信以获取更多信息。希望你能发挥更大的作用:)