数据分析之EXCEL常用函数总结

亦凉 2021-09-28 11:34 408阅读 0赞

今天通过excel分析数据的时候，突然大脑宕机，瞬间想不起来excel各种函数的用法，下面整理了一下excel的常用函数  
1、用函数识别重复数据的方法，COUNTIF函数

CUOUNTIF(range, cirteria):对区域中满足单个指定条件的单元格进行计数。
    range:要计数的单元格范围
    criteria:计算条件，其形式可为数字、表达式或文本；例如条件可以表示为：12、“12”、“>12”或“wxy”

2、判断语句IF函数，IF函数有三个组成部分：需要判断的表达式，表达式为真时的显示值，表达式为假时的显示值。

IF(logical_test, value_if_true, value_if_false):执行真假值判断，根据逻辑计算的真假值，返回结果;
    logic_test:表示计算结果为TRUE或FALSE的表达式。
    value_if_true:为true时返回的值。
    value_if_false:为false时返回的值。
    注意：
    a、条件表达式是用比较运算符（<、=、>）建立的式子，无比较就无判断。
    b、两个值若是数值数据可直接书写，若是文本数据则要根据双引号标记。
    c、参数里面所有用到的标点符号都是英文状态下的标点符号。
    d、IF函数可进行嵌套，最多可以有七层。

3、判断语句OR

OR(logical1, logical2,...):至少一个参数为真，就返回TURE
    logical1:要检验的第一个条件，结果可以为TRUE或FALSE;
    logical2:要检验的其他条件，结果可为TRUE或FALSE，最多可包含255个条件

4、判断语句AND

AND(logical1, logical2,...):所有参数全部为真，才返回TRUE
    logical1:要检验的第一个条件，结果可以为TRUE或FALSE
    logical2:要检验的其他条件，计算结果可以为TRUE或FALSE，最多可包含255个条件

5、提取字符之LEFT和RIGHT

LEFT(text, [num_chars]):得到字符串左部指定个数的字符。
    RIGHT(text, [num_chars]):得到字符串右部指定个数的字符。
    text:包含要提取的字符的文本字符串
    [num_chars]:指定要由LEFT或RIGHT提取的字符的数量。

6、合并字段之CONCATENATE函数和“&”（逻辑与）运算符

CONCATENATE(text1, text2, ...):将几个文本字符串合并为一个文本字符串。
    text1/text2：需要合并的第1、2....N个文本项（N<=30），这些文本项可以为文本字符串、数字或对单个单元格的引用。
    例如：
    =CONCATENATE(A2, "HELLO", B3, "world")
    
    注：在将数字和文本合并到一个单元格中时，数字将成为文本格式，故而无法再对其做任何数学运算操作。

7、TEXT函数

它的作用是在使用连接运算符连接数字与文本字符串时，控制数字的显示方式。如果不使用TEXT，则默认显示引用单元格中的基本数据，而不是在单元格中所看到的巨头格式的值（显示0.1而不是10%），使用TEXT函数可恢复原数字格式。
    例：=A3&"hello"&TEXT(B3, "0%")

8、VLOOKUP函数  
VLOOKUP函数在查找与匹配中的应用非常广泛，它的作用是在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的单元格内容。

VLOOKUP匹配函数：在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的单元格内容。
    VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
    lookup_value:要在表格或区域的第一列中查找的值，其参数可以是值或引用
    table_array:包含数据的单元格区域，可以使用绝对区域（如A2:D8）或区域名称的引用。table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。
    col_index_num:希望返回的匹配值的序列号，其参数为1时，返回的table_array第一列中的值；参数为2时，返回table_array第二列中的值；以此类推；
    range_lookup:近似匹配（1）还是精确匹配（0），一般情况选0

以下情况会出现错误标识符“\#N/A”：  
a、数据存在空格，此时可以嵌套使用TRIM函数将空格批量删除。  
b、数据类型或格式不一致，此时将类型或格式转为一致即可。  
9、函数计算

AVERAGE(number1, number2, ...):求平均值
    SUM(number1, number2,...):求和
    number1,number2可为数字、单元格引用、区域或定义的名称

10、日期的加减法

DATE(year, month, day):返回某指定日期
    YEAR(serial_number):返回某日期对应的年份
    MONTH(serial_number):返回以序列号表示的日期中的月份，用整数1~12表示。
    DAY(serial_number):返回以序列号表示的日期的天数，用整数1~31表示。

例如

=DATE(YEAR(A2)+3, MONTH(A2)+1, DAY(A2)+5)
    YEAR(A2)+3:代表年，含义取A2的年份再加3年；
    MONTH(A2)+1:代表月，即取A2的月份再加1个月；
    DAY(A2)+5:带表天，即取A2的天数再加5天；

输入当前日期使用“=TODAY()”或“=NOW()”就能轻松搞定。  
11、用DATEDIF计算工龄  
DATEIF函数就是计算两个日期之间年/月/日的间隔数

DATEIF(start_date, end_date, unit) :返回两个日期之间的年/月/日间隔数
    start_date:时间段内的起始日期
    end_date:时间段内的结束日期
    unit:有Y/M/D/MD/YM/YD六种形式：
    Y：指时间段中的整年数
    M：指时间段内的整月数
    D：指时间段内的天数
    MD：为start_date与end_date日期中天数的差，忽略日期中的月和年
    YM：为start_date与end_date日期中月数的差，忽略日期中的日和年
    YD：为start_date与end_date日期中天数的差，忽略日期中的年

举例：

=DATEDIF(B1, B2, "Y")&"年"
    =DATEDIF(B1, B2, "Y")&"年"&DATEDIF(B1, B2, "YM")&"月"

12、用VLOOKUP函数进行数据分组  
举例

=VLOOKUP(A2, $D$2:$E$12, 2)

13、HLOOKUP函数  
HLOOKUP匹配函数：在表格的首行查找指定的数据，并返回指定的数据所在列中的指定行处的单元格内容。

HLOOKUP(lookup_vaule, table_array, col_index_num, range_lookup)
    lookup_value:要在表格或区域的第一行中查找的值，其参数可以是值或引用
    table_array:包含数据的单元格区域，可以使用绝对区域或区域名称的引用。table_array第一行中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。
    col_index_num:希望返回的匹配值的行序号，其参数为1时，返回table_array第一行中的值；参数为2时，返回table_array第二行中的值，以此类推。
    range_lookup:近似匹配（1）还是精确匹配（0）

举例

HLOOKUP(1, B5:D5, 1 ,0)：在B5:D5区域的第一行中查找数值1，找到该数值所在列，再返回该列所对应的单元格区域的第一行数据，并要求精确匹配，也就是必须找到1，如果找不到1，则返回“#N/A”

14、SEARCH函数  
SEARCH:返回指定的字符串在原始字符串中首次出现的位置

SEARCH(find_text, within_text, start_num)
    find_text:要查找的文本字符串
    within_text:要在哪一个字符串查找
    start_num:从within_text的第几个字符开始查找
    注：
    在find_text中，可以使用通配符，例如：“？”“*”，其中“？”代表任何一个字符，而“*”可以代表任何字符串。如果要查找的字符串就是问号或星号，则必须在这两个符号前加上“~”符号。

15、RAND函数

RAND():返回[0,1]的均匀分布随机数，而且每次计算工作表时都将返回一个新的数值。
    如果在编辑栏中输入“=Rand（）”后，保持编辑状态，按“F9”键，则生成的随机数将永远被保存，不再返回新的数值。