Аналіз адчувальнасці ў Excel (прыклад табліцы дадзеных)

Працэсы ў сферы фінансаў заўсёды ўзаемазвязаны - адзін фактар ​​залежыць ад іншага і змяняецца разам з ім. Адсочвайце гэтыя змены і разумейце, чаго чакаць у будучыні, магчыма, выкарыстоўваючы функцыі Excel і метады электронных табліц.

Атрыманне некалькіх вынікаў з дапамогай табліцы даных

Магчымасці табліц дадзеных з'яўляюцца элементамі аналізу "што, калі" - часта выконваецца праз Microsoft Excel. Гэта другая назва аналізу адчувальнасці.

агляд

Табліца даных - гэта тып дыяпазону ячэек, якія можна выкарыстоўваць для вырашэння задач шляхам змены значэнняў у некаторых вочках. Ён ствараецца, калі неабходна адсочваць змены кампанентаў формулы і атрымліваць абнаўленні вынікаў у адпаведнасці з гэтымі зменамі. Давайце разбярэмся, як выкарыстоўваць табліцы дадзеных у даследаваннях і якія яны бываюць.

Асновы табліц даных

Табліцы дадзеных бываюць двух тыпаў, яны адрозніваюцца колькасцю кампанентаў. Табе трэба скласці табліцу з акцэнтам на колькасць значэнняў uXNUMXbuXNUMXb, якія трэба з ёй праверыць.

Статысты выкарыстоўваюць табліцу адной зменнай, калі ў адным або некалькіх выразах ёсць толькі адна зменная, якая можа змяніць іх вынік. Напрыклад, часта выкарыстоўваецца ў спалучэнні з функцыяй PMT. Формула прызначана для разліку сумы рэгулярнага плацяжу і ўлічвае працэнтную стаўку, указаную ў дагаворы. У такіх разліках зменныя запісваюцца ў адзін слупок, а вынікі вылічэнняў - у другі. Прыклад таблічкі дадзеных з 1 зменнай:

Далей разгледзім таблічкі з 2 зменнымі. Яны выкарыстоўваюцца ў тых выпадках, калі на змяненне якога-небудзь паказчыка ўплываюць два фактары. Дзве зменныя могуць апынуцца ў іншай табліцы, звязанай з крэдытам, якую можна выкарыстоўваць для вызначэння аптымальнага перыяду пагашэння і сумы штомесячнага плацяжу. У гэтым разліку вам таксама трэба выкарыстоўваць функцыю PMT. Прыклад табліцы з 2 зменнымі:

Стварэнне табліцы даных з адной зменнай

Разгледзім метад аналізу на прыкладзе невялікай кнігарні, дзе ў наяўнасці ўсяго 100 кніг. Некаторыя з іх можна прадаць даражэй ($50), астатнія абыдуцца пакупнікам танней ($20). Падлічваецца агульны даход ад продажу ўсіх тавараў – уладальнік вырашыў, што 60% кніг ён прадасць па высокай цане. Трэба высветліць, наколькі павялічыцца выручка, калі павялічыць цану на большы аб'ём тавару - на 70% і гэтак далей.

Звяртаць увагу! Агульная выручка павінна быць разлічана па формуле, інакш скласці табліцу дадзеных будзе немагчыма.

1. Вылучыце вольную вочка далей ад краю ліста і запішыце ў яе формулу: = Ячэйка агульнага даходу. Напрыклад, калі ў ячэйцы C14 прапісаны даход (указваецца выпадковае абазначэнне), трэба напісаць наступнае: =S14.
2. Працэнт ад аб'ёму тавару запісваем у слупок злева ад гэтай ячэйкі - не ніжэй, гэта вельмі важна.
3. Выбіраем дыяпазон вочак, дзе знаходзіцца слупок працэнтаў і спасылка на агульны даход.

4. Знаходзім на ўкладцы «Дадзеныя» пункт «Што калі аналіз» і націскаем на яго - у якое адкрылася меню выбіраем пункт «Табліца дадзеных».

