excel在財務(wù)管理中的運(yùn)用第3章EXCEL的運(yùn)算與數(shù)據(jù)處理課件

Excel在財務(wù)管理中的應(yīng)用第3章EXCEL的運(yùn)算與數(shù)據(jù)處理學(xué)習(xí)目標(biāo)3.1EXCEL中的公式運(yùn)用3.2函數(shù)的使用3.3EXCEL的數(shù)據(jù)管理和分析3.4Excel圖表本章小結(jié)主要概念和觀念目錄學(xué)習(xí)目標(biāo)通過本章的學(xué)習(xí)，要求學(xué)生要掌握利用公式進(jìn)行數(shù)據(jù)計算的方法，掌握Excel2003的工作表、圖表和數(shù)據(jù)庫三大功能的操作方法和技巧等，熟悉常用函數(shù)的功能與使用方法，學(xué)會創(chuàng)建直觀、形象的統(tǒng)計圖表，能夠利用數(shù)據(jù)庫管理功能對數(shù)據(jù)進(jìn)行分析和統(tǒng)計管理。3.1.1公式中的運(yùn)算符算術(shù)運(yùn)行符:加號(+)、減號(-)、乘號(*)、除號(/)、百分比(%)、冪(∧)比較運(yùn)算符:等號(=)、大于號(>)、小于號(<)、大于等于號(>=)、小于等于號(<=)、不等于號(<>)文本連接符:&運(yùn)算符優(yōu)先級引用運(yùn)算符1算術(shù)運(yùn)算符2連接運(yùn)算符3比較運(yùn)算符43.1.2單元格的引用單元格地址:單元格引用:相對引用:指明公式或函數(shù)中使用了哪些單元格中的數(shù)據(jù),可以是單元格地址和單元格區(qū)域地址(左上角單元格地址:右下角單元格地址),例如（B4:F8）列號(A,B,C,……)+行號(1,2,3,……)；分為相對地址(A1,A2,B3,C4……)和絕對地址($A$1,$A$2,$B$3,$C$4,……)在公式中使用相對地址進(jìn)行引用，當(dāng)復(fù)制和移動公式到新的位置時，公式中引用的單元格地址回自動調(diào)節(jié)引用的地址。如SUM（C1：C4）。絕對引用混合引用在公式中使用絕對地址進(jìn)行引用，當(dāng)復(fù)制和移動公式到新的位置時，公式中引用的單元格地址不會發(fā)生變化。單元格地址列標(biāo)和行號前帶有“$”符號，如SUM（$C$1：$C$4）。在單元格地址中既有相對引用又有絕對引用，當(dāng)復(fù)制和移動公式到新的位置時，相對引用部分會發(fā)生變化，而絕對引用部分不變。單元格地址的列標(biāo)或行號中，有一個帶有“$”，如SUM（C$1：C$4）。

