Excel数据分析基础与实践（上篇共上中下3篇）

第1章数据分析概述1.1数据 1.2数据分析1.3数据分析工具Excel基础1

1.1数据1.1.1数据的概念及类型1.1.2数据的使用及价值1.1.3数据组织与数据结构21．数据的概念数据是指对客观事件进行记录并可以鉴别的符号，是对客观事物的性质、状态以及相互关系等进行记载的物理符号或这些物理符号的组合，是可识别的、抽象的符号。1.1.1数据的概念及类型32．数据的类型(1)按照所采用的计量尺度不同，统计数据可以分为定类数据、定序数据、定距数据和定比数据。(2)按照统计数据的收集方法，可以将其分为观测数据和实验数据。(3)按照被描述的对象与时间的关系，即从时空维度可以将统计数据分为截面数据、时间序列数据和面板数据。41．数据的使用数据有效利用的前提是对数据进行数据清洗。数据清洗，就是把数据变成一种可用的状态。这个过程需要借助于工具去实现数据转换，比如UNIX工具AWK、XML解析器和机器学习库等。还要进行数据管理，存储数据是为了更好地分析数据，分析数据需要借助于数据挖掘和机器学习算法，同时需要使用相关的大数据处理技术

。1.1.2数据的使用及价值52．数据的价值(1)各类被收集来的数据应当被尽可能长时间地保存下来，同时也应当在一定条件下与全社会分享，并产生更大的价值。(2)在大数据时代以前，极具价值的商品是石油，而今天和未来极具价值的商品是数据。61．数据组织

(1)文件：数据都是以文件形式存在的，文件名包含主名和扩展名，扩展名用来表示文件的类型。

(2)数据库：非结构化数据迅速增加，目前人类社会产生的数据中有90%是非结构化数据，因此，能够更好地支持非结构化数据管理的NoSQL数据库应运而生。1.1.3数据组织与数据结构72．数据结构数据结构是存储、组织数据的方式，是数据内部的构成方法。数据结构包括3类，即数据的逻辑结构、数据的存储结构和数据的运算结构。（1）数据主体：被记录数据的事物，包括动态的“事”和静态的“物”（2）数据表：记录一系列数据的集合（3）记录：该数据集中某个数据对象的所有信息（4）字段：每条记录中对数据主体的属性描述83．结构化数据和非结构化数据

结构化数据主要指在数据存储和数据处理过程中结构设计比较合理的数据。非结构化数据是由固定的行和列组成的数据表称为二维结构数据表，其中的数据是结构化数据。如果行和列的数量不固定，即不能由固定的行和列组成二维结构数据表来表示和存储的数据。9

1.2数据分析1.2.1数据分析的内涵1.2.2数据分析的流程1.2.3数据分析的应用场景1.2.4数据分析的常用指标和术语10（1）数据分析的定义

数据分析是指使用适当的统计分析方法(如聚类分析、相关分析等)对收集来的大量数据进行分析，从中提取有用信息和形成结论，并加以详细研究和概括总结的过程。（2）数据分析的目的

数据分析的目的在于，从一大批看似杂乱无章的数据信息中提取出有用信息，以求最大化地开发数据的功能，发挥数据的作用，并找出所研究对象的内在规律。1.2.1数据分析的内涵

11（3）数据分析的意义

在实际工作中，数据分析能够帮助管理者进行判断和决策，以便采取适当的策略与行动。（4）数据分析的作用

数据分析的作用可以分为三个方面，分别是分析现状、分析原因和预测未来发展趋势。121.数据分析对于企业的决策和发展至关重要，为了顺利地完成数据分析的任务，获得需要的结果，需要按照一定的步骤进行数据分析的工作。一般来说，数据分析可以分为6个既相互独立又互有联系的阶段，分别是：明确目的、数据收集、数据处理、数据分析、数据展现、报告撰写1.2.2数据分析的流程132．具体内容（1）明确目的（2）数据收集数据收集是在明确数据分析的目的之后，获取数据的过程，可以为数据分析提供直接的素材和依据。（3）数据处理主要包括数据清理、数据转换、数据提取、数据汇总、数据计算等数据处理方法14（4）数据分析数据分析是对处理过的数据进行分析，通过合适的方法及工具，从中推导出有价值的信息并形成有效结论的过程。（5）数据可视化数据可视化是将数据分析结果通过直观的方式(表格、图形等)呈现出来。（6）撰写报告151.分类（1）客户分析

