簡單,但是一旦學會就很牛逼的Excel函數

對,你沒看錯,確實存在一些很簡單,但是一旦掌握就很牛逼的Excel函數。

為什麼會取巧寫很簡單的函數呢?

是不是樓主浪得虛名,搞不懂複雜的函數,所以只能以簡單的函數「糊弄」大家。

……你……你……你怎麼每次都揭穿樓主啊……

但是,如果我告訴你這些函數需要滿足的條件,你就不會覺得吹牛逼了:

入選條件 1、簡單——必須的

2、常用——必須的

3、牛逼——這才是關鍵

可以說,把這幾個函數掌握了,能讓你實現從0到1的突破,迅速裝逼成功。

1Trim() 函數——刪除空格,統一格式

如果單元格中含有不確定的空格,那麼在進行公式計算、數據查找、引用時就會出錯。而且,對處女座的人來說,如果從某處粘貼過來的數據是這樣的:

你的內心一定是:這是什麼鬼?

Trim函數可以輕鬆把單元格內容里單詞之間空格之外的空格去掉。

例如=trim(B4),如果A1單元格里有空格,這個公式會只顯示其中非空格的內容。

2Clean()函數——刪除換行

有時表格中的數據不僅有空格,還有換行,甚至還有一些莫名其妙的亂碼和字元,作為審美標準極高的處女座,這是不能容許的。

Clean函數可以用來刪除文本中不能列印的內容,使你的數據就立馬變得乾淨整齊。比如下面這個函數應用之後,莫名其妙的多行就不見了。

Trim函數和Clean函數有什麼區別呢?

TRIM函數設計用於清除文本中的7位ASCII空格字元(值32)。CLEAN函數設計用於刪除文本中7位ASCII代碼中的前32個非列印字元(值0到31)。

官方解讀,看完不要懷疑人生,因為我也看不懂。

簡單來說,Trim刪除空格,Clean刪除換行。

3Left和Right函數

Left——從文本字元串的開頭返回指定數目的字元

Right——從文本字元串的尾部返回指定數目的字元

這兩個函數語法簡單:Left(A1,5),日常中用的也比較多,但是兩個函數結合使用,你用過么?

舉例:把身份證號中的出生年月提取出來。

思路:先把身份證後12位提取出來,再提前後12位中的前6位。

當然,這個案例可以使用這門的函數一次搞定,這裡只是擴展一種思路:通過簡單函數的組合,也能實現牛逼的功能。

4NETWORKDAYS

項目管理中的神奇!!!

問題:今天是3月13,你們的項目是3月15結束,老闆問你:離項目結束要工作幾天?

答:一天

特么這麼簡單的問題你是在侮辱項目管理人的智商么?

那如果項目是11月11結束呢?你難道真的要對著日曆一天天去數、一月月去算嗎?

干,數完之後老闆就叫你走人了。

Excel里的networkdays函數返回起始日期和結束日期之間完整的工作日數值。工作日不包括周末和專門指定的假期。

語法:

