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

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

1. Σχετικοί σύνδεσμοι. Χρησιμοποιείται για απλούς υπολογισμούς. Η αντιγραφή ενός τύπου συνεπάγεται αλλαγή των συντεταγμένων.
2. Απόλυτοι σύνδεσμοι. Εάν χρειάζεται να κάνετε πιο σύνθετους υπολογισμούς, αυτή η επιλογή είναι κατάλληλη. Για τη διόρθωση χρησιμοποιήστε το σύμβολο "$". Παράδειγμα: $A$1.
3. μικτές συνδέσεις. Αυτός ο τύπος διευθυνσιοδότησης χρησιμοποιείται στους υπολογισμούς όταν είναι απαραίτητο να διορθώσετε μια στήλη ή μια γραμμή ξεχωριστά. Παράδειγμα: $A1 ή A$1.

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

Σχετική αναφορά κελιού στο Excel

Αυτό είναι ένα σύνολο χαρακτήρων που καθορίζουν τη θέση ενός κελιού. Οι σύνδεσμοι στο πρόγραμμα γράφονται αυτόματα με σχετική διεύθυνση. Για παράδειγμα: Α1, Α2, Β1, Β2. Η μετάβαση σε διαφορετική γραμμή ή στήλη αλλάζει τους χαρακτήρες στον τύπο. Για παράδειγμα, η αρχική θέση A1. Η οριζόντια μετακίνηση αλλάζει το γράμμα σε B1, C1, D1, κ.λπ. Με τον ίδιο τρόπο, οι αλλαγές συμβαίνουν όταν κινείστε κατά μήκος μιας κατακόρυφης γραμμής, μόνο σε αυτήν την περίπτωση ο αριθμός αλλάζει – A2, A3, A4, κ.λπ. Εάν είναι απαραίτητο να αντιγράψετε ένας υπολογισμός του ίδιου τύπου σε ένα παρακείμενο κελί, ένας υπολογισμός εκτελείται χρησιμοποιώντας μια σχετική αναφορά. Για να χρησιμοποιήσετε αυτήν τη δυνατότητα, ακολουθήστε μερικά βήματα:

1. Μόλις εισαχθούν τα δεδομένα στο κελί, μετακινήστε τον κέρσορα και κάντε κλικ με το ποντίκι. Η επισήμανση με πράσινο ορθογώνιο υποδηλώνει την ενεργοποίηση του κελιού και την ετοιμότητα για περαιτέρω εργασία.
2. Πατώντας το συνδυασμό πλήκτρων Ctrl + C, αντιγράφουμε τα περιεχόμενα στο πρόχειρο.
3. Ενεργοποιούμε το κελί στο οποίο θέλετε να μεταφέρετε δεδομένα ή έναν τύπο που γράφτηκε προηγουμένως.
4. Πατώντας το συνδυασμό Ctrl + V μεταφέρουμε τα δεδομένα που είναι αποθηκευμένα στο πρόχειρο του συστήματος.

Συμβουλή ειδικού! Για να εκτελέσετε τον ίδιο τύπο υπολογισμών στον πίνακα, χρησιμοποιήστε ένα life hack. Επιλέξτε το κελί που περιέχει τον τύπο που εισαγάγατε προηγουμένως. Περνώντας το δείκτη του ποντικιού πάνω από ένα μικρό τετράγωνο που εμφανίζεται στην κάτω δεξιά γωνία και κρατώντας πατημένο το αριστερό κουμπί του ποντικιού, σύρετε στην κάτω σειρά ή στην ακραία στήλη, ανάλογα με την ενέργεια που εκτελείται. Αφήνοντας το κουμπί του ποντικιού, ο υπολογισμός θα γίνει αυτόματα. Αυτό το εργαλείο ονομάζεται δείκτης αυτόματης πλήρωσης.

Παράδειγμα σχετικού συνδέσμου

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

Η σειρά των ενεργειών:

