Excel 2016 商务数据处理与分析

第1章商务数据分析基础1.1 商务数据分析的概述1.1.1 什么是商务数据分析现今各个行业中都蕴含着大量的数据，并且数据量呈现出井喷式的增长速度，数据成为越来越有价值的资源，我们已经进入了大数据时代。大数据具有以下几个特征：（1）数据的价值密度低：大数据有巨大的潜在价值，同其呈现出的爆发式增长相比，某一对象或模块数据的价值密度较低，开发海量数据的难度和成本比较高。（2）处理的速度快：随着互联网和计算机技术的发展，数据生成、储存、分析、处理的速度远远超出人们的想象力，这是大数据区别于传统数据的显著特征。（3）数据量大：从2013年至2020年，人类的数据规模扩大了许多倍，每年产生的数据量将增长到44万亿GB。（4）数据类型复杂：大数据与传统数据相比，数据来源广泛、数据维度多、数据类型复杂。各种机器仪表在自动产生数据的同时，人自身的生活行为也在不断创造数据；不仅有企业组织内部的业务数据，还有海量相关的外部数据。1.1.2 商务数据分析的作用（1）评估产品机会产品构思初期，必要的需求调研及市场调研显得尤为关键。产品机会评估对后期产品设计及迭代都至关重要，它可以决定一个产品的未来和核心理念。例如，商家想要给一个新产品定价，产品的销售价格直接影响着产品的销售量、单位销售成本和销售利润。商家可以对同类产品销售统计数据进行分析，给出合理定价，以保证企业的长远利益和最佳经济效益的实现。（2）分析解决问题产品出现不良状况，肯定是存在缘由的。不能凭空想象臆造问题，必须尊重客观现实。那么只有通过必要的数据分析才能追溯问题根源，进而制定合理的解决方案。例如，通过分析商品的库龄可以了解哪些商品是滞销的，帮助商家调整商品结构，减少商品的库存周期，实现精准进货。因为库存属于商家的负资产，库存意味着被套牢的资金，而精准进货就是减少库存积压的重要手段。通过库存发现滞销的商品后，商家可以及时采取打折促销等措施来处理滞销的商品。（3）支持运营活动产品上线后效果如何？A方案和B方案哪个更好些呢？这些问题都牵涉到如何评判好还是坏？而最可靠的评判依据就是数据。只有给出真实、可靠、客观的数据，才能作出最真实的评判。例如，商家想要扩大销售量，常用的促销手段有：会员优惠、赠品、第二件半价、满减优惠、打折优惠等。商家可以分析历史的促销方式数据，对客户喜欢的促销方式进行排序，来最终决定采取何种促销手段可以促成客户从浏览商品、加入购物车到付款的过程，尽可能将流量转换成销量。（4）预测优化产品商务数据分析的结果不仅可以反应出以往产品的状态，还可以预测产品未来时间段内可能会遇到的问题，从而缩短迭代周期，精益求精。例如，商家想要规划下一年度的采购计划，可以利用已有的销售量统计数据，对未来市场销售量进行预估，预测下一个时段的销售量，以应对客户的需求变化。（5）提升客户价值分析客户数据可以实现对客户精准画像，充分挖掘客户的潜在价值，培养忠实客户，发现潜在客户，提高商家的销售额。例如，分析客户年龄和性别等属性，挖掘出这些属性与浏览商品、购买商品习惯之间的关联关系，实现为不同的客户提供个性化的商品推荐。1.1.3 常用商务数据分析度量（1）平均值平均数是一组数据之和除以这组数据的个数。例如，数据{15、2、3、4}的平均值是（15+2+3+4）÷4=6。（2）中位数中位数是一组有序数（从小到大排列）中居于中间位置的数。如果这组有序数的个数是偶数，则取中间两个数的平均值；如果这组有序数的个数是奇数，则取中间位置的数。例如，求数据{15、2、3、4}的中位数的过程是：先按照从小到大顺序排序{2、3、4、15}；共4个数据，为偶数，中位数的值是（3+4）÷2=3.5。求数据{15、2、9、3、4}的中位数的过程是：先按照从小到大顺序排序{2、3、4、9、15}；共5个数据，为奇数，中位数的值是4。考虑到一组数据中可能存在异常高值或异常低值的情况，所以中位数比平均值更好的反映出数据的集中趋势。（3）众数众数是一组数据中出现次数最多的数值。众数可以发现数据的特定规律，可以用来分析商品销售最多的尺码。例如，反映销售女鞋号码的数据{36、37、39、37、37、35、38、37、36}，其中，35出现1次；36出现2次；37出现4次；38出现1次；39出现1次，37出现的次数最多，所以这组数的众数是37，说明该鞋号是销售量最多的。（4）比例和比率比例是一个总体中各个部分的数值占总体数值的比重，反映的是部分与整体的关系。比率是一部分数值与另一部分数值的比较，反映的是部分与部分的关系。例如，一组数据反映出笔记本电脑和台式机电脑的销售数量{65，50}，则笔记本电脑销售比例为65:115;笔记本电脑销售比率为65:50。（5）同比与环比同比是对比去年同期的变化幅度。公式为：同比=（本期数据-去年同期数据）÷去年同期数据环比是对比上个同期的变化幅度。公式为：环比=（本期数据-上期数据）÷上期数据例如，2020年第一季度与2019年第一季度的销售变化是同比；2020年第一季度与2020年第二季度的销售变化是环比。同比反映的是长期的变化；环比反映的是短期的变化。

（6）动销率商品动销率是已销售商品的品种数占总商品的品种数的百分比。公式为：商品动销率=已销售商品的品种数÷商品总的品种数

100%（7）滞销率商品滞销率是指长期没有销售商品的品种数占总商品的品种数的百分比。公式为：商品滞销率=未销售商品的品种数÷商品总的品种数

