Σχετική και απόλυτη αναφορά κυψέλης και μορφοποίηση

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

ΣΧΟΛΙΚΗ ΠΛΟΗΓΗΣΗ

1. Γιατί χρειάζεστε τύπους και λειτουργίες?
2. Ορισμός και δημιουργία μιας φόρμουλας
3. Σχετική και απόλυτη αναφορά κυψέλης και μορφοποίηση
4. Χρήσιμες λειτουργίες που πρέπει να γνωρίζετε
5. Αναζητήσεις, γραφήματα, στατιστικά στοιχεία και πίνακες περιστροφής

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

Τι είναι το Cell Reference?

Ως "αναφορά κυψέλης" νοείται το κύτταρο στο οποίο αναφέρεται ένα άλλο κύτταρο. Για παράδειγμα, εάν στο κελί A1 έχετε = A2. Στη συνέχεια, το A1 αναφέρεται στο Α2.

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

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

Το πρώτο κελί στο υπολογιστικό φύλλο είναι A1, το οποίο σημαίνει στήλη Α, σειρά 1, Β3 αναφέρεται στο κελί που βρίσκεται στη δεύτερη στήλη, στην τρίτη σειρά και ούτω καθεξής.

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

Τύποι αναφορών κυττάρων

Υπάρχουν τρεις τύποι αναφορών κελιών.

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

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

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

Σχετικές αναφορές

Ας αναφερθούμε σε εκείνο το προηγούμενο παράδειγμα - ας υποθέσουμε ότι στο κελί Α1 έχουμε έναν τύπο που απλά λέει = Α2. Αυτό σημαίνει ότι το Excel εξάγεται στο κελί A1 ό, τι εισάγεται στο κελί A2. Στο κελί A2 έχουμε πληκτρολογήσει "A2" έτσι Excel δείχνει την τιμή "A2" στο κελί A1.

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

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

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

Αντιγράψαμε το κελί δύο στήλες προς τα δεξιά και τέσσερα προς τα κάτω. Αυτό σημαίνει ότι έχουμε αλλάξει το κελί που αναφέρεται δύο σε τέσσερα και τέσσερα κάτω. Α1 = Α2 τώρα είναι C5 = C6. Αντί να αναφέρεται στο Α2, τώρα το κύτταρο C5 αναφέρεται στο κελί C6.

Η τιμή που εμφανίζεται είναι 0, επειδή το κελί C6 είναι κενό. Στο κελί C6 γράφουμε "Είμαι C6" και τώρα το C5 εμφανίζει "Είμαι C6".

Παράδειγμα: Τύπος κειμένου

Ας δοκιμάσουμε ένα άλλο παράδειγμα. Θυμηθείτε από το Μάθημα 2, όπου έπρεπε να χωρίσουμε ένα πλήρες όνομα στο όνομα και το επώνυμο; Τι συμβαίνει όταν αντιγράψουμε αυτόν τον τύπο?

Γράψτε τον τύπο = ΔΕΞΙΑ (A3, LEN (A3) - FIND (",", A3) - 1) ή αντιγράψτε το κείμενο στο κελί C3. Μην αντιγράφετε το πραγματικό κελί, μόνο το κείμενο, αντιγράψτε το κείμενο, αλλιώς θα ενημερώσει την αναφορά.

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

Τώρα έχουμε:

Τίποτα δεν περίπλοκο, έχουμε μόλις γράψει μια νέα φόρμουλα στο κελί C3. Τώρα αντιγράψτε το C3 στα κελιά C2 και C4. Παρατηρήστε τα παρακάτω αποτελέσματα:

Τώρα έχουμε τα ονόματα του Alexander Hamilton και Thomas Jefferson.

Χρησιμοποιήστε τον κέρσορα για να επισημάνετε τα κελιά C2, C3 και C4. Τοποθετήστε το δρομέα στο κελί B2 και επικολλήστε τα περιεχόμενα. Κοιτάξτε τι συνέβη - παίρνουμε ένα σφάλμα: "#REF." Γιατί είναι αυτό?

