|
入職第一天，老板拿來一堆厚厚的歷史數(shù)據(jù)資料，并扔下一句話說：趕快把VLOOKUP函數(shù) 和數(shù)據(jù)透視表 自己研究研究，一定要學(xué)會，不然以后有你的苦日子了！ 于是一臉懵逼的我就開始了和VLOOKUP函數(shù)、數(shù)據(jù)透視表天天打交道的日子，也在這種反復(fù)的使用中深深體會到了掌握它們給工作帶來的便利。今天就通過案例講解的方式和大家詳細(xì)地分享下VLOOKUP函數(shù)、數(shù)據(jù)透視表應(yīng)用的基本原理和方法。
 數(shù)據(jù)之道 一、VLOOKUP函數(shù)
 VLOOKUP函數(shù)公式的基本結(jié)構(gòu) VLOOKUP函數(shù)公式的基本結(jié)構(gòu)是不是很容易就記住了呢，同時(shí)提醒大家尤其是初學(xué)者，如果第四參數(shù)為FALSE，則精確匹配，如果為TRUE，則模糊匹配。
如下圖所示的“人事信息表”中，我們要通過【姓名】列中對應(yīng)的姓名，來實(shí)現(xiàn)匹配身份證號信息。如果信息量少，我們自然可以對比復(fù)制粘貼，而涉及到大量信息的查找匹配時(shí)，我們就得用VLOOKUP函數(shù)了。  人事信息表 針對這個問題，應(yīng)該怎么寫VLOOKUP函數(shù)呢？先看動畫演示，再具體分析。  VLOOKUP函數(shù)單條件查找使用演示 具體操作步驟：在這里，我們直接應(yīng)用VLOOKUP函數(shù)進(jìn)行查找匹配，你要能正確的寫出VLOOKUP函數(shù)，相應(yīng)的問題也就迎刃而解： 1. 單元格內(nèi)輸入函數(shù)公式：半角狀態(tài)下，=VLOOKUP，Excel會自動顯示函數(shù)，方便書寫。如果對VLOOKUP的函數(shù)不熟悉，就可以點(diǎn)擊編輯欄左側(cè)的fx，彈出函數(shù)提示對話框。 2. 第一個參數(shù)（查找值）：我們的任務(wù)是通過查找兩表中相同的姓名，在另一表中自動匹配上身份證號信息，因此要查找的值就是姓名，也就是要查找的單元格O2。 注：在這里采用相對引用，因?yàn)椴檎抑岛蛦卧袷峭瑫r(shí)變化的。 3. 第二個參數(shù)（查找范圍）：查找的姓名所在的列作為查找范圍的第一列，要匹配的身份證號信息所在的列為最后一列，中間有數(shù)據(jù)的部分即為數(shù)據(jù)范圍（必須的數(shù)據(jù)部分）。在本例中，數(shù)據(jù)范圍為$B$2:$G$21。因?yàn)椴檎曳秶谴_定的，所以一定要用絕對引用，相對引用會引起查找范圍區(qū)間的變化，導(dǎo)致匹配結(jié)果不準(zhǔn)確，一定要牢記！ 注：絕對引用快捷鍵請按F4 4. 第三個參數(shù)（要查找的第幾列數(shù)據(jù)）：查找的姓名所在的列作為查找范圍的第一列，往右邊數(shù)，一直數(shù)到要匹配的身份證號信息所在的列是第幾列。在本例中為第6列 5.第四個參數(shù)（精確還是模糊）：精確查找，參數(shù)為0 公式寫完之后，進(jìn)行公式的自動填充：雙擊公式所在單元格右下角  VLOOKUP函數(shù)注意事項(xiàng)
 VLOOKUP函數(shù)多條件精確查詢使用演示 具體操作步驟及注意點(diǎn)： 1. 因?yàn)樾枰鶕?jù)姓名和性別兩個條件進(jìn)行查找匹配，首先在姓名前添加輔助列，對應(yīng)單元格內(nèi)進(jìn)行文本填充，如單元格B2，填充為=C2&D2，向下依次填充 2. 用&進(jìn)行多條件連接，本例中為P2&Q2 3. 輔助列作為查找范圍首列，確定數(shù)據(jù)范圍和列數(shù) 4. 通常用于多條件才是唯一查找值的場景
