Ανάλυση συσχέτισης στο Excel. Ένα παράδειγμα εκτέλεσης ανάλυσης συσχέτισης

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

Η ουσία της ανάλυσης συσχέτισης

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

Σκοπός ανάλυσης συσχέτισης

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

Σημαντικό! Στον 0ο συντελεστή, δεν υπάρχει σχέση μεταξύ των ποσοτήτων.

Υπολογισμός του συντελεστή συσχέτισης

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

Μέθοδος 1: Προσδιορισμός συσχέτισης μέσω του οδηγού συναρτήσεων

CORREL – μια συνάρτηση που σας επιτρέπει να εφαρμόσετε μια ανάλυση συσχέτισης. Γενική φόρμα - CORREL(massiv1;massiv2). Λεπτομερείς οδηγίες:

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

2. Ανοίγει ο Οδηγός λειτουργιών. Εδώ πρέπει να βρείτε ΚΟΡΕΛ, κάντε κλικ σε αυτό και μετά στο "OK".

3. Ανοίγει το παράθυρο ορισμάτων. Στη γραμμή "Array1" πρέπει να εισαγάγετε τις συντεταγμένες των διαστημάτων της 1ης από τις τιμές. Σε αυτό το παράδειγμα, αυτή είναι η στήλη Αξία πωλήσεων. Απλά πρέπει να επιλέξετε όλα τα κελιά που βρίσκονται σε αυτήν τη στήλη. Ομοίως, πρέπει να προσθέσετε τις συντεταγμένες της δεύτερης στήλης στη γραμμή "Array2". Στο παράδειγμά μας, αυτή είναι η στήλη Κόστος διαφήμισης.

4. Αφού εισαγάγετε όλα τα εύρη, κάντε κλικ στο κουμπί "OK".

Ο συντελεστής εμφανίστηκε στο κελί που υποδεικνύονταν στην αρχή των ενεργειών μας. Το αποτέλεσμα που προκύπτει είναι 0,97. Αυτός ο δείκτης αντικατοπτρίζει την υψηλή εξάρτηση της πρώτης τιμής από τη δεύτερη.

Μέθοδος 2: Υπολογισμός συσχέτισης χρησιμοποιώντας το Analysis ToolPak

Υπάρχει μια άλλη μέθοδος για τον προσδιορισμό της συσχέτισης. Εδώ χρησιμοποιείται μία από τις συναρτήσεις που βρίσκονται στο πακέτο ανάλυσης. Πριν το χρησιμοποιήσετε, πρέπει να ενεργοποιήσετε το εργαλείο. Αναλυτικές οδηγίες:

1. Μεταβείτε στην ενότητα "Αρχείο".

2. Θα ανοίξει ένα νέο παράθυρο, στο οποίο πρέπει να κάνετε κλικ στην ενότητα "Ρυθμίσεις".
3. Κάντε κλικ στο «Πρόσθετα».
4. Βρίσκουμε το στοιχείο "Διαχείριση" στο κάτω μέρος. Εδώ πρέπει να επιλέξετε "Πρόσθετα Excel" από το μενού περιβάλλοντος και να κάνετε κλικ στο "OK".

5. Ένα ειδικό παράθυρο πρόσθετων έχει ανοίξει. Τοποθετήστε ένα σημάδι επιλογής δίπλα στο στοιχείο "Πακέτο ανάλυσης". Κάνουμε κλικ στο "OK".
6. Η ενεργοποίηση ήταν επιτυχής. Τώρα ας πάμε στα Δεδομένα. Εμφανίστηκε το μπλοκ "Analysis", στο οποίο πρέπει να κάνετε κλικ στο "Data Analysis".
7. Στο νέο παράθυρο που εμφανίζεται, επιλέξτε το στοιχείο «Συσχέτιση» και κάντε κλικ στο «ΟΚ».

8. Το παράθυρο ρυθμίσεων ανάλυσης εμφανίστηκε στην οθόνη. Στη γραμμή "Διάστημα εισαγωγής" είναι απαραίτητο να εισαγάγετε το εύρος απολύτως όλων των στηλών που συμμετέχουν στην ανάλυση. Σε αυτό το παράδειγμα, αυτές είναι οι στήλες "Αξία πωλήσεων" και "Κόστος διαφήμισης". Οι ρυθμίσεις εμφάνισης εξόδου ορίζονται αρχικά σε Νέο φύλλο εργασίας, πράγμα που σημαίνει ότι τα αποτελέσματα θα εμφανίζονται σε διαφορετικό φύλλο. Προαιρετικά, μπορείτε να αλλάξετε τη θέση εξόδου του αποτελέσματος. Αφού κάνετε όλες τις ρυθμίσεις, κάντε κλικ στο "OK".

