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