早做完,不加班。效率是關鍵,公式是手段。加油,打工人打工魂。

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”也就是變成工作表名!單元格的格式。

這個&就相當於月老的紅繩,可以將相愛的兩個人牽在一起,如將小紅跟小明撮合在一起。

第一部分就先分享到這裡,記得點贊收藏

頂部