Excel2007技巧

1、Excel2007技巧2009年04月10日 星期五 13:57一、用Excel 2007制作能互動的函數(shù)圖像在Excel 2007中畫出一個函數(shù)圖像并不難。但是，Excel 2007的功能絕不僅僅如此，稍加改造，我們就可以利用Excel 2007制作出有交互功能的Excel函數(shù)圖像，利用滾動條來研究函數(shù)。 第一步：制作圖像我們來制作一個簡單的一次函數(shù)y=kx+b的圖像。在Excel 2007中，在A1單元格中輸入“x”。在A2:A4單元格區(qū)域中分別輸入“-10”、“-9.5”、“-9”，選中這三個單元格，拖動填充句柄向下至A42單元格。這樣，可以得到從-10到10的數(shù)據(jù)，間隔為0.5。在B1

2、單元格中輸入“y”，在C1、D1單元格中分別輸入“k”、“b”。在B2單元格中輸入公式“=A2*$C$2+$D$2”并回車。選中B2單元格拖動其填充句柄向下至B42單元格復(fù)制公式。在C2、D2單元格中分別填入數(shù)字“2”、“3”，如圖1所示。 輸入公式選中A1:B42單元格，點擊功能區(qū)“插入”選項卡“圖表”功能組中“散點圖”按鈕下的小三角形，再打開的菜單中點擊“帶平滑線的散點圖”，如圖2所示。就可以在當(dāng)前窗口中得到函數(shù)y=2x+3的圖像了，如圖3所示。 插入函數(shù)圖像此時，如果我們改變C2或D2單元格的數(shù)值，那么函數(shù)的圖像也會相應(yīng)地發(fā)生改變。但是，圖表中坐標(biāo)軸也會跟著發(fā)生變化，這并不符合我們的習(xí)慣

3、。而且，C2或D2單元格的數(shù)值改變?nèi)绻煌ㄟ^輸入的方式，那也顯得很麻煩。所以，我們還必須進(jìn)行下面兩步操作。第二步：固定坐標(biāo)軸選中圖表區(qū)，此時在功能區(qū)會新增加一個“圖表工具”選項卡集。點擊其中的“布局”選項卡 “坐標(biāo)軸”功能組中“坐標(biāo)軸”按鈕下小三角形，在彈出的菜單中選擇“主要橫坐標(biāo)軸其他主要橫坐標(biāo)軸選項”命令，如圖4所示。 選項在打開的“設(shè)置坐標(biāo)軸格式”對話框中，點擊左側(cè)的“坐標(biāo)軸選項”項目，然后將右側(cè)的“最小值”、“最大值”項目都設(shè)置為“固定”，并在其后的輸入框中分別輸入“-10”、“10”。如圖5所示。 選項用同樣的方法設(shè)置主要縱坐標(biāo)軸?？蓪ⅰ白钚≈怠焙汀白畲笾怠狈謩e設(shè)置為“-50”、“5

4、0”。這樣，坐標(biāo)軸就算固定好了。第三步：添加滾動條現(xiàn)在，我們應(yīng)該為C2單元格數(shù)據(jù)指定一個滾動條，并用滾動條來調(diào)整C2單元格數(shù)據(jù)。在Excel 2007中，我們需要首先找到添加滾動條的命令。點擊左上角的“自定義快速訪問工具欄”右側(cè)的小三角型按鈕，在彈出的快捷菜單中選擇“其他命令”命令，如圖6所示。在打開的“Excel選項”對話框中，確認(rèn)左側(cè)列表中選中“自定義”項目，在中間“從下列位置選擇命令”下拉列表中選擇“不在功能區(qū)中的命令”，然后在下方的列表中選中“滾動條(窗體控件)”，點擊“添加”按鈕，將其添加到“自定義快速訪問工具欄” 中。如圖7所示。 選項關(guān)閉對話框，點擊左上角“自定義快速訪問工具欄”

