黑色头发挑染:Excel办公实例精粹110讲

1、员工业绩统计表：
1）打开“月度销售表”如下：

2）创建数据透视表；
3）选定区域A2：G11；
4）设置数据透视表显示位置；
5）显示新建数据表，如下图所示：

6）设置行字段：将“客户名称”拖进“行字段”；
7）设置列字段：将“业务员”拖进“列字段”；
8）设置数据项：将“合计”拖进“数据项”；
9）调整表格如下：

10）查看数据透视表。在“客户名称”下拉列表中勾选“明谷电子技术有限公司”，得到表格如下：

11）勾选“全部显示”，双击右下角的单元格，可以将透视表归原到原始表格。
12）创建数据透视图如下图所示：

13）设置数据项：将“合计”拖进“数据项”；
14）设置分类轴：将“客户名称”项拖进“分类字段”；
15）设置图例：将“业务员”项拖进“系列字段”；
16）设置完成的透视图如下图所示。通过“客户名称”和“业务员”下拉列表可以选择要显示的数据。

2、员工销售工作评定表：
1）打开“员工销售工作评定表”如下：

2）输入查询条件：在单元格B16中输入人员名称为“白雪”，在单元格B17中输入商品名称为“CPU”；
3）计算所查询员工的销售量：在单元格B18中输入函数公式“=INDEX(A2:F12,MATCH(B16,A2:A12,0),MATCH(B17,A2:F2,0))”；
4）计算该员工是否完成销售任务：在单元格B18中输入函数公式“=CHOOSE(IF(INDEX(A2:F14,13,MATCH(B17,A2:F2,0))>B18,1,2),"未完成","完成"”；
5) 计算应得多少提成：在单元格B19中输入函数公式“=B18*VLOOKUP(B18,I2:K6,3)”；
6）计算该员工的提成总和：在单元格G10中输入函数公式“=B10*VLOOKUP(B10,$I$2:$K$6,3)+C10*VLOOKUP(C10,$I$2:$K$6,3)+D10*VLOOKUP(D10,$I$2:$K$6,3)+E10*VLOOKUP(E10,$I$2:$K$6,3)+F10*VLOOKUP(F10,$I$2:$K$6,3)”；
7）最后得到白雪的一些相关数据如下图所示：

8）实现查询功能。在“输入人员”对应的单元格B16中输入员工姓名“李洋”，在“商品名称”对应的单元格B17输入“硬盘”，按Enter键即可查询到李洋的一些相关数据。
3、员工学历表：
1）打开“员工学历表”如下：

2）单击工具栏中的“工具>选项”命令，弹出“选项”对话框，切换到“自定义序列”选项卡，输入序列如下图所示，然后单击“添加”按钮，输入的序列即添加到了“自定义序列”列表框中，如下图所示，然后单击“确定”按钮，

3）选中单元格G3，单击菜单栏中的“数据>排序”命令，弹出“排序”对话框，在主要关键字下拉列表中选择“学历”选项，然后单击“选项”按钮，如下图所示：

4）弹出“排序选项”对话框后，在“自定义排序次序”下拉列表中选择刚才自定义的序列，然后单击“确定”按钮，如下图所示：

5）返回“排序”对话框后单击“确定”按钮。
6）返回工作表中，工作表中的数据已经按照前面自定义的顺序进行了排序，单击菜单栏中的“数据>分类汇总”命令，弹出“分类汇总”对话框后，在“分类字段”下拉列表中选择“学历”，汇总方式为“计数”，然后单击“确定”按钮，如下图所示：

7）返回到工作表中，工作表中的“学历”项进行了计数汇总，如下图所示：

8）此时单击“名称框”下面的“”的数字1、2、3，则该汇总表会分别显示第一、二、三级，在第二级后面单击“+”按钮，则显示该学历的员工详细情况，如下图所示，显示的是硕士学历的员工的详细情况。

4、员工培训成绩表：
1）打开“员工培训成绩表”如下：

2）单击菜单栏中的“工具>宏>录制新宏”命令，打开“录制新宏”对话框，如下图所示：

3）在“宏名”文本框中输入“工作表格式1”，在“快捷键”文本框中输入字母“g”，在“说明”文本框中输入宏的说明文字“设置工作表格式，使工作表更美观”，如下图所示：

4）单击“确定”按钮后，弹出“停止录制”工具栏，即开始录制宏。如下图所示，为表格设置格式，设置完成后单击“停止录制”工具栏中的“停止录制”按钮“”；

5）打开录制好宏的“员工培训成绩表”工作薄，此时弹出一个提示对话框，提示需要更改宏的安全性等级，否则将无法使用工作薄中包含的宏，单击“确定”按钮，如下图所示：

6）单击菜单栏中的“工具>宏>安全性”命令，打开“安全性”对话框，切换到“安全级”选项卡，选中“中。您可以选择是否运行可能不安全的宏。”选项，然后单击“确定”按钮；
7）再次打开“员工培训成绩表”工作簿，此时会弹出“安全警告”对话框，单击“启用宏”按钮，打开“员工培训成绩表”工作簿后，切换到“专案部”工作表中，单击菜单栏中的“工具>宏>宏”命令，打开“宏”对话框；
8）弹出“宏”对话框，选择录制好的宏“工作表格式”，然后单击“执行”按钮，如下图所示：

