数据清洗类
- trim
功能:去除单元格两端的空格。
语法:=TRIM(text) - concatenate
功能:连接单元格内的内容
语法:= CONCATENATE(text1, [text2], …) - &
连接符 - left right mid
MID
功能:提取字符串中间的字符串
语法:= MID(text, start_num, num_chars)
LEFT
功能:提取字符串左边的字符串
语法:= LEFT(text, [num_chars])
RIGHT
功能:提取字符串右边的字符串
语法:=RIGHT(text,[num_chars]) - replace substitute
replace
功能:替换字符串中的连续几个字符或者某个字符
语法:= REPLACE(old_text, start_num, num_chars, new_text)
substitute
功能:替换字符串中的连续几个字符或者某个字符
语法:=SUBSTITUTE(text, old_text, new_text, [instance_num]) - LEN、LENB
=LEN(text)
对于len来说,一个中文字符的长度计算为1,对于lenb来说,一个中文字符的长度计算为2,对于英文字符或者数字则没有差异
关联匹配类
- vlookup
纵向查找
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
近似匹配,返回小于该数值的最大数值。
column()
返回列号 - HLOOKUP
横向查找
语法:= Hlookup(lookup_value、table_array、row_index_num)、[range_lookup])
近似匹配,返回小于该数值的最大数值。 - index
功能:根据位置返回单元格的值
语法:= INDEX(array, row_num, [column_num]) - match
功能:根据单元格的值返回位置。
语法:= MATCH(lookup_value, lookup_array, [match_type]) - rank
功能:返回一列数字的数字排位。数字的排位是其相对于列表中其他值的大小。
语法:RANK(number,ref,[order])
RANK.EQ
逻辑运算类
- if
语法:IF(logical_test, value_if_true, [value_if_false]) - and
功能:逻辑判断,相当于“并”。
全部参数为True,则返回True - or
功能:逻辑判断,相当于“或”。
只要参数有一个True,则返回Ture - Count/Countif/Countifs
countifs
功能:多条件统计。
语法:COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) - sum/sumif/sumifs
sumif
功能:条件求和。
语法:SUMIF(range, criteria, [sum_range])
sumifs
功能:多条件求和。
语法:SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) - sumproduct
功能:在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
语法:SUMPRODUCT(array1, [array2], [array3], …) - Average/Averageifs
average
功能:返回参数的平均值(算术平均值)。
语法:AVERAGE(number1, [number2], …)
averageifs
功能:返回满足多个条件的所有单元格的平均值(算术平均值)。
语法:AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) - QUARTILE
功能:返回一组数据的四分位点。 四分位点通常用于销售和调查数据,以对总体进行分组。
语法:QUARTILE(array,quart) - Substotal
语法:=Substotal(引用区域,参数)
汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化等
时间序列类
-
year,month,day
year
功能:返回对应于某个日期的年份。Year作为1900 - 9999之间的整数返回。
语法:YEAR(serial_number)
month
day -
weekday
功能:返回对应于某个日期的一周中的第几天
语法:WEEKDAY(serial_number,[return_type]) -
WEEKNUM
功能:返回特定日期的周数。 例如,包含 1 月 1 日的周为该年的第 1 周,其编号为第 1 周。
语法:WEEKNUM(serial_number,[return_type]) -
DATEDIF
功能:计算两个日期之间相隔的天数、月数或年数。
语法:DATEDIF(start_date,end_date,unit)