搜索
公式与函数基础一、运算符及其优先级运算符功能优先级-负值1%百分比2^幂3*和/乘和除4+和-加和减5&连接6=、、=、、=和比较7Excel中有两种比较特殊的运算符：逗号和空格。使用逗号分隔两个单元格区域时，说明在一个公式里需要同时使用这两个区域。例如，COUNT（A1：A3，A5：B6）表示统计A1：A3和A5：B6两个区域中包含数字的单元格总数。如果使用一个空格来连接两个区域，则表示要得到两个区域的交集，即重叠的区域。例如，（A1：B5A3：B6）的交叉部分包含的单元格有A3、A4、A5、B3、B4、B5这6个单元格。二、数组公式一个数组公式可以完成多步计算。与输入普通公式不同，需要使用【Ctrl+Shift+Enter】组合键输入数组公式，Excel会自动在公式两侧添加一对大括号，以此来表明这是数组公式而非普通公式。选择数组公式所在的区域：1、单击数组公式所在区域的任一单元格，然后按【Ctrl+/】组合键。或2、单击数组公式所在区域的任一单元格，然后按【F5】键打开【定位】对话框，单击【定位】按钮打开【定位条件】对话框，选择【当前数组】单选按钮，【确定】。修改数组公式：如果同时在多个单元格中输入同一数组公式，将无法单独编辑数组公式所在的某个单元格内的公式。，要编辑这样的数组公式，需要先选中数组公式所在的整个区域，然后按【F2】键，再修改，最后按【Ctrl+Shift+Enter】组合键确认修改。删除方法与编辑类似，选中所有区域，再按【Delete】键扩展或缩小多单元格数组公式：1、选择包含数组公式的区域，然后按【F2】键进入单元格的编辑的状态。2、按【Ctrl+Enter】组合键，将数组公式改为普通公式，并将公式输入到区域内的每个单元格中。然后扩展选区，以便包含新数据的单元格。3、再次按【F2】键，进入单元格的编辑状态，修改公式中的区域引用。然后按【Ctrl+Shift+Enter】组合键，以数组公式的形式将公式输入到新区域的每个单元格内。三、A1引用和R1C1引用在默认情况下，工作表单元格区域的顶部由字母A、B、C等大写英文字母组成，而左侧由1、2、3等数字组成，这种表示单元格的方式称为A1引用样式。在另一种引用样式R1C1引用样式中用字母R+行数字以及字母C+列数字来引用某个单元格。（相对引用：在R、C后面的数字上加上方括号;绝对引用：不加方括号）将R1C1样式改为A1样式的具体操作如下：1、单击【文件】选项卡，选择【选项】命令2、选择【公式】选项卡，取消选中右侧的【R1C1引用样式】复选框。四、审核公式1、使用公式错误检查器如果Excel检测到单元格中包含错误，当单击该单元格时，单元格附件将显示错误检查器的图标，单击该图标，弹出菜单顶部显示的是错误的类型，菜单中其他命令的含义如下：A、关于此错误的帮助：选择该选项，将打开帮助窗口，并显示相应的错误帮助主题。B、显示计算步骤：选择该选项，可通过分步计算检查发生错误的位置。C、忽略错误：选择该选项，将不理会发生的错误，保留公式的当前值。D、在编辑栏编辑：选择该选项，将允许用户直接在公示栏中修改公式错误，在状态栏中显示“编辑”字样。E、错误检查选项：选择该选项，可设置Excel对错误的检查规则。2、跟踪单元格关系追踪错误单元格时，需要了解一下3个概念：A、引用单元格：被其他单元格的公式引用的单元格。例如，单元格B1中包含公式“=A1+A2”，单元格A1和A2就是单元格B1的引用单元格，更确切地讲是直接引用单元格。如果上面公式的单元格A1又包含公式“=A3*A4”，那么单元格A3和A4就是单元格B1的间接引用单元格B、从属单元格：从属单元格中包含引用其他单元格的公式。例如，单元格B1中包含公式“=A1+A2”，那么单元格B1就是单元格A1和A2的从属单元格。C、错误单元格：在公式中直接或间接引用的、包含错误的单元格。要追踪为公式提供数据的单元格（引用单元格），需要先单击包含公式的单元格，然后单击功能区中的【公式】—【公式审核】—【追踪引用单元格】按钮。这样讲显示所有为该公式提供数据的单元格，并使用箭头由这些单元格指向包含公式的单元格。（在追踪错误时，红色箭头表示有错误的单元格，蓝色箭头表示无错误的单元格。）3、使用公式求值器：如果出错的公式很复杂，由多个计算步骤组成，那么查找错误根源时会比较费力。使用Excel的分步计算功能可以将复杂计算过程分解为单步计算，找到错误出在哪里，以便快速解决公式问题。要进行分步计算，需要先单击出错公式所在的单元格，然后单击功能区中的【公式】—【公式审核】—【公式求值】按钮，打开公式求值对话框。在【公式求值】对话框中还有两个按钮——【步入】和【步出】，当要求值的公式包含从属单元格时，【步入】按钮将变得可用。单击该按钮，将显示当前单元格的第一个从属单元格。如果该单元格还有从属单元格，还可以再次单击【步入】按钮，显示间接从属单元格。如果不想显示从属单元格，可以单击【步出】按钮。五、处理公式中的错误1、以#号填充单元格有时输入公式后，单元格会被#号填充。出现这种情况可能有以下原因：A、列宽不足以容纳单元格中的内容。B、单元格包含一个无效的日期或时间。第一个问题很好解决，只需加大列宽即可。第二个问题则需要检查公式中的日期是否在Excel的有效范围之内。Excel不支持1900以前的日期或负的时间值。2、空但非空的单元格有些单元格中看似并无任何内容，但是使用ISBLANK函数或COUNTA函数进行判断或统计时，这些看似空白的单元格仍被计算在内。3、返回错误值下表列出了Excel中的7种错误值，每类错误值都以符号#开头。错误值发生原因#DIV/0!当数字除以0时，出现该错误#NUM!如果公式或函数中使用了无效的数值，出现该错误#VALUE!当在公式或函数中使用的参数或操作数类型错误时，出现该错误#REF!当单元格引用无效时，出现该错误#NULL!如果指定两个并不相交的区域的交点，出现该错误#NAME?当Excel无法识别公式的文本时，出现该错误#N/A当数值对函数或公式不可用时，出现该错误A、#DIV/0!错误及解决方法可能包含以下情况：a、公式除数中包含数字0。b、公式中的除数包含值为0的单元格或空白单元格。解决方法：检查输入的公式中是否包含除数为0的情况；如果除数为一个空白单元格，则Excel会将其当做0来处理。可通过修改该单元格的数据或单元格的引用来解决问题。B、#NUM！错误及解决方法可能包含以下几种情况：a、当公式或函数需要数字参数时，传递给公式或函数的却是非数字参数。b、公式或函数使用了一个无效参数。例如：公式“=SQRT（-4）”c、一个使用迭代算法的函数并且该函数无法得到计算结果。例如：IRR和RATE函数。d、公式的返回值太大或太小，超出Excel限制的范围。Excel对值得限制范围是-1E-307~1E307解决方法：确保函数的参数类型正确；确保输入的参数为有效参数；检查公式的返回值是否超出Excel的限制。C、#VALUE！错误及解决方法可能包含以下情况：a、当公式或函数的参数类型不正确时。例如，公式“=SUM（abc，def）”将返回#VALUE！错误值，因为SUM函数的参数必须是数字。b、当需要使用单个值却使用了区域c、输入或编辑数组公式后未按【Ctrl+Shift+Enter】组合键确认公式的输入。解决方法：确保公式或函数使用的参数类型正确无误；检查公式中的引用范围是否正确；确保在输入数组公式时按下【Ctrl+Shift+Enter】组合键。D、#REF！错误及解决方法可能包含以下几种情况：a、删除了已经被公式引用的单元格。b、复制公式到其他位置时，其中的单元格引用变为无效。c、剪切一个单元格，然后将其粘贴到公式使用的单元格中，公式将返回#REF！错误值。解决方法：撤销已删除的被公式引用的单元格；确保引用的单元格未出现在工作表范围之外。E、#NULL！错误及解决方法使用空格运算符连接两个不相交的单元格区域时，就会返回错误值#NULL！。解决方法：确保两个区域有重叠部分，或改用其他引用运算符连接不同的区域。F、#NAME！错误及解决方法可能包含以下几种情况：a、公式中使用了未定义的名称。b、函数名称或已定义的名称拼写不正确。c、公式中的文本未使用双引号括起、区域引用中未输入冒号分隔、引用其他工作表时未使用单引号括起。d、公式使用了未安装的加载项中的函数解决方法：检查是否使用了工作表或工作薄中不存在的名称，如果是则重新定义该名称；检查公式中是否存在未加双引号的文本，如果有则用双引号括起来；区域引用中为使用冒号“：”分隔，在区域引用中添加冒号。G、#N/A错误及解决方法可能包含以下几种情况：a、手工标记单元格为暂时缺少的数据。例如，在单元格中输入#N/A或公式“=NA（）”。b、省略了函数中必不可少的参数。c、使用LOOKUP、VLOOKUP等函数查找值时，当要查找的值小于查找区域中的第一个值时，将返回#N/A错误值。d、参加运算的数组为同一个方向，且尺寸不同。解决方法：删除手工标记的错误值#N/A；将函数的必选参数输入完整；修改LOOKUP、VLOOKUP等函数的参数，保证可以得到正确查找结果。六、公式使用技巧1、显示公式：【Ctrl+`】（【`】为【ESC】键下方的按键），切换公式与计算结果，或单击【公式】—【公式审核】—【显示公式】按钮，在工作表显示所有公式。2、查看公式的中间结果：在公式栏中选中要查看结果的部分，按【F9】键，将得到选中部分的计算结果。3、将公式转换为值：如果公式不需要再变化，可以将公式转换为数值，防止由于误操作而意外改变公式结果。可以使用下面任一种方法：A、选择公式所在的单元格，然后单击编辑栏，接着选中整个公式后按【F9】键，得到计算结果，最后按【Enter】键。B、选择公式所在单元格，按【Ctrl+C】键复制该单元格。然后；右击该单元格，在弹出的菜单中选择【粘贴选项】中的【值】图标。4、复制公式但不使用相对引用当公式中包含单元格的相对引用时，复制这个公式到其他单元格时，Excel会自动调整其中的相对引用。如果希望按原样复制公式，可以使用下面的方法：在公式的编辑栏直接复制，然后粘贴到目单元格。5、隐藏公式步骤1选择包含要隐藏公式的单元格或单元格区域，右击选区，在弹出的菜单中选择【设置单元格格式】命令。步骤2切换到【保护】选项卡，选中【隐藏】复选框，【确定】。步骤3单击功能区中的【审阅】—【更改】—【保护工作表】按钮，在打开的对话框中输入一个密码，【确定】，并再次输入密码。6、禁止任何人修改公式要对工作表中的所有公式进行包含，最好的方法是使用定位条件快速选择所有包含公式的单元格，然后在对工作表设置保护，具体操作方法如下：步骤1单击工作表左上角的标记选择工作表中的所有单元格。步骤2右击选区并选择【设置单元格格式】命令，然后在【保护】选项卡中取消选中【锁定】复选框，【确定】步骤3按【F5】键，打开【定位】对话框，单击【定位条件】按钮。步骤4选中【公式】单选按钮及其下方的4个复选框，然后单击【确定】按钮。步骤5再次打开【保护】选项卡，重新选中【锁定】复选框。最后按隐藏公式的方法对工作表进行保护。