1.4 行列设置

1.4.1 快速删除空白行

【问题】

在用Excel处理数据时，由于添加、删除、剪切、复制等操作，经常会造成数据区出现空行的现象，这时要删除空行。

【实现方法】

（1）纯空白行，无其他空白单元格。

当数据区除了有整行空白，没有其他空白单元格时，采用快速删除空白行的方法为：按Gtrl+G组合键，打开“定位”对话框。单击“定位条件”按钮，打开“定位条件”对话框，“选择”栏下选择“空值”项，单击“确定”按钮后，右击，在弹出的快捷菜单中选择“删除”→“整行”，如图1-133所示。

（2）既有空白行，又有空白单元格。

如果数据区域除了空白行，还有空白单元格，采用正确的方法是：

建立辅助列；在H2单元格中输入公式“=COUNTA（A2：G2），按Enter键执行计算，然后将公式向下填充，如图1-134所示；筛选出辅助列中为0的行，并选中；按Alt+；组合键，显示可见行，右击，在弹出的快捷菜单中选择“删除行”，如图1-135所示。Excel 2010以上版本，可以直接将选中筛选出的行删除。

1.4.2 插入行或删除行后，都可自动填写序号

【问题】

在数据处理过程中，插入行、删除行后，原有序号就会变得不连续了：新插入的行，序号是空的；删除行，序号则会间断了。

遇到这种情况，数据少时，可以手工修正，但数据如果有几千行，手工修正的效率就太低了。

【实现方法】

1）ROW函数

用公式“=ROW（）-1”代替原有序号，如图1-136所示。

因为序号是从第二行开始填写的，所以，序号=本行行号减1。当插入新行后，原有行则被向下“推”，序号也自动改变。但是，新插入的行，序号并不会自动出现，须要采用公式来填充。

2）ROW函数+表格

将序号用公式“=ROW（）-1代替”，再将原数据通过单击“插入”→“表格”转换为表格，即可实现序号自动出现，如图1-137所示。

但是，通过上述两种方法添加的序号，筛选以后，序号却不能从1开始，这将影响筛选结果的个数与后期的分类打印。

3）SUBTOTAL函数

在A2单元格中输入公式“=SUBTOTAL（3，$B$2：B2）”，按Enter键执行计算，再将公式向下填充。

这样得到的序号，无论怎么筛选，序号都是连续的，如图1-138所示。

1.4.3 数据转置与跳过单元格复制

【问题】

如图1-139所示，能不能直接将表格1的数据转变成表格3的数据呢？能不能一次将表格2的所有红色斜体数字复制到表格1和表格3中呢？

【实现方法】

（1）选择性粘贴，数据转置的使用。

选择表格1全部数据，右击，在弹出的快捷菜单中选择“复制”命令，然后选中A17单元格，右击，在弹出的快捷菜单中选择“选择性粘贴”命令，在打开的“选择性粘贴”对话框中勾选“转置”项，单击“确定”按钮，即可得到表格3，如图1-140～图1-143所示。

（2）跳过单元格复制。

在表格2中，先选中N列，再按住Ctrl键选中O列、P列，右击，在弹出的快捷菜单中选择“插入”命令，即可在原来O列、P列前插入空列，如图1-144和图1-145所示。

选择M3：S12数据区域，右击，在弹出的快捷菜单中选择“复制”命令，如图1-146所示。

选中表格1的B3单元格，右击，在弹出的快捷菜单中选择“选择性粘贴”命令，在打开的“选择性粘贴”对话框中勾选“跳过空单元”项，单击“确定”按钮，即可将表格2的所有红色斜体数字复制到表格1中，如图1-147～图1-149所示。

选中表格3的C17单元格，右击，在弹出的快捷菜单中选择“选择性粘贴”命令，在打开的“选择性粘贴”对话框中勾选“跳过空单元”“转置”两项，单击“确定”按钮，即可将表格2的所有红色斜体数字复制到表格3中，如图1-150和图1-151所示。

1.4.4 最快捷的一列转多列方式

【问题】

数据如图1-152所示，如何将左侧A列数据快速转为右侧多行多列呢？

【实现方法】

（1）将数据复制到C列。

