Przejdź do treści

Jak zablokować komórkę w Excelu - znak $ i klawisz F4

Piszesz formułę, sprawdzasz pierwszy wiersz, wynik się zgadza. Przeciągasz ją w dół i w kolejnych wierszach wychodzą bzdury albo zera. Excel nie pokazuje żadnego błędu, więc łatwo tego nie zauważyć i wysłać dalej cennik, w którym połowa cen jest źle policzona.

Przyczyna jest prawie zawsze ta sama: formuła odwołuje się do komórki, która powinna stać w miejscu, a przy kopiowaniu przesunęła się razem z formułą. Pokażę, jak taką komórkę zablokować znakiem dolara, jak zrobić to jednym klawiszem i kiedy dolar $ wstawia się tylko przy kolumnie albo tylko przy wierszu.

„Zablokować komórkę” znaczy w Excelu też coś innego: zabezpieczyć ją przed zmianą przez kogoś, kto dostanie plik. Ten sposób jest opisany w osobnym dziale na końcu. Wszystkie przykłady są w pliku do pobrania, na danych jednego sklepu rowerowego.

W tym poradniku:

  1. Co robi znak $ w Excelu
  2. Formuła bez dolara - błąd, którego Excel nie zgłasza
  3. Jak zablokować komórkę w formule
  4. Klawisz F4 - cztery zapisy jednego adresu
  5. Przykład: udział w sumie
  6. Dolar tylko przy kolumnie albo tylko przy wierszu
  7. Jak zablokować komórkę przed edycją
  8. Dlaczego blokada nie działa
  9. Podsumowanie
💡 Odwołania to podstawa każdej formuły, która ma działać po skopiowaniu
W kursie Excel Podstawowy odwołania względne i bezwzględne mają osobną lekcję, a potem wracają w każdym dziale z formułami. 40 lekcji, zadania z rozwiązaniami: 52.
Zobacz kurs →

Co robi znak $ w Excelu

Excel nie zapamiętuje w formule adresu komórki tak, jak my go czytamy. Gdy w C2 wpiszesz formułę, która sięga do B2, Excel zapisuje to jako „komórka o jedną kolumnę w lewo, w tym samym wierszu”. Po skopiowaniu formuły do C3 ta sama wskazówka prowadzi już do B3. Taki adres to odwołanie względne i dzięki niemu jedna formuła działa w całej kolumnie.

Znak $ wyłącza to przesuwanie. Stoi zawsze przed tym, co ma zostać na miejscu:

  • $F$2 - zablokowana kolumna i wiersz, formuła zawsze sięga do F2. To odwołanie bezwzględne,
  • F$2 - zablokowany tylko wiersz 2, kolumna przesuwa się przy kopiowaniu w bok,
  • $F2 - zablokowana tylko kolumna F, wiersz przesuwa się przy kopiowaniu w dół.

Dwa ostatnie to odwołania mieszane. Dolar nie zmienia wyniku formuły w komórce, w której ją wpisujesz. Zmienia tylko to, co się stanie po jej skopiowaniu. Dlatego błąd wychodzi dopiero w drugim wierszu.

W angielskim Excelu to samo nazywa się absolute reference (bezwzględne), relative reference (względne) i mixed reference (mieszane). Znak i klawisz są te same.

Formuła bez dolara - błąd, którego Excel nie zgłasza

W arkuszu Bez dolara jest cennik: sześć produktów z ceną netto w kolumnie B i stawka VAT 23% w jednej komórce, F2. Cena brutto rowera to cena netto razy jeden plus stawka:

=B2*(1+F2)

Wynik w pierwszym wierszu jest dobry: 1 890,00 zł netto daje 2 324,70 zł brutto. Formuła skopiowana w dół daje jednak taki obraz:

Cennik w Excelu z formułą bez znaku dolara: od drugiego wiersza cena brutto równa się cenie netto

Kask kosztuje 189,00 zł netto i 189,00 zł brutto, lampka 79,00 zł i 79,00 zł. Jasnozielona kolumna pokazuje, co się stało: w trzecim wierszu formuła sięga do F3, w czwartym do F4. Stawka przesunęła się razem z formułą, a tam są puste komórki. Pusta komórka to dla Excela zero, więc cena rośnie o 0%.

Najszybciej widać to w trybie edycji. Kliknij C3 i naciśnij F2. Excel obrysuje kolorem każdą komórkę, do której sięga formuła:

Formuła w komórce C3 w trybie edycji: Excel obrysowuje na czerwono pustą komórkę F3 zamiast stawki VAT w F2

Tu nie ma komunikatu, trójkącika ani #ARG!. Formuła jest poprawna składniowo, tylko liczy nie to, co trzeba. Gdyby stawka stała w kolumnie obok cen, każdy wiersz brałby inną liczbę i błąd byłby jeszcze trudniejszy do zauważenia.

Jak zablokować komórkę w formule

Wystarczy dopisać dwa dolary przed adresem stawki. Formuła w C2:

=B2*(1+$F$2)

