Πώς να προσθέσετε κορυφαία μηδενικά στο Excel - Όλα όσα πρέπει να γνωρίζετε

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

Για παράδειγμα, μπορεί να θέλετε να έχετε μια συνεπή εμφάνιση στο σύνολο δεδομένων σας όπως φαίνεται παρακάτω:

Σε αυτό το σεμινάριο, θα μάθετε διάφορους τρόπους για να προσθέσετε κορυφαία μηδενικά στο Excel:

• Μετατροπή της μορφής σε κείμενο
• Χρήση προσαρμοσμένης μορφοποίησης αριθμών
• Χρήση συνάρτησης κειμένου
• Χρήση λειτουργιών REPT/LEN
• Χρήση VBA

Κάθε μία από αυτές τις μεθόδους έχει κάποια πλεονεκτήματα και μειονεκτήματα (καλύπτονται σε κάθε ενότητα).

Ας δούμε πώς λειτουργεί καθένα από αυτά.

Προσθέστε κορυφαία μηδενικά μετατρέποντας τη μορφή σε κείμενο

Πότε να χρησιμοποιήσετε: Όταν έχετε ένα μικρό αριθμητικό σύνολο δεδομένων και σκοπεύετε να το κάνετε αυτό χειροκίνητα.

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

Έτσι προσπαθείτε να αλλάξετε το αναγνωριστικό εισάγοντας μηδενικά (00001 αντί για 1).

Αλλά προς έκπληξή σας, το Excel το μετατρέπει ξανά σε 1.

Αυτό συμβαίνει καθώς το Excel κατανοεί ότι τα 00001 και 1 είναι οι ίδιοι αριθμοί και πρέπει να ακολουθούν τους ίδιους κανόνες εμφάνισης.

Τώρα όσο απογοητευτικό κι αν είναι για εσάς, το Excel έχει τους λόγους του.

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

Ιδού λοιπόν τι πρέπει να κάνετε:

1. Επιλέξτε τα κελιά στα οποία θέλετε να προσθέσετε μη αυτόματα μηδενικά.
2. Μεταβείτε στην Αρχική σελίδα → Ομάδα αριθμών και επιλέξτε Κείμενο από το αναπτυσσόμενο μενού.

Αυτό είναι!

Τώρα, όταν εισάγετε μηδενικά κύρια μηδενικά, το Excel θα συμμορφωνόταν εύκολα.

Προσοχή: Όταν μετατρέπετε τη μορφή σε Κείμενο, ορισμένες λειτουργίες του Excel δεν θα λειτουργήσουν σωστά. Για παράδειγμα, η συνάρτηση SUM/COUNT θα αγνοήσει το κελί, επειδή είναι σε μορφή κειμένου.

Προσθέστε κορυφαία μηδενικά χρησιμοποιώντας προσαρμοσμένη μορφοποίηση αριθμών

Πότε να χρησιμοποιήσετε: Όταν έχετε ένα αριθμητικό σύνολο δεδομένων και θέλετε το αποτέλεσμα να είναι αριθμητικό (όχι κείμενο).

Όταν εμφανίζετε έναν αριθμό σε μια συγκεκριμένη μορφή, δεν αλλάζει την υποκείμενη τιμή του αριθμού. Για παράδειγμα, μπορώ να εμφανίσω τον αριθμό 1000 ως 1000 ή 1.000 ή 1000.00 ή 001000 ή 26-09-1902 (ακόμη και οι ημερομηνίες είναι αριθμοί στο backend στο Excel).

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

Για να προσθέσουμε κορυφαία μηδενικά, μπορούμε να το μορφοποιήσουμε για να το δείξουμε έτσι, ενώ η υποκείμενη τιμή θα παραμείνει αμετάβλητη.

Ακολουθούν τα βήματα για να χρησιμοποιήσετε αυτήν την τεχνική για να προσθέσετε κορυφαία μηδενικά στο Excel:

1. Επιλέξτε τα κελιά στα οποία θέλετε να προσθέσετε μηδενικά.
2. Μεταβείτε στην Αρχική σελίδα → Ομάδα αριθμών και κάντε κλικ στο πρόγραμμα εκκίνησης διαλόγου (ένα μικρό κεκλιμένο βέλος κάτω δεξιά). Αυτό θα ανοίξει το παράθυρο διαλόγου Μορφοποίηση κελιών. Εναλλακτικά, μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου: Control + 1.
3. Στο παράθυρο διαλόγου Μορφοποίηση κελιών, στην καρτέλα Αριθμός, επιλέξτε Προσαρμοσμένο στη λίστα Κατηγορία.
4. Στο πεδίο Τύπος, πληκτρολογήστε 00000
5. Κάντε κλικ στο OK.

Με αυτόν τον τρόπο θα εμφανίζονται πάντα όλοι οι αριθμοί ως πέντε ψηφία, όπου τα κορυφαία 0 προστίθενται αυτόματα εάν ο αριθμός είναι μικρότερος από 5 ψηφία. Έτσι 10 θα γίνονταν 00010 και 100 θα γίνονταν 00100.

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

Σημείωση: Αυτή η τεχνική θα λειτουργούσε μόνο για ένα αριθμητικό σύνολο δεδομένων. Σε περίπτωση που έχετε αναγνωριστικά υπαλλήλων όπως A1, A2, A3 και ούτω καθεξής, τότε αυτά είναι κείμενο και δεν θα αλλάξουν όταν εφαρμόζετε την προσαρμοσμένη μορφή όπως φαίνεται παραπάνω.

