Excel – 销售奖金二维表查询，累计求和的区域自动变色

来源：餐饮加盟

作者：小吃加盟· 发布时间

2025-10-13

核心提示：天的案例需求听上去有点复杂，但是通过巧妙设置，在演示汇报的时候领导一眼就能明白公式所要表达的意图，反而简化了工作沟通。而

天的案例需求听上去有点复杂，但是通过巧妙设置，在演示汇报的时候领导一眼就能明白公式所要表达的意图，反而简化了工作沟通。

而所需的设置，无非是老瓶装新酒，只要稍加点拨，我的老读者基本都能够独立完成。

案例：

下图 1 为某公司销售人员去年每个季度的奖金统计表。要求根据姓名和季度进行查询：

对指定销售人员的 Q1 至查询季度的所有奖金求和；

上述查询区域自动高亮显示

效果如下图 2、3 所示。

< class="pgc-img">

解决方案：

先制作“姓名”和“截止季度”下拉菜单。

1. 选中 G2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

< class="pgc-img">

2. 在弹出的对话框中点击“设置”选项卡 --> 按以下方式设置 --> 点击“确定”：

允许：序列

来源：选择 A2:A13 区域

< class="pgc-img">

3. 选中 H2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

< class="pgc-img">

4. 在弹出的对话框中点击“设置”选项卡 --> 按以下方式设置 --> 点击“确定”：

允许：序列

来源：选择 B1:E1 区域

< class="pgc-img">

接下来设置求和公式。

5. 在 I2 单元格中输入以下公式：

=SUM(INDEX($B:$B,MATCH(G2,$A:$A,0)):INDEX($B:$E,MATCH(G2,$A:$A,0),MATCH(H2,$B:$E,0)))

公式释义：

INDEX($B:$B,MATCH(G2,$A:$A,0))：

如果 $A:$A 区域中的值与 G2 单元格的内容完全匹配，则定位到 $B:$B 区域中的同等行；

这段公式的目的是根据姓名查找到对应的 Q1 列的值，即求和的起始单元格

INDEX($B:$E,MATCH(G2,$A:$A,0),MATCH(H2,$B:$E,0))：

根据“姓名”和“截止季度”两个条件，定位到查询区域内的交叉单元格；

也就是求和区域的截止单元格

SUM(...:...)：对起始和截止单元格之间的所有区域求和

* 除了 match 函数中的第一个参数以外，公式中的所有参数都必须绝对引用。

< class="pgc-img">

最后设置查询区域自动高亮显示。

6. 选中 B2:E13 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

< class="pgc-img">

7. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮：

=AND($A2=$G,COLUMN(A1)<=MATCH($H,$B:$E,0))

公式释义：

$A2=$G：

姓名列的值等于 G2 单元格中的姓名

通过该公式定位到行

COLUMN(A1)<=MATCH($H,$B:$E,0)：

列值为选中区域内的第一列至 H2 单元格相同值所在的列

AND(...)：同时满足上述行、列值要求的区域将被高亮显示

< class="pgc-img">

8. 在弹出的对话框中选择“填充”按钮 --> 选择所需的填充色 --> 点击“确定”

< class="pgc-img">

9. 点击“确定”

< class="pgc-img">

现在就已经全部设置完成。从 G、H 列的下拉菜单中选择不同的值，数据表中该员工从 Q1 至查询季度的区域会自动高亮显示，I 列则自动对高亮区域求和。

< class="pgc-img">

很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。

< class="pgc-img">

遇酸变红，遇碱变蓝”，这是一句几乎每一个理科生都熟记于心的化学顺口溜，指的就是石蕊试纸的特性。

石蕊试纸能够用于检测酸碱性，蓝色的石蕊试纸遇到酸性物质就会变成红色，红色的石蕊试纸遇到碱性物质则会变成蓝色。

但是你曾经是否有过疑问：它为什么叫做“石蕊试纸”呢？“石蕊”又是什么？为什么会变色？

< class="pgc-img">

其实“石蕊”，在生物学层面，是一类石蕊科地衣的名称，在化学层面，指的是一种从地衣中提取的混合物，因最初从石蕊科地衣中提取到而命名为“石蕊”。

< class="pgc-img">

