第四章  電子表格軟件在會計中的應用

【基本要求】

    1. 了解常用電子表格軟件的種類

    2. 了解電子表格軟件的主要功能

    3. 熟悉Excel軟件的用戶界面

    4. 熟悉啟動與退出Excel軟件的常用方法

    5. 熟悉Excel文件管理的常用方法

    6. 熟悉Excel圖表的插入方法

    7. 掌握數據輸入與編輯的常用方法

    8. 掌握Excel的公式及其運用

    9. 掌握Excel常用函數的使用

    10. 掌握數據清單的設計要求

    11. 掌握記錄單的使用方法

    12. 掌握Excel的數據排序、篩選與分類匯總的方法

    13. 掌握數據透視表創建與設置的方法

【考試內容】

    第一節  電子表格軟件概述

    一、常用的電子表格軟件

    電子表格，又稱電子數據表，是指由特定軟件制作而成的，用于模擬紙上計算的由橫豎線條交叉組成的表格。

    WINDOWS 操作系統下常用的電子表格軟件主要有微
軟的EXCEL、金山WPS電子表格等；Mac操作系統下則有蘋果的Numbers，該軟件同時可用于 iPad 等手持設備。此外，還有專業電子表格軟件如 Lotus Notes、第三方電子表格軟件如FormulaOne等。

    微軟的 Excel 軟件（以下簡稱 Excel）是美國微軟公司研制的辦公自動化軟件OFFICE的重要組成部分，目前已經廣泛應用于會計、統計、金融、財經、管理等眾多領域。考慮到其操作簡單直觀、應用范圍廣泛、用戶眾多且與其他電子表格軟件具有很好的兼容性，未特別說明時，本大綱主要介紹EXCEL有關內容。

    二、電子表格軟件的主要功能

    電子表格軟件的主要功能有：（1）建立工作簿；（2）管理數據；（3）實現數據網上共享；（4）制作圖表；（5）開發應用系統。

    （一）建立工作簿

    Excel 啟動后，即可按照要求建立一個空白的工作簿文件，每個工作簿中含有一張或多張空白的表格。這些在屏幕上顯示出來的默認由灰色橫豎線條交叉組成的表格被稱為工作表，又稱“電子表格”。工作簿如同活頁夾，工作表如同其中的一張張活頁紙，且各張工作表之間的內容相對獨立。工作表是Excel存儲和處理數據的最重要的部分，也稱電子表格。每張工作表由若干行和列組成，行和列交叉形成單元
格。單元格是工作表的最小組成單位，單個數據的輸入和修改都在單元格中進行，每一單元格最多可容納 32000 個字符。

    在Excel 2003 中，每個工作簿默認含有 3 張工作表，每張工作表由65536行和256列組成；在Excel 2013中，每個工作簿默認含有 1張工作表，該工作表由1048576行和16384列組成。默認的工作表不夠用時，可以根據需要予以適當添加。每個工作簿含有工作表的張數受到計算機內存大小的限制。

    （二）管理數據

    用戶通過Excel不僅可以直接在工作表的相關單元格中輸入、存儲數據，編制銷量統計表、科目匯總表、試算平衡表、資產負債表、利潤表以及大多數數據處理業務所需的表格，而且可以利用計算機，自動、快速地對工作表中的數據進行檢索、排序、篩選、分類、匯總等操作，還可以運用運算公式和內置函數，對數據進行復雜的運算和分析。

    （三）實現數據網上共享

    通過 Excel，用戶可以創建超級鏈接，獲取局域網或互聯網上的共享數據，也可將自己的工作簿設置成共享文件，保存在互聯網的共享網站中，讓世界上任何位置的互聯網用戶共享工作簿文件。

    （四）制作圖表

    Excel提供了散點圖、柱形圖、餅圖、條形圖、面積圖、折線圖、氣泡圖、三維圖等14類100多種基本圖表。Excel不僅能夠利用圖表向導方便、靈活地制作圖表，而且可以很容易地將同一組數據改變成不同類型的圖表，以便直觀地展示數據之間的復雜關系；不僅能夠任意編輯圖表中的標題、坐標軸、網絡線、圖例、數據標志、背景等各種對象，而且可以在圖表中添加文字、圖形、圖像和聲音等，使精心設計的圖表更具說服力。

    （五）開發應用系統

    Excel自帶VBA宏語言，用戶可以根據這些宏語言，自行編寫和開發一些滿足自身管理需要的應用系統，有效運用和擴大Excel的功能。

    三、Excel軟件的啟動與退出

    （一）Excel軟件的啟動

    通常可以采用下列方法啟動Excel軟件：

    1.點擊“開始”菜單中列示的Excel快捷命令

    通過“開始”菜單可啟動 Excel 軟件，同時建立一個新的文檔，該文檔在 Excel 軟件中被默認為工作簿。啟動 Excel后建立的第一個空白工作簿的缺省名和擴展名，在  Excel2003中分別默認為 “Book1”和“.xls”（在Excel 2013中則分別為“工作簿1”和“.xlsx”），但也可以另存為其他名字和類型

