Excel基础操作进阶（数据分析）笔记

一、常用技巧

1. 输入对号√

   alt+小键盘41420 笔记本用ascll码221

2. 隐藏单元格设置格式三个英文分号

3. 防止输入重复值 数据验证自定义 公式 =countif(A:A，A1)=1

4. 插入行

   选中行，shift鼠标向下拉

5. 调换列，shift和鼠标结合

6. 批量求和 alt +=

7. 删除单元格看不见的空格

   alt+0+1 表示空格和换行符

8. ctrl+g 定位快捷键

9. ctrl+1 设置单元格格式快捷键

10. 双击，快速设置单元格宽度

11. ctrl+E智能拆分

12. .alt+↓生成以前输入过的值

13. shift+F2插入批注快捷键

14. 快速美化表格 ctrl+T

15. ctrl+shift+l 快速筛选快捷键

16. ctrl+Y 快速恢复

17. ctrl+shift+方向键 快速选中

18. ctrl+shift+加号 插入行、列

19. ctrl+减号 快速删除行、列

二、常用函数

2.1 清洗类函数

1. left():是一个字符串函数，它返回具有指定长度的字符串的左边部分

   left(text,[num_chars]),其中text:文本单元格，num_chars=字符串长度默认返回第一个字符，超出索引返回全部，不可为负数，0为空 例如：=left(D6,3)

2. right():是一个字符串函数，它返回具有指定长度的字符串的右边部分（与左边同理）

3. mid():从指定位置开始，提取用户指定的字符串数

   mid(text,start_num,num_chars),其中text:文本单元格，start_num:起始位置超出字符返回空，num_chars:字符串长度，例如：mid(D6,3,3)

4. len():返回文本字符串中的字符数

   从一开始计数

5. lenb():lenb函数返回字符长度，与len函数不同的是lenb双字节字符会返回2个长度，单字节返回一个长度

   一个中文字，算作2，一个英文，算作1

6. concatenate():将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中

   concatenate(text1,text2,…)

7. text():按指定格式将数值转成文本

   text(value,format_text),其中value为数值，format_text为设置value格式

8. trim():把单元格内容前后的空格去掉，但并不去除字符之间的空格

9. replace():将一个字符串中的部分字符用另一个字符串替换

   replace(old_text,start_num,num_chars,new_text),其中statr_num:替换位置字符的起始位置，num_chars:替换几个字符

10. substitute():指定字符串进行替换

    substitute（text，old_text,new_text,[instance_num]),其中instance_num,需要替换的字符串重复时需要替换的是第几个

11. find():查找一个字符串在另一个字符串的位置，区分大小写

    find(find_text,within_text,[statr_num]),其中find_text:要查找的字符串“”，within_text:要查找的单元格，start_num:起始位置，从第几个开始找

12. search():查找一个字符串在另一个字符串的位置，不区分大小写

    同理2.2 时间类函数

​

2.2 时间类函数

1. today():返回当前日期

   =today()+10,此函数可以与数字结合

2. date():返回表示特定日期的连续序列号

   =date(year,month,day)

3. eomonth():返回某个月份的最后一天

   =eomonth(start_date,months),其中，start_date：选中的日期，months:0表示当月，-1表示上个月，1表示下个月

4. year():提取日期的年份

   =year(选中日期)

5. month():提取日期月份

6. day():提取日期的天

7. weekday():提取返回某日期的星期数，默认情况下，它的值为1(星期天)到7（星期六）之间的一个整数

   =weekday(serial_number,[return_type]),其中serial_number:选中的日期，return_type:1表示1（星期天）到7（星期六） 2表示1（星期一）到7（星期日）

8. weeknum(serial_number,[return_type]):用于返回指定日期是一年中是第几个星期的数字

   serial_number:选中的日期

   return_type:1:一周的第一天为星期天，2：一周的第一天为星期一

2.3 逻辑类函数

1. if(logical_test,[value_if_true],[calue_if_false]):如果为真时则返回一个值，如果为假则返回另一个值

2. and(logical1，[logical2],…):如果条件都为真，则返回true,否则为false

3. or(logical1，[logical2],…):如果有一个条件为真，则返回true,若全部为假时，则为false

4. iserror(value):用于测试函数式返回的数值是否有错，如果有错，该函数返回true,反之返回false

5. iferror(value,valur_if_error):用于判断某些内容的正确与否，正确则返回正确的结果，错误则返回需要显示的信息

   iferror(要测试的内容，如果错返回内容“”)

6. ture():返回逻辑值true,不需要参数

7. false():返回逻辑值false,不需要参数

2.4 关联匹配类函数

