1-2 求和类函数
1.SUM=SUM(区域)
2.SUMIF=SUMIF(条件区域,条件,求和区域)
| | | | | | |
|---|
| | | | | | |
| | | | | =SUM(B2:C5) | |
| | | | | =SUMIF(B2:C5,">10000000" | |
| | | | | | |
| | | | | | |
3-4 计数类函数
3.COUNT
=COUNT(区域)
4.COUNTIF
=COUNTIF(条件区域,条件)
| | | | | | |
|---|
| | | | | | |
| | | | | =COUNT(A8:B8) | |
| | | | | =COUNTIF(A8:A11,A9) | |
| | | | | =COUNTIF(A8:A11,A10) | |
| | | | | =COUNTIF(B8:B11,"<>"&B10) | |
5-6 查找类函数
5.VLOOKUP
=VLOOKUP(值,区域,n,精准0/近似1)
6.XLOOKUP
=XLOOKUP(查找值,查找区域,第几行,0/1)
| | | | | | |
|---|
| | | | | | |
| | | | | =VLOOKUP(D14,A14:B18,2,0) | |
| | | | | =XLOOKUP(D15,A14:A18,B14:B18) | |
| | | | | | |
| | | | | | |
| | | | | | |
7-8 查找类函数
7.MATCH
=MATCH(查找值,查找区域,精确/近似)
8.INDEX
=INDEX(查找范围,第几行,第几列)
| | | | | |
|---|
| | | | | |
| | | | =MATCH(39,B2:B4,1) | |
| | | | =MATCH(27,B2:B4,1) | |
| | | | | |
| | | | | |
|---|
| | | | | |
| | | | =INDEX(A7:B8,2,2) | |
| | | | =INDEX(A7:B8,2,1) | |
9.IF
=IF(条件,符合条件,不符合条件)
| |
|---|
| |
| =IF(B14>TIME(9,0,0),"迟到","") |
| =IF(C14<TIME(19,0,0),"早退","") |