生物学中，物种以域、界、门、纲、目、科、属、种这几个层级来分类。石蕊就是地衣大家庭中的一员，指的是石蕊科（Cladoniaceae）地衣。

有的石蕊科地衣呈直立丛生分枝状，顶端有一个个形似果实的子囊盘，比如顶端是鲜艳的红色的红头石蕊（Cladonia floerkeana）和顶端是黑色的大柱衣（Pilophorus acicularis），这两种石蕊科地衣也都因其十分像立在地上的一根根火柴，被形象地称为“魔鬼的火柴”。

< class="pgc-img">

左：红头石蕊 Cladonia floerkeana 右：大柱衣 Pilophorus acicularis

也有些石蕊形似一丛丛迷你灌木，比如鹿石蕊（Cladonia rangiferina），也叫做驯鹿地衣。

< class="pgc-img">

鹿石蕊 Cladonia rangiferina

还有的石蕊形状颇为奇特，整体呈圆柱状，顶端却膨大为喇叭状，像是精灵森林里的神奇植物，比如喇叭石蕊（Cladonia pyxidata）。

< class="pgc-img">

喇叭石蕊 Cladonia pyxidata

这些石蕊科地衣，除了外形奇特可爱，在化学领域也有重要作用。从石蕊科地衣中提取到的同样名为“石蕊”的化学物质，有遇到酸会变红，遇到碱会变蓝的特性，可以用于制作酸碱指示剂，应用于化学领域。

< class="pgc-img">

在化学中，石蕊是一种化学物质，是从地衣中提取的不同染料的水溶性混合物，英文中叫做“Litmus”。

这个单词源自古挪威语 “litmose”，“lit”是染料，“mose”是苔藓，结合起来的字面意思就是“用于染色的苔藓”，可以看出古挪威人大概是把石蕊地衣误认为是一种苔藓了。

石蕊中的一种化学物质7-羟基吩嗪酮（7-hydroxyphenoxazone），化学式为C12H7NO3，是石蕊试纸能够检测酸碱的关键。

< class="pgc-img">

7-羟基吩嗪酮结构变化示意图

当C12H7NO3遇到酸性溶液时，溶液中的氢离子（H+）会键结到环状结构的氮（N）上面，造成结构改变；而当C12H7NO3遇到碱性溶液时，羟基上的氢则会被溶液中的氢氧根（OH-）抢走，造成结构改变。而不同结构的化学物质，会吸收、反射不同波长的光，因此看起来颜色就会不同。

酸碱溶液改变了石蕊试纸中C12H7NO3的结构，使其吸收、反射的光的波长发生改变，呈现出不同的颜色。这就是石蕊试纸变色的原理。

< class="pgc-img">

蓝色、红色、紫色石蕊试纸检测酸性、碱性、中性溶液

但要注意的是石蕊试纸只能粗略地检测酸碱性，有红色、蓝色、紫色三种。以石蕊试纸为雏形改进的pH试纸，则能够进一步给出酸碱的大概数值。pH试纸本身多为浅黄色，试纸上有甲基红、溴甲酚绿、百里酚蓝这三种指示剂，颜色变化的范围更加广泛。

< class="pgc-img">

pH试纸颜色变化范围

< class="pgc-img">

约在 1300 年，最早由西班牙医师阿纳尔德斯·德·维拉·诺瓦 (Arnaldus de Villa Nova) 就开始使用石蕊来研究酸和碱。

在17世纪的某年夏天，英国著名化学家波义耳（Robert Boyle）的实验室里，还放着几朵因思念去世的女友而随身携带的紫罗兰。

但是意外却发生了，因操作不当，一滴浓盐酸溅到了紫罗兰上。但是波义耳发现，这朵花奇迹般地由紫色变成了红色，这个奇怪的现象勾起了他的探索欲，于是进行了一系列花草与酸碱相互作用的实验。

由此波义耳发现，大部分的花草遇到酸碱都会变色。其中又属从某种石蕊地衣中提取的紫色溶液变色最为明显，且遇酸变红，遇碱变蓝，波义耳利用这种特性，用石蕊提取液浸湿纸张，烤干之后就制成了石蕊试纸。

< class="pgc-img">

