10 Βασικές λειτουργίες του Microsoft Excel για την ανάλυση δεδομένων
Miscellanea / / November 29, 2021
1. ΣΥΝΕΝΤΑΣΗ
Το =CONCATENATE είναι μια από τις πιο κρίσιμες λειτουργίες για την ανάλυση δεδομένων καθώς σας επιτρέπει να συνδυάσετε κείμενο, αριθμούς, ημερομηνίες κ.λπ. από πολλά κελιά σε ένα. Η συνάρτηση είναι ιδιαίτερα χρήσιμη για το συνδυασμό δεδομένων από διαφορετικά κελιά σε ένα μόνο κελί. Για παράδειγμα, είναι χρήσιμο για τη δημιουργία των παραμέτρων παρακολούθησης για καμπάνιες μάρκετινγκ, τη δημιουργία ερωτημάτων API, την προσθήκη κειμένου σε μορφή αριθμού και πολλά άλλα πράγματα.
![Ανάλυση δεδομένων βασικών λειτουργιών του Excel 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
Στο παραπάνω παράδειγμα, ήθελα τον μήνα και τις πωλήσεις μαζί σε μία στήλη. Για αυτό, χρησιμοποίησα τον τύπο =CONCATENATE(A2, B2) στο κελί C2 για να πάρω ως αποτέλεσμα 700 $ Jan.
Τύπος: =ΣΥΝΔΕΞΗ(κύτταρα που θέλετε να συνδυάσετε)
2. ΛΕΝ
Το =LEN είναι μια άλλη εύχρηστη λειτουργία για την ανάλυση δεδομένων που ουσιαστικά εξάγει τον αριθμό των χαρακτήρων σε κάθε δεδομένο κελί. Η συνάρτηση μπορεί να χρησιμοποιηθεί κυρίως κατά τη δημιουργία ετικετών τίτλου ή περιγραφών που έχουν όριο χαρακτήρων. Μπορεί επίσης να είναι χρήσιμο όταν προσπαθείτε να ανακαλύψετε τις διαφορές μεταξύ διαφορετικών μοναδικών αναγνωριστικών που συχνά είναι αρκετά μεγάλα και δεν έχουν τη σωστή σειρά.
![Ανάλυση δεδομένων βασικών λειτουργιών του Excel 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
Στο παραπάνω παράδειγμα, ήθελα να μετρήσω τα νούμερα για τον αριθμό των προβολών που λάμβανα κάθε μήνα. Για αυτό, χρησιμοποίησα τον τύπο =LEN(C2) στο κελί D2 για να πάρω το 5 ως αποτέλεσμα.
Τύπος: =LEN(κύτταρο)
3. VLOOKUP
=Το VLOOKUP είναι ίσως μια από τις πιο αναγνωρίσιμες λειτουργίες για όποιον γνωρίζει την ανάλυση δεδομένων. Μπορείτε να το χρησιμοποιήσετε για να αντιστοιχίσετε δεδομένα από έναν πίνακα με μια τιμή εισόδου. Η λειτουργία προσφέρει δύο τρόπους αντιστοίχισης — ακριβή και κατά προσέγγιση, η οποία ελέγχεται από το εύρος της αναζήτησης. Εάν ορίσετε το εύρος σε FALSE, θα αναζητήσει μια ακριβή αντιστοίχιση, αλλά αν το ορίσετε σε TRUE, θα αναζητήσει μια κατά προσέγγιση αντιστοίχιση.
![Ανάλυση δεδομένων βασικών λειτουργιών του Excel 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
Στο παραπάνω παράδειγμα, ήθελα να αναζητήσω τον αριθμό των προβολών σε έναν συγκεκριμένο μήνα. Για αυτό, χρησιμοποίησα τον τύπο =VLOOKUP("Jun", A2:C13, 3) στο κελί G4 και πήρα το 74992 ως αποτέλεσμα. Εδώ, "Jun" είναι η τιμή αναζήτησης, A2:C13 είναι ο πίνακας πίνακα στον οποίο αναζητώ "Jun" και 3 είναι ο αριθμός της στήλης στην οποία ο τύπος θα βρει τις αντίστοιχες προβολές για τον Ιούνιο.
Το μόνο μειονέκτημα της χρήσης αυτής της συνάρτησης είναι ότι λειτουργεί μόνο με δεδομένα που έχουν ταξινομηθεί σε στήλες, εξ ου και το όνομα — κατακόρυφη αναζήτηση. Επομένως, εάν έχετε τακτοποιήσει τα δεδομένα σας σε σειρές, θα πρέπει πρώτα να το κάνετε μεταφέρετε τις γραμμές σε στήλες.
Τύπος: =VLOOKUP(αναζήτηση_τιμής, πίνακας_πίνακας, αριθμός_δείκτης_αριθμός, [αναζήτηση_περιοχής])
4. ΕΥΡΕΤΗΣ/ΑΓΩΝΑΣ
Όπως και η συνάρτηση VLOOKUP, οι συναρτήσεις INDEX και MATCH είναι χρήσιμες για την αναζήτηση συγκεκριμένων δεδομένων με βάση μια τιμή εισόδου. Το INDEX και το MATCH, όταν χρησιμοποιούνται μαζί, μπορούν να ξεπεράσουν τους περιορισμούς του VLOOKUP για την παροχή λανθασμένων αποτελεσμάτων (αν δεν είστε προσεκτικοί). Έτσι, όταν συνδυάζετε αυτές τις δύο συναρτήσεις, μπορούν να εντοπίσουν με ακρίβεια την αναφορά δεδομένων και να αναζητήσουν μια τιμή σε έναν πίνακα μεμονωμένων διαστάσεων. Αυτό επιστρέφει τις συντεταγμένες των δεδομένων ως αριθμό.
![Ανάλυση δεδομένων βασικών λειτουργιών του Excel 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
Στο παραπάνω παράδειγμα, ήθελα να αναζητήσω τον αριθμό των προβολών τον Ιανουάριο. Για αυτό, χρησιμοποίησα τον τύπο =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Εδώ, το A2:C13 είναι η στήλη δεδομένων που θέλω να επιστρέψει ο τύπος, το "Jan" είναι η τιμή που θέλω να αντιστοιχίσω, το A2:A13 είναι το στήλη στην οποία ο τύπος θα βρει "Jan" και το 0 σημαίνει ότι θέλω ο τύπος να βρει μια ακριβή αντιστοίχιση για το αξία.
Εάν θέλετε να βρείτε ένα κατά προσέγγιση ταίριασμα, θα πρέπει να αντικαταστήσετε το 0 με 1 ή -1. Έτσι, το 1 θα βρει τη μεγαλύτερη τιμή μικρότερη ή ίση με την τιμή αναζήτησης και το -1 θα βρει τη μικρότερη τιμή μικρότερη ή ίση με την τιμή αναζήτησης. Λάβετε υπόψη ότι εάν δεν χρησιμοποιείτε 0, 1 ή -1, ο τύπος θα χρησιμοποιεί 1, κατά.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
Τώρα, εάν δεν θέλετε να κωδικοποιήσετε το όνομα του μήνα, μπορείτε να το αντικαταστήσετε με τον αριθμό κελιού. Μπορούμε λοιπόν να αντικαταστήσουμε το "Jan" στον τύπο που αναφέραμε παραπάνω με F3 ή A2 για να έχουμε το ίδιο αποτέλεσμα.
Τύπος: =INDEX(στήλη των δεδομένων που θέλετε να επιστρέψετε, MATCH (κοινό σημείο δεδομένων που προσπαθείτε να αντιστοιχίσετε, στήλη της άλλης πηγής δεδομένων που έχει το κοινό σημείο δεδομένων, 0))
5. MINIFS/MAXIFS
Τα =MINIFS και =MAXIFS μοιάζουν πολύ με τις συναρτήσεις =MIN και =MAX, εκτός από το γεγονός ότι σας επιτρέπουν να λάβετε το ελάχιστο/μέγιστο σύνολο τιμών και να τα ταιριάξετε με συγκεκριμένα κριτήρια. Ουσιαστικά λοιπόν, η συνάρτηση αναζητά τις ελάχιστες/μέγιστες τιμές και τις αντιστοιχίζει με κριτήρια εισαγωγής.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
Στο παραπάνω παράδειγμα, ήθελα να βρω τις ελάχιστες βαθμολογίες με βάση το φύλο του μαθητή. Για αυτό, χρησιμοποίησα τον τύπο =MINIFS (C2:C10, B2:B10, "M") και πήρα το αποτέλεσμα 27. Εδώ C2:C10 είναι η στήλη στην οποία ο τύπος θα αναζητήσει τις βαθμολογίες, B2:B10 είναι μια στήλη στην οποία ο τύπος θα αναζητήσει τα κριτήρια (το φύλο) και το "M" είναι τα κριτήρια.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
Ομοίως, για τις μέγιστες βαθμολογίες, χρησιμοποίησα τον τύπο =MAXIFS(C2:C10, B2:B10, "M") και πήρα το αποτέλεσμα 100.
Φόρμουλα για MINIF: =MINIFS(ελάχιστο_εύρος, εύρος_κριτηρίων1, κριτήρια1,...)
Φόρμουλα για MAXIFS: =MAXIFS(μέγιστο εύρος, εύρος_κριτηρίων1, κριτήρια1,...)
6. AVERAGEIFS
Η συνάρτηση =AVERAGEIFS σας επιτρέπει να βρείτε έναν μέσο όρο για ένα συγκεκριμένο σύνολο δεδομένων με βάση ένα ή περισσότερα κριτήρια. Κατά τη χρήση αυτής της λειτουργίας, θα πρέπει να έχετε κατά νου ότι κάθε κριτήριο και το μέσο εύρος μπορεί να είναι διαφορετικό. Ωστόσο, στη συνάρτηση =AVERAGEIF, τόσο η περιοχή κριτηρίων όσο και η περιοχή αθροίσματος πρέπει να έχουν το ίδιο εύρος μεγέθους. Παρατηρήστε τη διαφορά ενικού και πληθυντικού μεταξύ αυτών των συναρτήσεων; Λοιπόν, εκεί πρέπει να είστε προσεκτικοί.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
Σε αυτό το παράδειγμα, ήθελα να βρω τη μέση βαθμολογία με βάση το φύλο των μαθητών. Για αυτό χρησιμοποίησα τον τύπο, =AVERAGEIFS(C2:C10, B2:B10, "M") και πήρα 56,8 ως αποτέλεσμα. Εδώ, το C2:C10 είναι το εύρος στο οποίο ο τύπος θα αναζητήσει τον μέσο όρο, το B2:B10 είναι το εύρος των κριτηρίων και το "M" είναι τα κριτήρια.
Τύπος: = AVERAGEIFS(μέσος_εύρος, εύρος_κριτηρίων1, κριτήρια1,...)
7. COUNTIFS
Τώρα, αν θέλετε να μετρήσετε τον αριθμό των περιπτώσεων που ένα σύνολο δεδομένων πληροί συγκεκριμένα κριτήρια, θα χρειαστεί να χρησιμοποιήσετε τη συνάρτηση =COUNTIFS. Αυτή η συνάρτηση σάς επιτρέπει να προσθέτετε απεριόριστα κριτήρια στο ερώτημά σας και, ως εκ τούτου, καθιστά τον ευκολότερο τρόπο εύρεσης της καταμέτρησης με βάση τα κριτήρια εισαγωγής.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
Σε αυτό το παράδειγμα, ήθελα να βρω τον αριθμό των μαθητών ή μαθητών που έλαβαν επιτυχία (δηλαδή >=40). Για αυτό χρησιμοποίησα τον τύπο =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Εδώ, το B2:B10 είναι το εύρος στο οποίο ο τύπος θα αναζητήσει τα πρώτα κριτήρια (φύλο), το "M" είναι το πρώτο κριτήριο, C2:C10 είναι το εύρος στο οποίο ο τύπος θα αναζητήσει τα δεύτερα κριτήρια (σημεία) και το ">=40" είναι το δεύτερο κριτήρια.
Τύπος: =COUNTIFS(κριτήρια_εύρος1, κριτήρια1,...)
8. SUMPRODUCT
Η συνάρτηση =SUMPRODUCT σάς βοηθά να πολλαπλασιάσετε εύρη ή πίνακες μαζί και στη συνέχεια επιστρέφει το άθροισμα των γινομένων. Είναι μια αρκετά ευέλικτη συνάρτηση και μπορεί να χρησιμοποιηθεί για μέτρηση και άθροιση πινάκων όπως COUNTIFS ή SUMIFS, αλλά με πρόσθετη ευελιξία. Μπορείτε επίσης να χρησιμοποιήσετε άλλες λειτουργίες στο SUMPRODUCT για να επεκτείνετε ακόμη περισσότερο τη λειτουργικότητά του.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
Σε αυτό το παράδειγμα, ήθελα να βρω το άθροισμα όλων των προϊόντων που πωλήθηκαν. Για αυτό, χρησιμοποίησα τον τύπο =SUMPRODUCT(B2:B8, C2:C8). Εδώ, το B2:B8 είναι ο πρώτος πίνακας (η ποσότητα των προϊόντων που πωλούνται) και ο C2:C8 είναι ο δεύτερος πίνακας (η τιμή κάθε προϊόντος). Στη συνέχεια, ο τύπος πολλαπλασιάζει την ποσότητα κάθε προϊόντος που πωλείται με την τιμή του και στη συνέχεια τα προσθέτει όλα για να παραδώσει τις συνολικές πωλήσεις.
Τύπος: =SUMPRODUCT(πίνακας1, [πίνακας2], [πίνακας3],...)
9. ΤΑΚΤΟΠΟΙΗΣΗ
Η συνάρτηση =TRIM είναι ιδιαίτερα χρήσιμη όταν εργάζεστε με ένα σύνολο δεδομένων που έχει πολλά κενά ή ανεπιθύμητους χαρακτήρες. Η λειτουργία σάς επιτρέπει να αφαιρείτε αυτά τα κενά ή χαρακτήρες από τα δεδομένα σας με ευκολία, επιτρέποντάς σας να λαμβάνετε ακριβή αποτελέσματα ενώ χρησιμοποιείτε άλλες λειτουργίες.
![Ανάλυση δεδομένων βασικών λειτουργιών του Excel 11](/f/04381c96e973bee237ac85395449540a.jpg)
Σε αυτό το παράδειγμα, ήθελα να αφαιρέσω όλα τα επιπλέον κενά μεταξύ των λέξεων Mouse και pad στο A7. Για αυτό χρησιμοποίησα τον τύπο =TRIM(A7).
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
Η φόρμουλα απλώς αφαίρεσε τα επιπλέον κενά και έδωσε το αποτέλεσμα Mouse pad με ένα μόνο διάστημα.
Τύπος: =TRIM(κείμενο)
10. ΕΥΡΕΣΗ/ΑΝΑΖΗΤΗΣΗ
Στρογγυλοποιώντας τα πράγματα είναι οι λειτουργίες FIND/SEARCH που θα σας βοηθήσουν να απομονώσετε συγκεκριμένο κείμενο μέσα σε ένα σύνολο δεδομένων. Και οι δύο συναρτήσεις είναι αρκετά παρόμοιες σε αυτό που κάνουν, εκτός από μια σημαντική διαφορά - η συνάρτηση =FIND επιστρέφει μόνο αντιστοιχίσεις με διάκριση πεζών-κεφαλαίων. Εν τω μεταξύ, η συνάρτηση =SEARCH δεν έχει τέτοιους περιορισμούς. Αυτές οι λειτουργίες είναι ιδιαίτερα χρήσιμες όταν αναζητάτε ανωμαλίες ή μοναδικά αναγνωριστικά.
![Ανάλυση δεδομένων βασικών συναρτήσεων του Excel 13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
Σε αυτό το παράδειγμα, ήθελα να βρω πόσες φορές εμφανίστηκε το 'Gui' στο Guiding Tech για τις οποίες χρησιμοποίησα τον τύπο =FIND(A2, B2), ο οποίος απέδωσε το αποτέλεσμα 1. Τώρα, αν ήθελα να βρω πόσες φορές εμφανίστηκε το 'gui' στο Guiding Tech, θα έπρεπε να χρησιμοποιήσω τον τύπο =SEARCH επειδή δεν κάνει διάκριση πεζών-κεφαλαίων.
Φόρμουλα για Εύρεση: =ΕΥΡΕΣΗ(find_text, inside_text, [start_num])
Τύπος αναζήτησης: =ΑΝΑΖΗΤΗΣΗ(find_text, inside_text, [start_num])