excel培训手册之潜龙在渊-飞龙在天--小本秘籍之一

excel培训手册之潜龙在渊-飞龙在天--小本秘籍之一内容摘要：

‱)，或在 Excel 内（ Word 内也行）按以下操作：【插入】菜单 〖符号〗子菜单，在字体： Lucida Sans Unicode，子集为 “ 广义标点 ” 中选取。 技巧： 如果你需要经常输入某个生僻的字符，你可以在【工具】菜单 → 〖自动更正选项〗子菜单将某字符串设置为自动替换为该字符，这样可大大 提高效率。 链接： 如果要经常输入某字符串，如 “ 逸凡账务系统是个 Excel 财务软件 ” ，既可采用自动更正的方法也可采用手工造词来达到快速输入的目的。 此方法在 office 其他软件里同样适用。 八、插入分页符 操作：【插入】 → 〖分页符〗 在要插入水平或垂直分页符的位置下边或右边选中一行或一列，单击右键，再单击 “ 插入分页符 ”。 链接：删除分页符 选定插入的分页符后的那行 /列，点击 “ 插入 ” 菜单，此时的 “ 分页符 ” 已变成 “ 删除分页符 ” ，点击即可删除分页符。 九、插入函数 操作：【插入】 → 〖函数〗 函数是 Excel最强大最实用的最迷人的功能之一。 什么是函数呢。 函数是一些预定义的公式，通过使用一些称为参数的特定数值来按特定的顺序或结构执行计算。 如果你不太理解这句话，你就把函数理解为 “ 函数就是能完成特定功能的字符串，这些字符串已经预设了公式。 要使用它，必须得按照既定的格式来使用，否则它就无法完成这些特定功能 ”。 函数的结构以等号 (=) 开始，后面紧跟函数名称和左括号，然后以逗号分隔输入该函数的参数，最后是右括号。 函数可以嵌套使用，但嵌套层数最多为七级。 在使用函数时必须注意函数中的引用类型。 如果需要多行或多列地复制公 式，那么请使用相对引用或正确的混合引用。 函数中可以使用预先定义的名称或行列标志。 具体用法请参见 Excel 的联机帮助。 链接 ：在编辑公式时选择要更改的引用，按 F4可切换引用类型。 下面简单介绍一下本人认为必须掌握的函数： if、 and、 or、 sum、 sumif、 dsum、 countif、 vlookup、round。 其它常用函数拟在《龙逸凡培训手册》之飞龙在天详述。 或直接到 、 的函数版面的精华贴下载各网友总结的精彩实例。 双击打开下表参看具体实例： 十、插入名称 操作：【插入】 → 〖名称〗 定义名称有如下好处： 减少输入的工作量、使公式更简洁、使人们更容易理解公式的含义、突破 Excel 对函数的嵌套层数仅为七层的限制（类似于使用辅助单元格）、使用某些特殊的函数（如 EVALUATE、 ） 使用方法：【插入】 → 〖名称〗，再单击 “ 定义 ”。 在 “ 在当前工作簿中的 名称 ” 框中，输入公式的名称。 在 “ 引用位置 ” 编辑框中，键入 “=” （等号）和公式内容或常量数值。 注意：名称的第一个字符必须是字母或下划线，名称中不能有空格，可以用下划线和句号作单词分隔符，名称中不区分大小写，名称最多可以包含 255 个字符。 技巧 1：数据有效性条件中不能使用对其他工作簿或工作表的引用，使用定义名称就可绕过此限制。 举例说明： Sheet1 的 A1 单元格仅限于输入 sheet2 表 B1： B20 单元格区域的数值，如果你在 A1单元格的数据有效性中定义其允许序列的来源为 sheet2!B1:B20，系统会提 示：数据有效性条件中不能使用对其他工作簿或工作表的引用。 此时你将 sheet2!B1:B20 定义为名称 “ 数据 A” ，然后将 Sheet1 的 A1的有效性设置为序列 “= 数据 A” 即可搞定。 技巧 2：定义的名称可引用多个工作表中的单元格或单元格区域 方法和定义名称一样，只是最后一个步骤有所不同： 单击需要引用的第一个工作表的标签，按住 Shift 同时单击需要引用的最后一个工作表的标签，选定需要引用的单元格或单元格区域，得出类以 “=Sheet1:Sheet3!$B$3:$B$8” 的公式。 技巧 3：将已有行列标志作为名称使 用 在创建需要引用本工作表中数据的公式时，可以使用工作表中的列标或行标来引用数据，此时引用数据不必定义名称，直接使用列标或行标即可。 默认情况下， Excel 不会识别公式中的标志。 若要在公式中使用标志，在【工具】菜单 → 〖选项〗 →“ 重新计算 ” 选项卡， “ 工作簿选项 ” 之下，选中 “ 接受公式标志 ” 复选框。 如果工作表中有层叠列标，即一个单元格标志的下面紧接着一个或多个标志。 在公式中可以使用层叠标志来引用工作表中的数据。 例如下图表中的公式使用层叠标志来分别计算工资 1 与工资 2 列的和。 链接： 在多个工作表中命名单元格实际上 就是使用了三维引用。 三维引用 是指对跨越工作簿中两个或多个工作表的区域的引用。 三维引用在对多工作表的同一单元格或单元格区域进行计算时很实用。 例如：需对 sheet1 到 sheet30 工作表的 B20 单元格求和，不必使用 =sheet1!B20+ sheet2!B20+„+ sheet30!B20 公式 ,只需使用 =sum(sheet1:sheet30!B20)公式即可。 十一、插入超链接 操作：【插入】 → 〖超链接〗 在一个工作簿有很多工作表时，用这个功能做目录时很实用。 超链接也可链接到其他文件或网页。 总之，您可创建指 向新文件、现有文件或网页、网页上的某个具体位置和 Office 文件中的某个具体位置的超链接，也可创建指向电子邮件地址的超链接。 还可指定在将鼠标指针停放在超链接上时显示的提示。 比较简单，不详述。 技巧： 在插入了超链接的单元格单击一次为用于跟踪，单击并按住不放可选定该单元格。 链接 ：在单元格中还可使用 HYPERLINK 函数创建超链接，两者最终效果一样， HYPERLINK 函数更灵活，但 “ 插入超链接 ” 的适用范围更广。 十二、设置单元格格式 操作：【格式】 → 〖单元格〗 单元格格式可设置数字格式、对齐格式、字体、 边框等格式。 “ 保护 ” 选项卡 “ 锁定 ” ：保护所选单元格以避免更改、移动、调整大小或删除。 只有在保护工作表时锁定单元格才有效。 若要保护工作表，请指向【工具】菜单上的〖保护〗，单击 “ 保护工作表 ” ，再选中 “ 内容 ”复选框。 隐藏：隐藏单元格中的公式，以便在选中该单元格时在编辑栏中不显示公式。 如果选择该选项，这只有在保护工作表时隐藏公式才有效。 若要保护工作表，请指向【工具】菜单上的〖保护〗，单击 “ 保护工作表 ” ，再选中 “ 保护工作表及锁定的单元格内容 ” 复选框。 “ 字体 ” 选项卡 下划线可设置成会计用单下划线、会 计用双下划线等格式。 会计用的下划线比普通下划线略为松散，如图： 在 “ 字体 ” 选项卡可将字符设置成上标、下标。 “ 对齐 ” 选项卡 可设置文本方向、单元格内文本的自动换行、将文本缩小字体填充至单元格。 注意：自动换行与缩小字体不能联用 技巧： 如要使某表格的表头处于居中，请尽量使用 “ 对齐 ” 选项卡的 “ 水平对齐 ” 中的 “ 跨列居中 ” 的对齐格式，而不要使用 “ 合并居中 ” 功能。 因为合并单元格无法使用部分功能，如下拉填充、使用格式刷等。 链接： 单元格内文本的强行换行用 Alt+Enter “ 数字 ” 选项卡 在该选项卡可将数字 设置成各种格式，如会计专用格式 [逸凡注：会计专用格式可对一列数值进行货币符号与小数点对齐 (此格式共综合了以下四种格式：千位分隔符、保留两位小数点、添加货币符号以及在数字与货币符号间用空格填充 )]、中文小写、大写数字格式。 逸凡注 ：别指望微软提供的中文大写数字格式能有很大实用价值，不过可以以此为基础，用函数公式编写出真正的中文大写数字，具体实例请到网上搜索或到我的博客下载（ 注意，所设置的格式仅为显示格式，并不影响数字的值。 下面重点讲解一下数字格式的自定义功能： 完整的格式代码由四个部分组成，这四部分顺序定义了格式中的正数、负数、零和文本。 格式代码各部分以分号分隔。 完整格式如下图： 它们如果只指定两个部分，则第一部分用于表示正数和零，第二部分用于表示负数。 如果只指定一个部分，则该部分可用于所有数字。 如果要跳过某一部分，则使用分号代替该部分即可。 可以自定义格式代码来定义如何显示数字、日期或时间、货币、百分比或科学计数以及文本或空格等， 具体方法请参见 Excel 的联机帮助 “ 创建或删除自定义数字格式 ”。 特殊符号在自定义格式中的使用： 在自定义格式中可以使用多种符号，它们的作用各不相同，下面分别介绍一些常用符号的用法。 在自定义格式中，下面的字符不必用引号括起来： $、 、 +、 /、 ( )、 :、 !、 ^、 amp。 、 39。 （左单引号）、 39。 （右单引号）、 ~、 { }、 =、＜、＞ 和空格符。 ，作为千位分隔符或以一千为单位表示数字的数量级，请参见本专题 技巧 7 的实例。 显示双引号之间的文本。 “” 或 “!” ：显示下一个字符。 和 用途相同。 且输入后会自动转变为 双引号表达。 不同的是 “”显示后面的文本，双引号是显示双引号中间的文本。 此设定此格式后。 如果单独使用 “” 或 “!” 来自定义格式则将输入的数字显示为所设定的文本，但对输入的文本无效。 只显示有效数字而不显示无效的零。 0（零）如果数字的位数少于格式中的零，则显示无效的零。 ? 在小数点两边添加无效的零，以便当按固定宽度字体设置格式（例如 Courier New）时，小数点可对齐。 还可对具有不等长数字的分数使用 ?。 * 可使星号之后的字符填充整个列宽。 例如，格式 “0*A” 可在数字后包含足够的 A以填充整个单元格，一直把整个单元格塞满为止，即使列宽增加，它还是会自动塞满的。 [] 中括号在自定义格式中有两个用途：使用颜色代码、使用条件。 如自定义格式 “ [红色 ][＜=100]。 [蓝色 ][＞ 100]” 表示以红色字体显示小于和等于 100 的数字，而以蓝色字体显示大于 100 的数字。 @若要在数字格式中包括文本部分，请在要显示输入单元格中的文本的地方加入符号 @，自定义格式 “ @逸凡账务系统 ” ，如在单元格输入 123，则显示 “123 逸凡账务系统 ”。 逸凡注： 在格式中使用 @与使用 有区别。 @可用于数字或文本，如 用于数字也是以文本形式存储的，而类似 “ 逸凡账务系统 ” 的格式仅适用于数字，对文本无效。 如下图： 技巧 1： 根据数值的正负自动判断加载不同的 “ 前后缀 ” ：如将格式设置为 “ 盈余。 短缺” 、或 “$ 剩余。 $短缺 ” ，分别输入正负数你便可体会到两者的区别。 技巧 2：数字格式中的智多星 —— 自动设置满足指定条件的数字格式。 在格式代码中加入带中括号的条件，条件由比较运算符和数值两部分组成。 例如，下面的格式以红色字体显示小于和等于 100 的数字，而以蓝色字体显示大于 100 的数字： [红色 ][＜ =100]。 [蓝色 ][＞ 100] 逸凡注 ：此功能有点类以于条件格式。 但此条件仅限于由比较运算符和数值两部分组成（此类运算符包括： = 等于、＞大于、＜小于、＞ =大于等于、＜ = 小于等于和 ＜＞ 不等于）。 而条件格式的适用范围更广，还可使用由函数组成的公式组成的条件，格式范围也更多更广。 技巧 3： 自动显示固定宽度：如将格式自定义成 000000，则输入小于 6 位的数字，自动在前面以 0填充，输入大于 6位的数字或文本则不变。 技巧 4： 如果单元格区域既含有五位数的邮政编码又含有九位数的邮政编码 ，则可以使用自定义格式以便同时正确显示这两种类型的邮政编码。 方法是：单击 “ 分类 ” 列表中的 “ 自定义 ” ，然后在 “ 类型 ” 框中键入下列内容： [＜ =99999]00000。 000000000 技巧 5:Excel 中的障眼法 在【格式】菜单上，单击〖单元格〗子菜单，再单击 “ 数字 ” 选项卡 ,在 “ 分类 ” 列表中，单击“ 自定义 ”, 在 “ 类型 ” 框中键入 “ 逸凡账务系统 ” 试试 ,不管输入什么 ,显示出来的都是 “ 逸凡账务系统 ” 了。 如需将输入的所有数字均以 *显示，自定义格式为 “ **。 **。 **” ，同理，将输入的所有数字均以A 显示 ，自定义格式为 “ *A。 *A。 *A”。 技巧 6: Excel 中的隐身术 方法 1:将单元格字体颜色设置成与底色一样的颜色 方法 2:【格式】 → 〖单元格〗 →“ 数字 ” 选项卡 ,在 “ 分类 ” 列表中，单击 “ 自定义 ”, 在 “ 类型 ”框中键入 “。 ” （三个分号）（此格式隐藏单元格所有的数值或文本）；格式 “。 ” （两个分号）隐藏数值而不隐藏文本。 格式 “。 ” 只显示正数；格式 “。 0。 ” 只显示零值。 格式 “ ” 隐藏正数和零值，负数显示为 ，文字不会隐藏；格式 “ ???” 仅隐藏零值 ,而不隐藏非零值和文本。 技巧 7： Excel 中的变形 术 在输入数字前加一个单引号 ’ ，数字就 “ 变形 ” 为文本了。 输入的单引号不会显示出来，也不计入字符串的长度（你可用 len 函数试试）。 如果要再次将该文本格式的数字转换为数字格式，请 “ 使用选择性粘贴 → 乘 ” 的功能（请参见 “ 选择性粘贴 →。