EXCEL大全學習教案

1、會計學1EXCEL大全大全建立數(shù)據(jù)清單建立數(shù)據(jù)清單建立數(shù)據(jù)清單的準則建立數(shù)據(jù)清單的準則使用記錄單使用記錄單數(shù)據(jù)排序數(shù)據(jù)排序?qū)?shù)據(jù)清單進行排序?qū)?shù)據(jù)清單進行排序創(chuàng)建自定義排序創(chuàng)建自定義排序數(shù)據(jù)篩選數(shù)據(jù)篩選自動篩選自動篩選高級篩選高級篩選分類匯總分類匯總分類匯總的計算方法分類匯總的計算方法匯總報表和圖表匯總報表和圖表插入和刪除分類匯總插入和刪除分類匯總插入圖表表示數(shù)據(jù)插入圖表表示數(shù)據(jù)復(fù)雜的數(shù)據(jù)鏈接復(fù)雜的數(shù)據(jù)鏈接使用數(shù)據(jù)透視表使用數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表編輯數(shù)據(jù)透視表編輯數(shù)據(jù)透視表controll toolbox usage (控件工具箱控件工具箱)課程大綱課程大綱第1頁/共69頁算術(shù)

2、運算符號算術(shù)運算符號運算符含義運算符含義示示 例例+(加號加號)加加2+3=5-(減號減號)減減3-1=2*(星號星號乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分號百分號)百分號百分號50%(脫字號脫字號)乘方乘方43=43=64第2頁/共69頁比較運算符比較運算符運算符含義運算符含義示示 例例=(等號等號)相等相等B1=C1,若若B1中單元格內(nèi)的值確實與中單元格內(nèi)的值確實與C1中的值中的值相等相等,則產(chǎn)生邏輯真值則產(chǎn)生邏輯真值TRUE,若不相等若不相等,則產(chǎn)生邏則產(chǎn)生邏輯假值輯假值FALSE(小于號小于號)小于小于B1(大于號大于號)大于大于B1C1,若若B1中數(shù)值為中數(shù)值為6,C1中

3、數(shù)值為中數(shù)值為4,則條件成則條件成立產(chǎn)生邏輯真值立產(chǎn)生邏輯真值TRUE,否則產(chǎn)生邏輯假值否則產(chǎn)生邏輯假值FALSE=(大于等于號大于等于號)大于等于大于等于B1=C1(不等號不等號)不等于不等于B1C1=(小于等于號小于等于號)小于等于小于等于B1=C1第3頁/共69頁引用運算符引用運算符運算符含義運算符含義示示 例例:區(qū)域運算符區(qū)域運算符,產(chǎn)生對包產(chǎn)生對包括在丙相引用之間的括在丙相引用之間的所有單元格的引用所有單元格的引用(A5:A15),聯(lián)合運算符聯(lián)合運算符,將多個引將多個引用合并為一個引用用合并為一個引用SUM(A5:A15,C5:C15)(空格空格)交叉運算符交叉運算符,產(chǎn)生對兩產(chǎn)生對

4、兩個引用構(gòu)有的單元格個引用構(gòu)有的單元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可寫為: A4=SUM(B4:F4)第4頁/共69頁第5頁/共69頁第6頁/共69頁第7頁/共69頁第8頁/共69頁邏輯值: FALSE :需要完全一致 TRUE: 相似值亦可轉(zhuǎn)換第9頁/共69頁語法語法作用作用SUM(number1,number2, )返回單元格區(qū)域中所有數(shù)值的和返回單元格區(qū)域中所有數(shù)值的和ISPMT(Rate,Per,Nper,Pv)返回普通返回普通(無提保無提保)的利息償還的利息償還AVERAGE(Number1,Number2, )計算參數(shù)的算術(shù)平均數(shù)計

