python吊打Excel？屁！那是你不會用！

相信大家總能在朋友圈刷到下圖這種教育廣告，python很強，但總是這么貶低Excel就沒必要了吧。

直到我看見了下面的回復(fù)，笑噴！

實話實說，從數(shù)據(jù)分析的角度來看，excel的可視化要差一些，數(shù)據(jù)采集也不能跟python相比，這都不是excel的專業(yè)，但是excel在數(shù)理統(tǒng)計上的表現(xiàn)還是很不錯的

所以當數(shù)據(jù)分析量小、想要快速出結(jié)果、邏輯關(guān)系簡單的情況下，excel很香！^[1]

而python就是給你一堆原材料，水泥、磚頭、鋼筋等等，你的工作就是自己要從頭摘，一步步把高樓給建造出來。

Excel雖然人人都會，但如果想要用來數(shù)據(jù)分析，要掌握的函數(shù)操作還真不少。

下文是Excel常用函數(shù)大全，建議收藏，不要吃灰！

文末三連不要忘了哈（閱讀、在看、分享）

這是一份很好的Excel常用函數(shù)大全，也是工作中經(jīng)常使用的函數(shù)操作，學(xué)習(xí)Excel常用函數(shù)，不要求面面俱到，而是選擇常用的函數(shù)進行學(xué)習(xí)，當再次碰到了其它函數(shù)，再單獨去學(xué)習(xí)即可。? ? ? ?——黃同學(xué)

1、excel函數(shù)需要掌握的基礎(chǔ)功能

??下面都是在使用Excel過程中，所使用過的一些基礎(chǔ)功能，限于篇幅這里就不詳細介紹，大家可以自行下去操作一遍。

• 相對引用
• 絕對引用
• 幫助功能
• 顯示公式
• 自動/手動計算
• 追蹤引用單元格
• 名稱編輯器
• 照相機

2、函數(shù)分類

3、學(xué)習(xí)函數(shù)的建議

• 不要死記硬背，但是看著某些函數(shù)一定要眼熟。
• 學(xué)會使用在線幫助功能(按F1鍵)。
• 合理的邏輯思維，excel中函數(shù)的使用，和我們學(xué)習(xí)其他編程語言中使用函數(shù)一樣，了解函數(shù)的功能、參數(shù)。

4、絕對引用、相對引用與半絕對引用

1）圖示講解含義

??注意：使用F4鍵，進行絕對引用和相對引用公式的切換。

2）利用九九乘法表講述“絕對引用”和“相對引用”

① 最簡單的一種

????????從上述案例可以看出，我們在B41單元格輸入了“=$A41*B$40”這個公式，這個公式表示B41單元格分別引用了“$A41和B$40”這兩個單元格，但是為什么是在A和40前面加“$”符號呢？這個需要我們好好琢磨一下。

????????我們可以先想象一下，當單元格B41從做左右拖拉填充的時候，是不是保持了“A41行不變，B40列變化”，因此40前面需要加一個“$”符號。當單元格B41從上往下拖拉填充的時候，是不是保持了“B40列不變，A41行變化”，因此40前面需要加一個“$”符號。

② 使用“&”連接符，顯示較全的九九乘法表

③ 配合if()函數(shù)，顯示上/下三角形式的九九乘法表

5、函數(shù)講解(最常用的個函數(shù))

??使用函數(shù)之前，學(xué)會下方的四個提示和一個注意：??一：所有的函數(shù)均是以“=”開頭；??二：所有的函數(shù)都是在“英文”狀態(tài)下輸入；??三：文本或日期等非單元格的引用，需要添加“雙引號”；??四：連接符是“&”；??注意：在excel單元格中，數(shù)字和日期都是靠著單元格右側(cè)，文本都是靠著單元格左側(cè)。

1）邏輯函數(shù)

① and

② or

③ if

當使用了if()函數(shù)進行多層嵌套，很容易寫錯，那么怎么規(guī)避這個問題呢？

2）字符串函數(shù)

① left

② right

③ mid

④ len(lenb自己下去學(xué)習(xí))

注意：len = 1中文 + 1英文；lenb = 2中文 + 1英文；這兩個函數(shù)可以完成中英文的分離。len - lenb表示的是中文個數(shù)；2len - lenb表示的是英文個數(shù)。

