【Excel】合并計(jì)算和模擬分析的應(yīng)用

彭懷文

一、合并計(jì)算的應(yīng)用

Excel中的合并計(jì)算功能經(jīng)常被忽視，其實(shí)它具備非常強(qiáng)大的合并功能，包括求和、平均值、計(jì)數(shù)、最大值、最小值等一系列合并計(jì)算功能，下面本文就以一個(gè)實(shí)例來(lái)說(shuō)明Excel中合并計(jì)算的使用方法。

案例：企業(yè)有3個(gè)門店，每天都將銷售情況匯報(bào)上來(lái)了，分別存在一個(gè)工作簿中的三張不同的表上，表名分別是"1店"、"2店"、"3店"，每張表上有品名、數(shù)量。表內(nèi)品名可能重復(fù)，表與表之間品名可能相同也可能不一致。

問(wèn)題：將三張表進(jìn)行匯總。如圖：5-4-1

操作步驟：

步驟1：在工作簿中增加增加一張工作表"匯總"，然后用鼠標(biāo)點(diǎn)擊選中單元格A1，然后點(diǎn)擊"數(shù)據(jù)"→"合并計(jì)算"，就出現(xiàn)如下對(duì)話框。圖5-4-2

步驟2：合并計(jì)算的設(shè)置。設(shè)置步驟基本上就是按照下圖（圖5-4-3）所示的步驟進(jìn)行。

1：選擇"函數(shù)"，就是合并的計(jì)算方式，包括求和、平均值、計(jì)數(shù)、最大值、最小值等一系列合并計(jì)算功能，根據(jù)需要選擇。

2：選擇"引用位置"，點(diǎn)擊右邊紅色小箭頭在本工作簿中選擇引用，點(diǎn)擊"瀏覽"則在其他工作簿引用。

3："添加"。在引用位置選擇好以后點(diǎn)擊"添加"，剛剛選中的引用就到了"所有引用位置"下方的空白處。如此引用錯(cuò)誤，點(diǎn)擊"刪除"去除。

4：勾選"標(biāo)簽位置"。一般來(lái)講，此處的"首行"和"最左列"是需要勾選的。如果是跨工作簿引用建議勾選"創(chuàng)建指向源數(shù)據(jù)的鏈接"，本工作簿內(nèi)引用建議不要勾選。

5：點(diǎn)擊"確定"，合并結(jié)果就呈現(xiàn)出來(lái)了。注意：合并計(jì)算的"匯總"表單元格A1是空白的，只有手工輸入了。

二、模擬分析的應(yīng)用

模擬分析是在單元格中更改值以查看這些更改將如何影響工作表中公式結(jié)果的過(guò)程。

Excel"數(shù)據(jù)"中的"模擬分析"帶了三種模擬分析工具：方案管理器、模擬運(yùn)算表和單變量求解。方案管理器和模擬運(yùn)算表可獲取一組輸入值并確定可能的結(jié)果。模擬運(yùn)算表僅可以處理一個(gè)或兩個(gè)變量，但可以接受這些變量的眾多不同的值。一個(gè)方案可具有多個(gè)變量，但它最多只能容納 32個(gè)值。單變量求解與方案和模擬運(yùn)算表的工作方式不同，它獲取結(jié)果并確定生成該結(jié)果的可能的輸入值。

（一）方案管理器

"方案管理器"是 Excel保存并可以在工作表單元格中自動(dòng)替換的一組值。可以在工作表中創(chuàng)建和保存不同的組值，然后切換到其中的任一新方案來(lái)查看不同的結(jié)果。

例如，有一筆對(duì)外投資，有三個(gè)方案，需要計(jì)算并考慮不同折現(xiàn)率對(duì)投資凈現(xiàn)值的影響。如圖：5-4-4

當(dāng)折現(xiàn)率發(fā)生變化時(shí)，三個(gè)不同方案的凈現(xiàn)值都會(huì)發(fā)生變化，我們現(xiàn)在使用"方案管理器"將其反映并顯示到一張表。

步驟1：點(diǎn)擊"數(shù)據(jù)"→"模擬分析"→"方案管理器"，出現(xiàn)如下對(duì)話框。圖5-4-5

步驟2：點(diǎn)擊圖5-4-5中的"添加"，出現(xiàn)如圖：5-4-6

此處，我們需要進(jìn)行兩處設(shè)置。

"方案名"：根據(jù)情況進(jìn)行命名。比如我們是要不同折現(xiàn)率的凈現(xiàn)值，此處就以折現(xiàn)率大小進(jìn)行命名，如10%、8%等。

"可變單元格"的設(shè)置：首先需要清楚我們計(jì)算中需要變化的單元格。我們案例中需要變化的是折現(xiàn)率，因此可變單元格是B1，通過(guò)點(diǎn)擊右邊紅色小箭頭更改。

