Excel秘籍大全，正文开始

文章最后有彩蛋！好礼相送！

一、库存表公式

1、每月出、入库汇总

D4=SUMIF($H$3:$BQ$3,D$3,$H4:$BQ4)

说明：向右填充公式至E4单元格，然后向下填充。用SUMIF函数对满足单条件的单元格求和。

2、求库存剩余数

F4=C4+D4-E4

说明：向下填充公式。库存=上月结余+当月入库-当月出库

3、库存不足提醒

G4=IF(F4<150,"补货","充足")

说明：向下填充公式。用IF函数进行条件判断，当库存不足150时，显示为补货；如何标记颜色，选中区域G4:G11，点击工具栏上的“开始”-“条件格式”-“新建规则”，弹出窗口，选中最后一种规则类型，输入公式:=$G4="补货"，接着是点击“格式”-“图案”，选中你想标记的颜色，最后点击“确定”即可。

4、求各产品每天出、入库数量

H4=SUMIFS(明细表!$E:$E,明细表!$C:$C,$B4,明细表!$B:$B,DATE(2021,12,INT(COLUMN(B2)/2)),明细表!$D:$D,H$3)

说明：向右填充公式。由于H2、I2合并为一个单元格，所以要让日期重复显示2次，先COLUMN函数返回某单元格所在的列号，再用INT函数将数字向下舍入到最接近的整数，再除以2，让数字重复2次，然后用DATE函数将年、月、日转换为日期。最后用SUMIFS函数对满足多条件的单元格求和。

二、出入库汇总表公式

1、提取不重复的产品名称

A3=INDEX(明细表!$B$3:$B$3000,MATCH(0,COUNTIF(A$2:A2,明细表!$B$3:$B$3000),0))&""

说明：按ctrl+shift+enter组合键结束，然后向下填充公式。先用COUNTIF函数计算区域中满足单个指定条件的单元格个数，返回的结果有很多，但只有0和1两种情况，再用MATCH函数查找0在指定的数组元素中第一次出现的位置（位于第几行），最后用INDEX函数返回区域中指定行序数的单元格内容。

2、汇总当月出、入库数量

B3=SUMIFS(明细表!$D$3:$D$3000,明细表!$C$3:$C$3000,B$2,明细表!$B$3:$B$3000,$A3)

说明：向右填充公式至C3单元格，然后向下填充。用SUMIFS函数对满足多条件的单元格求和。

3、求当月库存数

D3=B3-C3

三、出货单公式

1、求金额

G5=E5*F5

说明：向下填充公式至G10单元格，金额=数量*单价。

2、金额合计小写

G11=SUM(G5:G10)

说明：公式输入完毕，单击右键，设置单元格格式，点击“数字”中的“货币”，保留两位小数，然后“确定”即可。用SUM函数对某区域内所有的数字进行求和。

3、金额合计大写

C11=G11

说明：公式输入完毕，单击右键，设置单元格格式，点击“数字”中的“特殊”，选中“人民币大写”，然后“确定”即可。

4、设置编号

点击F2单元格，单击右键，设置单元格格式，点击“数字”中的“自定义”，在类型处输入“NO:00000000”，接着点击“确定”,再输入数字。

5、设置日期

点击G3单元格，单击右键，设置单元格格式，点击“数字”中的“日期”，选中你想要的类型，接着点击“确定”。

四、下半年出入库汇总表公式

1、各产品每月出、入库汇总

C4=SUMIFS(INDIRECT(INT(COLUMN(N1)/2)&"月份"&"!$E:$E"),INDIRECT(INT(COLUMN(N1)/2)&"月份"&"!$C:$C"),$B4,INDIRECT(INT(COLUMN(N1)/2)&"月份"&"!$D:$D"),C$3)

说明：向右填充公式至N4单元格，再向下填充。先用COLUMN函数返回某单元格所在的列号，再用INT函数将数字向下舍入到最接近的整数，再除以2，目的是让数字重复2次，接着用INDIRECT函数引用“7月份”至“12月份”工作表中的单元格内容，最后用SUMIFS函数对满足多条件的单元格求和。

2、各产品下半年出、入库汇总

O4=SUMIF($C$3:$N$3,O$3,$C4:$N4)

说明：向右填充公式，再向下填充。

五、累计数量求和

D2=SUM(C$2:C2)

