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

1.章節內容：

2.考情分析：

各種題型都有可能出現，預計考試分值在20分左右。

3.學習方法：

本章主要為Excel的一般操作知識，在日常的工作生活中有比較多的涉及，所以在學習起來較為親切，容易接受。由于考試題型的不確定性，仍要求大家重視動手操作。本章與第三章報表管理模塊聯系較為密切，尤其是公式與函數的應用，要結合起來融會貫通學習。

第一節電子表格軟件概述

一、常用的電子表格軟件（★）

Windows操作系統：微軟Excel、金山WPS、LotusNotes、

FormulaOne等Mac操作系統：Numbers

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

1.建立工作簿

2.管理數據

3.實現數據網上共享

4.制作圖表

5.開發應用系統

（一）建立工作簿

每個工作簿含有一張或多張工作表。

單元格是工作表的最小組成單位。每一單元格最多可容納32000個字符。

版本默認工作表數量行數列數（二）管理數據

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

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

（四）制作圖表

散點圖、柱形圖、餅圖、條形圖、面積圖、折線圖、氣泡圖、三維圖等14類100多種基本圖表。（五）開發應用系統

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

【例題·單選題】在Excel2013中，一個工作表由（）組成。

A.65536行和256列

B.65536行和16384列

C.1048576行和16384列

D.1048576行和256列

『正確答案』C

『答案解析』在Excel2013中，行數=256×642，列數=256×64【例題·多選題】電子表格軟件的主要功能有（）。

A.建立工作簿

B.管理數據

C.實現數據網上共享

D.制作圖表

『正確答案』ABCD

『答案解析』電子表格軟件的主要功能有建立工作簿、管理數據、實現數據網上共享、制作圖表、開發應用系統。三、Excel軟件的啟動與退出（★★）

（一）Excel軟件的啟動

1.通過“開始”菜單中的Excel快捷命令啟動

2.通過桌面或任務欄中的快捷方式啟動

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

4.通過打開已有Excel文件啟動

（二）Excel軟件的退出

1.通過標題欄的“關閉”按鈕退出

2.通過任務欄退出

3.通過快捷鍵退出：Alt+F44.雙擊標題欄的控制按鈕（Office按鈕）退出5.打開標題欄的控制按鈕（Office按鈕）點擊退出【例題·單選題】下列不屬于Excel軟件的退出方法的是（）。

A.通過標題欄的“關閉”按鈕退出

B.Alt+F4

C.雙擊標題欄的控制按鈕

D.Ctrl+F4

『正確答案』D

『答案解析』選項D，屬于關閉Excel文件的方法。四、Excel軟件的用戶界面（★★）

Excel2003及以下版本的默認用戶界面基本相同,由標題欄、菜單欄、工具欄、編輯區、工作表區、狀態欄和任務窗格等要素組成;

Excel2007及以上版本的默認用戶界面基本相同,主要由功能區、編輯區、工作表區和狀態欄等要素組成【提示】功能區將標題欄、菜單欄、工具欄和任務窗格進行了合并，非常方便操作。【提示】1.工具欄可分為常用工具欄和格式工具欄。

2.單元格是工作表中最基本的存儲單位。

3.如果需要打開任務窗格,可以選擇“視圖”菜單的“任務窗格”命令或使用快捷鍵Ctrl+F1將其打開。

4.如需隱藏功能區,可以在當前所在的選項卡處雙擊,再次雙擊將取消隱藏。在隱藏狀態下,可單擊某選項卡來查看功能區并選擇其中的命令。五、Excel文件的管理（★★）

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

1.Excel文件的新建

（1）啟動軟件時：自動建立新文件

（2）啟動軟件以后：

快捷鍵：“Ctrl+N”鍵

菜單欄：“文件”→“新建”

工具欄：“新建”按鈕2.Excel文件的保存

快捷鍵：“F12”鍵，另存為

“Ctrl+S”鍵，保存

工具欄：“保存”按鈕

菜單欄：“文件”→“保存”或“另存為”

自動保存

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

1.Excel文件的關閉

