《EXCEL教程 數(shù)據(jù)分析與管理PPT課件》由會(huì)員分享，可在線閱讀，更多相關(guān)《EXCEL教程 數(shù)據(jù)分析與管理PPT課件（50頁珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、8.1 合并計(jì)算 建立合并計(jì)算 所謂合并計(jì)算，是指用來匯總一個(gè)或多個(gè)源區(qū)域中的數(shù)據(jù)的方法。Excel提供了兩種合并計(jì)算數(shù)據(jù)的方法：一是按位置合并計(jì)算，即將源區(qū)域中相同位置的數(shù)據(jù)匯總；二是按分類合并計(jì)算，當(dāng)源區(qū)域中沒有相同的布局時(shí)，采用分類方式進(jìn)行匯總。 1按位置合并計(jì)算數(shù)據(jù) 按位置合并計(jì)算數(shù)據(jù)，是指在所有源區(qū)域中的數(shù)據(jù)被相同地排列以進(jìn)行合并計(jì)算，也就是說從每一個(gè)源區(qū)域中合并計(jì)算的數(shù)值必須在被選定源區(qū)域的相同位置上。 第1頁/共50頁“飛揚(yáng)分公司C”同前兩個(gè)工作表不在一個(gè)工作簿中輸入各分公司工作表中的數(shù)據(jù) 第2頁/共50頁為合并計(jì)算的數(shù)據(jù)選定目的區(qū)。選擇“飛揚(yáng)總公司”工作表為當(dāng)前工作表，然后選擇

2、“數(shù)據(jù)”“合并計(jì)算”菜單 第3頁/共50頁 選擇要引用的數(shù)據(jù)區(qū)域 第4頁/共50頁 第5頁/共50頁 合并后的所有數(shù)據(jù) 添加“飛揚(yáng)分公司B”的數(shù)據(jù)引用區(qū)域 第6頁/共50頁當(dāng)進(jìn)行合并計(jì)算時(shí)，還可以引用不同工作簿中的工作表區(qū)域，本例中“飛揚(yáng)分公司C”與其他兩個(gè)分公司的工作表不在同一工作簿中，操作時(shí)應(yīng)先打開“飛揚(yáng)分公司C”工作表。 添加飛揚(yáng)分公司A和B的引用區(qū)域 切換到“飛揚(yáng)分公司C”工作表所在工作簿 第7頁/共50頁 在打開的工作簿中選擇“飛揚(yáng)分公司C”工作表并選擇引用區(qū)域 添加所有引用數(shù)據(jù)區(qū)域 合并后的所有數(shù)據(jù) 第8頁/共50頁2通過分類來合并計(jì)算數(shù)據(jù) 通過分類來合并計(jì)算數(shù)據(jù)，是指當(dāng)多重來源區(qū)

3、域包含相似的數(shù)據(jù)卻以不同的方式排列時(shí)，可依不同分類進(jìn)行數(shù)據(jù)的合并計(jì)算。也就是說，當(dāng)選定的表格具有不同的內(nèi)容時(shí)，我們可以根據(jù)這些表格的分類來分別進(jìn)行合并計(jì)算。 下面以某班的成績(jī)統(tǒng)計(jì)為例，具體介紹如何利用Excel合并計(jì)算功能來登記考分，提高工作效率。 第9頁/共50頁 新建一個(gè)工作簿，并在其中建立6個(gè)工作表 第10頁/共50頁 在“匯總”工作表中單擊合并計(jì)算數(shù)據(jù)目標(biāo)區(qū)域左上角的單元格A1，然后 選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇“求和”函數(shù)，然后單擊“引用位置”輸入框后面的折疊按鈕 第11頁/共50頁 在“語文”工作表中，用鼠標(biāo)拖動(dòng)選定“姓名”和“語文”兩列數(shù)據(jù) 單擊“添加”按鈕，將選擇的源數(shù)據(jù)

