新手入职训练:EXCEL公式与函数处理数据

第 1章新手学数据处理必会知识与技巧 1.1数据计算中的单元格有哪些引用形式 .................................................... 2 1.1.1区分相对应用、绝对应用与混合应用 .............................................................. 2 1.1.2跨工作表的单元格引用方法 ............................................................................. 4 1.1.3跨工作簿的单元格引用方法 ............................................................................. 4 1.2用公式与函数处理数据必会操作 ........................................................... 6 1.2.1认清公式与函数 ............................................................................................... 6 1.2.2公式中的运算符和优先顺序 ............................................................................. 8 1.2.3掌握公式的输入方法 ...................................................................................... 11 1.2.4掌握函数的输入方法 ...................................................................................... 12 1.2.5如何复制公式计算同类数据 ........................................................................... 14 1.2.6理解函数的嵌套结构 ...................................................................................... 16 1.2.7你不得不知的数组公式应用 ........................................................................... 17 1.2.8 Excel中常见错误及解决方法 ........................................................................ 19 1.3计算的数据结果怎么保护.................................................................... 20 1.3.1将公式结果转化为数值的方法 ....................................................................... 20 1.3.2如何彻底隐藏工作表中的公式 ....................................................................... 22 1.4名称在数据计算中怎么用.................................................................... 24 1.4.1为什么要使用名称 ......................................................................................... 25 1.4.2了解名称的作用范围 ...................................................................................... 26 1.4.3定义名称的几种方法 ...................................................................................... 27 1.4.4在公式或函数中使用名称计算数据 ................................................................ 30 1.4.5对名称进行编辑与删除操作........................................................................... 32 第 2章招聘与培训管理数据处理 2.1人员面试及成绩分析 ........................................................................... 36 NO.001计算面试总人数【SUM()】 .......................................................................36 NO.002制作随机顺序的复试次序表【RAND()】 ...................................................38 NO.003查询面试*高成绩【 MAX()】 ....................................................................40 NO.004统计面试总成绩大于等于 320分的人数【SUM()】...................................41 NO.005计算面试人员的平均成绩【ROUND()/AVERAGE()】 ...............................43 2.2人员录用数据分析............................................................................... 44 NO.006判断面试人员是否被录用【IF()/AND()】...................................................44 NO.007统计录用人员中本科以上学历的人数【COUNTIF()】...............................47 NO.008统计符合录用条件的总人数【COUNTIFS()】...........................................49 2.3人员培训与转正数据分析 .................................................................... 51 NO.009判断员工是否缺考【IF()/ISBlANK()/OR()】 ..............................................51 NO.010根据考核成绩判断员工的考核结果【IF()/AND()/OR()】 ...........................54 NO.011计算员工测评总分【SUM()/LOOKUP()】..................................................56 NO.012计算员工的转正时间【YEAR()/MONTH()/DAY()/DATE()】 ......................59 NO.013判断员工是否符合转正条件【IF()/AND()/DATEDIF()/TODAY()】.............62 NO.014判断试用员工是否转正【IF()/NOT()/OR()】 .............................................64 第 3章员工档案数据处理 3.1员工身份证号码数据处理 .................................................................... 68 NO.015判断员工身份证号码位数是否正确【IF()/LEN()】.....................................68 NO.016快速恢复错误显示的身份证号码【TEXT()】.............................................70 NO.017从身份证号码中提取出生年月【CONCATENATE()/MID()】 .....................73 NO.018从身份证号码中提取生日信息【TEXT()/IF()/MID()】................................76 NO.019从身份证号码中提取员工性别信息【IF()/ISEVEN()/MID()】.....................77 NO.020根据身份证号码按性别统计人数【NOT()/MOD()/RIGHT()/LEFT()】........80 3.2员工工龄的计算与分析........................................................................ 82 NO.021计算员工工龄【VALUE()/YEAR()/NOW()】 ..............................................83 NO.022计算高级职称中的*小/大工龄【IF()/MIN()/MAX()】 ................................85 3.3员工基本信息查询与统计 .................................................................... 89 NO.023根据员工编号查找员工信息【CONCATENATE()/VLOOKUP()】..............89 NO.024查询某员工是否为本公司人员【IF()/MATCH()/ISERROR()】...................93 NO.025统计各部门的员工人数【COUNT()/SEARCH()】 .....................................96 NO.026统计公司本科学历以上的女员工总数【SUMPRODUCT()】 .....................99 第 4章员工考勤与出差数据处理 4.1员工考勤数据计算与统计查询........................................................... 102 NO.027自动生成考勤周报表的标题【CONCATENATE()/WEEKNUM()】.......... 102 NO.028从卡机数据中判断员工的归属单位【LOOKUP()/LEFT()/MID()】........... 104 NO.029从卡机数据中提取时间并判断员工是否迟到【RIGHT()】 ...................... 106 NO.030判断员工是否全勤【AND()】.................................................................. 108 NO.031查询指定员工*高缺勤次数【ISNA()/MATCH()/VLOOKUP()】 ............. 110 4.2员工出差管理与统计处理.................................................................. 112 NO.032计算顺利完成任务需要的出差人数【ROUNDUP()/SUM()】 .................. 112 NO.033计算可以组建的出差小组个数【FLOOR()/COUNTIF()】 ....................... 114 NO.034计算员工 3月份出差的报销费用【SUM()】 ........................................... 116 NO.035计算员工的误餐补贴【TIMEVALUE()/DAYS()/AND()/IF()】................... 118 第 5章员工工资数据处理与分析 5.1工资基本项目计算............................................................................. 124 NO.036已知工作年限计算员工的年限工资【IF()】............................................. 124 NO.037等额多梯度提成方式的提成工资计算【CHOOSE()/INT()/IF()】............. 125 NO.038将员工的提成工资保留到“角”位【ROUND()】................................... 128 NO.039计算员工加班所用时间【HOUR()/MINUTE()】 ...................................... 129 NO.040计算员工的考勤扣除【TRANSEPOSE()/SUMPRODUCT()】................ 132 NO.041计算员工的应纳税所得额【IF()/SUM()】................................................ 134 NO.042计算员工应缴纳的个人所得税【VLOOKUP()】...................................... 135 5.2员工工资查询与分析 ......................................................................... 138 NO.043根据员工姓名查找员工的提成工资【VLOOKUP()/IF()】........................ 138 NO.044根据员工的姓名查询工资数据【COLUMN()/VLOOKUP()】................... 140 NO.045计算各部门发放的工资的总额【SUMIF()】............................................ 143 NO.046制作工资条【OFFSET()/CHOOSE()/MOD()/ROW()/INT()】 ................. 145 第 6章公司日常财务数据管理 6.1办公财务数据管理............................................................................. 150 NO.047为办公费用添加计数编号【ROW()】...................................................... 150 NO.048计算领用的办公用品的价值【PRODUCT()】......................................... 151 NO.049计算车辆使用时给予驾驶员的补助费用【IF()/INT()】 ............................ 153 NO.050忽略隐藏行统计本月办公经费【SUBTOTAL()】 .....................................155 6.2收支记账与单据填写 ......................................................................... 158 NO.051在收支表中计算结余【SUM()/IF()】........................................................158 NO.052将金额进行分散填充【REPT()/MID()/TEXT()/COLUMN()】...................159 NO.053将合计数据转化为人民币大写【TEXT()/MOD()/SUBSTITUTE()】.........162 6.3固定资产折旧数据处理...................................................................... 165 NO.054计算每期折旧额相近的固定资产的折旧额【SLN()】...............................166 NO.055按工作量法计算固定资产的折旧额【DB()/LEFT()】 ...............................168 NO.056使用双倍余额递减法加速计提资产折旧【DDB()/SUM()】 ......................170 第 7章成本与货款数据的处理 7.1生产成本分析管理............................................................................. 176 NO.057计算各产品的平均生产成本【AVERAGE()】 ..........................................176 NO.058获取 3个*小材料消耗量【SMALL()】...................................................177 NO.059跟踪产品*近 3天的生产总量【TODAY()/SUMIF()/ROW()】 ................179 NO.060查询材料采购*频繁的车间【MODE()/INDEX()/MATCH()】 ..................181 NO.061计算各车间的平均生产成本【DAVERAGE()】........................................184 NO.062计算 2017年及之前某空调的平均出厂价【AVERAGE()/IF()/YEAR()】 ..........187 7.2货款数据管理.................................................................................... 189 NO.063计算货款的*后日期【TEXT()/EDATE()】..............................................189 NO.064计算货款的结款期【TEXT()/EOMONTH()】...........................................192 NO.065在提货明细表中添加结款期限倒计时提醒【TODAY()/ABS()/IF()】 ........193 第 8章采购与订单数据的处理 8.1采购数据处理.................................................................................... 198 NO.066整理供应商联系方式【MID()/FIND()】....................................................198 NO.067统一设置采购产品的型号【CONCAT()/TEXT()/REPT()】 ......................200 NO.068将采购数据的行号设置为连续状态【SUBTOTAL()】..............................202 NO.069查询指定厂家提供的某种产品的价格【ISBLANK()/VLOOKUP()】.........206 NO.070从到货产品中随机抽检 5种产品【RANDBETWEEN()】........................210 8.2订单数据处理.................................................................................... 213 NO.071统计订单数量大于 500的订单记录条数【COUNTIF()】.........................213 NO.072计算已收到订金的订单总额【SUMIF()】 ................................................215 NO.073统计服装仅有两种颜色的订单数量【SUBSTITUTE()/COUNTIF()】...... 216 NO.074汇总 3月份生产总量【SUMPRODUCT()/MONTH()】........................... 220 第 9章销售与库存数据的处理 9.1销售数据处理.................................................................................... 224 NO.075查询各部门每月*高销售额【MAX()】................................................... 224 NO.076计算**、二季度销售额变动情况【IF()/ABS()】 .................................. 225 NO.077统计各个利润区间的商品数量【FREQUENCY()】 ................................ 227 NO.078根据单价表和销量表汇总销售额【OFFSET()/N()/MATCH()】 ............... 230 NO.079计算打折期间的总营业额【MMULT()/SUMPRODUCT()】 .................... 233 9.2库存数据处理.................................................................................... 235 NO.080判断是否需要进货并换行显示判断结果【CHAR()/IF()】........................ 235 NO.081获取产品包装的长、宽、高并计算库存体积【FIND()/MID()/RIGHT()】..........239 NO.082输入库存编号自动查询库存明细【LOOKUP()】 .................................... 243