Excel教科書(06) 第4章 通過應用與組合，提升函數的威力

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

第4章 通過應用與組合，提升函數的威力

如果想要知道所有的Excel函數，那麼你可以買一本介紹Excel函數的詞典。但即便掌握了所有Excel函數，對工作也沒有任何幫助。最重要的是，要知道“如何組合使用單個或多個函數，來實現需要Excel完成的處理”。

在本章中，我會介紹幾個實例，看一下“在實際工作中應該如何使用函數”。我希望大家能從這些例子中明白一點：比起掌握具體的技巧，更重要的是學會如何“構思”。剛開始可能許多人會覺得很難，但習慣後，就可以靈活運用函數的各種特性，利用各種應用操作，思考如何自由地處理各種資料。運用Excel函數來提升工作效率，其實是一項富有創造性的腦力工作。

接下來，我們來舉幾個具體案例。

判斷儲存格中是否包含特定的字串

如何計算世田穀區的客戶人數

“想要從客戶資料中篩選出世田穀區的客戶人數。”

這時，如何才能簡單、快速地完成這項工作呢？

其實只需要按照下面這兩個步驟操作即可。

● 調查儲存格中是否包含“世田穀區”這四個字

● 如有，則在其他儲存格中輸入“1”

如此一來，只要計算含有“1”的儲存格的數目，就能得出包含“世田穀區”這四個字的位址的儲存格數目。

像這樣，“確認儲存格裡含有特定字串時，標記為數位1”的操作，屬於COUNTIF函數的應用。假設在A列中輸入住址，B列輸入數位1。

① 在儲存格B2中輸入以下公式。

=COUNTIF(A2,"*世田穀區*")

② 一直將公式複製到資料的最後一行。

這樣一來，在A列儲存格中若含有“世田穀區”四個字，B列中就會在相應的行顯示“1”。

此處出現的“*”符號叫作“星號”。無論是什麼樣的文字，無論有多少字，都可以這1個文字來代替使用（作為“萬用字元”使用的符號）。意思就是說，“世田穀區”前後含有其他文字。這樣一來，只要該字串符合“包含‘世田穀區’四個字”這樣的條件，即可被檢索出來。

現在讓我們來複習一下，COUNTIF函數是在第一參數指定區域中，計算符合第二參數指定條件的儲存格數目的函數。在儲存格B2中輸入的函數有這樣的意思：

在儲存格A2中包含“世田穀區”這個值的儲存格有多少個？

作為指定範圍的第一參數中，此處指定的是單個儲存格A2。判斷符合條件的儲存格有多少，答案只有1和0。如答案為1，那麼就說明此儲存格中包含“世田穀區”；如答案為0就是不包含。

接下來，再用SUM函數統計B列值的總和，就能得出A列中所有包含“世田穀區”的儲存格的數量。

SUM函數在儲存格B8中表示B列的總和

如何搜索除世田穀區以外的區域

前文仲介紹的是如何搜索儲存格中是否包含特定文字（世田穀區），直接將指定文字輸入到函數中並搜索。那麼如果不僅需要搜索“世田穀區”，也需要搜索包含其他區域的儲存格時，應該怎麼做？

搜索除世田穀區以外的區域

如果把各個區功能變數名稱稱直接輸入進函數，那麼需要重新輸入B列到F列每一列中的函數。這樣做非常麻煩，也很容易出錯。

這時，請不要採取這種直接輸入的方法，而是要採取引用儲存格的方法。工作表中的行首處會顯示搜索目的地區域的專案名稱，利用這些儲存格，就能簡化輸入函數的操作。

在此提醒各位讀者，引用儲存格來搜索時，要輸入以下公式：

=COUNTIF($A2,"*"&B$1&"*")

輸入=COUNTIF($A2,"*"&B$1&"*")

在第二參數中，連續輸入儲存格號碼與星號容易發生錯誤。為了方便大家理解，下面我將去掉絕對引用的$符號，告訴大家會容易出現什麼樣的錯誤。

=COUNTIF(A2,"*B1*")

這個公式的意思變成了要在儲存格A2裡，搜索是否含有“B1”這一字串。但是原本需要搜索的是儲存格中是否含有“包含‘B1’的字串”。為了區分指定星號標記與引用儲存格，需要用&符號連接。

