1

2

4.1 Excel 实例介绍

4.2 Excel 中数据的输入

4.3 Excel 中函数与公式

4.4 Excel 中数组的使用

4.5 Excel 的函数介绍

3

实例背景： 张某在“淘宝网”开了一家网上商店，主要销售手

机、相机、 MP3 、 MP4 等数码产品 。随着店铺宣传的开展，小张的网店生意越来越红火，往来账务也随之增加，导致了小张越来越苦恼该如何管理好所有店铺中的信息数据，并对有效的数据进行分析。

如何使用 Excel 对他的商品销售记录进行数据分析？如何有效记录每天卖出的商品？如何计算每月获得的销售利润？如何得知什么货销售量好，容易赚钱？……

4

• 进货清单 — 主要记录每次店铺进货的商品相关信息

• 销售清单 —主要记录每次销售的记录

• 库存清单— 主要记录现库存中的存货情况

• 销售统计—用于统计一个阶段的销售情况

• 分类统计报表（图）—对各个品牌、各种商品类别进行详细的统计，用户可以根据具体的时间品牌或类别进行条件性查询。

• 商品资料—用于存放店铺中所销售商品的基本信息资料

• 客户资料—用于存储客户的信息资料

• 其他资料设置—用于存储其他的相关信息资料

• 相关财务表（贷款偿还表，项目投资表、固定资产折旧表）

5

• 数据输入

• 函数与公式

• 筛选与排序

• 分类汇总

• 数据透视表（图）

6

4.2.1 自定义下拉列表输入

4.2.2 自定义序列与填充柄

4.2.3 条件格式

4.2.4 数据输入技巧

4.2.5 数据的舍入方法

7

作用： • 使用自定义下拉列表的方式进行数据的输入，

可以提高数据输入的速度和准确性。 建立方法：使用 “数据有效性” 命令来完成

• 普通方法设置：直接键入列表选项• 使用名称设置：使用名称建立列表选项

8

自定义序列：一组数据，可按重复方式填充列。 创建自定义序列有以下两种方式：

• 利用工作表中现有的数据项• 采用临时输入的方式

自定义序列可以包含文字或带数字的文本 注意：如果是包含数字的自定义序列，则需要进行格式

的设置，之后才能在单元格中输入序列项，选择列表并导入列表。

创建好自定义序列之后，采用填充柄进行填充能达到快速、有效的结果。

9

作用：通过设置数据条件格式，可以让单元格中的数据满足指定条件时就以特殊的标记（如：以红色、数据条、图标等）显示出来。

关于条件格式的设置主要通过“条件格式”对话框

10

1．特殊数据输入• 输入分数• 输入负数• 输入文本类型的数字• 输入特殊字符

2．快速输入大写中文数字3．自动超链接的处理

• 取消自动超链接• 关闭自动超链接 • 取消多个的超链接

11

1．舍入到最接近的倍数

2．货币值舍入

3．使用 INT 和 TRUNC 函数

4．舍入为 n位有效数字

5．时间值舍入

12

4.3.1 公式的概述

4.3.2 单元格的引用

4.3.3 创建名称及其使用

4.3.4 SUM 函数的应用

4.3.5 AVERAGE 函数的应用

4.3.6 IF 函数的引用

13

公式就是对工作表中的数值进行计算的式子 公式由操作符和运算符两个基本部分组成。

• 操作符可以是常量、名称。数组、单元格引用和函数等。•运算符用于连接公式中的操作符，是工作表处理数据的指令。

14

公式元素 运算符、单元格引用、值或字符串、函数其参数、括号

运算符 算术运算符 、逻辑运算符 、文本运算符 、引用运算符

运算符的优先级

15

相对引用• 总是以当前单元格位置为基准，在复制公式时，当前单元格改变

了，在单元格中引入的地址也随之发生变化。 • 相对地址引用的表示是，直接写列字母和行号，如 A1 ， D8 等。

绝对引用

• 在复制公式时，不想改变公式中的某些数据，即所引用的单元格地址在工作表中的位置固定不变，它的位置与包含公式的单元格无关

• 绝对地址引用的表示是，在相应的单元格地址的列字母和行号前加“ $”符号

16

混合引用• 公式中参数的行采用相对引用、列采用绝对引用，或者列采用相

