不会VBA也能行!超详细Excel生产数据分析看板搭建指南

一、前期准备

1. 设计思路

本看板是一个动态可视化仪表盘，基于 Excel 的数据透视表、图表、条件格式和公式实现，无需 VBA 编程。主要分为：

• 数据源表：存储原始数据

• 统计计算表：通过公式处理数据

• 看板展示页：用图表和控件展示分析结果

2. 创建表格结构

新建 Excel 工作簿，创建以下工作表：

1. Data（数据源）

2. Calc（统计计算）

3. Dashboard（看板）

二、数据源表（Data）设计

1. 基础字段设置

在 Data 表中创建以下列：

A列：日期 (如：2024-01-01) B列：生产项目 C列：生产数量 D列：不良数量 E列：不良类型1（不良类型2、3...从G列往后）

2. 公式列设置（紫色标题列）

• F列：不良率 → 公式：=IF(C2=0,0,D2/C2)

• 设置该列为百分比格式

3. 不良类型动态扩展

• 从 G列开始 为各不良类型的数量记录

• 不良类型名称写在第一行（G1、H1...）

• 后续可自由增加列，无需修改公式

三、统计计算表（Calc）设计

1. 创建月份选择单元格

• B1单元格：设置为月份选择，如 "2024-01"

• 使用数据验证创建下拉列表选择月份

2. 生产项目排名公式

# 当月生产数量前10名 =LET(     data, FILTER(Data!$B$2:$C$1000, TEXT(Data!$A$2:$A$1000,"yyyy-mm")=Calc!$B$1),     proj, CHOOSECOLS(data,1),     qty, CHOOSECOLS(data,2),     SORT(UNIQUE(HSTACK(proj, SUMIFS(qty, proj, proj)), FALSE, 2, TRUE), 2, -1) )

*注：Excel 2021/365 支持 LET 函数，旧版本可使用 SUMIFS+数据透视表实现*

3. 每日不良率统计

# 计算当月每日不良率 =FILTER(     HSTACK(         Data!$A$2:$A$1000,         Data!$F$2:$F$1000     ),     TEXT(Data!$A$2:$A$1000,"yyyy-mm")=Calc!$B$1 )

4. 不良类型TOP10

# 统计各不良类型数量并排名 =LET(     dates, Data!$A$2:$A$1000,     types, Data!$G$2:$Z$1000,     filtered, FILTER(types, TEXT(dates,"yyyy-mm")=Calc!$B$1),     typeNames, Data!$G$1:$Z$1,     totals, BYCOL(filtered, LAMBDA(col, SUM(col))),     sorted, SORT(HSTACK(TOROW(typeNames), TOROW(totals)), 2, -1),     TAKE(sorted, 10) )

四、看板制作（Dashboard）

1. 布局设计

将 Dashboard 页面划分为6个区域，对应PDF中的模块：

• 左上：生产项目排名 + 趋势对比

• 中上：月份选择器 + 关键指标（生产总数、不良总数）

• 右上：不良类型TOP10 + 趋势对比

• 中中：每日不良率折线图（带预警）

• 左下：每日产量柱状图

• 右下：生产占比环形图

2. 月份切换功能

1. 插入表单控件：开发工具 → 插入 → 组合框

2. 设置数据源为月份列表（如：2024-01, 2024-02...）

3. 链接到 Calc!$B$1

3. 生产项目排名列表

1. 复制 Calc 表中的排名数据

2. 使用条件格式 → 数据条可视化数量对比

3. 添加上月对比列，公式示例：

   =IFERROR(     VLOOKUP(A2, 上月数据区域, 2, FALSE) - B2,     "新项目" )

4. 不良率图表制作

步骤：

1. 插入折线图，数据源为 Calc 表中的每日不良率

2. 添加预警线：

• 在 Calc 表中新增预警值单元格（如 $K$1）

• 图表中添加新系列，值为 =$K$1

• 设置为红色虚线

3. 设置条件格式（黄色高亮）：

=AND($B2>=$预警值,$B2<>"")

• 设置填充颜色为黄色

• 选中日期数据区域

• 条件格式 → 新建规则 → 使用公式：

5. 每日产量柱状图

1. 插入柱状图

2. 数据源：Calc 表中的每日产量数据

3. 设置最高产量突出显示：

• 新增辅助列：=IF(B2=MAX($B$2:$B$31), B2, NA())

• 添加到图表作为新系列，设置为绿色

• 更改系列图表类型为柱形图

6. 生产占比环形图

1. 插入环形图

2. 数据源：

• 当月产量

• 上月产量

• 其他月份总和（全年-当月-上月）

3. 设置数据标签显示百分比

五、交互功能实现

1. 动态标题

在 Dashboard 上方添加标题单元格：

="生产数据分析看板 - " & TEXT(Calc!$B$1, "yyyy年mm月")

2. 关键指标卡片

当月生产总数：=SUMIFS(Data!$C:$C, Data!$A:$A, ">="&EOMONTH(Calc!$B$1,-1)+1, Data!$A:$A, "<="&EOMONTH(Calc!$B$1,0)) 累计生产总数：=SUM(Data!$C:$C) 当月不良总数：=SUMIFS(Data!$D:$D, Data!$A:$A, ">="&EOMONTH(Calc!$B$1,-1)+1, Data!$A:$A, "<="&EOMONTH(Calc!$B$1,0))

3. 趋势箭头

在排名对比列中添加趋势箭头：

=IF(C2>0, "↑", IF(C2<0, "↓", "→"))

设置条件格式：↑绿色、↓红色、→灰色

六、美化与保护

1. 整体美化

• 使用统一的配色方案（建议蓝、绿、灰为主）

• 设置合适的字体和大小

• 对齐所有图表和控件

• 添加公司Logo和标题样式

2. 保护设置

1. 解锁可编辑区域：选中需编辑的单元格（如预警值）→ 右键 → 设置单元格格式 → 保护 → 取消"锁定"

2. 保护工作表：审阅 → 保护工作表 → 设置密码

• 允许用户：选择锁定单元格、编辑未锁定单元格

3. 保护工作簿结构：防止增删工作表

3. 全屏查看设置

1. 添加提示文字："按 Ctrl+Shift+F1 全屏查看"

2. 可创建宏按钮一键全屏（可选）：

   Sub ToggleFullScreen()     ActiveWindow.DisplayFullScreen = Not ActiveWindow.DisplayFullScreen End Sub

七、使用与维护说明

1. 数据录入

• 每日在 Data 表最后一行添加新数据

• 不良类型列可按需增加

• 不要删除或修改紫色公式列

2. 功能调整

• 修改预警值：直接在 Dashboard 页的预警值单元格修改

• 增加不良类型：在 Data 表 G 列后直接添加新列

• 修改图表样式：右键图表 → 取消组合 → 修改 → 重新保护

3. 故障排除

八、进阶优化建议（可选）

1. 添加数据验证

在 Data 表设置下拉列表：

• 生产项目：数据验证 → 列表

• 不良类型：数据验证 → 列表

2. 自动数据更新

使用 Power Query：

1. 数据 → 获取数据 → 从文件 → 从工作簿

2. 连接外部数据源自动更新

3. 移动端适配

• 使用切片器替代下拉菜单

• 放大关键图表和文字

• 简化布局为单列排列