小R刚毕业，每天都在公司忙到10点半回家。

作为室友的我，总是打趣地问他：“是不是又在公司蹭空调蹭网络，干嘛不早点回来陪我吃鸡！”

没想到小R低下头落寞地抱怨道：“我也想早点回来，可是我真的搞不定啊！”

原来，小R每天的工作标配就是统计数据。可同事们十几分钟就能轻松搞定的Excel表格，他经常要加几个小时班才能勉强完成。刚开始，他以为是自己不够熟练，多多练习就好了。没想到埋头苦练了半个月，他还是全组最慢的那个，每天被领导催到怀疑人生，甚至被质疑工作能力。

没有对比就没有伤害。看着旁边妹子敲几下键盘就能轻松搞定表格，小R开始惧怕做Excel和数据统计。

如今，几乎所有公司在招聘时都离不开这条要求：熟练使用Excel，精通数据分析者优先。

做方案、汇报工作、数据分析、总账录入、项目进度、工作记录、写代码、帮助记忆、思维导图.......Excel几乎能解决你工作中的所有问题，它不仅仅是某一职业的必备技能，而真实涵盖所有行业：

做行政，你要学会做考勤表；做财务，你要学会做财会报表；做销售，你要学会做销售业绩表；做运营，你要数据分析、工作汇报；做数据分析师，Excel玩得6也是最最最基本的条件.....

大数据是眼下非常时髦的热词，同时也催生出了一些与大数据处理相关的职业，数据分析师便是其中之一。由于专业技能和量化的数据分析为客户或所在公司控制决策分析、保证利益最大化，“数据分析师”一职备受各界青睐，甚至被视为21世纪的黄金职业。

虽然发展前景大好，但很多想要转行数据分析的小白心里多少都会有些疑惑：不会R、Python、SPSS、Tableau，是不是做不了数据分析师？

事实上，精通Excel，对于转行数据分析来说相当重要。因为Excel拥有最大的客户群体，基本上所有的企业、个人的电脑中装的都是office，这也就意味着90%以上使用电脑的人，都会用到Excel，即便如BAT这样的企业，在处理百万级、千万级数据的时候，也会优先使用Excel。

那么，在没有统计学、数学、计算编程等基础的情况下，如何只用Excel，成功转行成为数据分析师呢？

这里就不得不介绍三款用Excel就可以实现数据分析的插件：Power Query（数据查询）、Power Pivot（数据建模）、Power View（数据可视化）。

这三个模块组成了数据分析全过程，这个过程就好比烹饪过程：

第一个模块数据查询——Power Query。与数据源直接对接，就像获取新鲜的食材，对食材进行清洗、分类、整理，使其达到准备入锅的使用状态。这一步非常重要，因为如果食材不新鲜，那么再厉害的大厨也不可能做出一道健康的美味，所以好的数据源是成功的一半。

第二个模块是数据建模——Power Pivot。这一步是数据分析过程中最具有技术含量的核心部分。我们把数据组合起来实现不同维度的分析，就像把食物组合起来利用烹、炸、煎、炒等方式，再添加油、盐、酱、醋等调料，以烹制出想要的味道。

但是光有味道是不够的，最后我们要呈现给顾客的是一道色、香、味俱全的菜，是否能以视觉效果吸引顾客品尝你的美味，这就要看第三个模块——Power View（数据可视化）的功力了。

例如，如果以前你的做表是这样的 :

那现在通过Power View几秒钟就可以做成这样：

是不是相当神奇？！

无论你是像小R一样的职场小白想要提高工作效率实现逆袭，还是零基础转行成为高大上的数据分析师，精通Excel可以让你彻底告别被数据搞晕的日子，实现加薪不加班！

Excel这么重要，如何在短时间内熟练掌握呢？这有一套为你打造的Excel精品课程：容大教育《Excel速成班——数据处理与分析实战》在线课程，教你用Excel实现数据采集、数据转换和清洗、数据分析与建模等，关注容大教育IT培训机构百家号获取学习资料！

1 用于展示数据分析结果的表格

以下是同样的数据、不同的格式所展示出的数据可读性：

很明显，第二份表格更具有可读性，显得一目了然，而不是杂乱无章。

是怎么做到的呢？

1.1 行高设为“18”（字体“11”保持默认）；

1.2 数字字体设为Arial；

1.3 项目下的细项进行了缩排（缩进栏宽设为“1”）；

1.4 数字列调整为相同宽度，其他列视内容调整宽度。且在上、下、左、右都有空白；

1.5 表格框线：上下粗，其余细或虚，保留横线，去掉竖线，具不显示网格线；

1.6 文字靠左对齐，数字靠右对齐；

1.7 善用背景色凸显重点；

1.8 一列数据尽量不要包括复合的内容，如将“单位”放到单独的列。

2 内容按手动输入、引用、公式区分字体颜色

