Զգայունության վերլուծություն Excel-ում (Տվյալների աղյուսակի նմուշ)

Ֆինանսների ոլորտում գործընթացները միշտ փոխկապակցված են. մի գործոնը կախված է մյուսից և փոխվում դրա հետ միասին: Հետևեք այս փոփոխություններին և հասկացեք, թե ինչ է սպասվում ապագայում, գուցե օգտագործելով Excel գործառույթները և աղյուսակների մեթոդները:

Տվյալների աղյուսակով բազմաթիվ արդյունքներ ստանալը

Տվյալների աղյուսակի հնարավորությունները ինչ-որ վերլուծության տարրեր են, որոնք հաճախ կատարվում են Microsoft Excel-ի միջոցով: Սա զգայունության վերլուծության երկրորդ անունն է:

Overview

Տվյալների աղյուսակը բջիջների տիրույթի տեսակ է, որը կարող է օգտագործվել որոշ բջիջների արժեքները փոխելով խնդիրները լուծելու համար: Այն ստեղծվում է, երբ անհրաժեշտ է հետևել բանաձևի բաղադրիչների փոփոխություններին և ստանալ արդյունքների թարմացումներ՝ ըստ այդ փոփոխությունների: Եկեք պարզենք, թե ինչպես օգտագործել տվյալների աղյուսակները հետազոտության մեջ և ինչ տեսակներ են դրանք:

Տվյալների աղյուսակների հիմունքները

Տվյալների աղյուսակների երկու տեսակ կա, դրանք տարբերվում են բաղադրիչների քանակով։ Դուք պետք է կազմեք աղյուսակ՝ կենտրոնանալով uXNUMXbuXNUMXb արժեքների քանակի վրա, որոնք դուք պետք է ստուգեք դրա հետ:

Վիճակագիրներն օգտագործում են մեկ փոփոխական աղյուսակ, երբ մեկ կամ մի քանի արտահայտություններում կա միայն մեկ փոփոխական, որը կարող է փոխել դրանց արդյունքը: Օրինակ, այն հաճախ օգտագործվում է PMT ֆունկցիայի հետ համատեղ: Բանաձևը նախատեսված է կանոնավոր վճարման չափը հաշվարկելու համար և հաշվի է առնում պայմանագրում նշված տոկոսադրույքը: Նման հաշվարկներում փոփոխականները գրվում են մի սյունակում, իսկ հաշվարկների արդյունքները՝ մեկ այլ սյունակում։ Տվյալների ափսեի օրինակ 1 փոփոխականով.

Հաջորդը, հաշվի առեք 2 փոփոխական ունեցող թիթեղները: Դրանք օգտագործվում են այն դեպքերում, երբ երկու գործոն ազդում է ցանկացած ցուցանիշի փոփոխության վրա. Երկու փոփոխականները կարող են հայտնվել վարկի հետ կապված մեկ այլ աղյուսակում, որը կարող է օգտագործվել մարման օպտիմալ ժամկետը և ամսական վճարման չափը որոշելու համար: Այս հաշվարկում դուք նույնպես պետք է օգտագործեք PMT գործառույթը: 2 փոփոխականներով աղյուսակի օրինակ.

Տվյալների աղյուսակի ստեղծում մեկ փոփոխականով

Դիտարկենք վերլուծության մեթոդը՝ օգտագործելով փոքր գրախանութի օրինակը, որն ունի ընդամենը 100 գիրք պահեստում: Դրանցից մի քանիսը կարող են վաճառվել ավելի թանկ (50 դոլար), մնացածը գնորդներին ավելի քիչ կարժենա (20 դոլար): Բոլոր ապրանքների վաճառքից ստացված ընդհանուր եկամուտը հաշվարկված է. սեփականատերը որոշել է, որ գրքերի 60%-ը թանկ գնով կվաճառի։ Դուք պետք է պարզեք, թե ինչպես կավելանա եկամուտը, եթե բարձրացնեք ավելի մեծ ծավալի ապրանքների գինը՝ 70% և այլն:

Ուշադրություն դարձրեք: Ընդհանուր եկամուտը պետք է հաշվարկվի բանաձևով, այլապես հնարավոր չի լինի տվյալների աղյուսակ կազմել։

1. Ընտրեք թերթի եզրից հեռու գտնվող ազատ բջիջ և դրանում գրեք բանաձևը. = Ընդհանուր եկամտի բջիջ: Օրինակ, եթե եկամուտը գրված է C14 բջիջում (նշված է պատահական նշանակում), դուք պետք է գրեք սա. =S14.
2. Մենք գրում ենք ապրանքների ծավալի տոկոսը այս բջիջի ձախ կողմում գտնվող սյունակում՝ ոչ ներքևում, սա շատ կարևոր է:
3. Մենք ընտրում ենք բջիջների այն շրջանակը, որտեղ գտնվում են տոկոսային սյունակը և ընդհանուր եկամտի հղումը:

4. «Տվյալներ» ներդիրում մենք գտնում ենք «Ինչ կլիներ, եթե վերլուծություն» կետը և սեղմեք դրա վրա – բացվող ընտրացանկում ընտրեք «Տվյալների աղյուսակ» տարբերակը:

5. Մի փոքրիկ պատուհան կբացվի, որտեղ «Փոխարինեք արժեքները ըստ տողերի…» սյունակում պետք է նշեք մի բջիջ՝ սկզբում բարձր գնով վաճառված գրքերի տոկոսով: Այս քայլն արվում է, որպեսզի վերահաշվարկվի ընդհանուր եկամուտը՝ հաշվի առնելով աճող տոկոսը։

Այն պատուհանում, որտեղ աղյուսակը կազմելու համար մուտքագրվել են տվյալները, «OK» կոճակը սեղմելուց հետո, տողերում կհայտնվեն հաշվարկների արդյունքները:

Բանաձևի ավելացում մեկ փոփոխական տվյալների աղյուսակում

Աղյուսակից, որն օգնել է հաշվարկել գործողությունը միայն մեկ փոփոխականով, կարող եք կատարել բարդ վերլուծության գործիք՝ ավելացնելով լրացուցիչ բանաձև: Այն պետք է մուտքագրվի արդեն գոյություն ունեցող բանաձևի կողքին. օրինակ, եթե աղյուսակը ուղղված է տողերին, մենք մուտքագրում ենք արտահայտությունը եղածից աջ գտնվող բջիջում: Երբ սյունակի կողմնորոշումը սահմանվում է, մենք գրում ենք նոր բանաձևը հնի տակ: Հաջորդը, հետևեք ալգորիթմին.

1. Կրկին ընտրեք բջիջների շրջանակը, բայց այժմ այն ​​պետք է ներառի նոր բանաձևը:
2. Բացեք «Իսկ եթե» վերլուծության ընտրացանկը և ընտրեք «Տվյալների թերթիկ»:
3. Համապատասխան դաշտում տողերով կամ սյունակներով նոր բանաձև ենք ավելացնում՝ կախված ափսեի կողմնորոշումից։

Ստեղծեք տվյալների աղյուսակ երկու փոփոխականներով

Նման աղյուսակի սկիզբը մի փոքր տարբերվում է. անհրաժեշտ է տոկոսային արժեքներից բարձր հղում դնել ընդհանուր եկամուտին: Հաջորդը, մենք կատարում ենք հետևյալ քայլերը.

1. Մեկ տողում գրեք գների տարբերակները եկամուտին հղումով՝ յուրաքանչյուր գնի համար մեկ բջիջ:
2. Ընտրեք մի շարք բջիջներ:

3. Բացեք տվյալների աղյուսակի պատուհանը, ինչպես մեկ փոփոխականով աղյուսակ կազմելիս՝ գործիքագոտու «Տվյալներ» ներդիրի միջոցով:
4. «Փոխարինեք արժեքները սյունակներով…» սյունակում փոխարինեք սկզբնական բարձր գնով բջիջը:
5. «Փոխարինել արժեքները ըստ տողերի…» սյունակում ավելացրեք թանկարժեք գրքերի վաճառքի սկզբնական տոկոսով բջիջ և սեղմեք «OK»:

Արդյունքում ամբողջ աղյուսակը լցված է ապրանքների վաճառքի տարբեր պայմաններով հնարավոր եկամուտների չափերով։

Արագացրեք հաշվարկները տվյալների աղյուսակներ պարունակող աշխատաթերթերի համար

Եթե ​​Ձեզ անհրաժեշտ են արագ հաշվարկներ տվյալների աղյուսակում, որոնք չեն առաջացնում ամբողջ աշխատանքային գրքի վերահաշվարկը, կան մի քանի բան, որ կարող եք անել՝ գործընթացը արագացնելու համար:

1. Բացեք ընտրանքների պատուհանը, ընտրեք «Բանաձևեր» կետը աջ կողմում գտնվող ընտրացանկում:
2. Ընտրեք «Ավտոմատ, բացառությամբ տվյալների աղյուսակների» կետը «Հաշվարկներ աշխատանքային գրքում» բաժնում:

3. Եկեք վերահաշվարկենք աղյուսակի արդյունքները ձեռքով: Դա անելու համար ընտրեք բանաձևերը և սեղմեք F ստեղնը:

Զգայունության վերլուծություն կատարելու այլ գործիքներ

Ծրագրում կան այլ գործիքներ, որոնք կօգնեն ձեզ կատարել զգայունության վերլուծություն: Նրանք ավտոմատացնում են որոշ գործողություններ, որոնք այլապես պետք է կատարվեին ձեռքով:

1. «Պարամետրի ընտրություն» ֆունկցիան հարմար է, եթե հայտնի է ցանկալի արդյունքը, և այդպիսի արդյունք ստանալու համար անհրաժեշտ է իմանալ փոփոխականի մուտքային արժեքը։.
2. «Լուծման որոնումը» հավելում է խնդիրների լուծման համար: Պետք է սահմաններ դնել և մատնանշել դրանք, որից հետո համակարգը կգտնի պատասխանը։ Լուծումը որոշվում է արժեքների փոփոխությամբ:
3. Զգայունության վերլուծությունը կարող է կատարվել՝ օգտագործելով Scenario Manager-ը: Այս գործիքը գտնվում է «Ինչ-եթե վերլուծության» ցանկում՝ «Տվյալներ» ներդիրի տակ: Այն փոխարինում է արժեքները մի քանի բջիջներում. թիվը կարող է հասնել 32-ի: Դիսպետչերը համեմատում է այդ արժեքները, որպեսզի օգտագործողը ստիպված չլինի դրանք ձեռքով փոխել: Սցենարի մենեջերի օգտագործման օրինակ.

Ներդրումային նախագծի զգայունության վերլուծություն Excel-ում

Ինչ-եթե վերլուծությունը հատկապես օգտակար է այն իրավիճակներում, որտեղ կանխատեսումներ են պահանջվում, ինչպիսիք են ներդրումները: Վերլուծաբաններն օգտագործում են այս մեթոդը՝ պարզելու համար, թե ինչպես կփոխվի ընկերության բաժնետոմսերի արժեքը որոշ գործոնների փոփոխության արդյունքում:

Ներդրումների զգայունության վերլուծության մեթոդ

«Իսկ եթե» վերլուծելիս օգտագործեք թվարկում՝ ձեռքով կամ ավտոմատ: Արժեքների շրջանակը հայտնի է, և դրանք հերթով փոխարինվում են բանաձևի մեջ: Արդյունքը արժեքների մի շարք է: Նրանցից ընտրեք համապատասխան թիվը։ Դիտարկենք չորս ցուցանիշ, որոնց նկատմամբ զգայունության վերլուծություն է իրականացվում ֆինանսների ոլորտում.

1. Զուտ ներկա արժեքը – հաշվարկվում է՝ հանելով ներդրման գումարը եկամտի չափից:
2. Ներքին եկամտաբերություն / շահույթ - ցույց է տալիս, թե որքան շահույթ է պահանջվում ստանալ ներդրումից մեկ տարվա ընթացքում:
3. Վճարման գործակիցը բոլոր շահույթի հարաբերակցությունն է սկզբնական ներդրումներին:
4. Զեղչված շահույթի ինդեքս - ցույց է տալիս ներդրման արդյունավետությունը:

Ֆորմուլա

Ներկառուցման զգայունությունը կարող է հաշվարկվել հետևյալ բանաձևով. Փոխել ելքային պարամետրը %-ով / Change input parameter in %-ով:

Ելքային և մուտքային պարամետրերը կարող են լինել ավելի վաղ նկարագրված արժեքները:

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 աղյուսակում, որպեսզի յուրաքանչյուր օգտվող կարողանա հասկանալ դրա կիրառման մեթոդները: