excel与access结合运用_当excel不够用时，如何利用Access进行数据分析？

知乎上有不少这样的问题：Excel经常卡死，都是数据统计，请问如何减负？

基础数据表3万多行，其他表要抓取该表数据，函数却无法运算？

5万多行的数据用Excel做地图经常卡死，除了换电脑还有什么好方法？

这些都很常见，随着数据积攒得越多，并且由于Excel本身和你电脑配置的限制，Excel处理和分析起大数据量来就捉襟见肘了，需要很好的工具配合减负或直接替代。

这里我给的方案是：

大数据量做报表或数据分析的方案百兆以上，10W+行excel的数据量：数据库Access+SQL

数据若不是达到亿万级别，直接用BI工具分析

再大，就不是尔等表哥表姐，数据分析师能解决的了

鉴于大家日常都习惯用Excel，本文将主要讲第一类方案，用到的是微软家的另一款工具——Access。处理大数据量时将Excel和Access两者结合，利用Access快速查询的优势，将Access中的数据导出或复制到Excel，最重要的是它可以免费啊，而且上手不难！

一、关于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,"1~1000元",

单笔订单消费<=2000,"1000~2000元",

单笔订单消费<=3000,"2000~3000元",

单笔订单消费<=4000,"3000~4000元",

单笔订单消费<=5000,"4000~5000元",

单笔订单消费<=6000,"5000~6000元",

单笔订单消费<=7000,"6000~7000元",

单笔订单消费<=8000,"7000~8000元",

单笔订单消费<=9000,"8000~9000元",

单笔订单消费<=10000,"9000~10000元",

单笔订单消费>10000,"10000以上") as 消费区间

from 订单

where 细分='消费者'

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生成数据地图。

然后就有了如下效果：

总结

文章内容可能并不能直观体现Access处理数据之快。这么说吧，这份7w多行的数据在我电脑上打开花了将近1分钟，加个筛选，电脑风扇转的嗡嗡响中间还卡顿了2次。而放在Access里，花一分钟写了条查询SQL，5秒内出结果，而且也少了等待的糟心事儿。

所以如果SQL用的熟练，Access处理大数据量简直毫无压力。