对引用、行采用绝对引用，如 $A1 、 A$1 。

三维引用

• 引用工作簿中多个工作表的单元格 • 三维引用的一般格式为：工作表标签！单元格引用

循环引用• 一个公式直接或者间接引用了自己的值，即出现循环引用

17

在 Excel 中，可以通过一个名称来代表工作表、单元格、常量、图表或公式等。如果在 Exce

l 中定义一个名称，就可以在公式中直接使用它 • 名称的创建

• 名称的使用

18

功能：返回指定参数所对应的数值之和 格式： SUM （ number1 ， number2 ，……）

• number1 ， number2 等这些是指定所要进行求和的参数• 注意：函数中可以包含的参数个数为 1 到 30 个之间，参

数类型可以是数字、逻辑值和数字的文字表示等形式

SUMIF 函数：用于计算符合指定条件的单元格区域内的数值进行求和 ，格式为： SUMIF （ range ， criteria ， sum_range ）

• range 表示的是条件判断的单元格区域；• criteria 表示的是指定条件表达式；• sum_range 表示的是需要计算的数值所在的单元格区域

19

功能：返回指定参数所对应数值的算术平均数

格式： AVERAGE （ number1 ， number2 ，……）• number1 ， number2 等是指定所要进行求平均值的参数

注意：该函数只对参数的数值求平均数，如区域引用中包含了非数值的数据，则 AVERAGE不把它包含在内。

20

IF 函数是一个条件函数 格式： IF （ logical_test ， value_if_true ， value_if_false ）

• logical_test ：当值函数的逻辑条件• value_if_true ：当值为“真”时的返回值

• value_if_false ：当值为“假”时的返回值 功能：能为对满足条件的数据进行处理，条件满足则输出 val

ue_if_true ，不满足则输出 value_if_false

注意：在 IF 函数中三个参数中可以省略 value_if_true 或 value_if_false ，

但不能同时省略 在 IF 函数中使用嵌套函数，最多可用嵌套 7层

21

4.4.1 数组的概述

4.4.2 使用数组常数

4.4.3 编辑数组

4.4.4 数组公式的应用

22

数组就是单元的集合或是一组处理的值的集合 .

数组公式：输入一个单个的公式，它执行多个输入操作并产生多个结果。

一个数组公式可以占用一个或多个单元区域，数组的元素可多达 6500 个。

与单值公式的区别：它可以产生一个以上的结果 数组公式的创建

23

数组常量：输入数值的数组

常量可以是数字、文本、逻辑值和错误值等。 • 数字：其类型可以是整数型、小数型和科学计数法形式 • 文本：必须使用引号引起来 ，例如：“星期一”

同一个数组常量中可以使用不同类型的值 数组常量中的值必须是常量，不可以是公式 注意： 1. 数组常量不能含有货币符号、括号或百分比符号 2. 所输入的数组常量不得含有不同长度的行或列 数组常量可以分为一维数组与二维数组，常用逗号将一行内的元素分开，用分号将各行分开。

24

一个数组包含数个单元格，这些单元格形成一个整体，所以，数组中的单元格不能单独的进行编辑、清除和移动，也不能插入或删除单元格。在对数组进行操作（编辑、清楚、移动单元格，插入、删除单元格）之前，必须先选取整个数组，然后进行相应的操作。

25

4.5.1 财务函数

4.5.2 文本函数

4.5.3 日期与时间函数

4.5.4 查找与引用函数

4.5.5 数据库函数

4.5.6 其他类型函数

26

财务函数是财务计算和财务分析的专业工具，有了这些函数的存在，可以很快捷方便地解决复杂的财务运算，在提高财务工作效率的同时，更有效的保障了财务数据计算的准确性。

主要介绍以下几个财务函数： 1 、 PMT 函数

2 、 IPMT 函数 3 、 FV 函数 4 、 PV 函数 5 、 SLN 函数

27

功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额

格式： PMT(rate ， nper ， pv ， fv ， type)

• rate ：贷款利率• nper ：该项贷款的总贷款期限或者总投资期• pv ：从该项贷款（或投资）开始计算时已经入账的款项，

或一系列未来付款当前值的累积和• fv ：未来值，或在最后一次付款后希望得到的现金余额，

如果忽略该值，将自动默认为 0

• type ：一个逻辑值，用以指定付款时间是在期初还是在期末， 1 表示期初， 0 表示期末，其默认值为 0

28

功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期限内的利息偿还额

格式： IPMT(rate ， per ， nper ， pv ， fv)

• rate ：各期利率 • per ：用于计算利息数额的期数，介于 1～ nper 之间 • nper ：总投资（或贷款）期，即该项投资（或贷款）的付款期总数

• pv ：从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和

• fv ：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为 0

29

功能：基于固定利率及等额分期付款方式，返回某项投资的未来值

格式： FV (rate ， nper ， pmt ， pv ， type)

• rate ：各期利率 • nper ：总投资（或贷款）期，即该项投资（或贷款）的付款期总数

• pmt ：各期所应支付的金额 • pv ：现值，即从该项投资开始计算时已经入账的款项，

或一系列未来付款的当前值的累积和，也称为本金 • type ：一个逻辑值，用以指定付款时间是在期初还是在期末， 1 表示期初， 0 表示期末，其默认值为 0

30

功能：一系列未来付款的当前值的累积和，返回的是投资现值

格式： PV(rate ， nper ， pmt ， fv ， type)

• rate ：贷款利率• nper ：该项贷款的总贷款期限或者总投资期 • pmt ：各期所应支付的金额 • fv ：未来值，或在最后一次付款后希望得到的现金余额，

如果忽略该值，将自动默认为 0

• type ：一个逻辑值，用以指定付款时间是在期初还是在期末， 1 表示期初， 0 表示期末，其默认值为 0

31

功能：某项资产在一个期间中的线性折旧值 格式： SLN(cost ， salvage ， life)

• cost ：资产原值• salvage ：资产在折旧期末的价值，即资产残值• life ：折旧期限，即资产的使用寿命

32

文本函数可以处理公式中的文本字符串

主要介绍以下几个文本函数： • EXACT 函数 • CONCATENATE 函数• SUBSTITUTE 函数 • REPLACE 函数 • SEARCH 函数

33

功能：用来比较两个文本字符串是否相同。如果两个字符串相同，则返回“ TRUE” ，反之，则返回“ FALSE”

格式： EXACT （ text1 ， text2 ）• text1 和 text2 ：两个要比较的文本字符串

注意： 1 、在字符串中如果有多余的空格，会被视为不同 2 、 EXACT 函数在判别字符串的时候，会区分英

文的大小写，但不考虑格式设置的差异

34

功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中

格式： CONCATENATE(text1 ， text2 ，…… )

• text1 ， text2 ，……：需要连接的字符文本或引用的单元格 注意： 1 、该函数最多可以附带 30 个参数 2 、如果其中的参数不是引用的单元格，且为文本

格式的，请给参数加上英文状态下的双引号

如果将上述函数改为使用“ &”符连接也能达到相同的效果

35

功能：实现替换文本字符串中的某个特定字符串 格式： SUBSTITUTE （ text ， old_text ， new_text ， instance_num ）

• text ：原始内容或是单元格地址• old_text ：要被替换的字符串• new_text ：替换 old_text 的新字符串• 如果字符串中含有多组相同的 old_text 时，可以使用参数

instance_num 来指定要被替换的字符串是文本字符串中的第几组。如果没有指定 instance_num 的值，默认的情况下，文本中的每一组 old_text都会被替换为 new_text 。

36

功能：将某几位的文字以新的字符串替换。其替换功能与 SUBSTITUTE 函数大致类似

格式： REPLACE （ old_text ， start_num ， num_chars ， new_t

ext ）• old_text ：原始的文本数据• start_num ：设置要从 old_text 的第几个字符位置开始替换

• num_chars ：设置共有多少字符要被替换

• new_text ：要用来替换的新字符串

37

功能：用来返回指定的字符串在原始字符串中首次出现的位置

格式： SEARCH （ find_text ， within_text ， start_num ）• find_text ：要查找的文本字符串• within_text ：要在哪一个字符串查找

• start_num ：从 within_text 的第几个字符开始查找。 注意：在 find_text 中，可以使用通配符，例如：问号

“？”和星号“ *” 。其中问号“？”代表任何一个字符，而星号“ *” 可代表任何字符串。如果要查找的字符串就是问号或星号，则必须在这两个符号前加上“～”符号。

38