Προσθέστε κορυφαία μηδενικά χρησιμοποιώντας τη συνάρτηση TEXT

Πότε να χρησιμοποιήσετε: Όταν θέλετε το αποτέλεσμα να είναι κείμενο.

Η λειτουργία TEXT σάς επιτρέπει να αλλάξετε την τιμή στην επιθυμητή μορφή.

Για παράδειγμα, εάν θέλετε το 1 να εμφανίζεται ως 001, μπορείτε να χρησιμοποιήσετε τη συνάρτηση TEXT για αυτό.

Ωστόσο, θυμηθείτε ότι η συνάρτηση TEXT θα άλλαζε τη μορφή και θα την έκανε TEXT. Αυτό σημαίνει ότι όταν κάνετε το 1 ως 001, το Excel αντιμετωπίζει το νέο αποτέλεσμα ως κείμενο με τρεις χαρακτήρες (ακριβώς όπως abc ή xyz).

Δείτε πώς μπορείτε να προσθέσετε κορυφαία μηδενικά χρησιμοποιώντας τη συνάρτηση TEXT:

1. Εάν έχετε τους αριθμούς στη στήλη A (πείτε από το A2: A100), επιλέξτε B2: B100 και εισαγάγετε τον ακόλουθο τύπο:
   = TEXT (A2, "00000)
2. Πατήστε Control + Enter για να εφαρμόσετε τον τύπο σε όλα τα επιλεγμένα κελιά.

Αυτό θα εμφανίσει όλους τους αριθμούς ως πέντε ψηφία, όπου τα κορυφαία 0 προστίθενται αυτόματα εάν ο αριθμός είναι μικρότερος από 5 ψηφία.

Ένα πλεονέκτημα της μετατροπής δεδομένων σε κείμενο είναι ότι μπορείτε τώρα να τα χρησιμοποιήσετε σε τύπους αναζήτησης όπως VLOOKUP ή INDEX/MATCH για να λάβετε τα στοιχεία ενός υπαλλήλου χρησιμοποιώντας το αναγνωριστικό υπαλλήλου του.

Σημείωση: Αυτή η τεχνική θα λειτουργούσε μόνο για ένα αριθμητικό σύνολο δεδομένων. Σε περίπτωση που έχετε αναγνωριστικά υπαλλήλων όπως A1, A2, A3 και ούτω καθεξής, τότε αυτά είναι κείμενο και δεν θα αλλάξουν όταν εφαρμόζετε την προσαρμοσμένη μορφή όπως φαίνεται παραπάνω.

Προσθέστε κορυφαία μηδενικά χρησιμοποιώντας τις λειτουργίες REPT και LEN

Πότε να χρησιμοποιήσετε: Όταν έχετε ένα σύνολο δεδομένων που είναι αριθμητικό/αλφαριθμητικό και θέλετε το αποτέλεσμα να είναι κείμενο.

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

Σε τέτοιες περιπτώσεις, ένας συνδυασμός λειτουργίας REPT και LEN κάνει το κόλπο.

Εδώ είναι πώς να το κάνετε:

1. Εάν έχετε τους αριθμούς στη στήλη A (πείτε από το A2: A100), επιλέξτε B2: B100 και εισαγάγετε τον ακόλουθο τύπο:
   = REPT (0,5-LEN (A2)) & A2
2. Πατήστε Control + Enter για να εφαρμόσετε τον τύπο σε όλα τα επιλεγμένα κελιά.

Αυτό θα κάνει όλους τους αριθμούς/συμβολοσειρές 5 χαρακτήρες με κορυφαία μηδενικά όπου χρειάζεται.

Δείτε πώς λειτουργεί αυτός ο τύπος:

• Το LEN (A2) δίνει το μήκος της συμβολοσειράς/αριθμών στο κελί.
• = REPT (0,5-LEN (A2)) θα έδινε τον αριθμό του 0 που πρέπει να προστεθεί. Εδώ έχω χρησιμοποιήσει 5 στον τύπο καθώς αυτό ήταν το μέγιστο μήκος συμβολοσειράς/αριθμών στο σύνολο δεδομένων μου. Μπορείτε να το αλλάξετε σύμφωνα με τα δεδομένα σας.
• = REPT (0,5-LEN (A2)) & A2 θα προσθέσει απλώς τον αριθμό των μηδενικών στην τιμή του κελιού. Για παράδειγμα, εάν η τιμή στο κελί είναι 123, αυτό θα επιστρέψει 00123.

Προσθέστε κορυφαία μηδενικά χρησιμοποιώντας προσαρμοσμένη λειτουργία (VBA)

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

Ακολουθεί ο κώδικας VBA που θα δημιουργήσει μια απλή λειτουργία για την προσθήκη μηδενικών:

«Κωδικός από Sumit Bansal από http://trumpexcel.com Λειτουργία AddLeadingZeroes (αναφορά ως εύρος, μήκος ως ακέραιος) Dim i ως ακέραιος Dim αποτέλεσμα ως συμβολοσειρά Dim StrLen Ως ακέραιος StrLen = Len (ref) Για i = 1 έως μήκος Αν i <= StrLen Στη συνέχεια Result = Result & Mid (ref, i, 1) Else Result = "0" & ​​Result End If Next i AddLeadingZeroes = Result End function

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

Or δημιουργήστε ένα πρόσθετο για αυτό και μπορείτε να το μοιραστείτε με τους συναδέλφους σας.