Strona główna komputer Szybkie blokowanie adresów w formułach Excela!

Szybkie blokowanie adresów w formułach Excela!

Przez

27 lutego 2019

10046

Jeśli w arkuszu kalkulacyjnym wykonujemy obliczenia na większej ilości danych i utworzyliśmy już jakąś formułę to nie musimy tworzyć kolejnych – możemy przyspieszyć pracę kopiując ją do przyległych komórek za pomocą uchwytu wypełniania. Wówczas wyświetlą różne wyniki dla każdej pozycji. Niestety czasami obliczenia nie są zgodne z naszymi oczekiwaniami. Dlaczego tak się dzieje? O tym w dzisiejszym artykule.

Aby formuła działała poprawnie, po jej skopiowaniu może zajść potrzeba zablokowania adresów. W Excelu mamy do czynienia z trzema rodzajami adresowania:

względne – jest tym standardowym (domyślnym) i wykorzystujemy jeśli chcemy aby cały adres zmieniał się przy kopiowaniu.
bezwzględne – jeśli chcemy aby cały adres został niezmienny przy kopiowaniu formuły w obu kierunkach wówczas symbol dolara stawiamy przed literą (kolumną) oraz cyfrą (wierszem).
mieszane – jeśli nie chcemy aby adres zmieniał się przy kopiowaniu w górę lub w dół to należy zablokować numer wiersza stawiając przed nim symbol dolara. Natomiast jeśli kopiujemy formułę w prawo lub lewo to blokujemy kolumnę stawiając symbol dolara przed literą.

Jak to wygląda w praktyce? Sam zobacz! Zrzuty ekranu w tym artykule zostały wykonane w programie Excel dla Office 365. Jeżeli masz inną wersję programu Excel, wygląd może się nieco różnić ale nie martw się funkcje pozostają takie same. Zaczynamy!

Dysponujemy tabelą z cenami podanymi w złotówkach dla 5 produktów. Naszym zadaniem będzie wyliczenie ich ceny w walucie Euro po aktualnym kursie.
Klikamy kursorem myszy w arkuszu kalkulacyjnym w to miejsce, w którym chcemy uzyskać wynik (w tym przypadku zaczynamy od pozycji I9). Przy obliczeniach wykorzystamy komórkę z kursem euro. Wpisujemy formułę zaczynając od znaku równości. Aby przeliczyć cenę należy podzielić ją przez kurs euro. Zatwierdzamy klawiszem „Enter”.
Jako, że otrzymaliśmy wynik w złotówkach (ponieważ liczyliśmy w walucie PLN) musimy go sformatować. W tym celu klikamy prawym przyciskiem myszy w komórce z wyliczoną ceną a następnie z listy wybieramy opcję „Formatuj komórki”. Otworzy się okno dialogowe. W zakładce „Liczby” przechodzimy do kategorii „Walutowe” a następnie w obszarze „Symbol” wybieramy EUR. Gotowe!
Po skopiowaniu tej formuły w dół (za pomocą uchwytu wypełniania) zauważamy błędy. Dlaczego? Jeśli formuła zawiera adresy komórek to w momencie przeciągania w dół, adresy te zmieniają się.
Na niebiesko zaznaczona jest komórka H9 z ceną w złotówkach a na czerwono komórka H3 z kursem euro. Jeśli przesuwamy teraz komórkę I9 (tę z wynikiem) w dół to również przesuną się komórki z adresu formuły. Efekt będzie taki, że weźmiemy cenę kolejnego produktu (herbaty) i podzielimy przez komórkę H4, która jest pusta. Dlatego Excel pokazał nam błąd. Aby to potwierdzić, edytowaliśmy kolejne komórki.
Aby zapobiec takim sytuacjom, właśnie korzystamy z blokowania adresów w formułach. W naszym przypadku, Excel musi brać ceny kolejnych produktów i dzielić je przez tą samą komórkę z kursem euro, czyli przez H3. Wracamy do pierwszej komórki z poprawnym wynikiem i klikamy dwa razy aby móc edytować formułę. Ustawiamy się w przy adresie H3 i wciskamy przycisk F4 lub klawisz funkcyjny czyli Fn a następnie F4. Pojawią się symbole dolara zarówno przed literką kolumny jak i cyfrą wiersza. Kliknij drugi raz klawisz F4 (lub Fn+F4) a symbol dolara pojawi się tylko przed numerem wiersza. Za trzecim razem symbol pojawi się przed literą kolumny.
W naszym przypadku w adresie H3 przy kopiowaniu w dół zmieni się jedynie numer wiersza (4, 5, 6..). Kolumna H pozostanie bez zmian. Dlatego musimy wstawić symbol dolara przed numerem wiersza. Zatwierdzamy klikając przycisk „Enter” a następnie kopiujemy formułę w dół. Hurra! Działa 🙂
Dla sprawdzenia klikamy w dowolną komórkę z tabeli aby zobaczyć, że faktycznie pierwszy adres czyli cena w złotówkach zmienia się dla każdego produktu drugi adres czyli komórka H3 pozostaje bez zmian.