Μορφοποίηση υπό όρους στο Excel: Ο τελικός οδηγός με παραδείγματα

Η μορφοποίηση υπό όρους είναι μια από τις πιο απλές αλλά ισχυρές δυνατότητες στα υπολογιστικά φύλλα του Excel.

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

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

Χρήση μορφοποίησης υπό όρους στο Excel (παραδείγματα)

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

• Γρήγορη αναγνώριση διπλότυπων με χρήση μορφοποίησης υπό όρους στο Excel.
• Επισημάνετε κελιά με αξία μεγαλύτερη/μικρότερη από έναν αριθμό σε ένα σύνολο δεδομένων.
• Επισήμανση τιμών επάνω/κάτω 10 (ή 10%) σε ένα σύνολο δεδομένων.
• Επισήμανση σφαλμάτων/κενών χρησιμοποιώντας μορφοποίηση υπό όρους στο Excel.
• Δημιουργία χαρτών θερμότητας χρησιμοποιώντας μορφοποίηση υπό όρους στο Excel.
• Επισημάνετε κάθε μιατη σειρά/στήλη χρησιμοποιώντας μορφοποίηση υπό όρους.
• Αναζήτηση και επισήμανση χρησιμοποιώντας μορφοποίηση υπό όρους στο Excel.

1. Γρήγορη αναγνώριση διπλότυπων

Η μορφοποίηση υπό όρους στο Excel μπορεί να χρησιμοποιηθεί για τον προσδιορισμό διπλότυπων σε ένα σύνολο δεδομένων.

Εδώ είναι πώς μπορείτε να το κάνετε αυτό:

• Επιλέξτε το σύνολο δεδομένων στο οποίο θέλετε να επισημάνετε διπλότυπα.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Επισήμανση κανόνων κελιού -> Διπλές τιμές.
• Στο παράθυρο διαλόγου Διπλότυπες τιμές, βεβαιωθείτε ότι έχει επιλεγεί το Διπλότυπο στο αριστερό αναπτυσσόμενο μενού. Μπορείτε να καθορίσετε τη μορφή που θα εφαρμοστεί χρησιμοποιώντας το δεξί αναπτυσσόμενο μενού. Υπάρχουν ορισμένες υπάρχουσες μορφές που μπορείτε να χρησιμοποιήσετε ή να καθορίσετε τη δική σας μορφή χρησιμοποιώντας την επιλογή Προσαρμοσμένη μορφή.
• Κάντε κλικ στο OK.

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

Δείτε επίσης: Ο τελικός οδηγός για την εύρεση και κατάργηση διπλότυπων στο Excel.

2. Επισημάνετε κελιά με αξία μεγαλύτερη/μικρότερη από έναν αριθμό

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

Ακολουθούν τα βήματα για να το κάνετε αυτό:

• Επιλέξτε ολόκληρο το σύνολο δεδομένων.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Επισημάνετε κανόνες κυττάρων -> Μεγαλύτερο από… / Λιγότερο από…
• Με βάση την επιλογή που επιλέγετε (μεγαλύτερη ή μικρότερη από), θα ανοίξει ένα πλαίσιο διαλόγου. Ας υποθέσουμε ότι επιλέγετε την επιλογή "Μεγαλύτερο από". Στο πλαίσιο διαλόγου, εισαγάγετε τον αριθμό στο πεδίο στα αριστερά. Η πρόθεση είναι να επισημανθούν τα κελιά που έχουν αριθμό μεγαλύτερο από αυτόν τον καθορισμένο αριθμό.

• Καθορίστε τη μορφή που θα εφαρμοστεί στα κελιά που πληρούν την προϋπόθεση χρησιμοποιώντας το αναπτυσσόμενο μενού στα δεξιά. Υπάρχουν ορισμένες υπάρχουσες μορφές που μπορείτε να χρησιμοποιήσετε ή να καθορίσετε τη δική σας μορφή χρησιμοποιώντας την επιλογή Προσαρμοσμένη μορφή.
• Κάντε κλικ στο OK.

Αυτό θα επισημάνει αμέσως όλα τα κελιά με τιμές μεγαλύτερες από 5 σε ένα σύνολο δεδομένων.Σημείωση: Εάν επιθυμείτε να επισημάνετε τιμές μεγαλύτερες από 5, θα πρέπει να εφαρμόσετε ξανά μορφοποίηση υπό όρους και πάλι με τα κριτήρια "Equal To".

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