5、算參數(shù)的算術(shù)平均數(shù),參數(shù)可以是數(shù)值或包含數(shù)值參數(shù)可以是數(shù)值或包含數(shù)值的名稱數(shù)組和引用的名稱數(shù)組和引用IF(Logical_test,Value_if_true,Value_if_false)執(zhí)行真假判斷執(zhí)行真假判斷,根據(jù)對指定條件進行邏輯評價的真假根據(jù)對指定條件進行邏輯評價的真假而返回不同的結(jié)果。而返回不同的結(jié)果。HYPERLINK(Link_location,Friendly_name) 創(chuàng)建快捷方式創(chuàng)建快捷方式,以便打開文檔或網(wǎng)絡(luò)驅(qū)動器或連接連以便打開文檔或網(wǎng)絡(luò)驅(qū)動器或連接連接接INTERNETCOUNT(Value1,Value2, )計算參數(shù)表中的數(shù)字參數(shù)和包含數(shù)字的單元格的個數(shù)計算參

6、數(shù)表中的數(shù)字參數(shù)和包含數(shù)字的單元格的個數(shù)MAX(number1,number2, )返回一組數(shù)值中的最大值返回一組數(shù)值中的最大值,忽略邏輯值和文字符忽略邏輯值和文字符SIN (number)返回紀念品定角度的正弦值返回紀念品定角度的正弦值SUMIF (Range, Criteria, Sum_range)根據(jù)指定條伯對若干單元格求和根據(jù)指定條伯對若干單元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投資或貸款的等額分期償還額投資或貸款的等額分期償還額第10頁/共69頁注:Rate 貸款利率。Nper 該項貸款的付款總數(shù)。Pv 現(xiàn)值,或一系列未來付款的

7、當前值的累積和,也稱為本金。Fv 為未來值,或在最后一次付款后希望得到的現(xiàn)金余額,如果省略 fv則假設(shè)其值為零,也就是一筆貸款的未來值為零。Type 數(shù)字 0 或 1,用以指定各期的付款時間是在期初還是期末。第11頁/共69頁主要用于分析和處理日期值和時間值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函數(shù)DATA語法：DATE(year,month,day)第12頁/共69頁第13頁/共69頁主要用于進行各種各樣的計算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。例：A

8、BS函數(shù)語法：ABS(number)第14頁/共69頁第15頁/共69頁3.在上圖的對話框中,選擇函數(shù)分類下面的函數(shù)類型及函數(shù)名下面的對應(yīng)函數(shù)名。4. 單擊確定按鈕,彈出如圖所示的相應(yīng)的調(diào)用函數(shù)對話框：第16頁/共69頁第17頁/共69頁第18頁/共69頁第19頁/共69頁函數(shù)的使用函數(shù)的使用 (COUNTIF & SUMIF)(COUNTIF & SUMIF)SUMIFSUMIF函數(shù)函數(shù)函數(shù)名稱：函數(shù)名稱：SUMIFSUMIF主要功能：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。主要功能：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。使用格式：使用格式：SUMIFSUMIF（Range

9、,Criteria,Sum_RangeRange,Criteria,Sum_Range）參數(shù)說明：參數(shù)說明：RangeRange代表條件判斷的單元格區(qū)域；代表條件判斷的單元格區(qū)域；CriteriaCriteria為指定條件表達式；為指定條件表達式；Sum_RangeSum_Range代表需要計算的數(shù)值所在的單元格區(qū)域。代表需要計算的數(shù)值所在的單元格區(qū)域。第20頁/共69頁應(yīng)用舉例：如圖應(yīng)用舉例：如圖7 7所示所示, ,在在B64B64和和C64C64單元格中分別輸入公式：單元格中分別輸入公式：=SUBTOTAL(3,C2:C63)=SUBTOTAL(3,C2:C63)和和=SUBTOTAL10

10、3,C2:C63),=SUBTOTAL103,C2:C63),并且將并且將6161行隱藏行隱藏起來起來, ,確認后確認后, ,前者顯示為前者顯示為6262（包括隱藏的行）（包括隱藏的行）, ,后者顯示為后者顯示為61,61,不不包括隱藏的行。包括隱藏的行。特別提醒：如果采取自動篩選特別提醒：如果采取自動篩選, ,無論無論function_numfunction_num參數(shù)選用參數(shù)選用什么類型什么類型,SUBTOTAL,SUBTOTAL函數(shù)忽略任何不包括在篩選結(jié)果中的行；函數(shù)忽略任何不包括在篩選結(jié)果中的行；SUBTOTALSUBTOTAL函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域, ,