1. vlookup(lookup_value,table_array,col_index_num,[range_lookup]):在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据

   一列一列

   lookup_value:要匹配查找的内容某一单元格

   table_array:去匹配查找的数组区域（匹配列在首列）

   col_index_num:返回的内容所在类数（首列为1）

   range_lookup:0精确配备 1近似匹配

2. HLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]):可以在表格或数值数组的首行查找指定的数值，并返回表格或数组中指定行的同一列的数值

   一行一行

   lookup_value:要匹配查找的内容某一单元格

   table_array:去匹配查找的数组区域（匹配行在首行）

   col_index_num:返回的内容所在列数（首行为1）

   range_lookup:0精确匹配 1近似匹配

3. lookup(lookup,lookup_vector,[result_vector]):函数是excel中的一种运算函数，实质是返回向量或数组中的数值，要求数值必须按升序排列

   lookup_value:要匹配查找的内容

   lookup_vector:去匹配查找的数组区域

   result_vector:返回的内容

4. index(array,row_num,[column_num]):返回表或区域中的值或值的引用，以行列数返回对应的内容

   array:选定的内容区域

   row_num：行（列）数以内容区域左上角为起点

   column_num:列数（可选）

   例如：=index(E7:G9,2,1) 返回第二行和第一列交叉的值

5. match(lookup_value,lookup_aarray,[match_type]):返回指定数值在指定数组区域中的位置

   lookup_value:要查找的内容单元格

   lookup_array:要在其查找的区域（行列）

   match_type:1小于或等于lookup_value的最大值

   ​ 0等于lookup_value的第一个值

   ​ -1大于或等于lookup_value的最小值

6. offset(reference，rows，cols,[height],[width]):以指定的（单元格或相连单元格区域中的引用）为参照物，通过给定偏移量得到新的引用

   reference:起点

   rows:上下偏移行数，向下为正，向上为负

   cols:左右偏移列数，向右为正，向左为负

   height（width）:可选引用区域行（列）数（含到达点）

7. row([refernce]):获取行号的函数

8. column([refernce]):获取列号的函数

2.3 计算统计类函数

1. count(value1,value2…):对给定数据集合或者单元格中数据的个数进行计数

   计算数字的个数

2. counta(value1,value2… ):计算区域中不为空的个数

3. countif(range,criteria):在指定区域中按指定条件对单元格进行计数(单条件计数)

   range:范围区域

   criteria:某一条件

   例如： =countif(F7:F10,“>200”) 返回区域中大于200的单元格的个数

4. countblank(range):统计指定区域内空白单元格的个数

5. countifs(criteria_range1,criteria1,criteria_range2,criteria2,…):统计多个区域中满足给定条件的单元格的个数

   criteria_range:范围区域

   criteria:某条件

6. sum():统计指定区域内数值的和

7. sumif(range,criteria,[sum_range]):对指定条件的值求和

   range:范围

   criteria:条件

   sum_range:实际求和范围

   例如：sumif(E7:F10,“datafrog”,F7:F10) 求姓名为datafrog的总花费

8. sumifs():统计多个区域中满足给定条件数据之和（单字段多条件求和）

9. sumproduct(array1*,[array2]*,[array3]…):在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和

10. max(number1,[number2 ]):返回一组中的最大值(可以多个区域中的数据)

11. min:返回一组中的最小值

12. average():返回一组中的平均值

13. mod(number,divisor):得出不能被整除的数，能被整除返回0，不能被整除返回余数

    number:被除数

    divisor:除数

14. rank(number,ref,[order]):返回一列数字的数字排位

15. round():四舍五入，保留小数

    number:数值

    num_digits：小数点后位数

16. floor():将数字向下舍入到最接近的整数或最接近的指定基数的倍数，如果number为正数，significance为负数，则函数返回错误值

    number:数值

    significance:向下舍入

    例如：floor(3.6,2) 将3.6向下取舍，使其等于2的倍数

17. rand():返回0-1之间的随机数，该函数不需要参数

18. int(数值):将单元格内的数值向下取整

19. randbetween():返回大于等于指定的最小值，小于指定最大值之间的一个随机整数

    randbetween(num,num2)

2.4 vlookup()函数的使用技巧

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

lookup_value:要匹配查找的内容某一单元格

table_array:去匹配查找的数组区域（匹配列在首列）

col_index_num:返回的内容所在类数（首列为1）

range_lookup:0精确配备 1近似匹配

1.基本使用

2.跨表使用

3.通配符查找

lookup_value与通配符结合使用 例如：B4&“*”

4.什么时候使用近似匹配

近似匹配找小于等于自己的最大值

近似匹配一般是在找数值区间的划分

5.数字格式问题

格式要一致

vlookup(–E16，，，)

文本型经过算术运算后会变成数值型

负负得正

6.match+index实现vlookup函数不能实现的条件不在第一列的情况

7.vlookup+match实现返回多列结果