EXCEL数据处理

标签：D数据分析 Excel

[TOC]

1. 单元格格式

1.1 转为指定格式的文本

text()

1.2 文本转为数值

2. 单元格结构

2.1 单元格长度

len() # 返回单元格字符串的长度

2.2 指定字符的位置

find("M", A1, 2) # 返回第2个字母M的位置数

find(text, within_text, num)

3. 返回值

3.1 左/右部分

left(A1,n)； right(A1,n)

3.2 指定位置的值

mid(A1,1,2) # 从第一个字符开始，返回2个字符的值

mid(text, start_num, num_chars)

3.3 特定条件的值

vlookup(A1, A1:D3, 2, 0) # A1在区域A1：D3中查询，并且返回区域A1：D3中从左到右第2列的值；0为精确匹配，即FALSE；

vlookup(lookup_value, table_array, col_index_num, [range_lookup])

4. 替换

4.1 常规替换

Ctrl + H

4.2 指定位置的值

replace(A1,3,4,"*") # 从第三个字符开始，之后的4个字符串替换我*号

replace(text,start_num,num_chars,new_text)

4.3 指定符号的值

substitute(A1,"^","*",2) # 对第2个出现的^号替换为*

substitute(text, old_text, new_text,[num])

5. 分列与合并

5.1 分列

数据 - 分列

5.2 合并

5.2.1 常规合并

使用符号 &

5.2.2 函数合并

concatenate(A1,A3,A4) # 将多个单元格中的值合并为一个单元格中的值

concatenate(text1,text2,…)

表中的结构与位置

单元格数量

非空单元格

counta(A1:A3) # 返回区域中非空单元格的数量

数值单元格

count(A1:A3) # 返回区域中数值单元格的数量

满足特定条件的单元格

countif(A1:B3,"<>"&A1) # 返回区域中不等于A1的值的单元格数量

countif(range, condition)

满足多区域，多条件的单元格

countifs(A1:A3,"是", B1:B3,500) # 返回同时满足区域A1:A3中等于“是”，B1：B3中数值为500的单元格数量

countifs(rang1, condition1, range2, condition2,…)

排序的位置

rank(1,A1：B2 ,0) # 返回数值1在区域A1：B2中的相对位置；默认按降序排列（0）

rank(num, range, 0/1) 1，表示按升序排列

Excel数据处理