Czasami możesz spotkać w Excelu formuły, w których odwołania do komórek wyglądają inaczej niż zwykle. Obok zwykłego odwołania do adresu danej komórki (np. =A1) pojawia się symbol dolara ($) (np. =$A$1). Jak większość rzeczy występujących w tym programie, również to rozwiązanie może być bardzo pomocne i potrafi znacznie ułatwić codzienną pracę.
Symbol dolara w Excelu jest związany z bardzo przydatną opcja. Dzięki której istnieje możliwość zablokowania adresu znajdującego się w formule, co zapobiegnie zmianie tego adresu np. przy przeciąganiu lub kopiowaniu formuły. Dzieję się tak, ponieważ w Excelu domyślnym sposobem odwoływania się do komórki (czyli adresowaniem) jest adresowanie względne. Polega to na tym, że wraz ze zmianą położenia formuły (np. poprzez kopiowanie) adresy, które w niej się znajdują automatycznie zmienią swoje odwołania o tyle kolumn i wierszy, o ile formuła została przesunięta. Aby zablokować tę możliwość wystarczy użyć adresowania bezwzględnego, co poskutkuje tym, że przy zmianie położenia formuły odwołanie pozostanie bez zmian. Excel umożliwia także skorzystanie z adresowania mieszanego, gdzie zablokowany zostaje jedynie fragment adresu (np. wiersz), natomiast drugi fragment (np. kolumna) reaguje na zmianę położenia.
Opis działania
Do blokowania adresów w formułach służy symbol dolara: $. Można wstawić go ręcznie w odpowiednie miejsce w adresie lub użyć do tego skrótu klawiszowego F4. Chcąc zmienić sposób adresowania danego adresu wystarczy ręcznie zmieniać położenie symbolu $ w formule lub klikając odpowiednią ilość razy klawisz F4. Zakładając, że obecnie w formule występuje adresowanie względne, czyli odwołanie do komórki A1 wygląda tak: =A1, zasady działania adresowania są następujące:
- Pierwsze wciśnięcie F4 poskutkuje zmianą sposobu adresowania na bezwzględne. Odwołanie zmieni swoją postać na:
=$A$1i przestanie reagować na zmianę położenia formuły. Nawet jeśli skopiujesz formułę do innej komórki lub przeciągniesz ją w dół, odwołanie nie zmieni się. - Drugie użycie klawisza F4 spowoduję zmianę adresowania na mieszane. Tym razem odwołanie do komórki będzie wyglądało tak:
=A$1, co oznacza, że wiersz komórki nie będzie reagował na zmianę położenia formuły, ale kolumna już tak. Czyli przy zmianie położenia formuły w górę lub dół jej postać nie zmieni się. Natomiast przy przeciąganiu lub kopiowaniu formuły w lewo lub prawo zostanie ona zaktualizowana zgodnie ze zmianą, jakiej dokonaliśmy. - Po trzecim wciśnięciu F4 zostanie zmieniony sposób adresowania mieszanego. Blokada adresu zadziała analogicznie jak w punkcie 2, jednak w tym przypadku kolumna nie będzie reagowała na zmiany, a będzie na nie reagował wiersz. Adres zmieni się przy przeciąganiu lub kopiowaniu formuły w dół i górę, ale nie zareaguje na zmiany położenia w bok.
- Czwarte użycie klawisza F4 poskutkuje powrotem do adresowania względnego, a więc reagującego na wszelkie zmiany położenia.
Podsumowując:
=A1 odwołanie względne, reaguje na każdą zmianę położenia (w górę, w dół, na boki)
=$A$1 bezwzględne, nie reaguje na żadną zmianę położenia
=A$1 mieszane, kolumna reaguje na zmianę położenia
=$A1 również mieszane, ale to wiersz reaguje na zmianę położenia
Przykładowe zastosowanie
Tutaj możesz ściągnąć plik, na którym pracowałem podczas pisania artykułu.
Przykład nr 1
Warto skorzystać z tej funkcjonalności, gdy chcemy wykonać obliczenia, w których jeden z elementów funkcji powinien być stały. Sprawdzimy działanie tej opcji na przykładzie danych podsumowujących sprzedaż w pewnej firmie. W tabeli znajdują się informacje o typie produktu, liczbie sprzedanych sztuk oraz cenie jednostkowej wyrażonej w PLN. Dzięki temu możliwe jest policzenie łącznej sprzedaży w PLN dla każdego z typów produktu. W związku z tym, że na potrzeby raportowe zarząd firmy musi tę wartość podać także w EUR, końcowe kwoty muszą zostać podzielone przez aktualny kurs euro.
Aby to zrobić zbudujemy rozwiązanie, które umożliwi nam dzielenie określonych wartości przez wybraną komórkę, w której będzie znajdował się kurs euro.
Dla pierwszej wartości z naszej tabeli formuła wyglądałaby bardzo prosto:
=D4/I1, czyli dzielimy wartość z kolumny D4 (Łączna sprzedaż PLN) przez kurs waluty, znajdujący się w komórce I1.
Jednak w tabeli znajdują się także inne typy produktów, dla których potrzebujemy wykonać analogiczne obliczenia. Nie chcemy dla wszystkich produktów osobno wskazywać ręcznie, że mają się odwoływać do komórki I1, więc upraszczając możemy spróbować przeciągnąć formułę w dół, na pozostałe typy produktów. Excel zadziała tak, że skopiuje formułę z pierwszej komórki na następne, jednocześnie aktualizując odwołania znajdujące się w nich o taką odległość, o jaką zostały przesunięte. Jeśli tak postąpimy, otrzymamy błąd #DZIEL/0!.
W naszej formule występuje odwołanie względne. Przeciągnięcie formuły poskutkowało zmianą wszystkich odwołań, także tym do komórki I1 zawierającej kurs walutowy. Próbowaliśmy podzielić przez pustą komórkę, więc Excel zwrócił błąd.
Możemy zapobiec temu stosując odwołanie bezwzględne. Chcemy, aby Excel zawsze przyjmował jako mianownik wartość z komórki I1, więc w pierwszej formule zablokujmy możliwość zmiany odwołania do tej komórki. Korzystając z opisanych wyżej sposobów zmieniamy sposób adresowania na bezwzględne, w efekcie czego funkcja dla pierwszego wiersza tabeli wygląda tak:
=D4/$I$1
Teraz, gdy spróbujemy przeciągnąć funkcję na pozostałe wiersze Excel zwróci wyniki, jakich oczekujemy – podzieli każdą wartość przez wpisany kurs euro. Takie rozwiązanie pozwoli również w szybki sposób reagować na zmieniający się kurs walutowy. Wystarczy, że kurs zostanie zmieniony w komórce I1 i automatycznie zostanie to uwzględnione we wszystkich formułach zawierających odwołanie do tej komórki.
Przykład nr 2
Możliwość zmiany adresowania przydaje się także w sytuacji, gdy tylko fragment formuły ma zostać przesunięty. Poniższa tabela zawiera informacje na temat rocznej sprzedaży w pewnej firmie, w podziale na typ produktu i miesiąc sprzedaży.
Dla każdego miesiąca mamy podsumowanie łącznej sprzedaży. Bazując na ten informacji, chcielibyśmy obliczyć udział poszczególnych typów produktu w miesięcznej sprzedaży. Aby to zrobić należy podzielić kwotę sprzedaży danego produktu przez łączną sumę sprzedaży w danym miesiącu. Czyli dla Produkt 1 chcąc obliczyć udział w sprzedaży styczniowej formuła powinna wyglądać tak:
=C1/C13
Tak skonstruowana formuła zadziała poprawnie tylko w tym jednym przypadku. Gdy spróbujemy ją przeciągnąć w dół lub w prawo, niestety otrzymamy błędne wartości.
Chciałbym to obliczyć analogicznie dla pozostałych produktów oraz pozostałych miesięcy. Bez odpowiedniej zmiany adresowania nie uda nam się tego szybko obliczyć. Musielibyśmy dla każdego typu produktu ręcznie wpisywać pierwszą formułę i wtedy ewentualnie przeciągnąć w prawo na pozostałe miesiące. Jednak nie byłoby to zbyt efektywne, a przy tabeli zawierającej znacznie więcej rekordów byłoby to zwykłe marnowanie czasu.
Na szczęście korzystając z opcji adresowania mieszanego możemy rozwiązać ten problem w ciągu paru sekund. Chcemy, aby formuła odnosiła się do tego samego wiersza, a zmieniała się jedynie kolumna, z której pobierana jest wartość. A więc chcemy umożliwić Excelowi zmianę odwołania, ale jedynie dla kolumn. Korzystając z wcześniejszych informacji koryguję formułę do następującej postaci:
=C3/C$13
Teraz mogę spokojnie przeciągać formuły i dzięki zmianie sposobu adresowania otrzymam oczekiwane wartości.
Przykład nr 3
Kolejna sytuacja, w której warto skorzystać z możliwości zmiany adresowania występuje, gdy dla danej listy rekordów należy wykonać jakieś obliczenia i przeciąganie formuły na inne komórki może spowodować powstanie błędnych wyliczeń. W tabeli znajdującej się poniżej znajduje się informacja o dziennej sprzedaży w sklepach pewnej sieci handlowej. Oto jej fragment:
Wyobraź sobie, że chcielibyśmy dla każdego sklepu obliczyć sumę sprzedaży w ciągu całego przedstawionego okresu. Moglibyśmy to zrobić w takiej tabelce:
W celu zsumowania wartości dla odpowiedniego sklepu skorzystamy z funkcji SUMA.JEŻELI, o której pisałem więcej w tym artykule. Dla sklepu nr 1 funkcja powinna wyglądać tak:
=SUMA.JEŻELI(B3:B102;F2;D3:D102)
I tak jak we wcześniejszych przykładach w pierwszym wierszu formuła zwróci prawidłowy wynik, ale jeśli przeciągniemy ją na pozostałe wiersze:
Tym razem funkcja nie zwraca błędu i mogłoby się wydawać, że wszystko jest OK. Jednak sprawdzając sumę kwot z kolumny D i sumę z naszej tabeli to uświadomimy sobie, że te wartości są różne. A nie powinny. Odpowiada za to fakt, iż w naszej formule występuje odwołanie względne, a więc przy przeciąganiu formuły zakres także się przesuwał. I nie brał pod uwagę wszystkich wymaganych komórek.
Skorygujmy formułę, aby w obszarach z tabeli wyjściowej (czyli kolumnach B i D) występowało adresowanie bezwzględne. Natomiast w kolumnie z tabeli podsumowującej adresowanie względne. Dzięki temu zablokujemy możliwość niepożądanego przesunięcia się zakresu, z którego chcemy obliczyć sumę. Jednocześnie zapewniając sobie możliwość przeciągnięcia formuły na pozostałe sklepy. Końcowa, prawidłowa formuła dla pierwszej pozycji na liście wygląda więc tak:
=SUMA.JEŻELI($B$2:$B$101;F2;$D$2:$D$101)
Teraz możemy swobodnie przeciągnąć formułę na pozostałe pozycje na liście i powinniśmy otrzymać prawidłowe wyniki.
Uff, wszystko się zgadza 🙂.
Podsumowanie
Mam nadzieję, że po przeczytaniu tego artykułu już wiesz, z jakiego powodu w formułach Excela pojawia się symbol dolara. I potrafisz odpowiednio korzystać z opcji adresowania. Wierzę, że ta wiedza pozwoli Ci uniknąć błędów w Twoich arkuszach, a także przyśpieszyć Twoją pracę.