日期与时间函数可以用来分析或操作公式中与日期和时间有关的值

主要介绍以下几个日期与时间函数：• DATE 函数 • DAY 函数 • TODAY 函数• TIME 函数

39

功能：计算某一特定日期的系列编号 格式： DATE （ year ， month ， day ）

• year ：指定年份• month ：每年中月份的数字• day ：在该月份中第几天的数字

注意： 1 、若 year 是介于 0～ 1899 之间，则 Excel会自动将该值加上 1900 ，再计算 year ；若 year 是介于 19

00～ 9999 之间，则 Excel将使用该数值作为 year 。 2 、如果所输入的月份month值大于 12 ，将从指

定年份一月份开始往上累加 。 3 、如果所输入的天数 day值大于该月份的最大天

数时，将从指定月数的第一天开始往上累加 。

40

功能：返回指定日期所对应的当月中的第几天的数值，介于 1～ 31 之间

格式： DAY （ serial_number ） • serial_number ：指定的日期或数值

DAY 函数的使用有两种方法：

• 参数 serial_number 使用的是日期输入 • 参数 serial_number 使用的是数值的输入：在 Excel 中，系统将 1900年 1 月 1日对应于序列号 1 ，后面的日期都是相对于这个时间进行对序列号的进行累加

41

功能：返回当前系统的日期 格式： TODAY （）

其语法形式中无参数，若要显示当前系统的日期，可以在当前单元格中直接输入公式 TODA

Y （）。

42

功能：返回某一特定时间的小数值，它返回的小数值从0～ 0.99999999 之间，代表 0:00:00(12:00:00A.M) ~ 23:59:

59 (11:59:59P.M) 之间的时间

格式： TIME （ hour ， minute ， second ） • hour ： 0～ 23 之间的数，代表小时

• minute ： 0～ 59 之间的数，代表分 • second ： 0～ 59 之间的数，代表秒

43

在一个工作表中，可以利用查找与引用函数功能按指定的条件对数据进行快速查询、选择和引用。查找与引用函数用于查找（查看）列表或表格中的值

主要介绍以下几个查找与引用函数：• VLOOKUP 函数 • HLOOKUP 函数 • LOOKUP 函数

44

功能：从一个数组或表格的最左列中查找含有特定值的字段，再返回同一列中某一指定单元格中的值

格式：VLOOKUP （ lookup_value ， talbe_array ， col_index_num ， range_looku

p ）

• look_value ：要在数组中搜索的数据，它可以是数值、引用地址或文字字符串

• table_array ：要搜索的数据表格、数组或数据库• col_index_num ：一个数字，代表要返回的值位于 table_array 中

的第几列。• rang_lookup ：一个逻辑值，如果其值为“ TRUE” 或被省略，则返回部分符合的数值；如果该值为“ FALSE” 时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃ N/A” 。

如果 range_lookup为“ TRUE” ，则 table_array第一列的值必须以递增次序排列

45

功能：可以用来查询表格的第一行的数据

格式：HLOOKUP （ lookup_value ， table_array ， row_index_num ， range_look

up ）• look_value ：要在表格第一行中搜索的值

• table_array ：要搜索的数据表格、数组或数据库• row_index_num ：要返回的值位于 table_array 列中第几行 ran

g_lookup ：一个逻辑值，如果其值为“ TRUE” 或被省略，则返回部分符合的数值；如果该值为“ FALSE” 时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃ N/A” 。

如果 range_lookup 为 “ TRUE” ，则 table_array 第一列的值必须以递增次序排列，如果 rang_lookup 是 “ FALSE” ，则 table_array不需要先排序。

46

LOOKUP 函数有两种语法形式：向量（较常用）和数组。 向量形式的格式：LOOKUP （ lookup_value ， lookup_vector ， result_vector ）

• look_value ：要录找的数据• lookup_vector ：一个单行或单列范围，内容可以是文字、数

字或逻辑值，但要以递增方式排列，否则不会返回正确的值 • result_vector ：一个单行或单列范围，大小应与 lookup_vecto

r 相同 查询时，若 LOOKUP 函数无法找到完全符合的 lookup_valu

e, 则会采用在 lookup_value 中仅次于 lookup_value 的值。

47

数据库函数是用于对存储在数据清单或数据库中的数据进行分析，判断其是否符合特定的条件。