1. Το παράδειγμα δείχνει ότι οι στήλες Β και Γ χρησιμοποιήθηκαν για τη συμπλήρωση της ποσότητας των αγαθών που πωλήθηκαν και της τιμής της. Αντίστοιχα, για να γράψετε τον τύπο και να λάβετε την απάντηση, επιλέξτε τη στήλη D. Ο τύπος μοιάζει με αυτό: = B2 *C

Δώσε προσοχή! Για να διευκολύνετε τη διαδικασία σύνταξης μιας φόρμουλας, χρησιμοποιήστε ένα μικρό κόλπο. Βάλτε το σύμβολο "=", κάντε κλικ στην ποσότητα των προϊόντων που πωλήθηκαν, ορίστε το σύμβολο "*" και κάντε κλικ στην τιμή του προϊόντος. Ο τύπος μετά το πρόσημο ίσον θα γραφεί αυτόματα.

2. Για την τελική απάντηση, πατήστε «Enter». Στη συνέχεια, πρέπει να υπολογίσετε το συνολικό ποσό του κέρδους που λαμβάνεται από άλλους τύπους προϊόντων. Λοιπόν, εάν ο αριθμός των γραμμών δεν είναι μεγάλος, τότε όλοι οι χειρισμοί μπορούν να εκτελεστούν χειροκίνητα. Για να συμπληρώσετε μεγάλο αριθμό σειρών ταυτόχρονα στο Excel, υπάρχει μια χρήσιμη συνάρτηση που καθιστά δυνατή τη μεταφορά του τύπου σε άλλα κελιά.
3. Μετακινήστε τον κέρσορα στην κάτω δεξιά γωνία του ορθογωνίου με τον τύπο ή το τελικό αποτέλεσμα. Η εμφάνιση ενός μαύρου σταυρού χρησιμεύει ως σήμα ότι ο κέρσορας μπορεί να συρθεί προς τα κάτω. Έτσι, γίνεται αυτόματος υπολογισμός του κέρδους που λαμβάνεται για κάθε προϊόν ξεχωριστά.
4. Αφήνοντας το πατημένο κουμπί του ποντικιού, έχουμε τα σωστά αποτελέσματα σε όλες τις γραμμές.

Κάνοντας κλικ στο κελί D3, μπορείτε να δείτε ότι οι συντεταγμένες των κελιών έχουν αλλάξει αυτόματα και τώρα μοιάζουν με αυτό: =B3 *C3. Από αυτό προκύπτει ότι οι σύνδεσμοι ήταν σχετικοί.

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

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

1. Δημιουργήστε έναν πίνακα και συμπληρώστε: A – όνομα προϊόντος. Β – ποσότητα που πωλήθηκε. C – κόστος; D είναι το ποσό που ελήφθη. Ας υποθέσουμε ότι υπάρχουν μόνο 11 είδη στη συλλογή. Επομένως, λαμβάνοντας υπόψη την περιγραφή των στηλών, συμπληρώνονται 12 γραμμές και το συνολικό ποσό κέρδους είναι Δ
2. Κάντε κλικ στο κελί E2 και εισάγετε =Δ2 /D13.
3. Αφού πατήσετε το κουμπί «Enter», εμφανίζεται ο συντελεστής του σχετικού μεριδίου πωλήσεων του πρώτου είδους.
4. Τεντώστε τη στήλη προς τα κάτω και περιμένετε το αποτέλεσμα. Ωστόσο, το σύστημα δίνει το σφάλμα "#DIV/0!"

Ο λόγος για το σφάλμα είναι η χρήση σχετικής αναφοράς για υπολογισμούς. Ως αποτέλεσμα της αντιγραφής του τύπου, οι συντεταγμένες αλλάζουν. Δηλαδή, για το E3, ο τύπος θα μοιάζει με αυτό =Δ3 /D13. Επειδή το κελί D13 δεν είναι γεμάτο και θεωρητικά έχει μηδενική τιμή, το πρόγραμμα θα δώσει ένα σφάλμα με την πληροφορία ότι η διαίρεση με το μηδέν είναι αδύνατη.

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

Πώς δημιουργείτε έναν απόλυτο σύνδεσμο στο Excel

Χάρη στη χρήση του συμβόλου $, κατέστη δυνατός ο καθορισμός των συντεταγμένων των κελιών. Πώς λειτουργεί αυτό, θα εξετάσουμε περαιτέρω. Εφόσον το πρόγραμμα χρησιμοποιεί από προεπιλογή σχετική διευθυνσιοδότηση, κατά συνέπεια, για να γίνει απόλυτη, θα χρειαστεί να εκτελέσετε έναν αριθμό ενεργειών. Ας αναλύσουμε τη λύση στο σφάλμα "Πώς να βρείτε τον συντελεστή από την πώληση πολλών ειδών αγαθών", εκτελώντας τον υπολογισμό χρησιμοποιώντας απόλυτη διευθυνσιοδότηση:

1. Κάντε κλικ στο E2 και εισάγετε τις συντεταγμένες του συνδέσμου =Δ2 /D13. Εφόσον η σύνδεση είναι σχετική, πρέπει να οριστεί ένα σύμβολο για να διορθωθούν τα δεδομένα.
2. Διορθώστε τις συντεταγμένες του κελιού D Για να εκτελέσετε αυτήν την ενέργεια, προηγηθείτε του γράμματος που υποδεικνύει τη στήλη και τον αριθμό της γραμμής τοποθετώντας ένα σύμβολο "$".

Συμβουλή ειδικού! Για να διευκολυνθεί η εργασία εισαγωγής, αρκεί να ενεργοποιήσετε το κελί για την επεξεργασία του τύπου και να πατήσετε το πλήκτρο F4 αρκετές φορές. Μέχρι να αποκτήσετε ικανοποιητικές τιμές. Ο σωστός τύπος είναι ο εξής: =D2/$D$13.

3. Πατήστε το κουμπί «Enter». Ως αποτέλεσμα των ενεργειών που εκτελούνται, θα πρέπει να εμφανιστεί το σωστό αποτέλεσμα.
4. Σύρετε τον δείκτη στην κάτω γραμμή για να αντιγράψετε τον τύπο.

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

Πώς να βάλετε έναν μικτό σύνδεσμο στο Excel

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

• Για παράδειγμα, για να αλλάξετε τη θέση μιας γραμμής, πρέπει να γράψετε το σύμβολο $ μπροστά από τον προσδιορισμό του γράμματος.
• Αντίθετα, εάν το σύμβολο του δολαρίου είναι γραμμένο μετά τον χαρακτηρισμό του γράμματος, τότε οι δείκτες στη γραμμή θα παραμείνουν αμετάβλητοι.

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

1. Για ακριβείς υπολογισμούς, εισάγετε =D1/$D$3 και πατήστε «Enter». Το πρόγραμμα δίνει την ακριβή απάντηση.
2. Για να μετακινήσετε τον τύπο στα επόμενα κελιά στη στήλη και να λάβετε ακριβή αποτελέσματα, σύρετε τη λαβή στο κάτω κελί.
3. Ως αποτέλεσμα, το πρόγραμμα θα δώσει τους σωστούς υπολογισμούς.

Προσοχή! Εάν ορίσετε το σύμβολο $ μπροστά από το γράμμα, τότε το Excel θα δώσει ένα σφάλμα "#DIV/0!", που θα σημαίνει ότι αυτή η λειτουργία δεν μπορεί να εκτελεστεί.

Διευθυνσιοδότηση «SuperAbsolute».

