自制Excel分欄模板

高大慶

對于只有寥寥幾列、寬度很小的Excel工作表，直接打印既不美觀又浪費紙，可惜Excel沒有分欄功能。利用公式設(shè)計一個分欄模板，分欄就方便了，設(shè)計步驟如下：

①新建工作簿，重命名三個工作表為“分欄數(shù)據(jù)”、“分欄參數(shù)”、“分欄結(jié)果”。

②設(shè)計輸入提示。如圖1，選中“分欄參數(shù)”表，在A1～A5單元格依次輸入“請輸入”、“頂端標題行數(shù)：”、“每頁數(shù)據(jù)行數(shù)：”、“分欄數(shù)：”、“欄間距：”，在B2～B5單元格依次輸入“1”、“46”、“2”、“1”，合并A1～B1單元格。

③計算“分欄數(shù)據(jù)”表的總列數(shù)和總行數(shù)。如圖1，在D1～D3單元格依次輸入“無需輸入”、“原表列數(shù)加1”、“原表行數(shù)”，合并D1～E1單元格。在E2單元格輸入公式“=SUMPRODUCT（MAX（（分欄數(shù)據(jù)！$1：$7<>""）*COLUMN（分欄數(shù)據(jù)！$1：$7）））+1”，在E3單元格輸入公式“=SUMPRODUCT（MAX（（分欄數(shù)據(jù)！$A：$C<>""）*ROW（分欄數(shù)據(jù)！$A：$C）））”，對于Excel 2003，此公式應(yīng)改為“=SUMPRODUCT（MAX（（分欄數(shù)據(jù)！$A$1：$C$65535<>""）*ROW（分欄數(shù)據(jù)！$A$1：$C$65535）））”。

④定義名稱使公式直觀、簡潔。選中B2單元格，單擊“公式→定義名稱”，打開新建名稱對話框，如圖2，Excel將為我們把引用位置“=分欄參數(shù)！$B$2”定義為在當前工作簿中的名稱“頂端標題行數(shù)”，直接確定。用同樣方法，把B3、B4、B5、E2、E3單元格也分別定義為名稱“每頁數(shù)據(jù)行數(shù)”、“分欄數(shù)”、“欄間距”、“原表列數(shù)加1”、“原表行數(shù)”。

⑤計算“分欄結(jié)果”表中的每個單元格引用的是“分欄數(shù)據(jù)”表中第幾行和第幾列的數(shù)據(jù)。選中“分欄結(jié)果”表，同上，打開新建名稱對話框，如圖2，在“名稱”中輸入“原表行號”，在“引用位置”中輸入“=INT（（ROW（）-頂端標題行數(shù)-1）/每頁數(shù)據(jù)行數(shù)）*每頁數(shù)據(jù)行數(shù)*分欄數(shù)+INT（（COLUMN（）-1）/原表列數(shù)加1）*每頁數(shù)據(jù)行數(shù)+MOD（ROW（）-頂端標題行數(shù)-1，每頁數(shù)據(jù)行數(shù)）+1+頂端標題行數(shù)”，確定。同樣，定義名稱“原表列號”的引用位置為“=MOD（COLUMN（）-1，原表列數(shù)加1）+1”。

⑥計算“分欄結(jié)果”表中每個單元格數(shù)值并定義分欄公式。同上，定義名稱“單元格數(shù)值”的引用位置為“=OFFSET（分欄數(shù)據(jù)！$A$1，IF（ROW（）<=頂端標題行數(shù)，ROW（），原表行號）-1，原表列號-1）”，定義名稱“分欄公式”的引用位置為“=IF（COLUMN（）>=分欄數(shù)*原表列數(shù)加1，""，IF（MOD（COLUMN（），原表列數(shù)加1）=0，REPT（" "，欄間距），IF（單元格數(shù)值=""，""，單元格數(shù)值）））”，定義名稱“flgs”的引用位置為“=分欄公式”。