設定絕對引用時一定要注意，在儲存格B2中輸入正確的公式之後，再直接複製到儲存格F7為止。

將輸入的公式複製到儲存格F7

另外，想計算含有各區功能變數名稱的儲存格的數目，只有選擇B8～F8，按Alt+=（AutoSUM 的快速鍵）即可立刻得出結果。

選擇B8～F8，按Alt+ =

順便，用IF函數是無法順利處理這項操作的。在儲存格A2中如包含有“世田穀區”打○，否則打×，在做這項操作時，有許多人反應使用下面的公式無法得到預期的結果。

=IF(A2="*世田穀區*","○"," ×")

這種情況下，需要在COUNTIF函數中嵌入判斷是否包含字串的條件。

=IF(COUNTIF(A2,"*世田穀區*")=1,"○","×")

隨後，就能在儲存格A2中檢索是否含有“世田穀區”這組字串。

消除重復資料的方法

如何判斷是否有重複

“電話征訂名單中，多次出現同一家公司！”

這是某個正在開展開發新客戶的銷售部門裡發生的事。這個部門負責電話征訂的共有10位銷售人員，這些人員先製作電話征訂名單，然後根據名單給客戶打電話。由於每位元銷售人員都是通過網路等方式調查並收集目標企業資訊的，所以同一個企業會出現在不同的銷售人員的電話名單中。

這時，如果大家一同開始給目標企業打電話，就會導致同一家公司多次接到同一公司的銷售人員的電話，最終一定會聽到客戶的投訴：“別再給我們打電話了！”因此，經常有人來問我如何才能避免這樣的事情發生。

像這樣，在管理客戶名單時，應該如何檢查是否存在重複的資料？

首先我們來看一下簡單的判斷方法。比如，A列為ID資訊，要想檢查其中是否有重複的內容，可按照以下邏輯判定。

● 計算該ID在A列中的數目

● 如結果為1個則表示沒有重復資料，如果是2個以上則可以認定為有重複

那麼，我們來看一下應該如何在Excel中處理重復資料。在此，假設想要在B列中顯示是否有重復資料的判定結果。

① 在儲存格B2中輸入以下公式：

=COUNTIF(A:A,A2)

這一公式用於計算在A列中與儲存格A2有相同數值的儲存格的數目。

若結果為1個，說明A列中不存在與儲存格A2有相同數值的儲存格……也就是說不存在重複數值。

若結果顯示為2，說明A列中存在與儲存格A2有相同值的儲存格，可以得知資料有重複。

② 複製到資料的最後一行

像這樣，在一列中連續輸入已經存在的數值時，需要複製的行數會增多。用滑鼠將相鄰列中的函數公式拖拽複製到最後一行，是一件十分麻煩的事。下面的技巧可以讓你在一瞬間完成這項操作。

在儲存格B2中輸入公式後，再次選中儲存格B2，將滑鼠移到被選中儲存格右下角的浮標上。這時，我們可以看到原本白色十字的游標變成了黑色。接下來，我們需要按兩下這個黑色遊標。

這樣，我們就能夠確認A列中的儲存格是否存在重複的資料。

選中並刪除重複的儲存格

即使知道工作表中存在重復資料的儲存格，也還有問題需要解決。一般來說，確認工作表中存在重複的資料後，需要刪除重複的資訊，將表格整理為沒有重復資料的狀態。利用先前的方法只能確認是否存在重複的資料，無法選中並刪除重複的儲存格。

因此，我們需要將原來的公式修改成這樣：

=COUNTIF($A$2:A2,A2)

在儲存格B2中輸入=COUNTIF($A$2:A2,A2)

在儲存格B2中輸入公式時，指定與第二參數一致的儲存格查找範圍的第一參數為“$A$2:A2”，也就是儲存格A2。因此，得出的結果自然為1。

接下來，按兩下右下角遊標，將這一儲存格複製到最後一行，就會出現以下畫面。

將儲存格B2複製到最後一行

這也就是我在第1章裡稍微提到過的自動篩選，即只抽出B列中值為2的儲存格後並刪除，即可刪除所有重複項。

自動篩選抽取B列值為2以上的儲存格

