第1部分 数据采集与整理

1.1 数据录入与基本设置

1.1.1 使用特殊符号自定义单元格格式

在Excel单元格中，常使用特殊符号自定义单元格格式。

1．“0”数字占位符

当使用“0”数字占位符自定义单元格格式时，如果单元格中的数字位数大于指定占位符的位数，则如实显示该数字；如果该数字位数小于占位符的位数，则用0补足该数字位数为占位符的位数。

如图1-1所示，“0000”代表自定义单元格格式为4位整数，其中A1单元格中数字123456位数大于4位，则显示为“123456”；A2单元格中数字123位数只有3位，则要将该数字用0补足为4位，即显示为“0123”。

如图1-2所示，“00.00”代表自定义单元格格式为小数部分保留两位。其中，A1单元格中数字为1.56，显示为“01.56”；A2单元格中数字为123.57，显示为“123.57”；A3单元格中数字为1.5，显示为“01.50”。

2．“＃”数字占位符

当使用“＃”数字占位符自定义单元格格式时，只显示数字有意义的零，不显示数字无意义的零。在小数点后，数字位数如果大于“＃”的位数，则按“＃”的位数四舍五入。

如图1-3所示，自定义单元格格式为“＃.＃＃”。其中，A1单元格中数字123.456显示为“123.46”；A2单元格中数字123显示为“123.”；A3单元格中数字121.1显示为“121.1”。

3．“？”数字占位符

当使用“？”数字占位符自定义单元格格式时，在小数点两边为无意义的零添加空格，以实现数字按小数点对齐。

如图1-4所示，自定义单元格格式为“？？？.？？？？”，其中，A1单元格中数字123.456显示为“123.456”，A2单元格中数字123显示为“123.”，A3单元格中数字121.1显示为“121.1”，且以小数点对齐。

4．“，”千分位分隔符

当使用“，”千分位分隔符自定义单元格格式时，在数字中，每隔3位数加进一个逗号，也就是千位分隔符，以免数字位数太多不好读取。可自定义添加千分位分隔符，如图1-5所示。

5．“＠”文本占位符

单个＠的作用是引用固定文本。

如图1-6所示，如要在输入数据之前自动添加文本“韩老师讲Office”，可以自定义单元格格式为“"韩老师讲Office"＠”，则在单元格中，输入“666”后，会在“666”前自动添加文本“韩老师讲Office”；如图1-7所示，如要在输入数据之后自动添加文本，可以自定义单元格格式为“＠"韩老师讲Office"”，则在单元格中，输入“666”后，会在“666”后自动添加文本“韩老师讲Office”。

＠文本占位符的位置决定了输入数据相对于添加文本的位置。如果使用多个＠文本占位符，则可以重复文本。如图1-8所示，自定义单元格格式为“＠"韩老师讲Office"＠”，则在单元格中，输入666后，会在文本“韩老师讲Office”前后重复显示“666”。

6．“！”原样显示后面的符号

在单元格格式中，“、”“＃”“？”等都是有特殊意义的字符。如果想在单元格中显示这些字符，则要在符号前加“！”。如图1-9所示，如果想在单元格中显示“666"＃”，可以自定义单元格格式为“＃！"！＃”（第一个“＃”为数字占位符）。

7．“＊”重复后面的字符

当使用“*”重复后面的字符时，可以重复显示“*”后面的字符，直到充满整个单元格。

如图1-10所示，在单元格中，如要在“666”后重复显示“-”，直至充满单元格，可自定义单元格格式为“＃*-”（第一个“＃”为数字占位符）。

8．颜色显示符

当自定义单元格格式为“[红色]；[蓝色]；[黑色]；[绿色]”时显示结果如图1-11所示，正数为红色，负数显示为蓝色，零显示为黑色，文本则显示为绿色。

有8种颜色可选：红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

1.1.2 设置数字以小数点对齐

【问题】

如图1-12所示，样表数据列设置为居中显示，不能一眼看出数字的大小。

