1、 Excel 是目前最佳的電子表格系統，它使電子表格軟件的功能、操作的簡易性，都進入了一個新的境界。系統具有人工智能的特性，它可以在某些方面判斷用戶的下一步操作，使操作大為簡化，用戶可以得心應手地使用Excel。 Excel具有強有力的數據處理能力，數據庫管理功能，和數據分析功能。 Excel的出現，取代了過去需要多個系統才能完成的工作，必將在用戶的工作中起到越來越大的作用。計計劃劃財財務務部部2005/12/7EXCEL在管理及財務中的運用培培訓訓提提綱綱基本操作數據處理調用函數表頁處理圖表操作數據管理保護數據宏計計劃劃財財務務部部2005/12/7EXCEL在管理及財務中的運用序序號號提提

2、綱綱內內 容容一一基基本本操操作作1.工具-選項的應用2.數據類型:文本型/數值型/日期時間型3.單元格名稱定義兩種方法:(1)直接定義(2)在插入-名稱-定義中定義4.粘貼:CTRL+C與復制:CTRL+V5.按CTRL,拖動鼠標復制6.公式刷應用7.鼠標右鍵,設置單元格式應用8.日期快捷:CTRL+分號9.時間快捷:CTRL+SHIFT+分號10.永久性刪除快捷鍵:SHIFT+DEL11.右下圖標的計算功能二二數數據據處處理理1.快速錄入數據的方法(1)自定義序列:工具-選項(2)自動填充:鼠標拖拽(3)自動填充:CTRL+ENTER(4)將數值型數據轉換成文本型數據(5)運用編輯-填充命

3、令(6)單元格選擇列表:ALT+下箭頭(7)單元格挑選功能-有效性應用(8)單元格有效性清除(9)鼠標右鍵拖放功能(10)參數三種引用方式,F4快捷鍵的應用例題2.清除命令 通過編輯-清除格式查看數據,如:3.3333333333333300000000000000003.打印批注文件-頁面設置-工作表4.選擇性粘貼(1)粘貼數值(2)運算功能(3)行列轉置(4)粘貼鏈接,具有指針作用,展示OFFICE間的應用5.隱藏部分的定位粘貼6.出錯信息解釋錯誤一：# 數值型、日期型數據列寬不足錯誤二：#VALUE 數據類型不符錯誤三：#DIV/0! 除數為零錯誤四：#NAME 找不到名字或函數名出錯錯

4、誤五：#N/A 沒有結果，但未必是一種錯誤錯誤六：#REF！ 丟失參數（注意鼠標左鍵刪除與DEL鍵的區別）例題7.循環引用的問題例題三三調調用用函函數數1.有關函數的約定（1）所有公式在英文狀態下輸入（2）左右兩個括號必須成對出現,括號前不能有空格2.函數輸入方法（1）直接輸入法（2）粘貼輸入法兩種方法: 菜單插入-函數選中單元格,敲上=,在名稱框中選擇3.運算符算術運算符：+、-、*、/、%、 比較運算符：=、=、=、 文本運算符：& 引用運算符：冒號，逗號4.函數應用介紹數學與三角函數類（SUM、SUMIF、INT） 例題統計類函數（COUNT、COUNTA、COUNTIF、AVE

5、RAGE） 例題文本類函數（MID 、FIND) 例題邏輯類函數（IF、AND、OR） 例題查找類函數（VLOOKUP、INDEX） 例題數據庫函數（DSUM、DAVERAGE、DCOUNT、DCOUNTA）5.應用練習1工資計算表6.應用練習2應收帳明細表四四表表頁頁處處理理多個工作表基本操作 SHIFT鍵的應用合并多個工作表數據 通過多個工作表選定 通過填充-至同組工作表復制數據合并多個工作簿 數據-合并計算的應用保存工作區 保存當前所有EXCEL文件:文件菜單 關閉當前所有EXCEL文件:SHIFT+文件菜單五五圖圖表表操操作作創建圖表 F11快捷鍵的應用例題編輯圖表 圖表框的功能應用格

