时间戳

首页 > 解决方案 > Excel 中的迭代公式可在不使用 VBA 的情况下将数据重新格式化到新工作表上

arrays - Excel 中的迭代公式可在不使用 VBA 的情况下将数据重新格式化到新工作表上

问题描述

我非常坚持这个项目，我希望也许我在正确的轨道上或在正确的地方提问，因为当谈到 Excel 并使用其中的函数时，我是一个非常新手。

目前，我得到了一个以非常不幸的方式格式化的数据集，以满足我的需求。在照片中，我创建了最简单的类似数据样本，尽管我使用的实际数据集通常约为 144 行 x 35 列。如果照片没有显示，我可以描述这些数据的最佳方式是：

然后 C2:H16 填写每个代理每天申请的信用对应的数字。如果代理当天没有申请信用，则相应的 3 个单元格将留空。

在此处输入图像描述

我想做的是，在同一个工作簿的单独工作表上，使用某种公式或可以让我的数据成为包含日期、代理名称、平均信用、信用金额和信用量的简单表格的东西作为我的标题，如下图： 在此处输入图像描述

理想情况下，如果日期下方有一个有效的非空白单元格，我什至只想在新工作表中放置值，但实际上我完全可以使用任何类型的嵌套公式，无论它是否过滤掉这些公式都可以完成工作或不。不幸的是，这必须在不使用 VBA 且不使用 Excel 中的内置功能区选项的情况下完成。

最初我希望我可以通过某种嵌套的 vlookup 和 if 语句来实现这一点，但我有点过头了。作为最后的努力，我计划在工作簿中创建 31 个额外的工作表，并在每张工作表上使用过滤器功能，同时过滤每张工作表上的不同列，即工作表 2 将过滤列 c 下的非空白数据点，表 3 将过滤 d 列下的非空白数据点等，虽然这很乏味，但我只需将数据复制并转置到一个完全独立的工作簿中。

非常感谢任何帮助、提示、提示或技巧。

标签： arraysexcelsortingautomation

解决方案

我看到两种方法：

  1. Power Query - 必须有 Excel for Windows 2010 或更高版本。
  2. 公式 - 必须有 Excel 2019 或 365。

电源查询 (PQ) 方法

这可能看起来很复杂，但它需要不到 5 分钟的时间，并且完全由鼠标完成。完成后，只需单击刷新即可更新数据。

首先将表格转换为 excel 表格并通过转到Data 选项卡并选择From Table/Range导入 PQ 编辑器：

在此处输入图像描述

对有标题的表格说“是”： 在此处输入图像描述

这将打开 PQ 编辑器。选择代理名称字段，然后从“转换”选项卡中选择“向下填充”。

在此处输入图像描述

现在选择代理名称和column1，然后右键单击并选择Unpivot Other Columns。

在此处输入图像描述

现在您的数据几乎就在那里 - 它们被规范化为具有属性的列，

在此处输入图像描述

所以您需要将它们转换为您在上面提出的形式。下一步是选择Column1，然后从Transform 选项卡中选择Pivot。它将弹出一个窗口，您可以在其中选择Value作为要旋转的字段：

在此处输入图像描述

单击确定，现在您可以看到您想要的表格：

在此处输入图像描述

现在你需要把它放回 Excel。在主页选项卡上，单击关闭并加载到：

在此处输入图像描述

然后选择表和新工作表：

在此处输入图像描述

你完成了。

在此处输入图像描述

如果您将新数据添加到数据中，您只需右键单击上面创建的表并选择刷新，它将使用新数据更新表。如果你想将它应用到未来的数据表中，它很容易修改，而无需重复整个过程。

公式法

这已经是一个很长的帖子了，这个方法需要一些严肃的公式工程，所以我现在只强调这个方法，但如果它有趣，请在评论中说出来，我会完成这个帖子。

这是一个反透视的公式，但它不适合您的表格，因为：

  • 它需要将这些合并的名称填充到下面的单元格中（即向下填充）和
  • 它将空白单元格视为零。

在 Power Query 用语中，我们会说您想要“ Unpivot C1:H16 By A1:B16”。

=LET( unPivMatrix, C1:H16,
      byMatrix, A1:B16,
       upC, COLUMNS( unPivMatrix ),
       byC, COLUMNS( byMatrix ),
       dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
       upCells, dmxR * upC,
       upSeq, SEQUENCE( upCells,, 0 ),
       upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
       upBody, INDEX( unPivMatrix,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, upC ) ),
       byBody, INDEX( byMatrix,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, byC ) ),
       attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
       mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
       demux, IFERROR( INDEX(
                             IFERROR( INDEX( byBody,
                                             IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
                                                      SEQUENCE( 1, byC + 1 ) ),
                                       attr ),
                             upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
                        mux ),
       demux
     )

要修改它需要相当于 Fill-Down，这有点挑战性，但可能。最后还需要一个过滤器来删除这些零。

这不会提供您的最终输出，它只会为您提供第一步。然后，您需要旋转未旋转的输出，这可以通过普通的 excel 数据透视表或通过编写另一个复杂的公式来旋转它来完成，这很难。

所以...如果您有 Excel for Windows 2010 或更高版本，我会选择 Power Query。它专为像您这样的问题而设计。

推荐阅读