5、上新添加的“滾動條”按鈕，當(dāng)鼠標(biāo)指針變?yōu)槭中螘r，在工作表中窗口中點擊產(chǎn)生一個滾動條。在滾動條上右擊鼠標(biāo)，在彈出的快捷菜單中選擇“設(shè)置控件格式”命令，打開“設(shè)置控件格式”對話框。點擊“控制”選項卡，在“單元格鏈接”右側(cè)的輸入框中輸入“$C$2”，指定其與C2單元格數(shù)據(jù)鏈接，如圖8所示。我們還可以在這個選項卡中指定滾動條的最大值和最小值。 設(shè)置對象格式同樣的方法為D2單元格設(shè)置一個滾動條，并做好鏈接。好了，現(xiàn)在拖動滾動條，函數(shù)的圖像就會發(fā)生相應(yīng)的變化。我們自然就可以由此來發(fā)現(xiàn)函數(shù)圖像與參數(shù)之間的關(guān)系。二、Excel 2007查詢操作中的函數(shù)應(yīng)用在Excel中，我們經(jīng)常會需要從某些工作表中查詢有關(guān)

6、的數(shù)據(jù)復(fù)制到另一個工作表中。比如我們需要把學(xué)生幾次考試成績從不同的工作表中匯總到一個新的工作表中，而這幾個工作表中的參考人數(shù)及排列順序是不完全相同的，并不能直接復(fù)制粘貼。此時，如果使用Excel的VLOOKUP、INDEX或者OFFSET函數(shù)就可以使這個問題變得非常簡單。我們以Excel 2007為例。 圖1 假定各成績工作表如圖1所示。B列為姓名，需要匯總的項目“總分”及“名次”位于H列和I列（即從B列開始的第7列和第8列）。而匯總表則如圖2所示，A列為姓名列，C、D兩列分別為要匯總過來的第一次考試成績的總分和名次。其它各次成績依次向后排列。圖2 一、VLOOKUP函數(shù)我們可以在“綜合”工作

7、表的C3單元格輸入公式“=VLOOKUP($B3,第1次!$B$1I$92,7,FALSE)”，回車后就可以將第一位同學(xué)第一次考試的總分匯總過來了。把C3單元格公式復(fù)制到D3單元格，并將公式中第三個參數(shù)“7”改成“8”，回車后，就可以得到該同學(xué)第一次考試名次。選中C33這兩個單元格，向下拖動填充句柄到最后就可以得到全部同學(xué)的總分及名次了。是不是很簡單呀？如圖3所示。VLOOKUP函數(shù)的用法是這樣的：VLOOKUP(參數(shù)1,參數(shù)2,參數(shù)3,參數(shù)4)?！皡?shù)1”是“要查找誰？”本例中B3單元格，那就是要查找B3單元格中顯示的人名?！皡?shù)2”是“在哪里查找？”本例中“第1次!$B$1I$92”就是告

8、訴Excel在“第1次”工作表的B1:I92單元格區(qū)域進(jìn)行查找。“參數(shù)3”是“找第幾列的數(shù)據(jù)？”本例中的“7”就是指從“第1次”工作表的B列開始起，第7列的數(shù)據(jù)，即H列。本例中“參數(shù)4”即“FALSE”是指查詢方式為只查詢精確匹配值。該公式先在“第1次”工作表的B!:I92單元格區(qū)域的第一列（即B1:B92單元格區(qū)域）查找B3單元格數(shù)據(jù)，找到后，返回該數(shù)據(jù)所在行從B列起第7列（H列）的數(shù)據(jù)。所以，將參數(shù)3改成“8”以后，則可以返回I列的數(shù)據(jù)。由此可以看出，使用VLOOKUP函數(shù)時，參數(shù)1的數(shù)據(jù)必須在參數(shù)2區(qū)域的第一列中。否則是不可以查找的。二、INDEX函數(shù)某些情況下，VLOOKUP函數(shù)可能會

9、無用武之地，如圖4所示?！熬C合”工作表中，姓名列放到了A列，而B列要求返回該同學(xué)所在的班級。但我們看前面的工作表就知道了，“班級”列是位于“姓名”列前面的。所以，此時我們不可能使用VLOOKUP函數(shù)來查找該同學(xué)的班級。而INDEX函數(shù)就正可以一試身手。圖4 在B3單元格輸入公式“=INDEX(第1次!$A$1I$92,MATCH(A3,第1次!$B$1B$92,0),1)”，回車并向下復(fù)制公式就可以了，如圖5所示。圖5 這里用到了兩個函數(shù)，INDEX和MATCH。先說說這個MATCH(A3,第1次!$B$1B$92,0)。它的意思是在“第1次”工作表的B1:B92單元格區(qū)域中查找A3單元格數(shù)據(jù)

