მგრძნობელობის ანალიზი Excel-ში (მონაცემთა ცხრილის ნიმუში)

ფინანსების სფეროში პროცესები ყოველთვის ურთიერთდაკავშირებულია - ერთი ფაქტორი დამოკიდებულია მეორეზე და იცვლება მასთან ერთად. თვალყური ადევნეთ ამ ცვლილებებს და გაიგეთ, რას უნდა ველოდოთ მომავალში, შესაძლოა Excel ფუნქციების და ცხრილების მეთოდების გამოყენებით.

მრავალი შედეგის მიღება მონაცემთა ცხრილით

მონაცემთა ცხრილის შესაძლებლობები არის რა-თუ ანალიზის ელემენტები — ხშირად კეთდება Microsoft Excel-ის საშუალებით. ეს არის მეორე სახელი მგრძნობელობის ანალიზისთვის.

მიმოხილვა

მონაცემთა ცხრილი არის უჯრედების დიაპაზონის ტიპი, რომელიც შეიძლება გამოყენებულ იქნას პრობლემების გადასაჭრელად ზოგიერთ უჯრედში მნიშვნელობების შეცვლით. იგი იქმნება მაშინ, როდესაც აუცილებელია ფორმულის კომპონენტებში ცვლილებების თვალყურის დევნება და ამ ცვლილებების მიხედვით შედეგების განახლებების მიღება. მოდით გავარკვიოთ, როგორ გამოვიყენოთ მონაცემთა ცხრილები კვლევაში და რა ტიპები არიან ისინი.

საფუძვლები მონაცემთა ცხრილების შესახებ

არსებობს ორი ტიპის მონაცემთა ცხრილი, ისინი განსხვავდებიან კომპონენტების რაოდენობით. თქვენ უნდა შეადგინოთ ცხრილი იმ მნიშვნელობების რაოდენობაზე ფოკუსირებული uXNUMXbuXNUMXb, რომელიც უნდა შეამოწმოთ მასთან.

სტატისტიკოსები იყენებენ ერთ ცვლადის ცხრილს, როდესაც არის მხოლოდ ერთი ცვლადი ერთ ან მეტ გამონათქვამში, რომელსაც შეუძლია შეცვალოს მათი შედეგი. მაგალითად, ის ხშირად გამოიყენება PMT ფუნქციასთან ერთად. ფორმულა შექმნილია რეგულარული გადახდის ოდენობის გამოსათვლელად და ითვალისწინებს ხელშეკრულებაში მითითებულ საპროცენტო განაკვეთს. ასეთ გამოთვლებში ცვლადები იწერება ერთ სვეტში, ხოლო გამოთვლების შედეგები მეორეში. მონაცემთა ფირფიტის მაგალითი 1 ცვლადით:

შემდეგი, განიხილეთ ფირფიტები 2 ცვლადით. ისინი გამოიყენება იმ შემთხვევებში, როდესაც ორი ფაქტორი გავლენას ახდენს ნებისმიერი ინდიკატორის ცვლილებაზე. ეს ორი ცვლადი შეიძლება დასრულდეს სესხთან დაკავშირებულ სხვა ცხრილში, რომელიც შეიძლება გამოყენებულ იქნას ოპტიმალური დაფარვის პერიოდისა და ყოველთვიური გადახდის ოდენობის დასადგენად. ამ გაანგარიშებაში, თქვენ ასევე უნდა გამოიყენოთ PMT ფუნქცია. ცხრილის მაგალითი 2 ცვლადით:

მონაცემთა ცხრილის შექმნა ერთი ცვლადით

განვიხილოთ ანალიზის მეთოდი პატარა წიგნის მაღაზიის მაგალითით, სადაც მხოლოდ 100 წიგნია მარაგში. ზოგიერთი მათგანი შეიძლება გაიყიდოს უფრო ძვირად (50$), დანარჩენი მყიდველებს ნაკლები დაუჯდებათ ($20). გამოითვლება მთლიანი შემოსავალი ყველა საქონლის გაყიდვიდან – მფლობელმა გადაწყვიტა, რომ წიგნების 60%-ს ძვირად გაყიდიდა. თქვენ უნდა გაარკვიოთ, როგორ გაიზრდება შემოსავალი, თუ გაზრდით ფასს უფრო დიდი მოცულობის საქონელზე – 70% და ა.შ.

ყურადღება მიაქციე! მთლიანი შემოსავალი უნდა გამოითვალოს ფორმულით, წინააღმდეგ შემთხვევაში მონაცემთა ცხრილის შედგენა შეუძლებელი იქნება.

1. აირჩიეთ თავისუფალი უჯრედი ფურცლის კიდედან მოშორებით და ჩაწერეთ მასში ფორმულა: = მთლიანი შემოსავლის უჯრედი. მაგალითად, თუ შემოსავალი ჩაწერილია C14 უჯრედში (მითითებულია შემთხვევითი აღნიშვნა), თქვენ უნდა დაწეროთ ეს: =S14.
2. ჩვენ ვწერთ საქონლის მოცულობის პროცენტს ამ უჯრედის მარცხნივ სვეტში - არა მის ქვემოთ, ეს ძალიან მნიშვნელოვანია.
3. ჩვენ ვირჩევთ უჯრედების დიაპაზონს, სადაც განთავსებულია პროცენტული სვეტი და მთლიანი შემოსავლის ბმული.

4. "მონაცემების" ჩანართზე ვპოულობთ პუნქტს "What if ანალიზი" და ვაწკაპუნებთ მასზე - მენიუში, რომელიც იხსნება, აირჩიეთ ვარიანტი "მონაცემთა ცხრილი".

5. გაიხსნება პატარა ფანჯარა, სადაც უნდა მიუთითოთ უჯრედი წიგნების პროცენტული მაჩვენებლით, რომელიც თავდაპირველად გაიყიდა ძვირად, სვეტში "შეცვალეთ მნიშვნელობები რიგების მიხედვით ...". ეს ნაბიჯი კეთდება იმისათვის, რომ გადაანგარიშდეს მთლიანი შემოსავალი მზარდი პროცენტის გათვალისწინებით.

"OK" ღილაკზე დაწკაპუნების შემდეგ ფანჯარაში, სადაც მონაცემები იყო შეყვანილი ცხრილის შედგენისთვის, გამოთვლების შედეგები გამოჩნდება ხაზებში.

ფორმულის დამატება ერთი ცვლადი მონაცემთა ცხრილში

ცხრილიდან, რომელიც დაეხმარა მოქმედების გამოთვლას მხოლოდ ერთი ცვლადით, შეგიძლიათ შექმნათ დახვეწილი ანალიზის ინსტრუმენტი დამატებითი ფორმულის დამატებით. ის უნდა იყოს შეყვანილი უკვე არსებული ფორმულის გვერდით - მაგალითად, თუ ცხრილი მწკრივზეა ორიენტირებული, გამოსახულებას შევიყვანთ არსებულის მარჯვნივ მდებარე უჯრედში. როდესაც სვეტის ორიენტაცია დაყენებულია, ჩვენ ვწერთ ახალ ფორმულას ძველის ქვეშ. შემდეგი, მიჰყევით ალგორითმს:

1. ისევ აირჩიეთ უჯრედების დიაპაზონი, მაგრამ ახლა ის უნდა შეიცავდეს ახალ ფორმულას.
2. გახსენით "რა თუ" ანალიზის მენიუ და აირჩიეთ "მონაცემთა ცხრილი".
3. ჩვენ ვამატებთ ახალ ფორმულას შესაბამის ველში მწკრივებში ან სვეტებში, ფირფიტის ორიენტაციის მიხედვით.

შექმენით მონაცემთა ცხრილი ორი ცვლადით

ასეთი ცხრილის დასაწყისი ოდნავ განსხვავებულია - თქვენ უნდა დააყენოთ ბმული მთლიან შემოსავალზე პროცენტული მნიშვნელობების ზემოთ. შემდეგი, ჩვენ ვასრულებთ ამ ნაბიჯებს:

1. ჩაწერეთ ფასების ვარიანტები ერთ სტრიქონში შემოსავლის ბმულით - თითო უჯრედი თითოეულ ფასზე.
2. აირჩიეთ უჯრედების დიაპაზონი.

3. გახსენით მონაცემთა ცხრილის ფანჯარა, როგორც ერთი ცვლადით ცხრილის შედგენისას – ხელსაწყოთა ზოლზე „მონაცემების“ ჩანართის მეშვეობით.
4. ჩაანაცვლეთ სვეტში "მნიშვნელობების ჩანაცვლება სვეტებით ..." უჯრედი საწყისი მაღალი ფასით.
5. დაამატეთ უჯრედი ძვირადღირებული წიგნების გაყიდვის საწყისი პროცენტით სვეტში "შეცვალეთ მნიშვნელობები რიგების მიხედვით ..." და დააჭირეთ "OK".

შედეგად, მთელი ცხრილი ივსება შესაძლო შემოსავლის თანხებით საქონლის გაყიდვის განსხვავებული პირობებით.

დააჩქარეთ გამოთვლები სამუშაო ფურცლებისთვის, რომლებიც შეიცავს მონაცემთა ცხრილებს

თუ გჭირდებათ სწრაფი გამოთვლები მონაცემთა ცხრილში, რომელიც არ გამოიწვევს მთელი სამუშაო წიგნის ხელახლა გამოთვლას, არსებობს რამდენიმე რამ, რისი გაკეთებაც შეგიძლიათ პროცესის დასაჩქარებლად.

1. გახსენით პარამეტრების ფანჯარა, აირჩიეთ პუნქტი "ფორმულები" მენიუში მარჯვნივ.
2. აირჩიეთ პუნქტი "ავტომატური, გარდა მონაცემთა ცხრილებისა" განყოფილებაში "გამოთვლები სამუშაო წიგნში".

3. მოდით ხელახლა გამოვთვალოთ ცხრილის შედეგები ხელით. ამისათვის აირჩიეთ ფორმულები და დააჭირეთ F ღილაკს.

სხვა ინსტრუმენტები მგრძნობელობის ანალიზის შესასრულებლად

პროგრამაში არის სხვა ხელსაწყოები, რომლებიც დაგეხმარებათ მგრძნობელობის ანალიზის შესრულებაში. ისინი ავტომატიზირებენ ზოგიერთ ქმედებას, რომელიც სხვაგვარად უნდა გაკეთდეს ხელით.

1. "პარამეტრის შერჩევის" ფუნქცია შესაფერისია, თუ სასურველი შედეგი ცნობილია და თქვენ უნდა იცოდეთ ცვლადის შეყვანის მნიშვნელობა ასეთი შედეგის მისაღებად..
2. "გამოსავალის ძიება" არის დანამატი პრობლემების გადასაჭრელად. აუცილებელია ლიმიტების დაწესება და მათზე მითითება, რის შემდეგაც სისტემა იპოვის პასუხს. გამოსავალი განისაზღვრება მნიშვნელობების შეცვლით.
3. მგრძნობელობის ანალიზი შეიძლება გაკეთდეს სცენარის მენეჯერის გამოყენებით. ეს ინსტრუმენტი გვხვდება რა-თუ ანალიზის მენიუში მონაცემთა ჩანართში. ის ცვლის მნიშვნელობებს რამდენიმე უჯრედში - რიცხვი შეიძლება მიაღწიოს 32-ს. დისპეტჩერი ადარებს ამ მნიშვნელობებს ისე, რომ მომხმარებელს არ მოუწიოს მათი ხელით შეცვლა. სკრიპტის მენეჯერის გამოყენების მაგალითი:

საინვესტიციო პროექტის სენსიტიურობის ანალიზი Excel-ში

რა-თუ ანალიზი განსაკუთრებით სასარგებლოა იმ სიტუაციებში, როდესაც საჭიროა პროგნოზირება, როგორიცაა ინვესტიცია. ანალიტიკოსები ამ მეთოდს იყენებენ იმის გასარკვევად, თუ როგორ შეიცვლება კომპანიის აქციების ღირებულება ზოგიერთი ფაქტორების ცვლილების შედეგად.

ინვესტიციის სენსიტიურობის ანალიზის მეთოდი

„რა იქნებოდა თუ“ ანალიზისას გამოიყენეთ ჩამოთვლა – ხელით ან ავტომატური. მნიშვნელობების დიაპაზონი ცნობილია და ისინი სათითაოდ იცვლება ფორმულაში. შედეგი არის ღირებულებების ნაკრები. აირჩიეთ მათგან შესაბამისი ნომერი. განვიხილოთ ოთხი ინდიკატორი, რომლებისთვისაც ტარდება მგრძნობელობის ანალიზი ფინანსების სფეროში:

1. წმინდა მიმდინარე ღირებულება - გამოითვლება ინვესტიციის ოდენობის შემოსავლის ოდენობის გამოკლებით.
2. ანაზღაურების/მოგების შიდა კოეფიციენტი – მიუთითებს, თუ რამდენი მოგებაა საჭირო ინვესტიციიდან წელიწადში.
3. ანაზღაურებადი კოეფიციენტი არის ყველა მოგების თანაფარდობა საწყის ინვესტიციასთან.
4. დისკონტირებული მოგების ინდექსი – მიუთითებს ინვესტიციის ეფექტურობაზე.

ფორმულა

ჩანერგვის მგრძნობელობა შეიძლება გამოითვალოს ამ ფორმულის გამოყენებით: გამომავალი პარამეტრის შეცვლა %-ში / შეყვანის პარამეტრის შეცვლა %-ში.

გამომავალი და შეყვანის პარამეტრები შეიძლება იყოს ადრე აღწერილი მნიშვნელობები.

1. თქვენ უნდა იცოდეთ შედეგი სტანდარტულ პირობებში.
2. ჩვენ ვცვლით ერთ-ერთ ცვლადს და ვაკვირდებით შედეგში ცვლილებებს.
3. ჩვენ ვიანგარიშებთ ორივე პარამეტრის პროცენტულ ცვლილებას დადგენილ პირობებთან მიმართებაში.
4. მიღებულ პროცენტებს ჩავსვამთ ფორმულაში და განვსაზღვრავთ მგრძნობელობას.

საინვესტიციო პროექტის სენსიტიურობის ანალიზის მაგალითი Excel-ში

ანალიზის მეთოდოლოგიის უკეთ გასაგებად საჭიროა მაგალითი. მოდით გავაანალიზოთ პროექტი შემდეგი ცნობილი მონაცემებით:

1. შეავსეთ ცხრილი მასზე პროექტის გასაანალიზებლად.

2. ჩვენ ვიანგარიშებთ ფულადი ნაკადს OFFSET ფუნქციის გამოყენებით. საწყის ეტაპზე ნაკადი ინვესტიციების ტოლია. შემდეგი, ჩვენ ვიყენებთ ფორმულას: =IF(OFFSET(ნომერი,1;)=2;SUM(შემოდინება 1:გადინება 1); SUM(შემოდინება 1:გამომავალი 1)+$B$ 5)
   ფორმულაში უჯრედების აღნიშვნები შეიძლება განსხვავდებოდეს ცხრილის განლაგებიდან გამომდინარე. დასასრულს ემატება საწყისი მონაცემების მნიშვნელობა - სამაშველო მნიშვნელობა.

3. ჩვენ განვსაზღვრავთ იმ პერიოდს, რომლისთვისაც პროექტი ანაზღაურდება. საწყისი პერიოდისთვის ჩვენ ვიყენებთ ამ ფორმულას: =უმეტესად(G7: გ17;»<0″). უჯრედის დიაპაზონი არის ფულადი ნაკადების სვეტი. შემდგომი პერიოდისთვის ჩვენ ვიყენებთ ამ ფორმულას: =საწყისი პერიოდი+IF(First e.stream>0; First 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. უნდა გავარკვიოთ, რომელ საქონელზე გაიზარდა მოთხოვნა ყველაზე მეტად მეორე თვეში. ჩვენ ვადგენთ ახალ ცხრილს მოთხოვნის ზრდისა და კლების დასადგენად. ზრდა გამოითვლება ამ ფორმულით: =IF((მოთხოვნა 2-მოთხოვნა 1)>0; მოთხოვნა 2- მოთხოვნა 1;0). შემცირების ფორმულა: =IF(ზრდა=0; მოთხოვნა 1- მოთხოვნა 2;0).

2. გამოთვალეთ საქონელზე მოთხოვნის ზრდა პროცენტულად: =IF (ზრდა/შედეგი 2 =0; შემცირება/შედეგი 2; ზრდა/შედეგი 2).

3. მოდით შევქმნათ დიაგრამა სიცხადისთვის - აირჩიეთ უჯრედების დიაპაზონი და შექმენით ჰისტოგრამა "ჩასმა" ჩანართის მეშვეობით. პარამეტრებში, თქვენ უნდა ამოიღოთ შევსება, ეს შეიძლება გაკეთდეს Format Data Series ინსტრუმენტის საშუალებით.

დისპერსიის ორმხრივი ანალიზი Excel-ში

დისპერსიის ანალიზი ტარდება რამდენიმე ცვლადით. განვიხილოთ ეს მაგალითით: თქვენ უნდა გაარკვიოთ, რამდენად სწრაფად ვლინდება რეაქცია სხვადასხვა მოცულობის ხმაზე მამაკაცებსა და ქალებში.

1. ჩვენ ვხსნით "მონაცემთა ანალიზს", სიაში თქვენ უნდა იპოვოთ დისპერსიის ორმხრივი ანალიზი გამეორებების გარეშე.
2. შეყვანის ინტერვალი – უჯრედები, რომლებიც შეიცავს მონაცემებს (სათაურის გარეშე). ჩვენ ვაჩვენებთ შედეგებს ახალ ფურცელზე და დააჭირეთ "OK".

F მნიშვნელობა უფრო დიდია ვიდრე F-კრიტიკული, რაც ნიშნავს, რომ იატაკი გავლენას ახდენს ბგერაზე რეაქციის სიჩქარეზე.

დასკვნა

ამ სტატიაში დეტალურად იყო განხილული Excel-ის ცხრილების მგრძნობელობის ანალიზი, რათა თითოეულმა მომხმარებელმა შეძლოს მისი გამოყენების მეთოდების გაგება.