儲存格B2的函數中的第一參數“$A$2:A2”，指定從A2到A2作為函數的範圍。冒號（:）前的內容表示只引用範圍起始點的儲存格，意為絕對引用。如此一來，將這一儲存格向下拖拽複製後，儲存格B3的範圍為“$A$2:A3”，儲存格B4為“$A$2:A4”，以此類推。也就是說，作為指定範圍的儲存格的起點，即儲存格A2是固定的，終點的儲存格卻是相對參照，可以不斷延續。這樣就讓人覺得第一參數指定的範圍在無限擴展。

在B列的各儲存格中的函數引用的並不是位於該儲存格下面的儲存格中的內容。所顯示的數位表示的是“該儲存格相鄰的儲存格的數值，在A列中出現了幾次”。

照此推斷，就能得出“B列中顯示有2以上的數位的數值表示：在A列中的前面的某行中已經出現過有相同值的儲存格”，表示資料有重複。因此，如果將B列中含有2以上的資料的儲存格全部刪去，A列中就不會存在重複的數值了。

專欄　不要使用“刪除重複”鍵

2007之後的Excel版本都追加了“刪除重複”功能，但我個人不推薦使用，因為在實際操作中曾發生過刪除了並沒有重複的資料的事例。

輸入連續的數字

在Excel中輸入1、2、3……這樣連續的數字到底有多少種方法呢？我們來逐個看一下。

使用“自動填充”功能

先介紹一下自動填充功能的使用方法。例如，在儲存格A2中輸入1，在儲存格A3中輸入2，然後同時選中儲存格A2和A3並向下方拖拽複製，一直拖拽到最後一行。這樣，每一行中的儲存格中的數字就是連續的。

同時選中儲存格A2和A3

向下方拖拽複製，一直到拖拽到最後一行

使用“製作連續資料”功能

如果是需要輸入小範圍的連續的數位，我們可以使用Excel自動填充功能來完成。但是如果結尾的數字很大，使用這種方法就有一定的限制。例如，要連續輸入1到1000的數位，使用自動填充功能的話，需要花費很長的時間。

這種要連續數到很大數位的情況下，就可以使用“製作連續資料”功能。

① 起始儲存格中輸入數位1，選中此儲存格。

輸入1後按回車鍵，下方儲存格也變為選中狀態。然後按Ctrl+Enter後，選中狀態仍停留在剛輸入完畢的儲存格。

② 【開始】選項卡→【填充】→點擊【系列】

③ 【序列產生在】選擇【列】，【終止值】輸入1000，點擊確定。

利用這個方法，就可以在儲存格中連續輸入1～1000，這個方法比自動填充更簡單，也更方便。

如何連貫輸入連續的數字

以上2種方法有個前提，就是所有輸入儲存格的數字要為固定值，因此如果刪去中間某一行或者插入一行，連續的數字就從中間斷開了。要想在這種情況下也讓數位保持連貫，我們可以使用ROW函數。無論刪掉還是穿插一行儲存格，都可以保持數位的連貫，不需要逐個修改。

輸入下列公式的儲存格，會顯示“該儲存格位於工作表中的第幾行”的資料。

【公式】

=ROW()

括弧中不要輸入任何內容。請記住像這樣在函數括弧中不輸入任何參數的方法（比如TODAY函數、NOW函數等）。

例如，在儲存格A2中輸入這個函數，儲存格A2中會顯示2。由於儲存格A2位於工作表中的第2行，因此數字2代表的是這個行數。

在儲存格A2中輸入=ROW()

如下圖所示，直接向下拖拽複製，從2開始的連續。

從儲存格A2向下拖拽複製

各儲存格“=ROW()”這個公式匯出的數位，這個數位表示該儲存格所處的行數，所以會在儲存格中顯示連續的號碼。

但是通常來說，連號都是從1開始。因此，需要在這個ROW函數中做減法。例如，想從第2行（這裡是儲存格A2）開始輸入連續的數字時，請輸入下列公式。

=ROW()-1

在儲存格A2輸入=ROW()-1

按回車鍵，ROW()取得的行數2再減去1，顯示結果得到1。

顯示結果為1

將此儲存格向下拖拽複製，各儲存格中就會出現連續的數字。

將儲存格A2向下方拖拽複製

這裡的連續的數字，按照各儲存格中的ROW函數取得的該儲存格時所在的行數，因此就算中間刪除或添加一行儲存格，都會從1開始保持數位的連貫。

