Office高级应用（第2版 · 微课版 · 含OfficeAI助手）课件 第7章-数据的计算

第2篇Excel2016数据处理与分析

—数据的计算Office2016高级应用湖南师范大学·刘相滨引言基本表：基本资料表、商品信息表、上期库存表业务表：进货清单、销售清单、库存清单、销售统计问题数据一致性：商品编号要唯一，必须是“基本资料表”中已有的商品编号，并且名称、类别、品牌等信息必须与“商品信息表”中的相应信息一致数据计算：有一些数据需要通过现有的数据计算，例如，商品的成本进价、期末库存、库存金额、成本金额、利润金额、利润率等公式与函数！Excel的公式与函数提供了强大的计算功能，除了加、减、乘、除四则运算外，还提供了财务、金融、统计等方面的复杂数据计算进销存系统—各表间数据关系基本资料表商品信息表上期库存表进货清单销售清单库存清单销售统计商品类别及每类商品的品牌商品编号、名称、类别、品牌、最高进货价、最低进货价、进货批次数、最后进货日期、最后进货价、成本进价商品编号(名称、类别、品牌、规格)期末库存、成本进价、库存金额进货单号、商品编号(名称、…)进货价格、数量、金额、进货日期销售单号、商品编号(名称、…)数量、售价、金额、销售日期、销售方式商品编号(名称、…)进货数量、销售数量、期末库存、成本进价、库存金额商品编号、(名称、…)销售数量、销售金额、成本金额、利润金额、利润率、利润排名1类别、品牌2商品信息3商品数量等4销售数据本章任务任务1：在进货清单中，根据进货价格和数量计算金额，在销售清单中，根据销售价格和数量计算金额(公式)任务2：完善进货清单、销售清单，构造库存清单、销售统计，其中的商品编号、商品名称、商品类别、品牌、规格等信息必须与“商品信息表”工作表中的一致(函数，VLOOKUP)任务3：在库存清单中，根据进货清单计算进货数量，根据销售清单计算销售数量，然后结合上期库存表计算期末库存，结合成本进价，计算库存金额(函数，SUMIF，VLOOKUP)任务4：在销售统计表中按指定的统计时间区间，根据销售清单计算每种商品的销售数量和销售金额，然后结合成本进价计算成本金额，再计算出利润金额和利润率，最后按利润进行排名(函数，SUMIF，SUMIFS，RANK.EQ等)任务5：在商品信息表中，根据进货清单（和上期库存表）计算成本进价、最高进货价、最低进货价、进货批次数、最后进货日期和最后进货价(数组公式）内容提要第7章数据的计算7.1

公式7.2

函数7.3

数组公式7.1公式形式：=表达式与数学中的表达式类似，由操作数和运算符组成操作数可以是函数、单元格（区域）引用和常量。例如销售清单中计算金额：

=H2*G2 单元格引用方式进货清单中计算金额：=[@进货价格]*[@数量] 表格引用方式公式中的元素运算符：算术、关系、文本、括号、引用等运算符常量：值或字符串等常量数据，如“5”或者“中国北京”单元格(区域)引用：单元格、单元格区域、名称等表格引用：对表格中元素的引用=VLOOKUP([@商品编号],上期库存表,5,FALSE)+[@进货数量]-[@销售数量]函数调用：如“SUM(A1:A10)”运算符算术运算符：+、-、*、/、%、^（加、减、乘、除、百分比、乘方）关系运算符：比较两个数值的大小关系,结果为逻辑值：TRUE、FALSE=、>、<、>=、<=、<>（等于,大于,小于,大于等于,小于等于,不等于）文本连接运算符：连字符&，连接两个文本字符串以产生一串文本"North"&"wind",结果为"Northwind"引用运算符：用于对单元格区域的运算括号运算符：改变运算次序运算符优先级公式中，运算符之间有优先级关系不同优先级，按从高到低的顺序计算同一优先级，按从左至右的顺序计算运算符说明:区域运算符（空格）交叉运算符,联合运算符–负数（如–1）%百分比^乘方*

/乘和除+–加和减&连接两个文本字符串（串连）=<><=>=<>关系运算符使用公式输入公式以“=”起头，作为公式的标识复制公式使用填充柄注意单元格的引用形式相对引用形式（A2：B8）绝对引用形式（A$2，$A$2）表格数据的引用表格是工作表中包含相关数据的一系列数据行，是一个规则的二维表格区域，相当于数据库管理系统中的一个数据表第一行为标题行，每一列为一个字段，标题名为字段名其他行为记录行，每一行为一条记录标题行一条记录记录行一个字段字段名表格数据的引用可以使用表格名称引用表格的数据区域在编辑公式时，若输入表格名称+[，例如“商品信息表[”，则Excel会给出提示信息：引用表格中的某一列数据：表格名[列名]引用表格的全部数据：表格名[#全部]引用表格的标题行：表格名[#标题]引用编辑单元格所在的当前行：表格名[@列名]表格中的公式自动成为表格公式，应用于每条记录=COUNTIF(商品信息表[商品编号],商品信息表[@商品编号])为什么要使用公式？公式复制：公式的价值不只是计算，而是构建计算模型，复制公式就是复制计算模型，当公式所在位置发生变化时，公式中的相对引用会随之发生变化，从而对不同的数据对象应用相同的计算模型进行计算计算自动更新：使用公式来处理数据的优越性在于，当公式中引用的单元格的数据发生变化时，系统将会重新计算，自动更新计算结果课堂实践：任务1任务1：在进货清单中，根据进货价格和数量计算金额，在销售清单中，根据销售价格和数量计算金额。(公式)7.2函数函数，本质上是为了解决某些通过简单公式运算不能处理的复杂问题而预先编写的特殊公式组成函数名称：用于引用参数：待处理的数据，增加函数的通用性和灵活性一般形式：函数名([参数1[,参数[,…]]])函数对一个或多个参数值执行运算，并返回一个或多个结果值VLOOKUP([@商品编号],商品信息表,2,FALSE)函数分类时间与日期函数工程函数财务函数信息函数逻辑函数查找和引用函数数学和三角函数统计函数文本函数其它查找函数VLOOKUP函数在指定数据区域的最左列中查找指定值，找到后，返回该行上指定列单元格中的值=VLOOKUP(“SD-KS-003”,A2:D20,2,false)在A2:D20的最左列（商品编号）中查找SD-KS-003，找到后，返回该行上第2列(商品名称)中的值课堂实践：任务2任务2：完善进货清单、销售清单，构造库存清单、销售统计，其中的商品编号、商品名称、商品类别、品牌、规格等信息必须与“商品信息表”工作表中的一致。(函数，VLOOKUP)课堂实践：任务3任务3：在库存清单中，根据进货清单计算进货数量，根据销售清单计算销售数量，然后结合上期库存表计算期末库存，结合成本进价，计算库存金额。(函数，SUM，SUMIF，VLOOKUP)求和函数SUM函数返回指定各参数所对应单元格（区域）内的所有单元格数值之和参数：单元格（区域）引用、数组、常量、另一个函数的结果，或它们组成的表达式=SUM(I2:I1421)或=SUM(销售统计[销售金额])条件求和函数SUMIF(range,criteria,[sum_range])对区域中符合指定条件的值求和range：待求和计算的单元格（区域）criteria：判断条件，其形式可以为数字、表达式、单元格引用、文本或函数sum_range：需要求和的实际单元格。如果省略，会对在range参数中指定的单元格（即满足criteria条件的单元格）求和=SUMIF(B2:B25,">5")B2:B25单元格区域中，对大于5的数值进行求和库存清单进货数量销售数量期末库存=SUMIF(进货清单[商品编号],[@商品编号],进货清单[数量])=SUMIF(销售清单[商品编号],[@商品编号],销售清单[数量])=VLOOKUP([@商品编号],上期库存表,5,FALSE)+[@进货数量]-[@销售数量]库存清单成本进价=(SUMIF(进货清单[商品编号],[@商品编号],进货清单[金额])+VLOOKUP([@商品编号],上期库存表,7,FALSE))/(SUMIF(进货清单[商品编号],[@商品编号],进货清单[数量])+VLOOKUP([@商品编号],上期库存表,5,FALSE))课堂实践：任务4任务4：在销售统计表中按指定的统计时间区间，根据销售清单计算每种商品的销售数量和销售金额，然后结合成本进价计算成本金额，再计算出利润金额和利润率，最后按利润进行排名

(函数，SUMIF，SUMIFS，RANK.EQ等)多条件求和函数SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)sum_range：待求和计算的单元格（区域）criteria_range1：用于对条件criteria1进行判断的单元格区域criteria1：作用于区域criteria_range1的判断条件criteria_range2,criteria2,…：最多允许127个如果需要对区域A1:A20中符合以下条件的单元格的值求和：B1:B20中的相应数值大于0且C1:C20中的相应数值小于10，则可以使用以下公式：=SUMIFS(A1:A20,B1:B20,">0",C1:C20,"<10")销售统计对销售统计工作表中各商品的销售情况按时间区间进行统计=SUMIFS(销售清单[数量],销售清单[商品编号],[@商品编号],销售清单[销售日期],">="&$B$2,销售清单[销售日期],"<="&$E$2)排位函数RANK.EQ函数等同于Office低版本中的RANK函数返回一个数值在一组数值中的排位，如果多个值具有相同的排位，则返回该组数值的最高排位按“利润金额”计算各商品的利润排位值条件计数函数COUNTIF函数对区域中满足单个指定条件的单元格进行计数统计商品信息表的每一种商品的进货批次数课堂实践：任务5任务5：在商品信息表中，根据进货清单（和上期库存表）计算成本进价、最高进货价、最低进货价、进货批次数、最后进货日期和最后进货价。(数组公式）7.3数组公式数组：单元的集合或是一组处理的值的集合数组公式：一个以数组为参数的公式。执行这个单一的公式，产生多个结果，并将每个结果显示在相应的单元格中例如，在为进货清单和销售清单计算金额时，采用的方法是先构造计算第一个商品的公式模型，然后将其复制到其它单元格，完成其它商品的金额计算，而如果利用数组公式，则只需先选定所有需要计算金额的单元格，书写一个数组公式即可完成所有商品金额的计算7.3数组公式数组公式本质上为多值公式，与单值公式最大的不同就是能够产生多个结果，这对于需要将满足特定条件的多个结果作为其它函数的参数进行进一步计算的情形是非常有用的例如，计算某个商品的最高进货价，需要先检索这个商品的所有进货价（多个结果），然后以此作为MAX函数的参数，得到最高进货价具体来说，数组公式的参数是数组，即输入有多个值（如所有的商品的数据）；输出结果可能是一个（如最高进货价），也可能是多个（如某个商品的所有进货价）7.3数组公式一个数组公式可以占用一个或多个单元格但要注意，在Excel的表格中，不能使用多单元格数组公式，即在多个单元格中定义一个共同的数组公式，因而需要先将表格转换为区域才能完成相关操作。因此，下面的介绍，是复制进货清单工作表，然后将其中的进货清单表格转化为单元格区域，再在此基础上进行的7.3数组公式由于一个数组被当成一个整体进行处理，如果需要对数组进行编辑、清除、移动、插入、删除等操作，必须先选取整个数组，然后进行相应的操作从而可以避免用户无意或有意破坏数据的完整性有些运算结果需要通过复杂的中间运算才能得到。例如，要计算某一商品的最高进价，利用单一的公式或操作无法得到，而利用数组公式却可以方便得到数组公式应用商品信息表中，需要根据进货清单(和上期库