Excel在會計(jì)與財(cái)務(wù)中的應(yīng)用課件：Excel的高級應(yīng)用

2.1公式的使用2.2函數(shù)的使用2.3數(shù)據(jù)的管理與分析2.4圖表的使用

Excel的高級應(yīng)用內(nèi)容簡介本章介紹了Excel2016的公式、函數(shù)的使用，數(shù)據(jù)的排序、篩選、分類匯總和透視表，以及圖表的創(chuàng)建與編輯。學(xué)習(xí)目標(biāo)掌握公式與函數(shù)的使用，特別是單元格引用、常用函數(shù)、函數(shù)的嵌套。熟悉數(shù)據(jù)排序與篩選，了解分類匯總，熟悉數(shù)據(jù)透視表。掌握圖表的創(chuàng)建與編輯。了解Excel的自動恢復(fù)、更改窗口顏色等設(shè)置。2024/4/182

Excel的高級應(yīng)用2.1公式的使用2.1.1公式概述2.1.2單元格引用2.1.3輸入與修改公式2.1.4復(fù)制與移動公式2.1.5顯示與隱藏公式2024/4/1831.引用運(yùn)算符2024/4/1842.1.1公式概述運(yùn)算符運(yùn)算功能示例:（冒號）區(qū)域運(yùn)算符，用于引用單元格區(qū)域A1:B2（包括A1、A2、B1、B2四個(gè)單元格）；B:B（表示整個(gè)B列）；3:3（表示整個(gè)第3行）。

（空格）交叉運(yùn)算符，用于引用兩個(gè)單元格區(qū)域相交部分A1:C3B2:C4（表示兩區(qū)域相交的部分B2:C3四個(gè)單元格）,（逗號）聯(lián)合運(yùn)算符，用于把兩個(gè)單元格區(qū)域合并在一起A1:B2,B3:C4（總共包括8個(gè)單元格）2.算術(shù)運(yùn)算符2024/4/1852.1.1公式概述運(yùn)算符運(yùn)算功能示例()（括號）提升運(yùn)算優(yōu)先順序，括號內(nèi)先運(yùn)算A1*(B1+B2)。先算加法后算乘法。-（負(fù)號）取相反數(shù)-B2。對B2存儲的數(shù)據(jù)取相反數(shù)。%（百分號）百分比50%。A1%表示對A1存儲的數(shù)據(jù)取縮小100倍，如圖1.99所示。^（脫字符號）求冪運(yùn)算2^3=8。A1^B1表示A1的B1次冪。*和/（星號和正斜杠）乘和除2*3=6。A1*B1。A1/B1。+和-（加號和減號）加和減2+3=5。A1+B1。A1-B1。3.文本運(yùn)算符使用文本運(yùn)算符“＆”（與號）可將兩個(gè)或多個(gè)文本值串起來產(chǎn)生一個(gè)連續(xù)的文本值。例如：A1輸入“我愛你”，B1輸入“中國！”，C1單元格輸入“=A1&B1”，則會生成“我愛你中國！”2024/4/1862.1.1公式概述4.比較運(yùn)算符2024/4/1872.1.1公式概述運(yùn)算符含義運(yùn)算符含義=（等號）等于<=（小于等于號）小于等于<（小于號）小于>=（大于等于號）大于等于>（大于號）大于<>（不等號）不等于5.運(yùn)算符優(yōu)先順序2024/4/1882.1.1公式概述運(yùn)算類型運(yùn)算符優(yōu)先級引用運(yùn)算:（冒號）1