3.1.3公式的輸入、修改、移動與復(fù)制輸入:公式可直接在單元格中或在編輯欄中輸入在公式中如需使用函數(shù)，可單擊編輯欄左端框旁的向下箭頭，從彈出的常用函數(shù)列表中選定所需要的函數(shù)。如果常用函數(shù)列表中沒有所需要的函數(shù)，可單擊“其他函數(shù)”項，屏幕彈出“插入函數(shù)”對話框，再從中選擇所需要的函數(shù)。修改:編輯欄修改或單元格修改移動:單元格引用不會改變復(fù)制:相對引用;根據(jù)需要設(shè)置為絕對引用或混合引用.=（B4+25）/SUM（D5:F5）公式特定符號區(qū)域范圍引用單元格地址數(shù)值型常量Excel函數(shù)運(yùn)算操作符號例如：有期末成績表，總評成績中平時成績占30%，期末成績占70%。3.1.5公式中返回的錯誤信息3.2.3嵌套函數(shù)的使用在某些情況下，用戶可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用。這就是函數(shù)的嵌套。當(dāng)嵌套函數(shù)作為參數(shù)使用時，它返回的數(shù)值類型必須與參數(shù)使用的數(shù)值類型相同。否則，Excel將顯示#VALUE!錯誤值。公式中最多可以包含七級嵌套函數(shù)。當(dāng)函數(shù)B作為函數(shù)A的參數(shù)時，函數(shù)B稱為第二級函數(shù)。說明：◆函數(shù)總是返回一個處理結(jié)果；◆參數(shù)之間用逗號隔開；◆既使無參數(shù)，圓括號也不能省略。3.2函數(shù)的使用3.2.1函數(shù)的基本語法函數(shù)名(參數(shù)1，參數(shù)2，······)函數(shù)的主體，代表函數(shù)功能函數(shù)運(yùn)行時參數(shù)，使用逗號(,)分隔，······表示與上一參數(shù)種類相同的參數(shù)可以有多個。不同類型的函數(shù)要求給定不同類型的參數(shù).有的函數(shù)無參數(shù)括號是必須的=2）使用“插入函數(shù)”(1)函數(shù)是一種簡單的表達(dá)式，是Excel自帶的一些已經(jīng)定義好的公式可以直接在單元格中輸入,但要以等號(=)開頭.(2)函數(shù)可以出現(xiàn)在表達(dá)式中。(3)利用函數(shù)向?qū)л斎牒瘮?shù)：選定要輸入函數(shù)的單元格；執(zhí)行“插入”?“函數(shù)”命令，或者單擊工具欄上的“粘貼函數(shù)()”圖標(biāo)，找開“粘貼函數(shù)”對話框；從“函數(shù)分類”列表框中選擇要輸入的函數(shù)的類別，在“函數(shù)名”列表框中選擇所需要的函數(shù)，從彈出的對話框中輸入函數(shù)的參數(shù)；單擊“確定”按鈕?！羰褂煤瘮?shù)向?qū)л斎耄汉瘮?shù)格式函數(shù)功能及參數(shù)說明參數(shù)說明引用的數(shù)據(jù)計算結(jié)果3.2函數(shù)的使用3.2.4常用函數(shù)介紹(1)算術(shù)函數(shù)

sum、sumif、ABS、exp、log、sqrt、int、round、mod等(2)統(tǒng)計函數(shù)

count、countif、average、Max、Min、Rank等(3)日期函數(shù)

now、month、today、year等(4)邏輯函數(shù)

and、or、not、if、false、true等SUMIF函數(shù)詳解語法：作用：參數(shù)說明：SUMIF(<范圍>,<條件>,<求和范圍>)求滿足條件的單元格中的數(shù)值之和<范圍>

指用于條件判斷的單元格區(qū)域。<條件>

指確定求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，<條件>可以表示為32、“32”、“>32”、“apples”注意：雙引號（””)對于字符數(shù)據(jù)是必須的<求和范圍>

指需要求和的實(shí)際單元格。只有當(dāng)<范圍>中的相應(yīng)單元格滿足條件時，才對<求和范圍>中的單元格求和。如果省略<求和范圍>,則直接對<范圍>中的單元格求和。SUMIF函數(shù)詳解例如：要求計算所有男生語文成績的總和:COUNT函數(shù)詳解作用:語法:COUNT(value1,value2,……)統(tǒng)計參數(shù)列表中數(shù)值型數(shù)據(jù)的個數(shù)參數(shù)說明：(1)單一數(shù)值型數(shù)據(jù)，例如：COUNT(50,35,65)(2)包含數(shù)值型數(shù)據(jù)單元格地址,例如：COUNT(A1,B2,C3),(3)單元格區(qū)域地址引用,例如:=COUNT(A1:F5),表示統(tǒng)計從A1單元格到F5單元格中所有數(shù)值型數(shù)據(jù)的個數(shù)。也可以為多個單元格區(qū)域的引用，用逗號分割。例如：=COUNT（A1:F5,E3:G8)。COUNTIF函數(shù)詳解=COUNTIF(E3:E9,”<60”)=COUNTIF(E3:E9,”>=70”)-COUNTIF(E3:E9,”>=80”)=E13/COUNT(E3:E9)AVERAGE函數(shù)詳解作用:語法:AVERAGE(number1,number2,……)計算給定參數(shù)中數(shù)值數(shù)據(jù)的算數(shù)平均值參數(shù)說明：(1)單一數(shù)值型數(shù)據(jù)，例如：=AVERAGE(50,35,65)等同于公式=(50+35+65)/3(2)包含數(shù)值型數(shù)據(jù)單元格地址,例如：AVERAGE(A1,B2,C3),等同于公式=(A1+B2+C3)/3(3)單元格區(qū)域地址引用,例如:=AVERAGE(A1:F5),表示對從A1單元格到F5單元格中所有數(shù)值型數(shù)據(jù)進(jìn)行求和。也可以為多個單元格區(qū)域的引用，用逗號分割。例如：=AVERAGE(A1:F5,E3:G8)。日期時間函數(shù)詳解Excel日期的內(nèi)部表示Excel將日期存儲為一系列連續(xù)的序列數(shù)，時間存儲為小數(shù)。通過將單元格的格式設(shè)置為“常規(guī)”格式，可以查看以系列值顯示的日期和以小數(shù)值顯示的時間。Excel支持兩種日期系統(tǒng)：1900年和1904年日期系統(tǒng)。默認(rèn)的日期系統(tǒng)是1900年日期系統(tǒng)。如果要更改日期系統(tǒng)，請執(zhí)行“工具”->“選項”命令，通過“重新計算”標(biāo)簽設(shè)置注意：當(dāng)輸入的年份為2位數(shù)字時,Excel將如下解釋年份：

