Excel Statistical Functions: Tutorial with Examples, Μέρος Τρίτο

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

Σε αυτό το άρθρο θα εμβαθύνουμε στις στατιστικές συναρτήσεις του Excel για τον υπολογισμό των συναρτήσεων γραμμής τάσης.

Λάβετε υπόψη ότι ορισμένες στατιστικές συναρτήσεις εισήχθησαν σε πρόσφατες εκδόσεις του Excel και επομένως δεν είναι διαθέσιμες σε παλαιότερες εκδόσεις.

Εκτιμώμενος χρόνος ανάγνωσης: 12 λεπτά

Λειτουργίες γραμμής τάσης

Forecast

Η συνάρτηση Forecast του Excel προβλέπει ένα μελλοντικό σημείο σε μια γραμμική γραμμή τάσης που ταιριάζει σε ένα δεδομένο σύνολο τιμών x και y.

σύνταξη

= FORECAST( x, known_y's, known_x's )

μαθήματα

• x: Μια αριθμητική τιμή x για την οποία θέλετε να προβλέψετε μια νέα τιμή y.
• known_y's: Ένας πίνακας γνωστών τιμών y
• known_x's: Πίνακας γνωστών x τιμών

Σημειώστε ότι το μήκος του πίνακα των known_x πρέπει να είναι το ίδιο με αυτό του known_y και η διακύμανση του known_x δεν χρειάζεται να είναι μηδέν.

Παράδειγμα

Στο παρακάτω υπολογιστικό φύλλο, η συνάρτηση FORECAST Το Excel χρησιμοποιείται για την πρόβλεψη ενός επιπλέον σημείου κατά μήκος της ευθύγραμμης γραμμής που ταιριάζει καλύτερα μέσω μιας σειράς γνωστών τιμών x και y (αποθηκευμένες στα κελιά F2:F7 και G2:G7).

Όπως φαίνεται στο κελί F7 του υπολογιστικού φύλλου, η συνάρτηση για τον υπολογισμό της αναμενόμενης τιμής y στο x=7 είναι :=FORECAST( 7, G2:G7, F2:F7 )

Αυτό δίνει το αποτέλεσμα 32.666667 .

Intercept

Μεταξύ των συναρτήσεων πρόβλεψης του Excel βρίσκουμε το Intercept. Η συνάρτηση Intercept του Excel υπολογίζει την τομή (την τιμή στην τομή του άξονα y) της γραμμής γραμμικής παλινδρόμησης σε ένα δεδομένο σύνολο τιμών x και y.

σύνταξη

= INTERCEPT( known_y's, known_x's )

μαθήματα

• known_y's: Ένας πίνακας γνωστών τιμών y
• known_x's: Πίνακας γνωστών x τιμών

Σημειώστε ότι το μήκος του πίνακα των known_x πρέπει να είναι το ίδιο με αυτό του known_y και η διακύμανση του known_x δεν χρειάζεται να είναι μηδέν.

Παράδειγμα

Το παρακάτω υπολογιστικό φύλλο δείχνει ένα παράδειγμα της συνάρτησης Intercept του Excel που χρησιμοποιείται για τον υπολογισμό του σημείου όπου η γραμμή γραμμικής παλινδρόμησης διασχίζει το known_x και known_y (αναφέρεται στα κελιά F2:F7 και G2:G7) τέμνει τον άξονα y.

Le known_x και known_y απεικονίζονται στο γράφημα του υπολογιστικού φύλλου.

Όπως φαίνεται στο κελί F9 του υπολογιστικού φύλλου, ο τύπος για τη συνάρτηση Intercept είναι :=INTERCEPT( G2:G7, F2:F7 )

που δίνει το αποτέλεσμα 2.4 .

Slope

Μια άλλη πολύ ενδιαφέρουσα συνάρτηση πρόβλεψης είναι η κλίση (Slope) Το Excel υπολογίζει την κλίση της γραμμής γραμμικής παλινδρόμησης μέσω ενός δεδομένου συνόλου τιμών x και y.

Η σύνταξη της συνάρτησης είναι:

σύνταξη

= SLOPE( known_y's, known_x's )

μαθήματα

• known_y's: Ένας πίνακας γνωστών τιμών y
• known_x's: Πίνακας γνωστών x τιμών

Σημειώστε ότι το μήκος του πίνακα των known_x πρέπει να είναι το ίδιο με αυτό του known_y και η διακύμανση του known_x δεν χρειάζεται να είναι μηδέν.

Παράδειγμα

