Excel表格运用培训

目录 一 EXCEL表基础介绍二 常用选择技巧三 常用输入与编辑技巧四 工作表的操作与美化五 数据管理技巧六 公式与函数七 图表制作八 常用设置九 高级应用与自动化 EXCEL培训资料 一 ECXCEL表基础介绍 软件简介 Excel具有电子表格软件的特点 以 表格 方式处理数据 在需要计算的位置立即显示结果 所见即所得 表格之间可通过公式动态联结 具有强大的作图功能 可实现 图文并茂 以数据库的管理方式管理表格数据 具有排序 检索 筛选 汇总计算等功能 数据共享功能 可与其他软件交互 一 ECXCEL表基础介绍 窗口介绍 Excel的窗口分为三大部分 控制区 标题栏 菜单栏 常用格式工具栏工作区 名字框 编辑栏 工作表显示区 工作表标签 状态栏 标签显示区 一 ECXCEL表基础介绍 基本概念 文件 工作簿 工作表Excel的文件称为工作簿 它默认包含3个工作表 最多255个 由Sheet1到Sheet255分别命名 用户可按需求增减工作表 工作簿文件的扩展名为 xls 单元格工作表中由横竖线交叉组成的矩形方框即为单元格每个工作表中有65536行 256列 最多可包含65536 256个单元格 每个单元格可显示1024个字符 二 常用选择技巧 1 选择切换工作簿 2 选择切换工作表 3 选择多个工作表 技巧 在同工作表中按Ctrl Tab键 区别Alt Tab键 技巧 选择第一个标签 按住Shift键 选择最后一个标签在标签中右键选 选定全部工作表 技巧 按Ctrl PageUp键前翻 按Ctrl PageDown键后翻 4 选择单元格 5 区域内快速切换单元格 正序 倒序 6 选择整行 整列 7 选择整个工作表 单行单列 单击行号或列标多行多列 与选择单元格一致 多个连续单元格 选择左上 按Shift键 选择右下多个不连续单元格 选择一个区域 按Ctrl键 选择另外一区域选择非隐藏单元格 点自定义工具栏上的选定可见单元格按钮再选择 单行单列 单击左上角行号与列标交叉处的按钮技巧 按Ctrl A键 技巧 选择一个区域 按Tab Shift Tab 按Enter Shift Enter 二 常用选择技巧 8 选择连续最远单元格 空或非空 9 重复最后以此操作 10 将选定区域扩展到与活动单元格同一行或同一列的最后一个非空白单元格 技巧 双击选择单元格的某边框全选技巧 按住Shift键双击 技巧 按F4或者Ctrl Y键 技巧 按Ctrl Shift 箭头键 二 常用选择技巧 三 常用输入与编辑技巧 1 撤销与恢复 2 填充 3 复制与粘贴 填充方法 按住填充柄拖拽连续填充技巧 双击填充柄等差数列技巧 输入前两个数 选中此两单元格填充等比数列技巧 类似等差 用右键填充 然后在跳出选项中选 等比数列 方法 按工具栏上 撤销 恢复 的按钮技巧 按Ctrl Z撤销 按Ctrl Y恢复 技巧 按Ctrl C键复制 按Ctrl V键粘贴技巧 直接拖动单元格剪贴 按Ctrl键拖动复制粘贴粘贴数值技巧 复制后按工具栏 粘贴数值 按钮 工具 自定义 转置粘贴 复制后按右键 选择性粘贴中选中 转置 复制与粘贴区域不允许有重叠 4 同一个单元格中折行 换行输入 5 输入 00 和位数较多的数字 如身份证号 6 使用下拉列表快速输入 技巧 输完一行后 在编辑状态按Alt Enter键换行 方法 把单元格设置成 文本 格式技巧 先输入一个 符号 英文状态 再输入数字 方法 选择单元格区域 单击 数据 有效性 命令 从 数据有效性 对话框中选择 设置 选项卡 在 允许 下拉列表中选择 序列 在 来源 框中输入我们设置下拉列表所需的数据序列 中间用英文状态下的逗号 隔开 来源 中也可以选取本表内的某单元格区域 三 常用输入与编辑技巧 7 快速编辑单元格 8 快速输入大量重复特殊符号 技巧 利用箭头键选择要编辑的单元格 按下F2 编辑单元格内容 编辑完成后 按Enter键确认所做改动 或者按ESC键取消改动 技巧 先使用一个字母代替输入 注意不能是本表中需要用到的 全部输入完毕后 通过 编辑 替换 命令 把此字母全部替换成需要的特殊符号即可 三 常用输入与编辑技巧 四 工作表的操作与美化 1 表激活 删除 插入 重命名 2 标签颜色设置3 表移动与复制 方法 右键单击工作表标签 选择相应的选项 方法 右键单击工作表标签 选择 工作表标签颜色 设置颜色 方法 打开有关工作表 在需要移动的工作表标签上单击右键 选择 移动或复制工作表 在选项框中选择需要移动到的工作薄 选中就 建立副本 则是复制 否则是移动 技巧 按住Ctrl键 拖动工作表标签可快速复制工作表 4 隐藏 显示表 5 保护 部分保护表6 冻结表 方法 选择菜单 格式 工作表 隐藏 取消隐藏 方法 选择菜单 工具 保护 保护 取消保护工作表 允许用户编辑部分工作表技巧 选择需要编辑的单元格 设置单元格格式 在保护选项卡中把锁定选项去掉 然后再保护工作表 方法 选择需要冻结行的下一行 或右一列 或行列相交的单元格 选择菜单 窗口 冻结 取消冻结窗格 技巧 利用工具栏上的 冻结拆分窗口 按钮 四 工作表的操作与美化 7 行高 列宽设置 8 删除 插入行 列 单元格9 隐藏 显示行 列 方法 鼠标放于行号 列标分割线处直接拖拽 技巧 双击此分割线 自动设置合适行高 列宽 方法 选择一行 列或多行 列 右键点删除 插入 技巧 选择单元格 右键 选插入 删除 在选项卡中选行 列 如果选下移 左移等 则是添加 删除单元格 方法 选择需要操作的行 列 右键点隐藏 取消隐藏 技巧 选择单元格 按Ctrl 9 Ctrl 0 隐藏行 列按Ctrl Shift 9 Ctrl Shift 0 取消隐藏行 列 四 工作表的操作与美化 10 格式 字体 对齐 边框的设置 方法 通过设置单元格格式中的数字格式设置 技巧 通过格式工具栏上的按钮设置 主要按钮功能如下 四 工作表的操作与美化 11 合并及居中 12 自动换行 缩小字体填充13 格式刷 作用 将数据显示在几个连续的单元格中间 方法 点工具栏中的合并居中按钮 作用 把较长的文字分成若干行显示 或自动缩小字体 以在单元格中全部显示 方法 设置单元格格式 对齐 文本控制 自动换行 缩小字体填充 作用 格式刷可以利用一些设置好格式的单元格区域 快捷的设置出另外一些需要同样设置的区域 方法 选择设置好格式的单元格区域 点工具栏上的格式刷工具 选择需要设置格式的单元格 点击鼠标即可 四 工作表的操作与美化 14 添加超链接 15 条件格式16 分组及分级显示 作用 快速转到链接的地方 方法 选单元格 右键 超链接 本文档中的位置 然后设定需要链接到的位置 作用 让单元格格式按照内容变化 用以突出显示某些内容 方法 选单元格 点菜单栏 格式 条件格式 设定不同条件下显示的格式 可以添加设置3个条件 作用 把表中行 列按一定的组合折叠起来 方法 选择需要折叠在一起的行 列 点数据 组及分级显示 组合 组合中可以包含另一个组合 四 工作表的操作与美化 五 数据管理技巧 1 查找与替换 2 排序 筛选与汇总 方法 按Ctrl F查找 替换消除所有空格技巧 在替换选卡中查找内容为 空格 替换为中内容不要输入 直接按全部替换即可部分替换技巧 选择需要替换的区域 再全部替换 排序方法 选数据菜单 点排序 先选是否有标题行 再选关键字与排序方法 可使用三个关键字段排序 筛选方法 选中字段名字行 选数据菜单 点筛选 自动筛选 技巧 筛选的时候可以自定义筛选条件 也可以筛选前10大 小数据汇总方法 先排序 选中全部工作表 选数据菜单中的分类汇总注意选择分类和汇总的字段以及汇总方式 3 数据分列 4 批注数据5 数据同乘 除10000 快速改变数据单位 6 快速浏览计算结果 作用 可把一列数据按一定的规律分成若干列 方法 把需要分列的列置于最右边 选择此列 选数据菜单中的分列项 按需要设置分列方法 方法 选择单元格 右键菜单中点插入 删除批注 编辑批注内容 技巧 在一个空单元格输入10000 复制 选中需要同乘 除的单元格右键单击 选择性粘贴 乘 除 同理可操作加减 方法 选择单元格 观看状态栏 默认显示求和结果 技巧 右键单击状态栏 可以把输出结果改为计数 平均值等 五 数据管理技巧 六 公式与函数 Excel的单元格中可输入公式和函数 利用公式和函数对数据进行复杂的计算及输出 当链接的数据改变时 公式能自动进行重算 保证数据的准确性 运算符和运算规则算术运算符 关系运算符 运算规则 先计算括号内的算式先乘除后加减同级运算从左至右 技巧 让公式单元格处于编辑状态 可查看链接的源单元格 公式的复制和移动工作表中的公式可像普通数据一样复制选取含有要复制的公式所在的单元格按复制 粘贴 或拖曳填充柄即可复制 相对引用与绝对引用公式复制时 单元格的引用是相对引用即引用的源会随着公式所在单元格的变动而变动若需引用单元格的绝对地址 可在地址上加上 符号 如E7表示为 E 7 技巧 可以分为行绝对引用和列绝对引用 如 E7和E 7 技巧 利用F4键可快速添加和切换绝对引用的方式 六 公式与函数 常用函数SUM求和COUNT计数IF条件判断SUMIF COUNTIF条件求和 条件计数VLOOKUP自动匹配函数ROUND四舍五入函数LEFT MID RIGHT 文本截取 连接函数NOW TODAY时间函数VALUE数值函数 六 公式与函数 1 SUM函数结构 SUM 参数1 参数2 参数3 返回参数之和注意 参数如为错误值或不能转换数值的文本 则会导致错误 value 参数可以是单元格区域 但参数不能超过50个 2 COUNT函数结构 COUNT 参数1 参数2 参数3 返回参数中数字个数之和 也可状态栏临时计数 六 公式与函数 3 IF函数结构 IF 条件 条件成立时的值 不成立时的值 按条件返回其中一个参数值 例如公式 IF A1 B1 A1 B1 A1 条件为返回一个 TRUE 或 FALSE 值的逻辑型数据 IF函数可以嵌套使用 但最多只能嵌套7层 4 SUMIF COUNTIF函数结构 SUMIF COUNTIF 条件单元格区域 条件 求和 计数单元格区域 返回条件区域中条件成立时 求和 计数区域的和 个数 技巧 条件参数可以有运算符 求和 计数单元格区域可以省略 省略则计算条件单元格区域 六 公式与函数 5 VLOOKUP函数结构 VLOOKUP 查找的数值 查找的区域 输出区域中的指定列 FALSE 返回在查找区域中查找到指定数值时 对应行的指定列的数值 如果没有查找到 则返回错误值 N A 应用 利用此函数快速对账 但注意仅匹配到查找区域中第一个能匹配的数值 6 ROUND函数结构 ROUND 数值 四舍五入到的小数位数 返回四舍五入后的数值 技巧 FALSE 参数固定 可以用 0 来代替 技巧 第二个参数可以是0 或者负数 0代表四舍五入到整数 负数代表在小数点左侧四舍五入 六 公式与函数 7 LEFT RIGHT MID函数结构 LEFT RIGHT 文本 提取的字符个数 返回从指定文本中左边或右边截取指定个数的字符文本 结构 MID 文本 开始截取的位数 截取的个数 返回从文本中间位置截取的一段文本 8 连接符结构 参数1 参数2 返回两个参数连接后组成的新文本 六 公式与函数 9 NOW TODAY函数结构 NOW TODAY 注意 函数不需要参数NOW返回现在时点 含日期TODAY返回今天日期返回结果是动态时间 在表更新或重算时函数值自动更新 10 VALUE函数结构 VALUE 以文本格式储存的数值 返回以数值方式储存的数值 六 公式与函数 七 图表制作 图表分为图表区 图形区 图例 图表标题 分类X轴 数值Y轴 坐标轴刻度 网格线 数据标记 数据表等 图表制作方法 1 在图表向导中选择图表类型2 选择数据源3 在 图表选项 对话框中 可进行图表的标题 坐标轴 网格线 图例 数据标志 数据表的设置 技巧 增加月份数据时 在绘图区单击右键 选源数据 在源数据编辑框中直接修改数据区域即可 七 图表制作 八 常用设置 1 定制菜单 首先单击 工具 自定义 命令 打开其中的 命令 选项卡 在左侧的 类别 窗口中选择欲增删的菜单类别 如果是增加菜单命令 你只需在右侧的 命令 格内进行选择 将其拖至对应的菜单项 菜单自动打开并出现一黑线后 将其插入黑线指示的位置 在空白处单击鼠标左键即可 如果是删除菜单命令 只须打开菜单选中需要删除的命令 按下鼠标左键将它拖至图中的 命令 格中即可 2 自定义工具按钮 单击 工具 自定义 命令 打开 自定义 对话框使Excel处于自定义模式 这时你可以用鼠标右键单击工具栏上的按钮图标 弹出快捷菜单 利用这个快捷莱单 我们可以完成好多自定义工作 1 使用 命名 改变工具按钮的名称 2 使用 复制按钮图像 可以将按钮的图标复制到剪贴板中 然后插入到文本或表格中 或者粘贴到另一个按钮上 3 使用 编辑按钮图像 来调用按钮编辑器 3 打印设置 单击 文件 页面设置 命令 可以打开页面设置选项卡 进行打印页面的参数设置 单击 打印预览 页边距 可以进行打印页边距可视化操作 选择单元格区域 单击 文件 打印区域 设置打印区域 可以只打印所选区域 4 常规选项与安全设置 单击 工具 选项 命令 常规设置选项卡 对表进行常规设置 八 常用设置 九 高级应用及自动化 1 推荐学习的非常用函数 日期函数 DATE DAY MONTH YEAR时间函数 HOUR MINUTE SECOND文本长度函数 LEN取余 取整函数 MOD INT偏移函数 OFFSET取行号 列表函数 ROW COLUMN查找函数 SEARCH FIND查错函数 ISERROR求引用函数 INDIRECT数组函数 SUM SUMPRODUCT 2 推荐学习的非常用操作 自定义名称监视窗口画图表趋势线数据透视表手动重算竖排文本 3 神奇的F4 Excel中有一个快捷键的作用极其突出 那就是F4键 作为 重复 键 F4键可以重复前一次操作 在很多情况下起作用 比如在工作表内加入或删除一行 然后移动插入点并按下F4键以加入或删除另一行 根本不需要使用菜单 九 高级应用及自动化 工作中应当养成的好习惯 同一对象请使用同一名称 而不要使用简写 别称 以便使用函数查找 引用 在要使用公式的单元格 尽量不使用合并单元格功能 可使用跨列对齐来居中 以便填充公式 排序 筛选 在要进行乘除等运算时 要养成在公式的最外层使用round函数的习惯 以免出现四舍五入的误差 在计算结果可能出现 DIV 0 VALUE N A等错误 而其它单元格又需对此单元格进行运算时 要养成使用if和ISERROR函数消除错误值的习惯 当工作表要提交他人 多人 填写时 养成保护工作表 锁定单元格以及对特定单元格设定数据有效性的习惯 以避免他人的错误操作影响报表的汇总 养成使用状态栏进行临时性的求和 计数 求平均值的习惯 在工作表或工作簿中移动和滚动的按键 按下完成箭头键向上 下 左或右移动单元格CTRL 箭头键移动到当前数据区域的边缘CTRL HOME移动到工作表的开头CTRL END移动到工作表的最后一个单元格 右下角 ALT PAGEDOWN向右移动一屏ALT PAGEUP向左移动一屏CTRL PAGEDOWN移动到工作簿中下一个工作表CTRL PAGEUP移动到工作簿中前一个工作表CTRL F6或CTRL TAB移动到下一工作簿或窗口CTRL SHIFT F6或移动到前一工作簿或窗口CTRL SHIFT TABF5显示 定位 对话框SHIFT F5或CTRL F显示 查找 对话框SHIFT F4重复上一次 查找 操作 等同于 查找下一个 TAB在保护工作表中的非锁定单元格之间移动 快捷键应用 课后练习 用于输入数据的按键 请按如果要ALT ENTER在单元格中折行CTRL ENTER用当前输入项填充选定的单元格区域TAB完成单元格输入并在选定区域中右移CTRL DELETE删除插入点到行末的文本F4或CTRL Y重复最后一次操作F2 SHIFT F2 编辑单元格 编辑单元格批注 CTRL D CTRL R 向下填充 向右填充 CTRL F3 F3 定义名称 将定义的名称粘贴到公式中 CTRL SHIFT ENTER以数组公式形式输入公式CTRL A在公式中键入函数名后显示公式选项板CTRL K插入超级链接SHIFT F3将函数粘贴到公式中F9 CTRL ALT F9 计算所有打开 活动 工作簿中的所有工作表SHIFT F9计算活动工作表ALT 等号 插入 自动求和 公式CTRL SHIFT 双引号 将上方单元格中数值复制到当前单元格CTRL 左单引号 在显示单元格值和显示单元格公式间转换CTRL 撇号 将上方单元格中公式复制到当前单元格ALT 下箭头键显示 记忆式键入 列表 选择单元格 列或行的按键 1 按下完成CTRL SHIFT 星号 选定当前单元格周围的区域SHIFT 箭头键将选定区域扩展一个单元格宽度CTRL SHIFT 箭头键将选定区域扩展到与活动单元格同一行或同一列的最后一个非空白单元格SHIFT HOME将选定区域扩展到行首CTRL SHIFT HOME将选定区域扩展到工作表的开始CTRL SHIFT END将选定区域扩展到表的最后一个使用单元格CTRL SPACEBAR选定整列SHIFT SPACEBAR选定整行CTRL A选定整个工作表SHIFT BACKSPACE如果已经选定了多个单元格 则只选定其中的活动单元格SHIFT PAGEDOWN将选定区域向下扩展一屏SHIFT PAGEUP将选定区域向上扩展一屏CTRL SHIFT SPACEBAR在选定了一个对象的情况下 选定工作表上的所有对象CTRL 6在隐藏对象 显示对象与对象占位符之间切换CTRL 7显示或隐藏 常用 工具栏F8使用箭头键启动扩展选中区域的功能 选择单元格 列或行的按键 2 按下完成END打开或关闭END模式END SHIFT HOME将选定区域扩展到工作表上包含数据的最后一个单元格 右下角 END SHIFT ENTER将选定区域扩展到当前行中