近期，各大城市 23 年第一批次土拍可謂驚險(xiǎn)刺激，華潤(rùn) 1 挑 13，「拼手氣」落子上海青浦區(qū)，王者歸來(lái)；金地激戰(zhàn) 86 輪，「中大獎(jiǎng)」入主東莞松山湖，一劍封神！

新聞滿天飛，讓一些不熟悉土拍規(guī)則的朋友一頭霧水，為什么說(shuō)開(kāi)發(fā)商拿地需要「拼手氣」？

這是因?yàn)?，?dāng)前很多城市的土地拍賣(mài)都會(huì)設(shè)置封頂價(jià)，在報(bào)價(jià)階段如出現(xiàn)多個(gè)競(jìng)買(mǎi)人「觸頂」，則將轉(zhuǎn)入一次性書(shū)面報(bào)價(jià)，由最終報(bào)價(jià)最接近平均值的競(jìng)買(mǎi)人競(jìng)得土地。

怎么理解土拍終次報(bào)價(jià)規(guī)則呢？

舉個(gè)例子，假設(shè) B 列是某地塊各開(kāi)發(fā)商的終次報(bào)價(jià)，如何計(jì)算出誰(shuí)最終競(jìng)得土地呢？

首先，需要計(jì)算出所有終次報(bào)價(jià)的平均值，然后計(jì)算每個(gè)報(bào)價(jià)與平均值的差額，取差額最小的，即為最終競(jìng)得人。

案例中的報(bào)價(jià)平均值為 5250，金 D 與之差額僅為 4，最小，因此金 D 競(jìng)得土地。

如下圖，C 列公式：

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

D 列公式：

=ABS(B2-C2)

E 列公式：

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

公式說(shuō)明：

在 C 列借由 AVERAGE 函數(shù)計(jì)算出均值，在 D 列計(jì)算出差額的絕對(duì)值，E 列使用 LOOKUP 函數(shù)的兩分法，根據(jù)最小值 MIN(D2:D8)在 D2:D8 中出現(xiàn)的位置，查詢開(kāi)發(fā)商名字，得到最終競(jìng)得人。

終次報(bào)價(jià)規(guī)則的本質(zhì)，是求最接近值問(wèn)題，這是一個(gè)比較復(fù)雜的 Excel 公式問(wèn)題。不熟悉 Excel 的小伙伴，可以像上圖那樣借助輔助列來(lái)進(jìn)行判斷，但這顯然不是最優(yōu)解。

今天小花就分享兩個(gè)解決該問(wèn)題的實(shí)用公式套路，一起來(lái)學(xué)習(xí)吧！

公式一：常規(guī)思路下的數(shù)組公式

和輔助列的思路一致，我們通過(guò)匹配最小差額在所有差額中的位置序數(shù)，進(jìn)而索引出對(duì)應(yīng)的開(kāi)發(fā)商名字，對(duì)應(yīng)數(shù)組公式如下：

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

公式說(shuō)明：該公式為數(shù)組公式，輸入公式后需按【Ctrl+Shift+Enter】才能正確執(zhí)行運(yùn)算。

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

運(yùn)用平均值函數(shù) AVERAGE 求 B2:B8 的平均值，再與 B2:B8 的每一個(gè)值分別求差，最后使用絕對(duì)值函數(shù) ABS 求絕對(duì)值；

② MIN(①)

求①中差額的最小值；

③ MATCH(②,①,0)

使用 MATCH 函數(shù)匹配最小差在差額數(shù)組中出現(xiàn)的位置序數(shù)值；

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

根據(jù)③中得到的序數(shù)值，索引 A2:A8 中對(duì)應(yīng)位置的開(kāi)發(fā)商名稱，即為終次報(bào)價(jià)最接近平均值而競(jìng)得土地的一方。

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

公式一在思路上是比較容易理解的，但公式設(shè)置冗余，不方便書(shū)寫(xiě)。而使用 FREQUENCY 函數(shù)來(lái)設(shè)置一個(gè)基于頻率分布的求解公式則是另一個(gè)極端，它公式簡(jiǎn)潔，但理解難度比較大。

