Excel相关常识

Excel前言

1.认识Excel

Excel中几种常用的文件类型：
a.XLS/XLSX 工作簿文件；
b.XLW 工作区文件；
把一个窗口变成两个窗口：视图选项卡——>新建窗口——>全部重排——>垂直并排，点击保存工作区，后续即可直接查看（选择xlw文件类型）新版的Excel没有这个功能了
Excel能做什么：数据存储——>数据处理——>数据分析——>数据呈现
敲F1会跳转到帮助
文件——>选项——>高级——>Lotus兼容性勾上，就可以写公式的时候不写“=”
注意：平时不要勾！！！
更改工作表标签颜色

1）交换两列的顺序：

选中整列，按住shift键拖拽

2）怎样到一个表格的最后：

选中一个单元格，在鼠标变成上下左右那个标识时双击朝下（就会到表格最后），朝上（到表格开头）

3）冻结窗格：

总是以当前单元格在哪进行冻结（总是冻结该单元格上方和左侧的单元格）；
（滚动的时候保持第一行不动）视图——>冻结窗格（冻结拆分窗格，可选中多行冻结）——>冻结首行

4）按Ctrl 再拖拽就会填充序列

右击——>以工作日填充

自定义填充：文件——>选项——>高级——>编辑自定义列表

在名称框中输入即可选中任意单元区域

1.分类汇总、数据有效性

分类汇总之前需要先排序！！！（把同类的数据放在一起）

先排序：
做两次分类汇总：不勾选替换当前分类汇总！

1）复制汇总后的数据：定位——>可见单元格

步骤：先选中数据——定位条件——>可见单元格——>ctrl C——>ctrl V

2）使用分类汇总批量合并内容相同的单元格

复制——>右击——>选择性粘贴——>仅粘贴格式

结果：


然后顺手点击合并单元格，结果：

最后删除分类汇总，这样就会多一列（合并单元格的这一列）

3）数据有效性

自定义中可直接写公式定义该单元格

2.单元格格式设置

1）使用"分列"工具

2）数字格式（Excel日期从1900年开始）

**当Excel中日期是文本格式的时候你这时候修改日期样式是不会变的！！！**如何处理？使用分列工具，它可以让数据在不同值之间跳转！（什么都不选，直接点击下一步）

一、数据透视表

数据透视表：做数据汇总统计的工具，选中任意含有数据的单元格，插入数据透视表。
经典数据透视表布局：右击——>数据透视表选项——>显示

1.更改数据透视表汇总方式：值字段设置，双击数据透视表中汇总的值可返回该值原始数据

2.数据透视表中的组合

1）日期组合：

注意：表中如果有空格，会导致日期没法组合（或者必须是同一类型：全是日期或者全是数值）
选择任意一个时间，右击——>组合

2）数值的组合

点击任一数据——>组合

3.汇总多列数据

员工姓名——>值字段设置——>无，即可取消汇总（使姓名和工号一一对应）

多次添加该字段，即可汇总多列数据

4.创建计算字段（即在数据透视表中写公式）

数据透视表中写公式：域，项目合集（计算字段）


右击——>设置单元格格式——>百分比；
设置错误值显示方式：数据透视表任一单元格——>数据透视表选项——>勾选对于错误值显示

5.生成多张工作表

生成多张工作表（分析，选项，显示报表筛选页）

按住shift键选中所有的表！！！

二、函数

&：连字符
<>：不等于
比较运算符的结果：TRUE FALSE
TRUE 1
FALSE 0

单元格引用：
绝对引用（位置不变） 按F4
相对引用（位置变）

使用定位工具（写公式时手工选区域不方便）：

ctrl +Enter 批量填充公式

1.IF函数：函数语法：IF(logical_test,[value_if_true],[value_if_false])

参数：logical_test 可为数值或者逻辑表达式；
value_if_true：当logical_test 为True时返回的结果；
value_if_false：当logical_test 为False时返回的结果

IF嵌套
公式不加双引号：


套的层次太多考虑使用vlookup函数

=IF(G6=“A级”,10000,0)+IF(G6=“B级”,9000,0)+IF(G6=“C级”,8000,0)+IF(G6=“D级”,7000,0)+IF(G6=“E级”,6000,0)+IF(G6=“F级”,5000,0)+IF(G6=“G级”,4000,0) 注：没有嵌套，只是每一个可能都想加起来

使用VLOOKUP:

=IF(G21=“A级”,“一级”,“”)&IF(G21=“B级”,“二级”,“”)&IF(G21=“C级”,“三级”,“”)&IF(G21=“D级”,“四级”,“”)

=IF(ISERROR(D35/C35),0,D35/C35)

=IF(AND(A3=“男”,B3>=60),1000,0)

=IF(OR(AND(B20>60,A20=“男”),AND(B20<40,A20=“女”)),1000,0)

2.COUNTIF()函数：带条件的数数

COUNT() 数数（数带有数字的单元格有多少个）
COUNTIF(range,criteria) 带条件的数数，参数:range,条件区域；criteria，条件

某个区域内某个银行卡有几个：

正确做法：（仍旧有点问题）
注意：需要使用绝对引用
思路：左边的人在右边里面有几个？如果有1个就是体检过了，0就是没有体检


**加颜色：**条件格式（当那个值符合什么条件的时候就把它改为什么颜色）开始——>条件格式——>新建规则：使用公式确定要设置格式的单元格

数据有效性：在某个区域里做一个属性


注意：需要绝对引用

1）COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2):多个条件

