Excel2003教程數(shù)據(jù)分析與管理

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

2、算的數(shù)據(jù)選定目的區(qū)。選擇“飛揚(yáng)總公司”工作表為當(dāng)前工作表，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇要引用的數(shù)據(jù)區(qū)域 合并后的所有數(shù)據(jù) 添加“飛揚(yáng)分公司B”的數(shù)據(jù)引用區(qū)域 當(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”工作表所在工作簿 在打開(kāi)的工作簿中選擇“飛揚(yáng)分公司C”工作表并選擇引用區(qū)域 添加所有引用數(shù)據(jù)區(qū)域 合并后的所有數(shù)據(jù) 2通過(guò)分類(lèi)來(lái)合并計(jì)算數(shù)據(jù)通過(guò)分類(lèi)來(lái)合并計(jì)算數(shù)據(jù) 通過(guò)分類(lèi)來(lái)合并計(jì)算數(shù)據(jù)，是指當(dāng)多重來(lái)源區(qū)域包含相似的數(shù)據(jù)

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

4、工作表的數(shù)據(jù)重復(fù)執(zhí)行上述步驟，將他們所在的地址全部添加到“所有引用位置”列表框中 在“標(biāo)簽位置”區(qū)選擇“首行”和“最左列”復(fù)選框 每個(gè)學(xué)生的各科成績(jī)匯總在一起 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ì)話(huà)框中選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復(fù)選框即可 對(duì)于一個(gè)已建立合并計(jì)算的工作表文件，還可以做進(jìn)一步的編輯，例如為合并計(jì)算增加源區(qū)域。但必須注意，只有對(duì)沒(méi)有建立與源區(qū)域鏈接的合并工作表，才能夠執(zhí)行編輯操作。 1為合并計(jì)算增加源區(qū)域?yàn)楹喜⒂?jì)算增加源區(qū)域 單

5、擊合并計(jì)算結(jié)果工作表任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇“政治”工作表中的引用區(qū)域，然后單擊折疊按鈕返回“合并計(jì)算”對(duì)話(huà)框 單擊折疊按鈕增加源區(qū)域后的工作表 單擊“添加”按鈕，將“政治”添加到“所有引用位置”列表中 單擊合并計(jì)算結(jié)果工作表中任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單 選擇要?jiǎng)h除的源區(qū)域，如物理，然后單擊“刪除”按鈕單擊“合并計(jì)算”對(duì)話(huà)框中的“確定”按鈕，結(jié)果得到刪除物“理源”區(qū)域后的工作表 3改變?cè)磪^(qū)域的引用改變?cè)磪^(qū)域的引用 單擊合并計(jì)算結(jié)果工作表中任一單元格，然后選擇“數(shù)據(jù)”“合并計(jì)算”菜單，打開(kāi)“合并計(jì)算”對(duì)話(huà)框 選擇要更改的源區(qū)域，如“數(shù)學(xué)”，然后單擊“引

6、用位置”折疊按鈕 在工作表重新選擇引用區(qū)域 單擊“添加”按鈕將新選擇的數(shù)據(jù)區(qū)域添加到“所有引用位置”列表中 選擇編輯前的源區(qū)域，單擊“刪除”按鈕將其刪除 改變區(qū)域引用后的結(jié)果 1創(chuàng)建數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表 假設(shè)某圖書(shū)公司有3個(gè)部門(mén)，分別為一部門(mén)、二部門(mén)、三部門(mén)，銷(xiāo)售的圖書(shū)有高等數(shù)學(xué)、工程制圖、政治經(jīng)濟(jì)學(xué)等，每天的銷(xiāo)售情況都輸入到一個(gè)工作表中。如果要統(tǒng)計(jì)每個(gè)部門(mén)銷(xiāo)售的各種圖書(shū)的數(shù)量、總數(shù)、銷(xiāo)售收入等，可通過(guò)創(chuàng)建數(shù)據(jù)透視表來(lái)實(shí)現(xiàn)。其操作步驟見(jiàn)視頻8-1。 8.2.1 建立數(shù)據(jù)透視表與數(shù)據(jù)透視圖建立數(shù)據(jù)透視表與數(shù)據(jù)透視圖 數(shù)據(jù)透視表是一個(gè)交互式表格，它可以快速地組合和比較數(shù)據(jù)。它不僅具有轉(zhuǎn)換行和列

7、來(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)。 完成后的數(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ù)透視圖。根據(jù)“一部門(mén)”圖書(shū)銷(xiāo)售情況創(chuàng)建的數(shù)據(jù)透視圖3“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄工具欄 在設(shè)置透視表的布局時(shí)，可隨意將數(shù)據(jù)清單的字段

8、拖到“行”、“列”或“數(shù)據(jù)”的位置上，因此，要想改變行列的順序，可重新布局?jǐn)?shù)據(jù)透視表，再改變一下字段的顯示位置就可以了，操作步驟見(jiàn)視頻8-3。 重新布局后的數(shù)據(jù)透視表 用戶(hù)可以在數(shù)據(jù)透視表中添加新的字段或刪除不需要的字段，以改變數(shù)據(jù)透視表中使用的數(shù)據(jù)。有兩種添加和刪除字段的方法：在工作表中添加或刪除字段在工作表中添加或刪除字段：將要添加的字段從“數(shù)據(jù)透視表字段列表”框中拖動(dòng)到工作表中要?jiǎng)?chuàng)建的字段類(lèi)型所在的區(qū)域即可；刪除字段，只要將字段拖出工作表即可。使用向?qū)砑踊騽h除字段使用向?qū)砑踊騽h除字段：與使用向?qū)е匦虏季謭D表的方法類(lèi)似。在打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)Р季帧睂?duì)話(huà)框后，若要添加字段，可

