Question to say "I can!"

EXCEL數據查找

2014-03-28

EXCEL有強大的數據處理功能,可以用它對數據進行組織和管理,當在工作中采集了大量數據后,出于某種需要,我們希望將符合某種條件的數據提取出來,有時是一個匯總結果,有時是所有符合條件的數據本身。很多剛接觸EXCEL的朋友不知道從哪個方面入手,下面我們通過幾個實例,快速掌握這些方法。
一、通過一定條件查找,返回匯總結果
在這個銷量表中,如果我們想統計出姓名為“張三”的全部銷量,可以用一個函數來做公式:
=SUMIF(銷量表!B:B,"張三",銷量表!C:C)
這個SUMIF函數是一個條件匯總函數,它有三個參數,第一個參數是要查找的區域,在本例中是B列,第二個參數是要查找的條件,在本例中是“張三”這個姓名,第三個參數是需要匯總的區域。
這個公式的含義是:在“銷量表”的B列查找“張三”這個內容,在B列找到后,把找到的同一行中的C列值相加。
我們還可以用下面這個公式達到同樣的效果:
=SUM(IF(銷量表!B1:B100="張三",銷量表!C1:C100))
這是一個數組公式,在輸入完成后要按CTRL+SHIFT+ENTER組合鍵確認,此時會自動在公式的兩端加上一對花括號(手工輸入花括號無效)。
在這個公式中用了兩個函數SUM與IF,先由IF對它右邊的條件“銷量表!B1:B100="張三"”做判斷,這個條件同樣是在銷量表的B列查找“張三”,當條件成立時,就執行“銷量表!C1:C100”這個部份,即返回C列同一行中的數值,最后由SUM函數將返回的所有相符的數值相加,得到正確結果。
第二個公式看似比第一個公式復雜,但它有一個“特殊”的作用,就是可以“多條件”查找。
再看一個例子,還是在“銷量表”中,把六七月份的“張三”的銷量統計出來(不要五月份的)
=SUM(IF((MONTH(銷量表!A2:A100)>5)*(銷量表!B2:B100="張三"),銷量表!C2:C100))
這個數組公式用了兩個條件,一個是A列的月份要大于5(即只要6、7月份的),另一個條件是B列等于“張三”,只有這兩條件都成立,才執行同一行中的C列匯總求和。
在這個公式中,由于要對“月份”進行計算,所以用一了個MONTH函數,它的作用是提取日期中的“月份”。
要注意一點,在SUMIF函數中可以用“整列”(即:銷量表!B:B),而在SUM與IF組合的數組公式中,不能用“整列”這個方式,只能用一個具體的區域(如:銷量表!A2:A100)。
提示1:在上面幾個公式中,為了方便使用,可以把“條件”放在一個固定的單元格中,然后在公式中只引用這個單元格。比如把要查找的“張三”輸入到K1格中,然后把公式改成: =SUMIF(銷量表!B:B,k1,銷量表!C:C)
提示2:如果公式不在“銷量表”中,那么公式中必須加上表格名字用以指定數據位置(如:“銷量表!B1:B100”前面的“銷量表!”),如果公式就在“銷量表”工作表中,則公式中不必加工作表的名字。
提示3:想達到上面的結果,還有很多其他方法,大家可根據自己的喜好選擇。
二、通過條件查找,返回符合條件的內容。
在上圖的表格中,每個姓名是唯一的,希望通過一個姓名查找到所需的電話號碼
比如查找李四的電話號碼
在F1單元格中輸入一個要查找的姓名“李四”,然后在F2單元格中用公式 =VLOOKUP(F1,A:B,2,0)
只要按需要改變F1格中的姓名,就可在F2單元格中得到對應的號碼。
這個公式使用了VLOOKUP函數,它有四個參數,第一個參數是要查找的值,第二個參數是要查找的范圍,第三個參數表示返回范圍中的第幾列內容,第四個參數如果為0,就表示“精確查找”,如果為1就表示“模糊查找”,一般用0值居多。
公式的含意是:在A:B兩列范圍的第一列A列中,查找F1單元格中的值,執行精確查找,找到后返回A:B兩列中第二列B列的內容。
還可以用另一個公式來達到這個效果(這個更靈活):
=INDEX(B:B,MATCH(F1,A:A,0))
這個公式用了兩個函數,MATCH函數返回F1單元格的值在A列的位置,第三個參數0表示只返回查找到的第一個值;INDEX函數通過前一個函數返回的位置,在B列中取出對應的內容。
這兩個公式各有特點,當查找區域是連續時,用VLOOKUP比較省事;當區域不在同一個位置時,就只能用第二個公式了。
三、通過指定條件,把所有符合的內容都顯示出來
還是以最上面的“銷量表”為例,通過一個公式把所有姓名等于“張三”的內容都顯示出來。
先在F1格輸入一個姓名“張三”,然后在H、I、J列使用公式:
H2單元格輸入公式 =INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))
I2單元格輸入公式 =INDEX($B$1:$B$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))
J2單元格輸入公式 =INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))
這三個數組公式中,只有INDEX的第一個參數不同,其他都是一樣的,公式完成后,把這三個單元格同時選中,用鼠標向下拖動復制到下面的其他格中。
這個公式對初學者來說,就比較難理解了,其實不論多復雜的公式,你可以把它分成多個部份,一部份一部份的來分析理解,就能知道它的作用了。
以I2格公式為例進行分析:
1、先看“IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000)”
它的意思是在B1:B10區域內查找F1單元格的值(一個要查找的姓名),如果區域內有這個值,就返回它的行號(ROW是返回行號的函數),如果沒有就返回一個比較大的數值(任意,只要大于數據的個數就行)。如果有多個符合的內容,就返回多個行號。
用本例數據查找“張三”來說明,通過這部份運算,會得到一串數值{1000;2;1000;4;5;1000;1000;1000;9;1000}
它表示在區域的第2、4、5、9行中找到了與“張三”相符的內容。為了方便說明,我給這串數值取個名字叫“行”
2、再看“SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1))”這部份,它等價于“SMALL(行,ROW(A1))”
這部份用SMALL函數返回一個“第幾小值”,由于I2單元格最后用的是ROW(A1),它是A1格的行號,即1,表示返回第一個最小的值,所以從上面“行”數值串中返回“2”。為了說明方便,我給它取名為“位置”。
提示:之所以在上面部份用ROW(A1)來代表1,是為了向下拖動復制公式時,它會自動遞增,每復制一行就自動增加1,例如把I2公式復制到I3格時,在I3格會變成ROW(A2)。這時在SMALL函數的第二個參數變成2,表示返回第二小值,即“行”字串中的“4”,依次類推。
3、最后看“INDEX($B$1:$B$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))”,它等價于“INDEX($B$1:$B$10,位置)”
這就好理解了,通過“位置”的數值,從B1:B10中取出相應位置的一個內容。
每個公式都可用這種“分解”的方法去理解和分析。

