第9章數據的與決策

1、第第9 9章章 數據的分析與決策數據的分析與決策o9.1 9.1 數據透視表數據透視表o9.2 9.2 數據透視圖數據透視圖o9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.1 9.1 數據透視表數據透視表p數據透視表可以將大量數據轉換成數據透視表可以將大量數據轉換成匯總匯總表。表。p從本質上講，數據透視表是從數據庫產生的從本質上講，數據透視表是從數據庫產生的一個一個動態(tài)動態(tài)匯總表格。匯總表格。9.1 9.1 數據透視表數據透視表9.1.1 9.1.1 數據透視表的創(chuàng)建數據透視表的創(chuàng)建基本數據表基本數據表p使用數據透視表統(tǒng)計每使用數據透視表統(tǒng)計每個學院不同地區(qū)的學生個學院不同地區(qū)的

2、學生數和平均入學成績。數和平均入學成績。9.1 9.1 數據透視表數據透視表9.1.1 9.1.1 數據透視表的創(chuàng)建數據透視表的創(chuàng)建單擊數據表中任意位置，選擇單擊數據表中任意位置，選擇“插入插入”- -“數據透視表數據透視表”9.1 9.1 數據透視表數據透視表9.1.1 9.1.1 數據透視表的創(chuàng)建數據透視表的創(chuàng)建設置透視表的布局設置透視表的布局9.1 9.1 數據透視表數據透視表9.1.1 9.1.1 數據透視表的創(chuàng)建數據透視表的創(chuàng)建9.1 9.1 數據透視表數據透視表9.1.2 9.1.2 數據透視表的查看數據透視表的查看如：查看安徽地區(qū)男女生如：查看安徽地區(qū)男女生的人數。的人數。9.1

3、 9.1 數據透視表數據透視表9.1.3 9.1.3 數據透視表的編輯數據透視表的編輯p數據表建好之后，表中的數據不可能一成不變。數據表建好之后，表中的數據不可能一成不變。p用戶可以根據需要更改數據透視表的數據源。如用戶可以根據需要更改數據透視表的數據源。如添加、刪除數據透視表的字段，更新數據透視表添加、刪除數據透視表的字段，更新數據透視表中的數據等等。中的數據等等。9.1 9.1 數據透視表數據透視表9.1.3 9.1.3 數據透視表的編輯數據透視表的編輯更改數據源更改數據源9.1 9.1 數據透視表數據透視表9.1.3 9.1.3 數據透視表的編輯數據透視表的編輯更新透視表中的數據更新透視

4、表中的數據p當數據源的數據發(fā)生改變時，所創(chuàng)建的當數據源的數據發(fā)生改變時，所創(chuàng)建的數據透視表中數據透視表中的數據不會發(fā)生改變的數據不會發(fā)生改變。p為了使透視表中的數據，隨著數據源中的數據改變而為了使透視表中的數據，隨著數據源中的數據改變而改變，需要更新數據透視表中的數據。改變，需要更新數據透視表中的數據。9.1 9.1 數據透視表數據透視表9.1.3 9.1.3 數據透視表的編輯數據透視表的編輯更新透視表中的數據更新透視表中的數據選中數據透視表中的任意單元格選中數據透視表中的任意單元格執(zhí)行執(zhí)行“數據數據” “刷新刷新”命令命令9.1 9.1 數據透視表數據透視表9.1.3 9.1.3 數據透視表

5、的編輯數據透視表的編輯更改透視表中的數據匯總方式更改透視表中的數據匯總方式9.1 9.1 數據透視表數據透視表o9.1.3 9.1.3 數據透視表的編輯數據透視表的編輯改變數據透視表的布局改變數據透視表的布局9.1 9.1 數據透視表數據透視表9.1.3 9.1.3 數據透視表的編輯數據透視表的編輯o報表布局報表布局 數據透視表為用戶提供了數據透視表為用戶提供了“以壓縮形式顯示以壓縮形式顯示”、“以大綱形式顯示以大綱形式顯示”和和“以表格形式顯示以表格形式顯示”3種報表種報表布局的顯示形式。布局的顯示形式。9.1 9.1 數據透視表數據透視表o9.1.3 9.1.3 數據透視表的編輯數據透視表

6、的編輯o顯示報表篩選頁顯示報表篩選頁 通過選擇報表篩選字段的項目，可以對整個數據透通過選擇報表篩選字段的項目，可以對整個數據透視表的內容進行篩選，但篩選結果仍然顯示在一張表視表的內容進行篩選，但篩選結果仍然顯示在一張表格中，即每次只能進行一種篩選。利用格中，即每次只能進行一種篩選。利用【數據透視表數據透視表工具工具】的的【顯示報表篩選頁顯示報表篩選頁】功能，可以創(chuàng)建一系列功能，可以創(chuàng)建一系列鏈接在一起的數據透視表，每一張工作表顯示報表篩鏈接在一起的數據透視表，每一張工作表顯示報表篩選字段中的一項。選字段中的一項。9.1 9.1 數據透視表數據透視表o9.1.3 9.1.3 數據透視表的編輯數據