根据各自函数所具有的功能不同，可分为两大类：• 数据库信息函数：直接获取数据库中的信息 • 数据库分析函数：分析数据库的数据信息

数据库函数格式为： 函数名称（ database ， field ， criteria ）

• database ：构成数据清单或数据库的单元格区域 • field ：指定函数所使用的数据列

• criteria ：一组包含给定条件的单元格区域

48

数据库信息函数 • DCOUNT 函数 ：返回列表或数据库中满足指定条件的记录

字段（列）中包含数值的单元格的个数 • DGET 函数：从列表或数据库的列中提取符合指定条件的单

个值 • DCOUNTA 函数：返回列表或数据库中满足指定条件的记录

字段（列）中非空单元格的个数 数据库分析函数

• DAVERAGE 函数：计算列表或数据库的列中满足指定条件的数值的平均值

• DMAX 函数：返回列表或数据库的列中满足条件的最大值 • DPRODUCT 函数：返回列表或数据库中满足指定条件的记

录字段（列）中数值的乘积 • DSUM 函数是用来返回列表或数据库中满足指定条件的记录

字段（列）中的数字之和

49

在 Excek2003 函数库中，除了以上介绍了财务函数、文本函数、日期与时间函数、查找与引用函数、数据库函数之外，还有统计函数、信息函数、逻辑函数、数学与三角函数以及工程函数 。

主要介绍以下几种函数：• IS 类函数• TYPE 函数 • COUNT 函数• ROUND 函数• MAX 函数、 MIN 函数

50

功能：测试单元格中的内容是否为目标内容或者格式。 格式： IS(xxx) （ value ）

• value ：测试的值或单元格地址 IS 类函数有以下一些：

• ISBLANK （ value ）：是否为空白单元格• ISERR （ value ）：是否为 #N/A 之外的任何一种错误值• ISERROR （ value ）：是否为任何一种错误值• ISLOGICAL （ value ）：是否为逻辑值• ISNA （ value ）：是否为错误值 #N/A

• ISNONTEXT （ value ）：是否为任何非文本或空单元格• ISNUMBER （ value ）：是否为数字• ISREE （ value ）：是否为引用• ISTEXT （ value ） ：是否为文本

51

功能：返回测试值的数据类型 格式： TYPE （ value ）

• value: ：任何数据值，如数字、文本、逻辑值等

返回结果情况： • 如果测试值 value 是数字，则函数会返回 1

• 如果测试值 value 是文本，则函数会返回 2

• 如果测试值 value 是逻辑值，则函数会返回 4

• 如果测试值 value 是错误值，则函数会返回 16

• 如果测试值 value 是数组型，则函数会返回 64

52

功能：用于返回数字参数的个数，即统计数组或单元格区域中含有数值类型的单元格个数

格式： COUNT(value1 ， value2 ，…… )

• value1 ， value2 ，……表示包含或引用各类型数据的参数 注意： 1 、函数可以最多附带上 1～ 30 个参数 2 、只有数字类型的数据才能被统计 类似函数： 1 、 COUNTA 函数：返回参数组中非空值的数目，即计算数组或单元

格区域中数据项的个数

2 、 COUNTBLANK 函数：计算某个单元格区域中空白单元格的数目

3 、 COUNTIF 函数：计算区域中满足给定条件的单元格的个数

53

功能：根据指定的位数，将数字四舍五入 格式： ROUND （ number ， num_digits ）

• number ：将要进行四舍五入的数字• num_digits ：用户希望得到的数字的小数点后的位数

类似函数： 1 、 ROUNDDOWN 函数：按指定位数舍去数字指定位数后面的小

数

2 、 ROUNDUP 函数：按指定位数向上舍入指定位数后面的小数

54

功能： MAX 函数：求参数列表中对应数字的最大值 MIN 函数：求参数列表中对应数字的最小值 格式： MAX （ number1 ， number2 ，……） MIN （ number1 ， number2 ，……）

• number1 、 number2 、……：从中找出最大值或最小值的 1～ 30 个数字参数

类似函数： MAXA （ value1 ， value2 ，……）

MINA （ value1 ， value2 ，……）

区别： MAXA 和 MINA 函数的参数类型可以是数字、空参数、逻辑值或数字的文本表示等形式 。