100%（8）库存周转率也称为存货周转率，是指在某一时间段内库存货物周转的次数，是反映库存周转快慢程度的指标。周转率越大表明销售情况越好。公式为：库存周转率=期间段出库总金额÷[(期初库存金额+期末库存金额）÷2

]

100%1.2 商务数据分析的流程及工具1.2.1 商务数据分析的基本流程数据分析就是将无价值的数据变成有价值的信息的过程，从数据到发现知识需要分步骤进行，数据分析通常需要按照图1-3所示的步骤进行。1.2.1 商务数据分析的基本流程1.明确目标首先要明确数据分析的任务和目标，希望得到哪方面的决策支持，解决什么业务问题。例如，商家销售种类繁多的商品，如果想知道哪些是热销产品，哪些是滞销产品，根据数据分析的结果增加热销产品的进货数量，对滞销商品进行打折促销。2.数据采集数据分析目标确定后，就需要开始采集相关的数据。可以直接将数据录入Excel中，也可以将外部数据如Access数据、网站数据、文本数据等导入到Excel文件中，再进行进一步的处理。3.数据预处理数据预处理主要包括数据清洗、数据格式转换等工作，是进行数据分析前不可缺少的阶段，为数据分析提供可靠的数据，保障数据质量，为下一步数据分析做好准备。4.数据分析数据分析是采用合适的统计方法，从大量的杂乱的原始数据中提取出有价值的信息，挖掘出数据的因果关系、内部联系和业务规律，从而获得有价值、有意义的结论。例如计算总销售额、发现最畅销的商品等。5.数据可视化数据可视化是使用图形化工具，直观清晰的表达信息。例如用直方图表示不同商品的销售额；用散点图表示客户的年龄分布情况等。6.数据分析报告数据分析的最后一步是将分析的结果、有理有据的用Word报告、PPT演示文稿等形式呈现出来，提供给决策者，这样最终的决策是建立在充分数据分析的基础之上，具备科学性和可行性。1.2.2 常用商务数据分析工具数据分析的目标明确后，用户可以选择合适的软件来完成其他的过程。目前有多种数据分析软件，如Excel、Python、SQL、GoogleAnalytics、PowerBI、Stata等。其中，Excel是最基本、最常用的数据分析工具，其特点是易学易用，非计算机专业人员可以快速掌握和运用。Excel功能丰富，可以导入不同格式的数据来实现数据采集；利用条件格式、删除重复项等功能实现数据预处理；利用种类繁多的函数进行数据计算、利用分类汇总、数据透视表等利器进行数据分类统计；用图表完成数据可视化，直观展示抽象的数据。第2章商务数据的获取与数据清洗2.1工作表结构的设计2.1.1字段列的设计在输入数据前首先要设计数据表的结构，即有哪些列，每一列包含什么样的数据，需要为字段列命名，命名方式要言简意赅，例如，姓名、销售量等。字段列通常采用原始数据，通常不采用间接的计算结果数据。例如，客户信息中包含姓名、性别、年龄、联系电话等信息。在设计表结构时，如果直接设置“年龄”字段列，带来的问题是每一年的年初需要及时更新年龄，即每个客户的年龄加一。更好的方法是设置如图所示的“出生日期”字段，我们会在第3章介绍如何通过“出生日期”列求出“年龄”列，这样年龄会随着当前日期动态变化，每逢新年年龄自动加一，无需手工操作。例如，商品销售表中的“销售额”字段是数量乘以单价的计算结果，需要将该字段分解成如图所示的“数量”和“单价”两个字段更合理。2.1.2数据规范的原则1.只有一行表头有些用户为了打印时每页都有表头行，人为在表中插入表头列，这样将导致数据分析无法正常进行。如果需要在每页打印出表头行，可以在页面设置中设置“打印标题”即可。多余表头行2.不能有空行/空列、不能任意合并单元格用户输入数据时为了观看效果，随意插入了空行、空列、合并单元格，可能会导致排序、数据透视表等操作失效。多余空行合并单元格错误3.不能有小计行在原始数据表中不能出现小计行、总计行等统计结果，否则会导致数据透视表等结果错误。不能有小计行2.2数据输入数据主要类型包括：

数值

文本

日期时间2.2.1手动输入数据输入数据有三种形式：

常量

公式

函数。其中常量可以直接输入；公式和函数必须先输入“=”号。1.输入数值数值除了数字（0~9）组成的数字串外，还包括正号（+）、负号（﹣）、百分号（%）、货币符号（￥、$）、小数点（.）、千位分隔符号（，）以及科学记数符号（E、e）等。默认的情况下，输入的数值自动以右对齐方式显示。输入数值可以按照如下步骤操作：①

单击要输入数值的单元格。②

在该单元格中输入数值。当输入正数时，数字前面的正号“+”可以省略；当输入负数时，应该在数字前面添加负号“﹣”。输入特殊数据的说明（1）输入分数。在整数和分数之间应有一个空格，例如“123/4”；当分数小于1时，为了避免将输入的分数视为日期，可以在分数前面添加“0”和空格。例如，输入“01/3”，如果输入“1/3”，则显示为“1月3日”（2）使用科学记数法。如果输入很大或很小的数值时，将以科学记数法显示，例如，输入1234567890，显示为1.23E+9。引用单元格内容计算时将以输入数值为准，而不以显示数值为准。（3）使用千位分隔符。在数字间可以加入逗号作为千位分隔符。但是如果加入的位置不符合千位分隔符的要求，系统将自动按照文本处理。输入特殊数据的说明（4）超宽度数值的处理。当输入一个较长的数字时，显示“#####”表示该单元格列宽不够，不足以容纳整个数字。此时增加列宽即可正确显示。（5）超长数字处理。数字的最大精度是15位有效数字，如果整数超过15位时，系统会自动将15位之后的数字变成0，例如，输入123456789123456789，单元格中的值是123456789123456000；如果小数超过15位时，系统会自动将15位之后的舍去。例如，输入0.123456789123456789，单元格中的值是0.123456789123456。2.输入文本Excel文本包括汉字、英文字母、空格、符号等，此外不需要进行计算的数字也可以作为文本来处理。例如，身份证号、手机号码等。文本不能用于数值计算，但可以比较大小。输入文本可以按照如下步骤操作：①

单击要输入文本的单元格。②

输入文本内容，输入的内容会显示在编辑栏中。默认的情况下，输入的文本自动以左对齐方式显示。（1）数字作为文本处理在默认情况下，如果在单元格中输入数字，将会被识别为数值，以零开头的数字中的“0”将丢失，并采用右对齐的显示方式。可以使用3种方法将数字作为文本表示：方法一：先输入一个单撇号“

”，然后输入数字。例如，电话号码“01061880202”可以输入为

“

01061880202”。方法二：先输入一个等号，再在数字的前后加上双引号。例如，=″01061880202″方法三：在“开始”选项卡的“数字”选项组中，单击“常规”下箭头，在下拉选项中选择“文本”，此时单元格中输入的数字将按照文本处理分别按照数值与文本的不同方式输入“01061880202”，

数值是右对齐，第1位的0丢失

文本是左对齐（2）长文本的显示当输入的文本长度超过单元格宽度时，按照以下两种情况分别进行处理：如果该单元格右边单元格无内容，则该单元格显示的内容扩展延展到右边单元格。如果右边单元格有内容，则该单元格中显示出一部分文本的内容，其余的文本被隐藏，但是文本的内容依然存在于单元格中。（3）文本自动换行选中长文本的单元格，单击“开始”选项卡的“对齐方式”选项组中的“自动换行”按钮即可完成自动换行，将文本内容全部显示出来。3.输入日期和时间Excel内置了一些日期和时间的格式，当单元格输入数据与这些格式相匹配时，将按照日期或时间数据自动识别，输入日期和时间可以按照以下步骤操作：①

单击要输入日期时间的单元格。3.输入日期和时间

对于日期，使用斜线“/”或连字符“-”分隔日期的各部分。例如，输入“2021/10/1”或“2021-10-1”。对于时间，使用冒号“：”分隔时间的各部分。例如，输入“10:08:50”。3.输入日期和时间③

若在一个单元格中同时输入日期和时间，可以使用空格来分隔。例如，输入“2021/10/110:08:50”。④

若需要动态更新日期和时间，可以使用TODAY()函数或NOW()函数，输入格式为“=TODAY()”或“=NOW()”。默认的输入时间为24小时的时钟系统，如果需要采用12小时制，则需要在输入的时间后面输入一个空格后再输入“AM”或“PM”。提示无论是日期还是时间数据，当输入的数据值不符合格式时，单元格中的数据将被视为文本处理。2.1.2自动填充数据1.使用填充柄填充柄是位于选定区域右下角的小黑方块。将鼠标指向填充柄时，鼠标的指针变成为黑十字形状。①选定需要复制的单元格或单元格区域。②用鼠标左键拖拽填充柄经过需要填充数据的单元格，然后释放鼠标按键，出现“自动填充选项”按钮。③

单击“自动填充选项”按钮，对填充的内容进行修正。复制单元格：实现数据和格式的复制。填充序列：实现数据按照序列的自动填充。仅填充格式：只填充格式而不填充数据。不带格式填充：只填充数据而不填充格式。.自动填充选项2“自动填充选项”会随着填充的数据类型不同而变化。例如，在A2单元格中输入“2021-10-23”，然后按下鼠标左键拖拽填充柄至H2单元格，将自动按照日期进行填充，如果按下“自动填充选项”按钮，将显示出不同的日期填充选项，3.产生填充序列创建等差或等比序列的数据，其操作步骤如下：①在待填充区域的第一个单元格中输入序列的初值。②选定包含序列初始区域的单元格区域。③在“开始”选项卡的“编辑”选项组中，单击填充图标，在出现的列表中选择“系列”。④在打开的“序列”对话框中，选择序列产生在行或列、序列的类型、步长值和终止值。3.产生填充序列A1单元格的值为3，采用“等比序列”类型，步长值为5，单击“确定”按钮后产生了序列为：3、15、75、375、1875、9375。限制数据输入的设置1.固定内容的下拉列表对于数据表中性别的录入，内容固定只有“男”和“女”两个选择，可以采用固定内容的下拉列表实现。操作过程如下。①在性别列中选定需要使用下拉列表的B2:B4单元格区域。②在“数据”选项卡的“数据工具”选项组中，单击“数据验证”按钮，选择“数据验证”命令，打开“数据验证”对话框。③在“设置”选项卡中，将“允许”下拉列表框中的数据类型指定为“序列”；在“来源”文本框中直接输入列表内容“男,女”（注意其中的逗号必须是英文符号），而且必须勾选“提供下拉箭头”复选框，如图所示。④单击“确定”按钮，完成性别的下拉列表设置，数据录入效果如图所示，此时用户只能选择下拉列表中的“男”或“女”，不可以进行任意输入。2.可变内容的下拉列表对于数据表中会员等级的录入，需要根据不同商家的会员分类情况进行设置，录入内容会有所变化，可以采用可变内容的下拉列表实现，在进行数据验证设置之前，首先要建立下拉列表中的列表数据。操作过程如下。①在工作表中任意选择一列来建立下拉列表数据，这里选择了F1:F3单元格区域。②在会员等级列中选定需要使用下拉列表的C2:C4单元格区域。③在“数据”选项卡的“数据工具”选项组中，单击“数据验证”按钮，选择“数据验证”命令，打开“数据验证”对话框。④在“设置”选项卡中，将“允许”下拉框中的数据类型指定为“序列”；在“来源”文本框中，通过区域拾取器选择“$F$1:$F$3”区域，注意必须勾选“提供下拉箭头”复选框，如图所示。⑤单击“确定”按钮，完成会员等级下拉列表设置，数据录入效果如图所示。2.3数据格式化2.3.1设置单元格格式在工作表中输入编辑数据后，需要对单元格的格式进行设置，通过“开始”选项卡实现。2.设置单元格对齐方式单元格对齐是常用的编辑操作，单元格对齐可以使数据更加美观。单元格对齐主要包括：水平对齐和垂直对齐。方向是指单元格中内容的排列显示方向，默认为水平横排。在“开始”选项卡中“对齐方式”选项组中，单击“方向”按钮，可以实现对单元格中内容的方向设置。“自动换行”是经常使用的功能，当某个单元格中文字内容过多时，内容不能完全显示出来，设置“自动换行”后，内容可以全部显示。3.设置单元格中数字格式单元格的数字格式是指单元格中存放的数据类型或数据样式。有常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本和自定义等数据格式。设置数字单元格格式，可以按照以下步骤操作：①

选择要设置数字类型的单元格或单元格区域。②

在“开始”选项卡的“数字”选项组中，提供了快捷设置单元格数字格式的按钮。4.设置单元格格式使用设置单元格设置的操作步骤如下：①在“开始”选项卡的“字体”、“对齐方式”或“数字”选项组中，单击扩展按钮；或者在“开始”选项卡的“单元格”选项组中，单击“格式”按钮，在出现的列表中选择“设置单元格格式”选项，都可以打开“设置单元格格式”对话框。②“设置单元格格式”对话框中有6张选项卡。

数字：设置单元格中的数字格式。

对齐：设置单元格中的文本对齐方式、文本控制（当文本内容较长，不能完全显示时，选中“自动换行”复选框）、文本方向（可以输入角度值）。字体：设置单元格的字体、字形、字号、颜色等。边框：在单元格或单元格区域的周围添加边框。首先选择“直线”的样式和颜色，然后再选择边框线。填充：使用单色或图案为单元格或单元格区域添加底纹。5.合并单元格编辑工作表时，有时需要把多个相邻的单元格合并为一个单元格。合并后的单元格引用是选定区域的左上角单元格，合并单元格的操作步骤如下：①选择要合并的单元格区域。②在“开始”选项卡的“对齐方式”选项组中，单击“合并后居中”按钮即可。6.套用表格样式用户可以为工作表设置填充颜色、字体颜色、边框颜色。如果需要快速且专业的设置颜色，保证数据清晰可见，可以套用表格样式，Excel提供了若干个表格样式模板供用户选择使用，操作步骤如下。①

选择需要建立表格格式的单元格区域。②

在“开始”选项卡的“样式”选项组中，单击“套用表格格式”按钮，出现若干个表格样式模板，如图所示。③

鼠标指向不同的样式模板上，可以预览样式效果。单击选定的样式模板，则确定套用的表格样式。④

在打开的“套用表格式”对话框中确定表数据的来源，例如，图中“$A$1:$E$7”单元格区域。单击“确定”按钮得到如图所示的效果。2.3.2设置条件格式条件格式基于条件更改单元格区域的外观。如果条件为“真”，则按照该条件设置单元格区域的格式；如果条件为“假”，则不设置单元格区域的格式。使用条件格式可以达到的效果有：突出显示所关注的单元格或单元格区域、强调异常值、使用数据条、颜色刻度和图标集来直观地显示数据。1.“突出显示单元格规则”和“项目选取规则”“突出显示单元格规则”和“最前/最后规则”这两个选项是常用的条件格式。设置的操作步骤如下：①

选择一个单元格区域。②

在“开始”选项卡的“样式”选项组中，单击“条件格式”按钮。③

在“条件格式”的下拉选项中，选择其中一种：④

单击规则列出的某个特征选项，会弹出一个对话框，在对话框中设置相应的规则条件和符合条件的单元格格式即可。【例2-2】将库存数量大于50的用“浅红填充色深红色文本”显示出来。①

选择需要设置条件格式的C2:C10单元格区域。②

在“开始”选项卡的“样式”选项组中，单击“条件格式”按钮。③

在“条件格式”的下拉选项中，选择“突出显示单元格规则”中的“大于”选项。在打开的对话框中输入数值：50，然后在“设置为”下拉列表中选择“浅红填充色深红色文本”，如图所示。2.“数据条”、“色阶”、“图标集”这三个条件格式选项是通过在单元格背景中显示条形图、颜色和小图标来展示数据值的大小。这三个选项只针对数值型数据。数据条的长度代表单元格中的值。数据条越长，表示值越高数据条越短，表示值越低图标集可以按照阈值将数据分为3~5个类别，每个图标代表一个值的范围。【例2-3】将库存数量以蓝色数据条形式显示出来。①

选择需要设置条件格式的C2:C10单元格区域。②

在“开始”选项卡的“样式”选项组中，单击“条件格式”按钮。③

在“条件格式”的下拉选项中，选择“数据条”中的“渐变填充”选项中的“蓝色数据条”命令。3.新建规则用户可以通过新建格式规则选择规则类型，设置数据的显示格式，设置的操作步骤如下：①

选择一个单元格区域。②

在“开始”选项卡的“样式”选项组中，单击“条件格式”按钮。③

在“条件格式”的下拉选项中，选择“新建规则”。④

在打开的“新建格式规则”对话框中选择需要的规则类型，并设置规则格式。4.清除规则当不需要突出显示数据时，可以清除规则，清除的操作步骤如下：①

选择一个单元格区域。②

在“开始”选项卡的“样式”选项组中，单击“条件格式”按钮。③

在“条件格式”的下拉选项中，单击

“清除规则”级联的“清除所选单元格的规则”按钮即可。2.4.1从文本文件导入数据用户可以将文本文件中的数据导入到Excel工作表中进行数据处理。其操作步骤如下：①

在“数据”选项卡的“获取外部数据”选项组中，单击“自文本”选项，打开“导入文本文件”对话框。②

选定文本文件，单击“确定”按钮。③

在随后弹出的“文本导入向导”对话框（共3步），分别选择“分隔符号”、具体的分隔符、列数据格式等，然后单击“完成“按钮。④

在弹出的“导入数据”对话框中，选择数据的放置位置即可完成文本数据的导入。2.4.2从Access数据库导入数据用户将Access数据库的数据导入到Excel工作表中也是经常使用到的功能，操作步骤如下：①

在“数据”选项卡的“获取外部数据”选项组中，单击“自Access”选项，打开“选取数据源”对话框。②

选定Access数据库文件，单击“确定”按钮。③

如果导入的Access数据库中包含了多个表，则弹出“选择表格”对话框，在该对话框中选择所需要的表格后单击“确定“按钮。④

在弹出的“导入数据”对话框中，选择数据放置的位置即可完成Access数据库的导入。【例2-4】以“商品.accdb”Access数据库文件为例，导入其中“商品表”的记录数据，并以Excel电子表格形式存储。操作步骤如下。①在“数据”选项卡的“获取外部数据”选项组中，单击“自Access”选项，打开“选取数据源”对话框。选定该Access数据库文件，单击“打开”按钮。②在打开的“导入数据”对话框中，选择数据放置在“新工作表”中，如图所示，单击“确定”按钮后，显示出导入的结果如图所示。2.4.3商务网站数据导入Excel用户将网站中的数据导入到Excel工作表中也是经常使用到的功能，操作步骤如下。①在“数据”选项卡的“获取外部数据”选项组中，单击“自网站”选项。②在打开的“新建Web查询”页面中的“地址”栏中输入需要导入数据的网址，然后单击“转到”按钮，打开相应的网页。③单击“导入”按钮，在“导入数据”对话框中确定数据存放位置后单击“确定”按钮完成网站数据的导入。2.5数据清洗在数据分析流程中数据预处理是保证最终分析结果正确的关键性步骤，特别是其中的数据预处理步骤。采集存入Excel表中的原始数据可能存在数据不完整、异常值和不同数据表中数据不一致的现象。例如商品的单价是空值、库存为负值、不同工作表中同一种商品的单价不一致，我们将这样的数据称为“脏数据”，脏数据可能会影响数据分析的结果，得出不正确的结论。所以在数据分析前要先进行数据清洗。数据清洗将保证数据的完整性和一致性。2.5.1数据格式转换采集到Excel中的数据可能存在数据类型不一致的问题，例如，库存数量是数字，但是该字段的格式是文本型的，将无法使用函数统计总的库存数量。所以首先要认真检查每个字段的格式，对于不合适的格式要进行格式的转换。例如，在导入的如图所示的“订单信息”工作表中，“交易时间”显示不正确、“结算金额”数据类型是文本型，可以参与四则运算，但是不能参与函数运算，这将导致如图所示的使用求和函数SUM错误。因此在数据统计分析前首先确定数据类型，完成单元格格式的设定。操作步骤如下。①

设置“交易时间”显示格式。选择B2:B4的单元格区域。在“开始”选项卡的“数字”选项组中，选择“数字格式”下拉列表中的“长日期”，如图所示，即可显示如图所示的正确效果。②文本型“结算金额”转换成数字型。选择D2单元格，按下【Ctrl+Shift+↓】组合键，选中D2单元格向下的所有数据区域。D列左侧会出现一个智能标记图标，单击智能标记图标右侧的下拉按钮，选择“转换为数字”命令，如图所示，即可把文本转换数字，转换成数字后是右对齐的显示形式。此时可以使用SUM函数求出如图所示的总结算金额。2.5.2删除空行和多余空格某些工作表中可能包含多个空行，统计分析数据时可能产生不准确的结果。逐一删除不仅耗时，还可能会遗漏某些空行。可以将工作表中的空行进行批量删除。操作步骤如下。①选中包含空行的单元格区域，如图所示的单元格区域A1:C16。②在“开始”选项卡的“编辑”选项组中，单击“查找和选择”按钮，在下拉选项中选择“定位条件”命令。③在打开的“定位条件”对话框中选择“空值”单选按钮，如图所示。单击“确定”按钮后，选中了数据区中的全部空白单元格④在“开始”选项卡的“单元格”选项组中，单击“删除”按钮。执行后的效果如图所示，所有空行全部被删除了。2.5.3缺失的数据处理

在Excel工作表中，缺失的数据内容会显示为空白单元格。此时不仅需要将缺失数据检查出来，还需要选择合适的处理方法将缺失数据对数据分析的影响降到最小，保障数据分析的可靠性。如果当数据量足够大，删除缺失数据不会影响数据分析结果时，可以直接删除缺失的数据记录；当删除缺失数据会影响数据分析结果，可以使用平均值来替换缺失的值。①在B14单元格计算出平均值，8个月的平均值为85。②在“开始”选项卡的“编辑”选项组中，单击“查找和选择”按钮，在下拉选项中选择“定位条件”。在打开的“定位条件”对话框中选择“空值”单选按钮，单击“确定”按钮后，选中了数据区中的空白单元格，如图所示，表示缺失了4个月的销售量。③保持空值选中状态，输入平均值85，按下【Ctrl+Enter】组合键，完成全部空值用平均值85替换，结果如图所示。2.5.4重复数据的处理重复数据通常是指完全相同的记录，即每个字段列都相同。重复记录通常是重复输入造成的错误，会影响数据分析结果的准确性。例如图中有三行记录完全相同，当统计商品的总数量时就会发生错误。①选择数据区域的任意一个单元格，在“数据”选项卡的“数据工具”选项组中，单击“删除重复项”按钮，打开“删除重复项”对话框。②在对话框中选择重复数据所在的字段列。如果要求删除所有字段内容都完全相同的记录，就要把所有列都勾选上；如果要删除的是某些列内容相同的记录，那么只需要勾选相应的列字段即可，如图所示。③单击“确定”按钮，立即得到删除重复项之后的数据清单，删除的行会自动由下方的数据行填补，并且不会影响数据表以外的其他区域。效果如图所示。2.5.5错误数据的处理用户录入数据时可能会产生录入错误，在数据分析前要尽可能发现不在正常范围内的数据异常值，并提示用户录入数据不正确，及时改正。例如，可以利用“条件格式”功能限定商品单价大于0，如果录入负值或零值则系统会提示输入错误；同样的方法也可以用于商品的库存数量，如果库存数量录入为负值，系统将及时提示数据录入者，这样在一定范围内避免了“脏数据”，为数据分析提供了高质量的数据。为了保证系统录入数据的正确性，Excel支持对单元格进行数据验证，以限定单元格中输入数据的类型和范围，并且能够在数据录入操作过程中及时给出提示信息。数据验证设置主要包括3部分内容。1.验证条件设置验证条件设置用于设定单元格内容的数据类型和范围。Excel支持的数据类型有：任何值、整数、小数、序列、日期、时间和文本长度，如图所示。范围条件需要根据用户所选择的数据类型进行相应的设置，范围条件可以是介于、未介于、等于、不等于、大于、小于、大于或等于、小于或等于，如图所示。2.输入信息设置输入信息设置是针对用户数据录入过程中的信息提示。当用户向单元格输入数据时，系统会自动显示输入信息设置所设定的提示信息。3.出错警告设置出错警告设置主要针对用户录入错误数据时，系统按照出错警告的设置内容向用户显示提示值息。【例2-5】对商品表中的数量所在列设置数据验证，只能输入范围是大于0的整数；在数据录入过程中，系统提示标题为“数量录入”内容为“有效范围：大于0”的输入信息；若发生录入错误，系统弹出标题为“数量录入错误”内容为“数量大于0！”的出错警告信息。①在数量列中选定需要设置数据验证的单元格或单元格区域。②在“数据”选项卡的“数据工具”选项组中，单击“数据验证”按钮，选择“数据验证”命令，打开“数据验证”对话框。③在“设置”选项卡中，通过“允许”下拉列表框指定数据类型为“整型”；通过“数据”下拉列表框指定需要满足的范围条件是大于最小值0，如图所示。④在“输入信息”选项卡中，勾选“选定单元格时显示输入信息”复选框，然后指定标题为“数量录入”；输入信息的内容为“有效范围：大于0”，如图所示。⑤在“出错警告”选项卡中，指定标题为“数量录入错误”，错误信息的内容为“数量大于0！”，如图所示。⑥单击“确定”按钮，完成设置。在设置了数据验证的单元格中输入信息时，系统会给出如图所示的提示信息；如果输入了无效数据，当离开该单元格时会弹出如图所示的出错警告信息。如果在设置了验证条件后，未设置出错警告信息，则默认显示如图所示的系统提示信息“此值与此单元格定义的数据验证限制不匹配”。2.5.6数据表结构变化1. 行列转换根据数据分析的目的，有时需要将数据表的行列进行转换，然后再对数据进行分析，可以采用转置来实现，操作步骤如下。①选中A1:C7单元格区域，如图所示。在“开始”选项卡的“剪贴板”选项组中，单击“复制”按钮。②选定目的单元格或单元格区域，单击“粘贴”按钮下部区域，选择“选择性粘贴”，将打开如图所示的“选择性粘贴”对话框，勾选“转置”复选框，完成如图所示的行和列相互交换。A10:G12单元格区域是A1:C7单元格区域转置后的结果。2.列拆分在采集到的数据表中，一个字段列中可能包含了多种信息，例如，如图所示的商家地址中包含了省份、城市、区等，如果需要对省份或城市进行统计分析时，就需要对字段数据进行拆分。操作步骤如下。①选中地址所在的B列。②在“数据”选项卡的“数据工具”选项组中，单击“分列”按钮，打开“文本分列向导”对话框（共3步），分别选择原始数据类型、具体的分隔符、列数据格式等，然后单击“完成“按钮。从图中的地址字段列中可以看出地址包含文字长度不相同，但是有空格分隔，可以勾选如图所示的“空格”复选框来拆分。③地址被拆分成3列，重新为每一列指定列名，如图所示第3章商务数据分析计算式3.1公式的概念公式是对工作表中的数据进行计算的有效操作。公式以等号（=）开头，后面跟一个表达式。例如，“=2*B2+5”3.1.1公式的组成公式中的表达式是由常量、单元格引用、函数、运算符和括号构成的。常量：直接输入到公式中的数字或文本，例如，数值123或文本“计算机”。单元格引用：引用某一个单元格或单元格区域中的数据，例如，B2或A2:D5。函数：系统提供的函数，例如，求和函数SUM(C3:E3)。运算符：连接公式中常量、单元格引用、函数的特定计算符号。括号：控制公式中的计算顺序。类型运算符功能应用举例引用运算符区域运算符（：）、联合运算符（，）用于对单元各区域进行合并计算。将多个引用合并为一个引用=SUM(A1:A2,D1:D2)实现计算A1+A2+D1+D2算术运算符+（加）、﹣（减）、*（乘）、/（除）、%（百分比）、^（乘方）完成数学运算，运算结果为数值=2^3+6/2结果是11文本运算符&（文本连接）可以连接一个或多个文本字符串=“Hello”&“World”结果是“HelloWorld”比较运算符=（等于）、>（大于）、<（小于）、>=（大于或等于）、<=（小于或等于）、<>（不等于）比较两个值之间的大小关系，运算结果为逻辑值TRUE或FALSE=5＞3结果是TRUE运算符的优先级为：引用运算符

算术运算符

文本运算符

比较运算符例如:公式1：=5>3+1按照运算符的优先级顺序，先计算3+1，结果为4；然后再计算5>4，结果为TRUE。公式2：=(5>3)+1先计算括号内的5>3，结果为TRUE（即1）;然后再计算1+1，结果为2。3.1.2单元格引用1.相对引用单元格相对引用的格式为：列号行号。例如，A1。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列复制公式，引用会自动调整。默认情况下，公式中使用相对引用。2.绝对引用单元格绝对引用的格式为：$列号$行号。例如，$A$1。绝对引用总是引用指定位置的单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列复制公式，绝对引用将不作调整。3.混合引用单元格混合引用的格式为：$列号行号（绝对列和相对行）或者是列号$行号（相对列和绝对行）。例如，$A1、A$1。如果公式所在单元格的位置改变，则相对引用部分改变，而绝对引用部分不变。如果多行或多列复制公式，相对引用自动调整，而绝对引用不作调整。提示

【F4】键是引用方式转换的快捷键。连续按下【F4】键，按照相对引用

绝对引用

相对列绝对行

绝对列相对行

相对引用

…循环变化。4.外部引用（1）引用不同工作表中的单元格如果需要引用同一工作簿中其他工作表上的单元格的数据，需要在单元格前加上工作表的名称和感叹号“！”。引用的格式为：=工作表名称!单元格引用。例如，需要引用工作表名为“sheet1”中A2单元格，则输入的公式为“=sheet1!$A$2”（2）引用不同工作簿中的单元格如果需要引用不同工作簿中某一工作表的单元格，需要包含工作簿的名称。引用的格式为：=[工作簿名称]工作表名称!单元格引用。5.三维引用如果需要引用同一工作簿中多个工作表上的同一个单元格的数据，可以采用三维引用的方式。使用引用运算符“：”指定工作表的范围。三维引用的格式为：工作表名称1:工作表名称n!单元格引用。例如，Sheet1:Sheet3!B2、Sheet2:Sheet5!B2:G6。【例3-1】Sheet1~Sheet4这四个工作表中B1单元格中分别存储了第一季度~第四季度的销量，计算这四个季度的销量总和并存入Sheet5的B1单元格。在Sheet5的B1单元格中输入公式“=SUM(Sheet1:Sheet4!B1)”。含义是计算Sheet1、Sheet2、Sheet3和Sheet4这四个工作表中B1值的总和3.2公式编辑3.2.1手动输入公式手动输入是将公式中的全部内容通过键盘输入的方法，手动输入公式的操作步骤如下：①选择需要输入公式的单元格。②输入“=”等号。③输入公式表达式。公式表达式通常由单元格引用、常数、函数、运算符和括号组成。④公式输入完毕后，按回车键完成输入；或者单击编辑栏上的输入按钮完成输入。公式输入结束后，包含公式的单元格内会显示公式的计算结果；而公式本身则显示在编辑栏中。当组成公式的单元格的内容发生变化时，公式的计算结果也会随之自动变化。提示公式必须以“=”开始，否则公式将被识别为一个字符串。单元格中显示公式计算结果，编辑栏中显示公式表达式。【例3-2】计算商品的应付金额。

在E3单元格中输入公式“=C2*D2”，计算结果为60.75。3.2.2复制公式多数情况下某列或某行会采用相同的计算方法，不需要逐列或逐行输入公式，可以通过复制公式的方法来快速完成公式的输入。可以使用自动填充的方法复制公式，也可以使用“复制与粘贴”的方法复制公式。1.自动填充的方法复制公式①选择需要复制公式的E2单元格。②将鼠标放置到E2单元格右下角填充柄处，当鼠标指针变成十字形状时，按下鼠标拖曳至所需要的单元格处释放鼠标，结果如图所示。当数据行较多时，可以双击填充柄自动复制公式至最后一行。2.“复制与粘贴”的方法复制公式通过“复制与粘贴”操作可以复制公式。在复制公式时，单元格引用会根据不同的引用类型而变化。①

选择需要复制公式的单元格。②

在“开始”选项卡的“剪贴板”选项组中，单击“复制”按钮。③

选择目标单元格，单击“粘贴”按钮即可复制公式及格式.【例3-3】计算男性和女性的购买比例。分析：首先分别计算出男性和女性三个月总购买数量，然后再分别计算出男性和女性的购买比例。①计算出男性和女性总购买数量。在B5单元格中输入公式“=B2+B3+B4”，计算出男性购买总数量，复制公式至C5单元格，计算出女性购买总数量。②计算出男性和女性的购买比例。在B8单元格中输入公式“=B5/(B5+C5)”，其中的单元格均为相对引用，计算出男性购买比例，设置显示格式为“百分比”，小数位数2位，结果是37.93%。复制公式至C8单元格，计算出女性购买比例，显示出“100.00%”结果显然不对，B8单元格中的公式“=B5/(B5+C5)”，其中的单元格在复制时发生了改变，复制后为“=C5/(C5+D5)”，如图所示。可以看到D5单元格中无数据，所以产生了错误。正确的方法是在B8单元格中输入公式“=B5/($B$5+$C$5)”,复制到C8单元格后公式为“=C5/($B$5+$C$5)”，其中公式中的$B$5、$C$5是单元格绝对引用，复制后不变，正确的结果如图3-7(b)所示。

提示输入单元格相对地址后，按【F4】键可以快速转换成绝对地址。3.3函数输入3.3.1手动输入函数Excel中有公式记忆式键入的功能，用户输入公式时会出现备选函数列表，操作步骤如下：①输入等号（=）。②输入函数名称开始的几个字母，此时显示一个动态列表，其中包含了与用户输入字母匹配的有效函数名。③双击需要的函数，在“（”后，输入以逗号分隔的各个参数；或者选择单元格或单元格区域作为参数。④输入“）”，然后按回车键或单击编辑栏中的确认按钮，完成函数输入。如果单击编辑栏中的取消按钮，则放弃函数输入。单元格中显示函数计算的结果公式框中显示函数表达式类型含义更正方法#####列宽不够增加列宽或缩小字号。#DIV/0!除数为0检查输入公式中除数是否为0或引用了空白单元格或值为0的单元格作为除数。#N/A数值对函数或公式不可用检查公式中引用的单元格的数据，并输入正确的内容。#NAME?无法识别公式中的文本检查输入文本时是否使用双引号；区域引用中是否缺少了冒号：。#NULL!区域运算符不正确使用区域运算符（：）引用连续的单元格区域；使用联合运算符（,）引用不相交的两个区域。#NUM!无效数据检查数字是否超出限定范围，或函数中的参数是否正确。#REF!单元格引用无效检查引用的单元格是否已被删除。#VALUE!参数或操作数的类型错误检查公式、函数中使用的运算符或参数是否正确。3.4常用函数3.4.1数学函数

1.INT函数语法格式：INT(number)函数功能：返回给定数值向下取整为最接近的整数。2.MOD函数语法格式：MOD(number,divisor)函数功能：返回两数相除的余数。参数说明：参数number是被除数，divisor为除数。结果的正负号与除数相同如果参数divisor为0，将会导致错误返回值#DIV/0!。3．ROUND函数语法格式：ROUND(number,num_digits)函数功能：返回数值四舍五入的结果。参数说明：

参数number为要四舍五入的数值num_digits为指定保留的小数位数。如果num_digits为0，则取整到最接近的整数。4.SUM函数语法格式：SUM(number1,number2,...)函数功能：返回参数中所有数字之和。参数说明：如果参数是一个数组或引用，则只计算其中的数字，空白单元格、逻辑值或文本将被忽略。5.SUMPRODUCT函数语法格式：SUMPRODUCT(array1,array2,...)函数功能：将数组间对应的元素相乘，并返回乘积之和参数说明：数组参数必须具有相同的长度。将非数值型的数组元素作为0处理【例3-4】计算顾客购买水果的总价钱。分析：B列中是水果的单价，C列是购买的数量，总价是每种水果的单价乘以数量的总和。选中B6单元格输入公式：“=SUMPRODUCT(B2:B4,C2:C4)”

计算B2

C2+B3

C3+B4

C46.SUMIF函数语法格式：SUMIF(range,criteria,[sum_range])函数功能：返回满足条件的单元格区域中数字之和。参数说明：

参数range是用于条件判断的单元格区域；criteria是计算的条件，其形式可以为数字、表达式或文本；sum_range是需要求和的实际单元格。

只有当range中的单元格满足criteria设定的条件时，才对sum_range中对应的单元格求和。

如果省略sum_range参数，则对range中的单元格求和。【例3-5】按照销售员姓名汇总销售额。①

在E2单元格中输入销售员姓名。例如，“王宏”。②

在F2单元格中输入公式“=SUMIF(B2:B7,E2,C2:C7)”，其中：B2:B7是判断条件的单元格区域；E2单元格是条件；C2:C7单元格区域是求和区域。含义是如果B2:B7的销售员中有E2中指定的销售员，则对其销售额进行求和。7.多条件求和函数SUMIFS语法格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)函数功能：返回满足多个条件的单元格区域中数字之和。参数说明：参数sum_range是求和的单元格区域。参数criteria_range1是用于判断criteria1条件的单元格区域；criteria_range2是用于判断criteria2条件的单元格区域。只有同时满足criteria1、criteria2…设定的条件时，才对sum_range中对应的单元格求和。【例3-6】按照商品和厂家汇总销售量。①在G2单元格中输入公式“=SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,F2)”，其中：$C$2:$C$6是求和区域；$A$2:$A$6是判断条件1的单元格区域，E2单元格是条件1；$B$2:$B$6是判断条件2的单元格区域，F2单元格是条件2。含义是如果A2:A6的商品是E2中指定的商品，并且B2:B6的厂家是F2中指定的厂家，则对其销售量进行求和。②将G2单元格中的公式复制到G4单元格，为了保证复制后单元格引用正确，函数中的$C$2:$C$6、$A$2:$A$6、$B$2:$B$6均采用绝对地址，复制后不会改变。3.4.2统计函数1.AVERAGE函数语法格式：AVERAGE(number1,number2,...)函数功能：返回参数的算术平均值。参数说明：如果参数是一个数组或引用，则只计算其中的数字，空白单元格、逻辑值或文本将被忽略。2.AVERAGEIF函数语法格式：AVERAGEIF(range,criteria,[average_range])函数功能：返回满足条件的单元格算术平均值。参数说明：参数range是用于条件判断的单元格区域；criteria是计算的条件，其形式可以为数字、表达式或文本；average_range是需要求平均值的实际单元格。只有当range中的单元格满足criteria设定的条件时，才对average_range中对应的单元格求和。如果省略average_range，则对range单元格区域求平均值。【例3-7】计算厂家的平均销售量。①在E2、E3单元格中分别输入厂家名称“天天食品公司”和“美味食品公司”。②在F2单元格中输入公式“=AVERAGEIF($B$2:$B$6,E2,$C$2:$C$6)”，其中：$B$2:$B$6是判断条件的单元格区域；E2单元格是条件；$C$2:$C$6单元格区域是求平均值的区域。含义是如果$B$2:$B$6的厂家中有E2中的厂家的名称，则对其销售量求平均值。③将F2单元格中的公式复制到F3单元格，完成计算。3.多条件求平均值函数AVERAGEIFS语法格式：AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)函数功能：返回满足多个条件的单元格区域中数字的平均值。参数说明：参数average_range是求平均值的单元格区域。参数criteria_range1是用于判断criteria1条件的单元格区域；criteria_range2是用于判断criteria2条件的单元格区域。只有同时满足criteria1、criteria2…设定的条件时，才对average_range中对应的单元格区域求平均值。3.COUNT函数语法格式：COUNT(value1,value2,...)函数功能：计算包含数字的单元格以及参数列表中数字的个数。参数说明：如果参数为数字、日期则被计算在内；文本、逻辑值不被计算在内。相关函数（1）COUNTA：统计非空单元格的个数。（2）COUNTBLANK：统计空单元格的个数。4.COUNTIF函数语法格式：COUNTIF(range,criteria)函数功能：统计符合给定条件的单元格个数。参数说明：参数range是要统计的单元格区域；criteria是统计条件，其形式可以为数字、表达式或文本。【例3-8】统计不同性别的人数。①

