Ανάλυση ευαισθησίας Excel (δείγμα πίνακα δεδομένων)

Οι διαδικασίες χρηματοδότησης είναι πάντα αλληλένδετες - ένας παράγοντας εξαρτάται από το άλλο και τις αλλαγές με αυτό. Παρακολούθηση αυτών των αλλαγών και καταλάβετε τι να περιμένουν στο μέλλον είναι δυνατό χρησιμοποιώντας λειτουργίες του Excel και τις πίνακες μεθόδων.

Λάβετε πολλά αποτελέσματα χρησιμοποιώντας έναν πίνακα δεδομένων

Οι δυνατότητες των τραπεζιών δεδομένων είναι στοιχεία της ανάλυσης "Τι γίνεται αν" συχνά πραγματοποιείται μέσω του Microsoft Excel. Αυτό είναι το δεύτερο όνομα της ανάλυσης ευαισθησίας.

Γενικός

Ο πίνακας δεδομένων είναι ο τύπος της κυτταρικής περιοχής, με τον οποίο μπορείτε να λύσετε τα προβλήματα που προκύπτουν με την αλλαγή των τιμών σε ορισμένα κύτταρα. Εμφανίζεται όταν είναι απαραίτητο να παρακολουθούνται αλλαγές στα συστατικά του τύπου και να λαμβάνουν ενημερώσεις των αποτελεσμάτων, σύμφωνα με αυτές τις αλλαγές. Μάθετε πώς να εφαρμόζετε δισκία δεδομένων σε μελέτες και ποια είδη είναι.

Βασικές πληροφορίες σχετικά με τους πίνακες δεδομένων

Υπάρχουν δύο τύποι πινάκων δεδομένων, διαφέρουν στον αριθμό των εξαρτημάτων. Κάντε ένα τραπέζι που απαιτείται με προσανατολισμό με τον αριθμό των τιμών που πρέπει να ελέγχονται με αυτό.

Οι στατιστικές ειδικοί εφαρμόζουν έναν πίνακα με μία μεταβλητή όταν υπάρχει μόνο μία μεταβλητή σε μία ή περισσότερες εκφράσεις, οι οποίες μπορεί να επηρεάσουν την αλλαγή στο αποτέλεσμα τους. Για παράδειγμα, χρησιμοποιείται συχνά σε μια δέσμη με τη λειτουργία PL. Ο τύπος έχει σχεδιαστεί για να υπολογίζει το ποσό της τακτικής αμοιβής και λαμβάνει υπόψη το επιτόκιο που ορίζεται στη σύμβαση. Με αυτούς τους υπολογισμούς, οι μεταβλητές καταγράφονται σε μία στήλη και τα αποτελέσματα των υπολογισμών σε άλλο. Ένα παράδειγμα πλάκας δεδομένων με 1 μεταβλητή:

ένας

Στη συνέχεια, εξετάστε τα σημάδια με 2 μεταβλητές. Εφαρμόζονται σε περιπτώσεις όπου δύο παράγοντες επηρεάζουν την αλλαγή σε οποιονδήποτε δείκτη. Δύο μεταβλητές μπορεί να βρίσκονται σε άλλο τραπέζι που σχετίζεται με ένα δάνειο - με τη βοήθειά του, μπορείτε να προσδιορίσετε τη βέλτιστη περίοδο πληρωμής και το ποσό της μηνιαίας πληρωμής. Αυτός ο υπολογισμός πρέπει επίσης να χρησιμοποιήσει τη λειτουργία PPT. Ένα παράδειγμα πλάκας με 2 μεταβλητές:

2 Δημιουργία πίνακα δεδομένων με μία μεταβλητή

Εξετάστε τη μέθοδο ανάλυσης στο παράδειγμα ενός μικρού βιβλιοπωλείου, όπου υπάρχουν διαθέσιμα μόνο 100 βιβλία. Μερικοί από αυτούς μπορούν να πωληθούν πιο ακριβά ($ 50), το υπόλοιπο θα κοστίσει τους αγοραστές φθηνότερα ($ 20). Το συνολικό εισόδημα από την πώληση όλων των αγαθών έχει σχεδιαστεί - ο ιδιοκτήτης αποφάσισε ότι σε υψηλή τιμή του 60% των βιβλίων. Είναι απαραίτητο να μάθετε πώς θα αυξηθεί τα έσοδα αν αυξήσετε την τιμή ενός μεγαλύτερου όγκου αγαθών - 70% και ούτω καθεξής.

1. Επιλέξτε μια ελεύθερη απόσταση από την άκρη του φύλλου και γράψτε τον τύπο σε αυτό: = το κελί των συνολικών εσόδων. Για παράδειγμα, εάν το εισόδημα καταγράφεται στο C14 Cell (εμφανίζεται μια τυχαία ονομασία), είναι απαραίτητο να γράψετε έτσι: = C14.
2. Καταγράφουμε το ποσό των εμπορευμάτων στη στήλη στα αριστερά αυτού του κελιού - όχι κάτω από αυτό, είναι πολύ σημαντικό.
3. Διαθέτουμε το φάσμα των κυττάρων όπου βρίσκεται η στήλη ενδιαφέροντος και ένας σύνδεσμος προς το συνολικό εισόδημα.

3.

1. Βρίσκουμε στην καρτέλα "Δεδομένα" της "Ανάλυσης" τι γίνεται αν "" και κάντε κλικ σε αυτό - στο μενού που ανοίγει, πρέπει να επιλέξετε την επιλογή "Πίνακας δεδομένων".

τέσσερις

1. Ένα μικρό παράθυρο θα ανοίξει, όπου πρέπει να καθορίσετε ένα κελί με ένα ποσοστό των βιβλίων που πωλούνται αρχικά σε υψηλή τιμή στη στήλη "για να αντικαταστήσετε τις τιμές στις γραμμές ...". Το βήμα αυτό γίνεται για να γίνει επανυπολογισμός γενικών εσόδων, λαμβάνοντας υπόψη το αυξανόμενο ποσοστό.

πέντε

Αφού πιέσετε το κουμπί "OK" στο παράθυρο όπου εισήχθη δεδομένα για να συντάξετε τον πίνακα, τα αποτελέσματα των υπολογισμών θα εμφανιστούν στις σειρές.

Προσθέτοντας μια φόρμουλα σε έναν πίνακα δεδομένων με μια μεταβλητή

Από το τραπέζι που βοήθησε να υπολογίσετε τη δράση μόνο με μία μεταβλητή, μπορείτε να δημιουργήσετε ένα περίπλοκο εργαλείο ανάλυσης προσθέτοντας έναν πρόσθετο τύπο. Πρέπει να εισαχθεί κοντά στην ήδη υπάρχουσα φόρμουλα - για παράδειγμα, εάν ο πίνακας επικεντρώνεται σε σειρές, εισάγετε την έκφραση στο κελί προς τα δεξιά του ήδη υπάρχοντος. Όταν ο προσανατολισμός είναι εγκατεστημένος στις στήλες, καταγράψτε μια νέα φόρμουλα κάτω από το παλιό. Στη συνέχεια θα πρέπει να ενεργεί σύμφωνα με τον αλγόριθμο:

1. Επισημαίνουμε και πάλι το φάσμα των κυττάρων, αλλά τώρα θα πρέπει να περιλαμβάνει μια νέα φόρμουλα.
2. Ανοίξτε το μενού ανάλυσης "Τι γίνεται αν" και επιλέξτε "πίνακα δεδομένων".
3. Προσθέστε μια νέα φόρμουλα στο αντίστοιχο πεδίο on line ή με στήλες, ανάλογα με τον προσανατολισμό της πλάκας.

Δημιουργία πίνακα δεδομένων με δύο μεταβλητές

Η αρχή της προετοιμασίας ενός τέτοιου πίνακα είναι ελαφρώς διαφορετική - πρέπει να τοποθετήσετε ένα σύνδεσμο με τα συνολικά έσοδα πάνω από τις ποσοστώσεις τοις εκατό. Στη συνέχεια, εκτελούμε αυτά τα βήματα:

1. Εγγραφή επιλογών για την τιμή μιας γραμμής με αναφορά στο εισόδημα - Κάθε τιμή είναι ένα κελί.
2. Επιλέξτε το εύρος των κυττάρων.

6.

1. Ανοίξτε το παράθυρο πίνακα δεδομένων, όπως κατά την κατάρτιση μιας μόνο μεταβλητής - μέσω της καρτέλας δεδομένων στη γραμμή εργαλείων.
2. Αντικαταστήστε την καταμέτρηση "για να αντικαταστήσετε τις τιμές στις στήλες σε ..." Cell με μια αρχική υψηλή τιμή.
3. Προσθέστε στη στήλη "Για να αντικαταστήσετε τις τιμές στις χορδές στο ..." Cell με ένα αρχικό ενδιαφέρον για τις πωλήσεις ακριβών βιβλίων και κάντε κλικ στο "OK".

Ως αποτέλεσμα, ολόκληρη η πλάκα γεμίζεται με τις ποσότητες πιθανού εισοδήματος με διαφορετικούς όρους πώλησης αγαθών.

7 Επιτάχυνση των υπολογισμών για τα φύλλα που περιέχουν πίνακες δεδομένων

Εάν απαιτούνται γρήγορες υπολογισμοί στην πινακίδα δεδομένων που δεν εκτελούν ολόκληρο τον επανυπολογισμό του βιβλίου, μπορείτε να εκτελέσετε διάφορες ενέργειες για να επιταχύνετε τη διαδικασία.

1. Ανοίξτε το παράθυρο παραμέτρων, επιλέξτε τη ρήτρα "Formula" στο μενού στα δεξιά.
2. Επιλέξτε το στοιχείο "Αυτόματη, εκτός από τους πίνακες δεδομένων" στον ενότητα "Υπολογισμοί στην ενότητα".

οκτώ

1. Εκτελέστε τον επανυπολογισμό των αποτελεσμάτων στην πλάκα με το χέρι. Για αυτό πρέπει να επισημάνετε τους φόρμουλες και να πατήσετε το πλήκτρο F

Άλλα εργαλεία για την εκτέλεση ανάλυσης ευαισθησίας

Το πρόγραμμα έχει άλλα εργαλεία για να βοηθήσει στην ανάλυση ευαισθησίας. Αυτοματοποιούν ορισμένες ενέργειες που διαφορετικά θα πρέπει να γίνουν χειροκίνητα.

1. Η λειτουργία "Επιλογή της παραμέτρου" είναι κατάλληλη εάν το επιθυμητό αποτέλεσμα είναι γνωστό και απαιτείται να μάθετε την τιμή εισόδου της μεταβλητής για να επιτευχθεί ένα τέτοιο αποτέλεσμα.
2. Η "αναζήτηση λύσης" είναι ένα πρόσθετο για την επίλυση προβλημάτων. Είναι απαραίτητο να καθοριστούν περιορισμοί και να τα δείξουμε, μετά την οποία το σύστημα θα βρει την απάντηση. Η λύση προσδιορίζεται με την αλλαγή των τιμών.
3. Η ανάλυση ευαισθησίας μπορεί να πραγματοποιηθεί χρησιμοποιώντας το διαχειριστή σεναρίου. Αυτό το εργαλείο βρίσκεται στο μενού ανάλυσης "Τι γίνεται" στην καρτέλα Δεδομένα. Αντικατάσταση των τιμών σε διάφορα κύτταρα - η ποσότητα μπορεί να φτάσει τα 32. Ο αποστολέας συγκρίνει αυτές τις τιμές και ο χρήστης δεν χρειάζεται να τις αλλάξει χειροκίνητα. Ένα παράδειγμα εφαρμογής διαχειριστή δέσμης ενεργειών:

εννέα

Ανάλυση της ευαισθησίας του επενδυτικού έργου στο Excel

Μέθοδος ανάλυσης ευαισθησίας στον τομέα της επένδυσης

Κατά την ανάλυση "τι γίνεται αν" χρησιμοποιεί το εγχειρίδιο προτομής ή αυτόματο. Γνωστό εύρος τιμών και είναι με τη σειρά τους υποκατεστημένες στον τύπο. Ως αποτέλεσμα, λαμβάνεται ένα σύνολο τιμών. Από αυτά, επιλέξτε μια κατάλληλη εικόνα. Εξετάστε τέσσερις δείκτες για τους οποίους η ανάλυση της ευαισθησίας στον τομέα της χρηματοδότησης:

1. Η καθαρή παρούσα αξία - υπολογίζεται αφαιρώντας το μέγεθος της επένδυσης από το ποσό του εισοδήματος.
2. Ο εσωτερικός ρυθμός κερδοφορίας / κερδών - δηλώνει ποια κέρδη απαιτείται από επενδύσεις για το έτος.
3. Ο λόγος αποπληρωμής είναι ο λόγος όλων των κερδών στην αρχική επένδυση.
4. Ο μειωμένος δείκτης κερδών - υποδεικνύει την αποτελεσματικότητα της επένδυσης.

Τύπος

Η ευαισθησία του εξαρτήματος μπορεί να υπολογιστεί χρησιμοποιώντας αυτόν τον τύπο: αλλάξτε την παράμετρο εξόδου σε% / αλλαγή στην παράμετρο εισόδου σε%.

Η παράμετρος εξόδου και εισόδου μπορεί να είναι οι τιμές που περιγράφονται προηγουμένως.

1. Είναι απαραίτητο να γνωρίζουμε το αποτέλεσμα υπό κανονικές συνθήκες.
2. Αντικαθιστούμε μία από τις μεταβλητές και ακολουθούμε τα αποτελέσματα του αποτελέσματος.
3. Υπολογίστε την ποσοστιαία μεταβολή και στις δύο παραμέτρους σε σχέση με τις καθορισμένες συνθήκες.
4. Τοποθετούμε τα ποσοστά που λαμβάνονται στον τύπο και προσδιορίζουμε την ευαισθησία.

Ένα παράδειγμα ανάλυσης της ευαισθησίας του επενδυτικού έργου στο Excel

Για καλύτερη κατανόηση των τεχνικών ανάλυσης, απαιτείται ένα παράδειγμα. Ας αναλύσουμε το έργο με τέτοια γνωστά δεδομένα:

10

1. Συμπληρώστε το τραπέζι για να αναλύσετε το έργο σε αυτό.

έντεκα

1. Υπολογίστε την ταμειακή ροή χρησιμοποιώντας τη λειτουργία μετατόπισης. Στο αρχικό στάδιο, η ροή είναι ίση με τις επενδύσεις. Στη συνέχεια χρησιμοποιούμε τον τύπο: = αν (μετατόπιση (αριθμός, 1,) = 2; ποσά (εισροή 1: εκροή 1); ποσά (εισροή 1: εκροή 1) + $ b $ 5) Οι ονομασίες των κυττάρων στον τύπο μπορεί Να είστε διαφορετικοί, εξαρτάται από τον πίνακα τοποθέτησης. Στο τέλος, προστίθεται η τιμή από τα αρχικά δεδομένα - η τιμή εκκαθάρισης.

12

1. Ορίζουμε την προθεσμία για την οποία θα αποπληρωθεί το έργο. Για την αρχική περίοδο, χρησιμοποιούμε αυτόν τον τύπο: = Silent (G7: G17; "0; πρώτη d.potok; 0). Το έργο βρίσκεται στο σημείο διάλειμμα για 4 χρόνια.

13

1. Δημιουργήστε μια στήλη για αριθμούς εκείνων των περιόδων όταν το έργο πληρώνει.

δεκατέσσερα

1. Υπολογίστε την κερδοφορία των επενδύσεων. Είναι απαραίτητο να δημιουργηθεί μια έκφραση, όπου το κέρδος σε συγκεκριμένο χρονικό διάστημα χωρίζεται σε αρχικές επενδύσεις.

δεκαπέντε

1. Προσδιορίστε τον συντελεστή προεξόφλησης για αυτόν τον τύπο: = 1 / (1 + δίσκο.%) ^ Αριθμός.

δεκαέξι

1. Υπολογίστε την παρούσα αξία από τον πολλαπλασιασμό - η ταμειακή ροή πολλαπλασιάζεται με το προεξοφλητικό επιτόκιο.

17.

1. Υπολογίστε το PI (δείκτης κερδοφορίας). Η δεδομένη αξία στο τμήμα του χρόνου χωρίζεται σε συνημμένα στην αρχή της ανάπτυξης του έργου.

δεκαοχτώ

1. Ορίζουμε το εσωτερικό ποσοστό των κερδών χρησιμοποιώντας τη λειτουργία του EMD: = FMR (εύρος ροών ταμειακών ροών).

Ανάλυση της ευαισθησίας των επενδύσεων χρησιμοποιώντας τον πίνακα δεδομένων

Για την ανάλυση των έργων στον τομέα των επενδύσεων, άλλες μέθοδοι είναι καλύτερα προσαρμοσμένες από τον πίνακα δεδομένων. Πολλοί χρήστες έχουν σύγχυση κατά την κατάρτιση του τύπου. Για να μάθετε την εξάρτηση ενός παράγοντα από αλλαγές σε άλλους, πρέπει να επιλέξετε τα σωστά κύτταρα υπολογισμού και να διαβάσετε τα δεδομένα.

Ανάλυση παράγοντα και διασποράς στο Excel με αυτοματοποίηση υπολογισμών

Ανάλυση διασποράς στο Excel

Ο σκοπός μιας τέτοιας ανάλυσης είναι η διαίρεση της μεταβλητότητας του μεγέθους τριών κατασκευαστικών στοιχείων:

1. Μεταβλητότητα ως αποτέλεσμα της επίδρασης άλλων τιμών.
2. Αλλαγές λόγω της σχέσης των τιμών που την επηρεάζουν.
3. Τυχαίες αλλαγές.

Εκτελέστε μια ανάλυση διασποράς μέσω ενός πρόσθετου από το Excel Add-On "Ανάλυση δεδομένων". Εάν δεν είναι ενεργοποιημένη, μπορεί να συνδεθεί σε παραμέτρους.

Ο πίνακας εκκίνησης πρέπει να συμμορφώνεται με τους δύο κανόνες: κάθε αξία λογαριασμών για μία στήλη και τα δεδομένα σε αυτό είναι διατεταγμένα σε αύξουσα ή φθίνουσα. Είναι απαραίτητο να ελεγχθεί ο αντίκτυπος του επιπέδου εκπαίδευσης στη συμπεριφορά σε σύγκρουση.

δεκαεννέα

1. Βρίσκουμε το εργαλείο "δεδομένων" δεδομένων "δεδομένων" και ανοίγει το παράθυρό του. Ο κατάλογος πρέπει να επιλέξει ανάλυση διασποράς ενός παράγοντα.

είκοσι

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. Διάστημα εισόδου - Κύτταρα όπου περιέχονται δεδομένα (χωρίς καπέλο). Φέρνουμε αποτελέσματα στο νέο φύλλο και κάντε κλικ στο "OK".

27.

Η ένδειξη F είναι μεγαλύτερη από την κρίσιμη F - αυτό σημαίνει ότι το πάτωμα επηρεάζει τον ρυθμό αντίδρασης στον ήχο.

28.

συμπέρασμα

Αυτό το άρθρο περιγράφει λεπτομερώς την ανάλυση ευαισθησίας στον επεξεργαστή τραπεζιών του Excel, έτσι ώστε κάθε χρήστης να μπορεί να καταλάβει τις μεθόδους χρήσης του.

Μήνυμα Η ανάλυση ευαισθησίας στο Excel (πίνακας δεδομένων δείγματος) εμφανίστηκε πρώτα στην τεχνολογία της πληροφορικής.