Когато имате сложна формула, зависима от множество променливи, и искате да разберете как промяната на тези входни данни влияе ефективно на резултатите, таблицата с данни за анализ „Какво ако“ в Excel е мощен инструмент. Позволява ви да видите всички възможни резултати с бърз поглед. Ето ръководство стъпка по стъпка как да създадете таблица с данни в Excel. Освен това ще обсъдим някои ключови точки за ефективно използване на таблици с данни и ще демонстрираме други операции като изтриване, редактиране и преизчисляване на таблици с данни.

Какво е таблица с данни в Excel?

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

• Таблица с данни, ориентирана по колони
• Редово ориентирана таблица с данни
• Множество формули за таблица с данни с една променлива

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

Ключови точки с помощта на таблиците с данни

Други операции за използване на таблици с данни

• Изтриване на таблица с данни
• Редактиране на резултата от таблицата с данни
• Преизчислете таблицата с данни ръчно

Какво е таблица с данни в Excel?

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

В Excel има два типа таблици с данни:

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

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

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

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

невероятно! Използване на ефективни раздели в Excel като Chrome, Edge, Firefox и Safari!
Спестете 50% от времето си и намалете хилядите кликвания на мишката за вас всеки ден!

---

Таблица с данни, ориентирана по колони

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

Да предположим, че обмисляте заем от $50,000 3, който планирате да изплатите за период от 36 години (еквивалентен на XNUMX месеца). За да прецените какво месечно плащане би било достъпно въз основа на вашата заплата, интересувате се да проучите как различните лихвени проценти биха повлияли на сумата, която трябва да плащате всеки месец.

Стъпка 1: Настройте основната формула

За да изчисля плащането, тук ще задам лихвата като 5%. В клетка B4 въведете формулата PMT, тя изчислява месечното плащане въз основа на лихвения процент, броя на периодите и размера на заема. Вижте екранна снимка:

= -PMT($B$1/12, $B$2*12, $B$3)

Стъпка 2: Избройте лихвените проценти в колона

В колона избройте различните лихвени проценти, които искате да тествате. Например, избройте стойности от 4% до 11% на стъпки от 1% в колона и оставете поне една празна колона вдясно за резултатите. Вижте екранна снимка:

Стъпка 3: Създайте таблицата с данни

1. В клетка E2 въведете тази формула: = B4.
   
   Забележка: B4 е клетката, в която се намира вашата основна формула, това е формулата, чиито резултати искате да видите промяна, докато променяте лихвения процент.
   
2. Изберете диапазона, който включва вашата формула, списък с лихвени проценти и съседни клетки за резултатите (напр. изберете D2 до E10). Вижте екранна снимка:
   
3. Отидете до лентата, щракнете върху Дата , след което щракнете върху Какво-ако анализ > Таблица с данни, вижте екранна снимка:
   
4. в Таблица с данни диалогов прозорец, щракнете върху Клетка за въвеждане на колона (тъй като използваме колона за входни стойности) и изберете клетката с променлива, посочена във вашата формула. В този пример избираме B1, който съдържа лихвения процент. Най-накрая щракнете OK бутон, вижте екранната снимка:
   
5. Excel автоматично ще попълни празните клетки до всяка от вашите променливи стойности (различни лихвени проценти) със съответните резултати. Вижте екранна снимка:
   
6. Приложете желания числов формат към резултатите (Валута) според вашите нужди. Сега таблицата с данни, ориентирана към колони, е създадена успешно и можете бързо да прегледате резултатите, за да прецените кои суми на месечно плащане биха били достъпни за вас, когато лихвеният процент се промени. Вижте екранна снимка:
   

Редово ориентирана таблица с данни

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

Стъпка 1: Избройте лихвените проценти в ред

Поставете стойностите на променливите (лихвени проценти) в ред, като се уверите, че има поне една празна колона отляво за формулата и един празен ред отдолу за резултатите, вижте екранната снимка:

Стъпка 2: Създайте таблицата с данни

1. В клетка A9 въведете тази формула: = B4.
   
2. Изберете диапазона, който включва вашата формула, списък с лихвени проценти и съседни клетки за резултатите (напр. изберете A8 до I9). След това щракнете Дата > Какво-ако анализ > Таблица с данни.
3. в Таблица с данни диалогов прозорец, щракнете върху Клетка за въвеждане на ред (тъй като използваме ред за входни стойности) и изберете клетката с променливи, посочена във вашата формула. В този пример избираме B1, който съдържа лихвения процент. Най-накрая щракнете OK бутон, вижте екранната снимка:
   
4. Приложете желания числов формат към резултатите (Валута) според вашите нужди. Сега таблицата с данни, ориентирана към редове, е създадена, вижте екранната снимка:
   

