excel学习库

本人分享的教程技巧，都是非常实用干货的，学了就能用上，真正的助你提高工作效率，碾压同事。

1.格式相同的多表汇总销售量

在汇总表输入公式：

=SUM('*'!C:C)

*就代表除了汇总表以外的所有工作表，回车以后，公式会自动帮你写成标准的。

WPS表格不支持*这种用法，只能写完整的。

=SUM('1月:7月'!C:C)

2.从系统导出来的数据求和为0

在C2输入公式下拉。

=--B2

在C9输入公式求和。

=SUM(C2:C8)

这样就可以求和了。从系统导出来的数据是文本数字，需要转换成数值。--的作用就是通过运算将文本数字转换成数值。

也可以用这个公式，省略掉辅助列。

=SUMPRODUCT(--B2:B8)

3.根据商品查找单价

在很多公司，都有商品对应表，直接通过VLOOKUP进行查找，就不用再手工输入了。

=VLOOKUP(A2,H:I,2,0)

语法：

=VLOOKUP(查找值,要在哪个区域查找,返回区域第几列,0)

4.统计总金额

如果原来区域已经有金额这一列，那SUM就可以。

=SUM(D2:D5)

如果原来区域没有金额这一列，要用SUMPRODUCT才行。SUMPRODUCT的用法非常多，这种用逗号隔开的就是最原始的，就是对两个区域的乘积再求和。

=SUMPRODUCT(B2:B5,C2:C5)

5.统计每个商品的总金额

商品都是不断的销售，因此就会产生很多条记录，需要进行汇总，SUMIF就派上用场。

=SUMIF(B:B,G2,E:E)

语法：

=SUMIF(条件区域,条件,求和区域)

6.统计每个月的总金额

SUMIF的条件区域不支持嵌套MONTH，只好借助SUMPRODUCT。

=SUMPRODUCT((MONTH($A$2:$A$9)=G2)*$E$2:$E$9)

语法说明：

=SUMPRODUCT((条件区域=条件)*求和区域)

这里有一个注意点，就是日期有的时候是跨年的，就不能直接用MONTH，还需要判断年份。

TEXT($A$2:$A$9,"emm")就是将日期转换成年月的形式。

=SUMPRODUCT((TEXT($A$2:$A$9,"emm")="2021"&G2)*$E$2:$E$9)

7.将文本转换成0

在C2输入公式下拉。

=N(B2)

N的作用就是将文本转换成0，转换成这样有什么好处呢？

这样方便后期数据处理，比如判断成绩是否及格。直接判断的话，文本会导致误判。

正确的做法是：

=IF(N(B2)>=60,"及格","不及格")

8.四舍五入，防止出现1毛钱的差异

在核对金额的时候，经常会出现这种情况，明明两个金额看起来一样，但是公式显示结果却有问题，怎么回事呢？

=IF(D2=E2,"正确","错误")

9.设置单元格为整数就是10，但实际还是9.9，设置单元格并不能改变数字原来的大小。如果要改变大小，进行四舍五入，需要嵌套ROUND。0就是保留整数，如果是2就是保留小数点2位。

=IF(ROUND(D2,0)=E2,"正确","错误")

9.查找重复的身份证

超过15位字符，条件要用B2&"*"，要不然会判断出错。其他情况条件直接写B2就可以。

=IF(COUNTIF(B:B,B2&"*")>1,"重复","")

身份证还有很多问题也一起讲了。

10.性别：第17位为奇数是男，偶数是女。

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

11.出生日期：7~10位为出生年份，11~12位为出生月份，13~14位为出生日。

=TEXT(MID(B2,7,8),"0-00-00")

12.周岁：

=DATEDIF(D2,TODAY(),"y")

13.年龄：

=DATEDIF(D2,TODAY(),"y")&"岁"&DATEDIF(D2,TODAY(),"ym")&"个月"

TODAY函数也可以改为NOW函数，效果一样。

14.提取年月日

=YEAR(A2)

=MONTH(A2)

=DAY(A2)

15.将日期转变成星期几

=TEXT(A2,"aaaa")

=TEXT(A2,"dddd")

16.动态序号，也就是说筛选的时候自动从1到N

使用公式：

=SUBTOTAL(3,B$2:B2)*1

如果不需要针对筛选，直接用这个公式。

=ROW(A1)

上面都是一些相对简单的案例，下面这些就很难。

17.提取不重复地市，并获取相对应的区县

地市，在D2输入公式，按Ctrl+Shift+Enter三键结束，下拉填充公式。

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($2:$19)-1,ROW($2:$19),4^8),ROW(A1)))&""

区县，在E2输入公式，按Ctrl+Shift+Enter三键结束，下拉和右拉填充公式。

=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($2:$19),4^8),COLUMN(A1)))&""

18.根据品名查询所有符合条件的值

在G4输入公式，按Ctrl+Shift+Enter三键结束，下拉和右拉填充公式，并将G列的区域设置为日期格式。

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$16=$H$1,ROW($2:$16)),ROW(A1))),"")

19.依次查找满足条件的值，如网络技术应用，第一个对应值是李四，第二个是丽萍，第三个是海华。

在E2输入公式，按Ctrl+Shift+Enter三键结束，下拉填充公式。

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$16=D2,ROW($2:$16)),COUNTIF(D$2:D2,D2))),"")

20.将小写金额转变成大写金额

在D4输入公式下拉。

=IF(C4<0,"无效数值",IF(C4=0,"",IF(C4<1,"",TEXT(INT(C4),"[dbnum2]")&"元")&IF(INT(C4*10)-INT(C4)*10=0,IF(INT(C4)*(INT(C4*100)-INT(C4*10)*10)=0,"","零"),IF(AND((INT(C4)-INT(C4/10)*10)=0,INT(C4)>0),"零"&TEXT(INT(C4*10)-INT(C4)*10,"[dbnum2]")&"角",TEXT(INT(C4*10)-INT(C4)*10,"[dbnum2]")&"角"))&IF((INT(C4*100)-INT(C4*10)*10)=0,"整",TEXT(INT(C4*100)-INT(C4*10)*10,"[dbnum2]")&"分")))

21.将B列的金额依次拆分到后面的单元格。

在C2输入公式下拉和右拉。

=LEFT(RIGHT(TEXT($B2*100," ￥000;;"),COLUMNS(F:$P)))

22.将字符内的数字提取出来

在B2输入公式，按Ctrl+Shift+Enter三键结束，下拉填充公式。

=-LOOKUP(1,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&1/17)),ROW($1:$15)))

23.根据D列的所有序号，查询A列在B列的对应番号，并合并起来。

直接输入公式，按Ctrl+shift+Enter三键结束。适用版本Excel2016。

=TEXTJOIN("，",1,IFERROR(VLOOKUP(N(IF({1},--TRIM(MID(SUBSTITUTE(D2,"，",REPT(" ",50)),{1,2,3,4,5,6,7,8,9}*50-49,50)))),A:B,2,0),""))

其实在我心中，没有所谓的简单跟复杂，只要能提高工作效率就可以。在你心中，Excel的作用是什么？