分别在D2、D3单元格中输入性别“男”和“女”。②

在E2单元格中输入公式“=COUNTIF(B2:B8,D2)”

：B2:B8是统计的单元格区域；D2单元格是条件。含义是统计B2:B8的学生中与D2中性别相同的男生人数。③

将E2单元格中的公式复制到E3单元格，自动计算出女生人数。6.多条件计数函数COUNTIFS语法格式：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...)函数功能：统计符合多个给定条件的单元格个数。参数说明：参数criteria_range1，criteria_range2是要统计的单元格区域；criteria1，criteria2是统计条件，其形式可以为数字、表达式或文本。7.MAX函数语法格式：MAX(number1,number2,...)函数功能：返回参数列表中的最大值。8.求最小值MIN语法格式：MIN(number1,number2,...)函数功能：返回参数列表中的最小值。6.MEDIAN函数语法格式：MEDIAN(number1,number2,...)函数功能：返回参数列表中的中值。中值不同于平均值，中值又称中位数，其值是一组有序数中居于中间位置的数，如果有偶数个数，则取最中间两个数的平均值。数据{15、2、3、4}的中值是3.5；该组数据的平均值是6。10.众数函数MODE语法格式：MODE(number1,[number2]...)函数功能：返回参数列表中出现频率最高的数。【例3-9】统计水果的价格平均值、价格中位数、价格众数、最高价格和最低价格。①计算价格平均值。在E2单元格中输入公式“=AVERAGE(B2:B8)”②计算价格中位数。在E3单元格中输入公式“=MEDIAN(B2:B8)”③计算价格众数。在E4单元格中输入公式“=MODE(B2:B8)”④计算最高价格。在E6单元格中输入公式“=MAX(B2:B8)”⑤计算最低价格。在E7单元格中输入公式“=MIN(B2:B8)”11.排序函数RANK语法格式：RANK(number,ref,[order])函数功能：返回一个数字在数字列表中的排位顺序。参数说明：number：需要排位的数字或单元格。ref：数字列表数组或对数字列表的引用，是一个数组或单元格区域，用来说明排位的范围。其中的非数值型参数将被忽略。order：指明排位的方式。为0或者省略时，对数字的排位是基于降序排列的列表；不为0时，对数字的排位是基于升序排列的列表。【例3-10】计算销售量的排名。在D2单元格中输入公式“=RANK(C2,C2:C11)”；复制D2单元格中的公式至D3~D11单元格中，可以看出结果出现了不同销售数量具有相同的销售排名的错误情况，原因是当公式复制到D3单元格时改变为“=RANK(C3,C3:C12)”，注意范围变化为：C3:C12，而正确的范围是应该为C2:C11，所以范围参数需要使用单元格的绝对引用法，即$C$2:$C$11；或者单元格的混合引用法，即C$2:C$11。这样在复制单元格时，排序的范围就不会改变了。重新在D2单元格中输入公式“=RANK(C2,$C$2:$C$11)”；复制D2单元格中的公式至D3~D11单元格中

