Συνήθεις τύποι εκκαθάρισης δεδομένων στο Excel

excel τύπους

Εδώ και χρόνια, έχω χρησιμοποιήσει τη δημοσίευση ως πηγή για να μην περιγράψω απλώς πώς να κάνω πράγματα, αλλά και για να διατηρήσω ένα ρεκόρ για τον εαυτό μου να ψάξει αργότερα! Σήμερα, είχαμε έναν πελάτη που μας έδωσε ένα αρχείο δεδομένων πελατών που ήταν καταστροφή. Σχεδόν κάθε πεδίο είχε λανθασμένη μορφή και ως αποτέλεσμα, δεν ήταν δυνατή η εισαγωγή των δεδομένων. Ενώ υπάρχουν μερικά υπέροχα πρόσθετα για το Excel για να κάνει τον καθαρισμό χρησιμοποιώντας τη Visual Basic, τρέχουμε το Office για Mac που δεν θα υποστηρίζει μακροεντολές. Αντ 'αυτού, αναζητούμε ευθείες φόρμουλες για βοήθεια. Σκέφτηκα ότι θα μοιραστώ μερικά από αυτά εδώ, έτσι ώστε άλλοι να μπορούν να τα χρησιμοποιήσουν.

Κατάργηση μη αριθμητικών χαρακτήρων

Τα συστήματα συχνά απαιτούν την εισαγωγή αριθμών τηλεφώνου σε έναν συγκεκριμένο, 11ψήφιο τύπο με τον κωδικό χώρας και χωρίς σημεία στίξης. Ωστόσο, οι άνθρωποι συχνά εισάγουν αυτά τα δεδομένα με παύλες και τελείες. Εδώ είναι μια εξαιρετική φόρμουλα για αφαίρεση όλων των μη αριθμητικών χαρακτήρων στο Excel. Ο τύπος εξετάζει τα δεδομένα στο κελί A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

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

Αξιολογήστε πολλά πεδία με OR

Εκκαθαρίζουμε συχνά ελλιπείς εγγραφές από μια εισαγωγή. Οι χρήστες δεν συνειδητοποιούν ότι δεν χρειάζεται πάντα να γράφετε σύνθετους ιεραρχικούς τύπους και ότι μπορείτε να γράψετε μια δήλωση OR. Σε αυτό το παράδειγμα παρακάτω, θέλω να ελέγξω τα A2, B2, C2, D2 ή E2 για ελλιπή δεδομένα. Εάν λείπουν δεδομένα, θα επιστρέψω ένα 0, διαφορετικά ένα 1. Αυτό θα μου επιτρέψει να ταξινομήσω τα δεδομένα και να διαγράψω τις εγγραφές που είναι ελλιπείς.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Περικοπή και συνδυασμένα πεδία

Εάν τα δεδομένα σας έχουν πεδία Όνομα και Επώνυμο, αλλά η εισαγωγή σας έχει πεδίο πλήρους ονόματος, μπορείτε να συνδυάσετε τα πεδία μαζί τακτοποιημένα χρησιμοποιώντας το ενσωματωμένο Excel Function Concatenate, αλλά φροντίστε να χρησιμοποιήσετε το TRIM για να αφαιρέσετε κενά κενά σημεία πριν ή μετά το κείμενο. Περιτυλίγουμε ολόκληρο το πεδίο με TRIM σε περίπτωση που ένα από τα πεδία δεν έχει δεδομένα:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Ελέγξτε για έγκυρη διεύθυνση email

Μια πολύ απλή φόρμουλα που αναζητά τόσο το @ όσο και το @. σε μια διεύθυνση email:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Εξαγάγετε τα πρώτα και τα τελευταία ονόματα

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

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

Και το επώνυμο:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Περιορίστε τον αριθμό των χαρακτήρων και προσθέστε…

Θέλατε ποτέ να καθαρίσετε τις μετα-περιγραφές σας; Εάν θέλετε να τραβήξετε περιεχόμενο στο Excel και μετά να περικόψετε το περιεχόμενο για χρήση σε ένα πεδίο περιγραφής Meta (150 έως 160 χαρακτήρες), μπορείτε να το κάνετε χρησιμοποιώντας αυτόν τον τύπο από Το σημείο μου. Διαγράφει καθαρά την περιγραφή σε ένα κενό και στη συνέχεια προσθέτει το…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Φυσικά, αυτά δεν προορίζονται να είναι ολοκληρωμένα… μόνο μερικές γρήγορες φόρμουλες που θα σας βοηθήσουν να ξεκινήσετε! Τι άλλους τύπους χρησιμοποιείτε; Προσθέστε τα στα σχόλια και θα σας δώσω πίστωση καθώς ενημερώνω αυτό το άρθρο.

Ποια είναι η γνώμη σας;

Αυτός ο ιστότοπος χρησιμοποιεί το Akismet για να μειώσει το spam. Μάθετε πώς επεξεργάζονται τα δεδομένα των σχολίων σας.