（空格）2,（逗號）3數(shù)學(xué)運(yùn)算()（括號）4-（負(fù)號）5%（百分號）6^（脫字符號）7*和/（星號和正斜杠）8+和-（加號和減號）9文本運(yùn)算&（與符號）10邏輯運(yùn)算=（等號）11<和>（小于和大于）<=（小于等于）>=（大于等于）<>（不等于）2.1.2單元格引用1.相對引用公式中的相對單元格引用是基于包含公式和單元格引用的相對位置。在F3單元格中輸入公式“=C3*D3”，表示Excel將在F3單元格的左側(cè)查找C3和D3單元格中的數(shù)據(jù)，并把找到的數(shù)值相乘，把結(jié)果存放在F3單元格中。此處C3和D3就是相對于公式所在的F3單元格的相對位置。2024/4/1892.1.2單元格引用2.絕對引用絕對引用是指在列標(biāo)和行號之前分別加上符號“$”。在復(fù)制公式或填充公式時(shí)，絕對引用的單元格地址不會發(fā)生變化。例如，在F6單元格中輸入公式“=$C$6*$D$6”，表示對C6和D6單元格的絕對引用，將公式復(fù)制到G6、F7單元格中，絕對引用的單元格地址不發(fā)生變化，故而G6、F7單元格中的公式結(jié)果和F6單元格中的數(shù)值一樣。2024/4/18102.1.2單元格引用3.混合引用混合引用包含相對引用和絕對引用，即在列標(biāo)或行號前加，“$”符號。在列標(biāo)前加“$”符號稱為絕對列，在行號前加“$”符號稱為絕對行。在復(fù)制或填充公式時(shí)，相對引用部分改變，絕對引用部分不改變。2024/4/18111.輸入公式Excel中的公式書寫順序與大家日常習(xí)慣的公式書寫順序略有不同，需要用戶先輸入一個(gè)等號（=），接著輸入運(yùn)算式。2024/4/18122.1.3輸入與修改公式2.修改公式選中含有錯(cuò)誤公式的單元格，這時(shí)會在編輯欄中顯示公式，將光標(biāo)定位到編輯欄中，接著修改公式，再按Enter鍵確認(rèn)。2024/4/18132.1.3輸入與修改公式1.復(fù)制公式選中需要復(fù)制公式的單元格，利用填充柄即可。2024/4/18142.1.4復(fù)制與移動公式2.移動公式選中單元格，將鼠標(biāo)移到單元格邊框變成十字箭頭，鼠標(biāo)左鍵按住，移動到需要的位置放開鼠標(biāo)即可。2024/4/18152.1.4復(fù)制與移動公式在“公式”選項(xiàng)卡的“公式審核”組中單擊“顯示公式”按鈕。2024/4/18162.1.5顯示與隱藏公式2.2函數(shù)的使用2.2.1函數(shù)概述2.2.2調(diào)用函數(shù)2.2.3常用函數(shù)2.2.4函數(shù)的嵌套2.2.5常見的公式錯(cuò)誤符號2024/4/18172024/4/18182.2.1函數(shù)概述函數(shù)類別函數(shù)功能數(shù)學(xué)和三角函數(shù)進(jìn)行數(shù)學(xué)計(jì)算，包括常規(guī)計(jì)算和三角函數(shù)方面的計(jì)算日期和時(shí)間函數(shù)對公式中的日期和時(shí)間進(jìn)行計(jì)算與格式設(shè)置邏輯函數(shù)設(shè)置判斷條件，以使公式更加智能化文本函數(shù)對公式和單元格中的文本進(jìn)行提取、查找、替換或格式化處理查找和引用函數(shù)查找或返回工作表中的匹配數(shù)據(jù)或特定信息信息函數(shù)判定單元格或公式中的數(shù)據(jù)類型，或返回某些特定信息統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)和分析工作表中的數(shù)據(jù)財(cái)務(wù)函數(shù)分析與計(jì)算財(cái)務(wù)數(shù)據(jù)工程函數(shù)分析與處理工程數(shù)據(jù)數(shù)據(jù)庫函數(shù)分類、查找與計(jì)算數(shù)據(jù)表中的數(shù)據(jù)多維數(shù)據(jù)集函數(shù)分析多維數(shù)據(jù)集合中的數(shù)據(jù)加載宏和自動化函數(shù)通過加載宏提供的函數(shù)，可擴(kuò)展Excel的函數(shù)功能兼容性函數(shù)這些函數(shù)已被新增函數(shù)代替，提供它們便于用于早期版本W(wǎng)eb函數(shù)網(wǎng)絡(luò)信息、數(shù)據(jù)的抓取與處理用戶可以在單元格或數(shù)據(jù)編輯欄中直接輸入函數(shù)。2024/4/18192.2.2調(diào)用函數(shù)1.求和函數(shù)（1）無條件求和函數(shù)——SUM函數(shù)功能：SUM函數(shù)用于計(jì)算單個(gè)或多個(gè)參數(shù)之和。公式：=SUM(numberl,number2,…)式中，“number1,number2,…”為1到255個(gè)需要求和的參數(shù)。（2）單一條件求和函數(shù)——SUMIF函數(shù)功能：根據(jù)指定條件對若干單元格求和。公式：=SUMIF(range,criteria,[sum_range])range集合代表某一單元格、某一行、某一列、某一選定區(qū)域（該區(qū)域可包含一個(gè)或若干連續(xù)單元格區(qū)域），或者某一三維區(qū)域。criteria代表查詢條件。sum_range集合代表求和單元格或區(qū)域。2024/4/18202.2.3常用函數(shù)1.求和函數(shù)（1）無條件求和函數(shù)——SUM函數(shù)功能：SUM函數(shù)用于計(jì)算單個(gè)或多個(gè)參數(shù)之和。公式：=SUM(numberl,number2,…)式中，“number1,number2,…”為1到255個(gè)需要求和的參數(shù)。（2）單一條件求和函數(shù)——SUMIF函數(shù)功能：根據(jù)指定條件對若干單元格求和。公式：=SUMIF(range,criteria,[sum_range])range集合代表某一單元格、某一行、某一列、某一選定區(qū)域（該區(qū)域可包含一個(gè)或若干連續(xù)單元格區(qū)域），或者某一三維區(qū)域。criteria代表查詢條件。sum_range集合代表求和單元格或區(qū)域。（3）多條件求和函數(shù)——SUMIFS函數(shù)功能：根據(jù)多個(gè)條件（至少一個(gè)條件）對若干單元格求和。公式：=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)2024/4/18212.2.3常用函數(shù)2.計(jì)數(shù)函數(shù)（1）COUNT函數(shù)功能：計(jì)算包含數(shù)字的單元格個(gè)數(shù)以及參數(shù)列表中數(shù)字的個(gè)數(shù)。公式：=COUNT(value1,[value2],...)（2）COUNTA函數(shù)功能：計(jì)算范圍中非空的單元格的個(gè)數(shù)。公式：=COUNTA(value1,[value2],...)（3）COUNTBLANK函數(shù)功能：計(jì)算單元格區(qū)域中的空單元格的數(shù)量。公式：=COUNTBLANK(range)（4）COUNTIF函數(shù)功能：統(tǒng)計(jì)滿足某個(gè)條件的單元格的數(shù)量。公式：=COUNTIF(range,criteria)（5）COUNTIFS函數(shù)功能：統(tǒng)計(jì)滿足多個(gè)條件的單元格的數(shù)量。公式：=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)2024/4/18222.2.3常用函數(shù)3.求平均函數(shù)（1）AVERAGE函數(shù)功能：返回參數(shù)的平均值(算術(shù)平均值)。公式：=AVERAGE(number1,[number2],...)（2）AVERAGEIF函數(shù)功能：返回某個(gè)區(qū)域內(nèi)滿足給定條件的所有單元格的平均值（算術(shù)平均值）。公式：=AVERAGEIF(range,criteria,[average_range])AVERAGEIF函數(shù)是用法與SUMIF函數(shù)的用法幾乎完全相同，參數(shù)也基本一樣。（3）AVERAGEIFS函數(shù)功能：返回滿足多個(gè)條件的所有單元格的平均值（算術(shù)平均值）。公式：=AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)AVERAGEIFS函數(shù)是用法與SUMIFS函數(shù)的用法幾乎完全相同，參數(shù)也基本一樣。2024/4/18232.2.3常用函數(shù)4.四舍五入函數(shù)（1）ROUND函數(shù)功能：將數(shù)字四舍五入到指定的位數(shù)。公式：=ROUND(number,num_digits)（2）ROUNDDOWN函數(shù)功能：將數(shù)字進(jìn)行向下舍入到指定的位數(shù)。公式：=ROUNDDOWN(number,num_digits)（3）ROUNDUP函數(shù)功能：將數(shù)字進(jìn)行向上舍入到指定的位數(shù)。公式：=ROUNDUP(number,num_digits)2024/4/18242.2.3常用函數(shù)5.最值函數(shù)（1）MAX函數(shù)功能：返回一組值中的最大值。公式：=MAX(number1,[number2],...)（2）MIN函數(shù)功能：返回一組值中的最小值。公式：=MIN(number1,[number2],...)2024/4/18252.2.3常用函數(shù)6.條件函數(shù)——IF函數(shù)功能：根據(jù)條件值進(jìn)行真假邏輯的判斷。公式：=IF(logical_test,value_if_true,value_if_false)logical_test表示判斷條件，是計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。value_if_true表示logical_test為TRUE時(shí)返回的值。value_if_false表示logical_test為FALSE時(shí)返回的值。其實(shí)IF函數(shù)的三個(gè)參數(shù)很像關(guān)聯(lián)詞造句，給定的關(guān)聯(lián)詞是：“如果……，那么……，否則……?！?024/4/18262.2.3常用函數(shù)7.且/或/非函數(shù)（1）AND函數(shù)功能：表示邏輯值“且”，用于確定所有條件是否均為TRUE。公式：=AND(logical1,[logical2],…)（2）OR函數(shù)功能：表示邏輯值“或”，用于確定只要有一個(gè)條件是TRUE。公式：=OR(logical1,[logical2],…)（3）NOT函數(shù)功能：表示邏輯值“否”，用于判定一個(gè)值不等同于另一值。2024/4/18272.2.3常用函數(shù)8.查找函數(shù)（1）VLOOKUP函數(shù)功能：在表格或區(qū)域中依據(jù)首列按行查找項(xiàng)目。公式：=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])為了方便記憶，可以這樣記VLOOKUP函數(shù)的四個(gè)參數(shù)：VLOOKUP（線索，去哪找，要第幾列，[是否近似匹配]）（2）MATCH函數(shù)功能：在單元格區(qū)域中搜索特定的項(xiàng)，然后返回該項(xiàng)在此區(qū)域中的相對位置（第幾行或第幾列）。公式：MATCH(lookup_value,lookup_array,[match_type])為了方便記憶，可以這樣記MATCH函數(shù)的三個(gè)參數(shù)：MATCH（找什么<唯一>，去哪找<某一行或某一列>，[是否近似匹配]）2024/4/18282.2.3常用函數(shù)9.引用函數(shù)（1）INDEX函數(shù)INDEX函數(shù)有兩種用法：如果想要返回指定單元格或單元格數(shù)組的值，請參閱數(shù)組形式。如果想要返回對指定單元格的引用，請參閱引用形式。①數(shù)組形式功能：返回單元格區(qū)域或數(shù)組中值的引用。公式：=INDEX(array,row_num,[column_num])②引用表單功能：返回指定的行與列交叉處的單元格引用。如果引用由非相鄰的選項(xiàng)組成，則可以選擇要查找的選擇內(nèi)容。公式：=INDEX(reference,row_num,[column_num],[area_num])2024/4/18292.2.3常用函數(shù)9.引用函數(shù)（2）OFFSET函數(shù)功能：返回對單元格或單元格區(qū)域中指定行數(shù)和列數(shù)的區(qū)域的引用。返回的引用可以是單個(gè)單元格或單元格區(qū)域?？梢灾付ㄒ祷氐男袛?shù)和列數(shù)。公式：＝OFFSET(reference,rows,cols,[height],[width])2024/4/18302.2.3常用函數(shù)2.2.3常用函數(shù)10.文本函數(shù)（1）LEFT函數(shù)功能：返回文本值中最左邊的字符。公式：=LEFT(text,[num_chars])（2）RIGHT函數(shù)功能：返回文本值中最右邊的字符。公式：=RIGHT(text,[num_chars])（3）MID函數(shù)功能：返回文本值中中間（Middle）的字符。公式：=MID(text,start_num,num_chars)（4）TEXT函數(shù)功能：設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本。公式：=TEXT(value,format_text)（5）VALUE函數(shù)功能：將表示數(shù)字的文本字符串轉(zhuǎn)換為數(shù)字。公式：=VALUE(text)2024/4/183111.日期函數(shù)——DATEDIF函數(shù)功能：計(jì)算兩個(gè)日期之間相隔的天數(shù)、月數(shù)或年數(shù)。公式：=DATEDIF(start_date,end_date,unit)2024/4/18322.2.3常用函數(shù)1.同函數(shù)嵌套——以IF函數(shù)為例例如，公司規(guī)定購買藥品需要繳納預(yù)付款，預(yù)付款預(yù)繳比例如表2.13所示。根據(jù)公司規(guī)定，制作G列“預(yù)付款”。G3單元格的公式為：“=IF(F3<100000,F3*100%,IF(F3<=500000,F3*50%,F3*25%))”2024/4/18332.2.4函數(shù)的嵌套銷售金額預(yù)付比例10萬以下100%10萬（含）~50萬（含）50%50萬以上25%2.不同函數(shù)嵌套——以VLOOKUP和MATCH函數(shù)為例實(shí)踐中，VLOOKUP+MATCH的組合是非常經(jīng)典、常用的。絕大多數(shù)情況下，MATCH函數(shù)充當(dāng)VLOOKUP函數(shù)的第3個(gè)參數(shù)。例如，在J3單元格設(shè)計(jì)公式，使其能夠根據(jù)不同的日期，查找出當(dāng)天銷售的商品名稱、銷售單價(jià)、數(shù)量等信息。J3單元格的公式為：“=VLOOKUP(I3,A:G,MATCH(J2,A2:G2,0),FALSE)”。2024/4/18342.2.4函數(shù)的嵌套2.2.5常見的公式錯(cuò)誤符號1.####錯(cuò)誤此錯(cuò)誤表示列不夠?qū)?，或者使用了?fù)日期或時(shí)間。2.#VALUE!錯(cuò)誤此錯(cuò)誤表示使用的參數(shù)或操作數(shù)的類型不正確。3.#DIV/0!錯(cuò)誤這種錯(cuò)誤表示使用數(shù)字除以零(0)。4.#N/A錯(cuò)誤當(dāng)數(shù)值對函數(shù)或公式不可用時(shí)，將出現(xiàn)此錯(cuò)誤。5.#NAME?錯(cuò)誤當(dāng)Excel無法識別公式中的文本時(shí)，將出現(xiàn)此錯(cuò)誤。6.#REF!錯(cuò)誤當(dāng)單元格引用無效時(shí)，會出現(xiàn)此錯(cuò)誤。7.#NUM!錯(cuò)誤如果公式或函數(shù)中使用了無效的數(shù)值，則會出現(xiàn)此錯(cuò)誤。8.#NULL!錯(cuò)誤如果指定了兩個(gè)并不相相交的區(qū)域的交點(diǎn)，則會出現(xiàn)錯(cuò)誤。2024/4/18352.3數(shù)據(jù)的管理與分析2.3.1數(shù)據(jù)排序2.3.2數(shù)據(jù)篩選2.3.3分類匯總2.3.4數(shù)據(jù)透視表2024/4/18361.默認(rèn)的排序順序在對數(shù)據(jù)進(jìn)行排序時(shí)，Excel有默認(rèn)的排序順序。按升序排序時(shí)，Excel的排序規(guī)則如下(在按降序排序時(shí)，除了空格總是在最后外，其他的排序順序反轉(zhuǎn))。①數(shù)字：從最小的負(fù)數(shù)到最大的正數(shù)排序。②文本以及包含數(shù)字的文本：首先是數(shù)字0~9，然后是字符’-(空格)!#$%&()*，./：;?@\^-{|}~+<=>，最后是字母A~Z。③邏輯值：FALSE排在TRUE之前。④所有錯(cuò)誤值的優(yōu)先級等效。⑤空格排在最后。2024/4/18372.3.1數(shù)據(jù)排序2.3.1數(shù)據(jù)排序2.簡單排序簡單排序，是指對數(shù)據(jù)列表中的單列數(shù)據(jù)進(jìn)行排序。直接右擊需要排序的標(biāo)題所在單元格，在下拉菜單中選擇“排序”以及排序方式即可。例如要依據(jù)“數(shù)量”升序排序，直接右擊D2單元格，選擇“排序”|“升序”即可完成操作2024/4/18381.自動篩選自動篩選的功能比較簡單，可以很快地顯示出符合條件的數(shù)據(jù)。2024/4/18392.3.2數(shù)據(jù)篩選2.取消篩選對工作表的數(shù)據(jù)進(jìn)行篩選后，工作表中將只顯示符合篩選條件的數(shù)據(jù)，需要查看其他數(shù)據(jù)時(shí)可以取消篩選。如果要取消自動篩選，則打開“數(shù)據(jù)”選項(xiàng)卡，單擊“排序與篩選”選項(xiàng)組中的“清除”按鈕即可。2024/4/18402.3.2數(shù)據(jù)篩選分類匯總是將數(shù)據(jù)按照某一字段進(jìn)行分類并計(jì)算匯總(個(gè)數(shù)、和、平均值等)。通過分類匯總可以方便地分析出各類數(shù)據(jù)在總數(shù)據(jù)中所占的位置。簡單來說，就是先按照一個(gè)標(biāo)準(zhǔn)進(jìn)行分類，再對各類型數(shù)據(jù)進(jìn)行匯總。2024/4/18412.3.3分類匯總2024/4/18422.3.4數(shù)據(jù)透視表頁面字段數(shù)據(jù)字段列字段行字段項(xiàng)目數(shù)據(jù)區(qū)域2.4圖表的使用2.4.1圖表的概述2.4.2創(chuàng)建圖表2.4.3編輯圖表2024/4/18432024/4/18442.4.1圖表的概述圖表類型用途柱形圖用于顯示一段時(shí)期內(nèi)數(shù)據(jù)的變化或者各項(xiàng)之間的比較關(guān)系折線圖用于顯示數(shù)據(jù)的變化趨勢餅圖用于顯示數(shù)據(jù)系列中各項(xiàng)占總體的比例關(guān)系條形圖用于描述各項(xiàng)之間的差異變化或者顯示各個(gè)項(xiàng)與整體之間的關(guān)系面積圖用于顯示局部和整體之間的關(guān)系，更強(qiáng)調(diào)幅值隨時(shí)間的變化趨勢XY(散點(diǎn)圖)多用于科學(xué)數(shù)據(jù)，用于比較不同數(shù)據(jù)系列中的數(shù)值，以反應(yīng)數(shù)值之間的關(guān)聯(lián)性股價(jià)圖用于分析股票價(jià)格的走勢曲面圖用于確定兩組數(shù)據(jù)之間的最佳逼近雷達(dá)圖用于多個(gè)數(shù)據(jù)系列之間的總和值的比較，各個(gè)分類沿各自的數(shù)值坐標(biāo)軸相對于中點(diǎn)呈輻射狀分布，同一序列的數(shù)值之間用折線相連組合圖將多個(gè)圖表進(jìn)行組合，在一個(gè)圖表中實(shí)現(xiàn)效果2.4.2創(chuàng)建圖表選中單元格區(qū)域。然后在“插入”選項(xiàng)卡的“圖表”組中單擊“XX圖”按鈕。2024/4/18451.設(shè)置圖表格式（1）設(shè)置圖表大小若要調(diào)整圖表大小，鼠標(biāo)左鍵選擇并按住圖表的角落進(jìn)行拖拽即可。若要準(zhǔn)確設(shè)置圖表的大小，在選中圖表后，單擊功能區(qū)“圖表工具”|“格式”選項(xiàng)卡，在“大小”組中分別設(shè)置高度和寬度。2024/4/18462.4.3編輯圖表1.設(shè)置圖表格式（2）選擇圖表樣式Excel2016中有很多預(yù)設(shè)的圖表樣式，選中圖表后，單擊功能區(qū)“圖表工具”|“設(shè)計(jì)”選項(xiàng)卡，在“圖表樣式”組中單擊“其他”按鈕，即可在展開的