"保護(hù)"建議默認(rèn)設(shè)置，然后點(diǎn)擊"確定"進(jìn)行下一步。

步驟3：輸入可變單元格的值。在步驟2點(diǎn)擊"確定"后出現(xiàn)如圖對(duì)話框。圖3-4-7

5-4-7

本案例中此時(shí)只需輸入一個(gè)希望的折現(xiàn)率就可以了，然后"確定"，出現(xiàn)下圖對(duì)話框。圖5-4-8

5-4-8

步驟4：繼續(xù)添加"方案"。點(diǎn)擊圖3-4-8，重復(fù)前述的步驟1到步驟3，比如本案例繼續(xù)輸入12%、8%、6%等，最后出現(xiàn)如下圖：5-4-9

步驟5：出結(jié)果報(bào)告。點(diǎn)擊5-4-9中的"摘要"，出現(xiàn)如下圖：5-4-10

點(diǎn)擊"結(jié)果單元格"右邊紅色小箭頭，選擇結(jié)果單元格，最后"確定"，結(jié)果如圖5-4-11和5-4-12

5-4-11

方案摘要型

5-4-12

數(shù)據(jù)透視表型

（二）模擬運(yùn)算表

模擬運(yùn)算表是進(jìn)行預(yù)測(cè)分析的一種工具，它可以顯示Excel工作表中一個(gè)或多個(gè)數(shù)據(jù)變量的變化對(duì)計(jì)算結(jié)果的影響，求得某一過(guò)程中可能發(fā)生的數(shù)值變化，同時(shí)將這一變化列在表中以便于比較。

運(yùn)算表根據(jù)需要觀察的數(shù)據(jù)變量的多少可以分為單變量數(shù)據(jù)表和多變量數(shù)據(jù)表兩種形式，下面以創(chuàng)建多變量數(shù)據(jù)表為例來(lái)介紹在Excel工作表中使用模擬運(yùn)算表的方法。本例數(shù)據(jù)表用于預(yù)測(cè)不同利率和不同年限所對(duì)應(yīng)的復(fù)利終值，創(chuàng)建的是一個(gè)有兩個(gè)變量的模擬運(yùn)算表。

步驟1：創(chuàng)建一張數(shù)據(jù)表。如圖：5-4-13

在單元格B4輸入函數(shù)公式"=ROUND(-FV($B$2,$B$3,,$B$1,1),2)"，并將該函數(shù)公式復(fù)制到單元格B6。

步驟2：將鼠標(biāo)點(diǎn)中單元格B6并選擇區(qū)域B6:L12，然后點(diǎn)擊"數(shù)據(jù)"→"模擬運(yùn)算"→"模擬運(yùn)算表"，出現(xiàn)下圖對(duì)話框（圖5-4-14）。

此時(shí)需要分清行和列單元格。在圖5-4-13中行單元格是"年限"，列單元格是"利率"。因此，"輸入引用行的單元格"應(yīng)該是：$B$3；"輸入引用列的單元格"應(yīng)該是：$B$2；然后，"確定"。如圖：5-4-15

5-4-15

最終結(jié)果，如圖5-4-16：

該表是不是就是一張復(fù)利終值系數(shù)表呢？

（三）單變量求解

如果您知道要從公式獲得的結(jié)果，但不確信為獲得該結(jié)果所需的公式輸入值，此時(shí)，您可以使用單變量求解功能。例如，我們?cè)谏弦粋€(gè)案例中的復(fù)利終值計(jì)算。如圖：如圖5-4-17

5-4-17

復(fù)利終值所在單元格B4我們輸入了函數(shù)公式，結(jié)果是根據(jù)利率、年限等計(jì)算而來(lái)。現(xiàn)在的問(wèn)題是：如果我們希望得到復(fù)利終值是150，而其他條件不變的情況下，利率是多少？

此時(shí)，我們就可以使用"單變量求解"。

步驟1：鼠標(biāo)點(diǎn)中單元格B4，然后點(diǎn)擊"數(shù)據(jù)"→"模擬分析"→"單變量求解"，出現(xiàn)下圖：5-4-18

5-4-18

步驟2：輸入目標(biāo)值和可變單元格。案例中問(wèn)題已提出我們的目標(biāo)值是150，因此在"目標(biāo)值"處輸入150；可變單元格是利率對(duì)應(yīng)的單元格B1,用鼠標(biāo)點(diǎn)擊一下。"單變量求解"就變成如圖：5-4-19

點(diǎn)擊"確定"，結(jié)果就出來(lái)了：圖5-4-20

利率變成了8.4472%，復(fù)利終值變成了150.00。

總結(jié)

以上是生活随笔為你收集整理的excel合并计算_【Excel】合并计算和模拟分析的应用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。