excel - Excel:Flashfill 偏移水平 + 垂直
问题描述
因此,我不是 VBA 的粉丝,而且我最近了解到,只要您的目标范围比您拥有的数据更长,就可以将 OFFSET 与 COUNTA 一起使用来快速填充范围。现在我希望能够同时为列和行实现这一点,其中行是平均的。这可以做到吗?我正在用头撞墙以找到一些逻辑来做到这一点,但只能设法以将行数与列数相乘的方式将其组合起来……当然,这是不希望的。
我在 Excel Online 中发布了一个最小可重现示例: https ://onedrive.live.com/view.aspx?resid=63EC0594BD919535!1491&ithint=file%2cxlsx&authkey=!ALmV0VtFb7QZCvI
如果您看到 Cell J9 和 J11,您将看到我想要组合的内容。J11 及以下的三行,我想在 J10 中取平均值,并且溢出/快速填充(如 J9 和 11 由于公式已经自动执行)它们从右到右,与范围 A1- 中的数据一样多的列G4..
所以我有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))
其中“天”是=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找到
解决方案
现在,根据您的解释,这是我的测试截图:
A1 部分:Exxx
我已经将该部分转换为一个名为«TblData»的表,具有许多优势:
- 它会自动扩展,无需任何额外的努力/公式
- 我们可以通过表自动归属的列来识别数据
[@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
注意:(1)我更喜欢重用表格,而不是使用 J10:N10 下面的内容,因为它会随着更多行的添加而自动扩展。 (2)除非真的有必要,否则我觉得从 J9:Nxxx 再次复制 A1:Exxx 也是一项双重工作,因为您可以将 Table 用于您需要的任何内容,并且维护较少。
请在我更新这些项目后找到附件:
文件链接:https ://drive.google.com/open?id=1wRbpUxg0XLpfGqdvMF4fNKXDrL7xPPWs
我们可以在下面提供更多信息以获取更多信息。希望你能发挥更大的能力:)
推荐阅读
- firebase - 在 Firestore 文档中实现细粒度数据验证
- excel - 我无法将 excel 文件导入 Microsoft 访问
- javascript - 框架 0.8.2 中的 EventListener 不起作用
- swift - NSButton 状态管理
- ruby-on-rails - 如何将 SimpleForm 输入配置为默认为无包装器
- android - 如何配置 gradle 以从资产目录中删除未使用的字体?
- php - 使用php代码“DELETE”,我怎样才能只为那一天保存一个不超过那一天的一个?
- java - 导入的 maven 项目在 STS 中不起作用
- node.js - 没有建议的筹码
- java - 我总是在 Spring 控制器的 **transferReceivedData** 对象中得到 null