巧妇难为无米之炊 。数据分析师的米就是数据 。作为一名数据分析师,通常有自己常用的数据来源 。比如公司业务平台产生的数据,也可以是去从公开网站抓取的数据,甚至是纸质的或脑中记忆的数据 。无论原来的数据在哪里,如果需要用到Excel,我们都需要有一个数据输入到Excel中的环节(除非原始数据形式已经是Excel) 。
首先我们要知道Excel中有三类数据类型:分别是数值、文本、公式 。
Excel数字格式
以上数字格式除最后一项是文本外,其他对应数据类型均是数字 , 只是将数值以不同格式化方式显示,本质上仍是数值,包括日期和时间 。
为什么日期和时间也是数值?因为在Excel中日期是按整数来存储的,时间则是按小数 。比如在Excel输入数字1,然后在数字格式中应用“短日期”数字格式 , 你会发现,这一天是1990年1月1日 。你感兴趣的话 , 还可以输入0和-1试试,结果是不一样的哦 。Excel支持从1900年1月1日到9999年12月31日(序号为2958465)的日期,一般也够用了吧 。如果把12:00:00改为数值,则是小数0.5,对应一整天的一半 。这样,时间和日期的差值就跟普通的加减乘除是一样的了 , 用不着复杂的函数 。
生成日期戳或时间戳的快捷键:当前日期:Ctrl ;当前时间:Ctrl Shift ;
我们根据数据的来源形式将Excel数据输入分为三类:
- 录入
- 导入
- 抓取
数据录入
建议在录入之前 , 先想好大概有几行几列 。然后选中单元格,从第一个单元格开始录入 。Excel默认情况下 , 输入之后按Enter是往下单元格,按Tab是往右单元格,同时按住Shift则是往上或往左 。如果选中几行几列的单元格区域,按Tab则活动单元格在选择区域中按Z字形循环移动,按Enter则是活动单元格在选择区域中按N字形循环移动 。加速录入的方法:
- 输入连续序列——自动填充
- 输入相同值(数值、文本或公式)——快捷键
【Excel数据分析——数据输入】但要注意的是,如果是数字,需要连续输入两个数 , 再选中这两个单元格之后拖十字架才能生成序列 。这两个数可以如果不连续,则拖出来的数字是等差序列 。但如果只输入了一个数,则拖十字架也只有那一个数字 。
Excel中自带的序列有:
支持自定义序列 。
输入相同值(数值、文本或公式),除了使用拖十字架或双击十字架的方法外,还可以使用快捷键 。最常用的是Ctrl D(往下复制)和Ctrl R(往右复制) 。比复制粘贴起码少一个操作 。
数据导入
- 复制粘贴
- 从文件导入
- Excel文件
- 文本文件csv txt
- 导入xml文件
- 获取网站表格
- 获取文件夹文件列表
- 反向透视表
前面都是很常见的导入方式,主要讲最常见的问题:
1. 中文乱码问题
一般从系统或者网站下下载下来的文本文件是UTF-8的编码 , 直接用Excel打开中文会显示乱码 。这里有两个方法,一个通过notepad将文件编码转换为ANSI,然后再用Excel打开,另一个是,先用notepad或其他文本编辑器打开再复制粘贴到Excel中 。如果这两种方法都没能很好的分列的话 , 再使用数据-分列功能即可 。一个好消息是,Excel2019已经支持UTF-8格式,不需要转换一道了 。
2. 分列错误
csv文本文件是以逗号分隔,但如果列文本中本身包含逗号,则会将错误地将文本分隔为两列 。为避免这种问题 , 应当在csv文件本身入手 。csv在导出的时候选择文本用引号包围,或者在导出前把可能含有逗号的文本中的逗号替换为其他文本,或者将文本分隔符替换为一个不太常用的其他分隔符(比如“《”书名号),然后在Excel分列中选择分隔符也是“《” 。
3. 文本数值
两种方法 。当数据比较多的时候我会用第二种——原文本数值乘以1强制转换为数值 。
其他一些导入情况比较少见,这里也简单介绍一下 。
如果碰到xml文件 , 先不要惊慌地去找其他方法,Excel可以导入一些xml文件的,先试试直接打开行不行 。
获取网站表格
Excel路径:数据-获取外部数据-自网站
比如国家统计局
把链接输入进去,等加载完之后选中黄色箭头 , 点击“导入”,搞定 。
这个方法比直接复制粘贴好在 , 可以一次导入多个表格 。
获取网站数据
数据-获取和转换-新建查询-从其他源-从web
获取文件夹文件列表
数据-获取和转换-新建查询-从文件-从文件夹
逆向透视表
字面意思,就是把向透视表那样的二维表逆向转变为一维表 。
数据抓取
WEBSERVICE(url)和FILTERXML(xml, xpath)配合使用
WEBSERVICE(url)返回网页xml,然后用FILTERXML(xml, xpath)解析
这就是一个简单的函数版爬虫 。但这种方式只适合部分网页的少量信息爬取,如果要正儿八经搞爬虫 , 建议还是用Python 。
Excel数据输入是数据处理的第一步,就先讲到这里了 。
下一篇讲数据处理 。
你的点赞和收藏,是我坚持写下去的助力~谢谢