Το παρακάτω υπολογιστικό φύλλο δείχνει ένα παράδειγμα της συνάρτησης Slope (κλίση) του Excel που χρησιμοποιείται για τον υπολογισμό της κλίσης της γραμμής γραμμικής παλινδρόμησης μέσω του known_x και το known_y, στα κελιά F2:F7 και G2:G7.

Le known_x και known_y απεικονίζονται στο γράφημα του υπολογιστικού φύλλου.

Όπως φαίνεται στο κελί F9 του υπολογιστικού φύλλου, ο τύπος για τη συνάρτηση Intercept είναι :=SLOPE( G2:G7, F2:F7 )

που δίνει το αποτέλεσμα 4.628571429.

Trend

Μια πολύ ενδιαφέρουσα συνάρτηση πρόβλεψης του Excel είναι η ΤΑΣΗ Το Excel (Trend) υπολογίζει τη γραμμική γραμμή τάσης μέσω ενός δεδομένου συνόλου τιμών y και (προαιρετικά), ενός δεδομένου συνόλου τιμών x.

Στη συνέχεια, η συνάρτηση επεκτείνει τη γραμμή γραμμικής τάσης για να υπολογίσει πρόσθετες τιμές y για ένα επιπλέον σύνολο νέων τιμών x.

Η σύνταξη της συνάρτησης είναι:

σύνταξη

