Excel-də Həssaslıq Analizi (Nümunə Məlumat Vərəqi)

Maliyyə sahəsində proseslər həmişə bir-biri ilə bağlıdır - bir amil digərindən asılıdır və onunla birlikdə dəyişir. Bu dəyişiklikləri izləyin və gələcəkdə nə gözlədiyinizi anlayın, ola bilsin ki, Excel funksiyaları və cədvəl metodlarından istifadə edin.

Data cədvəli ilə çoxlu nəticə əldə etmək

Datasheet imkanları, çox vaxt Microsoft Excel vasitəsilə həyata keçirilən nə-if analizinin elementləridir. Bu, həssaslıq analizinin ikinci adıdır.

Baxış

Məlumat cədvəli bəzi hüceyrələrdəki dəyərləri dəyişdirərək problemləri həll etmək üçün istifadə edilə bilən hüceyrələr çeşididir. Düsturun komponentlərindəki dəyişiklikləri izləmək və bu dəyişikliklərə uyğun olaraq nəticələrə dair yeniləmələri almaq lazım olduqda yaradılır. Tədqiqatda verilənlər cədvəlindən necə istifadə olunacağını və onların hansı növləri olduğunu öyrənək.

Məlumat cədvəlləri haqqında əsaslar

Məlumat cədvəllərinin iki növü var, onlar komponentlərin sayına görə fərqlənirlər. Yoxlamalı olduğunuz uXNUMXbuXNUMXb dəyərlərinin sayına diqqət yetirməklə bir cədvəl tərtib etməlisiniz.

Bir və ya bir neçə ifadədə onların nəticəsini dəyişə bilən yalnız bir dəyişən olduqda statistiklər tək dəyişən cədvəlindən istifadə edirlər. Məsələn, tez-tez PMT funksiyası ilə birlikdə istifadə olunur. Düstur müntəzəm ödənişin məbləğini hesablamaq üçün nəzərdə tutulmuşdur və müqavilədə göstərilən faiz dərəcəsini nəzərə alır. Belə hesablamalarda dəyişənlər bir sütunda, hesablamaların nəticələri isə digər sütunda yazılır. 1 dəyişənli məlumat lövhəsinin nümunəsi:

Sonra, 2 dəyişənli lövhələri nəzərdən keçirin. Onlar hər hansı göstəricinin dəyişməsinə iki amilin təsir etdiyi hallarda istifadə olunur. İki dəyişən kreditlə əlaqəli başqa bir cədvəldə başa düşə bilər ki, bu da optimal ödəmə müddətini və aylıq ödənişin məbləğini müəyyən etmək üçün istifadə edilə bilər. Bu hesablamada siz PMT funksiyasından da istifadə etməlisiniz. 2 dəyişənli cədvəl nümunəsi:

Bir dəyişən ilə verilənlər cədvəlinin yaradılması

Stokda cəmi 100 kitab olan kiçik bir kitab mağazası nümunəsindən istifadə edərək təhlil metodunu nəzərdən keçirək. Onların bəziləri daha baha satıla bilər (50 dollar), qalanları alıcılara daha ucuz başa gələcək (20 dollar). Bütün malların satışından əldə olunan ümumi gəlir hesablanır - sahibi kitabların 60%-ni baha qiymətə satmağa qərar verdi. Daha böyük həcmdə malların qiymətini artırsanız, gəlirin necə artacağını öyrənməlisiniz - 70% və s.

Diqqət yetirin! Ümumi gəlir düsturla hesablanmalıdır, əks halda məlumat cədvəlini tərtib etmək mümkün olmayacaq.

1. Vərəqin kənarından boş bir xana seçin və içinə düstur yazın: =Ümumi gəlirin xanası. Məsələn, gəlir C14 xanasına yazılıbsa (təsadüfi təyinat göstərilib), bunu yazmalısınız: =S14.
2. Bu xananın solundakı sütunda malların həcminin faizini yazırıq - onun altında deyil, bu çox vacibdir.
3. Faiz sütununun və ümumi gəlirə keçidin yerləşdiyi xanalar aralığını seçirik.

4. "Məlumatlar" sekmesinde "Analiz olsa nə olacaq" maddəsini tapırıq və üzərinə klik edirik - açılan menyuda "Məlumat cədvəli" seçimini seçin.

5. Kiçik bir pəncərə açılacaq, burada "Dəyərləri sətirlərlə əvəz et ..." sütununda əvvəlcə yüksək qiymətə satılan kitabların faizi ilə bir xana göstərməlisiniz. Bu addım artan faizi nəzərə alaraq ümumi gəliri yenidən hesablamaq üçün edilir.