Όταν αντιγράψαμε τα κελιά από τη στήλη C στη στήλη Β, ενημερώσαμε τη στήλη αναφοράς μία προς τα αριστερά = ΔΕΞΙΑ (A2, LEN (A2) - FIND ("," A2).

Αλλάζει κάθε αναφορά στο A2 στη στήλη στα αριστερά του A, αλλά δεν υπάρχει στήλη στα αριστερά της στήλης A. Ο υπολογιστής δεν ξέρει τι εννοείτε.

Ο νέος τύπος στο B2, για παράδειγμα, είναι = RIGHT (#REF!, LEN (# REF!) - FIND (",", # REF!) - 1)

Αντιγραφή μιας φόρμουλας σε μια σειρά κυττάρων

Η αντιγραφή κυττάρων είναι πολύ βολική επειδή μπορείτε να γράψετε ένα τύπο και να το αντιγράψετε σε μια μεγάλη περιοχή και η αναφορά ενημερώνεται. Με τον τρόπο αυτό αποφεύγετε να επεξεργάζεστε κάθε κελί για να βεβαιωθείτε ότι δείχνει τη σωστή θέση.

Με τον όρο "σειρά" εννοούμε περισσότερα από ένα κελί. Για παράδειγμα, (C1: C10) σημαίνει όλα τα κύτταρα από το κύτταρο C1 έως το κύτταρο C10. Έτσι είναι μια στήλη κυττάρων. Ένα άλλο παράδειγμα (A1: AZ1) είναι η πρώτη σειρά από τη στήλη Α στη στήλη AZ.

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

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

Τώρα αντιγράψτε τη φόρμουλα στο κελί C3 (= B3 + C2) στην υπόλοιπη στήλη για να δώσετε ένα τρέχον υπόλοιπο για τον προϋπολογισμό μας. Το Excel ενημερώνει την αναφορά κελιού καθώς την αντιγράφετε. Το αποτέλεσμα φαίνεται παρακάτω:

Όπως βλέπετε, κάθε νέο κελί ενημερώνεται συγγενής στη νέα θέση, οπότε η κυψέλη C4 ενημερώνει τον τύπο της ως = B4 + C3:

Οι ενημερώσεις κυττάρων C5 σε = B5 + C4 και ούτω καθεξής:

Απόλυτες αναφορές

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

Για παράδειγμα, πληκτρολογήστε τον τύπο = $ A $ 1 σε οποιοδήποτε κελί. Το $ μπροστά από τη στήλη Α σημαίνει ότι δεν αλλάζει η στήλη, το $ μπροστά από τη σειρά 1 σημαίνει ότι δεν αλλάζει η στήλη όταν αντιγράφετε ή μετακινείτε το κελί σε οποιοδήποτε άλλο κελί.

Όπως μπορείτε να δείτε στο παρακάτω παράδειγμα, στο κελί B1 έχουμε μια σχετική αναφορά = A1. Όταν αντιγράψουμε το B1 στα τέσσερα κελιά κάτω από αυτό, η σχετική αναφορά = A1 αλλάζει στο κελί προς τα αριστερά, έτσι ώστε το B2 να γίνει A2, B3 να γίνει A3, κλπ. Αυτά τα κύτταρα προφανώς δεν έχουν εισαχθεί αξία, έτσι η παραγωγή είναι μηδέν.

Ωστόσο, εάν χρησιμοποιούμε = $ A1 $ 1, όπως στο C1 και το αντιγράψουμε στα τέσσερα κελιά κάτω από αυτό, η αναφορά είναι απόλυτη, έτσι ποτέ δεν αλλάζει και η έξοδος είναι πάντα ίση με την τιμή στο κελί Α1.

Ας υποθέσουμε ότι παρακολουθείτε το ενδιαφέρον σας, όπως στο παρακάτω παράδειγμα. Ο τύπος στο C4 = B4 * B1 είναι το "επιτόκιο" * "υπόλοιπο" = "τόκος ετησίως".

Τώρα, έχετε αλλάξει τον προϋπολογισμό σας και έχετε αποθηκεύσει επιπλέον $ 2.000 για να αγοράσετε ένα αμοιβαίο κεφάλαιο. Ας υποθέσουμε ότι είναι ένα ταμείο σταθερού επιτοκίου και πληρώνει το ίδιο επιτόκιο. Καταχωρίστε τον νέο λογαριασμό και την ισορροπία στο υπολογιστικό φύλλο και στη συνέχεια αντιγράψτε τον τύπο = B4 * B1 από το κελί C4 στο κελί C5.

Ο νέος προϋπολογισμός μοιάζει με αυτό:

Το νέο αμοιβαίο κεφάλαιο κερδίζει $ 0 σε τόκους ετησίως, κάτι που δεν μπορεί να είναι σωστό, αφού το επιτόκιο είναι σαφώς 5 τοις εκατό.

Το Excel επισημαίνει τα κελιά στα οποία αναφέρεται ένας τύπος. Μπορείτε να δείτε παραπάνω ότι η αναφορά στο επιτόκιο (B1) μετακινείται στο κενό κελί B2. Θα έπρεπε να έχουμε κάνει την αναφορά στην Β1 απόλυτη γράφοντας $ B $ 1 χρησιμοποιώντας το σύμβολο των δολαρίων για να αγκυροβολήσετε τη γραμμή και τη στήλη αναφοράς.

Επαναλάβετε τον πρώτο υπολογισμό στο C4 για να διαβάσετε = B4 * $ B $ 1 όπως φαίνεται παρακάτω:

Στη συνέχεια αντιγράψτε αυτόν τον τύπο από C4 σε C5. Το υπολογιστικό φύλλο μοιάζει τώρα:

Αφού αντιγράψαμε τη φόρμουλα ένα κελί κάτω, δηλ. Αυξήσαμε τη σειρά κατά μία, ο νέος τύπος είναι = B5 * $ B $ 1. Το επιτόκιο αμοιβαίου κεφαλαίου υπολογίζεται σωστά τώρα, επειδή το επιτόκιο είναι αγκυροβολημένο στο στοιχείο Β1.

Αυτό είναι ένα καλό παράδειγμα όταν θα μπορούσατε να χρησιμοποιήσετε ένα "όνομα" για να αναφερθείτε σε ένα κελί. Ένα όνομα είναι μια απόλυτη αναφορά. Για παράδειγμα, για να αντιστοιχίσετε το όνομα "επιτόκιο" στο κελί B1, κάντε δεξί κλικ στο κελί και στη συνέχεια επιλέξτε "define name".

Τα ονόματα μπορούν να αναφέρονται σε ένα κελί ή ένα εύρος και μπορείτε να χρησιμοποιήσετε ένα όνομα σε μια φόρμουλα, για παράδειγμα = interest_rate * 8 είναι το ίδιο με το γράψιμο = $ B $ 1 * 8.

Μικτές αναφορές

Οι μικτές αναφορές είναι όταν είτε η σειρά ή στήλη είναι αγκυρωμένη.

Για παράδειγμα, ας υποθέσουμε ότι είστε αγρότης που κάνει έναν προϋπολογισμό. Διαθέτετε επίσης ένα κατάστημα τροφοδοσίας και πωλείτε σπόρους. Πρόκειται να φυτέψετε καλαμπόκι, σόγια και μηδική. Το υπολογιστικό φύλλο που ακολουθεί δείχνει το κόστος ανά στρέμμα. Το "κόστος ανά στρέμμα" = "τιμή ανά λίβρα" * "λίρες σπόρων ανά στρέμμα" - αυτό είναι που θα σας κοστίσει για να φυτέψετε ένα στρέμμα.

Καταχωρίστε το κόστος ανά στρέμμα ως = $ B2 * C2 στο κελί D2. Λέτε ότι θέλετε να αγκυρώσετε τη στήλη των τιμών ανά λίβρα. Στη συνέχεια, αντιγράψτε αυτόν τον τύπο στις άλλες σειρές στην ίδια στήλη:

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

Προσθέτουμε δύο στήλες: "λίβρα σπόρου στο απόθεμα" και στη συνέχεια "αξία αποθέματος". Τώρα, αντιγράψτε το κελί D2 στο F4 και σημειώστε ότι η αναφορά σειράς στο πρώτο μέρος του αρχικού τύπου ($ B2) 4 αλλά η στήλη παραμένει σταθερή επειδή το $ αγκυρώνει σε "B."

Αυτή είναι μια μικτή αναφορά επειδή η στήλη είναι απόλυτη και η σειρά είναι σχετική.

Βιβλιογραφικές αναφορές

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

Για παράδειγμα, δεν μπορείτε να γράψετε c3 = c3 + 1. Αυτός ο υπολογισμός ονομάζεται "επανάληψη" που σημαίνει ότι επαναλαμβάνεται. Το Excel δεν υποστηρίζει επανάληψη επειδή υπολογίζει τα πάντα μόνο μία φορά.

Εάν προσπαθήσετε να το κάνετε πληκτρολογώντας το SUM (B1: B5) στο κελί B5:

Εμφανίζεται μια προειδοποιητική οθόνη:

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

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

Αναφορές σε άλλα φύλλα εργασίας

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

Τα φύλλα εργασίας από προεπιλογή ονομάζονται Φύλλο1, Φύλλο2 κ.ο.κ. Δημιουργείτε ένα νέο κάνοντας κλικ στο "+" στο κάτω μέρος της οθόνης του Excel.

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

Ή μπορείτε απλά να κάνετε διπλό κλικ στην καρτέλα και να το μετονομάσετε.

Η σύνταξη για μια αναφορά φύλλου εργασίας είναι = φύλλο εργασίας! Μπορείτε να χρησιμοποιήσετε αυτό το είδος αναφοράς όταν χρησιμοποιείται η ίδια τιμή σε δύο φύλλα εργασίας, παραδείγματα ίσως είναι:

• Η σημερινή ημερομηνία
• Συναλλαγματική ισοτιμία από Δολάρια σε Ευρώ
• Οτιδήποτε σχετίζεται με όλα τα φύλλα εργασίας του βιβλίου εργασίας

Ακολουθεί ένα παράδειγμα φύλλου εργασίας "ενδιαφέρον" που αναφέρεται σε φύλλο εργασίας "δάνειο", κελιά Β1.

Αν δούμε το φύλλο εργασίας "δάνειο", μπορούμε να δούμε την αναφορά στο ποσό του δανείου:

Αμέσως μετά…

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

Αυτό είναι για το μάθημα του σήμερα, στο Μάθημα 4, θα συζητήσουμε μερικές χρήσιμες λειτουργίες που θα θέλατε να μάθετε για την καθημερινή χρήση του Excel.