Excel 公式错误:工作表中显示“#VALUE!”

如果公式所包含的单元格具有不同的数据类型,则Microsoft Excel将显示“#VALUE!”错误。如果启用了错误检查且将鼠标指针定位在错误指示器上,则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常,通过对公式进行较少更改即可修复此问题。

可能的原因和解决方法如下。

1)公式中所含的一个或多个单元格包含文本,并且公式使用标准算术运算符(+、-、*和/)对这些单元格执行数学运算。例如,公式=A1+B1(其中A1包含字符串“happy”,而B1包含数字1314)将返回“#VALUE!”错误。

解决方法:不要使用算术运算符,而是使用函数(例如SUM、PRODUCT或QUOTIENT)对可能包含文本的单元格执行算术运算,并避免在函数中使用算术运算符,而使用逗号来分隔参数。

2)使用了数学函数(例如SUM、PRODUCT或QUOTIENT)的公式包含的参数是文本字符串,而不是数字。例如,公式PRODUCT(3,”happy”)将返回“#VALUE!”错误,因为PRODUCT函数要求使用数字作为参数。

解决方法:确保数学函数(例如SUM、PRODUCT或QUOTIENT)中没有直接使用文本作为参数。如果公式使用了某个函数,而该函数引用的单元格包含文本,则会忽略该单元格且不会显示错误。

3)工作簿使用了数据连接,而该连接不可用。

解决方法:如果工作簿使用了数据连接,执行必要步骤以恢复该数据连接,或者,如果可能,可以考虑导入数据。

Excel 公式错误:工作表中显示“####”

当列不够宽,或者使用了负的日期或负的时间时,工作表会显示出现错误。

可能的原因和解决方法如下。

一、列宽不足以显示包含的内容,其解决方法有两种。

一是增加列宽:其方法是选择该列,单击鼠标右键,在展开的下拉列表中选择“列宽”选项,打开“列宽”对话框,对列宽的值重新进行设置即可。

二是字体填充:其方法是选择该列,右击该列的任意位置,从弹出的菜单中选择“设置单元格格式”命令,在弹出的“设置单元格格式”对话框中选择“对齐”选项卡,在“文本控制”列表框中选中“缩小字体填充”复选框。

二、由于使用了负的日期或负的时间显示出现错误,其解决方法如下:

如果使用1900年日期系统,Microsoft Excel中的日期和时间必须为正值。

如果对日期和时间进行减法运算,应确保建立的公式是正确的。如果公式是正确的,虽然结果是负值,但可以通过将该单元格的格式设置为非日期或时间格式来显示该值。

Excel 公式常见类型

接下来通过举案例说明可以在工作表中输入的公式类型。

  • 输入“=A1+A2+A3”:将单元格A1、A2和A3中的值相加。
  • 输入“=5+2*3”:将5加到2与3的乘积中。
  • 输入“=TODAY()”:返回当前日期。
  • 输入“=UPPER(“hello”)”:使用UPPER工作表函数将文本“hello”转换为“HELLO”。
  • 输入“=SQRT(A1)”:使用SQRT函数返回A1中值的平方根。
  • 输入“=IF(A1>1)”:测试单元格A1,确定它是否包含大于1值。

公式中还可以包含下列部分内容或全部内容:函数、引用、运算符和常量。

  • 常量:直接输入公式中的数字或文本值,例如8。
  • 引用:A3返回单元格A3中的值。
  • 函数:PI()函数返回值PI,3.141592654……
  • 运算符:^(脱字号)运算符表示数字的乘方,而*(星号)运算符表示数字的乘积。

Excel 2019定义不连续单元格区域

不连续的单元格区域也可以定义为名称,其定义方法如下所示。

使用“Shift”键或者“Ctrl”键配合鼠标准确选中要定义为名称的不连续的单元格区域。切换至“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项打开“定义名称”对话框,然后按照前面的操作方法对名称进行定义即可。

名称定义的规则

在定义单元格、数值、公式等名称的时候,需要遵循一定的规则,具体要求如下:

  1. 名称的第1个字符必须是字母、数字或者下划线,其他字符可以是字母、数字、句号或者下划线等符号。
  2. 名称长度不能超过255个字符,字母不区分大小写。
  3. 名称之中不能有空格符。
  4. 名称不能和单元格的名称相同。
  5. 同一工作簿中定义的名称不能相同。

名称定义的概念

在Excel 2019中,使用名称定义可以极大地简化公式,从而提高工作效率。具体来说,Excel中名称定义具有以下重要作用。