Στο τέλος, ας δούμε ένα άλλο παράδειγμα απόλυτου συνδέσμου – τη διευθυνσιοδότηση «SuperAbsolute». Ποια είναι τα χαρακτηριστικά και οι διαφορές του. Πάρτε τον κατά προσέγγιση αριθμό 30 και πληκτρολογήστε τον στο κελί B2. Είναι αυτός ο αριθμός που θα είναι ο κύριος, είναι απαραίτητο να εκτελέσετε μια σειρά ενεργειών μαζί του, για παράδειγμα, να τον ανεβάσετε σε μια ισχύ.

1. Για τη σωστή εκτέλεση όλων των ενεργειών, εισαγάγετε τον ακόλουθο τύπο στη στήλη Γ: =$B$2^$D2. Στη στήλη Δ εισάγουμε την τιμή των μοιρών.

2. Αφού πατήσουμε το κουμπί «Enter» και ενεργοποιήσουμε τον τύπο, τεντώνουμε τον δείκτη προς τα κάτω στη στήλη.
3. Παίρνουμε τα σωστά αποτελέσματα.

Το συμπέρασμα είναι ότι όλες οι ενέργειες που πραγματοποιήθηκαν αναφέρονταν σε ένα σταθερό κελί B2, επομένως:

• Η αντιγραφή ενός τύπου από το κελί C3 στο κελί E3, F3 ή H3 δεν θα αλλάξει το αποτέλεσμα. Θα παραμείνει αμετάβλητο – 900.
• Εάν πρέπει να εισαγάγετε μια νέα στήλη, οι συντεταγμένες του κελιού με τον τύπο θα αλλάξουν, αλλά το αποτέλεσμα θα παραμείνει επίσης αμετάβλητο.

Αυτή είναι η ιδιαιτερότητα του συνδέσμου «SuperAbsolute»: αν χρειαστεί να μετακινήσετε το αποτέλεσμα δεν θα αλλάξει. Ωστόσο, υπάρχουν περιπτώσεις κατά τις οποίες εισάγονται δεδομένα από πηγές τρίτων. Έτσι, οι στήλες μετατοπίζονται στο πλάι και τα δεδομένα ορίζονται με τον παλιό τρόπο στη στήλη Β2. Τι συμβαίνει σε αυτή την περίπτωση; Όταν αναμιγνύεται, ο τύπος αλλάζει ανάλογα με την ενέργεια που εκτελείται, δηλαδή, δεν θα δείχνει πλέον στο B2, αλλά στο C2. Επειδή όμως η εισαγωγή έγινε στο Β2, το τελικό αποτέλεσμα θα είναι λάθος.

Αναφορά! Για να μπορέσετε να εισαγάγετε μακροεντολές από πηγές τρίτων, πρέπει να ενεργοποιήσετε τις ρυθμίσεις προγραμματιστή (είναι απενεργοποιημένες από προεπιλογή). Για να το κάνετε αυτό, μεταβείτε στις Επιλογές, ανοίξτε τις ρυθμίσεις της κορδέλας και επιλέξτε το πλαίσιο στη δεξιά στήλη απέναντι από το "Προγραμματιστής". Μετά από αυτό, θα ανοίξει η πρόσβαση σε πολλές λειτουργίες που προηγουμένως ήταν κρυμμένες από τα μάτια του μέσου χρήστη.

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

1. Αντί για τις συντεταγμένες του κελιού B2, εισαγάγετε τις ακόλουθες ενδείξεις: =INDIRECT("B2"). Ως αποτέλεσμα, μετά τη μετακίνηση, η σύνθεση της σύνθεσης θα μοιάζει με αυτό: =INDIRECT("B2")^$E2.
2. Χάρη σε αυτή τη συνάρτηση, ο σύνδεσμος δείχνει πάντα το τετράγωνο με συντεταγμένες Β2, ανεξάρτητα από το αν προστίθενται ή αφαιρούνται στήλες στον πίνακα.

Πρέπει να γίνει κατανοητό ότι ένα κελί που δεν περιέχει δεδομένα δείχνει πάντα την τιμή "0".

Συμπέρασμα

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