2020年6月19日星期五

(04) Excel 效率手冊 : 早做完 不加班 04　最受歡迎的函數與公式

下午6:36 – by 0
申明: 本站飛宇網 https://feiyetopro.blogspot.com/。自網路收集整理之書籍、文章、影音僅供預覽交流學習研究，其[書籍、文章、影音]情節內容， 評論屬其個人行為， 與本網站無關。版權歸原作者和出版社所有，請在下載 24 小時內刪除，不得用作商業用途；如果您喜歡其作品，請支持訂閱購買[正版]。謝謝！

04　最受歡迎的函數與公式

函數與公式可以說是Excel的精髓，每天都有無數人在討論她的用法。基本上每天都有一些精妙的公式被發掘出來。學公式靠的是邏輯思維和思考問題的方法(角度)，不像技巧那樣需死記硬背。不經過認真思考、舉一反三是永遠學不好公式的。學好公式，絕大部分的工作都可以輕鬆搞定。曾經有人說過，只要學好公式再配合一些技巧，你就可以成為高手。讓我們一起向高手邁進一小步！

4.1　一起來學習函數與公式

溫馨提示

基礎知識很枯燥，也很重要。萬丈高樓平地起，要想直接蓋頂層而沒有地基肯定行不通。基礎不牢以後是要吃苦頭的，請用心學好。基礎知識需要怎麼學呢？一般我是先粗略地看完全部內容，然後從中挑選感興趣的內容，再細讀。對基礎知識有一個初步的瞭解後，暫時先放一邊，等實際遇到相關問題時，再打開來看這部分，無限迴圈。在不知不覺中就記牢了這些知識。切忌貪多，一次是無法記住全部基礎知識的，除非你的記憶力超級強。

學習前的準備

“實用為王”這才是學習的目的，畢竟我們學習技術為的就是要解決問題，不要一味貪多，現在用不上的函數就不要學習。到現在我對財務函數還一無所知，但這並不妨礙我解決實際問題，等到需要時再學習也不遲。
“助人為樂”各人的思維習慣與角度不盡相同，導致水準有高低之分，思維也有差異。幫助別人的時候就是幫助自己。

圖4-1　三個快速鍵
請牢記三大快速鍵，如圖4-1所示。當你熟練掌握後，對你會有莫大的好處！

4.1.1　基本概述

什麼是公式

有人也許會說，公式有什麼好學的，從小學開始我們就一直在接觸“公式”，如圓的面積公式。這是數學公式，跟Excel的公式是不同的。Excel中的公式是以“=”號開始的，通過使用運算子將資料、函數等元素按一定順序連接在一起，從而實現對工作表的數值執行計算的等式，如圖4-2所示。

圖4-2　數學公式

關於=號的位置小故事

很久很久以前，我在剛學Excel的時候，想要用她來進行計算。如圖4-3所示，在儲存格中輸入1+2沒反應，輸入1+2=依然沒反應，不禁歎息，原來Excel是不能計算的。直到有一天，無意間按一下自動加總按鈕，出現=SUM(C2：C6)，才恍然大悟，原來是將=放在最前面，然後輸入=20+30，立刻出現50，輸入=2*9，立馬出現18。老外的想法就跟我們中國人不同，就如稱呼Mr.Chen，完全顛倒思維。得出一條理論：要學老外的東西就得按老外的想法去做，否則肯定會出錯。

圖4-3　無反應的輸入
允許不輸入=，而用+符號替代。

你也敢跟計算器比速度

有很多人喜歡用計算器計算數字，老是說電腦錄入數位速度比不上計算器。真的嗎？NO！因為=號的位置跟小鍵盤隔得遠，第一次錄入=1……這一步比計算器慢。但其實可以不用按=號，而利用+號取代=，效果也是一樣。如輸入+1+1，回車就會得到2，查看公式儲存格的公式變成=1+1，如圖4-4所示。下回遇到有人說，我計算器按得快，你也有膽量跟他比速度。

圖4-4　用+取代=

什麼是函數

簡單來說，函數就是預先定義好的公式。如圖4-5所示，對A1：A10的數位進行求和，利用Sum可以輕鬆匯總，但用常規公式是很麻煩的。

圖4-5　函數與常規公式的比較
使用函數可以簡化公式，同時也可以完成公式無法完成的功能。如圖4-6所示，查詢表中所列人員屬於哪部影片：
    =VLOOKUP(D2,A:B,2,0)

圖4-6　使用函數簡化公式

4.1.2　初識函數

函數可以看成一個“黑箱”，我們只需關心它的輸入與輸出，對裡面的具體實現無須關心。這些黑箱能對輸入資料進行某種處理，最後輸出結果。這些輸入就叫參數，各參數之間用半形逗號“,”來劃分，輸出就是函數返回值，而黑箱可以用一對半形狀態的括弧“(,,,)”來表示，比較形象，就是將參數包裹起來。當然，人有姓，樹有名，為了對這些黑箱起個標識作用，就在括弧之前貼上了“標籤”，這個標籤就是函數名。函數名除用於標識外還起到一定的功能描述作用。
每個函數都有自己的名稱，如果你叫錯名就會出現#NAME?。
如圖4-7所示，如少了一個O：

圖4-7　函數理解示意圖
    =VLOKUP(D2,A:B,2,0)
正確寫法：
    =VLOOKUP(D2,A:B,2,0)

4.1.3　快速、準確輸入函數的兩種方法

Q函數名輸入錯誤，一般發生在2003版，高版本產生輸入錯誤的可能性很小。怎麼快速、準確輸入函數呢？
A解決方案

插入函數

按一下“公式”選項卡中的“插入函數”按鈕，出現“插入函數”對話方塊。選擇類型選擇“查找與引用”項，並選擇VLOOKUP函數。按一下“確定”按鈕，設置每個參數，再次按一下“確定”按鈕，如圖4-8所示。

圖4-8　插入函數
這種方法適用於前期學習階段，對函數熟練後就得學會用第二種方法。

公式記憶式鍵入

不要擔心Excel的記憶力，她遠比你想像的還要好。只要輸入前一兩個字母，就會出現跟這些字母有關的所有函數。如在儲存格中輸入=v，函數不區分大小寫。選擇VLOOKUP，按兩下，如圖4-9所示。
如果有一天，你在工作表裡輸入前幾個字母後沒有任何提示，不要懷疑是不是Excel老了，記憶力減退了，而是你的工作表被別人動了手腳，重新更改過來即可。

圖4-9　公式記憶式鍵入
2003版本不存在這個功能。
選擇“檔”→“選項”命令，打開“Excel選項”對話方塊。按一下“公式”項，選中“公式記憶式鍵入”核取方塊，再按一下“確定”按鈕，如圖4-10所示。

圖4-10　設置公式記憶式鍵入
設置好後，又可以重新提示。這個功能很好用，我經常用，感覺很方便。特別是出現函數嵌套的時候，你用插入函數很難完成，而用“公式記憶式鍵入”卻很輕鬆。

4.1.4　輸入公式三招

QA列數字的總計減去C列數字的總計，統計結果如圖4-11所示。
    =SUM(A1:A10)-SUM(C1:C10)
不要以為這些都是手工書寫的，這樣會令人很頭疼。懶人原則，借助外力幫你輸入公式。

圖4-11　兩列數字差異的和
A解決方案：

滑鼠選取

