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

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

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

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

Забележка: Можете да извършвате по-бързи изчисления с таблици с данни и Visual Basic for Applications (VBA). За повече информация вижте таблици с данни в Excel If: по-бързо изчисление с VBA.

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

Има три типа инструменти за условен анализ в 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 и Е2.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Solver за Excel

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

Чрез включването на различни числа в клетка можете бързо да измислите различни отговори на даден проблем. Чудесен пример използва функцията PMT с различни лихви и периоди на заема (в месеци), за да разберете колко от кредита можете да си позволите за жилище или кола. Въвеждате числата в диапазон от клетки, наречени таблица с данни.

Тук таблицата с данни е диапазонът от клетки B2: D8. Можете да промените стойността в B4, размерът на кредита и месечните плащания в колона D автоматично да се актуализират. Използвайки лихвена ставка 3,75%, D2 връща месечно плащане на $1 042,01 с помощта на тази формула: = PMT (C2/12; $B $3 $B $4).

Този диапазон от клетки B2:D8 е таблица с данни

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

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

В тази работна книга на живо клетка D2 съдържа формулата за плащане =PMT (C2/12; $B $3; $B $4). Клетка B3 е променливата клетка, където можете да включите различна продължителност на срока (брой месечни периоди на плащане). В клетка D2, функцията PMT включва в лихвената ставка 3,75%/12, 360 месеца и заем за $225 000 и изчислява $1 042,01 месечно плащане.

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

В тази работна книга на живо клетка C3 съдържа формулата за плащането, =PMT ($B $3/12, $B $2, B4), която използва две променливи клетки, B2 и B3. В клетка C2, функцията PMT се включва в лихвената ставка 3.875%/12, 360 месеца и заем за $225 000 и изчислява $1 058,03 месечно плащане.

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

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

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

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

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

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

×