商务数据分析与应用+商务数据分析与应用综合实训+数据分析工具之EXCEL+原创

主讲教师：王焱金华职业技术学院商务数据分析与应用综合实训数据分析工具之EXCEL分析工具之EXCEL（一）Excel软件简介

MicrosoftExcel是微软公司的办公软件Microsoftoffice的组件之一，是由Microsoft为Windows和AppleMacintosh操作系统的电脑而编写和运行的一款试算表软件。Excel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Excel发布了多个版本，常见的有Excel2007、2010、2013和2016版本，每次版本更新会增添了许多功能，使Excel功能更为强大。本篇以2016版本为例进行介绍。

Excel2007Excel2010分析工具之EXCEL（二）Excel软件数据分析操作1.数据导入点击导航栏“数据”-“新建查询”，可以从不同来源将数据导入Excel表格中，如图2-4所示。借用前面学习的SQL数据库（MySQL数据库）样表为例，输入服务器和数据库名称（如图2-5）。输入想要连接的数据库的用户名和密码（如图2-6）图2-4数据导入图2-5数据导入分析工具之EXCEL图2-6数据导入选择数据库中想要查询的表，点击加载（如图2-7）图2-7数据导入分析工具之EXCEL点击右键-“加载到…”，将已加载的数据加载到新表中（如图2-8）图2-8数据导入经过以上操作，MySQL数据库中examplecustomers表中的数据就导入到了excel表格中（如图2-9）。图2-9数据导入分析工具之EXCEL2.数据处理——使用Excel函数Excel中的函数其实是一些预定义的公式，用户可以直接用这些公式对指定区域的数据进行指定的运算。在计算机基础课程中，大家已经学习过一些常用的Excel函数，例如：SUM函数，对指定的区域进行求和运算；MAX函数，在指定区域内找到其中的数据最大值；AVERAGE函数，计算指定区域内数值的平均值。想要查看Excel中的函数，可以点击“公式”-“插入函数”。如果不知道函数名，可以“输入一条简短说明来描述你想要做什么”，Excel会推荐满足要求的函数，如图2-10。图2-10Excel的函数分析工具之EXCEL函数的表达形式一般是：函数名（参数1，参数2……），Excel已经定义好函数名不可改变，函数所需的参数写在括号里，多个参数间用西文逗号分隔，多个参数的顺序也是定义好的，在使用时要严格遵守，如果没有参数括号里可以是空。参数可以是数字、文本、逻辑值（TRUE或FALSE）、数组、单元格引用等，参数也可以是常量、公式或其它函数。函数在使用时除了确定函数名，用户还要给出函数应用的单元格区域、条件等参数才能使用函数得到计算结果。下面给大家介绍一些数据分析常用的Excel函数：（1）COUNTIF函数COUNTIF函数可以统计单元格区域中满足给定条件的单元格的个数，该函数的语法结构为：COUNTIF(rage,criteria)，其参数range表示需要统计其中满足条件的单元格数目的单元格区域；criteria表示指定的统计条件，其形式可以为数字、表达式、单元格引用或文本，如：COUNT(A2:A5,A3)意思为统计A2:A5这个范围内，A3特定内容出现的次数。分析工具之EXCEL来看一个具体例子，客户A-I来自不同的省份，现在想知道来自同一特定省份的客户有几个。通过公式：COUNTIF(B:B,B2)就可以统计出B2在B列出现的总次数，其中：B:B为范围，B2为指定内容。我们可以得出来自浙江省的客户有4人，来自江苏省的客户有3人，来自北京市的客户有2人（如图2-11）。通过这个函数我们统计了特定内容出现的总次数。另一张表，客户A-I来自多个不同的省份（如图2-12），如果我们想知道客户共来自哪几个省份该如何操作呢？通过公式：COUNTIF($B$2:B2,B2)就可以统计出B2在B列中第几次出现。其中：$B$2:B2为指定范围，$符号表示锁定，通过下拉的过程，该值不会随列数变化而变化，B2为特定内容。这样就可以知道B2在B列中第几次出现（步骤①）。然后在C列点击“筛选”，选择“1”（步骤②），则只有第一次出现的省份会显示（步骤③）。图2-11COUNTIF函数图2-12COUNTIF函数分析工具之EXCELCOUNTIF函数还经常用于查找不同表之间的重复数据，图2-13中有表一、表二两张表存有不同的客户姓名信息，现在要查找出表一中和表二重复的客户，如何操作呢？在B2中录入公式“=COUNTIF(表二!A:A,A2)”，意思是在表二A列中查找与表一A2单元格“李一”相同的个数。显示结果为0，表示表二中没有“李一”这一客户。同样利用筛选功能，我们可以查到两表中重复的客户（如图2-14）。图2-14COUNTIF函数在数据分析时，经常需要删除重复数据，就要用COUNTIF函数先把重复数据筛选出来在进行Delete删除。表一表二图2-13COUNTIF函数分析工具之EXCEL（2）VLOOKUP函数VLOOKUP函数是Excel中的一个纵向查找函数，功能是按列查找，最终返回该列所需查询列序所对应的值，与之对应的HLOOKUP是按行查找的。该函数的语法结构为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)，其中参数说明如右表2-9所示：

