excel的公式由什么构成,excel的公式有哪些

相对引用是指公式复制时随着单元格的变化而变化，引用的地址不固定（对于复制公式时特别有效）。

绝对引用是指公式复制时单元格固定不变。绝对引用前面有个$，相对引用则没有，混合引用就是行与列一个是相对引用，一个是绝对引用。利用F4键可以灵活切换相对引用和绝对引用。对于初学者，可以这样去记忆，“有钱能使鬼推磨”，有$就是绝对引用，一心一意跟着你不跑，没有$就是相对引用，像墙头草随风倒。

一个基本的公式可以按照一个或多个参数或者数值来产生一个单一的结果，用户既可以输入对包含数值的单元格的引用，也可以输入数值本身。在数组公式中，通常使用单元格区域引用，但也可以直接输入数值数组。输入的数值数组称为数组常量。

数组公式可能是功能最强大的公式，因为它可以在一个公式中执行多步计算，一次性处理多个操作，这是普通公式无法实现的。

数组中使用的常量可以是数字、文本、逻辑值（“TRUE”或“FALSE”）和错误值等。数组有整数型、小数型和科学计数法形式。文本则必须使用引号引起来，例如“星期一”。在同一个数组常量中可以使用不同类型的值。数组常量中的值必须是常量，不可以是公式。数组常量不能含有货币符号、括号或百分比符号。所输入的数组常量不得含有不同长度的行或列。

数组常量可以分为一维数组与二维数组。一维数组又包括垂直和水平数组。在一维水平数组中元素用逗号分开，如｛10，20，30，40，50｝；在一维垂直数组中，元素用分号分开，如｛100；200；300；400；500｝。而对于二维数组中，常用逗号将一行内的元素分开，用分号将各行分开。

数组公式与相同功能的普通公式：

{=SUM(B2:B7*C2:C7)}

=SUMPRODUCT(B2:B7,C2:C7)

COUNT只计数，文本、逻辑值、错误信息、空单元格都不统计。

COUNTA统计非空单元格个数，只要单元格有内容，就会被统计，包括有些看不见的字符

COUNTIF：满足一定条件计数

COUNTIF函数是对指定区域中符合指定条件的单元格计数的函数，该函数的语法规则如下：

COUNTIF（range，criteria）

参数：range 要计算其中非空单元格数目的区域；

参数：criteria 以数字、表达式或文本形式定义的条件。

判断A列的身份证号码是否重复。

=IF(COUNTIF($A$2:$A$10,A2)>1,"重复","")

COUNTIFS语法：

COUNTIFS（条件区域1，条件1，条件区域2，条件2，…）

7.1 VLOOKUP函数多条件查找：

将不同条件用&连接起来，使多个条件变为一个条件。

如下图所示，要查找产品名称和型号都匹配的单价，可以把产品名称和型号2个字段合并为一个字段，即辅助列内容，再用VLOOKUP查找。

7.2 VLOOKUP函数模糊查找

例如，要计算不同的销售额对应的提成比例，如果用IF函数，公式会很长，用VLOOKUP模糊查找，最后一个参数省略或者为TRUE或1，表明该查找模式为模糊查找；如果找不到精确匹配值，则返回小于lookup_value 的最大数值。table_array 第一列中的值必须以升序排序，否则 VLOOKUP 可能无法返回正确的值。D3公式为

=VLOOKUP(B3,$G$3:$H$11,2)

如下图，需要把A列中英文分开

B1公式为：

=RIGHT(A1,LENB(A1)-LEN(A1))。

公式解析：LENB按字节数计算，LEN按字符数计算，一个汉字算2个字节，公式=LEN("腾讯")返回结果是2，公式=LENB("腾讯")返回结果是4，因此LENB与LEN函数结果相减得到中文汉字字符数，再用RIGHT函数提取位于右边的中文字符。

C1公式为=LEFT(A1,LEN(A1)-(LENB(A1)-LEN(A1)))

公式解析：LENB(A1)-LEN(A1)得到中文汉字字符数，再用总字符数LEN(A1)减去中文汉字字符数就得到英文字符数，再用LEFT函数提取位于左边的英文字符。

这个问题也可以用快速填充功能实现，用公式的好处是如果A列原始数据变了，分开的中英文自动跟着变，而快速填充则需要重新操作，这充分体现了公式的魅力。

也可以使用以下公式和函数来实现：

B2公式：=LEFTB(A2,SEARCHB("?",A2)-1)

C2公式: =MIDB(A2,SEARCHB("?",A2),11)

公式说明：SEARCHB是在一个字符串中查找特定字符位置的函数，

而且可以区分单双字节，它和FIND的区别是可以使用通配符。公式中的？就是表示任意一个单字节的字符，属通配符，不是真的查找问号。

－End－