excel使用技巧培训[1]

EXCEL使用技巧培训 EXCEL学习方法 1 循序渐进从扫盲入手 学会EXCEL的基本操作 如数据输入 查找替换 单元格设置 排序等 向中级用户进军 熟练EXCEL菜单命令 学会使用数据透视表 掌握常用函数使用和图表制作 2 善用资源 学以致用利用学过的东西尽可能解决问题常用EXCEL帮助学会利用互联网学习EXCEL EXCEL使用小技巧 目录 公式和函数介绍 EXCEL图表制作 宏的录制和运用 身份证号码输入的解决方法在Excel中实现文本换行的方法使用鼠标右键拖动单元格自动填充在相对引用 绝对引用和混合引用间快速切换快速删除空行 重复行单元格下拉列表的录入方式锁定Excel工作表表格标题栏数据的自动计算对工作簿进行安全保护EXCEL打印技巧选择性粘贴的妙用数据的分列和合并列数据透视表的使用数据有效性和条件格式 EXCEL使用小技巧 相信在EXCEL这个软件中如何输入15位或18位身份证号码的问题已经困挠了许多人 因为在EXCEL中 输入超过11位数字时 会自动转为科学计数的方式 比如身份证号是 123456789012345 输入后就变成了 1 23457E 14 现在为大家说2种比较简单快速的方法 身份证号码输入的解决方法 1 在A1单元输入号码时 在前面先输入 号 就是 123456789012345 这样单元格会默认为该单元为文本方式 会完整显示出15个号码来 而不会显示那令人讨厌的科学计数方式来了 2 如果已经输入了大量的号码 全部以科学计数显示的话 而又懒的按照上面的方法一个一个的重新输入的话 也有这个好方法哦 比如已在单元A1 A100输入了号码 按以下步骤做 选择单元A1 A100 单击鼠标右键 设置单元格式 选择自定义 在 类型 中输入 0 即可 轻松搞定 目前 越来越多的用户开始使用Excel通过数据库的方式来管理庞大的数据信息 但是有很多用户用惯了写字板 记事本之类的编辑器 而对Excel的操作有些不适应 特别是Excel在输入文字的过程中不象其他常用的编辑器会自动换行 以下2种方法实现在Excel中自如地进行换行 在Excel中实现文本换行的方法 1 使用命令来实现自动换行使用该方法来自动换行时 首先用鼠标选定某一单元格 然后用鼠标单击菜单栏中的 格式 菜单 从弹出的下拉菜单中选择 单元格 命令 随后系统将会弹出一个标题为 单元格格式 的对话框 在该对话框中单击 对齐 标签 从弹出的界面中选中 自动换行 复选框 最后单击 确定 退出设置对话框 2 通过复合键即时换行在其他文本编辑器中 我们如果需要即时换行的话 一般只要单击一下键盘上的回车键就可以了 但是在Excel中 我们如果单击回车键时就会发现光标会移动到当前单元格的下一格 因此 我们如果要实现光标在当前单元格中换行的话 只须单击键盘上的Alt Enter组合键就可以了 当然我们也可对已输入的单元格进行换行 只要在光标插入点处按Alt Enter组合键就行了 如果你需要输入诸如表格中的项目序号 日期序列等一些特殊的数据系列 可以通过使用鼠标拖到填充柄实现Excel的自动填充 使用鼠标右键拖动单元格自动填充 在第一个单元格内输入起始数据 在下一个单元格内输入第二个数据 选定这两个单元格 将光标指向单元格右下方的填充柄 光标变成一个黑色的十字 沿着要填充的方向拖动填充柄 拖过的单元格中会自动按Excel内部规定的序列进行填充 如果需填充单元格前一列单元格非空白 则可将鼠标指向填充柄 然后双击左键 EXCEL将该列单元格按前一列单元格行数自动填充 使用鼠标右键拖动单元格填充柄则更具灵活性 在某单元格内输入数据 按住鼠标右键沿着要填充序列的方向拖动填充柄 将会出现包含下列各项的菜单 复制单元格 以序列方式填充 以格式填充 以值填充 以天数填充 以工作日该充 以月该充 以年填充 序列 此时 你可以根据需要选择一种填充方式 在相对引用 绝对引用和混合引用间快速切换 相对引用指公式中的单元格位置将随着公式单元格的位置而改变 绝对引用是指公式和函数中的位置是固定不变的 是在列字母和行数字之前都加上美元符号 如 A 4 C 6 混合引用是指在一个单元格引用中 既有绝对引用 也有相对引用 例 行变列不变 A4 列变行不变 A 4 在Excel进行公式设计时 会根据需要在公式中使用不同的单元格引用方式 这时你可以用如下方法来快速切换单元格引用方式 选中包含公式的单元格 在编辑栏中选择要更改的引用 按F4键可在在相对引用 绝对引用和混合引用间快速切换 例如选中 A1 引用 反复按F4键时 就会在 A 1 A 1 A1 A1之间切换 1 利用 自动筛选 功能来实现删除空行 先选择表中所有的行 选择 数据 筛选 自动筛选 在每一列的顶部 从下拉列表中选择 空白 在所有数据都被选中的情况下 选择 编辑 删除行 然后按 确定 即可 所有的空行将被删去 2 利用 高级筛选 功能来实现删除重复行 先选择表中所有的行 选择 数据 筛选 高级筛选 在 选择不重复的记录 前打钩 选择 将筛选结果复制到其它位置 可看到所有的重复行被删去 快速删除空行 重复行 有时为了删除Excel工作薄中的空行或重复行 你可能会将空行或重复的行一一找出然后删除 这样做非常不方便 这里介绍一种非常便捷的方式 在应用程序中要减少用户的录入工作量 我们经常采用下拉列表的录入方式 在Excel中也可以自己建立下拉列表来简化录入工作 单元格下拉列表的录入方式 先选中需要显示下拉列表的单元格 再选择 数据 菜单下的 有效数据 从弹出的对话框中选择 设置标签 在 许可 下拉列表中选择 序列 下一步点击 来源 栏右侧的小图标 会打开一个 有效数据 小对话框 输入下拉列表中所需要的数据 注意各录入项目之间要用逗号隔开 确定后还要在对话框中将 提供下拉箭头 复选框选定 最后确定就可以了 当你使用Excel编辑工作表时 在编辑靠后或靠右的工作表单元格内容时 可能由于看不到表格的标题栏 搞不清对应于哪一个标题信息 如果你希望不论如何滚动工作表 表格的标题栏始终位于屏幕的可视区域 则请按下述步骤操作 锁定Excel工作表表格标题栏 1 把需要锁定表格标题栏的工作表切换为当前工作表 2 如果要锁定表格顶部的标题栏 则需单击标题栏下面一行的行号 以选定该行 如果要锁定表格左侧的标题栏 则需单击标题栏右面一列的列标 以选定该列 如果要同时锁定表格顶部和左侧的标题栏 则需单击两个标题栏相交处右下方的单元格 比如顶部的标题栏占用了工作表的第1 3行 左侧的标题栏占用了工作表的第A B列 则你应单击C4单元格 3 单击 窗口 选单中的 冻结窗格 命令 之后 无论你怎样滚动工作表 标题栏都不会因表格移动而滚出屏幕的可视区域 这大大方便了你对大工作表的编辑工作 如果要取消对标题栏的锁定 则可单击 窗口 选单中的 撤消窗口冻结 命令 数据的自动计算 Excel具备了数据库的一些特点 可以把工作表中的数据做成一个类似数据库的数据清单 可以直接从状态栏中获得数据自动计算的结果 如果你不想别人打开或修改你的工作簿 那么想法加个密码吧 下面介绍2种工作簿保护方法 对工作簿进行安全保护 1 打开工作薄 选择 文件 菜单中的 另存为 命令 选取 工具 常规选项 根据用户的需要分别输入 打开文件口令 或 修改文件口令 按 确定 退出 2 还可对工作表中某些单元格区域的重要数据进行保护 防止他人擅自修改数据 首先 选定需保护的单元格区域 选取 格式 菜单中的 单元格 命令 选取 保护 从对话框中选取 锁定 单由 确定 按钮退出 然后选取 工具 菜单中的 保护 命令 选取 保护工作表 根据提示两次输入口令后退出 注意 不要忘记你设置有 口令 EXCEL打印技巧 一 设置打印标题当Excel工作表中的数据超过一页长时 无论是预览还是打印 工作表中的列标题只出现在第一页上 而其它页上的数据都没有列标题 这给阅读带来极大的不方便 解决这一问题的方法为 选取 文件 选单下的 页面设置 选项 在弹出的 页面设置 对话框中选择 工作表 标签 在 打印标题 下方 在 顶端标题行 对话框中输入行地址如 1 1 单击 确定 按钮 通过预览你会发现 标志列数据的列标题出现在所有其他页中 二 按指定的页数打印有时在打印一个工作表时 可能会出现打印超过一页 但另一页的数据又比较少 这样 我们可以调为一页打印 其操作步骤是 单击 文件 菜单的 页面设置 命令 然后单击 页面 选项卡 单击 调整为 选项 键入打印工作表内容所需要的页数 这样 打印的数据不会超出指定的页数范围 注意 Excel不会为了填满所有的页而放大数据 在将工作表以指定的页数打印时 Excel将忽略人工设置的分页符 一 快速的批量修改数据假如有一份Excel工作簿 里面有所有职工工资表 现在想将所有职工的补贴增加50 元 当然你可以用公式进行计算 但除此之外还有更简单的批量修改的方法 即使用 选择性粘贴 功能 首先在某个空白单元格中输入50 选定此单元格 选择 编辑 复制 选取想修改的单元格区域 例如从E2到E150 然后选择 编辑 选择性粘贴 在 选择性粘贴 对话框 运算 栏中选中 加 运算 按 确定 健即可 最后 要删除开始时在某个空白单元格中输入的50 同样的办法可以实现对一列 行 的乘 除 减等其它的运算操作 原表格的格式也没有改变 选择性粘贴的妙用 二 进行行列转置当我们设计好一份Excel表格后 如果发现行和列的位置不太符合自己的要求 希望将行与列的位置进行调换 这时你可先将需要转换行列位置的单元格选定 单击工具栏上 复制 按钮 然后在复制区域之外选定粘贴区域的左上角单元格 选择 编辑 菜单下的 选择性粘贴 命令 选中 转置 选项 点击 确定 按钮 这样就可完成表格行列转置 三 选择性粘贴数值当我们复制粘贴含有计算公式的单元格数据时 为确保黏贴后的数据不会随单元格变动而变动 需要用到选择性黏贴 选中数值 如此 该单元格数值固定不变了 一 数据分列功能EXCEL工作簿中 数据 分列 功能可以将选中的数据列按分隔字符或固定的宽度进行快速分列 分割字符可以是Tab键 分号 逗号 空格以及其它自定义的符号 由于数据分列将一列数据分列成多列 往往将需分列的数据调整至最后一列再进行分列 避免替换掉目标单元格的内容 二 列数据格式转换功能通过数据分列功能还可对某列数据格式进行设置 常规 数据格式将数值转换为数字 日期值会转换为日期 其余格式则转换成文本 三 合并列合并列即将2列或2列以上数据合并在一列中 可直接使用文本运算符 连接需合并的列数据 如在C2键入 A2 B2 则C2中显示的就是A2和B2合并后的数据 数据的分列和合并列 数据透视表的使用 数据透视表是交互式报表 可快速合并和比较大量数据 您可旋转其行和列以看到源数据的不同汇总 而且可显示感兴趣区域的明细数据 源数据第三季度高尔夫汇总的源值数据透视表C2和C8中源值的汇总 数据透视表的使用 数据有效性和条件格式 数据有效性和条件格式可以实现以下功能 1 在EXCEL中设置单列不允许出现重复值设定 2 单列有重复值用颜色标出 3 工作簿用颜色隔行标数据 数据有效性和条件格式 EXCEL使用小技巧 目录 公式和函数介绍 EXCEL图表制作 宏的录制和运用 使用公式 公式中的运算符算术运算符 使用公式 公式中的运算符2 文本运算符 号 可以将文本连接起来 3 比较运算符 使用公式 公式中的运算符4 引用运算符 例 A4 B4 C4 C4 E4 F4 可写为 A4 SUM B4 F4 使用公式 公式中的运算顺序如果在公式中要同时使用多个运算符 则应该了解运算符的优先级 算术运算符的优先级是先乘幂运算 再乘 除运算 最后为加 减运算 相同优先级的运算符按从左到右的次序进行运算 公式中出现不同类型的运算符混用时 运算次序是 引用运算符 算术运算符 文本运算符 如果需要改变次序 可将先要计算的部分括上圆括号 使用公式 引用单元格1 相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变2 绝对引用绝对引用是指公式和函数中的位置是固定不变的 绝对引用是在列字母和行数字之前都加上美元符号 如 A 4 C 63 混合引用混合引用是指在一个单元格引用中 既有绝对引用 也有相对引用 例 行变列不变 A4列变行不变 A 4 创建公式 公式是指对工作表中的数据进行分析和处理的一个等式 不仅用于算术计算 也用于逻辑运算 日期和时间的计算等 一个公式必须具备3个条件 以等式开头 2 含有运算符和运算对象 3 能产生结果公式的3个基本组成部分 数值 包括数字和文本单元格引用操作符 创建公式 创建公式的方法使用编辑栏直接在单元格中输入创建3种类型的公式创建简单公式 2 创建包含引用或名称的公式3 创建包含函数的公式 使用函数 函数是指预定义的内置公式 可以进行数学 文本和逻辑的运算或者查找工作表的信息 函数是一个已经提供给用户的公式 并且有一个描述性的名称 函数结构函数名称 如果要查看可用函数的列表 可以单击一个单元格并按Shift F3键 参数 参数可以是数字 文本 逻辑值 TRUE或FALSE 数值 错误值 如 N A 或单元格引用 参数工具提示 在输入函数时 会出现一个带有语法和参数的工具提示 输入公式 插入函数 对话框 函数的参数函数的参数是一个函数体中括号内的部分 逻辑值 FALSE 需要完全一致TRUE 相似值亦可转换 使用函数 函数的类型1 常用函数 常用函数指经常使用的函数 如求和或计算算术平均值等 包括SUM AVERAGE IF HYPERLINK COUNT MAX SIN SUMIF 它们的语法和作用如下表 使用函数 函数的类型2 时间和日期函数 主要用于分析和处理日期值和时间值 包括DATE DATEVALUE DAY HOUR TIME TODAY WEEKDAY和YEAR等 例 函数DATA语法 DATE year month day 使用函数 函数的类型3 数学与三角函数 4 统计函数 统计函数是用来对数据区域进行统计分析 主要用于进行各种各样的计算 包括ABS ASIN COMBINE COSLOG PI ROUND SIN TAN和TUUNC等 例 ABS函数语法 ABS number 函数是一种特殊的公式 因此所有的函数要以 开始 使用方法如下 1 选中要输入函数的单元格2 单击 Insert 菜单中的命令 弹出如图所示的对话框 使用函数 3 在上图的对话框中 选择 函数分类 下面的函数类型及 函数名 下面的对应函数名 4 单击 确定 按钮 弹出如图所示的相应的调用函数对话框 使用函数 函数的使用 SUM SUM函数函数名称 SUM主要功能 计算所有参数数值的和 使用格式 SUM Number1 Number2 参数说明 Number1 Number2 代表需要计算的值 可以是具体的数值 引用的单元格 区域 逻辑值等 特别提醒 如果参数为数组或引用 只有其中的数字将被计算 数组或引用中的空白单元格 逻辑值 文本或错误值将被忽略 如果将上述公式修改为 SUM LARGE D2 D63 1 2 3 4 5 则可以求出前5名成绩的和 函数的使用 AVERAGE 函数名称 AVERAGE主要功能 求出所有参数的算术平均值 使用格式 AVERAGE number1 number2 参数说明 number1 number2 需要求平均值的数值或引用单元格 区域 参数不超过30个 特别提醒 如果引用区域中包含 0 值单元格 则计算在内 如果引用区域中包含空白或字符单元格 则不计算在内 函数的使用 IF IF函数函数名称 IF主要功能 根据对指定条件的逻辑判断的真假结果 返回相对应的内容 使用格式 IF Logical Value if true Value if false 参数说明 Logical代表逻辑判断表达式 Value if true表示当判断条件为逻辑 真 TRUE 时的显示内容 如果忽略返回 TRUE Value if false表示当判断条件为逻辑 假 FALSE 时的显示内容 如果忽略返回 FALSE 说明 函数IF可以嵌套七层 用value if false及value if true参数可以构造复杂的检测条件 在计算参数value if true和value if false后 函数IF返回相应语句执行后的返回值 如果函数IF的参数包含数组 则在执行IF语句时 数组中的每一个元素都将计算 函数的使用 COUNTIF SUMIF COUNTIF函数函数名称 COUNTIF主要功能 统计某个单元格区域中符合指定条件的单元格数目 使用格式 COUNTIF Range Criteria 参数说明 Range代表要统计的单元格区域 Criteria表示指定的条件表达式 特别提醒 允许引用的单元格区域中有空白单元格出现 SUMIF函数函数名称 SUMIF主要功能 计算符合指定条件的单元格区域内的数值和 使用格式 SUMIF Range Criteria Sum Range 参数说明 Range代表条件判断的单元格区域 Criteria为指定条件表达式 Sum Range代表需要计算的数值所在的单元格区域 VLOOKUP函数 一 主要功能 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 使用格式 VLOOKUP lookup value table array col index num range lookup 参数说明 Lookup value为需要在数组第一列中查找的数值 Lookup value可以为数值 引用或文本字符串 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 例如数据库或列表 如果range lookup为TRUE 则table array的第一列中的数值必须按升序排列 2 1 0 1 2 Z FALSE TRUE 否则 函数VLOOKUP不能返回正确的数值 如果range lookup为FALSE table array不必进行排序 通过在 数据 菜单中的 排序 中选择 升序 可将数值按升序排列 Table array的第一列中的数值可以为文本 数字或逻辑值 文本不区分大小写 Col index num为table array中待返回的匹配值的列序号 Col index num为1时 返回table array第一列中的数值 col index num为2 返回table array第二列中的数值 以此类推 如果col index num小于1 函数VLOOKUP返回错误值值 VALUE 如果col index num大于table array的列数 函数VLOOKUP返回错误值 REF Range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A 说明如果函数VLOOKUP找不到lookup value 且range lookup为TRUE 则使用小于等于lookup value的最大值 如果lookup value小于table array第一列中的最小数值 函数VLOOKUP返回错误值 N A 如果函数VLOOKUP找不到lookup value且range lookup为FALSE 函数VLOOKUP返回错误值 N A VLOOKUP函数的应用 二 函数的使用 DATE TRUE DATE函数主要功能 给出指定数值的日期 使用格式 DATE year month day 参数说明 year为指定的年份数值 小于9999 month为指定的月份数值 可以大于12 day为指定的天数 应用举例 在单元格中输入公式 DATE 2003 13 35 确认后 显示出2004 2 4 特别提醒 由于上述公式中 月份为13 多了一个月 顺延至2004年1月 天数为35 比2004年1月的实际天数又多了4天 故又顺延至2004年2月4日 TRUE返回逻辑值TRUE 语法TRUE 说明可以直接在单元格或公式中键入值TRUE 而可以不使用此函数 函数TRUE主要用于与其他电子表格程序兼容 EXCEL使用小技巧 目录 公式和函数介绍 EXCEL图表制作 宏的录制和运用 制作与使用图表 图表是Excel为用户提供的强大功能 通过创建各种不同类型的图表 为分析工作表中的各种数据提供更直观的表示结果 使用图表向导根据工作表中的数据 创建不同类型的图表 在标准类型中运用较多的图型有柱形图 条形图 折线图 饼状图四种 在自定义类型中运用较多的是两轴折线图 两轴线 柱图 图表的编辑1 插入数据标志及增加图表标题2 改变图表的文字 颜色和图案 两轴线柱图表制作 我们往往会在一张图表上呈现2个不同维度的信息 这就需要用到2轴图表 点击插入 图表 自定义类型 选择两轴折线图或两轴线柱图 将2个不同维度的信息绘制在2个坐标轴上 图列 盐城各乡镇话务环比分析 EXCEL图表复制到WORD中可编辑的技巧 我们经常会碰到在EXCEL中制作的图表要复制到WORD中去 有时发现WORD中的图表数据需修改 往往需要重新在EXCEL中制作后再复制过来 比较麻烦 这里介绍一个小技巧 当在EXCEL中选中图片复制粘贴到WORD中时 会发现图表右下发有一个粘贴的小图标 用鼠标右键点击该图标 可看到三个选项 默认设置是第一个选项 如今后会修改到图表数据 则建议选第二个选项 如此 在WORD中双击图表时 可以如同在EXCEL中一样编辑修改数据了 EXCEL使用小技巧 目录 公式和函数介绍 EXCEL图表制作 宏的录制和运用 宏的录制与运用 什么是宏 如果经常在MicrosoftExcel中重复某项任务 那么可以用宏自动执行该任务 宏是一系列命令和函数 存储于VisualBasic模块中 并且在需要执行该项任务时可随时运行 例如 如果经常在单元格中输入长文本字符串 则可以创建一个宏来将单元格格式设置为文本可自动换行 录制宏在录制宏时 Excel在您执行一系列命令时存储该过程的每一步信息 然后即可运行宏来重复所录制的过程或 回放 这些命令 如果在录制宏时出错 所做的修改也会被录制下来 VisualBasic在附属于某工作薄的新模块中存储每个宏 宏的录制与运用 使宏易于运行可以在 宏 对话框的列表中选择所需的宏并运行宏 如果希望通过单击特定按钮或按下特定组合键来运行宏 可将宏指定给某个工具栏按钮 键盘快捷键或工作表中的图形对象 管理宏宏录制完后 可用VisualBasic编辑器查看宏代码以进行改错或更改宏的功能 例如 如果希望用于文本换行的宏还可以将文本变为粗体 则可以再录制另一个将单元格文本变为粗体的宏 然后将其中的指令复制到用于文本换行的宏中 VisualBasic编辑器 是一个为初学者设计的编写和编辑宏代码的程序 而且提供了很多联机帮助 不必学习如何编程或如何用VisualBasic语言来对宏进行简单的修改 利用 VisualBasic编辑器 您可以编辑宏 在模块间复制宏 在不同工作簿之间复制宏 重命名存储宏的模块或重命名宏 宏的录制与运用 宏安全性Excel对可通过宏传播的病毒提供安全保护 如果您与其他人共享宏 则可使用数字签名来验证其他用户 这样就可保证其他用户为可靠来源 无论何时打开包含宏的工作簿 都可以先验证宏的来源再启用宏 宏的录制与运用 创建宏录制宏将安全级设置为 中 或 低 操作方法在 工具 菜单上 单击 选项 单击 安全性 选项卡 在 宏安全性 之下 单击 宏安全性 单击 安全级 选项卡 再选择所要使用的安全级 在 工具 菜单上 指向 宏 再单击 录制新宏 在 宏名 框中 输入宏的名称 注意宏名的首字符必须是字母 其他字符可以是字母 数字或下划线 宏名中不允许有空格 可用下划线作为分词符 宏名不允许与单元格引用重名 否则会出现错误信息显示宏名无效 如果要通过按键盘上的来运行宏 请在 快捷键 框中 输入一个字母 可用Ctrl 字母 小写字母 或Ctrl Shift 字母 大写字母 其中字母可以是键盘上的任意字母键 快捷键字母不允许是数字或特殊字符 如 或 注释当包含宏的工作簿打开时 宏快捷键优先于任何相当的MicrosoftExcel的默认快捷键 宏的录制与运用 在 保存在 框中 单击要存放宏的地址 如果要使宏在使用Excel的任何时候都可用 请选中 个人宏工作簿 如果要添加有关宏的说明 请在 说明 框中键入该说明 单击 确定 如果