VLOOKUP函數(shù)+輔助列+相對引用 可以構(gòu)建小型數(shù)據(jù)查詢系統(tǒng)，如上例中，我們可以通過輸入姓名，實(shí)現(xiàn)性別、民族、部門、籍貫等多項(xiàng)信息自動顯示，具體演示如下：  VLOOKUP函數(shù)構(gòu)建小型數(shù)據(jù)查詢系統(tǒng)演示 具體操作步驟及注意點(diǎn)： 1.要通過查找姓名實(shí)現(xiàn)性別、民族、部門、籍貫、身份證號碼等信息的自動匹配和呈現(xiàn)，就要確保三點(diǎn)，一是要查找的值即姓名所在的單元格絕對引用，這樣才能保證都是通過姓名匹配性別、民族、部門、籍貫、身份證號碼等信息；二是查找范圍要包含上述所有信息項(xiàng)在的區(qū)域，絕對用用；三是保證性別、民族、部門、籍貫、身份證號碼等對應(yīng)的列動態(tài)變化，依次是2,3,4,5,6 2. 輔助列用于實(shí)現(xiàn)性別、民族、部門、籍貫、身份證號碼等對應(yīng)的列動態(tài)變化
如下圖中要實(shí)現(xiàn)對應(yīng)成績與對應(yīng)等級的匹配，就可以通過VLOOKUP函數(shù)實(shí)現(xiàn)  VLOOKUP函數(shù)的模糊查詢使用演示 √ 原理：從小到大升序排列；模糊查詢即區(qū)間查詢，取區(qū)間所匹配的數(shù)值  馬上進(jìn)入第二部分 二、數(shù)據(jù)透視表 數(shù)據(jù)透視表是一種可以快速匯總大量數(shù)據(jù)的交互式方法，是進(jìn)行數(shù)據(jù)查詢、分類匯總、數(shù)據(jù)計(jì)算 的利器，功能十分強(qiáng)大。那么如何創(chuàng)建和使用呢？
如下圖所示的“人事薪酬信息表”中，我們希望查看不同部門不同職務(wù)的工資分布情況  人事薪酬信息表 這個問題就涉及到了數(shù)據(jù)的分類和匯總問題，這恰恰是數(shù)據(jù)透視表的強(qiáng)項(xiàng)。那么怎么創(chuàng)建呢？先看動畫演示，再具體分析。  數(shù)據(jù)透視表的創(chuàng)建使用演示 具體操作步驟： 1. 在數(shù)據(jù)表中任意選中某個單元格—【插入】—最左側(cè)【數(shù)據(jù)透視表】 2. 在彈出的【創(chuàng)建數(shù)據(jù)透視表】對話框中選取數(shù)據(jù)表的范圍，一般情況下，Excel會自動識別數(shù)據(jù)區(qū)域范圍，如不需要修改，單擊確定完成創(chuàng)建。 3. 完成上述操作后，會在新的空白sheet中形成數(shù)據(jù)透視表工作區(qū)。數(shù)據(jù)透視表字段列表面板由兩部分組成，上半部分顯示了數(shù)據(jù)源表中的所有字段，也就是列標(biāo)題，下半部分則是由【數(shù)據(jù)報(bào)表】（篩選維度）、【列標(biāo)簽】、【行標(biāo)簽】、【數(shù)值】（具體統(tǒng)計(jì)數(shù)據(jù)）這四個窗口組成的矩陣。 數(shù)據(jù)透視表工作區(qū) 4. 根據(jù)分類、統(tǒng)計(jì)的需要，我們可以在字段列表中選定該字段并按住鼠標(biāo)左鍵拖放到下方的矩陣窗口中，完成數(shù)據(jù)透視表布局，并進(jìn)行各種數(shù)據(jù)統(tǒng)計(jì)。
除了常見的求和 和 計(jì)數(shù)（上例就是采用計(jì)數(shù)的方式），透視表也允許使用平均值、最大值/最小值、標(biāo)準(zhǔn)差、方差 等統(tǒng)計(jì)方式。 操作方式簡單：在數(shù)值區(qū)域中單擊鼠標(biāo)右鍵，在【值匯總依據(jù)】中選擇即可。 數(shù)據(jù)透視表的多種統(tǒng)計(jì)方式
如下圖中，我們使用數(shù)據(jù)透視表對不同日期下員工的休假情況進(jìn)行了統(tǒng)計(jì)。如果我們想按照每月來統(tǒng)計(jì)查看休假情況，應(yīng)該如何操作呢？ 創(chuàng)建組使用演示 具體操作方法：在某個日期上單擊鼠標(biāo)右鍵—在右鍵菜單中選擇【創(chuàng)建組】命令，進(jìn)入【組合】對話框。設(shè)置“起始于”和“終止于”對應(yīng)的數(shù)值，一般Excel會自動獲取。設(shè)置步長為“月”，單擊確定。之后我們可以通過數(shù)據(jù)透視表選項(xiàng)調(diào)整布局，使得閱讀更加方便。 同樣，這個我們也可以用于季度、年齡分段、工資分段等的統(tǒng)計(jì)。
數(shù)據(jù)透視表切片器是一種什么樣的存在呢？我們先通過動畫演示感受一下 切片器使用演示 在這里，我們通過數(shù)據(jù)透視表的切片器工具實(shí)現(xiàn)了數(shù)據(jù)之間的聯(lián)動，在本例中，通過切片器，我們可以輕松地查看什么人在什么休假類型上休了多少天 具體插入切片器的方法：選中數(shù)據(jù)透視表任意單元格—數(shù)據(jù)透視表選項(xiàng)——插入切片器 今天就到這里了 也請大家關(guān)注文集Excel常用技巧，與大家一起共同成長學(xué)習(xí)。 如果你覺得不錯或者有用，希望大家能點(diǎn)個喜歡?，歡迎打賞~~ 著作權(quán)歸作者所有 |
|
|
