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

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

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

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

Агульныя звесткі

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

Базавыя звесткі аб табліцах дадзеных

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

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

1

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

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

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

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

3

1. Знаходзім на ўкладцы «Дадзеныя» пункт «Аналіз" што калі "» і клікаем па ім - у якое адкрылася меню трэба выбраць опцыю «Табліца дадзеных».

4

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

5

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

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

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

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

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

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

1. Запісаць варыянты цэны ў адзін радок са спасылкай на прыбытак - кожнай цане адна ячэйка.
2. Вылучыць дыяпазон вочак.

6

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

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

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

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

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

8

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

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

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

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

9

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

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

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

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

формула

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

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

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

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

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

10

1. Запоўнім табліцу, каб аналізаваць праект па ёй.

11

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

12

1. Вызначаем тэрмін, за які праект акупіцца. Для пачатковага перыяду выкарыстоўваем гэтую формулу: = СУММЕСЛИ (G7: G17; "0; Першы д.поток; 0). Праект аказваецца ў кропцы бясстратнасці за 4 гады.

13

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

14

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

15

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

16

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

17

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

18

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

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

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

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

Дысперсійны аналіз у Excel

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

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

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

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

19

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

20

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

21

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

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

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

22

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

23

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

24

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

25 двухфакторную дысперсійны аналіз у Excel

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

26

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

27

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

28

заключэнне

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

Паведамленне Аналіз адчувальнасці ў Excel (прыклад табліцы дадзеных) з'явіліся спачатку на Інфармацыйныя тэхналогіі.