Για να κάνετε ακριβείς υπολογισμούς στο Excel, είναι απαραίτητο να κατανοήσετε πώς λειτουργούν οι διαφορετικοί τύποι αναφορών κελιών.
Αναφορές A1 έναντι R1C1
Τα φύλλα εργασίας του Excel περιέχουν πολλά κελιά και (από προεπιλογή) κάθε κελί προσδιορίζεται με το δικό του επιστολή στήλης ακολουθούμενο από τον αριθμό σειράς του. Αυτό είναι γνωστό ως αναφορά τύπου Α1. Παραδείγματα: Α1, Β4, Γ6
Στυλ αναφοράς Α1
Προαιρετικά, μπορείτε να μεταβείτε σε Λειτουργία αναφοράς R1C1 για αναφορά στη σειρά ενός κελιού & αριθμός στήληςΤο Αντί να αναφέρεστε στο κελί Α1, θα αναφερθείτε στο R1C1 (γραμμή 1, στήλη 1). Το κελί C4 θα αναφέρεται στο R4C3.
Στυλ αναφοράς R1C1
Η αναφορά σε στυλ R1C1 είναι εξαιρετικά ασυνήθιστη στο Excel. Εκτός εάν έχετε έναν καλό λόγο, μάλλον θα πρέπει να τηρήσετε την προεπιλεγμένη λειτουργία αναφοράς τύπου Α1. Ωστόσο, εάν χρησιμοποιείτε VBA, πιθανότατα θα συναντήσετε αυτό το στυλ αναφοράς.
Μεταβείτε στο στυλ αναφοράς R1C1
Για να αλλάξετε το στυλ αναφοράς, μεταβείτε στο Αρχείο> Επιλογή> ΤύποςΤο Επιλέξτε το πλαίσιο δίπλα Στυλ αναφοράς R1C1.
Ονομασμένο εύρος
Μία από τις δυνατότητες του Excel που δεν χρησιμοποιούνται πολύ είναι η δυνατότητα Named Ranges. Αντί να αναφέρετε σε ένα κελί (ή ομάδα κελιών) με βάση τη θέση του κελιού (ex B3 ή R3C2), μπορείτε να ονομάσετε αυτό το εύρος και απλώς να αναφέρετε το όνομα περιοχής στους τύπους σας.
Για να ονομάσετε ένα εύρος:
- Επιλέξτε το κελί ή τα κελιά που θέλετε να ονομάσετε
- Κάντε κλικ στο πλαίσιο Πλαίσιο ονόματος εύρους
- Εισαγάγετε το όνομα που θέλετε
- Πατήστε Enter
Τώρα μπορείτε να αναφέρετε το κελί Α1 πληκτρολογώντας = range_name1. Αυτό είναι πολύ χρήσιμο όταν εργάζεστε με μεγάλα βιβλία εργασίας με πολλά φύλλα εργασίας.
Σειρά κυττάρων
Όταν χρησιμοποιείτε τις ενσωματωμένες λειτουργίες του Excel, ίσως χρειαστεί να αναφέρετε εύρη κελιών. Τα εύρη κελιών εμφανίζονται ως ‘A1: B3’. Αυτή η αναφορά αναφέρεται σε όλα τα κύτταρα μεταξύ Α1 και Β3: κύτταρα Α1, Α2, Α3, Β1, Β2, Β3.
Για να επιλέξετε μια περιοχή κελιών κατά την εισαγωγή ενός τύπου:
- Πληκτρολογήστε το εύρος (διαχωρίστε το εύρος έναρξης και λήξης με ερωτηματικό)
- Χρησιμοποιήστε το ποντίκι σας για να κάνετε κλικ στην πρώτη αναφορά κελιού, κρατήστε πατημένο το κουμπί του ποντικιού και σύρετε το εύρος που θέλετε.
- Κρατήστε πατημένο το πλήκτρο shift και πλοηγηθείτε με τα βέλη για να επιλέξετε το εύρος σας
Απόλυτες (παγωμένες) και σχετικές αναφορές
Κατά την εισαγωγή αναφορών κελιών εντός τύπων, μπορείτε να χρησιμοποιήσετε σχετικές ή απόλυτες (παγωμένες) αναφορές. Οι σχετικές αναφορές θα μετακινηθούν αναλογικά όταν αντιγράψετε τον τύπο σε ένα νέο κελί. Οι απόλυτες αναφορές θα παραμείνουν αμετάβλητες. Ας δούμε μερικά παραδείγματα:
Σχετική αναφορά
Μια σχετική αναφορά στο Excel μοιάζει με αυτήν
= Α1
Όταν αντιγράφετε και επικολλάτε έναν τύπο με σχετικές αναφορές, οι σχετικές αναφορές θα μετακινηθούν αναλογικά. Εκτός εάν ορίζετε διαφορετικά, οι αναφορές κελιών σας θα είναι σχετικές (μη κατεψυγμένες) από προεπιλογή.
Παράδειγμα: Αν αντιγράψετε ‘= A1’ κάτω από μια σειρά, η αναφορά αλλάζει σε ‘= A2’.
Απόλυτες (κατεψυγμένες) αναφορές κυττάρων
Εάν δεν θέλετε οι αναφορές κελιών σας να μετακινούνται όταν αντιγράφετε έναν τύπο, μπορείτε να "παγώσετε" τις αναφορές κελιών σας προσθέτοντας σημάδια δολαρίου ($ s) μπροστά από την αναφορά που θέλετε να παγώσει. Τώρα, όταν αντιγράφετε και επικολλάτε τον τύπο, η αναφορά κελιού θα παραμείνει αμετάβλητη. Μπορείτε να επιλέξετε να παγώσετε την αναφορά γραμμής, την αναφορά στήλης ή και τα δύο.
Α1: Τίποτα δεν έχει παγώσει
$ A1: Η στήλη έχει παγώσει, αλλά η σειρά δεν έχει παγώσει
A $ 1: Η σειρά έχει παγώσει, αλλά η στήλη δεν έχει παγώσει
$ A $ 1: Και η σειρά και η στήλη έχουν παγώσει
Συντόμευση Απόλυτης Αναφοράς
Η μη αυτόματη προσθήκη πινακίδων δολαρίου ($ s) στους τύπους σας δεν είναι πολύ πρακτική. Αντ 'αυτού, ενώ δημιουργείτε τον τύπο σας, χρησιμοποιήστε το πλήκτρο F4 για εναλλαγή μεταξύ απόλυτων/σχετικών αναφορών κελιών.
Παράδειγμα Αναφοράς Απόλυτου Κυττάρου
Πότε πραγματικά θα χρειαστεί να παγώσετε μια αναφορά κελιού; Ένα κοινό παράδειγμα είναι όταν έχετε κελιά εισόδου που αναφέρονται συχνά. Στο παρακάτω παράδειγμα, θέλουμε να υπολογίσουμε τον φόρο πωλήσεων για κάθε ποσότητα στοιχείων μενού. Ο φόρος επί των πωλήσεων είναι σταθερός σε όλα τα είδη, επομένως θα αναφέρουμε επανειλημμένα το στοιχείο φόρου πωλήσεων.
Για να βρείτε τον συνολικό φόρο επί των πωλήσεων, εισαγάγετε τον τύπο ‘= (B3*C3)*$ C $ 1’ στη στήλη D και αντιγράψτε τον τύπο προς τα κάτω.
Μικτή Αναφορά
Μπορεί να έχετε ακούσει για μικτές αναφορές κυττάρων. Μικτή αναφορά είναι όταν η αναφορά γραμμής ή στήλης είναι κλειδωμένη (αλλά όχι και τα δύο).
Μικτή Αναφορά
Θυμηθείτε, χρησιμοποιώντας το πλήκτρο "F4" μπορείτε να κάνετε κύκλο στις σχετικές, απόλυτες αναφορές κελιών σας.
Αναφορές κελιών - Εισαγωγή & διαγραφή γραμμών/στηλών
Mayσως αναρωτιέστε τι συμβαίνει με τις αναφορές των κελιών σας όταν εισάγετε ή διαγράφετε γραμμές/στήλες;
Η αναφορά κελιού θα ενημερωθεί αυτόματα για να αναφέρεται στο αρχικό κελί. Αυτό συμβαίνει ανεξάρτητα από το αν η αναφορά κελιού είναι παγωμένη.
3D αναφορές
Μερικές φορές, μπορεί να χρειαστεί να εργαστείτε με πολλά φύλλα εργασίας με πανομοιότυπα μοτίβα δεδομένων. Το Excel σάς επιτρέπει να ανατρέχετε σε πολλά φύλλα ταυτόχρονα χωρίς να χρειάζεται να εισαγάγετε μη αυτόματα κάθε φύλλο εργασίας. Μπορείτε να αναφέρετε μια σειρά φύλλων παρόμοια με την αναφορά σε μια περιοχή κελιών. Παράδειγμα «Sheet1: Sheet5! A1» θα αναφέρει τα κελιά A1 σε όλα τα φύλλα από το Sheet1 έως το Sheet5.
Ας δούμε ένα παράδειγμα:
Θέλετε να προσθέσετε μαζί τις συνολικές μονάδες που πωλούνται για κάθε προϊόν σε όλα τα καταστήματα. Κάθε κατάστημα έχει το δικό του φύλλο εργασίας και όλα τα φύλλα εργασίας έχουν την ίδια μορφή. Θα μπορούσατε να δημιουργήσετε έναν τύπο παρόμοιο με αυτόν:
Αυτό δεν είναι πολύ δύσκολο μόνο με τέσσερα φύλλα εργασίας, αλλά τι γίνεται αν είχατε 40 φύλλα εργασίας; Θα θέλατε πραγματικά να προσθέσετε με μη αυτόματο τρόπο κάθε αναφορά κελιού;
Αντ 'αυτού, μπορείτε να χρησιμοποιήσετε μια αναφορά 3D για αναφορά πολλαπλών φύλλων ταυτόχρονα με ευκολία (παρόμοιο με το πώς μπορείτε να αναφέρετε μια σειρά κελιών).
Πρόσεχε! Η σειρά των φύλλων εργασίας σας έχει σημασία. Εάν μετακινήσετε ένα άλλο φύλλο μεταξύ των αναφερόμενων φύλλων (StoreA και StoreD), αυτό το φύλλο θα συμπεριληφθεί. Αντιστρόφως, εάν μετακινήσετε ένα φύλλο εκτός της περιοχής φύλλων (πριν από το StoreA ή μετά το StoreD), δεν θα περιλαμβάνεται πλέον.
Αναφορά κυκλικής κυψέλης
Μια κυκλική αναφορά κυψέλης είναι όταν ένα κελί παραπέμπει στον εαυτό του. Για παράδειγμα, εάν το αποτέλεσμα του κελιού Β1 χρησιμοποιείται ως είσοδος για το κελί Β1, τότε δημιουργείται μια κυκλική αναφορά. Το κελί δεν χρειάζεται να αναφέρεται απευθείας στον εαυτό του. Μπορεί να υπάρχουν ενδιάμεσα βήματα.
Παράδειγμα:
Σε αυτήν την περίπτωση, ο τύπος για το κελί Β2 είναι "A2+A3+B2". Επειδή βρίσκεστε στο κελί Β2, δεν μπορείτε να χρησιμοποιήσετε το Β2 στην εξίσωση. Αυτό θα ενεργοποιήσει την κυκλική αναφορά και η τιμή στο κελί "B2" θα οριστεί αυτόματα σε "0".
Συνήθως οι κυκλικές αναφορές είναι αποτέλεσμα σφάλματος χρήστη, αλλά σε περιπτώσεις όπου μπορεί να θέλετε να χρησιμοποιήσετε μια κυκλική αναφορά. Το κύριο παράδειγμα χρήσης μιας κυκλικής αναφοράς είναι ο υπολογισμός των τιμών επαναληπτικά. Για να το κάνετε αυτό, πρέπει να μεταβείτε στο Αρχείο> Επιλογές> Τύποι και Ενεργοποιημένος επαναληπτικός υπολογισμός:
Εξωτερικές αναφορές
Σε περιόδους υπολογισμού δεδομένων μπορεί να χρειαστεί να ανατρέξετε σε δεδομένα εκτός του βιβλίου εργασίας σας. Αυτό ονομάζεται εξωτερική αναφορά (σύνδεσμος).
Για να επιλέξετε μια εξωτερική αναφορά κατά τη δημιουργία ενός τύπου, μεταβείτε στο εξωτερικό βιβλίο εργασίας και επιλέξτε την αναφορά σας όπως θα κάνατε συνήθως.
Για να μεταβείτε στο άλλο βιβλίο εργασίας, μπορείτε να χρησιμοποιήσετε τη συντόμευση CTRL + TAB ή να μεταβείτε στο Προβολή> Αλλαγή Windows.
Αφού επιλέξετε το κελί, θα δείτε ότι μια εξωτερική αναφορά μοιάζει με αυτήν:
Παρατηρήστε ότι το όνομα του βιβλίου εργασίας περικλείεται σε αγκύλες [].
Μόλις κλείσετε το αναφερόμενο βιβλίο εργασίας, η αναφορά θα εμφανίσει τη θέση του αρχείου:
Όταν ανοίξετε ξανά το βιβλίο εργασίας που περιέχει τον εξωτερικό σύνδεσμο, θα σας ζητηθεί να ενεργοποιήσετε την αυτόματη ενημέρωση των συνδέσμων. Εάν το κάνετε αυτό, τότε το Excel θα ανοίξει την τιμή αναφοράς με την τρέχουσα τιμή του βιβλίου εργασίας. Ακόμα κι αν είναι κλειστό! Πρόσεχε! Αυτό μπορεί να το θέλετε ή όχι.
Ονομασμένες σειρές και εξωτερικές αναφορές
Τι συμβαίνει με την εξωτερική αναφορά κελιού όταν προστίθενται ή διαγράφονται γραμμές / στήλες από το βιβλίο εργασίας αναφοράς; Εάν και τα δύο βιβλία εργασίας είναι ανοιχτά, τότε οι αναφορές κελιών ενημερώνονται αυτόματα. Ωστόσο, εάν και τα δύο βιβλία εργασίας δεν είναι ανοιχτά, οι αναφορές κελιών δεν θα ενημερώνονται και δεν θα είναι πλέον έγκυρες. Αυτό αποτελεί τεράστια ανησυχία κατά τη σύνδεση με εξωτερικά βιβλία εργασίας. Πολλά σφάλματα προκαλούνται από αυτό.
Εάν συνδέετε με εξωτερικά βιβλία εργασίας, θα πρέπει να ονομάσετε την αναφορά κελιού με ένα όνομα περιοχής (ανατρέξτε στην προηγούμενη ενότητα για περισσότερες πληροφορίες). Τώρα ο τύπος σας θα αναφέρεται στο εύρος που ονομάζεται, ανεξάρτητα από τις αλλαγές που συμβαίνουν στο εξωτερικό βιβλίο εργασίας.
