excel FAQ

MS Office 常见问题解答（Excel部分）
CICC GAD
2008年9月
目录
1. 自定义起始页码 (1)
2. 设置打印区域 (1)
3. 设置打印标题行/列的方法 (2)
4. 设置页眉/页脚 (2)
5. 只复制可见单元格的内容 (4)
6. 整理下载的数据并且使之可以进行运算 (4)
7. 显示Excel堆积图中的柱子上方的Total值 (5)
8. 调整复合饼图的内容与大小 (6)
9. 数据转置（行列数据互换） (7)
10. 三维图的实现 (8)
11. 统计函数 (9)
12. 查找和引用函数 (10)
13. 文本函数 (10)
14. 逻辑运算函数 (11)
15. 日期和时间函数 (11)
16. 文件加密与解密 (12)
17. 工作簿保护 (13)
18. 单元格保护 (13)
19. 使工作表仅显示若干列 (14)
20. 窗口冻结 (15)
21. 反转表格中的列（如A->Z列顺序反转） (16)
22. 自动重算与手动重算 (16)
23. 分列Excel中英文混杂的内容 (17)
24. 快速整列填充数据 (19)
25. 快速删除重复记录的行 (19)
26. 建立连接到不相邻的若干块区域的超链接 (20)
27. 文件打开后无内容且为灰色背景 (21)
28. 设置单元格下拉菜单 (21)
29. 单元格绿色角标释义 (22)
1. 自定义起始页码
步骤：文件->页面设置->页面->起始页码->设置页码（如图1-1）
图1-1
2. 设置打印区域
步骤：
（1）选择需要打印的区域
（2）文件->打印区域->设定打印区域
（3）视图->分页预览
（4）分页（设须从11行处分页，则选中A12单元格）：插入->分页符；之后可通过拖动分页符自定义分页（如图1-2）
图1-2
（返回目录）
3. 设置打印标题行/列的方法
步骤：
（1）文件->页面设置->工作表->打印标题
（2）选择”顶端标题行”或”左端标题列”->在工作表中选择要作为标题的行或列->确定（如图1-3）
图1-3
4. 设置页眉/页脚
步骤：
（1）文件>页面设置->页眉/页脚->自定义页眉/页脚
（2）在弹出的”页眉/页脚”对话框中，选择需要插入的内容（如图1-4）
（3）设置完毕->确定->打印预览（如图1-5）
（返回目录）
图1-4
图1-5
注：
设置文本格式
插入页码；插入总页数
插入日期；插入时间
插入文件路径；插入文件名；插入标签名
插入图片；设置图片格式
（返回目录）
5. 只复制可见单元格的内容
步骤：选择需要复制的单元格->Alt+；->复制->粘贴
6. 整理下载的数据并且使之可以进行运算
步骤：
（1）数据->分列->固定宽度->下一步（如图1-6）
（2）如图1-7所示，单击空白处会出现向上的黑色箭头，将其拖动到数据的末尾处，就可消除多余的空格，使数据可以进行运算
（3）如果所有数据的位数并不完全相同，可以选中位数相同的一类数据进行上述操作
（4）也可使用任意分隔符号将数据或文本分为多个字段
图1-6
（返回目录）
图1-7
7. 显示Excel堆积图中的柱子上方的Total值
步骤：
（1）首先计算出多个系列的Total值，并将其画入堆积图
（2）双击图表区：数据系列格式->数据标志->勾选”值”
（3）双击最顶端任意Total值：数据标志格式->对齐->标签位置->轴内侧（如图1-8）
（4）将Total值得颜色和边框都设为透明：双击任意值->对齐->图案->边框->无
图案->区域->无
（返回目录）
图1-8
8. 调整复合饼图的内容与大小
步骤：
（1）若需要增减主/子图表的数据，只需将其拖动至子/主图表中（如图1-9）
（2）选中要更改的复合图->右键->数据系列格式（如图1-10）
（3）选择”选项”，在”第二绘图区”里进行大小调整即可（如图1-11）
图1-9
（返回目录）
1-10
图
9. 数据转置（行列数据互换）
步骤：选择单元格->复制->选择性粘贴->转置（如图1-12）
（返回目录）
图1-12
注：选择性粘贴的快捷操作：Alt+E+S
10. 三维图的实现
步骤：
（1）插入->图表->三维柱型图
（2）更改坐标轴次序，坐标轴格式->刻度->分类次序反转（如图1-13（1））
（3）效果如图1-13（2）
（4）调整视图格式：在背景墙格式下点击右键->设置三维视图格式（如图1-14）
图1-13（1）（返回目录）
图1-13（2）
图1-14
11. 统计函数
（1）Countif——计算区域中满足给定条件的单元格的个数
语法形式：Countif（range,criteria）
range为需要计算其中满足条件的单元格数目的单元格区域
criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例：求一列数中借贷方向为“借”的科目数量，则公式为=COUNTIF(rang,"借")
（2）Rank——返回一个数值在一组数值中的排位
语法形式：Rank（number,ref,order）
Number是需要计算其排位的一个数字
Ref是包含一组数字的数组或引用（其中的非数值型参数将被忽略）
Order为一数字，指明排位的方式。