= TREND( known_y's, [known_x's], [new_x's], [const] )

μαθήματα

• known_y's: Ένας πίνακας γνωστών τιμών y
• [known_x's]: Ένας ή περισσότεροι πίνακες γνωστών τιμών x. Αυτό είναι ένα προαιρετικό όρισμα που, εάν παρέχεται, θα πρέπει να έχει το ίδιο μήκος με το σύνολο known_y's. Εάν παραλειφθεί, το σύνολο των [known_x's] παίρνει την τιμή {1, 2, 3, …}.
• [new_x's]: Ένα προαιρετικό όρισμα, που παρέχει έναν ή περισσότερους πίνακες αριθμητικών τιμών που αντιπροσωπεύουν ένα σύνολο νέων x-τιμών, για τις οποίες θέλετε να υπολογίσετε τις αντίστοιχες νέες τιμές y. Κάθε πίνακας [new_x's] θα πρέπει να ταιριάζει με έναν πίνακα [known_x's]. Αν το επιχείρημα [new_x's] παραλείπεται, ορίζεται σε [known_x's] .
• [κόστος]: Ένα προαιρετικό λογικό όρισμα που προσδιορίζει εάν η σταθερά 'b', στη γραμμική εξίσωση y = m x + b , πρέπει να αναγκαστεί να είναι ίσο με μηδέν. αυτος [κόστος] είναι TRUE (ή αν αυτό το όρισμα παραλειφθεί) η σταθερά b αντιμετωπίζεται κανονικά.
• αυτος [κόστος] είναι FALSE η σταθερά b ορίζεται στο 0 και η ευθύγραμμη εξίσωση γίνεται y = mx .

Παράδειγμα

Στο παρακάτω υπολογιστικό φύλλο, η συνάρτηση Excel Trend χρησιμοποιείται για την επέκταση μιας σειράς τιμών x και y που βρίσκονται στην ευθεία γραμμή y = 2x + 10. Οι γνωστές τιμές x και y αποθηκεύονται στα κελιά A2-B5 του το υπολογιστικό φύλλο και εμφανίζονται επίσης στο γράφημα του υπολογιστικού φύλλου.

Σημειώστε ότι δεν είναι απαραίτητο τα σημεία που δίνονται να ταιριάζουν ακριβώς κατά μήκος της ευθείας y = 2x + 10 (αν και σε αυτό το παράδειγμα ταιριάζουν). Η συνάρτηση Trend του Excel θα βρει την καλύτερη γραμμή για κάθε σύνολο τιμών που παρέχετε.

Η συνάρτηση Trend χρησιμοποιεί τη μέθοδο των ελαχίστων τετραγώνων για να βρει την καλύτερη γραμμή προσαρμογής και στη συνέχεια τη χρησιμοποιεί για να υπολογίσει τις νέες τιμές y για τις νέες τιμές x που παρέχονται.

Σε αυτό το παράδειγμα, οι τιμές του [new_x's] αποθηκεύονται στα κελιά A8-A10 και η συνάρτηση Trend του Excel έχει χρησιμοποιηθεί, στα κελιά B8-B10, για την εύρεση των νέων αντίστοιχων τιμών y. Όπως φαίνεται στη γραμμή τύπων, ο τύπος είναι := ΤΑΣΗ( B2:B5, A2:A5, A8:A10 )

Βλέπετε ότι η συνάρτηση Trend στη γραμμή τύπων περικλείεται σε αγκύλες { }. Αυτό υποδηλώνει ότι η συνάρτηση εισήχθη ως τύπος πίνακα .

Growth

Μεταξύ των συναρτήσεων πρόβλεψης του Excel βρίσκουμε το Growth. Η λειτουργία Growth Το Excel υπολογίζει την καμπύλη εκθετικής ανάπτυξης μέσω ενός δεδομένου συνόλου τιμών y και (προαιρετικά), ενός ή περισσότερων συνόλων τιμών x. Στη συνέχεια, η συνάρτηση επεκτείνει την καμπύλη για να υπολογίσει πρόσθετες τιμές y για ένα επιπλέον σύνολο νέων τιμών x.

Η σύνταξη της συνάρτησης είναι:

σύνταξη

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

μαθήματα

• known_y's: Ένας πίνακας γνωστών τιμών y
• [known_x's]: Ένας ή περισσότεροι πίνακες γνωστών τιμών x. Αυτό είναι ένα προαιρετικό όρισμα που, εάν παρέχεται, θα πρέπει να έχει το ίδιο μήκος με το σύνολο known_y's. Εάν παραλειφθεί, το σύνολο των [known_x's] παίρνει την τιμή {1, 2, 3, …}.
• [new_x's]: Ένα σύνολο νέων τιμών x, για τις οποίες η συνάρτηση υπολογίζει τις αντίστοιχες νέες τιμές y. Εάν παραλειφθεί, θεωρείται ότι το σύνολο των [new_x's] είναι ίσο με αυτό του [known_x's] και η συνάρτηση επιστρέφει τις τιμές y που βρίσκονται στην υπολογισμένη καμπύλη εκθετικής ανάπτυξης.
• [κόστος]: Ένα προαιρετικό λογικό όρισμα που προσδιορίζει εάν η σταθερά 'b', στη γραμμική εξίσωση y = b * m^x , πρέπει να αναγκαστεί να είναι ίσο με 1. Εάν [κόστος] είναι TRUE (ή αν αυτό το όρισμα παραλειφθεί) η σταθερά b αντιμετωπίζεται κανονικά. αυτος [κόστος] είναι FALSE η σταθερά b ορίζεται στο 1 και η ευθύγραμμη εξίσωση γίνεται y = mx .

Παράδειγμα

Στο παρακάτω υπολογιστικό φύλλο, η συνάρτηση ανάπτυξης Excel χρησιμοποιείται για την επέκταση μιας σειράς τιμών x και y που βρίσκονται στην καμπύλη εκθετικής ανάπτυξης y = 5 * 2^x. Αυτά αποθηκεύονται στα κελιά A2-B5 του υπολογιστικού φύλλου και εμφανίζονται επίσης στο γράφημα υπολογιστικού φύλλου.

Η συνάρτηση Growth υπολογίζει την καμπύλη εκθετικής ανάπτυξης που ταιριάζει καλύτερα στις γνωστές τιμές x και y που παρέχονται. Σε αυτό το απλό παράδειγμα, η καλύτερη προσαρμοσμένη καμπύλη είναι η εκθετική καμπύλη y = 5 * 2^x.

Μόλις το Excel υπολογίσει την εξίσωση της εκθετικής καμπύλης ανάπτυξης, μπορεί να τη χρησιμοποιήσει για να υπολογίσει τις νέες τιμές y για τις νέες τιμές x που παρέχονται στα κελιά A8-A10.

Σε αυτό το παράδειγμα, οι τιμές του [new_x's] αποθηκεύονται στα κελιά A8-A10 και η συνάρτηση Growth του Excel έχει εισαχθεί στα κελιά B8-B10. Όπως φαίνεται στη γραμμή τύπων, ο τύπος για αυτό είναι:=Growth( B2:B5, A2:A5, A8:A10 )

Μπορείτε να δείτε ότι η συνάρτηση Growth στη γραμμή τύπων περικλείεται σε αγκύλες { }. Αυτό υποδηλώνει ότι η συνάρτηση εισήχθη ως τύπος πίνακα .

Σημειώστε ότι αν και τα σημεία στο παραπάνω παράδειγμα ταιριάζουν ακριβώς κατά μήκος της καμπύλης y = 5 * 2^x, αυτό δεν είναι απαραίτητο. Η λειτουργία Growth Το Excel θα βρει την καμπύλη που ταιριάζει καλύτερα για κάθε σύνολο τιμών που παρέχετε.

Οικονομικές λειτουργίες

Αποτέλεσμα

Η λειτουργία Effect Το Excel επιστρέφει το πραγματικό ετήσιο επιτόκιο για ένα δεδομένο ονομαστικό επιτόκιο και έναν δεδομένο αριθμό περιόδων ανατοκισμού ανά έτος.

Πραγματικό ετήσιο επιτόκιο

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

Το πραγματικό ετήσιο επιτόκιο υπολογίζεται χρησιμοποιώντας την ακόλουθη εξίσωση:

περιστέρι nominal_rate είναι το ονομαστικό επιτόκιο e npery είναι ο αριθμός των περιόδων ανάμιξης ανά έτος.

Η σύνταξη της συνάρτησης είναι:

σύνταξη

= EFFECT( nominal_rate, npery )

μαθήματα

• nominal_rate: Το ονομαστικό επιτόκιο (πρέπει να είναι μια αριθμητική τιμή μεταξύ 0 και 1)
• npery: Ο αριθμός των περιόδων σύνθεσης ανά έτος (πρέπει να είναι θετικός ακέραιος).

Παράδειγμα

Το ακόλουθο υπολογιστικό φύλλο δείχνει τρία παραδείγματα της συνάρτησης Excel Effect:

Αν το αποτέλεσμα της συνάρτησης Effect εμφανίζεται ως δεκαδικό ή δείχνει 0%, και τα δύο αυτά ζητήματα είναι πιθανό να οφείλονται στη μορφοποίηση του κελιού που περιέχει τη συνάρτηση Effect.

Επομένως το πρόβλημα μπορεί να λυθεί μορφοποιώντας το κελί σε ποσοστό, με δεκαδικά ψηφία.

Για να γινει αυτο:

1. Επιλέξτε τα κελιά για μορφοποίηση ως ποσοστό.
2. Ανοίξτε το πλαίσιο διαλόγου "Μορφοποίηση κελιών" χρησιμοποιώντας μία από τις ακόλουθες μεθόδους:
   • Κάντε δεξί κλικ στο επιλεγμένο κελί ή περιοχή και επιλέξτε την επιλογή Μορφοποίηση κελιών… από το μενού περιβάλλοντος.
   • Κάντε κλικ στην Εκκίνηση του πλαισίου διαλόγου στην ομαδοποίηση αριθμών στην καρτέλα Αρχική Κορδέλα Excel?
   • Χρησιμοποιήστε τη συντόμευση πληκτρολογίου CTRL-1 (δηλαδή επιλέξτε το πλήκτρο CTRL και, κρατώντας το πατημένο, επιλέξτε το πλήκτρο «1» (ένα)).
3. Στο πλαίσιο διαλόγου "Μορφοποίηση κελιών":
   • Βεβαιωθείτε ότι η κάρτα Αριθμός στο επάνω μέρος του πλαισίου διαλόγου είναι επιλεγμένο.
   • Επιλέξτε Ποσοστό από τη λίστα Κατηγορία στην αριστερή πλευρά του πλαισίου διαλόγου .Αυτό θα εμφανίσει πρόσθετες επιλογές στη δεξιά πλευρά του πλαισίου ελέγχου, επιτρέποντάς σας να επιλέξετε τον αριθμό των δεκαδικών ψηφίων που θέλετε να εμφανίζονται.
   • Αφού επιλέξετε τον αριθμό των δεκαδικών ψηφίων που θέλετε να εμφανίζονται, κάντε κλικ OK .

Nominal

Η λειτουργία Nominal Το Excel επιστρέφει το ονομαστικό επιτόκιο για ένα δεδομένο πραγματικό επιτόκιο και έναν δεδομένο αριθμό περιόδων ανατοκισμού ανά έτος.

Η σύνταξη της συνάρτησης είναι:

σύνταξη

= NOMINAL( effect_rate, npery )

μαθήματα

• effect_rate: Το πραγματικό επιτόκιο (αριθμητική τιμή μεταξύ 0 και 1).
• npery: Ο αριθμός των περιόδων σύνθεσης ανά έτος (πρέπει να είναι θετικός ακέραιος).

Παράδειγμα

Στο παρακάτω υπολογιστικό φύλλο, η συνάρτηση Nominal του Excel χρησιμοποιείται για τον υπολογισμό του ονομαστικού επιτοκίου τριών δανείων με διαφορετικούς όρους.

Ercole Palmeri