客户分析主要是根据客户的基本数据信息进行商业行为分析，首先界定目标客户，根据客户的需求、目标客户的性质、所处行业的特征以及客户的经济状况等基本信息。（2）营销分析

营销分析囊括了产品分析、价格分析、渠道分析、广告与促销分析这4类分析。产品分析主要是竞争产品分析，通过对竞争产品的分析制定自身产品策略。1.2.3数据分析的应用场景16（3）社交媒体分析社交媒体分析是以不同的社交媒体渠道生成的内容为基础，实现不同社交媒体的用户分析、访问分析和互动分析等。（4）网络安全大规模网络安全事件的发生，例如2017年5月席卷全球的WannaCry病毒，让企业意识到网络攻击发生时预先快速识别的重要性。（5）设备管理设备管理同样是企业关注的重点。设备维修一般采用标准修理法、定期修理法和检查后修理法等方法。17（6）交通物流分析物流是物品从供应地向接收地的实体流动，是将运输、储存、装卸搬运、包装、流通加工、配送和信`患处理等功能有机结合起来而实现用户要求的过程。（7）欺诈行为检测

身份信息泄露及盗用事件逐年增长，随之而来的是欺诈行为和交易的增多。18（1）平均数

平均数是统计学中最常用的统计量，包括算术平均数、几何平均数、调和平均数、加权平均数、指数平均数等。（2）绝对数与相对数

绝对数也是数据分析中的常用指标。统计中常用的总量指标就是绝对数，它是反映客观现象总体在一定时间、地点条件下的总规模、总水平的综合指标。1.2.4数据分析的常用指标和术语

19（3）百分比与百分点

百分比是一种表达比例、比率或分数数值的方法。它是相对数中的一种，也称为百分率或百分数。（4）比例与比率

比例是一个总体中各个部分的数量占总体部分的比重，用于反映总体的构成或结构。20（5）频数与频率

频数也称“次数”，指变量值中代表某种特征的数(标志值)出现的次数，频数可以用表或图形来表示。（6）倍数与番数

倍数是指一个数除以另一个数所得的商，比如A÷B=C，就可以说A是B的C倍。（7）同比和环比

同比指的是与历史同时期数据相比较而获得的比值，主要是反映事物发展的相对性。21

Excel是微软公司为使用Windows和AppleMacintosh操作系统的计算机编写的一款电子表格软件,是Office系列办公软件的一种,可以实现对日常生活、工作中的表格的数据处理。被广泛应用于管理、统计、财经和金融等诸多领域。

Excel功能全面，几乎可以处理各种数据；其具有丰富的数据处理函数与图表处理功能,能进行数据分析；其还能方便地进行数据交换,同时还有常用的Web工具。1.3数据分析工具Excel基础

221．启动Excel2016在Windows7以上版本操作系统的计算机中，单击【开始】选项卡，找到Excel图标并单击，或双击桌面Excel2016的图标，打开的用户界面如图1-2所示。1.3.2Excel软件用户界面23图1-2Excel2016用户界面2．用户界面介绍Excel2016用户界面包括标题栏、功能区、名称框、编辑栏、工作表编辑区和状态栏，如图1-3所示。24图1-3Excel2016用户界面的构成（1）标题栏标题栏位于应用窗口的顶端，如图1-4所示，包括快速访问工具栏、当前文件名、应用程序名称和窗口控制按钮。在图1-4中，框1为快速访问工具栏，框2为当前文件名，框3为应用程序名称，框4为窗口控制按钮。快速访问工具栏可以快速运行【保存】【撤销】【恢复】等命令。如果快速访问工具栏中没有所需命令，那么可以单击快速访问工具栏的按钮，选择需要添加的命令1.3.2Excel软件用户界面25图1-4标题栏

（2）功能区

标题栏的下方是功能区，如图1-5所示，由【开始】【插入】【页面布局】等选项卡组成，每个选项卡又可以分成不同的组，如【开始】选项卡由【剪贴板】【字体】【对齐方式】等命令组组成，每个命令组又包含了不同的命令按钮。在图1-6中，框1为选项卡，框2为命令组。26图1-5功能区

（3）名称框和编辑栏

功能区的下方是名称框和编辑栏，如图1-6所示。其中，名称框可以显示当前活动单元格的地址和名称，编辑栏可以显示当前活动单元格中的数据或公式。27图1-6名称框和编辑栏（4）工作表编辑区名称框和编辑栏的下方是工作表编辑区，如图1-7所示。由文档窗口、标签滚动按钮、工作表标签、水平滚动滑条和垂直滚动滑条组成。28图1-7工作表编辑区（5）状态栏状态栏位于用户界面底部，如图1-8所示。由视图按钮和缩放模块组成，用来显示与当前操作相关的信息。29图1-8状态栏3．关闭Excel2016

单击窗口控制按钮中的【关闭】按钮，如图1-10所示，或按组合键【Alt+F4】即可关闭Excel2016。30图1-10关闭Excel20161．工作簿的基本操作

（1）创建工作簿

单击【文件】选项卡，依次选择【新建】命令和【空白工作簿】即可创建工作簿，也可以通过按组合键【Ctrl+N】的方式快速新建空白工作簿。（2）保存工作簿

单击快速访问工具栏的【保存】按钮，即可保存工作簿，也可以通过按组合键【Ctrl+S】的方式快速保存工作簿。1.3.3Excel基本操作31

（1）创建工作簿

单击【文件】选项卡，依次选择【新建】命令和【空白工作簿】即可创建工作簿，也可以通过按组合键【Ctrl+N】的方式快速新建空白工作簿。（2）保存工作簿

单击快速访问工具栏的【保存】按钮，即可保存工作簿，也可以通过按组合键【Ctrl+S】的方式快速保存工作簿。32

（3）打开和关闭工作簿

单击【文件】选项卡，选择【打开】命令，或者通过按组合键【Ctrl+O】的方式弹出【打开】对话框，再选择一个工作簿即可打开工作簿。单击【文件】选项卡，选择【关闭】命令即可关闭工作簿。也可以通过按组合键【Ctrl+W】的方式关闭工作簿。332．工作表的基本操作

（1）插入工作表

在Excel2016中插入工作表有多种方法，以下介绍两种常用的方法插入工作表。①以【Sheet1】工作表为例，单击工作表编辑区按钮即可在现有工作表的末尾插入一个新的工作表【Sheet2】②以【Sheet1】工作表为例，右键单击【Sheet1】工作表，选择【插入】命令，弹出【插入】对话框，最后单击【确定】按钮即可在现有的工作表的之前插入一个新的工作表【Sheet3】，也可以通过组合键【Shift+F11】在现有的工作表的之前插入一个新的工作表。34

（2）重命名工作表

以【Sheet1】工作表为例，右键单击【Sheet1】标签，选择【重命名】命令，再输入新的名字即可重命名。

（3）设置标签颜色

以【Sheet1】标签为例，右键单击【Sheet1】标签，选择【工作表标签颜色】命令，再选择新的颜色即可设置标签颜色，如图1-18所示。35（4）移动或复制工作表①移动工作表：以【Sheet1】工作表为例，单击【Sheet1】标签不放，向左或右拖动到新的位置即可移动工作表。②复制工作表：以【Sheet1】工作表为例，右键单击【Sheet1】标签，选择【移动或复制】命令，在弹出【移动或复制工作表】对话框中，选择【Sheet1】标签，勾选【建立副本】按钮，最后单击【确定】按钮即可复制工作表。36（5）隐藏和显示工作表

以【Sheet1】工作表为例，右键单击【Sheet1】标签，选择【隐藏】命令，即可隐藏【Sheet1】工作表（注意，只有一个工作表时不能隐藏工作表）。（6）删除工作表

以【Sheet1】工作表为例，右键单击【Sheet1】标签，选择【删除】命令，即可删除工作表。37思考题1．简述对数据的理解。2．简述数据组织的形式。3．简述对数据结构的理解。4．简述对数据分析含义的理解。5．简述数据分析的流程。6．简述数据分析的应用场景。7．列举并解释数据分析的常用指标和术语。38第2章Excel公式与函数2.1Excel公式 2.2Excel函数2.3Excel公式与函数运用中的常见问题39

2.1Excel公式2.1.1Excel公式中的运算符2.1.2Excel公式中单元格的引用2.1.3Excel公式中地址的引用40单元格引用就是标识工作表上的单元格或单元格区域，指明公式中所用的数据的位置。在Excel中，可以引用同一工作表不同部分的数据，一工作簿不同工作表的数据，甚至不同工作簿的单元格数据。单元格或单元格区域引用一般式为：工作表名！单元格引用或[工作簿名]工作表名！单元格引用。2.1.2Excel公式中单元格的引用41

在“Exce项”对话框中，单击左侧列表中的“高级”选项，在“此工作表的显示选项”栏中，选中“在单元格中显示公式而非其计算结果”复选框，可以使单元格显示公式，而不是计算的结果。421．名称的语法规则（1）有效字符：名称的第一字符必须是字母、下划线或反斜杠（\）。名称中的其余字符可以是字母、数字、句点和下划线。（2）名称不能与单元格引用地址相同，如Z$100或R1C1。（3）不能使用空格：在名称中不允许使用空格，请使用下划线（_）和句点（.）作为单词分隔符。2.1.3名称的定义与运用43（4）名称长度限制：名称最多可以包含255个字符。（5）不区分大小写：名称可以包含大写字母和小写字母，但Excel在名称中不区分大写字母和小写字母。（6）唯一性：名称在其适用范围之内必须具备唯一性，不可重复。442．名称的适用范围

如果定义了一个名称（如Budget_FY08）且其适应范围为Sheet1，则该名称只能在Sheet1中被识别，如要在其他同一工作簿的工作表中使用该名称，必须加上工作表名称，如Sheet1!Budget_FY08。

如果定义了一个名称（如Sales_01）且适用范围是工作簿（即该Excel文件），则该名称对于该工作秒表中的所有工作表都是可识别的，但对于其他工作簿是不可识别的。453．为单元格或单元格区域定义名称（1）快速定义名称①选择要命名的单元格或单元格区域②单击“编辑栏”最左边的“名称”框。③在“名称”框中键入引用选定内容时要使用的名称。④按Enter键确认。46（2）将现有行和列标签转换为名称①选择要命名的区域，包括行或列标签。②在“公式”选项卡的“定义的名称”组中，单击“根据所选内容创建”按钮。③在弹出的“以选定区域创建名称”对话框中，通过选中“首行”、“左列”、“末行”、“右列”复选框来指定包含标签的位置，如图所示④单击“确定”按钮，完成名称的创建。通过该方式创建的名称仅引用相应标题下包含值的单元格，并且不包含现有行和列标题。47（3）使用“新名称”对话框定义名称①在“公式”选项卡的“定义的名称”组中，单击“定义名称”。②在“新建名称”对话框的“名称”框中，输入要用于引用的名称。③指定名称的适用范围：在“适用范围”下拉列表框中选择“工作簿”或工作簿中工作表的名称④根据需要，在备注栏中输入对该名称的说明性批注，最多255个字符。4849⑤在“引用位置”框中，执行下列操作之一：（a）要引用一个单元格，则鼠标单击“引用位置”框，然后在工作表中重新选择需要引用的单元格或单元格区域。（b）要引用一个常量，则输入＝（等号），然后输入常量值。（c）要引用公式，则输入＝（等号），然后输入公式。⑥单击“确定”按钮，完成命名并返回工作表。4．使用“名称管理器”管理名称使用“名称管理器”可以处理工作簿中所有已定义的名称和表名称。鼠标单击“公式”选项卡的“定义的名称”组中的“名称管理器”按钮，打开“名称管理器”对话框，如图所示。50（1）更改名称

如果更改某个已定义名称或表名称，则工作簿中该名称的所有实例也会随之更改。

打开“名称管理器”，在该对话框中单击要更改的名称，然后单击“编辑”按钮，打开“编辑名称”对话框，在该对话框中按照需要修改名称、引用位置、备注说明等，但适用范围不能更改，更改完成后单击“确定”按钮即可。51（2）删除名称

在“名称管理器”对话框中，选择要删除的名称，也可按住Shift并单击鼠标选择连续的几个名称或按住Ctrl并单击鼠标选择不连续的多个名称，单击“删除”按钮或按Delete键，再单击“确定”，确认删除。525．引用名称

名称可直接用来快速选定已命名的区域，也可在公式中引用名称以实现精确引用。（1）通过“名称框”引用

单击“名称框”右侧的黑色箭头，打开“名称”下拉列表，在其中显示所有已被命名的单元格名称，但不包括常量和公式的名称。

单击选择某一名称，该名称所引用的单元格或单元区域将公被选中，如果是在输入公式过程中，则该名称会出现在公式中。53（2）在公式中引用

单击要输入公式的单元格。

在“公式”选项卡的“定义的名称”组中，单击“用于公式”按钮，打开名称下拉列表。54

2.2Excel函数2.2.1统计函数2.2.2数学计算函数2.2.3文本处理函数2.2.4逻辑运算函数2.2.5关联匹配函数2.2.6日期与时间函数551．平均值函数平均值是表示一组数据集中趋势的量数，是反映数据集中趋势的一项指标。（1）算术平均值：功能是返回参数的平均值，语法格式为AVERAGE(number1,number2,...)。2.2.1统计函数56（2）几何平均值：功能是返回正数数组或数值区域的几何平均数，语法格式为GEOMEAN(number1,[number2],...)。（3）调和平均值：功能是返回一组正数的调和平均数，语法格式为HARMEAN(number1,[number2],...)。572．计数函数

在数据分析过程中，经常需要统计选定区域内数值型单元格的数目、空白单元格的数目、非空单元格的数目以及满足某条件的单元格的数目。（1）COUNT函数：功能是返回包含数字以及包含参数列表中的数字的单元格的个数；语法格式为COUNT(value1,value2,...)。（2）COUNTA函数：功能是计算范围中不为空的单元格的个数；语法格式为COUNTA(value1,[value2],...)。58（3）COUNTBLANK函数：功能是用于计算单元格区域中的空单元格的个数；语法格式为COUNTBLANK（range）。（4）COUNTIF函数：功能是计算区域中满足给定条件的单元格的个数；语法格式为COUNTIF(range,criteria)。593．其他统计函数（1）最大值函数：功能是返回一组值中的最大值；语法格式为MAX(number1,number2,...)。（2）最小值函数：功能是返回一组值中的最小值；语法格式为MIN(number1,number2,...)。（3）MEDIAN函数：功能是返回给定数值集合的中值.中值是在一组数据中居于中间的数,即在这组数据中，有一半的数据比它大,有一半的数据比它小；语法格式为MEDIAN(number1,number2,...)。60（4）Rank函数：功能是返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）；语法格式为RANK(number,ref,order)。（5）Frequency函数：功能是以一列垂直数组返回一组数据的频率分布；语法格式为Frequency(data_array,bins_array)。611．求和函数

在数据分析过程中，有时需要对数值型数据进行求和或根据指定条件求和等。（1）SUM函数：功能是返回某一单元格区域中所有数字之和；语法格式为SUM(number1,number2,...)，其中，Number1,number2,...为1到30个需要求和的参数，可以是一个具体的数值、一个单元格或单元格区域。2.2.2数学计算函数62（2）SUMIF函数：功能是对范围中符合指定条件的数值求和；语法格式为SUMIF(range,criteria,sum_range)。（3）SUMIFS函数：功能是用于计算其满足多个条件的全部参数的总量；语法格式为SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)。（4）SUMPRODUCT函数：功能是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和；SUMPRODUCT(array1,[array2],[array3],...)。632．其他数学函数（1）INT()函数：功能是将number数值向下舍入为最接近的整数；语法格式为INT(number)。（2）MOD函数：功能是返回两数相除的余数，结果的正负号与除数相同；语法格式为MOD(number,divisor)。641．字符串截取类函数（1）LEN()函数：功能是返回文本字符串中的字符数；语法格式为LEN(text)。（2）LENB()函数：功能是返回文本字符串中用于代表字符的字节数；语法格式为LENB(text)。（3）LEFT()函数：功能是LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符；语法格式为LEFT(text,num_chars)2.2.3文本处理函数65（4）RIGHT()函数：功能是根据所指定的字符数返回文本字符串中最后一个或多个字符）；语法格式为RIGHT(text,num_chars)。（5）MID()函数：功能是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定；语法格式为MID(text,start_num,num_chars)。662．字符串查找替换函数（1）FIND()函数：功能是FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号；语法格式为FIND(find_text,within_text,start_num)。（2）REPLACE()函数：功能是使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本；语法格式为REPLACE(old_text,start_num,num_chars,new_text)。（3）SUBSTITUTE()函数：功能是在文本字符串中用new_text替代old_text；如果需要在某一文本字符串中替换指定的文本语法格式为SUBSTITUTE(text,old_text,new_text,instance_num)。673．文本转换类函数

TEXT函数的功能是根据指定的数值格式将数字转换为文本。其语法格式为TEXT(value,format_text)。681．IF类函数（1）IF()函数：功能是执行真假值判断，根据逻辑计算的真假值，返回不同结果；语法格式为IF(logical_test,value_if_true,value_if_false)。（2）IFNA()函数：功能是如果表达式解析为#N/A，则返回value_if_na指定的值，否则返回表达式的结果；语法格式为IFNA(value,value_if_na)。2.2.4逻辑运算函数69（3）IFERROR()函数：功能是如果表达式是一个错误，则返回value_if_error的值，否则返回表达式自身的值；语法格式为IFERROR(value,value_if_error)。702．IS类函数（1）ISBLANK(value)：检查是否引用了空单元格。（2）ISERR(value)：检查一个值是否为#N/A以外的错误。（3）ISERROR(value)：检查一个值是否为错误。（4）ISLOGICAL(value)：检查一个值是否是逻辑值。（5）ISNA(value)：检查一个值是否为#N/A。（6）ISNONTEXT(value)：检查一个值是否不是文本。71（7）ISNUMBER(value)：检查一个值是否是数值。（8）ISREF(value)：检查一个值是否为引用。（9）ISTEXT(value)：检查一个值是否为文本。（10）ISEVEN(number)：检查number值是偶数。（11）ISODD(number)：检查number值是奇数。723．逻辑判断类函数（1）AND()函数：功能是检查是否所有参数均为TRUE；语法格式为AND(logical1,logical2,……)。（2）OR()函数：功能是如果任一参数值为TRUE，即返回TRUE，只有当所有参数值均为FALSE时，才返回FALSE；语法格式为OR(logical1,logical2,……)。（3）NOT()函数：功能是对参数的逻辑值求反，参数为TRUE时返回FALSE，参数为FALSE时返回TRUE；语法格式为NOT(logical)。731．关联类函数（1）VLOOKUP()函数：功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值；语法格式为VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。2.2.5关联匹配函数74（2）LOOKUP()函数：；LOOKUP()函数有向量形式和数组形式两种使用方式，向量形式的语法格式为LOOKUP(Lookup_value,Lookup_vector,Result_vector)，功能是从单行或单列中查找一个值；数组形式的语法格式为LOOKUP(Lookup_value,Array)，功能是从数组中查找一个值。752．查询类函数（1）行列函数：包括COLUMN()函数和ROW()函数；COLUMN()函数的功能是返回指定单元格引用的列号，语法格式为COLUMN([Reference])；ROW()函数的功能是返回一个引用的行号，语法格式为ROW([Reference])。76（2）INDEX()函数：INDEX函数有数组形式和引用形式两种使用方式，数组形式语法格式为INDEX(Array,Row_num,Column_num)，功能是返回表格或数组中的元素值，此元素由行号和列号的索引值给定；引用形式语法格式为INDEX(Reference,Row_num,Column_num,Area_num)，功能是返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。（3）MATCH()函数：功能是返回符合特定值特定顺序的项在数组中的相对位置，语法格式为MATCH(Lookup_value,Lookup_array,[Match_type])。771．计算天数函数

NETWORKDAYS()函数：功能是返回两个日期之间的完整工作日数，语法格式为NETWORKDAYS(Start_date,End_date,[Holidays])。2.2.6日期与时间函数782．年月日判断函数（1）TODAY()函数：功能是返回当前日期的序列号，TODAY无参数。（2）YEAR()函数：功能是返回对应于某个日期的年份，Year作为1900-9999之间的整数返回。（3）MONTH()函数：功能是返回日期（以序列数表示）中的月份。月份是介于1（一月）到12（十二月）之间的整数，语法格式为MONTH(Serial_number)。（4）DAY()函数：功能是返回以序列数表示的某日期的天数。天数是介于1到31之间的整数，语法格式为DAY(Serial_number)。79

2.3Excel公式与函数运用中的常见问题2.3.1常见的错误信息与处理方法2.3.2单元格的追踪2.1.3Excel公式中地址的引用802.3.1常见的错误信息与处理方法81错误常见原因处理方法#DIV/0!在公式中有除数为零，或者有除数为空白的单元格(Excel把空白单元格也当作0)。把除数改为非零的数值，或者用IF函数进行控制。#N/A在公式使用查找功能的函数(VLOOKUP、HLOOKUP、LOOKUP等)时，找不到匹配的值。检查被查找的值，使之的确存在于查找的数据表中的第一列。#NAME?在公式中使用了Excel无法识别的文本，例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。根据具体的公式，逐步分析出现该错误的可能，并加以改正。#NUM!当公式需要数字型参数时，却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。根据公式的具体情况，逐一分析可能的原因并修正。82#VALUE文本类型的数据参与了数值运算，函数参数的数值类型不正确；函数的参数本应该是单一值，却提供了一个区域作为参数；输入一个数组公式时，忘记按Ctrl＋Shift＋Enter键。更正相关的数据类型或参数类型；提供正确的参数；输入数组公式时，记得使用Ctrl＋Shift＋Enter键确定。#REF!公式中使用了无效的单元格引用。通常如下这些操作会导致公式引用无效的单元格：删除了被公式引用的单元格；把公式复制到含有引用自身的单元格中。避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。#NULL!使用了不正确的区域运算符或引用的单元格区域的交集为空。改正区域运算符使之正确；更改引用使之相交。（1）追踪引用单元格

选择包含需要找到其引用单元格的公式的单元格，鼠标单击“公式”选项卡的“公式审核”组中的“追踪引用单元格”，如图所示，蓝色箭头显示无错误的单元格，红色箭头显示导致错误的单元格。如果所选单元格引用了另一个工作表或工作簿上的单元格，则会显示一个从工作表图标指向所选单元格的黑色箭头。2.3.2单元格的追踪8384

若要移去引用单元格追踪箭头，则单击如图所示的“删除箭头”按钮旁边的箭头，在弹出的下拉列表中执行“删除引用单元格追踪箭头”命令。85（2）追踪从属单元格

选择要对其标识从属单元格的单元格，鼠标单击“公式”选项卡的“公式审核”组中的“追踪从属单元格”，可追踪显示引用了该单元格的单元格。同样，蓝色箭头显示无错误的单元格，红色单元格显示导致错误的单元格。86思考题1．简述Excel公式中的运算符。2．简述Excel公式中单元格的引用。3．简述Excel公式中地址的引用。4．简述Excel函数的种类。5．简述Excel公式与函数运用中的常见问题及处理方法。87第3章Excel数据获取3.1Excel数据类型3.2Excel数据获取88

3.1Excel数据类型3.1.1数值型数据3.1.2字符型数据3.1.3数据类型的转换89

数值型数据是以数字格式存储的数据。数值型数据可以进行计算，并且在Excel的单元格里默认靠右对齐的显示方式。在Excel中日期和时间是数值型数据，如图所示。

3.1.1数值型数据90

字符型数据类型就是文本格式的数据，如汉字、字母等，它不可以进行科学计算，在Excel的单元格里默认靠左对齐的显示方式，如图所示。

3.1.2字符型数据91

1．数值转字符

数值转字符有两种方法：一种是当逐条录入时，提前将单元格格式改为文本类型，再逐条输入；另一种是已有数据时，使用Excel的【分列】功能，具体操作步骤如下。（1）选中要转换的数据行，单击【数据】【数据工具】【分列】按钮。（2）选择【分隔符号】进行分列，如图所示。

3.1.3数据类型的转换9293（3）具体的分隔符号保持勾选默认项，即【Tab键】，如图所示。94（4）列数据格式要设置成【文本】，如图所示。95（5）单击【完成】按钮后，即可发现原先被折叠的数据已经以文本格式存储，并全部展示出来，如图所示。962．字符转数值

将以文本形式存储的数字转换为数值形式存储的数字有两种方法，一种是直接在左上角的小绿标里单击【转换为数字】；另一种是使用Excel的【分列】功能，如图所示。97

3.2Excel数据获取3.2.1内部数据的读入3.2.2外部数据的获取98

1．Excel打开csv文件出现乱码

.csv文件格式是一种存储数据的纯文本格式，Excel文件默认是ANSI编码，如果从数据库中导出的csv文件的编码方式为utf-8或unicode等其他编码，就可能会出现用Excel打开的csv文件乱码的情况。具体的解决方法是利用记事本重新编码，如图所示。

3.2.1内部数据的读入991002．Excel存储成csv格式后丢失sheet表

.csv文件格式只能保存当前工作表中的文本数值，也就是说如果一个Excel工作簿有多个sheet工作表，存储为csv格式后，只能保存当前显示的工作表，其他工作表会因为无法被存储而丢失，如图所示。具体的解决方法是在处理csv格式文件的时候，要注意避免新增sheet工作表。101102

1．Excel打开csv文件出现乱码

.csv文件格式是一种存储数据的纯文本格式，Excel文件默认是ANSI编码，如果从数据库中导出的csv文件的编码方式为utf-8或unicode等其他编码，就可能会出现用Excel打开的csv文件乱码的情况。具体的解决方法是利用记事本重新编码，如图所示。

3.2.1内部数据的读入103

1．从文本获取外部数据

除了获取内部数据外，还有从外部获取数据的情况，有时候数据是以文本形式（.txt）来存储的，我们想要导入到excel里的具体操作步骤如下。（1）单击【数据】——【获取外部数据】——自文本来获取，如图所示。3.2.2外部数据的获取104105（2）文件类型选择默认的以分隔符号划分，如图所示。106（3）分隔符号保持勾选默认的【Tab键】，即可完成导入，如图所示。（4）列数据格式选择默认的【常规】格式，单击【完成】按钮，如图3-1所示。之后，自文本获取外部数据的结果就被显示出来，如图3-2所示。1071081093-11103-22．从网站获取外部数据从网站获取外部数据的具体操作步骤如下。（1）单击【数据】【获取外部数据】【自网站】按钮，如图所示。111（2）选择一个想要获取数据的网站，将网址输入Excel内置的链接中，单击【转到】按钮，Excel会自动选取网页上的数据，选择想要的数据，单击【导入】按钮，如图所示。导出的数据如图所示。112113导出的数据如图所示。1143．从数据库获取外部数据

数据库，即用来存储和管理数据的仓库，常用的数据库有Access、SQLServer、MySQL、Oracle等。（1）从Access数据库获取数据从Access数据库获取数据的具体操作步骤如下。①单击【数据】【获取外部数据】【自Access】按钮，如图3所示。115116②选择数据源所在路径，Access数据文件以.accdb为扩展名，单击【打开】按钮，如图所示。117

③选择以【表】的显示方式，放置在现有工作表或新工作表中，单击【确定】按钮，如图所示。118此时，Access数据库中的数据就导入到了Excel表格中了，如图所示。119（2）从SQLServer数据库获取数据

导入SQLServer数据库数据的具体操作步骤如下。

①单击【数据】【获取外部数据】【自其他来源】【来自SQLServer】按钮，如图所示。120121

②在弹出的【数据连接向导】对话框中输入服务器名称，登录凭证选择【使用Windows验证】选项，单击【下一步】按钮，如图所示。122

③在弹出的【数据连接向导】对话框中，可以选择要导入的数据库。这里选择要导入的数据源在test数据库中的student表，单击【完成】按钮，如图所示。123

④在弹出的【导入数据】对话框中选择以【表】的形式显示，单击【确定】按钮，如图所示。124

此时，SQLServer数据库中的数据就导入到了Excel表格中了，如图所示。125思考题1．简述Excel的数据类型。2．简述不同数据类型之间的转换方式。3．简述获取数据的来源途径。4．简述获取外部数据的渠道。5．简述从文本获取外部数据的过程。6．简述从网站获取外部数据的过程。7．简述从数据库获取外部数据的过程。126第4章Excel数据处理4.1数据清洗4.2数据抽取4.3数据合并4.4数据计算1274.1数据清洗4.1.1缺失值的处理4.1.2重复值的处理4.1.3异常值的处理4.1.4不规范数据的处理1284.1.1缺失值的处理1．找出空值找出空值的方法有很多种，其中筛选空值和定位空值是两种典型的方法。2．处理空值在数据清洗过程中，可以根据需要对空值进行处理。下面介绍3种处理空值的方法。（1）删除空值（2）保留空值（3）使用替代值1294.1.2重复值的处理1．查找重复值查找重复值有两种方法：一种是使用COUNTIF()函数，另一种是使用条件格式功能。2．删除重复值在Excel中使用删除重复值功能可以删除重复值，具体操作步骤如下。1304.1.2重复值的处理（1）在工作表中选择A1:F18单元格区域，在“数据”选项卡“数据工具”组中单击“删除重复值”按钮，弹出“删除重复值”对话框，仅保持“学号”复选框的勾选，如下图所示。1314.1.2重复值的处理（2）单击“确定”按钮，重复值删除完成后会弹出提示对话框，单击“确定”按钮，如下图所示。1324.1.3异常值的处理异常值是指原始数据中个别明显偏离其余数据的值。异常值有两种处理方法：一种是删除异常值，如果异常值对数据分析影响不大，则直接删除异常值；另一种是修改异常值，可以在合理的情况下将异常值修改为正常值。1334.1.4不规范数据的处理1．处理合并单元格处理合并单元格的常用方法是取消合并单元格并进行相