EXCEL教程 數(shù)據(jù)分析與管理PPT學(xué)習(xí)教案

會(huì)計(jì)學(xué)1EXCEL教程教程 數(shù)據(jù)分析與管理數(shù)據(jù)分析與管理建立合并計(jì)算建立合并計(jì)算 所謂合并計(jì)算，是指用來(lái)匯總一個(gè)或多個(gè)源區(qū)域中的數(shù)據(jù)的方法。Excel提供了兩種合并計(jì)算數(shù)據(jù)的方法：一是按位置合并計(jì)算，即將源區(qū)域中相同位置的數(shù)據(jù)匯總；二是按分類合并計(jì)算，當(dāng)源區(qū)域中沒(méi)有相同的布局時(shí)，采用分類方式進(jìn)行匯總。1按位置合并計(jì)算數(shù)據(jù)按位置合并計(jì)算數(shù)據(jù) 按位置合并計(jì)算數(shù)據(jù)，是指在所有源區(qū)域中的數(shù)據(jù)被相同地排列以進(jìn)行合并計(jì)算，也就是說(shuō)從每一個(gè)源區(qū)域中合并計(jì)算的數(shù)值必須在被選定源區(qū)域的相同位置上。第1頁(yè)/共50頁(yè)“飛揚(yáng)分公司C”同前兩個(gè)工作表不在一個(gè)工作簿中輸入各分公司工作表中的數(shù)據(jù) 第2頁(yè)/共50頁(yè)為合并計(jì)算的數(shù)據(jù)選定目的區(qū)。選擇“飛揚(yáng)總公司”工作表為當(dāng)前工作表，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 第3頁(yè)/共50頁(yè) 選擇要引用的數(shù)據(jù)區(qū)域 第4頁(yè)/共50頁(yè) 第5頁(yè)/共50頁(yè) 合并后的所有數(shù)據(jù) 添加“飛揚(yáng)分公司B”的數(shù)據(jù)引用區(qū)域 第6頁(yè)/共50頁(yè)當(dāng)進(jìn)行合并計(jì)算時(shí)，還可以引用不同工作簿中的工作表區(qū)域，本例中“飛揚(yáng)分公司C”與其他兩個(gè)分公司的工作表不在同一工作簿中，操作時(shí)應(yīng)先打開(kāi)“飛揚(yáng)分公司C”工作表。添加飛揚(yáng)分公司A和B的引用區(qū)域 切換到“飛揚(yáng)分公司C”工作表所在工作簿 第7頁(yè)/共50頁(yè) 在打開(kāi)的工作簿中選擇“飛揚(yáng)分公司C”工作表并選擇引用區(qū)域 添加所有引用數(shù)據(jù)區(qū)域 合并后的所有數(shù)據(jù) 第8頁(yè)/共50頁(yè)2通過(guò)分類來(lái)合并計(jì)算數(shù)據(jù)通過(guò)分類來(lái)合并計(jì)算數(shù)據(jù) 通過(guò)分類來(lái)合并計(jì)算數(shù)據(jù)，是指當(dāng)多重來(lái)源區(qū)域包含相似的數(shù)據(jù)卻以不同的方式排列時(shí)，可依不同分類進(jìn)行數(shù)據(jù)的合并計(jì)算。也就是說(shuō)，當(dāng)選定的表格具有不同的內(nèi)容時(shí)，我們可以根據(jù)這些表格的分類來(lái)分別進(jìn)行合并計(jì)算。下面以某班的成績(jī)統(tǒng)計(jì)為例，具體介紹如何利用Excel合并計(jì)算功能來(lái)登記考分，提高工作效率。第9頁(yè)/共50頁(yè) 新建一個(gè)工作簿，并在其中建立6個(gè)工作表 第10頁(yè)/共50頁(yè) 在“匯總”工作表中單擊合并計(jì)算數(shù)據(jù)目標(biāo)區(qū)域左上角的單元格A1，然后 選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇“求和”函數(shù)，然后單擊“引用位置”輸入框后面的折疊按鈕 第11頁(yè)/共50頁(yè) 在“語(yǔ)文”工作表中，用鼠標(biāo)拖動(dòng)選定“姓名”和“語(yǔ)文”兩列數(shù)據(jù) 單擊“添加”按鈕，將選擇的源數(shù)據(jù)添加到“所有引用位置”列表框中 第12頁(yè)/共50頁(yè) 將“英語(yǔ)”、“數(shù)學(xué)”、“化學(xué)”和“物理”等工作表的數(shù)據(jù)重復(fù)執(zhí)行上述步驟，將他們所在的地址全部添加到“所有引用位置”列表框中 在“標(biāo)簽位置”區(qū)選擇“首行”和“最左列”復(fù)選框 第13頁(yè)/共50頁(yè)每個(gè)學(xué)生的各科成績(jī)匯總在一起 第14頁(yè)/共50頁(yè)3合并計(jì)算的自動(dòng)更新合并計(jì)算的自動(dòng)更新 單擊這些+號(hào)可查看可查看相關(guān)合并計(jì)算的明細(xì)數(shù)據(jù)，例如查看源區(qū)域中的明細(xì)數(shù)據(jù) 如果希望數(shù)據(jù)源改變時(shí)，Excel自動(dòng)更新合并的工作表，只需在“合并計(jì)算”對(duì)話框中選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復(fù)選框即可 第15頁(yè)/共50頁(yè)對(duì)于一個(gè)已建立合并計(jì)算的工作表文件，還可以做進(jìn)一步的編輯，例如為合并計(jì)算增加源區(qū)域。但必須注意，只有對(duì)沒(méi)有建立與源區(qū)域鏈接的合并工作表，才能夠執(zhí)行編輯操作。1為合并計(jì)算增加源區(qū)域?yàn)楹喜⒂?jì)算增加源區(qū)域 單擊合并計(jì)算結(jié)果工作表任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 第16頁(yè)/共50頁(yè)選擇“政治”工作表中的引用區(qū)域，然后單擊折疊按鈕返回“合并計(jì)算”對(duì)話框 單擊折疊按鈕第17頁(yè)/共50頁(yè)增加源區(qū)域后的工作表 單擊“添加”按鈕，將“政治”添加到“所有引用位置”列表中 第18頁(yè)/共50頁(yè) 單擊合并計(jì)算結(jié)果工作表中任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇要?jiǎng)h除的源區(qū)域，如物理，然后單擊“刪除”按鈕第19頁(yè)/共50頁(yè)單擊“合并計(jì)算”對(duì)話框中的“確定”按鈕，結(jié)果得到刪除物“理源”區(qū)域后的工作表 第20頁(yè)/共50頁(yè)3改變?cè)磪^(qū)域的引用改變?cè)磪^(qū)域的引用 單擊合并計(jì)算結(jié)果工作表中任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單，打開(kāi)“合并計(jì)算”對(duì)話框 選擇要更改的源區(qū)域，如“數(shù)學(xué)”，然后單擊“引用位置”折疊按鈕 在工作表重新選擇引用區(qū)域 第21頁(yè)/共50頁(yè) 單擊“添加”按鈕將新選擇的數(shù)據(jù)區(qū)域添加到“所有引用位置”列表中 選擇編輯前的源區(qū)域，單擊“刪除”按鈕將其刪除 改變區(qū)域引用后的結(jié)果 第22頁(yè)/共50頁(yè)1創(chuàng)建數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表 假設(shè)某圖書公司有3個(gè)部門，分別為一部門、二部門、三部門，銷售的圖書有高等數(shù)學(xué)、工程制圖、政治經(jīng)濟(jì)學(xué)等，每天的銷售情況都輸入到一個(gè)工作表中。如果要統(tǒng)計(jì)每個(gè)部門銷售的各種圖書的數(shù)量、總數(shù)、銷售收入等，可通過(guò)創(chuàng)建數(shù)據(jù)透視表來(lái)實(shí)現(xiàn)。其操作步驟見(jiàn)視頻8-1。第23頁(yè)/共50頁(yè)建立數(shù)據(jù)透視表與數(shù)據(jù)透視圖建立數(shù)據(jù)透視表與數(shù)據(jù)透視圖 數(shù)據(jù)透視表是一個(gè)交互式表格，它可以快速地組合和比較數(shù)據(jù)。它不僅具有轉(zhuǎn)換行和列來(lái)查看源數(shù)據(jù)的不同匯總結(jié)果、顯示不同頁(yè)面以篩選數(shù)據(jù)、根據(jù)需要顯示區(qū)域中的細(xì)節(jié)數(shù)據(jù)和設(shè)置報(bào)告格式等功能，還具有鏈接圖表的功能。數(shù)據(jù)透視圖則是一個(gè)動(dòng)態(tài)的圖表，它可以將創(chuàng)建的數(shù)據(jù)透視表以圖示的形式顯示出來(lái)。第24頁(yè)/共50頁(yè)完成后的數(shù)據(jù)透視表 2創(chuàng)建數(shù)據(jù)透視圖創(chuàng)建數(shù)據(jù)透視圖 創(chuàng)建數(shù)據(jù)透視圖與創(chuàng)建數(shù)據(jù)透視表的方法相同，只需在以上第3步中選擇“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”單選鈕即可，操作步驟見(jiàn)視頻8-2。當(dāng)創(chuàng)建數(shù)據(jù)透視表后，單擊“數(shù)據(jù)透視表”工具欄上的“圖表向?qū)А卑粹o，也可快速地創(chuàng)建數(shù)據(jù)透視圖。第25頁(yè)/共50頁(yè)根據(jù)“一部門”圖書銷售情況創(chuàng)建的數(shù)據(jù)透視圖第26頁(yè)/共50頁(yè)3“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄工具欄 第27頁(yè)/共50頁(yè)在設(shè)置透視表的布局時(shí)，可隨意將數(shù)據(jù)清單的字段拖到“行”、“列”或“數(shù)據(jù)”的位置上，因此，要想改變行列的順序，可重新布局?jǐn)?shù)據(jù)透視表，再改變一下字段的顯示位置就可以了，操作步驟見(jiàn)視頻8-3。第28頁(yè)/共50頁(yè) 重新布局后的數(shù)據(jù)透視表 第29頁(yè)/共50頁(yè)用戶可以在數(shù)據(jù)透視表中添加新的字段或刪除不需要的字段，以改變數(shù)據(jù)透視表中使用的數(shù)據(jù)。有兩種添加和刪除字段的方法：在工作表中添加或刪除字段在工作表中添加或刪除字段：將要添加的字段從“數(shù)據(jù)透視表字段列表”框中拖動(dòng)到工作表中要?jiǎng)?chuàng)建的字段類型所在的區(qū)域即可；刪除字段，只要將字段拖出工作表即可。使用向?qū)砑踊騽h除字段使用向?qū)砑踊騽h除字段：與使用向?qū)е匦虏季謭D表的方法類似。在打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)Р季帧睂?duì)話框后，若要添加字段，可將需要添加的字段從右側(cè)的字段列表拖動(dòng)到圖形區(qū)；若要?jiǎng)h除字段，可將其拖到圖形區(qū)之外。完成布局后，單擊“確定”按鈕，再單擊“完成”按鈕。第30頁(yè)/共50頁(yè)為了簡(jiǎn)化數(shù)據(jù)透視表，Excel可以按照頁(yè)字段分頁(yè)顯示數(shù)據(jù)透視表，每指定頁(yè)字段中的一個(gè)值，數(shù)據(jù)透視表就給出一種顯示（頁(yè)面），從而便于設(shè)計(jì)分類報(bào)表。本例中，“圖書名稱”便是頁(yè)字段 將“圖書名稱”字段拖到“頁(yè)”區(qū) 第31頁(yè)/共50頁(yè) 單擊透視表中單元格B1右側(cè)的按鈕，在彈出的列表框中選擇某類書，例如“高等數(shù)學(xué)”，單獨(dú)統(tǒng)計(jì)出這類書的銷售數(shù)量、銷售額等第32頁(yè)/共50頁(yè)建立頁(yè)字段后的數(shù)據(jù)透視表可以將每頁(yè)放在不同的工作表中，以進(jìn)行單獨(dú)的打印或繪圖操作。選定頁(yè)字段“圖書名稱”第33頁(yè)/共50頁(yè)工作簿中增加了新的工作表“Photoshop CS2”、“法學(xué)概論”等，每個(gè)工作表中都有相關(guān)類型的圖書統(tǒng)計(jì) 第34頁(yè)/共50頁(yè) 要改變數(shù)據(jù)透視表中的數(shù)據(jù)，首先應(yīng)切換到存放數(shù)據(jù)清單的工作表，在數(shù)據(jù)清單中修改所需數(shù)據(jù) 完成修改后再切換到需要更新的數(shù)據(jù)透視表，單擊數(shù)據(jù)“數(shù)據(jù)透視表”工具欄上的“刷新數(shù)據(jù)”按鈕，此時(shí)可看到當(dāng)前數(shù)據(jù)透視表閃動(dòng)一下，數(shù)據(jù)透視表中的數(shù)據(jù)自動(dòng)更新 第35頁(yè)/共50頁(yè)數(shù)據(jù)透視表使用概要函數(shù)來(lái)匯總數(shù)據(jù)。默認(rèn)情況下，透視表用“求和”（SUM）函數(shù)來(lái)計(jì)算“數(shù)值型”數(shù)據(jù)，用“計(jì)數(shù)”（COUNT）函數(shù)來(lái)計(jì)算“文本型”數(shù)據(jù)。默認(rèn)的函數(shù)有時(shí)不能滿足用戶需要，因此Excel提供了其他概要函數(shù)，例如平均值、最小值和最大值等函數(shù)。第36頁(yè)/共50頁(yè) 選擇匯總方式，如“平均值”，單擊“選項(xiàng)”按鈕，選擇數(shù)據(jù)顯示方式，如“占總和的百分比”變換匯總方式后的數(shù)據(jù)透視表 第37頁(yè)/共50頁(yè)單擊要?jiǎng)h除的數(shù)據(jù)透視表中的任一單元格，單擊“數(shù)據(jù)透視表”工具欄上的“數(shù)據(jù)透視表”按鈕，在彈出的菜單中選擇“選定”選項(xiàng)中的“整張表格”選擇“編輯”“清除”“全部”菜單，工作表中的“數(shù)據(jù)透視表”被刪除 第38頁(yè)/共50頁(yè)假設(shè)求解是在一定的假設(shè)條件或約束條件下，按一定的算法求得一個(gè)或一組結(jié)果。模擬運(yùn)算表模擬運(yùn)算表 模擬運(yùn)算表就是將工作表中的一個(gè)單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計(jì)算，測(cè)試使用一個(gè)或兩個(gè)變量對(duì)運(yùn)算結(jié)果的影響。在Excel中，可以構(gòu)造兩種模擬運(yùn)算表：?jiǎn)巫兞磕M運(yùn)算表和多變量模擬運(yùn)算表。第39頁(yè)/共50頁(yè)單變量模擬運(yùn)算表就是基于一個(gè)輸入變量，用它來(lái)測(cè)試對(duì)公式計(jì)算結(jié)果的影響。1單變量模擬運(yùn)算表單變量模擬運(yùn)算表 例如，我們準(zhǔn)備貸一筆款，比如50 000元，貸款期限為10年，年利率為8%，如果貸款利率發(fā)生了變化，如果按月償還，那么每月償還的金額是多少呢？利用單變量模擬運(yùn)算表解決此問(wèn)題的操作步驟見(jiàn)視頻8-4。第40頁(yè)/共50頁(yè)雙變量模擬運(yùn)算表就是考慮兩個(gè)變量的變化對(duì)公式計(jì)算結(jié)果的影響。2雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表 下面以剛才介紹的單變量模擬運(yùn)算的假設(shè)作為例子來(lái)了解一下雙變量模擬運(yùn)算的方法，其操作步驟見(jiàn)視頻8-5。第41頁(yè)/共50頁(yè)一般用公式計(jì)算時(shí)，是根據(jù)變量求結(jié)果。若先確定結(jié)果值，再求變量值，就需要用單變量求解來(lái)解決問(wèn)題，它是正常運(yùn)算的逆運(yùn)算，在實(shí)際工作中有很高的實(shí)用價(jià)值。但是，值得注意的是，用單變量求解一次只能求一個(gè)變量。假設(shè)要投建一個(gè)新的項(xiàng)目，希望從銀行貸款，若有能力按月付款50 000元，利率一定，如年利率為7.5%，償還期為10年，那么最高能貸到多少款呢？操作步驟見(jiàn)視頻8-6。第42頁(yè)/共50頁(yè)規(guī)劃求解可以對(duì)有多個(gè)變量的線性和非線性規(guī)劃問(wèn)題進(jìn)行求解，省去了人工編制程序和手工計(jì)算的麻煩。在求解規(guī)劃問(wèn)題之前，必須建立規(guī)劃模型。例如，某工廠1月份向用戶交貨80T（噸），2月份交貨120T（噸），3月份交貨150T（噸），工廠的最大生產(chǎn)能力每月250T（噸），月生產(chǎn)費(fèi)用為f(x)=(90 x+0.2x2)，這里的x為月產(chǎn)量。從函數(shù)可知，生產(chǎn)規(guī)模越大，平均生產(chǎn)費(fèi)用越低。若生產(chǎn)數(shù)量大于交貨數(shù)量，多余部分可以用于下月交貨，但需支付每T（噸）6元倉(cāng)儲(chǔ)費(fèi)用。那么如何生產(chǎn)才能使工廠的成本最低呢？第43頁(yè)/共50頁(yè)根據(jù)實(shí)際問(wèn)題建立規(guī)劃模型。設(shè)1月份，2月份、3月份的產(chǎn)量分別為x1、x2、x3，目標(biāo)值應(yīng)是工廠的費(fèi)用最小，其中費(fèi)用包括生產(chǎn)費(fèi)用P和可能發(fā)生的存儲(chǔ)費(fèi)用V之和，P和V的計(jì)算公式分別為：p=(90 x 1+0.2 x 12)+(90 x 2+0.2 x 22)+(90 x3+0.2 x 32)v=6(x 1-80)+6(x 1+x 2-80-120)因此，目標(biāo)值是：min(P+V)本問(wèn)題的約束條件包括：生產(chǎn)能力的約束條件：x1250，x2250，x3250交貨數(shù)量的約束條件：x180，x2200，x3350。使用“規(guī)劃求解”的方法見(jiàn)視頻8-7。第44頁(yè)/共50頁(yè)1建立方案建立方案 假設(shè)我們希望把“單變量模擬運(yùn)算表”中建立的“償還貸款計(jì)算”作為方案保存起來(lái)，則建立方案的操作步驟見(jiàn)視頻8-8。第45頁(yè)/共50頁(yè)2管理方案管理方案 建立方案后，可以對(duì)其進(jìn)行修改。首先選擇“工具”“方案”菜單，打開(kāi)“方案管理器”對(duì)話框，選擇待編輯方案的名稱，然后單擊“編輯”按鈕，即可進(jìn)行相應(yīng)的修改，例如在“方案變量值”對(duì)話框中輸入可變單元格所需的數(shù)值。如果要保存所做的修改，可單擊“確定”按鈕；如果要返回“方案管理器”對(duì)話框而不改變當(dāng)前方案，可單擊“取消”按鈕。如果在對(duì)方案進(jìn)行修改后，沒(méi)有改變它原來(lái)的名稱，那么修改后的可變單元格中的新值將替換原來(lái)方案中的值。第46頁(yè)/共50頁(yè)3多種方案匯總多種方案匯總 一個(gè)方案只能說(shuō)明一種情況，要進(jìn)行決策就應(yīng)該分析、比較多種方案，從中找到?jīng)Q策的最佳效益點(diǎn)。第47頁(yè)/共50頁(yè)此處要求輸入包含每個(gè)方案有效結(jié)果的單元格引用，多個(gè)引用間需用逗號(hào)隔開(kāi) 第48頁(yè)/共50頁(yè)方差分析方差分析 方差分析是對(duì)數(shù)據(jù)結(jié)果做分析的一種常用的統(tǒng)計(jì)方法，其類型包括3種：?jiǎn)我蛩胤讲罘治觥⒖芍貜?fù)雙因素分析和無(wú)重復(fù)雙因素分析。下面通過(guò)一個(gè)例子來(lái)說(shuō)明，操作步驟見(jiàn)視頻8-9。第49頁(yè)/共50頁(yè)