4、添加到“所有引用位置”列表框中 第12頁/共50頁 將“英語”、“數(shù)學(xué)”、“化學(xué)”和“物理”等工作表的數(shù)據(jù)重復(fù)執(zhí)行上述步驟，將他們所在的地址全部添加到“所有引用位置”列表框中 在“標(biāo)簽位置”區(qū)選擇“首行”和“最左列”復(fù)選框 第13頁/共50頁每個(gè)學(xué)生的各科成績(jī)匯總在一起 第14頁/共50頁3合并計(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頁/共50頁更改合并計(jì)算 對(duì)于一個(gè)已建立合并計(jì)算的工作表文件，還可以做進(jìn)一步的編輯，例

5、如為合并計(jì)算增加源區(qū)域。但必須注意，只有對(duì)沒有建立與源區(qū)域鏈接的合并工作表，才能夠執(zhí)行編輯操作。 1為合并計(jì)算增加源區(qū)域 單擊合并計(jì)算結(jié)果工作表任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 第16頁/共50頁選擇“政治”工作表中的引用區(qū)域，然后單擊折疊按鈕返回“合并計(jì)算”對(duì)話框 單擊折疊按鈕第17頁/共50頁增加源區(qū)域后的工作表 單擊“添加”按鈕，將“政治”添加到“所有引用位置”列表中 第18頁/共50頁2刪除源區(qū)域 單擊合并計(jì)算結(jié)果工作表中任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇要?jiǎng)h除的源區(qū)域，如物理，然后單擊“刪除”按鈕第19頁/共50頁單擊“合并計(jì)算”對(duì)話框中的“確定”按鈕，結(jié)

6、果得到刪除物“理源”區(qū)域后的工作表 第20頁/共50頁3改變?cè)磪^(qū)域的引用 單擊合并計(jì)算結(jié)果工作表中任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單，打開“合并計(jì)算”對(duì)話框 選擇要更改的源區(qū)域，如“數(shù)學(xué)”，然后單擊“引用位置”折疊按鈕 在工作表重新選擇引用區(qū)域 第21頁/共50頁 單擊“添加”按鈕將新選擇的數(shù)據(jù)區(qū)域添加到“所有引用位置”列表中 選擇編輯前的源區(qū)域，單擊“刪除”按鈕將其刪除 改變區(qū)域引用后的結(jié)果 第22頁/共50頁1創(chuàng)建數(shù)據(jù)透視表 假設(shè)某圖書公司有3個(gè)部門，分別為一部門、二部門、三部門，銷售的圖書有高等數(shù)學(xué)、工程制圖、政治經(jīng)濟(jì)學(xué)等，每天的銷售情況都輸入到一個(gè)工作表中。如果要統(tǒng)計(jì)每個(gè)部門

7、銷售的各種圖書的數(shù)量、總數(shù)、銷售收入等，可通過創(chuàng)建數(shù)據(jù)透視表來實(shí)現(xiàn)。其操作步驟見視頻8-1。 第23頁/共50頁8.2 數(shù)據(jù)透視表及數(shù)據(jù)透視圖 建立數(shù)據(jù)透視表與數(shù)據(jù)透視圖 數(shù)據(jù)透視表是一個(gè)交互式表格，它可以快速地組合和比較數(shù)據(jù)。它不僅具有轉(zhuǎn)換行和列來查看源數(shù)據(jù)的不同匯總結(jié)果、顯示不同頁面以篩選數(shù)據(jù)、根據(jù)需要顯示區(qū)域中的細(xì)節(jié)數(shù)據(jù)和設(shè)置報(bào)告格式等功能，還具有鏈接圖表的功能。數(shù)據(jù)透視圖則是一個(gè)動(dòng)態(tài)的圖表，它可以將創(chuàng)建的數(shù)據(jù)透視表以圖示的形式顯示出來。 第24頁/共50頁完成后的數(shù)據(jù)透視表 2創(chuàng)建數(shù)據(jù)透視圖 創(chuàng)建數(shù)據(jù)透視圖與創(chuàng)建數(shù)據(jù)透視表的方法相同，只需在以上第3步中選擇“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”

8、單選鈕即可，操作步驟見視頻8-2。當(dāng)創(chuàng)建數(shù)據(jù)透視表后，單擊“數(shù)據(jù)透視表”工具欄上的“圖表向?qū)А卑粹o，也可快速地創(chuàng)建數(shù)據(jù)透視圖。第25頁/共50頁根據(jù)“一部門”圖書銷售情況創(chuàng)建的數(shù)據(jù)透視圖第26頁/共50頁3“數(shù)據(jù)透視表”工具欄 第27頁/共50頁重新布局透視表 在設(shè)置透視表的布局時(shí)，可隨意將數(shù)據(jù)清單的字段拖到“行”、“列”或“數(shù)據(jù)”的位置上，因此，要想改變行列的順序，可重新布局?jǐn)?shù)據(jù)透視表，再改變一下字段的顯示位置就可以了，操作步驟見視頻8-3。 第28頁/共50頁 重新布局后的數(shù)據(jù)透視表 第29頁/共50頁添加或刪除字段 用戶可以在數(shù)據(jù)透視表中添加新的字段或刪除不需要的字段，以改變數(shù)據(jù)透視表中

