申明: 本站飛宇網 https://feiyetopro.blogspot.com/。自網路收集整理之書籍、文章、影音僅供預覽交流學習研究，其[書籍、文章、影音]情節內容， 評論屬其個人行為， 與本網站無關。版權歸原作者和出版社所有，請在下載 24 小時內刪除，不得用作商業用途；如果您喜歡其作品，請支持訂閱購買[正版]。謝謝！
第3章 需要事先掌握的6個函數
Excel中總共有400個以上的函數，當然也沒有必要全部掌握。工作中會用到、需要掌握的函數約有60～70個。
其中，我們應該優先掌握的是以下6個非常重要的基礎函數。
● “根據指定的條件來對應處理資料”……IF函數
● “這個月的銷售額是多少？”……SUM函數
● “這一銷售資料涉及幾筆交易？”……COUNTA函數
● “銷售額的明細如何？……比如分別計算每個負責人的銷售額”……SUMIF函數
● “出席者名單，多少人有××？”……COUNTIF函數
● “輸入商品名稱，無法自動顯示價格？”……VLOOKUP函數
接下來，我將會具體講解這6個函數。
根據條件改變答案——IF函數
IF函數的基礎知識
如果你是老師，你想以“考試分數在80分以上的是A，80分以下的是B”作為判斷條件，在B列中輸入所有分數後，C列中會顯示相應結果。可以按照下面的方法操作。
① 在儲存格C2中輸入以下公式：
=IF(B2>=80,"A","B")
② 按Enter鍵，C2中得出“B”
③ 將公式複製到其他儲存格，系統會根據分數自動做出判斷。
像這樣，根據作為判斷條件的數值，可以更改儲存格中的數值或公式的結果。這就是IF函數的作用。
下麵是IF函數的具體結構。
【公式】
=IF(條件運算式,條件為真,條件為假)
像這樣表示函數構造的形式，叫作“公式”。不是說一定要準確無誤地記住所有函數的公式。只要能做到看一眼就大概明白其中的含義，在實際操作中也能熟練運用就可以。
在此，我們來具體看一下函數結構中各部分所表示的含義。
● 第一參數：條件運算式（用於按照條件分別處理結果）
※上述例子（B2>=80）中，表示儲存格B2的值是否在80以上
● 第二參數：條件為真（即第一參數中的條件運算式成立，符合條件時返回的值）
● 第三參數：條件為假（即第一參數中的條件運算式不成立，不符合條件時返回的值）
也就是說，之前列出的公式，其實是一個命令句“B2的值大於等於80輸入A，不是則輸入B！”
如何判定複數條件
在判定複數條件時，請把多個IF函數嵌套在一個公式中。比如說，如果要表達“B2儲存格的值大於等於80為A，大於等於50為B，49以下為C”，就簡化為下列公式。
=IF(B2>=80,"A",IF(B2>=50,"B","C"))
乍一看也許有人會覺得這個公式又長又複雜，但它只是在重複下面的程式。
① 一開始的條件運算式(B2>=80)，如此條件為真，輸入值(A)。
② 下一個參數，再次從IF和括弧開始輸入。
③ 接著輸入下一個條件運算式。
如果不符合這兩個條件運算式中的任何一個條件，則表示“結果為假”，輸入的值則指定為”C”。
像這樣，在IF函數中嵌套一個IF函數的現象，叫作“多重條件函數”。IF函數的多重條件，在Excel2007以後的版本中，最多可以排入64個。但是，如果嵌套的函數太多，可能變成自己都難以理解的複雜算式，這點請務必注意。遇到這種情況，可以利用VLOOKUP函數的變換技巧（參考P272），或利用操作列（參考P125）劃分到多個儲存格分別處理。總之，可以採取不同的方法。
本月銷售額——SUM函數
SUM函數的基礎
在Excel中，加法用“+”符號進行運算。想要求儲存格A1與A2的數值總和，可以用下列算式做加法。
=A1+B1
但是，如果做加法的儲存格有很多，全部用“+”連接的話，需要多次輸入“+”，這樣做十分浪費時間。有一個函數專門用於簡化多個儲存格做加法時的輸入操作，那就是SUM函數。
例如，要計算儲存格B2到B11的值的總和，則在B2中輸入以下公式：
=SUM(B2:B11)
※目標儲存格範圍，用“:”（冒號）連接起始儲存格和最終儲存格。
也就是說，在SUM函數的括弧中的內容是需要計算總和的儲存格的範圍。
【公式】
=SUM（想要計算總和數的儲存格的範圍）
計算連續儲存格範圍內的總和——ΣSUM
在B12與C12中輸入數量與總銷售額。
在B12儲存格裡，輸入SUM(B2:B11)
其實，想要計算多個連續儲存格範圍內的總和，有更簡便的方法，那就是使用ΣSUM函數（SUM函數中的一種），它的功能就是能夠自動輸入SUM函數和計算總和的範圍。
可在【開始】欄目下點擊ΣSUM按鈕，或者不使用滑鼠，直接按快速鍵。雖然這兩種方法的區別甚微，但掌握快速鍵總是方便的。先選擇B12，然後按下以下快速鍵。
Alt+=
隨後，就會像前文中的畫面一樣，系統自動指定合計儲存格範圍，目標儲存格裡也含有SUM函數。
並且，這時候如果在B12與C12都被選中的前提下，按下這個快速鍵，處於自動選中合計儲存格範圍的SUM函數，會同時出現在這兩個儲存格中。
如何求多個分開的儲存格的總和
如果要計算多個分開的儲存格的總和，應該怎麼做呢？
這時，按照以下方式，按下Ctrl鍵並點擊滑鼠，就能輕鬆輸入公式。
① 選中想要求和的儲存格，輸入=SUM(。
※這裡選擇了儲存格C14
② 按Ctrl鍵，選擇需要求和的儲存格。
※如圖所示，點擊儲存格C2、C6、C10。
③ 輸入右括弧，按回車鍵確定。
如此，儲存格C14中顯示為
=SUM(C2,C6,C10)
像這樣，在需要求和的儲存格之間輸入“,”來隔開，就能夠大幅提升工作效率。
如何提高乘法運算、字串混合輸入的效率
在Excel中，同樣有能夠快速輸入乘法運算和字串的函數。
PRODUCT函數可以對括弧內指定的數值做乘法。例如，按如下方式輸入，即可算出儲存格A1到E1數值相乘後的結果。
=PRODUCT(A1:E1)
用星號（*）連接儲存格的話，公式則如下所示。很明顯，前面的方法要輕鬆得多。
=A1*B1*C1*D1*E1
除此之外，還有在括弧內連接多個指定文本的CONCATENATE函數。首先輸入：
=CONCATENATE(
之後，按住Ctrl鍵，點擊想要連接的儲存格，像這樣，選中的儲存格會被“,”隔開。
=CONCATENATE(A1,B1,C1,D1,E1)
用“&”連接各儲存格也是一樣，但存在多個需要連接的目標儲存格時，還是這種方法更簡便。
計算客戶名單的人數——COUNTA函數
“銷售額”不僅是金額的總和
前文仲介紹的SUM函數，是在日常工作中使用頻率最高的函數之一。但是，在實際操作時也會出現問題。比如在計算銷售額總和時，SUM 函數得出的結果為金額總和。但是，除金額以外，“成交件數”“銷售個數”“客戶人數”也是“銷售額”中的要素。也就是說，用SUM函數計算得出“銷售額為1億日元”之後，接下來有必要表示“這些銷售額中的成交量是多少”。
這時，我們就能用到COUNTA函數了。如果說SUM函數用來“算出指定儲存格的總和”，那麼COUNTA函數則是用來“計算指定儲存格的範圍內，包含有效數值的儲存格的個數（即非空白儲存格的個數）”。
例如，有一張按活動參加者分類顯示購買入場券數量的表格，如果現在想要知道有多少名參加者，應該怎麼做呢？
A列中輸入參加者的名字，想要在儲存格E1中顯示參加者人數的話，可以在儲存格E1中輸入如下公式：
=COUNTA(A:A)-1
在儲存格E1中輸入=COUNTA(A:A)-1
把函數翻譯成文字
這一函數，實際是通過以下方式進行計算的。
“數一數在A列中，有多少儲存格內含有資料（除空白儲存格以外的數量），並減去1”
為什麼要減去1呢？這是因為計算時要除去內容為“參加者姓名”的儲存格A1。像這樣，在實際使用Excel時，必須掌握“迎合不同情況，在函數公式中通過增減數位進行調整”這種能力和思維方式。
“能把函數用文字翻譯出來”非常重要。要習慣用文字來解釋說明函數公式在進行怎樣的處理。
此外，在此介紹的“整列儲存格數減去1”的公式，也可用於自動增減在輸入規則中的功能表選項。（參考P200）
與COUNT函數的區別
與COUNTA函數極為相似的函數是COUNT函數。它與COUNTA函數的區別如下：
● COUNTA函數
指定參數範圍內，計算除空白儲存格之外的儲存格的個數，即統計包含資料的儲存格的數量。
● COUNT函數
指定參數範圍內，計算含有數值的儲存格的數量。
也就是說，COUNT函數只計算含有數位的儲存格個數。因此，自動忽略統計含有文本的儲存格的數量。在具體實務操作上，一般用COUNTA函數就夠了，當需要計算輸入有數位、資料的儲存格的數量時，再使用COUNT函數即可。
按照負責人分別計算銷售情況——SUMIF函數
SUMIF函數的基礎
如下所示，A列為負責人，D列為銷售額數據。
A列：負責人，D列：銷售額
以這個資料表格為基礎，從G列開始，計算每一個負責人的銷售額的總和。
在做這項工作時，我看到很多人發生了以下“慘劇”。
● 使用電子計算器，手動計算資料。
● 輸入“=SUM(D2,D7,D12,D17,D18)”，統計每一名負責人的銷售額總數時，不斷重複這一操作。
那麼，怎樣做才正確的呢？
這時我們可以使用SUMIF函數，我們看一下具體的操作步驟吧。
① 在儲存格H2內輸入以下公式
=SUMIF(A:A,G2,D:D)
② 按下回車鍵後，儲存格H2內顯示“吉田”負責的銷售額。
③ 將儲存格H2中的公式拖拽複製至 H6，則會顯示相應的負責人的銷售額。
④ 想要得出所有負責人的銷售額總和時，則需要按兩下儲存格H7，再按下AUTOSUM 快速鍵Alt+=。
⑤ 按下回車鍵，可得出全員銷售額的總和。
SUMIF函數有三個參數。
● 第一參數：用於條件判斷的儲存格區域
● 第二參數：在第一參數指定的範圍裡，需要計算總和的行的判定條件
● 第三參數：實際求和的區域
按照步驟1輸入“=SUMIF(A:A,G2,D:D)”這一公式，Excel會自動識別，做出以下的處理：
● 需要計算總和的區域為D列數值。但並不是要算出D列中全部數值的總和。
● 在A列中，只計算與G2的值相同的行的D列數值的總和。
不能只匯出實數一覽表
決不能僅限於匯出實數一覽表。將得出的數字進行比較，才有其計算的意義。讓我們再算一下“結構比率”吧。
① 儲存格I2中，銷售額輸入計算吉田的銷售額占整體的比例的公式。
② 按下回車鍵，顯示出吉田的銷售額在整體中的所占比例。
③ 拖拽複製到儲存格I7，即可顯示每一名負責人所占的比例。
如此一來，即可顯示每一名負責人的銷售額所占比例，每一名負責人對公司的貢獻程度等情況也會一目了然。
另外，比起“A君的業績比誰都高啊”這種模糊的說明，通過使用“A君的銷售額占全員的43%”這樣帶有具體資料表達，就會讓說明變得更加詳細、更有說服力。
參加名單中，有多少人出席—COUNTIF函數
COUNTIF函數的基礎
假設要製作活動的參加者名單。參加與否一列中需要輸入○、△、×這3種符號。
活動出席人員名單
那麼，如自動計算出現在的參加者有幾人，即標記“○”的人數是多少，以及“缺席人員，標記‘×’的有幾個人”，應該怎樣處理呢？當然，我們不可能每次都口頭計算，再填到E1～E3的表格裡，這樣太浪費時間了。
為節省時間，有一種函數可以算出“在B列中，標有‘○’的儲存格有多少個”，那就是COUNTIF函數。
好了，我們來試著在B列中分別計算儲存格E1～E3中的○、△、×的數量吧。也就是說，即使這張參加名單表格有任何追加、變更的情況，各個記號的數量也會自動更新。
① 在儲存格E1中，輸入以下公式，計算B列中與D1有相同內容的儲存格的數量。
=COUNTIF(B:B,D1)
② 按回車鍵，在儲存格E1中顯示結果。
③ 將公式拖拽複製至儲存格E3，顯示其他記號的數量。
COUNTIF函數是由下麵2種參數構成的。
● 第一參數：計算其中非空儲存格數目的區域
● 第二參數：在第一參數的指定範圍內計算數目的條件
指定範圍（第一參數）中，計算出第二參數指定的值或者與指定的條件一致的儲存格的數目。
如何計算每名負責人員的銷售件數
用前面寫到的SUMIF函數可以算出每一位負責人員達成的銷售額，那麼這回來算一下每個人的銷售件數吧。利用前面介紹過的SUMIF函數，在H列的“銷售額”中輸入每一位負責人的銷售額。
I列的“銷售件數”，則顯示“這些銷售額分別來自多少件銷售業務”這一資料。在這個表格中，儲存格I2中的數字表示“A列中含有的儲存格G2數值的數目（即“吉田”）有多少”。
① 在儲存格I2中輸入以下公式，計算A列中有儲存格G2數值的數目（即“吉田”）有多少。
=COUNTIF(A:A,G2)
② 按回車鍵後，儲存格I2中顯示的數值表示：A列中出現的與G2有相同值的儲存格（即“吉田”）的數目。
③ 將儲存格I2中的公式拖拽複製到I6，然後選擇I7，按Alt+Shift+=（AutoSUM快速鍵）獲得總和。
完成以上三步，即完成統計。
這裡出現的數字，計算的是“A列中含有各負責人名字的儲存格，各有多少個”。把它作為一個商業資料概念來講的話，表示的是“吉田的銷售件數共有5件”。
另外，在這張圖中有一列空白的儲存格，此列資料是將每一位元負責人的銷售額除以銷售件數，得到的平均銷售額的資料。通過計算結果，就能分析出如“雖然以銷售件數來說吉田比佐藤多一些，但是佐藤的銷售額更高是因為佐藤的平均銷售額更高”這樣的結果。以此進一步瞭解到“吉田只要向佐藤看齊，增加每件交易的平均銷售額，即可提高總銷售額。”
只是像這樣簡單地分析，也能成為我們探討一些具體銷售戰略的契機，比如“為了這一目標，應該具體訂立怎樣的銷售策略？”“我們應該考慮什麼樣的促銷手段？”
通過使用COUNTIF函數，我們可以檢查資料是否重複、確認指定資料是否存在、儲存格中是否包含指定文本，等等。這是一個十分方便的重要函數，請一定要掌握。
輸入商品名，自動顯示價格——VLOOKUP函數
VLOOKUP函數的基礎
假設有以下資料表格。
這時，A列中輸入商品代碼後，單價一列即可自動出現價格，這樣不僅十分方便，還能避免輸入錯誤。
但是，要想實現這點，需要預先在其他地方準備好“各商品的價格”一覽表。在這張Excel工作表中，可作為參考資訊的表格（商品單價表）位於右側。
那麼，我們試著將與A列各商品代碼匹配的單價顯示在B列中吧。
① 在儲存格B2中輸入以下函數。
=VLOOKUP(A2,F:G,2,0)
② 按回車鍵確定後，將B2拖拽複製到儲存格B8。
由此，B列的各儲存格中出現了與商品代碼匹配的單價。
在此輸入的VLOOKUP函數，到底是什麼樣的函數呢？只有能夠用文字解釋，才算是完全掌握了這個函數。將VLOOKUP函數轉換成文字，則為以下的指令：
“在F列到G列範圍內的左邊一列（即F列）中，尋找與儲存格A2的值相同的儲存格，找到之後輸入對應的右邊一列（即G列）儲存格。”
VLOOKUP中的V，代表Vertical，表示“垂直”之意，意為“在垂直方向上查找”。此外，類似函數還有HLOOKUP函數，首字母H代表Horizontal，表示“水準”之意。因篇幅有限，本書無法做出更詳盡的說明，有興趣的讀者可自行瞭解。
4個參數的意義與處理流程
用逗號（,）隔開的4個參數，我們來看看這4個參數各自表達的意思吧。
● 第一參數：檢索值（為取得需要的數值，含有能夠作為參考值的儲存格）
● 第二參數：檢索範圍（在最左列查找檢索值的範圍。“單價表”檢索的範圍）
● 第三參數：輸入對應第二參數指定範圍左數第幾列的數值
● 第四參數：輸入0（也可以輸入FALSE）
這個函數，首先在某處搜索被指定為第一參數檢索值的值。至於搜索範圍則是第二參數指定範圍的最左邊的列。上述例子中，第二參數指定的是F列到G列的範圍，因此檢索範圍即為最左列的F列。
接下來，如果在F列裡發現了檢索值（如果是儲存格B2則指A2的值即“A001”，F列中對應的是F3），那麼這一儲存格資料即為往第三參數指定的數字向右移動一格的儲存格數值。這一例子中，第三參數指定為2，因此參考的是從F3往右數第2列的儲存格G3的數據。
之後，再在這張表的小計欄中輸入“單價×數量”的乘法算式，輸入數量後，系統就會自動計算小計欄中的資料。
如果在報價單與訂單的Excel表格裡設置這樣的構造，製作工作表時就會十分方便。這是一項能夠提高Excel操作效率的基礎。
用“整列指定”檢查
請注意一下在第二參數中指定F列和G列這兩個整列的這一操作。這樣，即便在單價表裡追加了新商品時，VLOOKUP函數依然可以做出相應的處理。在設定事先輸入VLOOKUP函數，就能自動顯示的格式時，也一併使用上述方便的功能吧。
下面的公式，僅指定了單價表範圍，每次增加商品時都需要修改VLOOKUP函數，這樣十分浪費時間。
=VLOOKUP(A2,$F$3:$G$8,2,0)
無論是SUMIF函數、COUNTIF函數還是VLOOKUP函數，基本都是以列為單位選取範圍。這樣不僅能夠快速輸入公式，使用起來也十分方便。
0 留言:
發佈留言