在工作表中沿行方向輸入連續的數字

那麼，如果想要在工作表中沿行方向，即向右方輸入連續的數字，應該怎麼做呢？這時，我們可以使用COLUMN函數。COLUMN函數的意義在於，在輸入如下內容的儲存格中，能夠得出該儲存格位於工作表的左數第幾列。

【公式】

=COLUMN()

例如，在儲存格B1中輸入這一函數會得到2。

在儲存格B1中輸入=COLUMN()

儲存格B1位於B列，即工作表的左數第2列。因此得出數字2。

如果繼續向右拖拽複製，就會開始從2連續輸入數字。

要想從1開始連續輸入的話，與ROW函數同理，減去數字做相應調整就行。

① 在儲存格B1輸入下列公式後，按回車鍵。

=COLUMN()-1

② 將儲存格B1向右方拖拽複製，出現連續的數字。

通過ROW函數、COLUMN函數在工作表中輸入連續的數字，可運用在以下的需求中。

● 在表格中隔行標注2種不同顏色

● 輸入連續的阿拉伯數字

● 快速沿行方向輸入大量 VLOOKUP函數

在這之後，我會逐個具體說明。

沿行方向輸入大量 VLOOKUP函數的方法

批量修改儲存格

如果遇到像下圖這樣，需要輸入大量的VLOOKUP函數，按照常規的方法處理需要花費大量的時間和精力。

“輸入表”中的各個儲存格裡，按照“商品No.”在“負責部分”中用VLOOKUP函數找出對應值。首先用常規的方法，在最開始的儲存格C3中輸入以下公式：

=VLOOKUP($B3,$I:$N,2,0)

將儲存格C3的公式向右一直複製到G列，為了不改變從屬儲存格，需要用絕對引用來固定第一參數的檢索值和第二參數的檢索範圍。

在儲存格C3輸入=VLOOKUP($B3,$I:$N,2,0)後

接著將它一直拖拽複製到儲存格G3。畫面顯示如下：

將儲存格C3一直拖拽複製到儲存格G3

所有儲存格中的資料都已經變成了相同數值。這是因為從儲存格C3到G3，每個儲存格中的函數如上變為了第三參數“2”。參考的是檢索範圍I:N列最左端開始數第2列的值。

因此，如果要讓C3到G3中的每個儲存格都顯示各自所屬正確的數值，就必須修改各單元中的VLOOKUP函數的第三參數。C3中VLOOKUP函數第三參數改為“2”、D3改為“3”、E3改為“4”、F3改為“5”、G3改為“6”，這樣每個儲存格中的數值才是正確的。

像這樣逐個修改還是很麻煩的。像前文中的例子那樣，如果需要修改的儲存格只有4個，那麼不會花費很多時間。但是工作中需要輸入VLOOKUP函數和修改第三參數的儲存格有時會多達50列。遇到這種情況，千萬不要動手逐個去修改。我告訴大家一個便捷的辦法，甚至可以不用逐個修改儲存格。

在粘貼的儲存格中變為合適的數字

這裡需要的並不是把VLOOKUP函數的第三參數輸入成2或3這樣的固定值，而是需要“輸入可以在粘貼的儲存格裡，即時轉化為合適的數字”這樣的設想。

最簡單的就是在表外的上方輸入想要指定的第三參數的數字，然後引用這一儲存格。例如，在儲存格C1到G1中，分別輸入從2到6的數字，在C3中輸入以下公式：

=VLOOKUP($B3,$I:$N,C$1,0)

將這個公式一直複製粘貼到G3，顯示如下。

在儲存格C3輸入=VLOOKUP($B3,$I:$N,C$1,0)並一直複製粘貼到G3

第三參數引用的是同一列的第1行的儲存格。也就是說，C列引用2，D列引用3，如此自動改變數值。這樣就不用在每個儲存格裡逐個輸入VLOOKUP函數的第三參數了，從而大大減輕了工作負擔。

無需在工作表外填入資料並完成連續輸入VLOOKUP函數

在這種情況下，由於“輸入表”與“負責部分”各項目的排列順序相同，VLOOKUP函數第三參數中指定的數字也要向右遞增，顯示連續的數位。因此，工作表外的上部不用輸入其他數位，也可以完成操作。

