散文網(wǎng) » 筆記 »全部筆記 » 3天的工作1秒完成！ 20萬行料號BOM表經(jīng)典Excel VBA字典行號法|VBA永遠的神

3天的工作1秒完成！ 20萬行料號BOM表經(jīng)典Excel VBA字典行號法|VBA永遠的神

2022-11-27 19:03 作者:Excel880實例教學(xué)網(wǎng) 0人讀過 | 我要投稿

需求提出

我在一個小工廠里上班，日常工作是做生產(chǎn)資料的BOM管理，經(jīng)常遇見BOM按料號去重，再查找父項的問題，今天我們來解決這個，即根據(jù)查詢的料號的標識，來查詢出對應(yīng)父項描述，父項。

這個數(shù)據(jù)量有20萬行，之前用人工做的時候，函數(shù)公式加手工操作，用盡所有力氣做一次就要兩三天，痛定思痛，努力學(xué)了VBA后，使用VBA代碼編寫完成本任務(wù)，每次運行3秒完成，本文案例中我用1萬6前行的數(shù)據(jù)來測試，vba代碼寫完后每次一秒之內(nèi)就完成統(tǒng)計，徹底解放了雙手，當然有人會說，老板肯定要安排你更多工作，我顯然沒那么傻啊，我還是3天才交任務(wù)，自己省下來的時間去學(xué)習充電，努力考證！記住千萬不要做的快就馬上上交結(jié)果！那是傻子干的事！

由于本文圖片尺寸較大，請大家盡量收藏轉(zhuǎn)發(fā)后，到電腦端大屏查看學(xué)習，效果更佳！

解題思路

由于數(shù)據(jù)比較大，這次我們使用鄭老師教的萬能字典行號法，使用VBA的行號定位+數(shù)組循環(huán)來完成，下面就開始我們愉快的學(xué)習旅程！

對源數(shù)據(jù)循環(huán)，VBA將相同料號的行號裝入字典，行號用逗號分隔記錄
循環(huán)字典中的料號關(guān)鍵字取出對應(yīng)的行號
按逗號拆分行號得到行號數(shù)組
循環(huán)行號數(shù)組，從源數(shù)據(jù)依次取出右側(cè)多列父項
結(jié)果放入結(jié)果數(shù)組，寫入結(jié)果區(qū)

前置知識

本案例屬于實戰(zhàn)案例，零基礎(chǔ)有點難，學(xué)習本案例需要至少有以下VBA基礎(chǔ)

VBA數(shù)組基礎(chǔ)
根據(jù)表格有效區(qū)域獲取數(shù)組
VBA字典基本知識
VBA字典行號法基礎(chǔ)

以上知識點，本人均在鄭廣學(xué)VBA175例教程中學(xué)習

鄭廣學(xué)VBA175例教程鏈接

解題詳細步驟

1 、定義變量，初始化VBA字典

首先需要定義一些變量和字典對象，和大家一樣，最開始這個字典對象很長，總是記不住，不過還好有工具可以幫助我們快速輸入，安裝VBA代碼助手插件后，在第25行，只需要輸入 dicd四個字母，然后按下空格，就會得到輸入提示，按下Tab鍵即直接輸入字典的定義，即使是第一天學(xué)vba的同學(xué)，也不怕記不住代碼了！

2、循環(huán)遍歷源數(shù)據(jù)數(shù)組，使用VBA字典按關(guān)鍵字記錄行號

第29行到36行，將arr數(shù)組中的第13的料號作為字典的關(guān)鍵字key,

并在循環(huán)中將料號對應(yīng)的行號連接并用分隔符隔開，存入字典。

循環(huán)完畢我們就將料號和他對應(yīng)的行號存入字典dic中，為了方便下面取出結(jié)果，

在第37-38行取出字典的keys,items數(shù)組

3 、寫入結(jié)果數(shù)組，輸出數(shù)組到結(jié)果表

定義一個結(jié)果數(shù)組brr ，循環(huán)對應(yīng)的料號數(shù)組keys，

分割取出對應(yīng)料號的原數(shù)據(jù)所在的行號，再從原數(shù)據(jù)數(shù)組arr 中

結(jié)果數(shù)組brr的第一列放料號

結(jié)果數(shù)組brr的第二列父項描述

結(jié)果數(shù)組brr的第三列父項

注意在放結(jié)果數(shù)組的時候，列數(shù)根據(jù)父項個數(shù) 動態(tài)重新定義結(jié)果數(shù)組brr的第二維數(shù)

最后輸出結(jié)果數(shù)組brr到sheet2中

以上知識點在 VBA175例 第194節(jié)多列求和通用解法 記錄行號二次循環(huán)法深入講解 詳細講解