|
干貨預(yù)警，全文18888字，閱讀需要用時(shí)10分鐘，建議先點(diǎn)贊收藏評(píng)論。 作為一名Excel愛(ài)好者，覺(jué)得想快速提升Excel水平，就要掌握Excel的核心功能：基本菜單功能、Excel函數(shù)、數(shù)據(jù)透視表、可視化圖表等四大方面內(nèi)容。 我剛學(xué)Excel讓我感動(dòng)效率有明顯提升就是Excel函數(shù)，從接觸數(shù)據(jù)透視表后，我的效率提升更明顯。 我們這里就單單從Excel函數(shù)講起，作為拋磚引玉、以小見(jiàn)大。Excel函數(shù)多達(dá)400多個(gè)，我們不可能掌握每一個(gè)函數(shù)，只需要掌握常用的四大函數(shù)：SUM、IF、SUMIF、VLOOKUP和數(shù)據(jù)透視表，即可解決大多數(shù)問(wèn)題。 一、認(rèn)識(shí)Excel函數(shù)的作用    關(guān)于Excel函數(shù)有470多個(gè)，不可能全部都學(xué)習(xí)。我們只需要掌握好Excel核心知識(shí)，能熟練處理問(wèn)題，就能成為別人眼中的Excel高手。 二、理解Excel函數(shù)構(gòu)造可能許多同學(xué)不太了解函數(shù)的構(gòu)造，一起來(lái)看看Excel函數(shù)在使用時(shí)有哪些規(guī)范，首先我們看一下標(biāo)點(diǎn)符號(hào)的使用規(guī)范：  在函數(shù)里面的標(biāo)點(diǎn)要使用英文輸入法下的標(biāo)點(diǎn)，在Excel上，SUM的參數(shù)個(gè)數(shù)是不固定的，區(qū)域和數(shù)值的參數(shù)也能混合使用。  接下來(lái)，我們簡(jiǎn)單認(rèn)識(shí)一下幾個(gè)高頻函數(shù)。 一、SUM函數(shù)
含義：  為了方便大家理解SUM函數(shù)的含義，特意制作了一個(gè)小案例，希望能幫助大家理解這2個(gè)含義： ①number參數(shù)可以寫(xiě)成，具體的數(shù)值，單元格，或者單元格區(qū)域 ②參數(shù)內(nèi)有空白單元格、邏輯值、文本將被忽略  SUM函數(shù)看似簡(jiǎn)單，其實(shí)在使用時(shí)有許多技巧的，今天就通過(guò)具體的栗子，帶大家認(rèn)識(shí)：快捷求和，隔行求和，累計(jì)求和，乘積求和，多表求和 1.快捷求和【ALT+=】 日常遇到規(guī)范的多個(gè)單元格求和，我們可以直接選中數(shù)據(jù)區(qū)域，按下快捷鍵【ALT+=】，直接實(shí)現(xiàn)求和。  2.隔行求和 求多個(gè)的不連續(xù)單元格和，利用【Ctrl+G】先定位【空值】，然后按下快捷鍵【ALT+=】，這樣提高效率非常明顯，記得找機(jī)會(huì)在同事眼前露一手。  3.累計(jì)求和 一些情況下，我們需要用到累計(jì)求和。其實(shí)很簡(jiǎn)單把開(kāi)頭引用的單元格固定，區(qū)域下方單元格相對(duì)引用即可。 公式：=SUM($C$1:C2)  4.乘積求和 買東西算錢時(shí)候，我們經(jīng)常用到乘積求和計(jì)算。像這種【單價(jià)】*【銷量】，直接利用乘積求和，算出總銷售額。結(jié)束記得是【Ctrl+shift+Enter】三鍵結(jié)尾，不能直接按Enter。 公式：=SUM(C2:C10*D2:D10) 5.跨表格求和 在統(tǒng)計(jì)商品銷售時(shí)，每個(gè)月數(shù)據(jù)分別在不同的工作表，我們就可以利用跨表格求和。值得注意的時(shí)，這種表格城市的順序一定是要一致的，即所有工作表城市順序一定是北京→上?！鷱V州→深圳→杭州。 公式：=SUM('1月:3月'!B2) 關(guān)于SUM函數(shù)就介紹到這里，除此之外IF函數(shù)也格外重要。 二、IF函數(shù)語(yǔ)法： ①判斷條件：可以為具體的判斷公式，或者具體某個(gè)單元格值，TRUE和非0的數(shù)字均判斷成立 ②成立時(shí)：可以返回一個(gè)公式，具體的數(shù)值，或者名稱（需要用“”引著名稱） ③不成立時(shí)：可以返回一個(gè)公式，具體的數(shù)值，或者名稱（需要用“”引著名稱） 關(guān)于判斷條件，許多人以為一定是等式或者不等式才能用作判斷公式，接下來(lái)通過(guò)案例證明：TRUE和非0的數(shù)字均判斷成立 1.條件成立 在單元格值為TRUE、非0的數(shù)值，小數(shù)、負(fù)數(shù)時(shí)，條件均為成立，返回結(jié)果為1 2.條件不成立 條件為FALSE，0值，或者空值時(shí)，條件判斷不成立，根據(jù)函數(shù)會(huì)返回為0 如果是使用其他英文或中文名稱，系統(tǒng)會(huì)無(wú)法識(shí)別顯示報(bào)錯(cuò)。 3.單條件判斷 輸入公式：=IF(C2>=6,'帥','一般') 1.顏值大于6為帥，否則為一般 2.名稱詞語(yǔ)需要用''引著 4.IF多級(jí)嵌套 要把學(xué)生的成績(jī)分類，總共有：優(yōu)、良、中、差，四個(gè)等級(jí)分類。 =IF(C2>=85,'優(yōu)',IF(C2>=70,'良',IF(C2>=60,'中','差'))) 許多人看到這么長(zhǎng)的公式就覺(jué)得很怕，其實(shí)我們把公式拆分就是每一個(gè)IF函數(shù)理解即可。成績(jī)的值首先經(jīng)過(guò)第一層判斷。 一層嵌套： 如果在第一層判斷就成立了，直接輸出結(jié)果【優(yōu)】，否則進(jìn)入第二層判斷。 第二層嵌套： 數(shù)值滿足第二個(gè)條件，直接輸出結(jié)果【良】，否則進(jìn)入第三層判斷。 第三層嵌套： 這就是我們最熟悉的IF函數(shù)，條件成立輸出【中】，不成立輸出【差】 此處注意的是，輸出【中】的值不是單單【>=60】，還要滿足不滿足上面【>=70】條件。所以該值范圍在（70>x>=60） 三、SUMIF函數(shù)SUMIF函數(shù)由SUM和IF組成，SUM代表求和，IF代表?xiàng)l件判斷，從字面意思了解到這是條件求和。 語(yǔ)法： 在公式中，最多要求和變化就是【求和條件】，可以細(xì)分為5種情況，一起看看有哪些類型。 1.可以直接輸入具體的數(shù)字，如32 公式： =SUMIF(B2:B10,32,C2:C10) 2.文本需要用引號(hào)括著，如'北京' =SUMIF(A2:A10,'北京',C2:C10) 3.邏輯判斷條件需要也需要引號(hào)，如'>35' =SUMIF(B2:B10,'>35',C2:C10) 4.單元格，如，A2 公式: =SUMIF($A$2:$A$10,E2,$C$2:$C$10) 當(dāng)需要求出多個(gè)數(shù)值，用到多個(gè)【條件】時(shí)，我們把對(duì)應(yīng)的條件寫(xiě)在單元格上。 5.條件還可以使用通配符：?jiǎn)柼?hào)(?)和星號(hào)(*) 公式：=SUMIF(A:A,'*州',C:C) 能求出廣州、杭州、蘇州三個(gè)城市的和，【*】星號(hào)匹配任意一串字符。 1.求出多行結(jié)果 利用求和條件的變化，可以求出多行不同條件的結(jié)果。①條件區(qū)域和③求和區(qū)域均設(shè)置絕對(duì)引用，②設(shè)置相對(duì)引用。 根據(jù)例子，輸入公式后，往下填充即可得出多個(gè)【城市】的數(shù)值結(jié)果。 我們看到公式很長(zhǎng)，其實(shí)我們可以通過(guò)簡(jiǎn)化①與③的區(qū)域，讓公式看起來(lái)更簡(jiǎn)潔。 2.求出多列結(jié)果 除了求出多個(gè)【城市】數(shù)值和，還能求出一個(gè)【城市】的多個(gè)【指標(biāo)】，這需要我們往右列填充。 把【求和區(qū)域】改為相對(duì)引用，拖動(dòng)填充可求出【年齡】和【數(shù)值】2個(gè)指標(biāo)，如有更多指標(biāo)均可求出。 如果需要用到更多的條件，可以利用SUMIFS函數(shù)來(lái)求和。 四、VLOOKUP函數(shù)VLOOKUP函數(shù)稱呼為函數(shù)之王，可見(jiàn)它的地位有多高。首先我們看一下vlookup函數(shù)的含義，就是這么一串英文，不用急著弄明白其中的意思，大概知道它有4個(gè)參數(shù)就可以。 第一次看到的人，看不太明白其中的意思，下面我們通過(guò)具體的例子來(lái)理解 1.正向查找 例子，我們要在成績(jī)單中找到喜洋洋、哆啦A夢(mèng)、大雄的段位分別是多少？ 這時(shí)候我們利用VLOOKUP正向查找即可，在段位處填入的公式為： =VLOOKUP(J3,$B$2:$F$94,3,0) 發(fā)現(xiàn)一：②查找范圍，要根據(jù)①用誰(shuí)找作為，查找范圍的第一列。 發(fā)現(xiàn)二：返回列數(shù)是以②查找范圍（紅色區(qū)域）作為參考系，而不是整個(gè)表格作為參考系。【段位】在查找范圍第3列，所以返回【3】 喜洋洋、哆啦A夢(mèng)、大雄的段位分別是黃金、白金、黃金。 2.查找多列 例如現(xiàn)在我們要找喜洋洋的段位，數(shù)學(xué)、語(yǔ)文成績(jī)?nèi)龢訓(xùn)|西 公式為： =VLOOKUP($J3,$B$2:$F$94,COLUMN(C1),0) 對(duì)比查找單列和查找多列的公式 查找單列 查找多列 發(fā)現(xiàn)一：①用誰(shuí)找的J3變成了$J3，固定引用J列 發(fā)現(xiàn)二：③返回第幾列，由固定的3變成相對(duì)引用COLUMN(C1)，當(dāng)我們往右拖動(dòng)填充時(shí)，里面的C1變成了D1，E1。 在函數(shù)中，COLUMN(C1)=3，COLUMN(D1)=4，COLUMN(E3)=5，COLUMN函數(shù)只與列有關(guān)，與行無(wú)關(guān)。 3.逆向查找 例：我們現(xiàn)在要查找哆啦A夢(mèng)的學(xué)號(hào) 逆向查找表達(dá)式： =vlookup(查找值，IF（{1,0}，查找值所在列，結(jié)果值所在列）,2,0） 正常表達(dá)式： 逆向查找表達(dá)式： 逆向查找其實(shí)是構(gòu)造出新的查找區(qū)域↑ 通過(guò)對(duì)比上面的2個(gè)公式發(fā)現(xiàn)，主要是②在哪里查找發(fā)生了變化，由原來(lái)的區(qū)域，變成了IF函數(shù)嵌套。我們可以理解為紅色部分的IF函數(shù)，重新構(gòu)造了②查找區(qū)域。 重構(gòu)的②查找區(qū)域存在代碼中，不真正顯示在單元格上 重構(gòu)后的查找區(qū)域只有2列，所以返回列數(shù)為2，匹配類型：【0】絕對(duì)匹配。 延伸閱讀在工作時(shí)，我們可以直接構(gòu)造出輔助列在數(shù)據(jù)最后一列，這樣可以沿用原來(lái)的正向查找的方法，可以用隱藏或填充顏色把【輔助列】隱藏掉。所以我們要學(xué)會(huì)靈活多變。 關(guān)于函數(shù)的先聊到這里，相信大得多數(shù)同學(xué)都能做到舉一反三，遇到不會(huì)的函數(shù)也能通過(guò)搜索快速掌握并引用在工作上。 想學(xué)習(xí)更多知識(shí)，和獲取練習(xí)文件，別忘了關(guān)注我? |
|
|
