hello_nika

1、AND函数 功能： 返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 语法：AND(logical1,logical2, …) 参数表示待测试的条件值或表达式。 应用举例： 在C5单元格输入公式： AND(A5>=60,B5>=60) 如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。 特别提醒： 如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值。 2、AVERAGE函数 功能： 求出所有参数的算术平均值。 语法：AVERAGE(number1,number2,……) 参数是需要求平均值的数值或引用单元格区域。 应用举例： 在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒： 如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。 3、COLUMN 函数 功能： 显示所引用单元格的列标号值。 语法：COLUMN(reference) 参数reference为引用的单元格。 应用举例： 在C5单元格中输入公式：=COLUMN(B5)，确认后显示为2（即B列）。 特别提醒： 如果在B3单元格中输入公式：=COLUMN()，也显示出2；与之相对应的还有一个返回行标号值的函数——ROW(reference)。 4、COUNTIF函数 功能： 统计某个单元格区域中符合指定条件的单元格数目。 语法：COUNTIF(Range,Criteria) 参数说明：第一参数是要统计的单元格区域；第二参数表示指定的条件表达式。 应用举例： 在C2单元格中输入公式：=COUNTIF(B2:B5,”>=80″) 可统计出B2至B5单元格区域中，数值大于等于80的单元格数目。 特别提醒：…

一、按年求和 数据表格如下图，要求用函数求出各年份的金额 公式1：用SUMIFS多条件求和 这问题可以转化为一个多条件求和，比如：要求2017年的，那么就是对既大于2017年1月1日，又小于2017年12月31日的日期数据进行求和，因而，我们可以使用SUMIFS函数： =SUMIFS($C$2:$C$33,$B$2:$B$33,”>=2017/1/1″,$B$2:$B$33,”<=2017/12/31″) 此公式不能直接下拉填充，以计算出2018年、2019年的金额，我们可以用DATE函数来计算日期： =DATE(E2,1,1) 将其代入到前面的公式，完整的公式为： =SUMIFS($C$2:$C$33,$B$2:$B$33,”>=”&DATE(E2,1,1),$B$2:$B$33,”<=”&DATE(E2,12,31)) 公式2：用SUMPRODUCT进行数组运算 SUMPRODUCT函数是将数组进行相乘，然后求乘积的和，它可以进行数组运算，而不必按Ctrl+Shift+Enter，因而我们可以先计算出各单元格的年份是否为指定年份（如果是，其结果为true，相当于1，不是的话，其结果为FASLE，相当于0），然后将其与金额相乘，其乘积之和就是年份的金额之和。 公式为： =SUMPRODUCT((YEAR($B$2:$B$33)=E2)*$C$2:$C$33) 二、按季度求和 1、用SUMIFS函数 本题就是要求按季度和年度求和，因而也是一个多条件求和，可用SUMIFS，在前面SUMIFS多条件按年求和公式的基础上，修改一下求和条件就是了。一季度是计算1月1日到3月31日，二季度是计算4月1日到6月30日。。。。。 假设季度数是N，那么该季度起始月就是3*N-2，季度起始日用DATE函数 DATE（2018，3*N-2，1） 该季度截止月就是3*N，由于截止月最后一日不固定，有些是30，有些是31，不用简单的用DATE函数，还需套用计算月末最后一天的EOMONTH函数（End+Of+Month），因而其公式为： =SUMIFS($C$2:$C$33,$B$2:$B$33,”>=”&DATE(F$10,$E11*3-2,1),$B$2:$B$33,”<“&EOMONTH(DATE(F$10,$E11*3,1),0)) 2、用SUMPRODUCT函数 有一个比较碰巧的规律： 假设各月份为M，求2的M次方，其结果的字符数，刚好等于月份所在的季度数 因而，我们可以用LEN(2^MONTH($B$2:$B$33))来计算B列各月的季度数，用SUMPRODUCT来计算各季度的合计，其公式为： =SUMPRODUCT((LEN(2^MONTH($B$2:$B$33))=$E2)*(YEAR($B$2:$B$33)=F$1)*$C$2:$C$33) 三、按月求和 只要理解了前面的公式，按月求和就比较简单了，公式分别为： =SUMPRODUCT((MONTH($B$2:$B$14)=E2)*$C$2:$C$14) =SUMIFS($C$2:$C$14,$B$2:$B$14,”>=”&DATE(2017,E2,1),$B$2:$B$14,”<“&DATE(2017,E2+1,1)) =SUMIFS($C$2:$C$14,$B$2:$B$14,”>=”&DATE(2017,E2,1),$B$2:$B$14,”<=”&EOMONTH(DATE(2017,E2,1),0)) 四、按旬求和 上旬1-10日： =SUMIFS($C$2:$C$46,$B$2:$B$46,”>=”&DATE(2018,$F2,1),$B$2:$B$46,”<“&DATE(2018,$F2,11)) 中旬11-20日： =SUMIFS($C$2:$C$46,$B$2:$B$46,”>=”&DATE(2018,$F2,11),$B$2:$B$46,”<“&DATE(2018,$F2,21)) 下旬：21-月末 =SUMIFS($C$2:$C$46,$B$2:$B$46,”>”&DATE(2018,$F2,20),$B$2:$B$46,”<=”&EOMONTH(DATE(2018,$F2,1),0)) 读者QQ群的读友031 – 硬币(37125126)…

