2024年度EXCEL常用函数培训

EXCEL常用函数培训REPORTING2023WORKSUMMARY2024/3/231目录CATALOGUEEXCEL函数基础文本处理函数数学计算函数逻辑判断函数日期时间处理函数数据统计与分析函数自定义函数与宏应用2024/3/232PART01EXCEL函数基础2024/3/233函数分类Excel中的函数可以分为以下几类函数定义在Excel中，函数是一种预定义的公式，用于执行特定的计算或操作。通过使用函数，可以简化复杂的数据处理和分析任务。数学和三角函数用于执行基本的数学运算，如加法、减法、乘法、除法等，以及更高级的数学函数，如指数、对数、三角函数等。函数定义与分类2024/3/234用于对数据集进行统计分析，如计算平均值、中位数、标准差、方差等。统计函数文本函数日期和时间函数用于处理和操作文本数据，如连接文本、查找和替换文本、提取子字符串等。用于处理日期和时间数据，如计算两个日期之间的差异、将日期格式化为特定格式等。030201函数定义与分类2024/3/235用于在数据表中查找特定值或引用其他单元格的值，如VLOOKUP、HLOOKUP等。查找和引用函数用于与数据库进行交互，如查询数据库中的数据、执行SQL语句等。数据库函数用于执行财务计算，如计算贷款还款额、投资回报率等。财务函数函数定义与分类2024/3/236每个Excel函数都有其特定的语法，即函数的名称和参数。语法通常以等号（=）开头，后跟函数名称和括号内的参数。例如，`=SUM(A1:A10)`表示对A1到A10单元格范围内的值进行求和。函数语法函数的参数可以是常量、单元格引用、其他函数或名称等。不同类型的参数在函数中起到不同的作用。例如，在`=SUM(A1:A10,C1:C5)`中，`A1:A10`和`C1:C5`是单元格引用参数，表示对这两个范围内的值进行求和。参数类型函数语法及参数2024/3/237相对引用默认情况下，Excel使用相对引用。当复制包含相对引用的公式时，引用的单元格地址会相对于公式所在单元格的位置进行调整。例如，在A1单元格中输入`=B1`，然后将该公式复制到A2单元格时，A2单元格中的公式会自动变为`=B2`。绝对引用通过在行号和列号前添加美元符号（$），可以将相对引用转换为绝对引用。绝对引用的单元格地址在复制公式时不会改变。例如，在A1单元格中输入`=$B$1`，然后将该公式复制到其他单元格时，引用的始终是B1单元格的值。混合引用混合引用是介于相对引用和绝对引用之间的一种引用方式。它允许对行或列进行绝对引用，而对另一维度进行相对引用。例如，`=B$1`表示列是相对引用而行是绝对引用。单元格引用方式2024/3/238PART02文本处理函数2024/3/239文本连接使用`&`符号或`CONCATENATE`函数可以将多个文本字符串连接成一个字符串。例如，`=A1&""&B1`可以将A1和B1单元格的内容连接起来，并在中间添加一个空格。文本拆分Excel没有直接的文本拆分函数，但可以通过结合使用`LEFT`、`RIGHT`、`MID`等函数来实现文本的拆分。例如，可以使用`=LEFT(A1,5)`来获取A1单元格中前5个字符。文本连接与拆分2024/3/2310文本查找与替换查找文本使用`FIND`和`SEARCH`函数可以在文本字符串中查找子字符串的位置。例如，`=FIND("abc",A1)`可以查找A1单元格中"abc"子字符串的起始位置。替换文本使用`SUBSTITUTE`函数可以在文本字符串中替换指定的子字符串。例如，`=SUBSTITUTE(A1,"old","new")`可以将A1单元格中的"old"子字符串替换为"new"。2024/3/2311使用`UPPER`、`LOWER`和`PROPER`函数可以分别将文本转换为大写、小写和首字母大写的格式。例如，`=UPPER(A1)`可以将A1单元格的内容转换为大写。大小写转换使用`VALUE`函数可以将文本转换为数值，而`TEXT`函数则可以将数值转换为指定格式的文本。例如，`=VALUE(A1)`可以将A1单元格的文本内容转换为数值，而`=TEXT(A1,"0.00")`可以将A1单元格的数值转换为保留两位小数的文本格式。数值与文本转换文本格式转换2024/3/2312PART03数学计算函数2024/3/2313乘法使用“*”符号进行乘法运算。加法使用“+”符号进行加法运算。减法使用“-”符号进行减法运算。除法使用“/”符号进行除法运算。幂运算使用“^”符号进行幂运算，表示将某个数乘以自身指定的次数。四则运算及幂运算2024/3/2314SIN(angle)，返回给定角度的正弦值。三角函数及反三角函数正弦函数COS(angle)，返回给定角度的余弦值。余弦函数TAN(angle)，返回给定角度的正切值。正切函数ASIN(value)，返回给定值的反正弦角度。反正弦函数ACOS(value)，返回给定值的反余弦角度。反余弦函数ATAN(value)，返回给定值的反正切角度。反正切函数2024/3/2315数组公式可以同时处理多个数据，并返回一个或多个结果。通过使用大括号“{}”来定义数组范围或数组常量。数组公式介绍SUM(A1:B2*C1:D2)将对应单元格相乘后的结果进行求和。数组公式示例使用F9键可以将选定的数组公式部分转换为值，以便更好地理解和调试公式。同时，还可以通过使用TRANSPOSE函数对数组进行转置操作。数组公式应用技巧数组公式应用2024/3/2316PART04逻辑判断函数2024/3/2317示例语法IF(logical_test,value_if_true,value_if_false)CHOOSE函数根据索引号从参数列表中选择并返回一个值。语法CHOOSE(index_num,value1,[value2],…)根据指定的条件进行逻辑判断，返回相应的结果。IF函数示例=IF(A1>B1,"A1大于B1","A1小于等于B1")=CHOOSE(2,"选项1","选项2","选项3")返回"选项2"。条件判断与选择2024/3/2318语法AND(logical1,[logical2],…)OR函数检查参数中是否有TRUE，如果有，则返回TRUE。示例=OR(A1>B1,B1=C1)当A1大于B1或B1等于C1时返回TRUE。AND函数检查所有参数是否均为TRUE，如果均为TRUE，则返回TRUE。示例=AND(A1>B1,B1>C1)当A1大于B1且B1大于C1时返回TRUE。语法OR(logical1,[logical2],…)010203040506多条件组合判断2024/3/2319错误值处理IFERROR函数如果公式的计算结果为错误，则返回指定的值。语法IFERROR(value,value_if_error)示例=IFERROR(A1/B1,0)当B1为0导致除数为零错误时，返回0。ISERROR函数检查一个值是否为错误值，如果是，则返回TRUE。语法ISERROR(value)示例=ISERROR(A1/B1)当A1除以B1产生错误时返回TRUE。2024/3/2320PART05日期时间处理函数2024/3/23210102DATE函数将年、月、日三个参数组合成一个日期。TIME函数将时、分、秒三个参数组合成一个时间。DATEVALUE…将文本格式的日期转换为日期格式。TIMEVALUE…将文本格式的时间转换为时间格式。自定义日期时间格式通过设置单元格格式，可以自定义日期时间的显示方式，如"yyyy-mm-dd"、"hh:mm:ss"等。030405日期时间格式设置2024/3/232201TODAY函数：返回当前日期。02NOW函数：返回当前日期和时间。03DATEDIF函数：计算两个日期之间的天数、月数或年数差异。04YEAR、MONTH、DAY函数：分别提取日期中的年、月、日信息。05HOUR、MINUTE、SECOND函数：分别提取时间中的时、分、秒信息。06日期时间比较：可以直接使用比较运算符（如=、>、<等）对日期和时间进行比较。日期时间计算与比较2024/3/23230102EDATE函数返回指定月份数之后的日期。EOMONTH函数返回指定月份的最后一天的日期。`NETWORKDAY…计算两个日期之间的工作日天数（排除周末和指定假期）。WORKDAY函数返回指定工作日天数之后的日期。日期时间周期性处理结合使用上述函数，可以处理诸如“下个月的第一个工作日”、“本季度最后一天的日期”等周期性事件。030405周期性事件处理2024/3/2324PART06数据统计与分析函数2024/3/2325

数据排序与筛选排序功能EXCEL提供了多种排序方式，如升序、降序、自定义排序等，可以方便地对数据进行排序。筛选功能通过筛选功能，可以快速找出符合特定条件的数据，便于进行数据分析和处理。高级筛选高级筛选功能可以实现更复杂的筛选需求，如同时满足多个条件的数据筛选。2024/3/2326EXCEL的分类汇总功能可以按照指定的字段对数据进行分类，并计算各类别的汇总值，如求和、平均值、最大值、最小值等。分类汇总功能可以实现多级分类汇总，即在已分类的基础上再进行细分，满足更复杂的分析需求。嵌套分类汇总除了系统提供的汇总方式外，还可以通过自定义公式实现个性化的分类汇总。自定义分类汇总数据分类汇总2024/3/232703数据透视表分析利用数据透视表提供的切片器、筛选器等工具，可以对数据进行深入分析，发现数据间的关联和趋势。01数据透视表创建通过数据透视表功能，可以将大量数据转换成易于理解的报表形式，便于进行数据分析和决策。02字段设置与布局在数据透视表中，可以通过拖拽字段的方式灵活设置报表的布局和显示内容。数据透视表应用2024/3/2328PART07自定义函数与宏应用2024/3/2329使用VBA编辑器打开Excel，按Alt+F11进入VBA编辑器，插入模块并在模块中输入函数代码。函数结构自定义函数需遵循特定的结构，包括函数名、参数、返回值和函数体。调试与测试在VBA编辑器中，可以使用调试工具对自定义函数进行调试和测试，确保函数正确无误。自定义函数编写方法2024/3/2330宏录制01在Excel中，可以通过“开发者”选项卡中的“录制宏”功能来录制宏。在录制过程中，可以执行一系列操作，这些操作将被记录为宏代码。宏编辑02录制的宏可以在VBA编辑器中进行编辑和修改。通过编辑宏代码，可以优化宏的性能、添加错误处理等功能。宏安全性03在使用宏时，需要注意宏安