3.4.3逻辑函数1.AND函数语法格式：AND(logical1,logical2…)函数功能：返回参数列表逻辑“与”的结果。当所有参数均为TRUE时，返回TRUE（真）；只要有一个参数为FALSE时，返回FALSE（假）。参数说明：参数必须是逻辑值TRUE或FALSE。如果指定的单元格区域包含非逻辑值，则AND函数将返回错误值#VALUE!。2．OR函数语法格式：OR(logical1,logical2…)函数功能：返回参数列表逻辑“或”的结果。只要有一个参数为TRUE时，返回TRUE；所有参数均为FALSE时，返回FALSE。参数说明：参数必须是逻辑值TRUE或FALSE。如果指定的单元格区域包含非逻辑值，则OR函数将返回错误值#VALUE!。3．NOT函数语法格式：NOT(logical)函数功能：返回参数列表逻辑“非”的结果。当参数为TRUE时，返回FALSE；当参数为FALSE时，返回TRUE。4.IF逻辑函数语法格式：IF(logical_test,value_if_true,value_if_false)参数说明：logical_test：逻辑表达式。逻辑表达式的结果可能是TRUE（真）或FALSE（假）。value_if_true：当Logical_test逻辑表达式为TRUE（真）时，函数的返回值。value_if_false：当Logical_test逻辑表达式为FALSE（假）时，函数的返回值。【例3-11】评价年度优秀销售员。①在G2单元格中输入公式“=IF(AND(C2>=4.5,D2>=4.5,E2>=4.5,F2>=4.5),"年度优秀销售员","")”，其中：IF的第一个参数是：AND(C2>=4.5,D2>=4.5,E2>=4.5,F2>=4.5)表示当C2、D2、E2和F2单元格的值均大于等于4.5时结果为TRUE，IF将返回“年度优秀销售员”；否则为FALSE，IF将返回空字符串。②复制G2单元格中的公式至G3~G5单元格中【例3-12】评价年度优秀销售员和季度优秀销售员。①在G2单元格中输入公式“=IF(AND(C2>=4.5,D2>=4.5,E2>=4.5,F2>=4.5),"年度优秀销售员",IF(OR(C2>=4.5,D2>=4.5,E2>=4.5,F2>=4.5),"季度优秀销售员",""))”。其中：IF的第一个参数是：AND(C2>=4.5,D2>=4.5,E2>=4.5,F2>=4.5)表示当C2、D2、E2和F2单元格的值均大于等于4.5时结果为TRUE，IF将返回“年度优秀销售员”；否则为FALSE，则执行嵌套的IF函数，OR(C2>=4.5,D2>=4.5,E2>=4.5,F2>=4.5)表示当C2、D2、E2和F2单元格的任意一个值大于等于4.5时结果为TRUE，将返回“季度优秀销售员”；否则返回空字符串。②复制G2单元格中的公式至G3~D5单元格中4.3.4文本函数和查找函数1.FIND函数语法格式：FIND(find_text,within_text,[start_num])函数功能：返回指定字符从指定位置开始在一个文本字符串第一次出现的位置。参数说明：find_text是要查找的文本。within_text包含要查找文本的文本。start_num是可选项。指定开始进行查找的位置。如果省略start_num，其值为1。2.LEN函数语法格式：LEN(text)函数功能：返回指定字符串的字符个数。字符串中的空格作为字符进行计数。【例3-13】检查客户手机号码的位数是否正确①

