Excel 制表规范及技术

DHPGVPOExcel辦公技能提高系列課程一吳俊芳FaeryJ.F.Wu/CEN/FOXCONN200804DHPGVPOExcel常用制表技朮2FaeryJ.F.Wu/CEN/FOXCONN講義大綱DHPGExcel制表基本規范設置網格線與凍結窗格格式刷的應用驗證清單簡化輸入篩選的自定義功能排序的擴展使用表頭常用統計函數Subtotal函數說明自學函數應用分組與隱藏選擇性粘貼的巧用格式化條件的妙用超鏈接的多種運用列印頁面設置表格保護的多種實現方式DHPGVPOExcel常用制表技朮3FaeryJ.F.Wu/CEN/FOXCONNDHPGExcel制表基本規范標題表格必須有主旨明確的標題定期更新的文檔,標題中的日期與文字主題分開以便更新更新日期在標題下一行用Update或Cutoff標明更新日期采用月/日/年樣式,在單元格格式中用自定義方式設定(mm/dd/yy)表頭顏色首選鵝黃色,該顏色需從儲存格格式中選擇.如表頭需多種顏色標識可采用淺藍,淺綠或淺紫.表頭建議采用淺色的原因是顏色過深在影響列印后的顯示效果.表頭中文字應采用水平置中顯示,垂直方向可置中或靠下文字應用較大字體加粗以突出重點.通常比標題小兩號,比正文大兩號.可依需要調整.格線表格應將預設的格線刪去使版面整潔步驟﹕工具--選項--檢視--去掉格線勾選項凍結窗格當表格內容超過一頁時為方便閱覽應將表頭進行凍結步驟﹕視窗--凍結窗格DHPGVPOExcel常用制表技朮4FaeryJ.F.Wu/CEN/FOXCONNDHPGExcel制表基本規范文字英文建議采用Arial或TimesNewRoman字體.這兩種字體在顯示數字時較美觀.同等字號Arial字型比較寬.中文正文采用新細明體,標題采用楷體或新細明體加粗表格中同時采用中文和英文字體時可先全選頁面設置中文再設置英文數字數字及貨幣金額應采用靠右排列以便比較.如果數字不大可根據實際需要置中.數字一律應采用千分號區隔.在管理性報表中一般不需保留小數位.需精確數字時可根據需要設定.負數采用紅色代括號形式以突出,因為負號在中文顯示中較不明顯.列印版面設置表格最左側保留一列,以免漏設邊框.表格在設計好后需調整好列印比例和垂直水平置中以便使用者列印.步驟﹕檔案--預覽列印--設置--縮放比例/或自動調整為一整頁頁面采用標准模式,以免出現分頁預覽中的“第1頁”等水印影響閱讀頁腳設置文檔標題和索引靠左頁碼和總頁數置中日期和時間靠右DHPGVPOExcel常用制表技朮5FaeryJ.F.Wu/CEN/FOXCONNDHPGExcel制表基本規范列印版面設置列印內容超出一頁時,應設定列印標題列步驟﹕檢視--頁首及頁尾--工作表--在列印標題中選擇需列印的標題范圍--確定進度表示字體顏色黑色為常用顏色,本頁內使用的公式單元也用黑色表示(當數據變更時不需要改變)藍色為可手動輸入數據或需要隨時修改的內容深紅色為跨頁引用數據的公式等無須手工修改內容已完成或已達成要求用綠色進行中用黃色有異常或無法達成用紅色DHPGVPOExcel常用制表技朮6FaeryJ.F.Wu/CEN/FOXCONN設置网格線與凍結窗格取消默認網格線步驟:工具選項視圖把網格線的鉤去掉讓標題和表頭始終在頁面上方顯示步驟:窗口凍結窗格回目錄格式刷的應用雙擊格式刷批量修改格式單擊格式刷每次刷完後，格式刷就變成不可用了。如果希望重複複製格式，就要不停地重複上述過程。通過雙擊格式刷，可以將選定格式複製到多個不連續位置。DHPGVPOExcel常用制表技朮7FaeryJ.F.Wu/CEN/FOXCONN篩選的自定義功能自定義篩選資料→篩選→自動篩選→自定義高階篩選參考實例2篩選唯一值步驟:資料→篩選→高階篩選在原有區域中篩選選擇篩選結果的輸出方式在選擇不重複的記錄選項上打勾確定完成在選定區域中顯示篩選結果回目錄驗證清單規范和簡化輸入參考實例1首先將預設的清單內容放置在標題以上的行中,設置好后方便隱藏.選中要設置下拉菜單的單元格或區域步驟:數據驗証清單框選預設的清單內容區域確定DHPGVPOExcel常用制表技朮8FaeryJ.F.Wu/CEN/FOXCONN排序的擴展使用循欄排序步驟:排序選擇排序關鍵字循列排序參考實例3步驟:排序選項循列排序回目錄DHPGVPOExcel常用制表技朮9FaeryJ.F.Wu/CEN/FOXCONN表頭常用統計函數SubtotalSUBTOTAL(function_num,ref1,ref2,...)參考實例4該函數的優點是可以與篩選結合使用,計數更靈活.SUBTOTAL(３,ref1,ref2,...)計算清單或資料庫中符合指定條件之非空白儲存格的個數。作用相當于COUNTA函數.SUBTOTAL(９,ref1,ref2,...)計算一個清單中所有數的總和。作用相當于SUM函數.回目錄函數說明自學函數應用插入函數函數說明可以輸入所需要的功能查找相應函數,通過函數說明中的參數介紹和應用實例,可幫助自學.DHPGVPOExcel常用制表技朮10FaeryJ.F.Wu/CEN/FOXCONN群組與隱藏群組在表格欄位比較多時,可將次要欄位群組放置,可使主要信息突出并節省空間.步驟:選定要群組的欄或列資料群組及大綱群組/取消群組隱藏作用與群組功能類似,但如與相應保護功能一起使用,可以達到鎖定隱藏信息的效果小提示當被隱藏的行列恰巧在A欄或第1行的時候,經常遇到很難解除隱藏的情況.在這種情況下可檢查是否有凍結窗格,如有,先解除窗格凍結再嘗試解除隱藏.回目錄DHPGVPOExcel常用制表技朮11FaeryJ.F.Wu/CEN/FOXCONN選擇性粘貼的巧用公式僅將公式復制到新表格中而不會復制原有內容和格式(網格線,字體顏色,格式化條件等).適用于批量設置公式時使用.數值僅將原表格中的文字或符號等復制到新表格,而原來的字體格式或公式不會被復制.適用于從網頁或帶有公式的表格中提取數據.格式僅將原表格中的格式(字體,顏色,網格線,格式化條件等),而原有的內容不會復制.適用于批量設定格式化條件.驗証將驗証清單復制到新表格中而不改變表格中原來的內容.轉置參考實例5將橫軸和縱軸的數據進行互換.应用：將縱向排列的數據變為橫排做驗証清單來源運算參考實例6對數列進行批量的加減乘除運算.应用：在管理性报表中，常需要将数字取整到千位数，此时可利用这一方法达到简化目的.回目錄DHPGVPOExcel常用制表技朮12FaeryJ.F.Wu/CEN/FOXCONN格式化條件的妙用步驟:格式設定格式化條件結合Countif函數可實現多種應用COUNTIF(range,criteria)Range是您想計算符合篩選條件之儲存格個數的儲存格範圍。Criteria是用以決定是否要列入計算的搜尋篩選條件，可以是數字、表示式或文字。實例7用格式化條件自動標示進度用格式化條件提示重復項目回目錄DHPGVPOExcel常用制表技朮13FaeryJ.F.Wu/CEN/FOXCONN超鏈接的多種運用鏈接本表格中的單元格步骤：选定要放置超鏈接的单元格→右鍵選擇超鏈接→在左側“鏈結到”欄中選擇“本文檔中的位置”→在“請鍵入單格引用”中輸入單格位址→可在“要顯示的文字”欄位元設置提示文字（見右下圖）。鏈結表格中的子表格步骤：选定要放置超鏈接的单元格→右鍵選擇超鏈接→在左側“鏈結到”欄中選擇“本文檔中的位置”→“在這篇文檔中選擇位置”框中選擇子表單。鏈結电脑中其他表格步骤：选定要放置超鏈接的单元格→右鍵選擇超鏈接→在左側“鏈結到”欄中選擇“原有文件或網頁”→選擇所要鏈接的文檔。鏈結網頁直接將網頁輸入單格回目錄DHPGVPOExcel常用制表技朮14FaeryJ.F.Wu/CEN/FOXCONN頁面設置頁面設置縱橫方向縮放比例頁邊踞居中方式頁面邊踞頁眉/頁腳[頁碼][總頁數][日期][時間][路徑][文件][標籤名]工作表設定列印標簽回目錄DHPGVPOExcel常用制表技朮15FaeryJ.F.Wu/CEN/FOXCONN表格保護的多種實現方式局部保護應用於保護公式或固定內容方式一步驟：工具→保護→允許用戶編輯區域（圖二）→選取開放區域→保護工作表→設定解保護密碼方式二步驟：全選表格→右鍵選擇單元格格式→保護→勾選鎖定後確定→選取要開放的單格→右鍵選擇單元格格式→保護→取消勾選鎖定後確定→工具→保護→保護工作表→設定解保護密碼在圖一可見保護工作表選項中可設定是否鎖定隱藏欄位，是否允許插入刪除欄位，是否允許自動篩選等功能。如允許操作則可勾選相關項目唯讀保護（圖三）整表保護，只允許讀，不許修改步驟：工具→選項→安全性→設置修改許可權密碼→勾選建議唯讀選項打開保護只有通過密碼解鎖才可开启文档步驟：工具→選項→安全性→設置打開許可權密碼回目錄DHPGVPOExcel常用制表技朮16FaeryJ.F.Wu/CEN/FOXCONN資料剖析通過資料剖析可將原始資料按分隔符號號或相應長度進行分拆，供提取相關資料使用。應用實例：通過資料剖析提取身份證中的出生日期步驟：資料→資料剖析→選擇按分隔符號或固定寬度剖析資料→下一步→選擇相應分隔符號或拉動寬度回目錄DHPGVPOExcel常用制表技朮17FaeryJ.F.Wu/CEN/FOXCONN