的文件。

    2.點擊桌面或任務欄中Excel的快捷方式圖標

    這種方法的前提是桌面或任務欄中已經創建 Excel快捷方式圖標。

    3.通過“運行”對話框啟動Excel軟件

    相關操作完成后，Excel 啟動，同時建立一個新的空白工作簿。

    4.打開現成的Excel文件

    直接點擊現成的 Excel 文件，通過打開該文件來啟動Excel軟件。

    （二）Excel軟件的退出

    通常可以采用下列方法退出Excel軟件：

    1.點擊標題欄最右邊的關閉按鈕

    點擊標題欄最右邊的關閉按鈕“   ”后，Excel軟件將被退出。

    2.點擊“關閉窗口”或“關閉所有窗口”命令

    右鍵單擊任務欄中的 Excel圖標，打開菜單選項，點擊“關閉窗口”命令可退出 Excel 文件。文件被關閉后，Excel軟件也隨之退出。

    3.按擊快捷鍵“Alt＋F4”

    按擊“Alt＋F4”鍵后，Excel軟件將被退出。

    以上三種方法操作時，如果退出前有編輯的內容未被保
存，將出現提示是否保存的對話框。

    需注意的是：以上操作方法均指的是當前只有一個工作簿的情形，如果當前有多個工作簿文件在運行，以上操作方法執行的結果將是光標所在的文件被關閉，其他處于打開狀態的 Excel 文件仍在運行，Excel 軟件并未退出。只有這些文件均被關閉后，Excel軟件才能退出。

    四、Excel軟件的用戶界面

    Excel 軟件啟動后，通常會建立一個新的空白工作簿或者打開一個現有的工作簿，并在屏幕上呈現一個最大化的工作簿窗口（簡稱窗口）。這一窗口是用戶操作Excel軟件的重要平臺，被稱為默認的用戶界面。

    Excel 軟件的默認用戶界面因版本不同而有所區別。其中，Excel 2003及以下版本的默認用戶界面基本相同，由標題欄、菜單欄、工具欄、編輯區、工作表區、狀態欄和任務窗格等要素組成；Excel 2007及以上版本的默認用戶界面基本相同，主要由功能區、編輯區、工作表區和狀態欄等要素組成。

    （一）標題欄

    標題欄位于窗口的最上方，依次列示Excel軟件的圖標、文檔的標題和控制Excel窗口的按鈕。

    （二）菜單欄

    Excel 2003的菜單欄默認位于標題欄的下方，但可移動
到窗口的其他適當位置，包含“文件”、“編輯”、“視圖”、“插入”、“格式”、“工具”、“數據”、“窗口”和“幫助”等9個默認的菜單項，包括 Excel的全部操作命令，每一菜單項分別含有對工作表進行操作的一組功能相關的命令選項。命令后面帶有“…”的，表示選擇了這一命令后將打開該命令的對話框；命令后面帶有“►”的，表示該選項后面帶有一個子菜單。

    （三）工具欄

    工具欄默認位于菜單欄的下方，但可移動到窗口的其他適當位置，它由一系列與菜單選項命令具有相同功能的按鈕組成。每個按鈕代表一個命令，能更加快捷地完成相應的操作。

    用戶不僅可以自行設定工具欄的顯示、隱藏及其在窗口中的位置，而且可以自行設定工具欄中的按鈕及其在工具欄中的位置。

    （四）編輯區

    編輯區默認位于工具欄的下方，由名稱框、取消輸入按鈕、確認輸入按鈕、插入函數按鈕和編輯欄構成，用來顯示當前單元格的名字和當前單元格的內容、取消或確認本次輸入的數據或公式。

    （五）工作表區

    工作表區默認位于編輯區的下方，是 Excel文件用于存儲和處理數據的專門區域，由工作表、工作表標簽、標簽滾
動按鈕、滾動條和滾動條按鈕、列和列號、行和行號、全選按鈕、單元格等要素組成。

    （六）狀態欄

    狀態欄默認位于窗口底部，可以顯示各種狀態信息，如單元格模式、功能鍵的開關狀態等。

    （七）任務窗格

    任務窗格默認位于Excel窗口的右邊，但可移動到窗口的其他適當位置，用于集中放置最常用的功能和快捷方式，具體包括“開始工作”、“幫助”、“搜索結果”、“剪貼畫”、“信息檢索”、“剪貼板”“新建工作簿”、“模板幫助”、“共享工作區”、“文檔更新”和“XML源”等11個任務窗格。

    （八）功能區

    功能區是由一系列在功能上具有較強相關性的組和命令所形成的區域，各功能區的主要功能由相應的選項卡標簽予以標識，用戶可以根據需要完成的操作，快速找到和調用包含當前所需命令的功能區。

    Excel 2013默認的選項卡標簽有“開始”、“插入”、“頁面布局”、“公式”、“數據”、“審閱”、“視圖”、“開發工具”，排列在標題欄的下方。此外，用戶還可以通過“自定義功能區”自定義選項卡。單擊任一選項卡標簽，其下方將出現一個以平鋪方式展開的“帶形功能區”，它由若干個功能相關的組和命令所組成。

    功能區的優勢主要在于，它將通常需要使用菜單、工具欄、任務窗格和其他用戶界面組件才能顯示的任務或入口點集中在一起，便于在同一位置查找和調用功能相關的命令。

    五、Excel文件的管理

    Excel文件的管理主要包括新建、保存、關閉、打開、保密、備份、修改與刪除等工作。

    （一）Excel文件的新建與保存

    1.Excel文件的新建

    單擊“開始”菜單中列示的 Excel 快捷命令、桌面或任務欄中 Excel 的快捷方式圖標或者通過“運行”對話框等方式啟動Excel軟件的，系統將自動建立一個新的空白工作簿，或者提供一系列模板以供選擇，選定其中的空白工作簿模板后，新的空白工作簿窗口將在屏幕上呈現出來，并在標題欄中顯示默認的文件名。

    以打開現成 Excel文件方式啟動Excel軟件的，可通過以下方法之一建立一個新的空白工作簿：1）按擊快捷鍵“Ctrl＋N”鍵；（2）打開“文件”菜單，點擊“新建”菜單命令，選定其中的空白工作簿模板；（3）點擊工具欄中的“新建”按鈕（Excel 2003 為常用工具欄，Excel 2013 為快速訪問工具欄）。

    2.Excel文件的保存

    為了繼續使用新建的 Excel文件，應當以合適的名稱和
