[理學(xué)]辦公軟件指南

1、.Excel 2007課后指引Excel 高級綜合運(yùn)用講師：陳理2011年8月目 錄第一章EXCEL選項設(shè)置41.1自定義快速訪問工具欄41.2默認(rèn)文件保存格式設(shè)置51.3更改默認(rèn)字體和工作表數(shù)5第二章數(shù)據(jù)輸入技巧72.1技巧總結(jié)72.2文字型數(shù)字的錄入72.3數(shù)據(jù)有效性72.4定位條件8第三章專業(yè)報表制作103.1去掉網(wǎng)格線103.2淡化邊框103.3表頭突出113.4隔行顯示123.5報表封面目錄頁133.6其他設(shè)置16第四章公式和函數(shù)184.1常用函數(shù)184.2查找函數(shù)（必須掌握的函數(shù)）184.3條件函數(shù)184.4文本函數(shù)184.5日期函數(shù)194.6公式審核194.7實例204.7.1求

2、匯率204.7.2RMB顯示萬元單位204.7.3查找對應(yīng)的數(shù)據(jù)224.7.4求出滿足條件的個數(shù)244.7.5求出特定區(qū)域內(nèi)數(shù)據(jù)的總和24第五章圖表265.1圖表適用范圍介紹265.2插入圖表265.3格式化圖表285.3.1選擇圖表樣式285.3.2添加圖表標(biāo)題295.3.3刪除網(wǎng)格線305.3.4調(diào)整圖例位置315.3.5添加數(shù)據(jù)標(biāo)簽315.3.6更改柱形圖中柱子的形狀325.3.7保存模板325.4要記住的幾點335.5其他圖表展示345.5.1折線圖345.5.2折線面積圖345.5.3線柱圖355.5.4餅形圖355.5.5目標(biāo)實際對比圖365.5.6堆疊柱形圖36第六章高級篩選37

3、6.1篩選出廣州的食品376.2篩選出深圳的電器以及王大膽的飲料386.3篩選出北京數(shù)量在50-80內(nèi)的數(shù)據(jù)39第七章條件格式417.1條件格式的使用417.2條件格式的應(yīng)用437.2.1將數(shù)值大于80的單元格填充為綠色437.2.2實際總和小于目標(biāo)值的數(shù)據(jù)行填充為橙色44第八章數(shù)據(jù)透視表478.1插入數(shù)據(jù)透視表478.2想怎么看就怎么拖508.3雙擊查看明細(xì)數(shù)據(jù)548.4按百分比查看數(shù)據(jù)56第一章 Excel選項設(shè)置1.1 自定義快速訪問工具欄在“快速訪問工具攔”上單擊右鍵，選擇“自定義快速訪問工具攔”，如下圖：在“Excel選項”窗口中選擇所有命令，然后找到并選中您需要的命令，再單擊添加按

4、扭。如下圖：1.2 默認(rèn)文件保存格式設(shè)置單擊 “Office按扭”，單擊“Excel選項”按扭，在“Excel選項”窗口中選擇“保存”，“將文件保存為此格式”中選擇“Excel 97-2003 工作簿(*.xls)”，如下圖：1.3 更改默認(rèn)字體和工作表數(shù)單擊 “Office按扭”，單擊“Excel選項”按扭，在“Excel選項”窗口中選擇“常規(guī)”，“新建工作簿時”中“使用的字體”選擇為“Arial”，“包含的工作表數(shù)”選擇“1”，如下圖：第二章 數(shù)據(jù)輸入技巧 2.1 技巧總結(jié)² 文本的快速編輯:F2² 重復(fù)上一步操作：F4² 快速復(fù)制上一行數(shù)據(jù)：Ctrl+D&#