6、式化圖表 針對數據源行列變動的情況 主體數據前后移位例題建立組合圖例題六六數數據據管管理理數據排序數據篩選例題分類匯總例題數據透視表例題單變量求解例題模擬運算表例題回歸分析例題規劃求解例題七七保保護護數數據據1.工作表保護2.工作簿保護3.鎖定格式4.隱藏公式八八宏宏1.錄制新宏例題2.運行宏3.創建圖標:工具-自定義目的:替代重復性的工作品名一月二月三月四月五月A產品66,565.0045.0045.45B產品-5,453.35C產品0.34D產品4.45C產品94.46小計66,565.0045.004.455,548.1445.45行政部行政部C產品合計B產品666554.5A產品545

7、33.45A產品3.4A產品44.53C產品944.56C產品722080.44B產品今今日日飲飲料料零零售售情情況況統統計計表表日期：2005/12/7利潤率：30%名稱包裝單位零售單價銷售量銷售額利潤評估可樂聽3.00120360.00108.00高雪碧聽2.8098274.4082.32正常美年達聽2.8097271.6081.48正常健力寶聽2.9080232.0069.60正常紅牛聽6.004532,718.00815.40高橙汁聽2.60140364.00109.20高汽水瓶1.50136204.0061.20正常啤酒瓶2.00110220.0066.00正常酸奶瓶1.209711

8、6.4034.92低礦泉水瓶2.3088202.4060.72正常合計14194,962.801,488.84高實現功能:進行公式定義以及格式調整IF函數的應用顏色定義存款到期利息表存期0.512358利率0.0190.0230.0380.0410.0510.068本金1000.9502.3007.60012.30025.50054.4002001.9004.6015.2024.6051.00108.805004.75011.5038.0061.50127.50272.0010009.50023.0076.00123.00255.00544.00200019.00046.00152.00246

9、.00510.001088.00500047.500115.00380.00615.001275.002720.001000095.000230.00760.001230.002550.005440.00編號工資納稅基數扣稅612000108001785編號工資納稅基數扣稅納稅基數稅率19000005%22500130010550010%345003300370200015%460004800595500020%5800068009852000025%6120001080017854000030%7200001880033856000035%8260002480048258000040%940

10、00038800832510000045%50006255575280029537052500250375050006255875VLOOKUP函數TRUE與FALSE的應用速算扣除率0251253751375337563751037515375消費結算單費用項目金 額餐費546.05房費254.00合 計800.05人人民民幣幣（大大寫寫）：8 80 00 0元元 零零5 5分分大酒店經辦人：集團公司經辦人：消費結算單費用項目金 額餐費546.05房費254.00合 計800.05人民幣大寫800元大酒店經辦人：集團公司經辦人：掌握: int取整函數應用設置單元格式的特殊應用設置單元格式的自

11、定義應用編號：備 注第一聯：大酒店留存￥800.05集團公司審批人：編號：備 注第二聯：集團公司留存￥800.05集團公司審批人：消費結算單消費結算單34,353,553,534.002005/12/73 JHJ #VALUE!100#DIV/0!3446760查詢:5#N/A編號工資190022500341004570039100272700namepricequanamountdatetvc200878175600 2004/2/19rec200244800 2004/2/20tvc4003614400 2004/2/21dfe500767383500 2004/2/22dfe600462

12、7600 2004/2/23rec3433411662190000 單個條件匯總求和條件namequan187262 多個條件匯總求和,LJC為數據定義范圍名稱tvc1005 公式中帶A與不帶A區別為取數值類型或文本類型rec302 計數函數4 公式中帶A與不帶A區別為取數值類型或文本類型373.83計算選定范圍的平均數掌握: SUMIF的應用DSUM的應用COUNT的應用COUNTIF的應用DCOUNTA的應用AVERAGE的應用單個條件匯總求和多個條件匯總求和,LJC為數據定義范圍名稱公式中帶A與不帶A區別為取數值類型或文本類型公式中帶A與不帶A區別為取數值類型或文本類型計算選定范圍的平均

