跟我一起,穿越时间!

回顾一下,在前面3期的连载里,我们已经讲过了Excel中的各种数据类型和转换技巧,对Excel能够处理的数据有了比较全面的认识,没有看过的建议先点击头像或通过文末链接进行阅读。

穿越时间•Excel升级之路连载1:Office2010安装体验及个性设置

穿越时间•Excel升级之路连载2:打牢Excel 2010中的数据类型基础

穿越时间•Excel升级之路连载3:数据类型检测与相互转换技巧

前几期的内容就像准备好了做菜时所需的各种原材料,但是想要做出一道美味佳肴,仅有原料是远远不够的,要想提高对公式的理解,我们不仅要清楚各类数据,更要清楚数据和数据之间是如何计算、如何发生关系的,今天的内容就像做菜时不可缺少的各种调味品。

让我们走到Excel升级之路连载4:精通公式运算规则和字符比较

excel公式笔记(穿越时间Excel升级之路连载4)(1)

一、Excel中的公式运算规则

1、运算符号和运算规则

Excel中的公式都是以等号 = 开头的,然后由数据、函数或运算符构成。

程序按照每个运算符的优先次序从左到右计算公式。普通的加减乘除自然无需多说,但是由于数据类型、逻辑判断、括号调序、引用嵌套的存在,如果你对Excel中公式运算符的含义和优先级不清楚,那自然是难以读懂复杂公式的。

俗话说“学好数理化,走遍天下都不怕”,学好Excel倒不太涉及物理和化学,只需要坚持基本的数学规则。

Excel公式的整体原则也是先计算高优先级的运算,然后计算低优先级的运算,遇到同优先级则自左向右依次计算,除了数学中常见的括号、指数、乘除法、加减法,Excel中还有引用运算、文本连接、逻辑运算等。

各运算符号和说明如下(优先级由高到低):

excel公式笔记(穿越时间Excel升级之路连载4)(2)

2、运算实例

下面通过一些实例,看看你是否能够准确写出这些公式的结果?

excel公式笔记(穿越时间Excel升级之路连载4)(3)

(1)引用运算问题

公式=C1:C3 A2:D2

返回结果22

上面的公式中使用了一个空格,即引用运算符中的单个空格,它的意思是什么?我们可以认为这是在求交集,即求出C1:C3和A2:D3共同的元素,答案为22

excel公式笔记(穿越时间Excel升级之路连载4)(4)

(2)算术运算问题

公式=A1^0 返回结果#NUM!

公式=-A2^0 返回结果1

公式=-(A2^0) 返回结果-1

一个是1,一个是-1,这个简洁的例子能很好的说明运算顺序的问题。

A1^0是求0的0次幂,结果为#NUM! 因为0的0次幂没有意义,函数或公式包含无效值,所以返回#NUM!错误

-A2^0是求负A2的零次幂,求负数的优先级比求幂的优先级高,所以先算-A2的结果为-20,然后再算-20的0次幂,结果为1

-(A2^0)是求A2的零次幂的负数,通过括号将求幂的优先级提高到了求负数之前,结果为-1

(3)文本连接运算问题

&符号是用来连接两个文本字符串,我们可以考虑以下几行公式:

=D1&Excel

=D1&"Excel"

=D1&"“Excel”"

=D1&"""Excel"""

其返回结果会是什么,这能说明一个什么问题?

&后必须跟上文本字符型数据,而Excel中文本字符型数据需要英文引号" "来引用,如果直接使用=D1&Excel则会产生#NAME?错误;

如果需要在结果中呈现出中文引号,那么直接把中文引号放到英文引号" "中引用即可;

如果需要在结果中呈现出英文引号,这时就需要用两个英文引号来引用,出现=D1&"""Excel"""这种写法。

excel公式笔记(穿越时间Excel升级之路连载4)(5)

(4)比较运算问题

=C1>B1 结果为TRUE

=C1=B1 结果为FALSE,这是最简单的比较问题;

=AND(C1>B1,B1>A1) 结果为TRUE,770大于600,同时600大于0的问题;

=OR(C1>B1,A1>B1) 结果为TRUE,770大于600,或者0大于600的问题;

以上的and函数和or函数,“并且”和“或者”的意思,他们也很好理解。

excel公式笔记(穿越时间Excel升级之路连载4)(6)

但是进一步:

=(C1>B1)*(B1>A1) 意思是什么?运算的结果是什么?

=(C1>B1) (A1>B1) 意思是什么?运算的结果是什么?

有人说=(C1>B1)*(B1>A1)之中的*等价于AND函数,并且的意思,也就是计算是不是满足770大于600,同时600大于0;=(C1>B1) (A1>B1)中的 等价于OR函数。