如果order为0 或省略，则按降序排列的数据清单进行排位。

如果order 不为零，ref当作按升序排列的数据清单进行排位（返回目录）
（3）Sumif——对满足某一条件的单元格区域求和
语法形式：sumif（range,criteria,sum_range）
range：用于条件判断的单元格区域
criteria：确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本
sum_range：需要求和的实际单元格
例：求借贷表中所有方向为“贷”的科目的余额数量，则公式为=SUMIF(rang,"贷",sum_rang)
12. 查找和引用函数
（1）Vlookup—在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值
语法形式：Vlookup（lookup_value,table_array,col_index_num,range_lookup）
lookup_value：为需要在数组第一列中查找的数值。

lookup_value 可以为数值、引用或文本字符串
table_array：为需要在其中查找数据的数据表。

可以使用对区域或区域名称的引用，例如数据库或列表
col_index_num：为 table_array 中待返回的匹配值的列序号。

col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为2，返回 table_array 第二列中的数值，以此类推
range_lookup：为一逻辑值，指明函数vlookup返回时是精确匹配还是近似匹配。

如果为 true或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果range_value 为 false，函数 vlookup 将返回精确匹配值。

（2）Hlookup—在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值
语法形式：Hlookup（lookup_value,table_array,row_index_num,range_lookup）
lookup_value：为需要在数据表第一行中进行查找的数值。

lookup_value 可以为数值、引用或文本字符串
table_array：为需要在其中查找数据的数据表。

可以使用对区域或区域名称的引用
row_index_num：为 table_array 中待返回的匹配值的行序号。

row_index_num 为 1 时，返回table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推
Range_lookup：为一逻辑值，指明函数 hlookup查找时是精确匹配，还是近似匹配。

如果为 true 或省略，则返回近似匹配值。

也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。

如果 range_value 为 false，函数hlookup 将查找精确匹配值。

13. 文本函数
（1）lower——将一个文字串中的所有大写字母转换为小写字母
语法形式：lower（text）
（返回目录）
（2）upper——将文本转换成大写形式
语法形式：upper（text）
（3）proper——将文字串的首字母及任何非字母字符之后的首字母转换成大写，将其余的字母转换成小写
语法形式：proper（text）
（4）trim——去除字符串中多余的空格
语法形式：trim（text）
（5）left——从左侧提取字符
语法形式：left（text,num_chars）
（6）mid——从左侧开始，提取中间字符
语法形式：mid（text,start_num,num_chars）
（7）right——从右侧提取字符
语法形式：right（text,num_chars）
（8）合并字符串—concatenate
语法形式：concatenate（text1,text2,...）
14. 逻辑运算函数
if—执行真假值判断，根据逻辑计算的真假值，返回不同结果。