在C2单元格中输入公式“=IF(LEN(B2)=11,"","错误位数")”其中：LEN(B2)=11是检查B2单元格的长度是否等于11，等于则返回空值，不等于则返回“错误位数”。②

复制C2单元格公式至G3~C5单元格中。3.LEFT函数语法格式：LEFT(text,[num_chars])函数功能：返回文本字符串中第一个字符或前几个字符参数说明：text：要提取文本的字符串。num_chars：指定从左提取字符的数量。如果省略num_chars，则其值为1。4.RIGHT函数语法格式：RIGHT(text,[num_chars])函数功能：返回文本字符串中最后一个或多个字符。参数说明：text：要提取的文本字符串。num_chars：指定从右提取的字符的数量，如果省略，其值为1【例3-14】从销售部门字段列拆分出所在销售部字段列和销售组字段列①在C2单元格中输入公式“=LEFT(A2,3)”，表示左取3个字。复制C2单元格公式至C3~C6单元格中。②在D2单元格中输入公式“=RIGHT(A2,2)”，表示右取2个字。复制D2单元格公式至D3~D6单元格中。5.MID函数语法格式：MID(text,start_num,num_chars)函数功能：返回文本字符串中从指定位置开始的指定数目的字符。参数说明：text：要提取的文本字符串。start_num：文本中要提取的第一个字符的位置。num_chars：指定从文本中提取字符的个数。6.REPLACE函数语法格式：REPLACE(old_text,start_num,num_chars,new_text)函数功能：使用新文本字符串替换旧文本字符串中指定起始位置和个数的文本。参数说明：old_text：旧字符文本。start_num：开始替换的位置。num_chars：指定替换字符的个数。new_text：新字符文本。【例3-15】隐藏客户手机号码的中间5位。①

