VLOOKUP函数之模糊查找,课后练习答案

一、计算员工年休假天数(15分)

NO员工姓名性别工龄(年)用vlookup模糊查找年假天数1刘光明男02张小五女11103陈列男954胡萝卜女155周末女20156高进军男1010二、计算出销售员提成金额（注意工龄不同提成比例不同,25分）

NO销售员工龄年数销售额用vlookup模糊查找提成金额1刘晶晶1 18,798 657.92孙建桃2 35,000 1,750.03王建军3 458,920 45,892.04王京京1 120,000 8,400.05肖慧琴2 99,800 5,988.06杨丽莉4 85,000 7,862.57要淑君1 108,000 7,560.08袁丽琴6 156,920 15,692.0三、个人所得税计算(25分)

NO工号姓名工资个税1NED015向群¥3,8079.212NED016尚红梅¥5,28573.503NED017应克艳¥1,5174NED018唐丽琴¥5,733118.305NED019尹佩佩¥5,840129.006NED020向世妮¥4,91242.367NED021王永艳¥4,16820.048NED022覃艳梅¥1,7209NED023李闪闪¥6,965241.50282 10NED024胡翠群¥8,205386.0011NED025孙来来¥3,5531.5912NED026鲍敬伟¥2,38313NED027李春¥7,500295.0014NED028黄慧敏¥5,31076.0015NED029肖琳¥2,28416NED030强世双¥9,666678.2017NED031贺丽丽¥2,64718NED032刘秀灵¥3,37219NED033黄炳荣¥7,662311.2020NED034李盈盈¥6,506195.6021NED035万艳香¥4,21621.4822NED036黄有丽¥6,387183.7023NED037唐会平¥6,591204.1024NED045石颖¥6,399184.9025NED046陈妹珍¥4,98844.6426NED047钟春¥1,02127NED048谭少琴¥3,21128NED049龙宏梅¥9,332611.4029NED050刘坤香¥3,26130NED051汤年珍¥8,364417.8031NED052徐大梅¥5,16861.8032NED053卢娟¥6,310176.0033NED054唐江荣¥9,593663.6034NED055李祖珍¥8,739492.8035NED056柯尊燕¥3,5882.6436NED057陈思梅¥8,194383.8037NED058匡丁山¥1,50838NED059唐贤于¥9,402625.4039NED060李士伦¥5,853130.3040NED061斯小兵¥6,393184.3041NED062黄冬群¥5,42387.30四、采购物品问题(35分)

NO123456789农产品名称采购量面粉大米大豆白糖猪肉排骨牛肉鸡肉草鱼500800501080521525总用多少钱(不包括回扣) 1,100.00 2,160.00 180.00 49.00 904.00 100.00 84.00 255.00 220.00 5,052.00回扣多少 44.00 86.40 3.60 0.49 18.08 - - 2.04 2.86 157.47=VLOOKUP(D81,单价规则,HLOOKUP(C81,项目符号区,2,0),1)*D81 引入下次课的Hlookup函数, 休年假规则表年限1-9年10-19年20年以上天数5天10天15天年限11020天数51015区域命名\"休假规则\" 提成比例规则

工龄<3年销售额分段点提成比例¥0¥5,000¥10,000¥20,000¥50,000¥100,000¥250,0001.50%3.25%3.50%5.00%6.00%7.00%8.00%区域命名\"三年以下提成\" 个税规则工龄≥3年销售额分段点¥0¥5,000¥10,000¥20,000¥50,000¥100,000提成比例2.00%6.25%7.25%8.25%9.25%10.00%区域命名\"三年以上提成起始数015004500900035000550008000税率速算（％）扣除数3010105205552510053027553555054513505区域命名\"税率规则\" 超市购物规则单价采购回扣农产品名称0-10斤面粉大米小米花生10-50斤50-100斤2.452.94.36.83.84.911.519411717.58.82.42.854.26.63.64.811.3184016.5178.5>=100斤0-10斤2.22.7463.24.510.817381615.8800000000000010-50斤50-100斤0.5%0.5%0.8%1.0%1.0%1.0%0.8%0.8%1.0%0.8%0.8%1.3%2.0%2.0%2.0%2.0%2.0%4.0%2.0%2.0%2.5%2.0%2.0%2.8%大豆白糖猪肉排骨牛肉鸡肉鸭肉草鱼2.503.004.507.004.005.0012204218189原表格转制变换

农产品名称面粉大米小米花生单价采购回扣1010501000105010022.502.452.42.200.5%2.0%4.0%33.002.92.852.700.5%2.0%4.0%44.504.34.2400.8%2.0%4.0%57.006.86.6601.0%2.0%4.0%大豆64.003.83.63.201.0%2.0%4.0%白糖75.004.94.84.501.0%4.0%6.0%规则\" 上提成\" >=100斤4.0%4.0%4.0%4.0%4.0%6.0%5.0%5.0%5.5%5.0%5.0%5.6%区域命名\"项目符号区\" 猪肉81211.511.310.800.8%2.0%5.0%排骨92019181700.8%2.0%5.0%牛肉104241403801.0%2.5%5.5%鸡肉11181716.51600.8%2.0%5.0%鸭肉121817.51715.800.8%2.0%5.0%草鱼1398.88.5801.3%2.8%5.6%区域命名\"单价规则\" 区域命名\"回扣规则\"