10、，然后返回該數(shù)據(jù)在B1:B92單元格區(qū)域中的行數(shù)。以本例而言，該公式返回的結(jié)果是“2”。這一點，只要看一下圖1所示工作表就清楚了。至于該公式中最后一個參數(shù)“0”是要求精確匹配，而且使用該參數(shù)時，B1:B92單元格區(qū)域不需要排序。再來看這個INDEX函數(shù)。它的用法是INDEX(參數(shù)1,參數(shù)2,參數(shù)3)。參數(shù)1是要查找的區(qū)域。參數(shù)2是行數(shù)，參數(shù)3是列數(shù)。比如公式INDEX(A2:C6,2,3)的意思就是要返回在A2:C6這個區(qū)域中第二行第三列的數(shù)據(jù)，即C3單元格的數(shù)據(jù)。所以，本例中公式的意思就是返回“第1次”工作表A1:I92單元格區(qū)域中第二行第一列的數(shù)據(jù)，呵呵，那不正是該同學(xué)所在的班級嘛！與VL

11、OOKUP函數(shù)相比較，INDEX函數(shù)的“限制”要少一些。三、OFFSET函數(shù)其實，我們還可以使用OFFSET函數(shù)完成這個任務(wù)的。比如我們要查A3單元格同學(xué)在第二次考試中的總分。那么只需要點擊E3單元格，并輸入公式“=OFFSET(第2次!$A$1,MATCH($A3,第2次!$B$1B$92,0)-1,7,1,1)”，回車后并向下復(fù)制公式，就可以得到所需要的數(shù)據(jù)了。如圖6所示。圖6 OFFSET函數(shù)是以指定的引用為參照系，通過給定偏移量得到新的引用，比如公式“=OFFSET(C3,2,3,1,1)”，它的意思就是返回以C3單元格為基準(zhǔn)，向下偏移2個單元格，向右偏移3個單元格處的單元格數(shù)據(jù)，即F

12、5單元格的數(shù)據(jù)，數(shù)一下就清楚了。所以本例公式中先由“MATCH($A3,第2次!$B$1B$92,0)”得到“第2次”工作表B1:B92單元格中與A3單元格相匹配的數(shù)據(jù)所在行數(shù)（本例結(jié)果為2），再減去1正好是它相對于“第2次”工作表A1單元格的向下偏移的行數(shù)，而后面的參數(shù)“7”，則是該同學(xué)的總分成績所在單元格相對于A1單元格的向右偏移量。因此，本例中在E3單元格的公式其實就是“=OFFSET(第2次!$A$1,1,7,1,1)”，其意思就是返回“第2次”工作表中相對于A1單元格下移一行右移7行處單元格數(shù)據(jù)。好了，這三個查找引用函數(shù)的用法應(yīng)該算說清楚了吧？只要我們認(rèn)真研究它們的用法，相信會有很多

13、妙用的。三、不用公式 讓Excel按人頭打印出工資條使用Excel按人頭打出工資條，有用Word郵件合并功能的，也有用VBA功能的，也有采用編寫公式直接產(chǎn)生的?？蓞⒖刺鞓O軟件辦公欄目的文章（Word、Excel配合按人頭打印工資條、僅需一個公式讓Excel按人頭打出工資條）但我覺得，對于普通公司員工來說，這些方法都顯得專業(yè)性太強。其實，變通一下，也可以不用公式，直接讓Excel按人頭打出工資條的，方法簡單，適合Excel新手使用。假定公司有員工100人，工資數(shù)據(jù)在A2:R101區(qū)域。在工作表的第一行A1:R1區(qū)域為工資項目，如圖1所示。我們需要的工資條是第一行為工資項目，第二行為各員工的工資，

14、第三行為空行，以便我們打印后分割。 圖1第一步：先選中A列，點擊右鍵，在彈出的快捷菜單中點擊“插入”命令，插入一個輔助列。在A2、A3、A4單元格分別輸入數(shù)字“2”、“5”、“8”。選中這三個單元格，拖動填充句柄向下至A101單元格，為這些單元格添加序號。第二步：復(fù)制A1:R1單元格，然后在A102單元格單擊，并粘貼。選中A102:R102單元格，將鼠標(biāo)定位于填充句柄，按下右鍵，向下拖動填充句柄至R200單元格。松開右鍵，在彈出的菜單中選擇“復(fù)制單元格”命令，如圖2所示。這樣就復(fù)制了99個工資項目，加上第一行的那個，正好100個。 圖2第三步：在A1單元格中輸入數(shù)字“1”，在A102、A103

