#笔记：为了方便自己以后查找，以便随时随地能查看、形成系统化学习！

1. 查找引用函数 ------------------包含----------Vlookup函数（if数组）、Hlookup函数、lookup函数、index函数+match函数、indirect 函数（数据有效性、row函数
2. 日期时间函数------------------包含----------
3. 数学函数
4. 统计函数
5. 数组入门
6. 函数常见错误

查找引用函数：

vlookup函数

tips：

1，标准表格数据干净，引用整列就好了；

2，若不是1，则使用绝对引用，使用F4来切换；

精确匹配

=vlookup(查找依据值，查找区域，第几行，0/false）

tips：查找依据必须为首列否则会报错！！！！！！！

1.如果所查询的区域首列有相同的数据，用精确查找的方式得出的结果将始终是第一数值；

2.另外如果所查询区域在首列没有查找的对象，其结果将出错；

近似匹配

=vlookup(需要查找的值，查找区域，第几行，1/true或忽略不写）

tips：使用有规律、会返回一个靠近值并且比它小的数；

1.如果所查询的区域首列有相同的数据，用模糊查找的方式得出的结果将很乱，没有规律；（因此模糊查找第一规律是必须要满足对引用的数字区域进行升序排列）

2.另外如果查询区域没有首列没有查找的对象，其结果将不会出错，始终有一个值，

两个区域，其里面的数值一样，区域一的数值没有排序，区域二的数值经过升序排列；

下面我用VLOOKUP函数模糊查找的方式（第4个参数使用TURE或者1），分别用数值25、60在区域一、区域二查找，

其返回的值显示没有进行排序值都是16，这样会无规律可寻显然错误，如果进行了排序的其返回的值是有规律的

（即给定一个值它会找到最近的值，并比它小的一个数，如本例区域二如果给定25，它会找到大小比较接近的值16和27，返回最小的数16；

区域二如果给定60，它会找到大小比较接近的值49和70，返回最小的数49）

Hlookup函数与vlookup函数一样，试试查找值为行；

对于查找值在首列或者依据值在查找值后面的情况

=vlookup(l21,if({0,1},c:c,a:a),2,0)

tips：

if数组：

if（条件，条件为真的返回值，条件为假的返回值）

https://jingyan.baidu.com/article/870c6fc37b7cc3b03fe4bef2.html

上面链接解释比较清晰

疑似就是将{0，1}，转变成{1，0}

lookup函数：

三个参数：

=lookup(查找依据值，依据列，结果列） tips：查找列与结果列数据行数相等否侧会导致错位

两个参数：；

=lookup(查找依据值，查找范围）

lookup 函数：只会按升序排列查找的函数

如果使用lookup函数来查找，首先将查找依据值做升序排列，否则会出现乱序导致数据不准确；

tips：单元格区域非标准单元格（也就是整列不全是同一个数据），这是就要选择单元格区域，区域选定后使用绝对引用；

lookup函数的近似查找

lookup的近似查找与vlookup的近似查找相似

=lookup(value,作用域）

tips：绝对引用+区域升序排列

------------------------以下是新的知识点---------------

经常会看到

=lookup（1，0/（c1:c21=k17),a1:a21)

条件1：

1的意思是查找值等于1

条件2：

0/0=0 错误值

0/1=1

当c1:c21中的值等于K17时，（c1:c21=k17)=1

条件3：为填充值

tips：可以处理依据列在查找列后面的

lookup函数多个条件的查找

通过姓名和所在部门查找实习生编号

=lookup(1,0/(c1:c21=k20)*(b1:b21=l20),a1:a21)

(c1:c21=k20)*(b1:b21=l20):两个并列条件使用逻辑符号连接；

疑问？

就是这里没有使用我们一直强调的绝对引用是什么原因的？（先标红等我有答案了再改）

答案：是因为结果为单一值，如果结果为一列或多行需要下拉就需要绝对引用了

lookup函数还可以返回最后一个数据

tips：此时不需要将依据列升序

=LOOKUP(1,0/({FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}),$C$25:$C$36)

数组：

({FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

返回最后一个true

总结：多个判断条件时需要用（括号）中间加上逻辑符号 and（*） or，

-----------------------------------------------手动分割-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MATCH(lookup-value,lookup-array,match-type)

lookup-value:表示要在区域或数组中查找的值，可以是直接输入的数组或单元格引用。

lookup-array:表示可能包含所要查找的数值的连续单元格区域，应为数组或数组引用。

match-type:表示查找方式，用于指定精确查找（查找区域无序排列）或模糊查找（查找区域升序排列）。取值为-1、1、0 。其中0为精确查找

----------------------------------------------------------------------时间日期函数----------------------------------------------------------------------------------------------------------------------------------------

时间日期函数：

Excel如何显示当前日期函数公式

显示当前日期函数：【=DAY((NOW()))】

显示当前星期函数：【=TEXT(NOW(),"aaaa")】

显示当前准确时间：【=TEXT(NOW(),"hh:mm:ss")】

显示当前年月日：【=TODAY()】

显示当前年月日和时间：【=NOW()】

显示当前年份函数：【=YEAR(NOW())】

显示当前月份函数：【=MONTH(NOW())】

Excel如何通过日期判断星期几

通过日期显示星期几：【=WEEKDAY(A2,2)】

返回日期在一年中的周数：【=WEEKNUM(A2,2)】2代表以周一为一周的第一天

通过日期显示中文带星期：【=TEXT(A2,"aaaa")】

通过日期计算中文星期：【=TEXT(A4,"aaa")】

通过日期计算英文星期简写：【=TEXT(A5,"ddd")】

通过日期计算英文星期：【=TEXT(A5,"dddd")】

使用方法：指定输入日期的单元格，输入以上函数公式即可获取星期。

Excel如何求月份天数函数

求某日期的月份天数：【=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))】 使用方法：需要指定包含日期的单元格。

直接求当前月份天数：【=DAY(EOMONTH(NOW(),0))】 使用方法：直接在某单元格输入以上公式即可获取天数。

求当前月份天数减去周六和周日：

【=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))】