3. Επισήμανση επάνω/κάτω 10 (ή 10%)

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

Ομοίως, μπορείτε επίσης να προσδιορίσετε γρήγορα τα 10 κάτω στοιχεία ή το κάτω 10% σε ένα σύνολο δεδομένων.

Ακολουθούν τα βήματα για να το κάνετε αυτό:

• Επιλέξτε ολόκληρο το σύνολο δεδομένων.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Κανόνες πάνω / κάτω -> Κορυφαία 10 στοιχεία (ή %) / Κάτω 10 αντικείμενα (ή %).
• Με βάση αυτό που επιλέγετε, θα ανοίξει το πλαίσιο διαλόγου. Ας υποθέσουμε ότι επιλέξατε τα Top 10 αντικείμενα και θα ανοίξει ένα πλαίσιο διαλόγου όπως φαίνεται παρακάτω:
• Καθορίστε τη μορφή που θα εφαρμοστεί στα κελιά που πληρούν την προϋπόθεση χρησιμοποιώντας το αναπτυσσόμενο μενού στα δεξιά. Υπάρχουν ορισμένες υπάρχουσες μορφές που μπορείτε να χρησιμοποιήσετε ή να καθορίσετε τη δική σας μορφή χρησιμοποιώντας την επιλογή Προσαρμοσμένη μορφή.
• Κάντε κλικ στο OK.

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

Επίσης, εάν έχετε λιγότερα από 10 κελιά στο σύνολο δεδομένων και ορίσετε τις επιλογές για να επισημάνετε τα κορυφαία 10 στοιχεία/Κάτω 10 στοιχεία, τότε όλα τα κελιά θα επισημανθούν.

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

4. Επισήμανση σφαλμάτων/κενών

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

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

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

Αυτό το σύνολο δεδομένων έχει ένα κενό κελί (A4) και σφάλματα (A5 και A6).

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

• Επιλέξτε το σύνολο δεδομένων στο οποίο θέλετε να επισημάνετε κενά κελιά και κελιά με σφάλματα.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Νέος κανόνας.
• Στο πλαίσιο διαλόγου Κανόνας νέας μορφοποίησης επιλέξτε Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε.
• Εισαγάγετε τον ακόλουθο τύπο στο πεδίο στην ενότητα «Επεξεργασία περιγραφής κανόνα»:
  = OR (ISBLANK (A1), ISERROR (A1))
  • Ο παραπάνω τύπος ελέγχει όλα τα κελιά για δύο συνθήκες - αν είναι κενό ή όχι και αν έχει σφάλμα ή όχι. Εάν κάποια από τις συνθήκες είναι TRUE, επιστρέφει TRUE.
• Ορίστε τη μορφή που θέλετε να εφαρμόσετε στα κελιά που είναι κενά ή έχουν σφάλματα. Για να το κάνετε αυτό, κάντε κλικ στο κουμπί Μορφοποίηση. Θα ανοίξει το πλαίσιο διαλόγου "Μορφοποίηση κελιών", όπου μπορείτε να καθορίσετε τη μορφή.
• Κάντε κλικ στο κουμπί Ok.

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

Σημείωση: Δεν χρειάζεται να χρησιμοποιήσετε ολόκληρο το εύρος A1: A7 στον τύπο σε μορφοποίηση υπό όρους. Ο παραπάνω τύπος χρησιμοποιεί μόνο Α1. Όταν εφαρμόζετε αυτόν τον τύπο σε ολόκληρο το εύρος, το excel ελέγχει ένα κελί κάθε φορά και προσαρμόζει την αναφορά. Για παράδειγμα, όταν ελέγχει το A1, χρησιμοποιεί τον τύπο = OR (ISBLANK (A1), ISERROR (A1)). Όταν ελέγχει το κελί A2, χρησιμοποιεί στη συνέχεια τον τύπο = OR (ISBLANK (A2), ISERROR (A2)). Προσαρμόζει αυτόματα την αναφορά (καθώς πρόκειται για σχετικές αναφορές) ανάλογα με το ποιο κελί αναλύεται. Επομένως, δεν χρειάζεται να γράψετε ξεχωριστό τύπο για κάθε κελί. Το Excel είναι αρκετά έξυπνο για να αλλάξει την αναφορά κελιού μόνο του 🙂