15、、A104單元格分別輸入數(shù)字“4”、“7”、“10”。然后選中A102:A104單元格向下拖動填充句柄，至A200單元格，為所有的工資項目添加序號。第四步：在A201、A202、A203分別輸入數(shù)字“3”、“6”、“9”。然后選中這三個單元格，向下拖動填充句柄至A300單元格。好了，現(xiàn)在您肯定明白了。我們在工資項目、工資數(shù)據(jù)、空行分別添加了相互間隔的序號?，F(xiàn)在，我們只要根據(jù)A列序號，升序進(jìn)行排列，那么就可以得到所需要的工資條了，如圖3所示。 圖3最后要做的，就是選中A列數(shù)據(jù)，點擊右鍵，在彈出的快捷菜單中選擇“隱藏”命令，將該輔助列隱藏起來，如圖4所示。 圖4四、Excel 2003使用技巧 特

16、殊班級排序方法在使用Excel 2003進(jìn)行成績統(tǒng)計時，一般都是把成績按班級錄入并計算出個人總分，然后把所有班級成績粘到一起，再以總分為關(guān)鍵字從高到低排列好，并填充上校名次，然后再按班級順序排列，以便以班級為單位打印輸出成績單。這樣一看成績單，就能知道班級在全校前50、100名有多少學(xué)生，每個學(xué)生在全校的名次等。但是在Excel 2003中卻不認(rèn)識一班、二班、三班的順序，而是以第一個漢字的拼音的第一個字母順序排列，排完的順序是八班、二班，讓人看起來很不舒服。于是便想辦法讓Excel 2003認(rèn)識了一班，二班，三班 1、在L1單元格中輸入1，L2單元格中輸入2，然后拖選上L1和L2單元格，把鼠標(biāo)

17、指針指向L2單元格右下角，等鼠標(biāo)指針變成填充柄（黑色實線加號）時，雙擊左鍵，就自動完成校名次的填加了。（如圖1）圖1     2、單擊“工具”菜單選項，在打開的“選項”對話框中選“自定義序列”選項卡，在“輸入序列”列表框中輸入“一班，二班，三班，四班，五班，六班，七班，八班，九班，十班”，中間用英文逗號分開，再依次點“添加”和“確定”按鈕。（如圖2）圖2     3、單擊“數(shù)據(jù)”菜單排序，在“排序”對話框中，“主要關(guān)鍵字”選“班級”并選擇后面的“升序”，再點“選項”按鈕，打開“排序選項”對話框，接著在“自定義排序次序”列表框中選“一班

18、，二班”，再依次點“確定”按鈕退出，看是不是非常聽話的按一班，二班，三班的順序排列了。（如圖3）圖3五、舉手之勞 實現(xiàn)Excel 2007文檔逆序打印在Word 2007中，只要依次點擊左上角Office徽標(biāo)打印，在打開的“打印”設(shè)置窗口中點擊“選項”按鈕，在接著出現(xiàn)的“Word 選項”窗口中切換到“高級”標(biāo)簽頁，然后勾選“打印”項中的“逆序打印頁面”選項(圖1)，這樣打印機打印時就會從最后一頁開始逆序打印到第一頁，打印完畢后，最后一頁自然處于最底下，而第一頁處于最上面，如此則無須逆序重排文檔，直接就可以裝訂成冊，對于長篇打印文檔尤其方便。 圖1 逆序打印頁面選項與Word相比， Excel的

19、打印功能就相形見絀，Excel 2007中也未見對打印功能有任何改善。但我們只要稍費心思，也能讓Excel 2007輕松擁有“逆序打印”功能。一.打印機屬性巧設(shè)置有些打印機只要安裝了自帶的驅(qū)動程序，就可以在打印屬性中找到類似“逆序打印”的打印選項。以筆者的Canon PIXMA iP1000為例(事先請安裝打印機附帶光盤中的驅(qū)動程序)，打印時，只要打開Excel 2007工作簿，依次點擊左上角Office徽標(biāo)打印，打開“打印”設(shè)置窗口，在打印機名稱中選中“Canon PIXMA iP1000”，然后點擊“屬性”按鈕，調(diào)出“Canon PIXMA iP1000屬性”窗口，切換到“頁設(shè)置”標(biāo)簽頁，

