Excel制作KPI仪表盘技巧分享

文章不知道大家是否熟悉？今天我将给大家介绍《Excel制作KPI仪表盘教程》，这篇文章主要会讲到等等知识点，如果你在看完本篇文章后，有更好的建议或者发现哪里有问题，希望大家都能积极评论指出，谢谢！希望我们能一起加油进步！

要在Excel中构建KPI仪表盘，需依次完成：一、规范数据源并创建智能表格与动态命名区域；二、用公式+条件格式制作KPI卡片；三、插入切片器联动的组合图表；四、用图标集实现交通灯状态指示；五、通过数据验证与INDIRECT函数添加交互式下拉筛选。

如果您希望在Excel中直观展示关键绩效指标（KPI），需将分散的数据转化为具备视觉反馈、动态更新和交互能力的仪表盘。以下是实现该目标的具体方法：

本文运行环境：MacBook Air，macOS Sequoia。

一、准备结构化数据源

仪表盘依赖准确、规范的数据基础。原始数据必须按时间、部门、指标类型等维度清晰分列，避免合并单元格、空行或不一致的标题命名，确保后续公式与图表能自动识别区域边界。

1、新建工作表，命名为“数据源”，将销售、人力、财务等KPI原始记录按列排布，首行为字段名（如“月份”“销售额”“目标完成率”）。

2、选中全部数据区域，按 Ctrl + T（Windows）或 ⌘ + T（Mac） 转换为智能表格，启用“表名称”栏中的默认名称（如Table1）。

3、在“公式”选项卡中点击“名称管理器”，为关键指标创建动态命名区域，例如定义“销售额趋势”引用=OFFSET(数据源!$B$2,0,0,COUNTA(数据源!$B:$B)-1,1)。

二、构建核心KPI卡片

KPI卡片以大号字体突出显示当前值、同比变化与状态标识，是仪表盘的信息焦点。通过单元格格式设置与条件公式组合，实现数值驱动的视觉反馈。

1、在新工作表“仪表盘”中，于A2单元格输入公式：=TEXT(ROUND(INDEX(数据源!$C:$C,MATCH(MAX(数据源!$A:$A),数据源!$A:$A,0)),2),"#,##0.00")，获取最新月份销售额。

2、在B2单元格输入公式：=IF(INDEX(数据源!$C:$C,MATCH(MAX(数据源!$A:$A),数据源!$A:$A,0))>INDEX(数据源!$C:$C,MATCH(LARGE(数据源!$A:$A,2),数据源!$A:$A,0)), "↑", "↓")，生成趋势箭头。

3、选中A2:B2区域，右键“设置单元格格式”，在“数字”选项卡中选择“自定义”，输入：[绿色]#,##0.00;[红色]-#,##0.00；再对B2应用字体颜色条件格式，使“↑”显示绿色，“↓”显示红色。

三、插入动态图表组件

图表需响应筛选操作且不随原始数据行增减而错位。使用切片器联动图表系列，并以组合图强化对比逻辑，例如柱形图配折线图展示实际值与目标线。

1、选中“数据源”中月份与销售额两列，插入“组合图”，将销售额设为簇状柱形图，另添加目标值列并设为折线图，勾选“次坐标轴”。

2、在“插入”选项卡中点击“切片器”，勾选“月份”字段，拖动至图表右侧；右键切片器→“报表连接”，勾选当前图表所在工作表。

3、双击图表纵坐标轴，在“填充与线条”中将目标线颜色设为深灰色虚线（#666666，短划线类型），并在数据标签中添加“目标值”文字说明。

四、添加状态指示器（交通灯）

利用条件格式中的图标集，将KPI完成率映射为红黄绿三色信号，无需额外图形对象，节省资源且易于维护。

1、在“仪表盘”工作表D2单元格输入公式：=INDEX(数据源!$D:$D,MATCH(MAX(数据源!$A:$A),数据源!$A:$A,0))，引用最新完成率数值。

2、选中D2单元格，进入“开始”→“条件格式”→“图标集”，选择“三色交通灯（无轮廓）”。点击“更多规则”，在“图标样式”中设定：>=95%显示绿色圆点，>=85%显示黄色三角，其余显示红色方块。

3、在E2单元格输入公式：=CHOOSE(MATCH(D2,{0,0.85,0.95},1),"未达标","接近目标","达成目标")，同步输出文字判定结果。

五、设置交互式下拉筛选

通过数据验证创建下拉列表，配合INDIRECT函数实现多维度指标切换，避免重复制作多张仪表盘。

1、在“仪表盘”工作表F1单元格输入“部门”，F2单元格设置数据验证：允许“序列”，来源为=数据源!$E$1:$E$5（假设有5个部门名称）。

2、在G1输入“指标类型”，G2设置数据验证，来源为{"销售额","回款率","客户数"}。

3、在H2输入公式：=SUMIFS(INDIRECT("数据源!"&SUBSTITUTE(G2,"销售额","C")&":"&SUBSTITUTE(G2,"销售额","C")),数据源!$E:$E,F2)，其中SUBSTITUTE部分根据G2值动态映射列字母，实现单公式响应多指标切换。

今天关于《Excel制作KPI仪表盘技巧分享》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于的内容请关注golang学习网公众号！