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

跟我一起，穿越时间!

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

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

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

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

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

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

一、Excel中的公式运算规则

1、运算符号和运算规则

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

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

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

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

各运算符号和说明如下（优先级由高到低）：

2、运算实例

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

（1）引用运算问题

公式=C1:C3 A2:D2

返回结果22

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

（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"""这种写法。

（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函数，“并且”和“或者”的意思，他们也很好理解。

但是进一步：

=(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

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

解析：=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中，逻辑型数据>文本型汉字>文本型字母>文本型数字>文本型空格>数值型数据

为什么呢？

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

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

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

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

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

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

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

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

我们都知道，计算机中以二进制来存储数据，也就是说，无论是文字、图片、视频还是其他类型的数据，他们在计算机中都是一串一串的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个小写英文字母，其余为一些标点符号、运算符号等。

以上是标准的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函数。

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

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

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

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

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

另外，按住键盘的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忽略了大小写）

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

例如："AB"<"AC"

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

"A"<"AA"

"AA">"A"

"A"<"AB"

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

""<"A"

""<"0"

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

"">9

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

有了这些，下面这些就不难看懂：

但是最后两行中，

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

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

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

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

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

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

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

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

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

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

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

更多精彩，敬请关注。

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

,