13、數憑證號代碼科目借方貸方日期001E14103 管理費用/午餐費225.000.0003/8/17001A11101 銀行存款/人民幣戶0.00225.0003/8/18002B11101 應付票據/A客戶8,460.000.0003/8/19002D20111 制造費用/間接材料41,040.000.0003/8/20002A10102 營業收入/拆裝箱0.0049500.0003/8/21003A11101 銀行存款/人民幣戶30,000.000.0003/8/22003A11101 銀行存款/人民幣戶0.0030,000.0003/8/23004A19102 其他應收款/應收個人款15,

14、000.000.0003/8/24004A11101 銀行存款/人民幣戶0.0015,000.0003/8/25005E14107 管理費用/辦公費1,464.700.0003/8/26005D10101 生產成本/直接材料0.001464.7003/8/27006E14107 管理費用/辦公費20,660.000.0003/8/28006A11101 銀行存款/人民幣戶0.005,660.0003/8/29006A19102 其他應收款/應收個人款0.0015,000.0003/8/30007E14107 管理費用/辦公費14.900.0003/8/31007E14110 管理費用/燃料費1

15、59.700.0003/9/1007A10101 現金/人民幣0.00174.6003/9/2掌握:一級名稱二級名稱金額管理費用03午餐費225.00銀行存款01人民幣戶-225.00應付票據01A客戶-8,460.00制造費用11間接材料-41,040.00營業收入02拆裝箱-49,500.00銀行存款01人民幣戶30,000.00銀行存款01人民幣戶-30,000.00其他應收款02應收個人款15,000.00銀行存款01人民幣戶-15,000.00管理費用07辦公費1,464.70生產成本01直接材料1,464.70管理費用07辦公費20,660.00銀行存款01人民幣戶-5,660.0

16、0其他應收款02應收個人款-15,000.00管理費用07辦公費14.90管理費用10燃料費159.70現金01人民幣-174.60MID的應用OR的應用FIND的應用&的應用關關于于循循環環引引用用的的問問題題收入100成本50贊助6.52利潤43.48循循環環引引用用問問題題可可在在：“工工具具選選項項重重新新計計算算”，選選擇擇迭迭帶帶計計算算職工工資計算表工號姓名工資獎金事假天數 病假天數扣工資扣獎金 實發工資001張三180050000002300002李四2100600100-202680003王五160035006-320-601570004趙六195050070-585

17、-1401725005陳七235080000003150要求：扣除部分以負值給出。條件：當月病假天數在5天以內，不扣工資；大于5天扣全月工資20%；當月事假天數在5天以內，不扣工資；大于5天扣全月工資30%。當月病、事假合計在10天以內，每天病假扣獎金10元；每天事假扣獎金20元；大于10天，停發當月獎金。應應收收帳帳明明細細表表制表人：AA制表日期：編編號號公公司司名名稱稱金金額額欠欠款款日日期期A3006Motorola346051999/7/15編編號號公公司司名名稱稱金金額額欠欠款款日日期期A1001Hipp-Media￥187,000.001995/5/20A1002Lekai fi

18、lm￥78,000.001997/4/16A1003Hipp-Media￥95,000.001999/4/16A1004Lekai film￥58,450.001999/12/5A1005Intel￥72,000.001998/8/1A1006Zero Investigating Company￥25,146.001999/1/28A1007Zhongbei￥55,051.001999/1/20A2001Sanxia lease￥29,362.002001/11/26A2002Great-wall advertising￥31,067.001999/9/18A2003Phenix￥56,386

