日志

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

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

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

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

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

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

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

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

➊ 选择单元格范围 E3：E11

选择单元格范围 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 的基础功能无法完成的处理时，也要有这样的精神。

Excel如何删除指定文字（替换指定内容）

接下来，将单元格 B2中删除连字符（-）后的数值提取到单元格 C2中。像这样，想要删除指定文字时，可以使用 SUBSTITUTE 函数。SUBSTITUTE 意为“替换”。

=SUBSTITUTE(B2,”-“,” “)

在单元格 C2中输入=SUBSTITUTE(B2,”-“,” “)

这个函数，是在第一参数指定的字符串的范围内，是将第二参数指定的文字替换为第三参数指定的文字。在这个例子中，第三参数为” “（空白），将连字符替换为空白，就是删除连字符。

整合这些逻辑的话，就是下面的公式。先用 ASC 函数转换为半角形式的字符串，再用 SUBSTITUTE 函数将连字符替换为空白。

=SUBSTITUTE(ASC(A2),”-“,” “)

把上面的公式一直复制粘贴到数据最末行，表格中所有电话号码就会变成统一的格式。

将=SUBSTITUTE(ASC(A2),”-“,” “)一直复制粘贴到数据最后一行

Excel如何判断字符串是否相同（内容找不同并标出）

我们在手动输入数据时，很容易发生格式不统一的情况。如果想将单元格中的数据整理成统一的格式，就需要花费大量的时间。

比如，在全角格式下输入的电话号码。为了检查客户名单中是否存在重复，我们需要以电话号码为标准，使用 COUNTIF 函数判定是否存在重复。这时，我们需要将所有的电话号码整理成统一的格式。即便是分别用全角和半角格式输入的相同的电话号码，在 Excel 中也不能将其判定为相同数据。

如下例，在 A 列中输入了两个相同的电话号码，但是单元格 A2中的数据为全角格式，单元格 A3为半角格式。单元格 B2输有 EXACT 函数（下列公式），用来判定两个字符串内容是否相同。

=EXACT(A2,A3)

判断单元格 A2与单元格 A3中的字符串是否相同（单元格 B2）

EXACT 函数，指定参数的两个字符串如果相同为 TRUE，不同则返回 FALSE。因此在该例中，结果为 FALSE（不同）。

这种情况下，我们需要统一数据格式。这种操作在不少处理字符串相关的函数中发挥着作用。

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

这里，让我们来看一下如何运用这3个函数将表示日期的8位数值转换成日期数据。

我前一章中曾提到过用 Excel 处理日期数据时，需要像下面这样用“/”将年、月、日隔开。

2013/11/12

但是，有些公司也会用“20131112”这样的8位数值来表示日期。但是，这并不是常规的日期形式，只是一种数值，我们也无法运用该数值计算出天数或星期。因此，我们需要先将其转换成日期的数据形式（序列值）。

我们可以使用 DATE 函数制作序列值。比如，想要制作“2014/1/1”这个日期数据，首先按照下列方式，在第一参数中指定公历年，第二参数中指定月份，第三参数指定日期。

=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 函数能够从字符串的左数或右数的，以及从字符串中间开始只提取指定的字符数，是字符串处理的基础操作。灵活运用这些函数，可以自由应对不同的数据处理需求。