Excel高階應用教程：資料處理與資料分析

資料分析v發表於2018-05-25

為一名合格的資料分析師或者說一名稱職的資料探勘領域從業者，大家肯定都耳熟能詳的認為其必須具有如下基本技能：

一、熟練的掌握SQL、Hive等；

二、R、SAS、Python等至少精通其中一種。

但往往大家忽視了最基本的技能要求：使用Excle進行資料處理與資料分析的能力。大家可以仔細回想下自己公司裡面，excel玩的很6的人絕對不是資料部門的人，而往往是業務部門或財務部門的同事。Excel作為資料分析工具，可以分為兩大部分，一是Excel的資料處理能力，一是Excle的高階資料分析能力。在小資料量下（注：大資料量下會存在處理效率的問題），Excel完全可以像R、SAS等統計軟體進行假設檢驗、相關分析、迴歸分析等資料分析與資料建模工作，這就是Excel的高階資料分析功能。本文作為Excel使用介紹的開篇，先和大家一起分享Excel的高階資料處理方法。

一

利用Vlookup函式進行資料查詢

在分享這個函式前，我們先來思考如下一個案例：

【案例1.1】假設所得稅的稅率如下圖1.1區域所示。其中的含義是：

0~500的稅率為0%，

500~1000的稅率為1%，

1000~1500的稅率為3%

……，4000以上的稅率為20%

問題：根據職工收入如何快速計算每位職工應繳的所得稅？

圖1.1 工資所得稅計算

計算所得稅的關鍵就在於根據收入找到其對應的所得稅率。肯定有同學會說，這個簡單，直接使用if函式就可以很方便的解決。誠然，if函式確實可以解決這個問題，但實際操作起來的時候，你會發現需要巢狀多個if函式方可。如果分段較多的話，if函式進行操作很不方便，因為很可能到最後你都不記得到底巢狀了多少層if函式。對於這個問題，Vlookup函式可以快速便捷的解決。

>>>>

Vlookup函式講解

功能

Vlookup按列查詢的方式從指定資料表區域的最左列查詢特定資料，它能夠返回查詢區域中與找到單元格位於相同行不同列的單元格內容。

格式

Vlookup (x, table, n, f)

其中，x是要查詢的值；table是一個單元格區域；n中table區域中要返回的資料所在列的序號。n=1時，返回 table 第1列中的數值；n=2時，返回 table 第2列中的數值；以此類推。f是一個邏輯值，表示查詢的方式。當其為true（或1）時，表示模糊查詢；當它為false（或0）時，表示精確查詢。

說明

Vlookup函式在table區域的第1列中查詢值為x的數值，如果找到，就返回與找到資料同行第n列單元格中的資料。當f為true時，table的第1列資料必須按升序排列，否則找不到正確的結果；當f為false時，table的第1列資料不需要排序。

注意

①如果Vlookup函式找不到x，且f=true，則返回小於等於x的最大值。

②如果x小於table第1列中的最小值，Vlookup函式返回錯誤值“#N/A”。

③如果Vlookup函式找不到x且f=FALSE，Vlookup函式返回錯誤值“#N/A”。

>>>>

案例講解

（1）用Vlookup進行模糊查詢

前面一開頭提出的計算所得稅那個問題，就可以使用Vlookup的模糊查詢進行完美解決（具體方法見圖1.2）。

圖1.2 Vlookup進行模糊查詢

（2）用Vlookup進行精確查詢

精確查詢就是指查詢資料完全匹配的查詢，Vlookup函式具有此項功能。在大表中查詢特定資料，或查詢不同工作表中的資料，特別是工作表資料較多， Vlookup函式顯得非常有效。

【案例1.2】某校某專業期末考試的資料庫成績表如圖的A:H列所示。由於人數較多，要檢視某個同學的成績非常困難。希望能按學號進行查詢，即在K5輸入某個學號後，就能自動顯示出該學號所對應的姓名和各種成績，如圖1.3的J4:M16所示。

圖1.3 個人成績查詢

案例解決方法如下：

（1）在M5中輸入公式：=VLOOKUP(K5,A5:H227,2,0)

（2）在L6中輸入公式：=VLOOKUP(K5,A5:H227,3,0)

（3）在L7中輸入公式：=VLOOKUP(K5,A5:H227,4,0)

（4）在L9中輸入公式：=VLOOKUP(K5,A5:H227,5,0)

（5）在L11中輸入公式：=VLOOKUP(K5,A5:H227,6,0)

（6）在L13中輸入公式： =VLOOKUP(K5,A5:H227,7,0)

（7）在L15中輸入公式： =VLOOKUP(K5,A5:H227,8,0)

二

Index和Match相結合查詢資料的方法

一、Index和Match相結合查詢資料的方法

同樣，在瞭解Index和Match函式前，我們先來思考如下一個案例。