5、178; 選擇同一列使用過的數(shù)據(jù):Alt+² 輸入當(dāng)天日期：Ctrl+;² 輸入當(dāng)下時間：Ctrl+shfit+；² 單元格內(nèi)強(qiáng)制性換行：Alt+Enter2.2 文字型數(shù)字的錄入² 日期格式設(shè)置² 輸入較長數(shù)字（如身份證號碼、銀行賬號）時前加 ² 分列功能：數(shù)據(jù)分列² 輸入相同數(shù)據(jù)：選擇多個單元格Ctrl+回車2.3 數(shù)據(jù)有效性選中數(shù)據(jù)列數(shù)據(jù)數(shù)據(jù)有效性數(shù)據(jù)有效性，如下圖：在數(shù)據(jù)有效性窗口中，“允許”一項選擇序列，“來源”下面填寫提供選擇的元素，元素間用英文逗號分隔開，如下圖： 2.4 定位條件填充空值，準(zhǔn)備數(shù)據(jù)，如下圖：光

6、標(biāo)定位于A3單元格，輸入公式“=A2”，復(fù)制A3單元格，選中A2：A12，開始查找和選擇定位條件空值確定，再單擊開始的粘貼按鈕，結(jié)果如下圖：第三章 專業(yè)報表制作 3.1 去掉網(wǎng)格線視圖顯示隱藏網(wǎng)格線，如下圖：3.2 淡化邊框開始格式設(shè)置單元格格式邊框，設(shè)置邊框，如下圖：3.3 表頭突出開始格式設(shè)置單元格格式填充，選擇填充顏色，如下圖：在“單元格格式”窗口中選擇“字體”中設(shè)置字體，如下圖：3.4 隔行顯示隔行顯示制作方法：選擇一行填充了顏色和一行沒有填充顏色的數(shù)據(jù)行，單擊格式刷，然后刷其他數(shù)據(jù)行便可。Excel 2007制作隔行顯示更快方法：選中任意一個有數(shù)據(jù)的單元格，開始樣式套用表格格式，如下

7、圖：其實這一步完全可以一次完成上面所有的精美報表步驟。3.5 報表封面目錄頁插入形狀圓角矩形，在工作表中繪制一個圓角矩形，設(shè)置好格式外觀后，輸入文字說明。右鍵圖形的外邊框，選擇“超鏈接”。在“插入超連接”窗口中“連接到”選擇“本文檔中的位置”，如下圖：在“或在這篇文檔中選擇位置”中選擇要轉(zhuǎn)跳到的工作表，如下圖：單擊“屏幕提示”按扭：“設(shè)置超連接屏幕提示”窗口中，“屏幕提示文字”下一欄中填入提示文字，如下圖：“確定”完成設(shè)置后，右鍵單擊剛才設(shè)置超鏈接的圖形，選擇“大小和屬性”，如下圖：在“大小和屬性”窗口中選擇“屬性”，將“打印對象”選項前的勾去掉，如下圖：3.6 其他設(shè)置改變工作表標(biāo)簽名稱：直

8、接雙擊工作表標(biāo)簽，輸入文字便可。隱藏工作表標(biāo)簽：Office 按扭Excel 選項高級此工作簿的顯示選項顯示工作表標(biāo)簽，如下圖設(shè)置：第四章 公式和函數(shù)4.1 常用函數(shù)Sum(要統(tǒng)計的單元格)Sumif(在哪列找,找什么,把哪列加總)Countif(在哪列找,找什么)Count(求數(shù)字個數(shù)，不包括文字及內(nèi)容)Round(數(shù)字，保留幾位小數(shù)) 四舍五入Roundup(數(shù)字，保留幾位小數(shù)) 只進(jìn)不舍Rounddown(數(shù)字，保留幾位小數(shù)) 只舍不進(jìn)4.2 查找函數(shù)（必須掌握的函數(shù)）Vlookup(找什么,在哪幾列找,找在哪幾列找的第幾列,0)4.3 條件函數(shù)Lf(條件,滿足條件結(jié)果,否則結(jié)果)And

9、（條件1,條件2,條件3,）條件同時滿足時為真Or（條件1,條件2,條件3,）只要有一個條件滿足時為真例如：And（A1>=10,A1<=20）用于判斷A1是否在10到20 之間4.4 文本函數(shù)Left/Right（內(nèi)容，取左/右邊第幾位）Mid（內(nèi)容，從第幾位開始取，取幾位）Len（內(nèi)容）求內(nèi)容的長度，有多少個字Find(找什么，從哪里找，從第幾位開始找)4.5 日期函數(shù)常用日期函數(shù)Date(年，月，日)將非日期變?yōu)槿掌赥oday()返回今天日期，是動態(tài)變化的，主要用于倒計時等Year(日期)返回日期中的年Month(日期)返回期中的月Day(日期)返回期中的天最有用的日期函數(shù)：

