excel使用技巧

ExcelExcel 使用技巧使用技巧 1 选择性粘贴 2 查找 替换 定位 3 插入行 列 4 设置单元格格式 5 隐藏工作表 设置工作表标签颜色 6 共享工作簿 7 自定义工具栏 8 工作簿选项设置 9 筛选数据 10 对数据进行分类汇总 1 常用快捷键 CtrL 显示当前日期 Ctrl Shift 显示当前时间 两命令间加一个空 格 可输入日期和时间 now 显示当前日期和时间 today 显示更 新的日期和时间 Ctrl Shift 复制粘贴一步到位 或 Ctrl Shift 拖动待移动的项 Ctrl Num 插入行或列 F2 将光标定位于单元格内 相当于双击的功能 F5 显示定位对话框 F11 显示图表区域 F12 另存为 Alt 回车 在同一单元格中实现分行填写 强制换行 Ctrl NUM 插入单元格 CTRL D 复制上面单元格的数据到下面的单元格 如果选定数据所在的 单元格及它以下的任意空白单元格 按 ctrl D 可将选定的数据复制到空白单元 格中 CTRL R 复制左边的数据到右边的单元格 CTRL SHIFT 下箭头或右箭头 从当前单元格选至当前区域最下面或最右边 CTRL 选定当前区域 CTRL 1 设置单元格格式 CTRL 9 隐藏所选单元格所在的行 若选中整列 则将隐藏全部单元 格 F3 执行上一次操作 F4 在相对引用 如 A1 和绝对引用 如 A 1 间进行转换 F5 打开 定位 对话框 相当于 Ctrl G F9 将公式直接转化为数值 1 1 选择性粘贴 选择性粘贴 操作 编辑 复制 编辑 选择性粘贴 或点右键 选 择性粘贴 复制就不多说了 快捷键为 Ctrl C 注意 不能对多重数据区域使用复制 剪切命令 1 粘贴公式 当复制公式时 单元格引用将根据所用引用类型而变化 如要使单元格引 用保证不变 请使用绝对引用 2 粘贴数值 将单元格中的公式转换成计算后的结果 并不覆盖原有的格式 仅粘贴来源数据的数值 不粘贴来源数据的格式 3 粘贴格式 复制格式到目标单元格 但不能粘贴单元格的有效性 链接 1 选择含有要复制格式的单元格或单元格区域 单击 常用 工具 栏上的 格式刷 也可复制单元格或区域的格式 格式刷适合复制比较复杂 的格式 而 F4 或 Ctrl Y 只适合重复单一的格式设置 4 粘贴时运算 可对粘贴对象进行加减乘除运算 如 A1 单元格数值为 2 复制 A1 单元 格 选定 B5 E7 区域 进行下列操作 选择性粘贴 运算 乘 则 B5 E7 单 元格的数据变成原来的 2 倍 其余运算类推 技巧 如果某单元格区域的数字已设成文本格式 无法对其进行加减 可 在某空白单元格输入数值 1 复制数值为 1 的单元格 再选定目标区域 选择 性粘贴 乘 即可将文本格式的数字转换为数字格式 另 用除 1 或加减零都 可转换 同理 使用 选择性粘贴 乘 的功能将单元格区域的数字转换正负 号 将以元为单位的报表转换为以千元或万元为单位是非常方便的 5 跳过空单元 防止用复制的空白单元格替换数据单元格 6 转置 复制区域的顶行数据将显示于粘贴区域的最左列 而复制区域的最左列将 显示于粘贴区域的顶行 很方便 7 有效性 粘贴单元格的有效性条件 技巧 全部 和 边框除外 选项也可复制数据有效性设置 2 2 查找 替换查找 替换 定位定位 操作 编辑 查找 替换 可查找 替换 某特定格式或某文本 可在整个工作簿内查找 默认为仅 在工作表内查找 可设定是否区分大小分 全角半角等 查找内容中可使用 等通配符 用法见下表 通配符类型用法 问号 任何单个字符 例如 sm th 查找 smith 和 smyth 星号 任何字符数 例如 east 查找 Northeast 和 Southeast 技巧 查找 或 符号本身 在 和 前加 即可使它们失去通配符的作用 操作 编辑 定位 什么是定位 定位就是 Excel 替你选定符合你所设定位条件的单元格 此 功能的快捷捷为 Ctrl G 打开定位对话框后单击 定位条件 按钮可选择包含 特殊字符或条目的对象 批注或单元格 这是一个很实用的功能 大家一般都 不太用 即使是经常使用 Excel 的朋友也不常用 下面介绍一下它的一些常用 选项 若要选择包含批注的单元格 请单击 批注 若要选择包含常量的单元格 请单击 常量 若要选择包含公式的单元格 请单击 公式 常量和公式选项又有数字 文本 逻辑值 错误等子选项可选 若要选择空白单元格 请单击 空值 若要仅选择区域中可见的单元格 虽然该区域也跨越隐藏的行和列 请单 击 可见单元格 若要选择当前区域 如整个列表 请单击 当前区域 注释 当前区域指填写了数据的区域 该区域包括当前选定的单元格或单 元格区域 该区域向四周扩展到第一个空行或空列 3 3 插入行 列插入行 列 操作 插入 行 列 这个功能就不介绍了 建议 在 工具 自定义 命令 选项卡里将 插入行 插 入列 删除行 删除列 的命令按钮拖动到工具栏 以方便插入或删 除行 列 技巧 插入多行 选定需要插入的新行之下相邻的若干行 选定的行数应与要插 入的行数相等 插入多列 选定需要插入的新列右侧相邻的若干列 选定的列数应与要插 入的列数相等 结合选定不连续的单元格 也可以一次性在不同的地方插入行或列 如选 定 A2 A4 以及 A11 A15 单元格区域 然后使用插入行命令可在原第 2 行前插 入 3 行 在原第 11 行数据前插入 5 行 插入工作表 操作 插入 工作表 此功能为插入空白工作表 也可在工作表标签点鼠标右键 点击 插入 然后选择 工作表 即可插入空白工作表 也可使用下面链接 1 的方法复制空 白工作表 链接 1 点击工作表标签 直接拖动工作表标签可移动工作表的顺序 按 住 Ctrl 拖动鼠标可复制工作表到本工作簿 插入新工作表的快捷键是 Alt Shift F1 如果你需插入 10 个表 不妨用快捷键 链接 2 如果工作簿里工作表较多 可以在工作表标签点鼠标右键 点 移动或复制工作表 使用此方法在较多工作表时移动很方便直观 如果点 移动或复制工作表 并将 建立副本 的选项勾上 选择要移动至工作簿 即可将工作表复制到其它工作簿 插入图表 操作 插入 图表 选定数据区域 点击图表向导或 插入 菜单 图表 根据图表 向导的提示 可完成简单的图表设置 插入符号 操作 插入 符号 使用此功能可插入一些键盘 软键盘上没有的符号 如 若要输入请按住 Alt 并在数字键盘键入此代码 0165 这是人民币符号哦 0128 另 万分号 的快捷键为 2031 Alt x 先在 word 文档内输入 2031 然后 按住 Alt x 输入的 2031 自动变为 或在 Excel 内 Word 内也行 按以下 操作 插入 菜单 符号 子菜单 在字体 Lucida Sans Unicode 子集 为 广义标点 中选取 技巧 如果你需要经常输入某个生僻的字符 你可以在 工具 菜单 自动更正选项 子菜单将某字符串设置为自动替换为该字符 这样可大大 提高效率 链接 如果要经常输入某字符串 如 逸凡账务系统是个 Excel 财务软件 既可采用自动更正的方法也可采用手工造词来达到快速输入的目的 此方法 在 office 其他软件里同样适用 插入分页符 操作 插入 分页符 在要插入水平或垂直分页符的位置下边或右边选中一行或一列 单击右键 再单击 插入分页符 链接 删除分页符 选定插入的分页符后的那行 列 点击 插入 菜单 此时的 分页符 已变成 删除分页符 点击即可删除分页符 插入函数 操作 插入 函数 函数的结构以等号 开始 后面紧跟函数名称和左括号 然后以逗号 分隔输入该函数的参数 最后是右括号 函数可以嵌套使用 但嵌套层数最多 为七级 在使用函数时必须注意函数中的引用类型 如果需要多行或多列地复制公 式 那么请正确地使用相对引用或混合引用 函数中可以使用预先定义的名称 或行列标志 具体用法请参见 Excel 的联机帮助 链接 在编辑公式时选择要更改的引用 按 F4 可切换引用类型 下面简单介绍一下本人认为必须掌握的函数 if and or sum sumif dsum countif vlookup round 双击打开下表参看具体实例 i if f函函数数 语语法法格格式式 if 检测条件 检测条件为值时返回的值 检测条件为假时返回的值 注注 检测条件可以是多条件 实例请参见and函数 姓名性别工资判断1判断2 职工A男800 00 低工资 职工B女3 600 00 高工资高工资 职工C男4 300 00 高工资 职工D男900 00 高工资低工资 职工E女2 900 00 注注 假设工资低于1000的为低工资 高于3000的为高工资 if函数是公式中的智能开关 也可以说是组合公式的指挥部 它对数值和公式进行条件检 测 执行真假值判断 根据逻辑计算的真假值 返回不同结果 插入名称 操作 插入 名称 定义名称有如下好处 减少输入的工作量 使公式更简洁 使人们更容易理解公式的含义 突破 Excel 对函数的嵌套层数仅为七层的限制 类似于使用辅助单元格 使用某 些特殊的函数 如 EVALUATE GET CELL 使用方法 插入 名称 再单击 定义 在 在当前工作簿中 的名称 框中 输入公式的名称 在 引用位置 编辑框中 键入 等号 和公式内容或常量数值 注意 名称的第一个字符必须是字母或下划线 名称中不能有空格 可以 用下划线和句号作单词分隔符 名称中不区分大小写 名称最多可以包含 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 列的和 工资1工资2 11001 SUM 管理人员 工资1 21002 SUM 管理人员 工资2 31003 41004 管理人员 链接 在多个工作表中命名单元格实际上就是使用了三维引用 三维引用 是指对跨越工作簿中两个或多个工作表的区域的引用 三维引用在对多工作表 的同一单元格或单元格区域进行计算时很实用 例如 需对 sheet1 到 sheet30 工作表的 B20 单元格求和 不必使用 sheet1 B20 sheet2 B20 sheet30 B20 公式 只需使用 sum sheet1 sheet30 B20 公式即可 插入超链接 操作 插入 超链接 在一个工作簿有很多工作表时 用这个功能做目录时很实用 超链接也可 链接到其他文件或网页 总之 您可创建指向新文件 现有文件或网页 网页 上的某个具体位置和 Office 文件中的某个具体位置的超链接 也可创建指向 电子邮件地址的超链接 还可指定在将鼠标指针停放在超链接上时显示的提示 比较简单 不详述 技巧 在插入了超链接的单元格单击一次为用于跟踪 单击并按住不放可 选定该单元格 链接 在单元格中还可使用 HYPERLINK 函数创建超链接 两者最终效果一 样 HYPERLINK 函数更灵活 但 插入超链接 的适用范围更广 4 4 设置单元格格式设置单元格格式 操作 格式 单元格 单元格格式可设置数字格式 对齐格式 字体 边框等格式 1 保护 选项卡 锁定 保护所选单元格以避免更改 移动 调整大小或删除 只有在 保护工作表时锁定单元格才有效 若要保护工作表 请指向 工具 菜单上的 保护 单击 保护工作表 再选中 内容 复选框 隐藏 隐藏单元格中的公式 以便在选中该单元格时在编辑栏中不显示公 式 如果选择该选项 这只有在保护工作表时隐藏公式才有效 若要保护工作 表 请指向 工具 菜单上的 保护 单击 保护工作表 再选中 保护 工作表及锁定的单元格内容 复选框 在 字体 选项卡可将字符设置成上标 下标 方法 选定上标或下标文 字 点右键 设置单元格格式 字体 选项卡 将 特殊效果 中的上 标或下标勾选上即可 上标 X2龙逸凡 下标 Q2龙逸凡 2 对齐 选项卡 可设置文本方向 单元格内文本的自动换行 将文本缩小字体填充至单元 格 注意 自动换行与缩小字体不能联用 技巧 如要使某表格的表头处于居中 请尽量使用 对齐 选项卡的 水 平对齐 中的 跨列居中 的对齐格式 而不要使用 合并居中 功能 因为 合并单元格无法使用部分功能 如下拉填充 使用格式刷等 链接 单元格内文本的强行换行用 Alt Enter 3 数字 选项卡 在该选项卡可将数字设置成各种格式 如会计专用格式 逸凡注 会计专 用格式可对一列数值进行货币符号与小数点对齐 此格式共综合了以下四种格式 千位分隔符 保留两位小数点 添加货币符号以及在数字与货币符号间用空格 填充 中文小写 大写数字格式 注意 所设置的格式仅为显示格式 并不影响数字的值 下面重点讲解一下数字格式的自定义功能 完整的格式代码由四个部分组成 这四部分顺序定义了格式中的正数 负 数 零和文本 格式代码各部分以分号分隔 完整格式如下图 它们如果只指定两个部分 则第一部分用于表示正数和零 第二部分用于 表示负数 如果只指定一个部分 则该部分可用于所有数字 如果要跳过某一 部分 则使用分号代替该部分即可 可以自定义格式代码来定义如何显示数字 日期或时间 货币 百分比或 科学计数以及文本或空格等 具体方法请参见 Excel 的联机帮助 创建或删除 自定义数字格式 特殊符号在自定义格式中的使用 在自定义格式中可以使用多种符号 它们的作用各不相同 下面分别介绍 一些常用符号的用法 在自定义格式中 下面的字符不必用引号括起来 短缺 0 00 或 0 00 剩余 0 00 短缺 分别输 入正负数你便可体会到两者的区别 技巧 2 数字格式中的智多星 自动设置满足指定条件的数字格式 在格式代码中加入带中括号的条件 条件由比较运算符和数值两部分组成 例如 下面的格式以红色字体显示小于和等于 100 的数字 而以蓝色字体显示 大于 100 的数字 红色 100 注 此功能有点类以于条件格式 但此条件仅限于由比较运算符和数值两 部分组成 此类运算符包括 等于 大于 大于等于 小 于等于和 不等于 而条件格式的适用范围更广 还可使用由函数组成的 公式组成的条件 格式范围也更多更广 技巧 3 自动显示固定宽度 如将格式自定义成 000000 则输入小于 6 位