Office电脑技能培训班Excel讲义2007.doc

壹、進入Excel 2002的世界1. Excel中文版的演進(順便提Lotus 123)92/1-3.193/1-4.095/12-7.097/3-97(8.0)99/7-2000版2001/7-2002版92年作業系統是Windows 3.0，95年作業系統是Windows 952. 介面改變：工作窗格：包括新增活頁簿、剪貼簿、基本搜尋、插入美工圖案智慧標籤：作用格右下方，包括自動填滿、貼上、插入、錯誤(用貼上作例子)彩色索引標籤3. 新增工具列及按鈕監看式視窗 空白處右鍵新增監看式公式稽核工具列 檢視工具列公式稽核頁首頁尾插入圖片 檢視頁首/頁尾自訂後2個按鈕框線繪製按鈕 框線紐下拉選單繪製框線F1即時求助功能貳、MS Excel 中文版的基本觀念1. 啟動：開始程式集Microsoft Excel2. 功能表按鍵盤Alt+(X)：可直接開啟功能表，按Esc跳開打開各功能後按 (X) 可直接開啟各功能雙箭頭朝下的符號表示底下另有其他指令虛字表示在此環境不能執行此功能指令右邊有號表示底下上有一系列指令3. 及時求助功能?號及小幫手（或按F1）搜尋打入問題點其他小幫手的選擇滑鼠直接可拖曳小幫手至新位置，右鍵快顯功能表4. 工具列（功能列之下）滑鼠置於工具鈕上約一秒即顯示說明Excel200只有一行工具列，部分隱藏，當叫出使用後便會調整成顯現可再滑鼠指標成四箭頭時拖曳該部分成另一行工具列(或工具自訂選項勾選成兩列)5. 檢視功能表檢視資料編輯列檢視狀態列全螢幕指令：檢視全螢幕顯示比例指令：檢視顯示比例 (或按住Ctrl滾動滑鼠滾輪)6. 關閉檔案與開新檔案(檔案開新檔案工作窗格)一般範本一般、試算表整合方案(舉例產品銷售目標)M上的範本7. 一次關閉所有檔案：按住Shift檔案關閉所有檔案叁、建立一個簡單的工作表1. 螢幕說明視窗標題：代表目前的檔名Workbook：共有3個Worksheet所組成工作窗格：2002新功能 (新增活頁簿,剪貼簿.)儲存格Cell：列(Roll)最多65536(原Word97為16384)列阿拉伯數字 欄(Column)256個欄英文字母(最後一個為IV)作用儲存格(Active Cell)：可供輸入或編輯名稱方塊：顯示作用儲存格的位址(可試鍵入A65536)，Ctrl+home可回到A1End + 、 可到工作表的各頂端填滿控點：作用儲存格右下角(複製用，和選範圍不同)垂直與水平捲軸、工具列、功能表列、狀態列、名稱方塊、資料編輯列儲存格區間表示法：(書寫表示)例為A2:A5為四個儲存格，C6:F9為16個儲存格，A2:A5,C6:F9為2個儲存區域2. 簡單資料的輸入與修改輸入快快樂樂在資料編輯列上更改成輕輕鬆鬆(亦可在儲存格上更改)刪除（按Delete）3. 正式建立工作表資料4. 自動加總鈕S：作用儲存格於C7按鈕S按Enter鍵5. 公式的拷貝：儲存格右下角填滿控點游標成十字形拖曳(C7拖至F7)6. 資料格式的設定：儲存格置B3:F7內任一格，選格式自動格式設定Colorful27. 選定區間：游標先定最左上儲存格，按住Shift鍵，游標再點入最右下儲存格，即形成一區域(示範幾個區域) ，從四個角落皆可開始按F8快速鍵：按F8，狀態列出現ext，再按對角儲存格8. 將標題置中：選定區域後按跨欄置中(先將第一列複製到其他列來示範)9. 儲存檔案(或另存新檔)為A：ch2_1，關閉後再練習開啟10. 建立圖表：選取C3:F4按圖表精靈鈕，逐步完成選圓形圖輸入標題(與A1同)資料標籤選顯示標題及百分比拖曳至適當位置11. 重新命名工作表(sheet1美男子選舉)直接於標籤上連按二下，或按右鍵選重新命名12. 列印：檔案版面設定邊界置中方式水平、垂直13. 試算表的分享與保護(進階班講授)工具保護允許使用者編輯範圍新表格輸入範圍密碼確定肆、輸入資料1. 智慧標籤：拷貝之後自動添加的包括四種選擇2. 輸入技巧：按鍵後儲存格移動方向Enter下格、Shift+Enter上格、Tab右格、Shift+Tab左格以上按上下左右鍵亦可3. 取消輸入：（儲存格作用中）按x或Esc 4. 儲存格內容修改：按Delete重新鍵入，或直接鍵入取代 5. 選取整列：按最左列數字碼6. 選取整欄：按最上欄（英文）碼7. 選取全部：按最左上列（欄）8. 滑鼠拖曳選取某一區域9. 選取不連續區：先選取一區域後再按住Ctrl拖曳另一區域10. 常數資料輸入（文字數值日期等等非公式皆是）數字加負號和括號皆是負數(練習-123,(-123) 格式儲存格數值數值是4/5必須寫0 4/5否則被視為日期數字前加上則被視為文字，靠左切齊(數字和日期靠右)數字太長無法顯示完全會以”#”來出現，只要更改欄寬即可11. 按Ctrl+；鍵可輸入今天日期，按Ctrl+Shift+；鍵可輸入現在時間輸入R91/12/12會顯示91/12/12但內容為2002/12/1212. 文字輸入（一儲存格最多可輸入255個字元）(2003版本已無限制)如輸入郵遞區號前加文字輸入太長溢到右格時，可選格式儲存格對齊自動換列來增列高13. 相同值填滿相鄰儲存格(複製)直接游標指在儲存格右下角時（+型）按住滑鼠拖曳包括原儲存格在內全部選取後選編輯填滿向右填滿14. 遞增方式填滿相鄰儲存格（Sunday、Sun、January、Jan、一月、星期一、甲、第一季、）：直接拖曳(亦可同時鍵入多項於同一欄一起拖曳)15. 每次遞增(減)1方式填滿相鄰儲存格(一定要是數字)：接游標指在儲存格右下角時（+型）按住Ctrl和滑鼠拖曳(往上拖曳為遞減)16. 等差遞增方式填滿：(遞減亦可)先建立1和8兩格全選後按住滑鼠拖曳成15、22、29、36其他格17. 自訂填滿清單：工具選項自訂清單新清單（可再編輯和刪除）鍵入如紅澄黃綠藍靛紫，亦可匯入18. 注音標示：格式注音標示編輯 (要先放大自行，自行輸入，可隱藏/顯示)Pre-Mous相關題組輸入修改儲存格內容EX2k0101A05A使用數列及填滿EX2k0201A04A自訂清單之設定EX2k1001A04A伍、輸入公式1. 打開範例ch5_1.xls2. (sigma)自動加總鈕：算邢一帥總分，也可一次做多個3. 建立簡單的數學公式（必須以=為起始字元）+加-減*乘/除%百分比乘幕(自乘幾次)例如G4求平均值公式：=(c4+d4+e4)/34. 以貼上函數建立公式(作用儲存格於F8陳黛婷的總分)按fx貼上函數選取函數名稱鍵入正確範圍（可預先看到結果）練習每科的平均G5(AVERAGE)最高分C9(MAX)最低分C10(MIN)如選次高分=LARGE(C4:C8,2)如選倒數第二名=SMALL(C4:C8,2)5. 定義範圍名稱：先選取範圍D4:D8名稱方塊輸入英文將來在D9(第五項)，按fx貼上函數選取函數名稱鍵入英文即可可以定義數個範圍，並且儲存格可以重疊定義刪除定義名稱：插入名稱定義定義範圍名稱易造成相對和絕對之混淆，宜注意6. 公式儲存格之複製：直接游標指在儲存格右下角時（+型）按住滑鼠拖曳，則會將公式改為自己同欄列之位址的公式7. 相對參照位址(如A1)，絕對參照位址(如$A$1)，當函數為絕對參照位址時複製就只會複製原位址而不會複製相對位址練習製作一個百分比的表格（一欄是數字與合計，一欄是各佔合計的百分比）8. 比較符號公式：例如計算不及格人數，C11公式為：=countif(c4:c8,60)亦可做公式儲存格之拖曳複製9. 排序資料：（這是課本方式，如資料改變，排序並不會改變，非常不好用）先選範圍A4:F8資料排序主要鍵（如選總分）次要鍵（當主要鍵有平分大小時）選遞增或遞減確定再手動排H欄的名次再重複以座號為主要鍵排序回來排序公式：=RANK(F4,$F$4:$F$8,0) (0為由大而小排列，非0為由小而大排列)10. 自動格式跨欄置中：選定範圍A1:H1按跨欄置中鈕即可套用格式：格式自動格式設定選3D Effect 2，即可得一立體面11. 自動計算功能：在下方狀態列之中央空格按右鍵會出現一些計算的選項，當選取範圍後可在狀態列另一空格看到計算結果12. 儲存格直接顯示公式：Ctrl(回復為再按一次)13. 財務函數：=PMT(rate,nper,pv,fv,type) (Pre_MOUS 2501)14. 範例檔案Evaluation邏輯函數：IF&多層次IF參照函數：VLOOKUP & HLOOKUP15. 數學函數：Sin,Cos函數(Pre_MOUS 2002)16. 絕對位址函數：(Pre_MOUS 1905)Pre-Mous相關題組名稱功能之運用EX2k1101A04A加總功能及一般計算EX2k1901A05A數學函數之運用EX2k2001A04A日期函數之運用EX2k2101A02A參照函數之運用EX2k2201A04A邏輯函數之運用EX2k2301A04A統計函數之運用EX2k2401A05A陸、編輯工作表1. 開啟ch6_1.xls檔案2. 插入(刪除)列(1)定好一儲存格A2按插入列，或編輯刪除整列(2)定好一儲存格按右鍵快顯功能表插入整列(刪除)3. 插入(刪除)欄(1)定好一儲存格A4按插入(刪除)欄(2)定好一儲存格按右鍵快顯功能表插入整欄4. 插入單一儲存格(或多個連續儲存格-要選範圍C5:C7)：A1按右鍵快顯功能表插入選現有儲存格往右移或往下移5. 刪除單一儲存格(或多個連續儲存格-要選範圍)：A1按右鍵快顯功能表刪除選下方儲存格往上移或右方儲存格往左移6. 儲存格的移動剪下與貼上（工具鈕、編輯、快顯功能表三種方式）選取範圍後滑鼠指著邊緣直接拖曳7. 儲存格的複製選取範圍後左手按住Ctrl鍵滑鼠指著邊緣直接拖曳至新位址完成統計(全部可一次完成)8. 選擇性貼上指令複製後快顯功能表選擇性貼上（解釋全部和值）A10鍵入第一季花費總計F8值貼上C10A11鍵入全年預估花費C11鍵入4將C10選擇性貼上值乘選擇性貼上的轉置為直式改橫式9. 清除儲存格最快方法為按右鍵快顯功能表清除內容10. 練習套用格式：格式自動格式設定選Accounting4柒、資料格式的設定1. 格式工具列的直接按鈕2. 更改欄寬(預設欄寬為8.38英文字母(1單位可容1個英文字母，以小b為標準代表)欄名之右框線拖曳格式欄欄寬輸入復原欄寬：編輯復原欄寬最適欄寬：(1)欄名之右框線上直接按二下 (2)格式欄(作用格要置於該格)最適欄寬3. 更改列高(預設列高為16.5，預設字形12)與更改欄寬相同(字型更大時會自動調大列高) 格式列列高4. 格式化字型：字型大小粗體斜體底線(格式工具列上)可以針對同個儲存格部分字做不同的格式化(編輯列上選取)，但改變時也要個別做5. 儲存格的字型指令：格式儲存格字型6. 設定資料對齊方式(打開ch16_01.xls)：按鈕上之靠左、靠右、置中、跨欄置中(也可多列)7. 儲存格的對齊指令：格式儲存格對齊（水平、垂直、方向-垂直與角度）加大列高做各方位之練習8. 按鈕填滿色彩與字型色彩說明9. 儲存格的圖樣指令：格式儲存格圖樣（最下方多選擇）10. 框線按鈕：按鈕為部分選擇(下方多了繪製框線)11. 儲存格的外框指令：格式儲存格外框(內外框線、線型、顏色)12. 五種數學格式的按鈕($、%、，、+.0、-.0)13. 儲存格的數字指令：格式儲存格數字14. 自訂數字格式（格式儲存格數字自訂）鍵入#,(千為單位)或#,(百萬為單位)鍵入如”剩下”0”天” 0代表結果的值12000顯示成壹萬貳千元整：數值特殊最後一個自訂最後加入”元”確定(aaa)-會出現(週三) 例：=today()(aaa) 在格式中加入不是直接輸入鍵入;則不顯示出來15. 設定格式化條件：格式格式化條件（設定如值合乎條件的顯示）16. 複製格式的按鈕：先定位按鈕後選定新範圍即可連續複製(快按兩下)17. 移除格式編輯清除格式18. 去除儲存格線工具選項檢視取消格線19. Pre_MOUS練習：0703(加入框線)解答敘述錯誤Pre-Mous相關題組修改字型格式EX2k0301A05A應用數字格式EX2k0401A05A欄及列的設定EX2k0501A04A儲存格內容之對齊EX2k0601A04A框線及網底功能之運用EX2k0701A05A捌、活頁簿的應用1. 先開啟ch18_1.xls再把工作標籤更名2. 1活頁簿設有3個工作表，最多可到255個(視儲存容量而定)3. 工作表標籤更名：（快顯功能表從新命名）第二個工作表亦更名4. 插入工作表：快顯功能表插入（插入作用中工作表之前）5. 刪除工作表：快顯功能表刪除6. 移動工作表滑鼠直接於工作表標籤拖曳編輯移動或複製工作表（亦可選到開啟中另外活頁簿）7. 複製工作表（新標籤會有原名加上（2）按住Ctrl鍵，滑鼠於工作表標籤拖曳至目的編輯移動或複製工作表勾選建立副本剪下和貼上是無法移動（複製）工作表8. 不同工作表間儲存格的複製選取範圍編輯複製移至不同工作表編輯貼上（可在別處連續貼上）9. 參考不同工作表的公式（將二個工作表資料合計）例如：Sheet3的C4儲存格鍵入=(sheet1!C4+sheet2!C4)/2就得到平均分打入=後按標籤和儲存格即可可拖曳Copy其他儲存格可練習增加一科增加一次平時成績（兩次月考各30%平時成績40%）10. 標籤捲動按鈕可顯出所有的工作表(於按鈕之右鍵)11. 新增色彩到工作表索引標籤(XP新功能)索引標籤上右鍵快顯功能表索引標籤色彩12. 編輯註解插入註解 (右鍵編輯註解、顯示、顯示時改顏色)13. Pre_MOUS練習：1702(同時選取多張工作表，一次完成)14. Pre_MOUS練習：0905(複製工作表並重新命名)以下進階班講解1. 監看式視窗(開啟ch8_4.xls) 將視窗拉寬先參考2個工作表的毛利淨利銷售目標公式第2個工作表C4右鍵新增監看式改第1個工作表數字可看到變更每一儲存格按右鍵單獨設立單獨刪除2. 公式稽核(再開啟ch8_4.xls 開啟公式稽核工具列)追蹤前導參照 (第1個工作表E4第2個鈕追蹤前導參照)追蹤從屬參照 (繼續第4個鈕追蹤從屬參照)第6個鈕可移除開啟中工作表的箭號錯誤檢查(隨便一格打入=sum)右鍵註解格式改變顏色 (注意插入點不可在內)Pre-Mous相關題組新增複製重新命名工作表EX2k0901A05A多工作表運算之應用EX2k1701A02A玖、建立圖表1. 圖表的類別：14種圖表，每一種包含數種副類型2. 開啟步驟：開啟ch9_1.xls選定欲建圖表的標記及資料範圍。範圍有時是連續，有時不相鄰（按Ctrl）按鈕圖表精靈或插入圖表根據圖表精靈漸次填入資料最後完成精靈進行中有預覽圖亦可加百分比等等、圖表位置可選入另一個Sheet/Chart拾、更改與格式化圖表1. 開啟ch10_1.xls選A3:F6(下及右的總計都不要選)按圖表精靈選直條圖下一步下一步圖表標題:2000年賓士轎車銷售表、數值Z軸單位:輛數下一步工作表中物件選sheet2完成再相同程序做一次到sheet3做完第一項可直接講右鍵的快顯功能2. 更改圖表字型大小：sheet2表內右鍵圖表區格式字型改10（表內之圖表標題改16）3. 圖表資料浮現（區域小擠壓部分資料）：拉大圖區域自然浮現(XP會自動調大圖區)4. 刪除圖表的資料數列：點選一個資料數列(C級轎車列產生小黑方塊)按Del5. 增加圖表的資料數列：圖表新增資料點鍵入=sheet1!$A$6:$F$6可看到C級轎車列回來6. 更改圖表的類型：表內右鍵圖表類型選其他的類型或副類型確定7. 改變圖表資料數列的順序：點選資料數列（列產生小黑方塊）右鍵資料數列格式數列順序往上(下)移8. 圖表的格線設定：圖表內右鍵圖表選項格線Y軸皆選主(次)要格線可看到次要格線9. 圖表的圖例設定(右邊小方塊)：刪除：直接按Del插入：圖表內右鍵圖表選項圖例顯示圖例圖例的格式設定：右邊圖例右鍵圖例格式圖樣(漸層較好)、字型、位置（左、右）10. 格式化座標軸的標題：座標右鍵座標軸標題格式圖樣(漸層較好)、字型、位置11. 變更圖表資料範圍：圖表內右鍵來源資料來源範圍(按右鈕)鍵入區間(如=sheet1!$A$3:$E$6) 已不含總計的資料12. 建立趨勢預測線：點選資料數列（列產生小黑方塊）右鍵加上趨勢線刪除點選趨勢線按Delete13. 滑鼠編修圖表：(打開ch9_7.xls)整個外圓可拉大(注意選取四方型)資料不擠在一塊：點選資料標籤右鍵資料標籤格式字型改12 (改中間的字型)每個分區可單獨點選拖曳分離出來（切蛋糕）14. 標示圖示：15. 沿用ch9_7.xls，打開繪圖工具列，可在圖表上畫上箭頭16. 數列端顯示數值：點選資料數列（列產生小黑方塊）右鍵資料數列格式資料標籤顯示數值17. 標題格式：可改直書18. 座標軸格式：可自訂刻度(要在數字上按右鍵) 座標軸格式刻度19. Pre_MOUS練習：2604(圖表刻度的修改，注意只有第五個打勾)本章重點：右鍵之各功能為圖表精靈的每一步驟，可隨時修改圓形圖之修正繪圖工具列之運用條狀bar填滿顏色Pre-Mous相關題組建立、修改圖表EX2k2601A05A拾壹、工作表的列印1. 打開ch11_2.xls活頁簿(含二個sheet)2. 預覽列印：縮放鈕和邊界鈕按出後在畫面按一下就復原，餘稍說明即可注意不要點選到圖3. 版面設定：頁面、邊界、頁首頁尾、工作表（每一項皆Review）縮放比例在某些低階印表機不支援，試用50%來觀察縮放比例：調整成幾頁會自動改變縮放比例選項(預覽列印之下按鈕)：可進入調整一張印幾頁(看印表機而不同)4. 頁首頁尾自訂頁首頁尾：字型、頁碼、總頁數、日期、時間、檔案名稱、標籤、插入圖片(最右邊按鈕可調圖片大小)5. 工作表的設定： 列印範圍：可選不連續區域用逗號隔開(如A2:C3，E5:F6) (但會印在不同的頁數)檔案列印範圍 亦可設定列印範圍(要先選區域) 標題列、標題欄：將某列或欄設定為列印時為固定標題(參考範例：LED材料表) 列印格線：列印出格線 欄名列號：印出欄名和列號 選項版面配置可選美章指印多少頁6. 列印工作表：檔案列印列印對話方塊詳細解釋7. 列印鈕：直接列印(較易後悔)Pre-Mous相關題組列印功能之運用EX2k0801A05A拾貳、樣式與多檔案的運用1. 準備ch12_1.xls檔案(內含第一次月考與第二次月考兩個工作表)2. 先將第一次月考A1:F1處理成粗體、跨欄置中，A3:F8處理成有格線置中對齊3. 選取A1:F1格式樣式鍵入考試標題按確定4. 選取A3:F8格式樣式鍵入Sample按確定5. 選取第二次月考A1:F1格式樣式選考試標題按確定和第一次月考A1:F1同格式6. 選取第二次月考A3:F8格式樣式選Sample按確定和第一次月考A3:F8同格式7. 修改會產生相同修改：選取第一次月考A3:F8格式樣式修改按確定後，第二次月考A3:F8亦會產生相同修改8. 視窗可含多組活頁簿（再開啟ch12_3.xls），在視窗最下兩個勾選來切換9. 參照其他活頁簿的儲存格：【活頁簿名稱】工作表標籤！儲存格例如學期成績為兩次月考的平均的公式為：C4=(ch12_1.xls第一次月考!C4+ch12_1.xls第二次月考!C4)/2打入=之後就可用按的，但會出現絕對位址，記的取消否則無法複製練習題(11-19頁)：再增加第三次月考成績，做成學期平均成績拾叁、清單的排序與篩選1. 篩選就是把不合乎條件的去除2. 建立ch13_1.xls檔案(此檔案適合做WORD合併列印的表格資料範本)3. 基本概念：清單第一列為欄標記，以下為清單資料，清單的單一列為一筆資料，同欄位包含相同屬性的資料從控制台國別設定日期可選定日期格式(要記得重新啟動電腦)兩碼年份解讀設定為19302029(你如打28/11/27電腦會辨識為2028/11/27，你如打30/11/27電腦會辨識為1930/11/27)4. A3:G14範圍為一個清單5. 作用格移至清單任一格，按資料篩選自動篩選，第一列欄標記會含鈕6. 每一鈕一定含全部、前10項、自訂三項7. 篩選清單（照序一條條解說）： 列出表演組所有員工：部門鈕表演組 (回復)列出所有員工：部門鈕全部 列出表演組內職位是演員的員工紀錄：部門鈕表演組職位鈕演員 (回復)列出所有員工：職位鈕全部部門鈕全部 列出職位是專員的員工紀錄：職位鈕專員 (回復)8. 自訂篩選清單： 列出1966/8/1以前出生的員