20、勾選“反向”復(fù)選框再點擊“確定”按鈕即可(圖2)。 圖2   頁設(shè)置二. 宏代碼實現(xiàn)法有些打印機不具備上述屬性，但通過宏照樣可以實現(xiàn)逆序打印功能。打開該Excel文件，切換到“視圖”選項卡，點擊“宏”“錄制宏”，出現(xiàn)“錄制新宏”窗口，在“宏名”定義一個名稱為：ReversePrint(圖3)，點擊“確定”退出; 圖3 錄制新宏再點擊“宏”“查看宏”，選擇“宏名”下的“ReversePrint”并點擊“編輯”，打開“Microsoft Visual Basic”編輯器，用如下內(nèi)容替換右側(cè)窗口中的所有代碼(圖4)，然后保存關(guān)閉VBA編輯器： 圖4   打開“

21、Microsoft Visual Basic”編輯器Sub ReversePrint()Dim NumPages As Long, Page As LongNumPages = ExecuteExcel4Macro("GET.DOCUMENT(50)")For Page = NumPages To 1 Step -1ActiveSheet.PrintOut from:=Page, To:=PageNext PageEnd Sub再點擊“宏”“查看宏”，選擇“宏名”下的“ReversePrint”并點擊“執(zhí)行”即可逆序打印。六、Excel 2007中批量刪除超鏈接的兩種方法E

22、xcel中某列單元格均含有超鏈接，用手工刪除超鏈接的方法必須一個個進(jìn)行：右擊該列中任何一個含有超鏈接的單元格，選擇“取消超鏈接”(圖1)。由于數(shù)量巨大，因此只能考慮采用批量刪除法。 圖1一、宏代碼去除法打開該Excel文件，切換到“視圖”選項卡，點擊“宏”“錄制宏”，出現(xiàn)“錄制新宏”窗口，在“宏名”定義一個名稱為：RemoveHyperlinks(圖2)，點擊“確定”退出; 圖2再點擊“宏”“查看宏”，選擇“宏名”下的“RemoveHyperlinks”并點擊“編輯”，打開“Microsoft Visual Basic”編輯器，用如下內(nèi)容替換右側(cè)窗口中的所有代碼(圖3)，然后保存關(guān)閉VBA編輯

23、器：Sub RemoveHyperlinks()Remove all hyperlinks from the active sheetActiveSheet.Hyperlinks.DeleteEnd Sub 圖3再點擊“宏”“查看宏”，選擇“宏名”下的“RemoveHyperlinks”并點擊“執(zhí)行”即可去除該工作表的鏈接。用以下代碼也可以達(dá)到相同的目的：Sub ZapHyperlinks()Cells.Hyperlinks.DeleteEnd Sub二、選擇性粘貼法右擊含有超鏈接的列并選擇“復(fù)制”，接著在該列(左)右邊插入一空白列，然后右擊該空白列，選擇“選擇性粘貼”(圖4)， 圖4在隨后出

24、現(xiàn)的“選擇性粘貼”窗口中，點選“數(shù)值”選項(細(xì)心的人會發(fā)現(xiàn)，當(dāng)選擇“數(shù)值”等選項時，“粘貼鏈接”按鈕就變成灰色不可用狀態(tài)，圖5，自然也就不會進(jìn)行超鏈接的粘貼)，最后保留該列，再刪除原先含有超鏈接的列即可。 圖5七、也談Excel表格行與列交叉著色的實現(xiàn)對于Excel表格中行與列的各種著色方法，網(wǎng)上介紹的非常詳細(xì)，筆者也從中受益不淺(詳見天極軟件的文章 及 )。以上幾種方法均涉及到函數(shù)，使用也很方便。的卻，Excel中的函數(shù)非常豐富，且功能強大，是它的一大特色。但實際應(yīng)用中，普通用戶對于函數(shù)的掌握卻僅僅局限在幾個常用函數(shù)上，對其它的還是比較陌生的。不用函數(shù)能否實現(xiàn)以上操作呢?經(jīng)過實踐筆者發(fā)現(xiàn)不用

