Excel数据动态看板制作-以教师薪酬统计为例
- 一、数据处理
- 二、数据分析
- 三、看板制作
- 四、插入切片器
- 五、图表类型
原始数据如图所示：
一、数据处理
1、工龄计算：=DATEDIF(G3,TODAY(),“Y”)
2、工龄工资计算：=IF(H350>500,500,H350)
3、出勤率计算：
（1）插入列：起始日期，结束日期，工作日
（2）起始日期：=DATE(2020,AF3,1)
（3）结束日期：=DATE(2020,AF3+1,0)
（4）工作日：=NETWORKDAYS(L3,M3)
（5）出勤率：=K3/N3（注意改成数字格式）
4、基本工资计算：
出勤率*岗位工资
5、全勤奖计算：
=IF(O3>=1,200,0)
6、税前应发工资总额：
基本工资+全勤奖+工龄工资+降温补贴
=P3+SUM(S3:W3)=SUM(P3,S3:W3)
二、数据分析
1、表格化处理：
全选表格（不包括最上面一行）——插入——表格
（不好使的话先筛选，再插入表格（包含标题列））
2、创建数据透视表：
全选表格（不包括最上面一行）——插入——数据透视表——选择其他工作表的要插入的位置
3、如需删除数据透视表：
选择数据透视表中的一个单元格——分析——选择——整个数据透视表——Delete
3、数据透视表相应处理：
（1）实发薪酬总额（税前总额）：税前应发总额拉入值——下三角号值字段设置——数字格式——小数后两位改为0位
累计总人次：累计姓名或者工作编号
人均月薪：先复制人次数字——实发薪酬总额/人次——小数点后删去
（2）基本工资占比、奖金占比、实发占比：
数字透视表中分析——字段——计算字段——编辑名称、公式（=sum（奖金）/sum(税前总））——添加、确定 
（3）各部门薪酬总额及人次情况：
行是部门——值是税前应发总额和姓名——删除总额后小数点
（4）各职务平均工资与基本工资情况：
行是职务——值是基本工资（平均值）和税前工资（平均值） 
（5）工龄情况分析（饼状图）：
行是工龄——值是姓名——工龄分组：数据分析表中分析——分组选择，步长为3
（6）各出勤率绩效情况（横向二维条形图）：
行是出勤率（分组，0.1步长）——值是绩效得分（平均值）
（7）各职位福利情况：行是职位——值是养老保险、医疗保险、公积金（平均值）
三、看板制作
1、新建工作簿：重命名薪酬看板
2、在薪酬看板中全选单元格，按照RGB填充背景颜色，复制题头背景
3、标题“薪酬看板”：插入文本框——设置字体、大小、颜色，无轮廓
4、表明日期：在其他工作表中添加today函数——在薪酬看板工作表中引用（添加文本框——上方公式框中=——点击要引用的单元格——直接回车）
5、实发薪酬总数：
（1）插入——矩形框——无填充（ctrl+1或右键）
（2）插入文本框：实发薪酬总额（元）
（3）将实发薪酬每个字母拆分：
=MID（实发薪酬总额单元格，COLUMN（第一列的某个单元格），1）
=MID（实发薪酬总额单元格，ROW（第一列的某个单元格），1）
（4）在薪酬看板中插入小矩形块——引用第一个数字单元格——复制单元格——依次更改每个单元格中的引用的单元格
（5）八个小矩形调整：
全选，格式中选择顶端对齐，横向分布
字体（微软雅黑），字号放大，居中
6、累计人次和人均月薪：
（1） 复制文本框修改文字——复制文本框引用统计分析指标中单元格的值（注意不能引用透视表函数，如值没有，需提前复制）
（2）调整文字和数字字号、颜色、填充、边框
7、占比：（环状图）
（1）复制文本框
（2）基本占比、奖金占比、实发占比的值以及辅助列变为百分比格式
（3）对于基本占比：选定基本占比以及辅助列的值——插入——圆环图——复制到薪酬看板工作表中——取消圆环图的图例和标题——对圆环图整体无填充无线框——双击优弧无边框、纯色填充（亮一点的蓝色）——双击劣弧重复上述操作——调整圆环粗度
（4）其他两个圆环图重复以上操作
（5）复制修改文本框添加标题名称
（6）复制文本框到圆环中间——引用相应百分比数字——复制——一起设置字题大小颜色等
8、各部门平均薪酬及人次情况（主面积图；次带点折线图）
（1）选择该数据透视表——插入——面积图
（2）粘贴到薪酬看板中——多余的内容隐藏或删除
（3）选中面积图——设计——更改图标样式——组合图——人数改为带点的折线图，添加次坐标轴，薪酬改为面积图
（4）调整图标样式：删除网格线——无填充
（5）选定折线——无线条——加大标记点（标记，内置）
（6）设定次坐标轴的最大值最小值：选择该坐标从-900开始（选中次坐标轴——坐标轴选项）
（7）修改面积图颜色，变亮一点
（8）将图中的字体颜色设为白色：选中整个图标，选择白色（9）添加图表标题，修改图标边框颜色
9、各职务平均基本工资与平均税前薪酬（主面积图；次带点折线图）
10、员工工龄情况分析（扇形图）
11、各职务平均工资与基本工资情况（三维柱状图）
13、各职位福利情况（三维簇状条形图）
四、插入切片器：
选中一个报表——分析——插入切片器——对切片器右键——报表链接
五、图表类型：
1、柱形图：
一般用于显示一段时间内的数据变化或说明各项之间的比较情况。在柱形图中，一般沿横坐标轴组织类型，沿纵坐标轴组织数值。
2、折线图：
一般用于显示随着时间变化的连续数据，用来反映在相等时间间隔下数据的趋势。通常类别数据沿横坐标轴均匀分布，数值沿垂直轴均匀分布。
3、饼图：
用于显示一个数据系列中各项的大小，以及各项与总和所成的比例。饼图中的数据点显示为整个饼图的百分比
4、条形图：
用于比较多个类别的数值。通常沿纵坐标轴组织类型，沿横坐标轴组织数值。
5、面积图：
强调数值随时间变化的程度，可引起人们对总值趋势的关注。通常显示所绘的值的总和或显示整体与部分间的关系。
6、散点图：
散点图用于显示若干数据系列中各个数值之间的关系。通常用于显示和比较数值。
7、气泡图：
是散点图的变形，能够表示三个变量（x、y、z）之间的关系。利用数据标记气泡的大小来显示第三个变量的大小。气泡图的水平轴和垂直轴都是数据轴。
8、圆环图：
像饼图一样，圆环图显示各个部分与整体之间的关系，但是它可以包含多个数据系列。圆环图的每个圆环分别代表一个数据系列。
9、雷达图：
雷达图显示各数值对应与中心点的变化。在填充雷达图时，由一个数据系列覆盖的区域用同一个颜色来填充。
