全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

来自网友求助案例,如何将数据表每一行的最大最小值都替换成 0?正常情况下,用几个 if 嵌套总能到达彼岸。但是今天我要教大家的公式,却没有用到一个 if 函数。

案例:

下图 1 是一个评委打分表,请将每个参赛者获得的最高分、最低分都替换为 0。

效果如下图 2 所示。

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

解决方案:

1. 在 B15 单元格中输入以下公式:

=SUMPRODUCT((((B2=MIN($B2:$H2)) (B2=MAX($B2:$H2)))=0)*1,B2)

公式释义:

  • B2=MIN($B2:$H2):判断 B2 是否是同一行中的最小值;结果为逻辑值 true 或 false;
  • B2=MAX($B2:$H2):判断 B2 是否是同一行中的最大值;结果同为逻辑值;
  • … …=0:将上述两个值相加,判断和是否为 0;只有上述两个条件都为 0,即既不是最小值也不是最大值时,和才为 0,此时返回逻辑值 true,反之则为 false;
  • …*1:将逻辑值 *1,转换为数值 1 或 0;
  • SUMPRODUCT(…,B2):将上述数值与 B2 单元格相乘,只有既非最大值、也非最小值,才能返回原单元格结果,否则相乘结果为 0。

* 请注意公式中的绝对和相对引用。

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

有关 sumproduct 函数的详解,请参阅

2. 向右向下拖动公式

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

为了方便核对,我们全选 0 值来比对一下。

3. 选中 B15:H25 区域 — 按 Ctrl F — 选中“查找”选项卡 — 在“查找内容”区域输入“0”– 点击“查找全部”按钮

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

4. 点击“值”选项卡,让 0 值全都排在上方 — 按住 Shift 键,用鼠标选中所有 0 值单元格 — 关闭“查找与替换”选项卡

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

这样就能看清楚了,每一行的最大、最小值都被替换成了 0 值。

全程不用一个 if 函数,快速去除 Excel 中每位选手的最高、最低分

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

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

==》用代理价购买淘宝、京东、拼多多商品,享全网最高返利,了解一下《==

本文来自投稿,不代表创客坊立场,如若转载,请注明出处:https://www.chuangkefang.cn/w/15185.html

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注