下载掌阅APP，畅读海量书库
立即打开
使用空行和空列也是一些人喜欢做的事情之一，导致在制作数据透视表时，无法创建透视表并弹出一个警告框，显示数据区域中有隐藏的空列。有时制作出的数据透视表数据不是求和而是计数，原因是数据区域有很多空行，导致必须重新设定计算。
对于基础表单来说，空行和空列尽管不影响使用函数和公式，但会直接影响数据分析工具（如数据透视表）的使用，所以要删除这些无用的空行和空列。
不论是空行还是空列，删除是很容易的，可以使用很多种方法，其中定位删除是最简单、最容易操作的方法。
图2-65所示是一个含有大量空行的表格，要求删除这些空行，结果如图2-66所示，详细的操作步骤如下。
图2-65 存在大量空行的表格
图2-66 删除所有空行的表格
步骤1 选择某个关键列（如果这列的某个单元格没有数据，就认为该行是空行，这里选择A列）。
步骤2 按F5键或Ctrl+G组合键，打开“定位”对话框，单击左下角的“定位条件”按钮，打开“定位条件”对话框，选中“空值”单选按钮，定位A列的空单元格，如图2-67所示。
步骤3 选择“开始”→“单元格”→“删除”→“删除工作表行”命令，如图2-68所示，即可将表格中的所有空行删除。
图2-67 定位A列的空单元格
图2-68 选择“删除工作表行”命令
删除空列也是类似的操作：选择某个关键行后定位空单元格，然后选择“开始”→“单元格”→“删除”→“删除工作表列”命令。
如果数据量不大，可以使用筛选方法获取没有空行的数据表，主要操作方法是：先建立筛选，然后在某列筛选掉空值，最后选择筛选出的没有空行的数据，复制粘贴到一个新工作表中。这种方法操作起来较为烦琐，不如前面介绍的定位删除方法简单。
在数据量很大的情况下，如果要批量删除所有空行，上述方法就很慢了。此时，最高效的方法是使用Power Query。
以案例2-7的数据表为例，利用Power Query快速删除空行的方法和技巧如下。
首先选择“数据”→“获取和转换”→“从表格”命令，打开“创建表”对话框，此时需要手工选择数据区域，并勾选“表包含标题”复选框，如图2-69所示。
图2-69 创建表并手工选择数据区域
打开Power Query编辑器，然后在某列筛选“(null)”，如图2-70所示。
图2-70 在某列筛选“(null)”
这样就得到了筛除所有空行的表格，如图2-71所示。
图2-71 筛除所有空行的表格
最后将数据导入Excel工作表中，得到一个没有空行的数据表。
很多人喜欢在基础表单中每个大项的底部插入多行或多列的小计，这样的小计其实是没有必要的。
需要强调的是，基础表单保存的是最原始的颗粒化数据，尽量不要增加计算行或计算列，尤其小计行和小计列更是多此一举，因为在制作分析报表时小计行和小计列是可以计算出来的。例如，制作的数据透视表的每个大项下就自动会有小计行。
删除小计行的方法有很多种，如筛选删除法、定位删除法等，其中定位删除法的操作最安全，不建议使用筛选删除法，因为筛选可能会对表格结构造成破坏。
如图2-72所示的表格，每个部门下都有一个多余的合计行，应该删除，具体的操作步骤如下。
图2-72 存在合计行的表格
步骤1 选择合计行所在的列（这里是B列）。
步骤2 按Ctrl+F组合键，打开“查找和替换”对话框，在“查找内容”文本框中输入“合计”，如图2-73所示。
图2-73 在“查找内容”文本框中输入“合计”
步骤3 单击“查找全部”按钮，即可查找出所有的“合计”单元格，如图2-74所示。
图2-74 找出所有的“合计”单元格
步骤4 在“查找和替换”对话框中按Ctrl+A组合键，即可全选查找出来的内容，如图2-75所示。
图2-75 选择所有的“合计”单元格
步骤5 关闭对话框，然后选择“开始”→“单元格”→“删除”→“删除工作表行”命令，如图2-76所示，即可将工作表中所有的合计行删除，如图2-77所示。
图2-76 “删除工作表行”命令
图2-77 删除合计行后的表格
可以使用筛选方法获取没有小计行的数据表，操作也很简单，先建立筛选，然后在有小计的列中筛选掉小计，最后选择筛选出来的没有小计的数据，复制粘贴到一个新工作表中。
如果数据量大，可以通过Power Query快速筛选掉小计行，操作方法在前面相关案例中已经介绍过。
日期是一类特殊的数据，也是非常容易出错的数据。例如，把日期输入成“2021.7.20”“20210720”的格式，而这种输入格式完全违背了Excel对日期的处理规则。
从本质上来说，日期是从1开始的正整数序号。例如，1表示1900年1月1日；2表示1900年1月2日；2021年7月20日对应数字44397。
Excel处理的最小日期是1900-1-1，最大日期是9999-12-31。
一般情况下，修改错误日期最高效的方法是使用分列工具。在某些特殊情况下，需要使用函数来解决。
使用分列工具是最简单、最快速的方法，可以瞬间修改某列中所有的非法日期，只保留正确的日期。
图2-78是从管理系统导出的发货单，A列的日期是文本型日期，并不是合法日期，现在需要将其转换为真正的日期（数值型日期），详细的操作步骤如下。
图2-78 A列中的文本型日期
步骤1 选中A列，选择“数据”→“数据工具”→“分列”命令，打开“文本分列向导”对话框，第1步和第2步保持默认，在第3步中选中“日期”单选按钮，如图2-79所示。
图2-79 选中“日期”单选按钮
步骤2 单击“完成”按钮，就得到了正确的日期，如图2-80所示。
图2-80 文本分列得到正确的日期
尽管使用分列工具修改非法日期是非常简便的，但需要注意：在分列向导的第3步中要选择设置正确的日期组合格式。
如图2-81所示的非法日期，除J列外，其他各列的非法日期都可以按照前面介绍的方法快速修改。
图2-81 非法日期示例
修改J列的日期需要在文本分列向导的第3步中进行设置，在“日期”单选按钮后的下拉列表中按照单元格日期组合的格式（年、月、日三个数字的组合顺序）选择相应选项，这里在下拉列表中选择MDY，因为单元格的日期数字组合顺序是“月－日－年”，如图2-82所示。
图2-82 选择日期组合MDY
此外，分列工具只能用在一列数据中，当有几列非法日期需要修改时，需要分别选择这些列，一列一列地应用分列工具进行转换。
在某些情况下无法使用分列工具修改非法日期，此时需要借助函数。具体使用哪些函数，需要根据具体情况进行选择。
如图2-83所示的示例数据表，将员工的进公司时间分成年、月、日三列数字保存，如果要获得一个完整的进公司时间，必须使用DATE函数将表示年、月、日的三个数字组合成正确的日期，公式如下：
=DATE(F3,G3,H3)
DATE函数很简单，就是把表示年、月、日的三个数字组合成日期。
下面的公式是错误的。
=F3&"-"&G3&"-"&H3
图2-83 使用DATE函数生成正确的日期
在制作数据自动化分析模板时，可能需要直接利用系统导出的原始数据进行计算，而在导出的原始数据中日期是非法日期时，就不能使用分列工具进行转换，此时也必须使用函数公式来处理。
如图2-84所示的示例数据表，左侧A～C列是从系统导出的原始数据，A列日期是非法的（210501表示2021年5月1日），现在要求利用这三列原始数据制作右侧的统计报表，这里月份名称是月份的英文简称，如Jan、Feb等。
图2-84 左侧是原始数据，右侧是要求的统计报表
既然要求直接使用原始数据制作统计报表，就只能使用函数来处理了。首先将A列的日期处理为正确的日期格式，然后将日期转换为月份的英文简称。G4单元格的计算公式如下：
=SUMPRODUCT((TEXT(TEXT($A$2:$A$15,"00-00-00"),"mmm")=$F4)*1,
($B$2:$B$15=G$3)*1,
$C$2:$C$15)
在这个公式中，表达式 TEXT($A$2:$A$15,"00-00-00")表示使用TEXT函数将非法日期转换为正确的日期格式（尽管这个日期是文本格式，但可以使用函数直接计算），计算结果如图2-85所示。
图2-85 使用公式直接转换日期
在表格中输入当天的日期可以直接按 Ctrl+;（分号）组合键。
如果要在某列输入一个以天为单位的日期序列，只需在第一个单元格中输入开始日期，然后往下拖动填充柄即可。
如果要在某列输入每月1日的日期，则在第一个单元格中输入如2021-1-1，然后往下拖动填充柄，再单击最后一个单元格右下角的“自动填充选项”按钮
，展开一个自动填充选项列表，从中选中“以月填充”单选按钮即可，如图2-86和图2-87所示。
图2-86 利用自动填充选项快速输入日期序列
图2-87 得到的每月1日的日期序列
日期是正整数，因此可以设置自定义格式，将表格中的日期显示为各种需要的样式，让表格更加容易阅读。
设置日期格式是在“设置单元格格式”对话框中进行的，除了在“分类”列表框中套用一些日期的固定格式外，还可以自定义日期格式，即选择“自定义”选项，然后在“类型”文本框中输入格式代码，如图2-88所示，就可以得到指定的格式。
图2-88 设置日期的自定义格式
在设置日期的自定义格式时，按表2-1中的日期格式代码任意组合，可以得到需要的效果。
表2-1 日期格式代码
例如，可以使用自定义日期格式的方法设计一个动态表头的考勤表，如图2-89所示，详细制作过程请观看本节视频。
图2-89 动态表头的考勤表