Απόλυτη αναφορά στο Excel (τρόπος δημιουργίας και χρήσης)

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

• Τι είναι απόλυτη αναφορά
• Πώς να κάνετε απόλυτες αναφορές
• Χρησιμοποιήστε απόλυτη αναφορά με παραδείγματα
• Υπολογίστε το ποσοστό του συνόλου
• Αναζητήστε μια τιμή και επιστρέψτε την αντίστοιχη τιμή αντιστοίχισης
• 2 κλικ για μαζική μετατροπή των αναφορών κελιών σε απόλυτες με Kutools
• Σχετική αναφορά και μικτή αναφορά
• Πράγματα που πρέπει να θυμόμαστε

Δωρεάν λήψη του δείγματος αρχείου

Βίντεο: Απόλυτη αναφορά

Τι είναι απόλυτη αναφορά

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

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

Μια απόλυτη αναφορά δημιουργείται προσθέτοντας ένα σύμβολο δολαρίου ($) πριν από τις αναφορές στηλών και σειρών στον τύπο. Για παράδειγμα, για να δημιουργήσετε μια απόλυτη αναφορά για το κελί A1, θα πρέπει να το αντιπροσωπεύσετε ως $A$1.

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

Για παράδειγμα, το εύρος A4:C7 περιέχει τις τιμές των προϊόντων και θέλετε να λάβετε τον πληρωτέο φόρο κάθε προϊόντος με βάση τον φορολογικό συντελεστή στο κελί B2.

Εάν χρησιμοποιείτε μια σχετική αναφορά στον τύπο όπως "=B5*B2", επιστρέφονται εσφαλμένα αποτελέσματα όταν σύρετε τη λαβή αυτόματης συμπλήρωσης προς τα κάτω για να εφαρμόσετε αυτόν τον τύπο. Δεδομένου ότι η αναφορά στο κελί B2 θα αλλάξει σε σχέση με τα κελιά του τύπου. Τώρα, ο τύπος στο κελί C6 είναι "=B6*B3" και ο τύπος στο κελί C7 είναι "=B7*B4"

Ωστόσο, εάν χρησιμοποιήσετε μια απόλυτη αναφορά στο κελί B2 με τον τύπο "=B5*$B$2", θα διασφαλίσετε ότι ο φορολογικός συντελεστής παραμένει ίδιος για όλα τα κελιά όταν ο τύπος σύρεται προς τα κάτω χρησιμοποιώντας τη λαβή αυτόματης συμπλήρωσης, τα αποτελέσματα είναι σωστά.

Χρήση σχετικής αναφοράς		Χρήση απόλυτης αναφοράς

Πώς να κάνετε απόλυτες αναφορές

Για να κάνετε μια απόλυτη αναφορά στο Excel, προσθέστε σύμβολα δολαρίου ($) πριν από τις αναφορές στηλών και σειρών σε έναν τύπο. Υπάρχουν δύο τρόποι για να δημιουργήσετε απόλυτη αναφορά:

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

Θα μπορούσατε να προσθέσετε μη αυτόματα σύμβολα δολαρίου ($) πριν από τις αναφορές στηλών και σειρών που θέλετε να κάνετε απόλυτες, ενώ πληκτρολογείτε τον τύπο σε ένα κελί.

Για παράδειγμα, εάν θέλετε να προσθέσετε τους αριθμούς στο κελί A1 και B1 και να τους κάνετε και τους δύο απόλυτους, απλώς πληκτρολογήστε τον τύπο ως "=$A$1+$B$1". Αυτό θα διασφαλίσει ότι οι αναφορές κελιών παραμένουν σταθερές όταν ο τύπος αντιγράφεται ή μετακινείται σε άλλα κελιά.

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

Χρησιμοποιώντας τη συντόμευση F4 για τη μετατροπή της σχετικής αναφοράς σε απόλυτη

1. Κάντε διπλό κλικ στο κελί με τον τύπο για να μπείτε στη λειτουργία επεξεργασίας.
2. Τοποθετήστε τον κέρσορα στην αναφορά κελιού που θέλετε να κάνετε απόλυτη.
3. Πατήστε το πλήκτρο "F4" στο πληκτρολόγιο για εναλλαγή των τύπων αναφοράς μέχρι να προστεθούν τα σύμβολα του δολαρίου πριν από τις αναφορές στηλών και σειρών.
4. Πατήστε το πλήκτρο "Enter" για έξοδο από τη λειτουργία επεξεργασίας και εφαρμογή των αλλαγών.

Το πλήκτρο F4 εναλλάσσει τις αναφορές μεταξύ σχετικών, απόλυτων και μικτών αναφορών.

A1 → $A$1 → A$1 → $A1 → A1

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

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Χρησιμοποιήστε απόλυτη αναφορά με παραδείγματα

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

Παράδειγμα 1 Υπολογίστε το ποσοστό του συνόλου

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

