别找了，你要的Excel求和問題妙解都在這裡了
繼續送書！今天送3本《Excel 跟盧子一起學 早做完，不加班》，從留言區随機抽獎。
全套總共90個案例，超級全面。堅持看完，你的水平肯定會更上一層樓。如果點贊數超過100個，明天繼續分享。
第一批：花了1年時間整理的90個公式，學完你就能碾壓99%的同事
第二批：第二批！花了1年時間整理的90個公式，學完你就能碾壓99%的同事
第三批：Excel中最牛的查找函數是VLOOKUP、LOOKUP，還是SUMIFS，誰最厲害？
1.計算文本表達式的和
在統計數據的時候忘記輸入=号，如何計算這些文本表達式的結果呢？
STEP01 單擊B2單元格，再單擊“公式”選項卡的“定義名稱”圖标，在彈出的“新建名稱”對話框，如果名稱：表達式，引用位置為下面的公式，單擊“确定”按鈕。
=EVALUATE(A2)STEP 02 在B2單元格輸入公式，并向下複制。
原理分析EVALUATE屬于宏表函數。宏表函數是早期低版本Excel中使用的，現在已由VBA頂替它的功能。但仍可以在工作表中使用，不過要特别注意的是：不能直接在單元格中、隻能在“定義的名稱”中使用。
EVALUATE函數語法如下：
EVALUATE(表達式)對以文字表示的一個公式或表達式求值，并返回結果。
在使用宏表函數或者VBA的時候，必須将工作簿另存為：Excel 啟用宏的工作簿，否則功能會失效。
補充說明，在WPS表格中，可以直接使用EVALUATE得到結果。
=EVALUATE(A2)
2.對帶顔色的項目進行求和我們在使用Excel時，會遇到這樣的情況：一個工作表中某些單元格填充為某種顔色，要求将填充了某種顔色的單元格進行快速求和。現在保價金額被分别填充成紅色跟綠色背景色，如何分别對這兩種背景色進行求和？
STEP 01 單擊G2單元格，再單擊“公式”選項卡的“定義名稱”圖标，在彈出的“新建名稱”對話框，如果名稱：顔色，引用位置為下面的公式，單擊“确定”按鈕。
=GET.CELL(63,D2)STEP 02 在G2單元格輸入公式，并向下複制。
=顔色STEP 03 将顔色産生的數字依次填入H2跟H3。
STEP 04 在I2單元格輸入公式，并向下複制。
=SUMIF(G:G,H2,D:D)STEP 05 将工作簿另存為：Excel 啟用宏的工作簿。
原理分析SUMIF函數雖然可以進行條件求和，但不知直接對顔色進行條件求和。需要借助宏表函數GET.CELL獲取背景色對應的數字，然後才能求和。
GET.CELL函數中的參數“63”的意思是：單元格填充顔色(背景)編碼數字。
3.對産品進行統計并引用自定義數字格式産品從不同國家購買，因此使用的貨币格式不一樣，如果對産品進行條件求和， 并引用B列原有的貨币格式？
STEP 01 單擊E2單元格，再單擊“公式”選項卡的“定義名稱”圖标，在彈出的“新建名稱”對話框，如果名稱：格式，引用位置為下面的公式，單擊“确定”按鈕。
=GET.CELL(7,INDEX(Sheet1!$B:$B,MATCH(Sheet1!$D2,Sheet1!$A:$A,0)))STEP 02 在E2單元格輸入公式，并向下複制。
=TEXT(SUMIF(A:A,D2,B:B),格式)STEP 03 将工作簿另存為：Excel 啟用宏的工作簿。
原理分析在名稱“格式”中，使用INDEX函數與MATCH函數配合，查詢D2在A列對應的值，并返回B列的單元格引用。使用GET.CELL函數，取得INDEX返回單元格的數字格式。
使用TEXT函數将SUMIF函數求得的和返回為“格式”的格式，“格式”是指原理B列對應的格式。
GET.CELL函數中的參數“7”的意思是：用于返回單元格的數字格式。
4.動态統計金額這是一份每天出差花費清單，經常要在總金額上面插入行。用SUM函數直接統計有時不會對新增加的金額進行統計，該如何處理？
STEP 01 單擊C18單元格，再單擊“公式” 選項卡的“定義名稱”圖标，在彈出的“新建名稱”對話框，如果名稱：上一行，引用位置為下面的公式，單擊“确定”按鈕。
=C17STEP 02 在C18單元格輸入公式。
=SUM(C2:上一行) 原理分析這個“上一行”的引用為相對引用，每插入一行引用位置就會動态變化，所以插入行也會自動彙總進去。
有人試過用SUM(區域)就能自動擴展區域，統計正确就認為任何情況下都可以，但實際上某些情況下還是不會自動擴展區域。幾年前我吃過一個虧，有一天驗證金額的時候，發現問題，金額最後一行沒有統計，也就是少統計一個産品的金額。
小心駛得萬年船，甯願麻煩一點，也要保證數據的準确性。
5.包含單位的金額求和這是一份每天出差花費清單，在輸入金額的時候在後面輸入單位，導緻用SUM函數直接求和得不到正确答案，怎麼樣才能讓含有單位的金額可以求和呢？
輸入公式，按Ctrl+Shift+Enter三鍵結束。
=SUM(--SUBSTITUTE(C2:C17,"元",""))
原理分析因為包含單位元，需要将單位去除掉才能求和。用SUBSTITUTE函數将元替換成空文本，也就是隻提取數字。
SUBSTITUTE函數屬于文本函數，所以得到的數字也屬于文本， 這裡叫做文本數字。數字有兩種類型，一種是文本數字，一種是真正的數字，就是數值。數值可以直接求和，而文本不能求和。如賬簿上的數字跟牆上的數字是不同，前者我們可以用這些數字進行各種分析，後者隻能當欣賞用。
那有什麼辦法還原數字的本質呢？
把文本型轉換成數值型，有專用的轉換函數 VALUE。
=VALUE("25")，它的結果就是一個數值。
=VALUE("25")=25，它的結果就是TRUE了。
在函數或公式中，運算過程會自動把文本轉換為數值(一個隐含過程)，再與數值進行運 算，負值運算(-)也是一種運算，能把文本轉換成數值：
-"25"=-25
還記得負負得正吧？
-(-"25")=-(-25)=25
簡寫為：
--"25"=25
--可以把文本轉換為數值，但它不是标準的轉換方式，是借用負運算的隐含功能。
6.含姓名求總金額金額跟姓名混合在一起，這樣的金額又該如何統計呢？
輸入公式，按Ctrl+Shift+Enter三鍵結束。
=SUM(--RIGHT(A2:A14,2*LEN(A2:A14)-LENB(A2:A14)))
原理分析因為姓名跟金額的字符數都不确定，所以不能直接提取金額出來。不過可以利用漢字是雙字節，數字是單字節的特點來提取金額。
金額在右邊可以用RIGHT函數，漢字的個數就是，總字節減去總字符數，而數字的個數，就是總字符數減去漢字的字符數，也就是：
=LEN(A2)-文字數=LEN(A2)-(LENB(A2)-LEN(A2))=2*LEN(A2)-LENB(A2)提取出來的數字都是文本數字，不能夠直接求和，需要要--将文本數字轉換成真正的數字才可以求和。
當然轉換成數字也可以用1*、0+、/1、-0等方法，隻要讓文本數字運算即可。
7.含錯誤值求總數量數量是從别的地方引用過來，導緻有部分數據是錯誤值，如何避開這些錯誤值進行求和呢？
=SUMIF(C:C,"<9E+307")原原理分析
Excel允許的最大數值是15位，9E+307是比最大數值還大的值，條件<9E+307就是包含所有數字，這樣就可以排除錯誤值求和。數量中包含邏輯值、文本這些也照樣可以用這種方法，因為最大的數字都比邏輯值、文本值、錯誤值還小。
8.根據姓氏統計産量明細表記錄了每個操作人員的産量，如何根據姓氏統計産量？比如張，就是姓名第一個字是張的所有人員。
=SUMIF(A:A,D2&"*",B:B)
原理分析D2&"*"就是以D2開頭的作為條件統計，*是通配符，代表全部。
9.統計銷量前5名的和明細表記錄着每個人的銷售量，如何統計銷量前5名的和？
輸入公式，按Ctrl+Shift+Enter三鍵結束。
=SUM(LARGE(B2:B17,ROW(1:5)))
原理分析最大值用MAX函數，前幾大用LARGE函數，函數語法如下：
LARGE(區域,N)比如第2大，N就是2。現在要前5名，也就是ROW(1:5)。
作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創始人，個人公衆号：Excel不加班（ID：Excelbujiaban）
上一篇
八字秘訣教授口訣（上）
下一篇
不舍的青春，隻因太精彩
最新文章
-
倪海廈——神農本草經筆記（一九0）紫草（紫根）
6分鐘前 -
《紅磨坊》與《如果·愛》，戲中戲裡的悲豔浮華
23分鐘前 -
風雷打闆：宜昌“瑞鶴仙”打闆思路解密
39分鐘前 -
如何做趨勢交易
39分鐘前 -
教師基本功（39）：教師相互聽課究竟應聽什麼
40分鐘前 -
經絡治療咽喉炎
40分鐘前 -
釣魚人都會準備的靈丹妙藥，多少人隻知其一不知其二
40分鐘前 -
家裡各個房間的重要風水禁忌，好好檢查一下家裡的風水！
42分鐘前
熱門文章
-
威士忌高階關于 OB 與 IB，一次給你說透！
8小時前 -
每日一誦傷寒論第241條
1周前 -
老張老李侃門球之140篇
2天前 -
這個穴位可以治療多種胃痛腹痛，還可以減肥
6天前 -
診餘雜記（師傳經驗）
4天前 -
美麗中國-2870：中國最大的内陸河，塔裡木河
2天前 -
為什麼五點鐘要起床答案讓人吃驚！（現在知道還不晚）
1周前 -
治療坐骨神經痛藥酒５方
1周前
有話要說...