请选择 进入手机版 | 继续访问电脑版


一步到位 Excel数据排序玩自动

4945
回复
13049527
查看
    [复制链接]
等级头衔

Rank: 45Rank: 45Rank: 45

用户成就
UID
4
听众
2474721
威望
550
贡献
196
在线时间
8 小时
注册时间
2014-2-25

发表于 2022-8-30 12:13:12 | 显示全部楼层 |阅读模式
经常需要对各项销售数据进行排序,如找出销售业绩最高的员工予以奖励、销售业绩最差的员工予以警示等。常规方法是输入数据后手动点击“排序”,但效率低下。而借助Excel内置的函数和VBA,我们则可以实现数据的自动排序,比手动排序的效率要高得多。下文以Excel 2019操作为例。
○使用函数
比如现在要在公司前台的大屏幕上实时展示每位员工的销售业绩排名,并对销冠和最后一名员工添加提示文本和颜色填充。
360截图20220830121049561.jpg
在Excel中打开销售数据后,定位到D2单元格并输入公式“=RANK(C2,C$2C$10)”,下拉填充到D10单元格,这样RANK函数就会自动从C2:C10区域中读取销售数据并显示位次。继续在E2单元格中输入公式“=IFERROR(IFS(C2=LARGE(C:C,9),A2&",你是倒数第一,请努力",C2=LARGE(C:C,1),A2&",你是销冠,请保持"),"")”并下拉,这样在销冠和最后一名员工的后面就会自动添加上相应的文字提示。
360截图20220830121058728.jpg
E2单元格公式解释:
这里先使用LARGE函数读取数据,然后将其作为IFS函数的判断条件,如果是第一名和最后一名则自动加上相应的文字提示,最后再将结果作为IFERROR函数的判断条件,符合的话则直接显示,否则显示为空。
火速链接:LARGE函数的具体使用方法,可以参考本刊2019年第8期的《条条道路通罗马 玩转单元格选择和定位》一文。
单元格的颜色填充效果可以借助条件格式实现。选中E2:E10区域,点击“条件格式→突出显示单元格规则→文本包含”,分别设置包含“销冠”和“倒数”文本时单元格填充不同的颜色,这样即可实现例图的效果。
360截图20220830121109471.jpg
由于这里使用函数进行排序和添加提示,因此当我们在C列中更新员工的销售数字后,工作表也会自动进行重新排序,实时显示员工的销售排名。为了能在大屏幕上获得更好的显示效果,还可以在E1单元格中输入文本“销售业绩统计,截止到今天”,对齐方式设置为“右对齐”。接着在F1单元格中输入公式“=NOW()”,右击该单元格并选择“设置单元格格式”,切换到“数字→时间”,“类型”选择“时分秒”,对齐方式为“左对齐”。接着依次选中E2:F2、E3:F3、……区域,点击“开始→合并后居中”,再点击“视图→去除网格线的勾选”。
360截图20220830121119392.jpg
复制A1:F10单元格,然后点击“开始→粘贴→其他粘贴选项→链接的图片”,粘贴到其他单元格处,这样即可在图片中实时展示销售数据,并且更改源数据后图片中的内容也会随之更新。最后再对图片稍加美化,如取消网格线、进行三维设置等,即可获得更好的展示效果。
○使用VBA
上述方法需要使用多个函数,操作步骤稍显繁琐,而借助VBA则会方便不少。比如现在需要将销售业绩前三名的员工始终突出排列在前三行,并分别添加冠军、亚军、季军字样。
360截图20220830121208033.jpg
在文档中输入销售数据后,插入一个B列,在B2单元格中输入公式“=C2”并下拉填充公式。接着为B列添加一个“数据条填充”的条件格式,填充颜色选择蓝色,这样能方便直观地查看数据比对。继续点击“开发工具→宏→录制宏→新建一个宏1”,选中C2:C12区域,点击“数据→排序→降序排列”,在打开的窗口中选择“扩展选定区域”,点击“排序”按钮,最后点击“停止录制”完成宏1的录制。
360截图20220830121217526.jpg
然后按“Alt+F11”快捷键打开“开发工具”窗口,在打开的VBA编辑窗口中点击“插入→模块”,在代码设计框中输入下列代码:
360截图20220830121228824.jpg
Private Sub Worksheet_Change(ByVal Target As Range)
宏1
End Sub
在D24单元格中依次输入冠军、亚军、季军字样,最后将文件另存为“a.xlsm”备用。由于代码中使用“Worksheet_Change”(工作表变化事件)来激活宏的运行,这样以后只要在该文件中输入员工的销售数据就会自动调用“宏1”完成排序,就能实现图6所示的效果。当然,也可以选中A112区域,将其粘贴为链接图片在大屏幕上展示。

学客联盟
回复

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
4476960
听众
0
威望
1
贡献
1
在线时间
0 小时
注册时间
2021-12-26

发表于 2022-8-30 12:13:13 | 显示全部楼层
我来了,我来了。
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
3971713
听众
0
威望
3
贡献
3
在线时间
0 小时
注册时间
2021-4-14

发表于 2022-8-30 12:13:24 | 显示全部楼层
只从来了这里,不想走了
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
4695819
听众
0
威望
2
贡献
2
在线时间
0 小时
注册时间
2022-2-3

发表于 2022-8-30 12:13:36 | 显示全部楼层
还是非常好的
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
5034446
听众
0
威望
1
贡献
1
在线时间
0 小时
注册时间
2022-3-19

发表于 2022-8-30 12:13:47 | 显示全部楼层
互联网的收费内容太多了,就这里免费
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
4337124
听众
0
威望
2
贡献
2
在线时间
0 小时
注册时间
2021-12-9

发表于 2022-8-30 12:13:58 | 显示全部楼层
简单一看就好了,不用那么繁琐
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
4626642
听众
0
威望
2
贡献
2
在线时间
0 小时
注册时间
2022-1-23

发表于 2022-8-30 12:14:09 | 显示全部楼层
挺好的,网站很漂亮,我学习到技术了
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
3680353
听众
0
威望
1
贡献
1
在线时间
0 小时
注册时间
2019-1-4

发表于 2022-8-30 12:14:20 | 显示全部楼层
现在的效果比之前好看多了。
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
4659347
听众
0
威望
1
贡献
1
在线时间
0 小时
注册时间
2022-1-28

发表于 2022-8-30 12:14:20 | 显示全部楼层
YYDS
学客联盟
回复 支持 反对

使用道具 举报

等级头衔

Rank: 5Rank: 5Rank: 5

用户成就
UID
5093975
听众
0
威望
1
贡献
1
在线时间
0 小时
注册时间
2022-3-26

发表于 2022-8-30 12:14:31 | 显示全部楼层
抢个位子
学客联盟
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

电脑技术交流学习
132-3591-5703
周一至周五 9:00-18:00
意见反馈:admin@cnxklm.com

扫一扫关注我们

Powered by NST! X3.4© 2001-2021技术支持( 闽ICP备14006427号 )|意见建议