7、透視表的編輯重命名字段重命名字段 如果要對字段重命名，讓列標題更加簡潔，可以直如果要對字段重命名，讓列標題更加簡潔，可以直接修改數據透視表的字段名稱。接修改數據透視表的字段名稱。 數據透視表中每個字段的名稱必須唯一，數據透視表中每個字段的名稱必須唯一，Excel不不接受任意兩個字段具有相同的名稱，即創(chuàng)建的數據透接受任意兩個字段具有相同的名稱，即創(chuàng)建的數據透視表的各個宇段的名稱不能相同，創(chuàng)建的數據透視表視表的各個宇段的名稱不能相同，創(chuàng)建的數據透視表字段名稱與數據源表頭標題行的名稱也不能相同，否字段名稱與數據源表頭標題行的名稱也不能相同，否則將會出現錯誤提示。則將會出現錯誤提示。9.2 9.2 數

8、據透視圖數據透視圖p使用數據透視表可以準確計算和分析數據，但是有使用數據透視表可以準確計算和分析數據，但是有時候如果數據源較大，則數據透視表中的數據將非時候如果數據源較大，則數據透視表中的數據將非常多，數據排列將非常復雜。常多，數據排列將非常復雜。p與數據透視表相比，數據透視圖更能直觀地分析數與數據透視表相比，數據透視圖更能直觀地分析數據，展示數據之間的關系。據，展示數據之間的關系。p創(chuàng)建數據透視圖會同時創(chuàng)建數據透視表創(chuàng)建數據透視圖會同時創(chuàng)建數據透視表9.2 9.2 數據透視圖數據透視圖9.2.1 9.2.1 創(chuàng)建數據透視圖創(chuàng)建數據透視圖- -直接創(chuàng)建直接創(chuàng)建9.2 9.2 數據透視圖數據透視

9、圖9.2.1 9.2.1 創(chuàng)建數據透視圖創(chuàng)建數據透視圖9.2 9.2 數據透視圖數據透視圖9.2.1 9.2.1 創(chuàng)建數據透視圖創(chuàng)建數據透視圖- -用數據透視表創(chuàng)建用數據透視表創(chuàng)建 選中已經創(chuàng)建的數據透視表選中已經創(chuàng)建的數據透視表-“插入插入”-“圖表圖表”9.2 9.2 數據透視圖數據透視圖9.2.2 9.2.2 數據透視圖的修飾數據透視圖的修飾- -改變透視圖的布局改變透視圖的布局9.2 9.2 數據透視圖數據透視圖9.2.2 9.2.2 數據透視圖的修飾數據透視圖的修飾- -改變透視圖的格式改變透視圖的格式9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合

10、并計算合并計算p合并計算是指用來匯總一個或多個源區(qū)域中數據的方法。合并計算是指用來匯總一個或多個源區(qū)域中數據的方法。p在進行合并計算時，首先必須為匯總信息定義在進行合并計算時，首先必須為匯總信息定義一個目的一個目的區(qū)區(qū)，另外需要選擇要，另外需要選擇要合并計算的數據項合并計算的數據項。p數據源可以來自一個工作表、多個工作表或多個工作簿。數據源可以來自一個工作表、多個工作表或多個工作簿。p合并計算包括合并計算包括按位置合并計算和按分類合并計算按位置合并計算和按分類合并計算9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按位置合并計算按位置合并計

11、算p按位置合并計算是指在所有數據源區(qū)域中的數據被相按位置合并計算是指在所有數據源區(qū)域中的數據被相同地排列以進行合并計算。同地排列以進行合并計算。p按位置合并計算只適合按位置合并計算只適合具有相同結構數據區(qū)域具有相同結構數據區(qū)域的計算。的計算。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按位置合并計算按位置合并計算插入新工作表，并在其中輸入上圖內容插入新工作表，并在其中輸入上圖內容9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按位置合并計算按位置合并計算選中單元格區(qū)域，執(zhí)行選中單元格

12、區(qū)域，執(zhí)行“合并計算合并計算”命令命令9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按位置合并計算按位置合并計算 “合并計算合并計算”對話框中確定引用位置對話框中確定引用位置9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按位置合并計算按位置合并計算 “合并計算合并計算”結果結果9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按分類合并計算按分類合并計算p如果要匯總計算一組具有如果要匯總計算一組具有相同的行和列標志相同的行和列標志但

