Excel公式.docVIP

Excel公式详解 企业销售产品清单 1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。 数据--有效性 使用VLOOKUP函数， 对Sheet1中的“3月份销售统计表”的“产品名称”列和“产品单价”列进行填充。（查找与引用） 产品名称=VLOOKUP(F3,$A$3:$C$10,2) 产品单价=VLOOKUP(F3,$A$3:$C$10,3) 使用数组公式，计算Sheet1中的“3月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。计算方法：销售金额 = 产品单价 * 销售数量 ={H3:H44*I3:I44} 4. 使用统计函数，根据“3月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。 =SUMIF($K$3:$K$44,N3,$L$3:$L$44) 5. 在Sheet1中，使用RANK函数，在“分部销售业绩统计”表中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。 =RANK(O3,$O$3:$O$5） 6. 将sheet1中的“三月份销售统计表”复制到Sheet2中（选择性黏贴），对Sheet2进行高级筛选。 筛选条件为：“销售数量”－3、“所属部门”－市场1部、“销售金额”-1000 将筛选结果保存在Sheet2中。 7. 根据Sheet1的“3月份销售统计表”中的数据，新建一个数据透视图Chart1。要求： 该图形显示每位经办人的总销售额情况； x坐标设置为“经办人”； 数据区域设置为“销售金额”； 求和项为销售金额； 将对应的数据透视表保存在Sheet3中。 采购情况表 1.在Sheet1中，使用条件格式将“瓦数”列中数据小于100的单元格中字体颜色设置 为红色、加粗显示。 格式--条件格式 2. 使用数组公式，计算Sheet1“采购情况表”中的每种产品的采购总额，将结果保 存到表中的“采购总额”列中。 计算方法为：采购总额=单价*每盒数量*采购盒数 ={E3:E18*F3:F18*G3:G18} 3.根据Sheet1中的“采购情况表”，使用数据库函数及已设置的条件区域，计算以下 情况的结果。 计算：商标为上海，寿命小于100的白炽灯的平均单价，并将结果填入Sheet1的G25 单元格中，保留小数2位； =DAVERAGE(A2:H18,E2,J4:L5) 表格右边有条件 计算：产品为白炽灯，其瓦数大于等于80且小于等于100的品种数，并将结果填入 Sheet1的G26单元格中。 =DCOUNT(A2:H18,,J9:L10) 表格右边有条件 4. 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。 对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。根据调查情况，制做出 Sheet2中的“吸烟情况调查表”。使用函数，统计符合以下条件的数值。统计函数 统计未登记的部门个数，将结果保存在B14单元格中；=COUNTBLANK(B3:E12) 统计函数 统计在登记的部门中，吸烟的部门个数，将结果保存在B15单元格中。=COUNTIF(B3:E12,B3) 5.使用函数，对Sheet2中的B21单元格中的内容进行判断，判断其是否为文本，如果 是，单元格填充为“TRUE”；如果不是，单元格填充为“FALSE”，并将结果保 存在Sheet2中的B22单元格当中。 =ISTEXT(B21) 信息函数 6. 将Sheet1中的“采购情况表”复制到Sheet3中，对Sheet3进行高级筛选。 筛选条件：“产品为白炽灯，商标为上海”；将结果保存在Sheet3中。选择性黏贴 7.根据Sheet1中的“采购情况表”，在Sheet4中创建一张数据透视表。要求： 行区域设置为“产品”；列区域设置为“商标”；数据区域为“采购盒数”； 求和项为“采购盒数”。 公司员工人事信息表 1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。 数据--有效性 使用大小写转换函数，根据Sheet1中“公司员工人事信息表”的“编号”列，对“新编号”列进行填充。“a001”更改后为“A001”。 =PROPER(A3)文本函数 3. 使用文本函数和时间函数，根据Sheet1中“公司员工人事信息表”的“身份证号码”列，计算用户的年龄，并保存在“年龄”列中。注意： 身份证的第7位~第10位表示出生年份； 计算方法：年龄=当前年份-出生年份。其中“当