举例

字体颜色

手动输入的数字

50

黑色

计算公式的数字

=A1+A2

绿色

引用其它工作表的内容

=Sheet3!A1

蓝色

如：

3 用于进行数据分析的事务性数据

具体内容请见《Excel技巧 | 高效的数据分析需要良好的制表习惯》。

　　随着数据量的增大，数据统计分析的计算量和复杂性也随之剧增，所以需要借助各种统计分析软件来提高运算效率与分析准确性。

　　Excel也提供一组数据分析工具，包含常用的数据统计分析工具，能够满足基本的数据分析需求。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果，某些工具在生成输出表格时还能同时生成图表。

　　一、常用的函数

　　1、Vlooup()：它可以帮助你在表格中搜索并返回相应的值。让我们来看看下面Policy表和Customer表。在Policy表中，我们需要根据共同字段 “Customer id”将Customer表内City字段的信息匹配到Policy表中。这时，我们可以使用Vlookup()函数来执行这项任务。

　　2、CONCATINATE()：这个函数可以将两个或更多单元格的内容进行联接并存入到一个单元格中。例如：我们希望通过联接Host Name和Request path字段来创建一个新的URL字段。

　　3、LEN()-这个公式可以以数字的形式返回单元格内数据的长度，包括空格和特殊符号。

　　4、LOWER()， UPPER() and PROPER()—这三个函数用以改变单元格内容的小写、大写以及首字母大写(即每个单词的第一个字母)。

　　5、TRIM()：这是一个简单方便的函数，可以被用于清洗具有前缀或后缀的文本内容。通常，当你将数据库中的数据进行转储时，这些正在处理的文本数据将会保留字符串内部作为词与词之间分隔的空格。并且，如果你对这些内容不进行处理，后面的分析中将产生很多麻烦。

　　二、由数据得出结论

　　1. 数据透视表：每当你在处理公司的数据时，你需要从“北区分公司贡献的收入是多少?”或“客户购买产品A订单的平均价格是多少?”以及许多类似的其它问题中寻找答案。

创建数据透视表的方法： 第一步：点击数据列表内的任何区域，选择：插入—数据透视表。EXCEL将会自动选择包含数据的区域，包括标题名称。如果系统自动选择的区域不正确，则可人为的进行修改。建议将数据透视表创建到新的工作表，点击New Worksheet（新工作表），然后点击OK。

第二步：现在，你可以看到数据透视表的选项板了，包含了所有已选的字段。你要做的就是把他们放在选项板的过滤器中，就可以看到在左边生成相应的数据透视表。

从上图可以看到，我们将“Region”放入行，“Productid”放入列中，“Premium”放入值中。现在，数据透视表中展示了“Premium”按照不同区域、不同产品费用的汇总情况。你也可以选择计数、平均值、最小值、最大值以及其他的统计指标。

2.创建图表：在EXCEL里面创建一个图表，你只要选择相应的数据，然后按F11，就会自动生成系统默认的图表。除此之外，你可以手工改变不同的图表类型。如果你倾向于在当前工作表中生成图表，可以按ALT+F1，而不是F11。

当然，在任何一种情况下，只要你创建了图表，就可以通过定义特定数据源来展示期望的信息。

三、数据清洗

1.删除重复值：EXCEL有内置的功能，可以删除表中的重复值。它可以删除所选列中所含的重复值，也就是说，如果选择了两列，就会查找两列数据的相同组合，并删除。

如上图所示，可以看到A001 和 A002有重复的值，但是如果同时选定“ID”和“Name”列，将只会删除重复值（A002，2）。

按照下列步骤操作可以删除重复值：选择所需数据-转到数据面板-删除重复值

2.文本分列：假设你的数据存储在一列中，如下图所示：

如上如所示，我们可以看到A列中单元格内容被“；”所区分。我们需要将其进行分列，建议使用EXCEL的文本分列功能。按照下面的步骤可以实现分列：1.选择A1：A62.点击：数据—分列

上图中，有两个选项，“分隔符号”和“固定宽度”。我选择“分隔符号”是因为有分隔符“；”。如果我们希望按照宽度分列，例如：前四个字符为第一列，第五到第十个字符为第二列，则可以选择按固定宽度分列。3.点击下一步—点击“分号”，然后下一步，然后点击完成。

评语：EXCEL作为使用最广泛的数据统计分析软件，无论你是小白还是资深用户，总会有一些东西值得你去学习。

对于p元总体（X1,X2,...,Xn），其样本为

其中第i个样本为

样本的第j个分量的均值定义为

样本的第j个分量的方差定义为

样本的第j个分量与第k个分量的协方差定义为

为样本的相关矩阵（Pearson相关矩阵）。

对于多元数据，与二元数据相同，采用数据框的输入方式，可以用mean()函数、cov()函数和cor()函数计算样本的均值、协方差矩阵和相关矩阵。关于相关性检验，仍是使用cor.test()函数作两两分量的相关性检验。

