Excel进阶:玩转单元格选择和定位

编辑:King 阅读:492 时间:2020-09-21 13:42:04

在Excel中整理、分析或者查找数据的时候,我们大多是围绕单元格进行操作的,这便离不开对单元格的选择和定位。下面笔者就给大家介绍如何快速高效地定位单元格的几种方法。

精准定位——快速找到符合条件的单元格

在统计数据的时候,我们经常需要对于特定行列的数据进行统计排序。比如公司每月都要对销售额排名前三的员工进行奖励,以下图所示的销售数据为例,现在每月输入员工销售数据后,要将排名前三的数据自动同步到A2:C2带颜色的单元格中(图1)。

图1 排序实例

我们首先利用Column函数,在C列中定位前三名数据,然后使用Large函数填充到A2:C2单元格。现在定位到A2单元格,输入公式“=LARGE($C$5:$C$12,COLUMN(A1))”,然后右拉填充到C2即可。由于这里使用公式填充,因此只要员工销售数据变化,排名就会同步发生变化,始终选取的都是前三名的数据(图2)。

图2 函数提取前三名单元格数据

公式解释:使用Column函数在指定列(C2:C12,使用绝对引用)之间获取销售数据的列号,然后使用Large函数进行排序并填充到指定单元格。

模糊定位——单元格数据查询

日常数据统计中,我们经常需要对不特定的对象进行查询。比如在上述例子中,为了查看某个业务员的实际业绩,现在需要制作一个查询数据库,只要输入特定员工的姓名就自动列出他的销售业绩数据。对于这类模糊定位,可以借助Match和Index函数实现。

定位到A15单元格,点击“数据→数据验证→设置”,在允许列表中选择“序列”,点击来源后的按钮,序列的内容选择“=$B$5:$B$12”,即员工姓名列表的内容,这样通过下拉列表就可以直接选择需要查询的员工(图3)。

图3 数据验证设置

返回工作表,定位到B15单元格,输入公式“=MATCH(A15,$B$5:$B$12,0)”,公式的意思是使用Match函数根据A15输入的内容在B5:B1(使用绝对引用)找到员工对应的行数。在C15输入公式“=INDEX($C$5:$C$12,B15)”,意思是根据C15显示的行数,找到对应函数的销售额数值。这样当我们在A15下拉列表选择特定员工姓名时,比如选择“黄可”,其所处行数是6(相对B5),对应的销售额为156,如此通过Index函数就可以轻松进行特定数据的查询了(图4)。

图4 模糊查询数据库

高亮定位——快速显示指定单元格

为了方便在一堆数据中快速找到自己所需的数据,我们可以设置特定的单元格高亮显示,这样方便我们一目了然找到所需的数据。比如在上述实例中,销售数据还要经财务核算才最终生效,为了方便查看数据是否已核算,我们可以利用条件格式对已核算的单元格数据进行高亮显示。

选中A5:D12区域(如果只要D列单元格高亮,则选择D5:D12区域),点击“添加格式→新建格式规则”,在规则列表选中“使用公式确定要设置格式的单元格”,在公式栏输入“=$D5="已核算"”,点击“格式”,在打开的窗户口中选择“填充”,将符合条件的单元格填充为“绿色”(图5)。

图5 设置格式

如此一来,在A5:D12区域中,只要我们在D5:D12单元格中输入“已核算”,那么这列的单元格就会自动被填充为绿色高亮色,这样数据是否核算是不是一目了然了呢(图6)。

图6 单元格高亮定位

分类定位——隐藏特定单元格

日常操作中,为了方便后续的输入,我们在一些工作表中经常会预留很多不连续的空白行(预备行不可删除,以备后续填充内容),现在工作表制作完成后,为了美观需要隐藏这些空行的显示(图7)。

图7 不连续的空白行

以上借助SpecialCells函数结合VBA脚本就可以轻松实现。按Alt+F11键启动VBA编辑器,点击工具栏中的“插入→模块”,将下列的代码粘贴到代码框,完成后运行这个脚本就可以隐藏指定的空行了(图8)。

图8 代码输入

写在最后

单元格作为Excel中最基本的独立单元,几乎所有的操作都是围绕单元格进行的,因此熟悉单元格的选择和定位可以给我们日常操作带来很多的便利。不过由于单元格只是基本独立的单元,这些定位函数也只有基本的应用,所以我们应该将其和其他函数结合起来,这样才能充分发挥这些定位函数的作用。

回到顶部