10、Datedif(“小日期”，“大日期”，“參數(shù)”)參數(shù)：Y M D4.6 公式審核公式公式審核，如下圖：追蹤引用單元格追蹤從屬單元格移去箭頭切換公式審核狀態(tài)：Ctrl4.7 實例4.7.1 求匯率準(zhǔn)備數(shù)據(jù)，如下圖：在B2單元格輸入公式“=A2*$D$2”，回車，將B2單元格的結(jié)果向下復(fù)制，如下圖：4.7.2 RMB顯示萬元單位在C2單元格輸入10000，如下圖：復(fù)制C2單元格，選中B2：B8，開始粘貼選擇性粘貼除確定，結(jié)果如下圖：選中B2：B8，打開“設(shè)置單元格格式”數(shù)字自定義,在類型輸入框中輸入“萬元”，如下圖： 結(jié)果，如下圖：4.7.3 查找對應(yīng)的數(shù)據(jù)準(zhǔn)備數(shù)據(jù)表1（數(shù)據(jù)一共325行），如下

11、圖：數(shù)據(jù)表2，如下圖：在數(shù)據(jù)表1的G2單元格輸入公式“=VLOOKUP(數(shù)據(jù)表1!$A2,數(shù)據(jù)表2!$A$1:$B$5,2,0)”,結(jié)果如下圖：4.7.4 求出滿足條件的個數(shù)在數(shù)據(jù)表1 H2單元格輸入公式“=COUNTIF(F:F,">150")”，求出單價大于150的個數(shù)是多少，如下圖：4.7.5 求出特定區(qū)域內(nèi)數(shù)據(jù)的總和在數(shù)據(jù)表1 的H5單元格輸入公式“=SUMIF(B:B,B2,D:D)”，求出電器的總數(shù)量，如下圖：第五章 圖表5.1 圖表適用范圍介紹柱形圖：用于數(shù)據(jù)對比、比較折線圖：用于數(shù)據(jù)變化的趨勢餅圖：用于數(shù)據(jù)占比5.2 插入圖表準(zhǔn)備數(shù)據(jù)，如下圖：選中數(shù)據(jù)

12、插入圖表類型圖表樣式柱形圖，如下圖：5.3 格式化圖表5.3.1 選擇圖表樣式選中圖表設(shè)計圖表樣式，如下圖：建議使用比較素雅的圖表樣式。5.3.2 添加圖表標(biāo)題選中圖表布局圖表標(biāo)題圖表上方，如下圖：輸入圖表標(biāo)題。5.3.3 刪除網(wǎng)格線選中圖表布局網(wǎng)格線主要橫網(wǎng)格線無，如下圖：5.3.4 調(diào)整圖例位置選中圖表布局圖例在頂部顯示圖例；調(diào)整圖表標(biāo)題和圖例的位置，置于圖表左上方；插入一個文本框，置于圖表左上角，輸入圖表副標(biāo)題“銷售業(yè)績”；在圖表左下方插入一個文本框，輸入數(shù)據(jù)來源，效果如下圖：5.3.5 添加數(shù)據(jù)標(biāo)簽選中圖表布局?jǐn)?shù)據(jù)標(biāo)簽數(shù)據(jù)標(biāo)簽內(nèi)，效果如下圖：5.3.6 更改柱形圖中柱子的形狀插入形狀箭