如图1-13所示，设置数字以小数点对齐后，可以很明了地看出数字的大小。

【实现方法】

（1）设置数据右对齐，如图1-14所示。

（2）设置单元格格式，自定义单元格格式为“＃.0？？？？？”，即可得数字以小数点对齐的效果，如图1-15所示。

【格式解析】

＃：保留原有整数位数。

0：1位数。

？：数字占位符，1个“？”占1位，5个“？”占5位。

本示例数字中，最多的小数位数是6位，如果是整数，保留1位小数，所以写成“＃.0？？？？？”；即使没有6位小数，也要占6位小数的位置。

1.1.3 单元格数据换行

【问题】

当填写表格时，单元格中的文字要换行，但只按Enter键是不能实现的，此时可以用以下两种方法轻松实现。

【实现方法】

1）自动换行

单击“开始”→“自动换行”，如图1-16所示。

只要选择此功能，就能实现单元格中的内容自动换行功能，并且根据单元格中的内容自动调整行高。但是，这种方法有一个局限，即换行后的单元格中的内容不能变成真正的段落，只是适应了单元格的大小。

如果想要实现真正的“另起一段”式的换行，就要按Alt+Enter组合键。

2）Alt+Enter组合键

将光标放在想要换行的地方，按Alt+Enter组合键即可实现换行，如图1-17所示。

取消换行的方法有以下两种：

（1）对于利用“自动换行”按钮实现的换行，只要直接取消“自动换行”就可以了。

（2）对于按Alt+Enter组合键（手动换行）实现的换行，如果要取消，可采取两种方式：如果换行较少，可以直接删除手动换行；如果换行较多，可以打开“查找和替换”对话框，在“查找内容”中输入“Alt+10”或“Ctrl+J”，在“替换为”中什么也不用输入，直接替换即可，如图1-18所示。

1.1.4 设置不能隔行或隔列填写数据

【问题】

某仓库管理员经常发表格给各个分仓库管理员，让其填写商品数据。可管理员们填写的商品数据表格很不规范。在表格中，时常会多出很多空白单元格，或者是少填写某项数据，这给后续数据统计工作带来很多麻烦。能不能限定填写数据时不能隔行或隔列填写呢？

可以用数据验证（数据有效性）来解决这个问题。

【实现方法】

（1）单击“数据验证”项。

选中要填写的区域，单击“数据”菜单中的“数据验证”项，如图1-19所示。

（2）设置验证条件。

在打开的“数据验证”对话框的“设置”选项卡中，将“验证条件”的“允许”设为“自定义”，“公式”设为“=COUNTBLANK（$A$2：A2）=0”，如图1-20所示。

（3）设置出错警告。

选中“数据验证”对话框的“出错警告”选项卡，勾选“输入无效数据时显示出错警告”项，将“样式”设为“停止”，“错误信息”设为“不能隔行或隔列填写”，如图1-21所示。

通过以上步骤，即可实现不能隔行或隔列填写。

【公式解析】

•$A$2：A2：一个变化的区域，起始位置为A2，结束位置是当前输入单元格。

•COUNTBLANK（$A$2：A2）：用于统计动态区域内空白单元格的数量。

•COUNTBLANK（$A$2：A2）=0：表示如果区域内空白单元格数量为0，则允许输入。

例如，要在输入C4单元格数据，如果A2：C4区域内空白单元格数量为0，则允许输入C4单元格数据。

1.1.5 设置倾斜的列标签

【问题】

经常会遇到Excel表格列标签内容较多，造成列很宽的情况。如图1-22所示，以日期为列标签时就会出现这样的情况：本来没几列数据，可整个数据表却很宽。

但做成倾斜的列标签，情况就好多了，如图1-23所示。

【实现方法】

单击“开始”菜单的“对齐方式”功能区中的“方向”按钮，就可以沿对角或垂直方向旋转文字，这是标记窄列的好方式，如图1-24所示。