那我们想问,为什么他们要把*等价于AND? 等价于OR?

不好理解吧!

可以换一种思路,

=(B1>B2)*(A2>B2)只是包含括号、乘号、大于号运算符的普通公式,完全遵循上述Excel运算顺序,那么原式就变为先计算优先级最高的括号内(B1>B2)得到逻辑值TRUE,再计算括号内(A2>B2)得到逻辑值TRUE,然后TRUE*TRUE=1,这里的1就是数值型的数字1

(涉及逻辑值本身的运算,多个逻辑型数据本身可以直接执行运算,结果为数值型,如果不理解,请看我前面的连载3文章)

同理,=(C1>B1) (A1>B1)的结果为数值型的1


Excel中的比较运算符号和编程语言中的比较运算符号是有区别的,诸如!=,==,&&等都是不受支持的。

我们来看一个终极公式,=D3>C3&C3>B3,Excel会返回运算结果TRUE

你是否清楚地知道运算过程?

excel公式笔记(穿越时间Excel升级之路连载4)(7)

解析:=D3>C3&C3>B3中同时存在&和>两种运算符,但&的运算优先级高。

所以原式=43>42&42>41

=43>"4242">41

=FALSE>41

=TRUE

为什么42&42成了"4242",(&连接后使数值型数据变成了文本型数据,不理解请看连载3)

为什么43>"4242"的结果是FALSE?(数字不比字符大)

为什么FALSE>41的结果又是TRUE?(逻辑值比数字大)

带着后面两个疑问,我们可以进入到下一部分,Excel中的字符比较。

二、Excel中的字符比较

其实字符比较的内容就是Excel比较运算符的延伸和应用,其灵活性极高,但是当我们熟悉其规则和原理之后,也可以轻松应对。

类似于程序设计语言中的基础知识,在Excel中,逻辑型数据>文本型汉字>文本型字母>文本型数字>文本型空格>数值型数据

excel公式笔记(穿越时间Excel升级之路连载4)(8)

为什么呢?

这里涉及到了字符型数据的比较方法、ASCII码。

我们首先要讲一下ASCII码,而要讲ASCII码,我们又需要先讲讲进制。

1、进制与Excel中的进制转换

进制就是进位计数制,它是一种计数方法,几进制就是逢几进一。

常见的有十六进制、十进制、八进制、二进制

进制有不同的表示方法,假如有一个十进制的数字5,我们可以用5(10)、(5)10、(5)O等方式表示,O代表的就是十进制Octal的缩写。

excel公式笔记(穿越时间Excel升级之路连载4)(9)

在Windows系统中,通过科学计算器可以非常快捷准确地实现进制的转换。

excel公式笔记(穿越时间Excel升级之路连载4)(10)

当然,我们没有必要用计算器来辅助Excel,Excel中内置的函数就可以进行不同进制数字间的转换了,这里共涉及到12个函数:

excel公式笔记(穿越时间Excel升级之路连载4)(11)

我们都知道,计算机中以二进制来存储数据,也就是说,无论是文字、图片、视频还是其他类型的数据,他们在计算机中都是一串一串的0、1字符,但0和1对用户来说难以识别,所以在人们输入计算机信息、计算机处理信息、计算机返回处理结果之间存在着必然的转换过程,这就是ASCII编码转换的产生基础。

2、ASCII码

ASCII,全称American Standard CODE for Information Interchange,美国标准信息交换代码,ASCII码是在上个世纪伴随全球互联应需而生并快速发展起来的,主要解决的就是各国字母、符号和计算机中的二进制数字的对应问题。

标准的ASCII码共包含128个字符(序号0~127),每个字符1字节,具体为33个控制字符和95个可打印字符。

0~31、127为控制字符或通信专用字符,包括控制符:LF(换行)、CR(回车)、FF(换页)、DEL(删除)、BS(退格)、BEL(响铃)等;通信专用字符:SOH(文头)、EOT(文尾)、ACK(确认)等。它们不可打印,没有特定的图形显示,但会依不同的应用程序,而对字符显示有不同的影响。

32~126:共95个,是可打印字符,其中32是空格,48~57为0到9十个阿拉伯数字,65~90为26个大写英文字母,97~122号为26个小写英文字母,其余为一些标点符号、运算符号等。

excel公式笔记(穿越时间Excel升级之路连载4)(12)

以上是标准的128个字符,每个字符占用1字节(1Byte)。

为什么是128个呢?为什么每个字符占用1字节(1Byte)?

要知道字节Byte并不是最基础的存储单元,位bit才是,只是由于历史上IBM System/360 8位EBCDIC编码的成功,奠定了计算机领域1字节等于8位(1Byte=8bit),1个字符用1个字节表示的基础。

