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

Забележка:  Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

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

В Microsoft Excel таблиците с данни са част от пакет от команди, известно като инструменти за условен анализ. Когато съставяте и анализиране на таблици с данни, които правят условен анализ.

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

Типове на условен анализ   

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

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

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

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

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

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

Използвайте таблица с данни с една променлива, ако искате да видите как различните стойности на една променлива в една или повече формули ще променят резултатите от тези формули. Например можете да използвате таблица с данни с една променлива, за да видите как различните лихви засягат месечните ипотечни плащания с помощта на функцията 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. Направете някое от следните неща:

    • В Excel 2007щракнете върху Бутона Microsoft Office Изображение на бутона Office , щракнете върху Опции на Excelи след това щракнете върху категорията формули .

    • Във всички версии, щракнете върху файл > Опции > формули.

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

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

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

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

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

Excel Solver

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

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на Excel, да получите поддръжка в общността за отговори или да предложите нова функция или подобрение на User Voice за Excel.

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×