小編有話說：作為一名財(cái)務(wù)人員，要想升職加薪，肯定不能只會(huì)統(tǒng)計(jì)數(shù)據(jù)，還需要根據(jù)公司的需要，做一些數(shù)據(jù)的預(yù)測(cè)，如果人工去預(yù)測(cè)，需要經(jīng)過很復(fù)雜的數(shù)學(xué)運(yùn)算，其實(shí)excel中為我們提供了一款工具可以很簡(jiǎn)便的完成數(shù)據(jù)預(yù)測(cè)，那就是Excel單變量求解功能。

當(dāng)下,分析預(yù)測(cè)越來越成為財(cái)務(wù)人員工作中的重要內(nèi)容。其中最為常見有效的就是單變量分析。完成單變量分析的方法有很多，比如插值法、試錯(cuò)法等等。但對(duì)于一個(gè)Excel達(dá)人，單變量求解無疑才是最佳答案。

一、什么是單變量求解？

如下圖所示，B2單元格為常量，而B4單元格是B2經(jīng)過某種特定運(yùn)算的結(jié)果。我們可以通過改變B2的數(shù)值，從而使B4也跟著改變。這里我們稱B2為可變單元格，B4為目標(biāo)單元格。

這種順序計(jì)算非常簡(jiǎn)單，比如B2為95，B4就為200。那么問題來了，如果我們想讓目標(biāo)單元格（B4）等于某一特定的值，與之對(duì)應(yīng)的可變單元格（B2）值應(yīng)該怎么求解呢？就好比小學(xué)數(shù)學(xué)里的，我們知道X值可以求得Y值，但如何根據(jù)Y值求出X值呢？你需要單變量求解的幫助。單變量求解是函數(shù)公式的逆運(yùn)算。

那么Excel中單變量求解在哪里，以及Excel中單變量求解原理、Excel單變量求解案例咱們都會(huì)在本文告訴大家。

舉一個(gè)例子，我們假設(shè)目標(biāo)值為250，具體操作如下：

Step 01 點(diǎn)擊Excel【數(shù)據(jù)】選項(xiàng)卡—【模擬分析】—【單變量求解】按鈕，彈出【單變量求解】對(duì)話框。

Step 02 在對(duì)話框中，目標(biāo)單元格設(shè)置為$B$4，再手動(dòng)輸入目標(biāo)單元格的目標(biāo)值為250，設(shè)置可變單元格為$B$2。如下圖所示。

需要注意的是，目標(biāo)值250并不是隨便設(shè)置的，而需要滿足以下兩個(gè)條件：

1．它是一個(gè)整數(shù)或小數(shù)，不能輸入文字或邏輯值；

2．它應(yīng)該包含在目標(biāo)單元格值域中，即存在某一可變單元格值使目標(biāo)單元格值等于目標(biāo)值，否則，單變量求解將會(huì)運(yùn)行得較長(zhǎng)且最終提示無法獲得滿足條件的解。

Step 03 點(diǎn)擊確定按鈕，執(zhí)行單變量求解。excel自動(dòng)進(jìn)行迭代運(yùn)算，最終得出使目標(biāo)單元格（B4）等于目標(biāo)值（250）時(shí)的可變單元格值（120），并自動(dòng)賦予可變單元格（B2）。如下所示，點(diǎn)擊確定。

二、Excel單變量求解實(shí)例

Excel單變量求解案例1  貸款利率問題

A員工是甲公司的融資專員，公司計(jì)劃向銀行申請(qǐng)五年期貸款3500萬，采取每月等額償還本息的方法歸還貸款本金并支付利息。按目前銀行初步提出的年利率6.3%方案，A員工經(jīng)過計(jì)算得出，甲公司每月需支付68.15萬。