想要沿著行的方向輸入連續的數字，我們可以使用COLUMN函數。利用COLUMN函數的特性，並將之嵌套在VLOOKUP函數的第三參數裡，就可以瞬間完成複雜的操作。

在儲存格C3中輸入以下公式：

=VLOOKUP($B3,$I:$N,COLUMN()-1,0)

一直複製粘貼到儲存格G3，Excel中就會出現如下頁面。

在儲存格C3裡輸入=VLOOKUP($B3,$I:$N,COLUMN()-1,0)並複製粘貼到G3

第三參數“COLUMN()-1”在C列中為2，在D列中為3。COLUMN函數所匯出的，是含有COLUMN()的儲存格位於工作表中第幾列的數位。

在儲存格C3中輸入的VLOOKUP函數，其第三參數指定數字為2。由於C3的COLUMN()為3，在此基礎上減去1後，則調整為2。同理，D列到G列中“COLUMN()”獲得的數字減去1就是VLOOKUP函數的第三參數，這樣就能順利地計算出正確的專案數值。

如何用VLOOKUP函數應對檢索範圍中豎列順序的變動狀況

輸入表與負責部分的專案順序不同時

在剛才的例子中，為了讓“輸入表”與“負責部分”的專案排列順序保持一致，第三參數按順序輸入2、3、4……這樣連續的序號。因此，VLOOKUP函數第三參數引用嵌入了COLUMN函數，這樣做會提高效率。

但是，如果像下面這樣，輸入表與負責部分的專案順序不同時該怎麼辦？也就是說第三參數不是連續數字的話，各儲存格中的VLOOKUP函數即便運用了COLUMN函數，也無法得出正確的第三參數。

輸入表與負責部分的專案順序不同時

此例中，D列的“單價”對應“負責部分”最左端往右數第6列，E列的“生產者”對應“負責部分”最左端往右數第5列。在這樣的前提下，如果想要在儲存格C3中輸入最開始的那個函數公式，之後只要複製到G列也都可以得出結果的話，我們應該怎麼做呢？

在C列商品名的儲存格輸入的VLOOKUP函數中第三參數應該是什麼數字呢？答案是2。那麼，我們只要輸入能自動匯出數字2的第三參數就可以了。這時候，我們就要用到MATCH函數。

我們通過以下例子具體解釋一下。

上述例子中，A1到D1項目名稱分別為“商品名”、“單價”、“生產者”、“最低訂購單位”，這些專案在F1到I1的範圍中位於左數第幾列，會相應地顯示在A2到D2中。以儲存格A2為例，“A1（即商品名）的值，在 F1：I1範圍裡位於左數第2個”，那麼A2中則會顯示數位2。

在儲存格A2做出這種處理的是下面的函數公式。

=MATCH(A1,$F$1:$I$1,0)

MATCH函數中第一參數指定的值，會匯出在第二參數指定範圍中位於第幾位元的數字。第三參數基本上“只要輸入0就行了”。

在圖中，將儲存格A2的公式一直複製粘貼到D2。因為第一參數不做絕對引用，儲存格B2裡被複製粘貼的公式中的第一參數為B1，儲存格C2裡被複製粘貼的公式的第一參數為C1，D2中則是D1。

第二參數限定了縱列或橫行的範圍。

▲指定縱列的範圍

第一參數指定的值為在此範圍內的上數第幾行。

▲指定橫行的範圍

第一參數指定的值為在此範圍內左數第幾列。

儲存格範圍限定為F1:I1，則呈現如下狀態：

● 儲存格A1即“商品名”位於左數第2個

● 儲存格B1即“單價”位於左數第4個

● 儲存格C1即“生產者”位於左數第3個

● 儲存格D1即“最低訂購單位”位於左數第1個

能夠在儲存格中顯示數位2、4、3、1，是因為MATCH函數的處理。

在VLOOKUP函數的第三參數中加入MATCH函數，即使“輸入表”與“負責部分”的專案的排列順序不同，也能夠通過MATCH函數取得“‘輸入表’的各專案名在‘負責部分’下位於第幾列”的數位，把這樣的結構嵌入VLOOKUP函數第三參數中就能夠解決順序不同的問題。在輸入表的儲存格C3，請輸入以下公式：

