Excel辦公室文員使用小技巧

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

2、中選擇需列表選擇性輸入的單元格區(qū)域D3:D6，單擊“數(shù)據(jù)/有效性”。如右圖3-1所示：圖3-1（2）在“數(shù)據(jù)有效性”對(duì)話框中“設(shè)置”選項(xiàng)卡，講“有效性條件”設(shè)為“序列序列”，在“來(lái)源來(lái)源”文本框中輸入“教授，副教授，講師，助講，職員”，即“職稱”的所有可選項(xiàng)，注意各項(xiàng)選項(xiàng)之間必須用西文下的逗號(hào)西文下的逗號(hào)“，”隔開隔開，且選中“忽略空格”和“提供下拉箭頭”復(fù)選框后，單擊確定按鈕完成設(shè)置，如右圖3-1所示：圖3-2（3）列表數(shù)據(jù)設(shè)置完畢之后，單擊設(shè)置區(qū)域中的任意單元格，其右邊會(huì)出現(xiàn)一個(gè)下拉箭頭，單擊下拉箭頭拉箭頭，即可在數(shù)據(jù)列表中單擊選擇完成輸入，如下圖3-3圖3-32、快速標(biāo)注不符合有效性規(guī)則

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

4、本將無(wú)法輸入。 若想取消數(shù)據(jù)有效性設(shè)置，先選定要取消數(shù)據(jù)有效的區(qū)域，如在圖3-2所示“數(shù)據(jù)有效性”對(duì)話框中單擊“全部取消”按鈕，或者將有效性條件改為任何值，單擊“確定”完成設(shè)置。3.1.2數(shù)據(jù)處理 1、查找數(shù)據(jù)、查找數(shù)據(jù) （1）使用記錄單查找數(shù)據(jù)，例如在“職工工資”中查找“公積”=120的所有講師的記錄”，如圖3-4：圖3-4(2)查找公式 當(dāng)工作表輸入大量公式后，有時(shí)候會(huì)找不到某個(gè)公式，此時(shí)按“ctrl+”鍵即可顯示出工作表中的所有公式，編譯查找。再按“ctrl+”鍵切換回原來(lái)的計(jì)算結(jié)果狀態(tài)。(3)快速選取包含公式的單元格 如果需要對(duì)工作表中包含公式的單元格加以保護(hù)，或與其他單元格加以區(qū)分是

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

6、字體”選項(xiàng)卡，設(shè)置字體顏色格式為“紅色”，執(zhí)行“確定”命令后，滿足條件的記錄全部用紅色顯示。 提示：如果要添加某種條件格式設(shè)置，單擊提示：如果要添加某種條件格式設(shè)置，單擊“添加添加”按鈕來(lái)進(jìn)行設(shè)置按鈕來(lái)進(jìn)行設(shè)置。如果不想顯示或打印的數(shù)據(jù)可將其格式設(shè)置為白色。如果不想顯示或打印的數(shù)據(jù)可將其格式設(shè)置為白色。3、獲得最合適的列寬、獲得最合適的列寬4、單元格中多行顯示、單元格中多行顯示 方法一：設(shè)置單元格，如圖3-7所示：圖3-7方法二：將光標(biāo)定位于需要換行的單元格處，使用快捷鍵“Alt+Enter”，可實(shí)現(xiàn)人工換行3.1.3制作工資條制作工資條 工資表制作完成后，將打印成工資條。工資條的特點(diǎn)是每個(gè)人

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

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

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

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

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

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

