繼續(xù)送書！今天送3本《Excel 跟盧子一起學(xué) 早做完，不加班》，從留言區(qū)隨機(jī)抽獎(jiǎng)。

全套總共90個(gè)案例，超級(jí)全面。堅(jiān)持看完，你的水平肯定會(huì)更上一層樓。如果點(diǎn)贊數(shù)超過100個(gè)，明天繼續(xù)分享。

第一批：花了1年時(shí)間整理的90個(gè)公式，學(xué)完你就能碾壓99%的同事

第二批：第二批！花了1年時(shí)間整理的90個(gè)公式，學(xué)完你就能碾壓99%的同事

第三批：Excel中最牛的查找函數(shù)是VLOOKUP、LOOKUP，還是SUMIFS，誰最厲害？

1.計(jì)算文本表達(dá)式的和

在統(tǒng)計(jì)數(shù)據(jù)的時(shí)候忘記輸入=號(hào)，如何計(jì)算這些文本表達(dá)式的結(jié)果呢？

STEP 01 單擊B2單元格，再單擊“公式”選項(xiàng)卡的“定義名稱”圖標(biāo)，在彈出的“新建名稱”對話框，如果名稱：表達(dá)式，引用位置為下面的公式，單擊“確定”按鈕。

=EVALUATE(A2)

STEP 02 在B2單元格輸入公式，并向下復(fù)制。

原理分析

EVALUATE屬于宏表函數(shù)。宏表函數(shù)是早期低版本Excel中使用的，現(xiàn)在已由VBA頂替它的功能。但仍可以在工作表中使用，不過要特別注意的是：不能直接在單元格中、只能在“定義的名稱”中使用。

EVALUATE函數(shù)語法如下：

EVALUATE(表達(dá)式)

對以文字表示的一個(gè)公式或表達(dá)式求值，并返回結(jié)果。

在使用宏表函數(shù)或者VBA的時(shí)候，必須將工作簿另存為：Excel 啟用宏的工作簿，否則功能會(huì)失效。

補(bǔ)充說明，在WPS表格中，可以直接使用EVALUATE得到結(jié)果。

=EVALUATE(A2)

2.對帶顏色的項(xiàng)目進(jìn)行求和

我們在使用Excel時(shí)，會(huì)遇到這樣的情況：一個(gè)工作表中某些單元格填充為某種顏色，要求將填充了某種顏色的單元格進(jìn)行快速求和?，F(xiàn)在保價(jià)金額被分別填充成紅色跟綠色背景色，如何分別對這兩種背景色進(jìn)行求和？

STEP 01  單擊G2單元格，再單擊“公式”選項(xiàng)卡的“定義名稱”圖標(biāo)，在彈出的“新建名稱”對話框，如果名稱：顏色，引用位置為下面的公式，單擊“確定”按鈕。

=GET.CELL(63,D2)

STEP 02 在G2單元格輸入公式，并向下復(fù)制。

=顏色

STEP 03 將顏色產(chǎn)生的數(shù)字依次填入H2跟H3。

STEP 04 在I2單元格輸入公式，并向下復(fù)制。

=SUMIF(G:G,H2,D:D)

STEP 05 將工作簿另存為：Excel 啟用宏的工作簿。

原理分析

SUMIF函數(shù)雖然可以進(jìn)行條件求和，但不知直接對顏色進(jìn)行條件求和。需要借助宏表函數(shù)GET.CELL獲取背景色對應(yīng)的數(shù)字，然后才能求和。

GET.CELL函數(shù)中的參數(shù)“63”的意思是：單元格填充顏色(背景)編碼數(shù)字。

3.對產(chǎn)品進(jìn)行統(tǒng)計(jì)并引用自定義數(shù)字格式

產(chǎn)品從不同國家購買，因此使用的貨幣格式不一樣，如果對產(chǎn)品進(jìn)行條件求和， 并引用B列原有的貨幣格式？