11、不適用于數(shù)據(jù)行或不適用于數(shù)據(jù)行或水平區(qū)域。水平區(qū)域。第21頁/共69頁第22頁/共69頁第23頁/共69頁TRUETRUE返回邏輯值返回邏輯值 TRUETRUE。語法語法TRUE( )TRUE( )說明說明可以直接在單元格或公式中鍵入可以直接在單元格或公式中鍵入值值 TRUETRUE，而可以不使用此函數(shù)。，而可以不使用此函數(shù)。函數(shù)函數(shù) TRUE TRUE 主要用于與其他電子表格主要用于與其他電子表格程序兼容。程序兼容。第24頁/共69頁第25頁/共69頁第26頁/共69頁第27頁/共69頁第28頁/共69頁第29頁/共69頁第30頁/共69頁第31頁/共69頁第32頁/共69頁第二步：選定需要

12、篩選的數(shù)據(jù)清單中任一單元格；第三步：選擇菜單”Data / Filter / Advanced Filter”第四步：在“條件區(qū)域”編輯框中，輸入條件區(qū)域的引用，并包括條件標志。第五步：若要更改篩選數(shù)據(jù)的方式，可更改條件區(qū)域中的值，并再次篩選數(shù)據(jù)。注：如果需要將篩選結(jié)果放到另一位置，請選擇”Copy to another Location”第33頁/共69頁請使用若要查找?（問號）任何單個字符例如，sm?th 查找“smith”和“smyth”*（星號）任何字符數(shù)例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 問號、星號或波形符例如，“fy91?

13、”將會查找“fy91?”通配符通配符以下通配符可作為篩選以及查找和替換內(nèi)容時的比較條件 :第34頁/共69頁第35頁/共69頁要分類匯總的列分類匯總步驟步驟:第36頁/共69頁要分類匯總的列分類匯總第37頁/共69頁第38頁/共69頁第39頁/共69頁數(shù)據(jù)透視表是交互式報表，可快速合并和比較大量數(shù)據(jù)。您可旋轉(zhuǎn)其行和列以看到源數(shù)據(jù)的不同匯總，而且可顯示感興趣區(qū)域的明細數(shù)據(jù)。 源數(shù)據(jù)第三季度高爾夫匯總的源值數(shù)據(jù)透視表C2 和 C8 中源值的匯總第40頁/共69頁何時應(yīng)使用數(shù)據(jù)透視表何時應(yīng)使用數(shù)據(jù)透視表如果要分析相關(guān)的匯總值，尤其是在要合計較大的列表并對每個數(shù)字進行多種比較時，可以使用數(shù)據(jù)透視表。在

14、上面所述報表中，用戶可以很清楚地看到單元格 F3 中第三季度高爾夫銷售額是如何通過其他運動或季度的銷售額或總銷售額計算出來的。由于數(shù)據(jù)透視表是交互式的，因此，您可以更改數(shù)據(jù)的視圖以查看更多明細數(shù)據(jù)或計算不同的匯總額，如計數(shù)或平均值。 數(shù)據(jù)是如何組織的數(shù)據(jù)是如何組織的在數(shù)據(jù)透視表中，源數(shù)據(jù)中的每列或字段都成為匯總多行信息的數(shù)據(jù)透視表字段。在上例中，“運動”列成為“運動”字段，高爾夫的每條記錄在單個高爾夫項中進行匯總。數(shù)據(jù)字段（如“求和項：銷售額”）提供要匯總的值。上述報表中的單元格 F3 包含的“求和項：銷售額”值來自源數(shù)據(jù)中“運動”列包含“高爾夫”和“季度”列包含“第三季度”的每一行。第41頁