00到29之間，解釋為2000到2029年；

30到99之間，解釋為1930到1999年。日期時間函數(shù)詳解Excel日期的內(nèi)部表示下表格顯示了每個日期系統(tǒng)中的第一天和最后一天的日期，及其對應(yīng)的序列數(shù)。日期系統(tǒng)19001904第一天

1900-1-11904-1-2第一天序列數(shù)11最后一天9999-12-31

9999-12-31最后一天序列數(shù)日期時間函數(shù)詳解TODAY()返回當(dāng)前機(jī)器的日期序列數(shù)。NOW()返回當(dāng)前機(jī)器的日期與時間序列數(shù)。DATE(<年>,<月>,<日>)，根據(jù)<年>,<月>,<日>產(chǎn)生日期序列數(shù)DAY(<日期序列數(shù)>)返回指定日期是當(dāng)月的“幾”號。YEAR(<日期序列數(shù)>)返回指日期的年份。MONTH(<日期序列數(shù)>)返回指定日期的月份。WEEKDAY(<日期序列數(shù)>，<返回類型>)返回指定日期是星期幾。無參數(shù)1:星期日=1,星期六=72:星期一=1,星期日=73:星期一=0,星期日=6日期時間函數(shù)詳解應(yīng)用舉例:計算年齡與當(dāng)前日期（動態(tài)日期）比較：1、=(today()-B3)/365