今天咱们一起来学习XLOOKUP函数。这个函数目前可以在Microsoft 365和网页版Excel使用，还在使用低版本的小伙伴，可以在网页版来练练手。 先来说说这个函数的常规用法： =XLOOKUP(查找内容,查找区域,回传区域) 如下图所示，要根据G3单元格中的姓名，在左侧数据区域中查询所属部门。 H3单元格公式为： =XLOOKUP(G3,D2:D7,B2:B7) 公式中的G3，是要查询的姓名，D2:D7是姓名所在的区域，B2:B7就是咱们要回传信息的区域了。 利用Microsoft 365的自动溢出功能，下面这个公式可以同时获取部门、职务信息。 =XLOOKUP(G3,D2:D7,B2:C7) 如果要使用多个条件来查询，该怎么办呢？ 如下图所示， 要根据G3和H3的姓名以及部门，来查询对应的职务，可以使用以下公式来实现： =XLOOKUP(G3&H3,D2:D7&B2:B7,C2:C7) 第一参数，把两个查询条件使用连接符合并到一起，第二参数把两个查询区域也合并到一起，就这么简单。 如果找不到指定的内容，咱们还可以使用第四参数让他返回指定的内容或者是其他的计算公式。 比如下面这个公式，在查找不到关键字时，就会返回指定的内容“找不到啊”。 =XLOOKUP(G3,D2:D7,B2:B7,”找不到啊”) 在查询数值时，还可以使用第五参数来指定使用匹配方式。 如下图，要根据右侧的对照表，将E2单元格中的考核分变成对应的等级，可以使用以下公式： =XLOOKUP(E2,H:H,I:I,,-1) 因为要使用近似匹配方式，所以不需要屏蔽错误值，这里XLOOKUP函数的第四参数省略就可以了。 第五参数可以使用0、1或是-1来指定不同的匹配方式，本例中使用-1，表示在H列中找不到E2单元格的值时，就以比E2小的最接近值来匹配。也就是在找不到66时，就以60来匹配，并返回I列对应的等级标准“巴结”。 如果要在第一参数中使用通配符来实现关键字的查询，第五参数需要写成2。是不是微软的工程师们觉得这种写法比较二图片。 =XLOOKUP(G3&”*”,B2:B7,D2:D7,,2) 如果查询区域中有多项符合条件的记录，还可以通过第6参数来指定返回第一个还是最后一个。 如下图所示，B列有两个采购部的记录，使用以下公式会以最后一个记录来匹配。 =XLOOKUP(G3,B2:B7,D2:D7,”找不到”,,-1) 如果要以第一个记录匹配，这里的参数可以省略，或者使用1就好了。 使用XLOOKUP函数，还能够从二维表中查询数据。 如下图所示，要根据I2单元格的部门和I3单元格的月份，从左侧表格中查询对应的数值，可以使用以下公式。 =XLOOKUP(I2,A2:A7,XLOOKUP(I3,B1:G1,B2:G7)) 这个公式里，用到了两个XLOOKUP。 先来看XLOOKUP(I3,B1:G1,B2:G7)部分，目的是根据I3单元格的月份，在B1:G1单元格区域中查询到该月份，并返回B2:G7单元格对应的内容，得到的是“4月”所在列的全部数值： 最外层的XLOOKUP，以I2单元格中的部门为查询值，以A列作为查询区域，以第二个XLOOKUP返回的结果作为回传区域，最终在二维表中返回了咱们需要的结果。

