Таблица с данни в Microsoft Excel

Често е необходимо да се изчислява крайният резултат за различни комбинации от входни данни. По този начин потребителят ще може да оцени всички възможни опции за действия, да избере онези, чиито резултати от взаимодействието го удовлетворяват, и накрая да избере най-оптималната опция. В Excel, за изпълнение на тази задача има специален инструмент - "Таблица с данни" ( "Таблица за заместване" ). Нека да разберем как да го използваме, за да изпълним горните сценарии.

Прочетете още: Избор на параметри в Excel

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

Инструментът "Таблица с данни" е предназначен да изчисли резултата за различните варианти на една или две дефинирани променливи. След изчисляването всички възможни варианти ще се появят под формата на таблица, която се нарича матрица на факторен анализ. "Таблица с данни" се отнася до групата за анализ "Какво, ако" , която се намира на лентата в раздела "Данни" в блока "Работа с данни" . Преди Excel 2007 този инструмент е наречен "Таблица за заместване" , което дори по-точно отразява неговата същност, отколкото сегашното име.

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

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

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

Основната разлика между различните начини на използване на таблицата с данни е броят на променливите, включени в изчислението: една променлива или две.

Метод 1: Прилагане на инструмент с една променлива

Веднага нека разгледаме опцията, когато таблицата с данни се използва с една променлива стойност. Нека вземем най-типичния пример за кредитиране.

Така че, в момента се предлагат следните кредитни условия:

• Срок на заема - 3 години (36 месеца);
• Размерът на заема е 900 000 рубли;
• Лихвеният процент е 12,5% годишно.

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

Необходимо е да се изчисли каква ще бъде сумата на месечното плащане, която включва изплащането на заема и лихвените плащания. За да направите това, в Excel има PLT оператор.

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

=ПЛТ(ставка;кпер;пс;бс;тип)

"Оцени" е аргумент, който определя лихвения процент на кредитните плащания. Индикаторът е зададен за определен период от време. Имаме период на изплащане от един месец. Ето защо годишната ставка от 12,5% трябва да се разбие на броя на месеците в една година, т.е. 12.

"Kper" - аргумент, който определя броя на периодите за целия период на заема. В нашия пример периодът е един месец, а периодът на кредитиране е 3 години или 36 месеца. По този начин броят на периодите ще бъде ранен 36.

"PS" е аргументът, който определя настоящата стойност на заема, т.е. размерът на кредита на тялото към момента на емитирането му. В нашия случай тази цифра е 900 000 рубли.

"BS" е аргумент, посочващ сумата на органа на заема към момента на пълното му плащане. Естествено този показател ще бъде нула. Този аргумент не е задължителен параметър. Ако го пропуснете, се приема, че то е равно на числото "0".

"Тип" също е незадължителен аргумент. Той съобщава кога ще бъде извършено плащането: в началото на периода (параметър - "1" ) или в края на периода (параметър - "0" ). Както си спомняме, плащаме в края на календарния месец, т.е. стойността на този аргумент ще бъде равна на "0" . Но, като се има предвид, че този индикатор не е задължителен, и по подразбиране, ако не го използвате, стойността и така трябва да бъде "0" , а в този пример той като цяло не може да бъде използван.

1. Така че, нека започнем изчислението. Изберете клетката в листа, където ще се покаже изчислената стойност. Кликваме върху бутона "Вмъкване на функция" .
2. Стартира съветникът за функции . Преминаваме към категорията "Финансови" , изберете името "PLT" от списъка и кликнете върху бутона "OK" .
3. След това се активира прозорецът с аргументи на горната функция.

   Поставяме курсора в полето "Залог" , след което кликнете върху клетката на листа с стойността на годишния лихвен процент. Както можете да видите, полето веднага показва координатите му. Но, както си спомняме, се нуждаем от месечна ставка, затова разделяме резултата на 12 ( 12 ).

   В полето "Kper" по същия начин влизаме в координатите на клетките на срока на кредита. В този случай не е нужно да разделяте нищо.

   В полето "Ps" трябва да посочите координатите на клетката, съдържащи размера на кредитното тяло. Ние го правим. Също така поставете знака "-" пред показаните координати. Фактът е, че функцията на PLT по подразбиране дава крайния резултат с отрицателен знак, правилно като се има предвид месечното плащане на заем като загуба. Но в името на яснотата в използването на таблицата с данни този брой трябва да бъде положителен. Затова поставяме знак минус пред един от аргументите на функцията. Както знаете, умножаването на "минус" с "минус" в крайна сметка дава "плюс" .

   В полетата "Bc" и "Type" ние изобщо не правим никакви данни. Кликнете върху бутона "OK" .

