Excel 教科书

    [日]吉田拳 著陈怡萍 译

    书名： Excel 教科书

    作者： [日]吉田拳

    译者： 陈怡萍

    书号： 978-7-5139-2341-5

    版权： 后浪出版咨询(北京)有限责任公司目录

    免责须知

    序言

    当你在用 Excel 工作时，也许根本都是在浪费时间

    取得资格证书，学习计算机课程，但实际操作还是不行？

    目标意识与“积极意义上的偷懒”想法

    第1章 使用 Excel 时，必须掌握的7个要点

    瞬间完成常规操作的方法

    知识量不足导致的致命弱点

    不要持续浪费时间

    提升 Excel 技能的3个必需项

    应对一般操作需掌握的67个函数

    必须了解的9个功能

    如何组合使用函数和功能

    数据只有4种类型1.数值

    2.文本

    3.日期·时间(序列值)

    4.公式·函数

    有效利用快捷键

    善用键盘

    重要快捷键一览表

    保存制作好的数据

    提高工作效率的关键

    务必设定自动保存

    如有错误操作，按 Ctrl+Z 返回上一步

    想暂停时按 Esc

    解除复杂功能，操作更简便

    取消修正文字

    取消超级链接

    取消将数字变为日期形式

    工作表的序号全部变成了数字

    消除单元格的错误提示

    取消输入时自动更正

    避免“粘贴选项”频繁出现的情况

    工作表标签无故消失

    出现异常状态的3个原因共享 Excel 文件时的注意点

    请注意文件保存形式

    保留1个新建工作簿的初期工作表

    确认“设置打印区域”

    第2章 如何在 Excel 中输入函数

    输入公式的操作步骤

    输入公式的4个步骤

    巧用函数，简化输入过程

    输入函数的5个步骤

    如何快速选择单元格范围

    熟练使用“引用”，快速计算

    活用单元格中的原始数据

    必须掌握的运算符

    复制带公式单元格时的陷阱

    利用“F4”与“”高效运用“绝对引用”

    无需记住错误值的种类与意义

    第3章 需要事先掌握的6个函数

    根据条件改变答案——IF 函数

    IF 函数的基础知识

    如何判定复数条件本月销售额——SUM 函数

    SUM 函数的基础

    计算连续单元格范围内的总和——ΣSUM

    如何求多个分开的单元格的总和

    如何提高乘法运算、字符串混合输入的效率

    计算客户名单的人数 ——COUNTA 函数

    “销售额”不仅是金额的总和

    把函数翻译成文字

    与 COUNT 函数的区别

    按照负责人分别计算销售情况 ——SUMIF 函数

    SUMIF 函数的基础

    不能只导出实数一览表

    参加名单中，有多少人出席 —COUNTIF 函数

    COUNTIF 函数的基础

    如何计算每名负责人员的销售件数

    输入商品名，自动显示价格 ——VLOOKUP 函数

    VLOOKUP 函数的基础

    4个参数的意义与处理流程

    用“整列指定”检查

    第4章 通过应用与组合，提升函数的威力

    判断单元格中是否包含特定的字符串如何计算世田谷区的客户人数

    如何搜索除世田谷区以外的区域

    消除重复数据的方法

    如何判断是否有重复

    选中并删除重复的单元格

    输入连续的数字

    使用“自动填充”功能

    使用“制作连续数据”功能

    如何连贯输入连续的数字

    在工作表中沿行方向输入连续的数字

    沿行方向输入大量 VLOOKUP 函数的方法

    批量修改单元格

    在粘贴的单元格中变为合适的数字

    无需在工作表外填入数据并完成连续输入 VLOOKUP 函数

    如何用 VLOOKUP 函数应对检索范围中竖列顺序的变动状况

    输入表与负责部分的项目顺序不同时

    分析 MATCH 函数的处理

    在有多个相同检索值的工作表中使用 VLOOKUP 函数的技巧

    VLOOKUP 函数会以最初达成一致的检索值单元格作为对象

    无重复状态下应加工后再处理

    是否能用 VLOOKUP 函数获得检索列左侧的数值？VLOOKUP 函数下，无法取得检索列左侧的数值

    什么是 OFFSET 函数

    将 OFFSET 函数与 MATCH 函数组合

    不显示错误值的技巧

    逐次修正错误会导致效率低下

    如结果有误，则返回空白值

    如使用2003之前的 Excel 版本的话，应该怎么做

    用 SUMIF 函数统计多个条件的方法

    追加带有统计条件的“工作列”

    要重视简单易懂

    第5章 Excel 中的日期与时间设置

    输入日期的基础操作

    Excel 中的日期为公历

    日期?时间实际为序列值

    处理时间数据

    时间数据的序列值为小数

    容易出现误差的地方

    如何输入正确的时间

    快速设置日期与时间

    避免数据变为日期形式

    经常更新工作表的日期想要将年、月、日分别输入不同的单元格时

    如何从日期数据中导出年、月、日

    熟练运用函数，快速设置日期和时间

    随时查看距截止日期还有几天

    如何导出除周末和节假日外的营业天数

    自动计算年龄

    如何从日期设置中导出星期

    第6章 快速处理字符串

    处理字符串的基础操作

    将单元格中的一部分字符串移至其他单元格内

    代表日期的8位数变为日期数据

    拆分字符串

    只从住址中选出都道府县

    如何从住址中区分都道府县与下级地方行政区

    如何从姓名中分别提取姓氏和名字

    整理数据

    如何判断字符串是否相同

    如何把全角字符转化成半角字符

    如何删除指定文字

    快速处理文字

    连续输入26个英文字母如何计算特定字符在单元格中的数量

    第7章 制作表格的秘诀如何提高整理日常资料的效率

    “用 Excel 工作”的本意是什么

    在单元格中输入数据，制作表格(制作数据库)

    利用已经含有数据的表格，制作新的资料

    利用“条件格式” 制作简单易懂的表格

    如何将比去年下降100%的单元格标红

    每隔一行标不同颜色，做成简单易懂的表格

    单元格内换行和添加框线

    单元格内的换行，以及换行后的数据

    消除单元格内换行的2个方法

    框线全部统一为同一种类

    活用“数据有效性”，避免无用功和错误

    使用“数据有效性”的2个好处

    如何限制单元格的数值

    如何修改错误提示框

    如何将输入模式更换为半角英数

    用序列输入快速改变引用范围

    在性别栏中自动输入性别的方法

    在工作表中预先制作选项一览

    为单元格或多个单元格范围定义名称使用名称，设定序列输入

    在序列输入时经常会增减选项

    掌握正确操作排序、自动筛选、数据透视表的条件

    建立数据库的4个条件

    排序的简单方法

    排序的详细方法

    无法正常排序的常见原因

    数据显示的特殊方法—用户自定义

    如何以千为单位表示大额金额

    如何在有公司名的单元格内自动添加“公启”二字

    在超过24小时的情况下，如何显示“25:00”

    如何运用“选择性粘贴”

    数值

    四则运算(加法、减法、乘法、除法)

    更换行列

    引用单元格的数值有变化，而计算结果没有改变

    快速处理复杂数据

    按单元格分割 CSV(逗号分隔值)数据

    如何同时删除多个单元格中的文字

    如何同时修正或删除多个相同模式的数据 ——查找与替换

    删除所有相同文字你必须知道的快捷操作

    同时打开多个窗口

    如何缩小格式较大的文件

    善用“冻结窗口”

    避免破坏表格格式

    保护整个工作表

    保持工作表中部分单元格无法修改

    关于打印的注意事项

    打印多页表格时，想要在每一页中打印标题行

    如何应对打印结果与画面显示有误差的情况

    第8章 掌握 Excel 操作的本质

    数据分析的基础

    分析的基础在于数字的“分类”与“比较”

    商务工作中分析数据的3个基础指标

    学会“用数据说话”

    必须清楚制作表格的目的

    将数据资料迅速转化为表格的技巧

    将新的统计标准追加添加到元数据中

    请勿使用数据透视表(Pivot Table)

    一旦建立格式，就可反复套用

    用最少的精力获得最大成果的帕累托1分析法为削减经费所付出的努力真的有意义吗？

    帕累托法则

    将帕累托分析法运用于制作图表的3个方法

    以“资金方块拼图”来理解公司的资金流动

    如何运用帕累托分析法

    平均值会说谎

    后记

    日常工作依然有很多需要改善的部分

    如何以 Excel 作为武器获得好评免责须知

    本书所记述的内容，仅以提供信息为目的。使用本书进行操

    作时，请各位读者务必根据自身的责任和判断来实施。关于使用

    这些信息的后果，出版社及著作者不承担任何责任。

    另外，软件本身若有版本更新，本书中的说明与功能内容、画面图等也会有所变化。购买本书前，请务必确认一下版本型号。

    请各位知晓上述注意事项后，再开始使用本书。

    关于商标、注册商标

    全书记载的产品名称，通常为相关企业的商标或注册商标。

    书中一律省略?、?等标记。序言

    当你在用 Excel 工作时，也许根本都是在浪费时间

    “每次用 Excel 制作表格都要花很长时间，真烦人。”

    “操作顺序太繁杂，总是出错。”

    “想要读本书学习如何使用 Excel，但就是不知道选哪本。”

    你是不是也经常有这样的烦恼呢？

    即使现在你仍然抱有“我现在多少会一点 Excel”这样的想法

    ，那么你花在使用 Excel 上的时间还是有99%的削减余地。想要

    提高使用 Excel 的效率，那么有很多技巧你一定要知道。

    取得资格证书，学习计算机课程，但实际操作还是

    不行？

    那么，要怎样做才能提升 Excel 的操作技巧呢？

    相信大多数人会选择以下两个方法，但事实上都没什么大用

    处。取得资格证书

    学习计算机课程

    我一直在举办使用 Excel 的学习研讨会。“我已经有了 Excel

    相关的资格证书，但是完全无法在实际工作中使用那些操作技巧。”许多学员都有着这样的烦恼，所以才来到我这求助。

    而且，客户公司的负责人们也经常找我们大吐苦水“我们聘

    用了持有资格证书的员工，但是他们根本不会用 Excel”“我们会

    要求员工去考相关证书，但他们的操作能力并没有得到提高”。

    那么，为什么会有这样的问题呢？这是因为，Excel 资格考

    试的考查范围是有关 Excel 功能和函数的知识，并且比较笼统和

    浅显。因此，这种考试的出题内容不一定是实际工作中所需要的

    技能。

    如果是由具有丰富的实战经验的人来开展的培训的话，我想

    参加计算机培训课也会对提升 Excel 的使用技巧有很大的帮助。

    但是，想要找到这样的学校绝非易事。虽然 Excel 培训教室里都

    有专业的老师，可是其中有些老师自身未必拥有丰富的实战经验

    ，他们只是参照培训班编辑的教材，按照课程计划和指导手册进

    行教学。在计算机培训学校，老师有时候也无法回答学员提出的

    问题。实际情况并不是老师们“不愿意回答”，而是由于老师的能

    力不足导致“他们无法回答”。

    我调查了某知名计算机培训学校的 Excel 课程内容，发现其

    课程安排如下：

    Excel 基础 90分钟×12次(2个月)

    Excel 应用 90分钟×12次(2个月)

    Excel 应用 90分钟×12次(2个月)

    Excel VBA 讲座 90分钟×13次(2个月)Excel 函数运用 90分钟×13次(2个月)

    Excel 商务 90分钟×10次(2个月)

    如今，许多工作繁忙的商务人士并没有很多时间到培训班学

    习 Excel 的操作技巧。当然，操作技巧确实重要，但我们需要的

    是短时间内能够迅速掌握的技能。不管怎么说，Excel 只是一种

    工具，并不能作为工作目的。

    目标意识与“积极意义上的偷懒”想法

    那么，我们该怎么做呢？

    为了回答这个问题，我执笔写下了这本书。此书的撰写目的

    ，就是为了让读者“在最短时间内掌握能够立刻实践的知识”。

    即使完全掌握 Excel 的各种功能与函数，也不能算是会熟练

    使用 Excel。更重要的是要有这样的目标意识：

    “使用 Excel 是为了做什么？”

    “想要制作什么样的商务文本，如何灵活运用 Excel？”

    还有这样的思考能力：

    “如何高效、轻松、无误地完成必须要的工作？”

    至今为止，我已在50家以上的客户公司开展了改善 Excel 操

    作的指导工作。同时，我会定期举办 Excel 学习研讨会，进行能

    够实际应用的 Excel 培训活动。总计有2000名以上的职场人士参加过我的授课。在此过程中，我也接到了很多咨询，在解决不同

    行业中的共同课题时，我经常听到咨询者这样说：

    “到现在我花了那么多时间究竟在干什么……”

    事实上，通过实践本书中介绍的方法，“仅用数秒就可完成

    原来要花30分钟的工作”“5分钟内完成之前需要花2天时间做的工

    作”，大幅缩短工作时间。不仅如此，由于省去了多余的步骤，也能够做到“不出错、确保成果的准确性”。

    如果要面对一项又花时间又麻烦的工作，我们不应该将耐性

    耗费在操作软件上面，而是应该考虑如何使这样的工作做起来更

    轻松，并且快速完成。希望读者朋友们都能抱有积极意义上的“

    偷懒”的想法。

    如果这本书能够给大家带来一些帮助，对身为作者的我来说

    ，真是无比的喜悦。第1章 使用 Excel 时，必须掌握的7个要点

    瞬间完成常规操作的方法

    知识量不足导致的致命弱点

    “我经常会使用别人制作好的 Excel 文件，可是我不知道表格

    里的内容的意义，只好糊里糊涂地做下去了……”

    我在帮助学员提高 Excel 的操作效率时，经常遇到这样的案

    例。即便表格中的内容是错误的，有些人也不会发现。而且不小

    心删除了数据，也不知道该如何恢复，也不会告诉其他人。而这

    么做的后果就是会导致无法分析重要的数据……诸如此类的“惨

    剧”时常发生。

    “处理 Excel 中的数据要花很长时间，只是做这件事，大半天

    就已经过去了……”

    这是每天都要加班到很晚的某个公司员工的例子。当我询问

    他经常加班的原因时，他是这样回答的：用 Excel 制作的客户名单中有几万个电话号码，要把原本用全角输入的数字一个一个改

    成半角，然后手动删除电话号码中的连字符。这样一来，每天都

    要加班4小时并且连续加3天左右。但是在我看来，只要稍花功夫

    ，用不上1分钟就能完成这个工作。

    像这样，由于不了解 Excel 的基础操作而浪费大量的时间，引发很多错误，降低工作效率的例子，真是不胜枚举。如果事先

    掌握简便的方法，1分钟就能完成原本要花几个小时的工作。如

    果这样的情况也发生在你的身上，你也会如此放任不管吗？

    虽然提高销售和沟通技巧也很重要，但是大多数情况下，我

    们需要通过 Excel 将“获得的实际成果”表示出来。虽然阅读“学会

    用数字说话”这类书也很有必要，但是不应为了计算数据而花费

    太长时间，也不应该在制作商务文本这件事上浪费过多的精力。

    迅速计算数字、制作商务文本，然后在最大程度上留出时间，对

    得出的数据进行思考、讨论进而付诸行动，这些才是作为职场人

    士最应该做的事。

    因此，对于现代的知识型劳动者来说，通过熟练使用 Excel

    并不只是单纯的制作表格，而是为了保证“工作”时间。使用 Exce

    l 的功能就能自动完成的简单工作就交给 Excel，我们必须将精力

    集中在只有人类才能做到的工作上。无聊且耗时较长的简单操作

    只会让你注意力涣散，频发小错误，降低工作的积极性。

    不要持续浪费时间

    ▲ 每天两个小时统计销售数据

    (2小时×20日=每月40小时)

    ▲ 每个月末花1周时间统计交通费

    (8小时×5日=每月40小时)

    ▲ 每周花5个小时制作提交给客户的数据(5小时×每月4次=每月20小时)

    如果利用 Excel 自动计算，每月花费在这些工作上的时间将

    缩短为3分钟。我曾指导过上述第3个工作的客户，有一天，他给

    我发来了这样的邮件：

    “原本我要花5个小时才能完成的工作……竟然2分钟就搞定

    了。2分钟啊。(笑)之前我每周都要花费的5个小时，到底都是

    在干什么……”

    每个月都要花40个小时的工作，瞬间就能缩短至5分钟，由

    此产生了39个小时55分钟的可利用时间。如果能把这个时间用在

    那些更具生产性的工作上，就会增加工作的价值，这样对公司来

    说也是一种贡献。如此一来，公司的收益提升，你也会得到更多

    的好评。

    反过来讲，如果没有注意到这种提高效率的方法，你每个月

    仍然会持续地浪费时间，同时还会产生相应的人员费用。这么一

    来，每月都会不断支付多余的人员费用，企业也绝对不会欣赏这

    样的人。提升 Excel 技能的3个必需项

    “在面试时被问到‘会不会使用 Excel’时，我真的没有信心回

    答‘会用’……”

    我经常会看到有人有这样的烦恼。确实，在招聘信息中，企

    业经常会写“能够熟练使用 WordExcel 者优先”，但是却没有写明

    “熟练使用”的程度。实际上，许多企业的招聘负责人对于“熟练使

    用 Excel”这句话的定义也不甚明了。

    那么，如果想要在任何情况下都能充满自信地说出“我会使

    用 Excel”这样的话，应该达到怎样的程度呢？

    想要提升 Excel 的操作技巧，必须要掌握“函数”与“Excel 的

    功能”。Excel 中有一些非常方便的函数与功能，可以大幅提高处

    理各种操作的速度。事先掌握都有哪些功能，在使用 Excel 工作

    时也会更加顺畅。

    应对一般操作需掌握的67个函数

    Excel 中总共有400多个函数，当然没有必要全部记住。那么

    ，我们需要掌握的函数是多少个呢？虽然根据职业的性质会有不

    同，但一般来说掌握30～50个就够了。根据我丰富的教学经验来

    看，应对一般的工作所需要掌握的函数为以下67个。

    SUMSUMIFSUMIFSPRODUCTMODABSROUNDROUN

    DUPROUNDDOWNCEILINGFLOORCOUNTCOUNTACOUN

    TIFCOUNTIFSMAXMINLARGESMALLRANKTODAYYEARMONTHDAYHOURMINUTESECONDWEEKNUMDATETI

    MEWORKDAYDATEDIFIFIFERRORANDORVLOOKUPHL

    OOKUPMATCHINDEXADDRESSINDIRECTOFFSETROWC

    OLUMNLENFINDLEFTMIDRIGHTSUBSTITUTEASCJISUP

    PERLOWERPROPERTEXTCODECHARCLEANPHONETIC

    CONCATENATEISERRORREPLACETRIMVALUENETWORK

    DAYS

    当然，没有必要立刻记住这些函数。在第3章中我会给大家6

    个函数，只要记住这6个基础的函数，就能够大幅提升 Excel 的操

    作效率。

    另外，这里没有提到通常被视为基础函数之一的 AVERAGE。大家平常过于依赖计算平均值这一操作，这样做是想给各位敲

    一下警钟。请记住“平均值不可信”这一风险，如果想要计算平均

    值，需要用总数除以参数的个数。

    必须了解的9个功能

    与函数同等重要的就是关于 Excel 功能的知识。与函数相同

    ，这些功能也分为许多种。而日常工作中经常用到的重要功能主

    要有以下9个。

    条件格式(【开始】?点击【条件格式】)

    例如，如果对比去年销售额降低了100%，就给这一数据标记

    颜色。这是根据单元格的数值设定单元格格式的功能。

    数据有效性(【数据】?点击【数据有效性】)

    如果要多次输入同一数值，可在下拉菜单中选择这一功能。

    同时，此功能还能限制单元格的输入值，防止输入错误。

    排序(【数据】?点击【排序】)例如，在分析客户资料时，最基础的操作就是按照销售额从

    高到低排序。

    自动筛选(【数据】?点击【筛选】)

    仅选中符合条件的数据。

    数据透视表(【插入】?点击【数据透视表】)

    这是计算数据总数时的非常有用的功能。但是，如果是在定

    期更新的资料中进行此项操作，反而会降低工作效率，需要注意。

    自动填充(含有连续数据起始数据的单元格，拖拽填充)

    这个功能可以快速输入数字、星期等连续的数据。

    保护工作表(【审阅】?点击【保护工作表】)

    这个功能是为了防止误删输入的函数，保护数据。

    查找与替换(快捷键 Ctrl + H )

    查找特定的数据，并且能够统一修改或删除文字的重要功能。如果只是用于查找，可以使用快捷键 Ctrl + F 。

    定位(快捷键 Ctrl + G )

    能够一次性选择“附有注释”“空白内容”等符合条件的单元格

    ，一并处理。

    如何组合使用函数和功能

    面对复杂的操作任务，应该寻找“有没有能够轻松解决的功

    能”，掌握方法并熟练运用。最开始不需要记住所有东西，有不

    明白的地方，可以查资料或者请教别人。

    掌握全部的 Excel 函数和功能并没有任何意义，思考如何将其进行组合，并组织其结构是最重要的。而且，这一过程本身就

    非常有意思。得到预期的结果后，不仅心情会变好，工作也会变

    得很有趣。

    另外，Excel 是一款非常直观的软件。在画面上方菜单栏里

    能轻松找到给单元格填充颜色、画线的功能。因此，我没有刻意

    说明这些在阅读本书的过程就能够掌握的一些基本操作。数据只有4种类型

    一般来说，即便说熟练使用 Excel，“在正确的单元格内输入

    正确的内容”才是最基础的操作。那么，在一般单元格中输入的

    数据都是什么呢？

    需要输入单元格的数据大致可分为4种类型。只要掌握这4种

    数据类型，就能解决大部分的难题。

    1.数值

    0、1、2、3……用于计算、统计。

    关于数值，请注意这一点：“计算有小数点的数值时会出现

    误差”。在 Excel 中无法正确算出小数点以后的数据这一点会令人

    感到意外。比如说，单元格内输入“0.1”，却并不准确指代“0.1”本

    身这个数值。关于这一问题，我会在后面详细解释。(参照 P134)。

    2.文本

    除数值之外的汉字、阿拉伯数字，以及其他符号等。

    关于此项请注意，如果在函数状态下使用文本，请用双引号

    (“”)圈起来。

    【例】=IF(A1>=80,A,B)

    作为数值的80直接输入到表格即可，而输入文字项“A”与“B”

    则需要用双引号标示。

    3.日期·时间(序列值)

    如果想在 Excel 中正确输入日期，例如“201411”这样，公历

    公元纪年法的年、月、日分别用“”(斜线)隔开，以半角格式输

    入。

    输入时间时需要则像“13:00”一样，将小时与分钟用“:(冒号)”隔开，以半角格式输入。

    像这样表示日期、时间的数据形式实际上就是“序列值”。由

    此可见，在 Excel 中日期和时间也可看作是数值的一种。

    另外说明一点，Excel 可表示的日期范围是从190011到9999

    1231。

    4.公式·函数

    在 Excel 中，可以计算和处理各类数据。为此，我们需要在

    单元格里输入公式与函数。因此，多掌握 Excel 的实践技巧和函

    数方面的知识十分重要。

    输入函数、公式时，全部要用半角英文，从等号(=)开始

    输入。有效利用快捷键

    善用键盘

    不只是 Excel，操作电脑一般都需要使用键盘和鼠标。只用

    鼠标时，不得不逐步点击各项菜单，如果是用键盘的话，一次就

    能够完成这些操作。这就是“快捷键”功能。

    例如，“设置单元格的格式”这一步，我发现很多人是将光标

    移至单元格上，点击鼠标右键，再选择“设置单元格格式”。接下

    来我要教给大家一个方法：只需按快捷键“ Ctrl + l ”，立刻就

    能打开“设置单元格格式”。

    【设置单元格格式】，按“ Ctrl + l ”立刻打开诸如此类，不使用鼠标就能完成的操作数不胜数。这样的方

    法掌握得越多，操作就会变得越来越顺畅。

    还有前面提到过的“查找和替换”这两个功能。

    “想删除 Excel 表格中电话号码中的连字符。”

    “想将半角格式的连字符统一替换为全角。”

    使用快捷键“ Ctrl + H ”就能立刻完成。如果使用鼠标的情况下，至少需要完成以下3个步骤(2010

    版本)。

    点击开始菜单?找到功能区最右侧的“查找和选择”并单击?

    点击“替换”

    即使事先知道【开始】菜单中有“查找和替换”这个选项，顺

    利找到的话最快也要花3秒钟。按照熟悉程度的不同，在这上面

    花费的时间可能有3倍的差。要是动作慢的人，甚至会花5秒钟以

    上。我们的日常工作需要熟练使用电脑，因此一定要熟练使用快

    捷键。

    另外，在想要“复制粘贴”时我们可以使用快捷键 Ctrl + C

    (复制)和 Ctrl + V (粘贴)。即便如此简单的操作，有人还

    是会使用鼠标右键来完成，这也会导致工作速度变慢。在速度上

    ，甚至可能会与其他人产生10倍以上的差距。

    重要快捷键一览表只要掌握几种常用的快捷键，操作速度就能提升10倍，更能

    掌握 Excel 的基础操作步骤。如果想要迅速提升 Excel 的操作效

    率，首先要了解以下快捷键操作。

    Ctrl + l ?打开“设置单元格格式”

    Ctrl + S ?保存。要经常保存文件

    Ctrl + Z ?撤销，回到上一步。操作错误时，请冷静地按

    下这个快捷键

    Ctrl + F ?查找工作表上或者文件内的字符串(检索功能)

    Ctrl + H ?一次性修改或删除多个字符串(替换功能)

    Ctrl + Enter ?一并输入多个单元格

    Ctrl + D ?复制上一个单元格的内容

    Ctrl + R ?复制左边单元格的内容

    F4 ?设定公式的绝对引用

    Ctrl + F2 ?在 Excel 2007之后的版本中，此快捷键可以显

    示打印预览

    Ctrl + F11 ?添加工作表

    Alt + = ?运行“自动求和”

    Ctrl + C ?复制

    Ctrl + V ?粘贴

    Ctrl + X ?剪切

    Alt + Enter ?单元格内换行

    Ctrl + 空格 ?选中活动单元格所在的整列Shift + 空格 ?选中活动单元格所在的整行(活动单元格

    为半角英文)

    Ctrl + - (减号)?删除单元格、行、列

    Shift + Ctrl + + (加号)?插入单元格、行、列保存制作好的数据

    提高工作效率的关键

    “电脑死机了!花了一上午时间做的表格全都消失了……”

    我经常能在推特上看到有人这样哀嚎。于是，我通过推特上

    的自动投稿功能，每天发布下面的文章：

    “我已经说过不止一遍了。使用 Excel 时，请注意设置自动保

    存，操作过程中也要经常按 Ctrl + S 实时保存。这样是为了保

    护你花费宝贵时间制作的 Excel 表格。请一定要这样做。我每天

    都能够看到抱怨资料不翼而飞的推文。”

    电脑处于不稳定的状态，制作表格时电脑突然死机导致做好

    的资料全部消失，这种悲剧也并不少见。或者在关闭文件时，最

    后鬼使神差地按下“不保存”，所有做好的资料顿时全部消失，我

    也经常听到有这种惨剧发生。

    而这些事情，其实可以用非常简单的方法解决。那就是经常

    按快捷键 Ctrl + S ，实时覆盖保存文件内容。

    即使掌握了很多 Excel 的功能和函数，并且想要借此来高效

    地完成工作，但如果没能保存做好的资料那就是鸡飞蛋打。突发

    的死机、资料消失这种情况通常都是突然发生的。其结果就是，花了几十分钟、几小时甚至大半天做的表格全部消失。如此一来，我们就不得不重新制作，会使得工作效率大大降低。因此，如

    果注意经常保存文件，即便出现电脑死机这样的事，也能将损失

    程度控制在最低。

    我偶尔也会看到这样的意见：“有时需要管理不同的版本，并不能随意保存。”但是，如果想要分别保存不同版本的文件，可以选择“另存为”，每次保存时可以在“文件名”中添加日期。只

    要不是做应用开发软件等相关的工作，一般我们在用 Excel 完成

    工作时，又有多少情况下需要我们保存不同版本的文件呢？

    我们应该最优先考虑的是不要丢失制作完成的内容。最简单

    的解决办法就是经常按 Ctrl + S 保存文件。

    务必设定自动保存

    同时，也要设定自动保存，并且间隔时间尽量要短，最好为

    一分钟。设定自动保存后，在电脑发生异常被迫终止操作时，会

    恢复到上一次保存时的状态。因此即使 Excel 被强制关闭，等到

    下次打开文件时也能自动恢复数据。也就是说，如果事先将自动

    保存的间隔时间设定为一分钟，遇到这种情况就能恢复到最新保

    存的版本了。

    自动保存的设定方法如下所示：

    【文件】?【选项】?点击【保存】选项卡?在【工作薄保

    存】中勾选“保存自动恢复信息”?保存间隔时间设为1分钟通过这一方法，再也不用担心做好的表格突然丢失了。

    这一点，可以说是提高操作效率的最基本的前提。

    如有错误操作，按 Ctrl + Z 返回上一步

    “天哪，删了万万不能删掉的数据!”

    平常工作中您一定经常出现这样的失误：误按 Delete 键、手忙脚乱中不小心按错……

    碰到这种状况，请一定要冷静地按下 Ctrl + Z ，这是返回

    上一步的快捷键。如果一不小心操作失误，请先试试这个办法，大多数情况都有效。

    但如果删掉的是工作表，那么就没有办法恢复。只好先选择

    不保存文件，在关闭文件后再重新打开，这样就能回到最新保存

    的版本了。因此，注意经常保存文件很重要。

    想暂停时按 Esc

    在单元格里输入内容时，有时候需要暂停输入。这时候如果

    先按下 Enter 键的话，输入内容就确定了，想要删除的话，需

    要按下 Delete 键。

    而我只要按下 Esc ，就能马上取消输入操作。

    另一方面，不小心打开了功能窗口时，除了按窗口右上方的

    ×，也可按 Esc 键直接关闭。解除复杂功能，操作更简便

    “明明打的是‘hsi’，电脑给我改成了‘his’……能不能别帮倒忙

    啊!”

    “每次打英文单词，第一个字母都会被改成大写，有什么解

    决办法呢？”

    “如果输入 URL 网址，会自动变成超级链接形式，真让人郁

    闷……”

    诸如此类，Excel 里有很多明明不想这样做却会被电脑自动

    修正的功能，而且不清楚这些功能的使用方法，实际遇到的时候

    确实会觉得烦躁。如果每次都处理相同的问题就会浪费大量的时

    间。想要避免这种事情发生，需要事先了解如何解除这些功能。

    这样就能摆脱负面情绪，更快速地完成工作。

    其实，在 Excel“选项”中可以更改大部分功能的设置。

    取消修正文字

    这一功能称为“自动修正”，会修改输入到单元格中的字符串。如果将其关闭，电脑就不会自动修改你所输入的文字了。

    【文件】?【选项】?【Excel 的选项】?【校对】?点

    击【自动更正选项】? 【自动更正】选项卡中，取消勾选【键入时自动替换】另外，根据需要，也可以取消“句首字母大写”“英文日期的第

    一个字母大写”等这些自动修正的设置。

    取消超级链接

    【自动更正】中的【键入时自动套用格式】选项卡，取消勾

    选【Internet 及网络路径替换为超链接】，之后输入网址与 URL

    也不会自动改为超级链接形式了。取消勾选【Internet 及网络路径替换为超链接】

    此项也是被大家“诟病”的“闲杂功能”之一，没有特殊需要还

    是取消这个设置吧。

    取消将数字变为日期形式

    在 Excel 中输入【1-1】【14】这样的格式时，电脑会将它

    变为日期形式。如果想正常显示输入的内容，有必要采取以下列出的对策。

    最开头加上单引号(')

    【设置单元格格式】中，将表示形式设为【文本】后再输入。

    与其坐在电脑前生闷气，不如试着找到取消不必要的功能的

    方法。

    工作表的序号全部变成了数字

    【Excel 的选项】?【公式】?【使用公式】?设置【R1C1

    引用样式】

    这样设置后，工作表序号将自动变为数字。因此，如果你发

    现工作表中的序号都成了数字，就可以检查一下这个设置。

    消除单元格的错误提示

    单元格公式等出现问题时，单元格左上角会出现带颜色的三

    角形，表示此单元格有错误。可这个“问题”，有可能是有意为之

    的，这时候就不需要 Excel 自动纠错。那么，我们可以通过以下

    方法关掉纠错功能。

    【Excel 的选项】?【公式】?点击【错误检查】?取消勾

    选【允许后台错误检查】

    取消输入时自动更正在连续输入整列的内容时，如果一开始输入了与上一行单元

    格的文本同样的内容，单元格会将接下来的内容自动替换为相同

    文本。当然有时这样很方便，但不需要时又很麻烦。这一功能可

    通过以下方法取消。

    【Excel 的选项】?点击【高级】?取消勾选【为单元格值

    启用记忆式键入】

    避免“粘贴选项”频繁出现的情况

    进行复制粘贴的操作时，会自动出现【粘贴选项】，如果你

    不经常使用这一功能的话也会觉得麻烦。如果想取消自动显示，可以按照以下步骤操作。

    【Excel 的选项】?【高级】?点击【剪切、复制、粘贴】

    取消勾选【粘贴内容时显示粘贴选项按钮】

    工作表标签无故消失

    偶尔会出现这样的状况：工作表标签完全不显示。这时请确

    认以下选项。

    【Excel 的选项】?【高级】?点击【此工作簿的显示选项

    】?勾选【显示工作表标签】

    除这项设置外，在【Excel 的选项】中还可以进行各种设定。各位读者也确认其他设置，相信一定会有不少发现。

    出现异常状态的3个原因“用方向键移动单元格，单元格根本没动，而画面却在移动……”

    “用键盘输入文字后，不知道为什么变成了数字……”

    “想要改写已经输入的文字，却改写了后面的文字……”

    在使用 Excel 的过程中，偶尔会出现上述这些莫名其妙的情

    况。这些状况让人感到非常困惑，如果没有查出原因就盲目地更

    改设置的话，可能会浪费许多时间。

    以上这些现象，分别是由于按下了“滚动锁定”“数码锁定”“插

    入键”导致的。发生的原因大部分如下：

    “不小心按到了这些键。”

    根据电脑的型号不同可能会有差异，但是如果发生了上述问

    题，请试着在键盘上找找这些按键。

    滚动锁定(Scroll Lock)

    数字锁定(Number Lock)

    插入模式(Insert)

    只要再次按下这些按键，就可以解除当前状态，有时也可能

    是同时按下了功能键。共享 Excel 文件时的注意点

    有时，制作好的 Excel 文件并不仅限于自己使用，而是经常

    会以邮件附件的形式发送给同事、顾客和客户。因此，为了让接

    收方能够顺利地打开 Excel 文件，我们在发送文件时需要做好准

    备。虽然这似乎是微不足道的小事，但如果连这些地方都考虑不

    到的话，我想这样的人也是无法做好工作的。

    请注意文件保存形式

    “我的电脑打不开别人发过来的 Excel 表格……”

    Excel 文件有几种保存形式。使用 Excel 2007以后的版本保存

    文件时，初始设定为“Excel 表格”，以“.xlsx”为扩展名。关于这个

    保存形式，有几点需要大家特别注意。

    Excel 2003之前的版本，无法打开以“.xlsx”为扩展名的文件

    虽然相比以前已经大幅减少了，但如今还是有不少企业在使

    用 Excel 2003之前的版本。

    假设，我们以邮件附件形式给客户发送对方需要的 Excel 文

    件。这时候，如果客户的 Excel 软件版本为2003版，而你发送的

    文件扩展名为“.xlsx”的话，客户就无法打开该文件。这样只能“重

    新发送”。

    “.xlsx”形式的表格无法保存宏

    Excel 中有一个叫作“宏”的功能，可以自动处理数据。直接保存“.xlsx”的文件，其中如果包含宏，在想要保存时会出现以下

    对话框。

    按【是】，不启用宏来保存

    此处若一不留意按下【是】，很容易发生做好的宏消失不见

    的惨剧。

    为了避免这样的情况出现，请将 Excel 文件的保存形式预先

    设置为“Excel 97-2003文件(.xls)”。如果不需要处理超过65536

    行的数据，在实际工作中用这种版本的文件来保存就足够了。具

    体请按以下顺序设定。

    【文件】?【Excel 的选项】?点击【保存】?将【文件保

    存格式】下拉选项改为【Excel 97-2003文件(.xls)】

    【文件保存格式】设置为【Excel 97-2003文件(.xls)】用的不是2003以前的版本，又要处理65536行以上的表格数

    据，选择【Excel 启用宏的工作表(.xlsm)】也可以保存。

    保留1个新建工作簿的初期工作表

    通常来说，新建 Excel 文件时，最初的工作表数量为3个(E

    xcel 2013版开始，基本设定为1个)。这样一来，假设你把表格

    数据输入第1张工作表中，以邮件附件形式发送给了同事、上司

    或客户。对方收到后，一打开发现总共有3张工作表，然后很可能会逐一去检查第2张、第3张工作表里是否有内容。

    “为什么要给我发送空白的工作表……”

    为了不引起不必要的误会，希望各位读者要注意删掉多余的

    工作表。

    也许这件事看似很简单。但还是希望大家对他人抱着“不给

    别人添麻烦”的态度去做这件事。

    可是不管怎么说，每次都要特意删掉多余的工作表也很麻烦

    ，并且也容易忘记。因此，请大家按照以下步骤设置，在新建 Ex

    cel 表格时设定为保留1个工作表。

    【文件】?【Excel 的选项】?【常规】?点击【新建工作

    薄时】?【包含的工作表数】设定为1后点击确定

    【包含的工作表数】设定为1有时候我也会听到有人这样问：“大多数情况下使用 Excel 文

    件时都会变成3个以上工作表，一开始就设定为3个不行吗？”针

    对这一疑问，我想反问一下：

    “如果最终还是少于3个工作表的话，不仅需要特意删除，还

    有可能忘记删除，留下这样的隐患有什么好处呢？”

    如果想要更多的工作表，使用快捷键 Shift + F11 就能立

    刻追加。并且在 Excel 2013版中，初始工作表数量已自动设定为1

    个。“一开始就觉得工作表有3张比较好”这种想法也没有意义。

    实际上，有非常多的朋友和我说：“幸亏知道了这个方法”。确认“设置打印区域”

    “用 Email 发送 Excel 时，请先好好检查一下‘设置打印区域’

    再发送。”

    在我还是一个上班族的时候，我的领导不止一次这样提醒过

    我。我认为打印出来是1张完整的页面，但实际打印出来却足足

    打了3页。我经常被领导训斥“太浪费打印纸了!”

    用 Email 发送 Excel 文件时，收件人有可能会打印 Excel 文

    件。如果你没有事先确认“设置打印区域”，本来只想打印在1张

    纸上，而实际打印区域却是2页，造成了纸张的浪费。当然了，需要打印的人也有必要事前进行确认，但还是需要制作文件的人

    事前设定好打印范围。

    比如，你制作了一个比较大的表格。如果想完整地将这张表格横向打印在1张 A4纸上，具体的设

    置方法如下。

    【页面布局】?【纸张方向】点击【横向】? 【视图】?点击【分页预览】

    呈现以下画面可以看到，蓝色虚线就是转换页面的地方，这样直接打印的

    话，这张表将被打印在4页纸上。

    用鼠标拖拽蓝色虚线，即可调整设置为1张打印页面。

    用鼠标拖拽蓝色虚线，调整为打印在一张纸上虽然只是偷懒了一下，但是这样不仅会给别人带来麻烦，也

    会给自己增加不必要的工作。请一定要提前设置好打印页面。第2章 如何在 Excel 中输入函数

    输入公式的操作步骤

    输入公式的4个步骤

    在此，我们一起来看一下 Excel 的基础——输入公式。虽然

    这看似是很简单的工作，但实际上从这里开始就能拉开工作效率

    的差距。

    比如做加法，首先在 Excel 中输入“=”，然后用加号将数字或

    单元格连结在一起。基本步骤如下：

    用半角模式输入(若发现当前为全角模式，请务必切换至

    半角)。

    从“=”开始输入。

    用鼠标或光标选择需要计算的单元格，输入公式。

    按回车键确定。

    比如，A1单元格中的数值为1，在某单元格中输入=A1+1

    按回车键确定后，算式答案自动计算为2。这就是“公式”。然后，按公式计算得出的结果显示在单元格中的值，被称为

    “返回值”。

    巧用函数，简化输入过程

    再比如，从单元格 A1到 A5，纵向分别输入1、2、3、4、5。求这5个单元格数值的总和最直接的方法就是输入以下公式：

    =A1+A2+A3+A4+A5

    将这一公式输入单元格 A6，会得到答案“15”。

    但是，这种方法非常麻烦。这次举的例子只涉及5个单元格

    ，可以使用这个方法，如果要用到100个、1000个，如果只是计

    算这些数据，算好的时候可能太阳都要下山了。

    为了提高这项操作的效率，Excel 中有一个功能为“多个单元

    格求和”，就是 SUM 函数。打个比方，在单元格 A6中输入以下

    公式，即可求得单元格 A1～A5的总和。

    =SUM(A1:A5)

    如果是要做乘法，则可使用 PRODUCT 函数，以同样方式整

    合计算。

    =PRODUCT(A1:A5)

    所以，无论是计算 A1～A100的数值，还是计算到 A1000、A10000，只要运用函数，就能一次性输入，快速完成计算。

    =SUM(A1:A100)

    =SUM(A1:A1000)

    =SUM(A1:A10000)

    诸如此类，各种计算或者文本处理加工等，Excel 设计了“函数”这样的公式体系，专门用来简化用户在使用 Excel 过程中所涉

    及的复杂操作。

    Excel 中的函数功能十分强大，或许有些功能大家一辈子都

    不会用到，所以完全没有必要全部记住。找出自己需要掌握的函

    数，并且熟练运用才是最紧要的。在 Excel 使用方面，由于无知

    而招致损失的第一点，就是缺乏函数的相关知识，这样说也完全

    不为过。

    输入函数的5个步骤

    输入函数时，在单元格中一定要先在半角英文模式下输入等

    号(=)，基础操作如下。这种表示函数结构的，叫作“格式”。

    【格式】

    =函数名(参数1，参数2……)

    格式中的“参数”是函数必须的构成要素。如果存在多个参数

    ，就用逗号(，)隔开，从第一个开始按顺序称作参数1、参数2……比如运用 IF 函数的话，函数构成如下：

    =IF(测试条件，真值，假值)

    这一情况中，“测试条件”为参数1、“真值”为参数2，“假值”

    为参数3。不同的函数，指定不同的参数会得出怎样不同的结果

    ，记住这些内容，其实也是在慢慢提高 Excel 的操作技能。

    Excel 2007之后的版本中，在输入函数的过程中会出现候补

    名单，运用 TAB 键即可快速输入函数。在此以 SUM 函数为例，请大家看一下输入函数的具体步骤。

    半角模式下输入等号(=)。? 在输入需要的函数的过程中会出现候补名单。

    用光标键从候补菜单中选择要使用的函数名，用 TAB 键

    确定(此操作可补充输入函数名称，也会显示前括号)。? 括号中输入参数。

    最后输入右括号，按 Enter 键或 TAB 键确定。

    按下 Enter 键后，活动单元格自动向下移动一个；按下 TA

    B 键后，活动单元格自动向右移动一个，输入后续的内容十分方

    便。

    如何快速选择单元格范围

    在平时的教课过程中，当我提出“选中某一范围的单元格”这

    一要求，会有一大部分人无法顺利做到。选择单元格范围是与在

    单元格中输入内容同等重要的操作，我们需要理解和掌握操作的

    种类与区别。

    选择单个单元格

    只需将光标移动到目标单元格并点击，或者可以利用键盘上

    的方向键选择单元格。

    选择多个单元格的范围

    点击该范围的起始单元格，用鼠标拖曳至终止单元格。这就

    是“拖拽”操作。另外，也可以同时按下 Shift 键与方向键，然后按下方向

    键，扩大单元格的选择范围。

    选择数据连续输入的单元格范围

    为了选择连续输入数据的单元格范围，可以同时按下 Shift

    + Ctrl +方向键，这样就能恰好选中目标单元格的范围。熟练使用“引用”，快速计算

    活用单元格中的原始数据

    快速输入数据十分重要，但如果能利用单元格里原始数据，就可不用逐个输入。为此，我们可以使用“引用”功能。

    比如说，在单元格 A1中输入价格，单元格 B1中要计算出此

    价格加上消费税的总和。需要在 B1中输入以下公式(假设消费

    税为8%)。

    =A11.08

    B1中的这个公式，是取 A1中的数值进行计算。也就是说，B

    1是在“引用”A1的值。

    “引用单元格”，可以理解为某个单元格对其他单元格做以下

    的操作：

    “向此单元格看齐”

    “提取此单元格的数值”

    “使用此单元格的数值”

    想要确认输入的公式引用了哪个单元格，则可以选择此格式

    所在的单元格，按下 F2 键。引用的单元格会被有色框线圈起，易于辨认。专栏 “从属单元格”和“引用单元格”

    在 B1中输入“=A1”，意思为“B1引用 A1的值”。换句话说，A

    1是 B1引用的目标，因此 A1是 B1的“从属单元格”。偶尔也会看

    到反过来的说法，“B1是 A1的引用单元格”。

    其实这种说法并不严密。正确点来讲，A1是 B1的“引用单元

    格”，B1是 A1的“从属单元格”。

    关于这一点，看到 Excel 界面的“追踪引用单元格”功能就明

    白了。图标上的箭头指向“对现在择取的单元格数值产生影响的

    单元格”。举例说明，选择 B1后，在“公式”选项卡中点击“追踪引

    用单元格”，会出现图中的箭头。

    点击【追踪引用单元格】后的画面

    图示蓝色箭头表示“B1的引用单元格为 A1”。

    反过来，如选择 A1后点击“追踪从属单元格”，会出现下图中

    的箭头。

    点击【追踪从属单元格】由于这两个用词比较容易引起误解，特在此稍作解释。

    必须掌握的运算符

    引用单元格中的数值可用于运算，或连接文本。其使用的符

    号，叫作“运算符号”。接下来我将逐个解说。

    四则运算

    加法符号“+”、减法符号“–”、乘法符号“”(星号)、除法符

    号“”(斜线)。

    例如，想要将 A1中的数值与 B1中的数值做乘法。在目标单

    元格中输入以下内容并按回车键确定。

    =A1B1

    文本运算符

    合并计算单元格数值时使用，以“”连接，即为文本运算符。

    例如，想合并 A1的数值与 B1的数值时，可以这样输入：

    =A1B1输有此公式的单元格最后显示的结果，就是 A1与 B1的合并

    数值。

    比较运算符

    在 Excel 中，通过使用功能与函数，依照单元格数值，可做

    拆分或变化处理。

    例如，以“考试分数80分以上为 A，79分以下为 B”作为条件

    ，根据考试分数(条件)在单元格中输入不同的结果(判定)。

    这种“在特定情况下”来设定条件时，使用的就是“比较运算符”，基本上等同于学校里学过的“等号”和“不等号”。

    >?左大于右

    
    >=?左大于或等于右

    <=?右大于或等于左

    =?右和左相等

    <>?左右不相等

    例如，利用第3章中会讲到的 IF 函数，以“如果 A1中的数值

    大于100则为 A，否则为 B”为条件做计算的话，可在目标单元格

    内输入以下公式。

    =IF(A1>100,A,B)

    此处出现“A1>100”(意为 A1的值比100大)这样的条件设置

    ，就是“逻辑运算”。

    复制带公式单元格时的陷阱有时候，我们需要将公式复制到其他单元格中。这时，如事

    先没有掌握相关知识，就会浪费一些不必要的时间。例如，下图

    是不同地区的分公司的销售额一览表，其中，处理“结构比率”一

    栏时，请输入正确的公式。

    各分公司的“结构比率”，是将各个分公司销售额除以全公司

    的销售额计算得出的。因此，首先请在 C2中输入“=B2B11”。

    在单元格 C2输入=B2B11※选择单元格 C2?输入等号(=)?点击单元格 B2?输入

    斜线()?点击单元格 B11

    详细的内容我会在第七章介绍，这里只稍微提一下。在“设

    置单元格格式”中，可以预先将 C 列的表示形式设为百分比，那

    么就可以知道北海道分公司在整个公司的销售额中所占的比例。

    接下来，同样在 C3～C11中输入计算占比的公式，就可以得

    出所有分公司的销售额在整体中所占的比例。当然，如果你在单

    元格中逐个输入相同的公式，做完的时候太阳都下山了。

    而且如果你这么做……还会出现这样的乱码：

    表格中显示“DIV、0!”单元格中出现“DIV0!”，似乎计算进行得并不顺利。

    那么到底出了什么问题？我们选中单元格 C3，按下 F2 键。

    【 F2 键的功能】

    使活动单元格处于可编辑状态。

    选中的活动单元格内容引用自其他单元格时，用有色框线显

    示被引用的单元格。

    于是，所选单元格的引用单元格如下图。

    选中单元格 C3，按下 F2 键除数引用了正确的单元格(B3)，被除数本应引用 B11中的

    数值，但却引用了单元格 B12的数值。就是说，指定被除数时出

    现了偏差。

    为什么会发生这种情况？

    原来，将最初输入的公式向下复制的同时，所引用的单元格

    也一同被“拖拽”向下移动。

    一开始在 C2中输入“=B2B11”，其实是引用了 B2和 B11数值。这是因为从单元格 C2的位置关系来看，系统将 B2和 B11这两

    个单元格分别当作为“用于计算的分子与分母的单元格”。从含有公式的单元格 C2来看，与单元格 B2和 B11的位置关系如下：

    B2?自己所在处向左1格的单元格

    B11?自己所在处向左1格、向下9格所到达的单元格

    而且，这种位置关系在被复制的单元格里也是同样。直接拖

    动复制，向下1格的 C3如先前画面所示，会自动变为“=B3B12”。

    作为除数的 B3，在含有公式的单元格 C3看来，就是“向左1

    格的单元格”，选中时会保持这种识别也没有问题。但是，关于

    被除数的话，在 C3看来引用的是“向左1格、向下9格的单元格”，也就是 B12。而 B12是一个空白单元格，那么这个算式就是 B3数

    值除以一个空白单元格数值……换句话说，被除数其实是0。

    数学中最基本的常识就是被除数不能为0。因此，单元格 C3

    最终表示的结果就会是“DIV0!”这样的乱码。

    像这样，在复制包含公式的单元格作为引用时，结果有所偏

    差的状态叫作“相对引用”。

    利用“F4”与“”高效运用“绝对引用”

    那么，应该怎么操作才能在向下拖拽复制公式的时候保持被

    除数固定不变呢？答案就是“绝对引用”。请试着用以下方式输入

    公式。

    在单元格 C2输入公式=B2B11。

    点击单元格 B11，按 F4 。可以看到，以 B11为引用单元

    格后，出现了符号。? 从单元格 B2开始拖拽至第11行，这次并没有出现错误，能够正常计算。如果不知道这个方法，就需要手动输入每一个被除数，这样

    就会浪费很多时间。

    顺带一提，指定引用单元格后，多次按下 F4 键，符号的

    所在位置也会发生变化。

    A1?固定列和行

    A1?固定行

    A1?固定列

    A1?不固定位置

    即使知道“符号为绝对引用”，还是有很多人不清楚按 F4可以输入这一操作方法。请大家一定要善用 F4 键。

    如需纵向、横向复制含有公式的单元格时，一般会有两种需

    求：只固定行、只固定列。这时，可用上述方法切换。

    无需记住错误值的种类与意义

    除了前文中提到的“DIV0!”，还有“NAME?”、“NA”等在

    单元格里输入函数后出现的各种难以理解的内容。这些是“错误

    值”，表示你当前输入的函数中出现了问题或偏差。

    错误值的种类有许多，但是不需要特意记住它们所表示的含

    义，只要会判断以下内容就足够了。

    NA?(VLOOKUP 函数的)检索值不存在

    DIV0!?以0位被除数

    REF!?引用单元格已被删除

    在错误值的处理问题上，最重要的是掌握设定不显示错误值

    的方法(请参照 P120)。第3章 需要事先掌握的6个函数

    Excel 中总共有400个以上的函数，当然也没有必要全部掌握。工作中会用到、需要掌握的函数约有60～70个。

    其中，我们应该优先掌握的是以下6个非常重要的基础函数。

    “根据指定的条件来对应处理数据”……IF 函数

    “这个月的销售额是多少？” ……SUM 函数

    “这一销售数据涉及几笔交易？”……COUNTA 函数

    “销售额的明细如何？……比如分别计算每个负责人的销售额” ……SUMIF 函数

    “出席者名单，多少人有××？” ……COUNTIF 函数

    “输入商品名称，无法自动显示价格？” ……VLOOKUP 函数

    接下来，我将会具体讲解这6个函数。根据条件改变答案——IF 函数

    IF 函数的基础知识

    如果你是老师，你想以“考试分数在80分以上的是 A，80分

    以下的是 B”作为判断条件，在 B 列中输入所有分数后，C 列中

    会显示相应结果。可以按照下面的方法操作。

    在单元格 C2中输入以下公式：

    =IF(B2>=80,A,B)? 按 Enter 键，C2中得出“B”? 将公式复制到其他单元格，系统会根据分数自动做出判断。像这样，根据作为判断条件的数值，可以更改单元格中的数

    值或公式的结果。这就是 IF 函数的作用。

    下面是 IF 函数的具体结构。

    【公式】

    =IF(条件表达式,条件为真,条件为假)

    像这样表示函数构造的形式，叫作“公式”。不是说一定要准

    确无误地记住所有函数的公式。只要能做到看一眼就大概明白其

    中的含义，在实际操作中也能熟练运用就可以。

    在此，我们来具体看一下函数结构中各部分所表示的含义。

    第一参数：条件表达式(用于按照条件分别处理结果)※上述例子(B2>=80)中，表示单元格 B2的值是否在80以

    上

    第二参数：条件为真(即第一参数中的条件表达式成立，符

    合条件时返回的值)

    第三参数：条件为假(即第一参数中的条件表达式不成立，不符合条件时返回的值)

    也就是说，之前列出的公式，其实是一个命令句“B2的值大

    于等于80输入 A，不是则输入 B!”

    如何判定复数条件

    在判定复数条件时，请把多个 IF 函数嵌套在一个公式中。

    比如说，如果要表达“B2单元格的值大于等于80为 A，大于等于5

    0为 B，49以下为 C”，就简化为下列公式。

    =IF(B2>=80,A,IF(B2>=50,B,C))

    乍一看也许有人会觉得这个公式又长又复杂，但它只是在重

    复下面的程序。

    一开始的条件表达式(B2>=80)，如此条件为真，输入值(A)。

    下一个参数，再次从 IF 和括号开始输入。

    接着输入下一个条件表达式。

    如果不符合这两个条件表达式中的任何一个条件，则表示“

    结果为假”，输入的值则指定为”C”。

    像这样，在 IF 函数中嵌套一个 IF 函数的现象，叫作“多重条

    件函数”。IF 函数的多重条件，在 Excel 2007以后的版本中，最多可以排入64个。但是，如果嵌套的函数太多，可能变成自己都难

    以理解的复杂算式，这点请务必注意。遇到这种情况，可以利用

    VLOOKUP 函数的变换技巧(参考 P272)，或利用操作列(参考

    P125)划分到多个单元格分别处理。总之，可以采取不同的方法。本月销售额——SUM 函数

    SUM 函数的基础

    在 Excel 中，加法用“+”符号进行运算。想要求单元格 A1与

    A2的数值总和，可以用下列算式做加法。

    =A1+B1

    但是，如果做加法的单元格有很多，全部用“+”连接的话，需要多次输入“+”，这样做十分浪费时间。有一个函数专门用于

    简化多个单元格做加法时的输入操作，那就是 SUM 函数。

    例如，要计算单元格 B2到 B11的值的总和，则在 B2中输入

    以下公式：

    =SUM(B2:B11)

    ※目标单元格范围，用“:”(冒号)连接起始单元格和最终单

    元格。

    也就是说，在 SUM 函数的括号中的内容是需要计算总和的

    单元格的范围。

    【公式】

    =SUM(想要计算总和数的单元格的范围)

    计算连续单元格范围内的总和——ΣSUM在 B12与 C12中输入数量与总销售额。

    在 B12单元格里，输入 SUM(B2:B11)

    其实，想要计算多个连续单元格范围内的总和，有更简便的

    方法，那就是使用ΣSUM 函数(SUM 函数中的一种)，它的功

    能就是能够自动输入 SUM 函数和计算总和的范围。

    可在【开始】栏目下点击ΣSUM 按钮，或者不使用鼠标，直

    接按快捷键。虽然这两种方法的区别甚微，但掌握快捷键总是方

    便的。先选择 B12，然后按下以下快捷键。Alt + =

    随后，就会像前文中的画面一样，系统自动指定合计单元格

    范围，目标单元格里也含有 SUM 函数。

    并且，这时候如果在 B12与 C12都被选中的前提下，按下这

    个快捷键，处于自动选中合计单元格范围的 SUM 函数，会同时

    出现在这两个单元格中。

    如何求多个分开的单元格的总和

    如果要计算多个分开的单元格的总和，应该怎么做呢？

    这时，按照以下方式，按下 Ctrl 键并点击鼠标，就能轻松

    输入公式。

    选中想要求和的单元格，输入=SUM(。

    ※这里选择了单元格 C14? 按 Ctrl 键，选择需要求和的单元格。

    ※如图所示，点击单元格 C2、C6、C10。? 输入右括号，按回车键确定。如此，单元格 C14中显示为

    =SUM(C2,C6,C10)

    像这样，在需要求和的单元格之间输入“,”来隔开，就能够大

    幅提升工作效率。

    如何提高乘法运算、字符串混合输入的效率

    在 Excel 中，同样有能够快速输入乘法运算和字符串的函数。PRODUCT 函数可以对括号内指定的数值做乘法。例如，按

    如下方式输入，即可算出单元格 A1到 E1数值相乘后的结果。

    =PRODUCT(A1:E1)

    用星号()连接单元格的话，公式则如下所示。很明显，前面的方法要轻松得多。

    =A1B1C1D1E1

    除此之外，还有在括号内连接多个指定文本的 CONCATEN

    ATE 函数。首先输入：

    =CONCATENATE(

    之后，按住 Ctrl 键，点击想要连接的单元格，像这样，选

    中的单元格会被“,”隔开。

    =CONCATENATE(A1,B1,C1,D1,E1)

    用“”连接各单元格也是一样，但存在多个需要连接的目标

    单元格时，还是这种方法更简便。计算客户名单的人数——COUNTA 函数

    “销售额”不仅是金额的总和

    前文中介绍的 SUM 函数，是在日常工作中使用频率最高的

    函数之一。但是，在实际操作时也会出现问题。比如在计算销售

    额总和时，SUM 函数得出的结果为金额总和。但是，除金额以

    外，“成交件数”“销售个数”“客户人数”也是“销售额”中的要素。

    也就是说，用 SUM 函数计算得出“销售额为1亿日元”之后，接下

    来有必要表示“这些销售额中的成交量是多少”。

    这时，我们就能用到 COUNTA 函数了。如果说 SUM 函数用

    来“算出指定单元格的总和”，那么 COUNTA 函数则是用来“计算

    指定单元格的范围内，包含有效数值的单元格的个数(即非空白

    单元格的个数)”。

    例如，有一张按活动参加者分类显示购买入场券数量的表格

    ，如果现在想要知道有多少名参加者，应该怎么做呢？

    A 列中输入参加者的名字，想要在单元格 E1中显示参加者人

    数的话，可以在单元格 E1中输入如下公式：

    =COUNTA(A:A)-1

    在单元格 E1中输入=COUNTA(A:A)-1把函数翻译成文字

    这一函数，实际是通过以下方式进行计算的。

    “数一数在 A 列中，有多少单元格内含有数据(除空白单元

    格以外的数量)，并减去1”

    为什么要减去1呢？这是因为计算时要除去内容为“参加者姓

    名”的单元格 A1。像这样，在实际使用 Excel 时，必须掌握“迎合

    不同情况，在函数公式中通过增减数字进行调整”这种能力和思

    维方式。

    “能把函数用文字翻译出来”非常重要。要习惯用文字来解释

    说明函数公式在进行怎样的处理。

    此外，在此介绍的“整列单元格数减去1”的公式，也可用于

    自动增减在输入规则中的菜单选项。(参考 P200)与 COUNT 函数的区别

    与 COUNTA 函数极为相似的函数是 COUNT 函数。它与 CO

    UNTA 函数的区别如下：

    COUNTA 函数

    指定参数范围内，计算除空白单元格之外的单元格的个数，即统计包含数据的单元格的数量。

    COUNT 函数

    指定参数范围内，计算含有数值的单元格的数量。

    也就是说，COUNT 函数只计算含有数字的单元格个数。因

    此，自动忽略统计含有文本的单元格的数量。在具体实务操作上

    ，一般用 COUNTA 函数就够了，当需要计算输入有数字、数据

    的单元格的数量时，再使用 COUNT 函数即可。按照负责人分别计算销售情况——SUMIF 函数

    SUMIF 函数的基础

    如下所示，A 列为负责人，D 列为销售额数据。

    A 列：负责人，D 列：销售额

    以这个数据表格为基础，从 G 列开始，计算每一个负责人的

    销售额的总和。在做这项工作时，我看到很多人发生了以下“惨剧”。

    使用电子计算器，手动计算数据。

    输入“=SUM(D2,D7,D12,D17,D18)”，统计每一名负责人的销

    售额总数时，不断重复这一操作。

    那么，怎样做才正确的呢？

    这时我们可以使用 SUMIF 函数，我们看一下具体的操作步

    骤吧。

    在单元格 H2内输入以下公式

    =SUMIF(A:A,G2,D:D)

    按下回车键后，单元格 H2内显示“吉田”负责的销售额。? 将单元格 H2中的公式拖拽复制至 H6，则会显示相应的负

    责人的销售额。

    想要得出所有负责人的销售额总和时，则需要双击单元格

    H7，再按下 AUTOSUM 快捷键 Alt + = 。? 按下回车键，可得出全员销售额的总和。

    SUMIF 函数有三个参数。

    第一参数：用于条件判断的单元格区域

    第二参数：在第一参数指定的范围里，需要计算总和的行的

    判定条件

    第三参数：实际求和的区域

    按照步骤1输入“=SUMIF(A:A,G2,D:D)”这一公式，Excel 会自

    动识别，做出以下的处理：需要计算总和的区域为 D 列数值。但并不是要算出 D 列中全

    部数值的总和。

    在 A 列中，只计算与 G2的值相同的行的 D 列数值的总和。

    不能只导出实数一览表

    决不能仅限于导出实数一览表。将得出的数字进行比较，才

    有其计算的意义。让我们再算一下“结构比率”吧。

    单元格 I2中，销售额输入计算吉田的销售额占整体的比例

    的公式。

    按下回车键，显示出吉田的销售额在整体中的所占比例。? 拖拽复制到单元格 I7，即可显示每一名负责人所占的比例。

    如此一来，即可显示每一名负责人的销售额所占比例，每一

    名负责人对公司的贡献程度等情况也会一目了然。

    另外，比起“A 君的业绩比谁都高啊”这种模糊的说明，通过

    使用“A 君的销售额占全员的43%”这样带有具体数据表达，就会

    让说明变得更加详细、更有说服力。参加名单中，有多少人出席—COUNTIF 函数

    COUNTIF 函数的基础

    假设要制作活动的参加者名单。参加与否一列中需要输入○

    、△、×这3种符号。

    活动出席人员名单

    那么，如自动计算出现在的参加者有几人，即标记“○”的人

    数是多少，以及“缺席人员，标记‘×’的有几个人”，应该怎样处理呢？当然，我们不可能每次都口头计算，再填到 E1～E3的表格

    里，这样太浪费时间了。

    为节省时间，有一种函数可以算出“在 B 列中，标有‘○’的单

    元格有多少个”，那就是 COUNTIF 函数。

    好了，我们来试着在 B 列中分别计算单元格 E1～E3中的○、△、×的数量吧。也就是说，即使这张参加名单表格有任何追加

    、变更的情况，各个记号的数量也会自动更新。

    在单元格 E1中，输入以下公式，计算 B 列中与 D1有相同

    内容的单元格的数量。

    =COUNTIF(B:B,D1)

    按回车键，在单元格 E1中显示结果。? 将公式拖拽复制至单元格 E3，显示其他记号的数量。COUNTIF 函数是由下面2种参数构成的。

    第一参数：计算其中非空单元格数目的区域

    第二参数：在第一参数的指定范围内计算数目的条件

    指定范围(第一参数)中，计算出第二参数指定的值或者与

    指定的条件一致的单元格的数目。

    如何计算每名负责人员的销售件数

    用前面写到的 SUMIF 函数可以算出每一位负责人员达成的

    销售额，那么这回来算一下每个人的销售件数吧。利用前面介绍

    过的 SUMIF 函数，在 H 列的“销售额”中输入每一位负责人的销

    售额。

    I 列的“销售件数”，则显示“这些销售额分别来自多少件销售

    业务”这一数据。在这个表格中，单元格 I2中的数字表示“A 列中

    含有的单元格 G2数值的数目(即“吉田”)有多少”。

    在单元格 I2中输入以下公式，计算 A 列中有单元格 G2数

    值的数目(即“吉田”)有多少。

    =COUNTIF(A:A,G2)? 按回车键后，单元格 I2中显示的数值表示：A 列中出现的

    与 G2有相同值的单元格(即“吉田”)的数目。

    将单元格 I2中的公式拖拽复制到 I6，然后选择 I7，按 Al

    t + Shift + = (AutoSUM 快捷键)获得总和。完成以上三步，即完成统计。

    这里出现的数字，计算的是“A 列中含有各负责人名字的单

    元格，各有多少个”。把它作为一个商业数据概念来讲的话，表

    示的是“吉田的销售件数共有5件”。

    另外，在这张图中有一列空白的单元格，此列数据是将每一

    位负责人的销售额除以销售件数，得到的平均销售额的数据。通

    过计算结果，就能分析出如“虽然以销售件数来说吉田比佐藤多

    一些，但是佐藤的销售额更高是因为佐藤的平均销售额更高”这

    样的结果。以此进一步了解到“吉田只要向佐藤看齐，增加每件

    交易的平均销售额，即可提高总销售额。”

    只是像这样简单地分析，也能成为我们探讨一些具体销售战

    略的契机，比如“为了这一目标，应该具体订立怎样的销售策略？”“我们应该考虑什么样的促销手段？”

    通过使用 COUNTIF 函数，我们可以检查数据是否重复、确

    认指定数据是否存在、单元格中是否包含指定文本，等等。这是

    一个十分方便的重要函数，请一定要掌握。输入商品名，自动显示价格——VLOOKUP 函数

    VLOOKUP 函数的基础

    假设有以下数据表格。

    这时，A 列中输入商品代码后，单价一列即可自动出现价格

    ，这样不仅十分方便，还能避免输入错误。

    但是，要想实现这点，需要预先在其他地方准备好“各商品

    的价格”一览表。在这张 Excel 工作表中，可作为参考信息的表格

    (商品单价表)位于右侧。

    那么，我们试着将与 A 列各商品代码匹配的单价显示在 B 列中吧。

    在单元格 B2中输入以下函数。

    =VLOOKUP(A2,F:G,2,0)

    按回车键确定后，将 B2拖拽复制到单元格 B8。

    由此，B 列的各单元格中出现了与商品代码匹配的单价。

    在此输入的 VLOOKUP 函数，到底是什么样的函数呢？只有能够用文字解释，才算是完全掌握了这个函数。将 VLOOKUP 函

    数转换成文字，则为以下的指令：

    “在 F 列到 G 列范围内的左边一列(即 F 列)中，寻找与单

    元格 A2的值相同的单元格，找到之后输入对应的右边一列(即

    G 列)单元格。”

    VLOOKUP 中的 V，代表 Vertical，表示“垂直”之意，意为“

    在垂直方向上查找”。此外，类似函数还有 HLOOKUP 函数，首

    字母 H 代表 Horizontal，表示“水平”之意。因篇幅有限，本书无

    法做出更详尽的说明，有兴趣的读者可自行了解。

    4个参数的意义与处理流程

    用逗号(,)隔开的4个参数，我们来看看这4个参数各自表达

    的意思吧。

    第一参数：检索值(为取得需要的数值，含有能够作为参考

    值的单元格)

    第二参数：检索范围(在最左列查找检索值的范围。“单价表”检索的范围)

    第三参数：输入对应第二参数指定范围左数第几列的数值

    第四参数：输入0(也可以输入 FALSE)

    这个函数，首先在某处搜索被指定为第一参数检索值的值。

    至于搜索范围则是第二参数指定范围的最左边的列。上述例子中

    ，第二参数指定的是 F 列到 G 列的范围，因此检索范围即为最左

    列的 F 列。接下来，如果在 F 列里发现了检索值(如果是单元格 B2则

    指 A2的值即“A001”，F 列中对应的是 F3)，那么这一单元格数

    据即为往第三参数指定的数字向右移动一格的单元格数值。这一

    例子中，第三参数指定为2，因此参考的是从 F3往右数第2列的单

    元格 G3的数据。

    之后，再在这张表的小计栏中输入“单价×数量”的乘法算式

    ，输入数量后，系统就会自动计算小计栏中的数据。

    如果在报价单与订单的 Excel 表格里设置这样的构造，制作

    工作表时就会十分方便。这是一项能够提高 Excel 操作效率的基

    础。

    用“整列指定”检查

    请注意一下在第二参数中指定 F 列和 G 列这两个整列的这一

    操作。这样，即便在单价表里追加了新商品时，VLOOKUP 函数

    依然可以做出相应的处理。在设定事先输入 VLOOKUP 函数，就

    能自动显示的格式时，也一并使用上述方便的功能吧。

    下面的公式，仅指定了单价表范围，每次增加商品时都需要

    修改 VLOOKUP 函数，这样十分浪费时间。

    =VLOOKUP(A2,F3:G8,2,0)

    无论是 SUMIF 函数、COUNTIF 函数还是 VLOOKUP 函数

    ，基本都是以列为单位选取范围。这样不仅能够快速输入公式，使用起来也十分方便。第4章 通过应用与组合，提升函数的威力

    如果想要知道所有的 Excel 函数，那么你可以买一本介绍 Ex

    cel 函数的词典。但即便掌握了所有 Excel 函数，对工作也没有任

    何帮助。最重要的是，要知道“如何组合使用单个或多个函数，来实现需要 Excel 完成的处理”。

    在本章中，我会介绍几个实例，看一下“在实际工作中应该

    如何使用函数”。我希望大家能从这些例子中明白一点：比起掌

    握具体的技巧，更重要的是学会如何“构思”。刚开始可能许多人

    会觉得很难，但习惯后，就可以灵活运用函数的各种特性，利用

    各种应用操作，思考如何自由地处理各种数据。运用 Excel 函数

    来提升工作效率，其实是一项富有创造性的脑力工作。

    接下来，我们来举几个具体案例。判断单元格中是否包含特定的字符串

    如何计算世田谷区的客户人数

    “想要从客户数据中筛选出世田谷区的客户人数。”

    这时，如何才能简单、快速地完成这项工作呢？

    其实只需要按照下面这两个步骤操作即可。

    调查单元格中是否包含“世田谷区”这四个字

    如有，则在其他单元格中输入“1”

    如此一来，只要计算含有“1”的单元格的数目，就能得出包

    含“世田谷区”这四个字的地址的单元格数目。

    像这样，“确认单元格里含有特定字符串时，标记为数字1”

    的操作，属于 COUNTIF 函数的应用。假设在 A 列中输入住址，B 列输入数字1。

    在单元格 B2中输入以下公式。

    =COUNTIF(A2,世田谷区)? 一直将公式复制到数据的最后一行。

    这样一来，在 A 列单元格中若含有“世田谷区”四个字，B 列

    中就会在相应的行显示“1”。

    此处出现的“”符号叫作“星号”。无论是什么样的文字，无论

    有多少字，都可以这1个文字来代替使用(作为“通配符”使用的

    符号)。意思就是说，“世田谷区”前后含有其他文字。这样一来

    ，只要该字符串符合“包含‘世田谷区’四个字”这样的条件，即可

    被检索出来。现在让我们来复习一下，COUNTIF 函数是在第一参数指定

    区域中，计算符合第二参数指定条件的单元格数目的函数。在单

    元格 B2中输入的函数有这样的意思：

    在单元格 A2中包含“世田谷区”这个值的单元格有多少个？

    作为指定范围的第一参数中，此处指定的是单个单元格 A2。判断符合条件的单元格有多少，答案只有1和0。如答案为1，那么就说明此单元格中包含“世田谷区”；如答案为0就是不包含。

    接下来，再用 SUM 函数统计 B 列值的总和，就能得出 A 列

    中所有包含“世田谷区”的单元格的数量。

    SUM 函数在单元格 B8中表示 B 列的总和

    如何搜索除世田谷区以外的区域

    前文中介绍的是如何搜索单元格中是否包含特定文字(世田

    谷区)，直接将指定文字输入到函数中并搜索。那么如果不仅需要搜索“世田谷区”，也需要搜索包含其他区域的单元格时，应该

    怎么做？

    搜索除世田谷区以外的区域

    如果把各个区域名称直接输入进函数，那么需要重新输入 B

    列到 F 列每一列中的函数。这样做非常麻烦，也很容易出错。

    这时，请不要采取这种直接输入的方法，而是要采取引用单

    元格的方法。工作表中的行首处会显示搜索目标区域的项目名称

    ，利用这些单元格，就能简化输入函数的操作。

    在此提醒各位读者，引用单元格来搜索时，要输入以下公式

    ：

    =COUNTIF(A2,B1)

    输入=COUNTIF(A2,B1)在第二参数中，连续输入单元格号码与星号容易发生错误。

    为了方便大家理解，下面我将去掉绝对引用的符号，告诉大家

    会容易出现什么样的错误。

    =COUNTIF(A2,B1)

    这个公式的意思变成了要在单元格 A2里，搜索是否含有“B1”这一字符串。但是原本需要搜索的是单元格中是否含有“包含‘B

    1’的字符串”。为了区分指定星号标记与引用单元格，需要用符

    号连接。

    设定绝对引用时一定要注意，在单元格 B2中输入正确的公

    式之后，再直接复制到单元格 F7为止。

    将输入的公式复制到单元格 F7另外，想计算含有各区域名的单元格的数目，只有选择 B8

    ～F8，按 Alt + = (AutoSUM 的快捷键)即可立刻得出结果。

    选择 B8～F8，按 Alt + =

    顺便，用 IF 函数是无法顺利处理这项操作的。在单元格 A2

    中如包含有“世田谷区”打○，否则打×，在做这项操作时，有许多

    人反应使用下面的公式无法得到预期的结果。

    =IF(A2=世田谷区,○, ×)这种情况下，需要在 COUNTIF 函数中嵌入判断是否包含字

    符串的条件。

    =IF(COUNTIF(A2,世田谷区)=1,○,×)

    随后，就能在单元格 A2中检索是否含有“世田谷区”这组字符

    串。消除重复数据的方法

    如何判断是否有重复

    “电话征订名单中，多次出现同一家公司!”

    这是某个正在开展开发新客户的销售部门里发生的事。这个

    部门负责电话征订的共有10位销售人员，这些人员先制作电话征

    订名单，然后根据名单给客户打电话。由于每位销售人员都是通

    过网络等方式调查并收集目标企业信息的，所以同一个企业会出

    现在不同的销售人员的电话名单中。

    这时，如果大家一同开始给目标企业打电话，就会导致同一

    家公司多次接到同一公司的销售人员的电话，最终一定会听到客

    户的投诉：“别再给我们打电话了!”因此，经常有人来问我如何

    才能避免这样的事情发生。

    像这样，在管理客户名单时，应该如何检查是否存在重复的

    数据？

    首先我们来看一下简单的判断方法。比如，A 列为 ID 信息

    ，要想检查其中是否有重复的内容，可按照以下逻辑判定。

    计算该 ID 在 A 列中的数目

    如结果为1个则表示没有重复数据，如果是2个以上则可以认

    定为有重复那么，我们来看一下应该如何在 Excel 中处理重复数据。在

    此，假设想要在 B 列中显示是否有重复数据的判定结果。

    在单元格 B2中输入以下公式：

    =COUNTIF(A:A,A2)这一公式用于计算在 A 列中与单元格 A2有相同数值的单元

    格的数目。

    若结果为1个，说明 A 列中不存在与单元格 A2有相同数值的

    单元格……也就是说不存在重复数值。

    若结果显示为2，说明 A 列中存在与单元格 A2有相同值的单

    元格，可以得知数据有重复。

    复制到数据的最后一行

    像这样，在一列中连续输入已经存在的数值时，需要复制的

    行数会增多。用鼠标将相邻列中的函数公式拖拽复制到最后一行

    ，是一件十分麻烦的事。下面的技巧可以让你在一瞬间完成这项

    操作。

    在单元格 B2中输入公式后，再次选中单元格 B2，将鼠标移

    到被选中单元格右下角的浮标上。这时，我们可以看到原本白色

    十字的游标变成了黑色。接下来，我们需要双击这个黑色游标。这样，我们就能够确认 A 列中的单元格是否存在重复的数据。选中并删除重复的单元格

    即使知道工作表中存在重复数据的单元格，也还有问题需要

    解决。一般来说，确认工作表中存在重复的数据后，需要删除重

    复的信息，将表格整理为没有重复数据的状态。利用先前的方法

    只能确认是否存在重复的数据，无法选中并删除重复的单元格。

    因此，我们需要将原来的公式修改成这样：

    =COUNTIF(A2:A2,A2)

    在单元格 B2中输入=COUNTIF(A2:A2,A2)在单元格 B2中输入公式时，指定与第二参数一致的单元格

    查找范围的第一参数为“A2:A2”，也就是单元格 A2。因此，得

    出的结果自然为1。

    接下来，双击右下角游标，将这一单元格复制到最后一行，就会出现以下画面。将单元格 B2复制到最后一行

    这也就是我在第1章里稍微提到过的自动筛选，即只抽出 B

    列中值为2的单元格后并删除，即可删除所有重复项。

    自动筛选抽取 B 列值为2以上的单元格单元格 B2的函数中的第一参数“A2:A2”，指定从 A2到 A2

    作为函数的范围。冒号(:)前的内容表示只引用范围起始点的单

    元格，意为绝对引用。如此一来，将这一单元格向下拖拽复制后

    ，单元格 B3的范围为“A2:A3”，单元格 B4为“A2:A4”，以此

    类推。也就是说，作为指定范围的单元格的起点，即单元格 A2

    是固定的，终点的单元格却是相对引用，可以不断延续。这样就

    让人觉得第一参数指定的范围在无限扩展。

    在 B 列的各单元格中的函数引用的并不是位于该单元格下面

    的单元格中的内容。所显示的数字表示的是“该单元格相邻的单

    元格的数值，在 A 列中出现了几次”。

    照此推断，就能得出“B 列中显示有2以上的数字的数值表示：在 A 列中的前面的某行中已经出现过有相同值的单元格”，表

    示数据有重复。因此，如果将 B 列中含有2以上的数据的单元格

    全部删去，A 列中就不会存在重复的数值了。

    专栏 不要使用“删除重复”键

    2007之后的 Excel 版本都追加了“删除重复”功能，但我个人

    不推荐使用，因为在实际操作中曾发生过删除了并没有重复的数

    据的事例。输入连续的数字

    在 Excel 中输入1、2、3……这样连续的数字到底有多少种方

    法呢？我们来逐个看一下。

    使用“自动填充”功能

    先介绍一下自动填充功能的使用方法。例如，在单元格 A2

    中输入1，在单元格 A3中输入2，然后同时选中单元格 A2和 A3

    并向下方拖拽复制，一直拖拽到最后一行。这样，每一行中的单

    元格中的数字就是连续的。

    同时选中单元格 A2和 A3向下方拖拽复制，一直到拖拽到最后一行使用“制作连续数据”功能

    如果是需要输入小范围的连续的数字，我们可以使用 Excel

    自动填充功能来完成。但是如果结尾的数字很大，使用这种方法

    就有一定的限制。例如，要连续输入1到1000的数字，使用自动

    填充功能的话，需要花费很长的时间。

    这种要连续数到很大数字的情况下，就可以使用“制作连续

    数据”功能。

    起始单元格中输入数字1，选中此单元格。输入1后按回车键，下方单元格也变为选中状态。然后按 Ct

    rl + Enter 后，选中状态仍停留在刚输入完毕的单元格。

    【开始】选项卡?【填充】?点击【系列】

    【序列产生在】选择【列】，【终止值】输入1000，点击

    确定。利用这个方法，就可以在单元格中连续输入1～1000，这个

    方法比自动填充更简单，也更方便。

    如何连贯输入连续的数字

    以上2种方法有个前提，就是所有输入单元格的数字要为固

    定值，因此如果删去中间某一行或者插入一行，连续的数字就从

    中间断开了。要想在这种情况下也让数字保持连贯，我们可以使

    用 ROW 函数。无论删掉还是穿插一行单元格，都可以保持数字

    的连贯，不需要逐个修改。

    输入下列公式的单元格，会显示“该单元格位于工作表中的

    第几行”的数据。【公式】

    =ROW

    括号中不要输入任何内容。请记住像这样在函数括号中不输

    入任何参数的方法(比如 TODAY 函数、NOW 函数等)。

    例如，在单元格 A2中输入这个函数，单元格 A2中会显示2。由于单元格 A2位于工作表中的第2行，因此数字2代表的是这

    个行数。

    在单元格 A2中输入=ROW

    如下图所示，直接向下拖拽复制，从2开始的连续。

    从单元格 A2向下拖拽复制各单元格“=ROW”这个公式导出的数字，这个数字表示该

    单元格所处的行数，所以会在单元格中显示连续的号码。

    但是通常来说，连号都是从1开始。因此，需要在这个 ROW

    函数中做减法。例如，想从第2行(这里是单元格 A2)开始输入

    连续的数字时，请输入下列公式。

    =ROW-1

    在单元格 A2输入=ROW-1按回车键，ROW取得的行数2再减去1，显示结果得到1。

    显示结果为1

    将此单元格向下拖拽复制，各单元格中就会出现连续的数字。

    将单元格 A2向下方拖拽复制这里的连续的数字，按照各单元格中的 ROW 函数取得的该

    单元格时所在的行数，因此就算中间删除或添加一行单元格，都

    会从1开始保持数字的连贯。

    在工作表中沿行方向输入连续的数字那么，如果想要在工作表中沿行方向，即向右方输入连续的

    数字，应该怎么做呢？这时，我们可以使用 COLUMN 函数。CO

    LUMN 函数的意义在于，在输入如下内容的单元格中，能够得出

    该单元格位于工作表的左数第几列。

    【公式】

    =COLUMN

    例如，在单元格 B1中输入这一函数会得到2。

    在单元格 B1中输入=COLUMN

    单元格 B1位于 B 列，即工作表的左数第2列。因此得出数字

    2。

    如果继续向右拖拽复制，就会开始从2连续输入数字。

    要想从1开始连续输入的话，与 ROW 函数同理，减去数字做

    相应调整就行。

    在单元格 B1输入下列公式后，按回车键。=COLUMN-1

    将单元格 B1向右方拖拽复制，出现连续的数字。

    通过 ROW 函数、COLUMN 函数在工作表中输入连续的数

    字，可运用在以下的需求中。

    在表格中隔行标注2种不同颜色

    输入连续的阿拉伯数字

    快速沿行方向输入大量 VLOOKUP 函数

    在这之后，我会逐个具体说明。沿行方向输入大量 VLOOKUP 函数的方法

    批量修改单元格

    如果遇到像下图这样，需要输入大量的 VLOOKUP 函数，按

    照常规的方法处理需要花费大量的时间和精力。

    “输入表”中的各个单元格里，按照“商品 No.”在“负责部分”

    中用 VLOOKUP 函数找出对应值。首先用常规的方法，在最开始的单元格 C3中输入以下公式：

    =VLOOKUP(B3,I:N,2,0)

    将单元格 C3的公式向右一直复制到 G 列，为了不改变从属

    单元格，需要用绝对引用来固定第一参数的检索值和第二参数的

    检索范围。

    在单元格 C3输入=VLOOKUP(B3,I:N,2,0)后

    接着将它一直拖拽复制到单元格 G3。画面显示如下：

    将单元格 C3一直拖拽复制到单元格 G3

    所有单元格中的数据都已经变成了相同数值。这是因为从单

    元格 C3到 G3，每个单元格中的函数如上变为了第三参数“2”。参

    考的是检索范围 I:N 列最左端开始数第2列的值。因此，如果要让 C3到 G3中的每个单元格都显示各自所属正

    确的数值，就必须修改各单元中的 VLOOKUP 函数的第三参数。

    C3中 VLOOKUP 函数第三参数改为“2”、D3改为“3”、E3改为“4”

    、F3改为“5”、G3改为“6”，这样每个单元格中的数值才是正确的。

    像这样逐个修改还是很麻烦的。像前文中的例子那样，如果

    需要修改的单元格只有4个，那么不会花费很多时间。但是工作

    中需要输入 VLOOKUP 函数和修改第三参数的单元格有时会多达

    50列。遇到这种情况，千万不要动手逐个去修改。我告诉大家一

    个便捷的办法，甚至可以不用逐个修改单元格。

    在粘贴的单元格中变为合适的数字

    这里需要的并不是把 VLOOKUP 函数的第三参数输入成2或3

    这样的固定值，而是需要“输入可以在粘贴的单元格里，实时转

    化为合适的数字”这样的设想。

    最简单的就是在表外的上方输入想要指定的第三参数的数字

    ，然后引用这一单元格。例如，在单元格 C1到 G1中，分别输入

    从2到6的数字，在 C3中输入以下公式：

    =VLOOKUP(B3,I:N,C1,0)

    将这个公式一直复制粘贴到 G3，显示如下。

    在单元格 C3输入=VLOOKUP(B3,I:N,C1,0)并一直复制粘

    贴到 G3第三参数引用的是同一列的第1行的单元格。也就是说，C

    列引用2，D 列引用3，如此自动改变数值。这样就不用在每个单

    元格里逐个输入 VLOOKUP 函数的第三参数了，从而大大减轻了

    工作负担。

    无需在工作表外填入数据并完成连续输入

    VLOOKUP 函数

    在这种情况下，由于“输入表”与“负责部分”各项目的排列顺

    序相同，VLOOKUP 函数第三参数中指定的数字也要向右递增，显示连续的数字。因此，工作表外的上部不用输入其他数字，也

    可以完成操作。

    想要沿着行的方向输入连续的数字，我们可以使用 COLUM

    N 函数。利用 COLUMN 函数的特性，并将之嵌套在 VLOOKUP

    函数的第三参数里，就可以瞬间完成复杂的操作。

    在单元格 C3中输入以下公式：

    =VLOOKUP(B3,I:N,COLUMN-1,0)

    一直复制粘贴到单元格 G3，Excel 中就会出现如下页面。

    在单元格 C3里输入=VLOOKUP(B3,I:N,COLUMN-1,0)

    并复制粘贴到 G3第三参数“COLUMN-1”在 C 列中为2，在 D 列中为3。COL

    UMN 函数所导出的，是含有 COLUMN的单元格位于工作表中

    第几列的数字。

    在单元格 C3中输入的 VLOOKUP 函数，其第三参数指定数

    字为2。由于 C3的 COLUMN为3，在此基础上减去1后，则调整

    为2。同理，D 列到 G 列中“COLUMN”获得的数字减去1就是 V

    LOOKUP 函数的第三参数，这样就能顺利地计算出正确的项目数

    值。如何用 VLOOKUP 函数应对检索范围中竖列顺序的

    变动状况

    输入表与负责部分的项目顺序不同时

    在刚才的例子中，为了让“输入表”与“负责部分”的项目排列

    顺序保持一致，第三参数按顺序输入2、3、4……这样连续的序

    号。因此，VLOOKUP 函数第三参数引用嵌入了 COLUMN 函数

    ，这样做会提高效率。

    但是，如果像下面这样，输入表与负责部分的项目顺序不同

    时该怎么办？也就是说第三参数不是连续数字的话，各单元格中

    的 VLOOKUP 函数即便运用了 COLUMN 函数，也无法得出正确

    的第三参数。

    输入表与负责部分的项目顺序不同时此例中，D 列的“单价”对应“负责部分”最左端往右数第6列，E 列的“生产者”对应“负责部分”最左端往右数第5列。在这样的前

    提下，如果想要在单元格 C3中输入最开始的那个函数公式，之

    后只要复制到 G 列也都可以得出结果的话，我们应该怎么做呢？

    在 C 列商品名的单元格输入的 VLOOKUP 函数中第三参数

    应该是什么数字呢？答案是2。那么，我们只要输入能自动导出

    数字2的第三参数就可以了。这时候，我们就要用到 MATCH 函

    数。

    我们通过以下例子具体解释一下。上述例子中，A1到 D1项目名称分别为“商品名”、“单价”、“

    生产者”、“最低订购单位”，这些项目在 F1到 I1的范围中位于左

    数第几列，会相应地显示在 A2到 D2中。以单元格 A2为例，“A1

    (即商品名)的值，在 F1：I1范围里位于左数第2个”，那么 A2

    中则会显示数字2。

    在单元格 A2做出这种处理的是下面的函数公式。

    =MATCH(A1,F1:I1,0)

    MATCH 函数中第一参数指定的值，会导出在第二参数指定

    范围中位于第几位的数字。第三参数基本上“只要输入0就行了”。

    在图中，将单元格 A2的公式一直复制粘贴到 D2。因为第一

    参数不做绝对引用，单元格 B2里被复制粘贴的公式中的第一参

    数为 B1，单元格 C2里被复制粘贴的公式的第一参数为 C1，D2

    中则是 D1。

    第二参数限定了纵列或横行的范围。

    ▲指定纵列的范围

    第一参数指定的值为在此范围内的上数第几行。

    ▲指定横行的范围

    第一参数指定的值为在此范围内左数第几列。

    单元格范围限定为 F1:I1，则呈现如下状态：

    单元格 A1即“商品名”位于左数第2个

    单元格 B1即“单价”位于左数第4个

    单元格 C1即“生产者”位于左数第3个

    单元格 D1即“最低订购单位”位于左数第1个能够在单元格中显示数字2、4、3、1，是因为 MATCH 函数

    的处理。

    在 VLOOKUP 函数的第三参数中加入 MATCH 函数，即使“

    输入表”与“负责部分”的项目的排列顺序不同，也能够通过 MAT

    CH 函数取得“‘输入表’的各项目名在‘负责部分’下位于第几列”的

    数字，把这样的结构嵌入 VLOOKUP 函数第三参数中就能够解决

    顺序不同的问题。在输入表的单元格 C3，请输入以下公式：

    =VLOOKUP(B3,I:N,MATCH(C2,I2:N2,0),0)

    然后复制到整个表格，画面则显示如下：

    在单元格 C3中输入=VLOOKUP(B3,I:N,MATCH(C2,I2

    :N2,0),0)并复制粘贴至全表

    分析 MATCH 函数的处理

    可能乍一看上述的公式很复杂，接下来我们来仔细分析一下。关键在于理解嵌入 VLOOKUP 函数第三参数的 MATCH 函数

    是如何发挥作用的。

    MATCH(C2,I2:N2,0)

    这个公式得出的数字指向的是，第一参数指定的单元格 C2的值(即商品名的值)位于第二参数指定范围(I2:N2)的左

    数第几个。在这一例子中为数字2，它与单元格 C3中以 B3的值(

    数字1)为检索值的 VLOOKUP 函数里，检索范围 I:N 从左数第

    几列的对应数字是一致的。

    将输有单元格 C3内容的单元格一致复制粘贴到 G6，为了不

    让参照项移位，需要设定绝对引用。在有多个相同检索值的工作表中使用 VLOOKUP 函

    数的技巧

    VLOOKUP 函数会以最初达成一致的检索值单元格

    作为对象

    在 A 列中重复输入了同一家客户公司的名称，B 列中则为相

    应的负责人的名字。

    如果以 A 列和 B 列中的数据为基础，想要在 E 列中按顺序

    输入相应的负责人，这时使用 VLOOKUP 函数可能会无法得到想

    要的结果。我们来实际操作一下。? 在单元格 E1中输入以下公式：

    =VLOOKUP(D2,A:B,2,0)

    将单元格 E1中的公式一直复制粘贴到第7行。

    E 列中的相同的公司对应同一名负责人。例如，ABC 股份有

    限公司，原本是按铃木、田中、加藤这样的顺序排列，而现在全

    部变为了“铃木”。

    像这样检索值存在重复的情况，VLOOKUP 函数会以从上数

    、与起始处一致的检索值的单元格为对象来处理数据。单元格 E2

    、E3、E4也同样如此，都以“ABC 股份有限公司”为检索值，在

    作为检索范围的 A 列中以最初的单元格 A2为对象运行 VLOOKUP 函数，所以会返回“铃木”这个值。

    无重复状态下应加工后再处理

    为了解决这个问题，我们可以把有重复数据的 A 列和 D 列

    中的数据“加工”成唯一的状态，也就是该列下无重复的状态。这

    里，我们需要重新追加操作用的数据列，再进行处理。

    这个方法的原理是，给重复的客户公司名称标上不同的固定

    编号。

    首先，在各个表的左侧分别追加2列，作操作用。

    表格左侧分别追加2列，供操作用

    按照以下步骤，给相同客户公司名称的每个数据分别标上编

    号。每个公式引用的哪个单元格，进行了怎样的处理，我们一边

    看一边分析。

    在单元格 A2输入以下公式，一直复制粘贴到第7行。

    =COUNTIF(C2:C2,C2)

    ※C 列的客户公司名称标上数字? 同样地，单元格 F2输入下列公式，一直复制粘贴到第7行。

    =COUNTIF(H2:H2,H2)

    ※H 列的客户公司名称标上数字

    在单元格 B2中输入以下结合了固定编号和客户公司名称

    的公式，一直复制粘贴到第7行。

    =A2C2? 同样地，在单元格 G2输入下列公式，一直复制粘贴到第7

    行。

    =F2H2

    做完以上步骤，在 I 列输入下列 VLOOKUP 函数后，目标单

    元格中就会自动显示相应的负责人了。

    =VLOOKUP(G2,B:D,3,0)显示个别对应的负责人名称

    这个方法的关键在于，用 COUNTIF 函数给每个数据设定编

    号(出现次数)，通过编号与检索值得到新的固定检索值，并将

    其嵌入 VLOOKUP 函数中，由此就能得出正确结果了。是否能用 VLOOKUP 函数获得检索列左侧的数值？

    VLOOKUP 函数下，无法取得检索列左侧的数值

    VLOOKUP 函数可以说是 Excel 中最重要的函数，这里让我

    们再来看看其具体的公式和功能。

    【公式】

    =VLOOKUP(检索值,检索范围,列数,0)

    【功能】

    在检索范围最左一列中查找与检索值相同的单元格，然后在

    该单元格中返回第三参数指定的列数中的某个单元格的值。

    “从检索范围的最左边的列返回到第三参数指定的列数中的

    某个单元格的值”，也就是“返回位于该列右侧的值”。

    那么，问题就来了。

    “难道无法直接用这一列左侧的数值吗？”

    “给第三参数做减法导出数值就可以了吧？”或许有许多人都

    抱有这样的疑问。但答案是：“不可以”。

    那么，如果想要获得位于检索列左侧的列中的数值，应该怎

    么办？什么是 OFFSET 函数

    组合使用 OFFSET 函数与 MATCH 函数可以解决前文中的问

    题。OFFSET 函数的本质是“确定作为基准的单元格，通过上下左

    右偏移得到新的区域的引用”。

    【公式】

    =OFFSET(基准单元格,偏移行数,偏移列数)

    【功能】

    是以基准单元格为起始，返回按移动行数、移动列数偏移的

    单元格的值。

    偏移行数，正数表示向下，负数表示向上。

    偏移列数，正数表示向右，负数表示向左。

    首先，举个非常简单的例子。

    在 Excel 工作表的单元格 C3中输入“100”。

    将下列公式输入任意一个单元格。

    =OFFSET(A1,2,2)

    输入有上述公式的单元格，将返回“100”。

    作为基准单元格的 A1，向下2行、向右2列的目标单元格是

    C3(值为100)。所以输有此公式的单元格所返回的值就是100。

    将 OFFSET 函数与 MATCH 函数组合

    运用这个公式，想办法引用检索列左侧的单元格。

    下列表格我们可以看到，按照单元格 E2的数字，在 F2、G2的“课程”和“单价”中会分别对应返回数据。首先，先在 E2里输入

    1。

    首先，F2的“课程”十分简单，通常使用 VLOOKUP 函数就能

    处理。

    =VLOOKUP(E2,B:C,2,0)

    在单元格 F2中输入=VLOOKUP(E2,B:C,2,0)后取得“课程”数

    据但是，单元格 G2的“单价”数据位于单价的检索列(B 列)的

    左侧，这样用 VLOOKUP 函数就无法处理了。

    这时候，我们可以组合使用 MATCH 函数和 OFFSET 函数。

    为了导出 E2中“No.”所对应的单价数据，G2中要输入以下公式：

    =OFFSET(B1,MATCH(E2,B:B,0)-1,-1)

    在单元格 G2中输入=OFFSET(B1,MATCH(E2,B:B,0)-1,-1)

    以单元格 B1为基准，作为第二参数的结果的数字向下、再

    向左移动1格的目标单元格数值将会出现在 G2中。

    第二参数的 MATCH 函数，会查找单元格 E2的值位于 B 列

    的上数第几列。单元格 E2的值若为1，B 列内容为1的单元格位于

    第2行，因此 MATCH 函数导出结果为“2”。在这个例子中，以单

    元格 B1为基准的 OFFSET 函数直接嵌入 MATCH 函数中，由于

    B1向下偏移数为2，产生了1格的误差，所以需要做出调整，在此

    基础上减去1。

    在 OFFSET 函数中，可以将第二参数的移动行数、第三参数

    的移动列数指定为负数值。也就是说，可以引用位于基准单元格

    的上方、左侧的单元格。利用这一特性，可以解决 VLOOKUP 函

    数无法引用位于检索列左侧单元格的缺陷。不显示错误值的技巧

    逐次修正错误会导致效率低下

    在输入订单的明细栏、单价等数据时，只要输入商品 No.就

    可以同时显示商品名和单价。如果预先可以设置这样的机制，就

    能快速推进工作了。同时，还能避免人工输入造成的错误。我们

    在 B 列中输入只要在 A 列中输入商品 No.，就能显示相应的商品

    名称的 VLOOKUP 函数。

    在单元格 B2中输入以下 VLOOKUP 函数：

    =VLOOKUP(A2,E:G,2,0)

    按回车键确定，并将公式一直复制粘贴到最后一行。如图所示，单元格中会出现“NA”这样的错误值。这是由于

    函数公式中存在错误所误造成的。若是在单元格 A2中输入1，就

    会从负责商品栏中导出对应的商品名称。

    在单元格 A2中输入1，显示商品名

    总而言之，由于插入的是以商品 No.为检索值的函数，如果

    A 列中皆为空白单元格，自然就会出现错误。

    如果是仅在公司内部使用的工作表，这样也没什么问题。但

    是，如果是制作报价单或订单的话，要尽可能避免这种错误值的

    出现。但是，只是单纯删去单元格中的函数，再次使用时还是需

    要重新输入公式，这样非常没有效率。如结果有误，则返回空白值

    这一问题，可以运用处理“计算结果有误的话，返回空白值”

    的函数公式来解决。这时，我们会用到 IFERROR 函数(Excel 20

    07之后的版本中具备的函数)。

    通常都是因为先输入基本公式后，才发现有可能会有错误，再进行隐藏错误的处理。因此，输入公式时就要嵌入先前提到的

    VLOOKUP 函数。最终，单元格 B2中要输入以下公式：

    =IFERROR(VLOOKUP(A2,E:G,2,0),)

    选择单元格 B2，按 F2 键，使单元格处于可编辑状态

    在等号(=)之后输入“i”后出现候选菜单，选择第2个“IF

    ERROR”。? 按 TAB 键确定后，补充输入=IFERROR(。

    完成公式后按回车键确定，并将公式一直复制粘贴到最后

    一行，就可以隐藏错误值。? 在 A 列中输入商品 No.，会自动显示商品名与单价的数据。

    IFERROR 函数第二参数中，连续输入了2个引号，这是指

    定空白值的意思。

    把 B 列的公式复制到 C 列，VLOOKUP 函数第三参数改为3。

    IFERROR 函数的特点在于，第一参数指定的函数为错误值

    时，就会返回第二参数指定的值。在这个例子中，第二参数指定

    的是空白值，因此也就设定了“第一参数的 VLOOKUP 函数若为

    错误值，显示为空白结果”这样的机制。如使用2003之前的 Excel 版本的话，应该怎么做

    只有在 Excel 2007之后的版本才可以使用 IFERROR 函数隐

    藏错误值。如果你的 Excel 是2003版之前的，可以使用下面的公

    式：

    =IF(ISERROR(公式),,公式)

    ISERROR 函数可以检查括号内指定的公式是否为错误值。

    如果是则为“真”，否则返回“伪”值。以此为基础来解读 IF 函数，便可知其处理过程是这样的：第一参数的逻辑式若为真，也就是

    说 ISERROR 函数结果为真，则返回第二参数的空白值，否则将

    继续处理公式。用 SUMIF 函数统计多个条件的方法

    追加带有统计条件的“工作列”

    SUMIF 函数和 COUNTIF 函数，都是用于计算符合条件的单

    元格的总和，以及单元格个数的函数。如果想使用这两种函数计

    算出2个条件以上的统计结果的话，我们需要稍微动一下脑筋。

    比如下表，仅在单元格 H4中为 A 列负责人“吉田”、B 列商

    品代码为“A001”这个条件下，在 D 列中显示销售额数值。SUMIF 函数第一参数只能指定1列。但在此表中，元数据中

    无法在1列中同时判定负责人和商品代码这2个条件。A 列只能判

    定负责人，B 列只能判定商品代码。

    这时候，就需要“在元数据中追加作为新的统计条件的数据

    列”。这样的做法，通常被称为追加“工作列”或“计算单元格”。

    我们来尝试添加结合负责人姓名和商品代码的数据列。具体

    操作如下。

    在单元格 E4输入下列公式，并一直复制粘贴到数据最后

    一行。=A4B4

    在单元格 H4输入下列公式：

    =SUMIF(E:E,G4H3,D:D)? 将单元格 H4中的公式复制至全表。在这里，设置绝对引用也十分重要。利用指定 SUMIF 函数

    的参数指定各个单元格时，按几次 F4 键就会像上面这样出现符

    号“”。

    然后，将最开始在 H4中输入的公式一直向右复制至 M 列，向下复制至第8行。这里，为使引用单元格不偏离正确的列和行

    ，设定了绝对引用。

    要重视简单易懂

    在2007版本之后的 Excel 中，追加了复数条件下也能统计数

    据总和的 SUMIFS 函数和 COUNTIFS 函数。甚至像前文中的例子一样，不需要追加工作列也可以求和。但是，如果统计条件增

    多，参数的指定就会变得复杂，因此，需要追加工作列，分成几

    个步骤来处理。

    另外，数组公式和 SUMPRODUCT 函数也可以用同样的方式

    处理，但就从简单易懂这点上来看，我还是推荐大家采用追加工

    作列这种方法来处理。第5章 Excel 中的日期与时间设置

    输入日期的基础操作

    Excel 中的日期为公历

    “在员工名单中输入了利用员工生日计算出年龄的函数，但

    是结果居然是0。”

    如果没有完全掌握在 Excel 中处理日期的基本方法，就会发

    生这样的事情。在本章中，我将告诉大家在 Excel 中输入日期的

    方法，以及时间数据的特性。

    首先来看一下关于输入日期的基本事项。有一项非常重要的

    原则是“必须按照公历格式输入日期”。例如，想要输入2014年1

    月1日，在半角模式下，按以下格式将公历年、月、日，并用“”

    隔开。

    201411

    此时，如果省略公历年份直接输入“41”，则画面显示如下。

    省略公历年份，输入41单元格内显示的是“4月1日”，并非以公历表示，但在算式栏

    中显示为“201441”。也就是说，不输入公历仅以“月日”格式输入

    的情况下，日期将自动变为输入当时的公历年即“今年”的日期。

    如果想要输入不是今年的日期，却不输入具体的年份，会导致单

    元格不显示公历年份，你也就很难注意到有错误。

    Excel 虽然有可以从出生日期计算年龄的函数，但实际输入

    的过程中不小心漏掉公历年份的话，所有的数据都会自动变成“

    今年”的。因此，无论你是否要输今年的日期，一定要将年、月

    、日全部输入到单元格中，并用斜线()隔开。这样虽然有点麻

    烦，但一定要记住这是最基本的操作。

    专栏 如何快速输入今天的日期与现在的时间

    想要快速地输入今天的日期，使用快捷键 Ctrl + ; 最方

    便。按下快捷键，在活动单元格中会自动显示今天的日期。

    顺便一提，按 Ctrl + : 可以输入现在的时间。也许有人会

    问“谁会使用这个啊？”工作中用 Excel 做会议记录时，有时会需

    要记录发言的时间。这时，就会用到这个技巧。

    日期?时间实际为序列值Excel 中的日期数据几乎都是以“201411”的形式显示在单元

    格中的。而日期数据的实质其实是“序列值”。

    比如说，在单元格 A1中输入201411，在设置单元格格式的

    选项中可以将 A1的显示形式变更为“数值”，就会出现41640。这

    就是序列值。

    这种序列值，按照“以1900年1月1日为第1天”的算法，算出

    单元格中的日期为第几天。那么，2014年1月1日从1900年1月1日

    算起正好是第41640天，所以“201411”的序列值即为41640。

    “单元格输入1，出现了‘190011’。这什么意思啊？”

    我经常听到这样的疑问。这是因为目标单元格的表现形式变

    成了日期的缘故。这时候，如果将单元格的格式改回“数值”或“常

    规”，就会正常地显示数字“1”了。

    实际处理日期数据时，一般不需要在意序列值。明明输入的

    是日期却出现“41702”这种数字，如果发生这种状况，我们需要

    知道这是代表日期的“序列值”，其原因是单元格的格式为“日期”

    而不是“数值”或“常规”，这样我们就可以做相应的处理了。

    在看 Excel 函数的相关解说时，若是看到“做成序列值”“将参

    数指定为序列值”这种说法，要意识到“序列值=日期”。Excel 中

    在处理关于日期的数据时，比如计算天数、年龄，从日期数值中

    得出星期几的函数，就是利用这种序列值处理的。

    例如，用 Excel 计算从2014年3月28日到2014年4月3日为止一

    共有多少天。我们可以在单元格 A2输入“2014328”，B2单元格

    输入“201443”，为了得出这两个日期之间的天数，在单元格 C2

    输入下列公式。

    =B2-A2

    这样，从 B2的日期减去 A2日期得到的结果“6”会显示在单元格 C2中。

    单元格 B2的日期数据“201443”，对应的序列值为41732。

    单元格 A2的日期数据“2014328”，对应的序列值为41726。

    用 B2的序列值减去 A2的序列值，即“41732-41726”，就可以

    得出“6”这个答案。

    经常能够遇到的情况则是：像 A2为“20140328”、B2为“2014

    0403”这样的形式，虽然在 Excel 中不会被当成数据来处理，如果

    直接将这两个数据看作是日期并做减法，想要计算出这中间的天

    数，是无法得出正确结果的。

    这2个数据说到底只是代表“20140328”这个数字，并不是指“2

    014年3月28日”这个日期。因此，在输入有“=B2-A2”的单元格，虽然是将上述2个八位数做减法，却会得出“75”这个结果。这时

    ，我们应该把代表日期的序列值改为日期形式再进行计算。(参

    考150页)。处理时间数据

    时间数据的序列值为小数

    接下来我们来看一下如何处理具体时间。一般输入时间数据

    时，需要用“:”隔开时、分、秒，如下：

    13:00:00

    在记录田径竞技成绩时一般需要精确到秒，而在管理工作时

    间等事务时不必精确到秒，只用“：”区隔小时和分即可。

    时间的数据也可以转换成序列值。日期的序列值为整数，而

    时间的序列值则为0～1的小数。

    日期的序列值，以1900年1月1日为起始(即1)，每加上1就

    代表第二天的日期(Excel 能够处理的最后日期为9999年12月31

    日，其序列值为2958465)。另一方面，时间的序列值，以上午0

    时0分0秒为起始(即0)，每多1秒就会加上“186400”。因为，一

    天是24(时)×60(分)×60(秒)=86400(秒)，因此第二天上

    午0时0分0秒的序列值为1。

    【例】

    上午6:00的序列值：0.25

    中午12:00的序列值：0.5

    下午6:00的序列值：0.75

    虽然，在实际操作中我们没有必要记住这些序列值，但是与日期相同，如果单元格的格式被设置为常规，单元格中就会出现

    不明所以的小数。这时候，我们要知道这是“时间的序列值”，并

    且将单元格的格式更正为“时间”。

    容易出现误差的地方

    计算机在处理小数点以后的数值的计算时肯定会出错，我们

    一定要牢记这一点。Excel 在计算含有小数的数值时，无法得出

    正确答案。计算机的数据是以二进制表示的，如果公式中存在无

    法识别的小数数值，在计算时就会出现误差。在用 Excel 计算序

    列值为小数数值的时间数据时，也同样会发生这一问题。

    例如，将 B 列的开始时间与 C 列的结束时间做减法，在 D

    列中显示经过的时间。A 和 B 的经过时间在目标单元格中皆显示

    为1:01，但比较这两个单元格，却判断为不同值(D4单元格)。

    明明经过了相同的时间，却被判定为不同值

    之所以会发生这种情况，是由于各时间数据中实际上包含了

    以秒为单位的数值，如果不知道一些简便的处理方法，在进行相

    关处理时就会变得非常麻烦。如何输入正确的时间

    如果要详细解说应该如何处理时间数据，反而会阻碍大家的

    理解……真要详细地讲，那么这样的解说将会变成读起来都会觉

    得很厌烦的长篇大论。所以，在这里我只给大家介绍解决对策。

    首先，我们来了解一下 TIME 函数。它是处理时间数据的函

    数，能够指定时、分、秒。比如要制作“9:30:00”这样的时间数据

    ，我们可以输入下面的公式：

    =TIME(9,30,0)

    反过来，单元格 A1中含有时间数据(如“9:00”)时，想要从

    此单元格中分析出小时、分、秒的数值的话，就要用到 HOUR 函

    数、MINUTE 函数和 SECOND 函数。分别可通过以下公式导出

    相应的数值。

    =HOUR(A1) : 导出单元格 A1中时间数据的小时数

    =MINUTE(A1) : 导出单元格 A1中时间数据的分钟数

    =SECOND(A1) : 导出单元格 A1中时间数据的秒数

    在处理任何时间数据时都可以用下面的函数公式，这样能够

    导出绝对没有误差的时间数据(假定单元格 A1中含有时间数据)。

    =HOUR(A1)60+MINUTE(A1)

    这样一来，如果单元格 A1中是“8:25”则会自动返回“505”这

    个数值。这个数字表示的是从“上午0:00”到“上午8:25”经过的分钟

    数，正好是505分钟。像这样，将时间数据转换为不含小数点的

    整数，就能在计算时避免出现误差。

    以上一个出现误差的案例，可通过以下方式解决。出现误差的时候，中途增加处理步骤

    在 E 列与 F 列中，输入前文中提到的相应函数，将开始时间

    与结束时间转换为分别距离上午0:00的分钟数。

    将 E 列到 G 列的单元格的格式改为“数值”。将这些转换后的

    数值相减，就会得到 G 列上的经过的分钟数，由于结果是不含小

    数点的整数，也不会产生误差。在单元格 G4中输入的是这两项

    经过的分钟数是否为相同值的判定逻辑式(=G3=G2)。结果为

    TRUE，就是说判定为经过的时间相同。快速设置日期与时间

    避免数据变为日期形式

    即便你不想输入日期，但只要输入“1-11”、“121”这类数据

    ，Excel 会自动认定该数据为日期数据，并将其转换成“1月11日”

    的形式。如果不需要自动转换，可选择下面的方法解决。

    将单元格的格式设置中的表示形式改为“文本”

    在开头处输入单引号(')

    顺带一提，想要显示分数形式的话，可通过以下方法输入。

    将表示形式改为“分数”

    像“0 12”这样的格式，在开头处输入0和半角模式下的空格。

    经常更新工作表的日期

    “这份订单的制作日期怎么还是上一周啊!”

    像订单这样的 Excel 表格，只是改变日期和内容来重复使用

    同一张工作表，经常会发生这种忘记更改相关项目的失误。为避

    免这样的情况发生，我们可以使用 TODAY 函数，自动将工作表的日期更新为当前日期。只要输入这个函数，之后就没有必要手

    动更新日期了。

    =TODAY

    输入=TODAY后显示的结果

    TODAY 函数在对截止交货期的天数、年龄、入社时间等需

    要自动计算的任务可以发挥很大的作用。如果要用 Excel 处理日

    期数据，熟练使用 TODAY 函数是我们最先需要掌握的技巧。

    但是，在使用 TODAY 函数修改订单等工作表中的日期栏时

    必须注意一点，那就是 TODAY 函数会实时更新当天的日期。直

    接保存 Excel 制作的订单后，工作表中的日期会自动调整为当前

    日期。因此，需要保留原始数据时，请把文件转存成 PDF 形式。

    想要将年、月、日分别输入不同的单元格时

    我在前文中曾经提过“在输入日期时，请务必用“”将年、月

    、日隔开。”但如果实际上这样操作非常麻烦。因此，需要“将年

    、月、日分别输入到不同的单元格，用作日期字段”，这样能够提高操作效率。但是想要分别输入不同的单元格时，需要将所在

    单元格的格式设置为非日期数据(序列值)，否则 Excel 就无法

    自动将之认定为日期形式来处理。也就是说无法进行天数、时间

    段和年龄等计算，也不能将这日期自动转换成星期。

    这时，要用到可以把年、月、日3个数值变为日期数据，即

    序列值的函数，那就是 DATE 函数。在导出显示日期形式的单元

    格中输入以下公式。首先，输入“=DATE(”，然后按住 Ctrl 键

    ，同时按顺序点击单元格 A2、B2、C2，这样能够快速完成操作。

    =DATE(A2,B2,C2)

    在单元格 D2中输入=DATE(A2,B2,C2)

    DATE 函数是按照顺序在第一参数到第三参数中输入年、月

    、日的数字，并以此制作日期数据(即序列值)的函数。想要计

    算不是正确日期格式的日期数据时，应该先使用 DATE 函数将其

    转换为日期数据。

    如果需要处理的日期数据为2014年1月1日，有时会用“20140

    101”的8位数值形式保存。如果想把它变为正确的日期数据，还

    是需要用到 DATE 函数来处理。这时，我们就用到后面接下来会

    介绍的 LEFT 函数、MID 函数、RIGHT 函数，分别抽出相应的

    年、月、日的数据，再逐个组入 DATE 函数。【例】

    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

    关于这个技巧，我会在下一章的关于字符串操作中详细讲解。

    如何从日期数据中导出年、月、日

    那么相反地，如果想从日期数据中提取出年、月、日的数据

    ，需要用到 YEAR 函数、MONTH 函数、DAY 函数。例如，从

    单元格 A1中的日期数据分出年、月、日数据，可利用相应的函

    数按以下方式导出。

    =YEAR(A1)?A1的公历年

    =MONTH(A1)?A1的月份

    =DAY(A1)?A1的日期熟练运用函数，快速设置日期和时间

    随时查看距截止日期还有几天

    在利用 Excel 管理客户档案时，最方便的莫过于能自动显示

    距离每位客户的生日、合同的更新日期还有几天这样的数据。如

    果想要在含有更新日期数据表格中的“剩余天数”一栏，实时计算

    出“距离更新日期还有几天……”，可以用“更新日期减去当前的日

    期”。

    例如，按以下方式输入，就能导出截止到单元格 B2中的日

    期的剩余天数。

    =B2-TODAY

    在单元格 C2中输入=B2-TODAY

    B2中的日期数据所对应序列值，与 TODAY 函数导出的当前

    日期的序列值，二者相减就会得出上述结果。“利用序列值来处理日期的相关计算”，希望诸位读者朋友能够从这个事例中掌握

    这个诀窍。

    如何导出除周末和节假日外的营业天数

    想要计算除双休日和节假日外距某个截止日期的营业天数，可以使用 NETWORKDAYS 函数。在一般的工作中，这种计算营

    业天数的案例十分常见。

    由于 Excel 本身并不配备节假日的相关数据，因此我们在前

    期需要另外准备节假日一览表。在此制作一个以“节假日表”命名

    的工作表，然后照着下表制作一张节假日一览表。可以在网络上

    搜索节假日数据表。

    节假日一览表在单元格 A2中输入交货日期，想要计算出除去周末和节假

    日外距离该交货日期的工作日还剩几天时，只要用“当前日期”减

    掉“除周末和节假日外的截止日期”即可，公式如下：

    =NETWORKDAYS(TODAY,A2,节假日表!A2:A195)

    此函数的参数表示意义如下：第一参数：日期计算的开始日

    第二参数：日期计算的结束日

    第三参数：需要从日期计算过程中去掉含有节假日的范围

    这种方式可以得出“距离今天为止还有多少工作日”的结果，所以开始日期中要填入 TODAY 函数。

    第三参数用于指定节假日，在这一例子中实际指定为“节假

    日表”中含有节假日日期数据的单元格范围(即 A2：A195)。如

    果要把公司自己规定的休息天数考虑进去的话，可根据需要自行

    调整第三参数。

    自动计算年龄

    Excel 还有种函数，输入出生日期后，可以自动计算出年龄

    ，它叫作 DATEDIF 函数。每天花几小时查看出生日期，如果发

    现当前日期是生日的话再手动将年龄数据加1……我见过不少会“

    永无止尽”重复如此操作的案例。在此提醒各位，只要掌握这个

    函数，就可以完全避免花费大量时间进行如此麻烦的操作。

    DATEDIF 函数的结构如下：

    【格式】

    =DATEDIF(起始日期,结束日期,单位)

    通过指定起始日期与结束日期，得出间隔的数据。

    第三参数则根据想要如何表示间隔数据的单位，进行指定。

    “Y”?年

    “M”?月“D”?日

    计算年龄数据时需要选择“年”来作为单位，因此需要按照以

    下方式输入(假定 B2为出生日期)。顺带一提，这个函数无法

    使用辅助输入功能，必须手动输入“=DATEDIF(”。

    =DATEDIF(B2,TODAY,Y)

    在单元格 C2中输入=DATEDIF(B2,TODAY,Y)

    想要通过这种方法自动计算出年龄，将出生日期指定为起始

    日期后，通常会输入能够导出当前日期的 TODAY 函数作为结束

    日期。接着，从出生日期到今天为止所间隔的时间以年作单位来

    表示的话，需要在第三参数单位中输入“Y”。

    想计算出准确的结果，需要准确无误地输入公历年的出生日

    期。如果不了解日期数据的基础，只输入月份和日期的话，那么

    年份则会变成当前年份，这样就无法计算出正确的年龄。所以，请一定记住“所有日期都要从公历年开始输入”。

    用“×年×个月×日”表示年龄和间隔期间的数据

    在实际的工作中经常会遇到“用“×年×个月×日”表示年龄和间

    隔期间”这样的事。想要完成此项操作，需要牢记如何导出除去

    年份后的从起始日期到结束日期的月份数(即×个月的部分)，或者除去年份和月份的数值后的起始日期到结束日期的天数(即

    ×日的部分)。

    若想计算出“×个月”部分，将第三参数的单位代码指定为“Y

    M”。

    在单元格 D2中输入=DATEDIF(B2,TODAY,YM)，得到月

    份的数值

    若想算出“×日”部分，将第三参数的单位代码指定为”MD”。

    在单元格 E2中输入=DATEDIF(B2,TODAY,MD)，得到天

    数这样，我们就能在不同的单元格中分别得出对应的数值。

    顺带一提，想要在一个单元格里得出“×年×个月”的结果，可

    用“”连结字符串等混合字段，从而实现组合输入数值与函数公

    式。

    如何从日期设置中导出星期

    Excel 还可以从日期数据中得到当前日期为星期几。掌握这

    个方法后，在制作日历和行程表时会非常有效率。

    Excel 中有个函数叫作 WEEKDAY 函数，其主要用途为返回

    某日期的星期数。但事实上，还存在一种比它更简单的函数——

    TEXT 函数。

    例如，想要在单元格 B2中显示单元格 A2中的日期为星期几

    ，我们可以在 B2中输入以下公式：

    =TEXT(A2,aaa)

    在单元格 B2中输入=TEXT(A2,aaa)这时候，按照第二参数的指定方法，星期几的表示形式则变

    为：

    “aaa”?日

    “aaaa”?星期日

    “ddd”?Sun

    “dddd”?Sunday第6章 快速处理字符串

    处理字符串的基础操作

    将单元格中的一部分字符串移至其他单元格内

    Excel 的方便之处不仅限于统计数值这一项，字符串在迅速

    处理单元格内的内容时也发挥着强大的作用。这里，我向大家介

    绍一下在处理各种数据时必须掌握的字符串处理技巧。

    首先，我们需要掌握如何把单元格内的字符串的一部分提取

    至其他单元格的函数。这里所谓的“一部分”，指的是譬如“左数几

    个字”“右数几个字”“中间几个字”这样的范围。其对应的函数为 L

    EFT 函数、RIGHT 函数和 MID 函数。

    =LEFT(A1,3)?抽取单元格 A1左数3个字符

    =RIGHT(A1,4)?抽取单元格 A1右数4个字符

    =MID(A1,5,2)?抽取单元格 A1第5个字开始的2个字符

    LEFT 函数与 RIGHT 函数，第一参数指定单元格的左起或右

    起，第二参数指定只返回多少个字符。MID 函数，第二参数指定开始提取的位置，第三参数指定抽

    取的字符数。

    代表日期的8位数变为日期数据

    这里，让我们来看一下如何运用这3个函数将表示日期的8位

    数值转换成日期数据。

    我前一章中曾提到过用 Excel 处理日期数据时，需要像下面

    这样用“”将年、月、日隔开。

    20131112

    但是，有些公司也会用“20131112”这样的8位数值来表示日

    期。但是，这并不是常规的日期形式，只是一种数值，我们也无

    法运用该数值计算出天数或星期。因此，我们需要先将其转换成

    日期的数据形式(序列值)。

    我们可以使用 DATE 函数制作序列值。比如，想要制作“201

    411”这个日期数据，首先按照下列方式，在第一参数中指定公

    历年，第二参数中指定月份，第三参数指定日期。

    =DATE(2014,1,1)

    那么，如何从单元格 A2的“20131112”中提取年、月、日的数

    值呢？请大家按照以下思路思考。

    “年”的数值，提取单元格 A2“20131112”左数4个字符“2013”

    “月”的数值，提取单元格 A2“20131112”第5个字开始的2个字

    符“11”

    “日”的数值，提取单元格 A2“20131112”右数2个字符“12”

    像这样，想要从目标单元格的数据中提取一部分文字，就要用到 LEFT 函数、MID 函数和 RIGHT 函数。

    想要抽取单元格 A2左数第4个字符，需要在 B2中输入以下

    公式：

    =LEFT(A2,4)

    在单元格 B2中输入=LEFT(A2,4)

    接下来导出月份数值。请按以下方式输入 MID 函数，在单

    元格 A2中从第5个字开始提取2个字符。

    =MID(A2,5,2)

    在单元格 C2中输入=MID(A2,5,2)

    最后提取日期数值。为了返回单元格 A2右数2个字符，按以

    下公式输入 RIGHT 函数。=RIGHT(A2,2)

    在单元格 D2中输入=RIGHT(A2,2)

    像这样，分别提取出年、月、日的数据后，再按照以下方式

    指定 DATE 函数的参数，我们就能够得到该日期的序列值。

    在单元格 E2中输入=DATE(B2,C2,D2)

    上述的操作步骤可通过以下公式在1个单元格中集中处理。

    =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

    LEFT 函数、RIGHT 函数和 MID 函数能够从字符串的左数

    或右数的，以及从字符串中间开始只提取指定的字符数，是字符

    串处理的基础操作。灵活运用这些函数，可以自由应对不同的数

    据处理需求。拆分字符串

    只从住址中选出都道府县

    “住址如果是以都道府县为开头的文本，现在需要把都道府

    县与下级地址数据分开”。

    这种操作是拆分字符串的基础。从根本上来说，为避免后期

    进行这样的操作，应该在制作工作表时“就将都道府县放入单独

    的单元格中”。但是，如果在原工作表中已经是同时出现在一个

    单元格中的状态的话就必须要拆分单元格了。这时，我们需要掌

    握如何将都道府县的数据单独提取到其他单元格中。

    想要解决这个问题，仅仅熟知 Excel 中的功能和函数是不够

    的，重点在于以独立思考出多种处理方法。

    首先，我们来思考这一问题“日本的47个都道府县名是什么

    类型的数据呢？”大多为3个或4个文字吧。

    其中，4个字的只有“和歌山县”、“神奈川县”、“鹿儿岛县”这

    3个县。四个字的县名，每个都搭着“县”字，剩余全部都是3个字。

    明白这一点，就能按照以下逻辑，从住址单元格中提取出都

    道府县的数据了。

    “如果住址单元格中的第4字为‘县’，只抽选左数4个字符；(

    第4字不是‘县’)否则，只抽选左数3个字符”。

    以上逻辑若转换为 Excel 函数，就是下面的公式。

    =IF(MID(A2,4,1)=县,LEFT(A2,4),LEFT(A2,3))复制粘贴含有这一公式的单元格，就能做到提取所有单元格

    中的都道府县名。

    在单元格 B2中输入=IF(MID(A2,4,1)=县,LEFT(A2,4),LEFT(

    A2,3))，一直复制到单元格 B12

    “第4字符为‘县’”这一条件，就是”从地址单元格的4个字中只

    提取1个字符的结果即为‘县’”，可以使用 MID 函数实现这一点。

    根据这一逻辑的判定真伪结果不同，用 LEFT 函数改变提取的字

    符数，并用 IF 函数指定操作。

    如何从住址中区分都道府县与下级地方行政区

    那么，在前文的表格中，如何在 C 列中提取除都道府县外的

    市町村等级别的数据呢？在这一点上，还是“思考方法”最为重要，并且“思考有什么更

    简便的方法”也很重要。

    我们需要事先了解 Excel 具体有何种类型的函数。即便不清

    楚，也应该思考“使用什么函数可以完成这项处理”？

    首先，想从住址中提取都道府县的话，使用 LEFT 函数确定“

    从左开始抽取多少文字”。另一方面，想提取出市町村的话，就

    要考虑“从右开始提取多少文字”，此时使用 RIGHT 函数。

    接下来的处理需要用到能够“计算单元格内字符数”的函数。

    这时我们要用到 LEN 函数。LEN 就是 Length(长度)的意思。

    通过以下公式，得出单元格 A1中的字符数。

    =LEN(A1)

    了解这个函数后就会获得好的想法。

    在前文的例子中，A 列中有地址数据，旁边的 B 列中只提取

    出都道府县的数据。在这个状态下，想要在 C 列中提取都道府县

    以下的行政区的数据，就需要思考在 A 列中需要从右数提取多少

    字符。答案如下：

    “从地址栏的字符数中减去都道府县栏的字符数，从 A 列中

    数据的右侧开始提取。”

    可以利用以下公式实现这一点。从单元格 A2内右侧开始，提取单元格 A2的字符数减去单元格 B2字符数的字符数。

    =RIGHT(A2,LEN(A2)-LEN(B2))

    将这个公式输入单元格 C2，一直复制到数据最后一行，就

    可提取出所有地址中都道府县以下的地方行政区的数据。

    在单元格 C2中输入=RIGHT(A2,LEN(A2)-LEN(B2))，一直复

    制到单元格 C12如何从姓名中分别提取姓氏和名字

    运用连字符“”可以合并字符串，但是要拆分字符串多少有

    点复杂。例如，像下面这样用半角空格隔开姓氏和名字的情况下

    ，怎样才能把姓氏和名字分别提取到不同单元格中呢？

    姓氏与名字以半角空格隔开的数据这种情况下，如有半角空格等形式的“分隔文字”(将空格视

    为1个字符)，其实也能做到把空格前后的数据提取到不同单元

    格中。我们来看一下操作顺序。

    提取姓氏

    首先提取姓氏数据。提取单元格中的姓氏就是说“从左开始

    提取多少单元格内的字符串中的字符”，这里要用到 LEFT 函数。

    问题在于如何提取指定的字符数。

    这里，我们需要知道“分隔文字是第几个字”。例如，单元格

    A2中的“吉田拳”，其分隔文字是半角空格，是第3个字符。接下

    来，用3减去1可以得出2，也就是说从左开始抽取2个字符即可得

    到姓氏。换句话说就是这样：

    “分隔文字为第几个字符，用这一数字减去1所得到的数字，就是需要从字符串左边开始提取的字符数。”

    公式如下：

    =LEFT(A2,FIND( ,A2)-1)

    接下来，要注意如何在第二参数中使用 FIND 函数。这是用

    于定位指定文字在单元格内的位置的函数。

    并且，这样连续输入两个引号则表示“空白”，如果在

    双引号之间加入半角空格( )，则表示“半角空格”。

    将这一公式输入进单元格 B2，就可以在 B2中提取单元格 A2

    中的半角空格之前的字符，在这里就是姓氏数据。

    在单元格 B2中输入=LEFT(A2,FIND( ,A2)-1)我们既然已经知道分隔文字的半角空格是第3个字符，那么

    要想提取姓氏，需要从字符串左侧开始应该提取的字符就是3减1

    ，即两个。这样，就能够只提取出“吉田”这两个字，也就是位于

    字符串最左侧的两个字符。

    提取名字

    接下来，我们来提取名字。这次需要从右开始提取，所以要

    用到 RIGHT 函数。问题在于应该如何设定“从右侧开始提取的字

    符数”。我们可用下面的函数公式处理。

    =RIGHT(A2,LEN(A2)-FIND( ,A2))

    在单元格 C2中输入=RIGHT(A2,LEN(A2)-FIND( ,A2))在 RIGHT 函数的第二参数中，使用 LEN 函数和 FIND 函数

    指定了需要提取的字符数。“用单元格 A2的字符数减去单元格 A

    2中半角空格所在第几个文字后得到的数字”，按照这样的方式进

    行计算。在这个例子中，单元格 A2的字符数是4(半角空格也算

    作1个字符)。

    半角空格是第3个文字，所以4-3=1。在单元格 A2的右侧开

    始提取1个文字，即半角空格之后的字符，也就是提取出名字。

    接下来，将公式复制到下面几行，就能进行同样处理了。

    将公式复制到其他单元格

    但是，这种处理方式，如果遇到没有空格的情况(此例中，姓氏与名字之间没有半角空格)就无法使用了。最初在输入数据

    时的操作会给后续的操作带来影响，因此需要谨慎考虑。原则上

    来说，最好的办法就是“尽量做细致划分”。后面可根据实际情况

    再行合并单元格或字符串。整理数据

    如何判断字符串是否相同

    我们在手动输入数据时，很容易发生格式不统一的情况。如

    果想将单元格中的数据整理成统一的格式，就需要花费大量的时

    间。

    比如，在全角格式下输入的电话号码。为了检查客户名单中

    是否存在重复，我们需要以电话号码为标准，使用 COUNTIF 函

    数判定是否存在重复。这时，我们需要将所有的电话号码整理成

    统一的格式。即便是分别用全角和半角格式输入的相同的电话号

    码，在 Excel 中也不能将其判定为相同数据。

    如下例，在 A 列中输入了两个相同的电话号码，但是单元格

    A2中的数据为全角格式，单元格 A3为半角格式。单元格 B2输有

    EXACT 函数(下列公式)，用来判定两个字符串内容是否相同。

    =EXACT(A2,A3)

    判断单元格 A2与单元格 A3中的字符串是否相同(单元格 B2)EXACT 函数，指定参数的两个字符串如果相同为 TRUE，不同则返回 FALSE。因此在该例中，结果为 FALSE(不同)。

    这种情况下，我们需要统一数据格式。这种操作在不少处理

    字符串相关的函数中发挥着作用。

    如何把全角字符转化成半角字符

    想要将全角字符改为半角字符，我们可以使用 ASC 函数。

    例如，利用下面的函数公式可以将单元格 A2中的全角字符

    变成半角字符。

    =ASC(A2)

    在单元格 B2中输入=ASC(A2)如何删除指定文字

    接下来，将单元格 B2中删除连字符(-)后的数值提取到单

    元格 C2中。像这样，想要删除指定文字时，可以使用 SUBSTIT

    UTE 函数。SUBSTITUTE 意为“替换”。

    =SUBSTITUTE(B2,-, )

    在单元格 C2中输入=SUBSTITUTE(B2,-, )这个函数，是在第一参数指定的字符串的范围内，是将第二

    参数指定的文字替换为第三参数指定的文字。在这个例子中，第

    三参数为 (空白)，将连字符替换为空白，就是删除连字符。

    整合这些逻辑的话，就是下面的公式。先用 ASC 函数转换

    为半角形式的字符串，再用 SUBSTITUTE 函数将连字符替换为

    空白。

    =SUBSTITUTE(ASC(A2),-, )

    把上面的公式一直复制粘贴到数据最末行，表格中所有电话

    号码就会变成统一的格式。

    将=SUBSTITUTE(ASC(A2),-, )一直复制粘贴到数据最后

    一行快速处理文字

    连续输入26个英文字母

    Excel 设有“自动填充”功能。比如在单元格 A2中输入“星期一”，向下复制粘贴，就能自动从“星期一”开始连续填入数据。

    在单元格 A2中输入“星期一”，向下复制粘贴，自动连续填充

    数据

    这种自动填充其实无法连续输入英文字母。但是，“想要从

    A 开始按顺序连续输入项目名称”这种需求非常常见，解决方法

    有两种。

    使用 CHAR 函数

    比较简单的方法就是使用 CHAR 函数。它可以将参数指定的

    字符代码转换成字符。比如，英文字母 A 对应的字符代码为65。也就是说，输入下

    列公式的单元格中会显示 A。

    =CHAR(65)

    将字符代码65改为66则得出 B。也就是说，每次增加1个字

    符代码且连续输入 CHAR 函数的话，就会在表格中连续输入英文

    字母。比如，想要从单元格 A2开始沿列连续输入英文字母，那

    么我们可以在参数中嵌入 ROW 函数，输入以下函数公式：

    =CHAR(ROW+63)

    单元格 A2位于工作表的第2行，因此这一公式的 ROW 函数

    在 A2中会得出2。以2为调整数值加上63，就能得到 A 的字符代

    码65。

    输入这个公式并复制粘贴到其他单元格中，就能输入连续的

    英文字母了。

    在单元格 A2中输入=CHAR(ROW+63)，一直复制粘贴到单

    元格 A27? 使用 SUBSTITUTE 函数与 AD ......