Ο γενικός τύπος για τον υπολογισμό του ποσοστού του συνόλου:

Percentage = Sale/Amount

Χρησιμοποιήστε τη σχετική αναφορά στον τύπο για να πάρετε το ποσοστό του πρώτου φρούτου ως εξής:

=B4/B8

Όταν σύρετε τη λαβή αυτόματης πλήρωσης προς τα κάτω για να υπολογίσετε το ποσοστό άλλων φρούτων, #DIV/0! τα σφάλματα θα επιστραφούν.

Δεδομένου ότι όταν σύρετε τη λαβή αυτόματης συμπλήρωσης για να αντιγράψετε τον τύπο στα παρακάτω κελιά, η σχετική αναφορά B8 προσαρμόζεται αυτόματα σε άλλες αναφορές κελιών (B9, B10, B11) με βάση τις σχετικές θέσεις τους. Και τα κελιά B9, B10 και B11 είναι άδεια (μηδενικά), όταν ο διαιρέτης είναι μηδέν, ο τύπος επιστρέφει σε σφάλμα.

Για να διορθώσετε τα σφάλματα, σε αυτήν την περίπτωση, πρέπει να κάνετε την αναφορά κελιού B8 απόλυτη ($B$8) στον τύπο για να μην αλλάζει όταν μετακινείτε ή αντιγράφετε τον τύπο σε οποιοδήποτε σημείο. Τώρα ο τύπος ενημερώνεται σε:

=B4/$B$8

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

Παράδειγμα 2 Αναζητήστε μια τιμή και επιστρέψτε στην αντίστοιχη τιμή αντιστοίχισης

Υποθέτοντας ότι θέλετε να αναζητήσετε τη λίστα ονομάτων στο D4:D5 και να επιστρέψετε τους αντίστοιχους μισθούς τους με βάση τα ονόματα του προσωπικού και τον αντίστοιχο ετήσιο μισθό που παρέχεται στο εύρος (A4:B8).

Ο γενικός τύπος για αναζήτηση είναι:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Εάν χρησιμοποιείτε τη σχετική αναφορά στον τύπο για να αναζητήσετε μια τιμή και να επιστρέψετε την αντίστοιχη τιμή αντιστοίχισης ως εξής:

=VLOOKUP(D4,A4:B8,2,FALSE)

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

Όταν σύρετε τη λαβή πλήρωσης προς τα κάτω για να αντιγράψετε τον τύπο στο παρακάτω κελί, οι αναφορές στον τύπο προσαρμόζονται αυτόματα κατά μία σειρά. Ως αποτέλεσμα, η αναφορά στο εύρος του πίνακα, A4:B8, γίνεται A5:B9. Επειδή το Lisa δεν μπορεί να βρεθεί στην περιοχή A5:B9, ο τύπος επιστρέφει ένα σφάλμα.

Για να αποφύγετε τα σφάλματα, χρησιμοποιήστε την απόλυτη αναφορά $A$4:$B$8 αντί της σχετικής αναφοράς A4:B8 στον τύπο:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

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

2 κλικ για μαζική μετατροπή των αναφορών κελιών σε απόλυτες με Kutools

Είτε επιλέξετε να πληκτρολογήσετε χειροκίνητα είτε να χρησιμοποιήσετε τη συντόμευση F4, μπορείτε να αλλάζετε μόνο έναν τύπο κάθε φορά στο Excel. Εάν θέλετε να κάνετε απόλυτες αναφορές κελιών σε εκατοντάδες τύπους στο Excel, το εργαλείο "Μετατροπή αναφορών" του... Kutools for Excel"μπορεί να σας βοηθήσει να διαχειριστείτε την εργασία με 2 κλικ.

Για να κάνετε τις αναφορές κελιών απόλυτες σε πολλούς τύπους, επιλέξτε τα κελιά του τύπου και κάντε κλικ στο "Kutools" > "Περισσότερα" > "Μετατροπή Αναφορών". Στη συνέχεια, επιλέξτε την επιλογή "Σε απόλυτη τιμή" και κάντε κλικ στο "Ok" ή στο "Εφαρμογή". Τώρα όλες οι αναφορές κελιών των επιλεγμένων τύπων έχουν μετατραπεί σε απόλυτες τιμές.

Σχετική αναφορά και μικτή αναφορά

Εκτός από την απόλυτη αναφορά, υπάρχουν άλλοι δύο τύποι αναφοράς: σχετική αναφορά και μικτή αναφορά.

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

Για παράδειγμα, όταν πληκτρολογείτε έναν τύπο σε ένα κελί όπως "=A1+1", μετά σύρετε τη λαβή αυτόματης συμπλήρωσης προς τα κάτω για να συμπληρώσετε αυτόν τον τύπο στο επόμενο κελί, ο τύπος θα αλλάξει αυτόματα σε "=A2+1".

Μικτή αναφορά αποτελείται τόσο από απόλυτη αναφορά όσο και από σχετική αναφορά. Με άλλα λόγια, η μικτή αναφορά χρησιμοποιεί το σύμβολο του δολαρίου ($) για να διορθώσει είτε τη γραμμή είτε τη στήλη όταν ένας τύπος αντιγράφεται ή συμπληρώνεται.

Πάρτε για παράδειγμα έναν πίνακα πολλαπλασιασμού, οι σειρές και οι στήλες αναφέρουν τους αριθμούς από το 1 έως το 9, τους οποίους θα πολλαπλασιάσετε ο ένας τον άλλον.

Για να ξεκινήσετε, μπορείτε να χρησιμοποιήσετε τον τύπο "=B3*C2" στο κελί C3 για να πολλαπλασιάσετε το 1 στο κελί B3 με τον αριθμό (1) στην πρώτη στήλη. Ωστόσο, όταν σύρετε τη λαβή αυτόματης συμπλήρωσης προς τα δεξιά για να γεμίσετε τα άλλα κελιά, θα παρατηρήσετε ότι όλα τα αποτελέσματα είναι λανθασμένα εκτός από το πρώτο.

Αυτό συμβαίνει επειδή όταν αντιγράφετε τον τύπο προς τα δεξιά, η θέση της γραμμής δεν θα αλλάξει, αλλά η θέση της στήλης αλλάζει από B3 σε C3, D3 κ.λπ.. Ως αποτέλεσμα, οι τύποι στα δεξιά κελιά (D3, E3, κ.λπ.) αλλάξτε σε "=C3*D2", "=D3*E2" και ούτω καθεξής, όταν θέλετε πραγματικά να είναι "=B3*D2", "=B3*E2" και ούτω καθεξής.

Σε αυτήν την περίπτωση, πρέπει να προσθέσετε ένα σύμβολο προσθήκης δολαρίου ($) για να κλειδώσετε την αναφορά στήλης "B3". Χρησιμοποιήστε τον τύπο όπως παρακάτω:

=$B3*C2

Τώρα, όταν σύρετε τον τύπο προς τα δεξιά, τα αποτελέσματα είναι σωστά.

Στη συνέχεια, πρέπει να πολλαπλασιάσετε τον αριθμό 1 στο κελί C2 με τους αριθμούς στις παρακάτω σειρές.

Όταν αντιγράφετε τον τύπο προς τα κάτω, η θέση της στήλης του κελιού C2 δεν θα αλλάξει, αλλά η θέση της γραμμής αλλάζει από C2 σε C3, C4 κ.λπ. Ως αποτέλεσμα, οι τύποι στα παρακάτω κελιά αλλάζουν σε "=$B4C3", "=$B5C4", κ.λπ. που θα παράγουν λανθασμένα αποτελέσματα.

Για να λύσετε αυτό το πρόβλημα, αλλάξτε το "C2" σε "C$2" για να διατηρήσετε σταθερή την αναφορά της σειράς όταν σύρετε τη λαβή αυτόματης συμπλήρωσης προς τα κάτω για να συμπληρώσετε τους τύπους.

=$B3*C$2

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

Πράγματα που πρέπει να θυμόμαστε

• Περίληψη αναφορών κελιών

  Χαρακτηριστικά	Παράδειγμα	Σύνοψη
  Απόλυτη αναφορά	$A$1	Μην αλλάζετε ποτέ όταν ο τύπος αντιγράφεται σε άλλα κελιά
  Σχετική αναφορά	A1	Η αναφορά τόσο της γραμμής όσο και της στήλης αλλάζει με βάση τη σχετική θέση όταν ο τύπος αντιγράφεται σε άλλα κελιά
  Μικτή αναφορά	$A1/A$1	Η αναφορά γραμμής αλλάζει όταν ο τύπος αντιγράφεται σε άλλα κελιά, αλλά η αναφορά στήλης είναι σταθερή/Η αναφορά στηλών αλλάζει όταν ο τύπος αντιγράφεται σε άλλα κελιά, αλλά η αναφορά σειρών είναι σταθερή.

• Γενικά, οι απόλυτες αναφορές δεν αλλάζουν ποτέ όταν μετακινείται ένας τύπος. Ωστόσο, οι απόλυτες αναφορές θα προσαρμόζονται αυτόματα όταν μια γραμμή ή στήλη προστίθεται ή αφαιρείται από την κορυφή ή αριστερά στο φύλλο εργασίας. Για παράδειγμα, σε έναν τύπο "=$A$1+1", όταν εισάγετε μια σειρά στο επάνω μέρος του φύλλου, ο τύπος θα αλλάξει αυτόματα σε "=$A$2+1".

  

• Το πλήκτρο "F4" μπορεί να εναλλάσσεται μεταξύ σχετικών, απόλυτων και μικτών αναφορών.