利用EXCEL制作仪表盘图（市场营销KPI分析图）
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">仪表盘图，在广义上，指的是汽车仪表盘上的<span style="color: black;">各样</span>指示灯和图形<span style="color: black;">表示</span>，用于向驾驶员<span style="color: black;">供给</span>车辆运行状态的实时信息。然而，在数据可视化或Excel的语境中，仪表盘图（Dashboard Chart）<span style="color: black;">一般</span>指的是一种高度集成、视觉化呈现的数据报表，它能够快速地让用户<span style="color: black;">认识</span>到一系列关键的业务指标和数据点。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">【背景】</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">KPI即关键绩效指标法。它把对绩效的<span style="color: black;">评定</span>简化为对几个关键指标的考核，将关键指标当作<span style="color: black;">评定</span>标准，对市场<span style="color: black;">营销</span>进行<span style="color: black;">评定</span>。做市场<span style="color: black;">营销</span>工作，需要<span style="color: black;">定时</span><span style="color: black;">或</span>周期性地对之前的工作做数据的监控和<span style="color: black;">评定</span>。市场<span style="color: black;">营销</span>分析，指分析、判断项目投产后所生产的<span style="color: black;">制品</span>在限<span style="color: black;">按时</span>间内<span style="color: black;">是不是</span>有市场，<span style="color: black;">或</span><span style="color: black;">周期</span>性的<span style="color: black;">制品</span><span style="color: black;">营销</span>的<span style="color: black;">状况</span>，以及采取<span style="color: black;">怎么样</span>的营销战略来实现<span style="color: black;">营销</span><span style="color: black;">目的</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">【数据类型】</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/09ffb728b99541c2bccc77507ff73840~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=L0ochvOjhdchqKjCWgl8forHm7w%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">【制作<span style="color: black;">过程</span>】</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1.<span style="color: black;">插进</span>辅助列。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">由于</span>计划用30格刻度，20格用来<span style="color: black;">表示</span>0~100，剩下10格空余，用来<span style="color: black;">表示</span>标题，<span style="color: black;">因此</span><span style="color: black;">创立</span>两列、30行的辅助列，数值都是1。接下来需要对刻度线的标签即(0~100)<span style="color: black;">亦</span>做一个辅助列，<span style="color: black;">这儿</span>需要<span style="color: black;">重视</span>的是，数字与数字之间需要间隔一行，还有需要与后面的饼图对应。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/0d4e910633f9449f9702132e20604f8a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=fkrD%2FH8%2BtjwnyGspWGfZOIJ9aiM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">最后还需要<span style="color: black;">创立</span>一个关于指针的辅助列。假如<span style="color: black;">咱们</span>的KPI是“100分制”，<span style="color: black;">然则</span><span style="color: black;">由于</span>上面采用了30格的刻度，其中直接和<span style="color: black;">咱们</span>的数据表现<span style="color: black;">相关</span>的<span style="color: black;">便是</span>20格。假如KPI的数据是36，<span style="color: black;">那样</span>在刻度上，<span style="color: black;">实质</span><span style="color: black;">表示</span>的数据是:36/3*2=24。<span style="color: black;">因此</span>，需要将<span style="color: black;">营销</span>数据转化为指针，<span style="color: black;">而后</span>做一个辅助列处理一下。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在任意空单元格中输入:=IF(A3<=50,66.7+A31*2/3,A31*2/3-66.6)，其中A3是KPI数据所在位置。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/996be5f177534630ad1df04b65b1ce6b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=6UnGPn9Wex59%2B%2FqiG%2FBz9TgwiRU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在A9位置，输入:0.75；在A10位置，输入:=100-C37。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cad5e3b44e6f44b499996cc47a2102f5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=U0IaMIXk8C%2B8o%2FgQHTRD6ZxH%2Bg4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2.生成原始的圆环图。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选取</span>“E2:F31”数据区域“<span style="color: black;">插进</span>”选项卡|“<span style="color: black;">插进</span>饼图或圆环图”|“圆环图”|“圆环图”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c6400395870f4700850995a4548c4ebc~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=751OUZkOmXGZ%2B4L5rwg2KHL13UU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3.删除图例，修改图表区和绘图区的背景填充。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击图中的图例，按键盘上的Delete键删除。双击图中的绘图区，弹出“设置绘图区格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/dbcee0b2cf374ca0a43087e32ad8d5ce~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=a38WnbS2NwgGNUNBI2n16jUcwII%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">双击图中的图表区，弹出“设置图表区格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”|“边框颜色”|“无线条”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cb4cd4fb6cd84d4b861d199a29091e14~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=tdjOP4wtsc0IYmkOvdvOsjimfTM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4.修改数据系列填充颜色。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">双击图中内环的系列弹出“设置数据系列格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/403655e0b98f4dc69d24463e9224c89c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=%2FyqQ45VgzK6v4jTqrIBwDAl9njQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击图中，外环的系列，鼠标对准需要<span style="color: black;">隐匿</span>的数据点，双击数据点，弹出“设置数据点格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6bcd42c522464c59a2d6642086ec2b63~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=FuA7XfC9j0bSth3q87eolyExzlU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">双击图中的数据系列，弹出“设置数据系列格式”对话框，<span style="color: black;">选取</span>“圆环图分离程度”|“6%”|“圆环图内径<span style="color: black;">体积</span>”|“73%”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b15c856e2b0847579077092c93115b38~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=1d6ZB0w4aMvtJqS6tApxX0yQkM4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击图中外环的系列，鼠标对准需要修改的数据点(先从最左边的<span style="color: black;">起始</span>)，双击数据点，弹出“设置数据点格式”界面，<span style="color: black;">选取</span>“填充”|“纯色填充”“颜色”|“深红”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/05e694b151e94400a1c2321da895e644~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=ZshhRvlhIQKah%2Fh02PFTelvhB9Q%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">根据</span><span style="color: black;">一样</span>的<span style="color: black;">办法</span>，依次对剩下的 19 个数据点的填充颜色进行修改，最后的效果如图所示。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/65f79bd3db44411eb98bed9e331ccee5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=hjDJ4tFNWRI2Bvaf2qpTRlZVeFM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5.制作仪表盘的指针。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选取</span>“A8:A10”单元格区域，单击“<span style="color: black;">插进</span>”选项卡|“饼图”|“二维饼图”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/87aa71eca85141e08c073684f1b4c5e8~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=AJYTCscpPHAH%2BTHnJymEs6BVlgg%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选取</span>“E2:E31”单元格区域，按Ctrl+C组合键复制数据区域，单击选中图中的饼图，按Ctrl+V组合键粘贴。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/2c77e4f804634aea8af5782a7b08db8d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=WxX8DmNksYlMjdT1rrLEPlzv01E%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击图中的图例，按键盘的Delete键删除。双击图中的系列1，弹出“设置数据系列格式”界面，<span style="color: black;">选取</span>“系列选项”|“系列绘制在：次坐标轴”|“饼图分离：20%”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/17487353e7774219847a4c782c216d19~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=iygqDY0%2Btcygrb4z49ay52VJbfc%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">双击图中的系列2(底层的饼图)，弹出“设置数据系列格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”。在同一界面下单击“边框颜色”|“实线”|“颜色”|“黑色”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/725b626080dd45c8bac8eaa70978a327~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=6O0hlhPuqkFrvHBRm08nyan3S3Q%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击选中图中间的饼图，再次单击，选中蓝色区域块，往饼图的圆心移动，选中绿色区域块，往饼图的圆心移动，选中绿色区域块，往饼图的圆心移动，最后的效果如图所示。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/8af364e495c6457a8df943e36f6cfc40~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=svj3lELsheipTpcww7Ud1qn%2BSYc%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击选中图中间的饼图，再分别双击蓝色和灰色区域，弹出“设置数据点格式”界面，<span style="color: black;">选取</span>“填充”|“纯色填充”|“颜色：白色“。双击橙色区域，弹出“设置数据点格式”界面，<span style="color: black;">选取</span>“线条”|“实线”|“颜色：橙色“。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/9f06fff8981c4191bce96a61c2f2a7d1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=dPRHkosRGl6WjNutmUnD4AJx97k%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">双击图中的绘图区，弹出“设置绘图区格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”。双击图中的图表区，弹出“设置图表区格式”界面，<span style="color: black;">选取</span>“填充”|“无填充”|“边框颜色”|“无线条”。单击图中的大饼图|“图表工具’选项卡|“设计”|“图表布局”|“添加图表元素”|“数据标签”|“数据标签外”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/93414daaa4cb42bcaebc8fd312105fe2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=SKpeXvVqdubDkR5qnBzdBG9TJZU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">把多余的10个刻度格对应的数据标签删除。依次选中，按Delete键删除。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/470f3d985414486bb4d7bfbaad835ad3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=nLcRFGagJEe%2BmfoJv3n%2FJMN9ACE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">单击图中的大饼图，单击右键，<span style="color: black;">选取</span>“<span style="color: black;">选取</span>数据”，弹出“<span style="color: black;">选取</span>数据源”界面，<span style="color: black;">选取</span>“系列2”|水平(<span style="color: black;">归类</span>)轴标签|“编辑”，弹出轴标签界面，<span style="color: black;">选取</span>轴标签区域“=Sheetl!$G$2:$G$31”|“确定”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/279219a92168401bbff99e6502bae9e5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=1VZOi4qK9aK2F0CIqT5v2yoSMXI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/0fd9d7993c9c49119d247188f28b4d63~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=rkhJUBSer67%2BfsOLWk1q%2FNewfhU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/8fa00dc0fa8d4b62853924a845b0b6c9~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=VOTInp1J%2FU1SYKmTIotcmpq2UNA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">双击图中的大饼图的数据标签，弹出“设置数据标签”界面，<span style="color: black;">选取</span>“标签选项”|勾选“类别名<span style="color: black;">叫作</span>”|勾选掉“值”。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/3fddafdee25b4f3a8f7092c67ef08166~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=ocPACjQ4OAZXoLeKKE5U4NqrxqA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">6.仪表盘和指针合二为一，修改标题以及细节<span style="color: black;">调节</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">将指针图表的图表标题四个字删掉，<span style="color: black;">而后</span>将图中的仪表盘等比放大覆盖在图中指针上。修改标题，<span style="color: black;">最后</span>效果如图所示。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f460699d575945118da757a16d9628fd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685107&x-signature=8EgyGwUHqmF6xr23KRLsSkQTFi0%3D" style="width: 50%; margin-bottom: 20px;"></div>
一看到楼主的气势，我就觉得楼主同在社区里灌水。 回顾历史，我们感慨万千；放眼未来，我们信心百倍。 这夸赞甜到心里，让我感觉温暖无比。
页:
[1]