Teraz po skopiowaniu w dół zmienia się tylko B2, a $F$2 zostaje. Arkusz Ze znakiem $ w pliku pokazuje ten sam cennik z poprawioną formułą, znów z C3 w trybie edycji:

Formuła z odwołaniem bezwzględnym $F$2 w komórce C3: ramka wskazuje stawkę VAT w F2, ceny brutto są poprawne

Kask kosztuje teraz 232,47 zł brutto, lampka 97,17 zł, bidon 35,67 zł. W kolumnie z formułami adres $F$2 jest identyczny w każdym wierszu i po tym najłatwiej sprawdzić, czy blokada zadziałała.

Jest też druga korzyść: stawka siedzi w jednej komórce. Wpisz w F2 8% zamiast 23%, a przeliczą się wszystkie ceny naraz. Gdyby 23% było wpisane w każdą formułę osobno, trzeba by poprawiać je jedną po drugiej.

Klawisz F4 - cztery zapisy jednego adresu

Dolarów nie trzeba wpisywać ręcznie. W trakcie pisania formuły, zaraz po kliknięciu komórki (albo z kursorem w jej adresie) naciśnij F4. Każde kolejne naciśnięcie zmienia zapis:

Tabela czterech rodzajów odwołań w Excelu: A1, $A$1, A$1 i $A1, z opisem, co się zmienia przy kopiowaniu i ile razy nacisnąć F4
  1. raz - $F$2, blokada kolumny i wiersza,
  2. dwa razy - F$2, blokada samego wiersza,
  3. trzy razy - $F2, blokada samej kolumny,
  4. cztery razy - z powrotem F2.

W praktyce najczęściej wystarcza jedno naciśnięcie. Gdy formuła jest już wpisana, kliknij komórkę, naciśnij F2, ustaw kursor w adresie, który chcesz zablokować, i dopiero wtedy F4. Zaznaczenie kilku adresów naraz i F4 zmienia je wszystkie jednocześnie.

Dwie sytuacje, w których F4 „nic nie robi”:

  • laptop - klawisze F1-F12 często obsługują jasność i głośność, a jako F-klawisze działają dopiero z Fn. Wtedy naciśnij Fn + F4,
  • Mac - w Excelu na Macu ten sam efekt daje Cmd + T.

Poza trybem edycji F4 działa zupełnie inaczej: powtarza ostatnią czynność, na przykład ostatnie formatowanie. Jeśli po naciśnięciu komórka nagle zmieniła kolor zamiast dostać dolary, tryb edycji nie był włączony.

Przykład: udział w sumie

Drugi typowy przypadek to dzielenie przez sumę. Arkusz Udział w sumie ma sprzedaż w październiku: rower 15 120 zł, kask 3 402 zł i tak dalej, razem 25 522 zł w B8. Udział każdego produktu to jego sprzedaż podzielona przez tę samą sumę:

=B2/$B$8
Udział produktu w sprzedaży: formuła =B3/$B$8 w trybie edycji, ramki na sprzedaży kasku i na sumie w B8

Rower to 59,2% sprzedaży, kask 13,3%, bidon 3,4%. Bez dolarów w drugim wierszu formuła dzieliłaby przez B9, czyli pustą komórkę, i dawała #DZIEL/0!. Ten błąd przynajmniej widać od razu, w przeciwieństwie do cennika wyżej.

Ta sama zasada dotyczy każdej wartości wspólnej dla całej kolumny: kursu waluty, stawki prowizji, progu premii, daty raportu.

Dolar tylko przy kolumnie albo tylko przy wierszu

Odwołania mieszane przydają się wtedy, gdy formułę kopiujesz jednocześnie w dół i w bok. Arkusz Dolar raz to tabela rabatów: ceny netto w kolumnie B, rabaty 5%, 10% i 15% w wierszu 1. Całą siatkę wypełnia jedna formuła wpisana w C2:

=$B2*(1-C$1)
  • $B2 - cena zawsze z kolumny B, ale z bieżącego wiersza,
  • C$1 - rabat zawsze z wiersza 1, ale z bieżącej kolumny.

Po skopiowaniu formuły na cały zakres C2:E7 komórka E7 zawiera =$B7*(1-E$1), czyli cenę bidonu i rabat 15%:

Tabela rabatów w Excelu: formuła w E7 w trybie edycji bierze cenę z B7 i rabat z nagłówka E1

Bidon za 29,00 zł z rabatem 15% kosztuje 24,65 zł. Z $B$2 cała siatka liczyłaby rabat od ceny roweru. Z $C$1 każda kolumna miałaby rabat 5%. Pełna blokada jest tu za mocna, a jej brak za słaby.

Najprościej sprawdzić to pytaniem: w którą stronę kopiuję formułę i co ma przy tym zostać na miejscu? Kolumna z ceną nie może się przesunąć w prawo, więc dolar stoi przed B. Wiersz z rabatami nie może zjechać w dół, więc dolar stoi przed 1.

Jak zablokować komórkę przed edycją

