七夕來襲，細(xì)數(shù)Excel中六大超甜CP！

?

作者：賦春風(fēng)來源：部落窩教育發(fā)布時(shí)間：2021-08-13 15:24:23點(diǎn)擊：2218

分享到:

收藏收藏人氣：0人

版權(quán)說明：原創(chuàng)作品，禁止轉(zhuǎn)載。

編按：

Hello各位小伙伴~ 明天就是2021年的8月14日農(nóng)歷七月初七，滿街的情侶和鮮花無不在提醒小E明天不只是周六還是七夕情人節(jié)。作為單身貴族的小E痛定思痛，決計(jì)不被這樣的氛圍影響，堅(jiān)強(qiáng)的翻開書提升自我，仔細(xì)看了半天，才從字縫里看出字來，滿書都寫著兩個(gè)大字“單身”！于是我又打開Excel決定提升一下自己的Excel能力，結(jié)果就連這篇教程都寫著“Excel中超甜的六大CP……”。

好的另一半會成就更好的自己，也就是說兩人在一起能起到1+1>2的效果。今天春風(fēng)要在Excel中撒狗糧，跟大家介紹一下Excel中的六對超甜CP，它們組合在一起能夠幫你解決超多的Excel問題，他們分別是查找函數(shù)、日期函數(shù)、字符函數(shù)、數(shù)學(xué)函數(shù)、引用函數(shù)，以及快捷鍵組合。好的，下面就請六大CP閃亮登場。

一、查找函數(shù)CP——INDEX+MATCH函數(shù)

這對CP是出鏡率最高的查詢類函數(shù)。用MATCH函數(shù)來定位查詢值的位置，再用INDEX函數(shù)返回指定區(qū)域中指定位置的內(nèi)容，二者結(jié)合，可以實(shí)現(xiàn)上下左右全方位的查詢。

例子：求第二季度橘子的銷售量。

在C12單元格中輸入“=INDEX(B2:E5,MATCH(A12,A2:A5,0),MATCH(B12,B1:E1,0))”即可得到第二季度橘子的銷售量。

第一個(gè)MATCH函數(shù)，MATCH(A12,A2:A5,0)，返回的是A12單元格在A2：A5中的位置，返回的是數(shù)字2；第二個(gè)MATCH函數(shù)MATCH(B12,B1:E1,0)返回的是B12單元格在B1:E1中的位置，返回的是數(shù)字2。先用MATCH函數(shù)來定位查詢值的位置，接著用INDEX函數(shù)返回B2:E5區(qū)域中指定位置的內(nèi)容，即B2:E5區(qū)域中第2行第2列。

二、日期函數(shù)CP——DAY+EOMONTH函數(shù)

關(guān)于Excel中的日期問題，是很多Exceler都繞不開的話題。介紹一下這對計(jì)算日期問題的最佳拍檔——DAY+EOMONTH日期函數(shù)，用該組合函數(shù)可以計(jì)算指定日期所在月的總天數(shù)。其中，DAY函數(shù)用來根據(jù)日期返回其在一個(gè)月中的天數(shù)。EOMONTH函數(shù)用來返回指定日期之前或之后某個(gè)月的最后一天的日期，該函數(shù)的語法結(jié)構(gòu)為：=EOMONTH(開始日期，指定起始日期前后的月份)。

例子：求某一天的當(dāng)月天數(shù)。

在B2單元格輸入“=DAY(EOMONTH(A2,0))”，其中，EOMONTH(A2,0)部分返回當(dāng)前日期所在月份最后一天的日期“2021/1/31”，然后用函數(shù)DAY返回日期“2021/1/31”的天數(shù)31。

其實(shí)，常用的日期類函數(shù)其實(shí)并不多，也都不難，難點(diǎn)在于將具體問題分析明白并且找到適用的函數(shù)組CP。根據(jù)不同問題所涉及的計(jì)算規(guī)則利用一些數(shù)學(xué)計(jì)算的思路就能得到正確的結(jié)果。

三、文本函數(shù)CP——RIGHT+LEN+LENB函數(shù)

由于慣例、系統(tǒng)設(shè)定等諸多原因，混合文本不可避免。于是，混合文本提取數(shù)字，成了很多Excel用戶必須面對的問題。該組合函數(shù)能從混合文本中提取數(shù)字。

例子：從A列混合文本中中提取聯(lián)系電話到B列。