5. Адкрыецца невялікае акно, дзе ў слупку «Падставіць значэння па радках у …» трэба пазначыць вочка з адсоткам кніг, першапачаткова прададзеных па высокай цане. Гэты крок робіцца для таго, каб пералічыць агульную выручку з улікам нарастаючага працэнта.

Пасля націску кнопкі «ОК» у акне ўводу даных для складання табліцы ў радках з’явяцца вынікі разлікаў.

Даданне формулы ў адну зменную табліцу даных

З табліцы, якая дапамагла разлічыць дзеянне толькі з адной зменнай, вы можаце зрабіць складаны інструмент аналізу, дадаўшы дадатковую формулу. Яе трэба ўводзіць побач з ужо існуючай формулай – напрыклад, калі табліца радковая, мы ўводзім выраз у вочка справа ад існуючай. Калі ўстаноўлена арыентацыя слупка, мы пішам новую формулу пад старой. Далей дзейнічаем па алгарытме:

1. Зноў абярыце дыяпазон вочак, але цяпер ён павінен уключаць новую формулу.
2. Адкрыйце меню аналізу «што, калі» і абярыце «Табліцу».
3. Мы дадаем новую формулу ў адпаведнае поле ў радках або слупках, у залежнасці ад арыентацыі пласціны.

Стварыце табліцу даных з дзвюма зменнымі

Пачатак такой табліцы крыху іншы - над працэнтнымі значэннямі трэба паставіць спасылку на агульную выручку. Далей выконваем наступныя дзеянні:

1. Напішыце цэнавыя апцыёны ў адзін радок са спасылкай на даход - па адной ячэйцы для кожнай цаны.
2. Выберыце дыяпазон ячэек.

3. Адкрыйце акно табліцы дадзеных, як і пры складанні табліцы з адной зменнай - праз закладку «Дадзеныя» на панэлі інструментаў.
4. Падстаўце ў слупок «Падставіць значэння па слупках у …» вочка з пачатковай высокай цаной.
5. Дадайце ў слупок «Падставіць значэння па радках у …» вочка з пачатковым працэнтам продажаў дарагіх кніг і націсніце «ОК».

У выніку ўся табліца запаўняецца сумамі магчымага даходу з рознымі ўмовамі рэалізацыі тавару.

Паскорыць вылічэнні для лістоў, якія змяшчаюць табліцы даных

Калі вам патрэбны хуткія разлікі ў табліцы даных, якія не выклікаюць пераразліку ўсёй працоўнай кнігі, ёсць некалькі рэчаў, якія вы можаце зрабіць, каб паскорыць працэс.

1. Адкрыйце акно параметраў, у меню справа абярыце пункт «Формулы».
2. У раздзеле «Вылічэнні ў кніжцы» выберыце пункт «Аўтаматычна, за выключэннем табліц дадзеных».

3. Пералічым вынікі ў табліцу ўручную. Для гэтага вылучыце формулы і націсніце клавішу F.

Іншыя інструменты для правядзення аналізу адчувальнасці

У праграме ёсць і іншыя інструменты, якія дапамогуць вам правесці аналіз адчувальнасці. Яны аўтаматызуюць некаторыя дзеянні, якія ў іншым выпадку прыйшлося б рабіць уручную.

1. Функцыя «Выбар параметру» падыдзе ў тым выпадку, калі вядомы жаданы вынік, а для атрымання такога выніку неабходна ведаць уваходнае значэнне зменнай.
2. «Пошук рашэння» - дадатак для рашэння задач. Неабходна ўсталяваць ліміты і ўказаць на іх, пасля чаго сістэма знойдзе адказ. Рашэнне вызначаецца змяненнем значэнняў.
3. Аналіз адчувальнасці можна зрабіць з дапамогай дыспетчара сцэнарыяў. Гэты інструмент знаходзіцца ў меню аналізу "што, калі" на ўкладцы "Дадзеныя". Ён падстаўляе значэння ў некалькіх ячэйках - іх колькасць можа дасягаць 32. Дыспетчар параўноўвае гэтыя значэнні, каб карыстачу не прыйшлося мяняць іх уручную. Прыклад выкарыстання дыспетчара сцэнарыяў:

Аналіз адчувальнасці інвестыцыйнага праекта ў Excel

Што-калі аналіз асабліва карысны ў сітуацыях, калі патрабуецца прагназаванне, напрыклад, пры інвестыцыях. Аналітыкі выкарыстоўваюць гэты метад, каб даведацца, як зменіцца кошт акцый кампаніі ў выніку змены некаторых фактараў.

Метад аналізу адчувальнасці інвестыцый

Пры аналізе «што, калі» выкарыстоўвайце пералік - ручной або аўтаматычны. Дыяпазон значэнняў вядомы, і яны па чарзе падстаўляюцца ў формулу. У выніку атрымліваецца набор значэнняў. Выберыце з іх адпаведную лічбу. Разгледзім чатыры паказчыка, па якіх праводзіцца аналіз адчувальнасці ў сферы фінансаў:

1. Чысты прыведзены кошт – разлічваецца шляхам аднімання сумы інвестыцый з сумы даходу.
2. Унутраная норма даходнасці / прыбытку - паказвае, колькі прыбытку неабходна атрымаць ад інвестыцый у год.
3. Каэфіцыент акупнасці - гэта стаўленне ўсяго прыбытку да першапачатковых інвестыцый.
4. Індэкс дыскантаванай прыбытку - паказвае на эфектыўнасць інвестыцый.

Формула

Адчувальнасць убудавання можна разлічыць па гэтай формуле: Змена выходнага параметра ў % / Змена ўваходнага параметра ў %.

Выхаднымі і ўваходнымі параметрамі могуць быць апісаныя раней значэння.

1. Трэба ведаць вынік пры стандартных умовах.
2. Замяняем адну з зменных і сочым за зменамі выніку.
3. Разлічваем працэнтнае змяненне абодвух параметраў адносна ўсталяваных умоў.
4. Ўстаўляем атрыманыя працэнты ў формулу і вызначаем адчувальнасць.

Прыклад аналізу адчувальнасці інвестыцыйнага праекта ў Excel

Для лепшага разумення метадалогіі аналізу неабходны прыклад. Давайце прааналізуем праект з наступнымі вядомымі дадзенымі:

1. Запоўніце табліцу для аналізу праекта па ёй.

2. Мы разлічваем грашовы паток з дапамогай функцыі OFFSET. На пачатковым этапе паток роўны інвестыцыям. Далей прымяняем формулу: =КАЛІ(ЗРУШЧЭННЕ(Лік,1;)=2;СУМ(Уваход 1:Выхад 1); СУММ(Уваход 1:Выхад 1)+$B$ 5)
   Абазначэнне вочак у формуле можа быць розным, у залежнасці ад выгляду табліцы. У канцы дадаецца значэнне з зыходных даных – аднаўленчы кошт.

3. Вызначаем тэрмін, за які акупіцца праект. Для пачатковага перыяду мы выкарыстоўваем наступную формулу: =СУММА(G7: Г17;»<0″). Дыяпазон вочак - гэта слупок грашовых патокаў. Для наступных перыядаў мы прымяняем наступную формулу: =Пачатковы перыяд+КАЛІ(Першы e.stream>0; Першы e.stream;0). Праз 4 гады праект выходзіць на бясстратнасць.

4. Ствараем слупок для лічбаў тых перыядаў, калі праект акупляецца.

5. Разлічваем рэнтабельнасць інвестыцый. Неабходна скласці выраз, дзе прыбытак за пэўны перыяд часу дзяліць на першапачатковыя ўкладанні.

6. Мы вызначаем каэфіцыент дыскантавання па гэтай формуле: =1/(1+Дыск.%) ^ Нумар.

7. Мы разлічваем цяперашні кошт з дапамогай множання - грашовы паток памнажаецца на каэфіцыент дыскантавання.

8. Разлічым PI (індэкс рэнтабельнасці). Цяперашняя кошт у часе дзеліцца на інвестыцыі ў пачатку праекта.

9. Давайце вызначым унутраную норму прыбытку з дапамогай функцыі IRR: =IRR(Дыяпазон грашовага патоку).

Аналіз адчувальнасці інвестыцый з выкарыстаннем табліцы даных