2、=year(today())–year(B3)當(dāng)前日期日期數(shù)據(jù)單元格與某個單元格內(nèi)的日期比較：1、=($B$10-B3)/3652、=year($B$10)–year(B3)固定日期單元格B10IF函數(shù)詳解作用:語法:參數(shù)說明：IF(Logical_test,Logaical_if_ture,Logaical_if_false)根據(jù)條件是否成立返回特定的值Logical_test判斷條件,返回值為TURE或FALSELogaical_if_ture如果條件成立,則返回當(dāng)前值。如果忽略則返回TURELogaical_if_false如果條件不成立,則返回當(dāng)前值。如果忽略則返回FALSEIF函數(shù)詳解例一：在成績表后添加一“是否補(bǔ)考”列，如果物理成績不及格則填入“補(bǔ)考”課堂練習(xí)：IF函數(shù)可以嵌套7層。如果成績表中包含數(shù)學(xué)，語文，物理成績。怎么完成以上要求IF函數(shù)詳解例二：有學(xué)生基本信息表，包括“出生日期”列和“說明”列，如果學(xué)生的出生月份和當(dāng)前月相等，則在說明欄里填入“祝你生日快樂”3.3.1數(shù)據(jù)記錄單的使用1）使用記錄單輸入數(shù)據(jù)2）使用記錄單修改數(shù)據(jù)3）使用記錄單刪除數(shù)據(jù)4）使用記錄單查找數(shù)據(jù)3.3EXCEL的數(shù)據(jù)管理和分析3.3.2數(shù)據(jù)的排序1）數(shù)據(jù)排序的規(guī)則默認(rèn)排序Excel在默認(rèn)排序時是根據(jù)單元格中的數(shù)據(jù)進(jìn)行排序的，在按升序排序時，Excel使用如下順序：1.數(shù)值從最小的負(fù)數(shù)到最大的正數(shù)排序2.文本和數(shù)字的文本按從0~9，a~z，A~Z的順序排列3.邏輯值False排在True之前4.所有錯誤值的優(yōu)先級相同5.空格排在最后2）數(shù)據(jù)排序方法與步驟◆使用工具欄排序按鈕：①選定要進(jìn)行排序的數(shù)據(jù)區(qū)域；②單擊“常用”工具欄上的“降序()”按鈕（或“升序()”按鈕）。3）自定義排序使用排序命令:選取需排序的單元格區(qū)域；執(zhí)行“數(shù)據(jù)”“排序”命令；在“排序”對話框中選擇排序的依據(jù)和排序的方式；最后單擊“確定”按鈕。3.3EXCEL的數(shù)據(jù)管理和分析3.3.3數(shù)據(jù)的篩選1）自動篩選①選擇工作表中數(shù)據(jù)的列標(biāo)題；②單擊“數(shù)據(jù)”→“篩選”→“自動篩選”；③根據(jù)所要篩選的數(shù)據(jù)內(nèi)容，單擊相應(yīng)列標(biāo)題的“篩選條件”按鈕，打開“篩選條件”列表框，單擊所需選項；④若需進(jìn)一步篩選數(shù)據(jù)，可重復(fù)第③步的操作?！昂Y選條件”列表中項目的含義項目含義全部顯示所有數(shù)據(jù)。前10個從當(dāng)前數(shù)據(jù)列中篩選出若干個最大或最小的數(shù)據(jù)。自定義利用比較運(yùn)算符，對當(dāng)前數(shù)據(jù)列設(shè)置篩選條件。數(shù)據(jù)從當(dāng)前數(shù)據(jù)列中篩選出與指定數(shù)據(jù)相同的內(nèi)容?？瞻自诋?dāng)前數(shù)據(jù)列中，篩選出數(shù)據(jù)為空值的數(shù)據(jù)。非空白在當(dāng)前數(shù)據(jù)列中，篩選出數(shù)據(jù)為非空值的數(shù)據(jù)。（1）自動篩選①選擇工作表中數(shù)據(jù)的列標(biāo)題；②單擊“數(shù)據(jù)”→“篩選”→“自動篩選”；③根據(jù)所要篩選的數(shù)據(jù)內(nèi)容，單擊相應(yīng)列標(biāo)題的“篩選條件”按鈕，打開“篩選條件”列表框，單擊所需選項；④若需進(jìn)一步篩選數(shù)據(jù)，可重復(fù)第③步的操作。2）高級篩選①在工作表的任一空白區(qū)域，輸入篩選條件；語文數(shù)學(xué)>80>70語文數(shù)學(xué)>80>70注意：同行條件是“與”的關(guān)系，不同行的條件是“或”的關(guān)系.例1：語文大于80而且數(shù)學(xué)大于70。例2：語文大于80或數(shù)學(xué)大于70條件單元格區(qū)域（2）高級篩選②單擊“數(shù)據(jù)”→“篩選”→“高級篩選”，打開“高級篩選”對話框；③單擊選擇“將篩選結(jié)果復(fù)制到其他位置”選項；④在“數(shù)據(jù)區(qū)域”文本框中單擊，然后拖動鼠標(biāo)選擇將被篩選的數(shù)據(jù)單元格區(qū)域；⑤在“條件區(qū)域”文本框中單擊，然后拖動鼠標(biāo)選擇條件單元格區(qū)域；⑥在“復(fù)制到”文本框中單擊，然后單擊欲放置篩選結(jié)果區(qū)域的起始單元格；⑦單擊“確定”。3.3.4數(shù)據(jù)的分類匯總1）分類匯總的建立①選擇分類匯總數(shù)據(jù)所在區(qū)域；②對所選區(qū)域的數(shù)據(jù)，按分類要求進(jìn)行排序；③單擊“數(shù)據(jù)”→“分類匯總”，打開“分類匯總”對話框；④按需要進(jìn)行選擇后，單擊“確定”。2）分類匯總的清除（2）刪除分類匯總①選擇分類匯總數(shù)據(jù)所在的區(qū)域；②單擊“數(shù)據(jù)”→“分類匯總”，打開“分類匯總”對話框；③單擊“全部刪除”。3.3EXCEL的數(shù)據(jù)管理和分析什么是數(shù)據(jù)透視表？數(shù)據(jù)透視表是用來從Excel數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫文件或OLAP多維數(shù)據(jù)集中的特殊字段中總結(jié)信息的分析工具。它是一種交互式報表，可以快速分類匯總、比較大量的數(shù)據(jù)，并可以隨時選擇其中頁、行和列中的不同元素，以達(dá)到快速查看源數(shù)據(jù)的不同統(tǒng)計結(jié)果，同時還可以隨意顯示和打印出你所感興趣區(qū)域的明細(xì)數(shù)據(jù)。數(shù)據(jù)透視表有機(jī)的綜合了數(shù)據(jù)排序、篩選、分類匯總等數(shù)據(jù)分析的優(yōu)點(diǎn)，可方便地調(diào)整分類匯總的方式，靈活地以多種不同方式展示數(shù)據(jù)的特征。一張“數(shù)據(jù)透視表”僅靠鼠標(biāo)移動字段位置，即可變換出各種類型的報表。同時，數(shù)據(jù)透視表也是解決函數(shù)公式速度瓶頸的手段之一。因此，該工具是最常用、功能最全的Excel數(shù)據(jù)分析工具之一。數(shù)據(jù)透視表有什么用？數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交互式動態(tài)表格，能幫助用戶分析、組織數(shù)據(jù)。例如，計算平均數(shù)、標(biāo)準(zhǔn)差，建立列聯(lián)表、計算百分比、建立新的數(shù)據(jù)子集等。建好數(shù)據(jù)透視表后，可以對數(shù)據(jù)透視表重新安排，以便從不同的角度查看數(shù)據(jù)。數(shù)據(jù)透視表的名字來源于它具有“透視”表格的能力，從大量看似無關(guān)的數(shù)據(jù)中尋找背后的聯(lián)系，從而將紛繁的數(shù)據(jù)轉(zhuǎn)化為有價值的信息，以供研究和決策所用?？傊?，合理運(yùn)用數(shù)據(jù)透視表進(jìn)行計算與分析，能使許多復(fù)雜的問題簡單化并且極大的提高工作效率3.3.5數(shù)據(jù)透視表的使用1）建立數(shù)據(jù)透視表2）數(shù)據(jù)的透視分析3）數(shù)據(jù)更新4)顯示數(shù)據(jù)項的明細(xì)數(shù)據(jù)3.3.6數(shù)據(jù)的審核與跟蹤分析1）跟蹤引用單元格（引用）2）跟蹤從屬單元格（被引用）3）數(shù)據(jù)的有效性4）圈釋無效數(shù)據(jù)3.4Excel圖表3.4.1圖表的建立方法一：F11自動插入一張圖表（自動顯示簡單柱形圖表）方法二：工具欄——圖表方法三：插入——圖表(表格向?qū)В?）柱形圖的制作2）餅圖的制作3.4.2圖表的編輯1）設(shè)置坐標(biāo)、標(biāo)題、圖例等的格式2）改變圖表尺寸3）移動圖表4）復(fù)制圖表5）刪除圖表6）添加數(shù)據(jù)標(biāo)志圖表操作圖例數(shù)據(jù)標(biāo)記數(shù)據(jù)序列類型軸(X)刻度線值軸（Y）圖表相關(guān)名詞術(shù)語圖表操作1.創(chuàng)建圖表選定要包含在統(tǒng)計圖中的單元格數(shù)據(jù)。執(zhí)行“插入”?“圖表”命令，或單擊工具欄中的“圖表向?qū)А眻D標(biāo)()從“圖表向?qū)?圖表類型)”對話框中選擇合適的圖表類型，并單擊“下一步”按鈕。如果數(shù)據(jù)源不合可以在打開的對話框中修改。一般，簡單地單擊“下一步”按鈕。在打開的對話框中設(shè)置圖表的標(biāo)題、坐標(biāo)軸、網(wǎng)線、圖例等。一般，簡單地單擊“下一步”按鈕最后指定圖表插入位置，并單擊“完成”按鈕。圖表操作1.創(chuàng)建圖表選定要包含在統(tǒng)計圖中的單元格數(shù)據(jù)。執(zhí)行“插入”?“圖表”命令，或單擊工具欄中的“圖表向?qū)А眻D標(biāo)()從“圖表向?qū)?圖表類型)”對話框中選擇合適的圖表類型，并單擊“下一步”按鈕。如果數(shù)據(jù)源不合可以在打開的對話框中修改。一般，簡單地單擊“下一步”按鈕。在打開的對話框中設(shè)置圖表的標(biāo)題、坐標(biāo)軸、網(wǎng)線、圖例等。一般，簡單地單擊“下一步”按鈕最后指定圖表插入位置，并單擊“完成”按鈕。圖表操作1.創(chuàng)建圖表選定要包含在統(tǒng)計圖中的單元格數(shù)據(jù)。執(zhí)行“插入”?“圖表”命令，或單擊工具欄中的“圖表向?qū)А眻D標(biāo)()從“圖表向?qū)?圖表類型)”對話框中選擇合適的圖表類型，并單擊“下一步”按鈕。如果數(shù)據(jù)源不合可以在打開的對話框中修改。一般，簡單地單擊“下一步”按鈕。在打開的對話框中設(shè)置圖表的標(biāo)題、坐標(biāo)軸、網(wǎng)線、圖例等。一般，簡單地單擊“下一步”按鈕最后指定圖表插入位置，并單擊“完成”按鈕。圖表操作1.創(chuàng)建圖表選定要包含在統(tǒng)計圖中的單元格數(shù)據(jù)。執(zhí)行“插入”?“圖表”命令，或單擊工具欄中的“圖表向?qū)А眻D標(biāo)()從“圖表向?qū)?圖表類型)”對話框中選擇合適的圖表類型，并單擊“下一步”按鈕。如果數(shù)據(jù)源不合可以在打開的對話框中修改。一般，簡單地單擊“下一步”按鈕。在打開的對話框中設(shè)置圖表的標(biāo)題、坐標(biāo)軸、網(wǎng)線、圖例等。一般，簡單地單擊“下一步”按鈕最后指定圖表插入位置，并單擊“完成”按鈕。圖表操作2.圖表的移動和大小的調(diào)整(1)移動圖表

