Excel22 – 第185页

Excel框线全部统一为同一种类

在制作 Excel 表格时一定会用到框线。除常见的实线外，Excel 中还有虚线、粗线等框线。但考虑到操作效率，建议不要在表格中使用过多种类的框线，最好全部统一成实线。

像下面的例子，项目单元格框线用实线，而下面几行则用虚线，这样就会让表格看上去更清楚了，呈现突出重点的效果。

项目用实线，接下来用虚线的表格

虽然这个表格看起来用心设计了排版，实际上却并不会对工作结果带来任何好处。

另外，例如在单元格 D2中输入“数量×单价”的计算公式“=B2*C2”后，一直拖拽复制到 D 列最下方的单元格，那么单元格的格式也会被一并复制过去，好不容易设置的虚线框线就会都变成实线了。

原本虚线的框线都变成实线了

针对这种状况，有个方法可以在不复制格式的状态下复制公式。（选择格式粘贴功能，按下Ctrl +Enter ，在多个单元格中输入内容；右击的拖拽复制）。但是，使用这些功能将原本的框线设置为虚线，这多出来的2个步骤，并不会给提高工作效率带来实际的帮助。这样看，将表格中的框线都统一为实线的话，可以大幅提高工作效率。

表格的格式与工作成果并不存在因果关系。即便表格看上去很整齐，没有实际内容的话也是毫无意义。修饰表格可以放到后期的工作中，一定要先弄清工作中的优先顺序。

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

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

第1种，使用 CLEAN 函数。例如，在含有以下公式的单元格中，会返回消除单元格 A1的换行后的数值。

=CLEAN(A1)

另一种方法就是使用替换功能。例如，想要一次性消除 A 列中所有单元格的换行，可进行如下操作。

➊ 选择 A 列，按Ctrl +H 启动替换功能

➋ 点击【替换】菜单后，按下快捷键Ctrl +J （菜单中没有显示内容，不要介意，继续操作）

➌ 点击【全部替换】➛点击【关闭】

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

制作表格时，特别是当项目名称无法完全显示在一行单元格中，就需要换行输入。想要在 Excel 的单元格中换行，可以在需要换行的位置按下面的快捷键。

Alt +Enter

切记不要按空格换行。

但是，我们要注意，换行后的单元格的值，会与换行前的单元格的值有所不同。比如下面这个例子，单元格 A2与单元格 B2分别输入“大大改善”。单元格 B2在“大大”之后按下Alt +Enter 进行了换行处理。在单元格 C2中输入 EXACT 函数来检查两个单元格内的数值是否相同。结果是返回 FALSE。也就是说，这两个单元格内的数值并不相同。

内容都是“大大改善”，换行后却变成了不同的数值

因此，在处理用于 VLOOKUP 函数的检索值、检索范围等数值的换行时有必要注意这一点。如果有对某一个数值进行过换行处理，那么在计算时就有可能无法得到预期的结果。这是因为这个快捷键具有“强制换行”的意思。

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

作为使用条件格式的应用实例，我们可以制作出下表这样每隔一行填充颜色的条纹式表格，让数据看起来更清晰。

每隔一行填充颜色的表格

当然，这种操作也绝对不能“逐个手动填充”，务必牢记要将复杂的操作变得轻松、简单。

在这个例子中想法最重要。如何利用条件格式设置“每隔一行填充颜色”呢？

答案是“仅对奇数行或偶数行填充颜色”，这样就能每隔一行填充颜色了。

例如，要给奇数行填充颜色，那么针对表格内的单元格，设定“若此单元格为奇数行则填充颜色”。单元格的行数可用 ROW 函数取得。若用 ROW 函数得到的行数是否为奇数，可以用“该数字除以2余1则为奇数”这个逻辑进行判定。让我们看一下具体的操作步骤。

➊ 选择想要设置的范围

➋ 【开始】菜单栏➛【条件格式】➛点击【新建规则】

➌ 选择【使用公式确定要设置格式的单元格】

➍ 栏目中输入以下公式

=MOD(ROW(),2)=1

➎ 【格式】➛在【填充】中选择喜欢的颜色点击确定➛回到【新建规则】点击确定

