作為一名財(cái)務(wù)工作者，在日常少不了會(huì)做一些查詢、統(tǒng)計(jì)分析之類的工作，Excel作為一款為數(shù)據(jù)分析而生的優(yōu)秀辦公軟件，為我們提供了一系列強(qiáng)大的數(shù)據(jù)分析功能，比如今天我們要講的，就是如何利用多條件函數(shù)運(yùn)算，為我們求得所需要的數(shù)據(jù)。
所謂的多條件函數(shù)運(yùn)算，也即針對(duì)需要提取所需信息的數(shù)據(jù)源，按照不同的條件進(jìn)行篩選，取得滿足所有條件的數(shù)據(jù)并進(jìn)行相應(yīng)運(yùn)算，最終得出需要的結(jié)果；有時(shí)，我們也可以利用數(shù)據(jù)篩選、數(shù)據(jù)透視表等功能來(lái)實(shí)現(xiàn)，但不管是以上哪種方法，都需要多步操作才能實(shí)現(xiàn)，有沒(méi)有一個(gè)更簡(jiǎn)便的方法來(lái)實(shí)現(xiàn)我們的目的哪？下面我們就來(lái)學(xué)習(xí)如何利用一個(gè)函數(shù)公式，求出滿足多條件下函數(shù)運(yùn)算的結(jié)果。
一、分析功能需求
首先進(jìn)行功能需求分析，我們這篇文章的數(shù)據(jù)源如下所示：
要實(shí)現(xiàn)的目的，就是求出指定員工代碼、指定銷售地區(qū)以及指定產(chǎn)品條件下的銷售總金額，如本例中，我們假設(shè)要求出數(shù)據(jù)源區(qū)域里，滿足員工代碼是1003、銷售地區(qū)是河南、銷售產(chǎn)品是產(chǎn)品D的所有銷售總金額，我們將這幾個(gè)條件列在數(shù)據(jù)源右側(cè)，并在最下方一行列示運(yùn)算出的結(jié)果：
要計(jì)算出符合條件的銷售總金額，需要按照以下順序進(jìn)行運(yùn)算：
1、從這上千行的數(shù)據(jù)源里，將同時(shí)滿足員工代碼為1003、銷售地區(qū)為河南、產(chǎn)品為產(chǎn)品D的數(shù)據(jù)行遴選出來(lái)；
2、在遴選出來(lái)的數(shù)據(jù)行里，將對(duì)應(yīng)的每行銷售單價(jià)與銷售數(shù)量進(jìn)行相乘，把每行的銷售額計(jì)算出來(lái)；
3、將每行計(jì)算出的銷售額相加，得出滿足所有條件的銷售總金額；
二、精煉函數(shù)
有了需求，我們就可以對(duì)照需求尋找可以實(shí)現(xiàn)該功能的函數(shù)公式；
1、利用函數(shù)來(lái)取得滿足員工代碼為1003的行次，將這些行次利用數(shù)組標(biāo)記為true，方法是：全選A列數(shù)據(jù)區(qū)域A2:A1000或手動(dòng)輸入該區(qū)域，利用A2:A1000=“1003”，將A列區(qū)域中是否滿足條件的數(shù)據(jù)利用邏輯值進(jìn)行標(biāo)識(shí)，形成一個(gè)包含999個(gè)邏輯值的數(shù)組，其中滿足1003的Excel將以true標(biāo)識(shí)，不滿足的以false標(biāo)識(shí)；
2、同理，利用函數(shù)來(lái)取得滿足銷售地區(qū)為河南的行次，將這些行次利用數(shù)組標(biāo)記為true，方法是：全選B列數(shù)據(jù)區(qū)域B2:B1000或手動(dòng)輸入該區(qū)域，利用B2:B1000=“河南”，將B列區(qū)域中是否滿足條件的數(shù)據(jù)利用邏輯值進(jìn)行標(biāo)識(shí)，形成一個(gè)包含999個(gè)邏輯值的數(shù)組，其中等于“河南”的Excel將以true標(biāo)識(shí)，不滿足的以false標(biāo)識(shí)；
3、第三個(gè)條件，我們依然利用函數(shù)來(lái)取得滿足銷售產(chǎn)品為產(chǎn)品D的行次，將這些行次利用數(shù)組標(biāo)記為true，方法是：全選C列數(shù)據(jù)區(qū)域C2:C1000或手動(dòng)輸入該區(qū)域，利用C2:C1000=“產(chǎn)品D”，將C列區(qū)域中是否滿足條件的數(shù)據(jù)利用邏輯值進(jìn)行標(biāo)識(shí)，形成一個(gè)包含999個(gè)邏輯值的數(shù)組，其中等于“產(chǎn)品D”的Excel將以true標(biāo)識(shí)，不滿足的以false標(biāo)識(shí)；
4、通過(guò)以上步驟，我們獲得了三個(gè)數(shù)組，每個(gè)數(shù)組包含999個(gè)邏輯值，分別對(duì)應(yīng)標(biāo)識(shí)A、B、C列里的每一個(gè)數(shù)據(jù)是否滿足對(duì)應(yīng)條件。我們知道，在Excel運(yùn)算中，true=1，false=0，因此邏輯值與邏輯值的運(yùn)算規(guī)則是：當(dāng)多個(gè)邏輯值相乘時(shí)，只有都為true時(shí)，結(jié)果才會(huì)是1，否則有一個(gè)為false時(shí)，結(jié)果就是0。利用這個(gè)特性，將三個(gè)邏輯數(shù)組對(duì)應(yīng)相乘，生成一個(gè)新的數(shù)組，新數(shù)組里為1的數(shù)組元素，就是能同時(shí)滿足A、B、C列條件的那行數(shù)據(jù)；
5、接下來(lái)，我們將上述三個(gè)數(shù)組相乘得出的數(shù)組，與D列銷售單價(jià)的數(shù)據(jù)區(qū)域D2:D1000以及E列銷售數(shù)量的數(shù)據(jù)區(qū)域E2:E1000繼續(xù)對(duì)應(yīng)相乘，也即生成一個(gè)新的一維數(shù)組，該數(shù)組中包含不滿足三個(gè)條件之一的行次對(duì)應(yīng)的數(shù)值0，以及同時(shí)滿足三個(gè)條件的行次中，由銷售單價(jià)和銷售數(shù)量相乘得出的銷售額；
6、在這個(gè)數(shù)組外面，加上sum（）求和函數(shù)，將數(shù)組求和，即可得出符合條件的銷售總金額。
三、函數(shù)組合
根據(jù)以上所述，我們整理出需要填充在結(jié)果單元格的公式，該公式不受填充位置的限制，如下所示：=SUM((A2:A1000=I4)*(B2:B1000=I5)*(C2:C1000=I6)*D2:D1000*E2:E1000)；
其中紅色的函數(shù)(A2:A1000=I4)是對(duì)第一個(gè)條件進(jìn)行判斷的邏輯值數(shù)組，I4為引用的單元格數(shù)值“1003”，其中符合條件的數(shù)組元素是true，不符合的為false；
綠色的函數(shù)(B2:B1000=I5)是對(duì)第二個(gè)條件進(jìn)行判斷的邏輯值數(shù)組，I5為引用的單元格數(shù)值“河南”，其中符合條件的數(shù)組元素是true，不符合的為false；
藍(lán)色的函數(shù)(C2:C1000=I6)是對(duì)第三個(gè)條件進(jìn)行判斷的邏輯值數(shù)組，I6為引用的單元格數(shù)值“產(chǎn)品D”，其中符合條件的數(shù)組元素是true，不符合的為false；
前面三個(gè)邏輯值數(shù)組與最后的紫色銷售金額數(shù)組D2:D1000*E2:E1000相乘，最外側(cè)加上sum（）求和函數(shù)，Ctrl+shift+enter三鍵將公式中的區(qū)域轉(zhuǎn)換為數(shù)組進(jìn)行計(jì)算，得出的就是同時(shí)符合三個(gè)條件的銷售總金額。
四、總結(jié)
1、理解Excel中與數(shù)組有關(guān)的概念及運(yùn)算規(guī)則；
2、注意列出公式后，如果想讓Excel將公式內(nèi)部的單元格區(qū)域視為數(shù)組進(jìn)行計(jì)算，一定要在輸入完整公式后，用ctrl+shift+enter三鍵激活單元格數(shù)組，才會(huì)在公式兩側(cè)出現(xiàn)代表數(shù)組運(yùn)算的花括號(hào)“{ }”；
3、采用ctrl+shift+enter三鍵將公式中的區(qū)域轉(zhuǎn)換為數(shù)組進(jìn)行計(jì)算的公式，一旦用鼠標(biāo)激活焦點(diǎn)，則兩側(cè)花括號(hào)“{ }”自動(dòng)消失，需要再次三鍵才會(huì)將其作為數(shù)組參與運(yùn)算；
4、采用這個(gè)方法計(jì)算數(shù)據(jù)，可以不受條件個(gè)數(shù)限制，將同時(shí)滿足所有條件的行次進(jìn)行運(yùn)算，靈活實(shí)現(xiàn)Excel函數(shù)中And（）函數(shù)的功能。
本文作者：東奧撰稿人diaolia
聯(lián)系客服