参数：条件区域1，条件1，条件区域2，条件2

3.SUMIF()函数：对满足条件的单元格求和

SUMIF(range,criteria,[sum_range])
参数：条件区域，条件，[求和区域] 可选


关于第三参数简写问题的注意事项（sumif ），第三参数可以简写成F1之类的

1）跨区域求和：第一个参数和第三个参数平齐

2）把多个条件组合成一个条件：

或者使用sumifs（多个条件求和），不同的是第一参数就是求和区域，后面的参数是条件！
参数：求和项，条件区域1，条件1，条件区域2，条件2

替代vlookup

数据有效性：

4.VLOOKUP函数：总共四个参数

lookup_value：要查找的值；
table_array：要在其中查找值的区域；最左侧不能多选，或者说需要保证最左侧一列和要查找的值那列相同
col_index_num：区域中包含返回值的列号；
range_lookup：精确匹配或者近似匹配（精确匹配用0或者False指代，近似匹配用1或True指代，参数省略时默认近似匹配）
如果没有选取整列，而是选择的某一个区域，需要使用绝对引用！

1）基本用法

2）跨表引用(注意表不要来回切换）

3）注意使用连字符和需要使用精确匹配0

使用通配符做精确匹配！！！

4）模糊匹配：觉得表里面没有还要去找的时候

工作中大部分都是精确匹配！！！找一些数值区间的划分会使用模糊匹配！使用模糊匹配时查找的值要从小到大排序！

5）数字格式问题：通过数值找文本

1.把一个数值连接一个东西Excel会把它处理成文本

2.文本转数值
把文本*1，文本会变成数值（或者将它经过运算，都会变成数值）

3.先用数值找然后再用文本找（或者直接文本转换也行）
如果数值找不出来，就用文本，否则就用数值！

6）HLOOKUP:之前一行是一条数据，现在一列是一条数据

5.Match和Index

1）Vlookup函数语法

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

2）MATCH(lookup_value,lookup_array,[match_type])

Vlookup缺点： 在使用它时都是使用左侧的数据来查询右侧的某一个数据，无法通过右侧的查找左侧的数据！！!（有时可以互换AB两列位置）
使用vlookup的时候做了两件事：一是查找；二是引用。
**Match只负责找到位置！！！**不负责引用。Match的第一参数相当于vlookup的第一参数，第二参数相当于vlookup的第二参数的左侧的一半，第三参数相当于vlookup的第四参数

3）INDEX(array,row_num,[column_num])

给它一个区域，到区域中返回值！：在什么范围取，取这个范围的第几个。

组合两个函数：

4）Match+Index与Vlookup函数比较

数据源：

5）使用Match与Vlookup函数嵌套返回多列结果

混合引用

与原表结构一致的：

使用match()做：

与原表结构不一致的：

6）认识column函数：求列号的

括号里什么都不写都可以，这时候它会返回当前所在单元格的列号

按住Alt键拖，使图片充满单元格！

6.邮件合并（Word中）

1）每页显示多条记录

邮件——>开始邮件合并——>邮件合并分部向导——>选择目录——>选择使用当前文档

7.日期函数

实际是天数！（自1900年起）



datedif（）函数：
第一参数：起始日期；
第二参数：终止日期；
第三参数：返回的值（年或月或者日）

YM:去掉年份，算月数；（忽略日期中的天和年份）
MD:去掉月份，算天数

WEEKNUM():返回第几周，两个参数：第二个参数，把星期几做为一周的第一天。

WEEKDAY():算周几




把文本*1会变成数字！，然后设置单元格格式选择日期即可

8.条件格式与公式

切片器：

可以看成筛选按钮或者分类按钮

回到最初的时候：清除筛选器


先做大范围，再做小范围：（和if函数相反）

自定义条件格式：

9.简单文本函数

1）从中间开始取：mid()

先取17位，然后再获取最后一位数：

2）FIND():

返回一个字符串在另一个字符串中出现的起始位置

3）LEN(),LENB() 分别求字符和求字节

通过文本处理函数处理的数据一定是文本！需要先将文本转成数值

2）使用MOD函数与文本函数，提取身份证号性别信息

10.数学函数

ROUND():参数1：四舍五入的值；参数二：小数点后的位数
ROUNDUP()：进位
ROUNDDOWN()：舍弃
INT():直接取整
MOD():求余数


转置：

ROW():求行数

11.VLOOKUP函数与数组

sumif（）：参数：条件区域，条件，求和区域

SUMIFS()参数:求和区域，条件区域1，条件1，条件区域2，条件2

使用VLOOKUP，作多条件查询用啥呢？VLOOKUPS？没有！
如果是数组公式，按住Ctrl+Shift+Enter


注意：以上选的是一个区域，需要使用绝对引用！
可以使用SUMPRODUCT()，就不用三键了直接按回车即可

复习VLOOKUP()

LOOKUP()没有第四参数（精确匹配或者模糊匹配）

它采用的是模糊匹配，需要把它变成精确匹配

第一个参数写0或者1都行。

使用LOOKUP做精确匹配

注意：上图使用Ctrl+Shift+Enter

12.indirect函数

间接引用

1）跨表引用

做多个表的汇总
首先使用文本描述的方式，再使用INDIRECT()

跨表引用（顺序不同）

需要使用混合引用！

2）制作二级下拉列表

数据验证——序列

制作二级下拉列表


为什么使用indirect(F1)：因为之前已经将吉林省下面的所有城市定义成了吉林省

三、图表基础

动态图表

在系列创建中是单列引用的！