今天看到一条新闻,女人入职被公司的乐捐制度吓跑。涨见识了,原来罚款就是乐捐,真够坑人的。而卢子从不坑人,给你的都是最好的Excel教程,学了就能用上,真正的助你提高工作效率,碾压同事。*就代表除了汇总表以外的所有工作表,回车以后,公式会自动帮你写成标准的。这样就可以求和了。从系统导出来的数据是文本数字,需要转换成数值。--的作用就是通过运算将文本数字转换成数值。 在很多公司,都有商品对应表,直接通过VLOOKUP进行查找,就不用再手工输入了。
=VLOOKUP(查找值,要在哪个区域查找,返回区域第几列,0) 如果原来区域没有金额这一列,要用SUMPRODUCT才行。SUMPRODUCT的用法非常多,这种用逗号隔开的就是最原始的,就是对两个区域的乘积再求和。商品都是不断的销售,因此就会产生很多条记录,需要进行汇总,SUMIF就派上用场。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) N的作用就是将文本转换成0,转换成这样有什么好处呢?这样方便后期数据处理,比如判断成绩是否及格。直接判断的话,文本会导致误判。=IF(N(B2)>=60,"及格","不及格") 在核对金额的时候,经常会出现这种情况,明明两个金额看起来一样,但是公式显示结果却有问题,怎么回事呢?
9.9设置单元格为整数就是10,但实际还是9.9,设置单元格并不能改变数字原来的大小。如果要改变大小,进行四舍五入,需要嵌套ROUND。0就是保留整数,如果是2就是保留小数点2位。=IF(ROUND(D2,0)=E2,"正确","错误") 超过15位字符,条件要用B2&"*",要不然会判断出错。其他情况条件直接写B2就可以。=IF(COUNTIF(B:B,B2&"*")>1,"重复","") =IF(MOD(MID(B2,17,1),2),"男","女") 11.出生日期:7~10位为出生年份,11~12位为出生月份,13~14位为出生日。=TEXT(MID(B2,7,8),"0-00-00") =DATEDIF(D2,TODAY(),"y")&"岁"&DATEDIF(D2,TODAY(),"ym")&"个月" 地市,在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)))&"" 在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))),"") =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]")&"分"))) =LEFT(RIGHT(TEXT($B2*100," ¥000;;"),COLUMNS(F:$P))) 在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),""))
|