D2 單元格公式如下：

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

公式說(shuō)明：該公式也執(zhí)行數(shù)組運(yùn)算，但由于 LOOKUP 自帶數(shù)組運(yùn)算能力，因此只需正常輸入即可。

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

原理見(jiàn)公式一片段①，不再贅述；

② FREQUENCY(0,①)

這是該公式的核心，它使用到冷門(mén)函數(shù) FREQUENCY。

FREQUENCY 函數(shù)的運(yùn)算原理類似隔板法，可以理解為將第二個(gè)參數(shù)間隔數(shù)組逐一標(biāo)注在第一個(gè)參數(shù)統(tǒng)計(jì)數(shù)組上，并計(jì)算統(tǒng)計(jì)數(shù)組被參數(shù)數(shù)組分隔出的每個(gè)區(qū)間內(nèi)，依次各自有幾個(gè)數(shù)值，即頻率分布。

乍一聽(tīng)，或許難以理解。如果我們將上圖案例描繪在數(shù)組上，你一定會(huì)恍然大悟。

了解了 FREQUENCY，再回頭看公式片段②FREQUENCY(0,①)，就顯得非常簡(jiǎn)單了。

統(tǒng)計(jì)數(shù)值為{0}，分組數(shù)組為一組絕對(duì)值，由于絕對(duì)值總是大于等于 0，因此，在數(shù)軸上，0 總是在①中數(shù)組的最小值之前，即僅有最小差值所對(duì)應(yīng)的分段區(qū)間頻率為 1，其余區(qū)間均為 0，公式片段②返回{0;0;0;1;0;0;0;0}。

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

構(gòu)建 0/②結(jié)構(gòu)，將②中的 0 轉(zhuǎn)化成錯(cuò)誤值#DIV/0!，1 轉(zhuǎn)化為 0，得到{#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}。

使用 LOOKUP 匹配 1 在 0/②中的位置，由于 LOOKUP 可以自動(dòng)忽略錯(cuò)誤值返回最后一個(gè)小于且最接近于查詢值 1 的數(shù)值所在的位置，因此，唯一的非錯(cuò)誤值被鎖定，LOOKUP 根據(jù)其所在的位置序數(shù) 4，返回 A2:A8 中的第 4 個(gè)值「金 D」，問(wèn)題得解。

以上，就是土拍終次報(bào)價(jià)規(guī)則本質(zhì)——最接近值問(wèn)題的 Excel 公式分享，主要有三種求解思路：

? 依次構(gòu)建均值和差額兩個(gè)輔助列，利用 LOOKUP 完成查詢；

? 構(gòu)建基于常規(guī)思路的 INDEX+MATCH 數(shù)組公式；

? 使用 FREQUENCY 計(jì)算分布頻率，通過(guò) LOOKUP 兩分法鎖定位置。

這下，你知道土拍終次報(bào)價(jià)規(guī)則及其 Excel 解法了嗎？

你還遇到過(guò)哪些某一行業(yè)特有的 Excel 問(wèn)題，不妨留言與我們分享，一同探索，一同成長(zhǎng)吧！

如果你想學(xué)習(xí)更多 Excel 小技巧，那就報(bào)名秋葉《3 天 Excel 集訓(xùn)體驗(yàn)營(yíng)》，有大神帶你學(xué)習(xí)表格飛速排版、數(shù)據(jù)高效整理、圖表美化設(shè)計(jì)……??！和志同道合的小伙伴一起交流進(jìn)步~

秋葉《3 天 Excel 集訓(xùn)體驗(yàn)營(yíng)》

課程原價(jià)?99?元?

但只要你是秋葉 Excel 的讀者

就能限時(shí)?1 元秒殺??！

僅需 3 天

你就可能成為 Excel 高手！

趕緊掃碼搶課吧??！

優(yōu)惠名額有限，先到先得！

現(xiàn)在掃碼報(bào)名

還能免費(fèi)領(lǐng)《35 個(gè)函數(shù)使用手冊(cè)》！

*廣告