Jeśli chodzi ci o to, żeby ktoś, kto dostanie plik, nie nadpisał formuł, potrzebujesz ochrony arkusza. Dolar tu nie pomoże, bo działa tylko w formułach.

Każda komórka w Excelu ma ustawioną opcję Zablokuj, ale blokada działa dopiero po włączeniu ochrony arkusza. Dlatego najpierw odblokowujesz to, co ma zostać do zmiany, a dopiero potem włączasz ochronę:

  1. zaznacz komórki, które użytkownik ma móc zmieniać (w pliku: ceny netto B2:B7 i stawkę F2),
  2. naciśnij Ctrl + 1, przejdź na kartę Ochrona i odznacz Zablokuj,
  3. na wstążce wybierz Recenzja → Chroń arkusz,
  4. wpisz hasło albo zostaw puste pole i kliknij OK.
Chroniony arkusz w Excelu: żółte komórki z cenami netto i stawką VAT można zmieniać, formuły w kolumnie C są zablokowane

W arkuszu Ochrona możesz zmienić cenę albo stawkę i ceny brutto przeliczą się same. Próba wpisania czegokolwiek w kolumnie C kończy się komunikatem, że komórka jest chroniona. Ochronę zdejmuje Recenzja → Nie chroń arkusza. W pliku jest bez hasła, więc da się ją zdjąć od razu.

Ochrona arkusza chroni przed pomyłką, nie przed kimś, kto chce się dostać do danych. Hasło do arkusza da się obejść, a dane zostają w pliku. Jeśli plik ma czegoś nie zawierać, nie wysyłaj go z tym.

W angielskim Excelu: Review → Protect Sheet, a w oknie formatowania karta Protection i pole Locked.

Dlaczego blokada nie działa

  • dolar stoi w złym miejscu - F$2 zamiast $F$2 przy formule kopiowanej w bok. Sprawdź, w którą stronę kopiujesz, i porównaj z tabelą przy F4,
  • zablokowany jest nie ten adres - przy =B2*(1+F2) łatwo nacisnąć F4 z kursorem przy B2. Wtedy cena stoi w miejscu, a stawka dalej ucieka. Tryb edycji (F2) i kolorowe ramki pokazują to od razu,
  • F4 nie działa - na laptopie Fn + F4, na Macu Cmd + T, i zawsze w trybie edycji,
  • adres z dolarem i tak się zmienił - to nie błąd. Gdy nad stawką wstawisz wiersz albo przeniesiesz ją w inne miejsce, Excel poprawi $F$2 na nowy adres. Dolar blokuje adres przy kopiowaniu formuły, nie przy przenoszeniu danych,
  • stawka stoi na innym arkuszu - dolar działa tak samo: =B2*(1+Ustawienia!$B$1),
  • dane są tabelą Excela - w formułach pojawiają się nazwy kolumn zamiast adresów i dolar nie jest potrzebny.,
  • ochrona arkusza nic nie blokuje - komórki mają odznaczone Zablokuj albo ochrona nie została włączona. Sama opcja w oknie formatowania nie wystarczy.

Odwołania bezwzględne pojawiają się prawie w każdej większej formule. W WYSZUKAJ.PIONOWO blokuje się zakres tabeli, żeby nie przesuwał się przy kopiowaniu, a w SUMA.JEŻELI zakres kryteriów.

Podsumowanie

Blokada komórki w formule to jeden znak przed adresem, ale błąd z jej braku potrafi przejść niezauważony przez cały raport. Przed skopiowaniem formuły sprawdź:

  • czy formuła sięga do wartości wspólnej dla wszystkich wierszy (stawka, kurs, suma),
  • czy ta wartość ma dolary: $F$2, nie F2,
  • przy kopiowaniu w dół i w bok: czy dolar stoi przy właściwej części adresu,
  • czy w drugim i ostatnim wierszu wynik się zgadza (F2 i kolorowe ramki),
  • wartości stałe są w osobnych komórkach, a nie wpisane w formułę,
  • jeśli plik idzie do innych osób: komórki do zmiany odblokowane, arkusz chroniony.

Plik z wszystkimi przykładami pobierzesz poniżej. Każdy dział ma w nim osobny arkusz, więc możesz sprawdzić formuły i od razu spróbować samodzielnie.

Formuły, które działają po skopiowaniu, to dopiero początek
✓Odwołania, formuły i najważniejsze funkcje
✓JEŻELI, WYSZUKAJ.PIONOWO, wykresy i tabele
✓Zadania z rozwiązaniami: 52 · certyfikat
✓40 lekcji · gwarancja zwrotu 30 dni
Zobacz kurs
Kurs Excel Podstawowy
Pobierz plik ćwiczeniowy

Plik jest Twój bez zapisywania się na cokolwiek. Jeśli chcesz przejść Excela po kolei, zacznij od darmowego kursu.

Dodaj komentarz

Twoja ocena

Twój adres email nie zostanie opublikowany. Wymagane pola są oznaczone *