Excel、数据录入与格式化

第二讲 Excel 数据录入与格式化 一 Excel 的规范与限制 参考资料 二 Excel 的数据输入类型与默认 我们输入数据是在 Excel 单元格中进行的 单元格是工作簿中 最小的组成单位 Excel 输入的数据类型大致有 4 类 1 文本 字符串 单元格内容 文本 的长度 每个单 元格可以最多 32 767 个字符 单元格中只能显示 1 024 个字 符 而编辑栏中可以显示全部 32 767 个字符 如果在数值型 数据前加单引号 可将数值型数据作为文本型数据处理 系统 默认文本左对齐 2 数字 值 系统默认右对齐 3 日期 是一种特殊的数字 Excel 中有 2 种日期形式 1900 年 1 月 1 日 如果使用 1904 年日期系统 则为 1904 年 1 月 1 日 系统默认使用 1900 年 1 月 1 日 对应数值为 1 即第一天 其后每日顺延对应数值 系统最晚日期为 9999 年 12 月 31 日 时间有年 月 日 时 分 秒 上午 下午等参数 可以输 入的最大时间是 9999 59 59 则 Excel 会以 1900 年为起点 365 天为单位 将其转换为日期 例如 序列数 3650 转换后的日期为 1909 年 12 月 28 日 输入 当前日期 Ctrl 分号 或输入 now 输入当前时间 则 Ctrl Shift 冒号 4 其他对象 如图 音频 视频等 三 数据的简单输入方法 1 文本输入 文本是指不以数字开头的字符串 可以是首 字字符为字母 汉字 非数字符号的多个字符组成的字符串 在默认情况下 一个单元格只显示 8 个字符 如果输入文本宽 度超过了 8 个字符 如其右边单元格有内容 则只显示其中的 前半部分 如其右边单元格无内容 则显示覆盖右侧单元格 对于产品序列号 邮政编码 身份证号码等不参与计算功 能的数字 应当以文本方式输入 输入方法 1 先设置单元格格式为 文本格式 2 在输入数据前加单引号 如输入 123 3 在输入时使用双引号 如输入 123 2 数值数据的输入 1 整数 正数的输入 123456 可以直接输入 也可以 输入 1 23456E5 2 小数的输入 在输入小数时 用户可以向平常一样使 用小数点 还可以利用逗号分隔千位 百万位等 当输入带有 逗号的数字时 在编辑栏并不显示出来 而只在单元格中显示 当你需要输入大量带有固定小数位的数字或带有固定位数的以 0 字符串结尾的数字时 可以采用下面的方法 选择 工具 选项 命令 打开 选项 对话框 单击 编辑 标签 选中 自动设置小数点 复选框 并在 位数 微调框中输入 或选择要显示在小数点右面的位数 如果要在输入比较大的数 字后自动添零 可指定一个负数值作为要添加的零的个数 比 如要在单元格中输入 88 后自动添加 3 个零 变成 88000 就在 位数 微调框中输入 3 相反 如果要在输入 88 后自动添加 3 位小数 变成 0 088 则要在 位数 微调框中输入 3 另外 在完成输入带有小数位或结尾零字 符串的数字后 应清除对 自动设置小数点 符选框的选定 以免影响后边的输入 如果只是要暂时取消在 自动设置小数 点 中设置的选项 可以在输入数据时自带小数点 0 00083 也可以输入 8 3E 4 3 负数的输入 在单元格中输入负数时 可在负数前输 入 作标识 也可将数字置在 括号内来标识 比如在单 元格中输入 88 按一下回车键 则会自动显示为 88 4 分数的输入 几乎在所有的文档中 分数格式通常用 一道斜杠来分界分子与分母 其格式为 分子 分母 在 Excel 中日期的输入方法也是用斜杠来区分年月日的 比如在 单元格中输入 1 2 按回车键则显示 1 月 2 日 为了避 免将输入的分数与日期混淆 我们在单元格中输入分数时 要 在分数前输入 0 零 以示区别 并且在 0 和分子之间 要有一个空格隔开 比如我们在输入 1 2 时 则应该输入 0 1 2 如果在单元格中输入 8 1 2 则在单元格中显示 8 1 2 而在编辑栏中显示 8 5 5 其他数值的输入 百分比 带货币符号的 科学计数的 等等 如 Excel 几乎支持所有的货币值 如人民币 英 镑 等 欧元出台以后 Excel2000 完全支持显示 输入 和打印欧元货币符号 用户可以很方便地在单元格中输入各种 货币值 Excel 会自动套用货币格式 在单元格中显示出来 如果用要输入人民币符号 可以按住 Alt 键 然后再数字小键 盘上按 0165 即可 3 日期数据输入 2009 年 10 月 3 日 可以输入 2009 10 3 或 2009 10 3 或 10 3 但是不可以输入 10 3 输入日期 Excel 是将日期和时间视为数字处理的 它能够识 别出大部分用普通表示方法输入的日期和时间格式 用户可以 用多种格式来输入一个日期 可以用斜杠 或者 来分 隔日期中的年 月 日部分 比如要输入 2001 年 12 月 1 日 可以在单元各种输入 2001 12 1 或者 2001 12 1 如 果要在单元格中插入当前日期 可以按键盘上的 Ctrl 组合 键 输入时间 在 Excel 中输入时间时 用户可以按 24 小时制输入 也可以按 12 小时制输入 这两种输入的表示方法是不同的 比 如要输入下午 2 时 30 分 38 秒 用 24 小时制输入格式为 2 30 38 而用 12 小时制输入时间格式为 2 30 38 p 注意字 母 p 和时间之间有一个空格 如果要在单元格中插入当前时 间 则按 Ctrl Shift 键 四 数据输入处理后 出现的错误值 经常用 Excel 的朋友可能都会遇到一些莫名奇妙的错误值 信息 N A VALUE DIV O 等等 出现这些错误的原 因有很多种 如果公式不能计算正确结果 Excel 将显示一个 错误值 例如 在需要数字的公式中使用文本 删除了被公式 引用的单元格 或者使用了宽度不足以显示结果的单元格 以 下是几种常见的错误及其解决方法 1 原因 如果单元格所含的数字 日期或时间比单元格宽 或者单元格的日期时间公式产生了一个负值 就会产生 错误 解决方法 如果单元格所含的数字 日期或时间比单元格 宽 可以通过拖动列表之间的宽度来修改列宽 如果使用的是 1900 年的日期系统 那么 Excel 中的日期和时间必须为正值 用较早的日期或者时间值减去较晚的日期或者时间值就会导致 错误 如果公式正确 也可以将单元格的格式改为非日 期和时间型来显示该值 2 VALUE 原因一 在需要数字或逻辑值时输入了文本 Excel 不能 将文本转换为正确的数据类型 解决方法 确认公式或函数所需的运算符或参数正确 并且公 式引用的单元格中包含有效的数值 例如 如果单元格 A1 包含 一个数字 单元格 A2 包含文本 学籍 则公式 A1 A2 将返回 错误值 VALUE 可以用 SUM 工作表函数将这两个值相加 SUM 函数忽略文本 SUM A1 A2 原因二 将单元格引用 公式或函数作为数组常量输入 解决方法 确认数组常量不是单元格引用 公式或函数 原因三 赋予需要单一数值的运算符或函数一个数值区域 解决方法 将数值区域改为单一数值 修改数值区域 使 其包含公式所在的数据行或列 3 DIV O 当公式被零除时 将会产生错误值 DIV O 原因一 在公式中 除数使用了指向空单元格或包含零值 单元格的单元格引用 在 Excel 中如果运算对象是空白单元格 Excel 将此空值当作零值 解决方法 修改单元格引用 或者在用作除数的单元格中 输入不为零的值 原因二 输入的公式中包含明显的除数零 例如 5 0 解决方法 将零改为非零值 4 NAME 在公式中使用了 Excel 不能识别的文本时将产生错误值 NAME 原因一 删除了公式中使用的名称 或者使用了不存在的 名称 解决方法 确认使用的名称确实存在 选择菜单 插入 蓝色 0 00 自定义数字格式 用户 在单元格中录入负数时 Excel 就会将数字以蓝色显示 三 在自定义数字格式中使用条件格式 在 Excel 自定义数字格式中用户可以进行条件格式的设置 当单元格中数字满足指定的条件时 Excel 可以自动将条件格 式应用于单元格 Excel 自定义数字格式中可以使用如下六种 标准的比较运算符 运算符 含义 等于 大于 大于等于 小于等于 不等于 例如 在学生成绩工作表中 当我们想以红色字体显示大于 等于 90 分的成绩 以蓝色字体显示小于 60 分的成绩时 其余 的成绩则以黑色字体显示 这时只需将自定义数字格式设置为 红色 90 蓝色 90 蓝色 90 蓝 色 90 G 通用格式 蓝色 60 G 通用格式 黑色 G 通用格式 你只需选择此格式即 可达到同样的使用效果 实例一 在工人奖金列表中 当我们想以小于 50 的显示 低 50 至 100 间显示 中等 大于 100 的显示 高 这时只需将 自定义数字格式设置为 100 高 中等 实例二 把 数学 语文 成绩中 90 分以上替换成 优 90 优 四 隐藏单元格中的数值 在 Excel 工作表中 有时为了表格的美观或者别的因素 我 们希望将单元格中的数值隐藏起来 这时我们使用 三 个分号 的自定义数字格式就可达到此目的 这样单元格中的值 只会在编辑栏出现 并且被隐藏单元格中的数值还不会被打印 出来 但是该单元格中的数值可以被其他单元格正常引用 在 Excel 中要想设置满足指定条件数字的格式 在自定义数字格 式代码中必须加入带中括号的条件 条件由比较运算符和数值 两部分组成 在 Excel 自定义数字格式的格式代码中 用户最多可以指定 四个节 每个节之间用分号进行分隔 这四个节顺序定义了格 式中的正数 负数 零和文本 如果用户在表达方式中只指定 两个节 则第一部分用于表示正数和零 第二部分用于表示负 数 如果用户在表达方式中只指定了一个节 那么所有数字都 会使用该格式 如果在表达方式中要跳过某一节 则对该节仅 使用分号即可 2 使用自动填充功能 3 使用数据有效性功能 4 使用数据记录单功能 5 使用公式输入数据 六 批量录入数据 1 文本文件导入 2 ACCESS 数据导入 3 dbf 文件导入 4 网页数据导入 七 特殊数据的输入 案例 在 Excel 中将人民币金额小写转成大写 Excel 中要将人民币小写金额转换成大写格式 将自定义格式 类型中的 G 通用格式 改为 G 通用格式 元 来实现 但在转 换小数时却出现了问题 比如 6 050 09 只能转换为 陆仟零 伍拾 零玖圆 那怎么解决这一先天不足呢 方法有以下两种 以下均对 B1 单元格转换 假设 B1 通过 函数 ROUND 四舍五入取得二位小数的小写金额数 其值为 6 050 09 一 在三个连续的同行单元格中转换 1 B2 中输入公式 IF B1 0 金额为负无效 INT B1 计算结果为 6 050 00 然后点击 格式 单 元格 数字 特殊 中文大写数字 确定 B2 显示 陆仟零伍 拾 再点击 格式 单元格 数字 自定义 将 类型 编辑框 中的 G 通用格式 修改为 人民币 G 通用格式 元 此时 B2 显示 人民币 陆仟零伍拾元 2 C2 中输入 IF B1 0 INT B1 10 INT B1 10 同样 C2 改为 G 通用格式 角 C2 显示 零角 3 D2 中输入 IF B1 0 INT B1 100 INT B1 10 10 同样 D2 改为 G 通用格式 分 D2 显示 玖分 这样在连续的三个同行单元格中实现了大写金额格式的转 换 如图 1 为了使转换符合财务格式 B2 应右对齐 C2 列 宽无间隔 且不设左右边框线 D2 要左对齐 图 1 显示大写金额 二 用 Excel 提供的函数转换 个位为 0 则不显示 不为 0 显示 XXX 元 不为 0 且角位和分位均为 0 显示 XXX 元整 元角分位均为 0 则 显示 零元整 小数点后面第一位为 0 且元位或分位有一为 0 则不显 示 为 0 且元位和分位均不为 0 则显示 零 不为 0 显示 X 角 小数点后面第二位为 0 则显示 整 不为 0 显示 X 分 根据以上思路在 B2 中输入公式 IF B1 0 金额为负无效 IF OR B1 0 B1 人民币 零元整 IF B1 1 人民币 TEXT INT B1 人民币 G 通 用格式 0 00 自定义数字格式的应用实例之四 分数的不同显示方式分数的不同显示方式 Excel 内置了一些分数的格式 另外 用户还可以使用自定义数 据格式得到更多分数的表示方法 比如在显示的时候加上 又 字 加上表示单位的符号 或者使用一个任意的数字作为分母 如表 2 6 所示为使用自定义数据格式设置的一些分数显示效果 及其代码 自定义数字格式的应用实例之五 隐藏某些类型的内容 利用自定义数据格式 用户还可以隐藏某些类型的输入内 容 或者把某些类型的输入内容用特定的内容来替换 如 表 2 7 是使用自定义数据格式隐藏数值的例子 自定义数字格式的应用实例之六 日期和时间型数据自定义格 式 Excel 虽然内置了很多时间和日期的格式设置 但是用户 还可以使用自定义数据格式得到更多时间和日期的表示方 法 如表 2 8 所示为使用自定义格式设置的时间和日期效 果 三 条件格式应用 1 设置基于数值的条件格式化 运行 格式 条件格式 命令 弹出 条件格式 对话框 中用默认的 单元格数值 选项 在其后关系框中选择 大 于或等于 等关系运算 在数字输入框中输入 85 等对应 数字即可 2 设置基于公式的条件格式化 运行 格式 条件格式 命令 弹出 条件格式 对话框 在 条件格