1、问题 位置根据百分点参照表统计每个业务员的提成 A1统计各科成绩大于平均分的人数 K1求出所有人最后一次考试成绩的平均分 U1按部门分别统计员工实发工资的平均工资 A21统计各科大于等于本科平均分的分数的平均分 K21分别统计各科男生、女生的平均分 U21汇总指定商品的销售量 A41统计各部门的员工数量 K41分别统计各科男生、女生的总分 U41检查是否存在重复姓名 A61求路南区有一个车库、卧室不少于三间的房地产的平均价格 K61根据支付的利息对银行帐户中的金额求和 A81统计超出销售配额的次数或人员数 K81根据员工信息表制作员工信息动态卡片 U61计算员工年休假的法定休假天数 U81求每
2、人应缴纳的个人所得税 U101找出漏做其工资表的员工 K101查询不同商品在不同商店的卖价（平均单价） A101找出品名为A、型号为的最后一次销售数量 A121根据编号查询出员工的相关信息并置于新表内 K121统计奖金=80且=2000000 0.045 91287.45朱明 1666100 =1500000 0.035 58313.5戴大刚 499999 =1000000 0.025 0冯萍 1874720 =500000 0.015 65615.2李玲兰 976080 =60 张小东 61 =70 王晓杭 62 =80 李立扬 88 =90 钱明明 13 100 程坚强 21 对应规律：叶
3、明梅 72 分数 分数/10-5周学军 98 50 0赵爱君 45 60 1黄永抗 5 70 280 390 4100 5花生peanut 花生peanut 花生peanut提示：按F9键分数会随机变化问题：根据分数和给定标准，评选出等级 解法1 解法2姓名 成绩 等级谢福清 41 (留级) (留级)朱明 90 优 优戴大刚 5 （退学） (退学)冯萍 37 （退学） (退学)李玲兰 100 优 优郭丽丽 86 良 良徐彪 56 差 差王兰 58 标准 等级 差 差陈苇玲 33 =90 优 （退学） (退学)殷华民 17 =80 良 （退学） (退学)刘玉玲 96 =60 中 优 优薛琴 22
4、 =50 差 （退学） (退学)李清 37 =40 （留级） （退学） (退学)6的数字解法3 问题：统计出186开头，尾号为9的手机号码有多少个 解法1手机号码18031502632 2186307921791317053701918085395640131832614341868977856113136797614180582893841809340262713160659084提示：公式中第二个LEFT函数的第二参数使用常量数组，函数结果再与复姓表比较，是本题解题的关键。“+1”是使得公式简洁的妙手。注意凡数组公式皆需“三键结束”提示：与字符串中的字符排列顺序无关，LENB(字符串)-L
5、EN(字符串)=字符串中双字节字符的个数；2*LEN(字符串)-LENB(字符串)=字符串中单字节字符的个数。解法2中，“？”部分是查找开头单字节字符位置的典型方法；“20”可以是任意适当较大数字1864550879918092418481131542699511864587735818055928598提示：手机号码列合并了单元格，但不会影响本题结果。解法2，因为需要使用通配符，所以不能运用FIND函数，只能运用SEARCH函数。公式输入，“三键结束”解法3 问题：根据身份证号码自动判断男女性别 解法1姓名 身份证号景海清 350500198712250010 男许明 35050019860
6、2030037 男王莉莉 543322980809768 女郭晓 350500198211090141 女戴大刚 350500199207120315 男刘茂楷 510322198301041235 男曹家梅 132426590620124 女提示：18位（新身份证）看17位，15位（旧身份证）看15位，奇数为男，偶数为女。因MOD函数的第一参数对数字位数有限制（最高位数约为12或13），所以本例解法1中LEFT函数取17位数字后不能直接取余解法3 问题：含双“-”号的编号，如何去掉后一“-”号及其后面的字符 解法1编号 去尾后编号DDD-F1546-118 DDD-F1546Q-AS23-7
7、9356 Q-AS23MMMM-DH564-VV MMMM-DH564JJ-FK578-9 JJ-FK578FGGG4G-G6-3R FGGG4G-G61-VFBVG-9RW 1-VFBVG提示：以嵌套FIND函数找到第二个“-”的位置后，也可以运用LEFT或MID函数得到同样结果解法3 问题：如何用函数方法强制换行（同一格内，英文放在汉字下面） 解法1水果名称 结果 数值粘贴结果苹果apple 苹果apple 苹果apple电冰箱refrigerator电冰箱refrigerator电冰箱refrigerator提示：本题关键是使用&技巧及FIND函数第一参数运用了数组。MID函数中的12可
8、以更换为任意适当较大数字提示：本题拆分道路名的关键是使用&技巧及FIND函数第一参数运用数组。“道路名”列公式中的数字“5”，是为了避开地址列中唯一一个“多余”的“路”（唐山市路南区）字提示：思路关键是，找到号码首字符的位置。技术关键是，FIND函数第一参数使用常量数组，第二参数使用连字符&。5/19是包含有数字09的一个小数，可替代在式中输入09。99可为任意较大数。梨pear 梨pear 梨pear桃子peach 桃子peach 桃子peach电视机television 电视机television 电视机television花生peanut 花生peanut 花生peanut提示：如果输入
9、公式后没有换行效果，请将单元格格式设置为“自动换行”解法3 问题：根据分数评星级 解法1姓 名 分数 星级杨平 60 =60 张小东 74 =70 王晓杭 72 =80 李立扬 72 =90 钱明明 11 100 程坚强 39 对应规律：叶明梅 14 分数 分数/10-5周学军 14 50 0赵爱君 40 60 1黄永抗 71 70 280 390 4100 5提示：按F9键分数会随机变化解法3 问题：将编码的最后一串字符提取出来 解法1编码 结果FFG-456HB-FB-FB FB6556-BG-KIK-GH GHSFDS-jl jlbhgb-AS-35435H-HMH HMHFFD-L L
10、FGVF-GBNG-454-WQTY WQTY-JNNMN JNNMNGH23 GH23提示：强制换行快捷键alt+enter。强制换行符的数字代码是10。提示：解法2相对复杂，需要用到表中“小知识”，且最后输入公式需要“三键结束”电冰箱refrigerator电冰箱refrigerator电冰箱refrigerator提示：注意公式中后一个“内输入有一个空格。“9”可为适当的任意大数解法3 问题：根据身份证号码自动判断当前年龄(二) 解法1身份证号码 年龄1 年龄2(留级) 130206197009115815 46 47优 130206198107276713 36 36(退学) 1302
11、06197109251136 45 46(退学) 130206198201145314 35 35优 130206198906263257 28 28良 130206195801099895 59 59差 130206195511175462 61 62差 130206197307178486 44 44(退学) 130206198108276221 36 36(退学) 130206198803216255 29 29优 130206197309246323 43 44(退学) 130206201405057343 03 3(退学) 130206201312319888 03 41302062
12、01305044321 04 4130206201305057568 04 4130206201305037568 04 4提示：注意“年龄2”须为常规数字格式。“年龄1”是按天计算，即满366天（闰年天数）算一岁；“年龄2”是按年计算，即跨年即算一岁解法3 问题：如何根据出生日期计算出生肖 解法1姓名 出生日期 生肖金菁心 1981/07/27 生肖对照表 辅助理解列 鸡乌家芬 1957/12/25 1900 鼠 5 鸡王灵红 1982/01/14 1901 牛 6 狗徐珏明 1989/06/26 1902 虎 7 蛇景海清 1958/01/09 1903 兔 8 狗许明 1955/11/1
13、7 1904 龙 9 羊王莉莉 1973/07/17 1905 蛇 10 牛郭晓 1981/08/27 1906 马 11 鸡叶琪 1988/03/21 1907 羊 12 龙刘巧巧 1973/09/24 1908 猴 1 牛曹家梅 2014/05/05 1909 鸡 2 马范丽来 2013/05/04 1910 狗 3 蛇尤佳佳 1912/05/03 1911 猪 4 鼠提示：解法3 问题：自左至右、自上而下，将表列成语排成一单列（多行多列转单列） 解法1欲盖弥彰 胆大包天 下里巴人 不拘一格 单列异想天开 半老徐娘 阳春白雪 春风得意 欲盖弥彰大义凛然 实事求是 晶莹剔透 自强不息 胆大包
14、天胡搅蛮缠 精益求精 楚楚动人 虎踞龙盘 下里巴人看懂下面，本题将迎刃而解 不拘一格转单列后的地址规律 单列 异想天开K K 462 462 欲盖弥彰 半老徐娘L L 462 462 胆大包天 阳春白雪M M 462 462 下里巴人 春风得意N N 462 462 不拘一格 大义凛然提示：按F9键分数会随机变化提示：按F9键可以更新“成绩”。解法2运用了自定义数字格式中的“嵌套条件格式”。解法3中“+1”是为了避免出现函数第一参数自动去尾后成为0提示：解法1，必须先“三键结束”输入数组公式，然后才可以合并单元格。解法2的关键是利用CHOOSE函数，在内存中构造一个工号在首列的列表K K 46
15、3 463 异想天开 实事求是L L 463 463 半老徐娘 晶莹剔透M M 463 463 阳春白雪 自强不息N N 463 463 春风得意 胡搅蛮缠K K 464 464 大义凛然 精益求精L L 464 464 实事求是 楚楚动人M M 464 464 晶莹剔透 虎踞龙盘提示：公式本身并不难理解，关键是“凑数”，有些麻烦，得多试几次解法3 问题：如何将实际数量控制在50500之间 解法1商品名称 单价（元/kg）计划数量（kg）实际数量（kg）实际金额（元）齿轮油 18 674 500 9000汽轮机油 19 360 360 6840液压油 21 652 500 10500传动油 2
16、0 788 500 10000提示：需要好好琢磨一下，否则看似简单的东东，也会把你弄得很郁闷。按F9键可以更新数据提示：红色框内公式须“三键结束”。解法1相对简洁、容易理解。解法2是运用LEN函数避免提取“空值”的方法；解法2中，无须理会F9键刷黑INDIRECT函数时出现的“异常”；此处ROW函数不能直接使用INDIRECT函数中的参数，也显示出INDIRECT函数的重要作用之一。提示：评分规则是除去最低分、最高分，然后计算平均分。解本题依次三道“坎”(本人自己的经历)为：1、没有透彻理解OFFSET函数的表述方式；2、不知道运用既简洁又支持多维引用的SUBTOTAL函数；3、不会运用MMU
17、LT函数将行数值相加，在“死胡同”中郁闷并痛苦解法2 解法3 问题：求出所有人最后一次考试成绩的平均分姓名 施俞芬 郭明仪 祁爱国 李明第1次 48 97 43 75第2次 74 82 54 75第3次 64 84 68 82第4次 70 84 91第5次 5555 84 84 91提示：解法1可命名为“错位区域重叠法”。两区域中“上下重叠”的空格会自动忽略。解法2 解法3 问题：分别统计各科男生、女生的平均分姓名 性别 班级 数学 语文 英语 政治李明 男 高二1班 82 75 87 91苏梦蝶 女 高二1班 52 75 81 64施俞芬 女 高二3班 93 82 79 85周玉芳 女 高二
18、2班 97 91 93 89祁爱国 男 高二4班 84 79 73 50张军强 男 高二4班 92 89 97 90史一宾 男 高二3班 79 82 88 67丁晓玲 女 高二2班 81 77 82 94郭明仪 男 高二1班 71 81 89 63包天亮 男 高二2班 91 90 99 92陈承志 男 高二3班 79 62 50 77男生 83 80 83 76女生 81 81 84 83提示：解法2 解法3 问题：分别统计各科男生、女生的总分姓名 性别 班级 数学 语文 英语 政治李明 男 高二1班 82 75 87 91苏梦蝶 女 高二1班 52 75 81 64施俞芬 女 高二3班 93
19、 82 79 85周玉芳 女 高二2班 97 91 93 89祁爱国 男 高二4班 84 79 73 50张军强 男 高二4班 92 89 97 90史一宾 男 高二3班 79 82 88 67丁晓玲 女 高二2班 81 77 82 94郭明仪 男 高二1班 71 81 89 63包天亮 男 高二2班 91 90 99 92陈承志 男 高二3班 79 62 50 77提示：提示：男生 578 558 583 530女生 323 325 335 332提示：解法2 解法3 问题：根据员工信息表制作员工信息动态卡片编号 姓名 性别 籍贯 年龄 婚否 工资JTG001 杨 平 男 河北唐山 25 已
20、婚 2500JTG002 张小东 男 内蒙包头 45 已婚 3600JTG003 王晓杭 女 山东滨州 36 未婚 3500JTG004 李立扬 男 福建福州 21 已婚 1100JTG005 钱明明 男 广东东莞 19 已婚 920JTG006 程坚强 男 湖北武汉 28 已婚 2400JTG007 叶明梅 女 新疆喀什 32 已婚 2600JTG008 周学军 男 湖南醴陵 26 未婚 2700JTG009 赵爱君 女 贵州遵义 22 已婚 1100编号 JTG002姓名： 张小东 工资： 3600年龄： 45 籍贯： 内蒙包头性别： 男 婚否： 已婚解法1提示：解法2 解法3 问题：计算
21、员工年休假的法定休假天数姓名 性别 工龄（年）杨 平 男 11张小东 男 4王晓杭 女 1李立扬 男 7钱明明 男 5程坚强 男 2附表：法定休假天数工龄（年） 休假天数0 无2 15天5 20天10 30天提示：解法3不依赖于“附表”；解法2依赖于“附表”，但公式最短解法2 解法3 问题：求每人应缴纳的个人所得税工号 姓名 工资 个税020001 李明 ￥12,100020002 苏梦蝶 ￥8,532020003 施俞芬 ￥4,810020004 周玉芳 ￥9,566提示：提示：提示：计数王伟超出苹果汁、牛奶和酱油销售配额的次数；计算有多少销售人员同时超出其苹果汁和牛奶配额；计数李芳和赵军同
22、时超出苹果汁、牛奶和酱油销售配额的次数020005 祁爱国 ￥1,904020006 张军强 ￥5,658020007 史一宾 ￥7,982 附表：020008 丁晓玲 ￥4,523 个税=（工资-3500）*税率-扣除数020009 郭明仪 ￥3,215 应纳税额（元） 税率 扣除数（元）020010 包天亮 ￥5,901 0 3% 0020011 陈承志 ￥2,174 1500 10% 105020012 王平琴 ￥6,139 4500 20% 555020013 刘学鹤 ￥3,969 9000 25% 1005020014 华秋玲 ￥4,125 35000 30% 2755020015
23、邹明明 ￥3,807 55000 35% 5505020016 李芳菲 ￥5,000 80000 45% 13505提示：应纳税额含自身值，0纳税额这个特殊情况用IF函数消除解法2 解法3 问题：统计奖金=80且100的人数姓名 奖金（元）谢福清 150朱明 67戴大刚 87冯萍 125李玲兰 87郭丽丽 143徐彪 85王兰 101陈苇玲 58殷华民 145刘玉玲 75薛琴 69李清 131胡云 102金菁心 98提示：解法2 解法3 问题：自上而下动态求累计数量水果名称 水果数量苹果 50柑桔 40梨 35菠萝 65香蕉 24花生 31核桃 54柠檬 29山竹 18葡萄 41水果 核桃 累
24、计数量解法2解法3提示：提示：因为下表列序与上表不同，所以有些麻烦啦提示：INDEX函数同时返回“暗含”的单元格地址，如上述解法1中，“山竹”的INDEX函数返回“18”的同时，还表示18所在单元格的地址“V151”解法2 解法3 问题：计算员工的退休日期姓名 性别 出生日期 退休日期张平光 男 1985/7/8郭建峰 男 1981/2/5曾国芸 女 1991/12/15王伟 男 1986/10/17钱梅宝 女 1972/4/30张宇 男 1990/11/27沈迪 女 1958/1/12徐飞 女 1961/4/25赵国辉 男 1959/11/13罗劲松 男 1965/7/28提示：退休年龄，男
25、60，女55。解法2的公式比较简洁，应优先采用解法2 解法3 问题：判断所在年份是闰年还是平年年份 平/闰年3 3 19041 1 19084 4 20784 4 20022 2 20534 4 20482 2 20004 4 2010201120122013201420152016提示：解法2中，简写函数DATE(年份,)表示“年份-1”年11月30日。实际上，能够被4整除的年份即为闰年，反之为平年，如解法3解法2 解法3 问题：判断一组数字中各个数字的小数位数是否不小于两位数字1 11 0.231 9.7861 82 9.92 768.9992 21提示：解法3容易理解，也比较简便提示：如果结果显示不正确，请在结果单元格内重新设置单元格数字格式为“常规”。表中员工年龄每年会自动增加提示：解法1，提取月份，然后*10，然后再提取月份，是根据月份求取其季度的精妙数学方法，暂时“死记”此法即可。解法2的原理请参见K201解法1。解法3的原理请参见K201解法2