15、/共69頁第42頁/共69頁第43頁/共69頁第44頁/共69頁第45頁/共69頁創(chuàng)建圖表圖表是Excel為用戶提供的強大功能,通過創(chuàng)建各種不同類型的圖表,為分析工作表中的各種數(shù)據(jù)提供更直觀的表示結(jié)果。l使用圖表向?qū)?下面我們先在工作薄中建立數(shù)據(jù)表如下頁圖所示,根據(jù)工作表中的數(shù)據(jù),創(chuàng)建不同類型的圖表。l圖表的編輯 1.插入數(shù)據(jù)標志及增加圖表標題 2.改變圖表的文字、顏色和圖案第46頁/共69頁第47頁/共69頁操作步驟如下： 1)選定工作表中包含所需數(shù)據(jù)的所有單元格。 2)單擊常用工具欄中的圖表向?qū)О粹o ,彈出圖（1-16）所示的對話框： 圖表的使用圖表的使用第48頁/共69頁第49頁/共69

16、頁圖表的使用（續(xù)圖表的使用（續(xù)2 2）4）然后繼續(xù)單擊下一步彈出圖對話框 選擇圖表存放的位置,若要將圖表放到另一個新的工作表上,請選中As new sheet單選框,然后作為新工作表插入文本框,鍵入新工作表的名字,默認為,如果選擇As object in單選框,可將： 第50頁/共69頁第51頁/共69頁圖表標題分類X軸數(shù)值Y軸第52頁/共69頁第53頁/共69頁數(shù)據(jù)標志數(shù)據(jù)標志說明說明category name category name 顯示指定到數(shù)據(jù)點的分類顯示指定到數(shù)據(jù)點的分類valuevalue顯示數(shù)據(jù)點的值顯示數(shù)據(jù)點的值percentage percentage 對于餅圖、圓環(huán)圖顯

17、示出對整體的百分比對于餅圖、圓環(huán)圖顯示出對整體的百分比bubble sizebubble size對于離散圖、氣泡圖顯示出氣泡的百分比對于離散圖、氣泡圖顯示出氣泡的百分比第54頁/共69頁若要對標題進行編輯,可將鼠標移至標題的左下角處雙擊,即會出現(xiàn)右圖所示對話框,用該對話框可以對標題的字體、字體的背景色等進行修改。第55頁/共69頁若要對圖表區(qū)域進行修改,可將鼠標移至圖表的空白區(qū)域雙擊鼠標,則出現(xiàn)圖（1-23）對話框,在此對話框中可選定要修改的內(nèi)容。第56頁/共69頁若要對圖例進行修改,可將鼠標移至圖例區(qū)雙擊鼠標,則出現(xiàn)如圖（1-24）對話框。在此對話框中可對圖例進行編輯,如圖案、字體及位置。

18、第57頁/共69頁復(fù)選框復(fù)選框 可通過選中或清除來打開或關(guān)閉的選項。您可以在一個工作表中同時選中多個復(fù)選框。 文本框文本框 可以向其中鍵入文字的框。命令按鈕命令按鈕 單擊可啟動某項操作的按鈕。選項按鈕選項按鈕 用于從一組選項中選擇某一選項的按鈕。列表框列表框 包含項目列表的框。組合框組合框 含有下拉列表框的文本框。您可以從列表中選擇,或在框中鍵入所需內(nèi)容。切換按鈕切換按鈕 單擊后保持按下狀態(tài),再單擊時又彈起的按鈕。微調(diào)按鈕微調(diào)按鈕 可附加在單元格上或文本框上的按鈕。若要增加數(shù)值,請單擊向上箭頭；若要減小數(shù)值,請單擊向下箭頭。滾動條滾動條 可通過單擊滾動箭頭或拖動滾動塊來滾動數(shù)據(jù)區(qū)域的一種控件。

