|
|
知乎上有不少这样的问题：
- Excel经常卡死，都是数据统计，请问如何减负？
- 基础数据表3万多行，其他表要抓取该表数据，函数却无法运算？
- 5万多行的数据用Excel做地图经常卡死，除了换电脑还有什么好方法？
这些都很常见，随着数据积攒得越多，并且由于Excel本身和你电脑配置的限制，Excel处理和分析起大数据量来就捉襟见肘了，需要很好的工具配合减负或直接替代。
这里我给的方案是：
大数据量做报表或数据分析的方案
- 百兆以上，10W+行excel的数据量：数据库Access+SQL
- 数据若不是达到亿万级别，直接用BI工具分析
- 再大，就不是尔等表哥表姐，数据分析师能解决的了
鉴于大家日常都习惯用Excel，本文将主要讲第一类方案，用到的是微软家的另一款工具——Access。处理大数据量时将Excel和Access两者结合，利用Access快速查询的优势，将Access中的数据导出或复制到Excel，最重要的是它可以免费啊，而且上手不难！
在正文开始前，也分享下我整理的一份数据分析流程知识图谱，内含数据分析12个常见分析模型、18个理论分支、136个详细知识要点和60多个实际分析场景案例，不懂或不记得的知识点拿出地图就能查，数据人必备！高清电子版获取方式↓↓
一、关于ACCESS数据库
Access是一种关系型数据库，用于存放具有一定逻辑结构的数据，表与表之间存在关联性。但是利用Excel查询统计，除了要熟悉Access的操作，还要掌握SQL语言，它是调用数据/表的一种语言。
关于SQL，这里不多展开，可以去W3school花上两天学习下 SQL 教程 。
以下将以一个实际的分析项目为案例，力求让大家对Access有一个基本了解，从而找到分析大批量数据的思路和方法。
二、数据分析实操
下图是本文进行Access数据分析的原始数据源，原表格在excel里面有7W多行，反正我的x1c操作起来一顿一顿的。这里出于隐私对数据做了一点处理并截取了前十分之一。
基于以上数据，这里想用Access对原始表格进行：
- 细分客户销售利润分布权重分析
- 了解消费者单笔订单额的消费分布
- 分析各省市销售利润情况（数据地图）
分析一：细分客户销售利润分布权重分析
1.数据导入
首先先打开Access，并在表下面导入Excel数据表，这里我用的都是ofice
2013版。
之后便会得到类似Excel的展示，此表就是作为元数据表。
2.写SQL建立查询
之后我们开始第一个查询，统计出公司、消费者、小型企业这三个细分客户各自的销售额。
如上图，在“创建”选项卡里新建一个“查询设计”，然后取消显示表，右下角有一个“SQL”按钮，点击进入SQL对话框，输入如下语句：
select 细分,sum(销售额) as 总销售额 ,sum(利润) as 总利润, Round(总利润/总销售额*100,4) &"%" as 利润率
from 订单
group by 细分（SELECT是SQL中的查询函数，这段话的意思就是：从订单表中筛选出每个细分客户的销售额、利润额，并且汇总起来，并计算每个的利润率，利润率这里的一串公式表示数据按照百分位两位数处理）
然后，点击“设计”下的“运行”，得到如下结果：
最后复制到excel里处理美化一下。
这是一个比较简单的查询，在Excel表格中操作的话就是筛选汇总，但是如果你能很顺畅的写出那句SQL的话，运行起来就很是很快。
分析二：消费者单笔订单额的消费分布
为了让大家更好的理解，这里将此分析分成两步。首先我要汇总好每笔订单的销售额，然后按照1000的区间分成11类，并判断每一笔订单是在哪个区间，标记好，命名为“消费者订单明细”
于是，按照分析一的操作，同样新建一个查询，并写下如下SQL：
select 订单ID, sum(销售额) as 单笔订单消费,
switch(单笔订单消费<=1000,&#34;1~1000元&#34;,
单笔订单消费<=2000,&#34;1000~2000元&#34;,
单笔订单消费<=3000,&#34;2000~3000元&#34;,
单笔订单消费<=4000,&#34;3000~4000元&#34;,
单笔订单消费<=5000,&#34;4000~5000元&#34;,
单笔订单消费<=6000,&#34;5000~6000元&#34;,
单笔订单消费<=7000,&#34;6000~7000元&#34;,
单笔订单消费<=8000,&#34;7000~8000元&#34;,
单笔订单消费<=9000,&#34;8000~9000元&#34;,
单笔订单消费<=10000,&#34;9000~10000元&#34;,
单笔订单消费>10000,&#34;10000以上&#34;) as 消费区间
from 订单
where 细分=&#39;消费者&#39;
group by 订单ID;（这里用到一个函数Switch，它是计算一组表达式列表的值，然后返回与表达式列表中最先为 True 的表达式所相关的 Variant 数值或表达式）
之后我要统计每个区间产生了多少笔订单，各自占据多少比重，来判断消费者的消费水平如何。于是，有新建了一个查询，在“消费者订单明细”表的基础上，并命名为“消费区间分许”
select 消费区间，count(订单ID) as 订单数
from 消费者订单明细
group by 消费区间;
将上述数据贴到Excel里面做了个饼图（如下），是不是一目了然。
因为平均每笔订单的消费额在4417元（消费者订单明细表的数据贴到Excel里面求平均得到），可以发现0~4000元的订单占据77%，低消费者占绝大多数。
分析3：各省市销售额情况（数据地图）
各省市的销售额情况用Excel中的Power Map展示再合适不过了。
先将数据在Access里按照省市汇总。
select 城市,省,sum(销售额) as 总销售额
form 订单
group by 城市,省
数据贴到Excel里选中打开Power MAP生成数据地图。
关于数据地图可参照：李启方：怎么在 Excel 上做数据地图？
然后就有了如下效果：
总结
文章内容可能并不能直观体现Access处理数据之快。这么说吧，这份7w多行的数据在我电脑上打开花了将近1分钟，加个筛选，电脑风扇转的嗡嗡响中间还卡顿了2次。而放在Access里，花一分钟写了条查询SQL，5秒内出结果，而且也少了等待的糟心事儿。
所以如果SQL用的熟练，Access处理大数据量简直毫无压力。
公众号：数据分析不是个事儿
常年分享数据分析干货，不定期分享好用的职场技能工具。
近期，将在我的公众号里写一个“数据分析入门系列”，感兴趣的可以关注。 |
|
发表于 2022-12-11 09:02:11