類型將 Excel 文件保存在適當的位置。Excel 文件在編輯修改完畢或退出 Excel 軟件之前，均應進行保存。保存 Excel文件的常用方法包括：

    （1）通過敲擊功能鍵“F12”鍵進行保存。

    （2）通過按擊快捷鍵“Ctrl＋S”鍵進行保存。對于之前已經保存過的文件，按擊快捷鍵“Ctrl＋S”鍵后，將直接保存最近一次的修改，不再彈出“另存為”對話框。

    （3）通過單擊常用工具欄（適用于Excel 2003）或快速訪問工具欄（適用于 Excel 2013）中的“保存”或“另存為”按鈕進行保存。

    （4）通過“文件”菜單（或Excel 2003“工具欄”菜單）中的“保存”或“另存為”命令進行保存。

    為了避免Excel軟件意外中止而丟失大量尚未保存的信息，系統通常會默認保存自動恢復信息的時間間隔，這一時間間隔還可以自定義。

    （二）Excel文件的關閉與打開

    1.Excel文件的關閉

    Excel 軟件退出前必須關閉打開的文件，因此，也可以采用前述三種Excel軟件的退出方法來關閉處于打開狀態的文件。此外，還可采用以下方法來關閉處于打開狀態的Excel文件：

    （1）點擊“工具欄”中的“關閉”按鈕或命令。

    Excel 2013中由于沒有“工具欄”菜單，但可點擊快速訪問工具欄中的“關閉”按鈕。

    （2）點擊“文件”菜單中的“關閉”命令。

    （3）按擊快捷鍵“Ctrl＋F4”。

    上述三種方法關閉的均是當前文件，其他處于打開狀態的 Excel 文件仍處于打開狀態，Excel 軟件仍在運行，并可通過按擊“Ctrl＋N”鍵等方式創建新工作簿。

    2.Excel文件的打開

    打開Excel文件的方法主要有：

    （1）通過直接點擊Excel文件打開。

    （2）通過快捷菜單中“打開”命令打開。

    （3）通過Excel“文件”菜單中的“打開”命令進行打開。

    （4）通過常用工具欄（適用于Excel 2003）或快速訪問工具欄（適用于Excel 2013）中的“打開”按鈕進行打開。

    （5）通過按擊快捷鍵“Ctrl＋O”（字母 O 的按鍵）進行打開。

    （三）Excel文件的保密與備份

    1.Excel文件的保密

    對于設置了打開權限密碼的 Excel文件，只有輸入正確的密碼才能打開。對于設置了修改權限密碼的Excel文件，只有輸入正確的密碼才能修改，否則只能以只讀方式打開。

    2.Excel文件的備份

    Excel 軟件根據原文件自動創建備份文件的名稱為原文件名后加上“的備份”字樣，圖標與原文件不同。

    （四）Excel文件的修改與刪除

    1.Excel文件的修改

    Excel 文件的修改通常在已打開的 Excel 文件中進行，包括修改單元格內容、增刪單元格和行列、調整單元格和行列的順序、增刪工作表和調整工作表順序等。

    2.Excel文件的刪除

    Excel文件的刪除方法包括：

    （1）選中要刪除的Excel文件，按擊“Delete”鍵進行刪除。

    （2）鼠標右鍵點擊要刪除的 Excel 文件，選擇刪除命令。

    第二節  數據的輸入與編輯

    一、數據的輸入

    （一）數據的手工錄入

    Excel 中，數據的輸入和修改都在當前單元格或者對應的編輯欄中進行。Excel 文件打開后，所有單元格均默認處于就緒狀態，等待數據的輸入。

    1.在單個單元格中錄入數據

    選定目標單元格，錄入所需的數字或文本。

    2.在單張工作表的多個單元格中快速錄入完全相同的數據

    選定單元格區域，在當前活動單元格或者對應的編輯欄中錄入所需的數字或文本，通過組合鍵“Ctrl＋Enter”確認錄入的內容。

    3.在單張工作表的多個單元格中快速錄入部分相同的數據

    相關設置完成后，在相應的單元格輸入數據時，只需要輸入不重復的數字部分，系統會在輸入的數字前自動加上重復部分。

    4.在工作組的一個單元格或多個單元格中快速錄入相同的數據

    可將工作簿中多張工作表組合成工作組。在目標單元格，如同按照在單個單元格中錄入數據的方法錄入相關數據；在一個單元格區域，如同按照在單張工作表的多個單元格中錄入相同數據的方法錄入相關數據。完成數據錄入后，可采用以下方法取消工作組：

    （1）單擊所在工作簿中其他未被選中的工作表標簽（即組外工作表標簽），如果該工作組包含工作簿中的所有工作表，則只需單擊活動工作表以外的任意一個工作表標簽.
    （2）指向該工作簿任意一個工作表標簽，單擊右鍵，從彈出的快捷菜單中選定“取消成組工作表”。

    （二）單元格數據的快速填充

    1.相同數據的填充

    某單元格的內容需要復制到其他單元格時，通常可點擊該單元格右下角的填充柄，鼠標箭頭隨之變為黑十字形，按住鼠標左鍵向上下左右的任一方向拖動，然后松開鼠標左鍵，該單元格的內容即被填充到相關單元格。

    2.序列的填充

    序列是指按照某種規律排列的一列數據，如等差數列、等比數列等。使用填充柄可自動根據已填入的數據填充序列的其他數據。使用填充序列的操作步驟是：

    （1）在需要輸入序列的第一個單元格中輸入序列第一個數或文本內容，緊接第二個單元格輸入序列第二個數或文本內容；

    （2）選中上述兩個單元格，點擊第二個單元格右下角的填充柄，按住鼠標左鍵拖動，在適當的位置釋放鼠標，拖過的單元格將會自動進行填充。

    3.填充序列類型的指定

    利用自動填充功能填充序列后，可以指定序列類型，如填充日期值時，可以指定按月填充、按年填充或者按日填充等。

    拖動填充柄并釋放鼠標時，鼠標箭頭附近出現“自動填充選項”按鈕，單擊該按鈕打開下拉菜單以選擇填充序列的類型。

    （三）導入其他數據庫的數據

    EXCEL可以獲取SQL Server、ACCESS等數據庫的數據，實現與小型數據庫管理系統的交互。

    二、數據的編輯

    （一）數據的復制和剪切

    1.數據的復制和粘貼

    Excel中，可以使用“粘貼”命令粘貼復制的內容，還可以使用“選擇性粘貼”命令有選擇地粘貼剪貼板中的數值、格式、公式、批注等內容。

    2.數據的剪切與粘貼

    數據的剪切與復制不同。數據復制后，原單元格中的數據仍然存在，目標單元格中同時還增加原單元格中的數據；數據剪切后，原單元格中數據不復存在，只在目標單元格中增加原單元格中的數據。

    （二）數據的查找和替換

    1.查找和替換特定數據

    如果只需要查找，單擊“查找下一個”逐個查找或單擊“查找全部”一次性全文查找；如果需要替換，單擊“替換”逐個替換或單擊“全部替換”一次性全部替換。

    2.選中包含公式的單元格

    依次單擊“編輯”、“查找和選擇”、“公式”，選中工作簿中所有包含公式的單元格。

    3.替換格式

    進行相應格式設置后單擊確定回到“查找與替換”對話框，單擊“全部替換”即完成對內容和格式的批量替換。

    三、數據的保護

    （一）保護工作簿

    Excel 可以為重要的工作簿設置保護，限制進行相應的操作。

    1.限制編輯權限

    工作簿被保護后所有的操作都不可進行。如果要撤銷保護工作簿，按設置保護工作簿的路徑選擇“保護工作簿”，輸入正確的密碼后可撤銷保護。

    2.設置工作簿打開權限密碼

    設置密碼完成后，當再次打開工作簿時需要輸入正確的密碼才能打開。

    （二）保護工作表

    在Excel 2013中，可以對工作表進行編輯權限設定，限制他人對工作表的編輯權限，如插入行、插入列等。取消權限保護需輸入正確的密碼。

    如果要撤銷保護工作表，按設置保護工作簿的路徑選擇

