Excel 2019 (część 14): Względne i bezwzględne odwołania do komórek

W programie Excel istnieją dwa typy odwołań do komórek: względne i bezwzględne. Odniesienia względne i bezwzględne zachowują się inaczej po skopiowaniu i wypełnieniu innych komórek. Odniesienia względne zmieniają się, gdy formuła jest kopiowana do innej komórki. Z drugiej strony odniesienia bezwzględne pozostają niezmienione niezależnie od tego, gdzie zostaną skopiowane.

Odniesienie względne

Domyślnie wszystkie odwołania do komórek są odniesieniami względnymi. Po skopiowaniu do wielu komórek zmienią się one w zależności od względnego położenia wierszy i kolumn. Na przykład, jeśli skopiujesz formułę =A1+B1 z wiersza 1 do wiersza 2, formuła zmieni się na =A2+B2. Odniesienia względne są szczególnie wygodne, gdy trzeba powtórzyć te same obliczenia w wielu wierszach lub kolumnach.

Jak tworzyć i kopiować formuły przy użyciu odniesień względnych

Poniższy przykład ma na celu utworzenie formuły, która pomnoży cenę każdego przedmiotu przez ilość. Zamiast tworzyć nową formułę dla każdego wiersza, możesz utworzyć pojedynczą formułę w komórce D4 , a następnie skopiować ją do pozostałych wierszy. W przykładzie zostaną użyte odniesienia względne, aby formuła obliczyła prawidłową sumę dla każdego elementu.

1. Wybierz komórkę, która będzie zawierać formułę. Na przykład wybierzemy komórkę D4.

2. Wprowadź wzór, aby obliczyć żądaną wartość. Na przykład wprowadź =B4*C4.

3. Naciśnij Enter na klawiaturze. Formuła zostanie obliczona, a wyniki wyświetlone w komórce.

4. Znajdź uchwyt wypełniania w prawym dolnym rogu żądanej komórki. Przykład określi położenie uchwytu wypełniania dla komórki D4.

5. Kliknij i przeciągnij uchwyt wypełniania do komórek, które chcesz wypełnić. Na przykład wybierzemy komórki D5:D13.

6. Zwolnij mysz. Formuła zostanie skopiowana do wybranych komórek wraz z odniesieniami względnymi, wyświetlając wyniki w każdej komórce.

Możesz dwukrotnie kliknąć wypełnione komórki, aby sprawdzić dokładność ich formuł. Względne odwołania do komórek muszą być różne dla każdej komórki, w zależności od ich wiersza.

Absolutne odniesienie

Może się zdarzyć, że nie chcesz, aby odwołanie do komórki uległo zmianie po skopiowaniu do innych komórek. W przeciwieństwie do odniesień względnych, odniesienia bezwzględne nie zmieniają się po skopiowaniu lub wypełnieniu. Możesz użyć odniesienia bezwzględnego, aby zachować stały wiersz i/lub kolumnę.

Odniesienie bezwzględne określa się we wzorze poprzez dodanie znaku dolara ( $ ). Ten symbol może poprzedzać odwołanie do kolumny, odwołanie do wiersza lub jedno i drugie.

Ogólnie rzecz biorąc, podczas tworzenia formuł zawierających odwołania bezwzględne będziesz używać formatu $A$2 . Pozostałe dwa formaty są stosowane znacznie rzadziej.

Podczas pisania formuły możesz nacisnąć klawisz F4 na klawiaturze, aby przełączać się między względnymi i bezwzględnymi odwołaniami do komórek, jak pokazano na poniższym filmie. Jest to prosty sposób na szybkie wstawienie odniesienia bezwzględnego.

Jak tworzyć i kopiować formuły przy użyciu odwołań bezwzględnych

W poniższym przykładzie komórka E2 (która zawiera stawkę podatku 7,5%) zostanie wykorzystana do obliczenia podatku od sprzedaży dla każdego towaru w kolumnie D. Aby mieć pewność, że odniesienie do stawki podatku pozostanie takie samo – nawet jeśli formuła zostanie skopiowana i wypełniona w innych komórkach – komórka $E$2 musi być ustawiona jako odniesienie bezwzględne.