13、以但以不同不同的方式組織數據的方式組織數據的工作表的工作表, ,則可按分類進行合并計算。則可按分類進行合并計算。p這種方法對每一張工作表中具有相同標志的數據進行這種方法對每一張工作表中具有相同標志的數據進行合并計算。合并計算。 9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按分類合并計算按分類合并計算插入新工作表，并在其中輸入上圖內容插入新工作表，并在其中輸入上圖內容9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按分類合并計算按分類合并計算將光標定位將光標定位A2A2單元格，執(zhí)行單元

14、格，執(zhí)行“合并計算合并計算”命令命令9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按分類合并計算按分類合并計算將光標定位將光標定位A A單元格，執(zhí)行單元格，執(zhí)行“合并計算合并計算”命令命令9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -按分類合并計算按分類合并計算執(zhí)行執(zhí)行“合并計算合并計算”結果結果9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.1 9.3.1 合并計算合并計算- -多表分類匯總多表分類匯總9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.

15、2 9.3.2 單變量求解單變量求解p單變量求解就是尋求公式中的特定解。單變量求解就是尋求公式中的特定解。p使用單變量求解，能夠通過使用單變量求解，能夠通過調整可變單元格調整可變單元格中的數值，中的數值，按照給定公式來滿足按照給定公式來滿足目標單元格中目標單元格中的目標值。解決一的目標值。解決一些實際生活和工作中的問題。些實際生活和工作中的問題。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.2 9.3.2 單變量求解單變量求解p假設某人每月工資收入為假設某人每月工資收入為12001200元，其中基本生活費元，其中基本生活費600600，房租費，房租費220220，電話費，電話

16、費100100以及車費以及車費5050已經不能再已經不能再進行調整，可自由支配的費用是進行調整，可自由支配的費用是230230。p如果現在工資上漲為如果現在工資上漲為20002000，那么可自由支配的費用將，那么可自由支配的費用將變成多少？變成多少？9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.2 9.3.2 單變量求解單變量求解在目標單元格中進行求和計算在目標單元格中進行求和計算9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.2 9.3.2 單變量求解單變量求解執(zhí)行執(zhí)行“工具工具” “單變量求解單變量求解”命令命令9.3 9.3 數據的高級分析與處理數據的高

17、級分析與處理9.3.3 9.3.3 方案求解方案求解p實際工作中，可能遇到很多可變因素的問題，或者要實際工作中，可能遇到很多可變因素的問題，或者要在幾種假設中找出最佳執(zhí)行方案。這就需要用方案管在幾種假設中找出最佳執(zhí)行方案。這就需要用方案管理器，創(chuàng)建和管理自定義的方案理器，創(chuàng)建和管理自定義的方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解案例案例p已知某食品公司已知某食品公司20092009年的總銷售額以及各種食品的銷年的總銷售額以及各種食品的銷售成本，要在此基礎上制定一個五年計劃。由于市場售成本，要在此基礎上制定一個五年計劃。由于市場的變化，

18、只能對總銷售額及各種食品銷售成本的增長的變化，只能對總銷售額及各種食品銷售成本的增長率做一些估計。率做一些估計。p最好的方案就是總銷售額增長率高，各種食品的銷售最好的方案就是總銷售額增長率高，各種食品的銷售成本增長率低。成本增長率低。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解案例案例p假設假設20092009年總銷售額為年總銷售額為800000800000，餅干、牛肉干、方便面，餅干、牛肉干、方便面和飲料的銷售成本分別為和飲料的銷售成本分別為5000050000，6000060000，7000070000和和8000080000。p最好的估

19、計最好的估計是總銷售額增長率達到是總銷售額增長率達到18%18%，餅干、牛肉干、，餅干、牛肉干、方便面和飲料的銷售成本分別增長方便面和飲料的銷售成本分別增長8%8%，9%9%，12%12%和和7%7%。p最壞的估計最壞的估計是餅干、牛肉干、方便面和飲料的銷售成本是餅干、牛肉干、方便面和飲料的銷售成本分別增長分別增長9%9%，12%12%，15%15%和和9%9%。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解案例案例p最好的估計最好的估計情況下，公司五年內的成本及利潤估算情況如圖情況下，公司五年內的成本及利潤估算情況如圖所示：所示：9.3 9.

20、3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案從從“數據數據”- -“模擬分析模擬分析”- -“方案管理器方案管理器”9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估算結果保存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估算結果保存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估算結果保

21、存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估算結果保存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估算結果保存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估算結果保存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解定義方案定義方案將估