8个二进制位可以表示28=256种状态,而英语中的字母和各种符合用128种状态就可以全部包含了。由于单个字符所占用的是1字节,转换为二进制代码后只需7位,首位以0补齐。但为了最大化利用256中状态,后来IBM等扩展了标准的ASCII码至256个,128-255是用来表示框线、音标和其它欧洲非英语系的字母。

将目光再放眼全球,法语、俄语、中文中又会涉及更多的基本符号,数量之大使得256种状态亦无法涵盖,于是Unicode,以及具体实现Unicode的UTF-8编码出现了。这里我们不具体讨论具体的编码规则,聚焦到Excel中的字符比较,我们需要了解的只是ASCII码中的内容。

3、Excel中数据的比较规则

Excel中,数据的比较总是遵循一定的规律的,其基础是ASCII码值的大小。

我们先来认识两个函数,查询代码值的code函数和根据代码值显示数据的CHAR函数。

excel公式笔记(穿越时间Excel升级之路连载4)(13)

例如查询文本型字符A的code值,可以通过公式=CODE(A1),我们可以看到返回65,正好是上面表格中对应的数字。

excel公式笔记(穿越时间Excel升级之路连载4)(14)

当然,如果你记住了字符的代码值,通过char函数可以直接在单元格中输入对应字符,这里我们两个例子,一个是ASCII码范围内的,一个是ASCII码范围外的。

例如已知?的代码是63,通过公式=CHAR(63)即可得到?

excel公式笔记(穿越时间Excel升级之路连载4)(15)

已知对号√的代码是41420(不在标准ASCII码之内),通过=CHAR(41420)即可快速得到√

excel公式笔记(穿越时间Excel升级之路连载4)(16)

同理叉号×的代码是41409,五角星★的代码是41455,黑方块■的代码是41462,各种箭头的代码是41466-41469等

excel公式笔记(穿越时间Excel升级之路连载4)(17)

另外,按住键盘的ALT键,然后通过小键盘输入字符的ASCII码值,松开ALT键后也可以在单元格中输入相应的字符。


好了,最后回到Excel中数据比较的细节:

(1)逻辑型数据>文本字符型数据>数值型数据

(2)逻辑型数据中TRUE>FALSE,

(3)文本字符型数据中汉字>字母>文本型数字>文本型空格,比较过程中Excel不区分大写字母和小写字母

(4)数值型数据中9>8>……>1>0

文本字符型数据比较时,从最左边第一个字符开始比较,ASCII码值大的字符串大,ASCII码值小的字符串小。

例如:"0"<"9" "A"<"B" "A"<"z" (符合ASCII码值的大小)

但是"Z">"a"(不符合ASCII码值的大小,因为Excel忽略了大小写)

excel公式笔记(穿越时间Excel升级之路连载4)(18)

如果第一个字符相同,则继续向右比较第二个字符,ASCII码值大的字符串大,ASCII码值小的字符串小;

例如:"AB"<"AC"

如果比较几个字符后第一个字符串结束了,那么第二个没结束的字符串大;

"A"<"AA"

"AA">"A"

"A"<"AB"

文本字符串空格也可以参与比较,因为其ASCII码值为32,所以一个空格比任意一个文本型的字母或数字小。

""<"A"

""<"0"

这里注意一个文本型空格比任意的数值型数字都要大。

"">9

最后,涉及汉字进行比较时,其实是在比较构成拼音的字符串的大小,这个涉及多音发音,不展开。

有了这些,下面这些就不难看懂:

excel公式笔记(穿越时间Excel升级之路连载4)(19)

但是最后两行中,

Excel为什么不能按照ASCII码值比较出"A" 和"a"的大小呢?

为什么只有="A"="a"会返回TRUE?

为什么Excel会认为"A" 和"a"一样大?

如果你仔细看了前面内容,相信心中应该会有答案的。

原因就是Excel忽略了字母大小写。

不要认为这是一个小问题,在运用函数和公式时,如果不能返回正确的计算结果,可能就是大小写的原因。

最后,Excel中有一个函数exact可以用来精确判断两个字符串是否完全相同,这里就会区分大小写了。

excel公式笔记(穿越时间Excel升级之路连载4)(20)

好了,以上就是连载4的全部内容,难度逐渐提升了,没有看过前面连载的可以点击头像或链接跳转,:

穿越时间•Excel升级之路连载1:Office2010安装体验及个性设置

穿越时间•Excel升级之路连载2:打牢Excel 2010中的数据类型基础

穿越时间•Excel升级之路连载3:数据类型检测与相互转换技巧

更多精彩,敬请关注。

(原创连载,个人观点保留,禁止任何未经授权的非本人账号复制文章到其他平台发布)

,