⑦設(shè)置頂端標題行和打印區(qū)域。同上，定義名稱“Print_Titles”的引用位置為“=IF（頂端標題行數(shù)=0，""，OFFSET（分欄結(jié)果！$1：$1，，，頂端標題行數(shù)））”，定義名稱“打印區(qū)域”的引用位置為“=OFFSET（分欄結(jié)果！$A$1，，，頂端標題行數(shù)+CEILING（（原表行數(shù)-頂端標題行數(shù)）/每頁數(shù)據(jù)行數(shù)/分欄數(shù)，1）*每頁數(shù)據(jù)行數(shù)，原表列數(shù)加1*分欄數(shù)-1）”，定義名稱“dyqy”和“Print_Area”的引用位置為“=打印區(qū)域”。在A1單元格中輸入“=flgs”，確定后再選中A1單元格。

⑧保存為模板。選中“分欄數(shù)據(jù)”表，在A1單元格中輸入“粘貼數(shù)據(jù)于此”，單擊“文件→另存為”，彈出“另存為”對話框，輸入文件名為“分欄模板”，選擇保存類型為“Excel模板（*.xltx）”，保存，關(guān)閉文件，收工。

本模板用法如下：

①打開包含分欄數(shù)據(jù)的工作簿，選中需要分欄的數(shù)據(jù)，復(fù)制。單擊“文件→新建→我的模板”，單擊“分欄模板.xltx”，確定后即可打開本模板，右擊“分欄數(shù)據(jù)”表的A1單元格，在彈出的菜單中點“粘貼”。

②選中“分欄參數(shù)”表，輸入各分欄參數(shù)。

③選中“分欄結(jié)果”表，如圖3，單擊名稱框，輸入“dyqy”或“打印區(qū)域”，回車后即可選定分欄結(jié)果的打印區(qū)域，再單擊編輯欄，看到公式“=flgs”，按組合鍵“Ctrl+Enter”向該區(qū)域輸入公式（不能單按回車）。

④設(shè)置好“分欄結(jié)果”表的格式，然后全選，再雙擊任意兩個列標之間的分隔線，調(diào)整全部列的寬度為自動列寬。

⑤若不要求每頁數(shù)據(jù)行數(shù)為某一固定值，則拖動垂直滾動條查看每頁實際行數(shù)，即自動分頁符所在行號（設(shè)為η），圖3中為“47”，選中“分欄參數(shù)”表，輸入“每頁數(shù)據(jù)行數(shù)”的值為η與“頂端標題行數(shù)”之差，并完成第③步，分欄結(jié)束;否則，在“分欄結(jié)果”表中反復(fù)調(diào)整行高，直到η正好等于“分欄參數(shù)”表中“頂端標題行數(shù)”與“每頁數(shù)據(jù)行數(shù)”之和，分欄結(jié)束。

調(diào)整行高的方法：首先全選或只選中數(shù)據(jù)行，向上（或向下）拖動其中任意兩個行號之間的分隔線，可減?。ɑ蛟龃螅┬懈撸敠墙咏繕酥担础胺謾趨?shù)”表中“頂端標題行數(shù)”與“每頁數(shù)據(jù)行數(shù)”之和）時，右擊其中任一行號，在彈出菜單中點“行高”，可查看行高值并以0.25的倍數(shù)增減來精確微調(diào)。

注意：全部數(shù)據(jù)行必需為同一高度，才能保證所有頁的行數(shù)相同，而頂端標題行的行高可任意設(shè)置。

顯示分頁符的幾種方法：①打印預(yù)覽;②單擊“視圖→頁面布局”;③單擊“文件→選項→高級”，在“此工作表的顯示選項”中勾選“顯示分頁符”;④單擊“視圖→分頁預(yù)覽”，若字號太小，再單擊“視圖→顯示比例”設(shè)置合適的顯示比例。

當“分欄參數(shù)”表中的“每頁數(shù)據(jù)行數(shù)”為1時，可橫向排列數(shù)據(jù)。

本模板下載地址為：（請?zhí)峁?，下載后打開，另存為模板即可。75292030-02C6-4668-BB2C-AE1672F03C37