Ενώ εργάζεστε με τύπους Excel, ενδέχεται κάποια στιγμή να δείτε την ακόλουθη προειδοποιητική προτροπή.
Αυτή η προτροπή σας λέει ότι υπάρχει μια κυκλική αναφορά στο φύλλο εργασίας σας και αυτό μπορεί να οδηγήσει ή έναν λανθασμένο υπολογισμό από τους τύπους. Σας ζητά επίσης να αντιμετωπίσετε αυτό το θέμα κυκλικής αναφοράς και να το ταξινομήσετε.
Σε αυτό το σεμινάριο, θα καλύψω όλα όσα πρέπει να γνωρίζετε για την κυκλική αναφορά, καθώς και πώς να βρείτε και να αφαιρέσετε κυκλικές αναφορές στο Excel.
Ας ξεκινήσουμε λοιπόν!
Τι είναι η κυκλική αναφορά στο Excel;
Με απλά λόγια, μια κυκλική αναφορά συμβαίνει όταν καταλήξετε να έχετε έναν τύπο σε ένα κελί - το οποίο από μόνο του χρησιμοποιεί το κελί (στο οποίο έχει εισαχθεί) για τον υπολογισμό.
Επιτρέψτε μου να το εξηγήσω με ένα απλό παράδειγμα.
Ας υποθέσουμε ότι έχετε το σύνολο δεδομένων στο κελί A1: A5 και χρησιμοποιείτε τον παρακάτω τύπο στο κελί A6:
= SUM (A1: A6)
Αυτό θα σας δώσει μια κυκλική προειδοποίηση αναφοράς.
Αυτό συμβαίνει επειδή θέλετε να αθροίσετε τις τιμές στο κελί A1: A6 και το αποτέλεσμα θα πρέπει να είναι στο κελί A6.
Αυτό δημιουργεί έναν βρόχο καθώς το Excel συνεχίζει να προσθέτει τη νέα τιμή στο κελί A6, το οποίο συνεχίζει να αλλάζει (ως εκ τούτου, ένας κυκλικός βρόχος αναφοράς).
Πώς να βρείτε κυκλικές αναφορές στο Excel;
Ενώ η προειδοποίηση κυκλικής αναφοράς αναφοράς είναι αρκετά ευγενική για να σας πει ότι υπάρχει στο φύλλο εργασίας σας, δεν σας λέει πού συμβαίνει και ποιες αναφορές κελιών την προκαλούν.
Έτσι, εάν προσπαθείτε να βρείτε και να χειριστείτε κυκλικές αναφορές στο φύλλο εργασίας, πρέπει να γνωρίζετε έναν τρόπο να τα βρείτε με κάποιο τρόπο.
Παρακάτω είναι τα βήματα για να βρείτε μια κυκλική αναφορά στο Excel:
- Ενεργοποιήστε το φύλλο εργασίας που έχει την κυκλική αναφορά
- Κάντε κλικ στην καρτέλα Τύποι
- Στην ομάδα Επεξεργασία τύπου, κάντε κλικ στο αναπτυσσόμενο εικονίδιο Έλεγχος σφάλματος (μικρό βέλος προς τα κάτω δεξιά)
- Τοποθετήστε τον δείκτη του ποντικιού πάνω στην επιλογή Κυκλικές αναφορές. Θα σας δείξει το κελί που έχει μια κυκλική αναφορά στο φύλλο εργασίας
- Κάντε κλικ στη διεύθυνση κελιού (που εμφανίζεται) και θα σας μεταφέρει σε αυτό το κελί στο φύλλο εργασίας.
Αφού αντιμετωπίσετε το ζήτημα, μπορείτε να ακολουθήσετε ξανά τα ίδια βήματα παραπάνω και θα εμφανίσει περισσότερες αναφορές κελιών που έχουν την κυκλική αναφορά. Εάν δεν υπάρχει καμία, δεν θα δείτε καμία αναφορά κελιού,
Ένας άλλος γρήγορος και εύκολος τρόπος για να βρείτε την κυκλική αναφορά είναι να κοιτάξετε τη γραμμή κατάστασης. Στο αριστερό μέρος του, θα σας δείξει το κείμενο Κυκλική αναφορά μαζί με τη διεύθυνση του κελιού.
Υπάρχουν μερικά πράγματα που πρέπει να γνωρίζετε όταν εργάζεστε με κυκλικές αναφορές:
- Σε περίπτωση που ο επαναληπτικός υπολογισμός είναι ενεργοποιημένος (καλύπτεται αργότερα σε αυτό το σεμινάριο), η γραμμή κατάστασης δεν θα εμφανίζει τη διεύθυνση κυψέλης κυψέλης αναφοράς
- Σε περίπτωση που η κυκλική αναφορά δεν βρίσκεται στο ενεργό φύλλο (αλλά σε άλλα φύλλα στο ίδιο βιβλίο εργασίας), θα εμφανίζει μόνο την κυκλική αναφορά και όχι τη διεύθυνση κελιού
- Σε περίπτωση που λάβετε μια προειδοποιητική προειδοποίηση κυκλικής αναφοράς μία φορά και την απορρίψετε, δεν θα εμφανιστεί ξανά το μήνυμα την επόμενη φορά.
- Εάν ανοίξετε ένα βιβλίο εργασίας που έχει την κυκλική αναφορά, θα σας εμφανιστεί το μήνυμα αμέσως μόλις ανοίξει το βιβλίο εργασίας.
Πώς να αφαιρέσετε μια κυκλική αναφορά στο Excel;
Αφού διαπιστώσετε ότι υπάρχουν κυκλικές αναφορές στο φύλλο σας, ήρθε η ώρα να τις αφαιρέσετε (εκτός αν θέλετε να είναι εκεί για κάποιο λόγο).
Δυστυχώς, δεν είναι τόσο απλό όσο το πάτημα του πλήκτρου διαγραφής. Δεδομένου ότι αυτά εξαρτώνται από τους τύπους και κάθε τύπος είναι διαφορετικός, πρέπει να το αναλύσετε κατά περίπτωση.
Σε περίπτωση που είναι απλώς θέμα αναφοράς κελιού να προκαλεί το πρόβλημα κατά λάθος, μπορείτε απλά να το διορθώσετε προσαρμόζοντας την αναφορά.
Αλλά μερικές φορές, δεν είναι τόσο απλό.
Η κυκλική αναφορά μπορεί επίσης να προκληθεί με βάση πολλά κύτταρα που τροφοδοτούν το ένα το άλλο σε πολλά επίπεδα.
Επιτρέψτε μου να σας δείξω ένα παράδειγμα.
Παρακάτω υπάρχει μια κυκλική αναφορά στο κελί C6, αλλά δεν είναι απλή περίπτωση αυτοαναφοράς. Είναι πολυεπίπεδο όπου τα κελιά που χρησιμοποιεί στους υπολογισμούς αναφέρονται επίσης μεταξύ τους.
- Οι τύποι στο κελί Α6 είναι = SUM (A1: A5)+C6
- Ο τύπος είναι το κελί C1 είναι = A6*0.1
- Ο τύπος στο κελί C6 είναι = A6+C1
Στο παραπάνω παράδειγμα, το αποτέλεσμα στο κελί C6 εξαρτάται από τις τιμές στα κελιά A6 και C1, οι οποίες με τη σειρά τους εξαρτώνται από το κελί C6 (προκαλώντας έτσι το κυκλικό σφάλμα αναφοράς)
Και πάλι, επέλεξα ένα πραγματικά απλό παράδειγμα μόνο για demo σκοπούς. Στην πραγματικότητα, αυτά θα μπορούσαν να είναι αρκετά δύσκολο να κατανοηθούν και ίσως πολύ μακριά στο ίδιο φύλλο εργασίας ή ακόμη και διάσπαρτα σε πολλά φύλλα εργασίας.
Σε μια τέτοια περίπτωση, υπάρχει ένας τρόπος αναγνώρισης των κυττάρων που προκαλούν κυκλική αναφορά και στη συνέχεια η θεραπεία αυτών.
Χρησιμοποιεί την επιλογή Trace Precedents.
Παρακάτω παρατίθενται τα βήματα για τη χρήση προηγούμενων ιχνών για την εύρεση κυττάρων που τροφοδοτούν το κύτταρο που έχει την κυκλική αναφορά:
- Επιλέξτε το κελί που έχει την κυκλική αναφορά
- Κάντε κλικ στην καρτέλα Τύποι
- Κάντε κλικ στο Trace Precedents
Τα παραπάνω βήματα θα σας δείξουν μπλε βέλη που θα σας πουν ποια κύτταρα τροφοδοτούν τον τύπο στο επιλεγμένο κελί. Με αυτόν τον τρόπο, μπορείτε να ελέγξετε τους τύπους και τα κελιά και να απαλλαγείτε από την κυκλική αναφορά.
Σε περίπτωση που εργάζεστε με σύνθετα οικονομικά μοντέλα, μπορεί να είναι πιθανό αυτά τα προηγούμενα να πηγαίνουν επίσης σε πολλά επίπεδα.
Αυτό λειτουργεί καλά εάν έχετε όλους τους τύπους που αναφέρονται σε κελιά στο ίδιο φύλλο εργασίας. Εάν υπάρχει σε πολλά φύλλα εργασίας, αυτή η μέθοδος δεν είναι αποτελεσματική.
Πώς να ενεργοποιήσετε/απενεργοποιήσετε τους επαναληπτικούς υπολογισμούς στο Excel
Όταν έχετε μια κυκλική αναφορά σε ένα κελί, λαμβάνετε πρώτα την προειδοποιητική προτροπή όπως φαίνεται παρακάτω και αν κλείσετε αυτό το παράθυρο διαλόγου, θα σας δώσει 0 ως αποτέλεσμα στο κελί.
Αυτό συμβαίνει επειδή όταν υπάρχει μια κυκλική αναφορά, είναι ένας ατελείωτος βρόχος και το Excel δεν θέλει να το πιάσει. Έτσι επιστρέφει το 0.
Αλλά σε ορισμένες περιπτώσεις, μπορεί πραγματικά να θέλετε η κυκλική αναφορά να είναι ενεργή και να κάνετε μερικές επαναλήψεις. Σε μια τέτοια περίπτωση, αντί για άπειρο βρόχο, και μπορείτε να αποφασίσετε πόσες φορές θα πρέπει να εκτελεστεί ο βρόχος.
Αυτό ονομάζεται επαναληπτικός υπολογισμός στο Excel.
Παρακάτω είναι τα βήματα για την ενεργοποίηση και τη διαμόρφωση επαναληπτικών υπολογισμών στο Excel:
- Κάντε κλικ στην καρτέλα Αρχείο
- Κάντε κλικ στην επιλογή Επιλογές. Αυτό θα ανοίξει το παράθυρο διαλόγου Επιλογές Excel
- Επιλέξτε Τύπος στο αριστερό παράθυρο
- Στην ενότητα Επιλογές υπολογισμού, επιλέξτε το πλαίσιο «Ενεργοποίηση επαναληπτικού υπολογισμού». Εδώ μπορείτε να καθορίσετε τις μέγιστες επαναλήψεις και τη μέγιστη τιμή αλλαγής
Αυτό είναι! Τα παραπάνω βήματα θα επιτρέψουν τον επαναληπτικό υπολογισμό στο Excel.
Επιτρέψτε μου επίσης να εξηγήσω γρήγορα τις δύο επιλογές στον επαναληπτικό υπολογισμό:
- Μέγιστες επαναλήψεις: Αυτός είναι ο μέγιστος αριθμός φορών που θέλετε να υπολογίσει το Excel πριν σας δώσει το τελικό αποτέλεσμα. Έτσι, εάν το ορίσετε ως 100, το Excel θα εκτελέσει τον βρόχο 100 φορές πριν σας δώσει το τελικό αποτέλεσμα.
- Μέγιστη Αλλαγή: Αυτή είναι η μέγιστη αλλαγή, η οποία εάν δεν επιτευχθεί μεταξύ των επαναλήψεων, ο υπολογισμός θα σταματούσε. Από προεπιλογή, η τιμή είναι 0,001. Όσο χαμηλότερη είναι αυτή η τιμή, τόσο πιο ακριβές θα είναι το αποτέλεσμα.
Θυμηθείτε ότι όσο περισσότερες φορές εκτελούνται οι επαναλήψεις, τόσο περισσότερος χρόνος και πόροι χρειάζονται για να το κάνει το Excel. Σε περίπτωση που διατηρήσετε τις μέγιστες επαναλήψεις υψηλές, μπορεί να οδηγήσει σε επιβράδυνση ή διακοπή του Excel σας.
Σημείωση: Όταν είναι ενεργοποιημένοι οι επαναληπτικοί υπολογισμοί, το Excel δεν θα σας εμφανίσει την προειδοποιητική προειδοποίηση κυκλικής αναφοράς και επίσης θα την εμφανίσει τώρα στη γραμμή κατάστασης.Ηθελημένη χρήση κυκλικών αναφορών
Στις περισσότερες περιπτώσεις, η παρουσία κυκλικής αναφοράς στο φύλλο εργασίας σας θα ήταν σφάλμα. Αυτός είναι ο λόγος για τον οποίο το Excel σας εμφανίζει ένα μήνυμα που λέει - "Δοκιμάστε να αφαιρέσετε ή να αλλάξετε αυτές τις αναφορές ή να μετακινήσετε τους τύπους σε διαφορετικά κελιά."
Αλλά μπορεί να υπάρχουν κάποιες συγκεκριμένες περιπτώσεις όπου χρειάζεστε μια κυκλική αναφορά, ώστε να έχετε το επιθυμητό αποτέλεσμα.
Μια τέτοια συγκεκριμένη περίπτωση έχω ήδη γράψει για τη λήψη της χρονικής σφραγίδας σε ένα κελί σε ένα κελί στο Excel.
Για παράδειγμα, ας υποθέσουμε ότι θέλετε να δημιουργήσετε έναν τύπο έτσι ώστε κάθε καταχώριση να γίνεται σε ένα κελί στη στήλη Α, η χρονική σήμανση να εμφανίζεται στη στήλη Β (όπως φαίνεται παρακάτω):
Ενώ μπορείτε εύκολα να εισαγάγετε μια χρονική σήμανση χρησιμοποιώντας τον παρακάτω τύπο:
= IF (A2 "", IF (B2 "", B2, ΤΩΡΑ ()), "")
Το πρόβλημα με τον παραπάνω τύπο είναι ότι θα ενημερώνει όλες τις χρονικές σημάνσεις μόλις πραγματοποιηθεί οποιαδήποτε αλλαγή στο φύλλο εργασίας ή εάν το φύλλο εργασίας ανοίξει ξανά (καθώς ο τύπος ΤΩΡΑ είναι ασταθής)
Για να ξεπεράσετε αυτό το ζήτημα, μπορείτε να χρησιμοποιήσετε μια κυκλική μέθοδο αναφοράς. Χρησιμοποιήστε τον ίδιο τύπο, αλλά ενεργοποιήστε τον επαναληπτικό υπολογισμό.
Υπάρχουν επίσης κάποιες άλλες περιπτώσεις όπου είναι επιθυμητή η δυνατότητα χρήσης κυκλικής αναφοράς (μπορείτε να βρείτε ένα παράδειγμα εδώ).
Σημείωση: Παρόλο που μπορεί να υπάρχουν περιπτώσεις όπου μπορείτε να χρησιμοποιήσετε την κυκλική αναφορά, θεωρώ καλύτερο να αποφύγω τη χρήση της. Οι κυκλικές αναφορές μπορούν επίσης να επηρεάσουν την απόδοση του βιβλίου εργασίας σας και να το καθυστερήσουν. Σε σπάνιες περιπτώσεις όπου το χρειάζεστε, προτιμώ πάντα τη χρήση κωδικών VBA για να ολοκληρώσω τη δουλειά.Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο!
Άλλα σεμινάρια Excel που μπορεί να σας φανούν χρήσιμα:
- #REF! Σφάλμα στο Excel. Πώς να διορθώσετε το σφάλμα αναφοράς!
- Χειρισμός σφαλμάτων Excel VBA
- Χρησιμοποιήστε το IFERROR με το VLOOKUP για να απαλλαγείτε από σφάλματα #N/A
- Πώς να αναφέρετε ένα άλλο φύλλο ή βιβλίο εργασίας στο Excel (με παραδείγματα)
- Απόλυτες, σχετικές και μικτές αναφορές κυττάρων στο Excel