1)减少输入的工作量。如果在一个文档中需要输入很多相同的文本,可以使用定义的名称。例如,定义图表=“数据透视表和数据透视图”,那么在需要输入该文本的位置处输入“=图表”,就会显示“数据透视表和数据透视图”。

2)快速定位。例如,在大型数据库中,经常需要选择某些特定的单元格区域进行操作,那么可以事先将这些特定的单元格区域定义为名称。当需要定位时,可以在“名称框”下拉列表中选择名称,程序会自动选择特定的单元格区域。

3)方便计算。简化了编辑公式的时候对单元格区域的引用,尽可能地减少出错概率。

Excel 2019删除模拟运算结果图解

由于模拟运算得到的结果是以数组形式保存在单元格中的,因此无法更改或删除模拟运算结果中某个单元格的值。

如果用户想删除模拟运算结果,则可以执行以下操作步骤。

选中显示模拟运算结果的所有单元格区域,按“Delete”键即可删除。也可以在选中的区域处单击鼠标右键,在弹出的隐藏菜单中选择“清除内容”选项即可。

Excel 2019恢复排序前表格

当用户反复地对表格进行各种排序以后,表格的原有次序已经被打乱。如果在排序后做了一些必要的编辑或修改操作,就不方便再使用Excel的撤销功能。这时,如果需要让表格恢复排序前的状态,就存在一定的难度了。

如果用户在排序前就打算保持表格在排序前的状态,则在表格的左侧或右侧插入一列空白列,并填充一组连续的数字,例如1,2,3……设置完成后,无论用户对表格进行怎样的排序,只要最后以插入的空白列为标准做一次升序排序,就能够返回表格排序前的次序。

Excel 2019数值排序的3个函数

(1)RANK函数

RANK函数是Excel计算序数的主要工具。它的语法为:RANK(number,ref,order),其中参数number为参与计算的数字或含有数字的单元格,参数ref是对参与计算的数字单元格区域的绝对引用,参数order是用来说明排序方式的数字(如果order为零或省略,则以降序方式给出结果,反之按升序方式排序)。

(2)COUNTIF函数

COUNTIF函数可以统计某一区域中符合条件的单元格数目。它的语法为COUNTIF(range,criteria),其中参数range为参与统计的单元格区域;参数criteria是以数字、表达式或文本形式定义的条件,数字可以直接写入,表达式和文本必须加引号。

(3)IF函数

Excel自身带有排序功能,可使数据以降序或升序方式重新排列。如果将它与IF函数结合,可以计算出没有空缺的排名。根据排序需要,单击Excel工具栏中的“降序排序”或“升序排序”按钮,即可使工作表中的所有数据按要求重新排列。

Excel各版本历史介绍(至2019版)

下面介绍一下Excel各个版本的发展历史,帮助读者对Excel软件有个总体的认识。

1982年

Microsoft推出了它的第一款电子制表软件-Multiplan,并在CP/M系统上大获成功,但在MS-DOS系统上,Multiplan败给了Lotus1-2-3(一款较早的电子表格软件)。这个事件促使了Excel的诞生,正如Excel研发代号DougKlunder:做Lotus1-2-3能做的,并且做得更好。

1983年9月

微软最高的软件专家在西雅图的红狮宾馆召开了3天的“头脑风暴会议”。比尔·盖茨宣布此次会议的宗旨就是尽快推出世界上最高速的电子表格软件。

1985年

第一款Excel诞生,它只用于Mac系统,中文译名为“超越”。

1987年

第一款适用于Windows系统的Excel也产生了(与Windows环境直接捆绑,在Mac中的版本号为2.0)。Lotus1-2-3迟迟不能适用于Windows系统,到了1988年,Excel的销量超过了Lotus1-2-3,使得Microsoft站在了PC软件商的领先位置。这次的事件,促成了软件王国霸主的更替,Microsoft巩固了它强有力的竞争者地位,并从中找到了发展图形软件的方向。

此后大约每两年,Microsoft就会推出新的版本来扩大自身的优势。

早期,由于和另一家公司出售的名为Excel的软件同名,Excel曾成为商标法的目标,经过审判,Microsoft被要求在它的正式文件和法律文档中以Microsoft Excel来命名这个软件。但是,随着时间的过去,这个惯例也就逐渐消逝了。Excel虽然提供了大量的用户界面特性,但它仍然保留了第一款电子制表软件VisiCalc的特性。

