1、2018年 9 月二级 MS Excel 题库公式大全 第 1 套 1.3. 在工作表“初三学生档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”、出生日期“年月日”和年龄。其中：身份证号的倒数第 2位用于判断性别，奇数为男性，偶数为女性；身份证号的第 714位代表出生年月日；=IF(MOD(MID(C2,17,1),2)=1,“男 “,“女 “) =MID(C2,7,4)并计算 “总分”、平均分”、“总平均分”、 “排名 =SUM(B3:J3) =AVERAGE(B3:J3) =RANK(K3,$K$3:$K$56,0) =AVERAGE(B3:B6) 第 3 套 3.2.参照“产
2、品基本信息表”所列，运用公式或函数分别在工作表“一季度销售情况表”、“二季度销售情况表”中 ,填入各型号产品对应的单价，并计算各月销售额填入 F列中。 =VLOOKUP(B2,产品基本信息表 !$B$2:$C$21,2,0) =D2*E2 3.3在 “ 产品销售汇总表 ” 中 ,分别计算各型号产品的一、二季度销量、销售额及合计数，填入相应列中。 =SUMIF(一季度销售情况表 !$B$2:$B$44,B2,一季度销售情况表 !$D$2:$D$44) =SUMIF(一季度销售情况表 !$B$2:$B$44,B2,一季度销售情况表 !$F$2:$F$44) =C2+E2 =D2+F2 3.4. 在
3、 “产品销售汇总表”中，在不改变原有数据顺序的情况下，按一二季度销售总额从高到低给出销售额排名，填入 I列相应单元格中。 =RANK.EQ(H2,$H$2:$H$21,0) 第 4 套 4.5. 在合并后的工作表“比较数据”中的数据区域最右边依次增加“人口增长数”和“比重变化”两列，计算这两列的值，并设置合适的格式。其中：人口增长数 =2010年人口数 -2000年人口数；比变化 =2010年比重 -2000年比重。 =B2-D2 =C2-E2 4.7. 在工作薄“统计指标 .xlsx”的工作表“统计数据”中相应的单元格内填入统计结果。=SUM(表 12000年人口数（万人） ) =SUM(表
4、 22010年人口数（万人） ) =SUM(表 3人口增长数 ) （插入 sum 函数，选中第五次普查数据 2000 年人口数（万人） 数据 B2:B34，点击确定，依次同上） 第 5 套 5.3. 在“ 2012级法律”工作表中，利用公式分别计算“总分”、“平均分”、“年级排名”列的值。 =SUM(D3:L3) =AVERAGE(D3:L3) 或 =AVERAGE(M3/9) =RANK(M3,M$3:M$102,0) 5.4. 在 “ 2012级法律 ” 工作表中，利用公式、根据学生的学号、将其他班级的名称填入“班级”列，规则为：学号的第三位为专业代码 、第四位代表班级序号，即 01为“法
5、律一班”， 02为“法律二班”， 03为“法律三班”， 04为“法律四班”。 =IF(MID(B3,3,2)=”01” ， ” 法律一班 IF(MID(B3,3,2)=”02”,” 法律二班 ” ，IF(MID(B3,3,2)=”03”,” 法律三班 ” ， ” 法律四班 ” ) 第 6 套 6.1.在“销售订单”工作表的“图书编号”列中，使用 VLOOKUP函数填充所对应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。 =VLOOKUP(图书名称 ,表 3,2,0) 6.3、 在“ 2013 年图书销售分析”工作表中，统计 2013 年各类图书在每
6、月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。 每月销售量： =SUMIFS(表 1销量（本） ,表 1图书名称 ,A4,表 1日期 ,“=2013-1-1“,表 1日期 ,“=40,单价 *销量（本） *0.93,单价 *销量（本） ) 10.5.根据“订单明细”工作表的“发货地址”列信息，并参考“城市对照”工作表中省市与销售区域的对应关系，计算并填写“订单明细”工作表中每笔订单的“所属区域”。 =VLOOKUP(MID(发货地址 ,1,3),表 3,2,0) 10.6.根据“订单明细”工作表中的销售记录，分别创建名为“北区”、“
7、南区”、“西区”和“东区”的工作表，这 4个工作表中分别统计本销售区域各类图书的累计销售金额，统计格式请参考“ Excel素材 .xIsx“文件中的“统计样例”工作表。将这 4个工作表中的金额设置为带千分位的、保留两位小数的数值格式。 =GETPIVOTDATA(“销售额小计 “,北区 !$A$1,“图书名称 “,“ Access 数据库程序设计 “,“所属区域 “,“北区 “) =GETPIVOTDATA(“销售额小计 “,南区 !$A$1,“图书名称 “,“ Acces数据库程序设计 “,“所属区域 “,“南区 “) =GETPIVOTDATA(“销售额小计 “,西区 !$A$1,“图书名
8、称 “,“ Acce数据库程序设计 “,“所属区域 “,“西区 “) =GETPIVOTDATA(“销售额小计 “,东区 !$A$1,“图书名称 “,“ Access 数据库程序设计 “,“所属区域 “,“东区 “) 10.7.在“统计报告”工作表中，分别根据“统计项目”列的描述，计算并填写所对应的“统计数据”单元 =SUMIFS(表 1销售额小计 ,表 1日期 ,“=2013-1-1“,表 1日期 ,“=2012-1-1“,表 1日期 ,“=2013-7-1“,表 1日期 ,“=2012-1-1“,表 1日期 ,“=2013-1-1“, 表 1 日 期 ,“=2013-1-1“,表 1日期
9、,“80000,K3*45%-13505) 13.6 利用公式计算 “实发工资 “列，公式为：实发工资 =应付工资合计 -扣除社保 -应交个人所得税。 =I3-J3-L3 第 14套 14.4.将工作表“平均单价”中的区域 b3： c7 定义名称为“商品均价”。运用公式计算工作表“销售情况”中 f列的销售情况，要求在公式中通过 vlookup函数自动在工作表“平均单价”中查找相关商品的单价，并在公式中引用所定义的名称“商品均 价”。 =VLOOKUP(D4,商品均价 ,2,0)*E4 第 15套 15.2. 根据图书编号，请在 “订单明细表 “工作表的 “图书名称 “列中，使用 VLOOKUP
10、 函数完成图书名称的自动填充。 “图书名称 “和 “图书编号 “的对应关系在 “编号对照 “工作表中。 =VLOOKUP(图书编号 ,表 2#全部 ,2,0) 15.3. 根据图书编号，请在 “订单明细表 “工作表的 “单价 “列中，使用 VLOOKUP 函数完成图书单价的自动填充。 “单价 “和 “图书编号 “的对应关系在 “编号对照 “工作表中。 =VLOOKUP(图书编号 ,表 2#全部 ,3,0) 15.4. 在 “订单明细表 “工作表的 “小计 “列中，计算每笔订单的销售额。 =单价 *销量（本） 15.5. 根据 “订单明细表 “工作表中的销售数据，统计所有订单的总销售金额，并将其
11、填写在 “统计报告 “工作表的 B3 单元格中。 =SUM(表 3小计 ) 15.6. 根据 “订单明细表 “工作表中的销售数据，统计 MS Office 高级应用图书在 2012 年的总销售额，并将其填写在 “统计报告 “工作表的 B4 单元格中。 =SUMIFS(表 3小计 ,表 3图书名称 ,“ MS Office 高级应用 “,表 3日期 ,“=2012-1-1“,表 3日期 ,“=2011-7-1“,表 3日期 ,“=2011-1-1“,表 3日期 ,“5,” 是 ” ,” 否 ” ) 16.3使用公式统计每个活动地点所在的省份或直辖 市，并将其填写在“地区”列所对应的单元格中，例如
12、“北京市”、“浙江省” =LEFT(C3,3) 16.4依据“费用类别编号”列内容，使用 VLOOKUP 函数，生成“费用类别”列内容，对照关系参考“费用类别”工作表。 =VLOOKUP(E3,费用别类 !$A$3:$B$12,2,FALSE) 16.5在“差旅成本分析报告”工作表 B3 单元格中，统计 2013 年第二季度发生在北京市的差旅费用总金额。 =SUMIFS( 费 用 报 销 管 理 !G3:G401, 费用报销管理 !A3:A401,” =2013-04-01” , 费 用 报 销 管理 !A3:A401,” 60000“)/COUNT(表 1-月份 ) 17.5 在“按月统计”
13、工作表中，分别通过公式计算各月排名第 1、第 2和第 3的销售业绩，并填写在“销售第一名业绩”、“销售第二名业绩”和“销售第三名业绩”所对应的单元格中。要求使用人民币会计专用数据格式，并保留 2位小数 . =LARGE(表 1一月份 ,1) =LARGE(表 1一月份 ,2) E5 : =LARGE(表 1四月份 ,3) =LARGE(表 1一月份 ,3) E6: =LARGE(表 1四月份 ,4) 第 18套 18.5. 在“销售记录”工作表的 E4:E891中，应用函数输入 C列（类型）所对应的产品价格 =VLOOKUP(C4,价格表 !$B$2:$C$5,2,0) 18.6. 在“销售记
14、录”工作表的 F4:F891中，计算每笔订单记录的金额，应用货币格式，计算规则：金额 =价格 *数量 *（ 1-折扣百分比），折扣百分比由订单中的订货数量和产品类型决定 =D4*E4*(1-VLOOKUP(C4，折扣表 !$B$9:$F$11,IF(D4=2014-1-1“,表 1咨询日期 ,“=2014-2-1“,表 1咨询日期 ,“=2014-3-1“,表 1咨询日期 ,“12) 将各项数据填入相应的单元格， 其中统计男女人数时应使用函数并应用已定义的名称，最低笔试分数线按部门统计 =COUNTIFS(名单 !F$4:F$1777,B5,名单 !D$4:D$1777,“女 “) =COUN
15、TIFS(名单 !F$4:F$1777,B5,名单 !D$4:D$1777,“男 “) =D5+E5 =D5/F5 =MIN(IF(名单 !F$4:F$1777=统计分析 !B5,名单 !J$4:J$1777) 对工作表“ 统计分析 ” 设置条件格式，令其只有在单元格非空时才会自动以某一浅色填充偶数行、且自动添加上下边框线。 =mod(row(),2=0) 图标 标题与数据上方第 1 行的标题内容一致并可同步变化 =统计分析 !$B$1 第 24套 24.2.在 “ 成本分析 ” 工作表的单元格区域 F3:F15，使用公式计算不同订货量下的年订货成本，公式为 “ 年订货成本 =（年需求量 /订
16、货量） 单次订货成本 ” ，计算结果应用货币格式并保留整数。 =$C$2/E3*$C$3 24.3.在 “ 成本分析 ” 工作表的单元格区域 G3:G15，使用公式计算不同订货量下的年存储成本，公式为 “ 年存储成本 =单位年存储成本 订货量 0.5” ，计算结果应用货币格式并保留整数。 =$C$4*E3*0.5 24.4.在 “ 成本分析 ” 工作表的单元格区域 H3:H15，使用公式计算不同订货量下的年总成本，公 式为 “ 年总成本 =年订货成本 +年储存成本 ” ，计算结果应用货币格式并保留整数。 =F3+G3 24.7.在工作表 “ 经济订货批量分析 ” 的 C5 单元格计算经济订货批
17、量的值，公式为： ，计算结果保留整数。 =SQRT(2*C2*C3/C4)” 24.8.在工作表 “ 经济订货批量分析 ” 的单元格区域 B7:M27 创建模拟运算表，模拟不同的年需求量和单位年储存成本所对应的不同经济订货批量；其中 C7:M7为年需求量可能的变化值，B8:B27为单位年 储存成本可能的变化值，模拟运算的结果保留整数。 =SQRT(2*C2*C3/C4)” 第 25套 25.2 在 “主要城市降水量 ”工作表中，将 A 列数据中城市名称的汉语拼音删除，并在城市名后面添加文本 “市 ”，如 “北京市 ”； =LEFT(A2,LENB(A2)-LEN(A2)&“市 “ 25.5 在
18、单元格区域 N2:N32 中计算各城市全年的合计降水量，对 其应用实心填充的数据条条件格式，并且不显示数值本身 ； =SUM(降水量统计 1 月 :12 月 ) 25.6 在单元格区域 O2:O32 中，根据 “ 合计降水量 ” 列中的数值进行降序排名 ； =RANK.EQ(N2,$N$2:$N$32,0) 说明： RANK.和 RANK.EQ 函数功能相同。 25.8在 S3单元格中建立公式，使用 Index函数和 Match函数，根据 R3 单元格中的城市名称和 S2 单元格中的月份名称，查询对应的降水量； =INDEX(降水量统计 城市（毫米） :12 月 ,MATCH(R3,降水量统计
19、 城市（毫米） ,0),MATCH(S2,降水量统计 #标题 ,城市（毫米） :12 月 ,0) 第 26 套 26.3. 在“订单明细”工作表中，完成下列任务： 根据 B列中的产品代码，在 C列、 D列和 E列填入相应的产品名称、产品类别和产品单价（对应信息可在“产品信息”工作表中查找）。 =VLOOKUP(B2,产品信息 ,2,0) =VLOOKUP(B2,产品信息 ,3,0) =VLOOKUP(B2,产品信息 ,4,0) 设置 G列单元格格式，折扣为 0的单元格显示“ -”，折扣大于 0的单元格显示为百分比格式，并保留 0位小数（如 15%）。 在 H列中计算每订单的销售金额，公式为“金
20、额 =单价数量 (1-折扣 )”，设置 E列和 H列单元格为货币格式，保留 2位小数。 =E2*F2*(1-G2) 26.4. 在“订单信息”工作表中，完成下列任务： 根据 B列中的客户代码，在 E列和 F列填入相应的发货地区和发货城市（提示：需首先清除 B列中的空格和不可见字符），对应信息可在“客户信息”工作表中查找。 =TRIM(CLEAN(B2) =VLOOKUP(B2,客户信息 ,6,0) =VLOOKUP(B2,客户信息 ,5,0) 在 G列计算每订单的订单金额，该信息可在“订单明细”工作表中查找（注意：一个订单可能包含多个产品），计算结果设置为货币格式，保留 2位小数。 =SUMI
21、F(订单明细 !$A$2:$A$907,订单信息 !A2,订单明细 !$H$2:$H$907) 5. 在“产品类别分析”工作表中，完成下列任务： 在 B2:B9单元格区域计算每类产品的销售总额，设置单元格格式为货币格式，保留2位小数；并按照销售额对表格数据降序排序。 =SUMIF(订单明细 !$D$2:$D$907,A2,订单明细 !$H$2:$H$907) 7. 在“客户信息”工作表中，根据每个客户的销售总额计算其所对应的客户等级（不要改变当前数据的排序），等级评定标准可参考“客户等级”工作表；使用条件格式，将客户等级为 1级 5级的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色
22、 ,背景 1”。 =IFERROR(CHOOSE(INT(SUMIF(订单信息！ $B$2： $B$324,客户信息！ A2,订单信息！ $G$2:$G$342)/5000)+1 =IF(VALUE(LEFT($G2,FIND(“ 级 ” $G2)-1)=80 ，“优良” ,IF(D3=60,“中”，“不及格” ) 14、逻辑“与”函数： AND 条件： 80 = 80 ， C2 = 80 ， C2 80000,K3*45%-13505),2) 。 应交个人所得税。 “ =IF(F2=102,“优秀 “,良好 “,IF(F2=72,“及格 “,IF(F272,“及格 “,“不及格 “)” 9、 SUBTOTAL(109,B4:B11)” . 109=sum, 对 AL31-AL34 求和 . 10、 =IF(MOD(MID(C2,17,1),2)=1,“男 “,“女 “)”输入男女生性别。 11、 TEXT(MID(C2,7,8),“0-00-00“)”出生年月日。 12、 =DATEDIF(-TEXT(MID(C2,7,8),“0-00-00“),TODAY(),“y“)，年龄计算。