9）此时宏将“专案部”工作表自动格式化，执行宏的效果如下图所示。

5、员工考核成绩报告表：在下表的D4单元格中输入函数公式：“=COUNTIF(C3:C16,">60")”可以计算出“及格人数”；在F5单元格中输入函数公式：“=COUNTIF(C3:C16,">85")”可以计算出“优秀人数”；在F7单元格中输入函数公式：“=SUMIF(C3:C16,">85")/F5”可以计算出“优秀平均分”；在F8单元格中输入函数公式：“=SUMIF(C3:C16,">60")/F4”可以计算出“及格平均分”。

6、员工业务能力查询表：
1）打开“员工业务能力查询表”如下：

2）选中单元格D3，输入公式“=CHOOSE(IF(C3<90,1,IF(C3>=95,3,2)),"一般","好","优秀")”；
3）输入公式确定无误后，按Enter键后显示该员工业务能力为“一般”；
4）拖动鼠标，将单元格D3中的公式向下填充至单元格D21；
5）在单元格G3中输入“=LOOKUP(G2,A3:B21)”；在单元格G4中输入“=LOOKUP(G2,A3:C21)”；在单元格G5中输入“=LOOKUP(G2,A3:D21)”；公式输入完成后，返回的都是错误值“#N/A”，这是因为单元格G2中没有输入信息的结果，如下图所示：

6）查询员工信息：在单元格G2中输入员工工号“266”，按Enter键即可自动返回员工“景艳”的信息，如下图所示：

7、公司组织结构图：
1）首先创建一个空白表格，取消网格线，然后输入表格标题“公司组织结构图”，设置标题格式；
2）选择图示类型。单击菜单栏中的“插入>图示”命令，弹出“图示库”对话框后，单击“组织结构图”选项，然后单击“确定”按钮，如下图所示：

3）插入组织结构图。单击“确定”按钮后，即在工作表中插入组织结构图，同时工作表中还弹出“组织结构图”工具栏；
4）给第一级插入助手。在“组织结构图”工具栏上单击“插入形状”按钮，然后从其下拉列表中单击“助手”选项，连续两次添加助手的效果如下图所示，在其中输入助手名称“行政助理”和“业务助理”；

5）输入各下属的名称。例如“销售部经理”、 “行政部经理”、 “开发部经理”，右击其中任意一个编辑框，从弹出的快捷菜单中选择“下属”命令继续添加经理的下属；
6）添加销售部经理的下属。如下图所示给“销售部经理”添加下属，并依次输入名称“秘书”、“文员”、“销售人员”；

7）添加所有经理的下属。给所有经理添加下属。
8）编辑自选图形格式。用鼠标左键拖动选中工作表中的所有编辑框后鼠标右击，在弹出的快捷菜单中单击“设置自选图形格式”命令，弹出“设置自选图形格式”对话框后，切换到“颜色与线条”选项卡中，单击“颜色”文本框旁的下三角按钮，在弹出的列表框中单击“填充效果”命令，设置填充效果如下图所示：

9）设置完填充效果后，单击“确定”按钮，然后切换到“字体”选项卡，设置字体格式，然后单击“确定”按钮；
10）完成上述操作后返回到工作表中，可以看到公司组织结构图的最后效果如下所示。

8、企业生产成本的分析透视图
1）打开表格如下：

2）启动数据透视表和数据透视图向导；
3）选择要建立数据透视表的数据源区域如下图所示：

4）选择数据透视表显示的位置；
5）设置数据透视表的布局：单击“布局”按钮，将“成本项目”拖至“页”设置区域，将“历史先进水平年2004年”、“上年实际平均”和“本年累计实际平均”拖至数据区域内，双击并将其汇总方式改为“求和”，如下图所示：

6）查看生成的数据透视表如下图所示：

7）更换汇总和数据的位置：单击“数据”所在的A3单元格不放，然后将其拖至“汇总”所在的B3单元格，效果如下图所示：

8）选择成本项目；
9）查看“直接人工”的汇总情况如下图所示：

10）查看创建的数据透视图如下图所示：

11）重新选择图表类型为折线图；
12）查看生成的折线图会发现无任何折线；
13）显示折线：单击“成本项目”右侧的下三角按钮，从弹出的下拉列表中选择“产品生产成本选项”，然后单击“数据”按钮，按住不放拖至“汇总”字样上，然后释放鼠标，此时的数据透视图如下图所示：

14）下面为折线添加趋势线；
15）选择趋势线类型为“趋势预测/回归分析类型”选项组中单击“线性”选项；
16）设置趋势前推周期为“1”周期，如下图所示：

17）查看添加趋势线后的效果图如下：

18）隐藏单元格：切换至“总体产品生成成本表”中，选中第6行的所有数据，然后右击这些选中的数据，从弹出的快捷菜单中单击“隐藏”命令，如下图所示：

19）打开“图表类型”，设置数据区域为$C$5:$C$9单元格区域，设置“分类标志”的值为总体生产成本表中的$A$5：$A$9单元格区域；
20）设置数据标志，勾选“类别名称”和“百分比”复选框，最后生成的效果图如下图所示：

21)如下图所示选择图表生成位置，最后单击“完成”生成最后图表。