22、算結果保存為方案將估算結果保存為方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解顯示方案顯示方案p建立方案后，任何時候都可以查看不同方案對總建立方案后，任何時候都可以查看不同方案對總利潤帶來的影響。利潤帶來的影響。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解合并方案合并方案9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解合并方案合并方案合并之前合并之前合并之后合并之后9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.39.3.3方

23、案求解方案求解建立方案報告建立方案報告p當用戶對一張工作表建立多個方案時，如果想對當用戶對一張工作表建立多個方案時，如果想對比各種方案，可以建立方案報告。比各種方案，可以建立方案報告。p建立方案報告，便于對各種方案采用的可變參數建立方案報告，便于對各種方案采用的可變參數及方案結果進行對比并尋求最佳方案。及方案結果進行對比并尋求最佳方案。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解建立方案報告建立方案報告9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解建立方案報告建立方案報告9.3 9.3 數據的

24、高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解建立方案報告建立方案報告9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.3 9.3.3 方案求解方案求解建立方案報告建立方案報告9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解p規(guī)劃求解適用于需要規(guī)劃求解適用于需要通過多個變量的變化通過多個變量的變化來找到來找到一個一個目標值目標值的情況。的情況。p只要問題有只要問題有單一的目標單一的目標，有，有明確的不等式約束條件明確的不等式約束條件，有有直接或間接影響約束條件的一組輸入值直接或間接影響約束條件的一組輸入值

25、，就可以應，就可以應用規(guī)劃求解解決。用規(guī)劃求解解決。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解p已知某家具廠目前庫存情況是：木料已知某家具廠目前庫存情況是：木料40004000個單位，油個單位，油漆漆45004500個單位，活頁個單位，活頁20002000個單位，螺絲個單位，螺絲20002000個單位。個單位。工廠生產工廠生產A,B,C,DA,B,C,D這四種產品。不同產品消耗的材料這四種產品。不同產品消耗的材料不同，售價也不同。試問，該廠利用現有的庫存應該不同，售價也不同。試問，該廠利用現有的庫存應該生產生產4 4種產品各多少件才能達到利

26、潤的最大化。種產品各多少件才能達到利潤的最大化。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解建立規(guī)劃模型建立規(guī)劃模型9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解規(guī)劃求解規(guī)劃求解9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解規(guī)劃求解規(guī)劃求解9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解規(guī)劃求解規(guī)劃求解9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.4 9.3.4 規(guī)劃求解規(guī)劃求解

27、規(guī)劃求解規(guī)劃求解9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析p回歸分析研究變量之間的非確定因果關系?；貧w分析研究變量之間的非確定因果關系。p通常把其中的一些變量作為自變量（預測變量，通常把其中的一些變量作為自變量（預測變量，X X），），來預測另外一些隨自變量變化而變化的變量（被預測來預測另外一些隨自變量變化而變化的變量（被預測變量，變量，Y Y）。）。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析線性回歸線性回歸p兩個變量之間有準確嚴格的直線相關關系。兩個變量之間有準確嚴格的直線相關關系。p

28、一個變量的變化引起另外一個變量變化的程度可以用一個變量的變化引起另外一個變量變化的程度可以用線性回歸方程式來表示。線性回歸方程式來表示。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析線性回歸線性回歸p例如，下表是某公司例如，下表是某公司1010年來廣告費和銷售量數據，試探據年來廣告費和銷售量數據，試探據此估計該公司為了達到此估計該公司為了達到40004000萬的銷售量，萬的銷售量，20102010年的廣告費年的廣告費應投入多少。應投入多少。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析線性回歸線

29、性回歸9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析線性回歸線性回歸9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析線性回歸線性回歸9.3 9.3 數據的高級分析與處理數據的高級分析與處理回歸系數回歸系數：選中：選中b15:c15，輸入公式，輸入公式=LINEST(C2:C11,B2:B11),按下按下Ctrl+shift+Alt相關系數相關系數=CORREL(C2:C11,B2:B11)9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析非線性回歸非線

30、性回歸p兩個變量之間之間的關系不是單純的直線關系。兩個變量之間之間的關系不是單純的直線關系。p如年齡與收入的關系，這種情況下要用到非線性回歸如年齡與收入的關系，這種情況下要用到非線性回歸分析方法。分析方法。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 回歸分析回歸分析非線性回歸非線性回歸p例如，根據以下基本數據，預測某人例如，根據以下基本數據，預測某人6565歲時的收入。歲時的收入。9.3 9.3 數據的高級分析與處理數據的高級分析與處理9.3.5 9.3.5 根據以下基本數據，預測某人根據以下基本數據，預測某人6565歲時的收入。歲時的收入。9.3 9.3 數據的高級分析與處理數據的高級分