VLOOKUP大类
-
#vlookup基础概念
vlookup(查找单元格,检索区域,返回列距离,精确匹配/区间匹配)
参数1需要位于参数2检索区域的首列
精确是0，模糊是1，模糊匹配通常用来匹配区间范围
eg.
vlookup(B2,店铺税号!$B:$G,4,0) 意思是：根据本sheet中B列的值，查找店铺税号这个sheet中的B到G列，取出其中E列对应的值到本sheet的目标单元格位置。
-
vlookup扩展配合数组查找
vlookup(查找单元格,检索区域,{返回列距离1,返回列距离2},精确匹配/区间匹配)
与常规方式不同的是，返回列距离这个参数使用了数组模式
eg.
vlookup(B2,店铺税号!$B:$G,{4,6},0) 意思是：根据本sheet中B列的值，查找店铺税号这个sheet中的B到G列，取出其中E和G列对应的值到本sheet的2个目标单元格位置。
-
VLOOKUP的反向查找
=VLOOKUP(A2,CHOOSE({2,1},$N$2:$N$200,$O$2:$O$200),2,0)
A列：店铺名
N列：组别名
O列：店铺名
相当于根据A列找N和O中对应的N列，但是排序中N列在O列的左边，也就是形成了反向查找，这对于v是做不到的
所以将N和O通过choose函数进行重构，重构中可以看到O列被赋予1，N列被赋予2，我在前面因为偷懒，就写成了{2,1}，实际上也可以写成顺序的，只不过把后面的$N$2:$N$200,$O$2:$O$200 换个顺序。
然后在后面的第3个参数地方写2
也就是完成了第一参数A2，第3参数2，然后根据第二参数中的排列找到第二列，也就是N列，即完成了反向通过店铺名查找组别名的操作；
类似的操作还可以用if来重构，大致原理也差不多，当然了，后面的H搞这个更简单
-
vlookup+match
eg.
VLOOKUP(B2,店铺税号!B:G,MATCH($H$1,店铺税号!$B$1:$G$1,0),0) 其他参数如常，第3个参数由match来提供，可以用在多行的表中，就不用自己一个个去数数了
#利用通配符*进行有限的模糊查找
将查找单元格写为"*"&查找单元格&"*"
有限的模糊查询，查找单元格不能有太多类似值，否则容易匹配错误
-
#构建辅助列对多列进行匹配
比如将BC列作为查找单元格进行匹配，可以先在检索区域前添加一个辅助列，内容A1=B1&C1，假设G列和H列作为匹配的查找单元格分别对应B列和C列，则函数第一个参数可以写成G1&H1，即对应辅助列A1内容
-
#文本数值切换
假设索引区间头列为A，匹配查找单元格列为H
索引头列A为文本格式，匹配列H为数值模式；索引头列和匹配列若为文本和数值类型，则匹配会报错，需要切为同类型
文本切数值：A1*1
乘以1，会让格式变为数值。即：vlookup(A1*1,索引区间,距离,0)
数值切文本：H1&""
连接一个空值，连接符会把数字变为文本。即：vlookup(H1&"",索引区间,距离,0)
----------
XLOOKUP大类
#基础概念
xlookup(查找值,查找区域,返回结果区域,找不到结果的返回值,匹配方式,搜索模式)
参数1与vlookup相同，不做过多介绍；
参数2与vlookup不同，v是一个区间，比如B:G表示在B列到G列之间查找，但是x不能这样，你要找的是哪列就要标明是哪列；比如说你查找内容是B列(即：参数1)，对应着店铺这个sheet的D列，那么前2个参数可以写成xlookup(@B:B,店铺!D:D,参数3) （注：这里的参数一是动态数组的写法，但是为了防止溢出出现#SPILL报错，所以前面加了@,这样返回的就不是数组了）
参数3-返回结果区域：v是从搜索区域的第一列开始往右数，x是直接写出该区域，比如查找区域是$B:$G,返回区域在C列，则该参数可以写成$C:$C；
参数4-找不到结果的返回值：相当于v外面套了一层iferror；
参数5-匹配方式：
参数6-搜索模式：
----------
DGET大类
#DGET基础概念
数据库函数，只能查一行，不能下拉
dget(检索区域,对应结果单元格,查询条件区域)
dget函数就相当于sql的查询语句：检索区域就是table，对应结果单元格就是select后面跟的内容（单行），查询条件区域相当于where条件（通常是2行）
eg.
DGET($A:$E,H1,$G1:$G2) 意思是：select H1 from $A:$E where （列名为G1）G1列值=G2
需要注意的是，AE列要锁定，G1G2这2个单元格也要锁定（因为是where查询条件），但是H1可以不锁定，可以横拉出其他对应的列名。
----------
IF大类
if(判断条件,满足条件的返回值,不满足条件的返回值)
IF(F11<>"",SUM(I:I)-K11,"左侧对应金额列没有数值") 这个函数的意思是，首先检测F11单元格是否为空，如果不为空，则函数所在单元格计算结果为I全列求和减去K11单元格的差值；如果F11单元格为空，则返回“左侧对应金额列没有数值”
eg.
if(A1:A10<>0,A1:A10,"") 意思是：将A1到A10区域内的0替换为空
-
扩展·if搭配vlookup逆向查找
eg.
if({1,0},正向区域,逆向区域)
VLOOKUP(G2,IF({1,0},B:B,A:A),2,0) 意思是：根据G列查找对应的A列值，其中G列在B列里
-
#iferror的套用
iferror(正确对应的结果,错误对应的结果)
该函数通常用来解决错误值N/A，将其改为0或者其他较为友好的显示方式
iferror套用vlookup案例
iferror(vlookup(查找单元格,检索区域,返回列距离,精确匹配/区间匹配),"未匹配数据")
eg.
iferror(vstack(a1:ag10,a11:a15)),"") 意思是：将合并表格中的错误值替换为空白单元格
-
count函数、countif函数、countifs函数
count函数就是统计某个区域内，非空单元格的数量
eg.
count(A1:A7) 意思是：统计A1到A7单元格中，非空单元格的个数
-
countif函数则是统计某个区域内，符合条件的单元格的数量
eg.
countif(A1:A7,"3") 意思是：统计A1到A7单元格中，值为3的单元格个数
延伸查重
IF(countif($A:$A,A1)>1,"重复","")
-
countifs函数是多区域的条件同时满足取交集，统计出符合条件的单元格数量
eg.
countifs(A1:A7,F16,B1:B7,G16) 意思是：统计A1到A7区域等于F16、B1到B7区域等于G16的值的单元格数量
MATCH函数
match(查找单元格,查找范围,精确/模糊查找)
eg.
match(B2,店铺税号!B:B,0) 意思是：查找B2单元格在店铺税号这张表的B列中的行数排行。
eg.
match(H1,店铺税号!$B$1:$G$1,0)：意思是查找H1这个标题在店铺税号这张表的B1到G1这个标题中的列数排行。
上面2个例子，就确定了行参数和列参数，无论是带入INDEX还是VLOOKUP中，都可以很好的使用了。
eg.
match(b6:j6,b2:j2,0)：意思是b到j的第6行分别对应着b到j第二行的列数位置，这样可以用来处理一些混乱的表格排序，用于数据整理对齐
INDEX函数
index(查找区域,行参数,列参数)
结合前面的match的两个例子，可以直接套用
eg.
index(B:G,match(B2,店铺税号!B:B,0),match(H1,店铺税号!$B$1:$G$1,0)) 意思是在B列到G列的查找区域里，根据match找到的对应行列，取到所需要的数值
Filter函数
常用于1对多返回，但这个多列通常也是连续的
filter(返回区域,匹配条件,找不到结果时的返回值)
eg.
filter(A:E,B:B=J20,"找不到") 意思是B列的值等于J20的值时，返回A到E列中对应的5列数值，如果没有这样的值，就返回"找不到"，第三参数可以省略。
---
FILTER和VLOOKUP的对比
取多列数据来说，FILTER更方便一些，有点像XLOOKUP，但是FILTER使用过程中也有很多限制，因为是列匹配列，所以匹配的长度需要一致
比如写成 FILTER(A1:A10,B2=C1:C20)就不行，这个A和C列拉进来匹配的字段要一致，否则就报错。而且在这个公式中，ABC应该是同一片区域且有相应的对应关系，比如前面的这个公式，如果强行写成A1:A20来对应后面的C1:C20的话，A列后面10行如果没有值，则对比出来的数值会有问题。
其次vlookup可以用来筛查数据是否存在，而用filter则不行
比如我要检测A列的值，是否在E列中，我可以写成
VLOOKUP(A1,E1:E100,1,0)
方便查找的话，可以套个IF把存在的单元格消掉，写成IF(VLOOKUP(A1,E1:E100,1,0)<>"","","×"),这样可以把存在的值变为空，不存在的值标记出来
但是用FILTER无法做到这点，前面说过FILTER列进去的第一和第二参数他们应该在同一片区域
sumifs函数
sumifs(求和列,条件1列,条件1,条件2列,条件2)
3个参数起步，后面依次是5,7,9的等差数列条件
eg.
=SUMIFS(D:D,E:E,{"杰","七"},F:F,{"1","2"})
查找杰的第1月销量和七的第2月销量，注意第五个参数数组中间是逗号
=SUMIFS(D:D,E:E,{"杰","七"},F:F,{"1";"2"})
查找的是杰和七的第一月销量，然后换行再列出杰和七的第二月销量；注意第5参数1和2之间是分号。
=SUMIFS(D:D,E:E,{"杰";"七"},F:F,{"1","2"})
列出了杰的一二月销量，然后列出七的一二月销量，分号在第3个参数
上面3个sumifs主要用来列出函数中有数组时逗号和分号对应的情况，如果是求和，还要在外层套一个sum
choose函数
该函数通常搭配Filter函数一起食用
Filter函数的第一个参数，即是最后显示的区域，比如上面例子中的A:E这个范围，会取出5列数据。
但是这通常是连续的5列，如果只想取出其中的1、2、5这3列，则无法通过Filter本身做到，在vlookup中，可以通过数组来进行单独提取，而Filter则可以嵌套一个choose函数进行取值。
choose({1,2,3},A:A,B:B,E:E) 意思是取出A、B、E这三列，取出之后分别放在对应的1、2、3列里面；将这个函数嵌套进Filter的第一个参数即可。
嵌套Filter的写法则为：filter(choose({1,2,3},A:A,B:B,E:E),B:B=J20,"找不到")
choosecols和chooserows函数
可以视为上面choose函数的延伸
上面的嵌套写法filter(choose({1,2,3},A:A,B:B,E:E),B:B=J20,"找不到")
如果用choosecols改写
filter(choosecols(A:E,1,2,5),B:B=J20,"找不到")
choosecols主要是对列的筛选，chooserows则是对行，后面有eg了再补上
延伸choosecols嵌套match
choosecols(取值范围,MATCH(当前目标范围,筛选范围,0))
eg.
原语句：filter(choosecols(A:E,1,2,5),B:B=J20,"找不到")
现语句（处理前语句的1,2,5）：filter(choosecols(A:E,match(F1:H1,A1:E1,0)),B:B=J20,"找不到")。意思是F1到H1这3个单元格标题肯定对应着第一行中的A1,B1,E1,也就是列排行为1,2,5
SORT
有4个参数，第一个参数必选，是排序范围；第二个参数是排序条件；第三个参数默认1是升序，-1是降序；第4参数默认0按行排，1是按列。
第二个参数条件如果是单个数值，指的是按第一参数的第几列来排序；或者第2参数也可以是个条件，比如说类似第一参数的值都大于5这样的。
SORT排序出来的结果是个数组，所以第一参数最好不要是整列，不然容易#spill。
eg.
=SORT(A2:C274,2,{-1;1})
在这里分号明显是行不通的，我本来想试一下把选中区域按照B列先倒序再顺序排，这个明显和sumifs里面可以用分号列出不同。老老实实用逗号写
eg.
=SORT(A2:C274,{3,2},{-1,1})
先按C列的单价倒序排，再按B列的评论数正序排。截图里面是个分号，结果不影响，仍然是按正常逗号在排序。
写成SORT(A2:C274,{2,3},{1,-1})与前面结果不一样，意味着先按B正序再按C倒序排。
再来个倒反天罡的烧烧脑子
eg.
=SORT(A2:C5,{2,3},{1,-1},1)
其实这个公式首先看的是第4参数（如果有的话），第4参数是1，意味着是按行排，那么前面的{2,3}就不再是原数组里面的2列和3列，而是2行和3行，这个公式翻译过来就是区间的第2行先按正序排列，，然后把3行按倒序排列。原数据区域的第二行是And咖啡,1235,31；给他按正序排直接变成了31，1235，And咖啡。整个区域都变了，在这个基础上再去倒序排第3行已经无效了。
offset函数
offset(指定单元格/指定区域,行偏移量,列偏移量,返回区域行高,返回区域列宽)
偏移参数中，正数表示向下和向右，负数表示向上和向左
eg.
offset(B28,-4,0,4,1)
意思是B28这个单元格，向上偏移4行，向右偏移0列，（也就是从B28偏移到B24），之后选取向4行高，1列宽的这么一个区域（也就是相当于B24:B27）
offset+countif
eg.
countif(offset(B28,-4,0,4,1),"休息")
表示选定区域为B24:B27，统计这个区域中值为"休息"的数量
vstack函数
该函数用于对指定区域的纵向连接
eg.
vstack(a1:ag10,a14:ag25)
意思是把a1到ag10的区域和a14到ag25的区域拼接起来组成新的区域，需要注意的是，这种拼接起来的区域里面的空单元格，都会被自动赋0，所以要注意做清零的操作
eg.
想要把3月和4月的客服休息表合并
VSTACK(排班分月!A1:AG10,排班分月!A12:AG20)
结果如下图
可以看到合并单元格效果被取消，空格全部被赋0.
因此用一个IF函数来去掉0
IF(VSTACK(排班分月!A1:AG10,排班分月!A12:AG20)<>0,VSTACK(排班分月!A1:AG10,排班分月!A12:AG20),"")
