序言操作描述盡量詳細,但需要一點點基礎,excel純新人可能會看不明白。 本系列既是寫給自己,也是送給大家的小福利,避免遺忘知識點后到處百度。本系列大部分參考都會是Excel幫助文檔,例子都是自己編寫,結(jié)合實際,盡量簡化。 常見需求有時候,會有 需要匯總的數(shù)據(jù),分散在兩張表格 的情況。 如果兩張表,都有某種列,比如都是唯一性數(shù)據(jù)(比如工號,身份證號),用 VLOOKUP 函數(shù),來合并表格是個方便的選擇。 VLOOKUP函數(shù)能把關鍵詞作為索引來查找數(shù)據(jù)。它是一個查找和引用函數(shù)。 語法 VLOOKUP ( lookup_value , table_array , col_index_num , [range_lookup])▼
公式舉例 = VLOOKUP('王炸',B2:C7,2,FALSE) 說明:在 B2:C7 這個區(qū)域內(nèi),查找 “王炸” 這個數(shù)據(jù),如果有,就定位 “王炸” 所在行,從 B 列 開始往右數(shù),第二列的數(shù)據(jù)。FALSE 代表可以精確匹配,比如“王炸”可以,但是“王炸之狗”就算另一個數(shù)據(jù)。 場景圖例▼ 說明:1,輸入公式時,不要忘記輸入 等于號“=”。 2,注意,在單元格輸入公式,必須是英文輸入法,特別注意標點符號一定是半角字符,輸入全角或者中文標點可能會出錯。只有引號內(nèi)的數(shù)據(jù)可以是中文,比如“王炸”、“狗腿”之類。 3,請一定要使用 FALSE 精確匹配模式,使用 TRUE 近似匹配會有意想不到的錯誤。哪怕是純數(shù)字數(shù)據(jù)。 4,如果匹配不到數(shù)據(jù),會顯示 #N/A 。不要怕,這是正常現(xiàn)象, 。為了顯示的更直觀,可以人為改進,比如: = IFERROR( VLOOKUP ( '王炸', B2:C7, 2, FALSE), '未找到王炸')。這時候,如果選區(qū)內(nèi)找不到“王炸”,單元格就會顯示'未找到王炸'。 實際操作(簡化場景)▼題目:因為業(yè)務需要,組織宣布重大任務,需要根據(jù)表1和表2,得到 表三——顏值銷量關系表(表頭如下)。▼ 就是說要把顏值和銷量放在一張表里,才方便分析顏值和銷量的關系。 我們假設有個員工叫小李,由他來操作。 小李發(fā)現(xiàn),表三需要的信息,分散在表1和表2里面。表1缺少了銷量,多了地址,表2 缺的比較多。所以機智的小李選擇改造表1,來獲得表三。 步驟:1,表1和表2,都在sheet1中。小李復制表1,粘貼到sheet2中,去掉了不需要的地址列,添加了銷量列。▼ 2,小李觀察到,工號是兩張表共有的數(shù)據(jù),且和身份證一樣,擁有唯一性,于是就使用工號作為索引。小李在sheet2的 E3 單元格中輸入 = VLOOKUP ( C3 , Sheet1!B11:C14 , 2 , FALSE)▼
3,小李拉了下,獲得了全部數(shù)據(jù)。▼ 4,這個 #N/A 有點丑,沒銷量就是0嘍,小李改進了下公式 =IFERROR(VLOOKUP(C3,Sheet1!B11:C14,2,FALSE),0)▼ 5,小李使用了數(shù)據(jù)透視▼ 6,小李得意的將報告交給了領導,然而被領導甩了一臉:“小李,人越丑越努力,他們的努力取得了成功,獲得了銷量。你的顏值是0.1,為什么銷量是0呢?!” 好了,小李比較委屈,那我們換個場景,順便初步了解一下宏和VBA的用法。 VBA——更自由的操作小李感到委屈,換工作到了大企業(yè),人比較多,大概一百萬人吧。 可天有不測風云,領導提出了類似的任務(連表格格式都一樣),數(shù)據(jù)量大,數(shù)萬行,寫好公式后,用拖拽產(chǎn)生數(shù)據(jù),也要累死人的,手工輸入幾乎就是不可能了。 正在煩惱的小李睡著了,被成龍托夢,想起了小霸王,哦不,是VBA(小霸王學習機內(nèi)置QBASIC編輯器)。 于是小李開始了操作。
Excel 2016VBA功能開啟步驟:▼表格操作步驟:模擬表格,5000行(要十萬行也是可以的,但是作為例子也沒啥意義)。所要做的工作是合并兩張表:▼ 好了我們通過操作(略),有了5000行“顏值表”、“銷量表”的原始數(shù)據(jù)表格了(數(shù)據(jù)生成見附錄部分)
下面開始VBA吧 1,新建一頁,做個表頭▼ 2,打開VBA編輯界面▼ 3,右鍵模塊,選擇插入模塊▼ 4,修改模塊名稱▼ 5,輸入代碼▼ 6,回到當前“匯總表”sheet,執(zhí)行宏“整理合并”▼ 小李按下了回車鍵,得到結(jié)果,舒適的擺了個姿勢,看小電影去了: ▼ 【正文完】 附錄,5000行數(shù)據(jù)隨機生成的VBA預備工作,新建兩張表,寫好列▼一、對于顏值表,我們采用錄制宏,并編輯宏的方法簡便生成5000行數(shù)據(jù),步驟如下——作為VBA入門,這里簡化VBA的編程,對不熟悉VBA的人比較友好。 1,點擊錄制宏。▼ 2,填寫工號初始數(shù)字▼ 3,顏值用隨機函數(shù)確定▼ B3單元格輸入 = RANDBETWEEN(1,10) 4,拖拽一下,形成復制。不用多,拖個兩三行就行了▼
5,點擊關閉錄制宏。開始編輯宏。▼ 6,找到錄制的宏,VBA代碼,修改一下▼ 1),把 Selection.AutoFill Destination:=Range('A3:A7'), Type:=xlFillDefault。其中的'A3:A7',改成'A3:A5002' 2)把 Selection.AutoFill Destination:=Range('B3:B7'), Type:=xlFillDefault。其中的'B3:B7',改成'B3:B5002' 然后,ctrl+s保存。▼ 可能會提示要保存成可以執(zhí)行宏的格式,看自己情況吧。如果不保存VBA,可以直接點是,如果想保存這個宏,以后用于別的文件,可以點擊否,然后在excel里另存為宏格式的文件。 7,回到當前VBA顏值表sheets執(zhí)行▼ 8,查看結(jié)果,工號已到105000,正好5000人。▼ 9,注意,上面生成的隨機數(shù),會隨著修改文件而不斷變化的缺陷(特性、features),之所以這么操作,是為了讓大家看一看,最簡單的VBA,就是通過錄制宏,然后按需簡單修改而成的。如果想要更好一點的方式,可以改成下面這種,就不會“數(shù)據(jù)閃來閃去”了。▼ 二、對于銷量表,我們來直接用VBA編寫方法1 代碼輸入▼ 執(zhí)行結(jié)果,也得到了5000組數(shù)據(jù)▼ 會發(fā)現(xiàn),照抄宏錄制的代碼,大腦放空,隨意編寫,執(zhí)行速度會很慢,有種窒息的感覺,但是也算能用吧。 方法2 于是我們可以稍微改進下。速度快一點,主要是沒那么卡了,代碼如下:▼
|
|
來自: 老來樂64 > 《3.2.4 VLOOKUP函數(shù)》