哈啰,大家好
在今天的影片我们要来聊聊
以下这几个主题
首先,我们来看「布林逻辑」
所谓「布林」 指的是一种资料的类型
它不同于数字、文字或日期
而单单只有两种变化
也就是 TRUE (是) 和 FALSE (否)
布林常常应用在公式中逻辑的判断
同时也具有简化公式的作用
举例来说
如果今天我想要判断这家商店的消费者
他们的消费金额是否达到 1000 元
有些人可能会直接联想到 IF 这个公式
的确,我们可以在输入 IF 之后
判断总金额是否大于或等于 1000
如果是的话就显示 TRUE
否的话就回传 FALSE
如此我们就可以得知
这些会员中有两位消费达到了一千元
不过刚才这个 IF 公式
我们也可以使用布林逻辑来加以简化
我们只要请 EXCEL 直接判断
E3 栏位是否大于或等于 1000
就可以使这个栏位得到相同的判断结果
两相比较之下
使用布林逻辑可以使公式显得精简许多
布林逻辑除了可以针对数值
进行大、小的比较之外
它也可以针对「文字」类型的资料来进行核对
譬如今天我想要判断名单中的会员
是否有缴年费
那么我可以先点击 C3 栏位之后
请 Excel 判断它的内容是否为「有」
符合的话布林逻辑同样会回传 TRUE 这个结果
反之则会显示 FALSE
另外,在布林逻辑中
还有一个叫做「不等于」的符号
如果应用在刚才的公式里的话
则会产生与「等于」相反的结果
不过,一般在进行逻辑判断时
我们往往都会遇到一个以上的条件
因此这时我们就得搬出 Excel 中
使用频率非常高的两个函数
也就是 AND 和 OR
我们知道在中文里
AND 代表「且」
而 OR 代表「或」
使用时这两个函数在语法结构上
是完全相同的
以 AND 来说
如果我们丢了三个条件请它进行判断
那么 AND 只有在所有条件
都为 TRUE 的情形之下
它才会回传 TRUE 这个答案
而这些条件之中只要有一个为「否」
那么 AND 就会显示 FALSE 的结果
我们不妨来看一个实际的范例
假设今天我们打算将「消费次数满 5 次以上」
而且「消费金额达到 $1000」的会员
自动升等为 VIP
由于这两个条件必须「同时成立」
才符合 VIP 的资格
因此我们可以藉由 AND 函数
依序将这两个条件输入到公式内
再将公式向下复制
如此只要 Excel 侦测到这两个条件
都为 TRUE 的情形下
就会立即回传 TRUE 的结果
相对地,OR 函数的判定标准
比起 AND 来说比较宽松一些
如果我们在括号里放了三个条件
那么只要有一个条件过关
OR 函数就会显示 TRUE
只有在全部条件都阵亡的情形之下
OR 函数才会产生 FALSE 的结果
OK,我们同样来看一个例子
如果今天我们稍微修改一下
升等 VIP 的条件
只要会员的消费金额达到 $1000
「或着」他有缴交年费就可以升等的话
那么我们就可以使用 OR 函数
同样将这两个条件依序输入到公式内
如此 Excel 就可以立即判断出
有那些会员具有升等的资格喔
当然,AND 和 OR 函数
也可以写在同一条公式内
以应付较为复杂的逻辑判断
举例来说
如果我们把刚才的 VIP 升等
整合为以下这两个条件
那么在建立公式时
我们可以先利用 OR 函数
来判断会员是否有缴年费
接着,再利用 AND 函数
来将第二个条件的内容依序输入到公式内
如此就可以顺利地找出可以升等的会员啰
不过,为了让表格读起来更为简单明了
我们通常不会将公式的结果
以英文的 TRUE 和 FALSE 来呈现
而是改用中文来进行表达
因此,这里我们可以借助 IF 函数
来将刚才的公式放在函数的第一栏
并且将要显示的中文讯息
输入到函数的后面
在实际操作时
我们可以在公式的前面加上 IF 的函数名称
然后再到后面输入一个逗号
并且依序输入「可升等 VIP」
和「条件不符」这两则讯息
如此公式的判断结果就显得更平易近人啰
OK,布林逻辑也可以应用在
Excel 另一个很知名的功能
「格式化的条件」之内
举个例子
如果今天我想要把
可以升等的会员利用颜色标注出来
那么我们同样可以先反白刚才的逻辑公式
按下 Ctrl +C 执行复制
然后在工作表内框选会员的资料表格
接着,我到功能区内
按下「设定格式化的条件」
选择「新增规则」
类型设为「使用公式」
再到底下的栏位按下 Ctrl +V 执行贴上
前面则补上一个 = 符号
为了使公式在复制到其它位置时
储存格仍能锁定在同一个栏位
因此我在参照位置的英文前方
加上一个货币符号来固定这些栏位
然后按下「格式」按钮
在对话框中我们可以设定
想要套用的字型外观
框线
或是储存格的填色
再按下「确定」来关闭这些对话框
如此只要符合升等 VIP 的会员
Excel 就会使用我们所指定的色彩
来进行标注喔
OK,当我们谈到布林逻辑时
就不能不提到另一个 Excel 鼎鼎有名的函数
SUMPRODUCT
这个函数在字面上是由两个单字所组成
SUM 代表了「加总」
而 PRODUCT 是「乘积」的意思
因此这个函数最基本的用途
就是拿两个对应的栏位相乘之后
再将乘积加总起来
其中最常见的一个例子
就是商品总金额的计算
如果我想要了解
所有的产品总共卖了多少钱
一般我们会建立第三个栏位
来将「数量」乘上「单价」
再将这个栏位里的金额进行加总
不过如果使用 SUMPRODUCT 这个函数的话
我们只要先输入函数的名称
然后依序框选「数量」
和「单价」这两个栏位
SUMPRODUCT 就可以立即计算出
两个栏位相乘后的加总结果
成绩的计算
是 SUMPRODUCT 另一个常见的应用
譬如今天不同的科目有各自的加权倍数
那么进行加权计分时
我们同样可以使用 SUMPRODUCT 函数
来将「分数」和「权重」相乘
再按下 Enter 键来求出加权后的总分
如果要计算「加权平均」的话
我们只要将这个总分
除以「权重」倍数的总和就 OK 啰
接下来的这个表格
也是一个很适合使用 SUMPRODUCT 的例子
如果我要计算出每一个班级
花费在文具上的总金额
我只要在第一个班级的栏位
输入 SUMPRODUCT 的函数名称
然后依序框选文具的「单价」
以及第一个班级的订购数量
至于文具的的单价栏
我们同样必须按一下键盘的 F4
把它锁定起来
如此我们就可以很轻松地算出
每个班级所要缴交的金额
看了几个 SUMPRODUCT 的简单应用之后
我们可能会想 SUMPRODUCT
跟布林逻辑又为什么会扯上关系呢?
这个就必须要提到
布林逻辑在使用上还有一个特性
就是当我们利用它来进行运算时
TRUE 和 FALSE 分别代表了数字的 1 和 0
我们可以利用算式来做一个简单的验证
譬如我如果拿 TRUE 乘上 1 的话
那么计算的结果依然会等于 1
相对地
如果拿 FALSE 乘上 1 的话
0 乘上 1 的结果就依然会是 0
OK,那么 TRUE 等于 1
FALSE 等于 0 的概念
要如何应用在公式里呢?
我们来看看先前会员消费的例子
假设今天我想要查询已缴年费的会员
一共有几位的话
那么我们可以输入 SUMPRODUCT 函数之后
请它判断表格中的 C 栏是否等于「有」
此时判断的结果
会产生一排的 TRUE 和 FALSE
而为了将这些 TRUE 和 FALSE
转换成可用来运算的 1 和 0
我们可以在公式内将这个判断式乘以 1
或着有些人会在括号前加上两个减号
再按下 Enter 键
如此 SUMPRODUCT
就会将这些 TRUE 和 FALSE 进行加总
就可以算出有缴年费的会员人数啰
OK,那如果今天
我们想要进一步查询有缴年费
而且住在「东区」的会员一共有几位的话
公式又该如何设定呢?
这一次我把查询的条件写在底下的栏位
然后同样输入 SUMPRODUCT 的函数名称
判断年费栏是否为「有」
接着输入一个「乘号」
再请 SUMPRODUCT 判断会员的居住地
是否为「东区」
就可以立即计算出来
这个公式的原理同样是基于布林逻辑的概念
Excel 会将「有缴年费」
和居住在「东区」的栏位标记为 TRUE
其余的栏位则会产生 FALSE
由于我们这两个判断式之间
输入了一个乘号
所以相乘时只有 TRUE 乘上 TRUE
也就是两个条件都满足时才会等于 1
其余带有 FALSE 的栏位
在相乘之后只会得到 0
而不会纳入计数的结果之内
因此藉由这个方式
SUMPRODUCT 把这些结果加总起来
我们就可以得知符合「有缴年费」
和「住在东区」有几个人啰
最后,如果我们继续将问题扩大为
有缴年费,而且住在东区的会员
总共消费了多少钱的话
我们只要在这个公式的后面
同样输入一个乘号
然后框选「总金额」的栏位就可以了
而它的原理一样是透过
TRUE 和 FALSE 的相乘来进行筛选
如此只有那些有缴年费
住在东区的会员才能显示正常的消费金额
其余会员的金额栏都只会显示 0
最后再透过 SUMPRODUCT 函数
来进行加总就可以计算出
这些会员的消费金额
当然,这里我们也可以随时更改筛选的条件
SUMPRODUCT 也会根据
我们所输入的资料立即更新计算的结果喔
OK,那今天关于布林逻辑
和 SUMPRODUCT 函数我们就聊到这里,拜拜