Excel是第一款允许用户自定义界面的电子制表软件(包括字体、文字属性和单元格格式)。它还引进了“智能重算”的功能,当单元格数据变动时,只有与之相关的数据才会更新,而原先的制表软件只能重算全部数据或者等待下一个指令。同时,Excel还有强大的图形功能。

1993年

Excel第一次被捆绑进Microsoft Office中时,Microsoft就对Microsoft Word和Microsoft Powerpoint的界面进行了重新设计,以适应这款当时极为流行的应用程序。

从1993年起,Excel就开始支持Visual Basic for Applications(VBA)。VBA是一款功能强大的工具,它使Excel形成了独立的编程环境。使用VBA和宏,可以把手工步骤自动化,VBA也允许创建窗体来获得用户输入的信息。但是,VBA的自动化功能也导致Excel成为宏病毒的攻击目标。

1995年

Excel被设计为用户所需要的工具。无论用户是做一个简单的摘要、制作销售趋势图,还是执行高级分析,无论正在做什么工作,Microsoft Excel能按照用户希望的方式帮助用户完成工作。

1997年

Excel 97是Office 97中一个重要程序,Excel一经问世,就被认为是当时功能强大、使用方便的电子表格软件。它可完成表格输入、统计、分析等多项工作,可生成精美直观的表格、图表,为日常生活中处理各式各样的表格提供了良好的工具。此外,因为Excel和Word同属于Office套件,所以它们在窗口组成、格式设定、编辑操作等方面有很多相似之处,因此,在学习Excel时要注意应用以前Word中已学过的知识。

2001年

利用Office XP中的电子表格程序Microsoft Excel 2002版,可以快速创建、分析和共享重要的数据。智能标记和任务窗格的新功能简化了常见的任务;协作方面的增强则进一步精简了信息审阅过程;新增的数据恢复功能确保用户不会丢失自己的劳动成果;可刷新查询功能使用户可以集成来自Web及任意其他数据源的活动数据。

2003年

Excel 2003使用户能够通过功能强大的工具将杂乱的数据组织成有用的Excel信息,然后分析、交流和共享所得到的结果。能帮助用户在团队中工作的更为出色,并能保护和控制对用户工作的访问。另外,还可以使用符合行业标准的扩展标记语言(XML)更方便地连接到业务程序。

2007年

在Excel 2003中显示活动单元格的内容时编辑栏常会越位,挡住列标和工作表的内容,特别是在编辑栏下面的单元格有一个很长的公式,此时单元格内容根本看不见,也无法双击、拖动填充柄。而在Excel 2007中的编辑栏上下箭头(如果调整编辑栏高度,则出现滚动条)和折叠编辑栏按钮完全解决此问题,不再占用编辑栏下方的空间。调整编辑栏的高度有两种方式——拖曳编辑栏底部的调整条,或双击调整条。调整编辑栏的高度时,表格也随之下移,因此表里的内容不会再被覆盖。同时为这些操作添加了“Ctrl+Shift+U”组合键,以便在编辑栏的单行和多行模式间快速切换。

Excel 2003中名称地址框是固定的,不够用来显示长名称。而Excel 2007则可以左右活动,有水平方向调整名称框的功能。用户可以通过左右拖曳名称框的分隔符(下凹圆点),来调整宽度,使其能够适应长名称。

Excel 2003编辑框内的公式限制还是让人感到不便,而Excel 2007有几个改进:

  1. 公式长度限制(字符),2003版限制:1K个字符,2007版限制:8K个字符。
  2. 公式嵌套的层数限制,2003版限制:7层,2007版限制:64层。
  3. 公式中参数的个数限制:2003版限制:30个,2007版限制:255个。

2015年

Excel 2016预览版发布,相比以前的Excel,Excel 2016经历了一次幅度很大的调整,并获得了贴靠和智能滚动等新功能。它的界面对于触控操作非常友好。用户可以通过界面当中的状态栏来在工作簿当中切换表单,并浏览选定单元格的常见公式结果。

在最新版本的Excel 2016中,默认增加了Power Query功能。此功能原来需要以插件形式单独下载,然后安装到2010或2016版本中才能用。

2018年

这是一个只能运行在Windows 10 操作系统上的版本,这就意味着市长占有率极大的win7用户不能使用。Excel 2019是Office 2019的重要组成部分,与以前版本相比,Excel 2019的功能更加强大,操作更加灵活。Excel 2019继承了Excel 2016以功能区为操作主体的操作风格,更加便于用户操作。本章将介绍Excel 2019新增的功能、Excel 2019的启动与退出、Excel2019的工作环境、文件转换与兼容性,以及如何自助学习Excel 2019。