例子：为测试某种橡胶的性能，现抽取10个样品，每个测量三项指标：硬度、变形和弹性。其数据如下所示，试计算样本均值、样本协方差和样本相关矩阵，并用Pearson相关性检验确认变量X1、X2、X3是否相关？

首先建立数据文件（文件名：rubber.txt），其格式为：

X1X2X3

1654527.6

2704530.7

3704831.8

4694632.6

5665031.0

6674631.3

7684737.0

8724333.6

9664733.1

10684834.2

>#读取数据

>rubber<-read.table("rubber.text")

>#计算均值

>apply(rubber,2,mean)

68.1046.5032.29

>#计算协方差矩阵

>cov(rubber)

X14.766667-1.94444441.9344444

X2-1.9444443.83333330.6166667

X31.9344440.61666676.1898889

>#计算相关矩阵

>cor(rubber)

X11.0000000-0.45488320.3561291

X2-0.45488321.00000000.1265962

X30.35612910.12659621.0000000

>#相关性检验

>cor.test(~X1+X2,data=rubber)

Pearson'sproduct-momentcorrelation

data:X1andX2

t=-1.4447,df=8,p-value=0.1865

alternativehypothesis:truecorrelationisnotequalto0

95percentconfidenceinterval:

-0.84305350.2448777

sampleestimates:

cor

-0.4548832

>cor.test(~X1+X3,data=rubber)

data:X1andX3

t=1.078,df=8,p-value=0.3125

-0.35254860.8052056

0.3561291

>cor.test(~X2+X3,data=rubber)

data:X2andX3

t=0.36097,df=8,p-value=0.7275

-0.54659850.7003952

0.1265962

计算结果可得出：X1、X2、X3两两均不相关。

基于相关系数的变量分类

现在以一个示例说明相关系数的应用——基于相关系数的变量分类。

例子：现有48位应聘者应聘某公司的某职位，公司为这些应聘者的15项指标打分，这15项指标分别是：求职信的形式（FL）、外貌（APP）、专业能力（AA）、讨人喜欢（LA）、自信心（SC）、洞察力（LC）、诚实（HON）、推销能力（SMS）、经验（EXP）、驾驶水平（DRV）、事业心（AMB）、理解能力（GSP）、潜在能力（POT）、交际能力（KJ）和适应性（SUIT）。每项分数是从0到10分，0分最低，10分最高。每位求职者的15项指标被记录在"employee.txt"文件中，公司计划录用6名最优秀的申请者，问公司将如何挑选这些应聘者。

解：通常的作法是：做15项指标的平均值

录用分数最高的6名应聘者。

"employee.txt"数据如下：

>#读取数据，计算平均得分，并按照大小顺序排列

>rt<-read.table("employee.txt")

>AVG<-apply(rt,1,mean)

>sort(AVG,decreasing=TRUE)

4039872322224

9.6000009.4666679.0000008.6000008.6000008.5333338.4666678.400000

91016344122017

8.1333337.6666677.6666677.4000007.4000007.2000007.2000007.000000

4662115112745

7.0000006.8666676.7333336.6666676.6666676.6666676.4000006.333333

133814437151826

6.2000006.1333336.0000005.9333335.8666675.6666675.5333335.533333

1925363132333043

5.4000005.1333335.1333335.0666675.0666674.6666674.5333334.266667

4134354228474829

3.6666673.4000003.1333332.8666672.7333332.6000002.4000002.000000

将上述语句的mean该外sum，即求应聘者的总得分，其选择结果是仙童的。当然，也可按加权平均值计算：

其中w1,w2,...,w15是权值，满足w1+w2+...+w15=1,wi(i=1,2,...,15),它表示第i项指标的重要性，这里需要确定每项指标的权重。

上述方法有它的缺点，因为有些指标是相关的，而有些指标不相关。实际上，相关类多的项占的权重大，而相关类少的项占的权重小，因此在作评分之前，应先作相关性分析。

>cor(rt)

FLAPPAALASC

FL1.000000000.23880570.0440408890.3063130370.092144656

APP0.238805731.00000000.1234192960.3796141510.430769427

AA0.044040890.12341931.0000000000.0015897660.001106763

LA0.306313040.37961420.0015897661.0000000000.302439887

SC0.092144660.43076940.0011067630.3024398871.000000000

LC0.228432050.37125890.0768244940.4827749280.807545017

HON-0.106749470.3536910-0.0302696010.6454085950.410090809

SMS0.270699190.48954900.0547274210.3616438800.799630538

EXP0.548379630.14092490.2655853520.1407234150.015125832

DRV0.345576330.34054930.0935220300.3931641480.704340067

AMB0.284644840.54963590.0440659810.3465550340.842122228

GSP0.338201960.50629870.1975045520.5028093050.721108973

