EXCEL技巧培训.pptx

Excel技巧培训 2020 3 21 1 目录 2020 3 21 2 数字处理日期计算字符串处理查找和引用判断公式统计公式求和公式 2020 3 21 3 一 数字处理 1 取绝对值 ABS 2 取整 INT 数字 3 四舍五入 ROUND 数字 小数位数 说明 1 取整和四舍五入结果是不一样的 四舍五入数字大小可能就变了2 ABS 函数做一些数据分析的时候用的可能多一点 2020 3 21 4 二 日期计算 两日期相隔年 月 天数计算假设A1是开始日期 2012 01 01 B1是结束日期2015 6 30相隔多少天 DATEDIF A1 B1 D 结果 1276相隔多少月 DATEDIF A1 B1 M 结果 41相隔多少年 DATEDIF A1 B1 Y 结果 3不考虑年相隔多少天 DATEDIF A1 B1 YD 结果 181不考虑月相隔多少天 DATEDIF A1 B1 MD 结果 29不考虑年相隔多少月 DATEDIF A1 B1 YM 结果 5 DATEDIF函数第三个参数说明 Y 时间段中的整年数 M 时间段中的整月数 D 时间段中的整天数 YD 天数的差 忽略日期中的年 MD 天数的差 忽略日期中的月和年 YM 月数的差 忽略日期中的日和年 2020 3 21 5 三 字符串处理 1 多单元格字符串合并 公式 D2 PHONETIC A2 C7 说明 1 PHONETIC只能对字符型内容合并 数字不可以2 合并的时候 先横向合并再纵向合并 2020 3 21 6 三 字符串处理 2 截取除后3位之外的部分 公式 B5 LEFT B3 LEN B3 3 说明 1 LEN计算出总长度 2 LEFT从左边截总长度 3个 2020 3 21 7 三 字符串处理 3 截取 前的部分 公式 B2 LEFT A2 FIND A2 1 说明 1 用FIND函数查找位置2 用LEFT函数截取 2020 3 21 8 三 字符串处理 4 字符串查找 公式 B2 IF COUNT FIND 河南 A2 0 否 是 说明 1 FIND查找成功 返回字符的位置 否则返回错误值2 COUNT可以统计出现数字的个数 3 借助IF来判断查找是否成功 2020 3 21 9 三 字符串处理 5 字符串一对多查找 公式 B5 IF COUNT FIND 辽宁 黑龙江 吉林 A5 0 其他 东北 说明 1 设置FIND第一个参数为常量数组2 用COUNT函数统计FIND查找的结3 借助IF来判断查找是否成功 2020 3 21 10 四 查找和引用 1 单条件查找公式 公式 C11 VLOOKUP B12 B4 F8 4 FALSE 说明 查找是VLOOKUP最擅长的 基本用法 2020 3 21 11 四 查找和引用 2 双向查找公式 公式 D10 INDEX C 3 H 7 MATCH B10 B 3 B 7 0 MATCH C10 C 2 H 2 0 说明 1 利用MATCH函数查找位置 用INDEX函数取值注意 MATCH函数是在一定范围单元格区域内查找位置 而FIND是查找一个字符串在另外一个字符串中出现的起始位置 2020 3 21 12 四 查找和引用 3 查找最后一条符合条件的记录 公式 C13 LOOKUP 1 0 C 5 C 10 B13 D 5 D 10 说明 查找最后一条符合条件的记录通用公式 LOOKUP 1 0 条件 查找的数组或区域 或者 LOOKUP 2 1 条件 查找的数组或区域 2020 3 21 13 四 查找和引用 4 多条件查找 公式原理同上一个公式 说明 多个条件区域 2020 3 21 14 四 查找和引用 5 指定区域最后一次非空值查找 公式 B16 LOOKUP 1 0 B4 B15 A 4 A 15 2020 3 21 15 2020 3 21 16 四 查找和引用 6 按数字区域间取对应的值 公式 详见下图 说明 VLOOKUP和LOOKUP函数都可以按区间取值 一定要注意 销售量列的数字一定要按照升序排列 2020 3 21 17 五 判断公式 1 把公式产生的错误显示为空 公式 C2 IFERROR A2 B2 说明 如果是错误值则显示为空 否则正常显示 2020 3 21 18 五 判断公式 2 IF多条件判断返回值 公式 C2 IF AND A2 500 B2 未到期 补款 说明 两个公式同时成立用AND 任何一个成立用OR 2020 3 21 19 六 统计公式 1 统计同一个或者两个表格重复的内容 公式 B2 COUNTIF E E A2 说明 如果返回值大于0说明另一个区域范围存在 0则不存在 2020 3 21 20 六 统计公式 2 统计不重复的人数 公式 C2 SUMPRODUCT 1 COUNTIF A2 A8 A2 B8 说明 用COUNTIF统计出每人出现的次数 用1除的方式把出现次数变成分母 然后相加 2020 3 21 21 七 求和公式 1 隔列求和 公式 H3 SUMIF A 2 G 2 H 2 A3 G3 或者 SUMPRODUCT MOD COLUMN B3 G3 2 0 1 B3 G3 说明 或者如果标题行没有规则用第2个公式 2020 3 21 22 七 求和公式 2 单条件求和 公式 F2 SUMIF A A E2 C C 说明 SUMIF函数的基本用法 2020 3 21 23 七 求和公式 3 单条件模糊求和 公式 详见下图 说明 如果需要进行模糊求和 就需要掌握通配符的使用 其中星号是表示任意多个字符 如 A 就表示a前和后任意多个字符 即包含A 2020 3 21 24 七 求和公式 4 多条件模糊求和 公式 C11 SUMIFS C2 C7 A2 A7 A11 B2 B7 B11 说明 在SUMIFS中也可使用通配符 2020 3 21 25 七 求和公式 5 多表相同位置求和 公式 B2 SUM Sheet1 Sheet19 B2 说明 在表中间删除或添加表后 公式结果会自动更新 2020 3 2