數(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ù)轉(zhuǎn)換成匯總表。從本質(zhì)上講，數(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)計每個學(xué)院不同地區(qū)的學(xué)生數(shù)和平均入學(xué)成績。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)建②設(shè)置透視表的布局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)容進行篩選，但篩選結(jié)果仍然顯示在一張表格中，即每次只能進行一種篩選。利用【數(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ù)排列將非常復(fù)雜。與數(shù)據(jù)透視表相比，數(shù)據(jù)透視圖更能直觀地分析數(shù)據(jù)，展示數(shù)據(jù)之間的關(guān)系。創(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ù)被相同地排列以進行合并計算。按位置合并計算只適合具有相同結(jié)構(gòu)數(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合并計算-按位置合并計算④

“合并計算”結(jié)果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í)行“合并計算”結(jié)果9.3數(shù)據(jù)的高級分析與處理9.3.1合并計算-多表分類匯總9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解規(guī)劃求解適用于需要通過多個變量的變化來找到一個目標值的情況。只要問題有單一的目標，有明確的不等式約束條件，有直接或間接影響約束條件的一組輸入值，就可以應(yīng)用規(guī)劃求解解決。Excel規(guī)劃求解問題由以下3部分組成（1）可變單元格（2）目標函數(shù)（3）約束條件打開Excel2023工作文件——>【鼠標左鍵】單擊【Microsoftoffice】按鈕——>【鼠標左鍵】單擊【Excel選項】按鈕；然后在彈出的【Excel選項】下——>【鼠標左鍵】單擊【加載項】——>在【管理：】右方選擇【Excel

加載項】選項卡——>【鼠標左鍵】單擊【轉(zhuǎn)到】按鈕；再在彈出的【加載宏】方框下——>【鼠標左鍵】勾選【規(guī)劃求解加載項】——>【鼠標左鍵】單擊【確定】按鈕；最后在Excel工作文檔中【鼠標左鍵】單擊【數(shù)據(jù)】選項卡即可看到位于該選項卡下右方的【數(shù)據(jù)分析】模塊，【鼠標左鍵】單擊【規(guī)劃求解】即可看到【規(guī)劃求解】模塊。案例某肥料廠專門收集有機物垃圾，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進不同比例的泥土和礦物質(zhì)來生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料4種。為使問題簡單，假設(shè)收集廢物的勞動力是自愿的，除了收集成本之外，材料成本是低廉的。該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價等如下各表所示。問題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤，最大利潤是多少？表2生產(chǎn)肥料的庫存原材料庫存情況現(xiàn)有庫存泥土4100有機垃圾3200礦物質(zhì)3500修剪物1600表1各肥料成品用料及其價格表產(chǎn)品泥土有機垃圾礦物質(zhì)修剪物單價底層肥料55547623105.00中層肥料6432452084.00上層肥料43329844105.00劣質(zhì)肥料1845231857.00表3單位原材料成本單價項目單位成本泥土0.20有機垃圾0.15礦物質(zhì)0.10修剪物0.23規(guī)劃求解第一步——建立求解工作表規(guī)劃求解第二步——設(shè)置求解參數(shù)選擇“開發(fā)工具”|“規(guī)劃求解”菜單，設(shè)置求解的各項參數(shù)，如下圖所示。規(guī)劃求解第3步——求解