輸入=SUM(，然後用滑鼠選取A1：A10的儲存格區域，如圖4-12所示。

圖4-12　滑鼠選取

複製相同內容

當=SUM(A1：A10)-到這裡時，停下你的小手，不要再次輸入SUM(，而是直接複製SUM(A1：A10)，然後用滑鼠更改區域，如圖4-13所示。

圖4-13　複製相同內容
你也許對這事不以為然，“我輸入這些內容比複製要快”，真的如此嗎？
那假如是下面的情況，又該如何？難不成你真的不用複製？
    =S U M I F ($A$1:$A$1 0 ,＂＞5＂
    )-SUMIF($A$1:$A$10,＂＞9＂)
    =S U M I F ($A$1:$A$1 0 ,＂＞5＂)/
    COUNTIF($A$1:$A$10,＂＞5＂)

在=號前面錄入空格

寫公式能一氣呵成，那是最好不過的，但有些時候出於某些原因卻無法一次性完成。如寫到一半的時候，突然朋友找你有事，但你已經錄入了一大半公式，直接按一下“√”出現錯誤，按一下“×”所輸入的公式則前功盡棄，如圖4-14所示。

圖4-14　錯誤提示
在左右為難之時，你肯定會怪朋友，早不來，晚不來，偏偏這個時候來。其實只要在=號前面錄入一個空格，就可以搞定這個問題。現在的公式變成了文本，直接回車就可以了。等到事情忙完，重新去掉空格，編輯公式即可。
技巧雖小，但挺實用，如圖4-15所示。

圖4-15　錄入空格

4.1.5　三種不同引用方式

Q如果你細心的話，可以看到很多公式都帶有$符號，這個符號是幹什麼用呢？
A一美元($)的差別：

相對參照

如圖4-16所示，不給行列(行就是數位1，列就是字母A)美元($)，想讓行列不動沒門！

圖4-16　相對參照

混合引用

如圖4-17所示，給行美元($)，美元($)一給，行就乖乖不動了。

圖4-17　混合引用1
如圖4-18所示，給列美元($)，美元($)一給，列也乖乖不動了。

圖4-18　混合引用2

絕對引用

行列都給美元($)，美元($)一給，行列都不動，如圖4-19所示。

圖4-19　絕對引用
一句話：有錢能使鬼推磨，有美元($)行列都聽你的，叫誰不動，誰就乖乖不動。

實際例子

求人口累計跟累計占比，如圖4-20所示。

圖4-20　參考類型實例
人口累計：
    =SUM(B$2:B2)
累計占比：
    =C2/$B$6
三種引用方式混合引用最難理解。當你理解了九九乘法表的時候，也就是你理解混合引用的時候，如圖4-21所示。當初我就是練這個學會混合引用的。

圖4-21　九九乘法表
    =IF($A2＞=B$1,B$1＆＂×＂＆$A2＆＂=＂＆B$1*$A2,＂＂)
公式的思路我就不說了，這個靠自己去領悟，自己悟出來的東西才能記得牢。
這些引用方式不是輸入的，而是在編輯欄選擇儲存格引用，按F4鍵不斷切換而來的。默認是相對參照A1，按一下變成絕對引用$A$1，再按變成混合引用A$1、$A1，如圖4-22所示。

圖4-22　切換參考類型

4.1.6　兩種不同引用樣式

最常用的引用樣式是A1引用樣式，而R1C1引用樣式用得相對比較少。

A1引用樣式

如圖4-23所示，這是預設引用樣式。預設情況下，Excel使用A1引用樣式，此樣式引用字母識別欄位(從A到XFD，共16 384列)以及數位識別碼行(從1～1 048 576)。這些字母和數位統稱為行號和列標。若要引用某個儲存格，請輸入後跟行號的列標。例如，B2引用列B和行2交叉處的儲存格。
前面涉及的公式全部是A1引用樣式，如圖4-23所示。這裡就不再說明了。

圖4-23　A1引用樣式

R1C1引用樣式

R1C1引用樣式對計算位於巨集內的行和列的位置很有用。在R1C1樣式中，Excel指出了行號在R後而列號在C後的儲存格的位置。這種引用在公式中用得比較少，稍微瞭解就行，如圖4-24所示。

圖4-24　R1C1引用樣式
一般情況下，R 1 C 1樣式都是跟INDIRECT函數結合運用，如：
    =INDIRECT(＂R1C1＂,)
R1C1就是A1，相當於絕對引用A1的值。
    =INDIRECT(＂R[-1]C＂,)
R[-1]表示上一行，C沒有數字表示本列，R[-1]C也就是始終引用本列上一個儲存格。
在VBA中經常出現這種R1C1引用樣式，效率比A1引用樣式稍微快點。現在舉一個經典的提取目錄的例子。
利用複合鍵Alt+F11調出VBA編輯器，然後按一下ThisWorkbook，輸入代碼，再按一下“運行”按鈕。
Cells(R,C)中R由1到N，C為1，也就是說將目錄羅列在A列，如圖4-25所示。

圖4-25　羅列目錄到A列中
    Sub目錄()
     For i=1 To Sheets.Count
      Cells(i,1)=Sheets(i).Name
     Next
    End Sub

跟R1C1有關的例子兩則

@Tina-雨：我的Excel睡了一覺，今早上就變成這樣了，如圖4-26所示。請問橫列的字母怎麼變成了數位，求解？

圖4-26　列號變成字母
@小笨熊奧特曼：求解，Excel儲存格求和公式顯示異常，但是結果正常，如圖4-27所示。有沒有達人可以解釋一下啊？

圖4-27　求和公式異常
一般使用VBA寫代碼經常會用到R1C1引用樣式，但在使用函數的時候，很少引用這種樣式，因為不容易解讀。怎麼解決呢？其實很簡單，只要取消對R1C1引用樣式的選中就可以了。
具體操作方法：切換到“檔”選項卡，再選擇“選項”命令，打開“Excel選項”對話方塊。選擇“公式”項，再取消選中“R1C1引用樣式”核取方塊，按一下“確定”按鈕，如圖4-28所示。

圖4-28　取消選中R1C1引用樣式

4.1.7　借助名稱讓公式更容易理解

Q在2.2.5小節中我們提到利用定義名稱的方法欺騙資料有效性，其實在公式中也經常定義名稱，借助名稱讓公式更好理解。
    =VLOOKUP(D2,對應表,2,0)
對應表就是一個名稱，它是引用A：B兩列的數據。
如果要知道對應表的第1列，則第2個對應值可以用
    =INDEX(對應表,2,1)
名稱在公式比較長的時候更能體現出價值，如圖4-29所示。

圖4-29　定義名稱
A創建名稱的三種方法：

使用“名稱框”

最快捷的方法就是利用“名稱框”，選擇區域，輸入名稱，回車即可，如圖4-30所示。

圖4-30　名稱框

以選定區域的值創建名稱

當需要創建很多名稱時，這是最好的辦法。選擇區域，然後按一下“公式”選項卡中的“根據所選內容創建”按鈕，選中“首行”核取方塊，再按一下“確定”按鈕，如圖4-31所示。

圖4-31　以選定區域的值創建名稱

使用功能區命令

這種方法適合創建動態名稱。當資料來源經常變動時，這種方法是首選的。按一下“公式”選項卡中的“定義名稱”按鈕。在“名稱”文字方塊中輸入“動態資料來源”，“引用位置”輸入下面的公式，然後按一下“確定”按鈕，如圖4-32所示。
    =OFFSET(定義名稱！$A$1,,,COUNTA(定義名稱！$A:$A),2)

圖4-32　使用功能區命令
當然這裡沒有強制性要求使用哪種方法，只要你覺得順手就行。正常情況下我是利用第三種方法創建，只是習慣了而已。
Q怎麼給名稱命名呢？
原則：簡單、容易記，就好。
當然如果你不嫌麻煩的話，可以起一個
長長的名字，比如“我吃飽了撐著你拿我怎麼辦”。
A管理名稱：
按一下“公式”選項卡中的“名稱管理器”按鈕，這時所有定義的名稱就會展示在我們面前。我們可以對名稱進行編輯、刪除。如剛才那個“我吃飽了撐著你拿我怎麼辦”的名稱是開玩笑的，沒有意義，選擇這個名稱，然後按一下“刪除”按鈕，再按一下“確定”按鈕，如圖4-33所示。

圖4-33　管理名稱

4.1.8　瞭解另外一片天地——陣列公式

假如把Excel劃分成九級，你學好普通公式最多也就到了第三級，而陣列公式即使再差也算第四級。別看兩者相差不多，但每往上一級能力相差就越大。
Q什麼是陣列？
A如四大名著是一個整體，“西遊記”就是其中一個組。只要我一說“西遊記”，就會一次性出現唐僧、孫悟空、豬八戒和沙僧。再如點“三國演義”，裡面的四個人也會一齊出現，而不用逐個點名，這就是跟普通公式的差異，如圖4-34所示。

圖4-34　西遊記組
Q什麼是陣列公式？
A陣列公式可以執行多項計算並返回一個或多個結果。陣列公式必須按複合鍵Ctrl+Shift+Enter結束。在輸入陣列公式時，Excel會自動在大括弧{}之間插入該公式，目的是告訴Excel，我可不是普通人，要對我特殊對待，一副盛氣淩人的樣子。
一般情況下，能力跟脾氣是成正比的，能力越強，脾氣越大。正因為陣列公式的能力比普通公式強，所以它脾氣大點，動不動就耍脾氣不幹了。

需謹記於心的內容

你不是說一喊“西遊記”所有人就出來了嗎，怎麼只出來一個唐僧，其他人跑哪去啦？
你把爺當成普通人，當然不鳥你拉。選擇區域，記得要按複合鍵Ctrl+Shift+Enter結束，如圖4-35所示。

圖4-35　沒按三鍵
注意編輯欄的變化，如圖4-36所示。

圖4-36　按三鍵
“西遊記”只有四人，如果區域多選的話也會出錯，如圖4-37所示。

圖4-37　區域不匹配
“西遊記”的四人是一個整體，不能更改其中一個，要改就得全部改，如圖4-38所示。

圖4-38　刪除部分公式
在2013版中連回車都不允許，在其他版本顯示“不能更改陣列的某一部分”。可以按一下“×”按鈕，然後選擇整個區域，刪除(或修改)公式。
按一下包含多儲存格陣列的任意儲存格，使用複合鍵Ctrl+/就可以快速選中整個陣列區域，如圖4-39所示。
只要你摸清陣列公式的脾氣，以後注意點就沒事。

圖4-39　快速選中整個陣列區域
西遊記是一個定義好的名稱。

玩笑開完，進入正題

Q例1：如圖4-40所示，根據單價跟數量，求總金額？

圖4-40　求總金額
A常規方法是先用輔助列求金額之和，然後匯總，分兩步進行。
利用陣列公式只需一步就可以，記得按複合鍵Ctrl+Shift+Enter結束。
    =SUM(B2:B3*C2:C3)
Q例2：如圖4-41所示，將數值四捨五入保留整數。

圖4-41　將數值四捨五入並保留整數
A常規方法需要下拉公式才能獲取結果。利用陣列公式，選擇B2：B8，然後輸入公式，一步就可以，記得按複合鍵Ctrl+Shift+Enter結束。
    =ROUND(A2:A8,0)
也就是說，不管過程有幾步，陣列公式都能一步到位，省去了中間步驟。

常量陣列

陣列公式的參數有時候並不是直接引用儲存格，而是自己輸入的，如{0,60,80}，{＂差＂,＂中＂,＂優秀＂}，這些就是常量陣列。常量陣列跟陣列公式有點不同，不需要按複合鍵Ctrl+Shift+Enter結束。
例：將分數小於60的劃分為差，60～80為中，80以上為優秀，如圖4-42所示。
    =LOOKUP(A2,{0,60,80},{＂差＂,＂中＂,＂優秀＂})

圖4-42　劃分等級
一般劃分區間都是用常量陣列，其他情況是直接引用儲存格區域。當然常量陣列也可以通過引用儲存格，然後按F9鍵獲得，如圖4-43所示。

圖4-43　引用儲存格

4.2　函數經典再現

2011年的某一天，我像往常一樣在群裡幫人解答問題，一般情況下只給出公式而已。突然，好友簡單說了一句：給出公式不解釋又有何用？雖然只是這麼一句話，但讓我想了很多，確實授人於魚，不如授人於漁。經過一天的準備，邀請了幾個好友簡單、無言的人、simple、吳姐等開展了一個為期兩個月的函數講座，教網友如何使用公式，解讀公式。現在對這些函數進行重新整理，挑選裡面較為精彩的部分，以及有說不到位的再重新補充說明。

學習三過程

信任幫助：哪裡不懂第一時間就按F1鍵查看幫助。幫助是微軟開發的，可信度還是很高。
鑽研幫助：特別是幫助的說明。如SUM，如果參數是一個陣列或引用……文本被忽略，那以後用SUM就可以不用管區域有沒有文本。
懷疑幫助：不能被幫助牽著鼻子走，要學會思考。如PHONETIC函數，提取文本字串中的拼音(furigana)字元。該函數只適用于日文版。難道這個只能這麼用，沒有其他用途？其實它還可以將區域的文本連接在一起。
學習的過程就是一邊不斷摸索進步，一邊無私分享的過程，分享越多證明你瞭解得越多。

4.2.1　閒聊SUM

盧子：你會SUM函數嗎？
網友：別開玩笑啦，這個誰不會。如圖4-44所示，選擇儲存格A7，在“公式”選項卡裡，按一下“自動加總”按鈕，就自動對區域進行求和，簡單得要死。

圖4-44　自動加總
盧子：你還知道SUM的其他用法嗎？
網友：這個不就自動加總而已，還有什麼用法？
盧子：按F1鍵調出幫助，輸入sum搜索，會出現這個函數的用法，如圖4-45所示。

圖4-45　SUM的用法
還沒等盧子繼續說下去，網友就耐不住性子了。
網友：説明也不過如此，就是對所有數字求和，我以為有什麼稀奇。
盧子：實際幫助說到的用法，僅僅是最基礎的用法，連SUM的冰山一角還沒有見著。
網友：有這麼誇張嗎，你倒是說說看？
盧子：這裡通過兩部分來說明SUM的用法——基礎用法與知識擴展。

基礎用法

Q案例1：如圖4-46所示，這是一份每月銷售清單。現在想按月份累計銷售額，該怎麼辦？

圖4-46　每月銷售清單
A選擇C2儲存格，輸入公式，並向下填充公式。
     =SUM(B$2:B2)
給第一個B2的行塞點美元(B$2)，讓行站住不動，下拉的時候不會有任何變化，依然是B$2。第二個B2因為沒給美元，下拉就變成B3、B4…B13了。在C3區域就變成了B$2：B3，也就是對B2：B3區域求和。在C13區域變成了B$2：B13，也就是對B2：B13區域進行求和。適當給點美元，會起到意想不到的效果。
Q案例2：如圖4-47所示，這是一份人員銷售清單，需要匯總銷售額，如果是你會怎麼匯總呢？
網友：銷售額分成三列，每一列就用“,”隔開，公式如下：
     =SUM(F2:F7,H2:H7,J2:J7)
盧子：看來你對幫助還理解不夠透徹，一起來看看函數說明。

圖4-47　人員銷售清單
A函數說明
如果參數是一個陣列或引用，則只計算其中的數字。陣列或引用中的空白儲存格、邏輯值或文本將被忽略。
如果任意參數為錯誤值或為不能轉換為數位的文本，Excel將會顯示錯誤。
也就是說，如果區域中有文本，將被忽略，所以只要寫一個區域就行了。
     =SUM(E2:J7)
網友：這些小細節還真沒注意看，多謝提醒。
盧子：前面都是基礎的，再來看看難度大點的。

知識擴展

Q案例3：如圖4-48所示，這是一份沒經過任何處理的不良明細，存在錯誤值，直接求和出錯。這個有辦法解決嗎？

圖4-48　含錯誤值不良明細
網友：這回我仔細研究了幫助，如果任意參數為錯誤值或為不能轉換為數位的文本，那麼Excel將會顯示錯誤。這種問題只有刪除錯誤值才可以統計，不然會出錯。呵呵，這回我沒說錯吧，我也挺用功的。
盧子：説明僅供參考，我們還需要學會思考問題。“定位”錯誤值，然後刪除也是一種辦法。其實也可以直接求和，在這之前先瞭解一下IFERROR函數，如圖4-49所示。

圖4-49　IFERROR函數說明
A說白了，就是可以將錯誤值顯示成你想顯示的任何形式。
    =IFERROR(錯誤值,顯示值)
如圖4-50所示，既然這樣，可以先通過將錯誤值顯示成0，然後再匯總。

圖4-50　輔助列匯總
雖然這樣可以匯總，但會產生一個輔助列。回到基礎知識，再看看陣列公式的概念。
陣列公式可以執行多項計算並返回一個或多個結果。陣列公式必須按複合鍵Ctrl+Shift+Enter結束，在輸入陣列公式時，Excel會自動在大括弧{}之間插入該公式。
利用陣列可以省略輔助列，直接得到結果。
    =SUM(IFERROR(B2:B8,0))
先將錯誤值全部轉換成0，然後再匯總，因為轉換過程需要重新運算，所以需要按複合鍵Ctrl+Shift+Enter結束。借助陣列可以省略很多中間步驟，如果你想成為別人眼中的高手，陣列必須熟練掌握。下面再通過兩個例子來鞏固對陣列公式的理解。
Q案例4：如圖4-51所示，這是一份每月銷售清單，現在要統計銷售額大於500的次數？

圖4-51　每月銷售清單
A先來瞭解一下IF函數的用法，如圖4-52所示。

圖4-52　IF函數説明
通俗點就是：
    =IF（條件，滿足條件的返回值，不滿足條件的返
    回值）
可以通過IF來判斷銷售額是否滿足大於500，讓大於500的顯示1，小於或等於500的顯示0。可以在儲存格中輸入公式，看看是否跟我們想的一樣。
    =IF（F2＞500，1，0）
剛好F2是600，顯示1，下拉試試，F3是120，顯示0。跟我們的預想一樣。如果是整個區域判斷：
    =IF（F2：F13＞500，1，0）
在編輯欄按F9鍵將公式抹黑，得到
    {1；0；1；0；0；0；0；1；0；0；0；0}
跟我們在儲存格下拉公式得到的結果是一樣的，只是顯示在陣列中。這樣只是起到判斷而已，還需要求和。只需要在IF外面再嵌套SUM函數就行了，因為要執行多重計算，所以是陣列公式。
    =SUM（IF（F2：F13＞500，1，0））
網友：貌似看懂了點，對了這個F9鍵是幹嘛用？
盧子：F9鍵，人稱“獨孤九劍”，看過《笑傲江湖》的人應該知道令狐沖的獨孤九劍很厲害。既然F9鍵有這個雅稱，一定有過人之處。F9鍵是解讀公式的利器，公式如果太長了看不懂，將看不懂的地方抹黑就知道運算結果了。看完後再按複合鍵Ctrl+Z返回，否則公式就變了。步步高點讀機有一句廣告詞：哪裡不會點（抹）哪裡，so easy！媽媽再也不用擔心我的學習了。用在這裡再適合不過，如公式：
    =SUM（SMALL（IF（B$1：B$10=5，ROW（$1：$10）），
    ROW（1：2））*{-1；1}）-1
這個ROW（$1：$10）看不懂，你就抹黑它再按F9鍵。
    =SUM（SMALL（IF（B$1：B$10=5，{1；2；3；4；5；6；7；8；
    9；10}），ROW（1：2））*{-1；1}）-1
原來相當於1～10，記得按複合鍵Ctrl+Z返回哦，Excel是允許你後悔的。
網友：原來是協助解讀公式的一個工具。
盧子：這個很好用，我經常用。還有一個叫公式求值的功能，效果跟這個差不多，有空你也可以瞭解一下。不過公式求值讓人覺得自己就是一個機械操作工，而F9鍵讓人覺得自己是一個劍客，凡事隨心所欲。

再舉一個例子來說明陣列的用法

Q案例5：還是以每月銷售清單那個附件來說明，求銷售額大於500的人的總銷售額。
A有了上面的基礎，再來瞭解這個就很容易了。
    =SUM（IF（F2：F13＞500，F2：F13，0））
用IF進行判斷，讓大於500的顯示原來的銷售額，其他顯示0，進行求和剛好得到銷售額大於500的人的總銷售額。
網友：這回懂了，謝謝盧子。
盧子：今天這個SUM函數就先告一段落，自己有空再去熟練一下，欲知SUM更多用法，且聽下回分解。
網友：謝謝，期待下回更精彩的講解！
這邊剛結束，就收到簡單、Simple的私聊消息。
簡單：辛苦了。
盧子：累死人，沒想到一講就是兩個小時，比1000米賽跑還累。
簡單：明天把這些整理一下，分享到群裡。
盧子：好的。
Simple：講得不錯，挺有邏輯性的。
盧子：已盡力了，但願這次能收到好的評價。回頭你看看大家的評價怎樣？
Simple：明後天應該就能知道大家的想法，到時跟你彙報。
盧子：那我先睡了，腦力活原來比體力活更累。
Simple：那早點休息吧。
……
第二天晚上，收到Simple的私聊資訊。
Simple：大家評價蠻高的，都在打聽什麼時候再組織講座，到時得提前通知。群裡不斷有陌生人加入，說要聽課。看你太辛苦，昨晚的講座內容已經幫你整理好了。
盧子：謝了，回頭我直接把你整理的分享出來就行。這回的辛苦總算沒白費，我回頭再準備下，爭取這兩天舉行第二回講座。
經過兩天的準備，盧子把SUM函數的其他資料整理好了。這回只是對上回知識的補充，並不會涉及太多的知識點。
盧子：今晚繼續學習SUM函數，通過三個小例子來對上一回的知識進行補充說明，大約1小時就可以講完了，呵呵。
網友：老師辛苦了，沒想到小小SUM函數居然這麼神奇，這回要用功學習才是。
盧子：很好，那一起開始學習吧。
Q案例1：如圖4-53所示，這是一份含有單位的人員銷售清單，直接求和得不出正確答案，怎麼才能讓含單位的銷售額可以求和呢？

圖4-53　含單位的人員銷售清單
A説明提到，SUM函數會自動忽略文本，600元就是文本，不屬於數位。最簡單的做法就是將“元”替換成空，然後自訂單元格格式G/通用格式“元”。很多人就是搞不明白一格一屬性的道理，才會造成匯總資料困難重重。正確的做法應該將“元”寫在欄位名那裡變成銷售額（元），這樣別人一看便知。廢話了這麼多，還沒扯到今晚的正題，別見怪，剛才只是有感而發而已。
剛才提到了替換這個詞，函數中也有屬於自己的替換函數，SUBSTITUTE函數，用法如圖4-54所示。

圖4-54　SUBSTITUTE函數説明
SUBSTITUTE的第四參數為可選，那就先別管它，其他參數可以理解為：
    =SUBSTITUTE（文本，需要替換的舊字元，
    替換成新的字元）
儲存格中的“元”是多餘的，需要替換成空，空可以用＂＂表示，替換成空後直接求和，可以嗎？
    =SUM（SUBSTITUTE（B2：B7，＂元＂，
    ＂＂））
網友：這個是陣列公式，用法也跟前面說得差不多，應該可以匯總。
盧子：SUBSTITUTE函數屬於文本函數，所以替換得到的數位也屬於文本，在這裡叫作文本數位。數位有兩種類型，一種是文本數位，一種是真正的數位，即數值。數值可以求和，而文本不能求和。如帳簿上的數位跟牆上的數位是不同的，前者我們可以用這些數字進行各種分析，後者只能當欣賞用。那有什麼辦法還原數字的本質呢？
利用VALUE函數可以將文本型轉換成數值型。
    =VALUE（＂600＂）
但一般情況下不會這麼做，而是通過運算轉換。

一起來瞭解“減負”運算

在函數或公式中，運算過程會自動把文本轉換為數值（一個隱含過程），再與數值進行運算，負值運算（-）也是一種運算，能把文本轉換成數值。
    -＂600＂=-600
還記得負負得正吧？例如：
    -（-＂600＂）=-（-600）=600
可簡寫為：
    ——＂600＂=600
——可以把文本轉換為數值，但它不是標準的轉換方式，而是借用負運算的隱含功能。
其實負負運算稱為減負運算更好，即減去數位的負擔，還原數位的本質。
    =SUM（——SUBSTITUTE（B2：B7，＂元＂，＂＂）），
將這一部分用F9鍵抹黑，得到：
    =SUM（{600；120；1000；210；129；123}）
這樣就能夠求和了。
綜上，最終的陣列公式為：
    =SUM（——SUBSTITUTE（B2：B7，＂元＂，＂＂））
網友：沒想到數字還有這些學問，長見識了。
盧子：再來看另一種不規範輸入的案例。
Q案例2：如圖4-55所示，這是一份含附加分的成績明細表，分數分為基本分（左），附加分（右），怎麼匯總分數呢？

圖4-55　含附加分成績明細
A仔細觀察發現：
有附加分的中間都隔著空格，如圖4-56所示。這跟分數寫法的前半部分一樣，只是少了斜杠（/）+分母。既然這樣，我們就可以構造後半部分。分數＆“/1”，E2就得到“90 5/1”，通過＆函數得到的是文本數值，前面加“——”讓它變成數值，E3本身就是數位，不必轉換。

圖4-56　分數的組成部分
通過上面的分析，問題已經解決一大半了，只需判斷分數是不是數值，是的話就顯示本身的值，不是就用——（分數＆“/1”）。怎麼判斷儲存格的值是不是數值呢？先來瞭解一下ISNUMBER函數，它只有一個參數。
    =ISNUMBER（值）
如果是數位就顯示TRUE，否則顯示FALSE。
到這裡思路都出來了，只需輸入陣列公式到儲存格裡來驗證我們的想法是否正確就可以了。
    =SUM（IF（ISNUMBER（E2：E7），E2：E7，——
   （E2：E7＆＂/1＂）））
網友：測試通過，原來寫公式跟斷案一樣，需要嚴謹的分析，才能不判錯案子。
盧子：公式主要是靠邏輯，大腦要經常動，這樣才不會生銹。

溫馨提示

分子不能超過5位數，否則出錯。
如：1 100000/1用=ISNUMBER（＂1 100000/1＂）測試顯示為FALSE，證明這個是文本。
正常的話，不會出現這樣的分數，稍微瞭解一下就行。
Q案例3：如圖4-57所示，這是一份人員銷售清單，經常要在總銷售額處插入新的人員。用SUM直接統計不會對新增加人員的銷售額進行匯總，該怎麼處理呢？

圖4-57　人員銷售清單
A按複合鍵Ctrl+F3定義一個叫“上一行”的名稱，引用位置為B20。
這個引用為相對參照，每插入一行引用位置就會動態變化，所以用下面的公式就可以搞定，以後插入行也會自動匯總進去，如圖4-58所示。

圖4-58　定義名稱
    =SUM（B15：上一行）

一個過來人的忠告

有人說直接用SUM（區域）就行，插入行後區域會自動擴展。但我說這是Excel的BUG你信嗎？曾經我就因為直接用SUM（區域）導致開錯兩張單，最後核對金額的時候才發現異常，還好金額不大，如果是大金額，這將給公司帶來多大的損失呀。小心駛得萬年船，如果你堅持你的想法，用SUM（區域）插入行後請選擇區域，查看狀態列的總計跟公式匯總是否一致。圖4-59為直接用SUM（區域）插入行後的結果，數量添加的行會自動匯總進去，金額卻沒有自動增加。為了保險起見還是定義名稱，當然還有其他方法，例如：

圖4-59　在“合計”上面插入行後的結果
    =SUM（B15：INDEX（B：B，ROW（）-1））
關於SUM的用法到這裡已經結束了，如果還有什麼疑問可以向Simple提出來，到時我再統一解答。
網友：又學到了幾招，謝謝老師。
盧子：對了，以後叫我盧子就行，這樣我還習慣點，下節課根據大家的回饋再決定講什麼函數。

4.2.2　求和之王SUMPRODUCT

盧子：經過這幾天的回饋，有兩個問題提的人比較多。
Q其1：實例是用公式=SUM（IF（F2：F13>500，F2：F13，0）），但現實有很多公式是這種形式=SUM（（F2：F13>500）*F2：F13），後者是怎麼得出來的？
其2：SUM的陣列公式要按複合鍵Ctrl+Shift+Enter結束才能得到正確答案，很麻煩，經常會忘記按複合鍵Ctrl+Shift+Enter，有沒有其他函數可以取代SUM的陣列形式呢？
A一起來看看問題1，如圖4-60所示，以銷售額大於500的人的總銷售額為例進行說明。

圖4-60　銷售額大於500的人的總銷售額
=SUM（（F2：F13＞500）*F2：F13）可能很多人想知道抹黑地方的意思，有句話叫心急吃不了熱豆腐，凡事得一步步慢慢來，急不得。
=B2＞500如果成立就顯示TRUE，否則顯示FALSE。在這裡B2＞500成立，顯示TRUE。
=（B2＞500）*B2等同於=TRUE*B2，得到600，也就是說在這裡TRUE相當於1。
=（B3>500）*B3等同於=FALSE*B3，得到0，也就是說在這裡FALSE相當於0。
*可以讓符合條件的值顯示其本身，不符合條件的值顯示0，但它不是標準的轉換方式，而是借用乘法運算的隱含功能。再回到公式上
    =SUM（（F2：F13＞500）*F2：F13）
按F9鍵抹黑得到：
    =SUM（（{TRUE；FALSE；TRUE；FALSE；FALSE；F
    ALSE；FALSE；TRUE；FALSE；FALSE；FALSE；FAM
    LSE}）*B2：B13）
得到一組由TRUE跟FALSE組成的陣列：
    =SUM（（{TRUE；FALSE；TRUE；FALSE；FALSE；F
    ALSE；FALSE；TRUE；FALSE；FALSE；FALSE；
    FALSE}）*B2：B13）
陣列*B2：B13，讓符合條件的都顯示其本身，其他顯示0。
    =SUM（{600；0；1000；0；0；0；0；1000；0；0；0；0}）
到這步應該可以理解了吧。解讀公式一開始先不要直接用整個區域解讀，先分成一個儲存格的判斷解讀，儲存格理解後再轉換成區域理解。這樣更有助於理解，適當的時候配合F9鍵，效果會更好。
網友：原來公式是這麼解讀的，老想一步就到位，反而理解不好。先拆開，再合併，先記住這個方法。
盧子：問題2用SUMPRODUCT可以取代SUM的陣列公式。還是老方法，先看SUMPRODUCT函數的説明，如圖4-61所示，對多個區域先相乘，後匯總。

圖4-61　SUMPRODUCT幫助

基礎用法

畢竟是出自一家人，幫助都差不多，非常好理解，如圖4-62所示，統計總金額。

圖4-62　統計總金額
    =SUMPRODUCT（B2：B4，C2：C4）
只強調一句，SUMPRODUCT將非數值型的陣列元素作為0處理，如B4現在的值是文本=SUMPRODUCT（{2；5；＂無＂}，C2：C4），“無”在這裡等同於0。在此不再對這個函數的基礎用法進行說明，否則會讓大家誤認為侮辱了你們的智商。

知識擴展

通過對SUM的學習知道了它可以求和、計數，SUM能做到的SUMPRODUCT都能做到，而且做得更好。SUMPRODUCT函數本身就支援陣列，所以條件計數、求和的時候不需要按複合鍵Ctrl+Shift+Enter，正因為這樣它才受到大多數人的喜歡。有人把它比喻成璀璨的明珠，光芒四射，魅力無窮，稱為求和之王也不為過。

通用公式

計數：
    =SUMPRODUCT（（條件1）*（條件2）*（條件3）*…*（
    條件N））
求和：
    =SUMPRODUCT（（條件1）*（條件2）*（條件3）*…*
    求和區域）
如圖4-63所示，這是IT部落窩隨機抽查的人員資料表，下面通過10個小例子來說明條件計數、求和的用法。

圖4-63　IT部落窩隨機抽查的人員資料表

計數

例子1：女性有幾個人？
    =SUMPRODUCT（——（C4：C22=＂女＂））
例子2：潛水時間大於15天的男人有多少？
    =SUMPRODUCT（（E4：E22＞15）*（C4：C22=＂男＂））
例子3：2月份發言的男人有多少？
    =SUMPRODUCT（（MONTH（D4：D22）=2）*（C4：C22=＂男＂））
這裡涉及一個新函數MONTH，其作用就是將日期轉換成月份。相關聯的函數還有YEAR，其作用是將日期轉換成年，DAY函數可將日期轉換成日。
例子4：不包括“笑看今朝”的男人有幾個？
    =SUMPRODUCT（（A4：A22＜＞＂笑看今朝＂）*（C4：C22=＂男＂））
＜＞（不等於）屬於比較運算子，還有=（等於），＞（大於），＜（小於），＞=（大於等於）和＜=（小於等於），跟數學的表示方法略有差別，但作用一樣。

求和

例子5：女性潛水總天數。
    =SUMPRODUCT（（C4：C22=＂女＂）*E4：E22）
例子6：潛水時間大於15天的男性的潛水天數。
    =SUMPRODUCT（（E4：E22＞15）*（C4：C22=＂男＂）*E4：E22）
例子7：2月份發言的男性的潛水天數。
    =SUMPRODUCT（（MONTH（D4：D22）=2）*（C4：C22=＂男＂）*E4：E22）
例子8：QQ號首位是8的人的潛水天數。
    =SUMPRODUCT（（LEFT（B4：B22）=＂8＂）*E4：E22）
LEFT的語法：LEFT（文本，N），提取左邊的N位元文本，省略第二參數，就是提取1位。
例子9：姓名字元數為2，不包括“月亮”的人的潛水天數。
    =SUMPRODUCT（（LEN（A4：A22）=2）*（A4：A22＜＞＂月亮＂）*E4：E22）
LEN的語法：LEN（字元），統計字元個數，漢字、字母、數位都是一個字元；LENB（字元），統計位元組個數，漢字兩個位元組，字母、數位為一個位元組。
例子10：“笑看今朝”和“冷逸”的潛水天數。
    =SUMPRODUCT（（（A4：A22=＂笑看今朝＂）+（A4：A22=＂冷逸＂））*E4：E22）
+在這裡是“或”的意思，只要滿足其中一個條件就行，它有時可以替代OR的功能，如=IF（OR（A4=＂笑看今朝＂，A4=＂冷逸＂），1，0）等同於=IF（（A4=＂笑看今朝＂）+（A4=＂冷逸＂），1，0），但OR不能替代+在陣列中的用法，切記！

簡化

    =SUMPRODUCT（（A4：A22={＂笑看今朝＂，＂冷逸＂}）*E4：E22）
公式剖析，老辦法，先轉換成儲存格比較。
A5={＂笑看今朝＂，＂冷逸＂}，一個儲存格跟兩個值同時比較，滿足就顯示TRUE，否則顯示FALSE。
A5={＂笑看今朝＂，＂冷逸＂}，按F9鍵得到{TRUE，FALSE}。
（{TRUE，FALSE}）*E5，按F9鍵得到{6，0}。也就是說，只要儲存格滿足其中一個值，就一定會得到由0跟儲存格本身組成的常量陣列，完全不滿足就顯示{0，0}。因為儲存格不可能同時滿足兩個條件，所以不會出現{6，6}這種情況。
    =SUMPRODUCT（（A5={＂笑看今朝＂，＂冷逸＂}）*E5）
抹黑得到：
    =SUMPRODUCT（{6，0}）
同理：
    =SUMPRODUCT（（A4：A22={＂笑看今朝＂，＂冷逸＂}）*E4：E22）
抹黑得到：
    =SUMPRODUCT（{0，0；6，0；0，5；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0；0，0}）
這裡就不再進行解釋了，留點空間給大家思考。
有SUM作為鋪墊，理解SUMPRODUCT會異常簡單。今天就到此結束，有疑問可以回饋出來。
網友：謝謝，回去好好消化一下。

4.2.3　既生SUMIF（COUNTIF），何生SUMPRODUCT

網友：SUMPRODUCT函數太好用了，導致現在Excel專門的條件求和、計數函數SUMIF（COUNTIF）都不去使用了。
盧子：一部《三國演義》讓大家把諸葛亮當神看待——神機妙算，無所不能。以至於周瑜感歎“既生瑜，何生亮”。其實周瑜也是一個很有才華的人，只是被掩蓋了。
扯遠了，回到正題。
SUMIF（COUNTIF）其實也很好用，有好事者測試了SUMIFS跟SUMPRODUCT多條件求和統計速度，前者是後者的三倍。那SUMIF單條件統計速度比SUMPRODUCT快一點還是可以肯定的。不過對你我來說，可以忽略這個速度的問題。
先來瞭解一下COUNTIF函數。怎麼學函數，還是老話按F1鍵調出幫助。不要對每次的重複操作厭倦，幫助可以給我們提供很多有用的資訊。如圖4-64所示，這是COUNTIF函數的説明。

圖4-64　COUNTIF幫助
通俗點的語法：
    =COUNTIF（條件區域，條件）
統計區域滿足條件的個數。
下面通過幾個小例子來說明COUNTIF的用法。如圖4-65所示，這是2006年電腦配件銷售一覽表。

圖4-65　2006年電腦配件銷售一覽表
例子1：數量大於30的有幾個？
    =COUNTIF（D4：D22，＂＞30＂）
例子2：營業部中含“河”字的有幾個？
    =COUNTIF（A4：A22，＂*河*＂）
萬用字元的說明：*代表所有字元，？代表一個字元。如果需要統計營業部中的兩個字元，且“河”字在最後面，可以這麼寫公式：
    =COUNTIF（A4：A22，＂？河＂）
例子3：在商品列中是否有鍵盤？
    =IF（COUNTIF（B4：B22，＂鍵盤＂）＞0，＂存在＂，＂不存在＂）
如果存在鍵盤COUNTIF統計出來的次數大於0；否則等於0；公式可以稍做簡化。
    =IF（COUNTIF（B4：B22，＂鍵盤＂），＂存在＂，＂不存在＂）
網友：＞0這部分怎麼可以省略？這是什麼原理？
盧子：一起來看看下面幾個判斷。=IF（3，＂存在＂，＂不存在＂），返回存在；=IF（-3，＂存在＂，＂不存在＂），返回存在；=IF（0，＂存在＂，＂不存在＂），返回不存在。
也就是說任何不等於0的數字在這裡都等同於TRUE，0等同於FALSE。如果不相信，可以自己多試幾個看看。不過建議初學者不要用簡寫，用標準寫法更有助於理解。前面幾個例子的條件都是手寫的，其實條件可以直接引用儲存格。
例子4：如圖4-66所示，統計每個營業部出現的次數。

圖4-66　引用儲存格
    =COUNTIF（$A$4：$A$22，L4）
因為公式要下拉，為防止區域改變，所以加絕對引用。絕對引用、相對參照和混合引用，可以通過用F4鍵切換得到。
例子5：統計共有幾個不重複營業部？
    =SUMPRODUCT（1/COUNTIF（A4：A22，A4：A22））
=SUMPRODUCT（1/COUNTIF（區域，區域））是計算區域不重複個數的經典公式，需要好好理解。為了便於解讀公式，應把區域改小，公式變成：
    =SUMPRODUCT（1/COUNTIF（A4：A9，A4：A9））

觀察

    =SUMPRODUCT（1/COUNTIF（A4：A9，A4：A9）ff）
按F9鍵抹黑：
    =SUMPRODUCT（1/{1；3；3；2；3；2}）
按Esc鍵返回：
    =SUMPRODUCT（1/COUNTIF（A4：A9，A4：A9））
按F9鍵抹黑：
    =SUMPRODUCT（{1；0.333333333333333；0.333333333333333；0.5；0.333333333333333；0.5}）
按Esc鍵返回，在儲存格處按回車看到結果：3。

分析

按F9鍵觀察有時不太直觀，回到工作表中繼續看看。
=COUNTIF（A4：A9，A4：A9）是多儲存格陣列，等同於=COUNTIF（$A$4：$A$9，A4）下拉的結果，也就是統計每個儲存格本身出現的次數，如1。
=1/COUNTIF（A4：A9，A4：A9）是多儲存格陣列，等同於=1/COUNTIF（$A$4：$A$9，A4）下拉的結果，也就是1/每個儲存格本身出現的次數。為了讓資料更直觀地轉換成分數形式，如2，出現3次就變成1/3，出現2次就變成1/2，出現1次就1。1/3+1/3+1/3=3*（1/3）=1，1/N+…+1/N=N*（1/N）=1，不管出現幾次，相加都等於1，如圖4-67所示。

圖4-67　輔助理解
最後將這些分數相加就得到不重複的數量，如3。

解讀公式的一些習慣

把區域改小，這樣便於查看，如將A1：A1000改成A1：A3。
F9鍵配合複合鍵Ctrl+Z或者Esc鍵不斷地看運算過程再返回，重複到理解為止。
輸入公式後回到儲存格查看運算過程，這相對比較直觀。

分析

第2、第3點可選，看你對公式的熟練程度而言，如果不熟練選擇3，熟練的話選擇2。
關於計數就說到這裡，回頭再聊聊求和。
先來瞭解一下SUMIF函數的説明，如圖4-68所示。

圖4-68　SUMIF幫助
通俗語法：
    =SUMIF（條件區域，條件，求和區域）
實例以圖4-65所示的2006年電腦配件銷售一覽表為例進行說明。
例子1：求匯總顯示器的數量。
    =SUMIF（B4：B22，＂顯示器＂，D4：D22）
例子2：求數量大於30的總數量。
    =SUMIF（D4：D22，＂＞30＂）
第3個參數省略了，求和區域相當於D4：D22，公式的作用等同於：
    =SUMIF（D4：D22，＂＞30＂，D4：D22）
例子3：求匯總數量在30至40之間的數量總和。
    =SUMIF（D4：D22，＂>=30＂）-SUMIF（D4：D22，＂＞40＂）

圖4-69　求[30，40]區間的總和
[30，無窮大）跟[40，無窮大）的交集是[40，無窮大），[30，無窮大）減去交集[40，無窮大）剛好是[30，40]這個區間，所以>=30的總和減去>40的總和就是30～40之間的總和。
網友：這個沒有數學基礎真的不好理解，看來數學還是挺重要的。
盧子：很多東西都是相互借鑒的，學好數學有助於學好函數。例子4：求最後字元為“河”且總字元為3個的營業部的總金額。
    =SUMIF（A4：A22，＂？？河＂，F4：F22）
？代表1個字元，“？？河”就是最後字元為“河”且是3個字元。
例子5：如圖4-70所示，數位包含錯誤值，怎麼避開錯誤值求和

圖4-70　數位包含錯誤值
    =SUMIF（A：A，＂＜9E+307＂）
9E+307是9乘以10的307次方，相當於Excel最大的數位，任何數位都比它小。資料的排序依據為數位＜文本＜邏輯值＜錯誤值，再大的數位都比不上錯誤值，所以可以避開錯誤值求和。
例子6：如圖4-71所示，多個區域求型號等於A03的總數量。

圖4-71　多個區域求型號等於A03的總數量
    =SUMIF（A2：G10，＂A03＂，B2）
    =SUMIF（A2：G10，＂A03＂，B2：H10）
兩個公式的效果是一致的，SUMIF的第3個參數會自動擴展區域，但不建議簡寫，那樣會導致運算速度變慢。

溫馨提示

sum_range參數與range參數的大小和形狀可以不同。求和的實際儲存格可使用sum_range參數中左上角的儲存格作為起始儲存格，然後包括與range參數大小和形狀相對應的儲存格。但是，當SUMIF函數中的range和sum_range參數不包含相同的儲存格個數時，工作表重新計算需要的時間可能比預期的長。
網友：原來簡單不一定好，簡單是以付出效率作為代價的。
盧子：在使用函數的過程中還是使用標準用法為好，而在學習過程中多瞭解其他用法也好。
SUMIF跟COUNTIF函數有點相似，只要理解一個，要瞭解另一個就簡單了。多條件求和SUMIFS跟COUNTIFS也比較常見，有興趣的話可以瞭解一下。

4.2.4　無處不在的IF

盧子：只要留心觀察，生活中到處充滿IF，我們每天都在跟IF打交道。
如果明天下暴雨，我就不去上班。
如果有你陪在我身邊，我會很開心。
如果有網路，我就上網，否則睡覺。
如果你來了，請你喝功夫茶，否則我自己喝。
如果2007年畢業不去東莞，我就不會接觸Excel；如果不學習Excel，我就不會去論壇、交流群；如果不交流，我就不會認識這麼多Excel愛好者；如果……，否則一切都是空談。
還有太多太多的例子，不知道你們看到這裡有什麼想法。
網友：很多時候我們都是在做假設，有的時候只有一個假設，有的時候多個假設同時出現。有假設就有相對應的返回值，有的只有一個，有的是兩個。
今朝：IF其實就是如果的意思，在閒聊SUM的時候就說過這個函數的用法。IF（條件，條件為真返回值，條件為假返回值），現在就以上面幾條如果來聊聊IF函數。如果你細心的話，可以看到前2條都只返回一個對應值。
網友：對哦，不是說這個函數有三個參數嗎，怎麼現在兩個也行？
今朝：如果明天不下暴雨，肯定要去上班；如果你不陪在我身邊，我就開心不起來。這個不用說別人都知道了，其實IF函數也跟我們一樣不廢話。
    =IF（A1=＂明天下暴雨＂，＂不去上班＂）
    =IF（A2=＂你陪在我身邊＂，＂很開心＂）
第3個參數省略，就是返回FALSE，這是約定俗成的東西。明明知道要返回FALSE，而你還在後面添加其他東西就有點多此一舉了。
接著看，如果你不說明第3個參數，不知道你要返回什麼，這時就必須強調。返回值1跟返回值2不一定有關聯。
    =IF（A3=＂有網路＂，＂上網＂，＂睡覺＂）
    =IF（A4=＂你來了＂，＂請你喝功夫茶＂，＂自己喝＂）
網友：這麼說就好理解了。在確定返回值的時候，可以省略第3個參數；在不確定返回值時，必須寫上第3個參數。
今朝：接著看最後1條，很多事情並不一定只有一個如果，一個如果會產生無數個如果，前面的選擇會影響到後面的所有選擇。就如你選擇學Excel跟選擇學PS，你將走兩條完全不同的路。認識的人不同，做的事也不同。
    =IF（A5=＂東莞＂，＂接觸Excel＂，IF（A6=＂學習Excel＂，＂去論壇、交流群＂，IF（A7=＂交流＂，＂認識Excel愛
    好者＂，＂空談＂）））
為了加深理解，下面用幾個小例子來說說邏輯函數。圖4-72是今朝學淘寶的模擬圖。

圖4-72　今朝學淘寶
例子1：如果B2是今朝，最近要學淘寶，否則待定。
    =IF（B2=＂今朝＂，＂最近要學淘寶＂，＂待定＂）
例子2：如果B2是今朝且C2是不會，就慘了，否則待定。
    =IF（AND（B2=＂今朝＂，C2=”不會＂），＂慘了＂，＂待定＂）
“且”就是同時滿足的意思，AND（條件1，條件2，條件N），即同時滿足所有條件才顯示TRUE，否則顯示FALSE。AND也可以用*表示，通過運算得到1或者0，任何非0數位就是TRUE，0就是FALSE。
    =IF（（B2=＂今朝＂）*（C2=＂不會＂），＂慘了＂，＂待定＂）
例子3：如果B2是紫陌、冷逸、月亮其中一個就會淘寶，否則不會。
    =IF（OR（B2=＂紫陌＂，B2=＂冷逸＂，B2=＂月亮＂），＂會＂，＂不會＂）
OR（條件1，條件2，條件N），只要滿足其中一個條件就顯示TRUE，否則顯示FALSE。OR可以換成+，有興趣的朋友可以試試看。
邏輯函數是基礎，經常會用到，必須熟練掌握。接著瞭解IF（{1，0}，區域1，區域2）這種常用的形式，學好它有助於以後對VLOOKUP反向查找的理解。下面以圖4-73所示國花網路投票表進行說明。

圖4-73　國花網路投票表

向目標前進一步

    =IF（1，B4，C4）
    =IF（0，B4，C4）
條件為1，返回B4的對應值“杜鵑花”；條件為0，返回C4的對應值24。

向目標再前進一步

    =IF（{1，0}，B4，C4）
    =IF（{0，1}，B4，C4）
選擇兩個儲存格輸入，然後按複合鍵Ctrl+Shift+Enter結束。條件為{1，0}，返回B4：C4的對應值順序不變；條件為{0，1}，返回B4：C4的對應值，順序對換。也就是說，通過改變1跟0的位置，可以調換兩個儲存格的前後位置。

走向目標

    =IF（{1，0}，B4：B9，C4：C9）
    =IF（{0，1}，B4：B9，C4：C9）
選擇兩列輸入，然後按複合鍵Ctrl+Shift+Enter結束。跟我們用儲存格表示看到的差不多，通過改變1跟0的位置，可以調換兩個區域的前後位置。
三種形式的效果，如圖4-74所示。

圖4-74　三種效果
如果不瞭解這個用途沒關係，先記下。下一節課“學VLOOKUP，認識Excel愛好者”逆向查找就會用到這個組合。這節課看似簡單，但都是為了以後學好其他函數做鋪墊。
網友：謝謝盧子的講解，雖然基礎，但內容不枯燥，比看幫助強多了。

4.2.5　學VLOOKUP，認識Excel愛好者

盧子：據網路調查VLOOKUP、SUM、IF是使用頻率最高的三個函數，當初就是見識了VLOOKUP的神奇才讓我深深地迷戀上函數。一起來學VLOOKUP函數，並認識一些Excel的愛好者。這些愛好者都是我在網路上結識的，通過跟他們交流才使我的Excel水準更上一層樓。
網友：聽說VLOOKUP函數很強大，這回一定要學學。
盧子：我們還是看實例吧。
例子1：如圖4-75所示，“簡單”是哪個地區的人？

圖4-75　“簡單”是哪個地區的人
    =VLOOKUP（F4，A2：D7，2，0）
原來是安徽的，那裡的黃山好出名，被世人譽為“天下第一奇山”，有機會真想去那裡見識一下。
    =VLOOKUP（查找值，區域，區域中第N列，查找模式）

參數說明

查找值：要去區域A2：D7查找的條件值；
區域：首列包含F4的區域；
區域中第N列：如區域A2：D7中的第2列就是返回地區；
查找模式：0代表精確查找，1代表模糊查找（省略也是模糊查找），90％的情況都是用精確查找。
網友：報告老師，你是不是忘記曬幫助了？
盧子：説明是需要自己自覺去瞭解的，前面之所以一直反復強調看幫助，是因為幫助是我們最好的老師，但有的東西說多了反而惹人煩。就如你去吃飯，別人第一次告訴你這個好吃，你吃了覺得還真的好吃，你會感謝他；第二回、第三回他告訴你好吃，你應該不會再感謝他，只是禮貌性地說“好的”；第N回他告訴你好吃，你肯定覺得煩，你有完沒完，這個誰不知道好吃。以後覺得幫助好，要自覺看，就像看到好吃的，別人不告訴你，你都會多吃幾塊。閒話少說，進入主題。
例子2：如圖4-76所示，“笑看今朝”的性別。

圖4-76　“笑看今朝”的性別
    =VLOOKUP（F9，A2：D7，3，0）
網友：怎麼回事，感覺公式沒錯啊，怎麼返回#N/A？
盧子：#N/A是區域中找不到對應值的原因。公式沒錯，那就是資料來源的問題，在輸入資料的時候，有時會不小心按了空格。肉眼看不出，但實際上會造成影響。先用LEN函數測試儲存格都存在幾個字元。如圖4-77所示，資料來源輸入規範，沒有問題，問題出在查找值多了一個空格。

圖4-77　字元測試
網友：原來是多了個空格，利用替換功能將空格替換掉就OK了。
盧子：沒錯，其實函數中有專門去除掉多餘空格的函數TRIM。
    =VLOOKUP（TRIM（F9），A2：D7，3，0）
網友：“笑看今朝”是男的，這回就對了。“笑看今朝”應該就是盧子你自己吧？
盧子：笑看今朝是我的QQ名，論壇名我叫盧子。如果返回錯誤值，要學會推測，出現錯誤值的原因很多，如文本型數位跟數值，看似一樣，但價值不同。錯誤值也不是一無是處，善於利用錯誤值也能產生價值。例子3：如圖4-78所示，判斷Simple有沒有在我列舉的人員清單上。

圖4-78　判斷Simple有沒有在我列舉的人員清單上
    =VLOOKUP（F15，A2：D7，1，0）
查找不到就返回#N/A，ISNA可以檢測是不是存在#N/A這種錯誤值，存在就顯示TRUE，否則顯示FASLE。
    =IF（ISNA（VLOOKUP（F15，A2：D7，1，0）），＂否＂，＂在＂）
如圖4-79所示，IS類函數作用的對應表。

圖4-79　IS類函數作用的對應表
網友：善於利用各種錯誤，也能變廢為寶，長見識了。
盧子：很多時候我們叫一個人的名字不會叫全名，就如笑看今朝，叫成盧子。無言的人，叫成無言。在我們潮汕地區，叫全名反而是對人的一種不尊重。我的親戚朋友都叫我盧，感覺這樣好親切。聽說北方人就喜歡叫全名，每個地方的風俗習慣不同。
例子4：如圖4-80所示，“無言”是哪個地區的人。

圖4-80　“無言”是哪個地區的人
    =VLOOKUP（＂*＂＆F20＆＂*＂，A2：D7，2，0）
星號是萬用字元，代表所有字元，問號代表一個字元。
網友：無言原來也是你們潮汕地區的，你們那邊的Excel愛好者還真不少。潮州菜、功夫茶聽說很出名，有機會真想去那邊品嘗一下。
盧子：好啊，如果你們來了，我做東。
例子5：如圖4-81所示，按順序查找“坤哥”的各項資料。

圖4-81　按順序查找“坤哥”的各項資料
通過上面的例子，我們知道可以通過更改第3個參數，返回各項對應值。
    =VLOOKUP（I4，$A$2：$D$7，2，0）
    =VLOOKUP（I4，$A$2：$D$7，3，0）
    =VLOOKUP（I4，$A$2：$D$7，4，0）
如果項目少，更改幾次參數也沒什麼，但專案多了，肯定不方便。如圖4-82所示，可以通過ROW、COLUMN產生行列號，從而得到1，2，…，n的值。

圖4-82　通過ROW、COLUMN產生行列號
    =VLOOKUP（$I4，$A$2：$D$7，COLUMN（B1），0）
因為這裡是為同一行產生序號，所以用COLUMN。
因VLOOKUP函數而結識坤哥，好久以前我們都在論壇幫人解答問題，我每次都使用VLOOKUP函數，而坤哥每次都使用LOOKUP函數，上演了一場真實版的《VLOOKUP與LOOKUP一一過招》。看他資料是潮汕的，加為QQ好友。沒想到聊得不錯，最後成為朋友。
例子6：如圖4-83所示，不按原來順序查找“吳姐”的各項資料。

圖4-83　不按原來順序查找吳姐的各項資料
網友：這回看來只能手動更改第3個參數了，COLUMN完全派不上用場。
盧子：每當你覺得操作煩瑣時，就要停下來，也許Excel本身存在這個功能，只是自己一時想不到或者不知道而已。列號不管千變萬化，在資料來源的位置始終不變，利用這個特點可以搜索一下看看有什麼函數可以解決。
如圖4-84所示，在“搜索函數”文字方塊裡輸入“位置”，按一下“轉到”按鈕，就會出現跟“位置”有關的函數。查看每個函數的說明，找到我們需要的，如MATCH，返回符合特定值、特定順序的項在陣列中的相應位置，按一下“確定”按鈕。

圖4-84　搜索函數的用法
如圖4-85所示，在彈出的“函數參數”對話方塊中嘗試填寫相應的參數。每個參數的作用下面都有相關說明，填寫後會出現計算結果3，也就是性別在區域中是第3列。嘗試更改第1個參數為J8（人氣指數），計算結果是4，也就是區域中的第4列。經過嘗試，知道這個函數是我們要找的函數，按一下“取消”按鈕，返回工作表。

圖4-85　填寫相應的參數
如圖4-86所示，在儲存格中再做最後一次驗證。

圖4-86　驗證MATCH用法
到這一步已經十拿九穩了，將公式設置為：
    =VLOOKUP（$I9，$A$2：$D$7，MATCH（J$8，$A$2：$D$2，0），0）
吳姐邏輯思維很好，剛開始很多人都誤以為她是男的，後來有知情人士爆料才知道是女的，真是巾幗不讓鬚眉，女中豪傑。不愧是來自上海這座大城市的，見多識廣。
其實跟這些Excel愛好者認識都有一段故事，這裡就不再一一講述了。
網友：一邊學函數，一邊聽故事，真好。
盧子：繼續講VLOOKUP函數的用法。
例子7：如圖4-87所示，根據Excel愛好者，逆向查找地區。

圖4-87　根據Excel愛好者，逆向查找地區
説明提到VLOOKUP函數只能按首列查找，不能逆向查找。既然如此，那就得想辦法將非首列的區域轉換成首列。通過對IF函數的學習，知道通過{1，0}這種形式可以將各列調動位置，那我們就通過IF函數重新構造一個新的區域。如圖4-88所示，現在在這個新區域中Excel愛好者就是首列，符合按首列查找的條件。

圖4-88　構造新區域
    新區域=IF（{1，0}，C2：C7，A2：A7）
    =VLOOKUP（F3，新區域，2，0）
將兩條公式合併：
    =VLOOKUP（F3，IF（{1，0}，$C$2：$C$7，$A$2：$A$7），2，0）
網友：這個想法太好了，以後再也不用為了逆向查找而煩惱。
盧子：最後一個例子，模糊查找的運用。
例子8：如圖4-89所示，根據人氣指數，判斷等級，[0，59]為★，[60，79]為★★，[80，100]為★★★。

圖4-89　判斷等級
    =VLOOKUP（D2，{0，＂★＂；60，＂★★＂；80，＂★★★＂}，2）
第4個參數省略就是按模糊查找，0、60、80是劃分區域間用的，如80就是人氣指數大於或等於80的就返回★★★。
人氣指數為隨機值，虛擬的。
網友：這些星號怎麼輸入的？
盧子：借助搜狗輸入法的軟鍵盤實現的。如圖4-90所示，這裡提供了很多特殊字元，很好用。

圖4-90　軟鍵盤的使用
關於VLOOKUP函數就說到這裡，下一回瞭解LOOKUP函數，它是一個可以取代VLOOKUP所有功能的神奇函數。HLOOKUP函數因為設計表格的關係，很少用到水準查找，就不做說明了。
網友：VlOOKUP查找資料確實很好用，聽說這個函數還是泡妞利器，回頭一定要加深理解。

4.2.6　LOOKUP潮汕

網友：這個LOOKUP函數有什麼好學的，説明都提到，如果區域沒昇冪會可能導致出錯，既然這樣那作用明擺著就很小。
幫助：
為了使LOOKUP函數能夠正常運行，必須按昇冪排列查詢的資料。如果無法使用昇冪排列資料，請考慮使用VLOOKUP、HLOOKUP或MATCH函數。
盧子：幫助把LOOKUP當垃圾看，但卻被Excel愛好者發掘出各種各樣的功能，有人把LOOKUP函數比喻成查找之王。LOOKUP不因被幫助埋沒而被人遺棄，反而受到高手的追捧。一起來見證LOOKUP的神奇吧。
例子1：我們潮汕人都喜歡喝茶，那就以茶為例。如圖4-91所示，查找最後喝幾杯茶和最後喝什麼茶，也就是說查找最後一個數位跟文本。

圖4-91　查找最後喝幾杯茶和最後喝什麼茶
    =LOOKUP（9E+307，B：B）
    =LOOKUP（＂座＂，A：A）
網友：這個9E+307跟“座”是什麼意思？
盧子：先來看看下面幾條公式：
=LOOKUP（10，{4；8；6；1；7；5；6；4；6；9}），返回9
=LOOKUP（100，{4；8；6；1；7；5；6；4；6；9}），返回9
=LOOKUP（1000，{4；8；6；1；7；5；6；4；6；9}），返回9
也就是說，LOOKUP函數查找到最後一個滿足條件的值，在數位不確定的情況下，查找的值越大也能保證查找到的值的準確性。9E+307一個很大很大的數字，Excel允許最大的數字不能超過15位元，而9E+307是9乘以10的307次方，比最大值還要大，查找最後一個值是相當的保險。“座”是一個接近最大的文本，雖然還有比“座”還大的文本，但正常情況不會出現，所以寫“座”就能查找到最後一個文本。
其實LOOKUP函數是用二分法查找的，但二分法很不好理解，不過即使不會二分法照樣學好LOOKUP。下面說一個跟二分法有關的小故事。

戲說LOOKUP與美女

IT部落新來了四個美女，其中紫陌為最佳人選。不過要認識紫陌得通過LOOKUP大哥的考核才行。（考核就是根據排位元，選擇數位，匹配到合適的人員）LOOKUP大哥行事穩重，一言九鼎。
盧子聽到消息，趕緊跑來和LOOKUP大哥搞好關係，想通過他認識紫陌。
第一天
盧子一早找到LOOKUP大哥，嬉皮笑臉地說明來意。LOOKUP大哥也是個爽快之人，一口答應。不過言歸正傳，還是得通過考核才行。盧子選擇了4，=LOOKUP（4，{1；2；3；5}，{＂冷逸＂；＂月亮＂；＂小影＂；＂紫陌＂}）。LOOKUP大哥說：給你介紹小影好了。盧子心裡明白，小影哪比得上紫陌，於是說了一堆好話討好LOOKUP大哥。可惜LOOKUP大哥照章辦事，說紫陌排位在你後面，不能介紹她給你認識，只能介紹排位元在你之前的人。沒辦法，盧子只有走了。
第二天
盧子又來了。LOOKUP大哥看盧子不死心，又出一題。選擇了4，=LOOKUP（4，{1；2；3；5}，{＂冷逸＂；＂紫陌＂；＂月亮＂；＂小影＂}）。LOOKUP大哥說：給你介紹月亮好了。盧子立馬反駁：你昨天不是說，只要排位比她大就行嗎，怎麼不是紫陌？LOOKUP大哥慢條斯理地說：比她大沒錯，不過不能相差太遠，越接近越好。無奈，盧子只好走了。
第三天
盧子依然前來。LOOKUP大哥看盧子蠻有誠意，再出一題。選擇了4，=LOOKUP（4，{1；2；3；3}，{＂月亮＂；＂小影＂；＂紫陌＂；＂冷逸＂}）。LOOKUP大哥說：給你介紹冷逸好了。盧子有點惱火：不是已經滿足排位比她大，且最接近，怎麼還……LOOKUP大哥不慌不忙地說：沒錯，不過還有一個條件，就是只有最後一個滿足條件的人才可以介紹給你。盧子聽到這裡差點吐血，沒辦法，只能走。
第四天
不達目的誓不甘休，盧子信心滿滿地又來了。摸清LOOKUP大哥的底細，紫陌非我莫屬，不會再有意外了！LOOKUP大哥直接拋出題。盧子選擇了4，=LOOKUP（4，{1；5；2；3}，{＂小影＂；＂月亮＂；＂冷逸＂；＂紫陌＂}）。沒等LOOKUP大哥開口，盧子就說：這回是紫陌沒錯吧，哈哈。LOOKUP大哥搖搖頭說：不是，是小影。盧子這回生氣了：你耍賴，明明三個條件都滿足了怎麼還不是？LOOKUP大哥依然那樣鎮定：沒錯是小影。你看，這4個排位，我們分成兩半，中間就是5和2。我們先看5，你排位比她小，就只能看她之前的數，之前就只有1符合，不就是小影嗎？天啊！難道這是天意，盧子跟紫陌無緣。盧子長歎一聲，走了。回家思索良久，好像似有所悟，嘴角露出笑意。
第五天
盧子沒有退卻，依然前來。LOOKUP大哥又出題。盧子這回吸取前幾回的教訓，知道LOOKUP大哥非等閒之輩，對於他的問題要仔細思考才行。思索了好一陣子，盧子選擇了9，=LOOKUP（9，{1；5；2；3}，{＂小影＂；＂月亮＂；＂冷逸＂；＂紫陌＂}）。LOOKUP大哥點了點頭，說：如果排位元每個數字擴大100倍呢？盧子選擇了900，=LOOKUP（900，{1；5；2；3}*100，{＂小影＂；＂月亮＂；＂冷逸＂；＂紫陌＂}）。LOOKUP大哥又點了下頭，說：假如排位不確定呢？盧子選擇了9E+307，=LOOKUP（9E+307，{1；5；2；3}*1000，{＂小影＂；＂月亮＂；＂冷逸＂；＂紫陌＂}）。LOOKUP大哥這時終於說了一聲：好，我再出最後一題，答對了就滿足你的要求。盧子選擇了＂座＂，=LOOKUP（＂座＂，{＂小影＂；＂月亮＂；＂冷逸＂；＂紫陌＂}，{1；5；2；3}）。這回終於看到LOOKUP大哥露出笑臉：寶座非同一般，一人之下，萬人之上，既然你連這個也知道，不愧是個人才，好好努力，前途無量！
紫陌，出來認識一下盧子。此刻，盧子臉紅了，略帶羞澀……
五天的考驗，終於贏得美人歸。不過盧子已經耗累了，需要好好休息一下。這樣以後才有精力跟紫陌交朋友，呵呵！
只要肯付出總會有收穫，現在沒有收穫並不代表以後沒有，加油吧，盧子！圖4-92為輔助理解示意圖。

圖4-92　LOOKUP排位示意圖
網友：這樣生動的學習，對新人來說太好了。
盧子：LOOKUP喜歡以大欺小，LOOKUP的經典查找模式更將這個秉性發揮得淋漓盡致，完美地展現出來。VLOOKUP函數逆向查找需要重新構造資料來源，很麻煩，下面看看LOOKUP是怎麼解決這個問題的。
潮州話被稱為中國最難聽懂的語言之一，但最近卻有人將潮州話用英語寫譯音，將潮州話帶上世界的舞臺。
例子2：如圖4-93所示，根據譯音查找潮州話。

圖4-93　根據譯音查找潮州話
    =LOOKUP（1，0/（$B$2：$B$10=D2），$A$2：$A$10）
利用F9鍵將公式層層剝開來理解。
    =LOOKUP（1，0/（$B$2：$B$10=D2），$A$2：$A$10）
    =LOOKUP（1，0/{FALSE；FALSE；FALSE；FALSE；TRUE；FALSE；FALSE；FALSE；FALSE}，$A$2：$A$10）
通過比較，將符合條件的轉換成TRUE，不符合條件的轉換成FALSE。
    =LOOKUP（1，0/{FALSE；FALSE；FALSE；FALSE；TRUE；FALSE；FALSE；FALSE；FALSE}，$A$2：$A$10）
    =LOOKUP（1，{#DIV/0！；#DIV/0！；#DIV/0！；#DIV/0！；0；#DIV/0！；#DIV/0！；#DIV/0！；#DIV/0！}，$A$2：$A$10）
0/（條件）將TRUE轉換成0，FALSE轉換成錯誤值#DIV/0！，構成一個由0跟#DIV/0！組成的陣列。利用LOOKUP函數找不到LOOKUP_value，則該函數會與LOOKUP_vector中小於或等於LOOKUP_value的最大值進行匹配。用1在0跟#DIV/0！中查找，查找到小於或等於1的最大值也就是0的位置，然後返回result_vector（即區域$A$2：$A$10）。
經過上面的推斷，可以將公式進一步擴展，變成通用公式：
    =LOOKUP（1，0/（（條件1）*（條件2）*…*（條件n）），返回區域）
有了這個經典查找的通用公式，神馬查找都So easy！不信的話，接著看看其他各種各樣的查找。
例子3：如圖4-94所示，根據人均GDP排名獲取前三名的地區跟區縣。

圖4-94　2011潮汕三市人均GDP排名
    =LOOKUP（1，0/（ROW（A1）=$F$2：$F$19），A$2：A$19）
通過ROW（A1）產生1、2、3，跟排名比較就可以得到前三名的對應值，但這個公式不嚴謹，超過前三名還是會顯示出來，最後再加一個判斷，讓ROW（A1）大於3顯示空。
    =IF（ROW（A1）＞3，＂＂，LOOKUP（1，0/（ROW（A1）=$F$2：$F$19），A$2：A$19））
例子4：如圖4-94所示，根據GDP（元）獲取前三名的地區跟區縣。
    =IF（ROW（A1）＞3，＂＂，LOOKUP（1，0/（LARGE（$C$2：$C$19，ROW（A1））=$C$2：$C$19），A$2：A$19））
理解LARGE函數：
    =LARGE（$C$2：$C$19，1）
    =LARGE（$C$2：$C$19，2）
    =LARGE（$C$2：$C$19，3）
就是獲取前三大的對應值，前三大跟排名前三是一個意思（忽略GDP一樣有這種可能性）。N可以通過ROW（A1）獲取，剩下的就跟例3一樣。圖4-95就是這兩種方式的GDP前三名效果圖，從效果圖可以看出，人均排名靠前不一定總GDP靠前，這個還跟人口有很大關係。

圖4-95　兩種方式的GDP前三名效果圖
例子5：如圖4-96所示，從一句話中獲取小吃，D列為小吃對應表。

圖4-96　從一句話中獲取小吃
網友：VLOOKUP函數搞不定以多查少，難道LOOKUP函數可以，不太相信。
盧子：眼見為實，見證奇跡的發生。
    =LOOKUP（1，0/FIND（$D$2：$D$16，A2），$D$2：$D$16）
這裡用到了逆向思維法，把A2當成資料來源，把$D$2：$D$16當成查找值，跟我們傳統思想相反。FIND（$D$2：$D$16，A2）判定$D$2：$D$16是否存在A2，如果有就返回相應的位置，沒有就返回錯誤值。
    =LOOKUP（1，0/FIND（$D$2：$D$16，A2），$D$2：$D$16）
按F9鍵看運算過程。
    =LOOKUP（1，0/{#VALUE！；5；#VALUE！；#VALUE！；#VALUE！；#VALUE！；#VALUE！；#VALUE！；#VALUE！；#VALUE！；# VALUE！；#VALUE！；#VALUE！；#VALUE！；#VALUE！}，$D$2：$D$16）
利用0除以任何非0數位等於0的特點，將5也轉換成0，剩下就是以大欺小法的經典用法了。
網友：LOOKUP…
盧子：LOOKUP也能提取數字，你信嗎？不管你信不信，反正我信了。
例子6：如圖4-97所示，提取消費項目的金額。

圖4-97　提取金額
網友：這個打死我也不信。
盧子：呵呵，打不死，你就信了。
    =-LOOKUP（1，-RIGHT（A2，ROW（$1：$15）））
ROW函數現在我們已經很熟悉了，就是獲取行號，ROW（$1：$15）獲取1～15。那現在看看RIGHT的語法：
RIGHT（文本，提取右邊N位元）
=RIGHT（A2，1）得到0
=RIGHT（A2，2）得到50
=RIGHT（A2，3）得到費50
=RIGHT（A2，4）得到油費50
=RIGHT（A2，15）得到油費50
=RIGHT（A2，ROW（$1：$15））也就是獲取右邊1到15位={＂0＂；＂50＂；＂費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂；＂油費50＂}
=-RIGHT（A2，ROW（$1：$15））就是將數位變成負數，將文本變成錯誤值。
根據以大欺小法的原則=LOOKUP（1，-RIGHT（A2，ROW（$1：$15）））會提取到最後一個數字-50。既然將數字變成負數，就得想辦法將它復原，再加一個負號就可以了，負負得正。
    =-LOOKUP（1，-RIGHT（A2，ROW（$1：$15）））
網友：為什麼要提取1～15位而不是提取1到更多呢？
盧子：Excel允許的最大數字剛好是15位元，提取再多也沒有意義，只要保證能提取到全部數位就行。
網友：原來是這樣，那我將ROW（$1：$4）就行，正常消費超過萬元的可以忽略。
盧子：這樣也行，挺會取巧的。
網友：謝謝誇獎。
盧子：說了那麼多，最後一題就留給你們自己發揮。
例子7：如圖4-98所示，將合併儲存格填充，如A列效果。

圖4-98　將合併儲存格填充
網友：其實早就想發揮了，LOOKUP實在太強大了。
    =LOOKUP（＂座＂，B$2：B2）
    =LOOKUP（1，0/（B$2：B2＜＞＂＂），B$2：B2）
盧子：不錯嘛，這麼快就學會LOOKUP了。
網友：你教得好，當然學得快。LOOKUP真的強大得變態。

4.2.7　經典的INDEX+MATCH組合

盧子：函數與公式的神奇之處我認為在於對同一道題目，可以有多種解法。曾經我試過查找符合條件的資料，寫了30多種組合，不過其中有一半是湊數用的。在剛開始學習的時候，要儘量瞭解更多種用法，當你熟練以後就選擇你認為最適合的一種方法就可以了，其他的可以忽略。每個時期你對同一問題的想法都會不停地改變，就如我剛開始很喜歡用VLOOKUP查找，接著發現LOOKUP好用，後來發現INDEX+MATCH組合變幻莫測，感覺很多事情離開這個組合都很難做到一樣。
網友：既然你這麼說了，就學學看這個組合有多經典，多認識幾個函數也是好事。
盧子：其實公式就跟小朋友玩的積木一樣，按需要的模型找到合適的小形狀堆積而成。只要你將寫公式當成在玩積木，玩的同時就不知不覺學好公式了。下面通過幾個例子看看公式是怎麼堆積而成的。
MATCH前面我們已經知道，其作用是獲取專案在區域中的排位元。現在來看看INDEX，語法：INDEX（區域，行號，列號），是對區域的行列號交叉值的引用。下面通過一個簡單的小例子來說明INDEX的用法。
如圖4-99所示，在查詢表中查詢產品的價格。

圖4-99　產品價格查詢
型號所在行號為第8行：
    =MATCH（B3，D1：D10，0）
規格所在列號為第3列：
    =MATCH（B7，D1：G1，0）
產品價格，就是第8行跟第3列的交叉儲存格即70。
    =INDEX（D1：G10，B4，B8）
將前面三條公式組合起來，經典的組合就這麼誕生了。
    =INDEX（D1：G10，MATCH（B3，D1：D10，0），MATCH（B7，D1：G1，0））
網友：原來利用這個組合，多條件查詢這麼簡單。
盧子：現在來瞭解這個組合的擴展運用。
例子1：如圖4-100所示，對行列號匯總。

圖4-100　行列匯總
查找行號：
    =MATCH（C3，E1：E10，0）
行匯總：
    =SUM（INDEX（F2：H10，C4，0））
兩個合併：
    =SUM（INDEX（F2：H10，MATCH（C3，E2：E10，0），0））
剛開始不熟練這個組合，可以先拆開，然後再組合起來，這樣便於理解。看一下幫助。
注解：如果同時使用參數Row_num和Column_num，函數INDEX返回Row_num和Column_num交叉處儲存格中的值。
如果將Row_num或Column_num設置為0，函數INDEX則分別返回整個列或行的陣列數值。若要使用以陣列形式返回的值，請將INDEX函數以陣列公式形式輸入，對於行以水準儲存格區域的形式輸入，對於列以垂直儲存格區域的形式輸入，若要輸入陣列公式，請按複合鍵Ctrl+Shift+Enter。
注釋：在Excel Web App中，不能創建陣列公式。
Row_num和Column_num必須指向陣列中的一個儲存格，否則INDEX返回錯誤值#REF！。
如果行列號或者列號被設置為0，函數分別返回整個列或行的陣列數值。也就是說，剛才為什麼將INDEX的第3個參數設置為0，就是為了引用整行的資料。
有了前面的基礎，我們現在一步到位求列總計。
    =SUM（INDEX（F2：H10，0，MATCH（C8，F1：H1，0）））
例子2：如圖4-101所示，對區域匯總。

圖4-101　對區域匯總
SUM函數的區域可以理解為：
=SUM（開始儲存格：結束結束儲存格），如=SUM（G16：H21）。
只要將區域轉換成上面的形式就行，知道開始跟結束儲存格，就能匯總。
開始儲存格為97：
    =INDEX（F16：H24，MATCH（C16，E16：E24，0），MATCH（C18，F15：H15，0））
結束儲存格為79：
    =INDEX（F16：H24，MATCH（C17，E16：E24，0），MATCH（C19，F15：H15，0））
匯總的區域就是這兩個數位組成的區域G17：H21組合起來：
    =SUM（INDEX（F16：H24，MATCH（C16，E16：E24，0），MATCH（C18，F15：H15，0））：INDEX（F16：H24，MATCH（C17，
    E16：E24，0），MATCH（C19，F15：H15，0）））
其實公式又好像是牛，光看牛不知道牛的內部結構，只有操刀將牛分解才知道牛的內部構造。很多時候我們看到別人寫的公式很長很長，不知道什麼意思，就可以用庖丁解牛法解讀。
來看INDEX跟其他函數的高級組合，讓我們一起操刀，當一回庖丁，將牛大卸八塊，好好理解它的內部結構。
例子3：如圖4-102所示，將左邊的格式變身成右邊的形式。

圖4-102　格式變身
提取不重複地市：
    =INDEX（A：A，SMALL（IF（MATCH（$A$2：$A$19，$A$2：$A$19，0）=ROW（$A$2：$A$19）-1，ROW（$A$2：$A$19），4＾8），ROW（A1）））＆＂＂
根據不重複地市獲取所有區縣對應值：
    =INDEX（$B：$B，SMALL（IF（$A$2：$A$19=$D2，ROW（$A$2：$A$19），4＾8），COLUMN（A1）））＆＂＂
網友：這麼長，還沒把它大卸八塊，自己就先暈倒了。
盧子：當初我看到這兩條公式也嚇了一跳，不過後來轉念一想，公式拆開每個函數我都會，組合起來我應該也可以弄懂才對。
網友：也對哦，不能先被困難嚇倒。
盧子：那我們就來庖丁解牛，呵呵。
先來看看1、2、3，如圖4-103所示，庖丁解牛1：
=MATCH（$A$2：$A$19，$A$2：$A$19，0），得到每個地市在資料來源第一次出現的位置，如1。
=ROW（$A$2：$A$19）-1，獲取1到N的序號，如2。
=MATCH（$A$2：$A$19，$A$2：$A$19，0）=ROW（$A$2：$A$19）-1，將第一次出現的問題跟序號比較，如果一樣就顯示TRUE，否則顯示FALSE，如3。

圖4-103　庖丁解牛1
接著看4和5，如圖4-104所示，庖丁解牛2。

圖4-104　庖丁解牛2
為了便於解讀，將MATCH（$A$2：$A$19，$A$2：$A$19，0）=ROW（$A$2：$A$19）-1設置為牛1：
=IF（牛1，ROW（$A$2：$A$19），4＾8），通過牛1知道，排位跟序號相同就是TRUE，不同就是FALSE。通過IF將相同的顯示本身的序號，不同的顯示48即65536。2003版允許的最大行數，這一行通常是沒有資料的，也可以將48改成任意一個比較大的數，如10000。最後獲得由本身行號跟65536組成的區域，如4。
=SMALL（IF（牛1，ROW（$A$2：$A$19），4＾8）， ROW（A1）），SMALL（區域，N）就是將資料昇冪排序，也就是說將第一次出現的地市的序號放在最前面，如5。
經過這兩次庖丁解牛，已經完成了80％的工作，只需再解牛一次即可搞定。
最後看6和7，如圖4-105所示，庖丁解牛3。

圖4-105　庖丁解牛3
=INDEX（A：A，牛2），獲得序號的對應值65536，因為是空儲存格，引用過來就是0，如6。
=INDEX（A：A，牛2）＆＂＂，將引用過來的0轉變成空文本，這樣看起來美觀點，如7。
本來還想將公式大卸八塊，現在才七塊就搞定了，看來公式還不夠長。
網友：盧子你還真幽默，解牛三次，大切成七塊，厲害。
盧子：有了這次的剖解，下面這條公式就變得簡單多了，重點看不同的地方即可。
    =INDEX（$B：$B，SMALL（IF（$A$2：$A$19=$D2，
    ROW（$A$2：$A$19），4＾8），COLUMN（A1）））＆＂＂
$A$2：$A$19=$D2就是區域$A$2：$A$19跟$D2的比較，返回TRUE跟FASLE。
IF（$A$2：$A$19=$D2，ROW（$A$2：$A$19），4＾8）讓符合條件的顯示本身行號，否則顯示48。
SMALL（IF（$A$2：$A$19=$D2，ROW（$A$2：$A$19），4＾8），COLUMN（A1）），因為公式是向右拖拉，COLUMN（A1）可以水準獲得序號，從而得到前N個最小值。
=INDEX（$B：$B，SMALL（IF（$A$2：$A$19=$D2，ROW（$A$2：$A$19），4＾8），COLUMN（A1）））＆＂＂讓符合條件的值顯示出來，不符合的顯示空。
網友：沒想到這麼長的公式還能聽懂，真的佩服我自己。
盧子：通過這幾回的講解，公式與函數常見的用法，跟公式編寫、解讀的技巧都講得差不多了，剩下的就靠我們自己靈活運用了。要學會選擇合適自己的方法。

4.2.8　OFFSET的運用

盧子：今天一起來學習OFFSET函數的運用，這個函數的使用頻率遠遠比不上前面的那些函數，用得最多的就是定義動態名稱法，輔助透視表用。

語法

OFFSET（引用，偏移行數，偏移列數，行高，列寬）
行高、列寬均為可選參數。
語法詳解：
將A1下移4格=OFFSET（A1，4，0），如圖4-106所示。

圖4-106　下移4格
將A1右移4格=OFFSET（A1，0，4），如圖4-107所示。

圖4-107　右移4格
將A1下移3格，右移2格=OFFSET（A1，3，2），如圖4-108所示。

圖4-108　下移3格右移2格

實戰

獲取奇數月的銷售額，如圖4-109所示。

圖4-109　獲取奇數月份名稱
還是以A1作為起點，奇數月就是下移1，3，…，11行，月份只需再偏移1列即可。
那怎麼產生奇數行呢？
網友：在前面多次提到ROW函數，用它可以獲得行號，奇數就是2*ROW（A1）-1。
盧子：腦袋轉得挺快的。
    =OFFSET（$A$1，ROW（A1）*2-1，1）
如果以B1為開始，就不用偏移列了。
    =OFFSET（$B$1，ROW（A1）*2-1，0）
網友：這些都懂了，你說下參數——行高、列寬是怎麼回事？
盧子：？？？

語法詳解

將A1下移1格，右移1格，就得到B2，如圖4-110所示。

圖4-110　移動儲存格
B2行高為3，列寬為2，就是B2：C4這個區域。
    =OFFSET（A1，1，1，3，2）
網友：怎麼我在儲存格中輸入會報錯呢，如圖4-111所示。難道我的輸入方法有誤？

圖4-111　輸入出錯
盧子：B2：C4是一個區域，直接在一個儲存格中輸入當然會出錯！一個儲存格只能容納一個數，你現在要讓它容納6個數，哪裡容納得下！
網友：有點道理。
盧子：OFFSET一般都是作為其他函數的過渡，如對動態區域進行求和。

實戰

例子1：如圖4-112所示，對一個動態區域求和。

圖4-112　以A1為基點對引用區域匯總
    =SUM（OFFSET（A1，G3，G4，G5，G6））
    =SUM（B2：D4）
這兩個公式得到的結果是一樣的，證明我們剛才的說法是正確的。
例子2：如圖4-113所示，根據姓名及月份查找銷售額。

圖4-113　根據姓名及月份查找銷售額
    =OFFSET（A1，MATCH（F15，A2：A9，0），MATCH（G
    14，B1：D1，0））
OFFSET函數是用偏移量算得，所以引用區域的時候就得引用少一個儲存格（如A2：A9），如果是INDEX就得引用A1：A9。小小的差別，別搞錯了。
例子3：如圖4-114所示，求最近5個月的平均銷售額。

圖4-114　最近5個月的平均銷售額
幫助
Height：可選。需要返回引用的行高，Height必須為正數。
Width：可選。需要返回引用的列寬，Width必須為正數。
幫助提到行高、列寬必須為正數，根據這個設置公式為：
    =AVERAGE（OFFSET（B1，COUNT（B2：B13）－
    4，0，5））
COUNT（B2：B13）－4如果是6個月份有銷售額就得到2，B1偏移2行得到B3，行高5列為固定，也就是對區域B3：B7求平均值。
但我肯定地告訴你，幫助提到的行高與列寬必須為正數這個說法是錯誤的，可以是負數。負數就是向上的行高和列寬。
    =AVERAGE（OFFSET（B1，COUNT（B2：B13），0，
    －5））
這個我覺得更容易理解，COUNT（B2：B13）獲取偏移行數6，即得到B7，B7向上移5行就是最後5個月的銷售額。
網友：哎，連説明都會出錯，現在不知道信什麼好？
盧子：人無完人，幫助出錯也可以理解。學習的前期還是要依靠幫助，畢竟幫助出錯的可能性很小。
網友：也只能如此了。
盧子：
例子4：如圖4-115所示為資料來源，要定義一個動態的名稱。這個樞紐分析表經常用到。

圖4-115　資料來源
按複合鍵Ctrl+F3調出名稱管理器，如圖4-116所示，新建一個叫“透視用”的名稱，引用位置為：

圖4-116　定義名稱
    =OFFSET（動態資料來源！$A$1，0，0，COUNTA（動態
    資料來源！$A：$A），COUNTA（動態資料來源！$1：$1））
COUNTA統計非空儲存格，從而動態獲取行高和列寬。
網友：這個函數挺鍛煉想像力的，偏移來，又偏移去的。
區域必須為連續區域，否則會出錯。

4.2.9　百變神君TEXT

盧子：TEXT一個很神奇的函數，可以將資料變化成你想看到的任何形式，有萬能函數之稱。
網友：萬能？這麼牛？真想好好見識一下。
盧子：我是在日企工作，如圖4-117所示，經常會寫一些日語格式的星期幾、數字、日期，你們覺得輸入這些是不是很麻煩？

圖4-117　日語格式
網友：看都看不懂，別說輸入了，那你日語一定很厲害吧，經常輸入這些。
盧子：其實我有一個秘密一直沒跟外人說，我壓根兒不會日語，也很少用有道詞典翻譯。
網友：那你怎麼輸入這些？
盧子：我是借助自訂單元格格式跟TEXT函數而搞定的。下面開始瞭解一些TEXT的基礎，最後我再將我的絕招說出來。
萬能當然是誇張的說法，但確實很強大。其實，TEXT的宗旨就是將自訂格式體現在最終結果裡。TEXT函數主要是將數位轉換為文本。當然，也可以對文本進行一定的處理。
TEXT函數的語法：
    TEXT（value，format_text）
Value：數值、計算結果為數字值的公式，或對包含數字值的儲存格的引用。
Format_text：“單元格格式”對話方塊中“數位”選項卡上“分類”框中的文本形式的數位格式。
TEXT返回的是文本形式的資料。如果需要計算，可以先將文本轉換為數值，然後再計算。文本型數值遇到四則運算會自動轉為數值，但文本不會參與SUM之類的函數運算。
例子1：如圖4-118所示，TEXT函數基本的數文書處理方式。

圖4-118　TEXT函數基本的數文書處理方式
    =TEXT（12.34，＂0＂）
含義是將數位12.34四捨五入到個位，然後以文本方式輸出結果。這個公式也可以簡寫成：
    =TEXT（12.34，0）
當只有一個0的時候，引號可以不加。
    =TEXT（12.34，＂0.0＂）
得到12.3，可以看到結果是保留一位小數。寫幾個0，代表想要資料形成幾位元數。
0：數字預留位置。如果儲存格的內容大於預留位置，則顯示實際數位；如果小於預留位置的數量，則用0補足。
    =TEXT（12.34，＂00000＂）
就顯示為00012。
#：數字預留位置。只顯示有意義的0而不顯示無意義的0。小數點後數字若大於#的數量，則按#的位數四捨五入。
    =TEXT（12.34，＂#####＂）
就顯示為12。如果設置為＂###.##＂，12.1顯示為12.10；12.1263顯示為12.13。
0，0：裡面的逗號相當於千分符。
    =TEXT（123456，＂0，0＂）
就顯示成123，456。
G/通用格式：以常規的數字顯示，相當於“分類”清單中的“常規”選項。例如：代碼“G/通用格式”，10顯示為10；10.1顯示為10.1。
另外，前導0的效果，想顯示幾位元就寫幾個0，可以配合REPT函數來寫，REPT（字元，N），重複N次顯示字元。如圖4-119所示，就是一個為資料前面加0的結果。

圖4-119　為資料前面加0的結果
    =TEXT（D2，REPT（0，D2））
例子2：TEXT在日期時間處理方面的應用。
先說說日期這種特殊的資料類型。日期2010/5/25，其實就是數字40323，如圖4-120所示。

圖4-120　將日期格式設置為常規
    =TEXT（40323，＂yyyy/m/d＂）
可以顯示2010/5/25。TEXT是把日期所代表的真正數位，轉成所需要的日期格式的文本。如20100525要顯示2010/05/25的話，要用上面介紹的0的方法。
    =TEXT（20100525，＂0/00/00＂）
如圖4-121所示，公式中yyyy可以用e來代替。mm，表示顯示兩位元月份，m顯示一位元。中間的連接號，還可以換成其餘文本。

圖4-121　日期格式的應用
TEXT的結果是文本，如果TEXT返回2010/05/25，再去設置格式就改變不了了。
mmm、mmmm、ddd、dddd等都有各自的含義。ddd代表英文星期，中文的星期用aaa和aaaa。時間裡面有個m，和月份相同，所以單獨使用m的時候，系統預設是月份。m必須和h或s同用，才能表示分。[M]帶中括弧的時候，也表示分。因為加中括弧是時間的特殊表示方式。
例子3：TEXT表示四種資料類型。
    =TEXT（數據，＂正；負；零；文本＂）
TEXT裡面可以表示四種資料類型。正數、負數、零與文本，用分號隔開。根據資料的類型，返回對應位置裡的格式。
沒有分號，代表一種格式。
兩個分號：表示儲存格為兩種格式。分號前面為正數和0，分號後面為負數。
3個分號：表示儲存格為三種格式。第1部分用於正數，第2部分用於負數，第3部分用於0值。比如“0；-0；”，將只顯示正數和負數，但不顯示0；最後一個分號不能省略，如果寫成“0；-0”，表示的是不一樣的含義。
    =TEXT（數據，＂1；2；3；@＂）
@是文本的萬用字元，相當於數值中的0。
    =TEXT（數據，＂1；2；3；@＂）
    =if（資料＞0，1，if（數據＜0，2，3））
這兩種是一樣的。
當資料大於0時返回1，小於0時返回2，等於0時返回3。是文本的話，返回其本身。根據分號內的格式自動分配。
    =TEXT（數據，＂1；；；＂）
這種分號內沒有要顯示的格式，結果顯示為空。也就是說，當資料大於0時，顯示1，其餘顯示為空。3分號，4類型。
例子4：強制符號方面的應用。
    =TEXT（A2，＂0；！0；0；！0＂）
如圖4-122所示，強制符號“！”。有了它就可以強制顯示0了。大於0，顯示本身，其他顯示0。

圖4-122　強制符號的應用
例子5：條件判斷方面的應用。
公式1：
    =TEXT（A2，＂[＞10]0；1＂）
    =IF（A2＞10，A2，1）
公式2：
    =TEXT（B2，＂[>50]a；[＞10]b；c＂）
    =IF（A2>50，＂a＂，IF（A2＞10，＂b＂，＂c＂））
如圖4-123所示，TEXT很經典的用法就是在條件判斷方面。因為可以省字元，所以在陣列公式中常用；條件需要用中括弧括起來。這時，分號的作用就不是隔開正數、負數、零了。條件判斷的順序，是先左後右，如同IF函數一樣。

圖4-123　條件判斷方面的應用
例子6：中文數位中的應用，如圖4-124所示。

圖4-124　中文數位的應用
公式1：
    =TEXT（A2，＂[dbnum1]＂）
公式2：
    =TEXT（A2，＂[dbnum2]＂）
公式3：
    =TEXT（A2，＂[dbnum3]＂）
網友：頭大了，這麼多，哪裡記得住。
盧子：你會自訂單元格格式嗎？
網友：這個會。
盧子：前面說了那麼多，只是讓大家有一個初步的瞭解，知道TEXT函數可以做什麼。這麼多用法其實我也記不住，也無須記憶。
如圖4-125所示，輸入任意一個數位，設置單元格格式為貨幣格式，然後查看自訂格式，複製自訂格式，輸入：

圖4-125　自訂格式的運用
    =TEXT（23，＂¥#，##0.00；¥-#，##0.00＂）
利用同樣的方法，哪一種格式不會就設置單元格格式，再查看自訂格式代碼，這樣可以減輕我們的記憶負擔。
現在到了應該解開最開始留下的那個日文輸入法的時候了。
如圖4-126所示，預設情況下，在“特殊”這個格式對應的區域設置。這裡允許選擇任意國家的語言。如果你選擇日語，在“類型”這裡就會出現很多跟日語有關的數字格式，只要選擇這些就可以嘗試一些設置。

圖4-126　設置日語語言
善於借助一切可以為我們減去記憶負擔的方法，這樣學習起來就變得更加輕鬆了。
[DBNum2][$-411]aaaa
[DBNum2][$-411]G/通用格式
[DBNum2][$-411]yyyy/m/d
網友：以前以為看到這些都要記住，原來很多都藏在自訂裡，還有這個區域設置。以前從沒注意過這個問題，長見識了。
盧子：學習這些要有好奇心，有空點開一些你從沒看過的功能來看看，也許會發現很多你意想不到的功能。驚喜就由此產生。

4.2.10　字元提取MID、LEFT和RIGHT三兄弟

盧子：提到字元提取，不得不提到MID、LEFT和RIGHT三兄弟，不管什麼字元到它們手工，都能按要求完美地提取出來。老大LEFT可以從左邊提取字元，老二RIGHT可以從右邊提取字元，老三MID天賦最好，可以從任何位置提取字元。
網友：既然這樣就學MID就行，何必全部都學。
盧子：在函數的世界裡，講究合作精神，即使你再強大，也不能忽略別人的作用。其實做人又何嘗不是這樣，你能力好也不能看不起別人，因為別人通過努力也能成為有能力的人。
網友：說的也是，現在講究的是團隊合作精神，一個人如果離開團隊，能力再高也沒用。
盧子：那就通過幾個例子來說明這三兄弟吧。
例子1：如圖4-127所示，通過軟體截圖，預設情況下會出現軟體名、時間和尾碼，怎麼將它們分別提取出來？

圖4-127　軟體截圖效果1
    =LEFT（A2，6）
提取左邊6位元，也就是軟體名。
    =MID（A2，8，14）
從中間第8位開始提取14位，剛好就是時間。
    =RIGHT（A2，3）
從右邊提取3位，就是尾碼。
前面提到的是最理想狀態，如圖4-128所示，很多時候軟體名不確定，尾碼字元個數不確定。這樣僅僅通過簡單的辦法是無法滿足的，結合FIND和LEN函數會使問題變得簡單。

圖4-128　軟體截圖效果2
    =LEFT（A2，FIND（＂_＂，A2）-1）
通過觀察，軟體名後面都有“_”符號。利用FIND找到這個符號的位置，減去1就是軟體名最後一個字元的位置。
    =MID（A2，FIND（＂_＂，A2）+1，14）
時間都在“_”的後面，利用FIND找到這個符號的位置，加上1就是第一個數字的位置，因為是固定14位元，提取字元數14就可以了。
    =RIGHT（A2，LEN（A2）-FIND（＂.＂，A2））
尾碼在“.”符號後面，尾碼字元數就是總字元減去到“.”符號位置的總長度。也就是：
    N=總字元-＂.＂符號的位置FIND（＂.＂，A2）
    N=LEN（A2）-FIND（＂.＂，A2）
例子2：如圖4-129所示，身份證是每個成年人的名片，有了它，可以獲取省份、地區、出生日期、性別等資訊。身份證很重要，要記得妥善保管。

圖4-129　獲取身份證的資訊
身份證簡介：現行的身份證全部是18位元，早期的是15位。前2位元代表省份，前6位元代表地區碼。15位（7至12位）是出生年月，前面省略19。7～14位是出生年月。15位（13至15位）代表性別，15～17位代表性別，奇數就是男，偶數就是女。現行的身份證，18位號碼是驗證碼。
要知道省份跟地區碼必須有一份地區碼的明細表才可以獲取。如圖4-130所示，就是一份地區碼。

圖4-130　地區碼
前2位代表省份，前6位元代表地區碼。
    =VLOOKUP（LEFT（A2，2），地區碼！A：B，2，0）
先用LEFT提取左邊兩個字元，再用VLOOKUP獲取省份對應值，同理可以獲取地區。
    =VLOOKUP（LEFT（A2，6），地區碼！A：B，2，0）
回頭看看出生年月日：15位（7至12位）是出生年月，前面省略19。7～14位是出生年月。
    =TEXT（IF（LEN（A2）=15，19，＂＂）＆MID（A
    2，7，IF（LEN（A2）=15，6，8）），＂0-00-00＂）
先判斷是不是15位，如果是前面就連接19，否則連接空。
    IF（LEN（A2）=15，19，＂＂）
如果是15位就提取6位，否則提取8位。
    IF（LEN（A2）=15，6，8）
    IF（LEN（A2）=15，19，＂＂）
    ＆MID（A2，7，IF（LEN（A2）=15，6，8））
到這裡就是將出生日期變成統一8位的格式，如19870905。
利用TEXT將8位元的日期格式顯示成1987-09-05。
這是傳統的思路，其實可以將公式再做精簡，得到的結果需要將儲存格設置為日期格式。
    =TEXT（MID（A2，7，11）-500，＂#-00-00，＂）*1
網友：-500，還有#-00-00，是幹嘛用的？
盧子：先來看看這幾條公式。
=TEXT（1999，＂#，＂）顯示2。
=TEXT（1499，＂#，＂）顯示1。
=TEXT（1001，＂#，＂）顯示1。
=TEXT（501，＂#，＂）顯示1。
也就是說這個“，”的作用就是將數字除以1000並四捨五入的結果，也就是千位符。
再回頭看看MID（A2，7，11），不管是15還是18位身份證，從第7位開始提取11位就是將提取到日期跟性別組成的所有數字。後面的3位元是多餘的，需要去除。
    TEXT（MID（A2，7，11）-500，＂#-00-00，＂）
-500的作用就是將後面的數位變成小於500的數位，加上最後面的“，”，其實就是舍去最後3位，前面多取了3位，現在還回去，有借有還。
網友：如果這樣直接後面3位不提取不就得了，幹嘛繞那麼多彎。
盧子：如果不提取是不是要像最開始一樣判斷是不是15位，然後再決定取多少位，這樣反而多了一個判斷條件。不過這個公式有一個缺陷，就是當1930年前出生的人（如290815）會顯示錯誤。不過正常這種年齡的人，你用他們的身份證還有意義嗎，你懂的。一直以來我都覺得，寫公式就是一個不斷取巧的過程。
15位（13～15位）代表性別，18位（15～17位）代表性別，奇數就是男，偶數就是女。
    =IF（ISODD（MID（A2，15，3）），＂男＂，＂女＂）
奇數、偶數的判斷可以利用最後1位判斷，也可以通過所有字元判斷。如123，最後1位是奇數，它就是奇數，跟整個數字判斷的結果是一樣的。
MID（A2，15，3）提取15位的最後1位，跟提取18位元的3位元數字。
ISODD（MID（A2，15，3）），判斷數字是不是奇數，是就返回TRUE，否則返回FALSE。
IF就是返回男女的對應值。

溫馨提示

在低版本中用MOD（數字，2）來判斷奇數跟偶數。
網友：後面這兩個公式太巧妙了！
盧子：畢竟像這種公式可遇不可求，並不是任何人都可以想到的。學習階段可以讓公式縮減到最少字元，但實際工作中還是以正常思維處理為好，以防考慮不周全而出錯。
網友：收到，看來盧子還是屬於比較嚴謹的人。

4.2.11　SUBSTITUTE的運用

盧子：SUBSTITUTE函數跟查找替換有點類似，可以替換掉你不需要的字元。感覺這個函數，挺適合用來玩的。
網友：是嗎？
盧子：不信的話就一起來看看，怎麼玩轉笑看今朝。
例子1：如圖4-131所示，今朝一起來笑笑，通過A2變出右邊的4種效果。

圖4-131　今朝一起來笑笑
網友：你拿自己的網名“笑看今朝”開玩笑啊。
盧子：這樣沒什麼不好，總不能拿別人的名字來開玩笑吧。

函數語法

=SUBSTITUTE（字串，舊字元，新字元，N）
將字串中的舊字元換成新字元，N代表第幾個舊字元，省略就全部替換掉。
公式1是將“笑”全部替換成“今朝”，即N省略。
    =SUBSTITUTE（A2，＂笑＂，＂今朝＂）
公式2是將第1個“笑”替換成“今朝”，即N等於1。
    =SUBSTITUTE（A2，＂笑＂，＂今朝＂，1）
公式3是將第2個“笑”替換成“今朝”，即N等於2。
    =SUBSTITUTE（A2，＂笑＂，＂今朝＂，2）
公式4將第1個“笑”替換成“今朝”，第2個“笑”替換成“？”，這樣得通過兩次替換才可以。
    =SUBSTITUTE（SUBSTITUTE（A2，＂笑＂，＂今朝＂，1），＂笑＂，＂？＂）
這個函數的功能比較單一，比較好理解，重點在於N，只要理解這個，其他都不是問題。
例子2：圖4-132所示是一份人員清單，根據這份清單統計一些人員資訊。

圖4-132　人員清單
12月21日出現幾個姓楊的人：
    =LEN（B2）-LEN（SUBSTITUTE（B2，＂楊＂，＂＂））
SUBSTITUTE（B2，＂楊＂，＂＂）將包含“楊”的人替換掉，LEN（SUBSTITUTE（B2，＂楊＂，＂＂））統計替換後字元數，LEN（B2）-LEN（SUBSTITUTE（B2，＂楊＂，＂＂））總字元數減去替換後字元數，就算出含有幾個“楊”。

每天有多少人

    =LEN（B2）-LEN（SUBSTITUTE（B2，＂、＂，＂＂））+1
人員都是用“、”隔開，只要知道“、”的個數再加1就是人員數。思路跟統計“楊”字是一樣的，在C2公式下拉就可以得到結果。

楊曉鳳一共去了幾天

    =SUMPRODUCT（——（（LEN（B2：B9）-LEN（SUBSTITUTE（B2：B9，＂楊曉鳳＂，＂＂）））＞0））
LEN（B2：B9）-LEN（SUBSTITUTE（B2：B9，＂楊曉鳳＂，＂＂））統計有沒有字元減少，通過跟0比較，只要大於0就是包含，再用減負運算將TRUE轉換成1，然後匯總得出總天數。
此題用SUBSTITUTE方法顯得有點笨長，用COUNTIF更合適，只是為了說明才這麼用。
    =COUNTIF（B2：B9，＂*楊曉鳳*＂）
關於常用函數到這裡已告一段落，常用的就這十幾個，學好了會大大提高工作效率。別看只有十幾個，卻能變換出無數組合，這些只能靠大家平常慢慢積累。
網友：謝謝盧子這兩個月的講座，讓我們瞭解到很多知識，現在已經開始告別小菜鳥。
盧子：其實主要是有我的朋友在背後支持，才能堅持到現在，真的感謝他們的無私奉獻。雖然講座結束，但並不代表學習結束，而是另一個開始。以後有問題還可以繼續交流，讓我們共同進步，加油！
網友：一起加油！！！你將是我們學習的榜樣。

4.3　為自己量身定做的函數

Excel提供了很多內置函數，但有時並不能滿足我們各個方面的特定要求。我們可以為自己量身定做屬於自己的函數。自訂函數能為普通函數錦上添花。下面通過兩個小例子讓大家來認識自訂函數。

洗衣服流程

用洗衣機洗過衣服的人都知道流程為：進水——洗滌——放水——甩幹，這個過程有時會重複三四次。設置了一次流程的時間，再重複設置第二次、第三次，你會不會覺得煩瑣。現在的全自動洗衣機都有一個記憶模式，就是按你第一次設置好的流程時間來洗衣服。只要一按電源開關，你就可以不用管，時間到了再把衣服拿出來就好，減少了很多不必要的麻煩。自訂函數就如設置好記憶模式，一按電源開關，一切問題都搞定，可以簡化我們的工作。

姚明買衣服

姚明身高226cm，NBA明星，大家應該很熟悉。正常人的身高為170～180cm，市面上的衣服基本上都是為這個人群設計的。像他這種身高，如果出去買衣服，肯定買不到。既然買不到他穿什麼？他的衣服都是廠家專門定做的。定做的衣服，不要說226cm的身高，300cm都可以。自訂函數又如姚明買衣服，可以滿足我們個性化的需要，使我們的公式具有更強大、更靈活的功能。

4.3.1　連接所有字元LJ

網友：如圖4-133所示，我想將所有字元連接起來放在一個儲存格內，用＆連接起來感覺十分煩瑣，那麼多個儲存格，還怕按錯呢，有沒有簡單一點的辦法？

圖4-133　連接所有字元
盧子：如果字元都是文本，可以用PHONETIC函數，這個函數就是專門連接文本字元的。PHONETIC可以將所有文本連接起來，但遇到公式計算結果、數值將被忽略。
    =PHONETIC（A2：O2）
我看你的資料來源，包含了數位，那就只能用自訂函數了。
利用複合鍵Alt+F11調出VBA編輯器，然後“插入”模組，並在代碼視窗輸入自訂的代碼。
    Function LJ（Rng As Range）
      Dim Set_Rng As Range
    For Each Set_Rng In Rng
    LJ=LJ＆Set_Rng
      Next
    End Function
迴圈語句：
    For Each…Next
通俗用法：
    For Each儲存格In儲存格區域（物件）
    循環體＜/p＞
    Next
也就是從區域的第一個儲存格迴圈地將每個儲存格連接起來，如圖4-134所示。

圖4-134　自訂LJ函數
按一下視窗右上角的“關閉”按鈕，關閉VBA編輯器，返回Excel工作表介面。
如圖4-135所示，在函數清單的“使用者定義”類別中找到此自訂函數。

圖4-135　使用者定義函數
如圖4-136所示，現在使用我們自訂的LJ（函數是不區分大小寫的），試試效果。

圖4-136　使用定義函數
網友：自訂函數看起來好簡潔啊，我喜歡。
幾天後……
網友：如圖4-137所示，上回那個LJ用了一回以後就用不了，變成#NAME？，怎麼回事呢？

圖4-137　名稱錯誤
盧子：其實自訂函數也屬於VBA的一種過程，需要將巨集的安全性設置為低才可以使用。
如圖4-138所示，在“開發工具”選項卡中按一下“巨集安全性”按鈕。在巨集設置這裡，選擇“啟用所有宏”項。保存工作簿後，重新打開就可以使用了。
圖4-138　設置宏的安全性
有一段時間巨集病毒很倡狂，很多人都中了巨集病毒。不過巨集病毒並不可怕，百度一下就可以找到巨集病毒專殺，即使找不到也沒關係，現在一般的殺毒軟體都提供了這個功能。我們自己編寫的代碼是絕對安全的，所以不用擔心會中巨集病毒。
網友：這招還挺管用的，又能繼續使用。

4.3.2　將借閱的書籍合併HB

網友：對了，這裡我有一個問題，請教你一下。
盧子：說說看。
網友：如圖4-139所示，我現在想把每個借閱人借閱的書籍合併在一個儲存格內，並用“，”隔開，利用常規公式只能將書籍放到多個儲存格，有沒有好點的辦法？

圖4-139　將借閱的書籍合併
盧子：既然常規方法難以處理，那就用自訂函數，我先考慮一下怎樣編寫。
網友：好的，不急。
盧子：利用下面的代碼可以實現。
    Function HB（rng As Range）
       Dim i％
     For i=2 To Cells（Rows.Count， 1）.End（xlUp）.Row
       If rng=Cells（i， 3）Then
         HB=HB＆IIf（HB=＂＂，＂＂，＂，＂）＆Cells（i， 1）
      End If
       Next
    End Function
迴圈語句：
    For Next
跟For Each…Next的作用差不多。
Cells（Rows.Count， 1）.End（xlUp）.Row，這一句的意思就是動態判斷A列有多少行，不用我們自己去數。
IIf（HB=＂＂，＂＂，＂，＂）這跟我們在工作表中作用IF函數的功能一樣，就是判斷是不是為空，不為空就加個逗號，挺好理解。
如圖4-140所示是利用自訂函數HB生成的結果。

圖4-140　利用自訂函數HB生成的效果
網友：沒想到同樣是短短的幾句代碼，VBA真的太神奇！
過了幾天……
網友：我又發現了一個問題，我想在其他工作簿中使用這個自訂函數，但就是用不了，怎麼回事呢？
盧子：自訂函數只適用於輸入代碼的工作簿，其他工作簿是使用不了，但是有變通的辦法。
如圖4-141所示，將Excel另存為“Excel增益集”格式。

圖4-141　另存為“Excel增益集”格式
如圖4-142所示，在“開發工具”選項卡中按一下“載入項”按鈕，找到增益集，再按一下“確定”按鈕。現在就可以在別的工作簿中使用自訂的函數HB了。

圖4-142　啟用增益集
網友：原來還得多一步增益集才行。
盧子：這個我們自己定義的函數，Excel本身沒有，增益集就是告訴Excel，我現在也是有身份的人了，也屬於函數的一員，別把我當外星人看待。

4.3.3　提取批註內容PZ

網友：如圖4-143所示，我們公司現在有一批產品需要重新更改單價。由於當初用批註標示，現在要重新算新單價很困難，有沒有辦法將批註的內容提取到儲存格內，這樣便於後期操作？

圖4-143　批註標示扣減值
盧子：做什麼事就得考慮長遠，如果當初寫在儲存格中不就什麼事都沒有了嗎，真是自找麻煩。不過世上也沒後悔藥，幫你想想看有什麼辦法可以補救。
網友：批評得是，下回注意點。
盧子：想到了，可以用下面的自訂函數代碼。
    Function PZ（Rng As Range）
       On Error Resume Next
       PZ=Rng.Comment.Text
    End Function
On Error Resume Next做容錯處理，遇到沒有批註的儲存格繼續執行自訂函數過程。
Comment.Text就是批註的內容，結果如圖4-144所示。

圖4-144　效果圖
網友：現在把“扣減值”放在儲存格內，要算新單價就簡單多了。
盧子：既然說到批註這個問題，那就說一下完整的批註包含的內容，如圖4-145所示。

圖4-145　批註包含的內容
盧子：批註者，一般指電腦的用戶名。
分行符號：別以為看不見就沒有。
12：實際輸入的內容。
批註雖小，五臟俱全，呵呵。
現在以標準的形式來提取批註的數位。
    Function PZ（Rng As Range）
       On Error Resume Next
        x=Rng.Comment.Text
       PZ=Mid（x， InStr（x， Chr（10））+1）
    End Function
為了將代碼看起來簡潔，可以將Rng．Comment.Text賦值給x。其實這跟我們以前數學的x、y、z這些變數差不多。
InStr函數跟FIND差不多，查找字元在文本中的位置，但參數的順序跟FIND反過來了。
FIND（查找的字元，文本），查找不到顯示錯誤值。
InStr（文本，查找的字元），查找不到顯示0。
Chr（10）就是一個分行符號。
InStr（x， Chr（10））就是查找分行符號在批註中的位置，用戶名千變萬化，但分行符號是不變的。
Mid（x， InStr（x， Chr（10））+1）對於這個你可能會疑惑，Mid這個函數不是有3個參數嗎，怎麼最後1個參數被省略了，是不是忘記寫了？其實在VBA裡，第3個參數省略就是提取所有字元。
如圖4-146所示，就是完整版批註的結果。

圖4-146　完整批註顯示效果圖
網友：謝謝，又瞭解到新的知識。

4.3.4　自動錄入當前時間NowTime

網友：如圖4-147所示，如果店鋪編碼、類型和金額三列都有內容就錄入當前時間。我試過用NOW函數，這個錄入的時間會不斷變化，有沒有辦法讓錄入時間後保持不變？

圖4-147　自動錄入當前時間
盧子：這個用快速鍵倒是可以錄入當前時間，且時間不會變化，但就是需要自己判斷，是不是三列都有內容。
網友：行數太多，用這種方法不實際。
盧子：那我考慮一下，看看有沒有其他辦法。
網友：好的，拜託你了。
經過數分鐘的考慮。
盧子：那就自訂一個函數吧，代碼為：
    Functon NowTime（Rng As Range）
        If Applicaton.CountA（Rng）=3 Then
           NowTime=Format（Now，＂yyyy/mm/dd hh：mm：ss＂）
               Else
            NowTime=＂＂
        End If
    End Functon
Application.CountA（Rng）=3，判斷儲存格非空值，且是不是等於3。
Format（Now，＂yyyy/mm/dd hh：mm：ss＂），設置時間的格式。
如圖4-148所示，用自訂函數NowTime生成的結果。

圖4-148　用自訂函數NowTime生成的結果
網友：我試試看。
盧子：好的。
網友：還真的可以，保存後打開也不會改變。改天有空再幫我多設計幾個自訂函數，這個太好用了。
盧子：並不是任何東西都需要自訂函數，比如求和、查找，內置函數都有這個功能，你自訂一個函數毫無意義。不是你該幹的活，就不要跟別人搶。只有當內置函數實在很難完成的時候再考慮自訂函數，不能濫用！

4.4　本章小結

函數是Excel的精髓，要學好函數必須先從基礎概念學起，只有基礎牢固了才能更好地學習函數。學習函數不要貪多，先從常用的函數開始學習，逐個熟練。如工程函數、財務函數，這些也許有的人一輩子都用不了的函數，學不學無所謂，不會對你有任何影響。這兩類函數我到現在一個都不會。如果有精力的話，就瞭解點自訂函數的基礎，這個有時可以幫你大忙。當你山重水盡疑無路時，借助自訂函數也許會柳暗花明又一村。

0 留言:

發佈留言

訂閱： 發佈留言 (Atom)