“保護工作表”，正確輸入取消工作表保護時使用的密碼后可撤銷保護。

    （三）鎖定單元格

    鎖定單元格可以使單元格的內容不能被修改，使用“鎖定單元格”功能必須啟用保護工作表功能。

    第三節  公式與函數的應用

    一、公式的應用

    （一）公式的概念及其構成

    公式是指由等號“=”、運算體和運算符在單元格中按特定順序連接而成的運算表達式。運算體是指能夠運算的數據或者數據所在單元格的地址名稱、函數等；運算符是使 Excel自動執行特定運算的符號。Excel 中，運算符主要有四種類型：算術運算符、比較運算符、文本運算符和引用運算符。

    Excel中，公式總是以等號“=”開始，以運算體結束，相鄰的兩個運算體之間必須使用能夠正確表達二者運算關系的運算符進行連接。即公式的完整表達式按以下方式依次構成：等號“=”、第一個運算體、第一個運算符、第二個運算體，以下類推，直至最后一個運算體。

    （二）公式的創建與修改

    1.公式的創建

    Excel 中，創建公式的方式包括手動輸入和移動點擊輸入。

    手動輸入公式時如有小圓括號，應注意其位置是否適當以及左括號是否與右括號相匹配。

    當輸入的公式中含有其他單元格的數值時，為了避免重復輸入費時甚至出錯，還可以通過移動鼠標去單擊擬輸入數值所在單元格的地址（即引用單元格的數值）來創建公式。

    移動點擊輸入數值所在單元格的地址后，單元格將處于“數據點模式”。

    2.公式的編輯和修改

    公式編輯和修改的方法有：

    （1）雙擊公式所在的單元格直接在單元格內修改內容。

    （2）選中公式所在的單元格，按下“F2”建后直接在單元格內更改內容。

    （3）選中公式所在的單元格后單擊公式編輯欄，在公式編輯欄中作相應更改。

    需注意的是，在編輯或者移動點擊輸入公式時，不能隨便移動方向鍵或者單擊公式所在單元格以外的單元格，否則單元格內光標移動之前的位置將自動輸入所移至單元格的地址名稱。

    （三）公式的運算次序

    對于只由一個運算符或者多個優先級次相同的運算符（如既有加號又有減號）構成的公式，Excel 將按照從左到
右的順序自動進行智能運算；但對于由多個優先級次不同的運算符構成的公式，Excel 則將自動按照公式中運算符優先級次從高到低進行智能運算。

    為了改變運算優先順序，應將公式中需要最先計算的部分使用一對左右小圓括號括起來，但不能使用中括號。公式中左右小圓括號的對數超過一對時，Excel 將自動按照從內向外的順序進行計算。

    （四）公式運算結果的顯示

    Excel 根據公式自動進行智能運算的結果默認顯示在該公式所在的單元格里，編輯欄則相應顯示公式表達式的完整內容。該單元格處于編輯狀態時，單元格也將顯示等號“=”及其運算體和運算符，與所對應編輯欄顯示的內容相一致。

    1.查看公式中某步驟的運算結果

    單元格中默認顯示的運算結果是根據完整的公式表達式進行運算的結果，但可通過下述方法查看公式中某步驟的運算結果：

    （1）選中公式所在的單元格，雙擊或按“F2”鍵進入編輯狀態；

    （2）選中公式中需要查看其運算結果的運算體和運算符，按“F9”鍵后，被選中的內容將轉化為運算結果，該運算結果同時處于被選中狀態。

    在運算結果處于被選中狀態下，如果按下確認鍵或者移

動光標鍵，公式中參與運算的運算體和運算符將不復存在，而被該結果所替代；如果移動鼠標去點擊其他單元格，公式所在單元格將由編輯狀態切換成數據點狀態，公式所在單元格里同時顯示被選中單元格的地址或名稱。

    （3）按下“Esc”鍵或者“Ctrl＋Z”組合鍵（或單擊“撤消”按鈕），運算結果將恢復為公式表達式的原來內容。

    2.公式默認顯示方式的改變

    為了檢查公式整體或者其中某一組成部分的表述是否正確，可以通過下述方法使單元格默認顯示完整的公式表達式，實現公式表達式與運算結果之間的便捷切換。

    （1）在單元格顯示運行結果時，選中單元格，按下“Ctrl＋`”組合鍵或者點擊“顯示公式”（適用于 Excel 2013）菜單命令，可切換為顯示公式內容。

    （2）在單元格顯示公式內容時，選中單元格，按下“Ctrl＋`”組合鍵或者點擊“顯示公式”（適用于 Excel 2013）菜單命令，或者點擊“公式審核模式”（適用于Excel 2003）菜單命令，可切換為顯示運行結果。

    3.將公式運算結果轉換為數值

    采用復制粘貼的方法將公式原地復制后，進行選擇性粘貼，但只粘貼數值。

    二、單元格的引用

    單元格引用是指在不同單元格之間建立鏈接，以引用來

