取得&转换与条件格式 [英] Get & Transform vs Conditional Format

问题描述

我试图在 Get&的输出中使用条件格式。转换查询。 （Office 365 / Excel 2016 32位； Windows 10 Pro 64位）。

但是，刷新查询时，不是仅扩展/压缩条件格式来影响调整大小的列，而是刷新条件查询，如下图所示。

我知道我可以通过多种方式（包括VBA）获得所需的输出，并且在开发其他方法时不需要帮助。

我是在UI中忽略某些内容，还是在 Advanced Editor 中实现某些方法以完成CF的正常扩展？

要重现此问题：

基础数据

使用获取&转换以取消透视第2-4列，然后删除属性列

有条件的格式，以隐藏除A1列中的第一个唯一条目以外的所有内容

->

在原始数据表中添加另一行：

更新查询。

请注意，显示屏和CF适用于搞砸了，并添加了额外的CF条件

我有什么东西吗？可以在查询或Excel中执行以防止这种情况发生？还是我必须编辑条件格式（vba或手册）？ （或者使用其他解决方案来开发输出）。

我尝试使用结构化引用定义应用于范围，或者使用动态名称范围。但这没有用。 应用于更改为绝对引用，并在刷新查询时进行与上述相同的更改。

解决方案

这似乎对我有用（但是我从字面上了解到，您甚至可以通过阅读您的帖子来使用数据来完成此操作，所以谢谢！）。 / p>

将条件格式计算设置为

= COUNTIF（$ A $ 2：$ A2 ，$ A2）> 1

并将其应用于 = $ A $ 2：$ A $ 4 。

作为补充，每次更新原始表后刷新数据时，适用于格式规则将范围缩小了。

I am trying to use conditional formatting on the output of a Get & Transform query. (Office 365/Excel 2016 32 bit; Windows 10 Pro 64 bit).

However, when the query is refreshed, instead of the Conditional Formatting merely being extended/contracted to affect the resized column, it gets fubar'd as shown below.

I know I can obtain the desired output in a variety of ways, including VBA, and I do not need assistance in developing other methods.

Am I overlooking something either in the UI, or perhaps some method I could implement in the Advanced Editor to accomplish the normal extension of CF one would see if one just added a line to the table?

To reproduce the problem:

Base Data

Use Get & Transform to unpivot columns 2-4, and remove the Attribute column

Conditional format to hide all except the first unique entry in column A1

-->

Add another row to the original data table:

Update the query.

Note that the display and the CF "applies to" are messed up, and an extra CF condition is added

Is there anything I can do within the Query, or within Excel, to keep this from happening? Or do I have to edit the conditional formatting (vba or manual)? (Or use a different solution to develop the output).

I have tried defining the applies to range using the structured reference, or using a dynamic name range. but that did not work. The applies to gets changed to an absolute reference, and develops the same changes as noted above when the Query is refreshed.

解决方案

This seems to work for me (but I literally just learned you could even do this with data by reading your post, so thank you!).

Set your conditional formatting calculation to

=COUNTIF($A$2:$A2,$A2)>1

and apply it to =$A$2:$A$4.

As an added bonus, every time I refreshed my data after updating the original table, my Applies to under the conditional formatting rule expanded the range down.

这篇关于取得&转换与条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！