商場(chǎng)營(yíng)運(yùn)工作必會(huì)的EXCEL函數(shù)公式與營(yíng)運(yùn)實(shí)際操作案例，個(gè)個(gè)都很實(shí)用，一定要注意收藏哦！ by楊叫獸
叫獸開(kāi)場(chǎng)白
EXCEL中的函數(shù)很多，功能也非常強(qiáng)大，如能掌握一些常用的函數(shù)，將給日常的營(yíng)運(yùn)數(shù)據(jù)處理帶來(lái)很大的便利，幫助節(jié)省時(shí)間，提高工作效率。
下面，楊叫獸通過(guò)實(shí)際案例為各位營(yíng)運(yùn)小伙伴介紹一些非常實(shí)用的Excel技巧和公式，希望對(duì)大家有所幫助，讓大家一秒變工作小能手！
——（注：本文所有數(shù)據(jù)均為假設(shè)虛擬數(shù)據(jù)）
★案例——計(jì)算商鋪銷售完成率
如下圖，達(dá)到目標(biāo)銷售額就寫(xiě)完成，未達(dá)到則列出差額，公式為：
=IF(D2>C2,'完成',D2-C2)
★案例——可能負(fù)值的完成率計(jì)算
如下圖，根據(jù)預(yù)算和實(shí)際，計(jì)算完成率，公式為：
=IF(B3<0,2-c3>
（*關(guān)注上述預(yù)算是負(fù)值的完成率計(jì)算結(jié)果不一樣）
★案例——可能負(fù)值的完成率計(jì)算
如下圖，根據(jù)2016年和2015年，計(jì)算同比增長(zhǎng)率，公式為：
=(B3-C3)/IF(C3>0,C3,-C3)
（*關(guān)注上述2015年是負(fù)值的同比增長(zhǎng)率計(jì)算結(jié)果不一樣。）
★案例——處理公式產(chǎn)生的錯(cuò)誤值
如下圖，處理同比增長(zhǎng)率出現(xiàn)錯(cuò)誤的單元格，如果是錯(cuò)誤值則顯示為空，否則正常顯示。
把錯(cuò)誤值顯示為空，公式為：
=IFERROR(C3/D3-1,'')
把錯(cuò)誤值顯示為“新開(kāi)業(yè)”，公式為：
=IFERROR(C3/D3-1,'新開(kāi)業(yè)')
說(shuō)明：兩個(gè)條件同時(shí)成立用AND,任一個(gè)成立用OR函數(shù)。
★案例——判斷銷售額在一個(gè)區(qū)間：
如下圖，判斷銷售額在100萬(wàn)-150萬(wàn)之間的商鋪，顯示“是”與“否”，公式為：
=IF(AND(D2>1000000,D2<>是','否')
案例延伸：
同樣是上圖，判斷業(yè)態(tài)是餐飲且銷售額大于100萬(wàn)，公式為：
=IF(AND(D2>1000000,C2='餐飲'),'是','否')
判斷業(yè)態(tài)是餐飲或快時(shí)尚，公式為：
=IF(OR(C2='餐飲',C2='快時(shí)尚'),'是','否')
判斷業(yè)態(tài)是餐飲或快時(shí)尚，且銷售額大于100萬(wàn)，公式為：
=IF(and(OR(C2='餐飲',C2='快時(shí)尚'),D2>1000000),'是','否')
重點(diǎn)關(guān)注以下第6)項(xiàng)與第7）項(xiàng)，多個(gè)工作表求和和合并單元格求和：
Sum主要有以下6種用法：
1）對(duì)數(shù)字求和:
=sum(1,2,3,4)
2）對(duì)幾個(gè)單元格求和：
=sum(A2,C4,B3,B4,D2)
3）對(duì)連續(xù)單元格求和：
=sum(A2:A6)
4）對(duì)列或行求和：
=sum(A:A)
=sum(6:6)
5）對(duì)區(qū)域求和
=sum(A2:F6)
=sum(A2:F6,C2:G6)
6）多個(gè)工作表求和
如下圖，假設(shè)各個(gè)sheet的格式一致，每個(gè)sheet 代表一個(gè)樓層，且該樓層的總銷售額都在C1單元格，要求各樓層的總和，公式為：
=SUM(負(fù)一層:四層!C1)
7）合并單元格求和
如下圖所示，要求在D列對(duì)C列的類別求和：
=SUM(C2:C$10)-SUM(D3:D$10)
公式輸入方法：先選取D2:D10,在編輯欄中輸入上述公式，再按ctrl+enter完成批量輸入。
★案例——求各業(yè)態(tài)的銷售額
如下圖，已知各商鋪的銷售額，要對(duì)各業(yè)態(tài)的銷售額求和，公式為：
=SUMIF(C$2:C$8,F2,D$2:D$8)
案例延伸：
——同是上圖，假設(shè)商鋪號(hào)01-01中的前面兩位數(shù)是樓層號(hào)，已知各商鋪號(hào)，要對(duì)1層的商鋪銷售額進(jìn)行求和，公式為：
=SUMIF(B2:B8,'01*',E2:E8)
——同是上圖，計(jì)算商鋪名稱為三個(gè)字的銷售額之和，公式為：
=SUMIF(B2:B8,'???',E2:E8)s
——注：”*”和”?”都屬于通配符：
*可以代表任何文字或字符（任意個(gè)數(shù)）
僅代表單個(gè)文字或字符
★案例——隔列求和
如下圖，已知各商鋪的每月實(shí)際和目標(biāo)銷售額，要隔列求和，計(jì)算第一季度的合計(jì)公式為：
=SUMIF($C$2:$H$2,I$2,$C3:$H3)
（注：隔列的標(biāo)題必須完全一致）
★案例——求多種條件下（如各樓層、各業(yè)態(tài)等）的銷售額之和
如下圖，已知各商鋪的銷售額，要對(duì)各樓層各業(yè)態(tài)的銷售額求和，公式為：
=SUMIFS(E$2:E$8,A$2:A$8,G2,D$2:D$8,H2)
案例延伸：（如上圖）
——統(tǒng)計(jì)”除快時(shí)尚以外”的銷售額之和，公式為：
=SUMIF(D2:D8,'<>快時(shí)尚',E2:E8)
——也可以去掉行號(hào)，寫(xiě)成整列引用，但必須前后一致：
=SUMIF(D:D,'<>快時(shí)尚',E:E)
——統(tǒng)計(jì)”銷售額大于100萬(wàn)”的銷售額之和，公式為：
=SUMIF(E2:E8,'>1000000',E2:E8)
——sumifs可以用于無(wú)限個(gè)條件，語(yǔ)法為：
SUMIFS(統(tǒng)計(jì)區(qū)域，第一條件區(qū)域，條件，[第二條件區(qū)域，第二條件....])
Sumif與Sumifs易錯(cuò)點(diǎn)解析：
1）sumifs與sumif語(yǔ)法格式幾乎是相反的。
Sumif的統(tǒng)計(jì)區(qū)域在最后，Sumifs的統(tǒng)計(jì)區(qū)域在最前面。
2）要搞清楚絕對(duì)引用和相對(duì)引用，導(dǎo)致下拉公式時(shí)，需要固定的數(shù)據(jù)區(qū)域發(fā)生了變化；
3）原始表格的條件區(qū)域表格要規(guī)范（不能有時(shí)是“服飾”，有時(shí)是“普通服飾“，必須嚴(yán)格一致）
★案例——求各業(yè)態(tài)的銷售額
如下圖，已知各商鋪的面積和租金單價(jià)，要對(duì)總租金求和，公式為：
=SUMPRODUCT(D2:D8,E2:E8)
案例延伸：sumproduct函數(shù)也可以用于多條件求和，和多條件計(jì)數(shù)，但是數(shù)據(jù)量非常大時(shí)運(yùn)行速度較慢，所以不推薦使用。
運(yùn)行速度最快請(qǐng)用：
多條件求和——sumifs,
多條件計(jì)數(shù)——countifs.
【Count/Counta/Countblank】簡(jiǎn)單統(tǒng)計(jì)
如下圖所示，三個(gè)函數(shù)的不同功能和結(jié)果：
★案例——統(tǒng)計(jì)符合單一條件的商鋪數(shù)量
如下圖所示，用countifs做相關(guān)統(tǒng)計(jì)：
計(jì)算純保底的公式是：
（注：G3單元格=”純保底”）
如上圖，延伸計(jì)算：
銷售額大于100萬(wàn)的商鋪數(shù)：
=COUNTIF(D$2:D$19,'>1000000')
銷售額小于等于75萬(wàn)的商鋪數(shù)
=COUNTIF(D$2:D$19,'<>
銷售額大于75萬(wàn)且小于100萬(wàn)的商鋪數(shù)
=COUNTIF(D$2:D$19,'<><>
在上述countif的案例中，銷售額大于75萬(wàn)且小于100萬(wàn)的商鋪數(shù)
也可以用countifs(多條件求和)來(lái)處理：
=COUNTIFS(D$2:D$19,'>750000',D$2:D$19,'<>
★案例——統(tǒng)計(jì)符合多條件的商鋪數(shù)量
=countifs(C2:C19,'餐飲”,E2:E19, '>500000”)
——countifs可以用于無(wú)限個(gè)條件，語(yǔ)法為：
countifs(第一條件區(qū)域，條件，[第二條件區(qū)域，第二條件....])
★案例——用average時(shí)要注意空值與0值對(duì)結(jié)果的影響。
通過(guò)上圖結(jié)果（銷售額為0和空值在此處的情況是一樣的，均為商鋪未開(kāi)業(yè)），空值也數(shù)值為0的單元格，會(huì)影響average的計(jì)算，空值不參加平均計(jì)算，而0值會(huì)參加平均。
此處正確公式應(yīng)為：
=SUM(B2:D2)/COUNTIF(B2:D2,'>0')
★案例——用averageifs計(jì)算工作日、節(jié)假日平均
如上圖，計(jì)算工作日的日均公式是：
=AVERAGEIF(B2:B15,'工作日',C2:C15)
——AVERAGEIF可以用于無(wú)限個(gè)條件，語(yǔ)法為：
AVERAGEIF (統(tǒng)計(jì)區(qū)域，第一條件區(qū)域，條件，[第二條件區(qū)域，第二條件....])
——AVERAGE與AVERAGEIF語(yǔ)法格式幾乎是相反的。
【Rank】
★案例——商鋪銷售額排名
如上圖，已知各商鋪銷售額，對(duì)其進(jìn)行銷售排名，公式為：
=RANK(D2,D$2:D$19,0)
Rank函數(shù)易錯(cuò)點(diǎn)解析：
1）要搞清楚絕對(duì)引用和相對(duì)引用，導(dǎo)致下拉公式時(shí)，需要固定的數(shù)據(jù)區(qū)域發(fā)生了變化；
2）后面的數(shù)字0代表降序，如果改成1則為升序。
3）函數(shù)RANK對(duì)重復(fù)數(shù)值的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。如在一列整數(shù)中，若整數(shù)60出現(xiàn)兩次，其排位為5，則61的排位為7(沒(méi)有排位為6的數(shù)值)。
案例延伸：如果是多條件的排名，用的是 countifs，統(tǒng)計(jì)比數(shù)值大的商鋪有多少個(gè)，再+1 。
如下圖，即公式為：
=COUNTIFS(C$2:C$19,C2,D$2:D$19,'>'&D2)+1
兩者與average的語(yǔ)法相同。Max找出最大值，Min找出最小值。
★案例——找出商鋪的最大的月銷售額數(shù)值（或最小值），公式為：
=MAX(B2:D2) （最小值為：=MIN(B2:D2)）
★案例——找出前三名的銷售額
如下圖，已知各商鋪明細(xì)，找出前三名銷售額的公式為：
=LARGE(D$2:D$19,F2)
如果改成后三名的公式為：
=SMALL(D$2:D$19,F2)
語(yǔ)法：LOOKUP(要查找的數(shù)值,查找區(qū)域,結(jié)果區(qū)域)
要點(diǎn)： 這些數(shù)值必須按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則，函數(shù) LOOKUP 不能返回正確的結(jié)果。
通常情況下，最好使用函數(shù) HLOOKUP 或函數(shù) VLOOKUP 來(lái)替代函數(shù) LOOKUP
V=Vertical 垂直 即列 | H=Horizontal水平 即行 |
★案例——找出對(duì)應(yīng)的上月銷售額
如下圖，有本月（2月）和上月（1月）兩個(gè)sheet，但兩者商鋪列表不一致，需要把1月銷售額對(duì)應(yīng)到2月的表上：
公式為：=VLOOKUP(B2,上月!B:C,2,0)
Vlookup函數(shù)易錯(cuò)點(diǎn)解析：
以上面公式為例：=VLOOKUP(B2,上月!B:C,2,0)
（注:”上月！”是指跨表之間引用；如果是同一個(gè)表引用可以省略 ）
1）第一個(gè)參數(shù)“B2”，是查找對(duì)象；
2）第二個(gè)區(qū)域是查找區(qū)域，查找的對(duì)象必須在查找區(qū)域?qū)?yīng)的第一列（即上月sheet的B列）。如果在查找區(qū)域里沒(méi)有找到B2，則會(huì)返回錯(cuò)誤“#N/A”；
3）第三個(gè)參數(shù)“2“，指的是返回查找區(qū)域里相應(yīng)的第“2”列。查找區(qū)域必須至少包括2列，可以多，但不可以少，例如可以寫(xiě)成”B:Z“，但不可以寫(xiě)成”B:B。如果區(qū)域設(shè)置錯(cuò)誤，會(huì)返回錯(cuò)誤“#REF!”；
4）第四個(gè)參數(shù)“0“，表示精確查找，為1或省略時(shí)表示模糊查找。如果忘了設(shè)置第4個(gè)參數(shù)則會(huì)被公式誤以為是故意省略，按模糊查找進(jìn)行。當(dāng)區(qū)域也不符合模糊查找規(guī)則時(shí)，公式就會(huì)返回錯(cuò)誤值；
5）要搞清楚絕對(duì)引用和相對(duì)引用，導(dǎo)致下拉公式時(shí)，需要固定的數(shù)據(jù)區(qū)域發(fā)生了變化。
Hlookup的語(yǔ)法和Vlookup的語(yǔ)法是一致的。具體用法及注意事項(xiàng)說(shuō)明請(qǐng)參考Vlookup。
★案例——通過(guò)行去查找
以下圖為例，通過(guò)行去查找管理費(fèi)單價(jià)，公式為：
=HLOOKUP(B2,F$1:J$2,2,0)
Vlookup、Hlookup只能應(yīng)用于列與列、行與行之間查找（只能從左到右，從上到下），如果要列+行縱橫查找或反向查找（從右到左，從下到上），就必須要用到【Index+match】。
★案例——Index，Match分別的用法
（如上圖的數(shù)據(jù)案例，以下是公式功能解釋：）
語(yǔ)法：index（區(qū)域，第幾行，第幾列）
語(yǔ)法：match（目標(biāo)值，查找區(qū)域，0）
注：這里的0指的是精確查找。
★案例——Index+Match
Index和Match單用的意義不大，要配合起來(lái)用才會(huì)彰顯價(jià)值。
說(shuō)明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值。
如下圖，多條件查找商鋪銷售額，公式為：
=INDEX(B3:E8,MATCH(C12,A3:A8,0),MATCH(A12,B2:E2,0))
（注：通過(guò)Match找到相應(yīng)的行號(hào)和列號(hào)）
★案例——Index+Match進(jìn)階多條件查找
如下圖，通過(guò)多條件去查找，需要用以數(shù)組公式：
=INDEX(C3:F8,MATCH(C12&D12,A3:A8&B3:B8,0),MATCH(A12,C2:F2,0))
由于公式中含有數(shù)組運(yùn)算（一組數(shù)同另一組數(shù)同時(shí)運(yùn)算），所以公式需要按ctrl+shift+enter三鍵完成輸入。
1. 【ABS】取絕對(duì)值
=ABS(數(shù)字)
2. 【INT】取整
=INT(數(shù)字)
3. 【Round】四舍五入
=ROUND(數(shù)字,小數(shù)位數(shù))
上述三個(gè)函數(shù)的例子如下——
1）日期有固定的輸入模式，以下都可以：
2016/1/1
2016年1月1日
2016-1-1
1/1或1-1或1月1日(這時(shí)是如果不輸年份，是默認(rèn)為當(dāng)前年份)
更多日期格式可以參考——
2）月份顯示也是用日期表示，默認(rèn)是每月1日。
3）日期和時(shí)間的相關(guān)函數(shù)
語(yǔ)法：DATE(year,month,day)
語(yǔ)法：TIME(hour，minute，second)
參數(shù)：Hour是0到23之間的數(shù)，代表小時(shí);Minute是0到59之間的數(shù)，代表分;Second是0到59之間的數(shù)，代表秒。
實(shí)例：公式“=TIME(12，10，30)”返回序列號(hào)0.51，等價(jià)于12:10:30 PM。=TIME(9，30，10)返回序列號(hào)0.40，等價(jià)于9:30:10AM。=TEXT(TIME(23，18，14)，'h:mm:ss AM/PM')返回“11:18:14 PM”。
=DATEDIF(開(kāi)始日期,結(jié)束日期,返回什么結(jié)果)
第三參數(shù)寫(xiě)成'm'，就是計(jì)算兩個(gè)日期之間的整月數(shù)。
第三參數(shù)寫(xiě)成'Y'，就是計(jì)算兩個(gè)日期之間的整年數(shù)，這個(gè)在計(jì)算工齡的時(shí)候經(jīng)常用到的。
下面這幾種寫(xiě)法，用到的比較少，大家簡(jiǎn)單了解一下就可以了。
第三參數(shù)寫(xiě)成“MD”，返回日期中天數(shù)的差。忽略日期中的月和年。
第三參數(shù)寫(xiě)成“YM”，返回日期中月數(shù)的差。忽略日期中的日和年。
第三參數(shù)寫(xiě)成“YD”，日期中天數(shù)的差。忽略日期中的年。
常用的文本函數(shù)如下：
【LEN】
LEN(text) 返回字符串中的字符數(shù)
【TEXT】
TEXT(value,format_text)將一數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。
【MID】
MID(text,start_num,num_chars) 返回字符串中從指定位置開(kāi)始的特定數(shù)目的字符
【RIGHT】
RIGHT(text,num_chars) 根據(jù)指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符
【LEFT】
LEFT(text,num_chars) 基于所指定的字符數(shù)返回文本串中的第一個(gè)或前幾個(gè)字符。
【Find】
FIND(find_text,within_text,start_num)按指定的小數(shù)位數(shù)進(jìn)行四舍五入，利用句點(diǎn)和逗號(hào)，以小數(shù)格式對(duì)該數(shù)設(shè)置格式，并以文字串形式返回結(jié)果。
【Value】
VALUE(text) 將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字
★案例——通過(guò)身份證號(hào)提取營(yíng)業(yè)員生日
=--TEXT(MID(B2,7,8),'0-00-00')
公式解釋：MID(B2,7,8)的意思是提取從B2單元第7位開(kāi)始往后的8位數(shù)字，即19871209，TEXT函數(shù)前的兩個(gè)減號(hào)，負(fù)負(fù)為正的意思，被稱為減負(fù)運(yùn)算。
然后用TEXT函數(shù)把數(shù)據(jù)轉(zhuǎn)換成1987-12-09
★案例——文本函數(shù)制作會(huì)議時(shí)間議程表
功能：只要修改任意時(shí)長(zhǎng)或開(kāi)始時(shí)間，后面的時(shí)間全部自動(dòng)生成。
結(jié)束時(shí)間=開(kāi)始時(shí)間+時(shí)長(zhǎng)
下一階段的開(kāi)始時(shí)間=上一階段的結(jié)束時(shí)間
時(shí)間的整體顯示公式如下：
=TEXT(A4,'H:MM')&'-'&TEXT(C4,'H:MM')
總結(jié)前面的七大部分函數(shù)如下，方便各位小伙伴按需查找：
附——函數(shù)報(bào)錯(cuò)的類型：
【最后，關(guān)于EXCEL的函數(shù)應(yīng)用】
理解：因?yàn)楹瘮?shù)是英文，所以英文轉(zhuǎn)化為漢語(yǔ)，漢語(yǔ)轉(zhuǎn)化為真實(shí)含義，都是一個(gè)過(guò)程。理解之后，使用起來(lái)就會(huì)得心應(yīng)手。
運(yùn)用：許多函數(shù)忘記了，是因?yàn)榛居貌簧?，把好用的函?shù)用起來(lái)，后面就會(huì)越用越上癮。
目標(biāo)：是偷懶，偷懶的心，其實(shí)是前期設(shè)置好，后期自動(dòng)化。
心法：是解決問(wèn)題的總思路。這個(gè)學(xué)會(huì)了，所有的方法技巧，都可以隨便就能拿過(guò)來(lái)。
希望大家可以靈活運(yùn)用以上套路，提高工作效率，告別不必要的加班~~~
最后，EXCEL祝大家光棍節(jié)快樂(lè)~~~~~~
EXCEL你丫是故意的嗎？
編輯：營(yíng)運(yùn)部楊琪琪
聯(lián)系客服
