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

В 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. В раздела Данни щракнете върху Условен анализ > таблица с данни (в групата Инструменти за данни или групата Прогноза на 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

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