數(shù)據(jù)的分析與決策概述和特殊情況下的投資決策

第9章數(shù)據(jù)的分析與決策9.1數(shù)據(jù)透視表9.2數(shù)據(jù)透視圖9.3數(shù)據(jù)的高級分析與處理9.1數(shù)據(jù)透視表數(shù)據(jù)透視表可以將大量數(shù)據(jù)轉換成匯總表。從本質上講，數(shù)據(jù)透視表是從數(shù)據(jù)庫產(chǎn)生的一個動態(tài)匯總表格。9.1數(shù)據(jù)透視表9.1.1數(shù)據(jù)透視表的創(chuàng)建基本數(shù)據(jù)表使用數(shù)據(jù)透視表統(tǒng)計每個學院不同地區(qū)的學生數(shù)和平均入學成績。9.1數(shù)據(jù)透視表9.1.1數(shù)據(jù)透視表的創(chuàng)建①單擊數(shù)據(jù)表中任意位置，選擇“插入”-“數(shù)據(jù)透視表”9.1數(shù)據(jù)透視表9.1.1數(shù)據(jù)透視表的創(chuàng)建②設置透視表的布局9.1數(shù)據(jù)透視表9.1.1數(shù)據(jù)透視表的創(chuàng)建9.1數(shù)據(jù)透視表9.1.2數(shù)據(jù)透視表的查看如：查看安徽地區(qū)男女生的人數(shù)。9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯數(shù)據(jù)表建好之后，表中的數(shù)據(jù)不可能一成不變。用戶可以根據(jù)需要更改數(shù)據(jù)透視表的數(shù)據(jù)源。如添加、刪除數(shù)據(jù)透視表的字段，更新數(shù)據(jù)透視表中的數(shù)據(jù)等等。9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯更改數(shù)據(jù)源9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯更新透視表中的數(shù)據(jù)當數(shù)據(jù)源的數(shù)據(jù)發(fā)生改變時，所創(chuàng)建的數(shù)據(jù)透視表中的數(shù)據(jù)不會發(fā)生改變。為了使透視表中的數(shù)據(jù)，隨著數(shù)據(jù)源中的數(shù)據(jù)改變而改變，需要更新數(shù)據(jù)透視表中的數(shù)據(jù)。9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯更新透視表中的數(shù)據(jù)①選中數(shù)據(jù)透視表中的任意單元格②執(zhí)行“數(shù)據(jù)”→“刷新”命令9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯更改透視表中的數(shù)據(jù)匯總方式9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯改變數(shù)據(jù)透視表的布局9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯報表布局數(shù)據(jù)透視表為用戶提供了“以壓縮形式顯示”、“以大綱形式顯示”和“以表格形式顯示”3種報表布局的顯示形式。9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯顯示報表篩選頁通過選擇報表篩選字段的項目，可以對整個數(shù)據(jù)透視表的內(nèi)容進行篩選，但篩選結果仍然顯示在一張表格中，即每次只能進行一種篩選。利用【數(shù)據(jù)透視表工具】的【顯示報表篩選頁】功能，可以創(chuàng)建一系列鏈接在一起的數(shù)據(jù)透視表，每一張工作表顯示報表篩選字段中的一項。9.1數(shù)據(jù)透視表9.1.3數(shù)據(jù)透視表的編輯重命名字段

如果要對字段重命名，讓列標題更加簡潔，可以直接修改數(shù)據(jù)透視表的字段名稱。數(shù)據(jù)透視表中每個字段的名稱必須唯一，Excel不接受任意兩個字段具有相同的名稱，即創(chuàng)建的數(shù)據(jù)透視表的各個宇段的名稱不能相同，創(chuàng)建的數(shù)據(jù)透視表字段名稱與數(shù)據(jù)源表頭標題行的名稱也不能相同，否則將會出現(xiàn)錯誤提示。9.2數(shù)據(jù)透視圖使用數(shù)據(jù)透視表可以準確計算和分析數(shù)據(jù)，但是有時候如果數(shù)據(jù)源較大，則數(shù)據(jù)透視表中的數(shù)據(jù)將非常多，數(shù)據(jù)排列將非常復雜。與數(shù)據(jù)透視表相比，數(shù)據(jù)透視圖更能直觀地分析數(shù)據(jù)，展示數(shù)據(jù)之間的關系。創(chuàng)建數(shù)據(jù)透視圖會同時創(chuàng)建數(shù)據(jù)透視表9.2數(shù)據(jù)透視圖9.2.1創(chuàng)建數(shù)據(jù)透視圖-直接創(chuàng)建9.2數(shù)據(jù)透視圖9.2.1創(chuàng)建數(shù)據(jù)透視圖9.2數(shù)據(jù)透視圖9.2.1創(chuàng)建數(shù)據(jù)透視圖-用數(shù)據(jù)透視表創(chuàng)建

選中已經(jīng)創(chuàng)建的數(shù)據(jù)透視表-“插入”-“圖表”9.2數(shù)據(jù)透視圖9.2.2數(shù)據(jù)透視圖的修飾-改變透視圖的布局9.2數(shù)據(jù)透視圖9.2.2數(shù)據(jù)透視圖的修飾-改變透視圖的格式①9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算合并計算是指用來匯總一個或多個源區(qū)域中數(shù)據(jù)的方法。在進行合并計算時，首先必須為匯總信息定義一個目的區(qū)，另外需要選擇要合并計算的數(shù)據(jù)項。數(shù)據(jù)源可以來自一個工作表、多個工作表或多個工作簿。合并計算包括按位置合并計算和按分類合并計算9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按位置合并計算按位置合并計算是指在所有數(shù)據(jù)源區(qū)域中的數(shù)據(jù)被相同地排列以進行合并計算。按位置合并計算只適合具有相同結構數(shù)據(jù)區(qū)域的計算。9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按位置合并計算①插入新工作表，并在其中輸入上圖內(nèi)容9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按位置合并計算②選中單元格區(qū)域，執(zhí)行“合并計算”命令9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按位置合并計算③

“合并計算”對話框中確定引用位置9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按位置合并計算④

“合并計算”結果9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按分類合并計算如果要匯總計算一組具有相同的行和列標志但以不同的方式組織數(shù)據(jù)的工作表,則可按分類進行合并計算。這種方法對每一張工作表中具有相同標志的數(shù)據(jù)進行合并計算。9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按分類合并計算①插入新工作表，并在其中輸入上圖內(nèi)容9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按分類合并計算②將光標定位A2單元格，執(zhí)行“合并計算”命令9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按分類合并計算③將光標定位A單元格，執(zhí)行“合并計算”命令9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-按分類合并計算④執(zhí)行“合并計算”結果9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-多表分類匯總9.3數(shù)據(jù)的高級分析與處理9.3.2單變量求解單變量求解就是尋求公式中的特定解。使用單變量求解，能夠通過調整可變單元格中的數(shù)值，按照給定公式來滿足目標單元格中的目標值。解決一些實際生活和工作中的問題。9.3數(shù)據(jù)的高級分析與處理9.3.2單變量求解假設某人每月工資收入為1200元，其中基本生活費600，房租費220，電話費100以及車費50已經(jīng)不能再進行調整，可自由支配的費用是230。如果現(xiàn)在工資上漲為2023，那么可自由支配的費用將變成多少？9.3數(shù)據(jù)的高級分析與處理9.3.2單變量求解①在目標單元格中進行求和計算9.3數(shù)據(jù)的高級分析與處理9.3.2單變量求解②執(zhí)行“工具”→“單變量求解”命令9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解實際工作中，可能遇到很多可變因素的問題，或者要在幾種假設中找出最佳執(zhí)行方案。這就需要用方案管理器，創(chuàng)建和管理自定義的方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—案例已知某食品公司2023年的總銷售額以及各種食品的銷售成本，要在此基礎上制定一個五年計劃。由于市場的變化，只能對總銷售額及各種食品銷售成本的增長率做一些估計。最好的方案就是總銷售額增長率高，各種食品的銷售成本增長率低。9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—案例假設2023年總銷售額為800000，餅干、牛肉干、方便面和飲料的銷售成本分別為50000，60000，70000和80000。最好的估計是總銷售額增長率達到18%，餅干、牛肉干、方便面和飲料的銷售成本分別增長8%，9%，12%和7%。最壞的估計是餅干、牛肉干、方便面和飲料的銷售成本分別增長9%，12%，15%和9%。9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—案例最好的估計情況下，公司五年內(nèi)的成本及利潤估算情況如圖所示：9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案①從“數(shù)據(jù)”-“模擬分析”-“方案管理器”9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案②將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案③將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案④將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案⑤將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案⑥將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案⑦將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—定義方案⑧將估算結果保存為方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—顯示方案建立方案后，任何時候都可以查看不同方案對總利潤帶來的影響。9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—合并方案9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—合并方案合并之前合并之后9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—建立方案報告當用戶對一張工作表建立多個方案時，如果想對比各種方案，可以建立方案報告。建立方案報告，便于對各種方案采用的可變參數(shù)及方案結果進行對比并尋求最佳方案。9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—建立方案報告①9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—建立方案報告②9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—建立方案報告③9.3數(shù)據(jù)的高級分析與處理9.3.3方案求解—建立方案報告④9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解規(guī)劃求解適用于需要通過多個變量的變化來找到一個目標值的情況。只要問題有單一的目標，有明確的不等式約束條件，有直接或間接影響約束條件的一組輸入值，就可以應用規(guī)劃求解解決。9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解已知某家具廠目前庫存情況是：木料4000個單位，油漆4500個單位，活頁2023個單位，螺絲2023個單位。工廠生產(chǎn)A,B,C,D這四種產(chǎn)品。不同產(chǎn)品消耗的材料不同，售價也不同。試問，該廠利用現(xiàn)有的庫存應該生產(chǎn)4種產(chǎn)品各多少件才能達到利潤的最大化。9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解—①建立規(guī)劃模型9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.4規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析回歸分析研究變量之間的非確定因果關系。通常把其中的一些變量作為自變量（預測變量，X），來預測另外一些隨自變量變化而變化的變量（被預測變量，Y）。9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—線性回歸兩個變量之間有準確嚴格的直線相關關系。一個變量的變化引起另外一個變量變化的程度可以用線性回歸方程式來表示。9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—線性回歸例如，下表是某公司10年來廣告費和銷售量數(shù)據(jù)，試探據(jù)此估計該公司為了達到4000萬的銷售量，2023年的廣告費應投入多少。9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—線性回歸①9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—線性回歸②9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—線性回歸③9.3數(shù)據(jù)的高級分析與處理回歸系數(shù)：選中b15:c15，輸入公式=LINEST(C2:C11,B2:B11),按下Ctrl+shift+Alt相關系數(shù)=CORREL(C2:C11,B2:B11)9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—非線性回歸兩個變量之間之間的關系不是單純的直線關系。如年齡與收入的關系，這種情況下要用到非線性回歸分析方法。9.3數(shù)據(jù)的高級分析與處理9.3.5回歸分析—非線性回歸例如，根據(jù)以下基本數(shù)據(jù)，預測某人65歲時的收入。9.3數(shù)據(jù)的高級分析與處理9.3.5根據(jù)以下基本數(shù)據(jù)，預測某人65歲時的收入。9.3數(shù)據(jù)的高級分析與處理9.3.6多元線性回歸分析一元回歸分析適用于分析一個自變量和一個因變量之間的關系，而在實際情況中，常會遇到一個因變量和多個自變量數(shù)量關系的問題，多元回歸分析正是為了解決這些問題而產(chǎn)生的。9.3數(shù)據(jù)的高級分析與處理9.3.6多元線性回歸分析案例：銷售公司在各大城市都有分銷公司，公司采用的是經(jīng)銷商銷售模式。公司統(tǒng)計了2023年各大城市的人數(shù)、公司在該城市的經(jīng)銷商個數(shù)以及銷售量的數(shù)值，公司需要以該數(shù)據(jù)為基礎，分析影響公司銷售情況的各個因素以及預測公司的銷售前景。9.3數(shù)據(jù)的高級分析與處理9.3.6多元線性回歸分析9.3數(shù)據(jù)的高級分析與處理9.3.6多元線性回歸分析案例實現(xiàn)1、加載“數(shù)據(jù)分析”宏。選擇“開發(fā)工具”-“加載項”，打開加載宏對話框，在其中選擇加載“分析工具庫”，然后單擊“確定”按鈕。2、打開“回歸”對話框。選擇“數(shù)據(jù)”-“數(shù)據(jù)分析”選項，打開“數(shù)據(jù)分析”對話框，在其中選擇“回歸”選項，單擊“確定”按鈕。9.3數(shù)據(jù)的高級分析與處理9.3.6多元線性回歸分析案例實現(xiàn)3、設置回歸參數(shù)。在回歸對話框中，在Y值輸入?yún)^(qū)域選擇因變量數(shù)據(jù)區(qū)，在X值輸入?yún)^(qū)域選擇自變量數(shù)據(jù)區(qū)域。同時選擇“線性擬合圖”選項。4、根據(jù)回歸結果得到回歸函數(shù)。幾種特殊情況下的投資決策?設備更新決策設備更新決策是比較設備更新與否對企業(yè)的利弊。通常采用凈現(xiàn)值作為投資決策指標。設備更新決策可采用兩種決策方法，一種是比較新、舊兩種設備各自為企業(yè)帶來的凈現(xiàn)值的大小；另一種是計算使用新、舊兩種設備所帶來的現(xiàn)金流量差量，考察這一現(xiàn)金流量差量的凈現(xiàn)值的正負，進而做出恰當?shù)耐顿Y決策。?例（教材97－98頁）

