哪位Excel高人琢磨出的这个数据处理技巧，太有用了！

公众号新闻

2023-05-04 15:05

关注【秋叶 Excel】👆发送【礼包】

领办公神器、Office 模板、免商字体！

作者：小花

编辑：竺兰

近期，各大城市 23 年第一批次土拍可谓惊险刺激，华润 1 挑 13，「拼手气」落子上海青浦区，王者归来；金地激战 86 轮，「中大奖」入主东莞松山湖，一剑封神！

新闻满天飞，让一些不熟悉土拍规则的朋友一头雾水，为什么说开发商拿地需要「拼手气」？

这是因为，当前很多城市的土地拍卖都会设置封顶价，在报价阶段如出现多个竞买人「触顶」，则将转入一次性书面报价，由最终报价最接近平均值的竞买人竞得土地。

怎么理解土拍终次报价规则呢？

举个例子，假设 B 列是某地块各开发商的终次报价，如何计算出谁最终竞得土地呢？

首先，需要计算出所有终次报价的平均值，然后计算每个报价与平均值的差额，取差额最小的，即为最终竞得人。

案例中的报价平均值为 5250，金 D 与之差额仅为 4，最小，因此金 D 竞得土地。

如下图，C 列公式：

=AVERAGE($B$2:$B$8)

D 列公式：

=ABS(B2-C2)

E 列公式：

=LOOKUP(1,0/(MIN(D2:D8)=(D2:D8)),A2:A8)

公式说明：

在 C 列借由 AVERAGE 函数计算出均值，在 D 列计算出差额的绝对值，E 列使用 LOOKUP 函数的两分法，根据最小值 MIN(D2:D8)在 D2:D8 中出现的位置，查询开发商名字，得到最终竞得人。

终次报价规则的本质，是求最接近值问题，这是一个比较复杂的 Excel 公式问题。不熟悉 Excel 的小伙伴，可以像上图那样借助辅助列来进行判断，但这显然不是最优解。

今天小花就分享两个解决该问题的实用公式套路，一起来学习吧！

公式一：常规思路下的数组公式

和辅助列的思路一致，我们通过匹配最小差额在所有差额中的位置序数，进而索引出对应的开发商名字，对应数组公式如下：

{=INDEX(A2:A8, MATCH(MIN(ABS(B2:B8-AVERAGE(B2:B8))),ABS(B2:B8-AVERAGE(B2:B8)),0))}

公式说明：该公式为数组公式，输入公式后需按【Ctrl+Shift+Enter】才能正确执行运算。

① ABS(B2:B8-AVERAGE(B2:B8))

运用平均值函数 AVERAGE 求 B2:B8 的平均值，再与 B2:B8 的每一个值分别求差，最后使用绝对值函数 ABS 求绝对值；

② MIN(①)

求①中差额的最小值；

③ MATCH(②,①,0)

使用 MATCH 函数匹配最小差在差额数组中出现的位置序数值；

④ {=INDEX(A2:A8, ③,0))}

根据③中得到的序数值，索引 A2:A8 中对应位置的开发商名称，即为终次报价最接近平均值而竞得土地的一方。

公式二：利用频率分布求解

公式一在思路上是比较容易理解的，但公式设置冗余，不方便书写。而使用 FREQUENCY 函数来设置一个基于频率分布的求解公式则是另一个极端，它公式简洁，但理解难度比较大。

D2 单元格公式如下：

=LOOKUP(1,0/FREQUENCY(0,ABS(B2:B8-AVERAGE(B2:B8))),A2:A8)

公式说明：该公式也执行数组运算，但由于 LOOKUP 自带数组运算能力，因此只需正常输入即可。

① ABS(B2:B8-AVERAGE(B2:B8))

原理见公式一片段①，不再赘述；

② FREQUENCY(0,①)

这是该公式的核心，它使用到冷门函数 FREQUENCY。

FREQUENCY 函数的运算原理类似隔板法，可以理解为将第二个参数间隔数组逐一标注在第一个参数统计数组上，并计算统计数组被参数数组分隔出的每个区间内，依次各自有几个数值，即频率分布。

乍一听，或许难以理解。如果我们将上图案例描绘在数组上，你一定会恍然大悟。

了解了 FREQUENCY，再回头看公式片段②FREQUENCY(0,①)，就显得非常简单了。

统计数值为{0}，分组数组为一组绝对值，由于绝对值总是大于等于 0，因此，在数轴上，0 总是在①中数组的最小值之前，即仅有最小差值所对应的分段区间频率为 1，其余区间均为 0，公式片段②返回{0;0;0;1;0;0;0;0}。

③ LOOKUP(1,0/②,A2:A8)

构建 0/②结构，将②中的 0 转化成错误值#DIV/0!，1 转化为 0，得到{#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}。

使用 LOOKUP 匹配 1 在 0/②中的位置，由于 LOOKUP 可以自动忽略错误值返回最后一个小于且最接近于查询值 1 的数值所在的位置，因此，唯一的非错误值被锁定，LOOKUP 根据其所在的位置序数 4，返回 A2:A8 中的第 4 个值「金 D」，问题得解。

以上，就是土拍终次报价规则本质——最接近值问题的 Excel 公式分享，主要有三种求解思路：

❶ 依次构建均值和差额两个辅助列，利用 LOOKUP 完成查询；

❷ 构建基于常规思路的 INDEX+MATCH 数组公式；

❸ 使用 FREQUENCY 计算分布频率，通过 LOOKUP 两分法锁定位置。

这下，你知道土拍终次报价规则及其 Excel 解法了吗？

你还遇到过哪些某一行业特有的 Excel 问题，不妨留言与我们分享，一同探索，一同成长吧！

如果你想学习更多 Excel 小技巧，那就报名秋叶 Excel 3天集训营，有大神带你学习表格飞速排版、数据高效整理、图表美化设计……！！和志同道合的小伙伴一起交流进步~

秋叶 Excel 3 天集训营》

课程原价 99 元

但只要你是秋叶 Excel 的读者

就能限时 1 元秒杀！！

仅需 3 天

你就可能成为 Excel 高手！

赶紧扫码抢课吧！！

优惠名额有限，先到先得！

现在扫码报名

还能免费领《35 个函数使用手册》！

*广告

❤

关注公众号【秋叶 Excel】

回复关键词【礼包】

还能免费领办公神器

Office 模板和免商字体！

↓↓↓

微信扫码关注该文公众号作者

戳这里提交新闻线索和高质量文章给我们。

来源: qq

点击查看作者最近其他文章