Изчисляване на множество резултати с помощта на таблица с данни

В Microsoft Excel таблиците с данни са част от набор от команди, известни като What-If за анализ. Когато изграждате и анализирате таблици с данни, правите т.нал. анализ.

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

Типове т.на. анализ    

Има три типа инструменти за таен анализ в Excel: сценарии, таблици с даннии търсене на цели. Сценариите и таблиците с данни използват набори от входни стойности, за да изчисляват възможните резултати. Търсенето на цел е различно, използва един резултат и изчислява възможните входни стойности, които биха довели до този резултат.

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

Таблицата с данни не може да побере повече от две променливи. Ако искате да анализирате повече от две променливи, трябва вместо това да използвате сценарии. Въпреки че е ограничена само до една или две променливи (една за входната клетка за ред и една за входната клетка на колоната), таблицата с данни може да включва толкова различни променливи стойности, колкото искате. Сценарият може да има максимум 32 различни стойности, но можете да създадете толкова сценарии, колкото желаете.

Научете повече в статията Въведение в What-If анализ.

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

Таблици с данни с една променлива    

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

В следващата илюстрация клетка D2 съдържа формулата за плащане= PMT(B3/12;B4;-B5),която препраща към входната клетка B3.

Таблици с данни с две променливи    

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

В следващата илюстрация клетка C2 съдържа формулата за плащане= PMT(B3/12;B4;-B5),която използва две входни клетки – B3 и B4.

 

Изчисления на таблица с данни    

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

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

Изпълнете следните стъпки:

1. Въведете списъка със стойности, които искате да заместите във входната клетка – или с една колона, или в един ред. Оставете няколко празни реда и колоните от двете страни на стойностите.

2. Направете едно от следните неща:

   • Ако таблицата с данни е ориентирана към колони (променливите ви стойности са в колона), въведете формулата в клетката един ред по-горе и една клетка отдясно на колоната със стойности. Тази таблица с данни с една променлива е ориентирана към колони и формулата се съдържа в клетка D2.
     
     
     
     Ако искате да изследвате ефектите на различни стойности върху други формули, въведете допълнителните формули в клетките отдясно на първата формула.

   • Ако таблицата с данни е ориентирана към ред (променливите ви стойности са в ред), въведете формулата в клетката с една колона отляво на първата стойност и една клетка под реда със стойности.
     
     Ако искате да изследвате ефектите на различни стойности върху други формули, въведете допълнителните формули в клетките под първата формула.

3. Изберете диапазона от клетки, съдържащ формулите и стойностите, които искате да заместите. На фигурата по-горе този диапазон е C2:D5.

4. В раздела Данни щракнете върху What-If Analysis >таблица с данни (в групата Инструменти за данни или Групата Прогноза на Excel 2016 ). 

5. Направете едно от следните неща:

   • Ако таблицата с данни е ориентирана към колони, въведете препратка към клетка за входната клетка в полето За въвеждане на колона. На фигурата по-горе входната клетка е B3.

   • Ако таблицата с данни е ориентирана към ред, въведете препратката към клетка за въвеждане на клетка в полето За въвеждане на редове.

     Забележка: След като създадете таблицата с данни, може да искате да промените формата на клетките с резултати. На фигурата клетките на резултата се форматират като валута.

Формулите, които се използват в таблица с данни с една променлива, трябва да препращат към една и съща входна клетка.

Изпълнете следните стъпки:

1. Направете едно от следните неща:

   • Ако таблицата с данни е ориентирана към колони, въведете новата формула в празна клетка отдясно на съществуваща формула в горния ред на таблицата с данни.

   • Ако таблицата с данни е ориентирана към редове, въведете новата формула в празна клетка под съществуваща формула в първата колона на таблицата с данни.

2. Изберете диапазона от клетки, съдържащ таблицата с данни и новата формула.

3. В раздела Данни щракнете върху Таблица с> данни (в групата Инструменти за данни или Прогноза на Excel 2016 ).

4. Направете някое от следните неща:

   • Ако таблицата с данни е ориентирана към колони, въведете препратката към клетка за входната клетка в полето Въвеждане на колона.

   • Ако таблицата с данни е ориентирана към ред, въведете препратката към клетката за въвеждане в полето Входна клетка за ред.

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

Изпълнете следните стъпки:

1. В клетка в работния лист въведете формулата, която препраща към двете входни клетки.

   В следващия пример– в който началните стойности на формулата са въведени в клетки B3, B4 и B5, въвеждате формулата =PMT(B3/12;B4;-B5) в клетка C2.

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

   В този случай въведете различните лихвени проценти в клетки C3, C4 и C5.

3. Въведете втория списък в същия ред като формулата – отдясно.

   Въведете условията на заема (в месеци) в клетки D2 и E2.

4. Изберете диапазона от клетки, който съдържа формулата (C2), както редът, така и колоната със стойности (C3:C5 и D2:E2), и клетките, в които искате изчислените стойности (D3:E5).

   В този случай изберете диапазона C2:E5.

5. В раздела Данни, в групата Инструменти за данни или Прогноза (в Excel 2016 ) щракнете върху Т.е. Анализ >таблица с данни (в групата Инструменти за данни или Групата Прогноза на Excel 2016 ).  

6. В полето Входна клетка за ред въведете препратката към входната клетка за входните стойности в реда.
   Въведете клетка B4 в полето за въвеждане на ред.

7. В полето Входна клетка "Колона" въведете препратката към входната клетка за входните стойности в колоната.
   Въведете B3 в полето За въвеждане на колона.

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

Пример за таблица с данни с две променливи

Таблица с данни с две променливи може да показва как различните комбинации от лихвени проценти и условия на заема ще повлияят на месечното ипотечно плащане. Във фигурата тук клетка C2 съдържа формулата за плащане, =PMT(B3/12;B4;-B5),която използва две входни клетки – B3 и B4.

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

Следвайте тези стъпки, за да подобрите производителността на изчисленията:

1. Щракнете върху Файл > Опции > формули.

2. В секцията Опции за изчисление, под Изчисляване щракнетевърху Автоматично, с изключение на таблиците с данни.

   Съвет: Ако е възможно, в раздела Формули щракнете върху стрелката в Опции за изчисление, след което щракнете върху Автоматични освен таблици с данни (в групата Изчисление).

Можете да използвате няколко други инструмента Excel за извършване на т.нал. анализ, ако имате конкретни цели или по-големи набори от променливи данни.

Търсене на цел

Ако знаете резултата, който да очаквате от формула, но не знаете точно каква входна стойност трябва да получи формулата, използвайте Goal-Seek функция. Вижте статията Използване на "Търсене на цел", за да намерите резултата, който искате, като коригирате входна стойност.

Excel Solver

Можете да използвате добавката Excel Solver, за да намерите оптималната стойност за набор от входни променливи. Solver работи с група от клетки (наречени променливи на решения или просто променливи клетки), които се използват при изчисляването на формулите в клетките за цел и ограничение. Solver настройва стойностите в променливите на решението, за да удовлетворяват границите в клетките с ограничения, и поражда желания от вас резултат за целевата клетка. Научете повече в тази статия: Дефиниране и решаване на проблем с помощта на Solver.