19、.002001/6/11A2004Beijing BTA￥36,177.002002/7/7A2005Golden-butterfly software￥30,410.002002/2/22A3001Fujianshida￥49,897.002001/9/14A3002HP China￥26,790.002000/10/12A3003Maxmize￥34,379.001998/10/13A3004Ericson￥70,934.002001/2/25A3005Guanglian Ads.￥69,070.002000/5/6A3006Motorola￥34,605.001999/7/15要求：(1

20、)用INT函數完成對欠款年限的計算。 (2)建立對公司名稱，金額，欠款日期，欠款金額的查詢。12月7日欠欠款款年年限限77欠欠款款年年限限(月月）12810580738983834975544146516287586877應應收收帳帳明明細細表表玲玲花店 東東花店 明明花店玫瑰98096百合155699滿天星1361761301999年2000年2001年銷貨收入3,0003,1003,500銷貨成本2,3002,7002,600銷貨毛利700400900營業費用505255營業利潤650348845營業外收入投資收入431555租金收入505152雜項收入250170200營業外收入合計34

21、3236307營業外費用利息費用(￥18)(￥20)(￥25)投資損失(￥70)(￥100)(￥80)租賃費用(￥100)(￥120)(￥105)雜項支出(￥350)(￥240)(￥400)營業外費用合計(￥538)(￥480)(￥610)稅前盈余￥455￥104￥542所得稅（30%）￥137￥31￥163稅后凈利（損）￥319￥73￥3793,500 3,000 2,500 2,000 1,500 1,000 500 0 1999年2000年2001年銷貨收入營業費用稅后凈利（損）銷貨收入營業費用稅后凈利（損）3,500 3,000 2,500 2,000 1,500 1,000 500

22、0 1999年2000年2001年銷貨收入營業費用稅后凈利（損）銷貨收入營業費用稅后凈利（損）3,500 3,000 2,500 2,000 1,500 1,000 500 0 1999年2000年2001年銷貨收入營業費用稅后凈利（損）銷貨收入營業費用稅后凈利（損）全球網絡廣告收入增長情況 年份全球網絡廣告收入1994年281995年1371996年2671997年9061998年15001994年1995年1996年1997年1998年0%10%20%30%40%50%60%02004006008001000120014001600百分比全球網絡廣告收入百分比1%5%9%32%53%全球網

23、絡廣告收入增長情況1994年1995年1996年1997年1998年0%10%20%30%40%50%60%02004006008001000120014001600百分比全球網絡廣告收入華北區華東區華南區January799453434February321141334March60643942April34323810May100134665June80043549July56134469August87232436September217344234October261343422November34617330December23499843華北區華東區華南區12December234

24、99843index函數的應用December華北區華東區華南區020040060080010001200DecemberDecember通過INDEX在B17設立公式,選定B16:E17進行插入圖表,選中圖表框,將鼠標放置編輯框,點擊右鍵,選擇窗體,在圖表中輸入窗體 ,點擊窗體點擊右鍵,選擇設置控件格式,選擇數據列,建立鏈接,即可December華北區華東區華南區020040060080010001200DecemberDecemberTaskStart DateDurationEnd Date預算會議2002年12月29日1預算模板2002年12月30日11預算模板與關鍵假設下發2003年

25、1月13日9子單元編制2003年1月16日15集團合并子公司預算2003年1月16日18預算分析2003年2月3日4編寫報告2003年2月9日12發送初稿2003年2月23日1征集建議2003年2月24日4終稿完成2003年3月2日5提交董事會2003年3月9日1董事會會議2003年3月17日1時時間間計計劃劃圖圖-甘甘特特圖圖添加堆積條形編號品名供應商數量A003圓珠筆丙700A003圓珠筆丙220A003圓珠筆丙220A003圓珠筆丙400A003圓珠筆丙220A003圓珠筆丙100A001書甲110A004鉛筆甲200A001書甲500A001書甲300A004鉛筆甲100A002彩色筆

26、乙500A005筆記本乙100A002彩色筆乙500A005筆記本乙200A002彩色筆乙200高高級級篩篩選選的的應應用用編號品名A001書A004鉛筆供應商數量甲110甲100編號品名供應商數量A003圓珠筆丙700A002彩色筆乙500A001書甲300A003圓珠筆丙100A001書甲110A005筆記本乙200A001書甲500A003圓珠筆丙400A004鉛筆甲100A002彩色筆乙500A005筆記本乙100A003圓珠筆丙220A004鉛筆甲200A002彩色筆乙200分分類類匯匯總總應應用用分類匯總前需要先排序要列示多個匯總項目,只需將分類匯總中取消替換當前的分類匯總月份店名

27、數據1234朝陽分店求和項:數量10799 ￥149.00142求和項:銷售額2470022800 ￥34,540.0032720求和項:利潤74106840 ￥10,362.009816海店分店求和項:數量250158 ￥148.00169求和項:銷售額5600036280 ￥33,740.0037340求和項:利潤1680010884 ￥10,122.0011202西城分店求和項:數量193160 ￥135.00155求和項:銷售額4430037040 ￥30,900.0035500求和項:利潤1329011112 ￥9,270.0010650求和項:數量 的求和550417 ￥432.0

28、0466求和項:銷售額 的求和12500096120 ￥99,180.00105560求和項:利潤 的求和3750028836 ￥29,754.0031668創建計算字段公式計算字段,可以利潤演示567891085 ￥101.009174 ￥94.005019700 ￥23,560.002018017140 ￥21,680.00130005910 ￥7,068.0060545142 ￥6,504.003900149 ￥159.00196202 ￥197.0020133940 ￥36,960.004316047480 ￥45,700.004506010182 ￥11,088.0012948142

29、44 ￥13,710.0013518173 ￥179.00187194 ￥207.0021439700 ￥41,140.004292044560 ￥47,520.004916011910 ￥12,342.001287613368 ￥14,256.0014748407 ￥439.00474470 ￥498.0046593340 ￥ 101,660.00106260109180 ￥ 114,900.0010722028002 ￥30,498.003187832754 ￥34,470.00321661112 總計118 ￥187.00129727080 ￥41,120.002982208124 ￥1

30、2,336.0089466235 ￥226.00229053900 ￥52,160.0052172016170 ￥15,648.00156516219 ￥227.00224350340 ￥52,120.0051520015102 ￥15,636.00154560572 ￥640.005830131320 ￥ 145,400.00133514039396 ￥43,620.00400542店店名名軟軟件件名名稱稱月月份份數數量量單單價價銷銷售售額額海店分店KILL殺毒軟件1100 ￥260.00￥26,000.00海店分店KV殺毒軟件1150 ￥200.00￥30,000.00海店分店KILL殺毒

31、軟件278 ￥260.00￥20,280.00海店分店KV殺毒軟件280 ￥200.00￥16,000.00海店分店KILL殺毒軟件369 ￥260.00￥17,940.00海店分店KV殺毒軟件379 ￥200.00￥15,800.00海店分店KILL殺毒軟件459 ￥260.00￥15,340.00海店分店KV殺毒軟件4110 ￥200.00￥22,000.00海店分店KILL殺毒軟件569 ￥260.00￥17,940.00海店分店KV殺毒軟件580 ￥200.00￥16,000.00海店分店KILL殺毒軟件686 ￥260.00￥22,360.00海店分店KV殺毒軟件673 ￥200.0

32、0￥14,600.00海店分店KILL殺毒軟件766 ￥260.00￥17,160.00海店分店KV殺毒軟件7130 ￥200.00￥26,000.00海店分店KILL殺毒軟件8118 ￥260.00￥30,680.00海店分店KV殺毒軟件884 ￥200.00￥16,800.00海店分店KILL殺毒軟件9105 ￥260.00￥27,300.00海店分店KV殺毒軟件992 ￥200.00￥18,400.00海店分店KILL殺毒軟件1081 ￥260.00￥21,060.00海店分店KV殺毒軟件10120 ￥200.00￥24,000.00海店分店KILL殺毒軟件11115 ￥260.00￥2

33、9,900.00海店分店KV殺毒軟件11120 ￥200.00￥24,000.00海店分店KILL殺毒軟件12116 ￥260.00￥30,160.00海店分店KV殺毒軟件12110 ￥200.00￥22,000.00朝陽分店KILL殺毒軟件155 ￥260.00￥14,300.00朝陽分店KV殺毒軟件152 ￥200.00￥10,400.00朝陽分店KILL殺毒軟件250 ￥260.00￥13,000.00朝陽分店KV殺毒軟件249 ￥200.00￥9,800.00朝陽分店KILL殺毒軟件379 ￥260.00￥20,540.00朝陽分店KV殺毒軟件370 ￥200.00￥14,000.00

34、朝陽分店KILL殺毒軟件472 ￥260.00￥18,720.00朝陽分店KV殺毒軟件470 ￥200.00￥14,000.00朝陽分店KILL殺毒軟件545 ￥260.00￥11,700.00朝陽分店KV殺毒軟件540 ￥200.00￥8,000.00朝陽分店KILL殺毒軟件656 ￥260.00￥14,560.00朝陽分店KV殺毒軟件645 ￥200.00￥9,000.00朝陽分店KILL殺毒軟件733 ￥260.00￥8,580.00朝陽分店KV殺毒軟件758 ￥200.00￥11,600.00朝陽分店KILL殺毒軟件839 ￥260.00￥10,140.00朝陽分店KV殺毒軟件835

35、￥200.00￥7,000.00朝陽分店KILL殺毒軟件948 ￥260.00￥12,480.00朝陽分店KV殺毒軟件946 ￥200.00￥9,200.00朝陽分店KILL殺毒軟件1050 ￥260.00￥13,000.00朝陽分店KV殺毒軟件1255 ￥200.00￥11,000.00朝陽分店KILL殺毒軟件1158 ￥260.00￥15,080.00朝陽分店KV殺毒軟件1160 ￥200.00￥12,000.00朝陽分店KILL殺毒軟件1262 ￥260.00￥16,120.00朝陽分店KV殺毒軟件1270 ￥200.00￥14,000.00西城分店KILL殺毒軟件195 ￥260.00

36、￥24,700.00西城分店KV殺毒軟件198 ￥200.00￥19,600.00西城分店KILL殺毒軟件284 ￥260.00￥21,840.00西城分店KV殺毒軟件276 ￥200.00￥15,200.00西城分店KILL殺毒軟件365 ￥260.00￥16,900.00西城分店KV殺毒軟件370 ￥200.00￥14,000.00西城分店KILL殺毒軟件475 ￥260.00￥19,500.00西城分店KV殺毒軟件480 ￥200.00￥16,000.00西城分店KILL殺毒軟件585 ￥260.00￥22,100.00西城分店KV殺毒軟件588 ￥200.00￥17,600.00西城分

37、店KILL殺毒軟件689 ￥260.00￥23,140.00西城分店KV殺毒軟件690 ￥200.00￥18,000.00西城分店KILL殺毒軟件792 ￥260.00￥23,920.00西城分店KV殺毒軟件795 ￥200.00￥19,000.00西城分店KILL殺毒軟件896 ￥260.00￥24,960.00西城分店KV殺毒軟件898 ￥200.00￥19,600.00西城分店KILL殺毒軟件9102 ￥260.00￥26,520.00西城分店KV殺毒軟件9105 ￥200.00￥21,000.00西城分店KILL殺毒軟件10106 ￥260.00￥27,560.00西城分店KV殺毒軟件

38、10108 ￥200.00￥21,600.00西城分店KILL殺毒軟件11109 ￥260.00￥28,340.00西城分店KV殺毒軟件11110 ￥200.00￥22,000.00西城分店KILL殺毒軟件12112 ￥260.00￥29,120.00西城分店KV殺毒軟件12115 ￥200.00￥23,000.00計算方法第一種第二種貸款金額￥350,000.00￥350,000.00貸款年利率5%5%償還貸款所需時間206.911168122每月償還金額￥-2,309.85￥-5,000.00次數44本金￥-862.20￥-862.20利息￥-1,447.65￥-1,447.65第一種方法

39、:函數應用：PMT、PPMT、IPMT第二種方法:工具-單變量求解,如每月還5000元,需還幾年.目標單元格為C4,目標值為-5000,可變單元格為C3貸款金額￥350,000.00貸款年利率5%償還貸款所需時間20每月償還金額￥-2,309.85￥-2,309.8510￥-3,712.29￥-2,309.8515￥-2,767.7825￥-2,046.0730￥-1,878.88通過10年15年25年30年與20年月還款的比較操作:數據-模擬運算表,年限按列放,則將B16放于列,在行則放入行10152530￥-3,712.29￥-2,767.78￥-2,046.07￥-1,878.88第二種方法:工具-單變量求解,如每月還5000元,需還幾年.目標單元格為C4,目標值為-5000,可變單元格為C3銷量成本1990年80701991年100651992年120901993年140951994年1601101995年1801151996年2001201997年2201401998年2401551999年260150XY閃閃點點圖圖,通通過過出出入入圖圖表表做做,添添加加趨趨勢勢線線,趨趨勢勢線線格格式式顯顯示示公公式式Y=MX+B可得M=0.5,B=24.46R2為可信度