25、函數(shù)照樣可以實現(xiàn)Excel行列交叉著色，并且還可以對列進(jìn)行著色。其操作花樣繁多、簡單易行，比使用函數(shù)還方便。例如在進(jìn)行隔行填色時，先選定要著色的單元格B2:G2。然后在“繪圖”或者“格式”工具欄中找到“填充顏色”按鈕，單擊其右側(cè)的三角彈出下拉菜單，從中選好顏色后進(jìn)行填充。 填充顏色B2:G2單元格著色后，再選定B2:G3單元格，鼠標(biāo)指向選定區(qū)域右下角的填充柄，然后向下拖動鼠標(biāo)即可得到隔行著色的效果。 隔行著色的效果對于兩行或是多行交錯著色，參照以上方法同樣可以實現(xiàn)。先將前幾行分別著色，形成一個循環(huán)規(guī)律，然后選定這幾行再向下填充即可。 多行交錯著色多行交錯著色對于列的著色，可以參照行的著色方法實

26、現(xiàn)，同樣很方便。 列的著色列的著色呵呵，簡單吧?試一試，你也可以做出來。八、Excel數(shù)據(jù)透視表分類求學(xué)生成績平均值期末統(tǒng)考結(jié)束后，教育局要求全市學(xué)生的成績都匯總在如圖1的同一個Excel工作表中，并要求求出各學(xué)校、各專業(yè)、各學(xué)科的平均分。 全市三個學(xué)校、每個學(xué)校都有六個專業(yè)、每個專業(yè)都有五門課、各專業(yè)人數(shù)不等，總計有1000余人，工作量巨大。但如果采用Excel數(shù)據(jù)透視表來完成的話，呵呵，就簡單多了。 一、創(chuàng)建數(shù)據(jù)透視表點擊菜單命令“數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂υ捒?。第一步，如圖2所示，選擇“Microsoft Excel數(shù)據(jù)列表或數(shù)據(jù)庫”及下面的“數(shù)據(jù)

27、透視表”單選項。 第二步，如圖3所示，在“選定區(qū)域”輸入全部數(shù)據(jù)所在的單元格區(qū)域，或者點擊輸入框右側(cè)的“壓縮對話”按鈕，在工作表中用鼠標(biāo)選定數(shù)據(jù)區(qū)域。 第三步，在對話框中選定“新建工作表”單選項，以便將創(chuàng)建的數(shù)據(jù)透視表放到一個新的工作表中，再點擊“完成”按鈕，如圖4所示。 這樣，就可以建立一個空的數(shù)據(jù)透視表，并同時顯示“數(shù)據(jù)透視表”工具欄和“數(shù)據(jù)透視表字段列表”對話框，如圖5所示。二、利用數(shù)據(jù)透視表得到需要的結(jié)果根據(jù)要求，我們應(yīng)該得到各學(xué)校、各專業(yè)、各學(xué)科的平均分?jǐn)?shù)。因此，我們應(yīng)該把“學(xué)校”、“專業(yè)”作為行字段，而把各學(xué)科數(shù)據(jù)作為數(shù)據(jù)項。從“數(shù)據(jù)透視表字段列表”中，把“學(xué)?！蓖系綌?shù)據(jù)透視表左側(cè)

28、“將行字段拖至此處”位置，再將“專業(yè)”字段拖至“學(xué)?！弊侄蔚挠覀?cè)，待鼠標(biāo)變成“I”字形時松開鼠標(biāo)，如圖6所示。如此就會得到如圖7所示的效果。這正是我們想要計算平均分的各個項目。 現(xiàn)在，我們只要從“數(shù)據(jù)透視表字段列表”中依次將學(xué)科字段拖至數(shù)據(jù)透視表中“請將數(shù)據(jù)項拖至此處”位置，可以得到各學(xué)校、各專業(yè)、各學(xué)科的成績總和。如圖8所示。 現(xiàn)在我們將各個求和項改為求平均值就可以了。選中透視表中第一個“求和項：語文”，如圖中C4單元格，然后點擊“數(shù)據(jù)透視表工具欄”中“字段設(shè)置”按鈕，打開“數(shù)據(jù)透視表字段”對話框。在此對話框“匯總方式”列表中選擇“平均值”。如圖9所示。確定后，所有各學(xué)校、各專業(yè)的語文科就由

