【絕密乾貨】商場營運最常用EXCEL函數公式大全，幫你整理齊了，拿來即用！

商場營運工作必會的EXCEL函數公式與營運實際操作案例，個個都很實用，一定要注意收藏哦！              by楊叫獸

叫獸開場白

EXCEL中的函數很多，功能也非常強大，如能掌握一些常用的函數，將給日常的營運數據處理帶來很大的便利，幫助節省時間，提高工作效率。

下面，楊叫獸通過實際案例為各位營運小夥伴介紹一些非常實用的Excel技巧和公式，希望對大家有所幫助，讓大家一秒變工作小能手！

        ——（註：本文所有數據均為假設虛擬數據）

Happy Class一、判斷公式[11/11]1【IF】單條件判斷

案例——計算商鋪銷售完成率

如下圖，達到目標銷售額就寫完成，未達到則列出差額，公式為：

=IF(D2>C2,"完成",D2-C2)

案例——可能負值的完成率計算

如下圖，根據預算和實際，計算完成率，公式為：

=IF(B3<0,2-c3>

（*關註上述預算是負值的完成率計算結果不一樣）

案例——可能負值的完成率計算

如下圖，根據2016年和2015年，計算同比增長率，公式為：

=(B3-C3)/IF(C3>0,C3,-C3) 

（*關註上述2015年是負值的同比增長率計算結果不一樣。）

案例——處理公式產生的錯誤值

如下圖，處理同比增長率出現錯誤的單元格，如果是錯誤值則顯示為空，否則正常顯示。

把錯誤值顯示為空，公式為：

=IFERROR(C3/D3-1,"")

把錯誤值顯示為「新開業」，公式為：

=IFERROR(C3/D3-1,"新開業")

說明：兩個條件同時成立用AND,任一個成立用OR函數。

案例——判斷銷售額在一個區間：

如下圖，判斷銷售額在100萬-150萬之間的商鋪，顯示「是」與「否」，公式為：

=IF(AND(D2>1000000,D2<>是","否")

案例延伸：

同樣是上圖，判斷業態是餐飲且銷售額大於100萬，公式為：

=IF(AND(D2>1000000,C2="餐飲"),"是","否")

判斷業態是餐飲或快時尚，公式為：

=IF(OR(C2="餐飲",C2="快時尚"),"是","否")

判斷業態是餐飲或快時尚，且銷售額大於100萬，公式為：

=IF(and(OR(C2="餐飲",C2="快時尚"),D2>1000000),"是","否")

Happy Class二、求和公式[11/11]1【Sum】

重點關注以下第6)項與第7）項，多個工作表求和和合併單元格求和：

Sum主要有以下6種用法：

1）對數字求和:

=sum(1,2,3,4)

2）對幾個單元格求和：

=sum(A2,C4,B3,B4,D2)

3）對連續單元格求和：

=sum(A2:A6)

4）對列或行求和：

=sum(A:A)

=sum(6:6)

5）對區域求和

=sum(A2:F6)

=sum(A2:F6,C2:G6)

6）多個工作表求和

如下圖，假設各個sheet的格式一致，每個sheet 代表一個樓層，且該樓層的總銷售額都在C1單元格，要求各樓層的總和，公式為：

=SUM(負一層:四層!C1)

7）合併單元格求和

如下圖所示，要求在D列對C列的類別求和：

=SUM(C2:C$10)-SUM(D3:D$10)

公式輸入方法：先選取D2:D10,在編輯欄中輸入上述公式，再按ctrl+enter完成批量輸入。

2【Sumif】單條件求和

案例——求各業態的銷售額

如下圖，已知各商鋪的銷售額，要對各業態的銷售額求和，公式為：

=SUMIF(C$2:C$8,F2,D$2:D$8)

案例延伸：

——同是上圖，假設商鋪號01-01中的前面兩位數是樓層號，已知各商鋪號，要對1層的商鋪銷售額進行求和，公式為：

=SUMIF(B2:B8,"01*",E2:E8)

——同是上圖，計算商鋪名稱為三個字的銷售額之和，公式為：

=SUMIF(B2:B8,"???",E2:E8)s

——註：」*」和」?」都屬於通配符：

*可以代表任何文字或字元（任意個數）

僅代表單個文字或字元

案例——隔列求和

如下圖，已知各商鋪的每月實際和目標銷售額，要隔列求和，計算第一季度的合計公式為：

=SUMIF($C$2:$H$2,I$2,$C3:$H3)

（註：隔列的標題必須完全一致）

案例——求多種條件下（如各樓層、各業態等）的銷售額之和

如下圖，已知各商鋪的銷售額，要對各樓層各業態的銷售額求和，公式為：

=SUMIFS(E$2:E$8,A$2:A$8,G2,D$2:D$8,H2)

案例延伸：（如上圖）

——統計」除快時尚以外」的銷售額之和，公式為：

=SUMIF(D2:D8,"<>快時尚",E2:E8)

——也可以去掉行號，寫成整列引用，但必須前後一致：

=SUMIF(D:D,"<>快時尚",E:E)

——統計」銷售額大於100萬」的銷售額之和，公式為：

=SUMIF(E2:E8,">1000000",E2:E8)

——sumifs可以用於無限個條件，語法為：

SUMIFS(統計區域，第一條件區域，條件，[第二條件區域，第二條件....])

Sumif與Sumifs易錯點解析：

1）sumifs與sumif語法格式幾乎是相反的。

Sumif的統計區域在最後，Sumifs的統計區域在最前面。

2）要搞清楚絕對引用和相對引用，導致下拉公式時，需要固定的數據區域發生了變化；

3）原始表格的條件區域表格要規範（不能有時是「服飾」，有時是「普通服飾「，必須嚴格一致）

案例——求各業態的銷售額

如下圖，已知各商鋪的面積和租金單價，要對總租金求和，公式為：

=SUMPRODUCT(D2:D8,E2:E8)

案例延伸：sumproduct函數也可以用於多條件求和，和多條件計數，但是數據量非常大時運行速度較慢，所以不推薦使用。

運行速度最快請用：

多條件求和——sumifs,

多條件計數——countifs.

Happy Class三、統計公式[11/11]1

【Count/Counta/Countblank】簡單統計

如下圖所示，三個函數的不同功能和結果：

2【COUNTIF】單條件的統計

案例——統計符合單一條件的商鋪數量

如下圖所示，用countifs做相關統計：

計算純保底的公式是：

（註：G3單元格=」純保底」）

如上圖，延伸計算：

銷售額大於100萬的商鋪數：

=COUNTIF(D$2:D$19,">1000000")      

銷售額小於等於75萬的商鋪數

=COUNTIF(D$2:D$19,"<>

銷售額大於75萬且小於100萬的商鋪數

=COUNTIF(D$2:D$19,"<><>

在上述countif的案例中，銷售額大於75萬且小於100萬的商鋪數

也可以用countifs(多條件求和)來處理：

=COUNTIFS(D$2:D$19,">750000",D$2:D$19,"<>

案例——統計符合多條件的商鋪數量

統計餐飲業態銷售額大於50萬的商鋪數量：

=countifs(C2:C19,"餐飲」,E2:E19, ">500000」)

——countifs可以用於無限個條件，語法為：

countifs(第一條件區域，條件，[第二條件區域，第二條件....])

案例——用average時要注意空值與0值對結果的影響。

通過上圖結果（銷售額為0和空值在此處的情況是一樣的，均為商鋪未開業），空值也數值為0的單元格，會影響average的計算，空值不參加平均計算，而0值會參加平均。

此處正確公式應為：

=SUM(B2:D2)/COUNTIF(B2:D2,">0")

案例——用averageifs計算工作日、節假日平均

如上圖，計算工作日的日均公式是：

=AVERAGEIF(B2:B15,"工作日",C2:C15)

——AVERAGEIF可以用於無限個條件，語法為：

AVERAGEIF (統計區域，第一條件區域，條件，[第二條件區域，第二條件....])

——AVERAGE與AVERAGEIF語法格式幾乎是相反的。

【Rank】

案例——商鋪銷售額排名

如上圖，已知各商鋪銷售額，對其進行銷售排名，公式為：

=RANK(D2,D$2:D$19,0)

Rank函數易錯點解析：

1）要搞清楚絕對引用和相對引用，導致下拉公式時，需要固定的數據區域發生了變化；

2）後面的數字0代表降序，如果改成1則為升序。

3）函數RANK對重複數值的排位相同。但重複數的存在將影響後續數值的排位。如在一列整數中，若整數60出現兩次，其排位為5，則61的排位為7(沒有排位為6的數值)。

案例延伸：如果是多條件的排名，用的是 countifs，統計比數值大的商鋪有多少個，再+1 。

如下圖，即公式為：

=COUNTIFS(C$2:C$19,C2,D$2:D$19,">"&D2)+1

6【Max、Min】

兩者與average的語法相同。Max找出最大值，Min找出最小值。

案例——找出商鋪的最大的月銷售額數值（或最小值），公式為：

=MAX(B2:D2) （最小值為：=MIN(B2:D2)）

7【Large、Small】

案例——找出前三名的銷售額

如下圖，已知各商鋪明細，找出前三名銷售額的公式為：

=LARGE(D$2:D$19,F2)

如果改成後三名的公式為：

=SMALL(D$2:D$19,F2)

Happy Class四、查找與應用[11/11]1Lookup

語法：LOOKUP(要查找的數值,查找區域,結果區域)

要點： 這些數值必須按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則，函數 LOOKUP 不能返回正確的結果。

通常情況下，最好使用函數 HLOOKUP 或函數 VLOOKUP 來替代函數 LOOKUP

2【Vlookup】

案例——找出對應的上月銷售額

如下圖，有本月（2月）和上月（1月）兩個sheet，但兩者商鋪列表不一致，需要把1月銷售額對應到2月的表上：

公式為：=VLOOKUP(B2,上月!B:C,2,0)

Vlookup函數易錯點解析：

以上面公式為例：=VLOOKUP(B2,上月!B:C,2,0)

（注:」上月！」是指跨表之間引用；如果是同一個表引用可以省略 ）

1）第一個參數「B2」，是查找對象；

2）第二個區域是查找區域，查找的對象必須在查找區域對應的第一列（即上月sheet的B列）。如果在查找區域里沒有找到B2，則會返回錯誤「#N/A」；

3）第三個參數「2「，指的是返回查找區域里相應的第「2」列。查找區域必須至少包括2列，可以多，但不可以少，例如可以寫成」B:Z「，但不可以寫成」B:B。如果區域設置錯誤，會返回錯誤「#REF!」；

4）第四個參數「0「，表示精確查找，為1或省略時表示模糊查找。如果忘了設置第4個參數則會被公式誤以為是故意省略，按模糊查找進行。當區域也不符合模糊查找規則時，公式就會返回錯誤值；

5）要搞清楚絕對引用和相對引用，導致下拉公式時，需要固定的數據區域發生了變化。

Hlookup的語法和Vlookup的語法是一致的。具體用法及注意事項說明請參考Vlookup。

案例——通過行去查找

以下圖為例，通過行去查找管理費單價，公式為：

=HLOOKUP(B2,F$1:J$2,2,0)

4【Index+match】雙向查找/縱橫查找

Vlookup、Hlookup只能應用於列與列、行與行之間查找（只能從左到右，從上到下），如果要列+行縱橫查找或反向查找（從右到左，從下到上），就必須要用到【Index+match】。

案例——Index，Match分別的用法

（如上圖的數據案例，以下是公式功能解釋：）

語法：index（區域，第幾行，第幾列）

語法：match（目標值，查找區域，0）

註：這裡的0指的是精確查找。

案例——Index+Match

Index和Match單用的意義不大，要配合起來用才會彰顯價值。

說明：利用MATCH函數查找位置，用INDEX函數取值。

如下圖，多條件查找商鋪銷售額，公式為：

=INDEX(B3:E8,MATCH(C12,A3:A8,0),MATCH(A12,B2:E2,0))

（註：通過Match找到相應的行號和列號）

案例——Index+Match進階多條件查找

如下圖，通過多條件去查找，需要用以數組公式：

=INDEX(C3:F8,MATCH(C12&D12,A3:A8&B3:B8,0),MATCH(A12,C2:F2,0))

由於公式中含有數組運算（一組數同另一組數同時運算），所以公式需要按ctrl+shift+enter三鍵完成輸入。

Happy Class五、數字處理[11/11]

1.     【ABS】取絕對值

=ABS(數字)

2.     【INT】取整

=INT(數字)

3.     【Round】四捨五入

=ROUND(數字,小數位數)

上述三個函數的例子如下——

Happy Class六、日期與時間[11/11]1日期計算

1）日期有固定的輸入模式，以下都可以：

2016/1/1

2016年1月1日

2016-1-1

1/1或1-1或1月1日(這時是如果不輸年份，是默認為當前年份)

更多日期格式可以參考——

2）月份顯示也是用日期表示，默認是每月1日。

3）日期和時間的相關函數

語法：DATE(year,month,day)

3【Time】

語法：TIME(hour，minute，second)

參數：Hour是0到23之間的數，代表小時;Minute是0到59之間的數，代表分;Second是0到59之間的數，代表秒。

實例：公式「=TIME(12，10，30)」返回序列號0.51，等價於12:10:30 PM。=TIME(9，30，10)返回序列號0.40，等價於9:30:10AM。=TEXT(TIME(23，18，14)，"h:mm:ss AM/PM")返回「11:18:14 PM」。

=DATEDIF(開始日期,結束日期,返回什麼結果)

第三參數寫成"m"，就是計算兩個日期之間的整月數。

第三參數寫成"Y"，就是計算兩個日期之間的整年數，這個在計算工齡的時候經常用到的。

下面這幾種寫法，用到的比較少，大家簡單了解一下就可以了。

第三參數寫成「MD」，返回日期中天數的差。忽略日期中的月和年。

第三參數寫成「YM」，返回日期中月數的差。忽略日期中的日和年。

第三參數寫成「YD」，日期中天數的差。忽略日期中的年。

Happy Class七、文本處理[11/11]

常用的文本函數如下：

【LEN】

LEN(text) 返回字元串中的字元數

【TEXT】

TEXT(value,format_text)將一數值轉換為按指定數字格式表示的文本。

【MID】

MID(text,start_num,num_chars) 返回字元串中從指定位置開始的特定數目的字元

【RIGHT】

RIGHT(text,num_chars) 根據指定的字元數返迴文本串中最後一個或多個字元

【LEFT】

LEFT(text,num_chars) 基於所指定的字元數返迴文本串中的第一個或前幾個字元。

【Find】

FIND(find_text,within_text,start_num)按指定的小數位數進行四捨五入，利用句點和逗號，以小數格式對該數設置格式，並以文字串形式返回結果。

【Value】

VALUE(text) 將代表數字的文字串轉換成數字

案例——通過身份證號提取營業員生日

=--TEXT(MID(B2,7,8),"0-00-00")

公式解釋：MID(B2,7,8)的意思是提取從B2單元第7位開始往後的8位數字，即19871209，TEXT函數前的兩個減號，負負為正的意思，被稱為減負運算。

然後用TEXT函數把數據轉換成1987-12-09

案例——文本函數製作會議時間議程表

功能：只要修改任意時長或開始時間，後面的時間全部自動生成。

結束時間=開始時間+時長

下一階段的開始時間=上一階段的結束時間

時間的整體顯示公式如下：

=TEXT(A4,"H:MM")&"-"&TEXT(C4,"H:MM")

函數大全

總結前面的七大部分函數如下，方便各位小夥伴按需查找：

附——函數報錯的類型：

【最後，關於EXCEL的函數應用】

理解：因為函數是英文，所以英文轉化為漢語，漢語轉化為真實含義，都是一個過程。理解之後，使用起來就會得心應手。

運用：許多函數忘記了，是因為基本用不上，把好用的函數用起來，後面就會越用越上癮。

目標：是偷懶，偷懶的心，其實是前期設置好，後期自動化。

心法：是解決問題的總思路。這個學會了，所有的方法技巧，都可以隨便就能拿過來。

希望大家可以靈活運用以上套路，提高工作效率，告別不必要的加班~~~

最後，EXCEL祝大家光棍節快樂~~~~~~

EXCEL你丫是故意的嗎？

編輯：營運部楊琪琪

推薦閱讀：