以速卖通后台“选品专家”数据为例，可下载不同国家近30天的热销产品数据，并进行国家差异化分析，在分析数据之前，我们可利用Vlookup函数进行数据处理。参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域col_index_num返回数据在查找区域的第几列数正整数range_lookup模糊匹配/精确匹配TRUE（或不填）/FALSE表2-9VLOOKUP函数参数说明分析工具之EXCEL①扫描二维码下载数据（如图2-15），该数据速卖通卖家可在后台“数据纵横”-“选品专家”-“热销hotsale”板块中下载并查看。这里，以速卖通流量最大的俄罗斯、美国、巴西三国数据为例。②新建一个excel文件，设计表内容，复制商品关键词。我们希望在一个表中查看三个不同国家对于某一商品关键词的成交指数、转化率排名和竞争指数数据，以便于对比分析，如图2-16所示。图2-15速卖通平台服饰配件行业各国商品关键词数据图2-16新建表格分析工具之EXCEL3.数据分析（1）对比分析法根据对比的对象和方式，可以分为横向对比和纵向对比。横向对比是指在同一时间条件下，对不同总体指标的比较，例如，不同部门、地区、业内不同企业等的销售情况、业绩情况比较。纵向对比是指在同一总体条件下，对不同时期指标数据的比较。例如，与上季度比较、与上年同期比较、与活动前比较、与计划比较。（2）平均分析法：算术平均分析就是运用计算平均数的方法反应总体在一段时间、地点条件下，某一数量特征的一般水平。平均指标既可用于同一现象在不同地区、不同部门间的横向比较，也可用于同一现象在不同时间的对比。使用平均指标更能反映现象的持续能力或水平。算术平均数的计算公式为：总体各单位数值的总和/总体单位个数。（3）交叉分析法

交叉分析法常用于分析两个变量（字段）之间的关系，即同时将两个有一定联系的变量及其值交叉排列在一张表格内，使变量值成为不同变量的交叉结点，形成交叉表，从而分析交叉表中变量之间的关系。交叉表有二维的，也有多维的，维度越多就越复杂，而选择几个维度要根据分析的目的而定。分析工具之EXCELExcel中有很多图表样式可供展现不同的数据，包括柱形图、折线图、饼图、条形图、面积图等。因此，如何选择合适的图表非常重要。（1）柱状图：可以非常清晰的表达不同项目之间的差距和数值，通常用于不同时期或不同类别数据之间的比较，也可以用来反映不同时期和不同数据的差异。（2）拆线图：用来表达数据随着时间推移而发生变化的一种图表，可以预测未来的发展趋势。常通过若干条拆线来绘制若干组数据，判断每组数据的峰值与谷值，以及折级变化的方向、速率和周期等特征。（3）堆积柱状图：可以比较不同数值在总计中所占的比重，可选择使用单位或百分比显示。常用于比较数个总计的每个部分，看出各个组成总体的具体比重。（4）散点图：用来说明若干组变量之间的相互关系，可表示因变量随自变量而变化的大致趋势。一般呈现簇状不规则的分布，可用数据点