excel学习-函数-11

Excel中的SUBTOTAL函数和AGGREGATE函数的用法详解Excel是一款功能强大的电子表格软件，广泛应用于数据处理、数据分析和统计等领域。

其中，SUBTOTAL函数和AGGREGATE函数是Excel中常用的函数之一，本文将详细介绍它们的用法。

一、SUBTOTAL函数的用法SUBTOTAL函数是一种灵活且易于使用的汇总函数，它可以根据用户的需求计算所选数据的汇总结果。

SUBTOTAL函数的语法如下：SUBTOTAL(函数代码, 引用1, [引用2], ...)函数代码是一个数字，用于指定所需的计算方式。

常用的函数代码如下：- 1：计数（包括隐藏行）- 2：计数（不包括隐藏行）- 3：求和（包括隐藏行）- 4：求和（不包括隐藏行）- 5：求平均值（包括隐藏行）- 6：求平均值（不包括隐藏行）- 9：最大值（包括隐藏行）- 10：最大值（不包括隐藏行）- 11：最小值（包括隐藏行）- 12：最小值（不包括隐藏行）- 101：计数（仅包括可见行）- 102：求和（仅包括可见行）- 103：求平均值（仅包括可见行）- 104：最大值（仅包括可见行）- 105：最小值（仅包括可见行）引用1, [引用2], ...是要进行计算的数据范围，可以是单个区域或多个区域。

使用SUBTOTAL函数可以根据用户的需求对数据进行灵活的汇总计算。

例如，利用SUBTOTAL函数可以快速计算某一列数据中的总和、平均值、最大值、最小值等。

二、AGGREGATE函数的用法AGGREGATE函数是Excel 2010及其更高版本中新增的函数，它是一个功能强大且多用途的汇总函数。

AGGREGATE函数的语法如下：AGGREGATE(函数代码, 选项, 引用1, [引用2], ...)函数代码用于指定所需的计算方式，选项用于指定特定条件下的计算方式。

常用的函数代码和选项如下：函数代码：- 1：平均值- 2：计数（包括隐藏值）- 3：计数（不包括隐藏值）- 4：最大值- 5：最小值- 6：乘积- 7：标准差- 8：标准差（基于样本）- 9：求和- 10：求平方和- 11：求平方和（基于样本）- 12：求积- 13：总体标准差- 14：总体标准差（基于样本）- 15：总体方差- 16：总体方差（基于样本）- 0：忽略错误值- 1：忽略隐藏行和错误值- 2：忽略隐藏行、错误值和示例图表系列引用1, [引用2], ...是要进行计算的数据范围，可以是单个区域或多个区域。

Excel函数中文解释（全）应用举例特别提醒备注如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。

在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。

如果C5中返回TRUE，说明A5和B5中的数值均大如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。

在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。

在C11单元格中输入公式：=COLUMN(B11)，确认后显示为2（即B 列）。

如果在B11单元格中输入公式：=COLUMN()，也显示出2；与之相对应的还有一个返回行标号值的函数——在C14单元格中输入公式：=CONCATENATE(A14,"@",B14,".com")，确认后，即可将A14单元格中字符、@、如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：在C17单元格中输入公式：=COUNTIF(B1:B13,">=80")，确认后，即可统计出B1至B13单元格区域中，数允许引用的单元格区域中有空白单元格出现。

在C20单元格中输入公式：=DATE(2003,13,35)，确认后，显示出2004-2-4。