STEP 01  單擊E2單元格，再單擊“公式”選項(xiàng)卡的“定義名稱”圖標(biāo)，在彈出的“新建名稱”對話框，如果名稱：格式，引用位置為下面的公式，單擊“確定”按鈕。

=GET.CELL(7,INDEX(Sheet1!$B:$B,MATCH(Sheet1!$D2,Sheet1!$A:$A,0)))

STEP 02 在E2單元格輸入公式，并向下復(fù)制。

=TEXT(SUMIF(A:A,D2,B:B),格式)

STEP 03 將工作簿另存為：Excel 啟用宏的工作簿。

原理分析

在名稱“格式”中，使用INDEX函數(shù)與MATCH函數(shù)配合，查詢D2在A列對應(yīng)的值，并返回B列的單元格引用。使用GET.CELL函數(shù)，取得INDEX返回單元格的數(shù)字格式。

使用TEXT函數(shù)將SUMIF函數(shù)求得的和返回為“格式”的格式，“格式”是指原理B列對應(yīng)的格式。

GET.CELL函數(shù)中的參數(shù)“7”的意思是：用于返回單元格的數(shù)字格式。

4.動(dòng)態(tài)統(tǒng)計(jì)金額

這是一份每天出差花費(fèi)清單，經(jīng)常要在總金額上面插入行。用SUM函數(shù)直接統(tǒng)計(jì)有時(shí)不會(huì)對新增加的金額進(jìn)行統(tǒng)計(jì)，該如何處理？

STEP 01 單擊C18單元格，再單擊“公式” 選項(xiàng)卡的“定義名稱”圖標(biāo)，在彈出的“新建名稱”對話框，如果名稱：上一行，引用位置為下面的公式，單擊“確定”按鈕。

=C17

STEP 02 在C18單元格輸入公式。

=SUM(C2:上一行) 

原理分析

這個(gè)“上一行”的引用為相對引用，每插入一行引用位置就會(huì)動(dòng)態(tài)變化，所以插入行也會(huì)自動(dòng)匯總進(jìn)去。

有人試過用SUM(區(qū)域)就能自動(dòng)擴(kuò)展區(qū)域，統(tǒng)計(jì)正確就認(rèn)為任何情況下都可以，但實(shí)際上某些情況下還是不會(huì)自動(dòng)擴(kuò)展區(qū)域。幾年前我吃過一個(gè)虧，有一天驗(yàn)證金額的時(shí)候，發(fā)現(xiàn)問題，金額最后一行沒有統(tǒng)計(jì)，也就是少統(tǒng)計(jì)一個(gè)產(chǎn)品的金額。

小心駛得萬年船，寧愿麻煩一點(diǎn)，也要保證數(shù)據(jù)的準(zhǔn)確性。

5.包含單位的金額求和

這是一份每天出差花費(fèi)清單，在輸入金額的時(shí)候在后面輸入單位，導(dǎo)致用SUM函數(shù)直接求和得不到正確答案，怎么樣才能讓含有單位的金額可以求和呢？

輸入公式，按Ctrl+Shift+Enter三鍵結(jié)束。

=SUM(--SUBSTITUTE(C2:C17,"元",""))

原理分析

因?yàn)榘瑔挝辉?，需要將單位去除掉才能求和。用SUBSTITUTE函數(shù)將元替換成空文本，也就是只提取數(shù)字。

SUBSTITUTE函數(shù)屬于文本函數(shù)，所以得到的數(shù)字也屬于文本， 這里叫做文本數(shù)字。數(shù)字有兩種類型，一種是文本數(shù)字，一種是真正的數(shù)字，就是數(shù)值。數(shù)值可以直接求和，而文本不能求和。如賬簿上的數(shù)字跟墻上的數(shù)字是不同，前者我們可以用這些數(shù)字進(jìn)行各種分析，后者只能當(dāng)欣賞用。

那有什么辦法還原數(shù)字的本質(zhì)呢？

把文本型轉(zhuǎn)換成數(shù)值型，有專用的轉(zhuǎn)換函數(shù) VALUE。