设置了倾斜的列标签后，怎么再返回到列标签普通的横向显示状态呢？只要单击“方向”按钮，在下拉列表中选择“设置单元格对齐方式”命令，如图1-25所示。

在打开的“设置单元格格式”对话框中，将文字“方向”设为“0”度即可，如图1-26所示。

1.1.6 给单元格数据加滚动条显示

【问题】

经常要在Excel单元格中输入简介性质的文字，而又想保持表格的美观，不想把单元格拉得太大，能不能在单元格中加个滚动条来拖动显示呢？如图1-27所示，将所有文字都放在A2单元格中，通过拖动右侧滚动条来显示。

【实现方法】

（1）插入文本框控件。单击“开发工具”→“插入”→“文本框（Activex控件）”，如图1-28所示。

（2）设置文本框控件属性。右击文本框控件，在弹出的快捷菜单中选择“属性”命令。其中，更改“MultiLine”属性为“True”，即可实现自动换行；更改“ScrollBars”属性为“2-fmScrollBarsVertical”，即可实现纵向滚动条，如图1-29所示。

（3）关闭设计模式，输入文字。在“开发工具”菜单中，关闭“设计模式”，然后在文本框中输入文字，当文字不能完全显示在当前文本框内时，即可自动出现滚动条，实现滚动显示，如图1-30所示。

如果想修改文本框控件的大小等属性，可选中“设计模式”进行修改。

注：如果“菜单”中没有“开发工具”，则要单击“文件”→“选项”，在打开的“Excel选项”对话框中选择“自定义功能区”命令，在其右侧的“主选项卡”中，勾选“开发工具”项，如图1-31所示。

1.1.7 冻结窗格，轻松查看行、列数据

【问题】

如果工作表中的数据量比较大，进行数据处理时，在鼠标滚轮被滚上滚下或滚动条被拖来拖去后，就不知道某个数据对应哪一项了，还要返回工作表顶部或左侧去查看，这样上下左右地查来看去，处理数据的效率就会很低。

冻结窗格可以永远看到数据的对应项。

【实现方法】

1）冻结行

冻结行的结果：不管在工作表中怎样向下翻看数据，被冻结的行永远在数据的上方。

冻结行的关键步骤：将光标放在要被冻结行的下一行的第一个单元格，单击“视图”→“冻结窗格”→“冻结窗格”，如图1-32所示。

当然，如果冻结的是第一行，也可以直接选择“视图”→“冻结窗格”→“冻结首行”。

2）冻结列

冻结列的结果：不管在工作表中怎样向右翻看数据，被冻结的列永远在数据的左侧。当然，如果冻结的是第一列，也可以直接选择“视图”→“冻结窗格”→“冻结首列”。

3）冻结行和列

想知道数据对应的行、列的意义，最好是同时冻结行和列。

冻结行和列的关键步骤：将光标放在要被冻结行下、列右的第一个单元格，再单击“视图”→“冻结窗格”→“冻结窗格”，如图1-33所示。

灵活使用冻结窗格，可以大大提高工作效率。

1.1.8 轻松绘制单斜线、双斜线表头

【问题】

在稍复杂的Excel数据表中，须要知道数据区第一行、第一列、数值区各属于什么类别，因此须要绘制斜线表头，如图1-34和图1-35所示。

【实现方法】

1）单斜线表头

在“月份”“部门”之间按Alt+Enter组合键，将它们分为两行。在“月份”“销量”前加空格以调整它们的位置。

在绘制表头的单元格上右击，在弹出的快捷菜单中选择“边框”→“其他边框”，也可以直接打开“设置单元格格式”对话框来设置斜线边框，如图1-36所示。

2）双斜线表头

（1）在“月份”“销量”“部门”之间按Alt+Enter组合键，将它们分成3行。在“月份”“销量”前加空格以调整它们的位置。

（2）在插入“直线”形状时，要按住Alt键以绘制出与边框等长的直线形状，再调整直线形状的高度与宽度，如图1-37所示。

1.1.9 如何让数字以“万”为计数单位来显示