（2）在D1单元格中输入公式“=C6”，按Enter键执行计算。因为从C6开始名字将另起一列显示，如图1-153所示。

（3）公式向下填充，再向右填充，在C1：G5区域会囊括原来一整列分布的数据。

（4）选中D1：G5区域，复制，再进行选择性粘贴，用数值覆盖原有的公式。

（5）删除C1：G5区域以外的数据。

一整列数据即可呈现为多列分布，如图1-154所示。

1.4.5 聚光灯效果（阅读模式）改变当前行和列的颜色

【问题】

阅读模式，即通过鼠标单击到哪个单元格，该单元格对应的行和列都同时变成一种颜色。这种阅读模式，又叫聚光灯效果，能快速准确定位和修改相应数据，如图1-155所示。

【实现方法】

（1）条件格式设计颜色。

选中数据区“开始”→“条件格式”→“新建规则”，弹出“新建规则”对话框。在打开的“编辑格式规则”对话框中选择“使用公式确定要设置格式的单元格”，然后输入公式“=（CELL（"row"）=ROW（））+（CELL（"col"）=COLUMN（））”，该公式的含义是，当前单元格的行号或列号等于活动单元格的行号列号时，执行条件格式，如图1-156所示。

虽然CELL（）是易失性函数，但在使用时，颜色并不能随活动单元格的变化而自动的随之移动，还要进一步设置。

（2）颜色随单元格改变而移动。

•第1种方式，手动按F9键。先选择单元格，然后按F9键，行和列的颜色就移动到当前单元格了。但这种方式不是自动方式。

•第2种方式，使用VBA代码。只要一小段VBA代码就能实现完全自动颜色的移动。按Alt+F11组合键，打开VAB，执行如图1-157所示操作。

图1-157所示的这段代码的意思是，当活动单元格改变时就执行一次计算。

（3）工作簿的保存。

添加了VBA代码的工作簿一定要保存成“启用宏的工作簿”，下次打开后，要选择“启用宏”命令，才能正常使用。

1.4.6 将同部门员工姓名合并到同一单元格

【问题】

如图1-158所示，左侧每个员工姓名对应一个数据行，如何快速变成右侧每个部门对应一个数据行？

【实现方法】

（1）先按照部门进行排序。

（2）在D2单元格中输入公式“=IF（A2=A3，B2&"，"&D3，B2）”，按Enter键执行计算，再将公式向下填充，得到的结果如图1-159所示。

（3）将结果复制，并进行选择性粘贴，只保留数值，放到C列相应的位置，并删除D列。

（4）在D2单元格中输入公式“=COUNTIF（$A$2：A2，A2）”，再将公式向下填充，得到的结果是每个部门中的第一行编号都是1，即姓名最全的一行，如图1-160所示。

（5）筛选出“编号”不是1的各行，然后删除，即得结果，如图1-161所示。

1.4.7 将同一单元格的同部门员工姓名分行显示

【问题】

如图1-162所示，怎样把写在同一单元格中的员工姓名进行分行显示呢？

【实现方法】

（1）打开查询编辑器。

将光标放在数据区的任意位置，选择“数据”→“自表格/区域”，弹出“创建表”对话框，如图1-163所示。

选择A1：B5区域后，单击“确定”按钮，打开查询编辑器，如图1-164所示。

（2）姓名的分列显示。单击“各部门所有人员”列标签，选择“转换”→“拆分列”→“按分隔符”，如图1-165所示。

原数据区姓名之间是由“，”隔开的，所以“选择或输入分隔符”内，选择“逗号”命令，单击“确定”按钮，如图1-166所示。实现了姓名的分列显示，如图1-167所示。

（3）逆透视列。按住Ctrl键，选中所有的姓名列，然后选择“转换”→“逆透视列”，如图1-168所示。

各部门所有姓名同列显示，然后删除属性列，如图1-169和图1-170所示。

（4）关闭并上载。选择“开始”→“关闭并上载”，姓名出现在同列，并以“表格”的形式显示，如图1-171和图1-172所示。

将光标放在表格内，选择“设计”→“转换为区域”，可变为普通工作表数据，即得样表所示的结果，如图1-173所示。