酷炫的动态可视化交互大屏，用Excel就能做!

比眉伴天荒 2023-05-22 08:53 189阅读 0赞

↑ 关注 + 星标 ~ 有趣的不像个技术号

每晚九点，我们准时相约  ![format_png][]

![format_png 1][]

大家好，我是黄同学![format_png 2][]

前段时间[用tableau做了可视化大屏][tableau]，大家有的说说没学过tableau，有的说不会做，但就是觉得很炫。

因此，有人私聊我说用excel能做可视化大屏吗？当然，这是可以的，基于粉丝的这个需求，我们今天来讲述一下，怎么使用excel做一个简单的可视化大屏。

通过本文，你将会收获如下这些知识：数据去重、excel常用函数、数据验证、excel常用图表、切片器，以及如何利用excel做数据清洗。做完以后的效果图，我们先进行一个展示。

效果如下：

![format_png 3][]

## 1、项目需求 ##

现如今，手机销售市场竞争激烈。某通信公司要求我们从地区、品牌、时间、用户等多维度分析移动终端的销售数据，用于判断和辅助营销决策。

## 2、数据说明 ##

本次案例演示共涉及到两张表，都是从甲方的业务系统中抽取的，并经过脱敏后用于本次案例展示。一张表是详细的销售数据，共涉及到4万多条数据；一张表是手机的价格表，共117条数据。

## 3、本文写作思路 ##

![format_png 4][]

## 4、数据浏览 ##

数据浏览是为了让我们对数据有一个清楚的认识，知道数据源的每一列表示的什么，同时知道哪些数据是脏乱的数据，为以后数据清洗做了一个铺垫。

1）选中所有数据，并打开筛选器

从下图中可以看出，每一列的标题都多了一个筛选器。

![format_png 5][]

2）剔除无关列，保留有用字段

进行操作的时候，不要直接针对源数据进行操作，最好是复制一份数据出来，用于数据操作。经过删除之后，我们最终保留了如下数据。

![format_png 6][]

3）针对每一列数据，使用筛选器进行查看，看看哪些列需要进行数据处理

经过查看，我们发现如下数据存在脏数据，接下来我们将这些列进行标记一下，方便以后做数据清洗(由于现在列数较少，你可以不做标记，但是当列较多的时候，你不标记一下，以后肯定会忘记哪些列需要进行数据处理)。

![format_png 7][]

4）对存在脏数据的列进行表记

![format_png 8][]

## 5、数据清洗 ##

1）针对“月”列的数据清洗

![format_png 9][]

清洗完成以后，可以查看是否清洗干净。

2）针对“地区名字”列的数据清洗

![format_png 10][]

3）针对“年龄”列的数据清洗

![format_png 11][]

注意：针对这一列，我们直接求了一个绝对值。你可能会问，你咋知道这样处理呢？我也不知道，这个你需要和业务方沟通，他如果告诉你这种错误，是数据录入时，不小心加了一个“-”号导致的，那么你就这样处理。

4）针对“年龄”列，进行离散化处理

![format_png 12][]

5）针对“性别”列的数据清洗

其中，1和M表示男生；2和F表示女生。我们这里要做的，就是将所有的1和M变为男生，2和F变为女生。

![format_png 13][]

6）在源数据中增加“单价”这一列

![format_png 14][]

7）新增“销售额”这一列

![format_png 15][]

8）excel中使用if()函数的技巧

![format_png 16][]

## 6、维度汇总 ##

1）维度汇总的初始效果大致如下

当然，我只是为了更清晰的展示这个案例，才写的较为详细，这些步骤你完全可以在下面很快的进行操作。

![format_png 17][]

2）数据验证：添加“年份选择”的下拉框

![format_png 18][]

最终效果如下：

![format_png 19][]

3）求“总销量”：利用sumif()条件求和

![format_png 20][]

4）求“总销售额”：利用sumif()条件求和

![format_png 21][]

5）求“总订单数”：利用countif()条件计数

![format_png 22][]

6）针对“地区维度”的处理

① 数据去重

首先把“源数据”表的地区名字这一列，粘贴到地区这里，然后再选中这一列，点击“删除重复值”。

![format_png 23][]

② 使用sumifs()多条件求和：求出销量

![format_png 24][]

7）针对“品牌维度”的处理

① 数据去重

![format_png 25][]

② 使用sumifs()多条件求和：求出销量

![format_png 26][]

8）针对“时间维度”的处理

① 数据去重

![format_png 27][]

② 使用sumifs()多条件求和：求出销量

![format_png 28][]

9）针对“用户维度”的处理

① 数据去重

![format_png 29][]

② 使用sumifs()多条件求和：求出销量

![format_png 30][]

10）同理，进行销售额分析，这里就不进行一一演示了

![format_png 31][]

## 7、可视化大屏的制作过程 ##

1）给大屏添加一个“深蓝色”的背景

注意：可视化大屏的制作，我们也另外开一个SHEET窗口。

![format_png 32][]

2）插入文本框：添加一个标题

![format_png 33][]

3）另外插入几个文本框，用于制作“订单总数”、“总销售额”、“总销量”和“请插入年份”

![format_png 34][]

4）插入每一个需要展示的图形

这一步其实很简单，大家只需要选中“维度汇总”这个SHEET表中的数据源，然后添加你想绘制的图行即可。由下图可以看出，每一个维度的数据，我们都计算好了摆放在这，你需要啥，就使用啥。我们将做出来的图，直接ctrl+s粘贴到可视化大屏中即可。

唯一难的在哪里呢？就是格式的调整，但其实格式的调整也很容易，不就是“点击”吗，因此大家下去自行琢磨。

![format_png 35][]

最终效果如下：

![format_png 36][]

5）在“可视化大屏”中，进行联动效果的设置

![format_png 37][]

从上图中可以看出，这里每一个条件求和公式中，引用的日期都是“维度汇总”sheet表中的F1单元格。但是在大屏中，我们同样做了一个这样的下拉菜单，我们需要将这个页面中，引用了“维度汇总!$F1”单元格的公式，全部替换为“可视

化大屏的制作1”单元格的公式，全部替换为“可视化大屏的制作C$3”。

① 完成如下操作：显示公式的操作(以前没有用过的)

![format_png 38][]

② 当出现如下界面，点击ctrl+F，打开替换窗口，并完成如下操作

![format_png 39][]

③ 接着，关闭“显示公式”的操作

![format_png 40][]

④ 将“维度汇总”中如下三个单元格的公式，都复制粘贴到可视化大屏中

![format_png 41][]

操作如下：

![format_png 42][]

⑤ 效果展示

![format_png 3][]

## 8、数据的获取 ##

文中涉及的源数据

关注公众号「凹凸数据」后台回复“**0416**”即可获取

![format_png 43][]

[![format_png 44][]][format_png 44 1]

[![format_png 45][]][format_png 45 1]

[![format_png 46][]][format_png 46 1]

[![format_png 47][]][tableau]

**后台回复「****进群****」，加入读者交流群~**

昨日留言“安宁”、“66和熙熙”、“Gephi”+20积分

**点击**[**「****积分****」**][Link 1]**，了解积分规则~**

![format_png 48][]

**五**

【凹凸数据】为大家送上1本《从零开始学Hadoop大数据分析》，点赞最多留言可获得，截止时间4月17日晚8点

![format_png 49][]

朱小五

![format_png 50][]

[format_png]: /images/20230521/52b6341bb5394bc2a5eaaa7421eed61a.png
[format_png 1]: /images/20230521/9f3b27ed784347609b0fb9c06fa1e298.png
[format_png 2]: /images/20230521/e1e3a9eef4a44d169a2c9afbd4f59e44.png
[tableau]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d22bfc93aaba9bd906b43ccef454f9d94eed943a51d3c6901eb188a3cca3f8d8036ece866&idx=1&mid=2247485243&scene=21&sn=ed9387be66ef9b9711b57e79ac8a0baf#wechat_redirect
[format_png 3]: /images/20230521/b17a12d69380463dbd6ec6ee280f3a1c.png
[format_png 4]: /images/20230521/d988cf9b429d4380a9cd892395a64b82.png
[format_png 5]: /images/20230521/78bfc76230d946d2982cb0daad3f26ce.png
[format_png 6]: /images/20230521/625470f32efc4d939408d1a62b2408c4.png
[format_png 7]: /images/20230521/f00215df806840d2a91010a1ae1cfc55.png
[format_png 8]: /images/20230521/b832ec731e524c549c3f3284e07c83bb.png
[format_png 9]: /images/20230521/4f66e1cceab0478f9dabbfd54a29f71c.png
[format_png 10]: /images/20230521/be032b23c28f4c4fbc3a775f319b57c0.png
[format_png 11]: /images/20230521/39ee4617c7b840c6b56c84403f20c511.png
[format_png 12]: /images/20230521/32bf0ffe7a8d456eb45967e60b93462b.png
[format_png 13]: /images/20230521/149aa694fd124761a14364902615f22b.png
[format_png 14]: /images/20230521/d117deb0168d4118acd2dc328afed4a3.png
[format_png 15]: /images/20230521/b0abf8edbc7740c6866f13188d6794ce.png
[format_png 16]: /images/20230521/fab90176e7ad4d11bbb69d35f34bdb5f.png
[format_png 17]: /images/20230521/9542725fe44d48adb7fc0790f8ff3636.png
[format_png 18]: /images/20230521/9e0f07dc8f0c437d9f6abe99ecf80d7f.png
[format_png 19]: /images/20230521/01d369b6324b47b1b1bd68f48e8953e6.png
[format_png 20]: /images/20230521/1906aa9ed2fb40989d23c8bbf7ace28b.png
[format_png 21]: /images/20230521/0e768374cce14bc28fb893614f8f230a.png
[format_png 22]: /images/20230521/a67f47aef86f4233a17d3674a5d97dc2.png
[format_png 23]: /images/20230521/159534d7a51049a5bed581b186f9fcad.png
[format_png 24]: /images/20230521/93dea1c9650d4e999fe32b9b2fe307c3.png
[format_png 25]: /images/20230521/33a5dde5e4544108ac066d41b07fc8f3.png
[format_png 26]: /images/20230521/e60babc601774de9b72f6e7d75f24660.png
[format_png 27]: /images/20230521/598ed1b401084e4d92da566b5871b39a.png
[format_png 28]: /images/20230521/760dd5228fa145cf82020d9862aeca0c.png
[format_png 29]: /images/20230521/2e2f87afb9df4f05b754409c86fe7787.png
[format_png 30]: /images/20230521/34d681c7bcab4a9daba472919ee9bcf4.png
[format_png 31]: /images/20230521/718adb72e5934cccbb9870b490b38047.png
[format_png 32]: /images/20230521/df989b6dad464853942de29565bf81e6.png
[format_png 33]: /images/20230521/c89b858c51e2445a81c475b46e11c209.png
[format_png 34]: /images/20230521/463e37cf9ab94d019216a6bac91af312.png
[format_png 35]: /images/20230521/d67d7ad94c04406c8696d05a8feb8d1b.png
[format_png 36]: /images/20230521/4d93c0b901d14c8aa1b312012e14f591.png
[format_png 37]: /images/20230521/834a381d6bca4b49a3fd125b7a7f53a5.png
[format_png 38]: /images/20230521/da38870af23b4f07996f4ab3fd9b2d5c.png
[format_png 39]: /images/20230521/d9ee762f2aa74add90d4f3f2abb17fca.png
[format_png 40]: /images/20230521/fbb9fb3d6ada400088aad8df56f4a7cb.png
[format_png 41]: /images/20230521/1e4ee70232604049ad73f5cbeb757d63.png
[format_png 42]: /images/20230521/7bdd0b0a101c406d9fdcf5e16ca6e9ba.png
[format_png 43]: /images/20230521/4327c1faab294ac79e847c3e07bb05b8.png
[format_png 44]: /images/20230521/a04731de97a743c69e697b47cacd99a5.png
[format_png 44 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d2cd1c93aa5c7b334c35cfb6e5cc007e829e587d8ffa607364c402057f8f5f49e45fddef2&idx=1&mid=2247485781&scene=21&sn=65dd84aba6fea1b46fa3f0842e5c47e0#wechat_redirect
[format_png 45]: /images/20230521/a085dc4b039f420aba6a024e141650ae.png
[format_png 45 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d2c9ac93aa58c526505bc42a03b2dfe80ab49b0ba1aaaa712f7505444ec2a890b8551a001&idx=1&mid=2247485726&scene=21&sn=80b1d47b69b5ae44d1ce3f45e7beb200#wechat_redirect
[format_png 46]: /images/20230521/140bdaaef9b74ee4a6d446ca68f86cc6.png
[format_png 46 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d2cccc93aa5da0cfda62e842faf0347ba247196aa4a7d47f4e199e91a2575745a1856014d&idx=2&mid=2247485768&scene=21&sn=dcdf203014743d860b1f427954e114b9#wechat_redirect
[format_png 47]: /images/20230521/383edca584b24dbc8237a0910ee0496c.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d2d09c93aa41f27ba343ab6e4459459be2397442e01d437e19e0931cbafe0b73ebf0a2379&idx=1&mid=2247485581&scene=21&sn=816be9b259ca857499ae6bd6be9363d9#wechat_redirect
[format_png 48]: /images/20230521/80d19274746943c8840a0d1ef2aa07a1.png
[format_png 49]: /images/20230521/1d3787b4ef1442e9bae3d12b5a8dfcaa.png
[format_png 50]: /images/20230521/60d61a5bf57f470280ffe74d2e58260a.png