如下圖所示，使用公式：=PMI(年利率/12，期數(shù)，貸款額），就可以計(jì)算出每月歸還利息為68.15萬。

但經(jīng)過測(cè)算，公司取得貸款后，每月可用于還貸的資金預(yù)計(jì)只有67.5萬。財(cái)務(wù)總監(jiān)要求A員工算出公司能接受的最大年利率，以便與銀行商議。

A員工分析，年利率B5單元格是可變單元格，每月歸還本息額B6是目標(biāo)單元格，目標(biāo)值是67.5，這不正是單變量求解嗎？

Step 01 根據(jù)已經(jīng)設(shè)置好的數(shù)據(jù)關(guān)系，點(diǎn)擊【單變量求解】按鈕，設(shè)置目標(biāo)單元格為$B$6，目標(biāo)值為67.5，可變單元格為$B$5，點(diǎn)擊【確定】進(jìn)行單變量求解。

Step 02  經(jīng)過迭代運(yùn)算，excel很快給出了滿足條件的解，點(diǎn)擊【確定】，完成單變量求解。如下所示。

從上圖我們可以看出，公司可以接受的最大年利率為5.9%。這就是單變量求解，不僅高效，而且精準(zhǔn)！

Excel單變量求解案例2  等效利率問題

B員工是某公司的投資經(jīng)理，公司計(jì)劃取得一項(xiàng)新項(xiàng)目，需投入16億元（累計(jì)投資額現(xiàn)值），計(jì)劃單方售價(jià)18800，預(yù)計(jì)平均回款年限5年，經(jīng)計(jì)算，公司在該項(xiàng)目投資額的等效利率為8.8%。

如下圖所示，使用公式：=RRI(回款年限，總投資現(xiàn)值，銷售收入），可以求出當(dāng)前投資值對(duì)應(yīng)未來值的實(shí)際等效利率。

根據(jù)公司內(nèi)部要求，10億級(jí)項(xiàng)目的投資等效利率不能低于10%，所以需要調(diào)整售價(jià)。

B員工將售價(jià)B3作為可變單元格，等效利率B8作為目標(biāo)單元格，目標(biāo)值為10%。

Step 01 根據(jù)已經(jīng)設(shè)置好的數(shù)據(jù)關(guān)系，點(diǎn)擊【單變量求解】按鈕，設(shè)置目標(biāo)單元格為$B$8，目標(biāo)值為10%，可變單元格為$B$3，點(diǎn)擊【確定】進(jìn)行單變量求解。

Step 02 經(jīng)過迭代運(yùn)算，excel很快給出了滿足條件的解，點(diǎn)擊【確定】，完成單變量求解。

如上圖我們可以看到結(jié)果，最低售價(jià)調(diào)整為19,879.09元，就可以達(dá)到公司的要求。

Excel單變量求解案例3  盈虧平衡點(diǎn)問題

C員工是某制造公司的財(cái)務(wù)經(jīng)理，公司有一工廠，由于市場(chǎng)表現(xiàn)不佳，公司計(jì)劃縮減工廠產(chǎn)量至20萬，C員工需要測(cè)算出在該產(chǎn)量下該工廠的盈虧情況。已知產(chǎn)品平均售價(jià)隨產(chǎn)量上升而下降，三費(fèi)與銷售和收入成一定的比例。經(jīng)計(jì)算，20萬產(chǎn)量下的利潤總額為1383.68萬元。如下圖所示。

公司產(chǎn)品市場(chǎng)表現(xiàn)繼續(xù)下滑，公司高層考慮繼續(xù)縮減產(chǎn)量，要求將產(chǎn)量控制在盈虧平衡點(diǎn)左右。

C員工思考，將產(chǎn)量B3作為可變單元格，利潤總額B12作為目標(biāo)單元格，目標(biāo)值為0，一下子就計(jì)算出了盈虧平衡產(chǎn)量。

Step 01 根據(jù)已經(jīng)設(shè)置好的數(shù)據(jù)關(guān)系，點(diǎn)擊【單變量求解】按鈕，設(shè)置目標(biāo)單元格為$B$12，目標(biāo)值為0，可變單元格為$B$3，點(diǎn)擊【確定】進(jìn)行單變量求解。

Step 02 經(jīng)過迭代運(yùn)算，excel很快給出了滿足條件的解，點(diǎn)擊【確定】，完成單變量求解。

根據(jù)上圖我們可以看到，公司將生產(chǎn)量控制在8.19萬附近，可以盈虧平衡。

單變量求解算得上Excel中的一個(gè)高能冷門運(yùn)用，頗有“說破不值錢，不說累死人”的感覺。以前在這類單變量分析問題上，大家可能花很多時(shí)間手動(dòng)嘗試求解，從現(xiàn)在起，你可以從這樣的低效率勞作中解放出來了，單變量求解將成為你的得力助手。

本文配套的練習(xí)課件請(qǐng)加入QQ群：316492581下載。

如果您因工作所需使用到Excel，不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。

如果您想要隨時(shí)隨地學(xué)習(xí)excel，掃下方二維碼，可關(guān)注公眾號(hào)，每日為您推送優(yōu)質(zhì)excel教程：

Excel教程相關(guān)推薦：

《玩轉(zhuǎn)Excel圖表系列》