單擊要移動的圖表（此時在選中的圖表的四周將出現(xiàn)八個黑色的控點(diǎn)），用鼠標(biāo)拖動它到一個新的位置，再松開鼠標(biāo)即可。(2)改變圖表的大小

先選中要改變大小的圖表，然后將鼠標(biāo)指針移至圖表的四周的任一控點(diǎn)上，當(dāng)指針變成雙箭頭時，拖動鼠標(biāo)直至圖表變成滿意的大小后松開鼠標(biāo)。圖表操作3.圖表格式的編輯

(1)修改圖表的類型選中欲更改類型的圖表執(zhí)行“圖表”?“圖表類型”命令在打開的對話框中選擇一種合適的類型后單擊“確定”按鈕。

圖表操作3.圖表格式的編輯

(2)插入數(shù)據(jù)標(biāo)志單擊要添加數(shù)據(jù)標(biāo)志的圖表執(zhí)行“圖表”?“圖表選項”命令切換到“數(shù)據(jù)標(biāo)志”標(biāo)簽選擇需要的數(shù)據(jù)標(biāo)志種類，單擊“確定”按鈕數(shù)據(jù)標(biāo)志圖表操作3.圖表格式的編輯

(3)添加圖表標(biāo)題單擊要添加標(biāo)題的圖表“圖表”?“圖表選項”命令，切換到“標(biāo)題”標(biāo)簽輸入圖表標(biāo)題和X軸、Y軸、

Z軸標(biāo)后，單擊“確定”按鈕。數(shù)據(jù)標(biāo)志圖表標(biāo)題、圖表操作3.圖表格式的編輯

(4)改變圖表的文字、顏色和圖案雙擊需要修改的標(biāo)題或者在選中標(biāo)題后單擊“格式”菜單中的“坐標(biāo)軸標(biāo)題”命令（打開“坐標(biāo)軸標(biāo)題格式”對話框）。

通過“字體