同事用Excel做的动态条形图,为什么这么漂亮? |
您所在的位置:网站首页 › excel图表条形图降序 › 同事用Excel做的动态条形图,为什么这么漂亮? |
0
分享至
用微信扫码二维码 分享至好友和朋友圈 点击蓝字【秋叶 Excel】 发送【礼包】 免费领办公神器、Office 模板和免商字体! 本文作者:小花 本文编辑:竺兰 PS.文末下载练习文件。 在 文章中,小花分享了错位折线柱形组合图的绘制方法。 今天的文章,同样来自小 K 同学的实战需求,小花再次为她绘制了一款能够自动排名的条形图,一起来学习吧! 事情是这样的。负责 G 集团华南区域的小 K 同学,这次需要对区域内 10 个项目的业绩完成率排名进行晾晒。 领导要求用图表来表示各项目的完成情况,这根本难不倒我们的绘图小能手小 K 同学。 吸取上次绘图的经验,小 K 同学并没有直接直接这种基础款图表,而是对图表进行了美化,包括修改坐标轴最大值、条形填充色、间隙宽度等等。 没想到,还是无法让「懂哥」上司满意: 你这图的质量和上次那张有明显差距,这不是你的水平啊。你看啊,条形要从完成率高低排列,前三用金银铜三个颜色分别表示,其他用任意浅色就行,最后一名用黑色重点展示,我的意思你明白吗?不得不承认,「懂哥」此言非常在理,让小 K 同学自惭形秽,灰溜溜地回去改图了,好在修改起来并不算太困难,只需将数据源升序排列,再用十进制 RGB 色值进行标色,就能够完成。 注:金色 RGB 色值(255,215,000),银色 RGB 色值(192, 192, 192),铜色 RGB 色值(184,115,51),黑色 RGB 色值(0,0,0)。 你以为这次的作品「懂哥」就满意了? 「懂哥」看完图后,说道: 得让条形图能够根据完成率情况自动从高到低排列,方便定期观察完成率排名情况,能做到吗?这可难倒小 K 同学了,好在,有小花提供技术援助,轻松搞定「懂哥」的高级需求! 有经验的小伙伴一定发现了,小 K 同学的图表已经相当完备,完全没必要重新绘制,只需使数据源区域 A2:B11 能够根据完成率的大小自动更新即可。 注意,不能是直接排序,单元格位置不能产生变化,否则,条形图标色会出现错误。 正确的做法之一是将数据源填列在其他区域,将原绘图数据区域通过公式索引数据源,实现排序,即可确保图形不发生错乱。 很可惜,小 K 同学所得公司的 Excel 并不支持 SORT 函数这样的专业排序函数。 好在,我们可以使用 INDEX+SMALL+ROW 函数组合来构建数组运算实现自动排序功能,还不懂公式原理的小伙伴可以复习下小花的文章 。 A2 公式如下: {=INDEX($E$3:$E$12,MOD(SMALL($F$3:$F$12+ROW($1:$10)%%,ROW()-1),0.01)*10000)}B2 公式如下: =VLOOKUP(A2,$E$3:$F$12,2,0)设置好公式并绘制图表后,当数据源更新后,图表就可以实现自动按排名绘制条形图了。 当然,除了使用公式,我们还可以用 VBA 代码来完成自动排序功能,代码如下: Private Sub Worksheet_Change(ByVal Target As Range)Dim ma, mi, i, j, k, pDim arr(1 To 10, 1 To 2)Dim dirSet dir = CreateObject("Scripting.Dictionary")Rng = [a2:b11] '可修改单元格区域If Application.Intersect(Target, Range("B2:B11")) Is Nothing Then Exit Sub '可修改单元格区域 Application.EnableEvents = Falsema = Application.Max([b2:b11]) * 100 '可修改单元格区域及所需精度mi = Application.Min([b2:b11]) * 100 '可修改单元格区域及所需精度For i = 1 To UBound(Rng)dir(Rng(i, 2)) = dir(Rng(i, 2)) & "\" & Rng(i, 1)Next ik = 1For j = mi To maIf dir.exists(j / 100) Then '可修改所需精度For i = 1 To UBound(Split(dir(j / 100), "\"))arr(k, 1) = Split(dir(j / 100), "\")(i)arr(k, 2) = j / 100k = k + 1Next iEnd IfNext j[a2].Resize(k - 1, 2) = arr '此处可根据实际情况修改单元格区域Set dir = NothingApplication.EnableEvents = TrueEnd Sub只需点击【Alt+F11】,将该代码粘贴到当前工作表的代码窗口中即可。 之后,只要修改当前工作表的 B2:B11 单元格,代码即可自动运行,完成排序,进而完成自动排名条形图的绘制。 以上,就是小花分享的自动排名条形图绘制方法,实现方式有两种: ❶ 以 SORT 或 INDEX 等函数构建公式,构建动态索引的绘图数据区域; ❷ 使用 VBA 编写代码,通过 Worksheet 的 Change 事件驱动程序实现对源数据的动态排序。 你学会了吗?你还知道哪些其他实用又有趣的图表呢?欢迎留言与我们分享或留下你的问题,同学习,共进步,更待何时! 如果你需要练习文件,在我们公众号后台回复关键词【排名】即可下载~ 其实像这样提高效率的小技巧,Excel 中还有很多很多,比如 REPT 生成五星评分、SUM 快速求和、DATEDIF 计算年龄等等。 ▲ 左右滑动查看 为了方便大家搞定 Excel 问题,提高工作效率,我精心准备了如上图所示的【秋叶 Excel 知识卡片】。 一共109 个常用的 Excel 小技巧,函数、图表、技巧、透视表都有,全部制作成方便收藏、查看、打印的卡片形式,包含图片和 PDF 两种版本 (PDF 版目录添加了超链接,点击即可跳转到对应的页面,方便大家查找) 。 工作中遇到 Excel 问题,都可以先看看知识卡片有没有相关技巧,免得百度半天还找不到正确方法! 简单放个目录,给大家瞧瞧: ▲ 部分目录 如何下载知识卡片? 只要你是秋叶 Excel 的读者,仅需 1 元,报名《3 天 Excel 集训营》,即可免费领取以上资料包。 这门课专为职场人准备,全部基于职场真实表格案例设计,还有很多超实用 Excel 技巧教学。 每天学习大概30 分钟,从日常的功能出发,全程演示,一课一练,夯实进阶每一步。 秋叶3 天Excel 集训营 点击下方小程序立即加入 报名成功不要马上退出,记得添加班班微信 发送暗号【知识卡片】,领取福利哦~ 以上内容包含广告 特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。 Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services. /阅读下一篇/ 返回网易首页 下载网易新闻客户端 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |