办公小技巧：一步到位 Excel数据排序玩自动

很多公司经常需要对各项销售数据进行排序，如找出销售业绩最高的员工予以奖励、销售业绩最差的员工予以警示等。常规方法是输入数据后手动点击“排序”，但效率低下。而借助Excel内置的函数和VBA，我们则可以实现数据的自动排序，比手动排序的效率要高得多。下文以Excel 2019操作为例。

○使用函数

比如现在要在公司前台的大屏幕上实时展示每位员工的销售业绩排名，并对销冠和最后一名员工添加提示文本和颜色填充（图1）。

在Excel中打开销售数据后，定位到D2单元格并输入公式“=RANK(C2,C$2:$C$10)”，下拉填充到D10单元格，这样RANK函数就会自动从C2:C10区域中读取销售数据并显示位次。继续在E2单元格中输入公式“=IFERROR(IFS(C2=LARGE(C:C,9),A2&"，你是倒数第一，请努力",C2=LARGE(C:C,1),A2&"，你是销冠，请保持"),"")”并下拉，这样在销冠和最后一名员工的后面就会自动添加上相应的文字提示（图2）。

E2单元格公式解释：

这里先使用LARGE函数读取数据，然后将其作为IFS函数的判断条件，如果是第一名和最后一名则自动加上相应的文字提示，最后再将结果作为IFERROR函数的判断条件，符合的话则直接显示，否则显示为空。

火速链接：LARGE函数的具体使用方法，可以参考本刊2019年第8期的《条条道路通罗马 玩转单元格选择和定位》一文。

单元格的颜色填充效果可以借助条件格式实现。选中E2:E10区域，点击“条件格式→突出显示单元格规则→文本包含”，分别设置包含“销冠”和“倒数”文本时单元格填充不同的颜色，这样即可实现例图的效果（图3）。

由于这里使用函数进行排序和添加提示，因此当我们在C列中更新员工的销售数字后，工作表也会自动进行重新排序，实时显示员工的销售排名。为了能在大屏幕上获得更好的显示效果，还可以在E1单元格中输入文本“销售业绩统计，截止到今天”，对齐方式设置为“右对齐”。接着在F1单元格中输入公式“=NOW()”，右击该单元格并选择“设置单元格格式”，切换到“数字→时间”，“类型”选择“时分秒”，对齐方式为“左对齐”。接着依次选中E2:F2、E3:F3、……区域，点击“开始→合并后居中”，再点击“视图→去除网格线的勾选”（图4）。

复制A1:F10单元格，然后点击“开始→粘贴→其他粘贴选项→链接的图片”，粘贴到其他单元格处，这样即可在图片中实时展示销售数据，并且更改源数据后图片中的内容也会随之更新。最后再对图片稍加美化，如取消网格线、进行三维设置等，即可获得更好的展示效果（图5）。

○使用VBA

上述方法需要使用多个函数，操作步骤稍显繁琐，而借助VBA则会方便不少。比如现在需要将销售业绩前三名的员工始终突出排列在前三行，并分别添加冠军、亚军、季军字样（图6）。

在文档中输入销售数据后，插入一个B列，在B2单元格中输入公式“=C2”并下拉填充公式。接着为B列添加一个“数据条填充”的条件格式，填充颜色选择蓝色，这样能方便直观地查看数据比对。继续点击“开发工具→宏→录制宏→新建一个宏1”，选中C2:C12区域，点击“数据→排序→降序排列”，在打开的窗口中选择“扩展选定区域”（图7），点击“排序”按钮，最后点击“停止录制”完成宏1的录制。

然后按“Alt+F11”快捷键打开“开发工具”窗口，在打开的VBA编辑窗口中点击“插入→模块”，在代码设计框中输入下列代码（图8）：

Private Sub Worksheet_Change(ByVal Target As Range)

宏1

End Sub

在D2:D4单元格中依次输入冠军、亚军、季军字样，最后将文件另存为“a.xlsm”备用。由于代码中使用“Worksheet_Change”（工作表变化事件）来激活宏的运行，这样以后只要在该文件中输入员工的销售数据就会自动调用“宏1”完成排序，就能实现图6所示的效果。当然，也可以选中A1:D12区域，将其粘贴为链接图片在大屏幕上展示。

原文链接

https://www.cfan.com.cn/2022/0629/136672.shtml