1. Wybierz komórkę, która będzie zawierać formułę. Na przykład wybierzemy komórkę D4.

2. Wprowadź wzór, aby obliczyć żądaną wartość. W przykładzie wprowadzonoby =(B4*C4)*$E$2 , dzięki czemu $E$2 byłoby odniesieniem bezwzględnym.

3. Naciśnij Enter na klawiaturze. Formuła wykona obliczenia, a wynik zostanie wyświetlony w komórce.

4. Znajdź uchwyt wypełniania w prawym dolnym rogu żądanej komórki. Przykład określi położenie uchwytu wypełniania dla komórki D4.

5. Kliknij i przeciągnij uchwyt wypełniania do komórek, które chcesz wypełnić ( w przykładzie komórki D5:D13 ).

6. Zwolnij mysz. Formuła zostanie skopiowana do wybranych komórek z odniesieniami bezwzględnymi, a wartości zostaną obliczone w każdej komórce.

Możesz dwukrotnie kliknąć wypełnione komórki, aby sprawdzić dokładność formuł. Odwołania bezwzględne muszą być takie same dla każdej komórki, natomiast pozostałe odniesienia odnoszą się do wiersza komórki.

Pamiętaj o dołączeniu znaku dolara ( $ ) za każdym razem, gdy tworzysz odwołanie bezwzględne w wielu komórkach. W poniższym przykładzie pominięto znaki dolara. Powoduje to, że Excel interpretuje to jako odniesienie względne, co daje nieprawidłowy wynik podczas kopiowania do innych komórek.

Używaj odwołań do komórek w wielu arkuszach

Excel umożliwia odwoływanie się do dowolnej komórki w dowolnym arkuszu, co może być szczególnie przydatne, jeśli chcesz odwołać się do określonej wartości z jednego arkusza do drugiego. Aby to zrobić, po prostu rozpocznij odwołanie do komórki nazwą arkusza, po której następuje wykrzyknik ( ! ). Na przykład, jeśli chcesz odwołać się do komórki A1 w Arkuszu1 , jej odwołanie do komórki będzie brzmiało Arkusz1!A1.

Pamiętaj, że jeśli nazwa arkusza zawiera spacje, należy umieścić ją w cudzysłowie ( '' ). Na przykład, jeśli chcesz odwołać się do komórki A1 w arkuszu o nazwie Budżet lipcowy , odwołanie do komórki będzie brzmieć „Budżet lipcowy”!A1.

Jak odwoływać się do komórek w arkuszach

Poniższy przykład będzie odnosił się do komórki z obliczoną wartością znajdującą się pomiędzy dwoma arkuszami. Dzięki temu można używać dokładnie tej samej wartości w dwóch różnych arkuszach bez konieczności przepisywania formuł lub powielania danych.

1. Znajdź komórkę, do której chcesz się odwołać i zanotuj jej arkusz. Na przykład chcemy odwołać się do komórki E14 w arkuszu Kolejność menu .

2. Przejdź do żądanego arkusza. Przykładowo wybierzemy arkusz kalkulacyjny Faktura cateringowa .

3. Znajdź i wybierz komórkę, w której ma się pojawić wartość. Na przykład wybierzemy komórkę C4.

4. Wprowadź znak równości ( = ), nazwę arkusza, po której następuje wykrzyknik (!) i adres komórki. Na przykład wprowadź ='Kolejność menu'!E14.

5. Naciśnij Enter na klawiaturze. Pojawi się wartość komórki, do której następuje odwołanie. Teraz, jeśli wartość komórki E14 zmieni się w arkuszu Zamówienie menu , zostanie ona automatycznie zaktualizowana w arkuszu Faktura za catering.

Jeśli później zmienisz nazwę arkusza, odwołanie do komórki zostanie automatycznie zaktualizowane, aby odzwierciedlało nową nazwę arkusza.

Jeśli wprowadzisz błędną nazwę arkusza, pojawi się komunikat #REF! w komórce pojawi się błąd. W poniższym przykładzie nazwa arkusza została błędnie wpisana. Aby edytować, zignorować lub sprawdzić błędy, kliknij przycisk Błąd obok komórki i wybierz opcję z menu.