Cədvəlin tərtib edilməsi üçün verilənlərin daxil edildiyi pəncərədə “OK” düyməsini sıxdıqdan sonra sətirlərdə hesablamaların nəticələri görünəcək.

Tək Dəyişən Məlumat Cədvəlinə Düsturun əlavə edilməsi

Yalnız bir dəyişən ilə hərəkəti hesablamağa kömək edən cədvəldən əlavə düstur əlavə etməklə mürəkkəb analiz aləti yarada bilərsiniz. O, artıq mövcud formulun yanında daxil edilməlidir – məsələn, cədvəl sətir yönümlüdürsə, ifadəni mövcud olanın sağındakı xanaya daxil edirik. Sütun istiqaməti təyin edildikdə, köhnənin altına yeni düstur yazırıq. Sonra, alqoritmə əməl edin:

1. Yenidən xanaların diapazonunu seçin, lakin indi o, yeni formula daxil etməlidir.
2. "Nə olarsa" təhlil menyusunu açın və "Məlumat vərəqi" seçin.
3. Lövhənin istiqamətindən asılı olaraq, sətirlərdə və ya sütunlarda müvafiq sahəyə yeni bir düstur əlavə edirik.

İki dəyişənli verilənlər cədvəli yaradın

Belə bir cədvəlin başlanğıcı bir qədər fərqlidir - faiz dəyərlərindən yuxarı ümumi gəlirə keçid qoymalısınız. Sonra bu addımları yerinə yetiririk:

1. Gəlir linki ilə qiymət seçimlərini bir sətirdə yazın - hər qiymət üçün bir xana.
2. Bir sıra hüceyrə seçin.

3. Alətlər panelindəki "Məlumatlar" sekmesi vasitəsilə bir dəyişənli cədvəl tərtib edərkən olduğu kimi verilənlər cədvəli pəncərəsini açın.
4. "Dəyərləri sütunlarla əvəz et..." sütununda ilkin yüksək qiymətə malik olan xananı əvəz edin.
5. "Dəyərləri sətirlərlə əvəz et..." sütununa bahalı kitabların ilkin satış faizi olan bir xana əlavə edin və "OK" düyməsini basın.

Nəticədə, bütün cədvəl malların satışı üçün müxtəlif şərtlərlə mümkün gəlir məbləğləri ilə doldurulur.

Məlumat cədvəlləri olan iş vərəqləri üçün hesablamaları sürətləndirin

Əgər məlumat cədvəlində bütün iş kitabının yenidən hesablanmasına səbəb olmayan sürətli hesablamalara ehtiyacınız varsa, prosesi sürətləndirmək üçün edə biləcəyiniz bir neçə şey var.

1. Seçimlər pəncərəsini açın, sağdakı menyuda "Formullar" maddəsini seçin.
2. "İş kitabındakı hesablamalar" bölməsində "Məlumat cədvəlləri istisna olmaqla, avtomatik" maddəsini seçin.

3. Cədvəldəki nəticələri əl ilə yenidən hesablayaq. Bunu etmək üçün düsturları seçin və F düyməsini basın.

Həssaslıq Təhlilini yerinə yetirmək üçün digər alətlər

Proqramda həssaslıq təhlili aparmağa kömək edəcək başqa vasitələr də var. Əks halda əl ilə edilməli olan bəzi hərəkətləri avtomatlaşdırırlar.

1. İstədiyiniz nəticə məlumdursa, “Parametr seçimi” funksiyası uyğundur və belə bir nəticə əldə etmək üçün dəyişənin giriş dəyərini bilməlisiniz..
2. “Həll üçün axtarış” problemlərin həlli üçün əlavədir. Limitlər təyin etmək və onlara işarə etmək lazımdır, bundan sonra sistem cavabı tapacaq. Həll dəyərlərin dəyişdirilməsi ilə müəyyən edilir.
3. Həssaslıq təhlili Ssenari Menecerindən istifadə etməklə edilə bilər. Bu alət Məlumatlar tabının altındakı "Nə olarsa" təhlili menyusunda tapılır. O, bir neçə xanadakı dəyərləri əvəz edir – sayı 32-yə çata bilər. Dispetçer bu dəyərləri müqayisə edir ki, istifadəçi onları əl ilə dəyişməsin. Skript menecerindən istifadə nümunəsi:

Excel-də investisiya layihəsinin həssaslıq təhlili

Nə olarsa təhlili investisiya kimi proqnozlaşdırmanın tələb olunduğu hallarda xüsusilə faydalıdır. Analitiklər bəzi amillərin dəyişməsi nəticəsində şirkətin səhmlərinin dəyərinin necə dəyişəcəyini öyrənmək üçün bu üsuldan istifadə edirlər.

İnvestisiya həssaslığının təhlili metodu

“Nə olarsa” təhlili zamanı siyahıdan istifadə edin – əllə və ya avtomatik. Dəyərlərin diapazonu məlumdur və onlar bir-bir düsturla əvəz olunur. Nəticə dəyərlər toplusudur. Onlardan uyğun nömrəni seçin. Maliyyə sahəsində həssaslıq təhlilinin aparıldığı dörd göstəricini nəzərdən keçirək:

1. Xalis İndiki Dəyər – Gəlir məbləğindən investisiya məbləğini çıxmaqla hesablanır.
2. Daxili gəlir/mənfəət nisbəti – bir il ərzində investisiyadan nə qədər mənfəət əldə etmək lazım olduğunu göstərir.
3. Geri ödəmə nisbəti bütün mənfəətin ilkin investisiyaya nisbətidir.
4. Endirimli mənfəət indeksi – investisiyanın effektivliyini göstərir.

Formula

Yerləşdirmə həssaslığı bu düsturla hesablana bilər: Çıxış parametrinin % ilə dəyişməsi / Giriş parametrinin % ilə dəyişməsi.

Çıxış və giriş parametrləri əvvəllər təsvir edilmiş dəyərlər ola bilər.

1. Standart şərtlərdə nəticəni bilmək lazımdır.
2. Dəyişənlərdən birini əvəz edirik və nəticədəki dəyişiklikləri izləyirik.
3. Müəyyən edilmiş şərtlərə nisbətən hər iki parametrin faiz dəyişməsini hesablayırıq.
4. Alınan faizləri düstura daxil edirik və həssaslığı təyin edirik.

Excel-də investisiya layihəsinin həssaslıq təhlili nümunəsi

Təhlil metodologiyasını daha yaxşı başa düşmək üçün bir nümunə lazımdır. Layihəni aşağıdakı məlum məlumatlarla təhlil edək:

1. Layihəni təhlil etmək üçün cədvəli doldurun.

2. OFFSET funksiyasından istifadə edərək pul vəsaitlərinin hərəkətini hesablayırıq. İlkin mərhələdə axın investisiyalara bərabərdir. Sonra düsturu tətbiq edirik: =ƏGƏR(OFFSET(Nömrə,1;)=2;SUM(Daxil 1:Çıxış 1); SUM(Daxil 1:Çıxış 1)+$B$ 5)
   Cədvəlin tərtibindən asılı olaraq düsturdakı hüceyrə təyinatları fərqli ola bilər. Sonda ilkin məlumatdan alınan dəyər əlavə olunur – xilasetmə dəyəri.

3. Layihənin hansı müddətə öz bəhrəsini verəcəyini müəyyənləşdiririk. İlkin dövr üçün bu düsturdan istifadə edirik: =XÜLASƏ(G7: G17;»<0″). Hüceyrə diapazonu pul vəsaitlərinin hərəkəti sütunudur. Növbəti dövrlər üçün bu düsturu tətbiq edirik: =İlkin dövr+ƏG(Birinci e.axın>0; Birinci e.axın;0). Layihə 4 ildən sonra zərərsizlik nöqtəsindədir.

4. Layihənin bəhrə verdiyi dövrlərin nömrələri üçün bir sütun yaradırıq.

5. İnvestisiya gəlirini hesablayırıq. Müəyyən bir müddətdə qazancın ilkin investisiyaya bölündüyü bir ifadə etmək lazımdır.

6. Bu düsturdan istifadə edərək endirim faktorunu təyin edirik: =1/(1+Disk.%) ^Nömrə.

7. Çarpmadan istifadə edərək cari dəyəri hesablayırıq - pul vəsaitlərinin hərəkəti endirim əmsalı ilə vurulur.

8. Gəlin PI (gəlirlilik indeksi) hesablayaq. Zamanla cari dəyər layihənin başlanğıcındakı investisiyaya bölünür.

9. IRR funksiyasından istifadə edərək daxili gəlir dərəcəsini təyin edək: =IRR(Nağd pul axını diapazonu).