=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)

然後複製到整個表格，畫面則顯示如下：

在儲存格C3中輸入=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)並複製粘貼至全表

分析MATCH函數的處理

可能乍一看上述的公式很複雜，接下來我們來仔細分析一下。關鍵在於理解嵌入 VLOOKUP函數第三參數的MATCH函數是如何發揮作用的。

MATCH(C$2,$I$2:$N$2,0)

這個公式得出的數字指向的是，第一參數指定的儲存格C2的值（即商品名的值）位於第二參數指定範圍（$I$2:$N$2）的左數第幾個。在這一例子中為數字2，它與儲存格C3中以B3的值（數位1）為檢索值的VLOOKUP函數裡，檢索範圍I:N從左數第幾列的對應數字是一致的。

將輸有儲存格C3內容的儲存格一致複製粘貼到G6，為了不讓參照項移位元元，需要設定絕對引用。

在有多個相同檢索值的工作表中使用VLOOKUP函數的技巧

VLOOKUP函數會以最初達成一致的檢索值儲存格作為對象

在A列中重複輸入了同一家客戶公司的名稱，B列中則為相應的負責人的名字。

如果以A列和B列中的資料為基礎，想要在E列中按順序輸入相應的負責人，這時使用 VLOOKUP函數可能會無法得到想要的結果。我們來實際操作一下。

① 在儲存格E1中輸入以下公式：

=VLOOKUP(D2,A:B,2,0)

② 將儲存格E1中的公式一直複製粘貼到第7行。

E列中的相同的公司對應同一名負責人。例如，ABC股份有限公司，原本是按鈴木、田中、加藤這樣的順序排列，而現在全部變為了“鈴木”。

像這樣檢索值存在重複的情況，VLOOKUP函數會以從上數、與起始處一致的檢索值的儲存格為物件來處理資料。儲存格E2、E3、E4也同樣如此，都以“ABC股份有限公司”為檢索值，在作為檢索範圍的A列中以最初的儲存格A2為物件運行VLOOKUP函數，所以會返回“鈴木”這個值。

無重複狀態下應加工後再處理

為瞭解決這個問題，我們可以把有重復資料的A列和D列中的資料“加工”成唯一的狀態，也就是該列下無重複的狀態。這裡，我們需要重新追加操作用的資料列，再進行處理。

這個方法的原理是，給重複的客戶公司名稱標上不同的固定編號。

首先，在各個表的左側分別追加2列，作操作用。

表格左側分別追加2列，供操作用

按照以下步驟，給相同客戶公司名稱的每個資料分別標上編號。每個公式引用的哪個儲存格，進行了怎樣的處理，我們一邊看一邊分析。

① 在儲存格A2輸入以下公式，一直複製粘貼到第7行。

=COUNTIF($C$2:C2,C2)

※C列的客戶公司名稱標上數位

② 同樣地，儲存格F2輸入下列公式，一直複製粘貼到第7行。

=COUNTIF($H$2:H2,H2)

※H列的客戶公司名稱標上數位

③ 在儲存格B2中輸入以下結合了固定編號和客戶公司名稱的公式，一直複製粘貼到第7行。

=A2&C2

④ 同樣地，在儲存格G2輸入下列公式，一直複製粘貼到第7行。

=F2&H2

做完以上步驟，在I列輸入下列VLOOKUP函數後，目標儲存格中就會自動顯示相應的負責人了。

=VLOOKUP(G2,B:D,3,0)

顯示個別對應的負責人名稱

這個方法的關鍵在於，用COUNTIF函數給每個資料設定編號（出現次數），通過編號與檢索值得到新的固定檢索值，並將其嵌入VLOOKUP函數中，由此就能得出正確結果了。

是否能用VLOOKUP函數獲得檢索列左側的數值？

VLOOKUP函數下，無法取得檢索列左側的數值

VLOOKUP函數可以說是Excel中最重要的函數，這裡讓我們再來看看其具體的公式和功能。

【公式】

=VLOOKUP(檢索值,檢索範圍,列數,0)

【功能】

在檢索範圍最左一列中查找與檢索值相同的儲存格，然後在該儲存格中返回第三參數指定的列數中的某個儲存格的值。

