Excel函数和公式练习

一、求和函数（SUM、SUMIF）编号职务基本工资三八节003033厂长4,000.00-004071统计794.009.00010411作业员423.00-010413作业员427.008.00011092作业员399.008.00011229作业员429.008.00015047作业员314.008.00015155作业员237.00-011572作业员117.00-合计:7,140.0041.00非连续性单元格求和（SUM）10,067.00依条件逻辑判断求和（SUMIF）4,304.00二、对小数进行真正的四舍五入（ROUND）原多位小数取两位小数取四位小数数据一0.123456数据二0.2345670.230.2346合计值0.3580230.230.2346说明：简单的公式计算，常常会产生尾数差，而利用ROUND函数取值后，会将无限小数截为有限小数，从而避免尾差题目：函数格式ROUND(原小数对应地址，取小数位数)全面奖伙食补贴应发工资300.004,300.00660.001,463.00440.00863.00330.00765.00350.00757.00380.00817.00230.00552.00172.00409.0024.00141.002,586.00300.0010,067.001、求工资总额，值放在F13单元格中2、求“作业员”的工资总额，值放在F13单元格无限小数对无限取数对计算值取数0.6666666670.4285714290.4285714290.15641.0952380950.4285714290.1564说明：简单的公式计算，常常会产生尾数差，而利用ROUND函数取值后，会将无限小数截为有限小数，从而避免尾差1、对B18取两位小数，值放在C18单元格2、对B18取四位小数，值放在D18单元格3、对E18取九位小数，值放在F18单元格4、求E18的2/3，保留4位小数，值放在G18单元格违纪扣款实发工资-4,300.00-1,463.00-863.00-765.00-757.00-817.00-552.00-409.00-141.000.0010,067.00一、根据A列判断B列显示（AND，且）2501、判断当A1大于100且小于500时，值为真，值放在B2单元格0FALSE600FALSE求和2、判断B2、B3、B4的值同是为真时，值才为真，否则为假，值放在B5单元格一、根据A列判断B列显示（OR，或）2001、判断当A8小于0或大于500时，值为真，值放在B8单元格-100TRUE1000TRUE求和2、判断B8、B9、B10中有一个值为真，则值为真，值放在B11单元格三、条件判断函数（IF）职务基本工资三八节普通合计空值显示条件判定统计794.009.00803.00803正常1、判断E16的值，如果是空值则在F16单元格显示空值，如果不是空值，则在F16单元格显示正常作业员---并用填充柄填充F17-F19单元格作业员427.008.00435.00435作业员-作业员429.008.00437.00437合计1,650.0025.001,675.001675正常四、多层条件嵌套应用（IF）应发工资适用扣率实得工资实发工资-200工资异常#VALUE!020.00%025020.00%20080020.00%64090025.00%675150025.00%1125200035.00%13001、计算实发工资，规则为如果：工资0，显示异常；0=工资=800，扣率为20%；800工资=1500，扣率为25%；工资1500，扣率为35%五、根据条件判断相同值单元格数量（COUNTIF）低工资数31、如果扣率在20%以下的，判定为低工资，求总人数，值放在C34单元格六、销售业绩表列题综合练习原始数据如下：条件函数综合运用如下表：订单号订单金额销售人员200504015,000.00JARRY销售人员订单数订单总额200504024,500.00HELENANNIE2005040325,000.00MICHAELJARRY426,780.00200504044,200.00JARRYHELEN355,500.00200504054,500.00ANNIEMICHAEL2120,000.00200504062,500.00ANNIE订单总计9202,280.002005040715,000.00JARRY2005040895,000.00MICHAEL200504091,000.00HELEN说明：2005041050,000.00HELEN1、订单数：根据C列第40行至51行判断统计销售人员为ANNIE的订单数，值放F41单元格；200504112,580.00JARRY2、订单总额：在清单列C40至C51中检查销售人员为ANNIE的订单总额，统计求和，放在G41单元格；200504123,200.00ANNIE销售总额212,480.00查看书上AND函数的用法2、判断B2、B3、B4的值同是为真时，值才为真，否则为假，值放在B5单元格查看书上OR函数的用法2、判断B8、B9、B10中有一个值为真，则值为真，值放在B11单元格1、判断E16的值，如果是空值则在F16单元格显示空值，如果不是空值，则在F16单元格显示正常并用填充柄填充F17-F19单元格查看书上IF函数的用法跟我们前天讲的对成绩进行分段的是一样的1、计算实发工资，规则为如果：工资0，显示异常；0=工资=800，扣率为20%；800工资=1500，扣率为25%；工资1500，扣率为35%计算结果应该跟实得工资一样查看COUNTIF函数的用法销售奖金-2,678.008,325.00#########29,003.001、订单数：根据C列第40行至51行判断统计销售人员为ANNIE的订单数，值放F41单元格；2、订单总额：在清单列C40至C51中检查销售人员为ANNIE的订单总额，统计求和，放在G41单元格；一、查找函数（HLOOKUP、VLOOKUP）存货编码规格数量单价一单价二存货编码名称规格单价5671602592820,061#N/A0.036556701027前盖9990.188556701055928A-410.15320.153256701055前盖928A0.1532567010569292,2510.24970.249756701056前盖9290.249756738015966-100.07470.074756701112前盖999A0.1885567010669984,721#N/A0.249756701190前盖D20040.19835670403592832,291#N/A0.038256703011后盖998M0.171556703011998M26,7830.17150.171556703103后盖9990.030756708001998M3,7300.03650.036556704012电池盖998M0.036356704012998M4,8390.03630.036356704034电池盖9990.03825670102799935,4780.18850.188556705048透镜9290.046156703015928-35#N/A0.171556705147透镜D20040.137556704034999-110.03820.038256706152标牌AT-780.048456703103999-80.03070.030756708001灯镜998M0.036556701112999A-540.18850.188556716051装饰板D20040.260156706152AT-781,7380.04840.048456723004放音键D20040.059256724004快进键D20040.059256738015喇叭网9660.074710011002100310041005100610071008张三李四王五马六牛七羊八狗九小二10001006100811001122100210031133#N/A羊八小二#N/A#N/A李四王五#N/A说明：1、函数HLOOKUP用于上下行关系的查询，而VLOOKUP用于左右列关系的查询；2、被查询的对照组参数必须位于欲查询数据的右侧（或下行），即相对位置有要求，否则会出错；3、函数公式中参数TRUE表示模糊查询（如上表中的单价二），而FALSE为精确查询（如上表中的单价一）。公式组求得数据参照组已知数据参照组已知数据值对比组欲求数值公查看黄色单元格对于的地址栏中公式的用法，熟悉2、被查询的对照组参数必须位于欲查询数据的右侧（或下行），即相对位置有要求，否则会出错；3、函数公式中参数TRUE表示模糊查询（如上表中的单价二），而FALSE为精确查询（如上表中的单价一）。一、取出文本中部分字符函数（LEFT，RIGHT，MID）原文本内容取左编码从中提取取右文字56176003(光油)56176007(开油水)56176013(洗枪水)56176014(耐醇油漆)题目：1、用取左函数取左边8位字符，放在取左编码列2、用MID函数从第五位起取中间的6位字符，放在从中提取列3、用取右函数取右边6位字符放在取右文字列二、利用函数组合运用提取文本中的内容（LEN，获取字符串长度/个数）原文本内容提取文本内容获取文本长度得到预计结果56176003(光油)光油)#VALUE!56176007(开油水)开油水)4开油水56176013(洗枪水)洗枪水)4洗枪水56176014(耐醇油漆)耐醇油漆)5耐醇油漆题目：1、LEN函数可用来检测字符中所包含的个数，结合LEFT、RIGHT或MID函数，能进字符串提取的操作，用len函数计算B13单元格获取文本长度，放在C13单元格三、文本组合函数（CONCATENATE）文本一文本二文本三组合文本中国人民解放军中国人民解放军56176007(开油水)OK56176007(开油水)OK123456789999123456789999有四个‘9’说明：1、要将不同单元格的文本组合到一个单元格中可用CONCATENATE函数；2、要将一个单元格中的文本拆分到数个单元格中，可将LEFT、RIGHT、MID、LEN综合运用达到效果。综合运用公式光油开油水洗枪水耐醇油漆1、LEN函数可用来检测字符中所包含的个数，结合LEFT、RIGHT或MID函数，能进字符串提取的操作，用len函数计算B13单元格获取文本长度，放在C13单元格熟悉黄色部分CONCATENATE函数的用法，单击黄色部分单元格，查看地址栏中公式的用法2、要将一个单元格中的文本拆分到数个单元格中，可将LEFT、RIGHT、MID、LEN综合运用达到效果。熟悉黄色部分CONCATENATE函数的用法，单击黄色部分单元格，查看地址栏中公式的用法一、提取系统当前的时间值（NOW、TODAY）1、提取时间：2020/3/2412:422、提取系统日期：2020/3/243、部分时间提取：完整提取方法一方法二提取年份2020/3/2412:4220202020提取月份2020/3/2412:4233提取日期2020/3/2412:422424提取小时2020/3/2412:421212提取分钟2020/3/2412:424242提取秒钟2020/3/2412:425353点击黄色单元格，查看地址栏中的公式，熟悉年月日的提前公式点击黄色单元格，查看地址栏中的公式，熟悉年月日的提前公式一，判断公式结果出错时的相应处理（ISERROR）数值一数值二公式求值函数处理结果451.251.251042442.442.4022#DIV/0!非法公式12000