Excel辦公室文員使用小技巧(共37張)(PPT 37頁)

1、高級辦公應用第三章 Excel 2000的高級應用 第1頁，共37頁。 3.1 編制工資表 教學目標： 通過本節(jié)的學習能夠在日常工作中高效的完成數(shù)據(jù)的設計、分析、 管理，并能夠?qū)?shù)據(jù)進行安全性控制。 教學的主要內(nèi)容： 數(shù)據(jù)有效性設置 數(shù)據(jù)處理 打印技巧第2頁，共37頁。3.1.1數(shù)據(jù)的有效性1、列表選擇性輸入 在用Excel進行數(shù)據(jù)處理是，有大量的原始數(shù)據(jù)需要用鍵盤輸入。特別是有一類數(shù)據(jù)，其值只限于幾項之一，例如“性別”、“男女”、“婚否”僅限于“是”、“否”對于這類數(shù)據(jù)為保證快速完成數(shù)據(jù)的輸入同時又避免輸入錯誤，我們可以采用“列表選擇”的方式輸入。具體操作步驟如下：第3頁，共37頁。（1）在

2、“工資表.xls”中選擇需列表選擇性輸入的單元格區(qū)域D3:D6，單擊“數(shù)據(jù)/有效性”。如右圖3-1所示：圖3-1第4頁，共37頁。（2）在“數(shù)據(jù)有效性”對話框中“設置”選項卡，講“有效性條件”設為“序列”，在“來源”文本框中輸入“教授，副教授，講師，助講，職員”，即“職稱”的所有可選項，注意各項選項之間必須用西文下的逗號“，”隔開，且選中“忽略空格”和“提供下拉箭頭”復選框后，單擊確定按鈕完成設置，如右圖3-1所示：圖3-2第5頁，共37頁。（3）列表數(shù)據(jù)設置完畢之后，單擊設置區(qū)域中的任意單元格，其右邊會出現(xiàn)一個下拉箭頭，單擊下拉箭頭，即可在數(shù)據(jù)列表中單擊選擇完成輸入，如下圖3-3圖3-3第6

3、頁，共37頁。2、快速標注不符合有效性規(guī)則的數(shù)據(jù) 向工資表輸入數(shù)據(jù)信息時，有可能會出現(xiàn)輸入錯誤，而一旦數(shù)據(jù)有誤就會影響后面公式的計算，如要保證數(shù)據(jù)輸入無誤，通常我們在輸入數(shù)據(jù)前，利用“數(shù)據(jù)/有效性”命令的方式進行數(shù)據(jù)輸入有效性的炎癥，防止數(shù)據(jù)輸入時不必要的錯誤發(fā)生。 如果已經(jīng)在工資表中輸入了數(shù)據(jù)，如何快速查找出不符合限定條件的數(shù)據(jù)？其具體操作步驟如圖-GIF1：第7頁，共37頁。圖-GIF1第8頁，共37頁。3、數(shù)據(jù)有效性 列表選擇是數(shù)據(jù)有效性的一種設置，數(shù)據(jù)有效性的設置方法還有很多，例如有效性條件為“時間”，則可以設置有效時間段，不再有效時間段內(nèi)的時間將無法輸入；有效性條件為“文本長度”。則

4、可設置文本有效長度，超過有效長度的文本將無法輸入。 若想取消數(shù)據(jù)有效性設置，先選定要取消數(shù)據(jù)有效的區(qū)域，如在圖3-2所示“數(shù)據(jù)有效性”對話框中單擊“全部取消”按鈕，或者將有效性條件改為任何值，單擊“確定”完成設置。第9頁，共37頁。3.1.2數(shù)據(jù)處理 1、查找數(shù)據(jù) （1）使用記錄單查找數(shù)據(jù)，例如在“職工工資”中查找“公積”=120的所有講師的記錄”，如圖3-4：圖3-4第10頁，共37頁。(2)查找公式 當工作表輸入大量公式后，有時候會找不到某個公式，此時按“ctrl+”鍵即可顯示出工作表中的所有公式，編譯查找。再按“ctrl+”鍵切換回原來的計算結(jié)果狀態(tài)。(3)快速選取包含公式的單元格 如果