方法1，新舊設備凈現(xiàn)值比較繼續(xù)使用舊設備:每年經(jīng)營現(xiàn)金流量為20萬元，凈現(xiàn)值為：NPV＝20萬元×PVIFA（10％，10）＝20萬元×6.145＝122.9萬元?使用新設備：初始投資額＝120－10－16＝94(萬元)經(jīng)營現(xiàn)金流量現(xiàn)值＝40×PVIFA(10%,10)＝40×6.145＝245.8(萬元)終結現(xiàn)金流量現(xiàn)值＝20×0.386＝7.72(萬元)凈現(xiàn)值＝－94＋245.8＋7.72＝159.52(萬元)由于使用新設備的凈現(xiàn)值大于繼續(xù)使用舊設備的凈現(xiàn)值，故采用新設備。?方法2：差量比較法初始投資額＝120－10－16＝94(萬元)經(jīng)營現(xiàn)金流量差量＝40－20＝20(萬元)經(jīng)營現(xiàn)金流量差量現(xiàn)值＝20×6.145＝122.9(萬元)終結現(xiàn)金流量現(xiàn)值＝20×0.386＝7.72(萬元)現(xiàn)金流量差量凈現(xiàn)值＝－94＋122.9＋7.72＝36.62(萬元)?設備比較決策這一決策比較購置不同設備的效益高低。一般來講，進行這一決策時應比較不同設備帶來的成本與收益，進而比較其各自凈現(xiàn)值的高低。但有時我們也假設不同設備帶來的收益是相同的，因而只比較其成本高低即可。很多情況下，不同設備的使用期限是不同的，因此我們不能直接比較不同設備在使用期間的凈現(xiàn)值大小，而需要進行必要的調整。這種調整有兩種：一種是將不同設備的凈現(xiàn)值轉化為年金。一種是將不同設備轉化為相同的使用年限。?例：（教材98－99頁）