POT0.367452920.50737690.2900321510.6055075540.671821239

KJ0.467206190.2840928-0.3233193520.6851557680.482455962

SUIT0.585918220.38420840.1400173680.3269574190.250283416

LCHONSMSEXPDRVAMB

FL0.22843205-0.1067494720.270699190.548379630.345576330.28464484

APP0.371258940.3536909690.489549020.140924910.340549270.54963595

AA0.07682449-0.0302696010.054727420.265585350.093522030.04406598

LA0.482774930.6454085950.361643880.140723420.393164150.34655503

SC0.807545020.4100908090.799630540.015125830.704340070.84212223

LC1.000000000.3558444640.818020800.147201970.697515180.75754208

HON0.355844461.0000000000.23990754-0.155938490.280184990.21460636

SMS0.818020800.2399075391.000000000.255417580.814734210.85952656

EXP0.14720197-0.1559384950.255417581.000000000.337228210.19548192

DRV0.697515180.2801849890.814734210.337228211.000000000.78032317

AMB0.757542080.2146063590.859526560.195481920.780323171.00000000

GSP0.882848650.3858217580.782123220.299268230.714073190.78387073

POT0.777316170.4156574470.753609830.348338780.788400240.76886954

KJ0.526835620.4482455220.563284190.214953160.612807670.54712558

SUIT0.416144670.0027556170.558035850.692636170.622554060.43476824

GSPPOTKJSUIT

FL0.33820200.36745290.46720620.585918216

APP0.50629870.50737690.28409280.384208365

AA0.19750460.2900322-0.32331940.140017368

LA0.50280930.60550760.68515580.326957419

SC0.72110900.67182120.48245600.250283416

LC0.88284860.77731620.52683560.416144671

HON0.38582180.41565740.44824550.002755617

SMS0.78212320.75360980.56328420.558035847

EXP0.29926820.34833880.21495320.692636173

DRV0.71407320.78840020.61280770.622554062

AMB0.78387070.76886950.54712560.434768242

GSP1.00000000.87583090.54940760.527816315

POT0.87583091.00000000.53939680.573873154

KJ0.54940760.53939681.00000000.395798842

SUIT0.52781630.57387320.39579881.000000000

>

下面将变量分组，分组的原则是：同一组中变量之间的相关系数尽可能的高，而不同组之间的相关系数尽可能的低。从相关系数最大的变量开始，LC（洞察力）与GSP（理解能力）的相关系数是0.882，GSP与POT（潜在能力）的相关系数是0.876，而LC与POT之间的相关系数是0.777，因此，这三个变量可以看成一组。SMS（推销能力）也应该包含在这组中，因为它与LC、GSP和POT的相关系数分别是0.818、0.782、0.754，AMB（事业心）也应在此组中，其相关系数分别是：0.758、0.860、0.784和0.769。进一步研究，发现变量DRV（驾驶水平）和SC（自信心）也在此组中，此组中各个变量的相关系数至少在0.672以上。

再选择第二组变量，按照同样的原理选择FL（求职信的形式）、EXP（经验）和SUIT（适应性），其相关系数分别是：0.548、0.586和0.693。

第三组先选择KJ（交际能力）、LA（讨人喜欢），相关系数是0.685,，再选择HON（诚实），它与LA的相关系数是0.645，但它与KJ的相关系数只有0.448。由于全部数据均来自“人”的打分，HON变量分在这组也可以认为是合理的。

再看看AA（专业能力）、APP（外貌）两个变量，AA变量与其他变量的相关系数没有超过0.5，而APP变量与其他变量的相关系数虽然刚刚超过0.5，但仍地狱其他组内的相关系数。

最后得到五个分组：

由于每一组的指标基本上代表了同一组的里，因此，我们先得到各组的得分，即

最后每位申请者的得分是：

AVG=（G1+G2+G3+G4+G5）/5

编写相应的R程序如下：

>attach(rt)

>rt$G1<-(SC+LC+SMS+DRV+AMB+GSP+POT)/7

>rt$G2<-(FL+EXP+SUIT)/3

>rt$G3<-(LA+HON+KJ)/3

>rt$G4<-AA

>rt$G5<-APP

>AVG<-apply(rt[,16:20],1,mean)

840397239222

9.0000008.9714298.9142868.6190488.3904768.2095248.0666678.057143

241646510201344

8.0380957.5714297.5333337.3142867.3047627.2190487.2095247.095238

17141262745321

7.0761907.0476197.0285717.0190486.9142866.9047626.7333336.695238

151142618253819

6.6190486.4952386.3904766.3523816.2190486.2000006.1523815.952381

371364143313032

5.9238105.8857145.6857145.3619055.3142865.0476195.0095244.647619

4733354248342829

4.5238104.4380954.2571434.2571434.1904764.0476193.2666672.514286

