函数和公式

函数和公式

函数和公式的区别：

公式由用户自行设计对工作表进行计算和处理的计算式。函数是预先定义好的特殊公式，可以是公式的一部分，但公式不一定总需要包含函数。不管公式和函数总是以等号开始。函数名称后紧跟左括号，接着是用逗号分隔的称为参数，最后用一个右括号表示函数结束。

公式包含参数和运算符，运算符有：算数运算符、比较运算符、文本运算符、引用运算符。（函数类似）

①算数运算符有：+ (加) - (减) * (乘) / (除) % (百分比) ^ (乘方)

②文本运算符只有1个→&，功能是将单元格文本连接起来，如果输入文本必须用双引号""括起来。

③比较运算符有：= (等于) < (小于) > (大于) <= (小于等于) >= (大于等于) <> (不等于)

比较运算后会返回1个逻辑值，有逻辑假→FALSE(不符合条件）和逻辑真→TRUE(符合条件）④引用运算符有: (冒号)→区域运算符，包括两个引用之间（含两个引用）在内所有单元格的引用

, (逗号)→联合运算符，将多个引用合并为1个引用

空格→交叉运算符，对共有单元格的引用

单元格引用有相对引用和绝对引用：还有混合引用单元格（行采用相对引用，列采用绝对引用；或列采用相对引用，行采用绝对引用）

相对引用是用单元格所在的列标和行号作为其引用，就是输入格式或函数后可向下拖动填充。绝对引用是在列标和行号前加上符合"$",例如$a$1,特点是向下复制公式或函数时，单元格引用不会发生变化。

运算顺序是：先乘除后加减，先算括号里再算括号外

在公式中如果对文本的引用，需要在文本的两边用双引号(即英文状态下)围起来，数字则不需要用引号。

1、提取空格左边的数据的话，可以用公式=MID(A1,1,FIND(" ",A1,1)-1)

提取空格右边的数据的话，可以用公式=right(a1,len(a1)-find(" ",a1))

2、Excel中获取当前年份的函数是=year(now())

3、提取指定字符前面所有的字符(包括指定字符，"司"-指定的字符)

=LEFT(A1,(FIND("司",A1)))

4、提取指定字符前面所有的字符(不包括指定字符，"司"-指定的字符)

=LEFT(A1,FIND("司",A1)-1)

5、提取指定字符之后所有的字符(不包括指定字符，"司"-指定的字符)

=RIGHT(A1,LEN(A1)-FIND("司",A1))

6、去掉字符后面的几个字符

=LEFT(A1,LEN(A1)-2)

表述为：从单元格A1字符的左边起，提起所有的字符数，去掉后面两个字符。

7、去掉字符前面的几个字符

=RIGHT(A1,LEN(A1)-2)

表述为：从单元格A1字符的右边起，提起所有的字符数，去掉后面两个字符（即最左边的两个字符)。

8、从最左边起取字符

=left（A1，2）

表述为：从单元格A1的最左边起，提取2个字符。

9、从最右边起取字符

=right(A1,2)

表述为：从A1单元格最右边起，提取2个字符

10、在B2单元格中输入公式：=IF(A2>=60,"符合要求","不符合要求")，确定后，如果A列单元格中的数值大于或等于60，则B列单元格显示“符合要求”字样，反之显示“不符合要求”字样。

11、查字段长度

=len(A1)

12、对比两列内容（数值）是否一致

=A1=B1

13、批量加入一段字符（如都加入“中国”）

="中国"&A1 (加在前)

=A1&"中国" (加在后)

14、前后同时批量加入字符（如前加入“中国”，后加入“号”）

="中国"&A1&"号"

15、合并两列数据

=A1&B1

合并时中间加空格或横杠

=A1&"-"&B1

16、合并三列及以上数据

=A1&B1&C1

合并时中间加空格或横杠

=A1&" "&B1&" "&C1

17、任意提取字段

=MID(A1,2,3)

表述为：从单元格A1的第二个字符开始取，提取3个字符。

18、查单元格重复项

①=COUNTIF(A:A，A1）

结果为1表示不重复，结果为2表示重复的条数

②=COUNTIF(A:A,A1&"*")

如果单元格数字超过15位（身份证），即使格式为文本格式，但15位后数字就忽略了（也就是说前15位相同，后几位不同结果也显示为重复数据），必须在后面加上&"*"即强制文本格式，不管有多少位所有数字全部都为文本格式了

19、查单元格空格的位置

=find(" ",A1)

20、提取单元格固定字符后所有字符

=MID（A1,5,LEN(A1))

表述为：从单元格A1的第5个字符开始取所有的字符。

21、如何提取两个空格中间的字段

①=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,98))

②=LEFT(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)),""),FIND(" ",SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1)),""))-1)

③使用序列

22、在excel中，把手机号码的中间几位变成星号（假定原手机号在A列）

第1种方法

在A列后插入列，输入=REPLACE(A1,5,3,"***")

解释：将A1的字符内容从第5位开始，连续3位替换为***

结果为(1386***2234），

第2种方法

在A列后插入列，输入=left(A1,3)&"****"&RIGHT(A1,4)

结果为(138****1234），再把生成的星号列复制-选择性粘贴（数值），再把原来的列删除。

说明：

①无论哪种公式，都不能填写在原来的单元格内，写在其他列的单元格内如B1，然后通过复制或下拉公式全部改变

②第一个公式比较好，适用于各种号码，第二个适用于有固定位数的号码，如手机号固定为11位，若是另外的号码，这左右两边取的数字要因此改变，不够通用，所以，建议用第一个。