Δείτε επίσης: Χρήση IFERROR και ISERROR για τον χειρισμό σφαλμάτων στο excel.

5. Δημιουργία θερμικών χαρτών

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

Κάτι όπως φαίνεται παρακάτω:

Το παραπάνω σύνολο δεδομένων έχει τιμές μεταξύ 1 και 100. Τα κελιά επισημαίνονται με βάση την τιμή σε αυτό. Το 100 παίρνει το πράσινο χρώμα, το 1 παίρνει το κόκκινο χρώμα.

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

• Επιλέξτε το σύνολο δεδομένων.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Κλίμακες χρωμάτων και επιλέξτε ένα από τα χρώματα.

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

Σημείωση: Με παρόμοιο τρόπο, μπορείτε επίσης να εφαρμόσετε σύνολα δεδομένων Bard και εικονιδίων.

6. Επισημάνετε κάθε άλλη σειρά/στήλη

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

Αυτές ονομάζονται γραμμές ζέβρας και θα μπορούσαν να είναι ιδιαίτερα χρήσιμες εάν εκτυπώνετε τα δεδομένα.

Τώρα υπάρχουν δύο τρόποι για να δημιουργήσετε αυτές τις γραμμές ζέβρας. Ο πιο γρήγορος τρόπος είναι να μετατρέψετε τα δεδομένα των πινάκων σας σε πίνακα Excel. Εφαρμόζει αυτόματα ένα χρώμα σε εναλλακτικές σειρές. Μπορείτε να διαβάσετε περισσότερα για αυτό εδώ.

Ένας άλλος τρόπος είναι η χρήση μορφοποίησης υπό όρους.

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

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

• Επιλέξτε το σύνολο δεδομένων. Στο παραπάνω παράδειγμα, επιλέξτε A2: C13 (που εξαιρεί την κεφαλίδα). Εάν θέλετε να συμπεριλάβετε και την κεφαλίδα, επιλέξτε ολόκληρο το σύνολο δεδομένων.
• Ανοίξτε το πλαίσιο διαλόγου Μορφοποίηση υπό όρους (Αρχική σελίδα-> Διαμόρφωση υπό όρους-> Νέος κανόνας) [Συντόμευση πληκτρολογίου - Alt + O + D].
• Στο πλαίσιο διαλόγου, επιλέξτε «Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε».
• Εισαγάγετε τον ακόλουθο τύπο στο πεδίο στην ενότητα «Επεξεργασία περιγραφής κανόνα»:
  = ISODD (ΣΕΙΡΑ ())

• Ο παραπάνω τύπος ελέγχει όλα τα κελιά και αν ο αριθμός ROW ενός κελιού είναι περιττός, τότε επιστρέφει TRUE. Η καθορισμένη μορφή υπό όρους θα εφαρμοστεί σε όλα τα κελιά που επιστρέφουν TRUE.
• Ορίστε τη μορφή που θέλετε να εφαρμόσετε στα κελιά που είναι κενά ή έχουν σφάλματα. Για να το κάνετε αυτό, κάντε κλικ στο κουμπί Μορφοποίηση. Θα ανοίξει το πλαίσιο διαλόγου "Μορφοποίηση κελιών", όπου μπορείτε να καθορίσετε τη μορφή.
• Κάντε κλικ στο OK.

Αυτό είναι! Οι εναλλακτικές σειρές στο σύνολο δεδομένων θα επισημανθούν.

Μπορείτε να χρησιμοποιήσετε την ίδια τεχνική σε πολλές περιπτώσεις. Το μόνο που χρειάζεται να κάνετε είναι να χρησιμοποιήσετε τη σχετική φόρμουλα στη μορφοποίηση υπό όρους. Ορίστε μερικά παραδείγματα:

• Επισημάνετε εναλλακτικές ζυγές σειρές: = ISEVEN (ROW ())
• Επισημάνετε εναλλακτικές σειρές προσθήκης: = ISODD (ROW ())
• Επισημάνετε κάθε 3η σειρά: = MOD (ROW (), 3) = 0

7. Αναζήτηση και επισήμανση δεδομένων χρησιμοποιώντας υπό όρους μορφοποίηση

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

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω, με όνομα προϊόντων, αντιπρόσωπο πωλήσεων και γεωγραφία. Η ιδέα είναι να πληκτρολογήσετε μια συμβολοσειρά στο κελί C2 και αν ταιριάζει με τα δεδομένα σε οποιοδήποτε κελί (ες), τότε αυτό θα πρέπει να επισημανθεί. Κάτι όπως φαίνεται παρακάτω:

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

• Επιλέξτε το σύνολο δεδομένων.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Νέος κανόνας (Συντόμευση πληκτρολογίου - Alt + O + D).
• Στο παράθυρο διαλόγου Κανόνας νέας μορφοποίησης, επιλέξτε την επιλογή «Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε».
• Εισαγάγετε τον ακόλουθο τύπο στο πεδίο στην ενότητα «Επεξεργασία περιγραφής κανόνα»:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Ορίστε τη μορφή που θέλετε να εφαρμόσετε στα κελιά που είναι κενά ή έχουν σφάλματα. Για να το κάνετε αυτό, κάντε κλικ στο κουμπί Μορφοποίηση. Θα ανοίξει το πλαίσιο διαλόγου "Μορφοποίηση κελιών", όπου μπορείτε να καθορίσετε τη μορφή.
• Κάντε κλικ στο OK.

Αυτό είναι! Τώρα όταν εισάγετε οτιδήποτε στο κελί C2 και πατήσετε enter, θα επισημάνει όλα τα κελιά που ταιριάζουν.

Πως λειτουργεί αυτό?

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

Ο τύπος θα επιστρέψει TRUE για ένα κελί όταν πληρούνται δύο προϋποθέσεις:

• Το κελί C2 δεν είναι κενό.
• Το περιεχόμενο του κελιού C2 ταιριάζει ακριβώς με το περιεχόμενο του κελιού στο σύνολο δεδομένων.

Ως εκ τούτου, επισημαίνονται όλα τα κελιά που περιέχουν το κείμενο Japan.

Κατεβάστε το Παράδειγμα αρχείου

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

• Επισημάνετε ολόκληρη τη σειρά αντί για ένα κελί.
• Επισημάνετε ακόμη και όταν υπάρχει μερικός αγώνας.
• Επισημάνετε τα κελιά/γραμμές καθώς πληκτρολογείτε (δυναμικό) [Θα σας αρέσει αυτό το κόλπο :)].

Πώς να αφαιρέσετε τη μορφοποίηση υπό όρους στο Excel

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

Δεδομένου ότι είναι ασταθές, μπορεί να οδηγήσει σε αργό βιβλίο εργασίας του Excel.

Για να καταργήσετε τη μορφοποίηση υπό όρους:

• Επιλέξτε τα κελιά από τα οποία θέλετε να καταργήσετε τη μορφοποίηση υπό όρους.
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Διαγραφή κανόνων -> Διαγραφή κανόνων από επιλεγμένα κελιά.
  • Εάν θέλετε να καταργήσετε τη μορφοποίηση υπό όρους από ολόκληρο το φύλλο εργασίας, επιλέξτε Διαγραφή κανόνων από ολόκληρο το φύλλο.

Σημαντικά πράγματα που πρέπει να γνωρίζετε σχετικά με τη μορφοποίηση υπό όρους στο Excel

• Υπό όρους μορφοποίηση σε πτητικό. Μπορεί να οδηγήσει σε αργό βιβλίο εργασίας. Χρησιμοποιήστε το μόνο όταν χρειάζεται.
• Όταν αντιγράφετε κελιά επικόλλησης που περιέχουν μορφοποίηση υπό όρους, η μορφοποίηση υπό όρους αντιγράφεται επίσης.
• Εάν εφαρμόσετε πολλούς κανόνες στο ίδιο σύνολο κελιών, όλοι οι κανόνες παραμένουν ενεργοί. Σε περίπτωση επικάλυψης, προτιμάται ο κανόνας που εφαρμόζεται τελευταίος. Μπορείτε, ωστόσο, να αλλάξετε τη σειρά αλλάζοντας τη σειρά από το παράθυρο διαλόγου Διαχείριση κανόνων.