常见文本清洗函数
乱码问题:txt打开后再复制到excel 或使用其他转编码工具
find:确定字符位置
=find(find_text,within_text,start_num)
Find(要查找的文本,文本所在的单元格,从第几个字符开始查找[可选,省略默认为1,从第一个开始查找])
substitute:替换字符
=substitute(text,old_text,new_text,[instance_num])
=substitute(需要替换的文本,旧文本,新文本,第N个旧文本)
left right mid:对单元格内容进行截取。从左/右/中第一个字符开始截取,截取指定的长度
=left(text,num_chars)
text:将数值转化为自己想要的文本格式
=text(value,format_text)
concatenate:把多个字符文本或数值连接在一起,实现合并的功能
=concatenate(text1, [text2], ...)
Text1,Text2可以是文本或者数值
trim:去掉字符前后和字符之间的空格,在去掉字符之间的空格时,不会把全部空格都去掉,会留下一个空格
如果文本中包含有不可见的非空格元素,则TRIM 无法去除,可以使用SUBSTITUTE函数来进一步去除
replace:用新字符串替换旧字符串,而且替换的位置和数量都是指定的
=Replace(old_text,start_num,num_chars,new_text)
=replace(要替换的字符串,开始位置,替换个数,新的文本)
len:返回文本串的字符数
关联匹配函数
vlookup/index/match:
lookup:
=LOOKUP(lookup_value,lookup_vector,result_vector)
lookup_value意思为我们要在数据表中查找的“值”
lookup_vector意思为我们要查找的值的“数据表”
result_vector意思为我们通过数据表想要得到的"值“
row:
=row(reference)
如果省略reference,则默认返回row函数所在单元格的行数。
column:
=column(reference)
如果省略reference,则默认返回函数column所在单元格的列数
offset:
=OFFSET(Reference, Rows, Cols, [Height], [Width])
OFFSET(引用单元格, 行数, 列数, [返回引用高度], [返回引用宽度])
参数Rows——行偏移量
正数(代表在参照单元格的下方),负数(代表在参照单元格的上方)。
参数Cols——列偏移量
正数(代表在参照单元格的右边)或负数(代表在参照单元格的左边)。
参数Height——返回几行(必须为正数),即所要返回的引用区域的行数。
参数Width——返回几列(必须为正数),即所要返回的引用区域的列数。
hyperlink:
=HYPERLINK(link_location,[friendly_name])
Link_location 可以作为文本打开的文档的路径和文件名
Friendly_name 非必要参数,可以不存在。单元格中显示的跳转文本或数字值
逻辑运算函数
True False And Or IF Is Not
计算统计函数
sum:求和
sumproduct:多数组对应元素相乘后相加
count:计数 只计算数字型
counta:计数 非空单元格
max min:最大值 最小值
rank:得到单元格在数组中的排序
rand randbetween:生成随机数
averagea:平均值
quartile:四分位数
stdev:标准差
substotal:=Subtotal(功能代码,数值区域) 可执行多种操作
int:取整
round:按照指定位数四舍五入
时间序列函数
year:提取年份
month:提取月份
day:提取日
date:合并为日期
weekday:返回一周中第几天
now:当前系统时间
weeknum:一年中是第几个星期
today:当前系统日期