EXCEL 数据分析基础知识

1、编码

1 byte可以存256个字符，最初的存储方式ASCII，就存了英文+数字+符号

汉字远远大于256种可能，与使用2byte组合表示，叫做GB2312，后来为了表示更多的汉字，用了GBK，它是拓展版，连繁体字都包括了，

可是中国还有少数民族啊，少数民族的文字怎么办，于是有了GB18030，

可是全世界有多少国家多少少数民族？，于是发明了万国码，叫做unicode

UTF-8是一种整合版编码，编码英文的时候和ASCII一样采用8个字节，但是编码中文的时候却要32位，所以有时候为了节省存储空间，更喜欢用12位的GB系列编码方式编码含有中文的数据。

2、常见文本清洗函数

FINd : 找到要查找对象第一次出现的位置， find("要找的对象",要找的区域)

LEFT : 从左边第一个位置开始截取， left(要截取的对象，截取结束位置)

RIGHT : 从右边第一个位置开始截取， right(要截取的对象，截取结束位置)

CONCATENATE : &只可以单个字符拼接，concatenate可将最多 255 个文本字符串合并为一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合。CONCATENATE(text1, [text2], ...)

REPLACE : REPLACE函数的作用是根据指定的字符数，将部分文本字符串替换为不同的文本字符串。

REPLACE(old_text, start_num, num_chars, new_text)

TRIM : 删除对象前后的空格

 

SUBSTITUTE : 在某一文本字符串中替换指定的文本。SUBSTITUTE(text,old_text,new_text,[instance_num]),

Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。Old_text 为需要替换的旧文本。New_text 用于替换 old_text 的文本。Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；如果缺省则将用 new_text 替换 TEXT 中出现的所有 old_text。

len : 提取对象的长度

mid : 从中间提取。MID(text, start_num, num_chars)

3、关联匹配函数

VLOOKUP : 表关联查询

 

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

 

参数	简单说明	输入数据类型
lookup_value	要查找的值	数值、引用或文本字符串
table_array	要查找的区域	数据表区域
col_index_num	返回数据在查找区域的第几列数	正整数
range_lookup	模糊匹配/精确匹配	TRUE（或不填）/FALSE

index : 返回表或区域中的值或对值的引用,返回的是单元格的值

 

输入公式=INDEX(B3:D6,4,3)

数据表区域（B3:D6),行数(4),列数(3)

 

MATCH 函数是EXCEL主要的查找函数之一，该函数通常有以下几方面用途：(1) 确定列表中某个值的位置；(2)对某个输入值进行检验，确定这个值是否存在某个列表中；(3)判断某列表中是否存在重复数据；(4)定位某一列表中最后一个非空 单元格的位置。查找文本值时，函数 MATCH 不区分大小写字母。

MATCH函数 含义 ：返回指定数值在指定 数组 区域中的位置

语法 ：MATCH(lookup_value, lookup_array, match_type)

row : 返回所在行

COLUMN ：  返回所在列

Offset : 是 Excel 中的函数，在Excel中，OFFSET函数的功能为以指定的引用为参照系，通过给定 偏移量 得到新的引用。返回的引用可以为一个 单元格 或 单元格区域 。并可以指定返回的行数或列数。Reference 作为 偏移量 参照系的引用区域。Reference 必须为对 单元格 或相连 单元格区域 的引用；否则，函数 OFFSET 返回 错误值 #VALUE!。

函数语法

OFFSET(reference,rows,cols,height, width)

HYPERLINK : 转换为超链接

4、逻辑运算函数 ：  TRUE（1)   FALSE（0）  AND OR  IF  IS系列

5、逻辑运算函数：

SUM : 求和           SUMPRODUCT ： 两列乘积累加           COUNT ：统计个数         MAX/MIN ：最大最小值

RANK ： 查排名     RANDBETWEEN ：生成随机数字，RANDBETWEEN(1,10)       AVERAGE ：平均数

QUARTILE ： 求分位数      STDEV ： 求标准差  

SUBTOTAL ： 

 

语法: SUBTOTAL(function_num,ref1,ref2, ...)

Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。

Function_num （包含隐藏值）

为1到11之间的自然数，用来指定分类汇总计算使用的函数

1 、1 AVERAGE（算术平均值）

2、 2 COUNT（数值个数）

3、 3  COUNTA（非空单元格数量）

4 、4 MAX（最大值）

5 、5 MIN（最小值）

6 、6 PRODUCT（括号内所有数据的乘积）

7 、7 STDEV（估算样本的标准偏差）

8 、8 STDEVP（返回整个样本总体的标准偏差）

9、 9 SUM（求和）

10 、10 VAR（计算基于给定样本的方差）

11 、11 VARP（计算基于整个样本总体的方差）

 

INT : 向下取整，int(1.2345) = 1               

 ROUND：四舍五入取整， round(取整对象，小数位数) round(1.5342,2) = 1.53,若小数位数为负值，则会向百分位上扩展，如：round(976.396,-1) = 980

COUNTIF ：按条件统计 countif(统计区域,条件)   countif(Q:Q,>10)

COUNTIFS ：按多条件统计 countif(统计区域,条件，统计区域,条件)   countif(Q:Q,>10,R:R,<20)

SUMIF ：按条件求和, sumif(条件区域，条件，求和区域)

AVERAGEIF ： 按条件求平均数 

ABS ： 绝对值

6、时间序列函数

YEAR ： 返回年   MONTH ： 返回月   DAY： 返回日

WEEKDAY ： weekday(区域,格式) 返回代表一星期中某天的整数。   格式为1表示星期日为第一天，2表示星期一为第一天

WEEKNUM ： 返回本周是一年中的第几周

NOW ：返回当前时间，精确到秒              TODAY：返回当前时间，精确到天

DATE ： 日期拼接  date(年，月，日)

7、快捷键

Ctrl + 方向键 光标快速移动

Ctrl + Shift +方向键，快速框选

Ctrl + 空格键，选定整列

Shift + 空格键，选定整行

Ctrl + A，选定整张表

Alt + Enter 换行

 

 

 

: