【excel怎么确认公式】基于Excel公式和函数的

excel的公式是处理数据的重要工具。

1公式的组成

所有公式都用“=”符号指导，是通过运算符根据特定顺序组合处理数据运算的表达式。简单的公式有加、减、乘、除等计算。

公式可以包含函数，作为函数的返回结果参与运算，函数是根据特定算法生成计算结果或结果集的预定义特殊公式，函数也可以嵌套。

1可以以等号=开头，也可以以加号开关或@开头。

2运算符

3包含命名单元格和范围的单元格引用可以是当前工作表、当前工作簿中的其他工作表单元格，也可以是其他工作簿中的单元格。

4值或字符串

5工作表函数和参数；

6括号：控制公式中表达式的计算顺序。

1.1运算符：

运算符包括算术运算符、比较运算符、文本运算符、引用运算符、逻辑运算符等。可以使用运算符对数据执行各种操作。

1.2运算符优先级：

运算符的优先级是：如果一个运算符表达式中同时存在多个运算符，则首先计算优先级较高的运算符，括在括号中的运算符具有最高优先级。

1.3 A1参考样式

公式和函数的优点是可以引用工作表中的单元格并使用其值。引用本质上是行与列交叉的地址(Excel行号为1，2，3).列为a、b、c.C3是使用第3行的列)。在公式或函数中，对单元格的引用与使用变量相同。为了便于单元格引用，Excel使用两种引用方法：绝对引用。也就是说，对单元格的引用相对于公式位置不变(复制公式时)。相对引用相反，包含公式的单元格相对于引用单元格的位置(复制公式时)，因此公式的地址取决于公式所在的位置。如果单元格B2包含公式“=C3”，则两个单元格的寻址是相对的。引用单元格保留公式所在单元格的左下角，即下行、左列位置，如果存在相对引用，则保留相对引用。例如，如果在将公式复制到B3时使用相对引用，公式将变为'，因为其特性，如果确定对C3的引用不会更改，则使用绝对引用将公式更改为'=$C$3不考虑相对位置的变化。

用户不仅可以引用工作表中的单元格，还可以引用工作簿中多个工作表中的单元格。这称为3d参考。3d参考的一般格式是“工作表选项卡！单元格引用。例如，要引用Sheet1工作表中的单元格B2，请在该单元格中输入“Sheet1！必须输入“B2”。要分析工作簿中多个工作表中同一位置的单元格或单元格区域中的数据，必须使用3d引用。

要创建工作表和工作簿之间引用的公式：

=图纸名称！单元地址

='床单2 '！A1*5

='工作簿路径[工作簿名称]工作表名称'！单元地址

=[销售数据。xlsx]表2！A1*5

建立对多个工作表中相同储存格区域的3d参考。

开始工作表名称3360结束工作表名称！单元地址

=sum(图纸1:图纸3！(A1:A10)

=总计(图纸1！A1:A10、Sheet2！A1:A10、Sheet3！(A1:A10)

=总计(' * '！(A1:A10)

如多表相同位置求和：

=SUM('1月:12月'!C9)

在输入公式时，用户有时会将一个公式直接或者间接引用了自己的值，即出现循环引用。例如，在单元格A3中输入“＝A1＋A2＋A3”，由于单元格A3中的公式引用了单元格A3，因此就产生了一个循环引用。此时，Excel中就会弹出一条信息提示框，提示刚刚输入的公式将产生循环引用。

如果打开迭代计算设置，Excel就不会再次弹出循环引用提示。设置迭代计算的操作步骤如下。

步骤1：选择“文件”菜单中的“选项”命令，打开“选项”对话框，再选择“公式”选项卡。

步骤2：选中“启用迭代计算”复选框。

步骤3：在“最多迭代次数”文本框中输入循环计算的次数。

步骤4：在“最大误差”文本框中设置误差精度。

步骤5：单击“确定”按钮。

系统将根据设置的最多迭代次数和最大误差计算循环引用的最终结果，并将结果显示在相应的循环引用单元格当中。但是，在使用Excel时，最好关闭“启用迭代计算”设置，这样就可以得到对循环引用的提示，从而修改循环引用的错误。

2 公式类型

公式可以按参与运算的数据的类型区分为以下五种：

与普通公式不同，数组公式可以完成多步计算，而且需要使用【Ctrl+Shift+Enter】组合键输入数组公式，而不只是用【Enter】键。Excel会自动使用一对大括号将输入好的整个公式包围起来，以此来表明这是一个数组公式而非普通公式

公式应避免循环引用，包含直接和间接引用自己。

3 函数类型

根据公式所处理的数据类型不同，函数共12种，如下图所示，除了自定义函数之外，2003版本自带的函数有300多个，2007以及以上版本函数有400多个，一般来说，掌握常用的30～50个函数基本可以应对工作中的日常需求。

4 引用类型

Excel的工作表的单元格由行、列交叉而成，由行和列共同构成一个单元格的地址，在Excel中称为引用。是公式最重要的数据源。

引用的地址在进行公式复制时，并非固定不变，如B2的单元格输入=A2，复制到B3时，公式变更为=A3，复制到C4时，公式变更为=B4，引用的地址相对变化，这个公式可以理解为公式所在单元格等于左边单元格的值。

这样的引用称为相对引用。这是公式的强大之处，给公式复制和填充带来极大的方便。

再举个例子，下面E8=C8*D8，复制到F10的公式会是什么？

F10=D10*E10

上面公式使用相对引用，可以理解为“此单元格的值等于左边第二行特许以左边第二行的值”。

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

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

5 数组公式

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

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

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

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

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

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

=SUMPRODUCT(B2:B7,C2:C7)

6 count相关函数

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 vlookup函数使用

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)

8 实例，怎样把中英文分开

如下图，需要把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－