Blokowanie pojedynczej komórki za pomocą odwołań bezwzględnych

Bardzo często trzeba kierować wiele formuł do jednej wspólnej wartości: stawki podatku, kursu wymiany, celu lub stałej opłaty.

Ta pojedyncza wartość znajduje się w jednej komórce, a każda inna formuła musi nadal się do niej odwoływać, niezależnie od miejsca skopiowania. Umożliwia to odwołanie bezwzględne.

Odwołanie bezwzględne zawiera znak dolara przed kolumną i wierszem, jak $A$1. Jest całkowicie zablokowane.

Po skopiowaniu w dowolne miejsce arkusza formuły zawierającej $A$1 ta część zawsze będzie wskazywać komórkę A1. Przesuną się tylko niezablokowane odwołania w formule.

Wyobraźmy sobie, że stawka podatku od sprzedaży 0.08 znajduje się w komórce E1. Sumy zamówień znajdują się w kolumnie B. Aby dodać podatek do każdego zamówienia, w komórce C2 należy wpisać:

=B2*$E$1

Odwołanie B2 jest względne, więc podąża za każdym wierszem, natomiast $E$1 jest bezwzględne, więc zawsze odczytuje stawkę podatku.

Formułę należy wypełnić w dół, zaczynając od C2, i obserwować, co dzieje się z każdą częścią:

• B2 zmienia się na B3, a następnie na B4 (odwołanie względne, przesuwa się)
• $E$1 za każdym razem pozostaje $E$1 (odwołanie bezwzględne, nieruchome)

Dlatego wiersz 3 zawiera =B3*$E$1, a wiersz 4 zawiera =B4*$E$1. Stawka podatku nigdy nie zostaje utracona.

Proszę pominąć znaki dolara i wpisać =B2*E1. Przy kopiowaniu w dół E1 przesuwa się na E2, E3, które są puste.

Kolumna podatku wypełnia się zerami. Liczby wyglądają niepoprawnie, ale przyczyna jest prosta: odwołanie do stawki było względne, choć powinno być bezwzględne.

Aby szybko przekształcić E1 w $E$1, należy kliknąć E1 na pasku formuły i jednokrotnie nacisnąć F4. Excel wstawi oba znaki dolara.

Jest to znacznie szybsze i mniej podatne na błędy niż ręczne wpisywanie symboli, zwłaszcza w przypadku długich formuł.

Załóżmy, że komórka $B$1 zawiera kurs USD na EUR wynoszący 0,92, a kwoty w dolarach są wpisane w kolumnie A. Każdą z nich można przeliczyć za pomocą:

=A2*$B$1

Po skopiowaniu formuły do setek wierszy każde przeliczenie korzysta z tego samego kursu. Wystarczy raz zaktualizować kurs w komórce B1, aby cała kolumna natychmiast się przeliczyła.

Ponieważ obie części są zablokowane, odwołanie bezwzględne pozostaje niezmienione niezależnie od tego, czy jest kopiowane w dół, w prawo, czy w obu kierunkach.

Po skopiowaniu =B2*$E$1 w prawo otrzymujemy =C2*$E$1: kolumna B zmieniła się na C, ale $E$1 nadal pozostaje bez zmian. Blokada działa w każdym kierunku.

Odwołania bezwzględne są równie przydatne wewnątrz funkcji. Aby obliczyć udział każdej wartości w stałej sumie przechowywanej w komórce B1, należy użyć:

=A2/$B$1

Aby porównać każdy wynik z celem zapisanym w komórce D1, można użyć:

=IF(A2>=$D$1,"Pass","Fail")

Zablokowana komórka pozostaje punktem odniesienia w każdym wierszu.

Prawdziwą korzyścią z odwołania bezwzględnego jest jeden punkt sterowania. Ponieważ każda formuła odczytuje tę samą zablokowaną komórkę, zmiana tej jednej komórki aktualizuje całą kolumnę naraz.

Zmiana stawki podatku w komórce E1 z 0,08 na 0,09 powoduje natychmiastowe przeliczenie każdej formuły =B2*$E$1. Nie trzeba edytować dziesiątek formuł — jedna wartość wejściowa steruje wszystkimi.

Doświadczeni użytkownicy często umieszczają stałe, stawki i wartości docelowe w niewielkim, opisanym obszarze u góry arkusza, a następnie odwołują się do nich bezwzględnie.

Dzięki temu formuły pozostają czytelne, a zmiana jednej wartości wejściowej aktualizuje cały raport. To znak dolara sprawia, że każda formuła nadal wskazuje tę wartość.

Odwołanie bezwzględne, takie jak $A$1, blokuje zarówno kolumnę, jak i wiersz, dlatego po skopiowaniu zawsze wskazuje tę samą komórkę.

• Należy używać go dla wspólnych wartości, takich jak stawki, opłaty i wartości docelowe
• Można łączyć je z odwołaniami względnymi, aby przesuwały się tylko właściwe części
• Naciśnięcie F4 natychmiast dodaje oba znaki dolara

W następnym kroku zostanie zablokowana tylko jedna część — za pomocą odwołań mieszanych.