Datasheet istifadə edərək İnvestisiya Həssaslıq Təhlili

İnvestisiya sahəsində layihələrin təhlili üçün digər üsullar məlumat cədvəlindən daha uyğundur. Bir çox istifadəçi düstur tərtib edərkən çaşqınlıq yaşayır. Bir amilin digərlərindəki dəyişikliklərdən asılılığını öyrənmək üçün hesablamaları daxil etmək və məlumatları oxumaq üçün düzgün xanaları seçməlisiniz.

Hesablamaların avtomatlaşdırılması ilə Excel-də faktor və dispersiya təhlili

Həssaslıq təhlilinin başqa bir tipologiyası faktor analizi və variasiya təhlilidir. Birinci növ rəqəmlər arasındakı əlaqəni müəyyənləşdirir, ikincisi bir dəyişənin digərlərindən asılılığını ortaya qoyur.

Excel-də ANOVA

Belə təhlilin məqsədi dəyərin dəyişkənliyini üç komponentə bölməkdir:

1. Başqa dəyərlərin təsiri nəticəsində dəyişkənlik.
2. Ona təsir edən dəyərlərin əlaqəsinə görə dəyişikliklər.
3. Təsadüfi dəyişikliklər.

Gəlin “Məlumatların təhlili” Excel əlavəsi vasitəsilə variasiya təhlilini aparaq. Aktiv deyilsə, parametrlərdə aktivləşdirilə bilər.

İlkin cədvəl iki qaydaya əməl etməlidir: hər qiymət üçün bir sütun var və içindəki məlumatlar artan və ya azalan qaydada düzülür. Təhsil səviyyəsinin münaqişədə davranışa təsirini yoxlamaq lazımdır.

1. Məlumatlar sekmesinde Data Analizi alətini tapın və onun pəncərəsini açın. Siyahıda birtərəfli dispersiya təhlilini seçməlisiniz.

2. Dialoq qutusunun sətirlərini doldurun. Giriş intervalı başlıqlar və nömrələr istisna olmaqla, bütün xanalardır. Sütunlara görə qruplaşdırın. Nəticələri yeni vərəqdə göstəririk.

Sarı hüceyrədəki dəyər birdən böyük olduğundan, fərziyyə yanlış hesab edilə bilər - təhsil və münaqişədə davranış arasında heç bir əlaqə yoxdur.

Excel-də faktor təhlili: bir nümunə

Gəlin satış sahəsində məlumatların əlaqəsini təhlil edək – populyar və populyar olmayan məhsulları müəyyən etmək lazımdır. İlkin məlumat:

1. İkinci ayda hansı mallara tələbin daha çox artdığını öyrənməliyik. Tələbin artımını və azalmasını müəyyən etmək üçün yeni cədvəl tərtib edirik. Artım bu düsturla hesablanır: =ƏGƏR((Tələb 2-Tələb 1)>0; Tələb 2- Tələb 1;0). Düsturu azaldın: =ƏGƏR(Artım=0; Tələb 1- Tələb 2;0).

2. Mallara tələbin artımını faizlə hesablayın: =ƏGƏR(Artım/Nəticə 2 =0; Azalma/Nəticə 2; Artım/Nəticə 2).

3. Aydınlıq üçün diaqram yaradaq – bir sıra xanalar seçin və “Daxil et” nişanı vasitəsilə histoqram yaradın. Parametrlərdə, doldurmağı aradan qaldırmalısınız, bu, Format Data Series aləti vasitəsilə edilə bilər.

Excel-də variasiyanın ikitərəfli təhlili

Dispersiya təhlili bir neçə dəyişənlə aparılır. Bunu bir nümunə ilə nəzərdən keçirin: müxtəlif həcmli səsə reaksiyanın kişilərdə və qadınlarda nə qədər tez özünü göstərdiyini öyrənməlisiniz.

1. "Məlumatların Təhlili" ni açırıq, siyahıda təkrarlanmadan ikitərəfli dispersiya təhlilini tapmalısınız.
2. Daxiletmə intervalı – məlumatları ehtiva edən xanalar (başlıqsız). Nəticələri yeni vərəqdə göstəririk və "OK" düyməsini sıxırıq.

F dəyəri F-kritikindən böyükdür, yəni döşəmə səsə reaksiya sürətinə təsir edir.

Nəticə

Bu məqalədə Excel cədvəlində həssaslıq təhlili ətraflı müzakirə edilmişdir ki, hər bir istifadəçi onun tətbiqi üsullarını başa düşə bilsin.