（1）退出Excel軟件，文件自動關閉

（2）不退出Excel軟件：

工具欄：“關閉”按鈕

菜單欄：“文件”→“關閉”

快捷鍵：“Ctrl+F4”或“Ctrl+W”【提示】注意與軟件退出快捷鍵（Alt+F4）的區別2.Excel文件的打開

（1）雙擊Excel文件打開

（2）菜單：

右鍵快捷菜單：“打開”

菜單欄：“文件”→“打開”

（3）工具欄：“打開”

（4）快捷鍵：“Ctrl+O”（三）Excel文件的保密與備份

1.Excel文件的保密

（1）打開權限密碼

（2）修改權限密碼

“另存為”→“工具”→“常規選項”

2.Excel文件的備份

保存時，自動生成備份文件：“原文件名+的備份”

圖標與原文件不同（四）Excel文件的修改與刪除

1.Excel文件的修改

修改單元格內容、增刪單元格和行列、調整單元格和行列的順序、增刪工作表和調整工作表順序等。

2.Excel文件的刪除

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

（2）鼠標右鍵點擊要刪除的Excel文件,選擇刪除命令。【例題·多選題】新建Excel文件的方法有（）。

A.使用任務欄中的新建圖標

B.使用文件菜單中的新建命令

C.使用工具欄中的新建命令

D.使用“Ctrl+O”快捷鍵

『正確答案』BC

『答案解析』選項A應為單擊常用工具欄的新建圖標。選項D是打開文件的方法，新建應該是“Ctrl+N”。【例題·多選題】打開Excel文件的方法有（）。

A.右鍵快捷菜單“打開”命令

B.快捷鍵“Shift+O”

C.Excel“文件”菜單中的“打開”命令

D.常用工具欄“打開”按鈕

『正確答案』ACD

『答案解析』選項B，應該是“Ctrl+O”。

第二節數據的輸入與編輯一、數據的輸入（★★★）

（一）數據的手工錄入

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

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

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

【例題·實務操作題】在Excel表A1:A5區域中分別輸入“中華會計網校特色班、中華會計網校精品班、中華會計網校實驗班、中華會計網校通關班、中華會計網校面授班”數據。

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

（1）按住Ctrl鍵,選取多張工作表,將其組合成工作組

（2）錄入數據，方法同1、2、3

（3）取消工作組：

①單擊組外任何一個工作表標簽；

②右擊組內任何一個工作標簽，選擇“取消成組工（二）單元格數據的快速填充

1.相同數據的填充：填充柄、Ctrl鍵的使用

【提示】數字型以及不具有增或減序可能的文字型數據，可直接拖動；而日期型以及具有增或減序可能的文字型數據，應按住Ctrl鍵拖動。

2.序列的填充：

（1）輸入第一、二個單元格數據。

（2）選中上述兩個單元格，拖動填充柄。

【提示】是否按Ctrl鍵，與相同數據的填充正好相反！

3.填充序列類型的指定：

“自動填充選項”按鈕

4.自定義排序

Excel2003：工具→選項→自定義序列

Excel2007：Office按鈕→Excel選項→常用→編輯自定義列表

Excel2013：文件→選項→高級→編輯自定義列表

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

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

功能區：數據→獲取外部數據

二、數據的編輯（★★★）

（一）數據的復制、剪切、粘貼

復制：Ctrl+C、剪切：Ctrl+X、粘貼：Ctrl+V

數據的剪切與復制不同。

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

數據剪切后，原單元格中數據不復存在，只在目標單元格中增加原單元格中的數據。

（二）數據的查找和替換

1.查找和替換特定數據

Ctrl+F（查找）、Ctrl+H（替換）

Excel中的通配符“?”代替任意單個字符,“*”代替多個任意字符。

例如：查詢“?k*”,表示前一個字符是任意字符,第二個字符是“k”,后面是任意個任意字符。

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

編輯→查找和選擇→公式

3.替換格式

點擊“選項”按鈕，可以完成格式的批量替換。

【例題·判斷題】Excel中的通配符“?”代替多個任意字符,“*”代替任意單個字符。（）

