Aby dokonać dokładnych obliczeń w programie Excel, konieczne jest zrozumienie, jak działają różne typy odwołań do komórek.
Referencje A1 kontra R1C1
Arkusze programu Excel zawierają wiele komórek i (domyślnie) każda komórka jest identyfikowana przez jej litera kolumny po którym następuje numer wiersza. Jest to znane jako odwołanie w stylu A1. Przykłady: A1, B4, C6
Styl odniesienia A1
Opcjonalnie możesz przełączyć się na Tryb odniesienia R1C1 odwoływać się do wiersza komórki i numer kolumny. Zamiast odwoływać się do komórki A1 odwołujesz się do W1K1 (wiersz 1, kolumna 1). Komórka C4 byłaby określana jako R4C3.
Styl odniesienia R1C1
Odwołania w stylu R1C1 są niezwykle rzadkie w programie Excel. O ile nie masz dobrego powodu, prawdopodobnie powinieneś trzymać się domyślnego trybu referencyjnego w stylu A1. Jeśli jednak używasz VBA, prawdopodobnie napotkasz ten styl odniesienia.
Przełącz na styl referencyjny R1K1
Aby zmienić styl odniesienia, przejdź do Plik > Opcja > Formuła. Zaznacz pole obok Styl odniesienia W1K1.
Nazwane zakresy
Jedną z najczęściej wykorzystywanych funkcji programu Excel jest funkcja Nazwane zakresy. Zamiast odwoływać się do komórki (lub grupy komórek) według jej lokalizacji (np. B3 lub R3C2), możesz nazwać ten zakres i po prostu odwołać się do nazwy zakresu w swoich formułach.
Aby nazwać zakres:
- Wybierz komórkę lub komórki, które chcesz nazwać
- Kliknij wewnątrz pola nazwy zakresu
- Wpisz żądaną nazwę
- Wciśnij Enter
Teraz możesz odwołać się do komórki A1, wpisując zamiast tego =nazwa_zakresu1. Jest to bardzo przydatne podczas pracy z dużymi skoroszytami zawierającymi wiele arkuszy.
Zakres komórek
Podczas korzystania z funkcji wbudowanych programu Excel może być konieczne odwołanie się do zakresów komórek. Zakresy komórek wyglądają tak: „A1:B3”. To odwołanie odnosi się do wszystkich komórek pomiędzy A1 i B3: komórki A1,A2,A3, B1, B2, B3.
Aby wybrać zakres komórek podczas wprowadzania formuły:
- Wpisz zakres (oddziel zakres początkowy i końcowy średnikiem)
- Użyj myszy, aby kliknąć pierwsze odwołanie do komórki, przytrzymaj przycisk myszy i przeciągnij żądany zakres.
- Przytrzymaj klawisz Shift i poruszaj się za pomocą klawiszy strzałek, aby wybrać zakres
Odniesienia absolutne (zamrożone) i względne
Podczas wprowadzania odwołań do komórek w formułach można używać odwołań względnych lub bezwzględnych (zamrożonych). Odwołania względne przesuną się proporcjonalnie po skopiowaniu formuły do nowej komórki. Odniesienia bezwzględne pozostaną niezmienione. Spójrzmy na kilka przykładów:
Odniesienie względne
Względne odniesienie w programie Excel wygląda tak
=A1
Gdy skopiujesz i wkleisz formułę z odniesieniami względnymi, odniesienia względne zostaną przesunięte proporcjonalnie. O ile nie określisz inaczej, odwołania do komórek będą domyślnie względne (odmrożone).
Przykład: Jeśli skopiujesz „=A1” w dół o jeden wiersz, odwołanie zmieni się na „=A2”.
Bezwzględne (zamrożone) odniesienia do komórek
Jeśli nie chcesz, aby odwołania do komórek były przenoszone podczas kopiowania formuły, możesz „zamrozić” odwołania do komórek, dodając znaki dolara ($s) przed odwołaniem, które chcesz zamrozić. Teraz, gdy skopiujesz i wkleisz formułę, odwołanie do komórki pozostanie niezmienione. Możesz zamrozić odwołanie do wiersza, odwołanie do kolumny lub oba.
A1: nic nie jest zamrożone
$ A1: Kolumna jest zablokowana, ale wiersz nie jest zablokowana
A $1: Wiersz jest zamrożony, ale kolumna nie jest zamrożona
$ A $1: Zarówno wiersz, jak i kolumna są zamrożone
Skrót bezwzględnego odniesienia
Ręczne dodawanie znaków dolara ($s) do formuł nie jest zbyt praktyczne. Zamiast tego podczas tworzenia formuły użyj klawisza F4, aby przełączać się między bezwzględnymi/względnymi odwołaniami do komórek.
Przykład odniesienia do komórki bezwzględnej
Kiedy faktycznie trzeba by zamrozić odwołanie do komórki? Jednym z typowych przykładów jest sytuacja, gdy masz komórki wejściowe, do których często się odwołuje. W poniższym przykładzie chcemy obliczyć podatek od sprzedaży dla każdej ilości pozycji menu. Podatek od sprzedaży jest stały we wszystkich elementach, dlatego będziemy wielokrotnie odwoływać się do komórki podatku od sprzedaży.
Aby znaleźć całkowity podatek od sprzedaży, wprowadź formułę „=(B3*C3)*$C$1” w kolumnie D i skopiuj formułę w dół.
Mieszane odniesienia
Być może słyszałeś o mieszanych odwołaniach do komórek. Odwołanie mieszane występuje wtedy, gdy odwołanie do wiersza lub kolumny jest zablokowane (ale nie oba).
Mieszane odniesienia
Pamiętaj, że używając klawisza „F4” możesz przełączać się między względnymi, bezwzględnymi odwołaniami do komórek.
Odwołania do komórek - wstawianie i usuwanie wierszy/kolumn
Być może zastanawiasz się, co dzieje się z odwołaniami do komórek podczas wstawiania lub usuwania wierszy/kolumn?
Odwołanie do komórki zostanie automatycznie zaktualizowane, aby odwoływać się do oryginalnej komórki. Dzieje się tak niezależnie od tego, czy odwołanie do komórki jest zablokowane.
Referencje 3D
Czasami może być konieczna praca z kilkoma arkuszami roboczymi o identycznych wzorcach danych. Excel umożliwia odwoływanie się do wielu arkuszy jednocześnie bez konieczności ręcznego wprowadzania każdego arkusza. Możesz odwoływać się do zakresu arkuszy w sposób podobny do tego, w jaki odwołuje się do zakresu komórek. Przykład „Arkusz1:Arkusz5!A1” odwołuje się do komórek A1 na wszystkich arkuszach od Arkusz1 do Arkusz5.
Przeanalizujmy przykład:
Chcesz zsumować łączną liczbę sprzedanych jednostek każdego produktu we wszystkich sklepach. Każdy sklep ma swój własny arkusz, a wszystkie arkusze mają identyczny format. Możesz stworzyć formułę podobną do tej:
Nie jest to zbyt trudne z tylko czterema arkuszami roboczymi, ale co by było, gdybyś miał 40 arkuszy roboczych? Czy naprawdę chcesz ręcznie dodać każde odwołanie do komórki?
Zamiast tego możesz użyć odniesienia 3D, aby z łatwością odwoływać się do wielu arkuszy naraz (podobnie jak możesz odwoływać się do zakresu komórek).
Bądź ostrożny! Kolejność twoich arkuszy ma znaczenie. Jeśli przesuniesz inny arkusz między arkuszami, do których istnieją odniesienia (SklepA i SklepD), arkusz ten zostanie uwzględniony. I odwrotnie, jeśli przeniesiesz arkusz poza zakres arkuszy (przed MagazynA lub po MagazynD), nie będzie on już uwzględniany.
Odniesienie do komórki okrągłej
Odwołanie cykliczne do komórki ma miejsce, gdy komórka odwołuje się do siebie. Na przykład, jeśli wynik komórki B1 jest używany jako dane wejściowe dla komórki B1, tworzone jest odwołanie cykliczne. Komórka nie musi odwoływać się bezpośrednio do siebie Mogą istnieć etapy pośrednie.
Przykład:
W tym przypadku formuła dla komórki B2 to „A2+A3+B2”. Ponieważ jesteś w komórce B2, nie możesz użyć B2 w równaniu. Spowodowałoby to odwołanie cykliczne, a wartość w komórce „B2” zostanie automatycznie ustawiona na „0”.
Zwykle odwołania cykliczne są wynikiem błędu użytkownika, ale w pewnych okolicznościach możesz chcieć użyć odwołania cyklicznego. Podstawowym przykładem użycia odwołania cyklicznego jest iteracyjne obliczanie wartości. Aby to zrobić, musisz przejść do Plik > Opcje > Formuły oraz Włączone obliczenia iteracyjne:
Odniesienia zewnętrzne
Czasami podczas obliczania danych może być konieczne odwołanie się do danych spoza skoroszytu. Nazywa się to odnośnikiem zewnętrznym (link).
Aby wybrać odwołanie zewnętrzne podczas tworzenia formuły, przejdź do skoroszytu zewnętrznego i wybierz odwołanie w normalny sposób.
Aby przejść do drugiego skoroszytu, możesz użyć skrótu CTRL + TAB lub przejść do Widok > Przełącz okna.
Po wybraniu komórki zobaczysz odwołanie zewnętrzne, które wygląda tak:
Zwróć uwagę, że nazwa skoroszytu jest ujęta w nawiasy kwadratowe [].
Po zamknięciu skoroszytu, do którego się odwołuje, odwołanie pokaże lokalizację pliku:
Po ponownym otwarciu skoroszytu zawierającego łącze zewnętrzne zostanie wyświetlony monit o włączenie automatycznej aktualizacji łączy. Jeśli to zrobisz, program Excel otworzy wartość odniesienia z bieżącą wartością skoroszytu. Nawet jeśli jest zamknięty! Bądź ostrożny! Możesz tego chcieć lub nie.
Nazwane zakresy i odniesienia zewnętrzne
Co dzieje się z odwołaniem do komórki zewnętrznej, gdy wiersze/kolumny są dodawane lub usuwane ze skoroszytu odwołań? Jeśli oba skoroszyty są otwarte, odwołania do komórek są aktualizowane automatycznie. Jeśli jednak oba skoroszyty nie są otwarte, odwołania do komórek nie zostaną zaktualizowane i nie będą już prawidłowe. Jest to ogromny problem przy łączeniu ze skoroszytami zewnętrznymi. Spowodowane jest to wieloma błędami.
Jeśli łączysz się ze skoroszytami zewnętrznymi, powinieneś nazwać odwołanie do komórki nazwanym zakresem (więcej informacji w poprzedniej sekcji). Teraz Twoja formuła będzie odwoływać się do nazwanego zakresu bez względu na zmiany zachodzące w skoroszycie zewnętrznym.