Множество формули за таблица с данни с една променлива

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

Стъпка 1: Добавете нова формула за изчисляване на общата лихва

В клетка B5 въведете следната формула, за да изчислите общата лихва:

=B4*B2*12-B3

Стъпка 2: Подредете изходните данни на таблицата с данни

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

Стъпка 3: Създайте таблицата с данни:

1. В клетка E2 въведете тази формула: = B4 за създаване на препратка към изчислението на погасяването в оригиналните данни.
2. В клетка F2 въведете тази формула: = B5 за създаване на препратка към общия интерес към оригиналните данни.
   
3. Изберете диапазона, който включва вашите формули, списък с лихвени проценти и съседни клетки за резултата (напр. изберете D2 до F10). След това щракнете Дата > Какво-ако анализ > Таблица с данни.
4. в Таблица с данни диалогов прозорец, щракнете върху Клетка за въвеждане на колона (тъй като използваме колона за входни стойности) и изберете клетката с променлива, посочена във вашата формула. В този пример избираме B1, който съдържа лихвения процент. Най-накрая щракнете OK бутон, вижте екранната снимка:
   
5. Приложете желания числов формат към резултатите (Валута) според вашите нужди. И можете да видите резултатите за всяка формула въз основа на вариращите стойности на променливите.
   

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

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

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

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

В набора от данни по-долу имаме лихвения процент, срока на заема и сумата на заема и сме изчислили месечното плащане по тази формула: =-PMT($B$1/12, $B$2*12, $B$3) както добре. Тук ще се съсредоточим върху две ключови променливи от нашите данни: лихвен процент и сума на заема, за да наблюдаваме как промените в тези два фактора едновременно влияят върху сумите за погасяване.

Стъпка 1: Настройте двете променящи се променливи

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

Стъпка 2: Създайте таблицата с данни

1. Поставете формулата си в пресечната точка на реда и колоната, където сте посочили стойностите на променливите. В този случай ще въведа следната формула: = B4 в клетка E2. Вижте екранна снимка:
   
2. Изберете диапазона, който включва сумите на вашия заем, лихвените проценти, клетката с формула и клетките, където ще се показват резултатите.
   
3. След това кликнете върху Дата > Какво-ако анализ > Таблица с данни. В диалоговия прозорец Таблица с данни:
   • в Клетка за въвеждане на ред изберете препратката към клетката за въвеждане на стойностите на променливата в реда (в този пример B3 съдържа сумата на заема).
   • в Входна колона cell поле, изберете препратката към клетката за въвеждане на стойностите на променливите в колоната (B1 съдържа лихвения процент).
   • И след това щракнете OK Бътън.
     
4. Сега Excel ще попълни таблицата с данни с резултатите за всяка комбинация от сума на заема и лихвен процент. Той предоставя директен поглед върху това как различните комбинации от суми на кредита и лихвени проценти влияят върху месечното плащане, което го прави ценен инструмент за финансово планиране и анализ.
   
5. И накрая, трябва да приложите желания числов формат към резултатите (валута) според вашите нужди.
   

Ключови точки с помощта на таблиците с данни

• Новосъздадената таблица с данни трябва да бъде в същия работен лист като оригиналните данни.
• Резултатът от таблицата с данни зависи от клетката с формула в изходния набор от данни и всички промени в тази клетка с формула автоматично ще актуализират изхода.
• След като стойностите са изчислени с помощта на таблицата с данни, те не могат да бъдат отменени Ctrl + Z. Можете обаче ръчно да изберете всички стойности и да ги изтриете.
• Таблицата с данни генерира формули за масиви, така че отделните клетки в таблицата не могат да бъдат променяни или изтривани.

Други операции за използване на таблици с данни

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

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

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

Изберете всички клетки на таблицата с данни или само клетките с резултатите и след това натиснете Изтрий клавиш на клавиатурата.

Редактиране на резултата от таблицата с данни

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

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

Преизчислете таблицата с данни ръчно

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

За да избегнете автоматичното извършване на изчисления на Excel за всички таблици с данни всеки път, когато се направи промяна в работната книга, можете да превключите режима на изчисление от Автоматичен на Ръчен. Моля, направете следното:

Отидете в формули , след което щракнете върху Опции за изчисление > Автоматично с изключение на таблици с данни, вижте екранна снимка:

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

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

Като следвате стъпките, посочени в това ръководство, и като имате предвид ключовите точки, можете ефективно да използвате таблици с данни за вашите нужди за анализ на данни. Ако се интересувате да проучите повече съвети и трикове за Excel, нашият уебсайт предлага хиляди уроци, моля, щракнете тук за достъп до тях. Благодарим ви, че прочетохте и очакваме с нетърпение да ви предоставим още полезна информация в бъдеще!