“抄近道”Excel工作表批量操作

俞木發(fā)

數(shù)據(jù)透視表 快速建立多個(gè)工作表

如上所述，如果要建立類似1→12月份多個(gè)工作表，現(xiàn)在可以借助數(shù)據(jù)透視表的方法快速實(shí)現(xiàn)。啟動(dòng)Excel后在A列輸入工作表的名稱文字，這里可以根據(jù)自己需要，輸入任意名稱作為新工作表的名稱，如省份、子公司名稱，產(chǎn)品名稱等。選中A1：A13數(shù)據(jù)，點(diǎn)擊“插入→數(shù)據(jù)透視表”，在打開的窗口，選擇放置數(shù)據(jù)透視表的位置設(shè)置為C1，點(diǎn)擊“確定”（圖1）。

在右側(cè)數(shù)據(jù)透視表字段設(shè)置窗格，按提示將“月份”拖到下方的“篩選”字段位置，這樣在C1、D1會(huì)出現(xiàn)月份全部字樣（圖2）。

點(diǎn)擊“數(shù)據(jù)透視表工具→分析→數(shù)據(jù)透視表→顯示→數(shù)據(jù)透視表報(bào)表篩選頁”，在彈出的窗口點(diǎn)擊“是”，這樣在當(dāng)前工作簿會(huì)生成12個(gè)月份命名的工作表。選中任一新建工作表，選中A1、B1內(nèi)容，繼續(xù)選中“1月”工作表，右擊選擇“選擇全部工作表”，點(diǎn)擊“開始→清除→全部清除”，這樣就可以快速新建12個(gè)內(nèi)容空白的工作表了（圖3）。

VBA自動(dòng)導(dǎo)出多個(gè)工作表

上述方法是在一個(gè)工作簿建立多個(gè)工作表，如果需要將包含多個(gè)工作表的工作簿批量導(dǎo)出為多個(gè)獨(dú)立工作簿文件。比如將上例工作簿按照月份導(dǎo)出為12個(gè)獨(dú)立月份工作簿文件，現(xiàn)在可以借助VBA或者第三方軟件快速實(shí)現(xiàn)。

下載所需的代碼文件，接著啟動(dòng)Excel打開包含多個(gè)工作表的文件，按住Alt+F11鍵，然后打開Microsoft Visual Basic for Applications窗口。點(diǎn)擊“插入→模塊”，將下載的代碼粘貼到文本框（圖4）。

點(diǎn)擊“運(yùn)行→運(yùn)行子過程（窗體）”，這樣VBA腳本會(huì)自動(dòng)將每個(gè)工作表保存在和當(dāng)前工作簿同名的目錄下，每個(gè)月份一個(gè)工作簿文件（圖5）。

如果你對VBA代碼不熟悉，也可以到https：// w w w.cdn.ex tendof f ice.com/downloads/ KutoolsforExcelSetup.exe下載Kutools for Excel實(shí)現(xiàn)快速導(dǎo)出。安裝完Kutools for Excel后啟動(dòng)Excel打開包含多個(gè)工作表的文件，點(diǎn)擊“Kutools→分割工作簿”，在打開的窗口（可以選擇忽略工作表和隱藏工作表）也可以選擇需要導(dǎo)出為工作簿的工作表文件（圖6）。

接著選擇保存工作簿的目錄，點(diǎn)擊“確定”后可以將所有工作表導(dǎo)出為工作簿文件。

一次輸入 批量編輯工作表

對于保存多張工作表的工作簿，很多工作表都會(huì)輸入一樣的內(nèi)容。比如對于年度盤點(diǎn)表，每張表的表頭都要設(shè)置為合并單元格，并且輸入同樣的內(nèi)容（只是月份不同）。對于這些統(tǒng)一表頭內(nèi)容的輸入，現(xiàn)在可以實(shí)現(xiàn)一次輸入。

點(diǎn)擊第一個(gè)工作表標(biāo)簽，右擊選擇“選定全部工作表”（如果是不連續(xù)的工作表，則通過按Ctrl鍵依次選擇）。選中需要編輯的工作表后，在第一個(gè)工作表執(zhí)行單元格合并，并且輸入統(tǒng)一的表頭內(nèi)容（圖7）。

當(dāng)然還可以在表1設(shè)置文本格式，輸入自定義公式，設(shè)置單元格的格式（比如特殊數(shù)據(jù)如身份證、銀行卡號(hào)等需要設(shè)置為文本格式）。這樣輸入完成后在所有工作表都會(huì)實(shí)現(xiàn)同一內(nèi)容的輸入和設(shè)置，省去每個(gè)工作表的一一設(shè)置（圖8）。

更多批量處理技巧

除了上述介紹的批量操作技巧外，在實(shí)際工作中，我們還可以根據(jù)不同情況進(jìn)行更多的批處理操作。比如上述年度報(bào)表，假設(shè)我們要對1～12月進(jìn)行全年匯總，那么我們只需要在匯總表里面輸入“=sum（*！A1）”，其中的通配符*號(hào)就代表除當(dāng)前工作表以外的其他所有工作表，輸入后公式自動(dòng)變?yōu)椤皊um（1月：12月！A1）”，表示對1月到12月A1單元格的求和統(tǒng)計(jì)（圖9）。

Excel自帶的查找和替換也非常方便對所有工作表的同一元素進(jìn)行批量處理。例如要將工作表中的負(fù)數(shù)變?yōu)?（比如考勤表中出現(xiàn)太多曠工后會(huì)出現(xiàn)負(fù)數(shù)出勤，現(xiàn)在需要變?yōu)?），查找內(nèi)容為“-*”，這個(gè)代表負(fù)數(shù)跟后面所有內(nèi)容，替換內(nèi)容為“0”，替換范圍選擇工作簿，單擊全部替換按鈕即可。