这样就可以做到给每隔一行填充颜色。

每隔一行就填充上了颜色

此处出现的 MOD 函数，能够得出第二参数指定的数值除以第一参数指定的数字后得到的余数。下列逻辑式，针对在指定的单元格范围中的各个单元格设定了“ROW 函数取得的数字被2除余1”的条件。

=MOD(ROW(),2)=1

这样就能够设定给在选中的范围内符合这一公式的单元格填充颜色。

制作易懂的表格：如何将比去年下降100%的单元格标红

用于特定单元格的内容在符合某种情况下突出显示。一般企业在判断销售额等业绩时，最常用的指标是“与去年相比”。

今年的销售额比起去年提高了多少百分比？或者下降了多少？

如果有所下降，那原因又是什么？

那么应该如何验证，锁定特定原因，然后再讨论解决对策呢？

可以通过“给与去年相比增长率小于100%的单元格填充颜色”这样的处理方式，强调与去年的对比。但如果手动给目标单元格填充颜色的话，操作起来会非常麻烦。

能够自动完成这样的操作就是“样式”功能。它可以根据单元格内的数值自动调整单元格的格式。

例如，在计算对比去年的数据的单元格 E3～E11，如果单元格内的数值小于100%，则将此单元格填充为红色。

➊ 选择单元格范围 E3：E11

➋ 【开始】菜单栏➛【条件格式】➛点击【新建规则】

➌ 选择【使用公式确定要设置格式的单元格】

➍ 在【为符合此公式的值设置格式】栏中输入下列公式

=E3<100%

这时，【为符合此公式的值设置格式】中点击1次，工作表中预先选择的范围里点击只有1处为白色的单元格 E3。

接着，就会出现“=$E$3”的绝对引用形式，按3次F4 键，去掉$。

之后，输入后续的公式。

➎ 为了设定逻辑式为真时应该显示怎样的格式，可以点击【格式】打开【设置单元格格式】，在【填充】栏中选择红色，点击确定。

➏返回这个画面，再次点击确定

➐在选择范围内，仅指定更改数值低于100%的单元格的格式

我们不应该花费大量时间去做 Excel 能够自动完成的操作。即便事先不知道 Excel 的基础功能，也要尝试寻找“有没有更简单的方法”。

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

另一方面，如果是以下载的数据或累积的数据为材料进行加工、整理，并根据要求制作资料，就不只是输入函数这么简单了，我们必须要思考“利用何种材料，制作出何种资料”。

用 Excel 制作数据分析资料的基础为以下3点。

➊ 从数据库形式的表格，转换成由纵轴和横轴组成的倒 L 字形矩阵表

➋ 再次设定项目

“将按日计算的数字改为按月计算”

“将按都道府县计算的数字改为按地域计算”

像这样，大多数情况下会根据不同目的，将细分单位的项目转换为较大单位的项目。准备多重变换模式后，可以通过 VLOOKUP 函数处理。

还可以添加与前年的对比、预测&实际对比、结构比率等各种分析现状时需要的项目。至于应该添加怎样的项目，我会在第8章中详细说明。

➌ 需要定期制作、更新的资料，应预先在表格内输入函数

需要定期制作、更新的资料，不仅要花费大量时间进行复制粘贴，还容易发生粘贴错误数据等失误。这时，我们需要事先在表格中设计这样的结构：预先确定表格的格式，然后在表格中输入函数，使其能够自动统计数据并填充表格。如此一来，我们只要把材料数据粘贴到固定位置就能立刻完成表格更新。具体来说，这需要用到 SUMIF 函数和 COUNTIF 函数。

关于具体的操作步骤我会在第8章中详细解说，请大家先记住一个原则：关于使用数据透视表或自动填充功能来制作表格这项操作，还有很大余地能够对其进行改善。多数情况下，改为用函数来处理，就能够大幅度提高操作效率，节省工作时间。

接下来，我将以上述注意事项为基础，给大家介绍一些能够提高制作表格效率的功能与技巧。

在单元格中输入数据，制作表格（制作数据库）

例如，在 Excel 中输入名片信息，或者销售额和交易信息等操作就是这种形式。

