| No-1 | And | 场景 | 判定所有条件的真假，当所有条件都为真，返回True，有一个为假，返回FALSE |
|
|
|
|
|
|
|
| 语法 | =AND(条件1，条件2····) |
|
|
|
|
|
|
|
| 案例 | =AND(1=1,2=2,3=3) |
|
|
|
|
|
|
|
|
| =AND(1=1,2=2,3=0) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| No-2 | OR | 场景 | 判定所有条件的真假，当有一个条件为真，返回TRUE，当所有条件都为假，返回FALSE |
|
|
|
|
|
|
|
| 语法 | =OR(条件1，条件2····) |
|
|
|
|
|
|
|
| 案例 | =OR(1=0,2=0,3=3) |
|
|
|
|
|
|
|
|
| =OR(1=0,2=0,3=0) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| No-3 | IF | 场景 | 根据指定的判断条件，在正确或错误的状态下，分别返回不同的值 |
|
|
|
|
|
|
|
| 语法 | =IF(条件，条件为真返回值，条件为假返回值) |
|
|
|
|
|
|
|
| 案例 | 成绩 | 90 |
|
|
|
|
|
|
|
| =IF(E22<80,"良好","优秀") |
|
|
|
|
|
|
|
| 嵌套 | =IF( AND(E22<80,E22>60),"良好","==") |
|
|
|
|
|
|
|
| 嵌套 | =IF(E22<60,"不及格",IF(E22<80,"良好","优秀")) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| No-4 | LEFT | 场景 | 从左边开始，截取指定位数的字符 |
| ee |
|
|
|
|
|
| 语法 | =LEFT(截取对象，截取长度） |
| 55 |
|
|
|
|
|
| 案例 | 对象 | 2018_09 |
|
|
|
|
|
|
|
| =LEFT(E30,4) |
|
|
|
|
|
|
|
|
| =LEFT(E30,4)+0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| No-5 | RIGHT | 场景 | 从右边开始，截取指定位数的字符 |
|
|
|
|
|
|
|
| 语法 | =right(截取对象，截取长度） |
|
|
|
|
|
|
|
| 案例 | 对象 | 9,789,279 |
|
|
|
|
|
|
|
| =RIGHT(E37,5) |
|
|
|
|
|
|
|
|
| =RIGHT(E37,5)+0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| No-6 | MID | 场景 | 从左边指定字符位置开始，截取指定位数的字符 |
|
|
|
|
|
|
|
| 语法 | =MID(截取对象，开始字段位置，截取长度） |
|
|
|
|
|
|
|
| 案例 | 对象 | I Love You |
|
|
|
|
|
|
|
| =MID(E44,3,4) | 你拉客的 |
|
|
|
|
|
|
|
| =MID(E45,2,2)&0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| No-7 | LEN | 场景 | 返回指定对象的字符个数 |
|
|
|
|
|
|
|
| 语法 | =LEN(指定对象） |
|
|
|
|
|
|
|
| 案例 | 对象 | 2,780离开 |
|
|
|
|
|