有多种石蕊科地衣都能够提取石蕊试剂，比如鳞片石蕊（Cladonia squamosa）、喇叭石蕊（Cladonia pyxidata）等。

< class="pgc-img">

鳞片石蕊 Cladonia squamosa

但虽然石蕊试纸是称为“石蕊”试纸，但其实其他的地衣也可以提取到石蕊试剂，比如Roccella montagnei和Dendrographa leucophaea等。

< class="pgc-img">

Roccella montagnei

< class="pgc-img">

Dendrographa leucophaea

< class="pgc-img">

在英语的语境中，石蕊测试“litmus test”还有着另一重含义——试金石。这种用法也是基于化学中石蕊测试的隐喻。

在化学里，通过石蕊可以测试出溶液的酸碱性，在政治上，则是测试候选人能否胜任某个高级职位。试金石可以是一个向潜在的高级职位候选人提出的问题，该问题的回答将决定该候选人是否会继续被任命或提名。

< class="pgc-img">

设计师以这种双重含义为灵感，制作了《LITMUS》。将可持续发展相关的问题当作“试金石”，对超过350名设计师进行采访，以此来调查设计师是如何看待设计中的可持续性。

经过结果统计，约87%的设计师认为可持续设计正在流行，但只有 59% 的设计师认为自己了解该领域。将一系列的统计结果制作成柱状图，模仿石蕊试纸的美学形式印刷成册。

< class="pgc-img">< class="pgc-img">

石蕊地衣原料难以采集，石蕊试纸的制作方法也十分复杂。若想制作自己的酸碱指示剂则可以用花青素来替代。花青素可溶于水，遇到酸性物质变为红色，遇到碱性溶液则变成黄绿色。

< class="pgc-img">

紫甘蓝制作的酸碱指示剂颜色变化范围

紫甘蓝是我们日常较容易获得的原材料，同时富含花青素。用紫甘蓝汁给一块布料染色并晾晒。然后就可以用醋、小苏打、清洁剂、酸性果汁等等等在厨房就能找到的---酸碱溶液在织物上作画了。

当这些酸和碱扩散到织物中时，颜色会以一种美丽的方式混合在一起，十分类似水墨晕染的效果。

< class="pgc-img">< class="pgc-img">

我们在上一篇文章（编者注：没有陈尿，我们居然还用不起紫色…）中介绍了地衣可以制造染料，染出丰富的色彩。在本篇文章中可以了解到，在科学方面，从地衣中提取的石蕊试剂又能够辅助科学家们进行实验、创造。地衣的用途如此广泛，还有更多等待我们去发掘。

参考文献

[1] Litmus Paper, Encyclopedia.com

[2] Litmus Paper and the Litmus Test, Anne Helmenstine

[3] Red Cabbage Acid-base Indicator Artwork, Craftiments.com

[4] National Library of Medicine

[5] 中国生物多样性红色名录—大型真菌卷

本文经授权转载自微信公众号“as科学艺术研究中心”，原标题为《化学老师没讲过的故事》。

特别提示

1. 进入『返朴』微信公众号底部菜单“精品专栏“，可查阅不同主题系列科普文章。

2. 『返朴』提供按月检索文章功能。关注公众号，回复四位数组成的年份+月份，如“1903”，可获取2019年3月的文章索引，以此类推。

多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

< class="tt-column-card" data-content='{"url":"","content":"","thumb_url":"https://p3-sign.toutiaoimg.com/pgc-image/24ef7e0323e644b387c35536970b3ff4~noop.image?x-expires=2018581227&x-signature=ujrT4UWPA5JtUJ3eLOLUe6koi8Q%3D","title":"Excel从入门到精通","author_description":"Excel学习世界","price":99,"share_price":7.92,"sold":81,"column_id":"6725297002684367112","new_thumb_url":"https://p3-sign.toutiaoimg.com/pgc-image/24ef7e0323e644b387c35536970b3ff4~noop.image?x-expires=2018581227&x-signature=ujrT4UWPA5JtUJ3eLOLUe6koi8Q%3D"}'>

今天教一个复选框的应用案例。

很多读者总是刻意回避控件的学习，以为需要编程，其实并不是哦，不编程也可以用控件设置出动态表格和图表。