（1）将工作表命名为“工资表”（略）；选择H3，输入公式“=sum(E3:J3)”，并填充至H16。
（2）在V2、V3单元格中分别输入“男”和“女”，将V列隐藏（也可以部隐藏），选择D3，单击【数据】菜单下的【有效性】命令，在“允许”框中选择“序列”，单击“来源”框右侧的“折叠”按钮，选择V2:V3，单击【确定】按钮，再利用填充柄将D3填充至D16。
（3）选择I3单元格，输入公式“=round(IF(H3<=1300,0.02*H3,0.05*H3),2)”，再利用填充柄填充至I16。
（4）选择K3单元格，输入公式“=H3-I3-J3”，再利用填充柄填充至K16。 （5）选择E17单元格，输入公式“=SUM(E3:E16)”，再利用填充柄填充至K17。
（6）略 （7）略 （8）略
（9）在E18单元格中输入公式“=E17/$H$17”，再利用填充柄填充至G17，并设置相应单元格的格式为“百分比”；选择E2:G2，E18:G18单元格区域，仿照上题中的（12）建立图形。
（10）先按“科室”排序，再单击【数据】/【分类汇总】，在弹出的【分类汇总】对话框中，选定汇总项为“实发工资”“应发工资”后，按【确定】按钮。
（11）重点是掌握方法。选择A:J列，右击鼠标，选择【设置单元格格式】命令，弹出对话框，选择“保护”选项卡，清除“锁定”；单击【工具】菜单下的【保护】子菜单中的【保护工作表】命令，弹出【保护工作表】对话框，清除“选定锁定单元格”复选框，输入密码，单击【确定】按钮。
（12）选择A3单元格，单击【窗口】菜单下的【冻结窗格】命令。 （13）略 3．略
5
实验二 Excel函数、公式、数据管理和分析
一、实验目的
1．熟练掌握Excel公式的使用。
2．熟练掌握Excel中常用函数的功能和语法格式。
3．掌握数据的排序、筛选、分类汇总、合并计算和数据透视表的建立步骤。 4．掌握数据分析工具的使用。 二、实验内容
1．在Excel默认状态下建立工作表如表2-3所示，按照如下步骤操作，请写出相应的结果。
表2-3 工作表
1 2 3 4 5 6 7 A 2 B 5 10 C 1 2 3 D （1）在上述Excel表中，选中A1单元格，按下“Ctrl”键的同时拖动A1单元格右下角的填充柄到A6，则A2．A3被填充的数据分别为 、 。
（2）选择B1:B2单元格区域，拖动B2单元格右下角的填充柄到B6 ，则B3．B4被填充的数据分别为 、 。
（3）选中C3单元格， 拖动C3单元格右下角的填充柄到C4， 则C4被填充的数据为 。
（4）在D1单元格中输入公式―=$A1+C$2‖，然后按回车键，并将公式复制到D2、D3单元格中，则D1、D2、D3单元格中的数据分别为 、 、 。
（5）选中C5单元格，单击工具栏上的∑，然后按回车，则该单元格的值为 。
（6）先将A1:B2单元格区域命名为―求和‖，A7单元格中有公式―=SUM（求和）‖，此时A7单元格的值为 ；若重新将―求和‖指定为B1:C2单元格区域的名字，则A7单元格的值为 。
2.上机练习如下函数 （1）SUM函数
6
某企业往来单位余额对比表如表2-4所示，根据具体问题求和。
表2-4 某企业往来单位余额对比表
1 2 3 4 5 6 7 8 9 10 往来单位 单位-1 单位-2 单位-3 单位-4 单位-5 单位-6 单位-7 合计 A B 往来单位余额对比表 2011年 20000 30000 25000 96000 78000 100000 96000 2012年 12000 25000 14000 36000 85000 120000 85900 C ①计算2012年往来单位余额总和。
②计算―单位-1‖、―单位-2‖、―单位-5‖和―单位-7‖4个单位的2012年往来单位余额总和。
（2）SUMIF函数
某企业商品销售明细表如表2-5所示，根据要求求和。
表2-5 某企业商品销售明细表 1 2 3 4 5 6 7 8 9 10
A1 000A20 B1 B1 000A20 000A34E A1 000A1 A 商品销售明细表 商品名称 销售金额 1,200.00 2,500.00 1,400.00 10,000.00 2,000.00 1,000.00 3,000.00 100.00 B ①根据B列销售金额，对销售金额大于2000的数值进行求和。
②根据商品名称对商品名称为A1的商品相对应的销售金额进行求和。 ③计算商品名称包含―A‖的销售金额之和
④计算商品名称中第四五个字符为―A2‖且字符总长度为6的销售金额之和
7
（3）COUNTIF函数
某企业员工工资表如表2-6所示，根据要求计数。
表2-6某企业员工工资表
部门 财务部 财务部 销售部 人事部 销售部 财务部
姓名 张景生 杨会来 李琴 原林 吴凤莲 张飞 基本工资 1500 2000 3000 1200 2300 2100 提成工资 800 500 200 400 600 100 实发工资 2300 2500 3200 1600 2900 2200 ①统计实发工资大于2500的人数 ②统计财务部的人数
（4）平均函数——AVERAGE函数
某企业员工的工资表如表2-7所示，要求：求出所有员工总工资的平均值；
表2-7某企业员工的工资表
编号 2001 2002 2003 2004 2005 姓名 李碧华 林淑芬 王喜育 吕利萍 姚启明 性别 女 女 女 女 男 职务 行政 行政 财务 财务 业务 基本工资 1200 1200 1500 2400 1000 效绩工资 400 200 400 800 2200 总工资 1600 1400 1900 3200 3200
（5）DAVERAGE函数 资料如上题（表2-7），要求：求出职务是―财务‖的员工总工资的平均值。 （6）IF函数
某企业销售提成计算表如表2-8所示，C列显示的数据是根据B列的销售额进行判断的结果，如果―销售额‖大于30000，―提成额‖按―销售额*0.015‖计算，否则按―销售额*0.001‖计算。要求：用IF函数公式计算每个人的提成额
表2-8
姓名 张三 李四 王五 孙六 李七
销售额 25000 36000 30000 45000 21000 提成额 250 540 300 675 210 （7）VLOOKUP函数
某公司个人所得税计算表如表2-9所示，请求出每位员工应税所得额对应的税率、速算扣除数及交纳的税金。
8
表2-9个人所得税计算表
姓名 张雨 刘平 李正 王华 应税所得额 3700 2500 500 6500 对应对应速算税率 扣除数 10% 10% 3% 20% 105 105 0 555 税金 265 145 15 745 级数 1 2 3 4 5 6 7 应税所得 0 1500 4500 9000 35000 55000 80000 且不超过 1500 4500 9000 35000 55000 80000 税率 3% 10% 20% 25% 30% 35% 45% 速算扣除数 0 105 555 1005 2755 5505 13505 （8）MINVERSE和TRANSPOSE函数
?213???要求：求矩阵412的逆矩阵和转置矩阵。 ????223??（9）MMULT函数
?213??368?????要求：求矩阵412和5122的乘积。 ??????223????497??
3．函数F(X)=5X+8，利用模拟运算表的行引用和列引用两种方式分别计算X取值为-5、-4、-3、-2、-1、0、1、2、3、4、5时的F(X)的值。
4．利用模拟运算表编制三角形九九乘法口诀表。 5．某公司2011年7月份的销售数据如表2-10所示
表2-10某公司2011年7月份的销售数据
日期 2011-7-1 2011-7-3 2011-7-3 2011-7-8 2011-7-10 2011-7-13 2011-7-18 2011-7-18 2011-7-19 2011-7-20 2011-7-24 2011-7-24 2011-7-31 商品 彩电 空调 彩电 冰箱 电脑 电脑 冰箱 空调 空调 彩电 电脑 空调 冰箱 单价（元） 数量（台） 金额（元） 销售人员 4500 8500 1600 1700 6800 8600 2500 2300 2200 3800 8600 2600 220 2800 10 60 80 40 10 20 100 80 100 120 20 40 500 70 9
地区 济南 济南 济南 青岛 青岛 青岛 烟台 烟台 烟台 日照 北京 北京 北京 北京 45000 510000 128000 68000 68000 172000 250000 184000 220000 456000 172000 104000 110000 196000 张三 张三 张三 王五 王五 王五 赵六 赵六 赵六 周七 李四 李四 李四 李四 2011-7-28 电风扇