excel函数习题 excel函数练习 excel常用函数

问题索引 问题 位置 涉及函数 根据百分点参照 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 统计每个业务员的提成 A1 IF 统计各科成绩大于平均分的人数 K1 SUMAVERAGE 求出所有人最后一次考试成绩的平均分 U1 AVERAGEIF/OFFSETCOUNTAAVERAGE 按部门分别统计员工实发工资的平均工资 A21 AVERAGEIF 统计各科大于等于本科平均分的分数的平均分 K21 AVERAGEIFAVERAGE 分别统计各科男生、女生的平均分 U21 AVERAGEIF 汇总指定商品的销售量 A41 SUMIF 统计各部门的员工数量 K41 COUNTIF 分别统计各科男生、女生的总分 U41 SUMIF 检查是否存在重复姓名 A61 IFCOUNTIF/COUNTIFSUMCOUNTA 求路南区有一个车库、卧室不少于三间的房地产的平均价格 K61 AVERAGEIFS 根据支付的利息对银行帐户中的金额求和 A81 SUMIFS 统计超出销售配额的次数或人员数 K81 COUNTIFS 根据员工信息表制作员工信息动态卡片 U61 VLOOKUPIFSUM 计算员工年休假的法定休假天数 U81 IF/VLOOKUP 求每人应缴纳的个人所得税 U101 VLOOKUPIF 找出漏做其工资表的员工 K101 VLOOKUPISNAIF 查询不同商品在不同商店的卖价（平均单价） A101 VLOOKUPIF 找出品名为A、型号为Ⅱ的最后一次销售数量 A121 LOOKUP 根据编号查询出员工的相关信息并置于新表内 K121 MATCHVLOOKUP 统计奖金>=80且<100的人数 U121 SUMPRODUCT/COUNTIFSUM 根据星期和节次查询学习科目 A141 INDEXMATCHIF 根据姓名或科目汇总出总成绩 K141 MATCHINDEXSUM/SUMOFFSETMATCH 自上而下动态求累计数量 U141 MATCHINDEXSUM/SUM/N 求出女工的总工资 A161 SUM/SUMPRODUCT/SUMIFS 计算员工的年龄 K161 YEARTODAY 计算员工的退休日期 U161 IFDATEYEARMONTHDAY 制作倒计时显示器 A181 TODAYDAYEOMONTHYEAR /DATEYEAR 根据年月日求出其所对应的季度（一） K181 MONTH/INTMONTH/ROUNDUPMONTH 根据年月日求出其所对应的季度（二） K221 CEILINGMONTH/LENPOWERMONTH 判断所在年份是闰年还是平年 U181 IFDAYEOMONTH/IFDAYEOMONTHDATE/IFMOD 利用自然数，生成新的循环有序序列数 A201 MOD 利用自然数，生成新的重复有序序列数 K201 INT/ROUNDUP 判断一组数字中各个数字的小数位数是否不小于两位 U201 IFFLOOR 一批货要装箱,按商品名称分别计算所需箱子数量（各商品不足一箱的剩余数量不需要装箱） A221 FLOOR/ROUNDDOWN 关押一批罪犯，各个犯罪类型分别需要多少间囚室(每种类型仅允许一间囚室可以不满员) U221 CEILING/ROUNDUP 如何快速连接一行内单元格中的单个数字 A241 SUM/SUMPOWER 制作工资发放备钞张数计算表 K241 INTSUM 将姓名改为尊称姓+先生或女士 U241 LEFTIF 将含有复姓的姓名改为尊称姓+先生或女士 A261 LEFTORIF 将给出数字拆分为个位数再求和 K261 MIDIFSUM/MIDSUM 判断学生考试分数的等级 U261 IF/MID 将号码+汉字的信息数据分离 A281 LEFTRIGHTLENBLEN/SEARCHBMIDB 将第二个Q后面的内容提取出来 K281 FINDMID 统计得票次数 U281 FINDCOUNT 将特殊语句里的主语与描述语分离 A301 FINDMINLEFTMID 统计出186开头，尾号为9的手机号码有多少个 K301 LEFTRIGHTSUM/SEARCHCOUNT 有几组数字，在句子中的位置各不相同，如何提取 U301 MIDBSEARCHBLENLENB 如何将城市、地区、街道和门牌从地址中拆分出来 A321 LEFTFINDMIDMIN 根据身份证号码自动判断男女性别 K321 LEFTRIGHTMODIF/MIDMODIF 根据身份证号码自动判断当前年龄（一） U321 YEARTODAYNOWLEFTIFLENMID 根据身份证号码自动判断当前年龄（二） K421 TODAYTEXTMID/TODAYYEARTEXTMID 号码前信息既有字母，又有汉字，还有字母+汉字，如何提取号码 A341 FINDMINMID 含双“-”号的编号，如何去掉后一“-”号及其后面的字符 K341 FINDREPLACE 如何求出位于一个单元格内各个名称（以“、”号分隔）的总数 U341 LENSUBSTITUTE 如何用函数方法去掉强制换行符 A361 CHARSUBSTITUTE 如何用函数方法强制换行（同一格内，英文放在汉字下面） K361 REPLACELENBLENCHAR 如何将汉字后面的英文两端加上“（）”号并置于汉字下方 U361 REPLACELENBLENCHAR 给首字符是大写的单词标记符号“√” A381 IFEXACTLEFTUPPER/IFANDROWCODELEFT 根据分数评星级 K381 REPTIF 将编号中第一个“-”之前的字母替换为“K” U381 REPLACEFINDREPT 根据分数评星级（要求：不满五颗“★”时，以“☆”补齐） A401 IFREPTINT 将编码的最后一串字符提取出来 K401 TRIMRIGHTSUBSTITUTEREPT 将盈亏金额按显示要求改写 U401 TEXT 根据分数和给定 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 ，评选出等级 A421 IF/TEXT/CHOOSE 公司发票制作（局部） U421 IFORLEFTRIGHTCOLUMNSUMTEXT 当“工号”在最后一列时，如何根据工号返回“姓名”、“分数”、“等级” A441 VLOOKUPIF/VLOOKUPCHOOSECOLUMNROW 如何根据出生日期计算出生肖 K441 CHOOSEMODYEAR 如何使主表中点选框内容随附表改变并且点选框尺寸大小亦自动适应 U441 INDIRECTCOUNTA 如何对文本字符串中的单数字求和 A461 SUMTEXTMIDROW/SUMTEXTMIDROWINDIRECTLEN 自左至右、自上而下，将表列成语排成一单列（多行多列转单列） K461 INDIRECTCHARMODINTROW 如何将单列成语按图示重新排布（单列转多行多列） U461 INDIRECTCOLUMNROW 如何用一条公式做出最终评分的最高分 A481 MAXMMULTSUBTOTALOFFSETROW 如何将实际数量控制在50~500之间 K481 MINMAXIF 如何求出2014年5月的最大值和最小值 U481 MAXMINIF 汇总各月份、各品牌的销量 AE1 SUM/SUMIFS 将一组成绩分数排名（美式排名和中式排名） AE21 RANK/COUNTIFSUM 按评分规则及权重计算各部门的考评得分 AE41 SUMIFSUM 员工工龄的计算 AE61 DATEDIFIF 计算无色单元格的个数、黄色单元格内数值和、有色单元格内数值平均值 AE81 GET.CELLNSUMSUMIFCOUNTA 分别计算出黑体、斜体、带下划线字符的个数 AE101 GET.CELLCOUNTIF 根据等级和等级分列表给出得分 AE121 VLOOKUP/HLOOKUPTRANSPOSE 根据部门提取员工信息 AE141 SMALLIFROWINDEX 根据不同的部门组合，得到相应员工信息 AE161 IFROWSMALLINDEX/INDEXSMALLIFMMULTNTRANSPOSEROW 计算出大于某个分数的最低分和小于某个分数的最高分 AE181 LARGESMALLCOUNTIFIFSUMN 根据评委打出的分数，计算每个歌手的平均得分 AE201 SUBTOTALSUM/SUMMAXMIN 计算各个工资区段上的人数 AE221 FREQUENCY/COUNTIF 看看数据表中有多少个不相同的数值 AE241 FREQUENCYCOUNT 统计出不重复的姓名个数 AE261 MATCHFREQUENCYCOUNT 分别求出个人总分及学科总分 AE281 MMULT/SUM 统计出三车间女职工的工资总和 AE301 SUM/SUMPRODUCTN/MMULTTRANSPOSE 分别统计出不同业务员、不同销售产品的销售金额合计（如表所示） AE321 MMULTTRANSPOSEN 求第一次考试的总分（N+IF用法） AE341 SUMINDEXNIFMATCHROW 将指定品名的数量求和（T+IF用法） AE361 SUMVLOOKUPTIF 求出各科成绩的最高分 AE381 MAX/MAXINDEXMATCH 计算出每个人的综合评分 AE401 MMULTSUMIF 根据姓名查询出其销售业务最好的那个季度(如数值相同，以先出现的为准) AE421 MATCHMAXINDEX 根据书的类别，查询出所对应的全部书名 AE441 OFFSETMATCHCOUNTIF 工资至少有两个月都不到1000的人数是多少 AE461 SUMCOUNTIF/SUMCOUNTIFOFFSETROW 求出个人的最高平均工资 AE481 AVERAGEMAX/MAXSUBTOTALOFFSETROW 将编号和日期是无序排列的数量统计表变为编号唯一且按自然天数展示的数量统计表 AO1 SUMIFINDEXMATCH 用一条公式将无序排列的数组按行升序变为行升序数组 AY21 SMALLOFFSETROWCOLUMN 用一条公式将无序排列的数组按行升序变为行升序数组（难！） AO21 RIGHTSMALLROWCOLUMN 将单列升序字母重排为三组二维升序字母 AO41 LOOKUPROWCOLUMN 单列升序字母重新排布的习题 AO61 LOOKUPROW 把单元格区域内的数字转变为对应的数值(有附加题.难！) AO81 MMULTROW/NTRANSPOSEMMULTROW/COUNTIF 把表列7位数按行按数字展开 AO101 MIDCOLUMN 把绿框表列10位数按数字升序不重复排列（难！） AO121 TEXTSMALLIFISERRFINDROWCOLUMN 把绿框表列10位数中没有出现的数字按升序不重复排列（难！） AO141 TEXTSMALLIFISERRFINDROWCOLUMN 离散数列组合为二维数组的练习题(解法1) AO161 CHOOSETRANSPOSEROW 离散数列组合为二维数组的练习题(解法2)（难！） AO181 IFROW 间隔离散数列组合为二维数组的练习题（难！） A0201 IF 四列间隔离散数列组合为二维数组的练习题 AO301 TEXT 含有不同字符数的间隔离散数列组合为二维数组的练习题（解法1）（难！） AO221 MIDFINDSUBSTITUTETRANSPOSEROW 含有不同字符数的间隔离散数列组合为二维数组的练习题（解法2） AO241 INDEXTRANSPOSEROW 含有不同字符数的间隔离散数列组合为二维数组的练习题（解法3） AO261 CHOOSETRANSPOSEROW 如何使“AO282”显示出“123” AO281 INDIRECTCHARCODE 将黄色示意的列字符组插入到绿色示意的列字符组中 AO321 IFROWLOOKUP 将黄色示意的列字符组置于绿色示意的列字符组下方 AO341 IFROWLOOKUP 将第一列的数字，只取前两位，余下的设置为0 AO361 MID/TRUNCLEN/RIGHTLEN/REPLACEREPTLEN/LEFTLEN 通过合并数字（0-9）数组元素直观地学习MMULT函数的基本规则 AO381 MMULTROW问题 问题：根据百分点参照表统计每个业务员的提成。（提成百分点参照右表） 解法1 解法2 解法3 问题：统计各科成绩大于平均分的人数 解法1 解法2 解法3 问题：求出所有人最后一次考试成绩的平均分 解法1 解法2 解法3 问题：汇总各月份、各品牌的销量 解法1 解法2 解法3 问题：将编号和日期是无序排列的数量统计表变为编号唯一且按自然天数展示的数量统计表 解法 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 姓名 销售金额￥ 提成 临界点 百分比 提成 姓名 语文 数学 英语 自然 社会 总分 姓名 施俞芬 郭明仪 祁爱国 李明 78.5 月份 品牌 销量 品牌月份 索尼 夏普 松下 编号 日期 数量 谢福清 2028610 >=2000000 0.045 91287.45 宋辉 126 121 107 195 96 645 第1次 48 97 43 75 1 索尼 6 夏普 松下 AA133DL0013 2014-5-7 1000 朱明 1666100 >=1500000 0.035 58313.5 范丽来 125 137 92 190 97 641 第2次 74 82 54 75 1 夏普 13 1 13 13 14 解法1 AA133DL0013 2014-5-15 2000 戴大刚 499999 >=1000000 0.025 0 蔡荣 129 135 98 191 87 640 第3次 64 84 68 82 1 松下 14 2 0 9 0 AA133DL0013 2014-5-9 3000 冯萍 1874720 >=500000 0.015 65615.2 尤佳佳 117 134 102 189 95 637 第4次 70 84 91 1 索尼 7 3 15 12 18 AA133WO1044 2014-5-14 4000 李玲兰 976080 <500000 0 14641.2 章大寅 116 134 100 186 88 624 第5次 55 2 夏普 9 4 15 15 7 AA133WO1029 2014-5-8 5000 郭丽丽 1281600 32040 戴红 119 114 110 182 91 616 3 松下 7 5 0 9 12 AA133WO1032 2014-5-9 6000 徐彪 1109800 27745 翁震 125 133 103 172 90 607 3 索尼 15 AA133WO1042 2014-5-10 7000 王兰 996800 14952 韩峰 130 113 96 177 83 599 3 夏普 12 AA133DL0016 2014-5-12 8000 陈苇玲 459700 0 3 松下 11 品牌月份 索尼 夏普 松下 殷华民 1801800 63063 4 索尼 5 夏普 松下 2014-5-7 2014-5-8 2014-5-9 2014-5-10 2014-5-11 2014-5-12 2014-5-13 2014-5-14 2014-5-15 刘玉玲 2691140 121101.3 4 夏普 15 1 13 13 14 解法2 AA133DL0013 1000 0 3000 0 0 0 0 0 2000 薛琴 1826980 63944.3 4 松下 7 2 0 9 0 AA133DL0016 0 0 0 0 0 8000 0 0 0 李清 1589510 55632.85 4 索尼 10 3 15 12 18 AA133WO1029 0 5000 0 0 0 0 0 0 0 胡云 500000 7500 5 夏普 9 4 15 15 7 AA133WO1032 0 0 6000 0 0 0 0 0 0 金菁心 2580740 116133.3 解法2 5 松下 12 5 0 9 12 AA133WO1042 0 0 0 7000 0 0 0 0 0 乌家芬 1443100 36077.5 5 5 4 5 4 4 解法1 55 84 84 91 78.5 AA133WO1044 0 0 0 0 0 0 0 4000 0 提示： 提示： 提示：解法1可命名为“错位区域重叠法”。两区域中“上下重叠”的空格会自动忽略。 提示：按F9键可更新销量。解法1，红色框内编辑好公式后，注意要“三键结束”，然后右拉、下拉复制公式；还要注意单元格“引用”类型，否则复制后公式会出错。解法2，先选中AI13：AK17区域，然后再编辑“多条件求和”公式，注意数组的行、列排布；三键结束 提示：本题的前提是，源表的“编号+日期”无重复，否则结果错误。在红色框内编辑数组公式，三键结束；下拉、右拉。可以自定义格式后隐藏0值。本题根据本人解答网友问题改编 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 问题：按部门分别统计员工实发工资的平均工资 解法1 解法2 解法3 问题：统计各科大于等于本科平均分的分数的平均分 解法1 解法2 解法3 问题：分别统计各科男生、女生的平均分 解法1 解法2 解法3 问题：将一组成绩分数排名（美式排名和中式排名） 解法1 解法2 解法3 问题：用一条公式将无序排列的数组按行升序变为行升序数组（难！） 解法1 解法2 解法3 问题：用一条公式将无序排列的数组按行升序变为行升序数组 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 姓名 部门 基本工资 奖金 实发工资 辅助列 平均工资 学号 姓名 班级 数学 语文 英语 政治 姓名 性别 班级 数学 语文 英语 政治 姓名 分数 美式排名 中式排名 2 3 4 7 2 2 3 2 3 4 7 2 2 3 薛琴 企划 800 224 1024 部门 020001 李明 高二1班 82 75 87 91 李明 男 高二1班 82 75 87 91 杨平 100 1 1 3 4 2 2 6 3 2 3 4 2 2 6 3 2 刘惠民 销售 600 243 843 企划 921.92 020002 苏梦蝶 高二1班 52 75 81 64 苏梦蝶 女 高二1班 52 75 81 64 张小东 99 2 2 5 5 8 2 3 8 2 5 5 8 2 3 8 2 李宁宁 销售 600 143 743 销售 784.31 020003 施俞芬 高二3班 93 82 79 85 施俞芬 女 高二3班 93 82 79 85 王晓杭 99 2 2 5 6 8 9 4 5 6 5 6 8 9 4 5 6 张鑫 销售 600 163 763 生产 824.56 020004 周玉芳 高二2班 97 91 93 89 周玉芳 女 高二2班 97 91 93 89 李立扬 98 4 3 5 6 2 5 7 2 5 5 6 2 5 7 2 5 路程 销售 650 138.25 788.25 设计 823.25 020005 祁爱国 高二4班 84 79 73 50 祁爱国 男 高二4班 84 79 73 50 钱明明 97 5 4 3 8 1 4 9 3 5 3 8 1 4 9 3 5 陈丽 企划 800 144 944 020006 张军强 高二4班 92 89 97 90 张军强 男 高二4班 92 89 97 90 程坚强 97 5 4 1 9 4 4 5 4 1 1 9 4 4 5 4 1 高兴 生产 750 108.75 858.75 020007 史一宾 高二3班 79 82 88 67 史一宾 男 高二3班 79 82 88 67 叶明梅 97 5 4 1 9 4 4 5 1 4 1 9 4 4 5 1 4 王陈 生产 600 163 763 020008 丁晓玲 高二2班 81 77 82 94 丁晓玲 女 高二2班 81 77 82 94 周学军 95 8 5 陈岚 生产 700 233.5 933.5 020009 郭明仪 高二1班 71 81 89 63 郭明仪 男 高二1班 71 81 89 63 赵爱君 91 9 6 2 2 2 3 3 4 7 2 2 2 3 3 4 7 周嫒 生产 600 143 743 020010 包天亮 高二2班 91 90 99 92 包天亮 男 高二2班 91 90 99 92 2 2 2 3 3 4 6 2 2 2 3 3 4 6 王国强 设计 600 163 763 020011 陈承志 高二3班 79 62 50 77 陈承志 男 高二3班 79 62 50 77 2 2 3 5 5 8 8 2 2 3 5 5 8 8 刘倩如 企划 550 247.75 797.75 90 86 92 90 解法1 男生 83 80 83 76 解法1 4 5 5 6 6 8 9 4 5 5 6 6 8 9 陈雪如 设计 600 143 743 女生 81 81 84 83 2 2 5 5 5 6 7 2 2 5 5 5 6 7 蔡荣 设计 500 172.5 672.5 1 3 3 4 5 8 9 1 3 3 4 5 8 9 尤佳佳 设计 900 214.5 1114.5 1 1 4 4 4 5 9 1 1 4 4 4 5 9 1 1 4 4 4 5 9 1 1 4 4 4 5 9 提示： 提示： 提示： 提示：本题重点是红色框内公式的编辑。中式排名的数学方法有些费解，但使用F9键逐层分析，也可以理解 提示：选中红色框区域，编辑数组公式。红色标示出了公式中的理解难点：=--RIGHT(SMALL(ROW(AO22:AU29)*1000+AO22:AU29,(COLUMN(AO22:AU29)-40)+(ROW(AO22:AU29)-22)*7),3) 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 问题：汇总指定商品的销售量 解法1 解法2 解法3 问题：统计各部门的员工数量 解法1 解法2 解法3 问题：分别统计各科男生、女生的总分 解法1 解法2 解法3 问题：按评分规则及权重计算各部门的考评得分 解法1 解法2 解法3 问题：将单列升序字母重排为三组二维升序字母 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 员工号 商品 月销售量 指定商品 咖啡 姓名 部门 部门 姓名 性别 班级 数学 语文 英语 政治 项目部门 服务态度 办事效率 创新活动 得分 A A B C D E F A0101 绿茶 1940 销售汇总 2510 刘惠民 企划 企划 3 李明 男 高二1班 82 75 87 91 0.5 0.3 0.2 B G H I J K L B0101 可乐 400 李宁宁 销售 销售 3 苏梦蝶 女 高二1班 52 75 81 64 生产部 优 差 优 8.2 C A0102 果汁 1000 张鑫 销售 生产 5 施俞芬 女 高二3班 93 82 79 85 人事部 优 良 良 9 D A B C D B0101 咖啡 1300 路程 销售 设计 4 周玉芳 女 高二2班 97 91 93 89 财务部 优 良 良 9 E E F G H B0102 果汁 1100 陈丽 生产 祁爱国 男 高二4班 84 79 73 50 后勤部 中 优 优 8 F I J K L A0102 果汁 740 高兴 企划 张军强 男 高二4班 92 89 97 90 管理部 中 中 良 6.4 G A0103 果汁 1000 王陈 生产 史一宾 男 高二3班 79 82 88 67 H A B C A0101 绿茶 1430 陈岚 生产 丁晓玲 女 高二2班 81 77 82 94 I D E F A0102 咖啡 1210 周嫒 生产 郭明仪 男 高二1班 71 81 89 63 评分规则 分数 J G H I 王国强 生产 包天亮 男 高二2班 91 90 99 92 优 10 K J K L 刘倩如 设计 陈承志 男 高二3班 79 62 50 77 良 8 L 陈雪如 企划 男生 578 558 583 530 解法1 中 6 斯海凤 设计 女生 323 325 335 332 差 4 刘茂楷 设计 注：得分=0.5*服务态度得分+0.3*办事效率得分+0.2*创新活动得分 杜盼琳 设计 提示： 提示： 提示： 提示：“三键结束”红色框内公式，下拉复制公式即完成。本题的关键是，对SUMIF函数要理解透彻、对数组运算规则要烂熟于心（好像电脑内存中的数组运算在你脑海里一样） 提示：选中红色框区域，编辑数组公式。本题关键是分别将第一参数“凑数”为42~53的两行六列、三行四列、四行三列的三个数组 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 问题：检查是否存在重复姓名 解法1 解法2 解法3 问题：求路南区有一个车库、卧室不少于三间的房地产的平均价格 解法1 解法2 解法3 问题：根据员工信息表制作员工信息动态卡片 解法1 解法2 解法3 问题：员工工龄的计算 解法1 解法2 解法3 问题：单列升序字母重新排布的习题 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 姓名 性别 身份证号 类型 价格 区/镇 卧室数 是否有车库？ 编号 姓名 性别 籍贯 年龄 婚否 工资 姓名 入职日期 工龄 题目 倒叙 间隔取数 奇偶并列 朱明 女 76332625119704.00000000000000 重复 重复应舍去 2 舒适 230000 丰南 3 否 JTG001 杨平 男 河北唐山 25 已婚 2500 谢福清 6/7/80 37年2个月27天 A L A A B 戴大刚 男 71658288966133.00000000000000 平房 197000 路南 2 是 JTG002 张小东 男 内蒙包头 45 已婚 3600 朱明 1/2/87 30年8个月1天 B K C C D 冯萍 男 81576293686085.00000000000000 凉爽型 345678 路南 4 是 JTG003 王晓杭 女 山东滨州 36 未婚 3500 戴大刚 1/14/14 3年7个月20天 C J E E F 李玲兰 男 39770024032540.00000000000000 豪华级 321900 丰南 2 是 JTG004 李立扬 男 福建福州 21 已婚 1100 冯萍 6/28/75 42年2个月6天 D I G G H 郭丽丽 女 8490408399752.00000000000000 高级建筑 450000 路南 5 是 JTG005 钱明明 男 广东东莞 19 已婚 920 李玲兰 1/2/95 22年8个月1天 E H I I J 朱明 女 76332625119704.00000000000000 重复 普通别墅 395000 路南 4 否 JTG006 程坚强 男 湖北武汉 28 已婚 2400 郭丽丽 2/14/87 30年6个月20天 F G K K L 王兰 女 94460793764393.00000000000000 397839 JTG007 叶明梅 女 新疆喀什 32 已婚 2600 徐彪 10/10/86 30年10个月24天 G F 陈苇玲 女 82214450150472.00000000000000 JTG008 周学军 男 湖南醴陵 26 未婚 2700 王兰 11/12/84 32年9个月22天 H E 殷华民 男 61214920479723.00000000000000 重复 重复应舍去 JTG009 赵爱君 女 贵州遵义 22 已婚 1100 陈苇玲 5/12/14 3年3个月22天 I D 刘玉玲 女 50571092309493.00000000000000 殷华民 5/13/14 3年3个月21天 J C 薛琴 女 34263810653884.00000000000000 编号 JTG002 刘玉玲 12/19/79 37年8个月15天 K B 殷华民 男 61214920479723.00000000000000 重复 薛琴 5/12/85 32年3个月22天 L A 胡云 女 11985577063946.00000000000000 姓名： 张小东 工资： 3600 胡国华 5/12/13 4年3个月22天 金菁心 女 82689845178834.00000000000000 年龄： 45 籍贯： 内蒙包头 乌家芬 男 32360260456130.00000000000000 性别： 男 婚否： 已婚 解法1 提示：解法2处理的比较完美，其技术关键是通过解除对A63的绝对引用得到了动态条件区域。解法3只是指出有几个重复姓名（此处SUM函数结合"1/"方法算出不重复的姓名个数） 提示： 提示： 提示：此题还有更好的解法，有待补入 提示：运用LOOKUP函数，分别在红框、绿框、紫框区域内编辑数组公式。关键是构造出一个第一参数的数组，实际上，这在考验你玩弄数字的能力，函数本身并不难理解 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 问题：根据支付的利息对银行帐户中的金额求和 解法1 解法2 解法3 问题：统计超出销售配额的次数或人员数 解法1 解法2 解法3 问题：计算员工年休假的法定休假天数 解法1 解法2 解法3 问题：计算无色单元格的个数、黄色单元格内数值和、有色单元格内数值平均值 解法1 解法2 解法3 问题：把单元格区域内的数字转变为对应的数值(有附加题.难！) 解法1 附加题解 附加题另解 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 总计 帐户1 帐户2 帐户3 帐户4 销售人员 超出苹果汁配额 超出牛奶配额 超出酱油配额 姓名 性别 工龄（年） 姓名 销售业绩 辅助列 2 2 2 3 3 4 7 2223347 1 1 金额（人民币） 100 390 8321 500 王伟 是 否 否 杨平 男 11 30天 30天 30天 范丽来 7986 6 无色格数 4 2 2 2 3 3 4 6 2223346 1 1 支付利息(2000) 1% 0.50% 3% 4% 赵军 是 是 否 张小东 男 4 15天 15天 15天 蔡荣 3993 0 黄格数值和 24432 24432 2 2 3 5 5 8 8 2235588 1 1 支付利息(2001) 1% 1.30% 2.10% 2% 张颖 是 是 是 王晓杭 女 1 无 无 无 尤佳佳 8660 10 色格平均值 7691 4 5 5 6 6 8 9 4556689 1 1 支付利息(2002) 0.50% 3% 1% 4% 李芳 否 是 是 李立扬 男 7 20天 20天 20天 章大寅 2949 0 2 2 5 5 5 6 7 2255567 1 1 ① 500 钱明明 男 5 20天 20天 20天 戴红 3365 0 1 3 3 4 5 8 9 1334589 1 1 ② 8711 ① 1 程坚强 男 2 15天 15天 15天 翁震 9128 10 1 1 4 4 4 5 9 1144459 2 2 ② 2 韩峰 3689 0 1 1 4 4 4 5 9 1144459 2 2 ③ 1 沈卫 9605 10 朱燕 7254 6 附表：法定休假天数 余峰 5192 6 工龄（年） 休假天数 戴锦民 9701 10 0 无 邹爱慧 4000 6 2 15天 5 20天 10 30天 提示：①2000年利息高于3%以及2001年利息高于或等于2%的每个银行帐户的总额；②2002年利息介于1%到3%之间以及2001年利息高于1%的每个银行帐户的总额 提示：①计数王伟超出苹果汁、牛奶和酱油销售配额的次数；②计算有多少销售人员同时超出其苹果汁和牛奶配额；③计数李芳和赵军同时超出苹果汁、牛奶和酱油销售配额的次数 提示：解法3不依赖于“附表”；解法2依赖于“附表”，但公式最短 提示：无色，2000~3999，黄色，4000~7999，绿色，8000~9999。此题关键是先完成“辅助列”。此题存疑：销售业绩栏的数字不能设置为随机生成，底色也不能设置为条件格式，否则GET.CELL函数失效（即不同颜色都返回同一个数字） 提示：要求，选中红色框区域，输入一个多单元格数组公式来实现题目的要求。构造出第二参数中10的指数“(7-ROW(1:7))”，是本题的要点、难点。附加题解是根据红色框内已有数值，计算其出现的次数，要求运用数组公式，一条公式完成。 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 问题：查询不同商品在不同商店的卖价（平均单价） 解法1 解法2 解法3 问题：找出漏做其工资表的员工 解法1 解法2 解法3 问题：求每人应缴纳的个人所得税 解法1 解法2 解法3 问题：分别计算出黑体、斜体、带下划线字符的个数 解法1 解法2 解法3 问题：把表列7位数按行按数字展开 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 商品 商店 平均单价 工号 姓名 部门 工号 姓名 工资 个税 字符： 2347223 电视机 超商1 5000 商品 商店 平均单价 SC001 王玉花 生产部 020001 李明 ￥12,100 ￥1,165.00 D 5 P 得 发 6 84 3422632 电视机 百货1 6500 计算器 超商2 55 RS001 赵爱国 人事部 020002 苏梦蝶 ￥8,532 ￥451.40 C X 9 A 好 K 狗 5582382 电视机 超商2 5100 CW001 陆海长 财务部 漏做 020003 施俞芬 ￥4,810 ￥39.30 L 啊 H 后 b 框 K 5689456 电视机 百货2 7000 SC002 霍冬明 生产部 工资表 020004 周玉芳 ￥9,566 ￥658.20 答案： 5625725 电脑 超商1 8000 SC003 贾紫云 生产部 工号 姓名 部门 工资 020005 祁爱国 ￥1,904 ￥0.00 FALSE FALSE TRUE TRUE TRUE TRUE FALSE 3814935 电脑 百货1 7900 XS001 林永进 销售部 SC001 王玉花 生产部 1200 020006 张军强 ￥5,658 ￥110.80 TRUE FALSE FALSE FALSE FALSE FALSE FALSE 1944541 电脑 超商2 4500 GL001 周华良 管理部 RS001 赵爱国 人事部 1000 漏做 020007 史一宾 ￥7,982 附表： ￥343.20 FALSE TRUE FALSE TRUE FALSE TRUE FALSE 1944514 电脑 百货2 6000 SC004 江东南 生产部 SC002 霍冬明 生产部 1500 020008 丁晓玲 ￥4,523 个税=（工资-3500）*税率-扣除数 ￥30.69 黑体字符数 8 计算器 超商1 50 SC005 唐家宝 生产部 SC003 贾紫云 生产部 800 020009 郭明仪 ￥3,215 应纳税额（元） 税率 扣除数（元） ￥0.00 FALSE FALSE FALSE FALSE FALSE FALSE TRUE 2 3 4 7 2 2 3 计算器 百货1 60 IT001 黄越河 IT部 XS001 林永进 销售部 900 020010 包天亮 ￥5,901 0 3% 0 ￥135.10 FALSE TRUE TRUE FALSE FALSE FALSE TRUE 3 4 2 2 6 3 2 计算器 超商2 55 SC006 朱家梁 生产部 SC004 江东南 生产部 750 漏做 020011 陈承志 ￥2,174 1500 10% 105 ￥0.00 TRUE FALSE FALSE FALSE FALSE FALSE TRUE 5 5 8 2 3 8 2 计算器 百货2 80 XZ001 高峰青 行政部 SC005 唐家宝 生产部 1200 020012 王平琴 ￥6,139 4500 20% 555 ￥158.90 斜体字符数 6 5 6 8 9 4 5 6 移动硬盘 超商1 1200 IT001 黄越河 IT部 1050 020013 刘学鹤 ￥3,969 9000 25% 1005 ￥14.07 TRUE TRUE FALSE FALSE FALSE FALSE FALSE 5 6 2 5 7 2 5 移动硬盘 百货1 3120 XZ001 高峰青 行政部 1800 020014 华秋玲 ￥4,125 35000 30% 2755 ￥18.75 FALSE FALSE FALSE FALSE TRUE FALSE FALSE 3 8 1 4 9 3 5 移动硬盘 超商2 810 020015 邹明明 ￥3,807 55000 35% 5505 ￥9.21 FALSE FALSE TRUE FALSE TRUE FALSE FALSE 1 9 4 4 5 4 1 移动硬盘 百货2 400 020016 李芳菲 ￥5,000 80000 45% 13505 ￥45.00 下划线字符 5 1 9 4 4 5 1 4 提示：此例是VLOOKUP函数结合IF函数实现多条件查询。局限：只能返回一列的值，不能返回多列值 提示： 提示：应纳税额含自身值，0纳税额这个特殊情况用IF函数消除 提示：首先定义GET.CELL函数的名称（加粗代码20,斜体代码21,下划线代码22），编辑红色框内公式，右拉、下拉；然后在绿色框内运用SUMIF函数 提示：要求，选中红色框区域，然后编辑一个多单元格数组公式 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 提示： 问题：找出品名为A、型号为Ⅱ的最后一次销售数量 解法1 解法2 解法3 问题：根据编号查询出员工的相关信息并置于新表内 解法1 解法2 解法3 问题：统计奖金>=80且<100的人数 解法1 解法2 解法3 问题：根据等级和等级分列表给出得分 解法1 解法2 解法3 问题：把绿框表列10位数按数字升序不重复排列（难！） 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 问题： 解法1 解法2 解法3 品名 型号 数量 编号 姓名 性别 学历 工资 部门 出生日期 姓名 奖金（元） 4 4 姓名 等级 得分 等级分列表 1234567890 A Ⅱ 85 zy01 谢福清 男 高中 800 人事部 1/12/58 谢福清 150 李明 A++ 等级 得分 10 10 0422343632 A Ⅲ 965 zy02 朱明 男 大学本科 1000 企划部 2/15/61 朱明 67 苏梦蝶 A+ A++ 10 9 9 5581212382 B Ⅴ 53 zy03 戴大刚 男 大学本科 1200 企划部 11/3/59 戴大刚 87 施俞芬 B++ A+ 9 7 7 5400600700 A Ⅱ 36 zy04 冯萍 女 硕士 1500 设计部 5/11/60 冯萍 125 周玉芳 A A 8 8 8 0005625725 A Ⅱ 185 zy05 李玲兰 女 高中 900 人事部 8/29/85 李玲兰 87 祁爱国 D B++ 7 1 1 C Ⅶ 9 zy06 郭丽丽 女 大学本科 1100 设计部 5/23/78 郭丽丽 143 张军强 C++ B+ 6 4 4 4444444444 185 185 徐彪 85 史一宾 B+ B 5 6 6 0110051400 王兰 101 丁晓玲 B++ C++ 4 7 7 编号 zy02 陈苇玲 58 郭明仪 A+ C+ 3 9 9 0 1 2 3 4 5 6 7 8 9 殷华民 145 包天亮 A++ C 2 10 10 0 2 3 4 6 姓名 学历 性别 部门 工资 出生日期 刘玉玲 75 陈承志 A D 1 8 8 1 2 3 5 8 朱明 大学本科 男 企划部 1000 2/15/61 解法1 薛琴 69 王平琴 B++ 7 7 0 4 5 6 7 李清 131 刘学鹤 C++ 4 4 0 2 5 6 7 胡云 102 华秋玲 C++ 4 4 金菁心 98 邹明明 C+ 3 3 4 李芳菲 B+ 6 6 0 1 4 5 提示：解法2说明lookup函数是会忽略错误值的。解法2中的查找值0可以省略，也可以是任何不小于0的数值 提示：因为下表列