Excel在会计与财务中的应用课件：Excel的高级应用

2.1公式的使用2.2函数的使用2.3数据的管理与分析2.4图表的使用

Excel的高级应用内容简介本章介绍了Excel2016的公式、函数的使用，数据的排序、筛选、分类汇总和透视表，以及图表的创建与编辑。学习目标掌握公式与函数的使用，特别是单元格引用、常用函数、函数的嵌套。熟悉数据排序与筛选，了解分类汇总，熟悉数据透视表。掌握图表的创建与编辑。了解Excel的自动恢复、更改窗口颜色等设置。2024/4/182

Excel的高级应用2.1公式的使用2.1.1公式概述2.1.2单元格引用2.1.3输入与修改公式2.1.4复制与移动公式2.1.5显示与隐藏公式2024/4/1831.引用运算符2024/4/1842.1.1公式概述运算符运算功能示例:（冒号）区域运算符，用于引用单元格区域A1:B2（包括A1、A2、B1、B2四个单元格）；B:B（表示整个B列）；3:3（表示整个第3行）。

（空格）交叉运算符，用于引用两个单元格区域相交部分A1:C3B2:C4（表示两区域相交的部分B2:C3四个单元格）,（逗号）联合运算符，用于把两个单元格区域合并在一起A1:B2,B3:C4（总共包括8个单元格）2.算术运算符2024/4/1852.1.1公式概述运算符运算功能示例()（括号）提升运算优先顺序，括号内先运算A1*(B1+B2)。先算加法后算乘法。-（负号）取相反数-B2。对B2存储的数据取相反数。%（百分号）百分比50%。A1%表示对A1存储的数据取缩小100倍，如图1.99所示。^（脱字符号）求幂运算2^3=8。A1^B1表示A1的B1次幂。*和/（星号和正斜杠）乘和除2*3=6。A1*B1。A1/B1。+和-（加号和减号）加和减2+3=5。A1+B1。A1-B1。3.文本运算符使用文本运算符“＆”（与号）可将两个或多个文本值串起来产生一个连续的文本值。例如：A1输入“我爱你”，B1输入“中国！”，C1单元格输入“=A1&B1”，则会生成“我爱你中国！”2024/4/1862.1.1公式概述4.比较运算符2024/4/1872.1.1公式概述运算符含义运算符含义=（等号）等于<=（小于等于号）小于等于<（小于号）小于>=（大于等于号）大于等于>（大于号）大于<>（不等号）不等于5.运算符优先顺序2024/4/1882.1.1公式概述运算类型运算符优先级引用运算:（冒号）1