9、使用的數(shù)據(jù)。有兩種添加和刪除字段的方法：在工作表中添加或刪除字段：將要添加的字段從“數(shù)據(jù)透視表字段列表”框中拖動(dòng)到工作表中要?jiǎng)?chuàng)建的字段類型所在的區(qū)域即可；刪除字段，只要將字段拖出工作表即可。使用向?qū)砑踊騽h除字段：與使用向?qū)е匦虏季謭D表的方法類似。在打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)Р季帧睂?duì)話框后，若要添加字段，可將需要添加的字段從右側(cè)的字段列表拖動(dòng)到圖形區(qū)；若要?jiǎng)h除字段，可將其拖到圖形區(qū)之外。完成布局后，單擊“確定”按鈕，再單擊“完成”按鈕。 第30頁/共50頁利用頁面字段簡(jiǎn)化透視表 為了簡(jiǎn)化數(shù)據(jù)透視表，Excel可以按照頁字段分頁顯示數(shù)據(jù)透視表，每指定頁字段中的一個(gè)值，數(shù)據(jù)透視表就給出一種顯

10、示（頁面），從而便于設(shè)計(jì)分類報(bào)表。 本例中，“圖書名稱”便是頁字段 將“圖書名稱”字段拖到“頁”區(qū) 第31頁/共50頁 單擊透視表中單元格B1右側(cè)的按鈕，在彈出的列表框中選擇某類書，例如“高等數(shù)學(xué)”，單獨(dú)統(tǒng)計(jì)出這類書的銷售數(shù)量、銷售額等第32頁/共50頁建立頁字段后的數(shù)據(jù)透視表可以將每頁放在不同的工作表中，以進(jìn)行單獨(dú)的打印或繪圖操作。 選定頁字段“圖書名稱” 第33頁/共50頁工作簿中增加了新的工作表“Photoshop CS2”、“法學(xué)概論”等，每個(gè)工作表中都有相關(guān)類型的圖書統(tǒng)計(jì) 第34頁/共50頁改變透視表中的數(shù)據(jù) 要改變數(shù)據(jù)透視表中的數(shù)據(jù)，首先應(yīng)切換到存放數(shù)據(jù)清單的工作表，在數(shù)據(jù)清單中修

11、改所需數(shù)據(jù) 完成修改后再切換到需要更新的數(shù)據(jù)透視表，單擊數(shù)據(jù)“數(shù)據(jù)透視表”工具欄上的“刷新數(shù)據(jù)”按鈕，此時(shí)可看到當(dāng)前數(shù)據(jù)透視表閃動(dòng)一下，數(shù)據(jù)透視表中的數(shù)據(jù)自動(dòng)更新 第35頁/共50頁變換匯總方式 數(shù)據(jù)透視表使用概要函數(shù)來匯總數(shù)據(jù)。默認(rèn)情況下，透視表用“求和”（SUM）函數(shù)來計(jì)算“數(shù)值型”數(shù)據(jù)，用“計(jì)數(shù)”（COUNT）函數(shù)來計(jì)算“文本型”數(shù)據(jù)。默認(rèn)的函數(shù)有時(shí)不能滿足用戶需要，因此Excel提供了其他概要函數(shù)，例如平均值、最小值和最大值等函數(shù)。 第36頁/共50頁 選擇匯總方式，如“平均值”，單擊“選項(xiàng)”按鈕，選擇數(shù)據(jù)顯示方式，如“占總和的百分比” 變換匯總方式后的數(shù)據(jù)透視表 第37頁/共50頁刪

