☰

自动复制数据透视表列，但排除某些值？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007 microsoft-excel-2013
自动复制数据透视表列，但排除某些值？

我想将一列复制到新工作表上。可以过滤该列：

过滤菜单

我想复制此列中的所有内容，除了“已批准”单元格（下拉筛选菜单中的第一个选项）。这需要自动完成，这样如果我更改列中的数据或添加新数据，复制列会立即反映更改。这必须在没有宏的情况下完成，因为它们在运行此文件的计算机上运行速度太慢了。

我尝试了几种索引匹配和“if”语句的组合，但到目前为止，我尝试过的所有方法都没有效果。我完全不知道如何在不使用宏的情况下实现这一点。有人有什么想法吗？

答案1

情况 1： 旧记录得到正式认可的在 栏目中。

在此处输入图片描述

情况 2： 新纪录得到正式认可的在列中以及已复制/已拉取记录。

在此处输入图片描述

注意：

  • Red Cells是，Criteria其中1是Column Offset for Col B in Sheet 1且Approved是Value to match。
  • 和2nd Screen Shot 新纪录在Green Color。
  • 为了使公式动态化，我使用了a Cell for the Criteria。

怎么运行的：

  • 在第 2 页A1用Column Offset position和B1标准填充单元格。

  • 数组公式第 2 页单元格A3，用完成公式并向Ctrl+Shift+Enter下填充一些额外的行。

     {=IFERROR(INDEX(Sheet1!$B$4:$B$9, SMALL(IF((INDEX(Sheet1!$B$4:$B$9, , $A$1)<>$B$1), MATCH(ROW(Sheet1!$B$4:$B$9), ROW(Sheet1!$B$4:$B$9)), ""), ROWS(A3:$A$3)), COLUMNS($A$1:A1)),"")}

編輯：

(INDEX(Sheet1!$B$4:$B$9, , $A$1)变成，

INDEX($B$4:$B$9, , 1)并返回B4:B9。

(INDEX($B$4:$B$9, , $A$1)<>$B$1)返回，

{1;1;0;1;1;0}为了情况 1＆ 为了情况 2 {1;1;0;1;1;0;1;0;1}

最后又回来了，

({Ok;No;Fine;One}<=$B$1)为了情况 1

和

({Ok;No;Fine;One;Four;New}<=$B$1)为了情况 2。

这匹配部分看起来像，，

IF({1;1;0;1;1;0}, MATCH(ROW($B$4:$B$9), ROW($B$4:$B$9)), "") & IF({1;1;0;1;1;0;1;0;1}, MATCH(ROW($B$4:$B$9), ROW($B$4:$B$9)), "")

变成，

IF({1;1;0;1;1;0}, {1;2;3;4;5;6}, "") & IF({1;1;0;1;1;0;1;0;1}, {1;2;3;4;5;6;7;8;9}, "")

并返回，

{1;2;"";4;5;""} & {1;2;"";4;5;"";7;"";9}

和小的部分退货，

SMALL({1;2;"";4;5;""},1) & {1;2;"";4;5;"";7;"";9}, 1)并返回1。

笔记，为了更好地管理数据索引范围 B4:B9在第 1 页，应该是动态命名范围也避免改变索引范围，并向下扩展至与新条目数相同的行数。

按照以下步骤创建动态命名范围。

  • 按下Ctrl+F3打开姓名经理对话。
  • 查找并点击新的按钮，你会发现下面显示的对话框。

在此处输入图片描述

  • 像我一样分配您选择的名称，示例名称是Mydata。

  • 复制粘贴下面显示的公式指的是文本框。 =OFFSET($B$1,0,0,COUNT($B:$B),1)

  • 完成好的。

注意：

  • 然后，您可以Sheet1!$B$4:$B$9,用Mydata上面显示的主要公式进行替换。

  • 记得使用动态命名范围是一个可选部分。

答案2

如果您不需要“压缩”数据来隐藏空白单元格，则有一个简单的解决方案。假设您的数据位于 Sheet1 的 A 列，从第 2 行开始。在 Sheet2!A2 中，使用如下公式：

=IF(OR(ISBLANK(Sheet1!A2),Sheet1!A2="Approved"),"",Sheet1!A2)

根据需要将其复制到列的下方。这只会复制源单元格值，除非该值是“已批准”或空白，在这种情况下会产生空值。这将立即反映 Sheet1 上的任何更改，并且指向 Sheet1 上未使用单元格的任何预填充单元格都将显示为空白。

如果您想隐藏 Sheet2 上的空白单元格，那就更复杂了。您可以使用过滤器并取消选择空白单元格。但是，当重新计算工作表时，过滤器不会更新；您需要强制重新应用过滤器。

您只需按一下键即可手动完成此操作。sheet2 数据已经正确，隐藏空白单元格只是表面功夫。因此，这可能是一个微不足道的解决方案。

如果您需要自动隐藏空白单元格，则需要 VBA。根据 Sheet1 A 列的变化触发运行，然后只需重新应用过滤器即可。如果您关心的只是速度，那么这应该不是问题。

但是，如果您需要过滤，并且需要自动过滤，那么上面显示的公式就浪费了。您不妨只用对 Sheet1 的单元格引用预填充 Sheet2。然后过滤以隐藏空白单元格和“已批准”单元格。只需使用宏重新应用过滤器就不会是一项艰巨的任务。

相关内容