原文標(biāo)題：《哪位 Excel 高人琢磨出的這 4 個(gè)會(huì)計(jì)提效公式，太牛掰了！》
經(jīng)常有人問(wèn)小花：
你是怎么記住這么多函數(shù)以及他們的變形用法的？
同樣的，也有人問(wèn)：
為什么你的文章總喜歡就一個(gè)問(wèn)題反復(fù)探討不同的解法？
其實(shí)，這兩個(gè)問(wèn)題剛好互為應(yīng)和，他們是彼此的答案。
論語(yǔ)有云：
學(xué)而不思則罔，思而不學(xué)則殆。
今天，小花就以朋友近期的提問(wèn)，再次印證這古老的東方智慧。
這位朋友是一名會(huì)計(jì)，他需要根據(jù)「應(yīng)付賬款賬齡明細(xì)表」判斷每一筆應(yīng)付款的主要賬齡。
怎么計(jì)算主要賬齡？
比如：A 公司應(yīng)付余額中，賬齡在 1 個(gè)月以內(nèi)的絕對(duì)額最高，所以，A 公司的主要賬齡是 1 個(gè)月以內(nèi)。
那么如何用公式實(shí)現(xiàn)快速判斷呢？以下分享四種不同的解題思路。
1、常規(guī)查詢法
作為 VLOOKUP 函數(shù)的孿生兄弟，HLOOKUP 專門用于按列查詢。
唯一要解決的問(wèn)題是，結(jié)果列在查詢列上方，而非下方。
因?yàn)橹挥挟?dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，函數(shù) HLOOKUP 才能正確計(jì)算。
因此，我們需要使用 IF ({1;0},,）結(jié)構(gòu)來(lái)虛擬查詢數(shù)據(jù)表。
HLOOKUP 精確查詢公式：
=HLOOKUP(MAX(C2:G2)IF({1;0}C2:G2$C$1$G$1)2,0)
公式說(shuō)明：
1 代表 TRUE，0 代表 FALSE，分號(hào)代表分行。
IF {1;0} 將 C2:G2 與 C1:G1 重新組合為一個(gè)虛擬的、以 C2:G2 為首行的新數(shù)據(jù)表。
HLOOKUP 查詢 MAX 最大值在 C2:G2 出現(xiàn)的位置并返回 C1:G1 對(duì)應(yīng)位置的值。
這就是經(jīng)典的 IF 引導(dǎo)的逆向查詢公式：當(dāng)有多個(gè)最大值時(shí)，該公式返回滿足條件的首個(gè)查詢結(jié)果。
2、文本連接法
當(dāng)主要賬齡唯一時(shí)，我們還可以使用 IF 函數(shù)進(jìn)行判斷，將不滿足條件的文本轉(zhuǎn)換為空，再將所有文本聯(lián)和起來(lái)，就能得出主要賬齡。
具體公式如下??
CONCAT 條件文本連接公式：
{=CONCAT(IF(C2:G2=MAX(C2:G2)$C$1$G$1""))}
公式說(shuō)明：
這是一個(gè)數(shù)組公式，必須使用【Ctrl+Shift+Enter】才能準(zhǔn)確運(yùn)算。
它通過(guò)將 C2:G2 的每一個(gè)值與其最大值進(jìn)行比較，相等則返回 C1:G1 對(duì)應(yīng)賬齡分類，不等返回空。
CONCAT 函數(shù)將唯一的賬齡分類和其余空值連接起來(lái)，其結(jié)果就是主要賬齡。
該公式不適用多個(gè)相等最大值的情況。
3、頻率分布法
FREQUENCY 函數(shù)計(jì)算單值的頻率分布時(shí)，僅目標(biāo)值對(duì)應(yīng)頻率為 1，其余均為 0。
利用 FREQUENCY 的這一特性，我們可以很輕易的聯(lián)系到 LOOKUP 的兩分法，進(jìn)而構(gòu)建公式。
FREQUENCY 頻率分布查詢公式：
=LOOKUP(1,0/FREQUENCY(MAX(C2:G2)C2:G2)$C$1$G$1)
公式說(shuō)明：
FREQUENCY (MAX (C2:G2),C2:G2),$C$1:$G$1) 僅在 C2:G2 的最大值位置處返回 1，其余位置均為 0。
公式其余部分構(gòu)成了經(jīng)典的 LOOKUP 兩分法公式，原理此處不再贅述。
當(dāng)有多個(gè)最大值時(shí)，該公式返回最后一個(gè)最大值對(duì)應(yīng)的查詢結(jié)果。
4、條件排序法
2021 以上版本 OFFICE 或 WPS 的使用者，還可以用新函數(shù) SORTBY 來(lái)解決這一問(wèn)題。
此外，我們還需要 INDEX 來(lái)索引排序后的首個(gè)值，即主要賬齡。
SORTBY 排序索引公式：
=INDEX(SORTBY($C$1$G$1,C2:G2-1)1)
公式說(shuō)明：
SORTBY 函數(shù)以 C2:G2 為排序依據(jù)，以降序排列方式，將 C1:G1 單元格進(jìn)行重新排列，此時(shí)，應(yīng)付款最大值對(duì)應(yīng)的主要賬齡排到第 1 位，再使用 INDEX 索引即可。
當(dāng)有多個(gè)最大值時(shí)，該公式返回首個(gè)最大值對(duì)應(yīng)的賬齡。
以上，就是主要賬齡判斷的四種不同思路：
? 使用 IF ({1;0},,）結(jié)構(gòu)和 HLOOKUP 函數(shù)進(jìn)行逆向查詢法。
? IF 求組判斷去除非目標(biāo)文本后再使用 CONCAT 連接。
? LOOKUP 對(duì) FREQUENCY 概率分布進(jìn)行二分法查詢。
? 通過(guò)新函數(shù) SORTBY 對(duì)降序排列后再使用 INDEX 進(jìn)行索引。
不難發(fā)現(xiàn)，借由這樣一個(gè)簡(jiǎn)單問(wèn)題的不同求解思路，我們已經(jīng)學(xué)會(huì)了 8 個(gè)函數(shù)（MAX，IF，HLOOKUP，CONCAT，LOOKUP，F(xiàn)REQUENCY，INDEX，SORTBY）和 4 種高能用法（IF 重構(gòu)數(shù)據(jù)表，數(shù)組公式，LOOKUP 的兩分法，F(xiàn)REQUENCY 單值頻率分布）。
如果我們能對(duì)工作學(xué)習(xí)中的每一個(gè)簡(jiǎn)單問(wèn)題都進(jìn)行這樣開(kāi)放性的思考和實(shí)踐，Excel 水平定能突飛猛進(jìn)，你說(shuō)是嗎？
本文來(lái)自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：小花
廣告聲明：文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。