这时，工作表的形式必须是“数据库形式”（也被称作“清单形式”）。例如，含有名片信息的数据库就是下表的形式。

输有名片信息的数据库

在第1行中输入项目名称，第2行之后逐行输入数据。

第1行设定的项目要尽量细致

第1行设定的项目要尽可能的详细，这样在进行后续工作时才会更加方便。例如，在地址栏中分开输入都道府县和下级行政区，那么之后在计算在各都道府县的客户的分布状况等工作就会变得简单。因为，我们可以使用&或 CONCATENATE 函数将不同单元格的数据连接到一起，但拆分单元格中的内容会花费大量的时间。有时甚至无法使用函数拆分单元格内容，只能手动完成。

每列数据中设定输入有效性

接着，为了能够提高将信息输入数据库表格的效率，最重要的就是预先在每列数据（每列内容叫作“Field”）中按照各个项目，设定“输入规则”（之后会具体解释）。

另外，像下面这样灵活运用各种不同类型的函数，就能提高工作效率。

在年龄栏中输入 DATEDIF 函数后，只要输入出生日期，就会自动显示年龄

这样的表格被广泛应用于客户、销售额的管理中，而在日常工作中也经常利用这种表格制作各种数据分析的资料。

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

极端地说，在工作中使用 Excel 具体要做的事情就是“制作表格”。无论是会议资料，还是订单、财务数据，最终都要以“表格”的形式展现。因此要点在于，思考“应该制作怎样的表格”。

“用 Excel 制作表格”这项工作，大体可分为2种类型。

第一种，将数据输入新建的工作表。例如，输入名片的信息或日销售额数据。这种操作叫作制作“数据库”。

第二种，以 Excel 工作表中已经存在的数据，即以“数据库”为材料，制作出分析表或订单等“商务文本”或“资料”。

无论哪一种类型，我们需要先确定表格的格式。然后再思考为了快速输入需要的数据应该采取的操作顺序。

接下来，我们来看一下需要注意的地方。

Excel计算特定字符在单元格中的数量（查找统计指定内容）

如果只是计算单元格内的字符数，用 LEN 函数就能做到。但如果想要计算单元格内特定字符的个数，应该怎么做？

像下图这样，数据表的 A 列中含有表示 URL 的字符串。

A 列中含有 URL 数据

这时，我们应该如何计算 A 列中斜线符号（/）的数量，并让其显示在 B 列中呢？

像这样，想要计算单元格内特定的字符的数量，首先需要在单元格 B2中输入以下公式：

=LEN(A2)-LEN(SUBSTITUTE(A2,”/”,””)

将这个公式一直复制到数据最末行，就能提取每个 URL 中的斜线符号（/）的数量。

单元格 B2中输入=LEN(A2)-LEN(SUBSTITUTE(A2,”/”,” “)，一直复制粘贴到单元格 B6

在此，使用 LEN 函数与 SUBSTITUTE 函数，提取出在单元格 A2中有多少个“/”，公式如下。然后将2个数值相减即可得出想要的结果。逻辑如下：

单元格 A2的字符数减去 A2单元格字符串中除去“/”之后的文字数。

首先，用 LEN（A2）计算出单元格 A2的字符数，该数据为23。LEN(SUBSTITUTE(A2,”/”,””))的部分，是将 SUBSTITUTE 函数作为参数嵌入到 LEN 函数的。作为参数的 SUBSTITUTE 函数可以将单元格 A2中的斜线（/）替换为空白，然后再用 LEN 函数计算出单元格 A2中除去斜线后的字符数。由此可以得出此例中的单元格 A2的字符数为20。

二者相减后可得到3，就是单元格 A2中的斜线（/）的数量。

Excel连续输入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 函数与 ADDRESS 函数

下列公式，可设置成在 Z 之后继续输入 AA、AB 的形式。

=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1″”)

请大家先自行解读一下这个公式。将这个公式输入到第2行并向下复制粘贴。

“从无到有，需要自己创造。”

这是成年人在处理工作上的基本法则。而且，在想要做到使用 Excel 的基础功能无法完成的处理时，也要有这样的精神。