数据分析与Excel
1. 数据分析
随着互联网+的不断深入,网络中的数据量飞速膨胀。身处信息的海洋,在这个数据高速爆发的时代,企业想要快速发展,不能只简单地靠历史的经验,想要快速成功,就要认清数据、企业、社会三者之间的联系,因此就出现了数据分析这个职业。尤其是在以数据驱动为首的百度、美团、以及京东等企业中,数据分析都扮演了重要的角色。现在的传统企业也慢慢意识到了数据驱动的重要性,大部分企业也都在学着用数据分析解决问题,或者提升业绩,这类公司在进行数字化转型。比如:银行企业的中国银联,交通银行的东方航空,通信行业的移动、联通、电信等都在通过数据进行探索。
1.1 什么是数据分析
数据分析是通过技术手段,对业务进行流程梳理、指标监控、问题诊断以及效果评估,它的目的是对过去发生的现象进行评估和分析,并在这个基础上对未来事物的发生和发展做出预期分析处理,以此指导未来的一些关键性决策。
随着数据量的不断增长,数据处理以及信息挖掘技术也在迅速发展,人们对于数据的处理也不仅仅是数据存储以及信息的简单探索,而是结合一些模型的应用进一步分析。虽然现在出现了大量数据分析技术,例如:Python、R等编程语言以及MySQL、Hadoop等数据存储技术,但是Excel凭借其操作简单、灵活以及宽广的覆盖面,在数据分析中占据着一席之地。
1.2 数据分析流程
数据分析流程可以概括为:数据理解、提取数据、数据清洗、数据分析、数据可视化、撰写报告。
2. Excel用法
Excel是Office办公软件中的组件之一。Excel专长于对表格中的数据进行计算和统计管理,通常用于财务或其他数据管理的表格制作。同时Excel还有很好的可视化能力,可用于制作各种行业报告。
2.1 Excel文件创建与保护
2.1.1 创建
工作簿:直接创建一个后缀名为’.xlsx’或’xls’的文件。
工作表:打开工作簿之后,下方切换的N个活动页,创建一个工作簿的时候就会自动创建一个工作表。
单元格:打开工作表之后可以看到的每一个方格。
2.1.2 保护的操作及功能
-
保护工作簿
操作步骤:审阅 ----> 保护工作簿
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z2MuYEAg-1661264226680)(E:\成都千峰培训\素材图片\保护工作簿.png)]
功能:防止其他用户对工作簿的结构进行更改,如移动、删除或者添加工作表。
-
保护工作表
操作步骤:指定工作表 ----> 保护工作表 或者 审阅 ----->保护工作表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GNfuxmtD-1661264226681)(E:\成都千峰培训\素材图片\QQ截图20220822180524.png)]
或者
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bvDyLwSu-1661264226681)(E:\成都千峰培训\素材图片\QQ截图20220822180616.png)]
功能:通过限制其他用户的编辑能力来防止他们进行不需要的更改。
-
保护单元格
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bg9nufy2-1661264226682)(E:\成都千峰培训\素材图片\保护单元格.png)]
操作步骤:选中单元格 -----> 开始 -----> 格式 -----> 锁定单元格然后执行保护工作表的操作
2.2 快速输入数据
2.2.1 填充柄的使用
操作步骤:选中单元格,鼠标放置到单元格右下角按住鼠标右键向下拖曳,选择对应选项。
复制单元格 | 同时将单元格的样式和内容复制给其他单元格。 |
---|---|
填充单元格 | 复制单元格样式,将单元格内容按照一定规则变化。 |
仅填充格式 | 只复制单元格样式。 |
不带格式填充 | 只将单元格内容按照一定规则变化。 |
以年填充 | 按照年份变化。(以月填充、以天填充同理)。 |
填充工作日 | 与以天数填充同理,仅仅去除每周周末。 |
2.2.2 快速填充
方法:从数据列后方相邻单元格输入内容,选中单元格,鼠标放置单元格右下角后按住右键向下拖曳,选择对应选项。例如:
邮编100089中国 | 100089 | 中国 |
---|---|---|
中国56个民族 | 56 | 个民族 |
一年12个月 | 12 | 个月 |
邮编100092中国 | 100092 | 中国 |
邮编100093中国 | 100093 | 中国 |
邮编100094中国 | 100094 | 中国 |
2.2.3 自定义列表填充
操作步骤:文件 ----> 选项 ----> 高级 -----> 编辑自定义列表 ---->保存 -------> 执行填充步骤
2.2.4 多个单元格填充数据
-
多个不连续单元格同数据
Ctrl + 鼠标左键选中单元格,填入数据,ctrl + Enter补全。
-
多个不连续单元格不同数据
选中下方数据,Ctrl + G ,选中空值,对控制单元格进行引用,Ctrl + Enter
步骤一:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5ih3KJ6L-1661264226682)(E:\成都千峰培训\素材图片\1.png)]
步骤二:
步骤三:
2.2.5 文本记忆输入
- Alt + 向下箭头
- 直接输入内容,根据提示选择回车
注意:必须连续单元输入才会有效。
2.3 数据验证
2.3.1 数据有效性
选中单元格 ------> 数据 ----> 数据验证 ----> 设置 ----> 允许:序列 —> 来源
操作步骤如下图:
步骤一:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qzlhkYlQ-1661264226683)(E:\成都千峰培训\素材图片\11.png)]
步骤二:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KJv8592l-1661264226684)(E:\成都千峰培训\素材图片\12.png)]
步骤二:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-34LwtShZ-1661264226686)(E:\成都千峰培训\素材图片\13.png)]
2.3.2 圈释无效数据
选中单元格 ----> 数据 -----> 数据验证 -----> 数据验证 ----> 设置 —> 确定 ----> 圈出无释数据
步骤一:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4EihTuRN-1661264226687)(E:\成都千峰培训\素材图片\12.png)]
步骤二:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qJlnHCl3-1661264226687)(E:\成都千峰培训\素材图片\15.png)]
步骤三:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VLH6AIZE-1661264226688)(E:\成都千峰培训\素材图片\16.png)]
2.4 定位条件
操作步骤:开始 ----> 查找和选择 -----> 定位条件(Ctrl + G 或者 F5)
如图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SSrTLOVz-1661264226688)(E:\成都千峰培训\素材图片\19.png)]
然后对选择的单元格填充值,按组合键Ctrl+Enter填充。
2.5 单元格的查找与替换
步骤一:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l2cR0p5q-1661264226689)(E:\成都千峰培训\素材图片\17.png)]
步骤二:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aCJ433MT-1661264226689)(E:\成都千峰培训\素材图片\18.png)]
2.6 快速选择数据
操作步骤:ctrl + shift + 方向键
优点:可以快速选择规模较大的区域数据。
缺点:如果起始行或者起始列出现在空值,会出现选择不全的问题,需要稍加注意。
2.7 选择性粘贴
操作步骤:
- 输入需要增加的比例;
- 将需要变化的数据提前复制一份;
- 复制比例,选中数据,选择正确的选项即可。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-arleFdXu-1661264226690)(E:\成都千峰培训\素材图片\20.png)]
注意:以上选项根据需求选择。
2.8 冻结视窗
操作:视图 --> 冻结窗格
注意:如果要冻结前N行,要选到第N+1行再冻结。冻结列同理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yNKLqLm4-1661264226690)(E:\成都千峰培训\素材图片\21.png)]
2.9 导入外部数据
Excel支持从外部导入数据,导入途径包含:本地各类存储数据的文件、数据库、网站等。
操作方式:数据 --> 获取数据 –> 选择对应功能选项
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xnMlYcsc-1661264226690)(E:\成都千峰培训\素材图片\22.png)]
2.10 Excel数据类型
Excel中存在5中数据类型,分别是:文本、数值、日期和时间、逻辑值、错误值。
文本:姓名、性别、住址以及一些不需要计算的数值,比如:手机号、银行卡号等。
数值:整数、小数、科学计数等需要计算的数字。
逻辑值:TRUE、FALSE。
错误值:错误值通常是在使用公式时,由于种种原因没能返回需要的结果,而是返回了一串不同字符组成的错误值代码。
常见错误值:
#VALUE! 键入公式的方式错误。或者引用的单元格错误。
#DIV/0! 这个错误值是说数字被0除了,因为0不能当被除数嘛。
#NAME? 在公式中是因为公式名称中出现拼写错误。
#N/A! 公式找不到引用的值。
#REF! 公式引用无效单元格时将显示 #REF! 错误。 当公式所引用的单元格被删除或被粘贴覆盖时 最常发生这种情况。
#NUM! 公式或函数中包含无效数值时。
###### 如果单元格所含的数字、日期或时间比单元格宽,或者单元格的日期时间公式产生了 一个负值,就会产生#####。
#NULL! 如果在公式中使用了不正确的区域运算符,或者在区域引用之间使用了交叉运算符(空 格字符)来指定不相交的两个区域的交集,将显示此错误。
如何查看Excel单元格数据类型:TYPE()
数据 | 类型 |
---|---|
100 | 1 |
abc | 2 |
110 | 2 |
TRUE | 4 |
#DIV/0! | 16 |
2.11 单元格格式
对整个单元格以及单元格中的数据应用格式:一种方式是,更改单元格中文本格式,可以将单元格中文本格式改为数值、货币、时间等Excel定义格式,也可以改为按照一定规则设定的自定义格式。
另一种方式是,将单元格想象成图片框,框中的图片就是数据。 设置单元格的格式可能包括添加边框、添加颜色或阴影,或更改单元格的大小和样式。
2.11.1 更改单元格文本格式
选中单元格 ----> 点击鼠标右键 ----> 设置单元格格式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iTrbHhET-1661264226691)(E:\成都千峰培训\素材图片\23.png)]
2.11.2 更改单元格样式
自定义格式语法:
# 数字占位符:四舍五入;不保留无意义的0
0 数字占位符:四舍五入;会保留无意义的0
? 数字占位符:四舍五入;会用空格来代替数据中无意义的0
, 千位分隔符:使用数字占位符的时候,使用逗号分割的位数
@ 文本占位符;在格式中代表文本数据表本身
* 重复*后面的符号,直到把单元个填满为止
[颜色1][条件1];[颜色2][条件2];[颜色3][条件3];[颜色4][条件4]
23 | # | |
---|---|---|
230 | # | 230 |
23.267 | #.## | 23.27 |
23.26 | # | 23 |
23.64 | # | 24 |
2.78291 | #.### | 2.783 |
2.79987 | #.### | 2.8 |
23 | 0 | 23 |
230 | 0 | 230 |
23.2637 | 0 | 23 |
23.6736 | 0 | 24 |
2.78921 | 0.000 | 2.783 |
2.79987 | 0.000 | 2.800 |
2.79987 | #.??? | 2.8 |
2.738383 | #.00"元" | 2.74元 |
李四 | @“-”@ | 李四-李四 |
---|---|---|
34 | 总共"#"个 | 总共34个 |
abc | @*a | abcaaaaaaaaa |
12345845 | #,## | 12,345,845 |
1235446 | “¥”#,##.00 | ¥1,235,446.00 |
26 | [>=18]“成年”;[<18]“未成年”;“成年” | 成年 |
23 | [<0]“负数”;[>0]“正数”;“零” | 正数 |
20 | [红色][<0]“-”#.00;[绿色][>0]#.000;[白色] | -23.00 |
45 | [红色][>60];[黑色] | 50 |
2.12 条件格式
1.条件格式设置:选中单元格 ----> 开始 -----> 条件格式
- 借助"数据条"直观分析数据
- 使用"色阶"创建热图
- 借助"图标集"为数据分类
- 条件格式的新建、删除、编辑。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BSOHF8th-1661264226691)(E:\成都千峰培训\素材图片\24.png)]
2.添加辅助列及拆分
1)添加辅助列
操作步骤:在最前面插入新的列,并且输入每行数据对应的编号,数据对应的编号是1、3、5、7、9…然后在数据的后面添加2、4、6、8…,最后按照序号的值排序。
2)拆分
数据 | 名字 | 姓氏 | 公司名称 |
---|---|---|---|
Nancy,Smith,Contoso Ltd. | Nancy | Smith | Contoso Ltd. |
Andy,North,Fabrikam Inc. | Andy | North | Fabrikam Inc. |
Jan,Kotas,Relecloud | Jan | Kotas | Relecloud |
Mariya,Jones,Contoso Ltd. | Mariya | Jones | Contoso Ltd. |
Steven,Thorpe,Relecloud | Steven | Thorpe | Relecloud |
Michael,Neipper,Fabrikam Inc. | Michael | Neipper | Fabrikam Inc. |
Robert,Zare,Relecloud | Robert | Zare | Relecloud |
Yvonne,McKay,Contoso Ltd. | Yvonne | McKay | Contoso Ltd. |
方法一:选择需要拆分的数据 ----> 数据 -----> 分列
| Contoso Ltd. |
| Steven,Thorpe,Relecloud | Steven | Thorpe | Relecloud |
| Michael,Neipper,Fabrikam Inc. | Michael | Neipper | Fabrikam Inc. |
| Robert,Zare,Relecloud | Robert | Zare | Relecloud |
| Yvonne,McKay,Contoso Ltd. | Yvonne | McKay | Contoso Ltd. |
方法一:选择需要拆分的数据 ----> 数据 -----> 分列
方法二:快速填充