Excel基础到公式运用(个人整理)

1 18 Excel 基本基本 加快启动速度 在桌面 Excel 图标上点右键属性 在目标路径后加一个空格再加上 e 加载项 文件 选项 加载项 管理 转到 切换工作表 Crtl PageUP PageDown 选中连续的工作表 单击第一个工作表 按住 Shift 键再按最后一张工作表 界面中列标显示为数字而不是英文则是使用了 R1C1 引用样式 要恢复为英文状态可点击 文件 选项 公式 取消勾选 R1C1 引用样式 修改网格线颜色 文件 选项 高级 网格线颜色 最大行 最大列 16384 最大单元格数个 Ctrl 右方向键可定位到单元格所在行向右连续非空值的最后一行 Ctrl 下方向键可定位到单元格所在列向下连续非空值的最后一行 Ctrl Home 键可到达表格定义的左上角的单元格 Ctrl End 键可以到达表格定义的右下角的单元格 在名称框中输入字母 数字 可快速定位到此单元格 Shift 空格键 快速选定单元格所在的行 Ctrl 空格键 快速选定单元格所在的列 避免与切换中文输入法快捷键冲突 行高单位是磅 1 磅约为 0 35278mm 最大限制为 409 磅 即 144 286mm 列宽的单位是字符 列宽设置数字范围为 0 255 最小列宽为 0 等于隐藏 列宽的宽度约等于该列单元格中可以显示容纳数字字符的个数 标准列宽为 8 38 Ctrl Backspace 快速定位活动单元格所在的位置并显示在当前窗口中 可用 Ctrl G F5 定位 Excel 中可以表示和存储的数字最大精确到 15 位有效数字 对于超出的会自动将 15 位以后的数字变为 0 需输入超过 15 位数字或需显示以 0 开头的值可在前面加单引号 或者将单元格设成文本格式 Ctrl 1 键盘左侧数字 1 对于一些很大或很小的数值 Excel 会自动以科学记数法来表示 例 1 2345E 14 表示 1 2345 1014 Excel 中日期系统默认为 1900 年日期系统 即以 1900 年 1 月 1 日作为序列值的基准日 运算时按 360 天一年计算 日期系统的序列值是一个整数数值 一天的数值单位就是 1 1 小时表示为 1 24 天 1 分钟表示为 1 24 60 天 逻辑值是比较特殊的一类参数 它只有 TRUE 真 和 FALSE 假 两种类型 Excel 中规定 数字 字母 逻辑值 比较大小 错误值 1 出现原因 单元格所含的数字 日期或时间比单元格宽 或日期时间公式产生了负值 2 VALUE 出现原因 1 在需要数字或逻辑值时输入了文本 Excel 不能将文本转换为正确的数据类型 2 将单元格引用 公式或函数作为数组常量输入 3 赋予需要单一数值的运算符或函数一个数值区域 3 DIV 0 出现原因 公式被零除时或公式中包含的除数零 4 NAME 出现原因 1 删除了公式中使用的名称 或者使用了不存在的名称 2 名称拼写错误 3 在公式中输入文本时没有使用双引号 4 在区域的引用中缺少冒号 5 N A 出现原因 函数或公式中没有可用数值 6 REF 出现原因 单元格引用无效 7 NUM 出现原因 公式或函数中某个数字有问题 由公式产生的数字太大或太小 Excel 不能表示 8 NULL 出现原因 为两个并不相交的区域指定交叉点 公式 通常以 开头 号开关也可使用 Excel 识别其内容为公式 Ctrl 可以在公式与值的显示方式间切换 输入数据 当用户输入数据时在工作窗口底部状态栏左侧会显示 输入 字样 原有编辑栏左边会出现 按钮 单击 表示 取消输入 单击 表示确定输入 单击 时和按 Enter 有相同的效果 但按 Enter 键后会自动跳到下一单元格 按 则不会 Excel 中默认按 Enter 键是向下激活单元格 也可设置成其它 方法 文件 选项 高级 编辑选项 方向 编辑单元格内容 双击或者按 F2 也可激活目标单元格后在工作窗口编辑栏编辑 可以在键盘上按 Insert 键切换 插入 或 改写 模式 日期输入 系统默认将 0 29 之间的数字识别为 2000 年 2029 年 将 30 99 之间的数字识别为 1930 年 1999 年 用分隔符 来输入日期不能进行运算 数据输入技巧 强制换行 Alt Enter 在多个单元格输入相同的数据 同时选中要输入的单元格 输入数据 在输入结束时按 Ctrl Enter 分数输入 整数 空格 分数 可在编辑栏查看分数值 上下标 选中要设置的上下标值 Ctrl 1 设置单元格格式 字体 在列表中选择 当要在目标单元格中输入此单元格上方的某一单元格内的内容时可按 Alt 下方向键或点鼠标右键选从下拉列 表中选择 自动填充 输入数据用填充柄下拉 顺序填充 可在填充完后点右下角的填充菜单进行类型选择 双击填充柄 按住 Ctrl 键下拉为复制填充 Ctrl D 向下填充 Ctrl R 向右填充 需框选出填充区域后使用 使用填充菜单 开始 编辑 填充 序列 2 18 粘贴 粘贴选项 复制 粘贴后在右下角出现的粘贴选项 选择性粘贴 复制后在粘贴前点击鼠标右键选择的选择性粘贴选项 查找 替换 Ctrl F Ctrl H 可设置格式 查找通配符应用 Excel 中支持两个通配符 和 例 要查找以 E 开头以 L 结束可用 E L 以 EX 开头 L 结束的五个字母单词可用 EX L 隐藏单元格数据 隐藏单元格数据 选中要隐藏的单元格 点击右键 设置单元格格式 数字 自定义 在类型处打上三个半角分号 到这一步虽 隐藏了单元格表面显示的内容但在编辑栏还是可以看到它本身的内容 再点右键 设置单元格格式 保护 隐 藏 审阅 保护工作表 加密 即可完全隐藏单元格的内容 禁止编辑表格中部分区域 禁止编辑表格中部分区域 单击行号和列标交叉处的按钮 全选整张工作表 Ctrl A Ctrl 1 保护 取消勾选锁定 OK 选中要 禁止编辑的区域 Ctrl 1 保护 先勾选隐藏再勾选锁定 审阅 保护工作表 加密 OK 批注中插入图片 批注中插入图片 审阅 新建批注 选中批注外边框 点击右键设置批注格式 颜色与线条 填充 填充效果 图片 打印批注 页面布局 页面设置 点右下角小箭头 工作表 打印 批注 公式 函数公式 函数 公式以 号以引号 必须且只能返回值 函数按特定算法计算的产生一个或一个组结果的预定义的特殊公式 公式组成 序号公式说明 1 15 3 20 2 包含常量运算的公式 2 A1 3 A2 2 包含单元格引用的公式 3 单价 数量包含名称的公式 4 SUM A1 3 A2 2 包含函数的公式 在输入公式的状态下 鼠标选中其他单元格区域时 被选区域将作为引用自动输入到公式里 运算符 符号说明例 算术运算符 负号 8 5 40 算术运算符 百分号 60 5 3 算术运算符 乘幂 3 2 9 16 1 2 4 和 算术运算符 乘和除 3 2 4 1 5 和 算术运算符 加和减 3 2 5 0 比较运算符 比较大小 等于 不等于 大于 小于 大于 等于 小于等于 A1 A2 判断 A1 与 A2 相等 B1 B2 判断 B1 不等于 B2 C1 5 判断 C1 大于等于 5 文本运算符 将文本字符或字符串进行连接合并 连接文本 爱 点点 返回结果 爱点点 区域运算符 冒号 SUM A1 C10 引用冒号两边所引用的单元格左上 角和右下角之间的所有单元格组成的矩形区域 空格交叉运算符 单个空格 SUM A1 B5 A4 D9 引用 A1 B5 与 A4 D9 的交叉区 域 公式相当于 SUM A4 B5 联合运算符 逗号 SUM A1 A1 A10 C1 C10 第 2 参数引用 A1 A10 和 C1 C10 两个不连续的单元格区域 运算符优先顺序 序号公式说明 0 小括号 优先级高于其他运算符 如果公式内有多组括号进行嵌套 计算顺序则由最内层括号逐 级向外进行运算 1 空格 冒号 单个空格 逗号 2 负号 3 百分比 4 乘幂 5 和 乘和除 注意区别数学中的 和 6 和 加和减 7 连接文本 8 比较两个值 注意区别数学中的 相对引用 当复制公式到其他单元格时 Excel 保持从属单元格与引用单元格的相对位置不变 例 A1 向下复制为 A2 A3 A4 绝对引用 当复制公式到其他单元格时 Excel 保持公式所引用的单元格绝对位置不变 例 A 1 向下复制始终 A 1 不随公式复制改变 3 18 混合引用 当复制公式到其他单元格时 Excel 仅保持所引用单元格的行或行方向之一的绝对位置不变 而另一方向位置发生变化 例 A5 向下复制为 A6 A7 A8 向右复制始终 A5 A 5 向右复制为 B 5 C 5 D 5 向下复制始终 A 5 混合引用实例 跨表引用 在公式中引用其他工作表的单元格区域 表示方式 工作表名 半角感叹号 引用区域 方法 在公式编辑状态下 通过鼠标单击相应的工作表标签 然后选取相应的单元格区域 例 要在工作表 Sheet1 中的 A1 单元格中引用工作表 Sheet2 中的 A1 B10 区域 则在 Sheet1 表中 A1 单元格输入 SUM 后单击 Sheet2 工作表标签 然后选取 A1 B10 区域 按回车结束 则公式将显示为 SUM Sheet2 A1 B10 引用连续多工作表相同区域 当跨表引用多个相邻的工作表中相同的单元格区域进行汇总时 可以使用三维引用进行计算而无须逐个工作表 对单元格区域进行引用 它的表示方式为 按工作表排列顺序 使用冒号将起始工作表和终止工作表名进行连 接 作为跨表引用的工作表表名 例 工作薄内有连续工作表 1 2 3 4 要在另一汇总表内计算前 4 张表里的同一区域 假设为 B5 E10 数值 在需执行公式的单元格内 输入 SUM 然后单击 1 工作表标签 按住 Shift 键后再单击 4 工作表标签 然后选取引用区域 按 Enter 结束输入 显示公式为 SUM 1 4 B5 E10 如果汇总表在表 2 与表 3 之间 则公式为 SUM 1 2 B5 E10 3 4 B5 E10 套用表格格式 开始 套用表格格式 方法 选择某一区域 开始 套用表格格式 勾选表包含标题 可在设计选项下进行其 他操作 例如勾选汇总行 会在表下方自动出现汇总 在下拉列表中有各项汇总项 函数函数 函数表示 号开头 函数名称 左括号 以半角逗号相间隔的参数 右括号 公式中允许多个函数或计算式 通过运算符连接 函数的参数可以由数值 日期 文本等元素组成 可以使用常量 数组 单元格引用或其他函数 当使用函数作为另一个函数的 参数时 称为嵌套函数 有的函数没有参数或者不需要参数 如 NOW 返回日期时间格式的当前日期和时间 NOW NOW TODAY 返回当前日期 TODAY TODAY RAND 返回大于或等于 0 且小于 1 的平均分布随机数 RAND RAND PI 圆周率 PI PI 等 4 18 Alt 插入求和函数 Shift F3 打开插入函数对话框 生成 26 个大写字母序列 CHAR 64 COLUMN 小写字母序列 CHAR 96 COLUMN 合并并换行 先点自动换行按钮 再输入公式 G7 CHAR 10 H7 10 是自动换行符的 ANSI 编码 分离姓名和数字 LEFT 从一个文本字符串的第一个字符开始返回指定个数的字符 LENB 返回文本中包含的字符数 LEN 返回文本字符串中的字符个数 RIGHT 从一个文本字符串的最后一个字符开始返回指定个数 的字符 LENB 函数按照每个双字节字符 汉字姓名 为 2 个长度计算 单字节字符按 1 个长度计算 因此 LENB B4 LEN B4 可以求得单元格中双字 节字符的个数 2 LEN B4 LENB B4 则可以求得单元格中单字节字符的个数 LEFT 左 RIGHT 右 计算职工人数 G 列数据中带有 人 字 要在黄色填充部分求人数的和可 以用 SUBSTITUTE 函数将 人 字删除后再求和 SUBSTITUTE 函数获得的结果为文本型数字 所以要使用减 负 运算将基转换为数值 以便 SUMPRODUCT 函数求 和 也可用公式 DAYS360 H66 TODAY 360 来计算年龄 5 18 IF MOD RIGHT LEFT B3 17 2 男 女 MOD 取余函数 返回两数相除的余数 MOD 34 9 结果为 7 余数结果符号与除数一致 被除数是除数的整数倍时结果为零 判断奇偶性 IF MOD 15 2 0 奇数 偶数 解释 如果 15 除以 2 的余数大于 0 则返回奇数 否则就返回偶数 IF 条件函数 判断是否满足某个条件 如果满足就返回这个值 如果不满足则返回另一个值 INT 将数值向下取最接近的整数 例 INT 15 35 结果为 15 TRUNC 将数字截为整数或保留指定位数的小数 例 TRUNC 8 72 结果为 8 TRUNC 9 72 结果为 9 ROUNDUP 无条件向上舍入数字 例 ROUNDUP 23 416 2 结果为 23 42 和四舍五入类似 ROUNDOWN 无条件向下舍入数字 例 ROUNDUP 23 416 2 结果为 23 41 直接舍去 可用 TRUNC 函数代替使用 CEILING 向上舍入 例 CEILING 123 456 0 2 结果为 123 6 FLOOR 向下舍去 例 FLOOR 123 456 0 2 结果为 123 4 利用取舍函数控制单元格录入数值 数据有效性 在公式处输入 C3 FLOOR C3 0 01 C3 为可变更单元格 ROUND 按指定的位数对数值进行四舍五入 例 ROUND 123 456 2 结果为 123 46 日期和时间函数 允许输入日期区间 1900 1 1 至 9999 12 31 当年份缺省时默认为当年份缺省时默认为 19001900 年 当月份缺省为年 当月份缺省为 0 0 时表示上一年的时表示上一年的 1212 月 当日期缺省为月 当日期缺省为 0 0 时表示上一个月的月末天数 时表示上一个月的月末天数 常用日期函数 函数名称功能作用 TODAY 和 NOW用于生成当前日期和时间 DATE 根据指定的年份 月份和日期数返回具体的日期值 YEAR MONTH 和 DAY用于从日期数据中提取年份 月份和日期值 YEARFRAC 用于计算两个日期序列相差的年数 结果以实数显示 EDATE 根据指定月份数返回指定日期之前或之后的日期 EOMONTH 从任意一个日期返回指定月份数的之前或之后的月末日期 WORKDAY 或 WORKDAY INTL 可以随意指定周工作日 根据源日期按指定工作日天数返回之前或之后的日期 NETWORKDAYS 或 NETWORKDAYS INTL 可以随意指定周工作日 计算两个日期差工作日天数的函数 6 18 基本日期函数 快速生成当前日期和时间 Ctrl 和 Ctrl Shift 这样生成的日期和时间格式是固定不变的 Today 和 Now 生成的日期会自动更新 DATE A2 B2 1 C2 类似都 OK 从特定日期提取年月日 年份 YEAR 单元格名称 月份 MONTH 单元格名称 日期 DAY 单元格名称 如 DATE YEAR TODAY 1 10 1 会返回明年的国庆节日期 日期之间的运算 或 1 计算相差天数 日期 日期 数值 2 计算之前的日期 日期 数值 日期 3 计算之后的日期 日期 数值 日期 TODAY 2014 年 6 月 29 日 判断一个日期所在的季度 判断一个日期所在的季度 7 18 公式利用了 DAY A2 返回的日期数是 A2 所在月份从第 1 日起 的第几天的来进行处理 当日期减去天数后得出当前月第 0 天 的日期 即为上个月月末日期 再通过 DAY 函数得出天数 可根据此公式算出当前日期的下一 个星期天的日期 工作日工作日函数 NETWORKDAYS 返回参数 start date 和 end date 之间完整 的工作日数值 工作日不包括周末和专门指定的假期 可以使用函数 NETWORKDAYS 根据某一特定时期内雇员的 工作天数 计算其应计的报酬 提示 若要使用参数来指明周末的日期和天数 从而计算两 个日期间的全部工作日数 请使用 NETWORKDAYS INTL 函数 时间的计算 Time 用于按指定数字生成具体的时间 hour 用于提取时间系列值中的小时数 Minute 用于提取时间系列值中的分钟数 second 用于提取时间系列值中的秒数 计算下班时间计算下班时间 例 上班时间为 2014 7 6 8 30 计算工作 8 小时后的时间 8 18 统计与求和统计与求和 9 18 汇总大于 1000 的点点的合计类似 汇总指定商品的销量汇总指定商品的销量 10 18 计算退休日期 计算工龄 年龄 小数位数 开始时期 结束日期 11 18 开始 条件格式 利用利用 VLOOKUPVLOOKUP 函数查询并汇总指定产品月销量和函数查询并汇总指定产品月销量和 1 选中区域创建数据表 表 1 2 输入内容 3 制作查询表 4 输入数组公式数组公式 公式在输入结束后 按按CtrlCtrl ShiftShift EnterEnter完成数组公式的输入 按Ctrl Shift Enter两边多了个大括号 随便输入条件自动查询 12 18 使用通配符 二维区域的条件求和 二维区域的条件求和 SUMI