Как да извършвате анализи какво-ако с таблици с данни в Excel 2016

Чрез извършване на анализи какво-ако с таблици с данни на Excel 2016, вие променяте данните във входните клетки и наблюдавате какъв ефект има промяната на данните върху резултатите от формула. С таблица с данни можете да експериментирате едновременно с много различни входни клетки и по този начин да експериментирате с много различни сценарии.

Използване на таблица с един вход за анализ

В таблица с един вход ще разберете какви биха били различните резултати от формула, ако промените една клетка за въвеждане във формулата. Тук тази входна клетка е лихвеният процент по заема. Целта на тази таблица с данни е да разбере как месечните плащания по 30-годишна ипотека от $250 000 са различни, като се имат предвид различни лихвени проценти. Лихвеният процент в клетка B4 е входната клетка.

Таблица с данни с един вход.

Следвайте тези стъпки, за да създадете таблица с един вход:

Във вашия работен лист въведете стойности, които искате да замените със стойността във входната клетка.

За да накарате таблицата за въвеждане да работи, трябва да въведете заместващите стойности на правилното място:

• В колона: Въведете стойностите в колоната, започвайки една клетка по-долу и една клетка вляво от клетката, където се намира формулата. В показания пример формулата е в клетка E4, а стойностите са в диапазона от клетки D5:D15.

• На ред: Въведете стойностите в реда, започвайки една клетка по-горе и една клетка вдясно от клетката, където е формулата.

Изберете блока от клетки с формулата и заместете стойностите.

Изберете правоъгълник от клетки, който обхваща клетката с формула, клетката до нея, всички заместващи стойности и празните клетки, където скоро ще се появят новите изчисления.

• В колона: Изберете клетката с формула, клетката отляво, всички клетки със заместваща стойност и клетките под клетката с формула.

• В ред: Изберете клетката с формула, клетката над нея, заместващите стойности в клетките директно вдясно и вече празните клетки, където ще се появят новите изчисления.

В раздела Данни щракнете върху бутона Анализ какво ако и изберете Таблица с данни от падащия списък.

Виждате диалоговия прозорец Таблица с данни.

В текстовото поле Клетка за въвеждане на ред или Входяща клетка в колона въведете адреса на клетката, където се намира входната стойност.

За да въведете този адрес на клетка, излезте извън диалоговия прозорец Таблица с данни и щракнете върху клетката. Входната стойност е стойността, с която експериментирате във вашия анализ. В случая на показания работен лист, входната стойност се намира в клетка B4, клетката, която съдържа лихвения процент.

Ако новите изчисления се появят на редове, въведете адреса на входната клетка в текстовото поле Ред Input Cell; ако изчисленията се показват в колони, въведете адреса на входната клетка в текстовото поле Column Input Cell.

Щракнете върху OK.

Excel извършва изчисленията и попълва таблицата.

За да генерира таблица с един вход, Excel изгражда формула на масив с функцията TABLE. Ако промените препратките към клетките в първия ред или включите различни стойности в първата колона, Excel актуализира автоматично таблицата с един вход.

Използване на таблица с два входа за анализ

В таблица с два входа можете да експериментирате с две входни клетки, а не с една. Връщайки се към примера за плащане на заема, можете да изчислите не само как се променят плащанията по заема с промяна на лихвените проценти, но и как се променят плащанията, ако се промени срокът на заема. Тази фигура показва таблица с два входа за изследване на месечните плащания по заема при различни лихвени проценти и два различни срока за заема, 15 години (180 месеца) и 30 години (360 месеца).

Таблица с данни с два входа.

Следвайте тези стъпки, за да създадете таблица с данни с два входа:

Въведете един набор от заместващи стойности под формулата в същата колона като формулата.

Тук се въвеждат различни лихвени проценти в диапазона на клетките D5:D15.

Въведете втория набор от заместващи стойности в реда вдясно от формулата.

Тук се въвеждат 180 и 360. Тези числа представляват броя на месеците от живота на заема.

Изберете формулата и всички заместващи стойности.

Направете това правилно и избирате три колони, включително формулата, заместващите стойности под нея и двете колони вдясно от формулата. Избирате голям блок от клетки (диапазонът D4:F15, в този пример).

В раздела Данни щракнете върху бутона Анализ какво ако и изберете Таблица с данни от падащия списък.

Появява се диалоговият прозорец Таблица с данни.

В текстовото поле Ред за въвеждане на клетка въведете адреса на клетката, посочена в оригиналната формула, където могат да бъдат включени заместващи стойности отдясно на формулата.

Въведете адреса на клетката, като излезете извън диалоговия прозорец и изберете клетка. Тук, например, редовете вдясно от формулата са за дължина на стойностите, заместващи заема. Следователно, изберете клетка B5, клетката, посочена в оригиналната формула, където е посочена дължината на заема.

В текстовото поле Column Input Cell въведете адреса на клетката, посочена в оригиналната формула, където са заместващите стойности под формулата.

Заместващите стойности под клетката с формула са лихвени проценти. Следователно, изберете клетка B4, клетката, посочена в оригиналната формула, където е въведен лихвеният процент.

Щракнете върху OK.

Excel извършва изчисленията и попълва таблицата.