⑤ lower

⑥ upper

⑦ proper

⑧ find

注意：find()函數(shù)區(qū)分大小寫，search()函數(shù)不區(qū)分大小寫。

⑨ search

注意：find()函數(shù)區(qū)分大小寫，search()函數(shù)不區(qū)分大小寫。

⑩ rept

? replace

? substitute

注意：這個函數(shù)的最后一個參數(shù)很有用。如果一個文本中有幾個重復(fù)的內(nèi)容，最后一個參數(shù)可以指定，從第幾個重復(fù)內(nèi)容開始起，進行替換。

? trim

注意：該函數(shù)可以去掉字符串中所有的空格，但是會保留一個字符與字符之間的間隔空白符。

3）數(shù)學(xué)函數(shù)

① abs

② round

③ roundup

④ rounddown

⑤ even

注意：enen和odd都是朝著絕對值數(shù)字大的方向走的。

⑥ odd

注意：enen和odd都是朝著絕對值數(shù)字大的方向走的。

⑦ int

⑧ trunc

⑨ power

⑩ ^

? product

? mod

? rand

? randbetween

3）統(tǒng)計函數(shù)

① sum_sumif_sumifs

② average_averageif_averageif_averagea

注意：averagea()函數(shù)用法較為特殊，需要特別留意一下即可。

③ count_countif_countifs_counta_countblack

④ max_maxa_min_mina_median

⑤ sumproduct

⑥ subtotal

注意：這個函數(shù)可以實現(xiàn)我們之前學(xué)過的很多函數(shù)的功能，用起來也很方便，由于該函數(shù)涉及到的知識點太多，這里簡單介紹一下。我們在excel中輸入該函數(shù)，會有這么多提示：

下面介紹一些操作展示：

4）時間函數(shù)

① today_now：返回系統(tǒng)時間

② year_month_day：提取某個日期中的年、月、日

③ days_datedif：計算兩個日期之間的年、月、日

④ edate_eomonth：將時間前、后移動指定月份

⑤ weekday：返回星期幾的函數(shù)

這個函數(shù)需要特別注意一下，默認情況下，顯示的是國外的時間，也就是說星期天顯示的是1。但是我們國家希望星期天顯示的是7，星期一顯示的是1，怎么辦呢？我猜你肯定不知道，weekday()函數(shù)還有第二個參數(shù)，如下圖所示。

今天是2020年5月1號，按照國外默認來說，今天應(yīng)該是周六，即顯示結(jié)果為6。如果使用了第2個參數(shù)后，可以改成我們習(xí)慣的“星期”顯示方式。

⑥ text：返回各種日期、時間格式的函數(shù)(強大)

text()函數(shù)相當強大、有用，涉及到的知識點也是眾多的，因此這里提供一個學(xué)習(xí)鏈接給大家，就不詳細演示了：https://baijiahao.baidu.com/s?id=1615357210194597308&wfr=spider&for=pc^[2]

⑦ networkdays_networkdays.inl：計算工作日的常用函數(shù)

關(guān)于networkdays.inl的第4個參數(shù)，很有用，我們利用下圖進行展示一下，從下圖可以看出，每一個數(shù)字代表每一周的休息日是哪一天，例如“數(shù)字11”表示，休息日只有星期天，周六仍然需要上班，這就是我們所說的“單休”。

5）行數(shù)、列數(shù)統(tǒng)計函數(shù)

① column_columns_row_rows

6）匹配查找函數(shù)

① vlookup

注意：多條件查詢，需要添加輔助列。

② lookup及其高級應(yīng)用

注意：使用lookup()函數(shù)需要特別注意，“查找對象所在的列必須升序排列”，否則結(jié)果做出來都是錯的。同時，這個進行多條件查詢的時候，不需要添加輔助列。

假如，我不想改動源數(shù)據(jù)，仍然想要使用lookup()做查找，應(yīng)該怎么辦呢？那么就需要使用lookup()高級應(yīng)用了。關(guān)于lookup()高級應(yīng)用，這里需要記住一句話：首先它會默認你的查找區(qū)域是升序排列，當查找值在查找區(qū)域中找不到目標值的時候，就會返回該區(qū)域中的“最大值”，這就是下面要講述方法的“解題關(guān)鍵”。