（空格）2,（逗号）3数学运算()（括号）4-（负号）5%（百分号）6^（脱字符号）7*和/（星号和正斜杠）8+和-（加号和减号）9文本运算&（与符号）10逻辑运算=（等号）11<和>（小于和大于）<=（小于等于）>=（大于等于）<>（不等于）2.1.2单元格引用1.相对引用公式中的相对单元格引用是基于包含公式和单元格引用的相对位置。在F3单元格中输入公式“=C3*D3”，表示Excel将在F3单元格的左侧查找C3和D3单元格中的数据，并把找到的数值相乘，把结果存放在F3单元格中。此处C3和D3就是相对于公式所在的F3单元格的相对位置。2024/4/1892.1.2单元格引用2.绝对引用绝对引用是指在列标和行号之前分别加上符号“$”。在复制公式或填充公式时，绝对引用的单元格地址不会发生变化。例如，在F6单元格中输入公式“=$C$6*$D$6”，表示对C6和D6单元格的绝对引用，将公式复制到G6、F7单元格中，绝对引用的单元格地址不发生变化，故而G6、F7单元格中的公式结果和F6单元格中的数值一样。2024/4/18102.1.2单元格引用3.混合引用混合引用包含相对引用和绝对引用，即在列标或行号前加，“$”符号。在列标前加“$”符号称为绝对列，在行号前加“$”符号称为绝对行。在复制或填充公式时，相对引用部分改变，绝对引用部分不改变。2024/4/18111.输入公式Excel中的公式书写顺序与大家日常习惯的公式书写顺序略有不同，需要用户先输入一个等号（=），接着输入运算式。2024/4/18122.1.3输入与修改公式2.修改公式选中含有错误公式的单元格，这时会在编辑栏中显示公式，将光标定位到编辑栏中，接着修改公式，再按Enter键确认。2024/4/18132.1.3输入与修改公式1.复制公式选中需要复制公式的单元格，利用填充柄即可。2024/4/18142.1.4复制与移动公式2.移动公式选中单元格，将鼠标移到单元格边框变成十字箭头，鼠标左键按住，移动到需要的位置放开鼠标即可。2024/4/18152.1.4复制与移动公式在“公式”选项卡的“公式审核”组中单击“显示公式”按钮。2024/4/18162.1.5显示与隐藏公式2.2函数的使用2.2.1函数概述2.2.2调用函数2.2.3常用函数2.2.4函数的嵌套2.2.5常见的公式错误符号2024/4/18172024/4/18182.2.1函数概述函数类别函数功能数学和三角函数进行数学计算，包括常规计算和三角函数方面的计算日期和时间函数对公式中的日期和时间进行计算与格式设置逻辑函数设置判断条件，以使公式更加智能化文本函数对公式和单元格中的文本进行提取、查找、替换或格式化处理查找和引用函数查找或返回工作表中的匹配数据或特定信息信息函数判定单元格或公式中的数据类型，或返回某些特定信息统计函数统计和分析工作表中的数据财务函数分析与计算财务数据工程函数分析与处理工程数据数据库函数分类、查找与计算数据表中的数据多维数据集函数分析多维数据集合中的数据加载宏和自动化函数通过加载宏提供的函数，可扩展Excel的函数功能兼容性函数这些函数已被新增函数代替，提供它们便于用于早期版本Web函数网络信息、数据的抓取与处理用户可以在单元格或数据编辑栏中直接输入函数。2024/4/18192.2.2调用函数1.求和函数（1）无条件求和函数——SUM函数功能：SUM函数用于计算单个或多个参数之和。公式：=SUM(numberl,number2,…)式中，“number1,number2,…”为1到255个需要求和的参数。（2）单一条件求和函数——SUMIF函数功能：根据指定条件对若干单元格求和。公式：=SUMIF(range,criteria,[sum_range])range集合代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。criteria代表查询条件。sum_range集合代表求和单元格或区域。2024/4/18202.2.3常用函数1.求和函数（1）无条件求和函数——SUM函数功能：SUM函数用于计算单个或多个参数之和。公式：=SUM(numberl,number2,…)式中，“number1,number2,…”为1到255个需要求和的参数。（2）单一条件求和函数——SUMIF函数功能：根据指定条件对若干单元格求和。公式：=SUMIF(range,criteria,[sum_range])range集合代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。criteria代表查询条件。sum_range集合代表求和单元格或区域。（3）多条件求和函数——SUMIFS函数功能：根据多个条件（至少一个条件）对若干单元格求和。公式：=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)2024/4/18212.2.3常用函数2.计数函数（1）COUNT函数功能：计算包含数字的单元格个数以及参数列表中数字的个数。公式：=COUNT(value1,[value2],...)（2）COUNTA函数功能：计算范围中非空的单元格的个数。公式：=COUNTA(value1,[value2],...)（3）COUNTBLANK函数功能：计算单元格区域中的空单元格的数量。公式：=COUNTBLANK(range)（4）COUNTIF函数功能：统计满足某个条件的单元格的数量。公式：=COUNTIF(range,criteria)（5）COUNTIFS函数功能：统计满足多个条件的单元格的数量。公式：=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)2024/4/18222.2.3常用函数3.求平均函数（1）AVERAGE函数功能：返回参数的平均值(算术平均值)。公式：=AVERAGE(number1,[number2],...)（2）AVERAGEIF函数功能：返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。公式：=AVERAGEIF(range,criteria,[average_range])AVERAGEIF函数是用法与SUMIF函数的用法几乎完全相同，参数也基本一样。（3）AVERAGEIFS函数功能：返回满足多个条件的所有单元格的平均值（算术平均值）。公式：=AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)AVERAGEIFS函数是用法与SUMIFS函数的用法几乎完全相同，参数也基本一样。2024/4/18232.2.3常用函数4.四舍五入函数（1）ROUND函数功能：将数字四舍五入到指定的位数。公式：=ROUND(number,num_digits)（2）ROUNDDOWN函数功能：将数字进行向下舍入到指定的位数。公式：=ROUNDDOWN(number,num_digits)（3）ROUNDUP函数功能：将数字进行向上舍入到指定的位数。公式：=ROUNDUP(number,num_digits)2024/4/18242.2.3常用函数5.最值函数（1）MAX函数功能：返回一组值中的最大值。公式：=MAX(number1,[number2],...)（2）MIN函数功能：返回一组值中的最小值。公式：=MIN(number1,[number2],...)2024/4/18252.2.3常用函数6.条件函数——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时返回的值。其实IF函数的三个参数很像关联词造句，给定的关联词是：“如果……，那么……，否则……。”2024/4/18262.2.3常用函数7.且/或/非函数（1）AND函数功能：表示逻辑值“且”，用于确定所有条件是否均为TRUE。公式：=AND(logical1,[logical2],…)（2）OR函数功能：表示逻辑值“或”，用于确定只要有一个条件是TRUE。公式：=OR(logical1,[logical2],…)（3）NOT函数功能：表示逻辑值“否”，用于判定一个值不等同于另一值。2024/4/18272.2.3常用函数8.查找函数（1）VLOOKUP函数功能：在表格或区域中依据首列按行查找项目。公式：=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])为了方便记忆，可以这样记VLOOKUP函数的四个参数：VLOOKUP（线索，去哪找，要第几列，[是否近似匹配]）（2）MATCH函数功能：在单元格区域中搜索特定的项，然后返回该项在此区域中的相对位置（第几行或第几列）。公式：MATCH(lookup_value,lookup_array,[match_type])为了方便记忆，可以这样记MATCH函数的三个参数：MATCH（找什么<唯一>，去哪找<某一行或某一列>，[是否近似匹配]）2024/4/18282.2.3常用函数9.引用函数（1）INDEX函数INDEX函数有两种用法：如果想要返回指定单元格或单元格数组的值，请参阅数组形式。如果想要返回对指定单元格的引用，请参阅引用形式。①数组形式功能：返回单元格区域或数组中值的引用。公式：=INDEX(array,row_num,[column_num])②引用表单功能：返回指定的行与列交叉处的单元格引用。如果引用由非相邻的选项组成，则可以选择要查找的选择内容。公式：=INDEX(reference,row_num,[column_num],[area_num])2024/4/18292.2.3常用函数9.引用函数（2）OFFSET函数功能：返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。公式：＝OFFSET(reference,rows,cols,[height],[width])2024/4/18302.2.3常用函数2.2.3常用函数10.文本函数（1）LEFT函数功能：返回文本值中最左边的字符。公式：=LEFT(text,[num_chars])（2）RIGHT函数功能：返回文本值中最右边的字符。公式：=RIGHT(text,[num_chars])（3）MID函数功能：返回文本值中中间（Middle）的字符。公式：=MID(text,start_num,num_chars)（4）TEXT函数功能：设置数字格式并将其转换为文本。公式：=TEXT(value,format_text)（5）VALUE函数功能：将表示数字的文本字符串转换为数字。公式：=VALUE(text)2024/4/183111.日期函数——DATEDIF函数功能：计算两个日期之间相隔的天数、月数或年数。公式：=DATEDIF(start_date,end_date,unit)2024/4/18322.2.3常用函数1.同函数嵌套——以IF函数为例例如，公司规定购买药品需要缴纳预付款，预付款预缴比例如表2.13所示。根据公司规定，制作G列“预付款”。G3单元格的公式为：“=IF(F3<100000,F3*100%,IF(F3<=500000,F3*50%,F3*25%))”2024/4/18332.2.4函数的嵌套销售金额预付比例10万以下100%10万（含）~50万（含）50%50万以上25%2.不同函数嵌套——以VLOOKUP和MATCH函数为例实践中，VLOOKUP+MATCH的组合是非常经典、常用的。绝大多数情况下，MATCH函数充当VLOOKUP函数的第3个参数。例如，在J3单元格设计公式，使其能够根据不同的日期，查找出当天销售的商品名称、销售单价、数量等信息。J3单元格的公式为：“=VLOOKUP(I3,A:G,MATCH(J2,A2:G2,0),FALSE)”。2024/4/18342.2.4函数的嵌套2.2.5常见的公式错误符号1.####错误此错误表示列不够宽，或者使用了负日期或时间。2.#VALUE!错误此错误表示使用的参数或操作数的类型不正确。3.#DIV/0!错误这种错误表示使用数字除以零(0)。4.#N/A错误当数值对函数或公式不可用时，将出现此错误。5.#NAME?错误当Excel无法识别公式中的文本时，将出现此错误。6.#REF!错误当单元格引用无效时，会出现此错误。7.#NUM!错误如果公式或函数中使用了无效的数值，则会出现此错误。8.#NULL!错误如果指定了两个并不相相交的区域的交点，则会出现错误。2024/4/18352.3数据的管理与分析2.3.1数据排序2.3.2数据筛选2.3.3分类汇总2.3.4数据透视表2024/4/18361.默认的排序顺序在对数据进行排序时，Excel有默认的排序顺序。按升序排序时，Excel的排序规则如下(在按降序排序时，除了空格总是在最后外，其他的排序顺序反转)。①数字：从最小的负数到最大的正数排序。②文本以及包含数字的文本：首先是数字0~9，然后是字符’-(空格)!#$%&()*，./：;?@\^-{|}~+<=>，最后是字母A~Z。③逻辑值：FALSE排在TRUE之前。④所有错误值的优先级等效。⑤空格排在最后。2024/4/18372.3.1数据排序2.3.1数据排序2.简单排序简单排序，是指对数据列表中的单列数据进行排序。直接右击需要排序的标题所在单元格，在下拉菜单中选择“排序”以及排序方式即可。例如要依据“数量”升序排序，直接右击D2单元格，选择“排序”|“升序”即可完成操作2024/4/18381.自动筛选自动筛选的功能比较简单，可以很快地显示出符合条件的数据。2024/4/18392.3.2数据筛选2.取消筛选对工作表的数据进行筛选后，工作表中将只显示符合筛选条件的数据，需要查看其他数据时可以取消筛选。如果要取消自动筛选，则打开“数据”选项卡，单击“排序与筛选”选项组中的“清除”按钮即可。2024/4/18402.3.2数据筛选分类汇总是将数据按照某一字段进行分类并计算汇总(个数、和、平均值等)。通过分类汇总可以方便地分析出各类数据在总数据中所占的位置。简单来说，就是先按照一个标准进行分类，再对各类型数据进行汇总。2024/4/18412.3.3分类汇总2024/4/18422.3.4数据透视表页面字段数据字段列字段行字段项目数据区域2.4图表的使用2.4.1图表的概述2.4.2创建图表2.4.3编辑图表2024/4/18432024/4/18442.4.1图表的概述图表类型用途柱形图用于显示一段时期内数据的变化或者各项之间的比较关系折线图用于显示数据的变化趋势饼图用于显示数据系列中各项占总体的比例关系条形图用于描述各项之间的差异变化或者显示各个项与整体之间的关系面积图用于显示局部和整体之间的关系，更强调幅值随时间的变化趋势XY(散点图)多用于科学数据，用于比较不同数据系列中的数值，以反应数值之间的关联性股价图用于分析股票价格的走势曲面图用于确定两组数据之间的最佳逼近雷达图用于多个数据系列之间的总和值的比较，各个分类沿各自的数值坐标轴相对于中点呈辐射状分布，同一序列的数值之间用折线相连组合图将多个图表进行组合，在一个图表中实现效果2.4.2创建图表选中单元格区域。然后在“插入”选项卡的“图表”组中单击“XX图”按钮。2024/4/18451.设置图表格式（1）设置图表大小若要调整图表大小，鼠标左键选择并按住图表的角落进行拖拽即可。若要准确设置图表的大小，在选中图表后，单击功能区“图表工具”|“格式”选项卡，在“大小”组中分别设置高度和宽度。2024/4/18462.4.3编辑图表1.设置图表格式（2）选择图表样式Excel2016中有很多预设的图表样式，选中图表后，单击功能区“图表工具”|“设计”选项卡，在“图表样式”组中单击“其他”按钮，即可在展开的