|
目录 CONTENTS 养成好习惯 01 养成好习惯 2.2 偷 师……………………………… 27 2.2.1 2.2.2 2.2.3 2.2.4 2.2.5 2.2.6 2.2.7 2.2.8 2.2.9 2.2.10 2.2.11 2.2.12 输入多个0有技巧………………………… 28 录入特殊字符…………………………… 29 只允许输入某范围的日期……………… 31 防止录入重复姓名……………………… 31 数据有效性其实很好骗………………… 33 眼见不一定为实………………………… 34 所见即所得……………………………… 35 哪里不同刷哪里………………………… 36 将两列的内容合并成一列……………… 37 将行变成列……………………………… 37 给单元格加把锁………………………… 38 转换成PDF，放心将文档交给供应商… 40 1.1 1.2 1.3 1.4 了解Excel的四类表格…………………2 以身说法………………………………7 注重细节…………………………… 12 本章小结…………………………… 14 2.3 本章小结…………………………… 40 02 向有经验的人学习 03 常用小技巧 2.1 再当一回学生……………………… 16 2.1.1 2.1.2 2.1.3 2.1.4 2.1.5 2.1.6 2.1.7 2.1.8 初识Excel………………………………… 16 这条线怎么画…………………………… 16 怎么边框显示不全……………………… 19 输入的0跑哪去了………………………… 20 分数怎么变成了日期…………………… 22 生成编号序列…………………………… 22 不相邻单元格输入同一数据…………… 24 快速录入大量小数……………………… 26 3.1 3.2 排 序……………………………… 42 3.1.1 3.1.2 排序其实很简单………………………… 42 按城市的发展情况排序………………… 43 筛 选……………………………… 45 3.2.1 3.2.2 筛选中的搜索功能……………………… 45 将带颜色项目挑选出来………………… 46 V  目录 3.2.3 借助高级筛选，让筛选数据更贴心…… 47 4.2.4 4.2.5 4.2.6 4.2.7 4.2.8 4.2.9 4.2.10 无处不在的IF…………………………… 95 学VLOOKUP，认识Excel爱好者……… 98 3.3 3.4 分 列……………………………… 49 LOOKUP潮汕………………………… 经典的INDEX MATCH组合………… 105 112 117 120 3.3.1 3.3.2 3.3.3 按分隔符分列…………………………… 49 将姓名分成两列显示…………………… 50 将不标准日期转换成标准日期………… 51 OFFSET的运用 百变神君TEXT ……………………… ……………………… 字符提取MID、LEFT 零散小技巧………………………… 52 和RIGHT三兄弟 …………………… 124 127 3.4.1 闪电式数据填充………………………… 52 删除错误值……………………………… 54 让数据输入更简单……………………… 57 重复值问题……………………………… 58 套用表格样式…………………………… 60 4.2.11 SUBSTITUTE的运用………………… 3.4.2 3.4.3 3.4.4 3.4.5 4.3 4.4 为自己量身定做的函数………… 129 4.3.1 4.3.2 4.3.3 4.3.4 连接所有字符LJ……………………… 将借阅的书籍合并HB………………… 提取批注内容PZ……………………… 自动录入当前时间NowTime………… 130 133 135 136 3.5 本章小结…………………………… 62 本章小结………………………… 138 04 05 最受欢迎的函数 与公式 看透数据的数据 透视表 4.1 一起来学习函数与公式…………… 64 4.1.1 基本概述………………………………… 64 初识函数………………………………… 66 快速、准确输入函数的两种方法……… 67 输入公式三招…………………………… 68 三种不同引用方式……………………… 69 两种不同引用样式……………………… 70 借助名称让公式更容易理解…………… 73 了解另外一片天地——数组公式……… 74 4.1.2 4.1.3 4.1.4 4.1.5 4.1.6 4.1.7 4.1.8 5.1 5.2 多变的要求……………………… 140 5.1.1 烦人的1234…………………………… 140 跟领导聊聊数据透视表………… 142 5.2.1 5.2.2 5.2.3 5.2.4 5.2.5 5.2.6 5.2.7 5.2.8 什么是数据透视表…………………… 多角度分析数据……………………… 更改值的汇总依据…………………… 排序让数据更加直观………………… 原来手工也能排序…………………… 对销售额进行排名…………………… 筛选销售额前5名的客户……………… 让更改的数据源随时刷新…………… 142 143 147 148 149 150 151 151 4.2 函数经典再现……………………… 77 4.2.1 闲聊SUM………………………………… 78 求和之王SUMPRODUCT……………… 86 既生SUMIF（COUNTIF）， 4.2.2 4.2.3 何生SUMPRODUCT…………………… 90 VI  目录 5.2.9 5.2.10 插入表格，让数据源“动”起来…… 日期组合真奇妙……………………… 154 155 5.2.11 手工组合，实现客户分级…………… 158 07 5.2.12 5.2.13 善借辅助列，实现客户实际销售额分析… 利用数据透视图让分析更直观……… 160 160 5.3 5.4 惬意的生活……………………… 165 学E千日， 5.3.1 准备好数据源，让领导自己分析去… 165 用在一时 本章小结………………………… 166 06 7.1 7.2 年度数据分析…………………… 188 7.1.1 7.1.2 7.1.3 数据核对……………………………… 数据汇总……………………………… 数据分析……………………………… 189 191 195 神奇的SQL语句 本章小结………………………… 200 08 6.1 6.2 6.3 系统是浮云……………………… 168 SQL是神马……………………… 168 试探性查询……………………… 170 在娱乐中学习 6.3.1 6.3.2 6.3.3 6.3.4 认识SQL的储存地…………………… 查询所有记录………………………… 查询不重复客户……………………… 查询符合条件的客户………………… 170 174 175 176 8.1 8.2 模仿手机功能…………………… 202 6.4 有目的统计……………………… 177 8.1.1 8.1.2 8.1.3 8.1.4 8.1.5 8.1.6 手机号码的显示格式………………… 姓名简称……………………………… 防骚扰………………………………… 联系人去重复………………………… 智能拨号……………………………… QQ通讯录开启人员…………………… 202 203 204 206 207 208 6.4.1 6.4.2 6.4.3 统计猪肉金额………………………… 统计批发跟零售金额………………… 统计产品用量………………………… 177 179 180 6.5 6.6 强强联合，见证神奇…………… 181 6.5.1 6.5.2 进销存分析…………………………… 资金流动分析………………………… 181 184 其实我是算命的………………… 209 本章小结………………………… 186 8.2.1 戏说生辰八字………………………… 210 VII  目录 8.2.2 8.2.3 8.2.4 8.2.5 8.2.6 星座学………………………………… 生日密码……………………………… 面相…………………………………… 点菜无烦恼…………………………… 别用你的无知挑战概率……………… 211 212 214 215 216 8.3 8.4 不会数学计算，Excel来帮你…… 217 8.3.1 8.3.2 8.3.3 8.3.4 8.3.5 数学基本计算………………………… 解方程………………………………… 预测数字……………………………… 随机抽样……………………………… 利润最大化…………………………… 217 218 221 223 224 本章小结………………………… 226 257 09 后记 用合适的方法做 合适的事 9.1 简单就好………………………… 228 9.1.1 9.1.2 9.1.3 9.1.4 9.1.5 9.1.6 9.1.7 9.1.8 粘贴成图片，排版无烦恼…………… 计算文本表达式……………………… 利用排序生成工资条………………… 多表数据核对………………………… 多行多列提取不重复………………… 逗号分隔符…………………………… 提取数字……………………………… 多表关联……………………………… 228 231 232 234 235 238 240 243 9.2 9.3 聊聊跟Excel有关的事儿………… 247 9.2.1 9.2.2 不知者无罪…………………………… 假如生活就是Excel…………………… 247 255 本章小结………………………… 256 VIII  刚开始接触Excel会遇到各种疑难，如一 些数据录入技巧。只要你虚心请教别人，别人 也会乐于教你。但每个人都有自己的为人处事 方法，不要因为别人态度冷漠而退缩，还有就 是对你再好的人也只能帮助你一时而已，不可 能帮助你一世。很多东西还得靠自己自觉去学 习，在摸索中成长。虽然这个成长过程并不会 太快，但工作效率却在逐步提高。 02   Excel效率手册——早做完，不加班 2.1 再当一回学生 毕业并不代表学习的结束，而是另一种学习的开始。工作上我们会碰到各种各样的新事 物，不懂的要及时向有经验的人请教。请教别人不仅能更快掌握知识，同时也能增进彼此间的 感情。从今天起，就一起跟着卢子学习录入数据的技巧吧！ 2.1.1 初识Excel 2007年，卢子放弃读大学的机会只身来到了东莞工作，还好有香姐照顾，工作、生活都挺 顺利。刚来公司，香姐就告诉卢子有空的话要学点 Excel的知识，这个在工作上经常用到。卢 子那时啥也不懂，既然这个有用，那下班后就学习一下。那时晚上办公室是开放的，允许在里 面用电脑。卢子来到办公室，打开了Excel，一看除了格子外还是格子，这能干嘛呢？先不管 三七二十一，在格子里输入：“我在学习Excel，这个有什么用呢？我相当地好奇。”如图2-1 所示，没想到小小格子里可以容纳那么多内容，挺神奇的。往后几天，Excel就成了卢子抒发 心情的地方，一有什么想法就写在里面。呵呵，其实卢子的打字水平也是在这个时候提高的。 图2-1 第一次输入的内容 2.1.2 这条线怎么画 刚开始上班挺闲的，有空的话就看资料，学习Excel。突 然有一天，我打开了一个表格，发现有些字下面有一条线， 如图2-2所示。心想这个是怎么画的呢？看见香姐并不忙，于 是就上前请教问题。 图2-2 文字下面的线怎么画 卢子：姐，文字下面这条线是怎么画出来的，怎么以前没见过？ 16      第 2 章 向有经验的人学习 香姐：这个是下划线，具体的操作是，选择B3单元 格，在编辑栏用鼠标选中“隆成(新厂)”， 然后切换到“开始”选项卡，再单击“U” 按钮，如图2-3所示。 图2-3 添加下划线 卢子：姐，回头我试试看。 香姐：还可以用插入直线，不过这种方法有点麻 烦，你了解下也好。 如图2-4所示，切换到“插入”选项卡，然 后单击“形状”图标，再选择直线。按住 Shift键，然后拉到合适的长度。 图2-4 插入直线 卢子：这个Shift 键有什么作用，不用它也可以画 出一条直线。 香姐：按Shift键是保证画出来的线是直线，如图 2-5所示如果仅仅是画一条直线按不按它关 系不大，但如果是画一个圆呢，你能保证 画出来的是一个圆？有的细节要从一开始 就注意。 图2-5 Shift键的作用 卢子：如果要画正方形是不是也这样操作？ 香姐：没错，小脑袋转得挺快的。如果要画正 圆、正方形等，也需要按Shift键。 卢子：姐，今天就麻烦你了，回头我将你教的这两个方法练习一下。 香姐：这么客气干嘛，我们又不是外人。对了，晚上有空的话，一起到阿伯那里喝茶。 卢子：好的，来这边这么久还没喝过功夫茶，还有点小小的怀念家乡的功夫茶。 香姐：来到这边就不要客气，我们是“胶己人”。 17 Excel效率手册——早做完，不加班 温馨提示 出门在外，潮汕地区的人遇到老乡，都称呼“胶己人”，虽然只有三个字，但意义非同一般。 功夫茶小知识 品茶礼仪 传统的潮汕功夫茶一般只有 3个杯子，不管多少客人都只用3个杯子。第 一杯茶一定先给左手第一位客人，无论其身份尊卑，无论其年龄大小，也无 分性别。每喝完一杯茶要用滚烫的茶水洗一次杯子，然后再把带有热度的杯 子给下一个用。这种习俗据说是人们为了表示团结、友爱和互相谦让的美好 品德。 品茶，要先闻香味，然后看茶汤的颜色，最后才是品味道，一杯茶要 刚好分为三口品完。香味从舌尖逐渐向喉咙扩散，最后一饮而尽，可谓畅快 淋漓。这就是功夫茶的三个境界——“芳香溢齿颊，甘泽润喉咙，神明凌霄 汉”。据说专业的品茶师可以凭一杯茶品出茶艺师当时的心情。说得很玄，不 过功夫茶本来不就是一种平和心境、修身养性的方法嘛。 日常生活 潮汕工夫茶，在潮汕地区深受人们喜爱，不少人早上起来就泡上一壶 茶，倦意顿时一扫而光，只觉得神清气爽。潮汕人喜欢以茶会友，在细品慢 酌、谈笑风生中，人们互通信息、加深了感情。品茶早已超越了简单的解渴 的目的，它还蕴含着丰富的文化内容。六合家宴的江经理介绍说，潮汕人 把茶叫做“茶米”，茶在潮汕人心目中就像米一样，足以看到潮汕人嗜茶如 命，茶与米的不可分了。 到城中的高档潮菜馆吃饭，席间总是穿插上功夫茶。在你吃完海鲜鱼肉 的时候，喝一杯可以消除腥味；在你吃着一碗甜品有点腻的时候，喝一杯可 以去腻开胃；在你酒足饭饱觉得有点撑的时候，喝一杯解乏消滞。功夫茶与 潮菜，就像一个硬币的两面，相辅相成，共同造就了潮汕饮食文化的博大精 深。有人说吃潮菜不喝功夫茶，总是感觉不太正宗。 18 第 2 章 向有经验的人学习 2.1.3 边框怎么显示不全 香姐：这是列宽太小导致的。如图 2-7所示，只需 选中B列，在标题栏向右拖拉到列宽可以容 纳所有内容为止。现在边框是不是自动出 来了？ 卢子：姐，我发现一件怪事，有一个单元格不管 我怎么设置边框都不管用，死活不显示出 来，如图2-6所示。怎么回事呢？ 图2-6 边框显示不全 图2-7 调整列宽 卢子：如果有多列需要调整列宽，有没有快捷一 点的操作方法？ 香姐：如图2-8所示，选择 E~H四列，双击 H列标 题栏就可以自动调整列宽。 图2-8 自动调整列宽 卢子：又学到了一招，还是这招自动调整列宽好用。 香姐：有的时候还可以通过双击获取最合适的行 高。工作上还有一种情况就是行高跟列宽 为固定值，这时就不能通过这两种方法来 调整。如图2-9所示，切换到“开始”选项 卡，再单击“格式”下拉按钮，选择“列 宽”选项，更改列宽大小，最后单击“确 定”按钮。用同样的方法，可设置行高。 图2-9 设置列宽大小 19 Excel效率手册——早做完，不加班 卢子：我刚才看你设置列宽的时候发现一个问 题，这些宽度都是以像素作为单位，但我 们平常都是以“cm”作为单位，就如职工 的照片宽度是 3cm。如何让像素变成以 cm 为单位的列宽呢？ 卢子：没想到小小的列宽就有这么多学问！ 图2-10“像素”变“cm” 香姐：这个问题问得好。如图 2-11所示，切换到 “视图”选项卡，再单击“页面布局”图 标，然后选择A列，右击并从弹出的快捷菜 单中选择“列宽”命令，这时就是以“cm” 作为单位了。将数字改成3，再单击“确定” 按钮。用同样的方法，可设置行高。 图2-11 设置以“cm”作为单位的列宽 2.1.4 输入的0跑哪去了 卢子：姐，我输入了好多次，出货日期那里的数字，不管我怎么输入，0都会消失，如图2-12所示，不知 道它跑哪去了？ 香姐：常规格式下，数字前面的0会自动被忽略，可以将单元格设置为文本。具体操作如图 2-13所示，利 用组合键Ctrl 1，弹出“设置单元格格式”对话框。切换到“数字”选项卡，然后选择“文本” 项，再单击“确定”按钮。返回单元格重新输入数字，这时得到的就是文本数字，文本数字不管 你输入什么内容都不会改变。 图2-12 自动消失的0 图2-13 设置文本格式 20 第 2 章 向有经验的人学习 卢子：这个真好用，0再也跑不掉了。 香姐：平常在输入身份证号等长字符串时，数字 超过15位都是变成 0，只能先将单元格设 置为“文本”格式再输入才行。因为我们 公司的出货日期格式都是6位数，这个也可 以通过自定义单元格格式做到。按组合键 Ctrl 1，弹出“设置单元格格式”对话框。 切换到“数字”选项卡，然后选择“自定 义”选项，设置自定义格式代码，再单击 “确定”按钮，如图2-14所示。 设置单元格格式有很多学问，以后你可以慢 慢了解。 图2-14 自定义单元格格式 温馨提示 N年以后，卢子发现了日期格式的秘密。香 姐教我的方法虽然可以解决问题，但这样的日期 并不是标准日期，在对日期进行分析时会有小小 的麻烦。 按快捷键 Ctrl ；可以快速输入当天的日期 (静 态)，然后设置自定义格式代码为YYMMDD。 其中Y代表年，M代表月，D代表日。YY代表 年为两位，MM、DD也是同样的道理。 这样设置有一个好处，就是以后在用透视表的 自动组合时，可以轻松按年、月、日分组。 如果要获取动态日期，可以用=TODAY()，如 图2-15所示。 图2-15 获取动静态当天日期 21 Excel效率手册——早做完，不加班 2.1.5 分数怎么变成了日期 卢子：姐，我输入的分数怎么总是变成了日期 呢？如图2-16所示，Excel老是自作主张改 我的东西。 补充说明 Excel 会对你输入的分数进行化简、约 分，将输入的分数化为带分数或最简真分 数。如果你不需要被自作主张改掉，可以通 过设置自定义单元格格式，得到图2-17所示 的效果。 图2-16 分数自动变成了日期 香姐：前面跟你讲过将单元格设置为“文本”格 式，其实也可以用在这个地方，不过这样 虽然看起来是分数，实际上并不是。这里 先给你介绍一下分数的小知识。 假分数 0/0 不被化简的分数：分母固定为8。 0/8 分数的储存形式 如图2-17所示，分数由五部分组成：整数 部分 空格 分子 斜杠(/) 分母，一个都不能 少，即使整数部分为0也不能省略。输入分数 后，在编辑栏会显示分数对应的小数的值。 图2-18 分数自定义后的显示 卢子：回头我好好理解一下。 转折点 听说香姐怀孕了，要当妈妈了，这是一 个好消息，以后我就不能老麻烦她了。从今 以后要换一种方式来学习 Excel，毕竟不是每 个人都对你这么友善，耐心指导你的。 图2-17 分数的组成形式 2.1.6 生成编号序列 今天卢子在给产品输入编号，按下面的方法操作： 输入1，回车； 输入2，回车； 22 第 2 章 向有经验的人学习 输入3，回车； …… 突然，卢子停下了敲打键盘的手，思索着：应该有其他方法的，要不然那么多编号要输入 到什么时候？ 香姐现在的心都放在未出生的宝宝身上，去打扰她又不好。正一筹莫展时，想起了曾经香 姐说过的一句话：菜头是我们公司Excel用得最好的人。想到这里，卢子立马跑到菜头面前。 卢子：菜头，向你请教个问题可以吗？ 菜头：可以。 卢子：我刚才在输入编号，一个个手动输入，如图2-19所示，感觉输入很慢，有没有快捷的方法？ 图2-19 手工输入编号 菜头：如图2-20所示，在A2单元格中输入1，鼠标放在A2单元格右下方，出现“ ”字形，按住Ctrl键， 拖动鼠标到A45就可以生成1~45的编号。 图2-20 自动生成编号序列 卢子：谢谢，这回我就省事多了。 菜头：嗯。 早就听说菜头这人不好相处，还是溜之大吉，自己去试这个功能吧。按照菜头教的方法操 作果然成功，试了不按Ctrl键数字全部都是1。 23 Excel效率手册——早做完，不加班 摸索发现 1. 编号序列也可以通过先在单元格中输入 1、 值跟终止值，再单击“确定”按钮就可以生成多种 形式的序列。 2，然后下拉就生成。 2.先在单元格输入2、4，可以生成等差序列，如 图2-21所示。 有兴趣的朋友可以逐一测试一下序列的各种功 能，如图2-22所示。 图2-21 自动生成编号跟等差 3. 几个月后又发现序列对话框 N多功能。在 “开始”选项卡，选择“编辑”组中的填充，单击 “序列”项，这里提供了好多选择，只需填写步长 图2-22“序列”对话框 2.1.7 不相邻单元格输入同一数据 在制作不良产品报告的时候，经常会输 入多个相同日期，每次单独输入很麻烦。看 到上回菜头愿意帮忙，卢子再次跑去请教。 卢子：菜头，如图2-23所示，有没有办法在不相邻 单元格中同时输入当天的日期？ 菜头：利用Ctrl键的三种用途可以实现。 图2-23 在不同单元格中输入当天的日期 24 第 2 章 向有经验的人学习 STEP 01 按住Ctrl 键依次选择 D2、D4、C8和 C9，如图2-24所示。 卢子：原来Ctrl 键这么好用，上回就是借用这个 键，快速生成序列的。 菜头：嗯。 还是老样子，连一句多余的话也不说。 卢子没办法只得回到自己座位上去研究。 图2-24 选择单元格 摸索发现 STEP 02 按快捷键Ctrl ；生成当天日期，如 图2-25 所示。 其实还可以在多个表的同位置单元格 中输入相同内容。如在Sheet2~Sheet5的B2中 同时输入“卢子”。切换到Sheet2，接着按 住Shift键再单击Sheet5，这样就能选中4个表 格。在B2单元格中输入“卢子”，按组合键 Ctrl Enter结束。这时就可以看到这4个表格同 时输入好了，输入后的效果如图2-27所示。 如果输入的表格不相邻，要按Ctrl键依 次选择。 图2-25 生成当天日期 STEP 03 按快捷键Ctrl Enter结束，如图2-26所示。 图2-27 多表格操作 图2-26 批量生成日期 25 Excel效率手册——早做完，不加班 2.1.8 快速录入大量小数 领导不知从哪里搞来了一份单价表，让卢子录入数据。卢子仔细一看，都是一些小零件， 价格都是几毛钱而已，也就是说，所有单价都是零点几元。既然是领导安排的工作，卢子马上 着手输入。结果输入了几个数据后，就发现了一些小问题。这样输入单价挺烦琐的，都得输入 “0.”，前面都是重复的，还有就是有时小数点还会点错位置，甚至忘记输入。如果连这件小 事都出错，领导会怎么看卢子，没法只得请教菜头，虽然菜头不好说话，但一切以工作为主。 卢子：菜头，我这里有份单价表，但所有数字都 是含有小数点的，如图2-28 所示。有没有 办法快速输入？ 菜头：如图2-29所示，选择“文件”，然后依次 选择“选项”命令，单击“高级”按钮， 选中“自动插入小数点”复选框，再单击 “确定”按钮。 选中 图2-28 输入大量带小数点数字 图2-29 自动插入小数点 卢子知道菜头的为人，所以也不继续多 问，就自己动手尝试。经过这样的设置， 只要输入整数部分即可，如0.89，只要输入 89。没多久，卢子就把领导交代的事情完成 了，如图2-30所示。 图2-30 录入小数效果 26 第 2 章 向有经验的人学习 摸索发现 趁着空闲，卢子又仔细看了一下这个功能。发觉小数点的位数可以调节，允许为负数。如果将小数点位置设 置为负数就是扩大的倍数，如-3就是扩大10倍。但这个功能有一个局限，就是设置后整个工作表不管输入什么 3 数字都会自动扩大或者缩小，如果有其他数据录入，这个功能是不可取的。 解决方案1：障眼法 通过自定义单元格格式得到。 !0!.00 击 解决方案2：选择性粘贴（除） 如图2-31所示，在C1中输入100，然后复制C1。 选择A1:A5，右击并选择“选择性粘贴”命令，选中 “除”单选按钮，再单击“确定”按钮。这个方案我 觉得最好，得到的是真正的小数，而不影响其他内容 图2-31 选择性粘贴（除） 的输入。 2.2 偷?师 职场中有两种人，一种是热情帮助你的人(香姐)，一种是对你很冷漠的人(菜头)。遇到前一 种是你的福气，遇到后一种也不要悲伤，毕竟没有人有义务帮助你，即使可以帮你也只是一时 而已，很多事情都得靠自己努力。身边从不缺乏有能力的人，只是缺乏发现这些人的眼睛。只 要你留心，很多人都是你学习的榜样。明着我们可以不用请教他们，但暗地里却可以看这些人 以往留下的文档资料来学习。有时行走的步伐稍微放慢一点都可以学到知识，走到有经验的人 背后，偷偷瞄一眼都可以学到一个技能，曾经我就这么干过。偷学不在乎技能的大小，只要看 到了就学习。日积月累，常用的小技巧都将被你所掌握。 27 Excel效率手册——早做完，不加班 2.2.1 输入多个0有技巧 我们公司每天的出入账都是上百万， 甚至上千万。这么大的金额，每次输 入的时候都得数有多少个 0，怕输入错 误。如图2-32所示，怎么才可以快速、 准确输入这么多个0呢？ 教你一招，既能准确输入，又能快速 有效。 图2-32 快速无误输入多个0 STEP 01 如图2-33 所示，利用组合键 Ctrl 1 调 出“设置单元格格式”对话框。选择 “货币”选项，小数点位数改为0，单 击“确定”按钮。 图2-33 将单元格设置为没有小数点的货币样式 STEP 02 如图2-34所示，输入：数字**N，只能 用两个*，N代表几个0。 图2-34 快速、准备输入多个0的技巧 28 函数与公式可以说是 Excel 的精髓， 每天都有无数人在讨论她的用法。基本 上每天都有一些精妙的公式被发掘出 来。学公式靠的是逻辑思维和思考问 题的方法(角度)，不像技巧那样需死记硬 背。不经过认真思考、举一反三是永远 学不好公式的。学好公式，绝大部分的工 作都可以轻松搞定。曾经有人说过，只要 学好公式再配合一些技巧，你就可以成为 高手。让我们一起向高手迈进一小步！ 04 Excel效率手册——早做完，不加班 4.2.1 闲聊SUM 卢子：你会SUM函数吗？ 还没等卢子继续说下去，网友就耐不住 性子了。 网友：别开玩笑啦，这个谁不会。如图4-44所示， 选择单元格A7，在“公式”选项卡里，单 击“自动求和”按钮，就自动对区域进行 求和，简单得要死。 网友：帮助也不过如此，就是对所有数字求和， 我以为有什么稀奇。 卢子：实际帮助说到的用法，仅仅是最基础的用 法，连SUM的冰山一角还没有见着。 网友：有这么夸张吗，你倒是说说看？ 卢子：这里通过两部分来说明 SUM的用法——基 础用法与知识扩展。 基础用法 案例 1：如图 4-46所示，这是一份每 月销售清单。现在想按月份累计销售 额，该怎么办？ 图4-44 自动求和 卢子：你还知道SUM的其他用法吗？ 网友：这个不就自动求和而已，还有什么用法？ 卢子：按F1键调出帮助，输入sum搜索，会出现这 个函数的用法，如图4-45所示， 图4-46 每月销售清单 选择C2单元格，输入公式，并向下填 充公式。 =SUM(B$2:B2) 给第一个B2的行塞点美元(B$2)，让行站 住不动，下拉的时候不会有任何变化， 依然是B$2。第二个B2因为没给美元， 图4-45 SUM的用法 78 第 4 章 最受欢迎的函数与公式 下拉就变成B3、B4……B13了。在C3区域就变成了B$2:B3，也就是对B2:B3区域求和。在 C13区域变成了B$2:B13，也就是对B2:B13区域进行求和。适当给点美元，会起到意想不 到的效果。 案例2：如图4-47所示，这是一份人员 销售清单，需要汇总销售额，如果是 你会怎么汇总呢？ 网友：销售额分成三列，每一列就用“,”隔开， 公式如下： =SUM(F2:F7,H2:H7,J2:J7) 卢子：看来你对帮助还理解不够透彻，一起来看 看函数说明。 图4-47 人员销售清单 函数说明 如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑 值或文本将被忽略。 如果任意参数为错误值或为不能转换为数字的文本，Excel 将会显示错误。 也就是说，如果区域中有文本，将被忽略，所以只要写一个区域就行了。 =SUM(E2:J7) 网友：这些小细节还真没注意看，多谢提醒。 卢子：前面都是基础的，再来看看难度大点的。 知识扩展 案例3：如图4-48所示，这是一份没经 过任何处理的不良明细，存在错误值， 直接求和出错。这个有办法解决吗？ 图4-48 含错误值不良明细 79 Excel效率手册——早做完，不加班 网友：这回我仔细研究了帮助，如果任意参数为错误值或为不能转换为数字的文本，那么Excel 将会显 示错误。这种问题只有删除错误值才可以统计，不然会出错。呵呵，这回我没说错吧，我也挺 用功的。 卢子：帮助仅供参考，我们还需要学会思考问题。“定位”错误值，然后删除也是一种办法。其实也可 以直接求和，在这之前先了解一下IFERROR函数，如图4-49所示。 图4-49 IFERROR函数说明 说白了，就是可以将错误值显示成你 想显示的任何形式。 =IFERROR(错误值,显示值) 如图4-50 所示，既然这样，可以先通过 将错误值显示成0，然后再汇总。 虽然这样可以汇总，但会产生一个辅助 列。回到基础知识，再看看数组公式的概念。 图4-50 辅助列汇总 数组公式可以执行多项计算并返回一个或多个结果。数组公式必须按组合键Ctrl Shift Enter 结束，在输入数组公式时，Excel 会自动在大括号 { } 之间插入该公式。 利用数组可以省略辅助列，直接得到结果。 =SUM(IFERROR(B2:B8,0)) 先将错误值全部转换成 0，然后再汇总，因为转换过程需要重新运算，所以需要按组合键 Ctrl Shift Enter结束。借助数组可以省略很多中间步骤，如果你想成为别人眼中的高手，数组 必须熟练掌握。下面再通过两个例子来巩固对数组公式的理解。 80 第 4 章 最受欢迎的函数与公式 案例 4：如图 4-51所示，这是一份每 月销售清单，现在要统计销售额大于 500的次数？ 刚好F2是600，显示1，下拉试试，F3是 120，显示0。跟我们的预想一样。如果是整个区 域判断！ =IF(F2:F13>500,1,0) 在编辑栏按F9键将公式抹黑，得到 {1;0;1;0;0;0;0;1;0;0;0;0} 跟我们在单元格下拉公式得到的结果是 一样的，只是显示在数组中。这样只是起到判 断而已，还需要求和。只需要在IF外面再嵌套 SUM函数就行了，因为要执行多重计算，所以 是数组公式。 图4-51 每月销售清单 先来了解一下IF函数的用法，如图4-52 所示。 =SUM(IF(F2:F13>500,1,0)) 网友：貌似看懂了点，对了这个 F9键是干嘛用？ 卢子：F9键，人称“独孤九剑”，看过《笑傲江 湖》的人应该知道令狐冲的独孤九剑很厉 害。既然 F9键有这个雅称，一定有过人之 处。F9键是解读公式的利器，公式如果太 长了看不懂，将看不懂的地方抹黑就知道运 算结果了。看完后再按组合键Ctrl Z返回， 否则公式就变了。步步高点读机有一句广告 词：哪里不会点(抹)哪里，so easy！妈妈再 也不用担心我的学习了。用在这里再适合不 过，如公式： 图4-52 IF函数帮助 通俗点就是： =IF(条件,满足条件的返回值 ,不满足条件的返 回值) =SUM(SMALL(IF(B$1:B$10=5,ROW($1:$10)), ROW(1:2))*{-1;1})-1 可以通过 IF来判断销售额是否满足大于 500，让大于500的显示1，小于或等于500的显 示0。可以在单元格中输入公式，看看是否跟 我们想的一样。 这个ROW($1:$10)看不懂，你就抹黑它再按 F9键。 =SUM(SMALL(IF(B$1:B$10=5,{1;2;3;4;5;6;7;8; 9;10}),ROW(1:2))*{-1;1})-1 =IF(F2>500,1,0) 原来相当于 1~10，记得按组合键 Ctrl Z返 81 Excel效率手册——早做完，不加班 回哦，Excel是允许你后悔的。 网友：原来是协助解读公式的一个工具。 卢子：这个很好用，我经常用。还有一个叫公式求值的功能，效果跟这个差不多，有空你也可以了解一下。不过 公式求值让人觉得自己就是一个机械操作工，而F9键让人觉得自己是一个剑客，凡事随心所欲。 再举一个例子来说明数组的用法 案例5：还是以每月销售清单那个附件来说明，求销售额大于 500的人的总销售额。 有了上面的基础，再来了解这个就很容易了。 =SUM(IF(F2:F13>500,F2:F13,0)) 用IF进行判断，让大于500的显示原来的销售额，其他显示0，进行求和刚好得到销售额 大于500的人的总销售额。 网友：这回懂了，谢谢卢子。 卢子：今天这个SUM函数就先告一段落，自己有空再去熟练一下，欲知SUM更多用法，且听下回分解。 网友：谢谢，期待下回更精彩的讲解！ 这边刚结束，就收到简单、Simple的私聊消息。 简单：辛苦了。 卢子：累死人，没想到一讲就是两个小时，比1000米赛跑还累。 简单：明天把这些整理一下，分享到群里。 卢子：好的。 Simple：讲得不错，挺有逻辑性的。 卢子：已尽力了，但愿这次能收到好的评价。回头你看看大家的评价怎样？ Simple：明后天应该就能知道大家的想法，到时跟你汇报。 卢子：那我先睡了，脑力活原来比体力活更累。 Simple：那早点休息吧。 ………… 第二天晚上，收到Simple的私聊信息。 Simple：大家评价蛮高的，都在打听什么时候再组织讲座，到时得提前通知。群里不断有 陌生人加入，说要听课。看你太辛苦，昨晚的讲座内容已经帮你整理好了。 卢子：谢了，回头我直接把你整理的分享出来就行。这回的辛苦总算没白费，我回头再准 备下，争取这两天举行第二回讲座。 82 第 4 章 最受欢迎的函数与公式 经过两天的准备，卢子把SUM函数的其 他资料整理好了。这回只是对上回知识的补 充，并不会涉及太多的知识点。 卢子：今晚继续学习 SUM函数，通过三个小例子 来对上一回的知识进行补充说明，大约 1小 时就可以讲完了，呵呵。 晚的正题，别见怪，刚才只是有感而 发而已。 刚才提到了替换这个词，函数中也有 属于自己的替换函数，SUBSTITUTE 函数，用法如图4-54所示。 网友：老师辛苦了，没想到小小 SUM函数居然这 么神奇，这回要用功学习才是。 卢子：很好，那一起开始学习吧。 案例1：如图4-53所示，这是一份含有 单位的人员销售清单，直接求和得不 出正确答案，怎么才能让含单位的销 售额可以求和呢？ 图4-54 SUBSTITUTE函数帮助 SUBSTITUTE 的第四参数为可选，那 就先别管它，其他参数可以理解为： =SUBSTITUTE(文本,需要替换的旧字符, 替换成新的字符) 单元格中的“元”是多余的，需要替 换成空，空可以用＂＂表示，替换成 空后直接求和，可以吗？ 图4-53 含单位的人员销售清单 帮助提到，SUM函数会自动忽略文 本，600元就是文本，不属于数字。最 简单的做法就是将“元”替换成空， 然后自定义单元格格式G/通用格式 “元”。很多人就是搞不明白一格一属 性的道理，才会造成汇总数据困难重 重。正确的做法应该将“元”写在字 段名那里变成销售额 (元)，这样别人一 看便知。废话了这么多，还没扯到今 = SUM(SUBSTITUTE(B2:B7,＂元＂, ＂＂)) 网友：这个是数组公式，用法也跟前面说得差不 多，应该可以汇总。 卢子：SUBSTITUTE函数属于文本函数，所以替 换得到的数字也属于文本，在这里叫作文 本数字。数字有两种类型，一种是文本数 字，一种是真正的数字，即数值。数值可 83 Excel效率手册——早做完，不加班 以求和，而文本不能求和。如账簿上的数 综上，最终的数组公式为： 字跟墙上的数字是不同的，前者我们可以 用这些数字进行各种分析，后者只能当欣 赏用。那有什么办法还原数字的本质呢？ =SUM(--SUBSTITUTE(B2:B7,＂元＂,＂＂)) 网友：没想到数字还有这些学问，长见识了。 卢子：再来看另一种不规范输入的案例。 利用VALUE函数可以将文本型转换成 数值型。 案例2：如图4-55所示，这是一份含附 加分的成绩明细表，分数分为基本分 （左），附加分（右），怎么汇总分数呢？ =VALUE(＂600＂) 但一般情况下不会这么做，而是通过 运算转换。 一起来了解“减负”运算 在函数或公式中，运算过程会自动把文 本转换为数值(一个隐含过程)，再与数值进行 运算，负值运算(-)也是一种运算，能把文本 转换成数值。 图4-55 含附加分成绩明细 仔细观察发现： -＂600＂=-600 有附加分的中间都隔着空格，如图4-56 所示。这跟分数写法的前半部分一 样，只是少了斜杠 (/) 分母。既然这 样，我们就可以构造后半部分。分 数&“/1”，E2就得到“90 5/1”， 通过&函数得到的是文本数值，前面加 “－－”让他变成数值，E3本身就是 数字，不必转换。 还记得负负得正吧？例如： -(-＂600＂)=-(-600)=600 可简写为： --＂600＂=600 --可以把文本转换为数值，但它不是标准 的转换方式，而是借用负运算的隐含功能。 其实负负运算称为减负运算更好，即减 去数字的负担，还原数字的本质。 =SUM(--SUBSTITUTE(B2:B7,＂元＂,＂＂))， 将这一部分用F9键抹黑，得到： =SUM({600;120;1000;210;129;123}) 这样就能够求和了。 图4-56 分数的组成部分 84 第 4 章 最受欢迎的函数与公式 通过上面的分析，问题已经解决一大半 了，只需判断分数是不是数值，是的 话就显示本身的值，不是就用--(分数 &“/1”)。怎么判断单元格的值是不是 数值呢？先来了解一下 ISNUMBER 函 数，它只有一个参数。 案例3：如图4-57所示，这是一份人员销 售清单，经常要在总销售额处插入新的 人员。用SUM直接统计不会对新增加人 员的销售额进行汇总，该怎么处理呢？ =ISNUMBER(值) 如果是数字就显示 TRUE，否则显示 FALSE。 到这里思路都出来了，只需输入数组公 式到单元格里来验证我们的想法是否正 确就可以了。 图4-57 人员销售清单 按组合键Ctrl F3定义一个叫“上一 行”的名称，引用位置为B20。 =SUM(IF(ISNUMBER(E2:E7),E2:E7,-- (E2:E7&＂/1＂))) 这个引用为相对引用，每插入一行引 用位置就会动态变化，所以用下面的 公式就可以搞定，以后插入行也会自 动汇总进去，如图4-58所示。 网友：测试通过，原来写公式跟断案一样，需要 严谨的分析，才能不判错案子。 卢子：公式主要是靠逻辑，大脑要经常动，这样 才不会生锈。 =SUM(B15:上一行) 温馨提示 分子不能超过5位数，否则出错。 如：1 100000/1 用=ISNUMBER(＂1 100000/1＂)测试显示为 FALSE，证明这个 是文本。 图4-58 定义名称 正常的话，不会出现这样的分数，稍 微了解一下就行。 一个过来人的忠告 有人说直接用 SUM( 区域)就行，插入行 后区域会自动扩展。但我说这是Excel的BUG 85 Excel效率手册——早做完，不加班 你信吗？曾经我就因为直接用SUM(区域)导致开错两张单，最后核对金额的时候才发现异常， 还好金额不大，如果是大金额，这将给公司带来多大的损失呀。小心驶得万年船，如果你坚 持你的想法，用SUM(区域)插入行后请选择区域，查看状态栏的总计跟公式汇总是否一致。图 4-59为直接用SUM(区域)插入行后的结果，数量添加的行会自动汇总进去，金额却没有自动增 加。为了保险起见还是定义名称，当然还有其他方法，例如： =SUM(B15:INDEX(B:B,ROW()-1)) 图4-59 在“合计”上面插入行后的结果 关于SUM的用法到这里已经结束了，如果还有什么疑问可以向Simple提出来，到时我再统 一解答。 网友：又学到了几招，谢谢老师。 卢子：对了，以后叫我卢子就行，这样我还习惯点，下节课根据大家的反馈再决定讲什么函数。 4.2.2 求和之王SUMPRODUCT 卢子：经过这几天的反馈，有两个问题提的人比较多。 其1：实例是用公式 =SUM(IF(F2:F13>500,F2:F13,0))，但现实有很多公式是这种形式 =SUM((F2:F13>500)*F2:F13)，后者是怎么得出来的？ 其 2： SUM的数组公式要按组合键 Ctrl Shift Enter 结束才能得到正确答 案，很麻烦，经常会忘记按组合键 Ctrl Shift Enter，有没有其他函数可以取 代SUM的数组形式呢？ 一起来看看问题 1，如图4-60所示，以 销售额大于500的人的总销售额为例进 行说明。 图4-60 销售额大于500的人的总销售额 86 第 4 章 最受欢迎的函数与公式 =SUM((F2:F13>500)*F2:F13) 可能很多人 想知道抹黑地方的意思，有句话叫心急吃不 了热豆腐，凡事得一步步慢慢来，急不得。 开始先不要直接用整个区域解读，先分成一 个单元格的判断解读，单元格理解后再转换 成区域理解。这样更有助于理解，适当的时 候配合F9键，效果会更好。 网友：原来公式是这么解读的，老想一步就到 位，反而理解不好。先拆开，再合并，先 记住这个方法。 =B2>500如果成立就显示 TRUE，否则 显示FALSE。在这里B2>500 成立，显 示TRUE。 =(B2>500)*B2 等同于 =TRUE*B2， 得到600，也就是说在这里 TRUE相当 于1。 卢子：问题2用SUMPRODUCT可以取代SUM的数 组公式。还是老方法，先看SUMPRODUCT 函数的帮助，如图 4-61所示，对多个区域 先相乘，后汇总。 =(B3>500)*B3等同于=FALSE*B3，得到 0，也就是说在这里FALSE相当于0。 *可以让符合条件的值显示其本身，不 符合条件的值显示 0，但它不是标准的 转换方式，而是借用乘法运算的隐含 功能。再回到公式上 =SUM((F2:F13>500)*F2:F13) 按F9键抹黑得到： =SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;F ALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FA LSE})*B2:B13) 图4-61 SUMPRODUCT帮助 得到一组由TRUE跟FALSE组成的数组： 基础用法 =SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;F ALSE;FALSE;TRUE;FALSE;FALSE;FALSE; FALSE})*B2:B13) 毕竟是出自一家人，帮助都差不多，非 常好理解，如图4-62所示，统计总金额。 数组*B2:B13，让符合条件的都显示其 本身，其他显示0。 =SUM({600;0;1000;0;0;0;0;1000;0;0;0;0}) 到这步应该可以理解了吧。解读公式一 图4-62 统计总金额 87 Excel效率手册——早做完，不加班 =SUMPRODUCT(B2:B4,C2:C4) 只强调一句，SUMPRODUCT 将非数值型的数组元素作为 0处理，如B4现在的值是文本 =SUMPRODUCT({2;5;＂无＂},C2:C4)，“无”在这里等同于0。在此不再对这个函数的基础用 法进行说明，否则会让大家误认为侮辱了你们的智商。 知识扩展 通过对SUM的学习知道了它可以求和、计数，SUM能做到的SUMPRODUCT 都能做到， 而且做得更好。SUMPRODUCT函数本身就支持数组，所以条件计数、求和的时候不需要按组 合键Ctrl Shift Enter，正因为这样它才受到大多数人的喜欢。有人把它比喻成璀璨的明珠，光芒 四射，魅力无穷，称为求和之王也不为过。 通用公式 计数： =SUMPRODUCT((条件1)*(条件2)*(条件3)*…*( 条件N)) 求和： =SUMPRODUCT((条件1)*(条件2)*(条件3)*…* 求和区域) 如图4-63 所示，这是IT部落窝随机抽查 的人员资料表，下面通过10个小例子来说明 条件计数、求和的用法。 图4-63 IT部落窝随机抽查的人员资料表 计数 例子1：女性有几个人？ =SUMPRODUCT(--(C4:C22=＂女＂)) 例子2：潜水时间大于15天的男人有多少？ =SUMPRODUCT((E4:E22>15)*(C4:C22=＂男＂)) 88 第 4 章 最受欢迎的函数与公式 例子3：2月份发言的男人有多少？ =SUMPRODUCT((MONTH(D4:D22)=2)*(C4:C22=＂男＂)) 这里涉及一个新函数MONTH，其作用就是将日期转换成月份。相关联的函数还有 YEAR，其作用是将日期转换成年，DAY函数可将日期转换成日。 例子4：不包括“笑看今朝”的男人有几个？ =SUMPRODUCT((A4:A22<>＂笑看今朝＂)*(C4:C22=＂男＂)) < >（不等于）属于比较运算符，还有=（等于），>（大于），<（小于），>=（大 于等于） 和 < =（小于等于），跟数学的表示方法略有差别，但作用一样。 求和 例子5：女性潜水总天数。 =SUMPRODUCT((C4:C22=＂女＂)*E4:E22) 例子6：潜水时间大于15天的男性的潜水天数。 =SUMPRODUCT((E4:E22>15)*(C4:C22=＂男＂)*E4:E22) 例子7：2月份发言的男性的潜水天数。 =SUMPRODUCT((MONTH(D4:D22)=2)*(C4:C22=＂男＂)*E4:E22) 例子8：QQ号首位是8的人的潜水天数。 =SUMPRODUCT((LEFT(B4:B22)=＂8＂)*E4:E22) LEFT的语法：LEFT(文本,N)，提取左边的N位文本，省略第二参数，就是提取1位。 例子9：姓名字符数为2，不包括“月亮”的人的潜水天数。 =SUMPRODUCT((LEN(A4:A22)=2)*(A4:A22<>＂月亮＂)*E4:E22) LEN的语法：LEN(字符)，统计字符个数，汉字、字母、数字都是一个字符；LENB(字符 )，统计字节个数，汉字两个字节，字母、数字为一个字节。 例子10：“笑看今朝”和“冷逸”的潜水天数。 =SUMPRODUCT(((A4:A22=＂笑看今朝＂) (A4:A22=＂冷逸＂))*E4:E22) 在这里是“或”的意思，只要满足其中一个条件就行，它有时可以替代 OR的功能，如 =IF(OR(A4=＂笑看今朝＂,A4=＂冷逸＂),1,0)等同于=IF((A4=＂笑看今朝＂) (A4=＂冷逸＂),1,0)， 但OR不能替代 在数组中的用法，切记！ 89 Excel效率手册——早做完，不加班 简化 =SUMPRODUCT((A4:A22={＂笑看今朝＂,＂冷逸＂})*E4:E22) 公式剖析，老办法，先转换成单元格比较。 A5={＂笑看今朝＂,＂冷逸＂}，一个单元格跟两个值同时比较，满足就显示TRUE，否则显 示FALSE。 A5={＂笑看今朝＂,＂冷逸＂}，按F9键得到{TRUE,FALSE}。 ({TRUE,FALSE})*E5，按F9键得到{6,0}。也就是说，只要单元格满足其中一个值，就一 定会得到由0跟单元格本身组成的常量数组，完全不满足就显示{0,0}。因为单元格不可能同时 满足两个条件，所以不会出现{6,6}这种情况。 =SUMPRODUCT((A5={＂笑看今朝＂,＂冷逸＂})*E5) 抹黑得到： =SUMPRODUCT({6,0}) 同理： =SUMPRODUCT((A4:A22={＂笑看今朝＂,＂冷逸＂})*E4:E22) 抹黑得到： =SUMPRODUCT({0,0;6,0;0,5;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0}) 这里就不再进行解释了，留点空间给大家思考。 有SUM作为铺垫，理解SUMPRODUCT会异常简单。今天就到此结束，有疑问可以反馈出来。 网友：谢谢，回去好好消化一下。 4.2.3 既生SUMIF（COUNTIF），何生SUMPRODUCT 网友：SUMPRODUCT函数太好用了，导致现在Excel专门的条件求和、计数函数SUMIF(COUNTIF)都不 去使用了。 卢子：一部《三国演义》让大家把诸葛亮当神看待——神机妙算，无所不能。以至于周瑜感叹“既生 瑜，何生亮”。其实周瑜也是一个很有才华的人，只是被掩盖了。 扯远了，回到正题。 SUMIF(COUNTIF) 其实也很好用，有好事者测试了SUMIFS跟SUMPRODUCT多条件求和 统计速度，前者是后者的三倍。那SUMIF单条件统计速度比SUMPRODUCT快一点还是可以肯 定的。不过对你我来说，可以忽略这个速度的问题。 90 第 4 章 最受欢迎的函数与公式 先来了解一下 COUNTIF 函数。怎么 学函数，还是老话按 F1键调出帮助。不 要对每次的重复操作厌倦，帮助可以给 我们提供很多有用的信息。如图4-64 所 示，这是COUNTIF 函数的帮助。 通俗点的语法： =COUNTIF(条件区域,条件) 统计区域满足条件的个数。 图4-64 COUNTIF帮助 下面通过几个小例子来说明COUNTIF 的用法。如图4-65所示，这是2006年电脑 配件销售一览表。 例子1：数量大于30的有几个？ =COUNTIF(D4:D22,＂>30＂) 例子2：营业部中含“河”字的有几个？ =COUNTIF(A4:A22,＂*河*＂) 通配符的说明：*代表所有字符，？ 代表一个字符。如果需要统计营业部中的 两个字符，且“河”字在最后面，可以这 么写公式： =COUNTIF(A4:A22,＂河＂) 图4-65 2006年电脑配件销售一览表 91 Excel效率手册——早做完，不加班 例子3：在商品列中是否有键盘？ =IF(COUNTIF(B4:B22,＂键盘＂)>0,＂存在＂,＂不存在＂) 如果存在键盘COUNTIF统计出来的次数大于0；否则等于0；公式可以稍做简化。 =IF(COUNTIF(B4:B22,＂键盘＂),＂存在＂,＂不存在＂) 网友：>0这部分怎么可以省略？这是什么原理？ 卢子：一起来看看下面几个判断。 =IF(3,＂存在＂,＂不存在＂)，返回存在； =IF(-3,＂存在＂,＂不存在＂)，返回存在； =IF(0,＂存在＂,＂不存在＂)，返回不存在。 也就是说任何不等于0的数字在这里都等同于TRUE，0等 同于FALSE。如果不相信，可以自己多试几个看看。不过建议 初学者不要用简写，用标准写法更有助于理解。前面几个例子 的条件都是手写的，其实条件可以直接引用单元格。 图4-66 引用单元格 例子4：如图4-66所示，统计每个营业部出现的次数。 =COUNTIF($A$4:$A$22,L4) 因为公式要下拉，为防止区域改变，所以加绝对引用。绝对引用、相对引用和混合引用， 可以通过用F4键切换得到。 例子5：统计共有几个不重复营业部？ =SUMPRODUCT(1/COUNTIF(A4:A22,A4:A22)) =SUMPRODUCT(1/COUNTIF(区域,区域))是计算区域不重复个数的经典公式，需要好好理 解。为了便于解读公式，应把区域改小，公式变成： =SUMPRODUCT(1/COUNTIF(A4:A9,A4:A9)) 观察 =SUMPRODUCT(1/COUNTIF(A4:A9,A4:A9)) 按F9键抹黑： =SUMPRODUCT(1/{1;3;3;2;3;2}) 按Esc键返回： =SUMPRODUCT(1/COUNTIF(A4:A9,A4:A9)) 92 第 4 章 最受欢迎的函数与公式 按F9键抹黑： =SUMPRODUCT({1;0.333333333333333;0.333333333333333;0.5;0.333333333333333;0.5}) 按Esc键返回，在单元格处按回车看到结果：3。 分析 按F9键观察有时不太直观，回到工作表中继续看看。 =COUNTIF(A4:A9,A4:A9) 是多单元格数组，等同于 =COUNTIF($A$4:$A$9,A4)下拉的结果，也就是统计每个单元 格本身出现的次数，如1。 =1/COUNTIF(A4:A9,A4:A9) 是多单元格数组，等同于 =1/COUNTIF($A$4:$A$9,A4) 下拉的结果，也就是1/每个单元 格本身出现的次数。为了让数据更直观地转换成分数形式， 如2，出现3次就变成1/3，出现2次就变成1/2，出现1次就1。 1/3 1/3 1/3=3*(1/3)=1，1/N … 1/N=N*(1/N)=1，不管出现几 次，相加都等于1，如图4-67所示。 图4-67 辅助理解 最后将这些分数相加就得到不重复的数量，如3。 解读公式的一些习惯 把 区 域 改 小， 这 样 便 于 查 看， 如 将 A1:A1000改成A1:A3。 F9键配合组合键Ctrl Z或者Esc键不断地看 运算过程再返回，重复到理解为止。 输入公式后回到单元格查看运算过程， 这相对比较直观。 分析 第2、第3点可选，看你对公式的熟练 程度而言，如果不熟练选择 3，熟练的话选 择2。 关于计数就说到这里，回头再聊聊求和。 先来了解一下SUMIF函数的帮助，如 图4-68所示。 图4-68 SUMIF帮助 93 Excel效率手册——早做完，不加班 通俗语法： =SUMIF(条件区域,条件,求和区域) 实例以图4-65所示的2006年电脑配件销售一览表为例进行说明。 例子1：求汇总显示器的数量。 =SUMIF(B4:B22,＂显示器＂,D4:D22) 例子2：求数量大于30的总数量。 =SUMIF(D4:D22,＂>30＂) 第3个参数省略了，求和区域相当于D4:D22，公式的作用等同于： =SUMIF(D4:D22,＂>30＂,D4:D22) 例子3：求汇总数量在30至40之间的数量总和。 =SUMIF(D4:D22,＂>=30＂)-SUMIF(D4:D22,＂>40＂) [30,无穷大)跟[40,无穷大)的交集是[40,无 穷大)，[30,无穷大)减去交集[40,无穷大)刚好 是[30,40]这个区间，所以>=30的总和减去>40 的总和就是30~40之间的总和。 网友：这个没有数学基础真的不好理解，看来数 图4-69 求[30,40]区间的总和 学还是挺重要的。 卢子：很多东西都是相互借鉴的，学好数学有助于学好函数。 例子4：求最后字符为“河”且总字符为3个的营业部的总金额。 =SUMIF(A4:A22,＂河＂,F4:F22) 代表1个字符，“河”就是最后字符为“河”且是3个字符。 例子5：如图4-70所示，数字包含错误值，怎么避开错误值求和 =SUMIF(A:A,＂<9E 307＂) 9E 307是9乘以10的307次方，相当于Excel最大的数字，任何数字都比它小。数据的排序依 据为数字<文本<逻辑值<错误值，再大的数字都比不上错误值，所以可以避开错误值求和。 94 第 4 章 最受欢迎的函数与公式 例子6：如图4-71所示，多个区域求型号等于A03的总数量。 图4-70 数字包含错误值 图4-71 多个区域求型号等于A03的总数量 =SUMIF(A2:G10,＂A03＂,B2) =SUMIF(A2:G10,＂A03＂,B2:H10) 两个公式的效果是一致的，SUMIF的第3个参数会自动扩展区域，但不建议简写，那样会 导致运算速度变慢。 温馨提示 sum_range参数与 range参数的大小和形状可以不同。求和的实际单元格可使用 sum_range参数中左上角的单 元格作为起始单元格，然后包括与 range参数大小和形状相对应的单元格。但是，当 SUMIF函数中的range和 sum_ range参数不包含相同的单元格个数时，工作表重新计算需要的时间可能比预期的长。 网友：原来简单不一定好，简单是以付出效率作为代价的。 卢子：在使用函数的过程中还是使用标准用法为好，而在学习过程中多了解其他用法也好。 SUMIF跟COUNTIF函数有点相似，只要理解一个，要了解另一个就简单了。多条件求和 SUMIFS跟COUNTIFS也比较常见，有兴趣的话可以了解一下。 4.2.4 无处不在的IF 卢子：只要留心观察，生活中到处充满IF，我们每天都在跟IF打交道。 如果明天下暴雨，我就不去上班。 如果有你陪在我身边，我会很开心。 如果有网络，我就上网，否则睡觉。 如果你来了，请你喝功夫茶，否则我自己喝。
|
|
|