“從檢索範圍的最左邊的列返回到第三參數指定的列數中的某個儲存格的值”，也就是“返回位於該列右側的值”。

那麼，問題就來了。

“難道無法直接用這一列左側的數值嗎？”

“給第三參數做減法匯出數值就可以了吧？”或許有許多人都抱有這樣的疑問。但答案是：“不可以”。

那麼，如果想要獲得位於檢索列左側的列中的數值，應該怎麼辦？

什麼是OFFSET函數

組合使用OFFSET函數與MATCH函數可以解決前文中的問題。OFFSET函數的本質是“確定作為基準的儲存格，通過上下左右偏移得到新的區域的引用”。

【公式】

=OFFSET(基準儲存格,偏移行數,偏移列數)

【功能】

是以基準儲存格為起始，返回按移動行數、移動列數偏移的儲存格的值。

偏移行數，正數表示向下，負數表示向上。

偏移列數，正數表示向右，負數表示向左。

首先，舉個非常簡單的例子。

① 在Excel工作表的儲存格C3中輸入“100”。

② 將下列公式輸入任意一個儲存格。

=OFFSET(A1,2,2)

輸入有上述公式的儲存格，將返回“100”。

作為基準儲存格的A1，向下2行、向右2列的目標儲存格是C3（值為100）。所以輸有此公式的儲存格所返回的值就是100。

將OFFSET函數與MATCH函數組合

運用這個公式，想辦法引用檢索列左側的儲存格。

下列表格我們可以看到，按照儲存格E2的數字，在F2、G2的“課程”和“單價”中會分別對應返回資料。首先，先在E2裡輸入1。

首先，F2的“課程”十分簡單，通常使用VLOOKUP函數就能處理。

=VLOOKUP(E2,B:C,2,0)

在儲存格F2中輸入=VLOOKUP(E2,B:C,2,0)後取得“課程”資料

但是，儲存格G2的“單價”資料位於單價的檢索列（B列）的左側，這樣用VLOOKUP函數就無法處理了。

這時候，我們可以組合使用MATCH函數和OFFSET函數。為了匯出E2中“No.”所對應的單價資料，G2中要輸入以下公式：

=OFFSET(B1,MATCH(E2,B:B,0)-1,-1)

在儲存格G2中輸入=OFFSET(B1,MATCH(E2,B:B,0)-1,-1)

以儲存格B1為基準，作為第二參數的結果的數字向下、再向左移動1格的目標儲存格數值將會出現在G2中。

第二參數的MATCH函數，會查找儲存格E2的值位於B列的上數第幾列。儲存格E2的值若為1，B列內容為1的儲存格位於第2行，因此MATCH函數匯出結果為“2”。在這個例子中，以儲存格B1為基準的OFFSET函數直接嵌入MATCH函數中，由於B1向下偏移數為2，產生了1格的誤差，所以需要做出調整，在此基礎上減去1。

在OFFSET函數中，可以將第二參數的移動行數、第三參數的移動列數指定為負數值。也就是說，可以引用位於基準儲存格的上方、左側的儲存格。利用這一特性，可以解決VLOOKUP函數無法引用位於檢索列左側儲存格的缺陷。

不顯示錯誤值的技巧

逐次修正錯誤會導致效率低下

在輸入訂單的明細欄、單價等資料時，只要輸入商品No.就可以同時顯示商品名和單價。如果預先可以設置這樣的機制，就能快速推進工作了。同時，還能避免人工輸入造成的錯誤。我們在B列中輸入只要在A列中輸入商品No.，就能顯示相應的商品名稱的VLOOKUP函數。

① 在儲存格B2中輸入以下VLOOKUP函數：

=VLOOKUP($A2,$E:$G,2,0)

② 按回車鍵確定，並將公式一直複製粘貼到最後一行。

如圖所示，儲存格中會出現“#N/A”這樣的錯誤值。這是由於函數公式中存在錯誤所誤造成的。若是在儲存格A2中輸入1，就會從負責商品欄中匯出對應的商品名稱。

在儲存格A2中輸入1，顯示商品名

總而言之，由於插入的是以商品No.為檢索值的函數，如果A列中皆為空白儲存格，自然就會出現錯誤。

