实战讲解丨数据分析常用的43个Excel函数及用途

Excel是我们工作中经常使用的工具，也是处理数据最基本的工具。许多传统行业的数据分析师甚至可以掌握Excel和SQL。对于初学者来说，有时他们不需要急于学习R语言和其他专业工具（当然，这也是一个奖励项目），因为Excel涵盖了足够的功能，并且有许多统计、分析和可视化的插件。然而，当我们处理数据时，我们不知道如何使用许多函数。Excel的高级学习主要分为两部分：一是常用的Excel函数进行数据分析，二是用Excel进行简单完整的分析。本文主要介绍了数据分析中常用的43个Excel函数和用途，下一篇将解释实战分析。关于函数：Excel的函数实际上是一些复杂的计算公式。函数将复杂的计算步骤交给程序处理。只要将相关参数输入函数格式，就可以得到结果。SUM(A1)可以直接用于一个区域的和:C100)形式。所以对于函数，不要刻意记忆和背诵，只要你知道，比如“选择字段，使用Left／Right／Mid“函数，以及如何使用哪些参数，复杂的交给百度。函数分类:关联匹配类清洗处理类逻辑运算类计算统计类时间序列类1。关联匹配类是常规的，所需的数据不在同一个excel表或同一个excel表的不同sheet中。数据太多，复制麻烦不准确。如何整合？这种函数是用于多表关联或行列比较的场景，表越复杂，用得越多。函数HLOKUP和VLOKUP都是用来在表格中找到数据的。1、VLOOKUP功能：用于搜索首列符合条件的元素。语法：=VLOOKUP(需要找到的值应该包含返回值的列号，准确匹配或类似匹配–指定0/FALSE或1/TRUE）。例如：查询F5单元格中员工姓名的职位2、HLOKUP功能：搜索表的顶行或值数组中的值，并在表或数组中指定的行的同一列中返回一个值。语法：=VLOOKUP(需要找到的值应该包含返回值的行号，准确匹配或类似匹配在其中搜索值的区域–指定0/FALSE或1/TRUE）。区别:HLOKUP返回值与需要找到的值在同一列，VLOKUP返回值与需要找到的值在同一行。3、INDEX功能：返回表格或区域中的值或引用值。语法：=INDEX(返回值的单元格区域或数组、行、列)4、MATCH功能:用于在指定区域(某行或某列)返回指定内容。语法：=MATCH(单元格区域或数组返回值，搜索区域，搜索方法)5、RANK功能：在某一区域的一组数值中寻求某一数值的排名。语法：=RANK(参与排名的值，排名的值区域，排名模式-0是降序-1是升序-默认为0)。6、Row功能:返回单元格所在行7、Column功能:返回单元格所在列8、Offset功能：根据行列偏移量，从指定的基准位置返回指定的引用语法：＝Offset(指定点，偏移多少行，偏移多少列，返回多少行，返回多少列)二、清洗处理数据处理前，需要对提取的数据进行初步清洗，如清除字符串空间，合并单元格、替换、截取字符串、查找字符串的位置等。使用Trim//Ltrim/Rtrim合并单元格：使用concatenate截取字符串：使用Left//Right/Mid替换单元格内容：Replace/Substitute在单元格中找到文本的位置：Find/Search9、Trim功能:清除字符串两侧的空间10、Ltrim功能：清除单元格右侧的空间11、Rtrim功能：清除单元格左侧的空间12、concatenate语法：=Concatenate(单元格1，单元格2..)合并单元格中的内容，另一种合并方式是&，当需要合并的内容太多时，concatenate效率更快。13、Left功能:从左截取字符串语法:=Left(值所在单元格，截取长度)14、Right功能：从右截取字符串语法：=Right(值所在单元格，截取长度)15、Mid功能:从中间截取字符串语法:=Mid(指定字符串，开始位置，截取长度)例:根据身份证号码提取16个月、Replace功能：替换单元格字符串语法：=Replace(指定字符串，开始更换哪个位置，替换多个字符，替换什么)17、Substitute接近replace，区别在于replace根据位置实现替换，需要从第一位开始提供替换，替换后的新文本；Substitute根据文本内容替换，需要提供替换的旧文本和新文本，以及替换的旧文本。因此，Replace实现了固定位置的文本替换，Substitute实现了固定文本替换。例如：替换部分电话号码18、Find功能：找到文本位置语法：=Find(找字符，指定字符串，第几个字符)19、Search功能：返回指定字符或文本字符串在字符串中首次出现的位置，从左到右查找语法：=search(要找到的字符，字符所在的文本，从第几个字符开始搜索)Find和Search功能几乎相同，实现搜索字符的位置，区别在于Find函数准确搜索，区分大小写；Search函数模糊搜索，不区分大小写。20、Len功能:文字字符串字符数21、Lenb功能：返回文本中包含的字符数3、逻辑操作类22、IF功能:使用逻辑函数IF函数时，如果条件是真的，函数将返回一个值；如果条件是假的，函数将返回另一个值。语法：=IF(条件，true返回值，false返回值)23、AND功能：逻辑判断，相当于“并”。语法：=IF(条件，true返回值，false返回值)23、AND功能：逻辑判断，相当于“并”。语法：所有参数为True，则返回True，常用于多条件判断。24、OR功能：逻辑判断，相当于“或”。语法:只要参数有True，就返回Ture，常用于多条件判断。四、计算统计类在使用excel表格统计数据时，往往需要使用各种excel自带的公式，也是最常用的一种。重要性不言而喻。但excel都有自己的快捷功能。MIN函数:在某一区域找到最小值MAX函数:在某一区域找到最大值*ERAGE函数:在某一区域计算平均值COUNT函数:在某一区域计算包含数字单元格的数字COUNTIF函数:在某一区域计算满足给定条件的单元格数COUNTIFS函数:统计一组给定条件指定的单元格数SUM函数:计算单元格区域的所有值和SUMIF函数:对于满足条件的单元格和SUMIFS函数:对于一组满足条件指定的单元格和SUMPRODUCT函数:返回相应的数组或区域乘积和25、MIN功能:在某个区域找到最小值26、MAX函数功能:在某个区域找到最大值27、*ERAGE功能：计算某一区域的平均值28、COUNT功能：计算含有数字的单元格的数量。29、COUNTIF功能：在某一区域内计算符合给定条件的单元格数字语法：=COUNTIF(单元格1:例如，单元格2，条件=COUNTIF(Table1！29、COUNTIF功能：在某一区域内计算符合给定条件的单元格数字语法：=COUNTIF(单元格1:例如，单元格2，条件=COUNTIF(Table1！A1:Table1！C100,“YES)计算Table1中A1至C100区域单元格中值为“YES“单元格个数例子：统计制定店铺业务笔数30、COUNTIFS功能：统计单元格数语法指定的一组给定条件：=COUNTIFS(第一个条件区，第一个相应的条件，第二个条件区，第二个相应的条件，第N个条件区，第N个对应条件)例如:=COUNTIFS(Table1！A1:Table1！A1:Table1！A100YESTable1！C1:Table1！C100,“NO)计算Table1中A1至A100区域单元格中值为“YES同时，C区域值为“NO单元格数为31、SUM计算单元格区域中的所有值和32、SUMIF功能：单元格和语法符合条件：=SUMIF(单元格1:单元格2，条件，单元格3:单元格4)例:计算一班总分32、SUMIFS功能：对一组符合条件的单元格求和语法：=SUMIFS(实际求和区、第一个条件区、第一个相应的求和条件、第二个条件区、第二个相应的求和条件、第N个条件区、第N个相应的求和条件)=SUMIFS(Table1！C1:Table1！C1:Table1！C100，Table1！A1:Table1！A100,“YESTable1！B1:Table1B100，“NO)计算Table1中C1至C100区域，同时相应行A列值为“YES与B列值相对应的是“NO单元格的和。33、SUMPRODUCT功能：返回相应的数组或区域乘积和语法：=SUMPRODUCT(单元格1:单元格2，单元格3:例如：单元格4)：=SUMPRODUCT(Table1！A1:Table1！A100，Table2！B1Table2！B100)计算A1-A100和B1-B100的乘积，即A1*B1 A2*B2 A3*B3 …34、Stdev统计函数，要求标准差。35、Substotal语法：=Substotal(引用区域、参数)汇总函数，将平均值、计数、最大、最小、相乘、标准差、求和、方差等参数化。换句话说，只要你知道这个函数，上面的都可以抛弃。36、Int／Round取整函数，int向下取整，Round按小数位取数。round(3.1415)=3.14;round(3.1415，1)=3.15，时间序列类专门用于处理时间格式和转换。37、TODAY返回今天的日期，动态函数。38、NOW返回当前时间，动态函数。39、YEAR功能:返回日期的年份。40、MONTH功能:返回日期的月份。41、DAY功能:返回以序列数表示的日期天数。42、WEEKDAY功能：返回对应于某一日期的一周中的第几天。默认情况下，天数为1(周日)至7(周六)范围内的整数。语法：=Weekday(指定时间，参数)43、Datedif功能：计算两个日期之间的天数、月数或年数。语法：=Datedif(开始日期、结束日期、参数)