編按：成本管控對于每一家公司來說都是至關(guān)重要的事！所以，員工如何在數(shù)據(jù)篩選中查找到最優(yōu)的目標(biāo)數(shù)據(jù)，實現(xiàn)成本控制，是領(lǐng)導(dǎo)們很欣賞的特質(zhì)。在此類工作中，我們最常用的查詢函數(shù)是Vlookup，但是殊不知多條件查找的最強王者還是我們的查詢老大哥——Lookup函數(shù)！下面，小E給大家?guī)淼木褪撬趯嶋H工作中的精彩運用……

小伙伴們，今天和大家分享一個機械加工中選型設(shè)備的問題。

機加工領(lǐng)域，機加工設(shè)備的價值會分?jǐn)偟矫恳粋€產(chǎn)出的零件中。隨著使用年限的增加，分?jǐn)偟某杀緯饾u降低的。這具體表現(xiàn)在，相同規(guī)格型號的新舊兩臺設(shè)備在加工同樣工藝的零件時，分?jǐn)偟搅慵械某杀臼遣煌摹?/span>

下面就是某加工廠的一部分設(shè)備清單。

工廠的技術(shù)員工會根據(jù)零件的要求選擇不同型號的設(shè)備來生產(chǎn)。現(xiàn)有一批工件，需要選擇起步價最低的機器來完成生產(chǎn)。具體的要求如下：

1.   A列中含“鋁”字的必須要用“機型屬性”列中含“鋁”字的機器

2.   B列C列的規(guī)格必須小于或等于“長上限”列和“寬上限”列的規(guī)格

3.   根據(jù)A列中的材料及B列和C列中的規(guī)格， 在“起步價”列中查找起步價最低的機型

在單元格D2中我們輸入公式“=LOOKUP(,0/FREQUENCY(1%,I$3:I$18%%%+(H$3:H$18<>COUNTIF(A2,"<>*鋁*"))%-(B2>F$3:F$18)-(C2>G$3:G$18)),J$3:J$18)”并向下拖曳即可。

函數(shù)解析：

總體上講，這個題目是：求滿足一定條件下的最小價格。限定條件有哪些呢？

-          長和寬的尺寸有限制

-          一部分設(shè)備對加工材料有限制

對于這種多條件下的查詢問題，LOOKUP函數(shù)處理起來非常方便。

1.   (B2>F$3:F$18)和(C2>G$3:G$18)部分，對不符合長和寬的尺寸要求的數(shù)據(jù)行做一個判斷。他們返回的結(jié)果就是由TRUE和FALSE組成的數(shù)組。在后面的計算中分別減去了(B2>F$3:F$18)和(C2>G$3:G$18)，即表示將不符合尺寸要求的數(shù)據(jù)行排除在外。

2.   COUNTIF(A2,"<>*鋁*")部分，對材料中是否是鋁合金做一個判斷。是鋁合金的，COUNTIF(A2,"<>*鋁*")部分返回數(shù)字0；不是鋁合金的，COUNTIF(A2,"<>*鋁*")部分返回數(shù)字1。

3.   (H$3:H$18<>COUNTIF(A2,"<>*鋁*"))%部分，對機型做一個判斷。如果是鋁合金，則返回適合加工鋁合金的機型設(shè)備；反之，則返回所有的設(shè)備。完成后，將結(jié)果縮小100分之一。

4.   I$3:I$18%%%部分，將價格縮小1000000分之一。為什么是10^6次方分之一呢？首先因為我們要找的是最小的價格，在經(jīng)肉眼觀察出起步價最長的位數(shù)是4位后，可以得知價格要縮小足夠的大小才能讓價格信息出現(xiàn)在合并數(shù)據(jù)的最右側(cè)。而且，(H$3:H$18<>COUNTIF(A2,"<>*鋁*"))%這部分已經(jīng)縮小了100分之一，所以I$3:I$18%%%部分縮小10^6分之一后才能使價格信息出現(xiàn)在數(shù)據(jù)的最右側(cè)。

5.   I$3:I$18%%%+(H$3:H$18<>COUNTIF(A2,"<>*鋁*"))%-(B2>F$3:F$18)-(C2>G$3:G$18)這幾部分合并后既有正數(shù)，也有負(fù)數(shù)。正數(shù)所對應(yīng)的長和寬的尺寸符合要求的設(shè)備。其計算結(jié)果為{-0.9897;-0.9995;-0.9996;-0.9995;-0.9995;0.0106;0.0105;0.0007;0.0111;0.0013;0.0012;0.0115;0.0017;0.0016;0.0118;0.0017}。

6.   利用FREQUENCY函數(shù)對1%在上述區(qū)間內(nèi)計頻，在0.0105對應(yīng)的位置上計頻1，計算結(jié)果是{0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0}。

7.   0/FREQUENCY部分返回結(jié)果{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}。

8.   利用LOOKUP函數(shù)的特點，查找到價格最低的機型。

好啦，今天和大家分享的就是這些了！

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

怎么用vlookup在兩個查找區(qū)域里查找？

10種職場人最常用的excel多條件查找方法?。ńㄗh收藏）

在excel中，按條件查找最大、最小值，這3個方法最好用！

同一Excel文件如何讓不同人查看的內(nèi)容不同？

版權(quán)申明：

文本作者EXCEL應(yīng)用之家；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。