③ index

關(guān)于index()函數(shù)顯示某一行值，涉及到數(shù)組的操作，下面我們錄制了一個視頻。

④ match

注意：index()和match()進行搭配使用，進行多條件查找，相當有效，也特別好用。組合棋類的效果是這樣的：index(查找區(qū)域，行號，列號)。

⑤ offset

7）錯誤處理函數(shù)

① iferror

6、函數(shù)實戰(zhàn)操作

1）and_or_if_left_right實戰(zhàn)演示

=IF(D3>100000,"是","否") ? ? ? ? ? ? ? ? ? ? ? ?----1 =IF(OR(B3="北京",B3="上海"),"是","否") ? ? ? ? ? ----2 =IF(AND(E3>30000,E3<100000),"是","否") ? ? ? ? ?----3 =B3&"--"&A3&"--"&E3 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ----4 =LEFT(C3,7)&"" ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?----5 =LEFT(C3,3)&""&RIGHT(C3,4) ? ? ? ? ? ? ? ? ?----6

操作如下：

if嵌套函數(shù)演示如下：

=IF(B51<60,"不及格",IF(B51<70,"及格",IF(B51<80,"不錯",IF(B51<90,"良好","非常好"))))

操作如下：

2）find_lower_left_right實戰(zhàn)演示

=LOWER(B3)??????????????????????????????????---1
=RIGHT(B3,LENB(B3)-LEN(B3))?????????????????---2
=FIND(RIGHT(B3,LENB(B3)-LEN(B3)),B3)????????---3
=LEFT(B3,2*LEN(B3)-LENB(B3)-1)??????????????---4

操作如下：

3）abs_int_trunc_round_roundup_rounddown_even_odd實戰(zhàn)演示

=ABS(A2)
=INT(A2)
=TRUNC(A2,1)
=ROUND(A2,1)
=ROUNDUP(A2,1)
=ROUNDDOWN(A2,1)
=EVEN(A2)
=IF(A3>0,EVEN(A3),EVEN(A3)+2)
=ODD(A2)
=IF(A2>0,ODD(A2),ODD(A2)+2)

操作如下：

4）rand_randbetween實戰(zhàn)演示

=RAND()
=TRUNC((RAND()*10))
=RANDBETWEEN(1,11)
=TEXT(RANDBETWEEN(1,TODAY()),"yyyy-mm-dd")

操作如下：

5）sum_sumif_sumifs實戰(zhàn)演示

=SUM(P2:P7326)
=SUMIF(Q:Q,2018,P:P)
=SUMIFS(P:P,Q:Q,2019,M:M,"上海")
=SUMIFS(P:P,Q:Q,2019,M:M,"上海",N:N,"酒水")

操作如下：

6）average_averageif_averageif_averagea實戰(zhàn)演示

=AVERAGE(G:G)
=AVERAGEIF(H:H,2018,G:G)
=AVERAGEIFS(G:G,H:H,2019,F:F,"可樂")

操作如下：

7）count_countif_countifs_counta_countblack實戰(zhàn)演示

=COUNT(G:G)
=COUNTIF(H:H,2019)
=COUNTIFS(H:H,2019,D:D,"上海")
'=COUNTIFS(H:H,2019,D:D,"上海",F:F,"可樂")

操作如下：

8）max_maxa_min_mina_median實戰(zhàn)演示

=MAX(G2:G7327)
=MIN(G2:G7327)
=MEDIAN(G2:G7327)

操作如下：

9）sumproduct實戰(zhàn)演示

=SUMPRODUCT(G:G,J:J)
=SUMPRODUCT((H:H="2018")*1,G:G,J:J)
=SUMPRODUCT((H2:H7326="2018")*G2:G7326*J2:J7326)
=SUMPRODUCT((H:H="2019")*1,(D:D="上海")*1,G:G,J:J)
=SUMPRODUCT((H:H="2019")*1,(D:D="上海")*1,(E:E="酒水")*1,G:G,J:J)

操作如下：

參考文章