9、將需要添加的字段從右側(cè)的字段列表拖動(dòng)到圖形區(qū)；若要?jiǎng)h除字段，可將其拖到圖形區(qū)之外。完成布局后，單擊“確定”按鈕，再單擊“完成”按鈕。 為了簡(jiǎn)化數(shù)據(jù)透視表，Excel可以按照頁(yè)字段分頁(yè)顯示數(shù)據(jù)透視表，每指定頁(yè)字段中的一個(gè)值，數(shù)據(jù)透視表就給出一種顯示（頁(yè)面），從而便于設(shè)計(jì)分類(lèi)報(bào)表。 本例中，“圖書(shū)名稱(chēng)”便是頁(yè)字段 將“圖書(shū)名稱(chēng)”字段拖到“頁(yè)”區(qū) 單擊透視表中單元格B1右側(cè)的按鈕，在彈出的列表框中選擇某類(lèi)書(shū)，例如“高等數(shù)學(xué)”，單獨(dú)統(tǒng)計(jì)出這類(lèi)書(shū)的銷(xiāo)售數(shù)量、銷(xiāo)售額等建立頁(yè)字段后的數(shù)據(jù)透視表可以將每頁(yè)放在不同的工作表中，以進(jìn)行單獨(dú)的打印或繪圖操作。 選定頁(yè)字段“圖書(shū)名稱(chēng)” 工作簿中增加了新的工作表“Pho

10、toshop CS2”、“法學(xué)概論”等，每個(gè)工作表中都有相關(guān)類(lèi)型的圖書(shū)統(tǒng)計(jì) 要改變數(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)更新 數(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í)不能滿(mǎn)足用戶(hù)需要，因此Excel提供了其他概要函數(shù)，例如平均值、最小值和最大值等函數(shù)。 選擇匯總方式，如“平均值”，單擊“選

11、項(xiàng)”按鈕，選擇數(shù)據(jù)顯示方式，如“占總和的百分比” 變換匯總方式后的數(shù)據(jù)透視表 單擊要?jiǎng)h除的數(shù)據(jù)透視表中的任一單元格，單擊“數(shù)據(jù)透視表”工具欄上的“數(shù)據(jù)透視表”按鈕，在彈出的菜單中選擇“選定”選項(xiàng)中的“整張表格” 選擇“編輯”“清除”“全部”菜單，工作表中的“數(shù)據(jù)透視表”被刪除 假設(shè)求解是在一定的假設(shè)條件或約束條件下，按一定的算法求得一個(gè)或一組結(jié)果。 8.3.1 模擬運(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)算表。 單變量模擬運(yùn)算表就是基于一個(gè)輸入變量

12、，用它來(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。雙變量模擬運(yùn)算表就是考慮兩個(gè)變量的變化對(duì)公式計(jì)算結(jié)果的影響。2雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表 下面以剛才介紹的單變量模擬運(yùn)算的假設(shè)作為例子來(lái)了解一下雙變量模擬運(yùn)算的方法，其操作步驟見(jiàn)視頻8-5。 一般用公式計(jì)算時(shí)，是根據(jù)變量求結(jié)果。若先確定結(jié)果值，再求變量值，就需要用單變量求解來(lái)解決問(wèn)題，它是正常運(yùn)算的逆運(yùn)算，在實(shí)際工作中有很高

13、的實(shí)用價(jià)值。但是，值得注意的是，用單變量求解一次只能求一個(gè)變量。 假設(shè)要投建一個(gè)新的項(xiàng)目，希望從銀行貸款，若有能力按月付款50 000元，利率一定，如年利率為7.5%，償還期為10年，那么最高能貸到多少款呢？操作步驟見(jiàn)視頻8-6。規(guī)劃求解可以對(duì)有多個(gè)變量的線(xiàn)性和非線(xiàn)性規(guī)劃問(wèn)題進(jìn)行求解，省去了人工編制程序和手工計(jì)算的麻煩。 在求解規(guī)劃問(wèn)題之前，必須建立規(guī)劃模型。例如，某工廠(chǎng)1月份向用戶(hù)交貨80T（噸），2月份交貨120T（噸），3月份交貨150T（噸），工廠(chǎng)的最大生產(chǎn)能力每月250T（噸），月生產(chǎn)費(fèi)用為f(x)=(90 xx2)，這里的x為月產(chǎn)量。從函數(shù)可知，生產(chǎn)規(guī)模越大，平均生產(chǎn)費(fèi)用越低。若生

14、產(chǎn)數(shù)量大于交貨數(shù)量，多余部分可以用于下月交貨，但需支付每T（噸）6元倉(cāng)儲(chǔ)費(fèi)用。那么如何生產(chǎn)才能使工廠(chǎng)的成本最低呢？根據(jù)實(shí)際問(wèn)題建立規(guī)劃模型。設(shè)1月份，2月份、3月份的產(chǎn)量分別為x1、x2、x3，目標(biāo)值應(yīng)是工廠(chǎng)的費(fèi)用最小，其中費(fèi)用包括生產(chǎn)費(fèi)用P和可能發(fā)生的存儲(chǔ)費(fèi)用V之和，P和V的計(jì)算公式分別為：p=(90 x x 12) +(90 x x 22) +(90 x 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。1建立方案建立方案 假設(shè)我們希望把“單變量模擬運(yùn)算表”中建立的“償還貸款計(jì)算”作為方案保存起來(lái)，則建立方案的操作步驟見(jiàn)視頻8-8。 2管理方案管理方案 建立方案后，可以對(duì)其進(jìn)行修改。首先選擇“工具”“方案”菜單，打開(kāi)“方案管理器