Excel бағдарламасындағы сезімталдық талдауы (үлгі деректер парағы)

Қаржы саласындағы процестер әрқашан өзара байланысты – бір фактор екіншісіне тәуелді және онымен бірге өзгереді. Бұл өзгерістерді қадағалаңыз және Excel функциялары мен электрондық кесте әдістерін пайдаланып, болашақта не күтетінін түсініңіз.

Деректер кестесімен бірнеше нәтиже алу

Деректер парағының мүмкіндіктері - бұл не-егер талдаудың элементтері — көбінесе Microsoft Excel арқылы жасалады. Бұл сезімталдықты талдаудың екінші атауы.

қайта қарау

Деректер кестесі - бұл кейбір ұяшықтардағы мәндерді өзгерту арқылы мәселелерді шешу үшін қолданылатын ұяшықтар ауқымының түрі. Ол формуланың құрамдас бөліктеріндегі өзгерістерді қадағалап отыру және осы өзгерістерге сәйкес нәтижелерге жаңартуларды алу қажет болғанда жасалады. Мәліметтер кестелерін зерттеуде қалай пайдалану керектігін және олардың қандай түрлері бар екенін білейік.

Деректер кестелері туралы негізгі мәліметтер

Деректер кестелерінің екі түрі бар, олар құрамдас бөліктерінің саны бойынша ерекшеленеді. Сіз онымен тексеру қажет uXNUMXbuXNUMX мәндер санына назар аудара отырып, кестені құрастыруыңыз керек.

Статистиктер бір немесе бірнеше өрнекте олардың нәтижесін өзгерте алатын бір ғана айнымалы болған кезде бір айнымалы кестені пайдаланады. Мысалы, ол жиі 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. OFSET функциясы арқылы ақша ағынын есептейміз. Бастапқы кезеңде ағын инвестицияларға тең. Әрі қарай формуланы қолданамыз: =ЕСЕР(OFFSET(Number,1;)=2;SUM(Кіріс 1:шығатын 1); SOUM(Кіріс 1:шығатын 1)+$B$ 5)
   Формуладағы ұяшықтарды белгілеу кестенің орналасуына байланысты әртүрлі болуы мүмкін. Соңында бастапқы деректерден алынған мән қосылады – құтқару мәні.

3. Біз жобаның қайтарым мерзімін анықтаймыз. Бастапқы кезеңде біз мына формуланы қолданамыз: =ҚЫСҚАУ(G7: Ғ17;»<0″). Ұяшық диапазоны ақша ағыны бағаны болып табылады. Келесі кезеңдер үшін біз мына формуланы қолданамыз: =Бастапқы кезең+Егер(Бірінші e.ағын>0; Бірінші e.ағыны;0). Жоба 4 жылда залалсыздық нүктесіне жетеді.

4. Біз жоба өтелетін кезеңдердің сандары үшін баған жасаймыз.

5. Біз инвестицияның табыстылығын есептейміз. Белгілі бір уақыт кезеңіндегі пайда бастапқы инвестицияға бөлінген өрнекті жасау керек.

6. Төмендегі формула бойынша жеңілдік коэффициентін анықтаймыз: =1/(1+Диск.%) ^Нөмір.

7. Біз ағымдағы құнды көбейту арқылы есептейміз – ақша ағыны дисконт коэффициентіне көбейтіледі.

8. PI (рентабельділік индексі) есептейік. Уақыт бойынша ағымдағы құн жобаның басындағы инвестицияға бөлінеді.

9. IRR функциясы арқылы кірістің ішкі нормасын анықтайық: =IRR(Ақша ағынының ауқымы).

Деректер парағы арқылы инвестициялық сезімталдықты талдау

Инвестициялар саласындағы жобаларды талдау үшін деректер кестесіне қарағанда басқа әдістер қолайлы. Көптеген пайдаланушылар формуланы құрастыру кезінде шатастыруға тап болады. Бір фактордың басқалардағы өзгерістерге тәуелділігін анықтау үшін есептеулерді енгізу және деректерді оқу үшін дұрыс ұяшықтарды таңдау керек.

Есептеуді автоматтандыру арқылы Excel бағдарламасында факторлық және дисперсиялық талдау

Сезімталдық талдаудың тағы бір типологиясы факторлық талдау және дисперсияны талдау болып табылады. Бірінші түрі сандар арасындағы байланысты анықтайды, екіншісі бір айнымалының басқаларға тәуелділігін ашады.

Excel бағдарламасындағы ANOVA

Мұндай талдаудың мақсаты – шаманың өзгергіштігін үш құрамдас бөлікке бөлу:

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. Енгізу аралығы – деректерден тұратын ұяшықтар (тақырыпсыз). Нәтижелерді жаңа парақта көрсетеміз және «OK» түймесін басыңыз.

F мәні F-критикалық мәннен үлкен, бұл еденнің дыбысқа реакция жылдамдығына әсер ететінін білдіреді.

қорытынды

Бұл мақалада Excel электрондық кестесіндегі сезімталдық талдауы егжей-тегжейлі талқыланды, осылайша әрбір пайдаланушы оны қолдану әдістерін түсіне алады.