19、單擊滾動箭頭與滾動塊之間的區(qū)域時,可以滾動整頁數(shù)據(jù)。標簽標簽 添加到工作表或窗體中,用以提供有關(guān)控件、工作表或窗體信息的文本。圖像圖像 將圖片嵌入到窗體中的控件。其他控件其他控件其他 ActiveX 控件的列表。第58頁/共69頁關(guān)于宏關(guān)于宏如果經(jīng)常在 Microsoft Excel 中重復(fù)某項任務(wù),那么可以用宏自動執(zhí)行該任務(wù)。宏是一系列命令和函數(shù),存儲于 Visual Basic 模塊中,并且在需要執(zhí)行該項任務(wù)時可隨時運行。例如,如果經(jīng)常在單元格中輸入長文本字符串,則可以創(chuàng)建一個宏來將單元格格式設(shè)置為文本可自動換行。錄制宏錄制宏 在錄制宏時,Excel 在您執(zhí)行一系列命令時存儲該過程的每一步

20、信息。然后即可運行宏來重復(fù)所錄制的過程或“回放”這些命令。如果在錄制宏時出錯,所做的修改也會被錄制下來。Visual Basic 在附屬于某工作薄的新模塊中存儲每個宏。第59頁/共69頁使宏易于運行使宏易于運行 可以在“宏”對話框的列表中選擇所需的宏并運行宏。如果希望通過單擊特定按鈕或按下特定組合鍵來運行宏,可將宏指定給某個工具欄按鈕、鍵盤快捷鍵或工作表中的圖形對象。管理宏管理宏 宏錄制完后,可用 Visual Basic 編輯器查看宏代碼以進行改錯或更改宏的功能。例如,如果希望用于文本換行的宏還可以將文本變?yōu)榇煮w,則可以再錄制另一個將單元格文本變?yōu)榇煮w的宏,然后將其中的指令復(fù)制到用于文本換行

21、的宏中?！癡isual Basic 編輯器”是一個為初學者設(shè)計的編寫和編輯宏代碼的程序,而且提供了很多聯(lián)機幫助。不必學習如何編程或如何用 Visual Basic 語言來對宏進行簡單的修改。利用“Visual Basic 編輯器”,您可以編輯宏、在模塊間復(fù)制宏、在不同工作簿之間復(fù)制宏、重命名存儲宏的模塊或重命名宏。第60頁/共69頁宏安全性宏安全性 Excel 對可通過宏傳播的病毒提供安全保護。如果您與其他人共享宏,則可使用數(shù)字簽名來驗證其他用戶,這樣就可保證其他用戶為可靠來源。無論何時打開包含宏的工作簿,都可以先驗證宏的來源再啟用宏。第61頁/共69頁創(chuàng)建宏創(chuàng)建宏 錄制宏將安全級設(shè)置為“中”

22、或“低”。 操作方法在“工具”菜單上,單擊“選項”。 單擊“安全性”選項卡。 在“宏安全性”之下,單擊“宏安全性”。 單擊“安全級”選項卡,再選擇所要使用的安全級。 在“工具”菜單上,指向“宏”,再單擊“錄制新宏”。 在“宏名”框中,輸入宏 的名稱。 注意注意宏名的首字符必須是字母,其他字符可以是字母、數(shù)字或下劃線。宏名中不允許有空格；可用下劃線作為分詞符。宏名不允許與單元格引用重名,否則會出現(xiàn)錯誤信息顯示宏名無效。如果要通過按鍵盤上的來運行宏, 請在“快捷鍵”框中,輸入一個字母?？捎?Ctrl+字母（小寫字母）或 Ctrl+Shift+字母（大寫字母）,其中字母可以是鍵盤上的任意字母鍵?？旖萱I字母不允許是數(shù)字或特殊字符（如 或 #）。 注釋注釋 當包含宏的工作簿打開時,宏快捷鍵優(yōu)先于任何相當?shù)?Microsoft Excel 的默認快捷鍵。第62頁/共69頁在“保存在”框中,單擊要存放宏的地址。 如果要使宏在使用 Excel 的任何時候都可用,請選中“個人宏工作簿”。如果要添加有關(guān)宏的說明,請在“說明”框中鍵入該說明。單擊“確定”。 如果要使宏相對于活動單元格位置運行,請用相對單元格引用來錄制該宏。在“停止錄制”工具欄上,單擊“相對引用” 以將其選