语法形式：if（logical_test,value_if_true,value_if_false）
logical_test 表示计算结果为 true 或 false 的任意值或表达式
value_if_true：logical_test 为 true 时返回的值
value_if_false：logical_test 为 false 时返回的值
15. 日期和时间函数
（1）Today——返回系统当前日期
语法形式：Today（）
（2）Networkdays返回日期间的工作日数量
语法形式：NETWORKDAYS（start_date，end_date，holidays）
例：如计算2008年7月20日至9月20日，则公式为=NETWORKDAYS("2008-7-20","2008-9-20")，如需设置2008年8月8日为非工作日，则公式为=NETWORKDAYS("2008-7-20","2008-9-20","2008-8-8") 注：此函数需加载分析工具库，步骤：工具->加载宏->分析工具库（返回目录）
（3）Datedif--计算两个日期之间的天数、月数或年数
语法形式
DATEDIF（Start_Date,End_Date,Unit）
Start_date：为一个日期，它代表时间段内的第一个日期或起始日期
End_date：为一个日期，它代表时间段内的最后一个日期或结束日期
Unit：为所需信息的返回类型；Unit代表日期的计算方法，“Y”计算整年数；“M”计算整月数；“D”计算整日数；“YM”计算不到一年的月数；“YD”计算不到一年的日数；“MD”计算不到一个月的日数
（4）Year：返回某日期的年份
语法形式：YEAR（serial_number）
16. 文件加密与解密
步骤：
加密：
（1）打开文件->工具->选项->安全性
（2）打开权限密码->输入密码->确定（如图1-15）
（3）根据提示再次输入密码->确定->保存文件（如图1-16）
图1-15
（返回目录）
图1-16
解密：打开已加密的文件->工具->选项->安全性->打开权限密码->删除之前设置的密码->确定->保存
注：如需设置修改权限，则设置修改权限密码，方法同上；此方法也适用于word文档加/解密
17. 工作簿保护
步骤：工具->保护->保护工作簿（如图1-17）
可选保护内容：结构：工作表位置无法调整，不能插入删除工作表
窗口：冻结窗格、拆分等功能无法使用
图1-17
18. 单元格保护
步骤：
（1）全选工作表->右键->设置单元格格式->保护->取消锁定选项（如图1-18）
（2）选定所有需要保护的单元格->右键->设置单元格格式->保护->锁定（如图1-19）
（3）工具->保护->保护工作表（如图1-20）
（返回目录）
图1-18
图1-19
图1-20
注：如需保护整个工作表，则忽略步骤（1）、（2），直接进行（3）操作
19. 使工作表仅显示若干列
步骤：选中所有需要隐藏的列->右键->隐藏（如图1-21）
（返回目录）
图1-21
注：选定单元格的快捷操作：向右全选Ctrl+Shift+→，向下全选Ctrl+Shift+↓
20. 窗口冻结
步骤：
（1）确定光标所在单元格的位置，需冻结的行位于光标上方，需冻结的列位于光标左方（如图1-22）（2）窗口->冻结窗格，这样B4单元格以上的3行和左面的1列即被冻结（效果如图1-23）
图1-22
（返回目录）
图1-23
21. 反转表格中的列（如A->Z列顺序反转）
步骤：
（1）在需要反转的全部单元格上/下方添插入一行，依次命名为1-26
（2）全选需要反转的单元格及辅助行
（3）数据->排序->选项->方向->按行排序->确定->降序（如图1-24、1-25）
图1-24 图1-25
22. 自动重算与手动重算
问题一：公式拖动复制以后，后面的单元格只显示了第一个的值，在按下保存或者发送以后才显示出正确的值，为什么？
步骤：检查是否设置了“手动重算”，工具->选项->重新计算->计算->自动重算（如图1-26）
（返回目录）
图1-26
注：手动重算所有文档的快捷键为F9
问题二：在工作簿中粘贴了大量bloomberg或天相中带公式的数据，会导致工作簿每次操作执行都会很慢，如何解决？
步骤：工具->选项->重新计算->计算->将自动重算改为手动重算
23. 分列Excel中英文混杂的内容
步骤：
（1）手动添加分隔符号（如“，”）（如图1-27）
（2）数据->分列->分隔符号->选择添加的分隔符（如图1-28、1-29）
（3）在目标区域里可以选择分列后的存放位置，默认为原有单元格及其右侧一列（如图1-30）
图1-27
（返回目录）
图
1-28
（返回目录）
图1-30
注：也可选择已有的分隔符号作为字段的分隔符号
24. 快速整列填充数据
步骤：
（1）编辑->定位->输入需要填充的最后一个单元格编号（如A1000）
（2）向上快速全选单元格（Ctrl+Shift+↑）->在编辑栏中输入数值->Ctrl+Ente
注：快捷方式为按”Ctrl+D”组合键可快速向下填充，按”Ctrl+R”组合键可快速向右填充
25. 快速删除重复记录的行
步骤：
（1）选定含有重复记录的工作区域->数据->筛选->高级筛选
（2）选择”在原有区域显示筛选结果”->选择不重复的记录到列表区域（如图1-31）
（返回目录）
图1-31
注：将筛选结果复制到其他位置可以人工设置显示结果的区域
26. 建立连接到不相邻的若干块区域的超链接
步骤：
（1）插入->名称->定义->输入英文作为名称->选择引用位置->添加（如图1-32）
（2）选定需添加超链接的单元格->右键->超链接->本文档中的位置->已定义名称->选择已定义的名称（如图1-33）
图1-32
（返回目录）
图1-33
27. 文件打开后无内容且为灰色背景
步骤：工具->选项->常规->取消勾选忽略其他应用程序（如图1-34）
图1-34
28. 设置单元格下拉菜单
步骤：
（1）选中所有内容类别相同的单元格->数据->有效性
（2）设置->允许->序列->在来源中输入下拉菜单选择内容，并以英文“,”隔开（如图1-35）
（返回目录）
图1-35
注：如数据在同一sheet中可直接选择单元格范围，如数据在其他sheet中则必须设置自定义名称来实现，即插入->名称->定义
29. 单元格绿色角标释义
如图1-36所示，单元格会出现绿色角标，通常情况下，其原因如下：
（1）如果单元格中是数字，表示该数字是以文本形式存储的
（2）如果单元格中是公式，表示此公式错误或与周围单元格公式不同
图1-36
（返回目录）。