13、頭，在工作表空白處繪制箭頭，復(fù)制箭頭，單擊圖表的柱子，單擊粘貼按鈕，結(jié)果如下圖：5.3.7 保存模板選中圖表設(shè)計另存為模板更改模板名稱保存，如下圖：5.4 要記住的幾點“設(shè)計”中可以快速應(yīng)用已經(jīng)設(shè)計好的專業(yè)圖表樣式?！安季帧敝锌梢葬槍我辉剡M(jìn)行布局調(diào)整?！案袷健敝锌梢葬槍我辉剡M(jìn)行外觀更改。丟了什么、少了什么到“布局”中找。更多設(shè)置右鍵圖表元素，選擇設(shè)置元素屬性。5.5 其他圖表展示5.5.1 折線圖5.5.2 折線面積圖5.5.3 線柱圖5.5.4 餅形圖5.5.5 目標(biāo)實際對比圖5.5.6 堆疊柱形圖第六章 高級篩選6.1 篩選出廣州的食品應(yīng)用數(shù)據(jù)表1，新建空白工作表，設(shè)置條件區(qū)域，如

14、下圖：將光標(biāo)定位于數(shù)據(jù)表1的數(shù)據(jù)區(qū)域，數(shù)據(jù)高級，選擇條件區(qū)域，如下圖：確定，結(jié)果如下圖：6.2 篩選出深圳的電器以及王大膽的飲料設(shè)置條件區(qū)域，如下圖：篩選結(jié)果如下圖：6.3 篩選出北京數(shù)量在50-80內(nèi)的數(shù)據(jù)設(shè)置條件區(qū)域，如下圖：篩選結(jié)果如下圖：第七章 條件格式7.1 條件格式的使用選中數(shù)據(jù)，開始條件格式數(shù)據(jù)條，如下圖：選中數(shù)據(jù)，開始條件格式突出顯示單元格規(guī)格大于，如下圖：選中數(shù)據(jù)，開始條件格式新建規(guī)則新建格式規(guī)則，如下圖：7.2 條件格式的應(yīng)用7.2.1 將數(shù)值大于80的單元格填充為綠色選中數(shù)據(jù)，開始條件格式新建規(guī)則新建格式規(guī)則只為包含以下內(nèi)容的單元格設(shè)置格式，設(shè)置條件，如下圖：單擊格式按鈕

15、，選擇填充顏色為綠色，如下圖：單擊確定按鈕，結(jié)果如下圖：7.2.2 實際總和小于目標(biāo)值的數(shù)據(jù)行填充為橙色準(zhǔn)備數(shù)據(jù)，如下圖：選中A2：G9單元格區(qū)域，開始條件格式新建規(guī)則使用公式確定要設(shè)置格式的單元格，在“為符合此公式的值設(shè)置格式”輸入框中輸入公式“=sum($B2:$G2)<$A2”，并且設(shè)置格式填充為橙色，如下圖：結(jié)果如下圖：第八章 數(shù)據(jù)透視表8.1 插入數(shù)據(jù)透視表選中任意數(shù)據(jù)單元格插入數(shù)據(jù)透視表確定，如下圖： 出現(xiàn)下面窗口，直接單擊確定即可： 如果您的數(shù)據(jù)透視表初始狀態(tài)為下圖顯示的樣子，您還需要先設(shè)置一下：設(shè)置方法：選項數(shù)據(jù)透視表選項選項，如下圖： 在“數(shù)據(jù)透視表選項”窗口中選擇到“

16、顯示”，將“經(jīng)典數(shù)據(jù)透視表布局”選項勾選上，如下圖：設(shè)置完畢后，數(shù)據(jù)透視表區(qū)域?qū)⒊蔀槿缦碌臉幼樱?.2 想怎么看就怎么拖首先將右邊代表“日期”的那一個字段拖到行字段里面，如下圖：然后將代表“城市”字段拖到列字段處，如下圖：最后將“數(shù)量”字段拖到數(shù)據(jù)項處，如下圖：此時觀看透視表還不是很好，日期是一天一天顯示的，還需要修改為年方式顯示。設(shè)置方法：右鍵單擊任意一個日期，選擇“組合”，如下圖：在“分組”窗口中默認(rèn)“月”，如下圖：還可以按每年的每個季度查看，設(shè)置方法：右鍵單擊日期選擇“分組”，在“分組”窗口中選擇“季度”，如下圖：最終結(jié)果如下圖：8.3 雙擊查看明細(xì)數(shù)據(jù)將地區(qū)拖到行字段區(qū)，數(shù)量拖到數(shù)值區(qū)，如下圖：雙擊“地區(qū)”的任意一項，選擇日期，如下圖：再雙擊任