第三章    数据处理

1.    数据清理

1.1    小妙招

△    冻结窗格

△    自动筛选

△    快速隐藏：

        对应快捷键，隐藏行 “Ctrl+9”；隐藏列  “Ctrl+0”

△    “Ctrl+箭头键”：

         “Ctrl+箭头键” 可将光标移动到工作表中当前数据区域的边缘，数据区域不能存在空白单元格，否则Excel会将空白单元格默认为边缘。

         “Ctrl+Shift+箭头键” 可将单元格的选定范围扩展到活动单元格坐在的数据区域的边缘，同样，使用这种方式时数据区域不能存在空白单元格。

△    F4的妙用

①    相对引用

在C1单元格中输入 “=A1” （显示 “小白”），然后复制到D2，可以看到D2单元格的公式变为 “=B2” (显示 “小花” ），即公式的行和列都发生了相应的变化，如图3.1-1所示。

1.1-1 相对引用

②    绝对引用

将C1单元格内的公式改为 “=$A$1” 。 “A” 和 “1” 前面都加了一个 “$” ，结果C1中同样显示的是 “小白” 。将C1复制到D2，D2单元格的公式仍然是 “=$A$1” （仍然显示 “小白”），即引用单元格没有发生任何改变。 “$” 将行和列都锁定了，将C1复制到任何地方，显示的结果都不会改变。

1.1-2 绝对引用

③    混合引用

混合引用只有一个 “$” 符号，要么再列前面，要么再行前面，代表的意思是，具有绝对列和相对行，或者具有绝对行和相对列。例如将C1单元格的公式改为 “=A$1” ，代表着 “1” 被固定了，无论将公式复制到哪里，引用始终指着第一行。此时将公式复制到C2，C2的公式仍然为 “=A$1” 。

3.1-2 混合引用

△    F4的切换功能只对所选中的公式段起作用

1.2    重复数据处理

◇    用计算函数来识别重复数据

COUNTIF 函数

Ⅰ    选中B2单元格，输入函数公式：=COUNTIF(A:A,A2)

Ⅱ    选中C2单元格，输入函数公式：=COUNTIF(A$2:A2,A2)

Ⅲ    将公式复制到B3：C11的所有单元格，效果如下图所示。

1.2-1 利用COUNTIF函数识别重复值

B列中的结果代表的是每一个员工编号出现的次数，因此B列中大于1的单元格所对应的员工编号即重复的编号。

C列查找的是第二次重复值，以C9对应的 “A466074” 为例，结果 “3” 代表了从A1到A9，A466074是第三次重复出现。因此，筛选出C列中等于1的数即可找出数据中所有非重复项。

◇    用菜单操作来筛选重复数据

Ⅰ    选择包含数据的单元格区域

Ⅱ    在 “数据” 选项卡上的 “排序和筛选” 组中，单击 “高级” 按钮，弹出 “高级筛选” 对话框

Ⅲ    选择 “将筛选结果复制到其他位置” ，在 “复制到” 文本框输入B1区域，再勾选 “选择不重复的记录” ，单击 “确定” 。

1.2-2  通过菜单操作删除重复项

◇    用条件格式标识重复数据

选择 “开始” → “条件格式” →“突出显示单元格格式” → “重复值” ，即可将重复的数据或者所在单元格标为不同颜色，如下图所示。

1.2-3用条件格式标记重复值

若只需标识第二次重复的数据，步骤如下：

选择 “开始” → “条件格式” →“突出显示单元格格式” → “其他规则” → “使用公式确定要设置格式的单元格” ，在 “在符合此公式的值设置格式” 中输入 “=COUNTIF(A$2:A2,A2)>1” ，再单击 “格式” 按钮设置自己所需的格式即可，如下图。

1.2-4 用条件格式标识第二次重复的数据  

◇    用多重方法删除重复值

①通过菜单操作删除重复项

选中A1:A11数据区域 → “数据” → “删除重复项” 。

②通过排序删除重复项

借助之前COUNTIF辅助列。选中 “第二次重复项” 中任意一个有数据的单元格 → “开始” 主选项卡 → “编辑部分” 功能组 → “排序和筛选” → “降序” ，得到重新排序的数据，其中前三项是重复项（因为数值大于1）→ 删除前三项所在的行。

③通过筛选删除重复项

同样借助之前COUNTIF辅助列。选中 “第二次重复项” 中任意一个有数据的单元格 → “数据”主选项卡 → “排序和筛选” 功能区 → “筛选” ，单击下拉菜单，挑出不等于1的数值  →  “确定” 得到非重复项 → 删除筛选出来的行。

1.3    处理缺失数据

◇    定位输入

若缺失值都是以空白单元格形式出现在数据中，如何一步将所有空白单元格找出来？

采用定位功能。 “开始” 主选项卡 → “编辑” 功能区 → “定位条件” ，或直接使用 “Ctrl+G” 快捷键，会弹出 “”定位” 对话框 → “定位条件” → “空值” → “确定” 。

①    处理缺失数据的四种方法

☆    用一个样本统计量的值代替空值。如使用该变量的样本平均值，该方法较常见。

☆    用一个统计模型计算出来的值代替缺失值。常用模型有回归模型、判别模型等，得使用专业数据分析软件。

☆    将有缺失值的记录删除。可能导致样本量的减少。

☆    将有缺失值的个案保留，仅在相应的分析中做必要的排除。

②    “Ctrl+Enter” 快捷键

在不连续的区域中同时输入同一个数据或公式时，该快捷键很方便。

Ⅰ    选中一个单元格或区域，按住Ctrl键不放，再选第二个、第三个......单元格或区域，直到选中所有要输入数据的区域才松开Ctrl键，如下图。

1.3-1 “Ctrl+Enter” 快捷键—步骤1

Ⅱ    松开Ctrl后，输入要录入的数据，如在最后选中的单元格中输入 “小白” ，如下图。

1.3-2  “Ctrl+Enter” 快捷键—步骤2  

Ⅲ    此时不要着急按 Enter 键，而按 “Ctrl+Enter” 组合键，则所有选中的单元格都变成 “小白” ，如下图。

1.3-3  “Ctrl+Enter” 快捷键—步骤3 

“Ctrl+Enter” 组合键可以与定位查找搭配使用，用F5键或 “Ctrl+G" 定位方式定位到空白单元格之后，输入想要输入的值，再按 “Ctrl+Enter” ，即可让所有选中的单元格变成自己需要的样子。

◇    查找替换

①    常用的查找与替换方法

“开始” 选项卡 → “编辑” 组中 → “查找和选择”。对应快捷键，查找：Ctrl+F；替换：Ctrl+H。

②    用通配符模糊查找

如在一堆身份证号码中找出1987年出生的。身份证中第7至第10位是出生年份。选中所有身份证号码，按下 “Ctrl+F” ，在对话框的 “查找内容” 输入 “??????1987*” 即可。

这里每一个问号 “?” 代表任意单个字符，每一个星号 “*” 代表任意多个字符。在上面的例子中 “*” 可以省略。

1.3-4 模糊查找通配符

③    利用查找替换实现更多功能

在 “查找和替换” 对话框中，可以单击 “选项” 进一步定义搜索。具体如下图所示。

1.3-5 “查找和替换” 对话框中的选项设置

1.3-6 查找与替换功能列表

如要查找所有数据为 “0” 的单元格，在 “查找内容” 右栏里输入 “0” ，再勾选上 “单元格匹配” 复选框即可。若不勾选 “单元格匹配” ，则会将所有包含 “0” 的单元格都查找出来。

1.4    检查数据逻辑错误

◇    利用IF函数检查错误

“COUNTIF(B3:H3,"<>0”)” 代表 “对B3:H3区域中不等于0的单元格进行计数”

IF是判断语句，判断逻辑值是真还是假。“IF（COUNTIF(B3:H3,"<>0”)>3,“错误”，“正确”）”的意思是：如果选择的选项超过3个（COUNTIF(B3:H3,"<>0”)>3），则单元格显示 “错误” ；否则显示 “正确” 。

◇    利用条件格式标记错误

选中数据区域B3:H6 ，“开始” → “条件格式” → “突出显示单元格格式” → “其他规则” → “使用公式确定要设置格式的单元格” →，在 “为符合此公式的值设置格式” 文本框中输入 “=OR(B3=1,B3=0)=FALSE” ，最后单击 “格式” 将格式调整为红色加粗倾斜字体，完成设置。

OR函数代表的意思是：函数里面任意一个参数为真时，返回TRUE,否则返回FALSE。所以上述函数的意思是：“如果B3为1或者为0” 的命题是错误的，即B3即不为1也不为0，则会被条件格式突出标记出来。

◇    嵌套函数

不建议使用太多嵌套函数。可以把嵌套函数的每一步拆分到多个单元格中，再用一个最终的公式将所有单元格合并起来。二是利用Excel里的分步查看公式功能检验每一步的公式计算结果。

◇    分步查看公式计算值 （P88）

1.4-1 公式审核功能区

在 “公式求值” 弹出的对话框中单击 “求值” 以检查带下划线的引用值。按F9键也可查看公式的计算结果，检查完按Ese退出。若要用F9键计算的结果替换原公式选定的部分，可以按Enter键或 “Ctrl+Shift+Enter” 组合键返回普通公式或数组公式。

◇    在公式中查错

1.4-2 公式中出现的错误字符

2.    数据加工

2.1    数据抽取

◇    字段分列

①    菜单法

2.1-1 文本分列向导

②    函数法

LEFT和RIGHT函数

2.1-2 函数拆分字符示例

◇    字段合并

合并文本和数字有两种方式，利用CONCATENATE函数和 “&” 运算符，以下图为例。

2.1-3 字段合并

◇    字段匹配

VLOOKUP函数：在表格的首列查找指定的数据，并返回指定的数据所在行中指定列处的单元格内容。

2.1-4 VLOOKUP函数

注意，table_array第一列的值必须是要搜索的值（lookup_value）,否则就会出现错误标识符 “#/A”。

还有两种情况会出现错误标识符 “#/A”：

☆    数据存在空格，此时可以嵌套使用TRIM函数将空格去除。

☆    数据类型或格式不一致，需将类型或格式转为一致。

2.2    数据计算

◇    简单计算

就是字段通过加减乘除等简单算术运算就能计算出来。

◇    函数计算

①    计算平均值与总和

平均值函数AVERAGE()；总和函数SUM()。括号内为需要计算的参数，参数可以是数字、单元格引用、区域或者是定义的名称，参数与参数之间用逗号隔开。

②    利用MAX、MIN函数计算工时

求最大值MAX、最小值MIN，用法与平均值及求和函数相似。举例说明，如下图所示。

2.2-1 考勤表

上图即展示了如何用MAX、MIN函数计算工时。

③    在状态栏里查看计算结果

Ⅰ    选中需要求值的数据区域，这里我们选中D2:D11，即可看到状态栏右边有平均值、计数和求和结果，如下图所示。

2.2-2  通过状态栏里查看计算结果

Ⅱ    鼠标移到状态栏，单击鼠标右键，可在最大值和最小值前面打√，也就能看到所选数据区域中的最大值和最小值了。

④    日期的加减法

使用NOW或者TODAY函数快速输入当前日期。

2.2-3 输入当前日期和时间

公式输入的是动态的日期和时间，快捷键输入的是静态的日期和时间。

简单的加减符可在原日期的基础上增减天数，如A1单元格中输入 “2011-1-1“ ，B1单元格中输入 “=A1+5” ，B1中就会显示结果 “2011-1-6” ，如果要为日期添加 “年月日” 组合的时候，我们可以用DATE函数。

2.2-4 增减日期

DATE函数有三个参数，以此代表年、月、日，这三个参数是不可省略的。

⑤    利用函数DATEDIF计算工龄

DATEDIF（start_date,end_date,unit）：返回两个日期之间的年/月/日间隔数。以下图为例。

2.2-5 用函数DATEDIF计算工龄

这里VLOOKUP函数的应用与 “数据提取” 中有所不同，省略了最后一个参数range_lookup（逻辑值），即在此处默认的是近似匹配。所以，单元格B2的公式不是在D列中寻找0，而是寻找最接近A2，又不能大于A2的值，找到了D2是正好等于0的，所以返回D2的分组 “0-5元” 。

2.3    数据转换

◇    数据表的行列互换

①    选择性粘贴

选中需复制的区域复制后，快捷键 “Ctrl+Alt+V” ，弹出如下图所示对话框。

2.3-1 选择性粘贴

下图为常用的选择性粘贴功能：

2.3-2  选择性粘贴常用功能列表

在 “运算区域” 里边还有加、减、乘、除四个选项。例如，想在部分单元格前面加上负号，可以任取另一单元格，输入 “-1” ，再选择性粘贴 “乘” 到相变号的数字区域，则该区域的值全变成了相反数。

②    不用复制的粘贴

在一个单元格输入 “小白” ，选中，把光标移到选择范围的边缘，当光标变成四向箭头时，按住鼠标右键拖至其他空白单元格，松开右键，选择 “复制单元格” 选项即可。

◇    多选题录入数据方式之间的转换

将多重分类法录入的数据转变成二分法录入的数据。

2.3-3 将多重分类法录入的数据转变成二分法录入的数据

HLOOKUP函数区别于VLOOKUP函数在于，VLOOKUP是按列查找，HLOOKUP是按行查找，“H” 代表 “行”。

HLOOKUP：在表格的首行查找指定的数据，并返回指定的数据所在列中的指定行处的单元格内容。

2.3-4 另一种将多重分类录入法转换成二分法录入法  

SEARCH("1",B4）意思是在B4单元格中的字符串中查找1。

2.3-5 公式秘钥—SEARCH函数

3.    数据抽样

常用的数据调查方式：普查和抽样调查。

在数据抽样中，用到RAND函数：返回【0,1】的均匀分布随机数，而且每次计算工作表时都将返回一个新的数值。

如果在编辑栏中输入 “=Rand（）” 后，保持编辑状态，按<F9> ,则生成的随机数将永远保存，不再返回新的数值。

若要产生60~70之间的随机数，公式可以写成 “=RAND()*10+60” ，要取整数的话可以用公式 “=INT(RAND()*10+60)”。

a、b分别代表两个数字，其中a<b，若要生成a与b之间的随机实数，可以用公式 “=RAND()*（b-a）+a”。

本章小结

本章涉及的函数