在分组情况下，前6名应聘者是：8、40、39、7、23和9号。

一、描述统计

在数据分析的时候，一般首先要对数据进行描述性统计分析（Descriptive Analysis），以发现其内在的规律，再选择进一步分析的方法。描述性统计分析要对调查总体所有变量的有关数据做统计性描述，主要包括数据的频数分析、数据的集中趋势分析、数据离散程度分析、数据的分布、以及一些基本的统计图形，常用的指标有均值、中位数、众数、方差、标准差等等。

数据的集中趋势一般采用平均值、中位数表示。数据的离散程度一般采用方差、标准差表示。数据的分布情况一般采用直方图表示。

案例：北京房屋价格（数据文件：house_price.xlsx）

分析问题:

1)北京市政府为调控房地产价格，希望知道北京各小区房屋价格的分布，请分析房地产价格的集中趋势，并选择合适的图形呈现。

2)房地产商想知道北京各个环线房屋装修状况的对比情况，以便进行产品设计和市场拓展，计算指标并设计合适的图形呈现结果，最后给房地产商一些建议。

3)选择合适的图形反映北京各个区住宅区房屋分布情况

操作步骤：

1)基本描述统计

打开excel数据文件house_price.xlsx

选择描述统计，单击“确定”按钮。

2)直方图

根据描述统计的结果，在空白列构造间隔为0.5的等差数列作为接收区域D1:D19，最大值为9,最小值为0。

选择数据，单击“数据”选项卡，选择“数据分析”选项框中的“直方图”选项

输入区域选择房屋价格avgprice列$B$2:$B$186，接收区域选择第一步构造的接收数据，即D1:D19数据。

输出区域选择G3，勾选图表输出，然后单击“确定”按钮。

选中整个直方图，右键单击选择“设置数据系列格式”，单击“系列选项”，分类间距设为0。

备注：

基本概念：数据的集中趋势 离散程度 数据分布情况 透视表 直方图 柱形图 饼形图 堆积柱形图

二、排位与百分比排位

“排位与百分比排位”分析工具可以产生一个数据表，在其中包含数据集中各个数值的顺序排位和百分比排位。该工具用来分析数据集中各数值间的相对位置关系。该工具使用工作表函数 RANK 和 PERCENTRANK。

例：10名同学统计学考试成绩如下：

试进行排位和百分比排位。

（1）在EXCEL数据分析工具库中选择“排位与百分比排位”，弹出对话框如下：

排位与百分比排位对话框设置

（2）单击“确定”生成排位结果如图。

排位与百分比排位结果

（3）其中的百分比排位为：小于该值的个数/（小于该值的个数+大于该值的个数）

如88，小于该值的有7个，大于该值的有2个，百分比排位为7/9＝77.78%，该工具截去了十分位数。

每个人都会有机会进行数据展示，为什么别人展示永远获得正视，而我的展示永远只有自己愿意去看，别人在看手机？那怎样做数据图表分析呢请看以下步骤：

如何对表格进行修饰，本次小编带来两个技巧，一是使用“套用表格格式”，和使用“条件格式”。二是带领大家学会养成修饰表格的思维。

第一步是对表格进行粗略的修饰调整，思维：行高、列宽、对齐方式、表格线等；

使用“套用表格格式”、“条件格式”之后看数据不再枯燥无味，而且还更有看头。“条件格式”可以将筛选条件转换为颜色可视化，从而达到一目了然的效果。

第一个技巧，①“套用表格格式”。方法：任一单元格→开始→套用表格格式。

②“条件格式”，方法：选中单元格区域→开始→条件格式。

条件1：高于平均值

条件2：数据条

条件3：色阶

第二个技巧：养成修饰图表的思维。这次举例柱形图的修饰例子，其他希望大家动用类似的方法进行模拟实践。

步骤一：根据销售数据建立柱状图，建立方法可参考。选择数据源→插入→柱状图→选择数据源→编辑坐标

步骤二：添加辅助线。选择数据源→→添加→点击柱体右键，设置数据系列格式→次坐标轴→选中柱体，右键更改图表类型→折线图。

希望回答对你能有所帮助，如果觉得不错就来点个赞或关注吧，感谢各位了！

1、一列数据同时除以10000

复制10000所在单元格，选取数据区域 - 选择粘性粘贴 - 除

2、同时冻结第1行和第1列

选取第一列和第一行交汇处的墙角位置B2，窗口 - 冻结窗格

3、快速把公式转换为值

选取公式区域 - 按右键向右拖一下再拖回来 - 选取只保留数值。

4、显示指定区域所有公式

查找 = 替换为“ =”（空格+=号） ，即可显示工作表中所有公式

5、同时编辑所有工作表

全选工作表，直接编辑，会更新到所有工作表。

6、删除重复值

选取数据区域 - 数据 - 删除重复值

7、显示重复值