自其他單元格的數據。引用的作用在于標識工作表上的單元格或單元格區域，并指明公式中所使用的數據的位置。

    通過引用，可以在公式中使用工作表不同部分的數據，或者在多個公式中使用同一單元格的數值，常用的單元格引用分為相對引用、絕對引用和混合引用三種。此外還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格、甚至其他應用程序中的數據。

    （一）引用的類型

    1.相對引用

    如果公式使用的是相對引用，公式記憶的是源數據所在單元格與引用源數據的單元格的相對位置，當復制使用了相對引用的公式到別的單元格式，被粘貼公式中的引用將自動更新，數據源將指向與當前公式所在單元格位置相對應的單元格。在相對引用中，所引用的單元格地址的列坐標和行坐標前面沒有任何標示符號。Excel 默認使用的單元格引用是相對引用。

    2.絕對引用

    如果公式使用的是絕對引用，公式記憶的是源數據所在單元格在工作表中的絕對位置，當復制使用了絕對引用的公式到別的單元格式，被粘貼公式中的引用不會更新，數據源仍然指向原來的單元格。在絕對引用中，所引用的單元格地址的列坐標和行坐標前面分別加入標示符號“$”。如果要使復

制公式時數據源的位置不發生改變，應當使用絕對引用。

    3.混合引用

    混合引用是指所引用單元格地址的行標與列標中只有一個是相對的，可以發生變動，而另一個是絕對的。

    （二）輸入單元格引用

    在公式中可以直接輸入單元格的地址引用單元格，也可以使用鼠標或鍵盤的方向鍵選擇單元格。單元格地址輸入后，通常使用以下兩種方法來改變引用的類型：

    1.在單元格地址的列標和行標前直接輸入“$”符號。

    2.輸入完單元格地址后，重復按“F4”鍵選擇合適的引用類型。

    （三）跨工作表單元格引用

    跨工作表單元格引用是指引用同一工作簿里其他工作表中的單元格，又稱三維引用，需要按照以下格式進行跨表引用：

    工作表名！數據源所在單元格地址

    （四）跨工作簿單元格引用

    跨工作簿單元格引用是指引用其他工作簿中的單元格，又稱外部引用，需要按照以下格式進行跨工作簿引用：

    [工作簿名]工作表名！數據源所在單元格地址

    三、函數的應用

    在Excel中，利用函數可以快速執行有關計算。

    函數的基本格式是：函數名（參數序列）。參數序列是用于限定函數運算的各個參數，這些參數除中文外都必須使用英文半角字符。函數只能出現在公式中。

    （一）常用函數

    1.統計函數

    （1）MAX

    MAX（number1，number2，……）用于返回數值參數中的最大值，忽略參數中的邏輯值和文本。

    （2）MIN

    MIN（number1，number2，……）用于返回數值參數中的最小值，忽略參數中的邏輯值和文本。

    （3）SUM

    SUM（number1，number2，……）用于計算單元格區域中所有數值的和。

    （4）SUMIF

    SUMIF（range，criteria，sum_range）用于對滿足條件的單元格求和。

    （5）AVERAGE

    AVERAGE（number1，number2，……）用于返回參數的算術平均值。

    （6）AVERAGEIF

    AVERAGEIF（range，criteria，average_range）用于

返回某個區域內滿足給定條件的所有單元格的算術平均值。

    （7）COUNT

    COUNT（value1，value2，……）用于計算包含數字的單元格以及參數列表中數字的個數。

    （8）COUNTIF

    COUNTIF（range，criteria）用于對區域中滿足單個指定條件的單元格進行計數。

    2.文本函數

    （1）LEN

    LEN（text）用于返回文本字符串中的字符數。

    （2）RIGHT

    RIGHT（text，num_chars）用于從文本字符串中最后一個字符開始返回指定個數的字符。

    （3）MID

    MID（text，start_num，num_chars）用于返回文本字符串中從指定位置開始的指定數目的字符。

    （4）LEFT

    LEFT（text，num_chars）用于返回文本字符串中第一個字符開始至指定個數的字符。

    3.邏輯函數IF

    IF（logical_test，value_if_true，value_if_false）用于判斷“logical_test”的內容是否為真，如果為真則返回

    104

