Excel表格使用技巧大全

ExcelExcel 表格自动计算技巧表格自动计算技巧 1 点 点 D3 插入 名称 定义 插入 名称 定义 2 定义的名称可取 定义的名称可取 JX 公式栏里写 公式栏里写 EVALUATE C3 3 D3 中的公式为 中的公式为 ABS JX 一 显示单元格例有计算式的结果的设置方法 首先 插入 名称 定义在弹出的对话框 当前工作薄的名称 中输入 X 或 结果 的自定义名称 再在 引用位置 处粘贴 EVALUATE SUBSTITUT E SUBSTITUTE SUBSTITUTE SUBSTITUTE 计算 C 1 公式 注意要有 号 再对公式中 计算 C 1 选择上 然后 再到需要做公式的单元格中点击即可 如要相对引用 则要删除 字符 已经 OK 你在 C1 输入表达式 比如 15 5 3 在 D1 中输入 x 看看 应该是 30 二 如何在二 如何在 Excel 中输入计算式后另一单元显示计算结果中输入计算式后另一单元显示计算结果 菜单菜单 插入插入 名称名称 定义 输入定义 输入 AA 任意取名任意取名 在下面输入公式 在下面输入公式 EVALUATE A 1 然后在然后在 B1 单元格输入公式 单元格输入公式 AA 但本式不能识别如 但本式不能识别如 等符号进行计算 使用第一种较好 等符号进行计算 使用第一种较好 三 如何在 Excel 中编写自定义函数 象在表格中调用 SUM 一样 第一种情况 单元格 A1 2 单元格 B1 2 单元格 C1 2 单元格 D1 A1 B1 C1 显示结果为 8 那么如何才能在单元格 E1 中显示 2 2 2 的计算表达式 并且建立关联 当单元格 D1 变成 A1 BA C1 计算式后 显示结果为 16 那么 E1 也就 自动显示为 2 2 2 的计算表达式 也就是说随着单元格 D1 的计算公式变化 单元格 E1 显示的计算表达式也随之变化 2 第二种情况 是当计算的单元格任意变化时 怎么办 比如说计算式 E1 A1 B1 C1 D 1 也有 D2 A2 B2 C2 还有 F3 A1 B1 C1 D1 E1 时 如何将计算式变为 计算表达式 即计算式可以在任意一个单元格 计算公式所引用数据的单元格 可以任意变化 需要显示计算表达式的单元格也是任意的 针对第一种问题 分两步做 1 自定义一个函数 Public Function K x As Range K Right x Formula Len x Formula 1 End Function 自定义函数操作过程如下 工具 宏 Visual Basic 编辑器 右击 插入模块 再将上面的函数粘贴 进去即可 2 在 E1 中写入公式 SUBSTITUTE SUBSTITUTE SUBSTITUTE k D1 A ROW A1 A1 B ROW B1 B1 C ROW C1 C1 其它往下拖拉即可 I 当需要增加几个参数应用的单元格时 相应要增加几 个 SUBSTITUTE 再相应增加几个如 F ROW F1 F1 工具 宏 安全性 里面选择 无 不建议使用 这样加载宏的时 候系统不会每次都提示 自定义函数也可以换成使用宏表函数 GET CELL 第二个问题 请同理照推 四 编辑技巧 1 分数的输入 如果直接输入 1 5 系统会将其变为 1 月 5 日 解决办法是 先输入 0 然后输入空格 再输入分数 1 5 2 序列 001 的输入 3 如果直接输入 001 系统会自动判断 001 为数据 1 解决办法是 首先输 入 西文单引号 然后输入 001 3 日期的输入 如果要输入 4 月 5 日 直接输入 4 5 再敲回车就行了 如果要输入当前 日期 按一下 Ctrl 键 在 Excel 中填写表格时 怎样在一格中换行填字 输入一行后按 Alt Enter 就可以换行 5 自动切换输入法 在一张工作表中 往往是既有数据 又有文字 这样在输入时就需要来回 在中英文之间反复切换输入法 非常麻烦 如果你要输入的东西很有规律性 比如这一列全是单词 下一列全是汉语解释 你可以用以下方法实现自动切换 方法是 1 选中要输入英文的列 单击 数据 菜单 选择 有效性 命令 在弹 出的 数据有效性 对话框中 选中 输入法模式 选项卡 在 模式 框中选择 关闭 英文模式 命令 单击 确定 按钮 如图 12 2 选中要输入汉字的列 在 有效数据 对话框中 单击 IME 模式 选项 卡 在 模式 框中选择 打开 命令 单击 确定 按钮 这样 当光标在前一列时 可以输入英文 在下一列时 直接可以输入中文 从而实现了中英文输入方 式之间的自动切换 6 把数据彻底隐藏起来 工作表部分单元格中的内容不想让浏览者查阅 只好将它隐藏起来了 1 选中需要隐藏内容的单元格 区域 执行 格式 单元格 命令 打开 单元 格格式 对话框 在 数字 标签的 分类 下面选中 自定义 选项 然后在右边 类 型 下面的方框中输入 三个英文状态下的分号 2 再切换到 保护 标签下 选中其中的 隐藏 选项 按 确定 按钮退出 3 执行 工具 保护 保护工作表 命令 打开 保护工作表 对话框 设置好 密码后 确定 返回 经过这样的设置以后 上述单元格中的内容不再显示出来 就是使用 Exce l 的透明功能也不能让其现形 提示 在 保护 标签下 请不要清除 锁定 前面复选框中的 号 这样可 以防止别人删除你隐藏起来的数据 4 7 在 Excel 中自定义函数 Excel 函数虽然丰富 但并不能满足我们的所有需要 我们可以自定义一 个函数 来完成一些特定的运算 下面 我们就来自定义一个计算梯形面积的 函数 1 执行 工具 宏 Visual Basic 编辑器 菜单命令 或按 Alt F11 快捷键 打开 Visual Basic 编辑窗口 2 在窗口中 执行 插入 模块 菜单命令 插入一个新的模块 模块 1 3 在右边的 代码窗口 中输入以下代码 Function V a b h V h a b 2End Function 4 关闭窗口 自定义函数完成 以后可以像使用内置函数一样使用自定义函数 提示 用上面方法自定义的函数通常只能在相应的工作簿中使用 8 单元格内容的合并 解决办法是 在 C 行后插入一个空列 如果 D 列没有内容 就直接在 D 列操作 在 1 中输入 B1 C1 D1 列的内容就是 B C 两列的和了 选 中 D1 单元格 用鼠标指向单元格右下角的小方块 当光标变成 后 按 住鼠标拖动光标向下拖到要合并的结尾行处 就完成了 B 列和 C 列的合并 这 时先不要忙着把 B 列和 C 列删除 先要把 D 列的结果复制一下 再用 选择性 粘贴 命令 将数据粘贴到一个空列上 这时再删掉 B C D 列的数据 下面是一个 实际应用的例子 用 AutoCAD 绘图时 有人喜欢在 EXCEL 中存储坐标点 在绘制曲线时调用这些参数 存放数据格式为 x y 的形式 首先在 Excel 中输入坐标值 将 x 坐标值放入 A 列 y 坐标值放入到 B 列 然 后利用 将 A 列和 B 列合并成 C 列 在 C1 中输入 A1 B1 此时 C1 中的数据形式就符合要求了 再用鼠标向下拖动 C1 单元格 完成对 A 列和 B 列的所有内容的合并 如图 3 4 合并不同单元格的内容 还有一种方法是利用 CONCATENATE 函数 此 函数的作用是将若干文字串合并到一个字串中 具体操作为 CONCATENATE B1 C1 比如 假设在某一河流生态调查工作表中 B2 包含 物种 B3 包含 河鳟鱼 B7 包含总数 45 那么 输入 CONCATENATE 本次河流 生态调查结果 B2 B3 为 B7 条 公里 计算结果为 本次 河流生态调查结果 河鳟鱼物种为 45 条 公里 9 条件显示 5 我们知道 利用 If 函数 可以实现按照条件显示 一个常用的例子 就是 教师在统计学生成绩时 希望输入 60 以下的分数时 能显示为 不及格 输入 60 以上的分数时 显示为 及格 这样的效果 利用 IF 函数可以很方便地实现 假设成绩在 A2 单元格中 判断结果在 A3 单元格中 那么在 A3 单元格中 输入公式 if A2 60 不及格 及格 同时 在 IF 函数中还可以嵌套 I F 函数或其它函数 例如 如果输入 if A2 60 不及格 if A2 90 及格 优秀 就把成绩分成了三个等级 如果输入 if A2 60 差 if A2 70 中 if A20 SUM A1 A5 0 此式就 利用了嵌套函数 意思是 当 A1 至 A5 的和大于 0 时 返回这个值 如果小于 0 那么就返回 0 还有一点要提醒你注意 以上的符号均为半角 而且 IF 与 括号之间也不能有空格 创建条件格式 可以使用六种逻辑符号来设计一个条件格式 大于 大于等于 小于 小于等于 等于 不等于 如果你觉得这 些符号不好记 就干脆使用 或 号来表示 11 在 xcel 中小数点无法输入 按小数点 显示的却是逗号 无论怎样 设置选项都无济于事 该怎么办 这是一个比较特殊的问题 我曾为此花了十几个小时的时间 但说白了很 简单 在 indows 的控制面板中 点击 区域设置 图标 在弹出的 区域设 置属性 对话面板上在 区域设置 里选择 中文 中国 在 区域设置 属性 对话面板上在 数字 属性里把小数点改为 未改前是 按 确定 按钮结束 这样再打开 Excel 就一切都正常了 12 如何快速地将数字作为文本输入 在输入数字前加一个单引号 可以强制地将数字作为文本输入 13 如何对工作簿进行安全保护 如果你不想别人打开或修改你的工作簿 那么想法加个密码吧 打开工作 薄 选择 文件 菜单中的 另存为 命令 选取 选项 根据用户的需要 分别输入 打开文件口令 或 修改文件 D 令 按 确定 退出 工作簿 表 被保护之后 还可对工作表中某些单元格区域的重要数据进行保护 起到 双重保护的功能 此时你可以这样做 首先 选定需保护的单元格区域 选取 格式 菜单中的 单元格 命令 选取 保护 从对话框中选取 锁定 6 单由 确定 按钮退出 然后选取 工具 菜单中的 保护 命令 选取 保护工作表 根据提示两次输入口令后退出 注意 不要忘记你设置有 口令 14 工作表保护的口令忘记了怎么办 如果你想使用一个保护了的工作表 但口令又忘记了 有办法吗 有 选 定工作表 选择 编辑 复制 粘贴 将其拷贝到一个新的工作薄 中 注意 一定要是新工作簿 即可超越工作表保护 当然 提醒你最好不 用这种方法盗用他人的工作表 15 如何命名常数 有时 为常数指定一个名字可以节省在整个工作簿