4. След това операторът изчислява и извежда резултата от общото месечно плащане - 30108.26 рубли към предварително определената клетка. Но проблемът е, че кредитополучателят може да плати максимум 29 000 рубли на месец, т.е. той трябва да намери банка, предлагаща условия с по-нисък лихвен процент, или да намали кредитната институция, или да увеличи срока на заема. Таблицата на замените ще ни помогне да изчислим различните варианти за действие.
5. Първо, използваме таблица за замествания с една променлива. Да видим как стойността на задължителното месечно плащане варира в зависимост от различните вариации на годишния лихвен процент, започвайки от 9,5% годишно и завършвайки с 12,5% годишно с увеличение от 0,5% . Всички други условия остават непроменени. Начертаваме табличен обхват, чиито имена на колони съответстват на различни вариации в лихвения процент. Същевременно линията "Месечни плащания" остава такава, каквато е. Първата клетка трябва да съдържа формулата, която изчислихме по-рано. За повече информация можете да добавите линиите "Обща сума на кредита" и "Обща лихва" . Колоната, в която се намира изчислението, се извършва без заглавие.
6. След това изчисляваме общата сума на кредита при настоящите условия. За да направите това, изберете първата клетка от реда "Обща сума на кредита" и умножете съдържанието на клетките "Месечно плащане" и "Кредитен период" . След това кликнете върху клавиша Enter .
7. За да изчислим общата сума на лихвата при настоящите условия, ние изваждаме сумата на кредитната институция от общата сума на кредита по подобен начин. За да покажете резултата на екрана, кликнете върху бутона Enter . По този начин получаваме сумата, която плащаме при погасяване на заема.
8. Сега е време да приложите инструмента за таблицата с данни . Изберете целия табличен масив, с изключение на имената на редовете. След това отворете раздела "Данни" . Кликваме върху бутона на лентата "Анализ на какво, ако" , която се намира в групата "Работа с данни" (в Excel 2016, групата "Прогноза" ). След това се отваря малко меню. В него изберете елемента "Таблица с данни ..." .
9. Отваря се малък прозорец, който се нарича "таблица с данни" . Както виждате, той има две полета. Тъй като работим с една променлива, имаме нужда един от тях. Тъй като променяме променливата по колоните, ще използваме полето "Заменете стойностите по колони в" . Задайте курсора там и след това кликнете върху клетката в изходния набор от данни, който съдържа текущата процентна стойност. След като координатите на клетката се показват в полето, кликнете върху бутона "OK" .
10. Инструментът изчислява и запълва целия диапазон на таблицата със стойности, съответстващи на различните опции за лихвения процент. Ако поставите курсора в който и да е елемент на дадена област на таблицата, можете да видите, че формулата за формула не показва обичайната формула за изчисляване на плащането, а специална формула за неразделен масив. Тоест, сега не можете да променяте стойностите в отделните клетки. Можете да изтриете резултатите от изчислението само заедно, а не отделно.

Освен това можете да видите, че стойността на месечното плащане на 12.5% ​​годишно, получена в резултат на прилагането на таблицата на пермутациите, съответства на стойността за същия размер на лихвата, която получихме, като приложим функцията на PLT . Това още веднъж доказва правилността на изчислението.

Анализирайки този масив, трябва да се каже, че, както виждаме, само за 9,5% годишно е приемливо за нас нивото на месечните плащания (по-малко от 29 000 рубли).