“value_if_true”，如果為假則返回“value_if_false”的內容。

    4.查找與引用函數

    （1）LOOKUP

    LOOKUP  函數用于返回向量(單行區域或單列區域)或數組中的數值。它具有兩種語法形式：向量形式和數組形式。

    向量形式：LOOKUP（lookup_value，lookup_vector，result_vector）用于在單行區域或單列區域（稱為“向量”）中查找值，然后返回第二個單行區域或單列區域中相同位置的值。

    數組形式: LOOKUP（lookup_value，array）用于在數組的第一行或第一列中查找指定的值，并返回數組最后一行或最后一列內同一位置的值。數組是指用于建立可生成多個結果或可對在行和列中排列的一組參數進行運算的單個公式。數組區域共用一個公式；數組常量是用作參數的一組常量。

    （2）INDEX

    INDEX（array，row_num，column_num）用于返回表格或數組中的元素值，此元素由行號和列號的索引值給定。

    （3）MATCH

    MATCH（lookup_value，lookup_array，match_type）用于在單元格區域中搜索指定項，然后返回該項在單元格區域中的相對位置。

    5.日期與時間函數

    （1）YEAR

    YEAR（serial_number）用于返回某日期對應的年份。

    （2）MONTH

    MONTH（serial_number）用于返回某日期對應的月份，介于 1到12之間。

    （3）DAY

    DAY（serial_number）用于返回某日期對應的天數，介于1到31之間。

    （4）NOW

    NOW（）用于返回當前的日期和時間。

    （二）基本財務函數

    1.SLN

    SLN（cost，salvage，life）用于返回某項資產以直線法計提的每一期的折舊值。

    cost 是必需參數，指固定資產原值。salvage 是必需參數，指固定資產的殘值。life 是必需參數，指固定資產的折舊期數。

    2.DDB

    DDB（cost，salvage，life，period，factor）用于使用雙倍余額遞減法或其他指定的方法，計算一項固定資產在給定期間內的折舊值。

    cost 是必需參數，指固定資產原值。salvage 是必需參數，指固定資產的殘值。life 是必需參數，指固定資產的折舊期數。period 是必需參數，指需要計算折舊值的期間。period 必須使用與 life 相同的單位。factor 是可選參數，指余額遞減速率。如果 factor被省略，則默認為 2，即使用雙倍余額遞減法。

    3.SYD

    SYD（cost，salvage，life，per）用于返回某項資產按年數總和折舊法計算的在第“per”期的折舊值。

    cost 是必需參數，指固定資產原值。salvage 是必需參數，指固定資產的殘值。life 是必需參數，指固定資產的折舊期數。per 是必需參數，指第幾期，其單位必須與 life 相同。

    第四節  數據清單及其管理分析

    一、數據清單的構建

    （一）數據清單的概念

    Excel中，數據庫是通過數據清單或列表來實現的。

    數據清單是一種包含一行列標題和多行數據且每行同列數據的類型和格式完全相同的 Excel工作表。

    數據清單中的列對應數據庫中的字段，列標志對應數據庫中的字段名稱，每一行對應數據庫中的一條記錄。
    （二）構建數據清單的要求

    為了使 Excel自動將數據清單當作數據庫，構建數據清單的要求主要有：

    1.列標志應位于數據清單的第一行，用以查找和組織數據、創建報告。

    2.同一列中各行數據項的類型和格式應當完全相同。

    3.避免在數據清單中間放置空白的行或列，但需將數據清單和其他數據隔開時，應在它們之間留出至少一個空白的行或列。

    4.盡量在一張工作表上建立一個數據清單。

    二、記錄單的使用

    （一）記錄單的概念

    記錄單又稱數據記錄單，是快速添加、查找、修改或刪除數據清單中相關記錄的對話框。

    （二）通過記錄單處理數據清單的記錄

    1.通過記錄單處理記錄的優點

    通過記錄單處理記錄的優點主要有：界面直觀，操作簡單，減少數據處理時行列位置的來回切換，避免輸入錯誤，特別適用于大型數據清單中記錄的核對、添加、查找、修改或刪除。

    2.“記錄單”對話框的打開

    打開“記錄單”對話框的方法是：輸入數據清單的列標志

后，選中數據清單的任意一個單元格，點擊“數據”菜單中的“記錄單”命令。

    Excel 2013的數據功能區中盡管沒有“記錄單”命令，但可通過單擊以自定義方式添入“快速訪問工具欄”中的“記錄單”按鈕來打開。

    “記錄單”對話框打開后，只能通過“記錄單”對話框來輸入、查詢、核對、修改或者刪除數據清單中的相關數據，但無法直接在工作表的數據清單中進行相應的操作。

    3.在“記錄單”對話框中輸入新記錄

    在數據錄入過程中，如果發現某個文本框中的數據錄入有誤，可將光標移入該文本框，直接進行修改；如果發現多個文本框中的數據錄入有誤，不便逐一修改，可通過單擊“還原”按鈕放棄本次確認前的所有輸入，光標將自動移入第一個空白文本框，等待數據錄入。

    4.利用“記錄單”對話框查找特定單元格

    通過查詢，符合條件的記錄將分別出現在對話框相應列后的文本框中，“記錄狀態”顯示區相應顯示記錄的次序數以及數據清單中記錄的總條數。這種方法尤其適合于具有多個查詢條件的查詢中，只要在對話框多個列名后的文本框內同時輸入相應的查詢條件即可。

    5.利用“記錄單”對話框核對或修改特定記錄

    查找到待核對或修改的記錄后，在對話框相應列后文本