将A2:A5单元格区域复制到D2:D5单元格区域中。②

在E2单元格中输入公式“=REPLACE(B2,4,5,"*****")”，其中：4是B2单元格的起始位置；5是要替换的个数；“*****”是替换的新文本。含义是将B2单元格从第4位开始的5位数字串替换为5个“*”。7.删除首尾空格函数TRIM语法格式：TRIM(text)函数功能：删除文本字符串中的前导空格和尾部空格。3.VLOOKUP函数VLOOKUP函数是个频繁使用的函数，可以进行灵活的查询操作。语法格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)函数功能：在指定的单元格区域中查找值，返回该值同一行的指定列中所对应的值。参数说明：lookup_value：需要在查找范围第一列中查找的数值，这个值可以是常数也可以是单元格引用。如果这个值不在第一列中，则函数返回错误。table_array：函数的查找范围，应该是大于两列的单元格区域。第一列中的值对应lookup_value要搜索的值，这些值可以是文本、数字或逻辑值。col_index_num：table_array中待返回匹配值的列序号，是一个数字，该数字表示函数最终返回的内容在查找范围区域的第几列。range_lookup：指定是精确匹配值还是近似匹配值。如果为TRUE或省略，则返回近似匹配值；如果为FALSE或0，则返回精确匹配值。【例3-16】按照客户姓名查询年龄。①在G2单元格中输入要查找的姓名，例如“刘丽”。②在H2单元格中输入公式“=VLOOKUP(G2,B2:E5,4,0)”，表示在B2:E5单元格区域中查找等于G2值，找到则返回第4列对应的值，注意，因为G2的值必须是查找范围的第一列，所以查找的范围是B2:E5而不可以是A2:E5；4是指查询区域B2:E5中的第4列即E列，而不是工作表的第4列；0表示精确匹配查找。提示VLOOKUP函数函数的第3个参数中的列号是需要返回的数据在查找区域中的第几列，而不是实际的列号。如果有多个满足条件的记录VLOOKUP函数默认只能返回第一个查找到的记录。9.INDEX函数语法格式一：INDEX(array,row_num,column_num)语法格式二：INDEX(reference,row_num,column_num,area_num)函数功能：返回指定的行与列交叉处的单元格引用。参数说明：array：单元格区域或数组常量；reference是对一个或多个单元格区域的引用。row_num：选择数组中的某行，函数从该行返回