規(guī)劃求解的結(jié)果報告運算結(jié)果報告2、敏感性報告3、極限報告9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解已知某家具廠目前庫存情況是：木料4000個單位，油漆4500個單位，活頁2023個單位，螺絲2023個單位。工廠生產(chǎn)A,B,C,D這四種產(chǎn)品。不同產(chǎn)品消耗的材料不同，售價也不同。試問，該廠利用現(xiàn)有的庫存應(yīng)該生產(chǎn)4種產(chǎn)品各多少件才能達到利潤的最大化。9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解—①建立規(guī)劃模型9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.2規(guī)劃求解—②規(guī)劃求解9.3數(shù)據(jù)的高級分析與處理9.3.3回歸分析回歸分析研究變量之間的非確定因果關(guān)系。通常把其中的一些變量作為自變量（預(yù)測變量，X），來預(yù)測另外一些隨自變量變化而變化的變量（被預(yù)測變量，Y）。9.3數(shù)據(jù)的高級分析與處理9.3.3回歸分析—線性回歸兩個變量之間有準確嚴格的直線相關(guān)關(guān)系。一個變量的變化引起另外一個變量變化的程度可以用線性回歸方程式來表示。9.3數(shù)據(jù)的高級分析與處理9.3.3回歸分析—線性回歸例如，下表是某公司10年來廣告費和銷售量數(shù)據(jù)，試探據(jù)此估計該公司為了達到4000萬的銷售量，2023年的廣告費應(yīng)投入多少。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ù)的高級分析與處理回歸系數(shù)：選中b15:c15，輸入公式=LINEST(C2:C11,B2:B11),按下Ctrl+shift+Alt相關(guān)系數(shù)=CORREL(C2:C11,B2:B11)9.3數(shù)據(jù)的高級分析與處理9.3.3回歸分析—非線性回歸兩個變量之間之間的關(guān)系不是單純的直線關(guān)系。如年齡與收入的關(guān)系，這種情況下要用到非線性回歸分析方法。9.3數(shù)據(jù)的高級分析與處理9.3.3回歸分析—非線性回歸例如，根據(jù)以下基本數(shù)據(jù)，預(yù)測某人65歲時的收入。9.3數(shù)據(jù)的高級分析與處理9.3.3根據(jù)以下基本數(shù)據(jù)，預(yù)測某人65歲時的收入。9.3數(shù)據(jù)的高級分析與處理9.3.4多元線性回歸分析一元回歸分析適用于分析一個自變量和一個因變量之間的關(guān)系，而在實際情況中，常會遇到一個因變量和多個自變量數(shù)量關(guān)系的問題，多元回歸分析正是為了解決這些問題而產(chǎn)生的。9.3數(shù)據(jù)的高級分析與處理9.3.4多元線性回歸分析案例：銷售公司在各大城市都有分銷公司，公司采用的是經(jīng)銷商銷售模式。公司統(tǒng)計了2023年各大城市的人數(shù)、公司在該城市的經(jīng)銷商個數(shù)以及銷售量的數(shù)值，公司需要以該數(shù)據(jù)為基礎(chǔ)，分析影響公司銷售情況的各個因素以及預(yù)測公司的銷售前景。9.3數(shù)據(jù)的高級分析與處理9.3.4多元線性回歸分析9.3數(shù)據(jù)的高級分析與處理9.3.4多元線性回歸分析案例實現(xiàn)1、加載“數(shù)據(jù)分析”宏。選擇“開發(fā)工具”-“加載項”，打開加載宏對話框，在其中選擇加載“分析工具庫”，然后單擊“確定”按鈕。2、打開“回歸”對話框。選擇“數(shù)據(jù)”-“數(shù)據(jù)分析”選項，打開“數(shù)據(jù)分析”對話框，在其中選擇“回歸”選項，單擊“確定”按鈕。9.3數(shù)據(jù)的高級分析與處理9.3.4多元線性回歸分析案例實現(xiàn)3、設(shè)置回歸參數(shù)。在回歸對話框中，在Y值輸入?yún)^(qū)域選擇因變量數(shù)據(jù)區(qū)，在X值輸入?yún)^(qū)域選擇自變量數(shù)據(jù)區(qū)域。同時選擇“線性擬合圖”選項。4、根據(jù)回歸結(jié)果得到回歸函數(shù)。幾種特殊情況下的投資決策?設(shè)備更新決策設(shè)備更新決策是比較設(shè)備更新與否對企業(yè)的利弊。通常采用凈現(xiàn)值作為投資決策指標。設(shè)備更新決策可采用兩種決策方法，一種是比較新、舊兩種設(shè)備各自為企業(yè)帶來的凈現(xiàn)值的大??；另一種是計算使用新、舊兩種設(shè)備所帶來的現(xiàn)金流量差量，考察這一現(xiàn)金流量差量的凈現(xiàn)值的正負，進而做出恰當?shù)耐顿Y決策。?例（教材97－98頁）

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

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

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