如果是僅在公司內部使用的工作表，這樣也沒什麼問題。但是，如果是製作報價單或訂單的話，要盡可能避免這種錯誤值的出現。但是，只是單純刪去儲存格中的函數，再次使用時還是需要重新輸入公式，這樣非常沒有效率。

如結果有誤，則返回空白值

這一問題，可以運用處理“計算結果有誤的話，返回空白值”的函數公式來解決。這時，我們會用到IFERROR函數（Excel2007之後的版本中具備的函數）。

通常都是因為先輸入基本公式後，才發現有可能會有錯誤，再進行隱藏錯誤的處理。因此，輸入公式時就要嵌入先前提到的VLOOKUP函數。最終，儲存格B2中要輸入以下公式：

=IFERROR(VLOOKUP($A2,$E:$G,2,0),"")

① 選擇儲存格B2，按F2鍵，使儲存格處於可編輯狀態

② 在等號（=）之後輸入“i”後出現候選功能表，選擇第2個“IFERROR”。

③ 按TAB鍵確定後，補充輸入=IFERROR(。

④ 完成公式後按回車鍵確定，並將公式一直複製粘貼到最後一行，就可以隱藏錯誤值。

⑤ 在A列中輸入商品 No.，會自動顯示商品名與單價的資料。

IFERROR函數第二參數中，連續輸入了2個引號""，這是指定空白值的意思。

把B列的公式複製到C列，VLOOKUP函數第三參數改為3。

IFERROR函數的特點在於，第一參數指定的函數為錯誤值時，就會返回第二參數指定的值。在這個例子中，第二參數指定的是空白值，因此也就設定了“第一參數的VLOOKUP函數若為錯誤值，顯示為空白結果”這樣的機制。

如使用2003之前的Excel版本的話，應該怎麼做

只有在Excel2007之後的版本才可以使用IFERROR函數隱藏錯誤值。如果你的Excel是2003版之前的，可以使用下面的公式：

=IF(ISERROR(公式),"",公式)

ISERROR函數可以檢查括弧內指定的公式是否為錯誤值。如果是則為“真”，否則返回“偽”值。以此為基礎來解讀IF函數，便可知其處理過程是這樣的：第一參數的邏輯式若為真，也就是說ISERROR函數結果為真，則返回第二參數的空白值，否則將繼續處理公式。

用SUMIF函數統計多個條件的方法

追加帶有統計條件的“工作列”

SUMIF函數和COUNTIF函數，都是用於計算符合條件的儲存格的總和，以及儲存格個數的函數。如果想使用這兩種函數計算出2個條件以上的統計結果的話，我們需要稍微動一下腦筋。

比如下表，僅在儲存格H4中為A列負責人“吉田”、B列商品代碼為“A001”這個條件下，在D列中顯示銷售額數值。

SUMIF函數第一參數只能指定1列。但在此表中，中繼資料中無法在1列中同時判定負責人和商品代碼這2個條件。A列只能判定負責人，B列只能判定商品代碼。

這時候，就需要“在中繼資料中追加作為新的統計條件的資料列”。這樣的做法，通常被稱為追加“工作列”或“計算儲存格”。

我們來嘗試添加結合負責人姓名和商品代碼的資料列。具體操作如下。

① 在儲存格E4輸入下列公式，並一直複製粘貼到資料最後一行。

=A4&B4

② 在儲存格H4輸入下列公式：

=SUMIF($E:$E,$G4&H$3,$D:$D)

③ 將儲存格H4中的公式複製至全表。

在這裡，設置絕對引用也十分重要。利用指定SUMIF函數的參數指定各個儲存格時，按幾次F4鍵就會像上面這樣出現符號“$”。

然後，將最開始在H4中輸入的公式一直向右複製至M列，向下複製至第8行。這裡，為使引用儲存格不偏離正確的列和行，設定了絕對引用。

要重視簡單易懂

在2007版本之後的Excel中，追加了複數條件下也能統計資料總和的SUMIFS函數和COUNTIFS函數。甚至像前文中的例子一樣，不需要追加工作列也可以求和。但是，如果統計條件增多，參數的指定就會變得複雜，因此，需要追加工作列，分成幾個步驟來處理。

另外，陣列公式和SUMPRODUCT函數也可以用同樣的方式處理，但就從簡單易懂這點上來看，我還是推薦大家採用追加工作列這種方法來處理。