5、需要對工作表中包含公式的單元格加以保護，或與其他單元格加以區(qū)分是，可以用以下方法快速選取包含公式的單元格，具體操作如圖3-5：圖3-5“編輯定位”單擊“定位條件”選擇“公式”選項第11頁，共37頁。2、條件格式 在Excel表格中我們通常對數(shù)據(jù)進行各種各樣的格式設置來美化表格。但在很多情況下，數(shù)據(jù)格式設置為有條件的，數(shù)據(jù)將按不同條件區(qū)分顯示。例如將“職工工資”中“基本工資”低于1500元的所有記錄用紅色顯示。（1）選取待條件格式設置的單元格區(qū)域“基本工資”所在列，執(zhí)行菜單欄上“格式條件格式”命令，打開“條件格式”對話框。如圖3-6所示：圖3-6（2）在“條件格式”對話框中，選擇、輸入條件“單元

6、格小于1500”；單擊“格式按鈕”，在打開的“單元格格式”對話框中，選擇“字體”選項卡，設置字體顏色格式為“紅色”，執(zhí)行“確定”命令后，滿足條件的記錄全部用紅色顯示。 提示：如果要添加某種條件格式設置，單擊“添加”按鈕來進行設置。如果不想顯示或打印的數(shù)據(jù)可將其格式設置為白色。第12頁，共37頁。3、獲得最合適的列寬4、單元格中多行顯示 方法一：設置單元格，如圖3-7所示：圖3-7方法二：將光標定位于需要換行的單元格處，使用快捷鍵“Alt+Enter”，可實現(xiàn)人工換行第13頁，共37頁。3.1.3制作工資條 工資表制作完成后，將打印成工資條。工資條的特點是每個人的記錄都要配有工資欄目，用一般的方

7、法并不容易，我們可以巧用排序的方法來解決。（1）將“工資表”中的所有職工記錄（注意：不包括工資欄目行）復制到里一張新工作表中（如sheet2），并在第一列前插入一新列，沖第一名職工開始，利用自動填充的方法輸入的差數(shù)列2，4，6，如圖3-8所示在第一列前插入一新列自動填充的方法輸入最后效果第14頁，共37頁。（2）將“工資表”中的工資欄目復制到sheet2中最后一名職工記錄之后，注意：有幾條職工記錄，即復制幾條工資欄目，然后再利用自動填充的方法輸入等差數(shù)列1，3，5，如圖3-9所示圖3-9（3）選定整個數(shù)據(jù)區(qū)域，按A列的序號數(shù)據(jù)升序排列。排好后將A列的序號數(shù)據(jù)刪掉即可得到所需工資條，如圖3-10

8、所示：圖3-10第15頁，共37頁。3.1.4表格打印技巧1、表格的縮放 一個表格，一頁多一點要縮為一頁，除了可以調(diào)整也邊距外，還可以用Excel里提供的縮放功能實現(xiàn)。 單擊菜單欄上“文件/頁面設置”命令，在“頁面”選項卡中，選中“調(diào)整為”單選項，輸入適當?shù)捻摂?shù)“1”，如圖3-11所示： 如果有一個工作表數(shù)據(jù)較多，需要使用A3的執(zhí)行才能在一頁全部顯示，而當前的打印機（例如hp LaserJet1010）最多只能打印A4的紙張。此時可以使用縮放打印的功能：在“頁面”選項卡中單擊“選項”按鈕，如圖3-11所示，打開“hp LaserJet1010屬性”對話框并選擇“紙張”選項卡，在“打印紙尺寸”下

9、拉列表框中選擇A4值上打印A3紙型的工作表。圖3-11第16頁，共37頁。2、表格欄目的凍結(jié) 如果一個表格由很多頁，當我們上下瀏覽表格時，第一頁能夠看到表格欄目，后續(xù)頁看不到，但往往后續(xù)各頁也需要顯示表格欄目，此時我們可以凍結(jié)欄目。其具體操作如下： （1）單擊菜單欄上“窗口/拆分”命令，界面上出現(xiàn)拆分條，將拆分條拖到欄目下。如圖3-12所示（2）單擊菜單欄上“窗口/凍結(jié)窗格”命令，則欄目一行被凍結(jié)。當拖動滾動條瀏覽時，欄目不會動，各頁均能看到欄目。 提示：要“撤銷窗口凍結(jié)”或“撤銷拆分窗口”，點擊“窗口”命令，選擇相應選項命令即可。第17頁，共37頁。 欄目凍結(jié)只適用于屏幕顯示，并不能用于打印

10、。若打印時希望每頁表格都有欄目，必須在“頁面設置”中進行調(diào)整。具體操作如下： 執(zhí)行菜單欄上“文件/頁面設置”命令，在“頁面設置”對話框中選擇“工作表”選項卡，在“頂端標題行”編輯框中輸入“$1：$1”，單擊“確定”完成設置。 如果想打印“行號列號”，在“工作表”選項卡中選中“行號列標”復選框。如圖3-13所示：3、表格欄目的重復打印圖3-13頁面設置注意： “$1：$1”表示第一行，同理“$A:$A”表示第一列， “$1：$2”表示第一行和第二行第18頁，共37頁。3.2 數(shù)據(jù)統(tǒng)計與分析教學目標： 通過本節(jié)學習，掌握數(shù)據(jù)透視表的基本結(jié)構(gòu)，并能夠運用數(shù)據(jù)透視表和透視圖對Excel工作表中格的數(shù)據(jù)

11、根據(jù)實際需要重新組織與統(tǒng)計分析。教學主要內(nèi)容： 創(chuàng)建數(shù)據(jù)透視表 使用數(shù)據(jù)透視表分析數(shù)據(jù) 創(chuàng)建數(shù)據(jù)透視圖 發(fā)布分析結(jié)果第19頁，共37頁。3.2.1 創(chuàng)建數(shù)據(jù)透視表 1.數(shù)據(jù)圖示表 數(shù)據(jù)透視表是Excel提供的強大的數(shù)據(jù)分析處理工具。他能夠?qū)?shù)據(jù)進行交叉分析，可以轉(zhuǎn)換行和列以查看眼數(shù)據(jù)的不同匯總結(jié)果，可以顯示不同頁面以篩選數(shù)據(jù)，還可以根據(jù)需要顯示所選區(qū)域中的明細數(shù)據(jù)，非常便于在一個清單中重新組織和統(tǒng)計數(shù)據(jù)。數(shù)據(jù)透視表主要組成： （1）字段：在數(shù)據(jù)透視表中被指定為行方向的源數(shù)據(jù)庫中的字段。 （2）列字段：在數(shù)據(jù)透視表中被指定為列方向的源數(shù)據(jù)庫中的字段。 （3）數(shù)據(jù)字段：含有數(shù)據(jù)的源數(shù)據(jù)庫中的字段項

12、，提供要匯總的數(shù)據(jù)值。 （4）頁字段：在數(shù)據(jù)透視表中被指定為頁方向的源數(shù)據(jù)庫中的字段。允許篩選整個數(shù)據(jù)透視表，以顯示單項或所有項的數(shù)據(jù)。第20頁，共37頁。2.創(chuàng)建數(shù)據(jù)透視表 創(chuàng)建數(shù)據(jù)透視表的操作比較復雜的，為方便用戶使用，Excel提供了數(shù)據(jù)透視表和圖表報告。通過次向?qū)?shù)據(jù)源進行交叉制表和匯總，并重新布局、計果，最終可以將平面的工作表數(shù)據(jù)轉(zhuǎn)換為立體的分析結(jié)果，具體操作步驟如圖3-14所示：第21頁，共37頁。圖3-14第22頁，共37頁。 3.2.2 使用數(shù)據(jù)透視表分析數(shù)據(jù) 原始的數(shù)據(jù)源清單記錄了銷售的詳細內(nèi)容，但只是一個流水賬式的工作表雖然可以使用篩選、分類匯總等技術手段對工作表中的數(shù)據(jù)

13、進一步分析但很難得到更靈活、更豐富的分析結(jié)果。建立數(shù)據(jù)透視表后，對當前生成的數(shù)據(jù)透表進行操作，可以獲得需要的分析數(shù)據(jù)。第23頁，共37頁。1、篩選數(shù)據(jù)（1）顯示頁字段篩選數(shù)據(jù)，如圖3-15所示圖3-15第24頁，共37頁。（2）顯示行字段篩選數(shù)據(jù)，如圖3-16所示（3）顯示列字段篩選數(shù)據(jù)，如圖3-17所示圖3-16圖3-17第25頁，共37頁。2、更改數(shù)據(jù)透視表的布局 創(chuàng)建數(shù)據(jù)透視表后。Excel自動打開“數(shù)據(jù)透視表”工具欄（或單擊“視圖/工具欄/數(shù)據(jù)透視表”命令，也可打開“數(shù)據(jù)透視表”工具欄），如圖3-17所示：圖3-18 “數(shù)據(jù)透視表”工具欄 使用該欄可對數(shù)據(jù)透視表進行板式布局、數(shù)據(jù)更新等

14、修改。修改數(shù)據(jù)透視表版式布局包括更換、增加、移動表結(jié)構(gòu)中的字段等。第26頁，共37頁。3、創(chuàng)建銷售月報表 在原始的數(shù)據(jù)表中，銷售數(shù)據(jù)是按照具體日期填寫的，而利用數(shù)據(jù)透視表還可以制作日報表、月報表、年報表等。現(xiàn)以創(chuàng)建銷售月報表為例，說明數(shù)據(jù)透視表的使用。如圖3-19所示：第27頁，共37頁。圖3-19第28頁，共37頁。4、重新設置匯總方式 創(chuàng)建的數(shù)據(jù)透視表默認的匯總方式是“求和”,重新設置匯總方式可以通過“數(shù)據(jù)透視表”工具欄中的“字段設置” 按鈕。其具體操作如下： 單擊匯總方式單元格區(qū)域的任意單元格，選擇“數(shù)據(jù)透視表”工具欄中的“字段設置”按鈕，打開“數(shù)據(jù)透視字段”對話框，重新輸入新字段名稱，

15、選擇新的匯總方式，例如求最大值。如圖3-20所示。單擊確定完成設置，即按新的匯總方式進行匯總。圖3-20第29頁，共37頁。5、更改數(shù)據(jù) 數(shù)據(jù)透視表的數(shù)據(jù)來源于數(shù)據(jù)源，不能再透視表中直接修改數(shù)據(jù) 。數(shù)據(jù)清單中的數(shù)據(jù)改變了，透視表中的數(shù)據(jù)必須執(zhí)行更新數(shù)據(jù)操作才能刷新。具體操作如下： 數(shù)據(jù)源清單中的數(shù)據(jù)改變后，單擊“數(shù)據(jù)透視表”工具欄中的“更新數(shù)據(jù)” 按鈕；或執(zhí)行菜單欄上“數(shù)據(jù)/更新數(shù)據(jù)”命令均可自動更新數(shù)據(jù)。第30頁，共37頁。 3.2.3 創(chuàng)建數(shù)據(jù)透視圖 數(shù)據(jù)透視表是用表格的形式來顯示和分析數(shù)據(jù)。而數(shù)據(jù)透視圖則是通過圖表的方式來顯示和分析數(shù)據(jù)。 對于數(shù)據(jù)工作表，創(chuàng)建數(shù)據(jù)透視圖的過程和創(chuàng)建數(shù)據(jù)透

16、表相似，不同之處是在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?3步驟之一”的對話框中選擇報表類型為“數(shù)據(jù)透視圖”，如圖3-21所示。圖3-21第31頁，共37頁。 對于創(chuàng)建好數(shù)據(jù)透視表的工作表，可以直接單擊“數(shù)據(jù)透視表”工具欄中的“圖表向?qū)А卑粹o，塊數(shù)創(chuàng)建數(shù)據(jù)透視圖如圖3-22所示。 圖3-22第32頁，共37頁。 在數(shù)據(jù)透視圖上同樣可以像數(shù)據(jù)透視表一樣方便的進行調(diào)整（行字段、列字段、頁字段都有相應的下拉箭頭），調(diào)整結(jié)果會通過透視圖表示出來。并且數(shù)據(jù)透視圖會隨著數(shù)據(jù)透視表中數(shù)據(jù)的變化而自動改變，具有自動更新功能。第33頁，共37頁。3.2.4發(fā)布分析結(jié)果 數(shù)據(jù)透視表除了可以在Excel工作表中更實用個，還可以發(fā)布成交忽視的web頁，通過IE瀏覽器對數(shù)據(jù)透視表盡心個查看和操作以“藥品銷售報表”為例將數(shù)據(jù)透視表發(fā)布到網(wǎng)上。其具體操作步驟如下：第34頁，共37頁。（1）在“藥品銷售報表”工作表中，選擇菜單欄上“文件/另存為