Μία από τις πιο κοινές προειδοποιήσεις σφαλμάτων που αντιμετωπίζουν οι χρήστες στο Excel είναι η «Κυκλική αναφορά». Χιλιάδες χρήστες έχουν το ίδιο πρόβλημα και αυτό συμβαίνει όταν ένας τύπος αναφέρεται στο δικό του κελί άμεσα ή έμμεσα, προκαλώντας έναν ατελείωτο κύκλο υπολογισμών.
Για παράδειγμα, έχετε δύο τιμές στα κελιά B1 και B2. Όταν ο τύπος =B1+B2 εισάγεται στο B2, δημιουργεί μια κυκλική αναφορά. ο τύπος στο B2 επανυπολογίζεται επανειλημμένα επειδή κάθε φορά που υπολογίζει, η τιμή B2 έχει αλλάξει.
Οι περισσότερες κυκλικές αναφορές είναι ακούσια λάθη. Το Excel θα σας προειδοποιήσει για αυτά. Ωστόσο, υπάρχουν επίσης προβλεπόμενες κυκλικές αναφορές, οι οποίες χρησιμοποιούνται για την πραγματοποίηση επαναληπτικών υπολογισμών. Οι ακούσιες κυκλικές αναφορές στο φύλλο εργασίας σας ενδέχεται να προκαλέσουν λανθασμένο υπολογισμό του τύπου σας.
Επομένως, σε αυτό το άρθρο, θα εξηγήσουμε όλα όσα πρέπει να γνωρίζετε για τις κυκλικές αναφορές, καθώς και πώς να βρείτε, να διορθώσετε, να αφαιρέσετε και να χρησιμοποιήσετε κυκλικές αναφορές στο Excel.
Πώς να βρείτε και να χειριστείτε την κυκλική αναφορά στο Excel
Όταν εργαζόμαστε με το Excel, μερικές φορές συναντάμε σφάλματα κυκλικής αναφοράς που συμβαίνουν όταν εισάγετε έναν τύπο που περιλαμβάνει το κελί όπου βρίσκεται ο τύπος σας. Βασικά, συμβαίνει όταν ο τύπος σας προσπαθεί να υπολογίσει τον εαυτό του.
Για παράδειγμα, έχετε μια στήλη αριθμών στο κελί A1:A4 και χρησιμοποιείτε τη συνάρτηση SUM (=SUM(A1:A5)) στο κελί A5. Το κελί A5 αναφέρεται απευθείας στο δικό του κελί, κάτι που είναι λάθος. Ως εκ τούτου, θα λάβετε την ακόλουθη κυκλική προειδοποίηση αναφοράς:
Μόλις λάβετε το παραπάνω προειδοποιητικό μήνυμα, μπορείτε να κάνετε κλικ στο κουμπί "Βοήθεια" για να μάθετε περισσότερα σχετικά με το σφάλμα ή να κλείσετε το παράθυρο του μηνύματος σφάλματος κάνοντας κλικ είτε στο κουμπί "OK" ή "X" και να λάβετε ως αποτέλεσμα το "0".
Μερικές φορές οι κυκλικοί βρόχοι αναφοράς μπορεί να προκαλέσουν διακοπή λειτουργίας του υπολογισμού σας ή να επιβραδύνουν την απόδοση του φύλλου εργασίας σας. Η κυκλική αναφορά μπορεί επίσης να οδηγήσει σε μια σειρά από άλλα ζητήματα, τα οποία δεν θα φανούν αμέσως. Επομένως, είναι καλύτερο να τα αποφύγετε.
Άμεσες και Έμμεσες Κυκλικές Αναφορές
Οι κυκλικές αναφορές μπορούν να κατηγοριοποιηθούν σε δύο τύπους: Άμεσες κυκλικές αναφορές και έμμεσες κυκλικές αναφορές.
Άμεση αναφορά
Μια άμεση κυκλική αναφορά είναι αρκετά απλή. Το προειδοποιητικό μήνυμα άμεσης κυκλικής αναφοράς εμφανίζεται όταν ο τύπος αναφέρεται απευθείας στο δικό του κελί.
Στο παρακάτω παράδειγμα, ο τύπος στο κελί A2 αναφέρεται απευθείας στο δικό του κελί (A2).
Μόλις εμφανιστεί το προειδοποιητικό μήνυμα, μπορείτε να κάνετε κλικ στο «OK», αλλά θα έχει ως αποτέλεσμα μόνο «0».
Έμμεση Εγκύκλιος Αναφορά
Μια έμμεση κυκλική αναφορά στο Excel εμφανίζεται όταν μια τιμή σε έναν τύπο αναφέρεται στο δικό του κελί, αλλά όχι απευθείας. Με άλλα λόγια, η κυκλική αναφορά μπορεί να σχηματιστεί από δύο κελιά που αναφέρονται το ένα στο άλλο.
Ας εξηγηθούμε με αυτό το απλό παράδειγμα.
Τώρα η τιμή ξεκινά από το A1 που έχει την τιμή 20.
Στη συνέχεια, το κελί C3 αναφέρεται στο κελί A1.
Στη συνέχεια, το κελί A5 αναφέρεται στο κελί C3.
Τώρα αντικαταστήστε την τιμή 20 στο κελί A1 με τον τύπο όπως φαίνεται παρακάτω. Κάθε άλλο κελί εξαρτάται από το κελί A1. Όταν χρησιμοποιείτε μια αναφορά οποιουδήποτε άλλου προηγούμενου κελιού τύπου στο A1, θα προκαλέσει μια κυκλική προειδοποίηση αναφοράς. Επειδή, ο τύπος στο A1 αναφέρεται στο κελί A5, το οποίο αναφέρεται στο C3, και το κελί C3 αναφέρεται στο A1, εξ ου και η κυκλική αναφορά.
Όταν κάνετε κλικ στο "OK", προκύπτει μια τιμή 0 στο κελί A1 και το Excel δημιουργεί μια συνδεδεμένη γραμμή που δείχνει τα Trace Precedents και Trace Dependents όπως φαίνεται παρακάτω. Μπορούμε να χρησιμοποιήσουμε αυτή τη δυνατότητα για να βρίσκουμε και να διορθώνουμε/αφαιρούμε εύκολα κυκλικές αναφορές.
Πώς να ενεργοποιήσετε / απενεργοποιήσετε τις κυκλικές αναφορές στο Excel
Από προεπιλογή, οι επαναληπτικοί υπολογισμοί είναι απενεργοποιημένοι (απενεργοποιημένοι) στο Excel. Οι επαναληπτικοί υπολογισμοί είναι επαναλαμβανόμενοι υπολογισμοί έως ότου πληροί μια συγκεκριμένη συνθήκη. Όταν είναι απενεργοποιημένο, το Excel εμφανίζει ένα μήνυμα κυκλικής αναφοράς και επιστρέφει το 0 ως αποτέλεσμα.
Ωστόσο, μερικές φορές απαιτούνται κυκλικές αναφορές για τον υπολογισμό ενός βρόχου. Για να χρησιμοποιήσετε την κυκλική αναφορά, πρέπει να ενεργοποιήσετε τους επαναληπτικούς υπολογισμούς στο Excel και θα σας επιτρέψει να εκτελέσετε τους υπολογισμούς σας. Τώρα, ας σας δείξουμε πώς μπορείτε να ενεργοποιήσετε ή να απενεργοποιήσετε τους επαναληπτικούς υπολογισμούς.
Στο Excel 2010, Excel 2013, Excel 2016, Excel 2019 και Microsoft 365, μεταβείτε στην καρτέλα "Αρχείο" στην επάνω αριστερή γωνία του Excel και, στη συνέχεια, κάντε κλικ στην επιλογή "Επιλογές" στο αριστερό παράθυρο.
Στο παράθυρο Επιλογές του Excel, μεταβείτε στην καρτέλα «Τύπος» και επιλέξτε το πλαίσιο ελέγχου «Ενεργοποίηση επαναληπτικού υπολογισμού» στην ενότητα «Επιλογές υπολογισμού». Στη συνέχεια, κάντε κλικ στο «OK» για να αποθηκεύσετε τις αλλαγές.
Αυτό θα επιτρέψει τον επαναληπτικό υπολογισμό και έτσι θα επιτρέψει την κυκλική αναφορά.
Για να το επιτύχετε αυτό σε προηγούμενες εκδόσεις του Excel, ακολουθήστε τα εξής βήματα:
- Στο Excel 2007, κάντε κλικ στο κουμπί Office > Επιλογές Excel > Τύποι > Περιοχή επανάληψης.
- Στο Excel 2003 και σε παλαιότερες εκδόσεις, πρέπει να μεταβείτε στην καρτέλα Μενού > Εργαλεία > Επιλογές > Υπολογισμός.
Μέγιστες επαναλήψεις & μέγιστες παράμετροι αλλαγής
Μόλις ενεργοποιήσετε τους επαναληπτικούς υπολογισμούς, μπορείτε να ελέγξετε τους επαναληπτικούς υπολογισμούς, καθορίζοντας δύο διαθέσιμες επιλογές στην ενότητα Ενεργοποίηση επαναληπτικού υπολογισμού, όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης.
- Μέγιστες επαναλήψεις – Αυτός ο αριθμός καθορίζει πόσες φορές θα πρέπει να υπολογιστεί εκ νέου ο τύπος πριν σας δώσει το τελικό αποτέλεσμα. Η προεπιλεγμένη τιμή είναι 100. Εάν την αλλάξετε σε '50', το Excel θα επαναλάβει τους υπολογισμούς 50 φορές πριν σας δώσει το τελικό αποτέλεσμα. Να θυμάστε ότι όσο μεγαλύτερος είναι ο αριθμός των επαναλήψεων, τόσο περισσότεροι πόροι και χρόνος χρειάζονται για τον υπολογισμό.
- Μέγιστη Αλλαγή – Καθορίζει τη μέγιστη αλλαγή μεταξύ των αποτελεσμάτων υπολογισμού. Αυτή η τιμή καθορίζει την ακρίβεια του αποτελέσματος. Όσο μικρότερος είναι ο αριθμός, τόσο πιο ακριβές αποτέλεσμα θα είναι το αποτέλεσμα και τόσο περισσότερος χρόνος χρειάζεται για τον υπολογισμό του φύλλου εργασίας.
Εάν είναι ενεργοποιημένη η επιλογή επαναληπτικών υπολογισμών, δεν θα λαμβάνετε καμία προειδοποίηση κάθε φορά που υπάρχει μια κυκλική αναφορά στο φύλλο εργασίας σας. Ενεργοποιήστε τον διαδραστικό υπολογισμό μόνο όταν είναι απολύτως απαραίτητο.
Βρείτε την κυκλική αναφορά στο Excel
Ας υποθέσουμε ότι έχετε ένα μεγάλο σύνολο δεδομένων και λάβατε την κυκλική προειδοποίηση αναφοράς, θα πρέπει ακόμα να μάθετε πού (σε ποιο κελί) προέκυψε το σφάλμα για να το διορθώσετε. Για να βρείτε κυκλικές αναφορές στο Excel, ακολουθήστε τα εξής βήματα:
Χρήση του Εργαλείου ελέγχου σφαλμάτων
Αρχικά, ανοίξτε το φύλλο εργασίας όπου έγινε η κυκλική αναφορά. Μεταβείτε στην καρτέλα «Τύπος», κάντε κλικ στο βέλος δίπλα στο εργαλείο «Έλεγχος σφαλμάτων». Στη συνέχεια, απλώς τοποθετήστε τον κέρσορα πάνω από την επιλογή «Κυκλικές αναφορές», το Excel θα σας δείξει τη λίστα με όλα τα κελιά που εμπλέκονται στην κυκλική αναφορά, όπως φαίνεται παρακάτω.
Κάντε κλικ σε όποια διεύθυνση κελιού θέλετε στη λίστα και θα σας μεταφέρει σε αυτήν τη διεύθυνση κελιού για να λύσετε το πρόβλημα.
Χρήση της γραμμής κατάστασης
Μπορείτε επίσης να βρείτε την κυκλική αναφορά στη γραμμή κατάστασης. Στη γραμμή κατάστασης του Excel, θα εμφανιστεί η πιο πρόσφατη διεύθυνση κελιού με μια κυκλική αναφορά, όπως "Κυκλικές αναφορές: B6" (δείτε παρακάτω στιγμιότυπο οθόνης).
Υπάρχουν ορισμένα πράγματα που πρέπει να γνωρίζετε όταν χειρίζεστε την κυκλική αναφορά:
- Η γραμμή κατάστασης δεν θα εμφανίζει την κυκλική διεύθυνση του κελιού αναφοράς όταν είναι ενεργοποιημένη η επιλογή Επαναληπτικός Υπολογισμός, επομένως πρέπει να την απενεργοποιήσετε προτού αρχίσετε να κοιτάζετε το βιβλίο εργασίας για κυκλικές αναφορές.
- Σε περίπτωση που δεν βρεθεί κυκλική αναφορά στο ενεργό φύλλο, η γραμμή κατάστασης εμφανίζει μόνο «Κυκλικές αναφορές» χωρίς διεύθυνση κελιού.
- Θα λάβετε μια κυκλική προτροπή αναφοράς μόνο μία φορά και αφού κάνετε κλικ στο «OK», δεν θα εμφανιστεί ξανά το μήνυμα την επόμενη φορά.
- Εάν το βιβλίο εργασίας σας έχει κυκλικές αναφορές, θα σας εμφανίζει την προτροπή κάθε φορά που το ανοίγετε μέχρι να επιλύσετε την κυκλική αναφορά ή μέχρι να ενεργοποιήσετε τον επαναληπτικό υπολογισμό.
Καταργήστε μια κυκλική αναφορά στο Excel
Η εύρεση κυκλικών αναφορών είναι εύκολη, αλλά η διόρθωση δεν είναι τόσο απλή. Δυστυχώς, δεν υπάρχει επιλογή στο Excel που θα σας επιτρέψει να αφαιρέσετε όλες τις κυκλικές αναφορές ταυτόχρονα.
Για να διορθώσετε κυκλικές αναφορές, πρέπει να βρείτε κάθε κυκλική αναφορά ξεχωριστά και να προσπαθήσετε να την τροποποιήσετε, να αφαιρέσετε εντελώς τον κυκλικό τύπο ή να τον αντικαταστήσετε με έναν άλλο.
Μερικές φορές, σε απλούς τύπους, το μόνο που χρειάζεται να κάνετε είναι να προσαρμόσετε ξανά τις παραμέτρους του τύπου έτσι ώστε να μην αναφέρεται στον εαυτό του. Για παράδειγμα, αλλάξτε τον τύπο στο B6 σε =SUM(B1:B5)*A5 (αλλαγή B6 σε B5).
Θα επιστρέψει το αποτέλεσμα του υπολογισμού ως «756».
Σε περιπτώσεις που είναι δύσκολο να βρεθεί μια κυκλική αναφορά του Excel, μπορείτε να χρησιμοποιήσετε τις λειτουργίες Trace Precedents και Trace Dependents για να την εντοπίσετε στην πηγή και να την λύσετε ένα προς ένα. Το βέλος δείχνει ποια κελιά επηρεάζονται από το ενεργό κελί.
Υπάρχουν δύο μέθοδοι ανίχνευσης που μπορούν να σας βοηθήσουν να διαγράψετε κυκλικές αναφορές δείχνοντας τις σχέσεις μεταξύ τύπων και κελιών.
Για να αποκτήσετε πρόσβαση στις μεθόδους ανίχνευσης, μεταβείτε στην καρτέλα "Τύποι" και, στη συνέχεια, κάντε κλικ είτε στην επιλογή "Προηγούμενα ανίχνευσης" είτε στην "Εξαρτημένη ανίχνευση" στην ομάδα Έλεγχος τύπου.
Ίχνη Προηγούμενα
Όταν κάνετε αυτήν την επιλογή, παρακολουθεί τα κελιά που επηρεάζουν την τιμή του ενεργού κελιού. Σχεδιάζει μια μπλε γραμμή που δείχνει ποια κελιά επηρεάζουν το τρέχον κελί. Το πλήκτρο συντόμευσης για τη χρήση προηγούμενων ιχνών είναι Alt + T U T.
Στο παρακάτω παράδειγμα, το μπλε βέλος δείχνει τα κελιά που επηρεάζουν την τιμή B6 είναι B1:B6 και A5. Όπως μπορείτε να δείτε παρακάτω, το κελί B6 είναι επίσης μέρος του τύπου, γεγονός που το καθιστά κυκλική αναφορά και αναγκάζει τον τύπο να επιστρέψει "0" ως αποτέλεσμα.
Αυτό μπορεί να διορθωθεί εύκολα αντικαθιστώντας το B6 με το B5 στο όρισμα του SUM: =SUM(B1:B5).
Trace Dependents
Η λειτουργία εξαρτώμενων ιχνών ανιχνεύει τα κελιά που εξαρτώνται από το επιλεγμένο κελί. Αυτή η δυνατότητα σχεδιάζει μια μπλε γραμμή που υποδεικνύει ποια κελιά επηρεάζονται από το επιλεγμένο κελί. Δηλαδή, εμφανίζει ποια κελιά περιέχουν τύπους που αναφέρονται στο ενεργό κελί. Το πλήκτρο συντόμευσης για τη χρήση εξαρτημένων είναι Alt + T U D.
Στο παρακάτω παράδειγμα, το κελί D3 επηρεάζεται από το B4. Εξαρτάται από το B4 για την αξία του για να παράγει αποτελέσματα. Ως εκ τούτου, το ίχνος εξαρτώμενο σχεδιάζει μια μπλε γραμμή από το B4 στο D3, υποδεικνύοντας ότι το D3 εξαρτάται από το B4.
Σκόπιμη χρήση κυκλικών αναφορών στο Excel
Η σκόπιμη χρήση κυκλικών αναφορών δεν συνιστάται, αλλά μπορεί να υπάρχουν μερικές σπάνιες περιπτώσεις όπου χρειάζεστε μια κυκλική αναφορά, ώστε να μπορείτε να λάβετε την έξοδο που θέλετε.
Ας το εξηγήσουμε χρησιμοποιώντας ένα παράδειγμα.
Αρχικά, ενεργοποιήστε τον "Επαναληπτικό Υπολογισμό" στο βιβλίο εργασίας του Excel. Αφού ενεργοποιήσετε τον Επαναληπτικό Υπολογισμό, μπορείτε να αρχίσετε να χρησιμοποιείτε κυκλικές αναφορές προς όφελός σας.
Ας υποθέσουμε ότι αγοράζετε ένα σπίτι και θέλετε να δώσετε μια προμήθεια 2% επί του συνολικού κόστους του σπιτιού στον αντιπρόσωπό σας. Το συνολικό κόστος θα υπολογιστεί στο κελί Β6 και το ποσοστό προμήθειας (αμοιβή αντιπροσώπου) υπολογίζεται στο Β4. Η προμήθεια υπολογίζεται από το συνολικό κόστος και το συνολικό κόστος περιλαμβάνει την προμήθεια. Δεδομένου ότι τα κελιά Β4 και Β6 εξαρτώνται το ένα από το άλλο, δημιουργεί μια κυκλική αναφορά.
Εισαγάγετε τον τύπο για να υπολογίσετε το συνολικό κόστος στο κελί B6:
=SUM(B1:B4)
Δεδομένου ότι το συνολικό κόστος περιλαμβάνει την αμοιβή αντιπροσώπου, συμπεριλάβαμε το Β4 στον παραπάνω τύπο.
Για να υπολογίσετε την προμήθεια αντιπροσώπου 2%, εισαγάγετε αυτόν τον τύπο στο B4:
=B6*2%Τώρα ο τύπος στο κελί B4 εξαρτάται από την τιμή του B6 για τον υπολογισμό του 2% της συνολικής αμοιβής και ο τύπος στο B6 εξαρτάται από το B4 για τον υπολογισμό του συνολικού κόστους (συμπεριλαμβανομένης της αμοιβής αντιπροσώπου), εξ ου και η κυκλική αναφορά.
Εάν ο επαναληπτικός υπολογισμός είναι ενεργοποιημένος, το Excel δεν θα σας δώσει προειδοποίηση ή 0 στο αποτέλεσμα. Αντίθετα, το αποτέλεσμα των κελιών Β6 και Β4 θα υπολογιστεί όπως φαίνεται παραπάνω.
Η επιλογή επαναληπτικών υπολογισμών είναι συνήθως απενεργοποιημένη από προεπιλογή. Εάν δεν το ενεργοποιήσατε και πότε εισάγετε τον τύπο στο B4 που θα δημιουργήσει μια κυκλική αναφορά. Το Excel θα εκδώσει την προειδοποίηση και όταν κάνετε κλικ στο "OK", θα εμφανιστεί το βέλος ανίχνευσης.
Αυτό είναι. Αυτό ήταν το μόνο που έπρεπε να ξέρετε για τις κυκλικές αναφορές στο Excel.