『正確答案』×

『答案解析』Excel中的通配符“?”代替任意單個字符,“*”代替多個任意字符。

三、數據的保護（★★★）

（一）保護工作簿

1.限制編輯權限：

設置兩項保護內容：結構、窗口

Excel2003：工具→保護→保護工作簿

Excel2007、2013：審閱→更改→保護工作簿

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

Excel2003：“另存為”→工具→常規選項

Excel2007、2013：左上角“Office或文件按鈕”

（二）保護工作表

Excel2003：工具→保護→保護工作表

Excel2007、2013：審閱→更改→保護工作表

·允許用戶編輯區域

在保護工作表情況下，可以設定允許用戶編輯區域。

（三）鎖定單元格

設置單元格格式→保護工作表→鎖定

【提示】鎖定單元格必須啟用保護工作表功能。

【例題·判斷題】保護工作簿必須啟用保護工作表功能。（）

『正確答案』×

『答案解析』保護工作簿可以單獨啟用。鎖定單元格必須啟用保護工作表功能。

【例題·多選題】Excel數據的保護包括（）。

A.保護工作簿

B.保護工作表

C.鎖定單元格

D.凍結窗格

『正確答案』ABC

『答案解析』凍結窗格不屬于數據的保護，只是視圖上的一個調整。當我們在制作一個Excel表格時，如果列數較多，行數也較多時，一旦向下滾屏，則上面的標題行也跟著滾動，在處理數據時往往難以分清各列數據對應的標題，這時利用“凍結窗格”功能。具體方法是將光標定位在要凍結的標題行（可以是一行或多行）的下一行，然后選擇“凍結窗格”即可。滾屏時，被凍結的標題行總是顯示在最上面，增強了表格編輯的直觀性。

第三節公式與函數的應用

一、公式的應用（★★★）（一）公式的概念及其構成

例如：=68+B2*3-SUM（C1:C5）

包含：=、運算體、運算符（公式總是以等號“=”開始）

1.運算體是指能夠運算的數據或者數據所在單元格的地址名稱、函數等。上例中68、B2、SUM（C1:C5）

2.運算符是使Excel自動執行特定運算的符號。Excel中,運算符主要有四種類型:算術運算符、比較運算符、文本運算符、引用運算符。（1）算術運算符:可以完成基本的數學運算,包括:“+”（加）、“-”（減）、“*”（乘）、“/”（除）、“^”（乘方）等,運算的結果為數值。（2）比較運算符:可以比較兩個同類型的數據（都是數值或都是字符或都是日期）,包括:“=”（等于）、“>”（大于）、“<”（小于）、“>=”（大于等于）、“<=”（小于等于）、“<>”（不等于）,運算的結果為邏輯值TRUE或FALSE。（3）文本運算符:“&”（連接運算符）,用于把前后兩個字符串連接在一起,生成一個字符串。算術運算符和文本運算符優于比較運算。

（4）引用運算符:是Excel特有的運算符,用于單元格引用,可以將單元格區域合并運算,包括:

①區域運算符“:”（冒號）:產生對包括在兩個引用之間的所有單元格的引用,上例中SUM（C1:C5）。

②聯合運算符“,”（逗號）:將多個引用合并為一個引用,即取多個區域的并集如SUM（A1:A5,C1:C5）。

③交叉運算符“”（空格）:產生對多個引用共有的單元格的引用,即多個區域的交集,如

SUM（A1:B5A4:D9）,相當于SUM（A4:B5）。（二）公式的創建與修改

1.公式的創建

手動輸入、移動點擊輸入

編輯欄顯示公式單元格顯示計算結果

【鏈接】報表管理模塊中公式的錄入，需要在“顯示公式”或“格式”狀態下。

2.公式的編輯

快捷鍵：F2（三）公式的運算次序

1.優先級次相同的運算符：

從左至右運算

2.多個優先級次不同的運算符：

從高至低運算

比如小括號、乘號等優先級較高。（四）公式運算結果的顯示

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