今天和大家分享XLOOKUP函数的一些典型用法。 这个函数目前仅可以在Office 365以及WPS 2021中使用，函数语法为： =XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式]) 前三个是必须的，后面几个参数可省略。 接下来咱们就一起看看这个函数的一些典型用法： 1、常规查询 如下图所示，要根据G1的部门，在A列查询该部门，并返回B列对应的负责人姓名。公式为： =XLOOKUP(G1,A2:A11,B2:B11) 第一参数是查询的内容，第二参数是查询的区域，查询区域只要选择一列即可。第三参数是要返回哪一列的内容，同样也是只要选择一列就可以。 公式的意思就是在A2:A11单元格区域中查找G1单元格指定的部门，并返回B2:B11单元格区域中与之对应的姓名。 2、逆向查询 由于XLOOKUP函数的查询区域和返回区域是分开的两个参数，这样就不用考虑查询的方向问题了，不仅能实现从左到右，还能从右到左、从下到上、从上到下等任意方向的查询。 如下图所示，要根据G1的部门，在B列查询该部门，并返回A列对应的负责人姓名。公式为： =XLOOKUP(G1,B2:B11,A2:A11) 3、返回多列 如果要根据指定的查询内容，返回不同列中的内容也很简单。 如下图所示，要根据G1单元格的部门，分别返回该部门对应的姓名、日期和销售金额。公式为： =XLOOKUP(G1,A2:A11,B2:D11) 这个公式里的第三参数选择了多列的范围。由于Office 365中的公式有溢出功能，所以只要输入一个公式，就可以返回B~D列的多项信息了。 4、自动除错 XLOOKUP函数还自带双黄连，当查询不到内容时，可以指定返回的提示信息。 如下图，XLOOKUP函数在A列查询不到G1单元格的“大兴店”，这时候只要加上一个参数，就能让公式不再返回错误值#N/A了。 =XLOOKUP(G1,A2:A11,B2:D11,”无此数据”) 第四参数，用于指定在查找不到结果时返回的提示内容。 5、近似查找 如下图，要根据F1单元格的应税所得额，在左侧的对照表中查询对应的预扣率和速算扣除数。公式为： =XLOOKUP(F2,B2:B8,C2:D8,0,-1) XLOOKUP在B列中查询F2的值，第五参数使用-1，表示如果找不到它，就从查询区域中返回下一个较小的值。 如果第五参数是1，如果找不到查询值，就返回查询区域中返回下一个较大的值。 这个用法还有一个比较牛掰的地方，就是查询区域不用事先排序。比如下面这个表格里，B列的数值就是乱序的： 如果查询值中使用了通配符，记得第五参数要选择2。 XLOOKUP函数还有一个第六参数，如果数据源中有两个符合条件的结果时，第六参数设置为1返回首个结果，设置为-1时返回最后一个结果。

今天给大家分享的Excel函数是XLOOKUP，例先说一下它的基本语法。它有六个参数，成功超越大哥大OFFSET，成为参数最多的函数之一。 =XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式]) 参数看起来很多，不过只有前三个是必须的，后面均可省略。 下面我们举12个例子+两道练习题，由易入难、从简到繁、从入门到进阶，让大家对XLOOKUP的作用和运算方式有一个全面的了解。 1）单条件查询 如下图所示，B:D列是数据明细，需要根据F列姓名查询相关电话号码。 公式如下： G2输入公式▼ =XLOOKUP(F2,B:B,D:D) F2是查找值，B列是查找范围，D列是结果范围，公式的意思也就是在B列查找F2，找到后返回D列对应的结果。 2）容错查询 如下图所示，B:D列是数据明细，需要根据F列姓名查询相关电话号码，但和上一个案例所不同的是，如果查无结果，需要返回指定值：查无结果。 公式如下： G2输入公式▼ =XLOOKUP(F2,B:B,D:D,”查无”) XLOOKUP的第4参数可以指定容错值，当查无结果时避免返回错误值#N/A，省去了外围再嵌套一个IFERROR函数。 3）模糊条件查询 如下图所示，A:B列是数据明细，需要根据F列姓名的简称查询相关特长。这是一个模糊查询的示例，比如查找星光，对应的结果为看见星光。 公式如下： E2输入公式▼ =XLOOKUP(““&D2&”“,A:A,B:B,”查无”,2) XLOOKUP的查找值是”*”&D2&”*”，*是通配符，可以代替0到多个字符串，”*”&D2&”*”也就指包含D2的字符串。 但和VLOOKUP所不同的是，XLOOKUP默认不支持通配符匹配，只有将第5参数设置为常数2时，才支持通配符匹配。 XLOOKUP的第5参数可以指定匹配方式，包含了精确匹配、区间匹配以及通配符匹配等。 4）区间查询 如下图所示，F:G列是评分标准，60以下不及格，80以下及格等，需要根据该评分标准，对C列的成绩计算评级。 公式如下： D2输入公式▼ =XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,””,-1) XLOOKUP第5参数为-1，指定了匹配方式是’精确匹配或下一个较小的项’，比如查找84，找不到精确匹配，则寻找比它小的项，也就是80，然后取其对应结果:’良好’。 这儿的XLOOKUP等同于LOOKUP函数▼ =LOOKUP(C2,F:G) 但和LOOKUP所不同的是，XLOOKUP函数不要求查找区域首列数据升序排列，即便把F:G列的数据打乱了，也不妨碍它寻找’精确匹配或下一个较小的项’的计算规则▼ 除此之外，XLOOKUP还支持’精确匹配或下一个较大的项’的计算规则▼ =XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,””,1) 第5参数指定值为1，比如查找80，找不到精确匹配，则寻找比它大的项，也就是90。 5）查询符合条件的最后一个结果 如下图所示，A:C列是数据明细，其中日期字段升序排列。需要根据E列姓名查询相关销售额，但和前面案例所不同的是，它需要查找每个人最后一次销售额，也就是符合条件的最后一条记录。 公式如下： F2输入公式▼…