Έφυγαν οι τελικές βαθμολογίες. Το αποτέλεσμα είναι το ίδιο όπως στην πρώτη μέθοδο - 0,97.

Ορισμός και υπολογισμός πολλαπλού συντελεστή συσχέτισης στο MS Excel

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

Λεπτομερής οδηγός:

1. Στην ενότητα "Δεδομένα", βρίσκουμε το ήδη γνωστό μπλοκ "Ανάλυση" και κάνουμε κλικ στο "Ανάλυση δεδομένων".

2. Στο παράθυρο που εμφανίζεται, κάντε κλικ στο στοιχείο «Συσχέτιση» και κάντε κλικ στο «ΟΚ».
3. Στη γραμμή "Input interval" οδηγούμε στο διάστημα για τρεις ή περισσότερες στήλες του πίνακα προέλευσης. Το εύρος μπορεί να εισαχθεί χειροκίνητα ή απλά να το επιλέξετε με το LMB και θα εμφανιστεί αυτόματα στην επιθυμητή γραμμή. Στην «Ομαδοποίηση» επιλέξτε την κατάλληλη μέθοδο ομαδοποίησης. Στο "Output Parameter" καθορίζει τη θέση όπου θα εμφανίζονται τα αποτελέσματα συσχέτισης. Κάνουμε κλικ στο "OK".

4. Ετοιμος! Κατασκευάστηκε ο πίνακας συσχέτισης.

Συντελεστής συσχέτισης ζεύγους στο Excel

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

Υπολογισμός συντελεστή συσχέτισης ζεύγους στο Excel

Για παράδειγμα, έχετε τιμές x και y.

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

1. Ας βρούμε τις μέσες τιμές χρησιμοποιώντας τη συνάρτηση ΚΑΡΔΙΑ.

2. Ας υπολογίσουμε το καθένα х и xavg, у и avg χρησιμοποιώντας τον τελεστή «-».

3. Πολλαπλασιάζουμε τις υπολογιζόμενες διαφορές.

4. Υπολογίζουμε το άθροισμα των δεικτών αυτής της στήλης. Ο αριθμητής είναι το αποτέλεσμα που βρέθηκε.

5. Να υπολογίσετε τους παρονομαστές της διαφοράς х и x-μέσος όρος, y и y-μέσο. Για να γίνει αυτό, θα εκτελέσουμε τον τετραγωνισμό.

6. Χρήση της λειτουργίας AUTOSUMMA, βρείτε τους δείκτες στις στήλες που προκύπτουν. Κάνουμε πολλαπλασιασμό. Χρησιμοποιώντας τη λειτουργία ROOT τετράγωνο το αποτέλεσμα.

7. Υπολογίζουμε το πηλίκο χρησιμοποιώντας τις τιμές του παρονομαστή και του αριθμητή.

8. Το CORREL είναι μια ενσωματωμένη λειτουργία που σας επιτρέπει να αποτρέπετε πολύπλοκους υπολογισμούς. Πηγαίνουμε στον "Οδηγό λειτουργιών", επιλέγουμε CORREL και καθορίζουμε τις συστοιχίες ενδείξεων х и у. Κατασκευάζουμε ένα γράφημα που εμφανίζει τις τιμές που λαμβάνονται.

Matrix of Pairwise Correlation Coefficients στο Excel

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

Βήμα προς βήμα οδηγίες:

1. Πηγαίνουμε στην "Ανάλυση δεδομένων", που βρίσκεται στο μπλοκ "Ανάλυση" της καρτέλας "Δεδομένα". Επιλέξτε Συσχέτιση από τη λίστα που εμφανίζεται.
2. Ρυθμίζουμε όλες τις απαραίτητες ρυθμίσεις. "Input interval" - το διάστημα και των τεσσάρων στηλών. "Διάστημα εξόδου" - το μέρος όπου θέλουμε να εμφανίσουμε τα σύνολα. Κάνουμε κλικ στο κουμπί "OK".
3. Ένας πίνακας συσχέτισης κατασκευάστηκε στο επιλεγμένο μέρος. Κάθε τομή μιας γραμμής και μιας στήλης είναι ένας συντελεστής συσχέτισης. Ο αριθμός 1 εμφανίζεται όταν ταιριάζουν οι συντεταγμένες.

Η συνάρτηση CORREL για τον προσδιορισμό της σχέσης και της συσχέτισης στο Excel

CORREL – συνάρτηση που χρησιμοποιείται για τον υπολογισμό του συντελεστή συσχέτισης μεταξύ 2 πινάκων. Ας δούμε τέσσερα παραδείγματα όλων των ικανοτήτων αυτής της λειτουργίας.

Παραδείγματα χρήσης της συνάρτησης CORREL στο Excel

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

Ο αλγόριθμος υπολογισμού μοιάζει με αυτό:

Η βαθμολογία που εμφανίζεται είναι κοντά στο 1. Αποτέλεσμα:

Προσδιορισμός του συντελεστή συσχέτισης της επίδρασης των ενεργειών στο αποτέλεσμα

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

Χρησιμοποιώντας τους παρακάτω τύπους, υπολογίζουμε τον συντελεστή συσχέτισης:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

Αποτελέσματα:

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

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

Τρίτο παράδειγμα. Ένα άτομο για να προωθήσει τα δικά του βίντεο στη φιλοξενία βίντεο YouTube χρησιμοποιεί κοινωνικά δίκτυα για να διαφημίσει το κανάλι. Παρατηρεί ότι υπάρχει κάποια σχέση μεταξύ του αριθμού των αναδημοσιεύσεων στα κοινωνικά δίκτυα και του αριθμού των προβολών στο κανάλι. Είναι δυνατόν να προβλεφθεί η μελλοντική απόδοση χρησιμοποιώντας εργαλεία υπολογιστικών φύλλων; Είναι απαραίτητο να προσδιοριστεί η εύλογη εφαρμογή της εξίσωσης γραμμικής παλινδρόμησης για την πρόβλεψη του αριθμού των προβολών βίντεο ανάλογα με τον αριθμό των αναδημοσιεύσεων. Πίνακας με τιμές:

Τώρα είναι απαραίτητο να προσδιοριστεί η ύπαρξη σχέσης μεταξύ 2 δεικτών σύμφωνα με τον παρακάτω τύπο:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Ισχυρή άμεση σχέση";"Ισχυρή αντίστροφη σχέση");"Αδύναμη ή καμία σχέση")' class='formula'>

Εάν ο συντελεστής που προκύπτει είναι μεγαλύτερος από 0,7, τότε είναι πιο κατάλληλο να χρησιμοποιηθεί η συνάρτηση γραμμικής παλινδρόμησης. Σε αυτό το παράδειγμα, κάνουμε:

Τώρα κατασκευάζουμε ένα γράφημα:

Εφαρμόζουμε αυτήν την εξίσωση για να προσδιορίσουμε τον αριθμό των προβολών σε 200, 500 και 1000 κοινοποιήσεις: =9,2937*D4-206,12. Παίρνουμε τα ακόλουθα αποτελέσματα:

Λειτουργία ΠΡΟΒΛΕΨΗ σας επιτρέπει να προσδιορίσετε τον αριθμό των προβολών τη δεδομένη στιγμή, εάν υπήρχαν, για παράδειγμα, διακόσιες πενήντα αναδημοσιεύσεις. Εφαρμόζουμε: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Οι τιμές δεν σχετίζονται")' class='formula'>. Παίρνουμε τα ακόλουθα αποτελέσματα:

Δυνατότητες χρήσης της συνάρτησης CORREL στο Excel

Αυτή η λειτουργία έχει τα ακόλουθα χαρακτηριστικά:

1. Τα άδεια κελιά δεν λαμβάνονται υπόψη.
2. Τα κελιά που περιέχουν πληροφορίες Boolean και τύπου κειμένου δεν λαμβάνονται υπόψη.
3. Η διπλή άρνηση "-" χρησιμοποιείται για να ληφθούν υπόψη οι λογικές τιμές με τη μορφή αριθμών.
4. Ο αριθμός των κελιών στους πίνακες που μελετήθηκαν πρέπει να ταιριάζει, διαφορετικά θα εμφανιστεί το μήνυμα #N/A.

Εκτίμηση της στατιστικής σημαντικότητας του συντελεστή συσχέτισης

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

Συμπέρασμα

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