377.Office技巧：玩转Excel二维表的数据跟踪！

SANSE

牛，怎么这表选择月份和部门，单元格颜色自动定位的？

经典的二维表条件格式颜色定位，这个必须掌握！

牛闪闪

Office技巧那么多，到底要学多少个技巧才能成为高效的办公牛人？在牛闪闪看来Office技巧你永远都学不尽，Office运用的场景太多了。事实上系统的学习某一个软件的工作原理才是你掌握Office的关键。理解他们才能灵活运用他们。

 

今天QQ群小伙伴提出了一个非常“经典”的问题，如何实现二维坐标的颜色定位？什么是二维坐标定位呢？

看看这个例子:在选择B17和B18单元格的“月份：8月和部门：研发部”，A1：M15的区域内（二维表）对应的8月和研发部的数字所在的单元格就会变成绿色。

这对跟踪数据的办公人员来说无疑是个福音。

 

场景：财务、HR、采购、市场，行政部、IT等需要二维表数据追踪的办公人士。

问题：如何实现根据月份和部门（二维表）实现单元格颜色定位？

解答：利用公式条件格式实现。

 

具体操作方法如下：

1

进入条件格式

注意数据选择区域B2：M77单元格，点击“条件格式—新建条件格式”（下图2 处）

2

设置公式

在新弹窗口中，选择“使用公式确定要设置格式的单元格”（下图3 处），在下图4处输入公式：

=AND($A2=$B$18,B$1=$B$17)，并设置格式为底色为紫色，白色字体。

=AND($A2=$B$18,B$1=$B$17)

 

这个公式的意义很重要,解释一下：

$A2=$B$18 表示A列的数据等于B18，注意A2中A有美元符号，表示条件格式会逐行判断A列的数据直到等于B18单元格的内容。上图就是判断A列的数据等于“研发部”。

B$1=$B$17 同理表示判断第一行的数据等于B17单元格的内容，上图就是判断第一行的数据是“8月”

 

=AND($A2=$B$18,B$1=$B$17)：AND函数的意思就满足A列数据是研发部，第一行数据是8月，这个两个条件都满足，条件格式选取内单元格的颜色才能变颜色。

 

我们只要改改B17和B18的月份和部门，对应的单元格就会显示颜色。

总结：这是 二维表数据跟踪中非常重要的一个功能。如果配合index+match函数的搭配，简直就是完美。既可以取数据又可以标记颜色。

END