1。
統計總銷售量(SUM函式)
這是某集團公司2014年各個分公司每月銷售量,如何統計總銷售量?
=SUM(C2:C11)
原理分析
本例中的求和區域是C2:C11,使用SUM函式對銷售量進行求和。SUM函式語法如下:
SUM(數字1,[數字2],……)
引數中數字可以是666之類的數字,C2之類的單元格引用或C2:C11之類的單元格範圍,引數1為必須引數,引數2為可選引數。
2。
統計每個公司的銷售量(SUMIF函式)
現在想知道每個公司的銷售量,如何統計呢?
=SUMIF(B:B,E2,C:C)
原理分析
本例中的條件區域為B列,條件為E2,求和區域為C列,藉助SUMIF函式進行單條件求和,求出每個公司的銷售量。
SUMIF函式語法如下:
SUMIF(條件區域,條件,[求和區域])
假如現在要對C列大於500的銷售量進行求和,正常用下面的公式:
=SUMIF(C:C,“>500”,C:C)
這時會發現一個問題,如果條件區域跟求和區域是一樣的,在這種情況下,求和區域可以省略,可將公式改成:
=SUMIF(C:C,“>500”)
3。
統計銷售量在500至700的和(SUMIFS函式)
前面說過藉助SUMIF函式可以求銷售量大於500的和,現在是500至700這個區間,該如何做呢?
=SUMIFS(C:C,C:C,“>=500”,C:C,“<=700”)
原理分析
本例中藉助SUMIFS函式可以實現多條件求和的特點,設定條件1為“>=500”和條件2為“<=700”,對C列的銷售量進行求和。SUMIFS函式語法如下:
SUMIFS(求和區域,條件區域1,條件1,[條件區域2],[條件2],……)
SUMIFS跟SUMIF的引數順序有點不同,SUMIFS求和區域在第一引數,而SUMIF函式求和區域在第三引數,這個需要特別注意一下。
4。
統計總銷售金額(SUMPRODUCT函式)
根據銷售明細表如何統計總銷售金額?
=SUMPRODUCT(D2:D11,E2:E11)
原理分析
藉助SUMPRODUCT函式對兩個區域先乘積,後求和。SUMPRODUCT函式語法:
SUMPRODUCT(區域1,區域2,……)
5。
統計上班天數(COUNTA函式)
這是某公司考勤記錄表,有上班就打勾(√),沒上班就空著,如何統計每個人的上班天數?
=COUNTA(B3:C33)/2
[圖片5]
原理分析
藉助COUNTA統計非空單元格個數,因為每天上班包含上下午,一個√算半天,所以要求出總數再除以2。
COUNTA函式語法如下:
COUNTA(值1,[值2],……)
COUNTA函式語法跟SUM函式一樣。
6。
考勤統計(COUNTIF函式)
上一個例子的考勤表就只有上班跟空白兩種情況,而實際上可以細分為正常上班、遲到、事假等等,現在如何一次統計每個人正常、遲到、事假的次數?
=COUNTIF($B2:$I2,J$1)
原理分析
COUNTIF函式就是條件計數函式,跟SUMIF函式有點類似,就是少了一個求和區域而已。
COUNTIF函式語法:
COUNTIF(條件區域,條件)
7。
統計性別為男、學歷為大專人數(COUNTIFS函式)
下圖為員工資訊表,如何統計性別為男、學歷為大專人數?
=COUNTIFS(C2:C11,“男”,E2:E11,“大專”)
原理分析
COUNTIFS函式可以實現多條件計數,函式語法如下:
COUNTIFS(條件區域1,條件1,條件區域2,條件2,……)
8。
猴子分桃(IF函式)
宋國有一個養猴的老人,喜歡猴子,把它們成群養著,他可以理解猴子的意思,猴子也可以理解老人的心意。養猴的老人寧可減少他與家人的食物也要滿足猴子的需求。不久,他家裡的糧食缺乏了,他將限定猴子的食物的數量。但又怕猴子不順從自己,就先欺騙猴子說:“給你們桃子,早上三個,晚上四個,夠嗎?”猴子們都站了起來並且十分惱怒。他又說:“給你們桃子,早上四個,晚上三個,夠了吧?”猴子都非常高興然後一個個都趴在地上。從這裡就可以看到2個判斷:
1.如果是早上就給3個桃子,否則(是晚上)就給4個
2.如果是早上就給4個桃子,否則(是晚上)就給3個
將資料輸入Excel中,我們就可以藉助IF函式替我們判斷。
在單元格C2輸入如下公式,並向下複製,即可得到每個時間段應該分多少桃子。
=IF(B2=“早上”,3,4)
=IF(B2=“早上”,4,3)
原理分析
IF函式的作用就是進行邏輯判斷,語法如下:
IF(條件,條件符合時返回的值,條件不符合時返回的值)
如第1個公式,條件就是判斷是否等於早上,滿足了就顯示3,否則(等於晚上)就顯示4。因為只是早晚需要分桃問題而已,不是早上,必然是晚上。
9。
旅遊必備條件(AND函式)
還沒參加工作的時候,有的是時間,總想到處去玩,去雲南、北京、西安……但這時因為沒錢,只能是想想而已。參加工作以後,錢就有了,可是天天上班,沒有時間出去玩。也就是說要出去外面玩這種機會還是比較少,需要同時滿足2個條件:1、有錢;2、有時間。
現在只有同時滿足有錢有時間就出去外面玩,否則就不玩。
在單元格C2輸入公式,並向下複製。
=IF(AND(A2=“有錢”,B2=“有時間”),“出去玩”,“不玩”)
原理分析
AND函式的作用就是同時滿足的意思,語法如下:
AND(條件1,條件2,……)
只有當所有條件同時都滿足的情況下才顯示TRUE,只要其中一個不滿足都顯示FALSE。
10。
讓女朋友開心的條件(OR函式)
現實生活中一定會發現經常遇到女朋友沒有理由的生氣,或者感覺女朋友無理取鬧,那麼在這個時候你應該怎麼辦呢?是晾著不管還是想辦法哄她開心呢。這個時候如果你選擇不管,那一定會進一步激化矛盾,所以你最好的辦法就是儘快找到合適的方法哄她開心。
下面的方法都可以讓女朋友開心:吃大餐、送禮物、安慰她、講趣事、去散心、陪伴……
哄女朋友開心的方法有很多,但只要你用了1種或者多種女朋友都會開心,而如果你什麼都不做,女友肯定會很不開心。
在G2輸入公式,並向下複製。
=IF(OR(A2=“吃大餐”,B2=“送禮物”,C2=“安慰她”,D2=“講趣事”,E2=“去散心”,F2=“陪伴”),“開心”,“不開心”)
原理分析
OR函式就是或者的意思,語法如下:
OR(條件1,條件2,……)
只要其中有一個條件滿足就顯示TRUE,全部不滿足就顯示FALSE。
11。
根據姓名查詢快遞單號(VLOOKUP函式)
現在我們買東西都是在網上購買,經常都會收到各種快遞,透過快遞單號查詢物件什麼時候到。現在有一份快遞清單,因為姓名的順序亂了,如何根據姓名查詢韻達快遞單號?
在單元格E2輸入公式,並向下複製公式。
=VLOOKUP(D2,A:B,2,0)&“”
[圖片11]
原理分析
本例藉助VLOOKUP函式查詢D2在區域A:B兩列中的第2列的對應值,也就是韻達單號。函式語法如下:
VLOOKUP(查詢值,查詢區域,返回查詢區域的第幾列,精確還是模糊查詢)
引數4:如果為0就是精確查詢,1就是模糊查詢。
後面&“”,就是將單號變成文字格式,防止顯示科學計數法。
12。
根據客戶號逆向查詢客戶姓名(LOOKUP函式)
現在有一份客戶姓名跟客戶號清單,想根據客戶號查詢客戶姓名。
=LOOKUP(1,0/($B$2:$B$34=D2),$A$2:$A$34)
原理分析
LOOKUP函式經典查詢模式通用公式:
LOOKUP(1,0/((條件1)*(條件2)*……*(條件n)),返回區域)
有了這個通用公式,即使是多條件查詢對應值也可以輕易做到,這就比VLOOKUP函式好用多了。
13。
查詢生肖的排位(MATCH函式)
這裡有一份12生肖對應表,要根據生肖,查詢自己的排位。
=MATCH(C2,A2:A13,0)
原理分析
MATCH函式的語法如下:
MATCH(查詢值,查詢的區域,精確或者模糊查詢)
第三引數0的時候為精確查詢,1為模糊查詢。
14。
路程導航(OFFSET函式)
我們去陌生的地方就經常會問朋友說我現在在某某地方,如何去某某地方。或者用百度地圖查詢。比如我現在起點是中國銀行,終點是潮州市政府。
這時先往後走630m到潮州大道跟楓春路這個十字路口,再往右邊走200m就到潮州市政府。
現在盧子要去瀟子家,該怎麼走呢?
有2種方案:
1.往右邊走3站到姐姐家,再往下邊走5站到瀟子家。
2.往下邊走5站到班長家,再往右邊走3站到瀟子家。只往右邊走3站,不向下走。
不管是哪種方案走,都必須向下5站,向右3站。
=OFFSET(B2,5,3)
原理分析
OFFSET函式的語法如下:
OFFSET(起點,向下多少行,向右多少列)
向下(右)用正數表示,向上(左)用負數表示。雖然兩點之間的距離最短,但OFFSET並不支援對角線走,因為對角線沒路可走。
比如現在瀟子要去盧子家,就是向上5行,向左3行。
=OFFSET(E7,-5,-3)
15。
間接引用多個表的合計(INDIRECT函式)
1月到4月機臺的產量,4個表格格式一模一樣,如何引用所有表格的合計呢?
使用公式:
=INDIRECT(A2&“!B11”)
原原理分析
INDIRECT函式的語法如下:
INDIRECT(單元格引用)
公式中的&就是將字元連線起來,變成了“1月!B11”也就是變成工作表名!單元格的格式。
這個&就相當於月老的紅繩,可以將相愛的兩個人牽在一起,如將小紅跟小明撮合在一起。
第一部分就先分享到這裡,記得點贊收藏