Αυτό το άρθρο θα σας βοηθήσει να κατανοήσετε όλες τις αιτίες των σφαλμάτων #SPILL καθώς και τις λύσεις για να τα διορθώσετε στο Excel 365.
#ΔΙΑΡΡΟΗ! είναι ένα νέο είδος σφάλματος του Excel που εμφανίζεται κυρίως όταν ένας τύπος που παράγει πολλαπλά αποτελέσματα υπολογισμού προσπαθεί να εμφανίσει τις εξόδους του σε ένα εύρος διαρροής, αλλά αυτό το εύρος περιέχει ήδη κάποια άλλα δεδομένα.
Τα δεδομένα αποκλεισμού μπορεί να είναι οτιδήποτε, συμπεριλαμβανομένης της τιμής κειμένου, των συγχωνευμένων κελιών, ενός χαρακτήρα απλού διαστήματος ή ακόμα και όταν δεν υπάρχει αρκετό μέρος για την επιστροφή των αποτελεσμάτων. Η λύση είναι απλή, είτε διαγράψτε το εύρος οποιωνδήποτε δεδομένων αποκλεισμού είτε επιλέξτε μια κενή συστοιχία κελιών που δεν περιέχουν κανένα είδος δεδομένων σε αυτήν.
Το σφάλμα διαρροής συμβαίνει συνήθως κατά τον υπολογισμό τύπων δυναμικού πίνακα, επειδή ο τύπος δυναμικού πίνακα είναι αυτός που εξάγει τα αποτελέσματα σε πολλαπλά κελιά ή έναν πίνακα. Ας δούμε λεπτομερέστερα και ας καταλάβουμε τι προκαλεί αυτό το σφάλμα στο Excel και πώς να το επιλύσετε.
Τι προκαλεί ένα σφάλμα διαρροής;
Από την κυκλοφορία των Dynamic arrays το 2018, οι τύποι του Excel μπορούν να χειρίζονται πολλές τιμές ταυτόχρονα και να επιστρέφουν αποτελέσματα σε περισσότερα από ένα κελιά. Οι δυναμικοί πίνακες είναι πίνακες με δυνατότητα αλλαγής μεγέθους που επιτρέπουν στους τύπους να επιστρέφουν πολλαπλά αποτελέσματα σε μια περιοχή κελιών στο φύλλο εργασίας με βάση έναν τύπο που έχει εισαχθεί σε ένα μεμονωμένο κελί.
Όταν ένας τύπος δυναμικού πίνακα επιστρέφει πολλαπλά αποτελέσματα, αυτά τα αποτελέσματα διαχέονται αυτόματα στα γειτονικά κελιά. Αυτή η συμπεριφορά ονομάζεται «Διαρροή» στο Excel. Και το εύρος των κελιών όπου διαχέονται τα αποτελέσματα ονομάζεται «Εύρος διαρροής». Το εύρος διαρροής θα επεκταθεί ή θα συρρικνωθεί αυτόματα με βάση τις τιμές πηγής.
Εάν ένας τύπος προσπαθεί να γεμίσει ένα εύρος διαρροής με πολλαπλά αποτελέσματα, αλλά αποκλείεται από κάτι σε αυτό το εύρος, τότε εμφανίζεται ένα σφάλμα #SPILL.
Το Excel διαθέτει τώρα 9 συναρτήσεις που χρησιμοποιούν τη λειτουργία Dynamic Array για την επίλυση προβλημάτων, σε αυτές περιλαμβάνονται:
- ΑΛΛΗΛΟΥΧΙΑ
- ΦΙΛΤΡΟ
- ΜΕΤΑΘΕΤΩ
- ΕΙΔΟΣ
- ΤΑΞΙΝΟΜΗΣΗ ΚΑΤΑ
- RANDARRAY
- ΜΟΝΑΔΙΚΟΣ
- XLOOKUP
- XMATCH
Οι τύποι δυναμικού πίνακα είναι διαθέσιμοι μόνο στο «Excel 365» και προς το παρόν δεν υποστηρίζονται από κανένα από τα λογισμικά του Excel εκτός σύνδεσης (π.χ. Microsoft Excel 2016, 2019).
Τα σφάλματα διαρροής δεν προκαλούνται μόνο από παρεμπόδιση δεδομένων, υπάρχουν διάφοροι λόγοι για τους οποίους μπορεί να εμφανιστεί το σφάλμα #Spill. Ας εξερευνήσουμε τις διαφορετικές καταστάσεις όπου μπορεί να συναντήσετε το #SPILL! σφάλμα και πώς να τα διορθώσετε.
Το εύρος διαρροής δεν είναι κενό
Μία από τις κύριες αιτίες για το σφάλμα διαρροής είναι ότι η περιοχή διαρροής δεν είναι κενή. Για παράδειγμα, εάν προσπαθείτε να εμφανίσετε 10 αποτελέσματα, αλλά εάν υπάρχουν δεδομένα σε οποιοδήποτε από τα κελιά στην περιοχή διαρροής, ο τύπος επιστρέφει ένα #SPILL! λάθος.
Παράδειγμα 1:
Στο παρακάτω παράδειγμα, έχουμε εισαγάγει τη συνάρτηση TRANSPOSE στο κελί C2 για να μετατρέψουμε την κατακόρυφη περιοχή των κελιών (B2:B5) σε μια οριζόντια περιοχή (C2:F2). Αντί να αλλάξει τη στήλη σε μια γραμμή, το Excel μας δείχνει το #SPILL! λάθος.
Και όταν κάνετε κλικ στο κελί του τύπου, θα δείτε ένα διακεκομμένο μπλε περίγραμμα που υποδεικνύει την περιοχή/εύρος διαρροής (C2:F2) που απαιτείται για την εμφάνιση των αποτελεσμάτων όπως φαίνεται παρακάτω. Επίσης, θα παρατηρήσετε ένα κίτρινο προειδοποιητικό σήμα με ένα θαυμαστικό πάνω του.
Για να κατανοήσετε την αιτία του σφάλματος, κάντε κλικ στο εικονίδιο προειδοποίησης δίπλα στο σφάλμα και δείτε το μήνυμα στην πρώτη γραμμή τονισμένο με γκρι χρώμα. Όπως μπορείτε να δείτε, λέει "Το εύρος διαρροής δεν είναι κενό" εδώ.
Το πρόβλημα εδώ είναι ότι τα κελιά στην περιοχή διαρροής D2 και E2 έχουν χαρακτήρες κειμένου (όχι κενούς), επομένως, το σφάλμα.
Λύση:
Η λύση είναι απλή, είτε διαγράψτε τα δεδομένα (είτε μετακινήστε είτε διαγράψτε) που βρίσκονται στην περιοχή διαρροής είτε μετακινήστε τον τύπο σε άλλη τοποθεσία όπου δεν υπάρχει εμπόδιο.
Μόλις διαγράψετε ή μετακινήσετε το μπλοκάρισμα, το Excel θα συμπληρώσει αυτόματα τα κελιά με τα αποτελέσματα του τύπου. Εδώ, όταν διαγράφουμε το κείμενο στα D2 και E2, ο τύπος μεταφέρει τη στήλη σε σειρά όπως προβλέπεται.
Παράδειγμα 2:
Στο παρακάτω παράδειγμα, παρόλο που το εύρος διαρροής εμφανίζεται κενό, ο τύπος εξακολουθεί να δείχνει τη διαρροή! λάθος. Είναι επειδή η διαρροή δεν είναι στην πραγματικότητα άδεια, έχει έναν αόρατο χαρακτήρα διαστήματος σε ένα από τα κελιά.
Είναι δύσκολο να εντοπίσετε χαρακτήρες διαστήματος ή οποιονδήποτε άλλο αόρατο χαρακτήρα που κρύβεται σε κάτι που φαίνεται να είναι κενά κελιά. Για να βρείτε τέτοια κελιά με ανεπιθύμητα δεδομένα, κάντε κλικ στο Error floatie (προειδοποιητικό σύμβολο) και επιλέξτε «Επιλογή κελιών απόφραξης» από το μενού και θα σας μεταφέρει στο κελί που περιέχει τα δεδομένα παρεμπόδισης.
Όπως μπορείτε να δείτε, στο παρακάτω στιγμιότυπο οθόνης, το κελί E2 έχει δύο χαρακτήρες διαστήματος. Όταν διαγράψετε αυτά τα δεδομένα, θα λάβετε το σωστό αποτέλεσμα.
Μερικές φορές, ο αόρατος χαρακτήρας θα μπορούσε να είναι ένα κείμενο μορφοποιημένο με το ίδιο χρώμα γραμματοσειράς με το χρώμα πλήρωσης του κελιού ή μια τιμή κελιού προσαρμοσμένη μορφοποιημένη με τον αριθμό κωδικού ;;;. Όταν προσαρμόζετε τη διαμόρφωση μιας τιμής κελιού με ;;;, θα κρύβει οτιδήποτε σε αυτό το κελί, ανεξάρτητα από το χρώμα της γραμματοσειράς ή το χρώμα του κελιού.
Το εύρος διαρροής περιέχει συγχωνευμένα κελιά
Μερικές φορές, το #SPILL! παρουσιάζεται σφάλμα όταν η περιοχή διαρροής περιέχει τα συγχωνευμένα κελιά. Ο τύπος δυναμικού πίνακα δεν λειτουργεί με συγχωνευμένα κελιά. Για να το διορθώσετε αυτό, το μόνο που έχετε να κάνετε είναι να καταργήσετε τη συγχώνευση κελιών στην περιοχή διαρροής ή να μετακινήσετε τον τύπο σε άλλη περιοχή που δεν έχει συγχωνευμένα κελιά.
Στο παρακάτω παράδειγμα, παρόλο που το εύρος διαρροής είναι κενό (C2:CC8), ο τύπος επιστρέφει το σφάλμα διαρροής. Είναι επειδή τα κελιά C4 και C5 συγχωνεύονται.
Για να βεβαιωθείτε ότι τα συγχωνευμένα κελιά είναι ο λόγος που λαμβάνετε το σφάλμα, κάντε κλικ στοπροειδοποιητικό σήμα και επαληθεύστε την αιτία – «Το εύρος διαρροής έχει συγχωνευθεί το κελί».
Λύση:
Για να καταργήσετε τη συγχώνευση των κελιών, επιλέξτε τα συγχωνευμένα κελιά και, στη συνέχεια, στην καρτέλα «Αρχική σελίδα», κάντε κλικ στο κουμπί «Συγχώνευση & Κέντρο» και επιλέξτε «Κατάργηση συγχώνευσης κελιών».
Εάν δυσκολεύεστε να εντοπίσετε τα συγχωνευμένα κελιά στο μεγάλο υπολογιστικό φύλλο σας, κάντε κλικ στην επιλογή «Επιλογή κελιών απόφραξης» από το μενού προειδοποιητικού σήματος για να μεταβείτε στα συγχωνευμένα κελιά.
Εύρος διαρροής στον πίνακα
Οι τύποι συστοιχιών που έχουν χυθεί δεν υποστηρίζονται σε πίνακες του Excel. Ο τύπος δυναμικού πίνακα πρέπει να εισαχθεί μόνο σε ένα μεμονωμένο κελί. Εάν εισαγάγετε έναν τύπο χυμένου πίνακα σε έναν πίνακα ή όταν η περιοχή διαρροής πέφτει σε έναν πίνακα, θα λάβετε το σφάλμα διαρροής. Όταν συμβεί αυτό, δοκιμάστε να μετατρέψετε τον πίνακα σε κανονικό εύρος ή μετακινήστε τον τύπο εκτός του πίνακα.
Για παράδειγμα, όταν εισάγουμε τον ακόλουθο τύπο χυμένου εύρους σε έναν πίνακα του Excel, θα λαμβάνουμε ένα σφάλμα διαρροής σε κάθε κελί του πίνακα, όχι μόνο στο κελί του τύπου. Αυτό συμβαίνει επειδή το Excel αντιγράφει αυτόματα οποιονδήποτε τύπο που έχει εισαχθεί σε έναν πίνακα σε κάθε κελί της στήλης του πίνακα.
Επίσης, θα λάβετε ένα σφάλμα διαρροής όταν ένας τύπος προσπαθεί να χυθεί τα αποτελέσματα σε έναν πίνακα. Στο παρακάτω στιγμιότυπο οθόνης, η περιοχή διαρροής εμπίπτει στον υπάρχοντα πίνακα, οπότε λαμβάνουμε ένα σφάλμα διαρροής.
Για να επιβεβαιώσετε την αιτία πίσω από αυτό το σφάλμα, κάντε κλικ στο προειδοποιητικό σήμα και δείτε την αιτία του σφάλματος – «Εύρος διαρροής στον πίνακα»
Λύση:
Για να διορθώσετε το σφάλμα, θα χρειαστεί να επαναφέρετε τον πίνακα Excel στο εύρος. Για να το κάνετε αυτό, κάντε δεξί κλικ οπουδήποτε μέσα στον πίνακα, κάντε κλικ στο «Πίνακας» και, στη συνέχεια, επιλέξτε την επιλογή «Μετατροπή σε εύρος». Εναλλακτικά, μπορείτε να κάνετε αριστερό κλικ οπουδήποτε μέσα στον πίνακα, μετά να μεταβείτε στην καρτέλα «Σχεδίαση πίνακα» και να επιλέξετε την επιλογή «Μετατροπή σε εύρος».
Το εύρος διαρροής είναι άγνωστο
Εάν το Excel δεν μπόρεσε να καθορίσει το μέγεθος του χυμένου πίνακα, θα ενεργοποιήσει το σφάλμα διαρροής. Μερικές φορές, ο τύπος επιτρέπει σε έναν δυναμικό πίνακα να αλλάζει το μέγεθος μεταξύ κάθε περασμάτων υπολογισμού. Εάν το μέγεθος του δυναμικού πίνακα συνεχίζει να αλλάζει κατά τη διάρκεια των περασμάτων υπολογισμών και δεν εξισορροπείται, θα προκαλέσει το #SPILL! Λάθος.
Αυτός ο τύπος σφάλματος διαρροής συνήθως ενεργοποιείται όταν χρησιμοποιούνται πτητικές συναρτήσεις όπως οι συναρτήσεις RAND, RANDARRAY, RANDBETWEEN, OFFSET και INDIRECT.
Για παράδειγμα, όταν χρησιμοποιούμε τον παρακάτω τύπο στο κελί B3, λαμβάνουμε το σφάλμα διαρροής:
=SEQUENCE(RANDBETWEEN(1, 500))Στο παράδειγμα, η συνάρτηση RANDBETWEEN επιστρέφει έναν τυχαίο ακέραιο μεταξύ των αριθμών 1 και 500 και η έξοδος της αλλάζει συνεχώς. Και η συνάρτηση SEQUENCE δεν γνωρίζει πόσες τιμές πρέπει να παράγει σε έναν πίνακα διαρροής. Ως εκ τούτου, το σφάλμα #SPILL.
Μπορείτε επίσης να επιβεβαιώσετε την αιτία του σφάλματος κάνοντας κλικ στο Προειδοποιητικό σύμβολο – «Το εύρος διαρροής είναι άγνωστο».
Λύση:
Για να διορθώσετε το σφάλμα για αυτόν τον τύπο, η μόνη σας επιλογή είναι να χρησιμοποιήσετε έναν διαφορετικό τύπο για τον υπολογισμό σας.
Το εύρος διαρροής είναι πολύ μεγάλο
Κατά καιρούς, μπορεί να εκτελέσετε έναν τύπο που βγάζει ένα εύρος διαρροής που είναι πολύ μεγάλο για να το χειριστεί το φύλλο εργασίας και μπορεί να εκτείνεται πέρα από τις άκρες του φύλλου εργασίας. Όταν συμβεί αυτό, μπορεί να πάρετε #SPILL! λάθος. Για να διορθώσετε αυτό το ζήτημα, μπορείτε να δοκιμάσετε να αναφέρετε ένα συγκεκριμένο εύρος ή ένα κελί αντί για ολόκληρες στήλες ή να χρησιμοποιήσετε τον χαρακτήρα «@» για να ενεργοποιήσετε την άρρητη τομή
Στο παρακάτω παράδειγμα, προσπαθούμε να υπολογίσουμε το 20% των αριθμών πωλήσεων στη στήλη Α και να επιστρέψουμε τα αποτελέσματα στη στήλη Β, αλλά αντ' αυτού, λαμβάνουμε ένα σφάλμα διαρροής.
Ο τύπος στο Β3 υπολογίζει το 20% της τιμής στο Α3, μετά το 20% της τιμής στο Α4 και ούτω καθεξής. Παράγει πάνω από ένα εκατομμύριο αποτελέσματα (1.048.576) και τα χύνει όλα στη στήλη Β ξεκινώντας από το κελί B3, αλλά θα φτάσει στο τέλος του φύλλου εργασίας. Δεν υπάρχει αρκετός χώρος για την εμφάνιση όλων των εξόδων, ως αποτέλεσμα, λαμβάνουμε ένα σφάλμα #SPILL.
Όπως μπορείτε να δείτε, η αιτία αυτού του σφάλματος είναι ότι το – «Το εύρος διαρροής είναι πολύ μεγάλο».
Λύσεις:
Για να επιλύσετε αυτό το ζήτημα, δοκιμάστε να αλλάξετε ολόκληρη τη στήλη με ένα σχετικό εύρος ή μια αναφορά ενός κελιού ή προσθέστε τον τελεστή @ για να πραγματοποιήσετε σιωπηρή τομή.
Διορθώστε 1: Μπορείτε να δοκιμάσετε να αναφέρετε εύρη και όχι ολόκληρες στήλες. Εδώ, αλλάζουμε ολόκληρο το εύρος A:A με A3:A11 στον τύπο και ο τύπος θα συμπληρώσει αυτόματα το εύρος με αποτελέσματα.
Διόρθωση 2: Αντικαταστήστε ολόκληρη τη στήλη μόνο με την αναφορά κελιού στην ίδια σειρά (A3) και, στη συνέχεια, αντιγράψτε τον τύπο προς τα κάτω στην περιοχή χρησιμοποιώντας τη λαβή πλήρωσης.
Διόρθωση 3: Μπορείτε επίσης να δοκιμάσετε να προσθέσετε τον τελεστή @ πριν από την αναφορά για να πραγματοποιήσετε σιωπηρή τομή. Αυτό θα εμφανίσει την έξοδο μόνο στο κελί του τύπου.
Στη συνέχεια, αντιγράψτε τον τύπο από το κελί B3 στο υπόλοιπο εύρος.
Σημείωση: Όταν επεξεργάζεστε έναν τύπο που έχει χυθεί, μπορείτε να επεξεργαστείτε μόνο το πρώτο κελί στην περιοχή/εύρος διαρροής. Μπορείτε να δείτε τον τύπο σε άλλα κελιά της περιοχής διαρροής, αλλά θα είναι γκριζαρισμένα και δεν μπορούν να ενημερωθούν.
Μη διαθέσιμη μνήμη
Εάν εκτελέσετε έναν τύπο χυμένου πίνακα που προκαλεί εξάντληση της μνήμης του Excel, μπορεί να ενεργοποιήσει το σφάλμα #SPILL. Κάτω από αυτές τις συνθήκες, δοκιμάστε να αναφέρετε έναν μικρότερο πίνακα ή εύρος.
Μη αναγνωρισμένη / Επιστροφή
Μπορείτε επίσης να λάβετε ένα σφάλμα διαρροής ακόμη και όταν το Excel δεν αναγνωρίζει ή δεν μπορεί να συμβιβάσει την αιτία του σφάλματος. Σε τέτοιες περιπτώσεις, ελέγξτε ξανά τον τύπο σας και βεβαιωθείτε ότι όλες οι παράμετροι των συναρτήσεων είναι σωστές.
Τώρα, γνωρίζετε όλες τις αιτίες και τις λύσεις για το #SPILL! σφάλματα στο Excel 365.