Excel 是微软推出的电子表格处理软件,核心作用是把数据录入计算统计可视化和自动化处理整合在同一个工作表环境当中,它不只是简单填格子的工具,完整体系包含单元格基础操作函数公式数据透视表图表数据验证条件格式宏与 VBA 数据导入导出协同办公等模块,掌握这套完整知识点之后,用户可以完成日常台账管理财务核算库存统计报表制作批量运算等各类数据工作,很多初学者误以为 Excel 只会求和,实际上它是一套结构化的数据处理系统,所有功能都围绕行列组成的表格结构展开,每一个单元格都可以存放文本数字日期公式,不同单元格之间可以建立引用关系,让数据联动更新,这也是 Excel 和普通纸质表格最本质的区别,之所以需要系统学习全部知识点,是因为单一功能只能解决零散小任务,组合运用之后才能处理大批量复杂业务,学习的时候建议按照基础操作函数进阶分析自动化的顺序循序渐进,先熟悉界面和单元格规则,再练习公式函数,之后学习透视表图表这类数据分析工具,最后接触高级自动化功能,学完之后就可以独立搭建完整的数据报表体系,大幅减少手工重复计算的工作量。
一 基础界面与核心对象
Excel 整个工作文件叫做工作簿,一个工作簿内部可以新建多个工作表,工作表就是我们看到的由行和列组成的网格,行用数字编号,列用字母编号,单元格就是行列交叉的位置,单元格地址就是列字母加行数字,比如 A1 就是 A 列第 1 行的单元格。
核心对象清单
1 工作簿 也就是保存后的 xlsx 或者 xls 文件,一个文件就是一个工作簿
2 工作表 工作簿里面的分页标签,可以重命名新增删除移动复制
3 单元格 最小的数据存储单位,支持文本数值日期布尔值公式
4 区域 多个连续或者不连续单元格组成的范围,比如 A1C10
5 名称框 编辑栏左侧位置,可以给单元格区域定义名称,方便公式调用
6 编辑栏 用来查看和修改当前单元格里面的内容和公式
基础操作要点
选中单元格可以单击,选中连续区域可以拖动鼠标,选中不连续区域按住 ctrl 点击,插入行列右键对应行号列号选择插入,删除行列同样右键操作,调整行高列宽可以拖动分隔线,也可以设置精确数值,冻结窗格可以固定表头,滚动数据的时候表头一直显示,这个是长表格非常实用的功能。
二 数据类型与单元格格式
Excel 里面的数据分为多种类型,类型不同计算规则不一样,很多计算出错都是因为数据类型不匹配。
表格说明
| 数据类型 | 特点 | 常见问题 |
|---|---|---|
| 数值型 | 可以直接加减乘除运算 | 数字变成文本格式之后无法求和 |
| 文本型 | 存储文字编号说明,不能直接参与算术运算 | 身份证号长数字变成科学计数 |
| 日期时间型 | 本质是序列数值,可以做日期差值计算 | 日期识别失败变成纯文本 |
| 逻辑值 | 只有 TRUE 和 FALSE,多用于判断函数 | 判断结果无法正常识别 |
单元格格式设置
数字格式可以设置百分比货币千分位小数位数,文本格式可以保留长编号,日期格式可以切换年月日显示样式,注意修改单元格格式只是改变显示外观,底层原始数据不会自动转换,如果是已经变成文本的数字,需要使用分列或者 VALUE 函数转换成真正数值。
三 公式与函数核心知识
公式必须以等号开头,这是 Excel 识别公式的标志,公式可以使用单元格引用,运算符包含算术运算符比较运算符文本连接运算符引用运算符。
引用类型是重点知识点
1 相对引用 比如 A1,公式向下向右填充的时候,引用单元格会跟着偏移
2 绝对引用 比如 $A$1,填充公式的时候引用位置固定不变
3 混合引用 比如 $A1 或者 A$1,锁定列或者锁定行,填充的时候只变化其中一部分
函数是内置封装好的计算公式,每个函数有函数名称参数,参数写在括号里面,多个参数用逗号分隔。
常用函数分类清单
基础统计 SUM 求和 AVERAGE 平均值 MAX 最大值 MIN 最小值 COUNT 计数 COUNTA 非空计数
逻辑判断 IF 嵌套判断 AND 并且 OR 或者 NOT 取反
查找匹配 VLOOKUP 纵向查找 HLOOKUP 横向查找 INDEX 加 MATCH 组合查找
文本处理 LEFT 从左侧截取 RIGHT 右侧截取 MID 中间截取 TRIM 清除多余空格 CONCAT 合并文本
日期函数 TODAY 当前日期 DATEDIF 计算两个日期间隔 YEAR 提取年份 MONTH 提取月份
数学运算 ROUND 四舍五入 INT 取整 MOD 求余数
公式错误值说明
VALUE 数据类型错误,文本参与算术运算
N/A 查找函数找不到匹配内容
DIV/0 除数为零
REF 引用的单元格已经被删除
NAME 函数名称写错或者名称不存在
NUM 数值参数无效
四 数据透视表
数据透视表是 Excel 里面最重要的数据分析工具,适合快速对大批量明细数据做分组汇总,不需要手写大量公式,它可以快速完成分类求和计数占比多维度交叉统计。
制作步骤
第一步 整理原始明细数据,第一行必须是表头,每一列是单一字段,不能有空表头,同一列数据类型统一
第二步 选中全部数据源,点击插入选项卡选择数据透视表,选择放置透视表的位置
第三步 在字段面板拖拽字段,行区域放置分类字段,列区域放置交叉分类字段,值区域放置需要汇总的字段,筛选区域放置全局筛选字段
第四步 修改值汇总方式,可以改成求和计数平均值最大值,还可以设置值显示方式,比如占总和百分比同比环比
进阶要点 可以刷新数据源,原始数据更新之后透视表一键刷新,支持切片器,切片器可以可视化筛选多个维度,还可以制作透视表图表,把透视表结果直接生成可视化图形。
五 图表功能
图表作用是把表格数字转换成图形,直观展示数据趋势对比占比,Excel 图表类型非常多,要根据数据选择合适图表。
常用图表类型清单
柱形图 适合不同类别数据对比
折线图 适合时间序列趋势变化
饼图 适合单一分类占比展示,分类数量不宜过多
条形图 适合长名称类别横向对比
散点图 适合分析两组数据相关性
组合图 同一图表同时展示柱形和折线,适合双指标量级差距很大的数据
图表制作步骤 选中数据源,插入选项卡选择对应图表类型,之后可以修改图表标题坐标轴标签数据系列颜色,添加数据标签,修改坐标轴刻度,还可以添加趋势线。
六 条件格式与数据验证
条件格式 按照设定规则自动改变单元格外观,比如大于某个数值标红,重复值高亮,数据条色阶图标集,适合快速识别异常数据,规则可以自定义公式,使用公式作为条件可以实现非常复杂的判断。
数据验证 也叫数据有效性,用来限制单元格输入内容,可以制作下拉选择列表,限制输入数值范围,设置输入提示和出错警告,防止录入错误数据,是规范台账录入的核心功能。
七 排序筛选分类汇总
排序 可以按照单列或者多列排序,支持升序降序,自定义序列排序,比如按照指定业务顺序排序,不是单纯字母数字顺序。
筛选 自动筛选可以给每一列表头添加下拉箭头,按照条件筛选显示符合条件的数据,隐藏不符合的数据,支持数字筛选文本筛选日期筛选,高级筛选可以设置复杂多条件,把筛选结果输出到其他区域。
分类汇总 必须先按照分类字段排序,之后才能使用分类汇总,会自动按分组插入汇总行,适合按类别统计小计,和透视表区别是分类汇总直接在原表生成汇总行,透视表是独立分析区域。
八 高级功能 VBA 与宏
宏就是录制或者编写的自动化操作脚本,VBA 是 Visual Basic for Applications 编程语言,用来编写宏,实现批量重复操作,比如批量合并工作表批量修改格式批量循环计算,普通用户录制宏可以不用写代码,高级用户可以编写自定义函数自定义窗体。
注意 带有宏的文件保存格式是 xlsm,普通 xlsx 默认不保存宏,打开宏文件的时候 Excel 会启用安全提示,默认禁用宏,需要手动启用,因为宏存在安全风险,只运行可信来源的宏代码。
九 数据导入导出与协同
Excel 支持从 txt csv access 数据库网页导入数据,也可以导出为 csv pdf 网页文件,Power Query 是进阶数据获取与转换工具,属于 Excel 高级知识点,用来清洗合并多个表格,合并多个工作表,拆分列逆透视,处理脏数据,是现在数据分析非常热门的模块。
协同功能 共享工作簿多人同时编辑,批注评论,修订追踪,可以记录修改人修改内容,保护工作表保护工作簿,设置密码锁定单元格,防止别人误改公式区域。
十 常见实战问题处理
1 公式填充不自动计算 检查计算选项是不是手动计算,改成自动重算
2 跨表引用报错 检查工作表名称是否有空格特殊字符,跨表引用格式是否正确
3 透视表刷新报错 检查数据源是否有空行空列,数据源范围是否固定,建议转换成超级表,数据源自动扩展
4 长数字变成科学计数 提前把单元格设置成文本格式,或者前面加单引号输入
【常见问题】
问题 1 Excel 里面 VLOOKUP 这个 Excel 核心函数为什么经常返回 N/A
回答 1 Excel 的 VLOOKUP 是纵向查找函数,返回 N/A 代表 Excel 没有在查找区域第一列找到匹配值,常见原因是两边数据存在多余空格,文本型数字和数值型数字不匹配,查找区域没有锁定绝对引用,查找列不在所选区域的第一列,解决办法可以先用 TRIM 清理空格,统一数据类型,查找区域加上美元符号锁定。
问题 2 Excel 数据透视表和 Excel 分类汇总两者有什么本质区别
回答 2 Excel 数据透视表属于独立多维分析工具,不需要提前排序,可以随时拖拽字段切换分析维度,原始明细数据不会改动,Excel 分类汇总必须提前对分类字段排序,汇总结果直接生成在原始表格当中,增加分级显示,切换维度操作没有透视表灵活,大批量多维度分析优先选择 Excel 数据透视表。
问题 3 Excel 公式当中相对引用和绝对引用在 Excel 填充公式的时候有什么差别
回答 3 Excel 相对引用在向下向右填充公式的时候,引用单元格地址会跟着公式位置自动偏移,Excel 绝对引用添加美元符号锁定行列,填充公式之后引用单元格保持固定不变,做批量查找固定基准单元格的时候必须使用 Excel 绝对引用,否则填充之后公式全部出错。
问题 4 Excel 的 Power Query 属于 Excel 哪个模块,主要用来做什么
回答 4 Excel 的 Power Query 属于 Excel 数据获取转换模块,也就是数据清洗工具,Excel Power Query 可以合并多个工作表多个外部数据源,拆分合并列,删除空行,修改数据类型,逆透视,处理完成之后加载到 Excel 表格,适合定期更新的大批量原始数据清洗工作。
【免责申明】
本文为 Excel 知识点教学介绍,内容基于通用版本 Excel 功能编写,不同版本 Excel 菜单名称部分功能位置会存在差异,文中操作方法仅供学习参考,实际业务使用请结合自身软件版本,本文不承担因操作失误造成的数据损坏丢失等相关责任。
本文来自作者[烟火知阅社]投稿,不代表启知新能源网立场,如若转载,请注明出处:https://www.qzdaily.com.cn/shbk/202611/6749.html
评论列表(4条)
我是启知新能源网的签约作者“烟火知阅社”!
希望本篇文章《Excel 的知识点介绍 深度全面讲解》能对你有所帮助!
本站[启知新能源网]内容主要涵盖:光伏,风电,储能,氢能,户用光伏,家用光伏,光伏投资,光伏新闻,光伏政策,太阳能,多晶硅,逆变器,太阳能电池,光热,组件
本文概览:Excel 是微软推出的电子表格处理软件,核心作用是把数据录入计算统计可视化和自动化处理整合在同一个工作表环境当中,它不只是简单填格子的工具,完整体系包含单元格基础操作函数公式数据透视表图表数据验证条件格式宏与 VBA 数据导入导出协同办公等模块,掌握这套完整知识点之后,用户可以完成日常台账管理财务核算库存统计报表制作批量运算等各类数据工作,很多初学者误以为 Excel 只会求和,实际上它是一套结构化的数据处理系统,所有功能都围绕行列组成的表格结构展开,每一个单元格都可以存放文本数字日期公式,不同单元格之间可以建立引用关系,让数据联动更新,这也是 Excel 和普通纸质表格最本质的区别,之所以需要系统学习全部知识点,是因为单一功能只能解决零散小任务,组合运用之后才能处理大批量复杂业务,学习的时候建议按照基础操作函数进阶分析自动化的顺序循序渐进,先熟悉界面和单元格规则,再练习公式函数,之后学习透视表图表这类数据分析工具,最后接触高级自动化功能,学完之后就可以独立搭建完整的数据报表体系,大幅减少手工重复计算的工作量。