Для аналізу праектаў у сферы інвестыцый лепш падыходзяць іншыя метады, чым табліца дадзеных. Многія карыстальнікі пры складанні формулы адчуваюць блытаніну. Каб высветліць залежнасць аднаго фактару ад змянення іншых, трэба выбраць правільныя вочкі для ўводу разлікаў і для счытвання даных.

Фактарны і дысперсійны аналіз у Excel з аўтаматызацыяй вылічэнняў

Яшчэ адна тыпалогія аналізу адчувальнасці - фактарны аналіз і дысперсійны аналіз. Першы тып вызначае сувязь паміж лікамі, другі выяўляе залежнасць адных зменных ад іншых.

ANOVA ў Excel

Мэта такога аналізу - падзяліць зменлівасць значэння на тры складнікі:

1. Зменлівасць як вынік уплыву іншых каштоўнасцей.
2. Змены з-за суадносін значэнняў, якія ўплываюць на яго.
3. Выпадковыя змены.

Выканаем дысперсійны аналіз праз надбудову Excel “Аналіз дадзеных”. Калі ён не ўключаны, яго можна ўключыць у наладах.

Пачатковая табліца павінна адпавядаць двум правілам: для кожнага значэння ёсць адзін слупок, і дадзеныя ў ім размешчаны ў парадку ўзрастання або змяншэння. Неабходна праверыць уплыў узроўню адукацыі на паводзіны ў канфлікце.

1. Знайдзіце інструмент «Аналіз дадзеных» на ўкладцы «Дадзеныя» і адкрыйце яго акно. У спісе трэба выбраць аднабаковы дысперсійны аналіз.

2. Запоўніце радкі дыялогавага акна. Інтэрвал уводу - гэта ўсе ячэйкі, за выключэннем загалоўкаў і лічбаў. Група па слупках. Выводзім вынікі на новым аркушы.

Паколькі значэнне ў жоўтай клетцы больш адзінкі, можна лічыць здагадку няслушным - паміж адукацыяй і паводзінамі ў канфлікце няма ніякай сувязі.

Фактарны аналіз у Excel: прыклад

Давайце прааналізуем ўзаемасувязь дадзеных у сферы продажаў - неабходна вызначыць папулярныя і непапулярныя тавары. Першапачатковая інфармацыя:

1. Нам трэба высветліць, на якія тавары больш за ўсё павялічыўся попыт за другі месяц. Складаем новую табліцу, каб вызначыць рост і падзенне попыту. Рост разлічваецца па формуле: =КАЛІ((Попыт 2-Патрабу 1)>0; Попыт 2- Попыт 1;0). Формула памяншэння: =КАЛІ(Рост=0; Попыт 1- Попыт 2;0).

2. Разлічыце рост попыту на тавары ў працэнтах: =КАЛІ(Рост/Вынік 2 =0; Зніжэнне/Вынік 2; Рост/Вынік 2).

3. Для нагляднасці зробім дыяграму - вылучым дыяпазон вочак і створым гістаграму праз ўкладку «Устаўка». У наладах трэба прыбраць заліванне, гэта можна зрабіць праз інструмент «Фарматаваць шэраг дадзеных».

Двухбаковы дысперсійны аналіз у Excel

Дысперсійны аналіз праводзіцца з некалькімі зменнымі. Разгледзім гэта на прыкладзе: трэба высветліць, наколькі хутка выяўляецца рэакцыя на гук рознай гучнасці ў мужчын і жанчын.

1. Адкрываем «Аналіз дадзеных», у спісе трэба знайсці двухбаковы дысперсійны аналіз без паўтораў.
2. Інтэрвал уводу – ячэйкі, якія змяшчаюць даныя (без загалоўка). Выводзім вынікі на новы ліст і націскаем «ОК».

Значэнне F больш, чым F-крытычнае, гэта азначае, што падлога ўплывае на хуткасць рэакцыі на гук.

заключэнне

У дадзеным артыкуле аналіз адчувальнасці ў табліцы Excel быў разгледжаны падрабязна, так што кожны карыстальнік зможа разабрацца ў спосабах яго прымянення.