【问题】

按国际通用方法，Excel的数字计数方式为“千分位分隔符”，但这种计数方式不太适合中国人用“万”来计数的习惯。

【实现方法】

1）设置单元格格式

选择单元格或区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在打开的“设置单元格格式”对话框的“数字”选项卡中选择“自定义”命令，并在“类型”中输入“0！.0，”，单击“确定”按钮，数字即可按“万”位显示，如图1-38所示，其设置结果如图1-39所示。

由图1-39可见，数字只保留了1位小数。如果想使数字后面带有“万”字，可以在“类型”中输入“0！.0，万”后，单击“确定”按钮，如图1-40所示，其设置结果如图1-41所示。

如果想保留4位小数，则在“类型”中输入“0！.0000”后，单击“确定”按钮，如图1-42所示，其设置结果如图1-43所示。

由于“万”位与国际上通用的“千位分隔符”相差一位，所以采用这种自定义方法生成显示“万”位的数字，必须将其保留1位或4位小数，不可将其只保留到整数位。

2）选择性粘贴除以10000

任意添加一个辅助单元格，并输入10000，然后复制，再选中要变“万”位的单元格并右击，在弹出的快捷菜单中选择“选择性粘贴”命令，如图1-44所示。在弹出的“选择性粘贴”对话框中，在“运算”中选择“除”，如图1-45所示，就可得到以“万”计数的数值，这样得出的数值，对其保留的小数位数可以不加限制，而且用过的辅助单元格是可以删除的。

1.1.10 设置仅能修改部分单元格数据

【问题】

小夏是某公司业务主管的助理，经常会将Excel表格发送给各个部门人员去填写。填写好后的Excel表格再上交给她，由她来汇总。然而，小夏常会遇到Excel表格的固有内容被修改得面目全非的情况。

如果Excel表格中只有部分单元格能被修改，就能解决这个问题。

如图1-46所示，除了工作表中E3：G23单元格数据是能被修改的，其他单元格区域是不能被修改的。

【实现方法】

（1）解除“锁定”。

选定可以修改的E3：G23区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，如图1-47所示。

在打开的“设置单元格格式”对话框的“保护”选项卡中，去掉“锁定”前面的钩，如图1-48所示。

（2）设置保护。

单击“审阅”→“保护工作表”，在打开的“保护工作表”对话框中勾选“选定未锁定的单元格”项，如图1-49所示。

注：如果同时勾选了“选定锁定单元格”项，那锁定的单元格可以被选中，但不能被修改；如果不勾选该项，那么锁定单元格既不能被选中，更不能被修改。

在弹出的“确认密码”对话框中，两次输入密码，单击“确定”按钮后即可，如图1-50所示。

在E3：G23区域可以输入数据，但在E3：G23区域以外的其他区域，更改或输入内容时，就会出现如图1-51所示的提示。

经过这样的设置后，Excel表格仅有部分单元格数据可以被修改，而且不需要密码就能打开其工作簿。

如果撤销以上操作，让所有的单元格都能被修改，可以选择“审阅”→“撤销工作表保护”，然后在打开的对话框中输入密码就可以了，如图1-52所示。

1.1.11 隐藏工作表

【问题】

在很多时候，为了保护数据不被修改，须要将工作表隐藏起来。此时，可以利用VBA来改变工作表属性的隐藏方式。

【实现方法】

（1）打开VBA对话框。

按Alt+F11组合键，打开VBA对话框。

（2）设置要隐藏的工作表属性。

在“工程”对话框中，选择要隐藏的工作表。如要隐藏Sheet1，选择该表，再修改“属性”对话框中的“Visible”属性，如图1-53所示。

“Visible”属性有以下3种。

•-1-xlSheetVisible：工作表完全可见。

•0-xlSheetHidden：工作表隐藏。

•2-xlSheetVeryHidden：工作表彻底隐藏。

如果想找到隐藏的工作表，须重新设置其Visible属性为“-1-xlSheetVisible”。