【案例2.1】某地域中各縣的蔬菜銷售單價表如圖2.1的A4:J18區域所示，希望能夠快捷地查詢到某地某蔬菜的單價。最好是輸入地名和蔬菜名，就能看到對應的蔬選單價，如圖2.1的B1:D3區域所示。

圖2.1 蔬選單價查詢

對於這種二維查詢的問題，像前面的vlookup函式或者大家熟悉的lookup函式是不能做到的，這時候就需要match函式和Index函式結合起來使用了。用一句簡單話來講，macth函式主要職責就在於定位，Index函式主要職責就在於根據match提供的位置資訊去指定區域“抓人”（取數）。Index函式和Match函式這兩個搭檔，就很像狙擊手裡面第一狙擊手和第二狙擊手，一個負責定位和觀察，一個負責精準狙擊。

>>>> Match函式使用講解

Match函式提供了比lookup（或Vlookup、Hlookup）函式更多的靈活性，它可以在工作表的一行（或一列）中進行資料查詢，並返回資料在行（或列）中的位置。

如果需要找出資料在某行（或某列）的位置，就應該使用Match函式而不是Lookup函式。

在多數情況下，Match函式的結果並不是所需要的最終答案，而是作為lookup（Vlookup，Hlookup）的第3個引數或作為Index函式的引數。

Match格式

Match ( x， r，f )

其中x是要查詢的數值，r可以是一個陣列常量，或某列（或行）連續的單元格區域，其中可能包含有要查詢的x。f用於指定match的查詢方式，它可以是-1，0或1。

功能

Match(x，r，f)表示的意思是：在陣列或連續的單元格區域r中查詢x，並返回x在r中的位置編號。當f為0是，match進行精確查詢，當f為1（或-1）時，match進行模糊查詢。

說明

f=-1時，r必須按降序排列，查詢大於或等於 x的最小數值

f=0時，r 不必排序，查詢等於x的第一個數值

f=1時，r必須按升序排列，查詢小於或等於x的最大數值

>>>>

Index函式使用講解

格式

Index(Area，r，c，n)

其中，Area是1個或多個單元格區域；r是某行的行序號，c是某列的列序號，該函式返回指定的行與列交叉處的單元格引用。如果r等於0，則返回整行單元格引用，如果c等於0，則返回整列單元格引用。

當Area包括多個單元格區域時，n=1就表示結果來自於Area中的第1個區域，n=2表示結果來源於第2個單元格區域……。如果省略n表示結果來源於第1個單元格區域。

功能

Index（Area，r，c，n）的功能是返回Area中第n個單元格區域中的r行，c列交叉處的單元格引用。

>>>>案例講解

前面提到的案例2.1，利用Index和Match函式結合起來可以很快速的進行解決，如下圖2.2。

圖2.2 蔬選單價查詢

三

D函式查詢資料的方法

如果能把Excel裡面某個區域裡面的資料看成是資料庫中一張表，在Excel裡面對資料進行資料庫裡面SQL一樣的操作該多好。

在Excel中，資料庫是指每列資料都有標題的資料表。Excel提供大約12個專用資料庫函式來簡化這種資料表的資料統計和資料查詢工作，這些函式都以D開頭，所以也稱為D函式。

D函式有相同的呼叫形式，相同參數列，格式如下：

Dname(database,field,criteria)

其中的Dname是函式名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、Dmax、Dmin等。各函式的功能如其名字所示，Dsum求總和，Daverage求平均數，Dget查詢資料，Dcount統計數字個數，Dcounta統計文字和資料的個數，Dmax求最大數，Dmin求最小數。

database是一個單元格區域，要求該區域中的每列資料都必須有標題；field是database區域中某列資料的列標題（稱為欄位，出現在字串中）；criteria稱為條件區域，它與高階篩選條件區域的含義和構造方法完全相同。

【案例3.1】某校某專業共有224名學生，某次期末考試的“資料庫系統應用”課程的成績表如圖所示。現在要查詢每位學生的成績，希望輸入學號後，就能夠得到該生的各種詳細資料，如圖3.1的J1:M8區域所示。此外，還希望對各班的考試情況進行簡單的統計分析，能夠隨時檢視各班的考試人數，最高成績，高低成績，及缺考人數等，如圖3.1的J10:N17區域所示。

圖3.1 班級成績分析

對於對各班的考試情況進行簡單的統計分析，如果是在資料庫裡面，就是一段簡單的SQL程式碼，如統計上機平均成績：

select avg(上機成績)

from database

where 班級=’0320302’；

在Excel裡面，這個就可以藉助D函式實現異曲同工之效。

案例3.1解決方案如下：

（1）在K13中輸入計算上機平均成績的公式：

=DAVERAGE(A4:H227,"上機成績",J12:J13)

（2）在M13輸入計算綜合平均成績的公式：

=DAVERAGE(A4:H227,"綜合成績",J12:J13)

（3）在K15輸入計算缺考人數的公式：