29、求和改成求平均值了。 其它學(xué)科照此辦理。五門課只要五次這樣的操作。與此同時，我們還會得到各學(xué)校的各學(xué)科平均分及全市三所學(xué)校的各學(xué)科平均分，如圖10所示。 當(dāng)然，用分類匯總的辦法也可以完成上面各數(shù)據(jù)的分析。但是，這需要首先對數(shù)據(jù)進(jìn)行相應(yīng)的排序，而且如果我們需要產(chǎn)生報表，那么需要自己設(shè)置相應(yīng)的表格。而用數(shù)據(jù)透視表來完成，則可以自動產(chǎn)生相應(yīng)的報表，省卻了設(shè)計表格的麻煩。另外，如果拖動行字段或列字段相應(yīng)的字段，則可以方便地創(chuàng)建另外的報表，非常方便。如圖11所示表格，即只需要將行字段中的“專業(yè)”字段拖至“列字段”，就可以了。九、僅需一個公式 讓Excel按人頭打出工資條使用Excel按人頭制作工資條，網(wǎng)

30、上有兩個解決方案，一個是利用Word的“郵件合并”功能(詳見這里)，另一個是利用VBA來實現(xiàn)。用“郵件合并” 功能操作雖說不難，但數(shù)據(jù)過多時也很麻煩；用VBA處理起來倒是很方便，但幾十行的程序也夠初學(xué)者忙活的。經(jīng)過一番試驗，筆者找出了一個比較簡單的方法，只需一個公式就可以按人頭打印出工資條來。新建一Excel文件，在sheet1中存放工資表的原始數(shù)據(jù)，假設(shè)有N列。第一行是工資項目，從第二行開始是每個人的工資。第一行是工資項目，從第二行開始是每個人的工資。在sheet2中我們來設(shè)置工資條。根據(jù)實際情況，工資條由三行構(gòu)成，一行對應(yīng)工資項目，一行對應(yīng)一個人的工資數(shù)據(jù)，然后是一個空行用來方便切割。這樣

31、三行構(gòu)成一個工資條。工資項目處在行號除以3余數(shù)為1的行上；空行處在行號能整除3的行上。以上兩行不難設(shè)置，關(guān)鍵是工資數(shù)據(jù)行，牽扯到sheet1與 sheet2中數(shù)據(jù)的對應(yīng)，經(jīng)分析不難看出“sheet1中的數(shù)據(jù)行=INT(sheet2中的數(shù)據(jù)行+4)/3)”。這樣我們在sheet2的A1單元格中輸入公式“=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$AN,INT(ROW()+4)/3),COLUMN()”。確認(rèn)后選擇A1單元格，把鼠標(biāo)放在A1單元格的右下角，鼠標(biāo)變成“+”時，向右拖動鼠標(biāo)自動填充

32、至N列，這樣工資條中的第一行就出來了。選定A1：N1，把鼠標(biāo)放在N1單元格的右下角，鼠標(biāo)再次變成“+”時，向下拖動鼠標(biāo)自動填充到數(shù)據(jù)的最后一行，工資條就全部制作完成了。該公式運用IF函數(shù)，對MOD函數(shù)所取的引用行號與3的余數(shù)進(jìn)行判斷。如果余數(shù)為0，則產(chǎn)生一個空行；如果余數(shù)為1，則固定取sheet1中第一行的內(nèi)容；否則運用INDEX函數(shù)和INT函數(shù)來取Sheet1對應(yīng)行上的數(shù)。最后來設(shè)置一下格式，選定A1：N2設(shè)上表格線，空行不設(shè)。然后選定A1：N3，拖動N3的填充柄向下自動填充，這樣有數(shù)據(jù)的有表格線，沒有數(shù)據(jù)的沒有表格線。最后調(diào)整一下頁邊距，千萬別把一個工資條打在兩頁上。怎么樣，還滿意吧?十、把Excel里顯示的錯誤標(biāo)識全部隱藏起來作者： 宋志明 原創(chuàng)Excel經(jīng)常會因為各種原因出現(xiàn)錯誤值標(biāo)識，比如“#DIV/0!”、“#N/A”等等。怎樣才能使這些錯誤值標(biāo)