Урокът: Изчисляване на анюитетно плащане в Excel

Метод 2: използвайте инструмент с две променливи

Разбира се, много е трудно, ако изобщо, да се намерят банки, които дават заеми на 9,5% годишно. Така че нека видим какви възможности съществуват да инвестирате в приемливо ниво на месечно плащане за различни комбинации от други променливи: размер на кредита и срок на заема. Същевременно запазваме лихвата без промяна (12.5%). При решаването на тази задача инструментът "Таблица с данни" ще ни помогне да използваме две променливи.

1. Изчертаване на нов табличен масив. Сега имената на колоните ще посочат кредитния период (от 2 до 6 години в месеци с нарастване от една година), а в редовете - сумата на кредитния орган (от 850000 до 950000 рубли с 10 000 рубли). Задължителното условие е клетката, в която се намира изчислителната формула (в нашия случай PLT ), да се намира на границата на имената на редове и колони. Без това условие, инструментът няма да работи, когато използва две променливи.
2. След това избираме целия обхват на таблицата, включително имената на колоните, редовете и клетката с формулата PLT . Отворете раздела "Данни" . Както и в предишния момент, кликваме върху бутона "Анализирай какво, ако" в групата "Работа с данни" . В отворения списък изберете елемента "Таблица с данни ..." .
3. Появява се прозорецът "Таблица с данни" . В този случай се нуждаем от двете полета. В полето "Заместване на стойностите по колони в" ние посочваме координатите на клетката, съдържаща кредитния период в първичните данни. В полето "Заменете стойностите по редове в" посочваме адреса на клетката на първоначалните параметри, съдържащи стойността на тялото на кредита. След въвеждане на всички данни. Кликнете върху бутона "OK" .
4. Програмата изчислява и запълва обхвата на таблицата с данни. На пресечната точка на редове и колони вече можете да видите точно какво ще бъде месечното плащане със съответната стойност на годишната лихва и определения кредитен период.
5. Както можете да видите, има много ценности. За да разрешите други проблеми, може да има още повече. Ето защо, за да направите резултата от резултатите по-видим и веднага да определите кои стойности не отговарят на даденото условие, можете да използвате инструментите за визуализация. В нашия случай това ще бъде условно форматиране. Изберете всички стойности в диапазона на таблицата, с изключение на заглавките на редовете и колоните.
6. Преместване в раздела "Начало" и кликване върху иконата "Условно форматиране" . Той се намира в полето "Стилове" на лентата. В падащото меню изберете "Правила за избор на клетки" . В допълнителния списък кликнете върху елемента "По-малко ..." .
7. След това се отваря прозорецът за условно форматиране. В лявото поле посочете стойността, по-малка от коя клетка ще бъде разпределена. Както си спомняме, ние сме доволни от условието, при което месечното плащане по заема ще бъде по-малко от 29 000 рубли. Въвеждаме този номер. В дясното поле можете да изберете цвета за подчертаване, въпреки че можете да го оставите по подразбиране. След като въведете всички необходими настройки, кликнете върху бутона "OK" .
8. След това всички клетки, стойности, в които отговарят на горепосоченото условие, ще бъдат маркирани с цвят.

Анализирайки таблицата, можете да извлечете някои изводи. Както виждаме, при съществуващия срок на кредита (36 месеца), за да инвестираме в горепосочената сума на месечното плащане, трябва да вземем заем, който не надхвърля 860000,00 рубли, т.е. 40 000 по-малко от първоначално планираното.

Ако все още възнамеряваме да отпуснем заем от 900 000 рубли, кредитният период трябва да бъде 4 години (48 месеца). Само в този случай месечното плащане няма да надвиши установения лимит от 29 000 рубли.

По този начин, като се използва тази масива и се анализират плюсовете и минусите на всеки вариант, кредитополучателят може да вземе конкретно решение относно условията на заема, като избере най-подходящия вариант от всички възможни варианти.

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

Урокът: Условно форматиране в Excel

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

• ← Отваряне на таблици за ODS в Microsoft Excel
• SQL заявки в Microsoft Excel →