选取数据区域 - 开始 - 条件格式 - 显示规则 - 重复值

8、把文本型数字转换成数值型

选取文本数字区域，打开左上角单元格的绿三角，选取 转换为数值

9、隐藏单元格内容

选取要隐藏的区域 - 设置单元格格式 - 数字 - 自定义 - 输入三个分号;;;

10、给excel文件添加密码

文件 - 信息 - 保护工作簿 - 用密码进行加密

11、给单元格区域添加密码

审阅 - 允许用户编辑区域 - 添加区域和设置密码

12、把多个单元格内容粘贴一个单元格

复制区域 - 打开剪贴板 - 选取某个单元格 - 在编辑栏中点击剪贴板中复制的内容

13、同时查看一个excel文件的两个工作表

视图 - 新建窗口 - 全部重排

14、输入分数

先后输入 0 ，再输入 空格， 再输入分数即可

15、强制换行

在文字后按alt+回车键即可换到下一行

16、删除空行

选取A列 - Ctrl+g打开定位窗口 - 定位条件：空值 - 整行删除

17、隔行插入空行

在数据表旁拖动复制1~N，然后再复制序号到下面，然后按序号列排序即可。

18、快速查找工作表

在进度条右键菜单中选取要找的工作表即可。

19、快速筛选

右键菜单中 - 筛选 - 按所选单元格值进行筛选

20、让PPT的图表随excel同步更新

复制excel中的图表 - 在PPT界面中 - 选择性粘贴 - 粘贴链接

21、隐藏公式

选取公式所在区域 - 设置单元格格式 - 保护：选取隐藏 - 保护工作表

22、行高按厘米设置

点右下角“页面布局”按钮，行高单位即可厘米

23、复制时保护行高列宽不变

整行选取复制，粘贴后选取“保持列宽。

24、输入以0开始的数字或超过15位的长数字

先输入单引号，然后再输入数字。或先设置格式为文本再输入。

25、全部显示超过11的长数字

选数区域 - 设置单元格格式 - 自定义 - 输入0

26、快速调整列宽

选取多列，双击边线即可自动调整适合的列宽

27、图表快速添加新系列

复制 - 粘贴，即可给图表添加新的系列

28、设置大于72磅的字体

excel里的最大字并不是72磅，而是409磅。你只需要输入数字即可。

29、设置标题行打印

页面设置 - 工作表 - 顶端标题行

30、不打印错误值

页面设置 - 工作表 - 错误值打印为：空

31、隐藏0值

文件 - 选项 - 高级 - 去掉“显在具有零值的单元格中显示零”

32、设置新建文件的字体和字号

文件 - 选项 - 常规 - 新建工作簿时....

33、快速查看函数帮助

在公式中点击下面显示的函数名称，即可打开该函数的帮助页面。

34、加快excel文件打开速度

如果文件公式过多，在关闭时设置为手动，打开时会更快。

35、按行排序

在排序界面，点击选项，选中按行排序

36、设置可以打印的背景图片

在页眉中插入图片即要

来源：网络整理

百家号-【袁帅数据分析运营】运营者：袁帅，会展业信息化、数字化领域专家。新社汇平台联合创始人，永洪数据科学研究院MVP。认证数据分析师、网络营销师、SEM搜索引擎营销师、SEO工程师、中国电子商务职业经理人。畅销书《互联网销售宝典》联合出品人。

相信大家对即将讲述的数据分析内容很感兴趣，想知道Excel数据分析包含哪些知识？本文就言简意赅地后面的系列文章会涉及到的一些内容，在这里进行一下简单的概括，大致分为八大部分分别如下：

第一部分引入数据挖掘的概念。简要介绍什么是数据挖掘，介绍Excel强大的数据挖掘功能，excel不支持的功能需要使用“加载宏”。

第二部分介绍简单的数据挖掘和问卷调查；介绍最基本的数据挖掘方法，即利用“平均数”这种最简单的数据统计模型，分析身边的数据或少量数据，介绍问卷调查这种收集数据的常用手段的设计技巧。通过预测商品预期价格。证明从少量样本中也能提取重要信息。

第三部融入案例预估二手车价格，介绍使信用回归分析进行预测和因子分析的知识，多重回归分析是预估数值和分析因子时非常有效的统计方法，是多变量分析中最常用的统计方法之一．本章以“拍卖行的二手车数据”为例对其进行解说。数据包含定性数据和定量数据，统称为“混合型数据”、经常出现在商务领域中。

第四部分内容涉及求最优化的问题“规划求解”。Excel支持“规划求解”这个强大的工具。本章介绍用“规划求解”求最优化问题的方法。经营管理中经常遇到如何利用有限的资源，实现营业额和利润最大化，以及费用和成本最小化的问题．用一次方程表示约束条件和目标叫做线性规划，求解方法叫做线性规划法。 “规划求解”不仅适合线性规划法，也适合非线性规划法。还支持整数规划法（这些统称为数理规划法或最优化规划法）．本章通过具体实例说明“规划求解”的使用方法。