設備A、B的使用期間成本現(xiàn)值分別為643573元和471622元，雖然B設備的成本現(xiàn)值小于設備A，但使用期限也小于設備A，所以二著不能直接比較。方法1，等年金比較年金現(xiàn)值公式：PV＝A×年金現(xiàn)值系數(shù)所以：A＝PV/年金現(xiàn)值系數(shù)A設備的成本現(xiàn)值＝40＋6.1×PVIFA(8%,5)=40＋6.1元×3.993＝64.36萬元其年金為：AA＝64.36萬元/3.993＝16.12萬元?B設備的成本現(xiàn)值＝25＋8.6×PVIFA(8%,5)＝25＋8.6×3.993＝47.16(萬元)其年金為：AB＝47.16元/3.993＝18.30萬元

由于設備B的成本年金高于設備A，所以應選擇設備A。?方法2，按相同使用年限比較這一方法首先要求出設備A、B使用年限的最小公倍數(shù)（本例中是15），在15年內(nèi)，設備A要更新3次，設備B要更新5次，分別計算出使用設備A、B各15年的總成本現(xiàn)值進行比較。?資本限量決策資本限量決策是在投資資金有限的情況下，選擇能給企業(yè)帶來最大投資收益的投資組合。資本限量產(chǎn)生的原因：企業(yè)無法籌措到足夠的資金；企業(yè)成長過快，缺乏足夠的管理