让同事傻眼的技能：10个Excel 数组公式，用过一次就彻底上瘾！！

2024-11-04 07:19

在日常的Excel数据处理中，我们经常会遇到一些复杂的计算和操作需求。而单一的Excel函数可能无法满足这些需求，这时就需要借助数组公式来解决问题了。数组公式是Excel中强大且灵活的功能之一，通过合理运用数组公式，我们可以实现条件求和、在混合文本里提取指定内容，等复杂的数据处理任务，大大提高工作效率。今天我们一起来探索数组公式的奥秘吧！内容干货十足，记得右上角收藏，有备无患哦~

SUM+SUMIF此函数组合用于条件求和，如下图，输入公式为：=SUM(SUMIF(A2:A5,{'球球';'青青'},C2:C5))

图片

SUM+COUNTIF这两个函数结合，可用来统计不重复的姓名个数，如下图，输入公式为：=SUM(1/COUNTIF(A2:A11,A2:A11))之后要按Ctrl+Shift+Enter组合键，即可完成~COUNTIF(A2:A11,A2:A11)：对区域进行统计判断~1/COUNTIF(A2:A11,A2:A11)：将重复值删除。若仅出现一个值，1除以1即为1；若出现两个则1除以2为1/2，两个1/2求和也为1。相当于得出不重复的姓名个数~

图片

VLOOKUP+IF使用VLOOKUP函数查找时，如果查找值不在第一列的话查找会出错，这时结合IF函数就可以完成查找，输入公式：=VLOOKUP(E2,IF({1,0},B2:B5,A2:A5),2,0)➤IF({1,0},B2:B5,A2:A5){1,0}是一个由数字1和0构成的常量数组，分别用1和0作为IF函数的第1个参数进行计算，把B2:B5的值放在1的位置，把A2:A5的值放在0的位置，重构一个2列4行的数组~所以，这里只是把原B列放在查找区域的第1列，A列放在第2列，IF({1,0}把两列值转换位置，实现了VLOOKUP的从左到右查找~

图片

VLOOKUP+MATCH此函数组合多用于交叉查询，输入公式：=VLOOKUP(G2,A1:E4,MATCH(H2,A1:E1,0),0)1.先用MATCH(H2,A1:E1,0)确定姓名所在的列~2.之后再使用VLOOKUP函数查找引用~

图片

INDEX+MATCH这一对组合函数，也是多用于数据查询引用，输入公式：=INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G1,B1:D1,0))1.第一个MATCH函数：查找青青在【A2:A5】单元格区域中的位置，返回数字3~2.第二个MATCH函数：查找数学在【B1:D1】单元格区域中的位置，返回数字2~3.将两个MATCH函数返回的结果，作为INDEX函数的第二、第三个参数，表示返回【B2:D5】单元格区域中第3行、第2列的值，即91~

图片

INDEX+SMALL+IF这个函数组合常用于获取满足条件的多个值~输入公式1：=INDEX(B:B,SMALL(IF($A$1:$A$13=$E$2,ROW($A$1:$A$13),4^8),ROW(A1)))&''

图片

输入公式2：=INDEX(C:C,SMALL(IF($A$1:$A$13=$E$2,ROW($A$1:$A$13),4^8),ROW(A1)))&''

图片

➤公式原理：SMALL函数：用于定位所有E2在A列中的位置（从小到大）~4^8是一个比较大的数：在IF公式中若单元格区域A1:A13的值等于E2，即显示E2在A列里的行号，若不等于则显示一个较大的数~SMALL函数获得行号之后，再结合INDEX函数一对多查找所需的值~最后的&''是将结果转换为文本格式，确保在没有匹配结果的时候显示为空字符串~

OFFSET+MATCH这对函数组合是多条件查找引用，如下图，输入公式为：=OFFSET(A1,MATCH(G2,A2:A5,0),MATCH(F2,B1:D1,0))1.先使用MATCH函数：分别定位出指定姓名和科目在A2:A5和B1:D1数据区域里的位置，作为OFFSET函数的第2个和第3个参数~2.再以A1为基准位置偏移对应的行数与列数~

图片

IF+AND(OR)公式一：IF与AND函数结合，用于获取同时满足多个条件的值=IF(AND(B2>=60,C2>=60,D2>=60),'及格','')

图片

公式二：IF与OR函数结合，可用来获取满足其中任一条件的值=IF(OR(B2>=90,C2>=90,D2>=90),'优秀','')

图片

LEFT(RIGHT)+LEN+LENB这两对组合函数公式，一般用于在混合文本里提取指定内容。如下图~➤公式一：=LEFT(A2,LENB(A2)-LEN(A2))

图片

➤公式二：=RIGHT(A2,2*LEN(A2)-LENB(A2))

图片

SUMPRODUCT+SUBSTITUTE这两个函数相结合使用，可用于求和统计带单位的数据，输入公式：=SUMPRODUCT(SUBSTITUTE(C2:C13,'分','')*1)&'分'1.SUBSTITUTE(C2:C13,'分','')：先将C列的“分”全部替换为空值，然后乘以1，即可将文本转为数值~2.再利用SUMPRODUCT函数求和即可~

图片

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。