Όταν εργάζεστε με δεδομένα στο Excel, ορισμένες εργασίες μπορεί να σας φαίνονται άσκοπα κουραστικές. Ίσως χρειαστεί να διαιρέσετε μια στήλη με πλήρη ονόματα σε ξεχωριστές στήλες για το όνομα και το επώνυμο ή να συνδυάσετε κείμενο από πολλά κελιά με συγκεκριμένα κόμματα. Αυτές δεν είναι πολύπλοκες αναλυτικές προκλήσεις—είναι βασικές εργασίες επεξεργασίας δεδομένων που εμφανίζονται τακτικά.
Τα καλά νέα είναι ότι το Excel έχει ενσωματωμένες συναρτήσεις που έχουν σχεδιαστεί ειδικά για αυτές τις περιπτώσεις. Ωστόσο, συχνά παραβλέπονται επειδή δεν αποτελούν μέρος του Το τυπικό σύνολο εργαλείων του Excel που μαθαίνουν οι περισσότεροι άνθρωποι, συμπεριλαμβανομένου και εμού. Οι συναρτήσεις που θα καλύψω εδώ δεν αφορούν προηγμένους υπολογισμούς, αλλά αν διαπιστώσετε ότι κάνετε επαναλαμβανόμενη εργασία δεδομένων, αυτές οι συναρτήσεις μπορούν να σας εξοικονομήσουν χρόνο.
Γρήγοροι σύνδεσμοι
5. TEXTSSPIT
Διαχωρίζει κείμενα που έχουν κολλήσει μεταξύ τους
Αν έχετε λάβει ποτέ ένα υπολογιστικό φύλλο όπου κάποιος έχει στριμώξει το μικρό και το επώνυμό του, και ίσως ακόμη και τα αρχικά του μεσαίου ονόματος, σε ένα μόνο κελί, γνωρίζετε τον πόνο της προσπάθειας διαχωρισμού αυτών των δεδομένων. Το TextSplit αντιμετωπίζει ακριβώς αυτό το πρόβλημα—λαμβάνει κείμενο από ένα μόνο κελί και το χωρίζει σε πολλές στήλες με βάση ένα διαχωριστικό που καθορίζετε.
Ας εργαστούμε με ένα δείγμα υπολογιστικού φύλλου πωλήσεων. Θα δείτε τα ονόματα των αντιπροσώπων πωλήσεων να αναφέρονται ως "Sarah Chen", "Mike Johnson" και "Lisa Park", όλα σε μία στήλη. Αντί να πληκτρολογείτε ξανά κάθε όνομα σε ξεχωριστές στήλες, το TextSplit μπορεί να κάνει την εργασία αυτόματα.
Ο τύπος έχει ως εξής:
=TEXTSPLIT(κείμενο, διαχωριστής_στηλών, [οριοθέτης_γραμμών], [κενό_αγνόηση], [λειτουργία_ταιριάσματος], [συμπλήρωση_με])
Να τι κάνει κάθε εκπαιδευτικός:
- κείμενο: Το κελί που περιέχει το κείμενο που θέλετε να διαιρέσετε.
- διαχωριστής_στηλών: Ο χαρακτήρας που διαχωρίζει τα δεδομένα σας (όπως κενό, κόμμα ή ερωτηματικό).
- διαχωριστής_γραμμής (προαιρετικό): Χρησιμοποιείται κατά τον διαχωρισμό σε γραμμές και στήλες.
- ignore_empty (προαιρετικό): Η τιμή TRUE αγνοεί τις κενές τιμές, η τιμή FALSE τις διατηρεί (η προεπιλογή είναι FALSE).
- λειτουργία_ταιριάσματος (προαιρετικό): Ελέγχει την ευαισθησία πεζών-κεφαλαίων (0 για ευαισθησία πεζών-κεφαλαίων, 1 για μη ευαισθησία πεζών-κεφαλαίων).
- pad_with (προαιρετικό): Με τι γεμίζετε τα κενά κελιά όταν τα αποτελέσματα έχουν άνισο μήκος;
Για παράδειγμα, για τα ονόματα των αντιπροσώπων πωλήσεων, θα χρησιμοποιούσα τον ακόλουθο τύπο για να χωρίσω τα ονόματα σε ξεχωριστές στήλες:
=TEXTSPLIT(A2, " ")
Η συνάρτηση δημιουργεί αυτόματα τον απαραίτητο αριθμό στηλών με βάση τα δεδομένα σας. Ενώ αυτή η βασική προσέγγιση λειτουργεί στις περισσότερες περιπτώσεις, υπάρχουν πρόσθετες παράμετροι που σας δίνουν λεπτομερή έλεγχο Συνάρτηση TEXTSPLIT στο Excel.
4. ΚΕΙΜΕΝΟ
Συγχώνευση πολλών κελιών σε ένα κελί
Το TEXTJOIN κάνει το αντίθετο από το TEXTSPLIT. Λαμβάνει κείμενο από πολλά κελιά και τα συνδυάζει σε ένα μόνο κελί χρησιμοποιώντας το διαχωριστικό που επιλέγετε. Αυτό είναι χρήσιμο όταν χρειάζεται να δημιουργήσετε διαδοχικές τιμές, όπως πλήρεις διευθύνσεις, περιγραφές προϊόντων ή λίστες email.
Ο τύπος μοιάζει με αυτό:
=TEXTJOIN(οριοθέτης, ignore_empty, κείμενο1, [κείμενο2], ...)
Δείτε τι ελέγχει κάθε παράμετρος:
- οριοθέτης: Ο χαρακτήρας ή το κείμενο που διαχωρίζει τις ενσωματωμένες τιμές (κόμμα, κενό, παύλα κ.λπ.).
- ignore_empty: Ορίστε TRUE για να αγνοήσετε τα κενά κελιά, FALSE για να τα συμπεριλάβετε στο αποτέλεσμα.
- κείμενο1, κείμενο2, κ.λπ.: Τα κελιά ή οι περιοχές που θέλετε να συγχωνεύσετε (μπορείτε να καθορίσετε μεμονωμένα κελιά ή ολόκληρες περιοχές).
Κοιτάζοντας το υπολογιστικό φύλλο πωλήσεων, αν έχω ξεχωριστές στήλες για το όνομα και την περιοχή, αλλά τις χρειάζομαι σε μία στήλη που να τα συνδυάζει, θα χρησιμοποιούσα την TEXTJOIN. ignore_empty Η ρύθμιση σε TRUE σημαίνει ότι τυχόν κενά κελιά παραλείπονται αυτόματα.
=TEXTJOIN("-"; TRUE; B2; D2)Κατά την επιλογή μεταξύ διαφορετικών μεθόδων ενσωμάτωσης κειμένου, η κατανόηση Διαφορές μεταξύ των συναρτήσεων CONCAT και TEXTJOIN Μπορεί να σας βοηθήσει να επιλέξετε το κατάλληλο εργαλείο για τις συγκεκριμένες ανάγκες ενοποίησης δεδομένων σας.
3. ΕΠΙΛΟΓΕΣ
Καθορίστε συγκεκριμένες στήλες των δεδομένων σας.
Η συνάρτηση CHOOSECOLS σάς επιτρέπει να εξαγάγετε συγκεκριμένες στήλες από ένα εύρος χωρίς να χρειάζεται να αντιγράψετε και να επικολλήσετε ή να δημιουργήσετε αναφορές. Εάν έχετε ένα μεγάλο σύνολο δεδομένων αλλά χρειάζεστε μόνο τις στήλες 2, 5 και 8 για την ανάλυσή σας, αυτή η συνάρτηση θα πάρει ό,τι χρειάζεστε και θα απορρίψει τα υπόλοιπα.
Με βάση τα δεδομένα πωλήσεων, ίσως θελήσω να εξαγάγω μόνο τον πωλητή και τα ονόματα των πωλητών, αγνοώντας τις ημερομηνίες παραγγελίας, τις κατηγορίες προϊόντων και άλλες λεπτομέρειες. Αντί να επιλέγει και να αντιγράφει στήλες χειροκίνητα, η συνάρτηση CHOOSECOLS δημιουργεί μια δυναμική αναφορά που ενημερώνεται αυτόματα όταν αλλάζουν τα δεδομένα προέλευσης.
Η συνάρτηση ακολουθεί τον ακόλουθο τύπο:
=CHOOSECOLS(πίνακας, αριθμός_στήλης1, [αριθμός_στήλης2], ...)
Δείτε πώς λειτουργεί κάθε παράμετρος:
- πίνακας: Η περιοχή ή ο πίνακας που περιέχει τα δεδομένα προέλευσης (μπορεί να είναι μια περιοχή κελιών όπως A1:F100 ή μια αναφορά πίνακα).
- στήλη_αριθμός1: Ο αριθμός της πρώτης στήλης που θέλετε να εξαγάγετε (1 για την πρώτη στήλη, 2 για τη δεύτερη στήλη, κ.λπ.).
- στήλη_αριθμός2, κ.λπ.: Πρόσθετοι αριθμοί στηλών που θέλετε να συμπεριλάβετε (προαιρετικά – μπορείτε να καθορίσετε όσους θέλετε).
Για παράδειγμα, αν ήθελα να εξαγάγω τα ονόματα των αντιπροσώπων πωλήσεων από τη στήλη 2 και την κατάστασή τους από τη στήλη 9, θα χρησιμοποιούσα:
=CHOOSECOLS(A1:I23, 2, 9)
Η συνάρτηση επιστρέφει και τις δύο στήλες ως έναν streamed array, του οποίου το μέγεθος αλλάζει αυτόματα ώστε να ταιριάζει στα δεδομένα. Γι' αυτό το CHOOSECOLS είναι ένα από τα Συναρτήσεις του Excel που μπορούν να σας εξοικονομήσουν πολύ χρόνοΕξαλείφει την ανάγκη για πολλαπλούς τύπους VLOOKUP ή την αντιγραφή στηλών με μη αυτόματο τρόπο κατά την εργασία με μεγάλα σύνολα δεδομένων.
Το Excel διαθέτει επίσης μια συνάρτηση CHOOSEROWS, η οποία λειτουργεί παρόμοια αλλά επιλέγει συγκεκριμένες γραμμές αντί για στήλες, χρησιμοποιώντας την ίδια δομή τύπων με αριθμούς γραμμών.
2. ΠΑΡΕ ΚΑΙ ΑΠΕΣ
Εξαγωγή τμημάτων των δεδομένων σας
Οι συναρτήσεις TAKE και DROP λειτουργούν ως ζεύγος για να καταγράψουν συγκεκριμένα μέρη του εύρους δεδομένων σας. Η συνάρτηση TAKE εξάγει έναν συγκεκριμένο αριθμό γραμμών ή στηλών από την αρχή ή το τέλος του συνόλου δεδομένων σας, ενώ η συνάρτηση DROP αφαιρεί γραμμές ή στήλες από την αρχή ή το τέλος, αφήνοντάς σας ό,τι έχει απομείνει.
Αυτές οι συναρτήσεις λειτουργούν ως ακριβή εργαλεία για τη δειγματοληψία δεδομένων. Είτε χρειάζεστε μόνο τις πρώτες δέκα γραμμές δεδομένων για γρήγορη ανάλυση είτε θέλετε να καταργήσετε γραμμές κεφαλίδας που εμποδίζουν τους υπολογισμούς σας, αυτές οι συναρτήσεις χειρίζονται την εργασία με ακρίβεια.
Το TAKE χρησιμοποιεί αυτόν τον τύπο:
=TAKE(πίνακας, γραμμές, [στήλες])
Το DROP ακολουθεί ένα παρόμοιο μοτίβο:
=DROP(πίνακας, γραμμές, [στήλες])
Δείτε πώς λειτουργούν οι παράμετροι και για τις δύο συναρτήσεις:
- πίνακας: Το εύρος δεδομένων προέλευσης που θέλετε να εξαγάγετε ή να τροποποιήσετε.
- σειρές: Αριθμός γραμμών που θα ληφθούν/αφαιρεθούν (οι θετικοί αριθμοί λειτουργούν από την κορυφή, οι αρνητικοί από το κάτω μέρος).
- στήλες (προαιρετικά):
Ο αριθμός των στηλών που θέλετε να αφαιρέσετε ή να αφαιρέσετε (θετικός από τα αριστερά, αρνητικός από τα δεξιά).
Για να λάβετε τις πρώτες πέντε γραμμές δεδομένων πωλήσεων, χρησιμοποιήστε τον ακόλουθο τύπο:
=TAKE(A1:C100, 5)
Για να καταργήσετε τις πρώτες 20 γραμμές και να εργαστείτε με καθαρά δεδομένα, δοκιμάστε:
=DROP(A1:C23, 20)
Μπορείτε να συνδυάσετε λειτουργίες γραμμών και στηλών. Για παράδειγμα, ο ακόλουθος τύπος σας δίνει τις πρώτες δέκα γραμμές και τις πρώτες τρεις στήλες:
=TAKE(A1:F23, 10, 3)
Αυτές οι συναρτήσεις γίνονται πολύ χρήσιμες, ειδικά όταν χρειάζεστε δυναμικά υποσύνολα δεδομένων που προσαρμόζονται αυτόματα. Μάθετε Πώς να χρησιμοποιήσετε τις συναρτήσεις TAKE και DROP στο Excel Ανοίγει η δυνατότητα δημιουργίας ευέλικτων αναφορών που προσαρμόζονται στα μεταβαλλόμενα μεγέθη συνόλων δεδομένων.
1. ΣΥΝΟΛΟ
Ισχυροί υπολογισμοί που χειρίζονται ακατάστατα δεδομένα
Το AGGREGATE συνδυάζει τη λειτουργικότητα 19 διαφορετικών στατιστικών συναρτήσεων σε έναν ευέλικτο τύπο. Αυτό που το κάνει να ξεχωρίζει είναι η ικανότητά του να αγνοεί σφάλματα, κρυφές γραμμές ή φιλτραρισμένα δεδομένα—κάτι που οι τυπικές συναρτήσεις όπως το SUM ή το AVERAGE δεν μπορούν να κάνουν αξιόπιστα.
Εάν τα δεδομένα σας περιέχουν ορισμένα σφάλματα #N/A ή εάν φιλτράρετε για να εμφανίσετε μόνο συγκεκριμένες περιοχές, το AGGREGATE μπορεί να υπολογίσει αθροίσματα, μέσους όρους ή άλλα στατιστικά στοιχεία χωρίς αυτά τα προβλήματα να διαταράξουν τα αποτελέσματά σας. Το βρίσκω χρήσιμο όταν εργάζομαι με δυναμικά σύνολα δεδομένων όπου η ορατότητα και η ποιότητα των δεδομένων αλλάζουν συχνά.
Η δομή της πρότασης περιλαμβάνει πολλά στοιχεία:
=AGGREGATE(αριθμός_συνάρτησης, επιλογές, πίνακας, [k])
Κάθε κριτήριο ελέγχει διαφορετικές πτυχές του υπολογισμού:
- αριθμός_συνάρτησης: Ένας αριθμός από το 1 έως το 19 που καθορίζει τη συνάρτηση που θα χρησιμοποιηθεί (1=ΜΕΣΟΣ ΟΡΟΣ, 4=ΜΕΓΙΣΤΟΣ, 9=ΣΥΝΟΛΟ, 12=ΔΙΑΜΕΣΟΣ, κ.λπ.).
- επιλογές: Ελέγχει τι θα αγνοηθεί κατά τον υπολογισμό (0=κανένα, 1=κρυφές γραμμές, 2=τιμές σφάλματος, 3=κρυφές γραμμές και σφάλματα, 5=μόνο τιμές σφάλματος, 6=κρυφές γραμμές και τιμές σφάλματος).
- πίνακας: Το εύρος των κελιών που θα υπολογιστούν.
- κ (προαιρετικό):
- Χρησιμοποιείται μόνο με ορισμένες συναρτήσεις όπως ΜΕΓΑΛΟ, ΜΙΚΡΟ ή ΕΚΑΤΟΣΤΑΤΟ.
Για να συνοψίσω τα ποσά πωλήσεων που εμφανίζονται αγνοώντας τυχόν σφάλματα, μπορώ να χρησιμοποιήσω:
=AGGREGATE(9, 6, D2:D23)
Ο αριθμός 9 καθορίζει το SUM και ο αριθμός 6 λέει στη συνάρτηση να αγνοήσει τόσο τις κρυφές γραμμές όσο και τις τιμές σφάλματος.
Αυτή η ισχυρή ικανότητα εκτέλεσης υπολογισμών είναι ακριβώς ο λόγος που περιλαμβάνεται το AGGREGATE. Λίστα συναρτήσεων του Excel που πρέπει να γνωρίζει κάθε υπάλληλος γραφείου—Ασχολείται με το χάος των δεδομένων του πραγματικού κόσμου που απλούστερες συναρτήσεις δεν μπορούν να διαχειριστούν αποτελεσματικά.
Ενσωματωμένα εργαλεία που αξίζει να χρησιμοποιήσετε
Οι πιο σημαντικές συναρτήσεις του Excel συχνά δεν είναι αυτές που μαθαίνουν πρώτα οι άνθρωποι. Ωστόσο, αντιμετωπίζουν τα λεπτά προβλήματα που προκύπτουν στην πραγματική εργασία με υπολογιστικά φύλλα, όπως η διαχείριση ακατάστατων δεδομένων κειμένου, η εξαγωγή συγκεκριμένων τμημάτων από μεγάλα σύνολα δεδομένων και η εκτέλεση υπολογισμών σε ελλιπή δεδομένα. Καμία από τις συναρτήσεις που έχουμε συζητήσει δεν απαιτεί προηγμένες δεξιότητες Excel. Ωστόσο, οι συναρτήσεις TEXTSPLIT, CHOOSECOLS, TAKE και DROP είναι διαθέσιμες μόνο στο Microsoft 365 και στο Excel για το Web.
Την επόμενη φορά που θα διαπιστώσετε ότι καθαρίζετε επανειλημμένα δεδομένα ή αντιγράφετε χειροκίνητα στήλες, θυμηθείτε ότι αυτές οι συναρτήσεις υπάρχουν. Είναι ήδη ενσωματωμένες στο Excel για να χειρίζονται την κουραστική διαδικασία, ώστε να μπορείτε να εστιάσετε σε αυτό που πραγματικά σας λένε τα δεδομένα.