说明：向下填充公式。SUM函数作用对区域内所有数字进行求和。

六、求产品装箱数及剩余数

E2=INT(C2/D2)&"箱"&MOD(C2,D2)&"包"

说明：向下填充公式。先用INT函数求两数相除的整数，再用MOD函数求两数相除的余数。

七、求各产品库存数

F3=SUMIF(C$3:C3,C3,D$3:D3)-SUMIF(C$3:C3,C3,E$3:E3)

说明：向下填充公式。用SUMIF函数对满足单条件的单元格求和,接着，库存=入库-出库。BB产品库存数为850，AA产品库存数为1250。

八、核对账面数、盘点数

F2=VLOOKUP(D2,$A$2:$B$10,2,FALSE)

G2=E2-F2

说明：选中区域F2:G2，向下填充公式。VLOOKUP函数的作用是查找数值，这样可以让一个表格以另一个表格顺序进行排序，最后让两个数相减。

2024-04-21 16:53·Excel秘籍大全

文章最后有彩蛋！好礼相送！

熟悉Excel的小伙伴们都知道，IF函数是Excel中最基础的条件判断函数，其用法也很简单：=IF(判断条件，真值，[假值])，第一参数判断条件可以是数值或表达式，真值TRUE为满足判断条件时返回的值，假值FALSE为不满足判断条件时返回的值，第三参数可省略。IF函数除了应用于基础的条件判断外，还有很多高级用法，很多小伙伴觉得IF函数很简单，看了以下用法，你还这样觉得吗？

1.IF函数基础用法

先来让大家回顾一下IF函数的基础用法，如图1所示，工龄大于等于3年，可享受薪资的5%作为提成，现在要求取满足条件的每名员工的提成金额，在G2单元格输入公式：=IF(D2>=3,F2*$I$2,"无提成")，并向下填充。

图1

2.逆向查询

VLOOKUP函数是目前职场应用频率最高的查询函数，但它仍然有不少不足之处，比如说，它本身并不支持逆向查询，需要搭配IF函数才可以。如图2所示，根据薪资查询对应的员工姓名，其公式为：=VLOOKUP(H2,IF({1,0},F1:F8,A1:A8),2,0)。

IF函数的第一个参数为数值{1,0}，当第一个参数为1时，它表示真值，返回IF函数的第二个参数F1:F8构建的数据区域，当第一个参数为0时，它表示假值，返回IF函数的第三个参数A1:A8构建的数据区域，将0和1返回的结果结合起来，IF函数会构建成F1:F8&A1:A8联合起来的两列数据区域，A1:A8为姓名所在的第二列数据区域，对应VLOOKUP函数的第三参数2。IF函数的作用就是将原本逆向的数据区域让其顺向排列，进而搭配VLOOKUP函数进行数据查询。

图2

3.多条件判断——并关系

如图3所示，当工龄大于等于3年，年龄大于等于30，两个条件都满足时可获得其薪资的5%作为提成，要计算满足两个条件的员工的提成金额，在G2单元格输入公式：=IF(AND($B$2:$B$8>=30,$D$2:$D$8>=3),F2*$J$2,"无提成")，并向下填充，也可以写成：=IF(($B$2:$B$8>=30)*($D$2:$D$8>=3),F2*$J$2,"无提成")。

AND是Excel中的逻辑值函数，当每个参数判断条件都成立时AND函数返回TRUE时，AND($B$2:$B$8>=30,$D$2:$D$8>=3)和($B$2:$B$8>=30)*($D$2:$D$8>=3)意义一样，表示“并”的关系，即要求两个条件都满足。$B$2:$B$8>=30返回逻辑值组成的数组{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}，$D$2:$D$8>=3返回数组{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}，TRUE为满足条件返回的值，FALSE为不满足条件返回的值，在Excel四则运算中，逻辑值TRUE可以视为1，FALSE视为0，当两个数组相乘时，返回数组{0;1;1;0;0;0;1}，1表示TRUE*TRUE即1*1，即两个条件都满足，当数组{0;1;1;0;0;0;1}为1时，返回IF函数的第二参数，为0返回IF第三参数。

图3

4.多条件判断——或关系

如图4所示，当满足工龄大于等于3或年龄大于等于30任意一个条件时，获得提成金额，要求满足条件的员工的提成金额，在G2单元格输入公式：=IF(($B$2:$B$8>=30)+($D$2:$D$8>=3),F2*$J$2,"无提成")，也可写成=IF(OR($B$2:$B$8>=30,$D$2:$D$8>=3),F2*$J$2,"无提成")。

逻辑值函数OR表示当其参数其中有一个返回TRUE，OR函数返回TRUE，($B$2:$B$8>=30)+($D$2:$D$8>=3)和OR($B$2:$B$8>=30,$D$2:$D$8>=3)意义一样，都表示“或”的关系。当逻辑值相加时，只有其中有一个是TRUE，那么结果就为TRUE。($B$2:$B$8>=30)+($D$2:$D$8>=3)返回数组{0;1;1;0;1;0;2}，当为1或2时表示真值返回IF第二参数，为0表示假值返回IF函数第三参数。

图4

5.判断数据是否存在重复

如图5所示，判断姓名是否重复，其公式为：=IF(COUNTIF($A$2:$A$10,A8)>1,"是","否")。

用COUNTIF函数计数，如果有重复就会返回大于1的数值，IF函数的第一个参数如果是大于0的数值，那么它表示真值就会返回第二参数，如果为0即为假值会返回第三参数。

图5

6.忽略错误值和文本求和

如图6所示，忽略文本和错误值统计总销量，其公式为：=SUM(IF(ISNUMBER(C2:C10),C2:C10,0))，并同时按下Ctrl+Shift+Enter。

图6

先用逻辑值函数ISNUMBER对数据区域C2:C10进行判断，当为数值时返回TRUE，否则返回FALSE，ISNUMBER(C2:C10)返回的结果为数组{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}，IF函数第二参数返回数组{121;"未统计";132;132;#VALUE!;211;166;#NAME?;138}，第一参数数组中对应的元素TRUE会返回第二参数的数组对应的元素，IF函数最终的值为{121;0;132;132;0;211;166;0;138}，再用SUM函数对这个数组进行求和。

7.多条件求和

如图7所示，统计薪资7500以上的男性员工的总工资，其公式为：=SUM(IF((C2:C8=H2)*(F2:F8>7500),F2:F8,0))，并同时按下Ctrl+Shift+Enter。

图7

(C2:C8=H2)*(F2:F8>7500)为逻辑值组成的数组相乘，结果为{0;0;1;0;0;0;1}，为1时返回IF函数的第二参数，为0时返回IF的第三参数，最终IF函数返回的结果为数组{0;0;8000;0;0;0;8500}，再用SUM函数对这个数组进行求和。

8.对不规则的重复值排序

如图8所示，对部门进行排序，其公式为：=IF(B2<>B1,1,A1+1)，对相邻的两个上下单元格进行判断，等不等于的时候，返回第二参数1，当等于时，返回第三参数，第三参数值为上个序号+1。

图8

9.IF+ISERROR处理错误值

IF+ISERROR处理错误值处理错误值比较常见 如图9所示，用VLOOKUP函数查找“小包”对应的线上销量，“小包”并不存在于数据区域中，也就是查找值不存在，当查找值不存在时VLOOKUP会返回#N/A错误值，这个时候就需要借助IF+ISERROR来将错误值转化为空值或者自行设定的文本，公式为：=IF(ISERROR(VLOOKUP(E2,A1:C10,3,FALSE)),"查找到此人",VLOOKUP(E2,A1:C10,3,FALSE))或者写：=IF(ISERROR(VLOOKUP(E2,A1:C10,3,FALSE)),"查找到此人",VLOOKUP(E2,A1:C10,3,FALSE))。

图9

10.IF多判断嵌套

IF多判断进行嵌套也是比较常见的情况，新版的EXCEL或者WPS支持IFS函数来代替IF函数的多条件嵌套，这里还是来给大家介绍原始的IF函数嵌套。如图10所示，判断成绩等级，其公式为：=IF(D2>=90,"优秀",IF(D2>=80,"良好",IF(D2>=60,"及格","不及格")))，也可以用IFS函数，其公式为：=IFS(D2>=90,"优秀",D2>=80,"良好",D2>=60,"及格",D2<60,"不及格")。

图10

以下《900套高逼格工作模板.xls 》免费下载，不收一分钱！

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言！

今天推荐的超实用干货是

《900套高逼格工作模板.xls 》

3.2G高逼格Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字：900

即可免费领取

资料来源于网络，公益分享，如有侵权，联系删除