Auto-Format Excel Groupby和Pivotby结果有条件格式

了解如何使用Excel的Groupby和Pivotby公式将数据转换为清晰而丰富多彩的摘要报告，并通过条件格式增强。

您可能已经听说过Excel动态阵列功能的最近两个添加：Groupby和Pivotby。这些功能在分组和汇总数据方面表现出色，但是由于缺乏格式，结果通常看起来很简单且难以理解。为了获得更好的可读性和演示，您当然可以手动格式化关键元素，例如标题，小计和总计。但是，由于结果数组是动态的，因此这些元素可能会四处移动以适应原始数据集中的更改。自然，您不想在每次更改数据时重新格式化。幸运的是，您可以通过条件格式自动化此过程，该过程根据所设置的标准动态更新。

• 如何使用Groupby和Pivotby公式使用条件格式
• 有条件格式组成结果
• 有条件格式的枢纽结果

如何将条件格式应用于Groupby和Pivotby结果

要自动格式化groupby或Pivotby公式的结果，您需要根据公式设置几个条件格式规则。这些步骤对于所有规则都是通用的，无论您是要突出显示标题，小计还是总数。这种变化在于用于识别每个元素和格式细节的特定公式。请密切遵守这些建议，以确保最佳结果：

1. 选择格式的范围。首先选择您的GroupBy或Pivotby公式返回的所有结果。如果您的原始数据集可能会发生变化，并且预计将来会增加，请扩展选择，以在下面包含其他空白行。这样，当添加更多数据时，条件格式将自动应用。
2. 访问条件格式。在“主页”选项卡上，单击条件格式>新规则…
3. 创建一个基于公式的规则。在新的格式规则窗口中，选择使用公式来确定要格式化的单元格。然后，在相应的框中输入公式。
4. 选择格式。点击格式按钮以打开格式选项。在这里，您可以根据规则选择将应用的所需格式。
5. 保存规则。通过根据需要单击确定的数量多次确认您的设置，这将保存并应用您的新条件格式规则。

有关详细说明，请参阅如何使用公式设置Excel条件格式规则。

具有条件格式的Excel Groupby公式

此示例显示了如何通过突出显示主要元素（例如标题行，小计和总计）来使Groupby功能的输出更具可读性。

选择有条件格式的应用范围

在此示例中，GroupBy公式将返回一个从F3到H16的动态阵列。但是，对于我们的条件格式规则，我们选择了F3到H32的范围。这包括额外的空白行，以适应未来的任何数据添加，以确保将新数据添加到源表中时自动适用。

选择适当的范围，单击条件格式>新规则…以设置第一个规则。

现在，让我们准确看看可以使用哪些公式和格式化选项。

格式化标题行

格式化组结果的标题行对于将其与其他数据区分开是至关重要的。

要标识标题行，您可以使用一个公式来搜索标题独特的特定单词或短语，该公式在数据集中没有出现在其他地方。这是两种方法：

确切的匹配公式

如果您的标头包含独特的一致术语，请使用此方法。例如，如果“项目类型”是返回数组中的第一个标题，则可以通过在F列中查找文本“项目类型”来识别它：

=$F3="Project type"

部分匹配公式

如果您的标题可能会有所不同（例如“项目类型”或“项目名称”），请使用搜索功能进行部分匹配：

=SEARCH("project", $F3)

由于搜索功能是不敏感的，因此您可以在任何字母的情况下键入文本。

笔记。无论您选择哪种公式，都请记住使用混合的单元格参考（例如$ f3）。这将锁定列，并允许行号进行调整，以确保在整个行中始终应用格式。

格式化

设置公式后，请继续对标题进行大胆的格式。在“格式单元格”对话框中，转到“字体”选项卡，然后在字体样式下选择BOLD 。

此外，您可能需要在标题行下方添加一条彩色线。为此，转到“边框”选项卡，在这里您选择所需的线样式和颜色，然后将其应用于下部单元边框。

这种简单而有效的格式选择大大提高了您的Groupby结果的可读性和专业外观。

突出显示小计

增强组结果中的小计的可见性可以使您的数据分析更加直观和访问。为了实现这一目标，您需要准确识别小计。通常，这些行包含第一级分组列中的值（本示例中的项目类型）和聚合的列（例如收入），而辅助分组列中的单元格（例如状态）是空的。

公式

要构建适当的公式，您可以利用检查这两个条件的功能和功能 - F（项目类型）是否为空白，G（状态）为空白：

=AND($F3"", $G3="")

请注意，上述公式还将格式化大排，因为它符合这两个条件。为了完善在不包括大总数的同时突出显示小计的公式，您可以添加一个逻辑测试：