由于上述公式中，月份为13，多了一个月，顺延至2004年1月；天数为35，比2004年1月的实际天数又多了4天，故又在C23单元格中输入公式：=DATEDIF(A23,TODAY(),"y")，确认后返回系统当前日期[用TODAY()表示）与这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。

EXCEL中常用函数及使用方法Excel函数一共有11类：数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查洵和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数.1.数据库函数当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。

例如, 在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1,000且小于2,500的行或记录的总数o Microsoft Excel共有12个工作表函数用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions,也称为D函数，每个函数均有三个相同的参数：databasex field和criteria。

这些参数指向数据库函数所使用的工作表区域。

其中参数database为工作表上包含数据清单的区域。

参数field为需要汇总的列的标志。

参数criteria 为工作表上包含指定条件的区域。

2.日期与时间函数通过日期与时间函数，可以在公式中分析和处理日期值和时间值。

3.工程函数工程工作表函数用于工程分析。

这类函数中的大多数可分为三种类型：对复数进行处理的函数、在不同的数字系统(如十进制系统、十六进制系统、八进制系统和二进制系统)间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。

4.财务函数财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。

财务函数中常见的参数：未来值(fv)~在所有付款发生后的投资或贷款的价值。

期间数(nper) 一投资的总支付期间数0付款(pmt)-对于一项投资或贷款的定期支付数额。

现值(pv)-在投资期初的投资或贷款的价值•例如，贷款的现值为所借入的本金数额。

利率(rate)-投资或贷款的利率或贴现率。

类型(type)-付款期间内进行支付的间隔，如在月初或月末。

5.信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型。

1. SUM - 求和用法：=SUM(A1:A10)解释：计算单元格A1到A10的数值总和。

2. AVERAGE - 平均值用法：=AVERAGE(B1:B10)解释：计算单元格B1到B10的数值平均值。

3. VLOOKUP - 查找用法：=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])解释：在给定的范围内查找特定的值，并返回对应的结果。

例如，=VLOOKUP("apple", A1:C10, 2, FALSE) 会在A1:C10范围内查找”apple”，并返回相同行的第二列的值。

4. IF - 条件判断用法：=IF(logical_test, value_if_true, value_if_false)解释：如果条件为真，则返回一个值，否则返回另一个值。

例如，=IF(A1>10, "High", "Low") 如果A1的值大于10，则返回”High”，否则返回”Low”。

5. COUNTIF - 计数用法：=COUNTIF(range, criteria)解释：计算满足特定条件的单元格数量。

例如，=COUNTIF(A1:A10, ">10") 会计算A1到A10中大于10的单元格数量。

6. SUMIF - 条件求和用法：=SUMIF(range, criteria, [sum_range])解释：对满足特定条件的单元格进行求和。

例如，=SUMIF(A1:A10, ">10", B1:B10) 会计算A1到A10中大于10的单元格对应的B列单元格的总和。

7. INDEX 和 MATCH - 组合查找用法：=INDEX(array, row_num, [column_num]) 和 =MATCH(lookup_value, lookup_array, [match_type])解释：INDEX 函数返回表格中特定行和列交叉点处的值，而 MATCH 函数返回特定值在表格中的相对位置。

Excel函数公式大全(图文详解全文为Excel干货实操指南，为方便学习，也给大家推荐视频课版，不想看大段文字的可以点击观看。

里面有技术大牛亲自下场指导，大厂实战案例助你提升视野，内容涵盖分析工具操作入门、数据可视化、数据分析+实战，迅速提升你的专业能力，让你少走弯路，真正实现从入门到提升！还有最新Excel自学手册1-6免费领取，需要的点击卡片即可知乎数据分析 3 天实战训练营目录一、求和SUM函数是一个求和函数，以将单个值、单元格引用或是区域相加，或者将三者的组合相加。

语法：SUM(number1,[number2],...)number1 （必需参数）要相加的第一个数字。

可以是具体数字，也可以是单元格引用或者单元格区域。

number2，这是要相加的第二个数字。

案例二、单条件求和SUMIF函数是对选中范围内符合指定条件的值求和。

sumif函数语法是：=SUMIF(range，criteria，sum_range)sumif函数的参数如下：第一个参数：Range为条件区域，用于条件判断的单元格区域。

第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。

第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。

案例三、多条件求和SUMIFS函数，快速对多条件单元格求和。

SUMIFS函数语法是：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)sumifs函数的参数如下：第一个参数：sum_range 是需要求和的实际单元格。

第二个参数：criteria_range1为计算关联条件的第一个区域。

第三个参数：criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本第四个参数：criteria_range2为计算关联条件的第二个区域。

第五个参数：criteria2为条件2。

实用EXCE的函数1.ADDRESS用途：以文字形式返回对工作簿中某一单元格的引用。