=VALUE("25")，它的結(jié)果就是一個(gè)數(shù)值。

=VALUE("25")=25，它的結(jié)果就是TRUE了。

在函數(shù)或公式中，運(yùn)算過程會(huì)自動(dòng)把文本轉(zhuǎn)換為數(shù)值(一個(gè)隱含過程)，再與數(shù)值進(jìn)行運(yùn)  算，負(fù)值運(yùn)算(-)也是一種運(yùn)算，能把文本轉(zhuǎn)換成數(shù)值：

-"25"=-25

還記得負(fù)負(fù)得正吧？

-(-"25")=-(-25)=25

簡寫為：

--"25"=25

--可以把文本轉(zhuǎn)換為數(shù)值，但它不是標(biāo)準(zhǔn)的轉(zhuǎn)換方式，是借用負(fù)運(yùn)算的隱含功能。

6.含姓名求總金額

金額跟姓名混合在一起，這樣的金額又該如何統(tǒng)計(jì)呢？

輸入公式，按Ctrl+Shift+Enter三鍵結(jié)束。

=SUM(--RIGHT(A2:A14,2*LEN(A2:A14)-LENB(A2:A14)))

原理分析

因?yàn)樾彰痤~的字符數(shù)都不確定，所以不能直接提取金額出來。不過可以利用漢字是雙字節(jié)，數(shù)字是單字節(jié)的特點(diǎn)來提取金額。

金額在右邊可以用RIGHT函數(shù)，漢字的個(gè)數(shù)就是，總字節(jié)減去總字符數(shù)，而數(shù)字的個(gè)數(shù)，就是總字符數(shù)減去漢字的字符數(shù)，也就是：

=LEN(A2)-文字?jǐn)?shù)=LEN(A2)-(LENB(A2)-LEN(A2))=2*LEN(A2)-LENB(A2)

提取出來的數(shù)字都是文本數(shù)字，不能夠直接求和，需要要--將文本數(shù)字轉(zhuǎn)換成真正的數(shù)字才可以求和。

當(dāng)然轉(zhuǎn)換成數(shù)字也可以用1*、0+、/1、-0等方法，只要讓文本數(shù)字運(yùn)算即可。

7.含錯(cuò)誤值求總數(shù)量

數(shù)量是從別的地方引用過來，導(dǎo)致有部分?jǐn)?shù)據(jù)是錯(cuò)誤值，如何避開這些錯(cuò)誤值進(jìn)行求和呢？

=SUMIF(C:C,"<9E+307")

原原理分析

Excel允許的最大數(shù)值是15位，9E+307是比最大數(shù)值還大的值，條件<9E+307就是包含所有數(shù)字，這樣就可以排除錯(cuò)誤值求和。數(shù)量中包含邏輯值、文本這些也照樣可以用這種方法， 因?yàn)樽畲蟮臄?shù)字都比邏輯值、文本值、錯(cuò)誤值還小。

8.根據(jù)姓氏統(tǒng)計(jì)產(chǎn)量

明細(xì)表記錄了每個(gè)操作人員的產(chǎn)量，如何根據(jù)姓氏統(tǒng)計(jì)產(chǎn)量？比如張，就是姓名第一個(gè)字是張的所有人員。

=SUMIF(A:A,D2&"*",B:B)

原理分析

D2&"*"就是以D2開頭的作為條件統(tǒng)計(jì)，*是通配符，代表全部。

9.統(tǒng)計(jì)銷量前5名的和

明細(xì)表記錄著每個(gè)人的銷售量，如何統(tǒng)計(jì)銷量前5名的和？

輸入公式，按Ctrl+Shift+Enter三鍵結(jié)束。

=SUM(LARGE(B2:B17,ROW(1:5)))

原理分析

最大值用MAX函數(shù)，前幾大用LARGE函數(shù)，函數(shù)語法如下：

LARGE(區(qū)域,N)

比如第2大，N就是2?，F(xiàn)在要前5名，也就是ROW(1:5)。