使用方法：直接在某单元格输入以上公式即可获取天数。

Excel快捷键快速获取日期时间

当然，在有些情况下，我们可以直接通过Excel快捷键来获取当前的日期和时间。

获取当前年月日快捷键：【Ctrl+;】

获取当前时间快捷键：【Ctrl+Shift+;】

获取年月日和时间：先在单元格使用【Ctrl+;】，然后空格在使用快捷键【Ctrl+Shift+；】

Excel日期时间运算函数

正常的加减乘除就好了

天（day）换算成小时*24

天（day）换算成分钟*24*60

天（day）换算成秒*24*60*60

DateIF函数

函数语法：

DATEDIF(start_date,end_date,unit)

参数1：start_date，表示起始日期

参数2：end_date，表示结束日期

参数1和参数2可以是带引号的文本串（例如："2014-1-1"）、系列号或者其他公式或函数的结果

参数3：unit为所需信息的返回时间单位代码。各代码含义如下：

"y"返回时间段中的整年数

"m”返回时间段中的整月数

"d"返回时间段中的天数

"md”参数1和2的天数之差，忽略年和月

"ym“参数1和2的月数之差，忽略年和日

"yd”参数1和2的天数之差，忽略年。按照月、日计算天数

>>>>>>>>>>>>>

例如：返回天数

=DATEDIF(A2,B2,"d")

NETWORKDAYS函数

NETWORKDAYS(start_date,end_date,holidays)

NETWORKDAYS函数返回参数start-data和end-data之间完整的工作日（不包括周末和专门指定的假期）数值

networkdays函数就是计算某一段时期内总天数减去双休日和法定节假日之后的工作天数，有三个参数，

第三个参数是可以缺省的，这个函数在缺省第三个参数的情况下，也是除开周末2个非工作日来进行计算的，得到的差值就是起止日期之间的净工作天数。而Holiday这个参数如果指定了，则会在遇到这些节日的时候减去。

注意：如果第三个参数给定的周六日，那么函数返回结果本身就是把这一天减去的，是不会重复减去的。

如下图，由于11月7日和8日是周六日，函数就不计算这两天，所以把这两天作为第三参数时，函数就减去一次，缺省与否返回结果是相同的。

数学计算函数

加：sum 函数（+）

SUM(A1,A2)

减： imsub 函数（-）

IMSUB(被减数，减数）

乘 ：product 函数（*）

PRODUCT(A1,A2)

除：quotient 函数（/）

QUOTIENT(被除数，除数）

tips：返回结果为整数quotient（7，3）>>>2

幂运算

次方：power函数 ：符号（^)

F2=2

F3=3

POWER(F2,F3)

>>>8

2^3=8

开方:sqrt函数：开平方根

F7:16

SQRT(F7)>>>>4

开多次方根：

=F7^(1/4)

>>>2

绝对值：ABS函数：ABS（）

COMBIN函数：在数学上都学过排列组合的概念和算法

combin函数有两个参数：

combin（number，number_chosen)

