Μετά από χρόνια ασχολίας με πολύπλοκα και ακατάστατα υπολογιστικά φύλλα, ανακάλυψα τέσσερις συναρτήσεις του Excel που μου εξοικονομούν ώρες εργασίας κάθε εβδομάδα, αυτοματοποιώντας εργασίες ρουτίνας που οι περισσότεροι άνθρωποι εκτελούν χειροκίνητα. Αυτές οι λειτουργίες είναι απαραίτητες για όποιον εργάζεται με δεδομένα σε τακτική βάση, είτε είστε επαγγελματίας αναλυτής δεδομένων είτε απλώς ένας περιστασιακός χρήστης που θέλει να απλοποιήσει την εργασία του.
Γρήγοροι σύνδεσμοι
4. XLOOKUP: Σύνθετη αναζήτηση σε υπολογιστικά φύλλα
XLOOKUP Πρόκειται για μια προηγμένη λειτουργία αναζήτησης σε προγράμματα υπολογιστικών φύλλων όπως το Microsoft Excel και το Google Sheets, η οποία υπερβαίνει τις δυνατότητες των παραδοσιακών λειτουργιών αναζήτησης, όπως π.χ. VLOOKUP و HLOOKUPΔιαθεσιμότητα. XLOOKUP Μεγαλύτερη ευελιξία, πιο αποτελεσματικός χειρισμός δεδομένων και μειωμένα συνηθισμένα σφάλματα που σχετίζονται με παλαιότερες λειτουργίες. XLOOKUP Ένα απαραίτητο εργαλείο για οικονομικούς αναλυτές, επιστήμονες δεδομένων και οποιονδήποτε εργάζεται με μεγάλες ποσότητες δεδομένων και χρειάζεται να εξάγει συγκεκριμένες πληροφορίες γρήγορα και με ακρίβεια. Χρησιμοποιώντας XLOOKUPΜπορείτε να αναζητήσετε μια τιμή σε ένα συγκεκριμένο εύρος και να επιστρέψετε μια αντίστοιχη τιμή από ένα άλλο εύρος, ανεξάρτητα από τη θέση των στηλών ή των γραμμών. Υποστηρίζει επίσης XLOOKUP Αναζητά από δεξιά προς τα αριστερά και από κάτω προς τα πάνω, γεγονός που το καθιστά πιο ευέλικτο από άλλες λειτουργίες.
Αντίο VLOOKUP: Το XLOOKUP είναι η τέλεια λύση
Σταμάτησα να χρησιμοποιώ το VLOOKUP πριν από χρόνια, όταν ανακάλυψα το XLOOKUP. Ενώ το VLOOKUP κάνει αναζήτηση μόνο προς τα δεξιά και παρουσιάζει σφάλμα όταν μετακινείτε στήλες, το XLOOKUP λειτουργεί προς οποιαδήποτε κατεύθυνση και παραμένει ευέλικτο. Το XLOOKUP είναι ένα από τα Συναρτήσεις του Excel που μπορούν να σας εξοικονομήσουν χρόνο Βρείτε συγκεκριμένα δεδομένα στα υπολογιστικά φύλλα σας.
Στα δεδομένα τιμολόγησης εξαρτημάτων του υπολογιστή μου, πρέπει να βρω συγκεκριμένες τιμές GPU με βάση τα μοντέλα προϊόντων. Με το VLOOKUP, θα έπρεπε να αναδιαρθρώσω ολόκληρο τον πίνακα. Αλλά με το XLOOKUP, το μόνο που έχω να κάνω είναι να πληκτρολογήσω:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB OC για παιχνίδια", C:C, D:D)
Το XLOOKUP αναζητά ολόκληρη τη στήλη προϊόντος, βρίσκει την GPU μου και επιστρέφει την αντίστοιχη τιμή. Δεν έχει σημασία πού βρίσκεται η στήλη τιμής και δεν θα παρουσιάσει σφάλμα αν προσθέσω περισσότερες στήλες αργότερα. Το χρησιμοποιώ συνέχεια για να αναφέρω πληροφορίες προϊόντος σε διαφορετικά φύλλα χωρίς να χρειάζεται να αναδιαμορφώσω τίποτα.
Ο βασικός τύπος για το XLOOKUP είναι:
=XLOOKUP(τιμή_αναζήτησης, πίνακας_αναζήτησης, πίνακας_επιστροφής)
- lookup_value: Η τιμή που θέλετε να αναζητήσετε.
- lookup_array: Το μέρος όπου αναζητάς αξία.
- return_array: Η στήλη ή η γραμμή που περιέχει την τιμή που θέλετε να επιστρέψετε.
Έτσι, στην περίπτωσή μου, η τιμή που ήθελα να βρω ήταν "GIGABYTE GeForce RTX 3060 12GB Gaming OC". Ήθελα να αναζητήσω αυτήν την τιμή στη στήλη C:C και να επιστρέψω την αντίστοιχη τιμή από το D:D στην ίδια σειρά όπου βρέθηκε η αντιστοιχία.
Κάτι άλλο που μου αρέσει στο XLOOKUP είναι ότι αν προσθέσω ",-1" στο τέλος του τύπου, κάνει αναζήτηση από κάτω προς τα πάνω, επιτρέποντάς μου να βρίσκω αυτόματα την πιο πρόσφατη καταχώριση τιμής. Αυτό με γλιτώνει από το να χρειάζεται να ταξινομώ χειροκίνητα τα δεδομένα κάθε φορά που ανανεώνω τα υπολογιστικά φύλλα μου.
3. Χρήση των συναρτήσεών μου SUMIFS و COUNTIFS Σε υπολογιστικά φύλλα
Επαγγελματισμός στη διαχείριση πολλαπλών προτύπων
Οι βασικές συναρτήσεις SUM και COUNT επαρκούν για απλές εργασίες, αλλά δεν επαρκούν όσον αφορά την ανάλυση σε πραγματικό κόσμο. Όταν χρειάζεται να αναλύσω τα δεδομένα τιμολόγησης υπό πολλαπλές συνθήκες, συνήθως χρησιμοποιώ τις συναρτήσεις SUMIFS και COUNTIFS. Αυτές μου επιτρέπουν να τμηματοποιώ εύκολα εκατοντάδες γραμμές.
Ας υποθέσουμε ότι θέλω να μετρήσω τον αριθμό των επεξεργαστών AMD που είναι διαθέσιμοι στο Amazon US. Αντί να φιλτράρω χειροκίνητα, πληκτρολογώ:
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
Αυτό μου δείχνει αμέσως ότι υπάρχουν 14 επεξεργαστές AMD που αναφέρονται στο Amazon στο σύνολο δεδομένων μου. Το ωραίο εδώ είναι ότι μπορώ να συγκεντρώσω όσα benchmarks χρειάζομαι.
Για την ανάλυση τιμών, η συνάρτηση SUMIFS λειτουργεί με τον ίδιο τρόπο. Για να υπολογίσω τη συνολική αξία όλων των επεξεργαστών Intel που είναι αυτήν τη στιγμή σε απόθεμα, χρησιμοποιώ:
=SUMIFS(D:D, K:K, "Intel", G:G, "Σε απόθεμα")
Αυτό προσθέτει όλες τις τιμές στη στήλη D όπου η επωνυμία είναι «Intel» και η κατάσταση αποθέματος είναι «In Stock».
Η σύνταξη για τη συνάρτηση SUMIFS είναι:
=SUMIFS(εύρος_αθροίσματος, εύρος_κριτηρίων1, κριτήρια1, εύρος_κριτηρίων2, κριτήρια2...)
- εύρος_συνόλων: Η στήλη που θέλετε να αθροίσετε.
- εύρος_κριτηρίων1: Η πρώτη στήλη για τον έλεγχο των συνθηκών.
- κριτήρια1: Συνθήκη πρώτης εμβέλειας.
- εύρος_κριτηρίων2, κριτήρια2: Πρόσθετοι όροι και προϋποθέσεις (προαιρετικά).
Η συνάρτηση COUNTIFS λειτουργεί παρόμοια, εκτός από το ότι μετρά τις αντίστοιχες γραμμές αντί να αθροίζει τις τιμές:
=COUNTIFS(εύρος_κριτηρίων1, κριτήρια1, εύρος_κριτηρίων2, κριτήρια2...)
Προτιμώ να χρησιμοποιώ τα SUMIFS και COUNTIFS για γρήγορες αναφορές, επειδή ενημερώνουν άμεσα τα νέα δεδομένα, ταιριάζουν άψογα στους υπάρχοντες τύπους μου και μου επιτρέπουν να διατηρώ τα πάντα ενσωματωμένα χωρίς να δημιουργώ ξεχωριστό συγκεντρωτικό πίνακα. Αυτά τα εργαλεία επιτρέπουν την ακριβή και αποτελεσματική ανάλυση δεδομένων, εξοικονομώντας χρόνο και προσπάθεια στην ανάπτυξη σύνθετων αναφορών. Η χρήση συναρτήσεων όπως τα SUMIFS και COUNTIFS είναι μια απαραίτητη δεξιότητα για κάθε αναλυτή δεδομένων που θέλει να εξάγει πολύτιμες πληροφορίες από δεδομένα γρήγορα και εύκολα.
2. Κούρεμα και καθάρισμα: Βασικά βήματα για τη διατήρηση της εμφάνισης
Αντίο στην ακαταστασία δεδομένων
Τίποτα δεν καταστρέφει ένα υπολογιστικό φύλλο πιο γρήγορα από τα μη δομημένα δεδομένα που είναι γεμάτα με επιπλέον κενά και κρυφούς χαρακτήρες. Το έμαθα αυτό με τον δύσκολο τρόπο όταν οι αναζητήσεις μου αποτύγχαναν συνεχώς λόγω επιπλέον κενών στο τέλος των ονομάτων των φορμών.
Η συνάρτηση TRIM αφαιρεί επιπλέον κενά από την αρχή και το τέλος του κειμένου, καθώς και τυχόν επιπλέον κενά μεταξύ των λέξεων. Όταν εισάγω δεδομένα από διαφορετικές πηγές, τα ονόματα προϊόντων συχνά συνοδεύονται από ασυνεπή κενά. Αντί να καθαρίζω χειροκίνητα κάθε κελί, δημιουργώ μια βοηθητική στήλη και χρησιμοποιώ:
=TRIM(C2)
Στη συνέχεια, μετακινώ τον δείκτη του ποντικιού στην άκρη του κελιού μέχρι να μετατραπεί σε σύμβολο συν (+) και στη συνέχεια τον σύρω προς τα κάτω σε όλες τις γραμμές στις οποίες θέλω να λειτουργήσει η συνάρτηση TRIM.
1. ΚΕΙΜΕΝΟ ΠΡΙΝ και ΚΕΙΜΕΝΟ ΜΕΤΑ: Μια λεπτομερής εξήγηση και η σημασία τους
Εξάγετε με ακρίβεια τα απαραίτητα δεδομένα
Οι συναρτήσεις TEXTBEFORE και TEXTAFTER είναι από τις αγαπημένες μου συναρτήσεις του Excel για τον καθαρισμό ακατάστατων υπολογιστικών φύλλων. Οι σύγχρονες συναρτήσεις κειμένου του Excel υπερέχουν στην εξαγωγή συγκεκριμένων πληροφοριών από μη δομημένες συμβολοσειρές κειμένου. Για παράδειγμα, η στήλη τιμών μου είχε καταχωρήσεις όπως "$177.52", "178.33 USD", "₱9055" και "9645.50 PHP" ανακατεμένες.
Η συνάρτηση TEXTBEFORE εξάγει όλα όσα προηγούνται ενός καθορισμένου διαχωριστή:
=TEXTBEFORE(D2, "USD")
Με αυτόν τον τρόπο, η συνάρτηση εξήγαγε αμέσως το "178.33" από το "178.33 USD".
Η συνάρτηση TEXTAFTER λειτουργεί αντίστροφα, εξάγοντας όλα τα στοιχεία μετά το διαχωριστικό:
=TEXTAFTER(C2, "AMD")
Με αυτόν τον τρόπο, εξήγαγα τη συνάρτηση "Ryzen 5 5700X 8-Core AM4 Processor" από το "AMD Ryzen 5 5700X 8-Core AM4 Processor".
Για σύνθετες εκχυλίσεις, συνδυάζω και τις δύο συναρτήσεις. Για να λάβω την αριθμητική τιμή των 177.52 δολαρίων ΗΠΑ:
=TEXTBEFORE(TEXTAFTER(D8; "$"), "USD")
Η γενική σύνταξη των συναρτήσεων TEXTBEFORE και TEXTAFTER είναι:
=TEXTBEFORE(κείμενο, οριοθέτης) και =TEXTAFTER(κείμενο, οριοθέτης)
Η τεράστια βελτίωση που προσφέρουν αυτές οι δύο συναρτήσεις έγκειται στην ακρίβειά τους. Αντί να χρησιμοποιώ πολύπλοκους συνδυασμούς συναρτήσεων MID, FIND και LEN, μπορώ να επιτύχω καθαρές εξαγωγές χρησιμοποιώντας απλούς, εύχρηστους τύπους. Χρησιμοποιώ αυτές τις συναρτήσεις συχνά για να διαχωρίζω αριθμούς μοντέλων, να εξάγω προδιαγραφές προϊόντων και να εξάγω καθαρά δεδομένα από εισαγόμενο κείμενο που παλαιότερα απαιτούσε ώρες χειροκίνητης επεξεργασίας.
Αυτές οι τέσσερις συναρτήσεις αντιμετωπίζουν μερικές από τις μεγαλύτερες σπατάλες χρόνου στο Excel, όπως η εύρεση δεδομένων χρησιμοποιώντας ευέλικτες αναζητήσεις, η ανάλυση με βάση πολλαπλά κριτήρια, ο καθαρισμός ακατάστατου εισαγόμενου κειμένου και η εξαγωγή συγκεκριμένων πληροφοριών από σύνθετες συμβολοσειρές κειμένου. Οι περισσότεροι άνθρωποι χειρίζονται αυτές τις εργασίες χειροκίνητα, αφιερώνοντας ώρες σε κάτι που κανονικά θα χρειαζόταν μόνο λίγα λεπτά για να εφαρμοστεί η σωστή φόρμουλα.
Έχετε χρησιμοποιήσει αυτές τις συναρτήσεις για τα πάντα, από την ανάλυση τιμολόγησης εξαρτημάτων έως τις αναφορές διαχείρισης αποθεμάτων. Λειτουργούν ανεξάρτητα από τον κλάδο σας, επειδή τα ακατάστατα δεδομένα και οι πολύπλοκες απαιτήσεις αναζήτησης είναι καθολικά προβλήματα. Μόλις κατακτήσετε αυτές τις συναρτήσεις, θα αναρωτιέστε πώς διαχειριζόσασταν ποτέ υπολογιστικά φύλλα χωρίς αυτές.