13、，對(duì)當(dāng)前生成的數(shù)據(jù)透表進(jìn)行操作，可以獲得需要的分析數(shù)據(jù)。1、篩選數(shù)據(jù)、篩選數(shù)據(jù)（1）顯示頁(yè)字段篩選數(shù)據(jù)，如圖3-15所示圖3-15（2）顯示行字段篩選數(shù)據(jù)，如圖3-16所示（3）顯示列字段篩選數(shù)據(jù)，如圖3-17所示圖3-16圖3-172、更改數(shù)據(jù)透視表的布局、更改數(shù)據(jù)透視表的布局 創(chuàng)建數(shù)據(jù)透視表后。Excel自動(dòng)打開“數(shù)據(jù)透視表”工具欄（或單擊“視圖/工具欄/數(shù)據(jù)透視表”命令，也可打開“數(shù)據(jù)透視表”工具欄），如圖3-17所示：圖3-18 “數(shù)據(jù)透視表”工具欄 使用該欄可對(duì)數(shù)據(jù)透視表進(jìn)行板式布局、數(shù)據(jù)更新等修改。修改數(shù)據(jù)透視表版式布局包括更換、增加、移動(dòng)表結(jié)構(gòu)中的字段等。3、創(chuàng)建銷售月報(bào)表創(chuàng)建銷

14、售月報(bào)表 在原始的數(shù)據(jù)表中，銷售數(shù)據(jù)是按照具體日期填寫的，而利用數(shù)據(jù)透視表還可以制作日?qǐng)?bào)表、月報(bào)表、年報(bào)表等?，F(xiàn)以創(chuàng)建銷售月報(bào)表為例，說(shuō)明數(shù)據(jù)透視表的使用。如圖3-19所示：圖3-194、重新設(shè)置匯總方式、重新設(shè)置匯總方式 創(chuàng)建的數(shù)據(jù)透視表默認(rèn)的匯總方式是“求和”,重新設(shè)置匯總方式可以通過(guò)“數(shù)據(jù)透視表”工具欄中的“字段設(shè)置” 按鈕。其具體操作如下： 單擊匯總方式單元格區(qū)域的任意單元格，選擇“數(shù)據(jù)透視表”工具欄中的“字段設(shè)置”按鈕，打開“數(shù)據(jù)透視字段”對(duì)話框，重新輸入新字段名稱，選擇新的匯總方式，例如求最大值。如圖3-20所示。單擊確定完成設(shè)置，即按新的匯總方式進(jìn)行匯總。圖3-205、更改數(shù)據(jù)、

15、更改數(shù)據(jù) 數(shù)據(jù)透視表的數(shù)據(jù)來(lái)源于數(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ù)”命令均可自動(dòng)更新數(shù)據(jù)。 3.2.3 創(chuàng)建數(shù)據(jù)透視圖創(chuàng)建數(shù)據(jù)透視圖 數(shù)據(jù)透視表是用表格的形式來(lái)顯示和分析數(shù)據(jù)。而數(shù)據(jù)透視圖則是通過(guò)圖表的方式來(lái)顯示和分析數(shù)據(jù)。 對(duì)于數(shù)據(jù)工作表，創(chuàng)建數(shù)據(jù)透視圖的過(guò)程和創(chuàng)建數(shù)據(jù)透表相似，不同之處是在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?3步驟之一”的對(duì)話框中選擇報(bào)表類型為“數(shù)據(jù)透視圖”，如圖3-21所示。圖3-21 對(duì)于創(chuàng)建好數(shù)據(jù)透視表的工作表，可以直接單擊“數(shù)據(jù)透視表”工具欄中的“圖表向?qū)А卑粹o，塊數(shù)創(chuàng)建數(shù)據(jù)透視圖如圖3-22所示。 圖3-22 在數(shù)據(jù)透視圖上同樣可以像數(shù)據(jù)透視表一樣方便的進(jìn)行調(diào)整（行字段、列字段、頁(yè)字段都有相應(yīng)的下拉箭頭），調(diào)整結(jié)果會(huì)通過(guò)透視圖表示出來(lái)。并且數(shù)據(jù)透視圖會(huì)隨著數(shù)據(jù)透視表中數(shù)據(jù)的變化而自動(dòng)改變，具有自動(dòng)更新功能。3.2.4發(fā)布分析結(jié)果發(fā)布分析結(jié)果 數(shù)據(jù)透視表除了可以在Excel工作表中更實(shí)用個(gè)，還可以發(fā)布成交忽視的web頁(yè)，通過(guò)IE瀏覽器對(duì)數(shù)據(jù)透視表盡心個(gè)查看和操作以“藥品銷售報(bào)表”為例將數(shù)據(jù)透視表發(fā)布到網(wǎng)上。其具體操作步驟如下：