第一个参数number：所要求组合的对象的总数量。

第二个参数number_chosen：每一个组合中对象的数量。(chosen:挑选出来的)

 MID函数：

详情连接：https://jingyan.baidu.com/article/ca00d56c7228fee99eebcfa7.html

MID字符串函数，作用是从一个字符串中截取出指定数量的字符

MID(text, start_num, num_chars)

 text被截取的字符

start_num从左起第几位开始截取（用数字表达）

 num_chars从左起向右截取的长度是多少（用数字表达）

A1:361181198306063585

去性别：

=IF(MOD(MID(A1,17,1),2)=1,"男","女"）

SUMPRODUCT()函数

详情连接：https://jingyan.baidu.com/article/3c343ff7074afe0d36796353.html

Sumproduct函数的适用范围，在给定的几组数组中，然后把数组间对应的元素相乘，最后返回乘积之和。

从字面上可以看出，sumproduct有两个英文单词组成，sum是和，product是积，所以是乘积之和的意思。

=SUMPRODUCT（array1,array2,array3, ...）Array为数组。（Array>>>数组）

（1）、当sumproduct函数中的参数只有一个数组时，即对数组｛1;2;3;4;5;6;7｝进行求和，1+2+3+4+5+6+7=28。

（2）、当sumproduct函数中参数为两个数组时，两个数组的所有元素对应相乘。

公式=sumproduct（A2:A8,B2:B8）可转化为

=sumproduct（数组1，数组2）

=sumproduct（｛1；2；3；4；5；6；7｝，｛1；2；3；4；5；6；7｝）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。

（3）、当sumproduct函数中参数为三个数组时，三个数组的所有元素对应相乘。

（4）、sumproduct函数案列——多条件求和

（5）、sumproduct函数案列——多条件计数

如图，输入公式=SUMPRODUCT((A2:A13="成都发货平台")*(C2:C13="重庆发货平台"))。利用sumproduct函数中TRUE和FALSE逻辑数组1和0的特效来计数。

公式解析=sumproduct（｛1；0；0；0；0；0；0；0；0；1；0；1｝*｛1；0；0；0；0；0；1；0；0；1；0；0｝）=2

（6）、sumproduct函数——实现有条件排名

输入公式=SUMPRODUCT((D2<$D$2:$D$8)*1)+1

数组公式D2<$D$2:$D$8这里是由TRUE和FALSE组成的逻辑数组｛FALSE；TRUE；TRUE；FALSE；TRUE；FALSE；TRUE｝转化为1和0的数组｛0；1；1；0；1；0；1｝。

这里利用的是该成绩和所以成绩进行比较，通过测算高于该成绩的成绩，每次累加1。

tips：sumproduct函数使用的注意点

sumproduct函数后面的参数必须是数组，即行和列的维度是一致的。参数维数不一致所以返回错误值＃VALUE!

sumproduct函数，逗号分割的各个参数必须为数字型数据。

如果是判断的结果逻辑值，就要乘1转换为数字。

如果不用逗号，直接用*号连接，就相当于乘法运算，就不必添加*1

基础知识点：

行：row（）返回所在的行数。

列：column（）返回所在的列数

INT函数：向下取整,保留接近小数最近一位整数,不保留小数（不会出现小数点）；

2.1        int（2.1）>>>>2;

-2.1       int(-2.1)>>>>>-3

TRUNC函数：截取到指定位置（truncate 截去）

详情连接https://jingyan.baidu.com/article/19192ad8017e1ae53e57079c.html

runc函数的语法为：

trunc（number，number_digits）

trunc函数有两个参数：

第一个参数number：指的是需要截尾取整的数字。

第二个参数number_digits：指定取整精度的数字。默认情况下，number_digits的值为0，也就是取整数了。

runc函数的语法为：

trunc（number，number_digits）

trunc函数有两个参数：

第一个参数number：指的是需要截尾取整的数字。

第二个参数number_digits：指定取整精度的数字。默认情况下，number_digits的值为0，也就是取整数了。

>>>>>> =TRUNC(A2)       这里，我们省略了第二个参数，也就是默认第二个参数是0。

ROUND函数;四舍五入

详情连接：https://jingyan.baidu.com/article/fdffd1f804de24f3e88ca140.html

round函数用来对数值进行四舍五入。

语法：ROUND(number, num_digits)

其中

number表示需要进行四舍五入的数值或单元格内容。

num_digits表示需要取多少位的参数。

num_digits>0时，表示取小数点后对应位数的四舍五入数值。

num_digits=0时，表示则将数字四舍五入到最接近的整数。

num_digits< 0时，表示对小数点左侧前几位进行四舍五入。

单元格保留位数

并不是真正意义上的四舍五入

将单元格复制到其他单元格上就会出现个与有数据的全部位数

--------------------------------------------------------------------------------------------------------------------手动分割----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

数据有效性：

1.选择要限制数据有效性的区域；

2.点开【数据】选项卡选择”数据验证“；

3.【设置】中选择”序列"；

4.若手动输入则需要将内容使用英文符号分割开来（比如：A级,B级）；

5.若嫌手动麻烦就使用右侧选择有效数据；

快速填写Excel中的内容

Alt+↑ 可以快速调用出钱面录入过得内容；

批量录入

1.经常输入带有小数点的数据

可以在【文件】、【选项】、【高级】中找到“自动插入小数点”小数点位数选择2，输入数据就会自动缩小一百倍

输入：1234

>>>>>12.34

此种情况多用于经常输入带有小数点的数据

输入金额很大的话也可以使用这种方式;

在插入小数位填写负数，就是增大相应的倍数。

2.定位选择、批量录入（Ctrl+Enter）

 快速填充空值：

方法：Ctrl+G 调用定位功能然后选择“空值” >>>>>>“=单元格”>>>>>>“Ctrl+enter 批量录入”

选择填充区域，复制后粘贴为值（这不很重要，不要忘记）

3.生成模拟器（RAND()函数、RANDBETWEEN()函数）

>>>>>>

RAND()函数

rand的含义是边缘，Excel中RAND函数是以0和1为边缘随机返回数值的函数。是不是感觉这个函数有点特别，甚至会质疑Excel程序开发者的智慧没有用对地方，其实RAND函数可以帮我们自动生成大量的数据，这么说小伙伴们会不会崇拜这个函数

RAND函数的功能

自动返回大于等于0并且小于1的随机数据，每次计算生成的数据都不一样。

RAND函数的语法结构

=Rand()

>>>>>>>

------------------------------------------------------------------------------------------------------------手动分割------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Replace函数

Replace函数的含义~

用新字符串替换旧字符串，而且替换的位置和数量都是指定的。

replace函数的语法格式

=Replace(old_text,start_num,num_chars，new_text)

=replace（要替换的字符串，开始位置，替换个数，新的文本）

举例子：

常见的把手机号码后四位屏蔽掉，输入公式

=REPLACE(A2,8,4,"****")

注意：

第三个参数是闭区间，包括第8位。从A2的第8位开始，替换后面4位，替换成****。

第四个参数是文本，要加上引号。

参数New_text——新的文本可省略，如图所示，也可以用""来代替，表示为空把old_text（旧文本）删除。

------------------------------------------------------------------------------------------------------------手动分割------------------------------------------------------------------------------------------------------------------------------------------------------------------------

一、文本录入技巧

输入开头为0的序号

当直接输入单元格中的数字第一个为0时系统会默认去掉

只需要经单元格格式改为文本或者在单元格输入前使用英文状态下的单引号（‘）

例如：'0001

>>>>>0001

输入身份证号码

单元格位数超过11位以后会一科学计数法显示，

输入18位的身份证会以科学计算法形式显示单元格，如果将单元格格式在选择数值时会发现单元格后面的位数变成000

同上，使用文本单元格格式或使用英文单引号

分段显示电话号码

------------------------------------------------------------------------------------------------------------手动分割------------------------------------------------------------------------------------------------------------------------------------------------------------------------

数据处理：

在数据进行分析使用时，需要去除原始数据中的脏数据，让统计数据均为有效数据；

统一表格的格式：去除空格、强制换行符

CLEAN（）函数：是去除单元格中的特殊字符；

TRIM（）函数：去除单元格中前后的空格；

SUBSTITUTE（）函数：去除单元格中字符串中的空格；

还有复制表格中不能替换的空格或其他特殊字符，然后Ctrl+H，替换称空白；

Ctrl+F：查找

Ctrl+G：定位

Ctrl+H：替换

源数据表：

中含有合计会造成无法进行统计、筛选和排序等

 快速填充空值：

方法：Ctrl+G 调用定位功能然后选择“空值” >>>>>>“=单元格”>>>>>>“Ctrl+enter 批量录入”

选择填充区域，复制后粘贴为值（这不很重要，不要忘记）

超级表功能的应用

规范表格、套用样式

Ctrl+T/Ctrl+L调用表创建表 ，或者插入中选择插入表；

------------------------------------------------------------------------------------------------------------手动分割------------------------------------------------------------------------------------------------------------------------------------------------------------------------

一、      表格转换

行列转置

复制

选择性粘贴 转置

COUNTBLACK（）

二、       添加空行

添加辅助列，编号，排序

三、       删除空行

COUNTBLACK（）

四、       数据对比

单项对比：

可以使用IF函数，现在我们介绍一个简单的数据对比方式：

选择好对比区域（对比库存数量和实际盘点），选择该区域按Ctrl+\(右斜杠) 然后选择背景色就可以找出不同的数据单元格。

多项对比：

IF(条件，结果1，结果2)

SUMIF(条件区域，条件，数据值)

返回符合条件的数据总和；

SUMIFS(数据值，条件一区域，条件一，条件二区域，条件二)

对多个条件的判断然后返回符合结果的相相加；

统计表对比：

五、       数据查找与标识

一对一查找

一对多查找

COUNTIF(计数区域，计数值)

如果计数区域出现，计数值记数增加

IFERROR(条件，结果)

如果条件错误则返回结果

MATCH(匹配值，匹配区域，0精确匹配/模糊匹配)

在区域中查找匹配所在的行或列

ROW()返回目标单元格的行数

COLUMN()返回目标单元格的列数

INDEX(区域，行，列)

返回指定行列的数据

高级数据处理

切换工作表：

方法一：

Ctrl+pageup 向左切换工作表

Ctrl+pagedown向右切换工作表

方法二：

右击工作表左下角，出现【激活】选择相应的工作表名称并点击确【确定】

组合工作表

需求：所有工作表开头增加一行：

1.      选中工作边【1日】按住shift键到汇总，就可以选中所有工作表

2.      或者右击任意工作表名称右击选择‘选定所有工作表’会选中所有工作表

3.      组合好后在工作表开头添加一行；

夸工作表求和的方法

格式规范的多工作表汇总

需求：求所有工作表的汇总

使用=SUM(‘*’!B3)——这里‘*’代表任意

=SUM(‘*’!B3)=SUM(‘1日:5日’!B3)

需求：求二、四工作表的汇总

=SUM(‘2日’!B3,’4日’!B3)

两个表之间使用“,”隔开

！的作用

‘’！” 用来标识 工作表名称的，在引用其他工作表时需要使用比如 在 sheet2 工作表内的 A2 单元格 引用 sheet1 的 A2 单元格 ，则 公式 为 =sheet1!A2

！是表示单元格与工作表的从属关系的，当跨表引用时，为了表明引用的单元格属于哪个工作表的，就在单元格前用其工作表加上！连接，如：sheet1!A1:D3，表示同一工作簿中名称为sheet1的工作表中的A1:D3单元格区域.

跨工作表单元格引用

编辑器

筛选

在Excel中数据筛选后如何拷贝粘贴

https://jingyan.baidu.com/article/64d05a0276e33fde55f73bd9.html

主要步骤：

复制内容

选择性粘贴  然后选择可见单元格

拆分工作表

在工作表右键查看代码，导航栏右键新建模块，粘贴代码

Private Sub 分拆工作表()

Dim sht As Worksheet

Dim MyBook As Workbook

Set MyBook = ActiveWorkbook

For Each sht In MyBook.Sheets

sht.Copy

ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" &
sht.Name & ".xlsx"     '将工作簿另存为 xlsx格式

ActiveWorkbook.Close

Next

MsgBox "文件已经被分拆完毕!"

End Sub

选择【运行】选项卡，选择‘运行过程/子窗体’

合并工作簿

使用VBA代码

使用宏完成

Alt+F11 调用VBA代码页面

#粘贴

Sub 合并工作薄()

Dim FileOpen

Dim X As Integer

Application.ScreenUpdating
= False

FileOpen =
Application.GetOpenFilename(FileFilter:="Microsoft Excel文件(*.xlsx),*.xlsx", MultiSelect:=True,
Title:="合并工作薄")

X = 1

While X <=
UBound(FileOpen)

Workbooks.Open
Filename:=FileOpen(X)

Sheets().Move
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

X = X + 1

Wend

ExitHandler:

Application.ScreenUpdating
= True

Exit Sub

errhadler:

MsgBox
Err.Description

End Sub

#保存后，在【开发者工具】中找到【宏】执行【确定】找到需要合并的多个文件夹，点击【打开】即可