NETWORKDAYS(start_date, end_date, [holidays])

  • Start_date 必需。 一個代表開始日期的日期。

  • End_date 必需。 一個代表終止日期的日期。

  • Holidays 可選。不在工作日曆中的一個或多個日期所構成的可選區域,比如法定假期、公司紀念日等

  • 5WORKDAY函數

    項目遙遙無期,你的老闆突然有一天開始懷疑人生了:這項目特么還要再干250天,這要干到猴年馬月?

    別哭……

    老闆開始懷疑人生的時候,就是你裝逼的時候。

    寫下這個函數,做成Gif,默默發給他。

    那我們來計算一下,恭喜你,再有175天就是光棍節了(項目就結束了)。

    但是這裡返回的常常是個數字,不是Excel算錯了,是你的數字格式有問題,按下Ctrl Shift 3,就能顯示日期!

    WorkDay返回在某日期(起始日期)之前或之後、與該日期相隔指定工作日的某一日期的日期值。 工作日不包括周末和專門指定的假日。

    語法:

    WORKDAY(start_date, days, [holidays])

  • Start_date 必需。 一個代表開始日期的日期。

  • Days 必需。 start_date 之前或之後不含周末及節假日的天數。 Days 為正值將生成未來日期;為負值生成過去日期。

  • Holidays 可選。需要排除的假日等。

  • 6IF函數

    If函數的意思就是「如果」,如果滿足某個條件,就返回一個值,如果不滿足,就返回另一個。

    If函數的語法是這樣的:

    IF(logical_test, value_if_true, [value_if_false])

    其中,logical_test 是測試條件; value_if_true是值如果真(別干我,我英文不好),即這個測試的結果為 TRUE 時希望返回的值;value_if_false是值如果假,即這個測試的結果為False 時希望返回的值。

    註:

    1)希望返回的值,這裡用」希望「這個有感情的詞就是想告訴你,這個值是需要用戶給出的;

    2)[]表示可選。對所有函數均適用。

    比如下面的姑娘顏值計算的表格里,根據數值是是否高於95來判定」女神「或是」非女神「。

    (數值為個人胡編,相關粉絲不要干我)

    個人認為if函數是一個基礎函數,什麼意思呢,就是基於if函數可以延伸出很多功能,比如和其他函數的疊加,比如設置條件格式,比如設置高端的數據有效性。關於這些內容,今後會開專題講解。

    7SUMIF 函數

    這就是IF函數和其他函數結合的一個例子。

    Sum的意思是「求和」,再加上「IF」,意思就是對範圍中符合指定條件的值求和。 例如,請求出顏值在85以上的女神的總顏值是多少?請使用以下公式:

    8SUBTOTAL函數——數值計算全能王

    SUBTOTAL函數其實是個函數組,就是返回一個列表或資料庫中的分類匯總情況。

    SUBTOTAL函數可謂是全能王,可以對數據進行求平均值、計數、最大最小、相乘、標準差、求和、方差。

    可以說,掌握了這個函數,相當於掌握了十幾個函數,是當之無愧的全能王。

    9SUMPRODUCT函數

    這個函數的功能是在給定的幾組數組中,將數組間對應的元素相乘,並返回乘積之和。

    這個函數比較能提現裝逼效果,不會用的人寫出的公式可能是這樣的:

    =(A1*B1 A2*B2 A3*B3 A4*B4 A5*B5 A6*B6 A7*B7 A8*B8 A9*B9)

    會用的人寫出的則是這樣的:

    =SumProduct(A1:A9,B1:B9)

    語法:

    SUMPRODUCT(array1, [array2], [array3], ...)

  • Array1 必需。 其相應元素需要進行相乘並求和的第一個數組參數。

  • Array2, array3,... 可選。 2 到 255 個數組參數,其相應元素需要進行相乘並求和。

  • 比如你女朋友的淘寶購物車裡商品的單價和數量是這樣的,算一下如何剁手吧。

    10SMALL & LARGE 函數

    SMALL函數可以用來找到一串數據中的第n小的值。

    例如SMALL(B2:B20,3)能夠找到B2到B20的範圍內第3小的數字。

    同理,LARGE函數就是用來找最大值的。


    最後壓軸製作還是要對得起標題的。

    畢竟文章這麼長,能看到這裡說明實在是捧場了,下面兩個神級函數,拿走不謝。(這兩個函數,每個都可以寫不止一篇文章,篇幅限制,這裡只做簡要說明)

    11Vlookup函數

    這個函數用處非常廣,但是很多朋友一直無法得要領,今天先進行入門的講解,後續再專門針對此函數詳細分析,保證學會。

    首先先設想一個場景:你手機通信錄中存了幾百個姑娘的電話號碼,突然有一天你老媽給你一份姑娘的名單(從你手機通訊錄中選出來的,100人左右),說:把這些姑娘的號碼選出來,準備相親!

    干,難題來了,你要如何去相完這100個姑娘,不,難題是你要如何快速的把這100位姑娘的電話號碼提取出來。

  • 第一個參數:B3,就是我們想要查找匹配的對象(你需要查找出號碼的那些姑娘);

  • 第二個參數:E:F這兩列,就是我們要在哪裡查找呢(就是你的手機通訊錄),注意,一定要把需要匹配的對象(在這裡是姓名)放在所選擇的第一列(比如本例中你只能從E列開始選擇,不能從D);

  • 第三個參數:2,就是你想返回第二個參數中的第幾列數據,這裡需要返回電話,屬於所選擇的第二列;

  • 第四個參數:0,精確查找;1,模糊查找。

  • 12INDEX MATCH函數

    INDEX MATCH函數堪稱是Excel里的神器,很多人甚至將這個函數的使用熟練程度作為判斷其Excel掌握情況的標準!

    這兩個函數有些複雜,一步一步將一下。

    index(r,n)是一個索引函數,在區域r內,返回第n個單元格的值。

    而 match(a,r,t)是一個匹配函數,返回區域r內與a值匹配的單元格順序位置;

    同樣t=0是為精確匹配,t=1時為模糊匹配。

    通常可以將兩個函數聯合起來進行表間關聯關係查詢,通過match函數找到匹配單元位置號,再用這個位置號,通過index函數找到與匹配值對應的關聯值。

    同樣,我們使用這兩個函數兩匹配一下電話號碼。

    我們來看看到底怎麼算的。

    =INDEX($F$4:$F$38,MATCH(B3,$E$4:$E$38,0))

    這個公式里,MATCH(B3,$E$4:$E$38,0)的意思是在E4到E38的區域里(就是你的通訊錄里)查找和B3(你就是要相親的第一個姑娘)名字嚴格匹配的那一行的序號。

    找到這一行的數字之後。Index函數的功能就是在F4到F38中(就是你通訊錄中對應的所有電話)的區域內找到這一行,並且返回相應的電話號碼。

    這樣你要相親的姑娘的電話就找出來了。

    之所以都加了「$」是為了讓這個區域「絕對引用」,不管我怎麼下拉,這個區域都是固定的。

    分析:INDEX MATCH與Vlookup都能實現給定條件的查找,而且前者比後者要麻煩,為什麼要用前者呢。簡單來看,在本例中,index雖然比Vlookup麻煩,這個Index Match函數,非常適用於查找區域不在首列的情況,且他的靈活性遠不止於此。

    你學會了么?


    避免講大道理,志在創作讓你感受到呼吸心跳的Excel體驗。每一篇都是經驗之談,絕不做教科書式的傳授。關注我們:


    推薦閱讀:

    怎麼做出100分的雞蛋?5招讓你全學會!
    既無神通又不懂圓光術,就必須學會這九種觀察(下)
    學會保鮮魅力不減
    8年被劈腿無數次,她卻說:學會祝福舊愛,才能遇見對的人
    病從腳起,腳底養生,學會這10招,身體沒毛病!

    TAG:函數 | 簡單 | Excel | Excel函數 | 學會 |