快捷鍵：F9（查看）、“Esc或Ctrl+Z”（恢復）

【提示】先按F2進入公式編輯狀態，再按F9查看。

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

快捷鍵：Ctrl+`（數字1左邊的按鍵）

功能區：公式→顯示公式

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

復制→選擇性粘貼

【例題·單選題】下列屬于區域運算符的是（）。

A.“/”B.“&”

C.“:”D.“,”

『正確答案』C

『答案解析』選項A為算術運算符；選項B為文本運算符；選項D為聯合運算符。

二、單元格的引用（★★★）例如：=68+B2*3-SUM（C1:C5）（一）引用的類型

1.相對引用：Excel默認使用的單元格引用

2.絕對引用例如：=68+$B$2*3-SUM（C1:C5）

3.混合引用例如：=68+$B2*3-SUM（C1:C5）【例題·實務操作題】如圖所示，計算學生的總成績。（二）輸入單元格引用

1.在列標和行標前直接輸入“$”符號。

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

【例題·實務操作題】把圖中D2的單元格公式改=$B$2+$C$2”,然后將公式復制到單元格D3中,D3中的值與D2相同,引用地址沒有改變。

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

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

例如：=68+Sheet1!$B$2*3-SUM（C1:C5）

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

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

例如：=68+[Book2]Sheet1!$B$2*3-SUM（C1:C5）

三、函數的應用（★★★）

基本格式：函數名（參數序列）

函數只能出現在公式中。除中文外都必須使用英文半角字符,參數無大小寫之分。

【提示】重點把握參數的含義和運用。

（一）常用函數

1.統計函數

（1）MAX（number1,number2,……）

（2）MIN（number1,number2,……）

MAX/MIN函數忽略參數中的邏輯值和文本。

（3）SUM（number1,number2,……）

數值、邏輯值、文本數字、單元格和區域的引用地址。例如：=SUM（“1”,2,TRUE）=4

（4）SUMIF（range,criteria,sum_range）

對于滿足條件的單元格求和

（5）AVERAGE（number1,number2,……）

（6）AVERAGEIF（range,criteria,average_range）（7）COUNT（value1,value2,……）

數字、空單元格、邏輯值、日期或文本數字

（8）COUNTIF（range,criteria）

例如,要在B1:B10單元格范圍內統計數值在80～90之間的人數,表達式應該是“=countif（B1:B10,">=80"）-countif

（B1:B10,">90"）”

2.文本函數

（1）LEN（text）

用于返回文本字符串中的字符數。

例如：=LEN（“中華會計網校”）=6

（2）RIGHT（text,num_chars）

用于從文本字符串中最后一個字符開始返回指定個數的字符。

例如：=RIGHT（“中華會計網校”,2）=“網校”

（3）MID（text,start_num,num_chars）

用于返回文本字符串中從指定位置開始的指定數目的字符。

例如：=MID（“中華會計網校”,3,4）=“會計網校”（4）LEFT（text,num_chars）

用于返回文本字符串中第一個字符開始至指定個數的字符。

例如：=LEFT（“中華會計網校”,4）=“中華會計”

3.邏輯函數IF

IF（logical_test,value_if_true,value_if_fa1se）

4.查找與引用函數

（1）LOOKUP

①向量形式

（lookup_value,lookup_vector,result_vector）

【提示】

如果函數LOOKUP在第二參數確定的區域中找不到與第一參數完全相同的值,則查找該區域中最接近第一參數且小于它的值。

如果第一參數小于第二參數確定的區域中的最小值,函數LOOKUP返回錯誤值“#N/A”。

②數組形式

LOOKUP（lookup_value,array）

用于在數組的第一行或第一列中查找指定的值,并返回數組最后一行或最后一列內同一位置的值。

如果lookup_value小于第一行或列（取決于數組維度）中的最小值,則LOOKUP會返回錯誤值。

例如：

=LOOKUP（5.2,{4.2,5,7,9,10}）=5,

=LOOKUP（1,{4.2,5,7,9,10}）=#N/A。

【提示】如果LOOKUP找不到lookup_value,它會使用該數組中小于或等于lookup_value的最大值。

2.INDEX（array,row_num,column_num）

用于返回表格或數組中的元素值,此元素由行號和列號的索引值給定。

例如：=INDEX（B1:E11,4,3）,確認后則顯示出B1至E11單元格區域中,第4行和第3列交叉處的單元格（即D4）中的內容。

3.MATCH（lookup_value,lookup_array,match_type）

用于在單元格區域中搜索指定項,然后返回該項在單元格區域中的相對位置。如果match_type為-1,查找大于或等于lookup_value的最小數值,lookup_array必須按降序排列；

如果match_type為0,查找等于lookup_value的第一個數值,lookup_array可以按任何順序排列；

如果match_type為1,查找小于或等于lookup_value的最大數值,lookup_array必須按升序排列；

如果省略match_type,則默認為1。

如圖所示,在F2中輸入“=MATCH（E2,B1:B11,0）”,則顯示的結果是8。

5.日期與時間函數

（1）YEAR（serial_number）

用于返回某日期對應的年份

例如：=YEAR（“1998/01/30”）=1998

（2）MONTH（serial_number）

用于返回某日期對應的月份

（3）DAY（serial_number）

用于返回某日期對應的天數

4.NOW（）

用于返回當前的日期和時間,該函數不需要參數。

【提示】日期有多種輸入方式,包括:帶引號的文本串（例如“1998/01/30”）、序列號（如表示1998年1月30日的35825）或其他公式或函數的結果（DATE（1998,1,30））等。

【例題·單選題】下列各項中屬于文本函數的是（）。

A.MAX

B.SUMIF

C.RIGHT

D.LOOKUP

『正確答案』C

『答案解析』MAX、SUMIF為統計函數；LOOKUP為查找與引用函數。

（二）基本財務函數

1.SLN

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

cost指固定資產原值;salvage指固定資產的殘值;life指固定資產的折舊期數。這三個參數都是必需參數。

2.DDB（cost,salvage,life,period,factor）

用于使用雙倍余額遞減法或其他指定的方法,計算一項固定資產在給定期間內的折舊值。

cost指固定資產原值;salvage指固定資產的殘值;life指固定資產的折舊期數;period指需要計算折舊值的期間,其單位必須與life相同。必選參數。

factor指余額遞減速率,是可選參數。默認為2，即雙倍余額遞減法。

3.SYD（cost,salvage,life,per）

用于返回某項資產按年數總和折舊法計算的在第“per”期的折舊值。

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

另外，常見的財務函數還有年金終值FV、年金現值PV、年金PMT、利率RATE、期數NPER等資金的時間價值函數，凈現值函數NPV，內含報酬率函數IRR第四節數據清單及其管理分析

一、數據清單的構建（★★★）

（一）數據清單的概念

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

數據清單是一種包含一行列標題和多行數據且每行同列數據的類型和格式完全相同的Excel工作表,例如工資表、應付賬款等。

數據清單中的列是數據庫中的字段,數據清單中的列標志是數據庫中的字段名稱,數據清單中的每一行對應數據庫中的一條記錄。

（二）構建數據清單的要求

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

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

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

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

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

【例題·單選題】下列關于Excel數據清單的說法中，不正確的是（）。

A.列標志應位于數據清單的第一行

B.數據清單中的列是數據庫中的字段

C.每一行對應數據庫中的一條記錄

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

『正確答案』D

『答案解析』同一列中各行數據項的類型和格式應當完全相同。

二、記錄單的使用（★★★）

（一）記錄單的概念

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

【提示】注意把握記錄單與數據清單的關系。

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

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

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

2.記錄單對話框的打開

Excel2003,“數據”菜單→“記錄單”

Excel2013,“快速訪問工具欄”→“記錄單”

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

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

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

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

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

【例題·判斷題】打開記錄單對話框以后，仍可以直接在工作表的數據清單中進行相應的操作。（）

『正確答案』×

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

三、數據的管理與分析（★★★）

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

（一）數據的排序

1.快速排序

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