第五部分一起来学习分析交叉表，介绍用交叉表判断属性（年龄、性别、职业等）是否有差异的方法。用Excel的函数功能求解；用大量实例详细说明。

第六部分会通过开发畅销产品的概念组合的案例介绍联合分析，消费者选择或决定购买商品时最重视什么？若能预知消费者重视的内容，就能开发山非常畅销的产品。“联合分析”是以“开发畅销产品的概念组合”为目的。为了把把握消费者和巾场的动向，被广泛运用在市场营销领域中的一种分析方法。计多企业都采用这种调查方法．联合分析也可以用Excel数据分析。

第七部分通过软件故障何时了的案例，来介绍用规划求解制作生长曲线，预估故障总数，生长曲线可以根据初始数据对商品的需求趋势，未来的人口数量和知识的掌握程度等的变化结果进行预测。本章用规划求解得出最优生长曲线。介绍预测软件停止发生故障的时间的方法。

最后一部分就也很有趣，是经典的求最优投资组合问题。近年来，对股票感兴趣的人越来越多．投资者最关心资产的运营安全。大部分人都希望：（ａ）收益越高越好（ｂ）风险越低越好．但是，鱼与熊掌难以兼得。投贤中最基础的理论是“高风险、高收益”，“低风险、低收益”。本章介绍能够兼得鱼与熊掌的“投资组合”方法。即将资产划分成几部分，使各部分之间的正负变动相抵消，尽量降低投资风险。

以上就是Excel数据分析包含哪些知识的一个概要介绍，当然内容远远不止这些，在接下来的系列精彩文章会运用大量实例介绍了许多数据挖掘的方法。小编希望通过普及这些方法，使所有人都能够将其灵活运用到自己的工作或研究中．这将是我们最大的愿望

这年头，不会点数据分析，都不好意思投简历找工作。

可是说起来容易做起来难，数据分析究竟是什么鬼？

不会HPCC也不会Hadoop，只会Excel可以吗？——答案是，可以！

对于非科班出身的人来说，Excel就是最接地气的数据分析工具。Excel中有自带的数据分析工具箱，只不过默认情况下，这个工具箱不在菜单栏中，要自己把它找出来哦。

在Excel工具箱中有多个数据分析，有相关系数、协方差、指数平滑等。看到这些名称先别晕，其实这些工具的使用方法很简单。不信？那么今天就来看看，如何通过相关系数分析，找出影响销量下降的原因。

第1步：在Excel表格中输入数据。例如你怀疑影响销量的原因可能有售价、客流量、促销折扣，但是不知道究竟哪个原因是罪魁祸首。

第2步：现在需要调出数据分析工具箱。如下图所示，单击【文件】菜单中 的【选项】选项。

第3步：在【加载项】菜单中选择【Excel加载项】，然后单击【转到】按钮。

第4步：选择【分析工具库】选项，单击【确定】按钮。此时就成功将分析工具添加到菜单栏中了。

第5步：单击【数据】选项卡下的【数据分析】按钮，这就是前面步骤中添加的数据分析工具库。

第6步：选择【相关系数】分析工具，单击【确定】按钮。

第7步：在【相关系数】对话框中设置数据区域。在【输入区域】中选择表格中的所有数据区域，分组方式为【逐列】，因为表格中，每一列为一组数据。选择【标志位于第一行】，这样分析结果中才会有标题字段。设置一个输出区域，例如选择G2单元格，表示要将输出结果放到G2单元格开头的单元格中。

第8步：此时就可以查看分析结果了。因为我们需要判断影响销量的因为，所以只需要在I列中，看销量与其他因素的相关系数值就可以了。负数表示负相关，正数表示正相关，正数正大和负数越小都表示相关系数很大。

例如在下图中，销量与促销折扣的相关程度是最大的，为-0.763，说明折扣是影响售价的最大因素。此外第二个因素是售价，为-0.3389。

虽然今天的案例比较简单，但是使用相关系数工具的思路却可以用到其他复杂的案例上。大家只要将各因素的数据列出来，使用相关系数分析，找到相关系数最大或最小的那个值就可以了。

相关系数

在金融投资中，投资组合经理常常利用具有负相关性的资产组合配置来分散风险。

申万宏源①通过计算28个行业的样本，得出纺织服装业和化工业的相关性最高，计算机行业和银行业的相关性最低，相关性越高。“同涨同跌”的概率越高，反之同理。

因此建议投资组合经理考虑构建银行和计算机的行业资产组合，来分散经济市场波动带来的风险。