分析A列數(shù)據(jù)，不含英文及其他字符，數(shù)字統(tǒng)一位于文本最右側(cè)，可以直接用RIGHT函數(shù)提取，無需確認(rèn)起始位置。

在B2單元格輸入“=RIGHT(A2,2*LEN(A2)-LENB(A2))”，即可提取數(shù)字B列。

本例中，唯一需要計(jì)算的參數(shù)就是文本長度，這里由于混合文本不含單字節(jié)字符(英文字符或半角符號)，我們可以使用LEN和LENB來確定數(shù)字長度。其中，LEN計(jì)算總字符數(shù)，LENB計(jì)算總字節(jié)數(shù).由于1個(gè)漢字=1個(gè)字符=2個(gè)字節(jié)，1個(gè)單字節(jié)字符=1個(gè)字符=1個(gè)字節(jié)，于是我們可以用2*LEN-LENB來計(jì)算數(shù)字的長度，從而完成提取。

四、引用函數(shù)CP——INDIRECT函數(shù)+“&”連接符

在某些公式中需要引用的單元格并不確定，可使用文本來“描述”單元格，并最終將以文本表示的單元格地址轉(zhuǎn)換為公式可引用的單元格地址，此時(shí)可以使用INDIRECT()函數(shù)來完成。INDIRECT函數(shù)經(jīng)常會與“&”共同應(yīng)用，使其變成具有引用樣式的文本字符串。

其語法結(jié)構(gòu)：INDIRECT(以文本形式表示的對單元格的引用,邏輯值)。如果邏輯值為TRUE或省略，引用被解釋為A1樣式的引用。如果邏輯值為FALSE，引用被解釋為R1C1樣式的引用。

例子：數(shù)據(jù)匯總

某公司每個(gè)季度都會制作一張銷售表，年終需要將這些表格中數(shù)據(jù)匯總到一張表中。需要從4張表中引用數(shù)據(jù)。觀察表格的結(jié)構(gòu)可以發(fā)現(xiàn)，每張工作表的名稱都在匯總的表格中列舉出來，可以使用INDIRECT()函數(shù)完成數(shù)據(jù)引用。

具體步驟：

在“匯總”工作表中的C4單元格輸入公式“=INDIRECT(C$3&"!C"&ROW()-1,TRUE)”，向右拖動單元格右下角的自動填充柄至F4單元格，向下無格式填充公式至7行，引用所有數(shù)據(jù)。

公式先獲取C3單元格的值（第一季度），這個(gè)第一季度和工作表的名稱（第一季度）相同，既要引用的工作表名稱，然后通過連接符連接“！C”文本，引用工作表通常使用感嘆號連接，并且引用的單元格需要加上雙引號,表示引用C3單元格表示的工作表中C列內(nèi)容，最后通過“ROW()-1”取得行號，即目標(biāo)工作表中當(dāng)前行號上一行的內(nèi)容，最后通過INDIRECT()函數(shù)將連接起來的文本轉(zhuǎn)換為A1引用樣式引用的單元格。

五、數(shù)學(xué)函數(shù)CP——SUM+SUMIF函數(shù)

這對求和函數(shù)中的CP各有各的作用，但是，把它們組合在一起，能起到化繁為簡的作用。

例子：求郭靖、黃蓉、楊康的消費(fèi)總額。

我們可以用SUMIF函數(shù)進(jìn)行條件求和，公式非常繁瑣，具體方法為在E2單元格輸入“=SUMIF(B2:B21,B2,C2:C21)+SUMIF(B2:B21,B3,C2:C21)+SUMIF(B2:B21,B4,C2:C21)”。就是分別在B列中找到郭靖、黃蓉、楊康，再在C列中找到對應(yīng)的消費(fèi)額，再求和。

但是，如果讓SUM+SUMIF函數(shù)這對CP亮相，復(fù)雜的問題就迎刃而解。具體方法為在E2單元格輸入“=SUM(SUMIF(B2:B21,B2:B4,C2:C21))”，然后按“Ctrl+Shift+Enter”組合鍵結(jié)束公式（這個(gè)很重要！），Excel會自動給公式最外邊加上“{}”用于和普通公式區(qū)別開來。這就是數(shù)組的形式，數(shù)組公式與普通公式不同，普通公式只占用一個(gè)單元格，只返回一個(gè)結(jié)果。而數(shù)組公式可以占用一個(gè)單元格，也可以占用多個(gè)單元格，且它對一組數(shù)或多組數(shù)進(jìn)行多重計(jì)算，并返回一個(gè)或多個(gè)結(jié)果。