语法：ADDRESS(row_num，column_num，abs_num，a1，sheet_text)参数：Row_num是单元格引用中使用的行号;Column_num是单元格引用中使用的列标;Abs_num 指明返回的引用类型(1或省略为绝对引用，2绝对行号、相对列标，3相对行号、绝对列标，4是函数称。

4。

参数：Index_num是用来指明待选参数序号的值，它必须是1到29之间的数字、或者是包含数字1到29的公式或单元格引用;value1，value2，...为1到29个数值参数，可以是数字、单元格，已定义的名称、公式、函数或文本。

实例：公式“=CHOOSE(2，"电脑"，"爱好者")返回“爱好者”。

公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3，A10，A20，A30)返回A30)。

4.COLUMN用途：返回给定引用的列标。

语法：COLUMN(reference)。

参数：Reference为需要得到其列标的单元格或单元格区域。

如果省略reference，则假定函数COLUMN是对所在单元格的引用。

如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则COLUMN函数将reference中的列标以水平数组的形式返回。

实例：公式“=COLUMN(A3)”返回1，=COLUMN(B3:C5)返回2。

5.COLUMNS用途：返回数组或引用的列数。

数值。

串A1:B3，1，“c”。

它文件。

语法：HYPERLINK(link_location，friendly_name)参数：Link_location是文件的路径和文件名，它还可以指向文档中的某个更为具体的位置，如Execl工作表或工作簿中特定的单元格或命名区域，或是指向Word文档中的书签。

Excel公式及函数公式可以说成是Excel中由用户自行设计对工作表进行计算和处理的计算式。

例如：=SUM(A2：A10)*B1+100这种形式的表达式就称之为公式。

它要以等号“=”开始（不以“=”开头不能称之为公式），等号后面可以包括函数、引用、运算符和常量。

上式中的“SUM(A2：A10)”函数，“B1”则是对单元格B1值的引用（计算时使用B1单元格中显示的数据），“100”则是常量，“*”和“+”则是算术运算符。

函数及参数的说明函数是应用于公式中的一个最重要的元素，有了函数的参与，可以解决非常复杂的手工运算，甚至是无法通过手工完成的运算。

函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数、接着则是标志函数以公式的形式出现，则需要在函数名称前面输入等号。

下面的图示显示了函数的结构。

函数分为有参数函数和无参数函数。

当函数有参数时，其参数就是反映函数名称后圆括号内的常量值、变量、表达式或函数，多个参数间使用逗号分隔。

无参数的函数只由函数名称与()组成，如：NA()。

Excel中部分常用函数功能、语法及参数说明1.SUM函数函数语法：=SUM（Number1,Number2,…）函数功能：SUM函数用于返回某一单元格区域中所有数字之和参数说明：Number1,Number2, …：表示参加计算的1～30个参数，包括逻辑值、文本表达式、区域和区域引用2.SUMIF函数函数语法：SUMIF（Range, Criteria, Sum_range）函数功能：SUMIF函数用于按照指定条件对若干单元格、区域或引用求和参数说明：Range：表示为用于条件判断的单元格区域Criteria：表示是由数字、逻辑表达式等组成的判定条件Sum_range：表示需要求和的单元格、区域或引用3.AVERAGE函数函数语法：=AVERAGE（Number1,Number2,…）函数功能：AVERAGE函数用于计算所有参数的算术平均值参数说明：Number1,Number2, …：表示要计算平均值的1～30个参数4.COUNT函数函数语法：=COUNT (value1,value2,…)函数功能：COUNT函数用于返回数字参数的个数，即统计数组或单元格区域中含有数字的单元格个数。

EXCEL常用函数公式大全1. SUM：求和函数，用于计算一组数值的总和。

语法：SUM (number1, [number2],…)示例：=SUM (A1:A10) 计算A1到A10单元格的总和。

2. AVERAGE：平均值函数，用于计算一组数值的平均值。

语法：AVERAGE ( number, number2,……)示例：=AVERAGE (B1:B10) 计算B1到B10单元格的平均值。

3. MAX：最大值函数，用于计算一组数值中的最大值。

语法：MAX (number1, [number2],…)示例：=MAX (C1:C10) 计算C1到C10单元格中的最大值。

4. MIN：最小值函数，用于计算一组数值中的最小值。

语法：MIN (number1, [number2],…)示例：=MIN (D1:D10) 计算D1到D10单元格中的最小值。