12、除數(shù)據(jù)透視表 單擊要?jiǎng)h除的數(shù)據(jù)透視表中的任一單元格，單擊“數(shù)據(jù)透視表”工具欄上的“數(shù)據(jù)透視表”按鈕，在彈出的菜單中選擇“選定”選項(xiàng)中的“整張表格” 選擇“編輯”“清除”“全部”菜單，工作表中的“數(shù)據(jù)透視表”被刪除 第38頁/共50頁8.3 假設(shè)求解 假設(shè)求解是在一定的假設(shè)條件或約束條件下，按一定的算法求得一個(gè)或一組結(jié)果。 模擬運(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頁/共50頁單變量模擬運(yùn)算表就是基于一個(gè)輸入變量，用它來測(cè)試對(duì)公式計(jì)算結(jié)果的影響

13、。 1單變量模擬運(yùn)算表 例如，我們準(zhǔn)備貸一筆款，比如50 000元，貸款期限為10年，年利率為8%，如果貸款利率發(fā)生了變化，如果按月償還，那么每月償還的金額是多少呢？利用單變量模擬運(yùn)算表解決此問題的操作步驟見視頻8-4。第40頁/共50頁雙變量模擬運(yùn)算表就是考慮兩個(gè)變量的變化對(duì)公式計(jì)算結(jié)果的影響。2雙變量模擬運(yùn)算表 下面以剛才介紹的單變量模擬運(yùn)算的假設(shè)作為例子來了解一下雙變量模擬運(yùn)算的方法，其操作步驟見視頻8-5。 第41頁/共50頁單變量求解 一般用公式計(jì)算時(shí)，是根據(jù)變量求結(jié)果。若先確定結(jié)果值，再求變量值，就需要用單變量求解來解決問題，它是正常運(yùn)算的逆運(yùn)算，在實(shí)際工作中有很高的實(shí)用價(jià)值。但是

14、，值得注意的是，用單變量求解一次只能求一個(gè)變量。 假設(shè)要投建一個(gè)新的項(xiàng)目，希望從銀行貸款，若有能力按月付款50 000元，利率一定，如年利率為7.5%，償還期為10年，那么最高能貸到多少款呢？操作步驟見視頻8-6。第42頁/共50頁規(guī)劃求解 規(guī)劃求解可以對(duì)有多個(gè)變量的線性和非線性規(guī)劃問題進(jìn)行求解，省去了人工編制程序和手工計(jì)算的麻煩。 在求解規(guī)劃問題之前，必須建立規(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ī)模越大，平

15、均生產(chǎn)費(fèi)用越低。若生產(chǎn)數(shù)量大于交貨數(shù)量，多余部分可以用于下月交貨，但需支付每T（噸）6元倉儲(chǔ)費(fèi)用。那么如何生產(chǎn)才能使工廠的成本最低呢？第43頁/共50頁根據(jù)實(shí)際問題建立規(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)本問題的約束條件包括：生產(chǎn)能力的約束條件：x1250，x2250

16、，x3250交貨數(shù)量的約束條件：x180，x2200，x3350。使用“規(guī)劃求解”的方法見視頻8-7。第44頁/共50頁使用方案 1建立方案 假設(shè)我們希望把“單變量模擬運(yùn)算表”中建立的“償還貸款計(jì)算”作為方案保存起來，則建立方案的操作步驟見視頻8-8。 第45頁/共50頁2管理方案 建立方案后，可以對(duì)其進(jìn)行修改。首先選擇“工具”“方案”菜單，打開“方案管理器”對(duì)話框，選擇待編輯方案的名稱，然后單擊“編輯”按鈕，即可進(jìn)行相應(yīng)的修改，例如在“方案變量值”對(duì)話框中輸入可變單元格所需的數(shù)值。如果要保存所做的修改，可單擊“確定”按鈕；如果要返回“方案管理器”對(duì)話框而不改變當(dāng)前方案，可單擊“取消”按鈕。如果在對(duì)方案進(jìn)行修改后，沒有改變它原來的名稱，那么修改后的可變單元格中的新值將替換原來方案中的值。 第46頁/共50頁3多種方案匯總 一個(gè)方案只能說明一種情況，要進(jìn)行決策就應(yīng)該分析、比較多種方案，從中找到?jīng)Q策的最佳效益點(diǎn)。 第47頁/共50頁此處要求輸入包含每個(gè)方案有效結(jié)果的單元格引用，多個(gè)引用間需用逗號(hào)隔開 第48頁/共50頁方差分析 方差分析是對(duì)數(shù)據(jù)結(jié)果做分析的一種常用的統(tǒng)計(jì)方法，其類型包括3種：?jiǎn)我蛩胤讲罘治觥⒖芍貜?fù)雙因素分析和無重復(fù)雙因素分析。下面通過一個(gè)例子來說明， 操作步驟見視頻8-9。 第49頁/共50頁感謝您的觀看。第50頁/共50頁