框中逐一核對或修改。在確認修改前，“還原”按鈕處于激活狀態，可通過單擊“還原”按鈕放棄本次確認前的所有修改。

    6.利用“記錄單”對話框刪除特定記錄

    記錄刪除后無法通過單擊“還原”按鈕來撤銷。

    三、數據的管理與分析

    在數據清單下，可以執行排序、篩選、分類匯總、插入圖表和數據透視表等數據管理和分析功能。

    （一）數據的排序

    數據的排序是指在數據清單中，針對某些列的數據，通過“數據”菜單或功能區中的排序命令來重新組織行的順序。

    1.快速排序

    使用快速排序的操作步驟為：

    （1）在數據清單中選定需要排序的各行記錄；

    （2）執行工具欄或功能區中的排序命令。

    需要注意的是，如果數據清單由單列組成，即使不執行第一步，只要選定該數據清單的任意單元格，直接執行第二步，系統都會自動排序；如果數據清單由多列組成，應避免不執行第一步而直接執行第二步的操作，否則數據清單中光標所在列的各行數據被自動排序，但每一記錄在其他各列的數據并未隨之相應調整，記錄將會出現錯行的錯誤。

    2.自定義排序

    使用自定義排序的操作步驟為：

    （1）在“數據”菜單或功能區中打開“排序”對話框；

    （2）在“排序”對話框中選定排序的條件、依據和次序。

    （二）數據的篩選

    數據的篩選是指利用“數據”菜單中的“篩選”命令對數據清單中的指定數據進行查找和其他工作。

    篩選后的數據清單僅顯示那些包含了某一特定值或符合一組條件的行，暫時隱藏其他行。通過篩選工作表中的信息，用戶可以快速查找數值。用戶不但可以利用篩選功能控制需要顯示的內容，而且還能夠控制需要排除的內容。

    1.快速篩選

    使用快速篩選的操作步驟為：

    （1）在數據清單中選定任意單元格或需要篩選的列；

    （2）執行“數據”菜單或功能區中的“篩選”命令，第一行的列標識單元格右下角出現向下的三角圖標；

    （3）單擊適當列的第一行，在彈出的下拉列表中取消勾選“全選”，勾選篩選條件，單擊“確定”按鈕可篩選出滿足條件的記錄。

    2.高級篩選

    使用高級篩選的操作步驟為：

    （1）編輯條件區域；

    （2）打開“高級篩選”對話框；

    （3）選定或輸入“列表區域”和“條件區域”，單擊“確定”按鈕。

    3.清除篩選

    對經過篩選后的數據清單進行第二次篩選時，之前的篩選將被清除。

    （三）數據的分類匯總

    數據的分類匯總是指在數據清單中按照不同類別對數據進行匯總統計。分類匯總采用分級顯示的方式顯示數據，可以收縮或展開工作表的行數據或列數據，實現各種匯總統計。

    1.創建分類匯總

    需設置采用的“匯總方式”和“選定匯總項”的內容，數據清單將以選定的“匯總方式”按照“分類字段”分類統計，將統計結果記錄到選定的“選定匯總項”列下，同時可以通過單擊級別序號實現分級查看匯總結果。

    2.清除分類匯總

    打開“分類匯總”對話框后，單擊“全部刪除”按鈕即可取消分類匯總。

    （四）數據透視表的插入

    數據透視表是根據特定數據源生成的，可以動態改變其版面布局的交互式匯總表格。數據透視表不僅能夠按照改變

后的版面布局自動重新計算數據，而且能夠根據更改后的原始數據或數據源來刷新計算結果。

    1.數據透視表的創建

    單擊“數據”菜單中的“數據透視表和數據透視圖…”命令項，接著按“數據透視表和數據透視圖向導”提示進行相關操作可創建數據透視表。

    數據透視表的布局框架由頁字段、行字段、列字段和數據項等要素構成，可以通過需要選擇不同的頁字段、行字段、列字段，設計出不同結構的數據透視表。

    2.數據透視表的設置

    （1）重新設計版面布局。在數據透視表布局框架中選定已拖入的字段、數據項，將其拖出，將“數據透視表字段列表”中的字段和數據項重新拖至數據透視表框架中的適當位置，報表的版面布局立即自動更新。

    （2）設置值的匯總依據。值的匯總依據有求和、計數、平均值、最大值、最小值、乘積、數值計數、標準偏差、總體偏差、方差和總體方差。

    （3）設置值的顯示方式。值的顯示方式有無計算、百分比、升序排列、降序排列等。

    （4）進行數據的篩選。分別對報表的行和列進行數據的篩選，系統會根據條件自行篩選出符合條件的數據列表。

    （5）設定報表樣式。數據透視表中，既可通過單擊“自

動套用格式”（適用于Excel 2003，單擊“格式”菜單后進入）或“套用報表格式”（適用于Excel 2013）按鈕選用系統自帶的各種報表樣式，也可通過設置單元格格式的方法自定義報表樣式。

    （五）圖表的插入

    框選需要生成圖表的數據清單、列表或者數據透視表，選擇“插入”菜單中的“圖表”菜單，按照相關步驟操作可完成圖表的插入。

    圖表不僅可以根據需要分別輸入標題和各軸所代表的數據含義，而且可以適當調整大小及其位置。