=AND($F3"", $G3="", $F3"grand total")

格式化

要将背景颜色应用于这些已确定的行，请转到格式单元格对话框中的“填充”选项卡。从那里，您可以选择自己的首选颜色，该颜色将应用于小计行。

可选地，您还可以通过在“字体”选项卡上选择此选项，以粗体斜体上的字句格式化文本。

格式化总排

引起人们对总数的关注对于立即注意数据的关键摘要至关重要。

公式

要识别总排，请制作一个类似于标题行的公式，但是这次您搜索文字“ Grand Total”。该公式是对病例不敏感的，因此您可以在任何情况下输入文本：

=$F3="grand total"

在Excel条件格式中，细胞引用相对于所应用范围内的左上角细胞。因此，我们再次使用混合参考 - 绝对列和相对行编写一个用于单元格$ F3的公式。

格式化

为了更好地区分大排，我们将改变两个元素 - 字体和细胞边框：

• 为了强调宏伟的总文本，请导航到“字体”选项卡，然后在字体样式下选择BOLD 。
• 要在总排上方添加一条线，请切换到“边框”选项卡。在这里，选择您喜欢的线样式和颜色，然后应用上边框以与其余数据的其余部分形成清晰的分离。

有条件格式化的组结果

通过条件格式增强的Groupby公式的结果不仅组织良好，而且在美学上令人愉悦且直观地导航。

笔记。在工作表中的GroupBy输出格式化时，请记住以条件格式的公式正确调整参考文献以匹配特定数据集的布局。有关更多信息，请参见Excel条件格式的单元格引用。

具有条件格式的Excel Pivotby公式

如您所知，Excel Pivottables是自动格式的，以突出显示标题，总计和小计。在此示例中，我们创建了一个枢纽公式，并以相同方式汇总该组并汇总源数据。现在，我们将尝试使用条件格式来复制枢轴表的格式，以实现相似的外观枢轴公式输出。

在创建有条件的格式规则之前，请选择“ Pivotby公式”的结果。可选地，包括下面的几个空白单元格，以自动格式化所有新数据，这些数据可能会在以后添加。在我们的情况下，我们选择范围G3：M32，然后单击条件格式>新规则…开始为标头行创建规则。

突出显示标题行

第一步是使标头行脱颖而出。

公式

在枢轴公式返回的动态阵列中，标题行中的前两个单元格为空白。您可以使用以下公式检查这两个条件：

=AND($G3="", $H3="")

为了使格式正确应用于整个行，请记住锁定列，并允许使用混合单元格引用（如$ g3和$ h3）调整行号。

格式化

为了使标题在视觉上弹出，请考虑应用三个不同的格式元素：

• 格式文本以粗体。这使标题文字引人注目。
• 在下面添加一行。将下部细胞边框格式化带有选定的颜色以创建清晰的分离。
• 强调。为标头行设置填充颜色，以使其在视觉上与众不同。

突出显示小计

如果您的枢轴公式配置为显示小写，则将其格式化以获得更好的可见性和组织是有意义的。

公式

识别小计排的公式检查列中的单元格是否不是空白，并且列中的单元格为空白。此外，它可以确保它不是总的整列，应该以不同的方式格式化。

=AND($G3"", $H3="", $G3"grand total")

格式化

对于小计的格式，您可以采用多种样式的组合：

• 大胆的文字。将粗体格式应用到小计文本以使其更明显。
• 下面的线。添加一个小计行下方的一条线，以在视觉上将其与随后的数据分开。

格式化总排

总排是您数据的关键摘要，因此应突出显示其快速参考。

公式

要识别大行，只需检查单元格是否完全包含此文本：

=$G3="grand total"

请注意，即使大总数位于返回阵列中的最后一行，该公式将在应用范围内的左上角电池中引用。

格式化

要强调大行，请应用以下格式：

• 大胆的文字。使宏伟的总文字粗体以突出其重要性。
• 线上。在大整行上方添加一条线，以将其与上面的数据分开。
• 背景颜色。将独特的填充颜色应用于大行，以使其脱颖而出。

有条件格式格式的枢轴结果

随着有条件格式的应用，枢轴公式的输出在视觉上与枢纽可比性相比，使其非常友善且信息丰富。

当我们结束文章时，我希望您准备使用这些条件格式的技术来使您的Groupby和Pivotby公式讲述一个引人入胜的故事并看起来很棒。

练习工作簿下载

Groupby和Pivotby具有条件格式（.xlsx文件）

以上是Auto-Format Excel Groupby和Pivotby结果有条件格式的详细内容。更多信息请关注PHP中文网其他相关文章！