作者：简超,杨怡
出版社：清华大学出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Excel 2010函数、图表及数据分析典型实例试读：
前言
1. 这是一本什么书？亲爱的朋友，感谢您从茫茫书海中将我捧起，从此，我将带您开启一扇学习的大门，也从这里出发送您远航。我希望在您人生远航的路上，能带给您更多的知识和经验，使您顺利到达理想的彼岸。为了让您的学习过程更加轻松，本书采用清新、淡雅的色调，同时在排版和设计上也营造出一种和谐的氛围。本书采用立体化设计，全方位考虑各知识点，旨在打造一本“您理想中的书”。
◆ 这是一本实例的书
本书以实例为主导，讲解了Excel软件中的函数、图表以及数据分析在实际工作中的应用，涉及的实例广泛，包括了日常办公的各个工作领域，如Excel公式与函数基础、Excel图表应用基础、日常事务管理、员工考勤管理、员工档案与考评管理、员工工资管理、产品生产与成本费用管理、产品订单管理、公司数据库管理、产品销售数据统计与分析、员工培训与销售业绩统计、常见财务表格数据计算与分析、财务预算与分析、图表的常见实例和函数的其他实例等。只要是在工作中可能遇到的问题，书中几乎都包含了，并且在讲解时尽可能以一种比较简便、易行的方式进行，以提高您在实际工作中的办公效率。
◆ 这是一本知识讲解的书
虽然本书以实例为主导，但并未因此忽略了知识讲解，而是在涉及知识点时尽可能地注意知识的系统性及全面性。本书首先在前两章中讲解了Excel软件的基础知识，以及Excel在办公过程中常使用到的函数、图表以及数据分析的一般方法；在后续的实例讲解过程中，首先在创建实例之前以“关键知识点”的形式列出完成本例必须掌握的知识点，然后在实例完成后对这些知识点进行深入的解析，使读者不再局限于掌握简单的基础知识和操作，还可以对知识点有一个深入的了解，在每章末更以“高手过招”的形式讲解与本案例有关且对读者有所提高的相应知识，以达到“知其然”且“知其所以然”的目的。
为了方便读者查阅本书讲解的知识点，对Excel软件进行更加系统的学习，本书还以“知识点索引”方式将书中讲解和涉及的知识以目录的形式列出。
◆ 这是一本告诉您操作真相的书
学习的目的在于理解和应用，而不是“翻开书了然，合上书茫然”。因此本书特意添加了“为什么这么做”和“关键提示”等板块，使您可以举一反三，知道操作背后的“秘密”。
◆ 这是一本结合实际的超值办公图书
本书实例丰富，在每个实例开始制作前先列出该实例的效果，让读者能直观地知道要制作的内容；然后讲解该实例的制作背景，使读者在实际工作时明白该类实例的设计要点和注意事项。为帮助读者更好地学习和掌握本书的知识，本书还附带了一张光盘，其中包含了书中所有实例的素材文件、效果文件以及视频演示文件，保证读者可以跟着本书做出相同的实例效果。2. 本书的结构如何？
本书共分为15章，由基础知识和实例制作两部分组成，分别介绍如下。● 基础知识（第1~2章）：以“知识讲解+高手过招”的形式，讲解
了Excel软件的一般操作方法。● 实例制作（第3~15章）：每章包括多个实例，将Excel 2010的知
识灵活、有序地分布在各个实例中。每个实例均以“效果图展示
+光盘引用+案例背景+关键知识点+操作步骤+关键知识点解析”
的方式进行讲解，方便读者更加立体地了解实例的制作方法及其
关键知识点的使用目的。在每一章的最后还通过“高手过招”的
形式进一步提高读者的操作技能。3. 学习过程中应注意什么？
为了帮助读者从书中更快学到自己需要的知识，在学习的过程中应注意如下几点。● 注意书中小板块的功能：本书中设有“关键提示”、“技巧秒杀”
和“为什么这么做”3个板块，它们在每一章中“不定时”出现，
或解决当前读者的疑问，或讲出更简便的操作方法。合理使用小
板块，可更快提升自己的软件应用能力。● 注意光盘的使用：书中配套了学习光盘，由于光盘易损，同时也
为了保护您的光驱，建议在使用光盘时先将其中的所有内容复制
到硬盘中，然后从硬盘中调阅素材和观看视频演示。● 注意合理寻求帮助：在学习本书的过程中如果遇到什么困难或疑
惑，除了可以“百度一下”，还可以通过网站（http://
www.jzbooks.com）或QQ群（122144955、120241301）联系我
们。请只选择一个QQ群加入，不要重复加入多个群。4. 本书由谁编著？
本书由九州书源组织编写，为保证实例的实用和知识精炼，让每位读者都能学有所用，参与本书编写的人员都是一线的职场办公人员，在Office软件应用方面有着较高的造诣。他们是简超、杨怡、李星、刘霞、何晓琴、董莉莉、廖宵、杨明宇、蔡雪梅、彭小霞、包金凤、尹磊、陈晓颖、向萍、曾福全和何周。第1章Excel公式与函数基础
本章将对公式和函数的基础知识进行了解和掌握，使用户打好基础，在制作各类实例文档时能得心应手。本章主要讲解公式与函数的结构、函数分类、使用公式与函数进行基本计算、单元格的引用与名称定义、单元格引用追踪、公式纠错等知识。1.1 认识公式与函数
在Excel中对数据进行分析与处理，公式与函数都起到很大的作用。用户通过公式与函数计算和分析表格中的数据，能避免手动计算数据的麻烦，同时又能够做到准确计算。下面将从公式与函数的结构等基础知识开始认识函数，为数据分析打下坚实的基础。1.1.1 公式的结构
Excel 2010中的公式是一种对工作表中的数值进行计算的等式，它可以帮助用户快速地完成各种复杂的数据运算。公式是对数据计算的依据，在Excel中，输入计算公式进行数据计算时需要遵循一个特定的次序或语法：最前面添加等号“=”，然后再是计算公式。公式中可以包含运算符、常量数值、单元格引用、单元格区域引用和函数等。公式中包含的变量含义分别介绍如下。● 常量数值：是指不随其他函数或单元格位置变化的数值，如
50。● 运算符：是指公式中对各元素进行计算的符号，如+、-、
*、/、%、<、>等。● 单元格引用：是指需要引用数据的单元格所在的位置，如D5表
示引用第D列和第5行交叉处单元格中的数据。● 单元格区域引用：是指需要引用数据单元格区域所在的位置，如
B4:F6。● 函数：是指Excel中预定义的计算公式，通过使用一些称为参数
的特定数值来按特定的顺序或结构执行计算。其中的参数可以是
常量数值、单元格引用和单元格区域引用等，如7+3+8可以表示
为SUM(7,3,8)。1.1.2 公式的运算符
使用公式离不开运算符，它是Excel公式中的基本元素。因此了解不同运算符的含义与作用可以更加灵活地运用公式对数据进行分析和处理。运算符分为4种：算术运算符、比较运算符、文本连接运算符和引用运算符。
1. 算术运算符
算术运算符能完成基本的数学运算，包括加法、减法、乘法、除法和乘方，合并数字及生成数值结果。
2. 比较运算符
比较运算符能够比较两个或多个数字、文本、单元格内容或函数结果的大小关系，当用这些运算符比较两个值时，结果为逻辑值：TRUE或FALSE。
3. 文本连接运算符
文本连接运算符只包含一个连接字符&，用于连接一个或多个文本字符串，最后生成一段文本。例如输入“="Microsoft"&"Excel"”将得到字符串“MicrosoftExcel”。
4. 引用运算符
引用运算符可以对单元格或单元格区域进行合并计算。运算符号含义示例冒号，区域运算符，生成：对两个引用之间所有单元B4:B10格的引用逗号，联合运算符，将多，SUM(B5:B15,D5:D15)个引用合并为一个引用交集运算符，生成对两个B7:D7 C6:C8（空格）引用中共有单元格的引用1.1.3 函数的结构
函数是一种在需要时可直接调用的表达式，通过使用参数的特定数值按特定的顺序或结构进行计算。利用函数能够很容易地完成各种复杂数据的处理工作，如SUMIF函数可计算满足条件的单元格的和；PMT函数可计算在固定利率下，贷款的等额分期偿还额；COUNT函数可计算包含数字的单元格以及参数列表中的数字个数等。
函数的一般结构为：函数名(参数1,参数2,...)，如“=IF(A2<10000,5%*A2,7.5%*A2)”，其中各部分的含义如下。● 函数名：即函数的名称，每个函数都有一个唯一的函数名，如
PMT和SUMIF等。● 参数：函数的参数可以是数字、文本、表达式、引用、数组或其
他的函数。1.1.4 函数类型
根据不同的计算需要，Excel中提供了各种应用分类的函数，这些函数分别适用于不同的场合。下面分别对不同类型的函数功能及应用进行介绍。
1. 财务函数
财务函数一般用于财务数据方面的计算，大致可分为折旧函数、投资预算函数、本金和利息函数、报酬率计算函数和证券计算函数等类型。下面以DB函数使用固定余额递减法计算资产的折旧值为例介绍财务函数的结构和使用方法，DB函数使用固定余额递减法，计算一笔资产在给定期间内的折旧值，其语法格式为：DB(cost,salvage,life,period,month)，各参数的含义分别如下。● cost：为资产原值，不能为负数。● salvage：为资产在折旧期末的价值（有时也称为资产残值）。● life：为折旧期限（有时也称作资产的使用寿命）。● period：为需要计算折旧值的期间。period必须使用与life相同的
单位。● month：为第一年的月份数，如省略，则假设为12。
假设某公司于2008年7月购买了一台价值为860000元的机床，使用到2013年6月时，估计其残值为305000元，输入DB函数“=DB($A$2,$D$2,$B$2,B4,C4)”，可计算该机床每年的折旧额和累计折旧额。
2. 逻辑函数
逻辑函数是根据不同条件，进行不同处理的函数，条件中使用比较运算符（如大于、小于、等于、不等于和小于等于）指定逻辑式，并用逻辑值表示它的结果。Excel 2010逻辑函数总共有7个，逻辑值是用TRUE和FALSE的文本表示指定条件是否成立。当条件成立时，逻辑值为TRUE，也称为“真”；当条件不成立时，逻辑值为FALSE，也称为“假”。
下面以使用AND函数进行交集运算为例介绍逻辑函数的结构和使用方法。当AND函数中所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE，与数学中的交集运算类似。其语法结构为：AND(logical1,logical2,...)，其中参数logical1,logical2,...是1～255个待检测的条件，它们可以为TRUE或FALSE。如输入AND函数“=AND(B3>=60,C3>=60,D3>=60)”判断成绩是否全部及格，每科成绩及格分数为60，当有一科成绩没有达到60，则返回FALSE值，判断学生成绩没有全部及格。
3. 文本函数
文本函数主要针对文本字符串进行一系列相关操作，在处理工作表中的数据时可根据需要对文本进行提取、删除、代替以及返回特定的字符等操作。
下面以使用CONCATENATE函数合并文本字符串为例介绍文本函数的结构和使用方法。CONCATENATE函数可将两个或多个文本字符串合并为一个文本字符串，其语法结构为：CONCATENATE(text1,text2,...)，其中参数text1,text2,...为2～255个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。如将B2、C2和D3的文本合并，生成商品名。
4. 日期和时间函数
日期和时间函数主要用来分析或操作公式中的与日期和时间相关的数值，如TODAY函数可根据系统时钟中存储的数据得到当前日期。下面以使用YEAR函数返回日期的年份为例介绍日期和时间函数的结构和使用方法，YEAR函数代表返回日期的年份值，返回值为1900～9999之间的整数，其语法结构为：YEAR(serial_number)，其中参数serial_number表示将要计算其年份数的日期。需要注意的是，不能以文本形式输入，否则将出现错误。例如，使用YEAR函数计算某单位职员从进入公司到现在的工龄，输入“=YEAR(TODAY())-D4”，其中，“=YEAR(TODAY())”可提取当前的年份，“-D4”表示用当前年份减去员工进入公司的年份（D4中显示的是员工进入公司的年份）。
5. 查找和引用函数
查找函数主要用于在数据清单或工作表中查找特定数值。引用函数则是在数据库或工作表中查找某个单元格引用的函数。（1）查找函数
在Excel中，查找分为水平查找、垂直查找以及查找元素位置等查找方式，而这些不同的查找方式都需要不同的函数去执行。下面以使用用于水平查找的HLOOKUP函数为例介绍查找函数的结构和使用方法。HLOOKUP函数可以在数据库或数值数组的首行查找指定的数值，并在表格或数组中指定行的同一列中返回一个数值，其语法结构为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)，各参数的含义分别如下。● lookup_value：表示需要在数组第一行中查找的数值，可以为数
值、引用或文本字符串。● table_array：表示需要在其中查找数据的数据表，其第一行的数
值可以为文本、数字或逻辑值。● row_index_num：表示table_array中待返回的匹配值的行序号。
当其小于1时，函数会返回错误值“#VALUE!”；而当其大于
table_array的行数时，则会返回错误值“#REF!”。● range_lookup：指明HLOOKUP函数在查找时是精确匹配，还是
近似匹配。其值可以为TRUE或FALSE，也可省略。
下面将在速算扣除数查询表中，利用HLOOKUP函数进行特殊的查找，输入员工工资便可查出应使用的税率和速算扣除数（起征点为3500元），其具体操作如下：
光盘\素材\第1章\速算扣除数查询.xlsx
光盘\效果\第1章\速算扣除数查询.xlsx
光盘\实例演示\第1章\查找函数
STEP 01 查找应采用税率
1打开“速算扣除数查询.xlsx”工作簿，在B8单元格中输入工资“7894”。
2在G8单元格中输入函数“=HLOOKUP((B8-3500),B2:H4,3,TRUE)”，按“Enter”键得出应采用税率。
STEP 02 查找应扣除数
在G9单元格中计算应扣除数，在其中输入公式“=HLOOKUP((B8-3500),B2:H5,4,TRUE)”，按“Enter”键得出结果。
技巧秒杀——HLOOKUP技巧
简单地说，HLOOKUP函数中的“H”就是代表行的意思。在函数的参数中，如果range_lookup为TRUE，则table_array第一行的数值必须按升序排列，否则HLOOKUP函数将不能给出正确的数值。如果range_lookup为FALSE，则table_array不必进行排序。（2）引用函数
在计算比较复杂的数据时，若直接引用数值，可能需要不断进行相应的转换，使用引用函数则只需更改参数值，这样能提高工作效率。下面以返回引用的列标、行标的COLUMN和ROW函数为例介绍引用函数的语法结构和使用方法。其语法结构分别如下：● COLUMN(reference)● ROW(reference)
在这两个函数中都有一个共同的参数reference，该参数表示需要得到其列标、行号的单元格，在使用该函数时，参数reference可以引用单元格，但是不能引用多个区域，当引用的是单元格区域时，将返回引用区域第1个单元格的列标。
如果将D2单元格名称定义为“Example1”，其范围是工作表“Sheet1”，然后在A13单元格中输入函数“=ROW(Example1)”，返回的结果将是名称“Example1”所在的行号，即C2单元格的行号“2”；如果输入的函数为“=COLUMN(Example1)”，返回的结果将是名称“Example1”所在的列号，即D2单元格的列号“4”。
6. 数学和三角函数
通过数学函数可以处理多种数学运算，如求绝对值、乘积、余数及求幂等。三角函数则在数学几何领域中较常运用，如余弦和反余弦等。（1）数学函数
下面以使用ABS函数返回数字的绝对值为例，介绍数学函数的语法结构和使用方法。ABS函数用于返回数字的绝对值，其中绝对值没有符号。其语法结构为：ABS(number)，这里的参数number为需要计算其绝对值的实数。
使用ABS函数计算某公司一台机器切割钢材的精确程度，在测量切割的尺寸时机器切割的实际长度可能比要求的长，也可能会短，这个差距可以利用绝对值表示。其方法是：在打开工作簿的目标单元格D2中输入函数“=ABS(C2-B2)”，按“Enter”键，得出第一次试验的差异，然后复制函数至D4单元格得出所有测试的差异。（2）三角函数
与数学中的应用相似，COS函数可求余弦值，ACOS函数求反余弦值；SIN函数求正弦值，ASIN函数求反正弦值；TAN函数求正切值，ATAN函数求反正切值。如使用COS函数可计算余弦值，其语法结构为：COS(number)，参数number表示余弦的角度，如COS(60)，计算结果为“0.5”。
7. 统计函数
统计函数可从不同角度去统计数据，并捕捉统计数据的所有特征。该函数用于对单元格或单元格区域进行分析或统计，如利用办公中常用的MAX/MIN函数求最大/最小值。下面以使用COUNTIF函数按单条件进行统计为例介绍统计函数的语法结构和使用方法。COUNTIF函数用于计算区域中满足给定条件的单元格的个数，其语法结构为：COUNTIF(range,criteria)，各参数的含义如下。● range：是一个或多个要计数的单元格，其中包括数字或名称、
数组或包含数字的引用。空值和文本值将被忽略。● criteria：是确定哪些单元格将被计算在内的条件，其形式可以为
数字、表达式、单元格引用或文本，如条件可以表示为
45、"45"、">45"或B4等。
下面使用COUNTIF函数在“工资统计表.xlsx”工作簿中统计工资分别在1500元以下、1500～3000元之间和3000元以上的员工人数，其具体操作如下：
光盘\素材\第1章\工资统计表.xlsx
光盘\效果\第1章\工资统计表.xlsx
光盘\实例演示\第1章\统计函数
STEP 01 导入Access数据
打开“工资统计表.xlsx”工作簿，在B14单元格中输入函数“=COUNTIF(D3:D13,"<1500")”，按“Enter”键得出少于1500元的员工人数。\r\n
STEP 02 选择导入的表格
1在B15单元格中输入函数“=COUNTIF(D3:D13,"<=3000")-COUNTIF(D3:D13,"<=1500")”。
2在B16单元格中输入函数“=COUNTIF (D3:D13,">3000")”，按“Enter”键求出1500～3000元之间和3000元以上的员工人数。
8. 其他函数
Excel中还有应用于其他领域的函数，下面分别进行介绍。● 工程函数：处理复杂的数字，并在不同的计数体系和测量体系中
进行转换，主要在工程应用程序中使用这类函数，使用时还必须
执行加载宏命令。● 多维数据集函数：多维数据集是联机分析处理（OLAP）中的主
要对象，是一项可对数据库中的数据进行快速访问的技术。它是
一个数据集合，通常从数据库的子集构造，并组织和汇总成一个
由一组维度和度量值定义的多维结构。● 信息函数：帮助用户鉴定单元格中的数据所属类型或单元格是否
为空等。1.2 使用公式计算数据
要使用公式计算分析Excel表格中的数据，首先应输入公式，如果对公式不满意还可以对其进行修改或编辑，以便得到正确的结果。1.2.1 输入公式
要使用公式，学会怎样输入公式是必须掌握的操作。在Excel中，用户可以在编辑栏或单元格中输入，还可通过选择单元格来输入公式。
1. 在单元格或编辑栏中输入公式
在单元格或编辑栏中输入公式与输入一般数据的方法基本相同，当公式比较简单时，可以直接在单元格中输入公式，公式较长时可以在编辑栏中输入公式以便更直观地查看，两者没有什么区别。其具体操作如下：
光盘\素材\第1章\儿童玩具店.xlsx
光盘\效果\第1章\儿童玩具店.xlsx
光盘\实例演示\第1章\在单元格或编辑栏中输入公式
STEP 01 输入表达式
打开“儿童玩具店.xlsx”工作簿，选择E3单元格，输入表达式“=C3*D3”。
STEP 02 计算结果
此时，可以看到系统将自动为两个单元格标记不同的颜色，按“Enter”键计算出结果。
2. 通过选择单元格输入公式
当输入的公式涉及单元格的引用时，可以直接用鼠标选择需要引用的单元格，让系统自动输入需要涉及运算的单元格地址。使用这种方法计算数据能够使整个过程更加快速方便。其具体操作如下：
光盘\素材\第1章\销量统计表.xlsx
光盘\效果\第1章\销量统计表.xlsx
光盘\实例演示\第1章\通过选择单元格输入公式
STEP 01 输入等号
打开“销量统计表.xlsx”工作簿，选择E2单元格，输入运算符号“=”。
STEP 02 选择引用的单元格
单击要引用的B2单元格，其地址将自动被输入到等号后面。
STEP 03 输入加号并选择引用单元格
输入加号“+”，再单击C2单元格，将自动输入该单元格的地址。
STEP 04 计算总计销售量
再次输入加号“+”，单击D2单元格，引用该单元格，然后按“Enter”键或单击“输入”按钮计算出结果。1.2.2 编辑公式
在Excel中输入公式后，可以根据需要对公式进行修改、复制等操作来快速计算数据，以及删除单元格中的公式，只保留计算结果。
1. 修改公式
输入公式后，如果发现输入错误或情况发生改变时，就需要修改公式。修改时，只需选中要修改的部分，重新输入后确认即可。其具体操作如下：
光盘\素材\第1章\日用品销售表.xlsx
光盘\效果\第1章\日用品销售表.xlsx
光盘\实例演示\第1章\修改公式
STEP 01 选择单元格内容
打开“日用品销售表.xlsx”工作簿，在E3单元格中选择需要修改的“D4”内容。
STEP 02 修改公式内容
输入“D3”之后，按“Enter”键得出正确的结果。
2. 复制填充公式
若要输入的多个公式结构相同，仅引用的单元格不同时，只需输入一个公式，然后采用复制或填充公式的方法快速输入其他公式。在复制或填充公式的过程中，Excel会自动改变引用单元格的地址，这是计算同类数据的最快方法。其具体操作如下：
STEP 01 复制公式
打开“日用品销售表1.xlsx”工作簿，选择E3单元格，按“Ctrl+C”快捷键复制公式。
STEP 02 粘贴公式
选择要粘贴公式的E4单元格，按“Ctrl+V”快捷键粘贴。
STEP 03 填充公式
移动鼠标指针到E4单元格边框的右下角，当其变成形状时，按住鼠标左键不放并向下拖动到E12单元格后释放鼠标填充公式。
STEP 04 计算数据结果
释放鼠标，其中将显示计算数值。
3. 删除公式
通过公式对单元格中的数据计算后，其公式仍然在单元格中，如果只需要复制计算出的数据，此时该怎样做呢？选择单元格后，按“Delete”键，可直接删除单元格中的所有数据及公式，若想只删除单元格中的公式而不删除计算结果，此时可通过“选择性粘贴”对话框设置实现。其具体操作如下：
STEP 01 复制单元格
打开“销量统计表1.xlsx”工作簿，选择E2:E11单元格区域，按“Ctrl+C”快捷键复制。
STEP 02 执行选择性粘贴命令
1切换到“年度销量”工作表，选择B2:B11单元格区域。
2单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴”命令。
关键提示——默认公式显示方式
在默认情况下，选中包含公式的单元格时，单元格中将显示公式的值，编辑栏中将显示公式，双击单元格将光标插入其中之后，单元格中也将显示公式。
试读结束[说明：试读内容隐藏了图片]