四、對多個重復數據,只顯示最后一個
還是以“銷量表”為例,這個表中的數據會不斷向下追加,如果我們希望在某一個單元格中只反映最新追加的數據,就需要用下面的公式了:
先在G1單元格中輸入一個姓名“張三”,然后在G2單元格中輸入數組公式 =INDEX(C1:C100,MAX(IF(B1:B100=G1,ROW(B1:B100),0)))
這個公式的含義與上面的差不多,只是把SMALL函數換成了MAX函數,MAX是求“最大值”的函數。
由于數據不斷追加,最新輸入的數據的行號肯定是最大的,通過MAX就能確定它的位置,然后用INDEX函數取出這個數據。
在上面介紹了一些常用的數據查找公式,尤其是后面的公式都使用了數組公式。數組公式在輸入后一定要通過按三個組合鍵來確認,否則無效。
數組公式的功能是強大的,但要注意,如果一個工作簿中使用了過多的數組公式,或數據區域非常龐大,會造成運算緩慢,效率變低。

作者:admin | Categories:技術人生 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 江苏体彩七位数走势图综合版 双码跟指什么数字 114博彩导航 体育彩票浙江20选5 江西多乐彩彩经网 广东11选五什么时候开盘 2020年大盘最低点 北京今天11选5走势图 从5万赚40亿期货 浙江20选5开奖号码查询 理财平台哪个安全可靠百科 河南福彩快三手机版 江苏11选5开奖结果势图 秒速赛车开奖软件 pc蛋蛋预测软件怎么用 深圳风采基本走势带坐标