有一份对零售业巨头沃尔玛的分析报告中指出，沃尔玛增加的分店数量与增长的净收入之间的相关性为正，但是非常低。这意味着虽然分店数量的增加导致了净收入的增长，但是增长的幅度非常小，比如增加了10家分店但是净收入仅上升了0.01个百分点。不禁使人思考这种现象是否是由于沃尔玛本身的战略和对不同地区市场的份额配比不均。例如, 2006年沃尔玛在中国地区开了 45 家分店，但这 45 家分店并没有成功形成规模效应； 甚至连中国地区的配送中心都寥寥无几，导致这 45 家新增的分店对沃尔玛全球净收入的贡献基本可以忽略。

尽早识别出不同商业因素的相关性，可以帮助企业提前对一些快速增长的新兴市场和要害地区进行战略布局。

在上面的例子中“相关性”反复出现，实际上相关性也是数据分析中常常用到的重要指标。在实际分析数据时，我们常常需要考虑如下问题：

1

如何查看不同维度之间是否具有相关性？

2

如何展现不同维度间相关性的强弱？

3

如何判断某些维度是否适用于所关注的问题？

在数据分析中，我们用相关系数来衡量两个维度之间的线性相关程度。

相关系数的公式：

取值范围：相关系数取值范围介于-1到1之间，-1代表两者完全线性负相关，1代表两者完全线性正相关。

Kyligence Enterprise 从v2.5.5版本开始支持设置相关系数的度量，通过预计算相关系数提升了数据分析的速度与企业决策效率，助力各个企业的新兴市场与关键战略布局。

小结案例

下面的例子计算了订单总价和营收账款的相关系数，按照商品折扣进行分组，数据来源于样例数据集 Star Schema Benchmark。

金融行业使用案例

案例背景

互联网金融企业常常借助用户画像来了解客户信息，定位目标客户。构建用户画像需要从海量的数据信息中，通过对用户不同维度的分析，精确地提取出有价值的用户数据范围。

对于互联网金融企业，用户收入决定了用户的消费能力，用户收入越高越有价值。为了构建用户画像，选取用户收入、年龄、学历、身高、体重等维度，考虑用户收入与其余各维度间的相关关系。

如果某一维度与用户收入呈正相关，则该维度数值越高，用户收入越高。反之呈负相关时，该维度数值越高，用户收入越低。若没有相关性，则该维度与用户收入无关，不建议用于构建用户画像。

案例实现

本段以用户收入与用户职业间的相关系数为例，从一个维度入手进行用户画像构建。为了定量计算用户收入和用户职业的相关系数，需要先将用户职业数值化。由于不同行业从业人员的平均受教育年限差别较为明显，所以选取平均受教育年限作为用户职业的数值代表。选取2015年全国20个行业的年平均工资和行业平均受教育年限进行数据分析②。

在 Kyligence Enterprise 中导入数据并按需求创建模型，在设置 Cube 时添加 CORR 函数。本例中 AVG_STUDY 代表用户平均受教育年限，AVG_SALARY 代表用户平均年工资。

在分析页面根据需求输入SQL语句：

select CORR(维度1，维度2) from 表名

在本例中相关系数高达0.8374，非常接近1，可以认为用户收入和用户职业呈高度线性正相关。因此在构建用户画像时，用户职业可以在很大程度上解释用户收入的高低，所以它是一个比较好的维度，用来标识不同收入人群的特征。

案例结论

类似的，可以计算出用户收入与不同维度之间的相关系数。

1.用户收入与年龄、受教育方向、工作地点等维度的相关关系都是高度线性相关。比如在其他条件相同的前提下，35岁左右的用户的平均收入高于30岁用户的平均收入等。

2.用户收入同用户的身高、体重、星座等维度的相关系数接近于0。也就是收入与用户身高、体重、星座关系非常弱，这些维度并不明显影响个人收入的多少。

3.高相关系数的指标对用户的消费能力影响较大，而低相关系数的指标则不具有较大的商业价值，不推荐作为指标加入用户画像构建。

结语

在数据分析中，CORR 函数可以定量展现不同维度的相关性，帮助各企业筛选出需要的维度。Kyligence Enterprise 帮助各企业进行基于海量数据的 CORR 函数计算，具有计算速度快、查询效率高、输出结果准确等特点。

之后还会针对更多统计函数推出实例介绍和使用展示，敬请期待。

①申万宏源：国内知名证券公司，其旗下研究所提出的行业分类被大部分国内其它的证券研究机构采用。

②数据来源于《中国劳动统计年鉴-2016》

Tips

【人工智能2018北京大会】 | Kyligence Robot 高效调优和诊断 Apache Kylin

【Workshop NO.2 | AWS专场】Kyligence on AWS 圆满落幕

Kyligence Cloud支持阿里云，加速云上大数据分析

手把手教你使用Kyligence Cloud，玩转云端大数据

Microsoft Azure 专场 | Kyligence 首期 Workshop 圆满结束

2018年第一期 | Apache Kylin 管理员认证培训圆满结束