5. COUNT：计数函数，用于计算一组数值中的非空单元格个数。

语法：COUNT (value1, [value2],…)示例：=COUNT (E1:E10) 计算E1到E10单元格中有多少个非空单元格。

6. IF：条件函数，用于根据条件是否成立返回不同的值。

语法：IF (logical_test,value_if_true,value_if_false)示例：=IF (F1>60,“及格”,“不及格”) 判断F1单元格的分数是否大于60，如果是则返回"及格"，否则返回"不及格"。

7. VLOOKUP：垂直查找函数，用于在一个表格中查找特定的值。

语法：VLOOKUP (lookup_value, table_array, col_index_num,[range_lookup])示例：=VLOOKUP (“张三”,G1:J10,4,FALSE) 在G1到J10的表格中查找姓名为"张三"的记录，并返回第四列（即J列）的值，精确匹配。

公式是单个或多个函数的结合运用。

AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。

条件判断AVERAGE 求出所有参数的算术平均值。

数据计算COLUMN 显示所引用单元格的列标号值。

显示位置CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。

字符合并COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。

条件统计DATE 给出指定数值的日期。

显示日期DATEDIF 计算返回两个日期参数的差值。

计算天数DAY 计算参数中指定日期或引用单元格中的日期天数。

计算天数DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。

条件统计FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。

概率计算IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。

条件计算INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。

数据定位INT 将数值向下取整为最接近的整数。

数据计算ISERROR 用于测试函数式返回的数值是否有错。

如果有错，该函数返回TRUE，反之返回FALSE。

逻辑判断LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。

截取数据LEN 统计文本字符串中字符数目。

字符统计MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。

匹配位置MAX 求出一组数中的最大值。

数据计算MID 从一个文本字符串的指定位置开始，截取指定数目的字符。

字符截取MIN 求出一组数中的最小值。

数据计算MOD 求出两数相除的余数。

数据计算MONTH 求出指定日期或引用单元格中的日期的月份。

日期计算NOW 给出当前系统日期和时间。

显示日期时间OR 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。

EXCEL表格里的Subtotal函数，一个抵11个函数，用法详解在EXCEL表格里，有这样一个函数，它能求和、求平均数、计算最大值、最小值、计算个数等11个功能，并能区分统计数据是否包含手动隐藏数据，这个函数就是Subtotal函数，它是职场EXCEL软件操作必备技能。

一、结构语法SUBTOTAL：返回一个数据区域的分类汇总的结果，它一共有11种对应的计算规则，分别使用对应的数字来代替。

语法：=SUBTOTAL(function_num,ref1,[ref2],...)第一参数：数据汇总的规则，当参数值为1-11的时候手动隐藏的数据也会被计算，当参数值为101-111的时候函数不会计算隐藏的数据区域，即仅统计可见单元格区域，具体的数字编号对应的函数，详见下图（使用频率较高的功能标记上了黄色）。

第二参数：第一个统计区域第三参数：第二个统计区域以此类推，最多可以设置254个引用的数据区域。

注意事项：统计区域仅仅对列数据有效，对行数据是无效的。

对于筛选隐藏的数据不管1-11还是101-111都不统计。

Subtotal函数会自动忽略嵌套在内的SUBTOTAL函数。

求平均值如图所示，在表格里使用SUBTOTAL函数的功能编号1和101分别对两列相同的数据求平均值。

函数公式“=SUBTOTAL(1,J3:J12)”和“=SUBTOTAL(101,J3:J12)”。

表格里数据行12手动隐藏了，K列的公式仅计算了可见单元格的平均值，而J列计算的平均值包含了隐藏的数据。

但需要注意的是，这两组功能编号仅区分手动隐藏的数据，如果是筛选隐藏了部分数据，这两组功能编号都只统计筛选出来的数据，即通过筛选隐藏的数据都不会计算。

计算数值和非空白单元格的个数在单元格内输入函数公式“=SUBTOTAL(2,J3:J12)”和“=SUBTOTAL(102,J3:J12)”，分别统计两列数据的个数，表格里手动隐藏了一行。

前者计算的个数包含了隐藏数据，后者仅统计了可见数字的个数。