Lekcja 6 – Jak Zablokować Komórkę w Excelu. Znak $ w Formułach
Czy wiesz, jak zablokować komórkę w Excelu, aby wyznaczony zakres danych był prawidłowy nawet po przekopiowaniu (przeciągnięciu) formuły? W tym celu wykorzystuje się odwołania bezwzględne, o których więcej dowiesz się z tego poradnika. Omówię w nim także odwołania względne oraz inne podstawowe funkcje programu Excel, takie jak suma, średnia, max, min i ile.liczb.
Słaba jakość filmu ? Wybierz Ustawienia “koło zębate” i ustaw rozdzielczość 1080p (HD)
- Jak zablokować komórkę w Excelu, czyli czym są odwołania względne i bezwzględne
- Zablokowanie komórki w Excelu na przykładzie zmiany waluty z dolara na złotówki
- Przykład jak zablokować komórkę w Excelu tylko dla wiersza lub tylko dla kolumny
Jak zablokować komórkę w Excelu, czyli czym są odwołania względne i bezwzględne?
W Excelu odwołania względne i bezwzględne odnoszą się do sposobu, w jaki formuła reaguje na przemieszczenie lub skopiowanie do innej komórki.
W przypadku odwołań względnych formuła dostosowuje się do nowej lokalizacji. Na przykład, jeśli formuła znajdująca się w komórce F6 zawiera mnożenie wartości z komórek D6 i E6
=D6*E6
, to przekopiowanie tej formuły o jedną komórkę w dół do komórki F7 spowoduje, że formuła zmieni odwołanie do komórek na D7*E7
=D7*E7
Z kolei w odwołaniach bezwzględnych, formuła zachowuje stałe odwołanie do określonej komórki, niezależnie od lokalizacji, do której kopiujemy formułę. Na przykład, jeśli formuła znajdująca się w komórce F6 zawiera formułę mnożenia wartości z komórek D6 i E6, ale chcielibyśmy zablokować komórkę E6, aby nie zmieniało się jej położenie, powinniśmy zablokować odwołanie do tej komórki, stosując znak $ przed literą i numerem komórki tj. $E$6 wtedy formuła przybierze postać.
=D6*$E$6
, po przekopiowaniu (przeciągnięciu) takiej formuły o jedną komórkę w dół do komórki F7 spowoduje, że formuła zmieni odwołanie tylko do pierwszej komórki a druga pozostanie bez zmian
=D7*$E$6
W tym miejscu warto zaznaczyć, że mam możliwość zablokowania komórki w 3 wersjach.
- Zablokowanie komórki w wierszu i kolumnie $E$6
- Zablokowanie komórki tylko w wierszu E$6 (odwołanie do kolumny komórki jest odblokowane)
- Zablokowanie komórki tylko w kolumnie $E6 (odwołanie wiersza komórki jest odblokowane)
Komórki w formule możesz zablokować także za pomocą klawisza funkcyjnego F4. Kilkukrotne naciśnięcie tego klawisza powoduje, że zmienia się typ blokowania komórki
Powyższa wiedza jest absolutną podstawą pracy w Excelu. Przejdźmy zatem do przykładów, aby zobaczyć, dlaczego jest to bardzo istotna wiedza.
Zablokowanie komórki w Excelu na przykładzie zmiany waluty z dolara na złotówki.
W tym przykładzie będziemy chcieli obliczyć wartość produktów o nazwach zawartych w kolumnie C w przeliczeniu na złotówki a ich wartość wprowadzić w kolumnie G (wartość w zł). Oczywiście do obliczenia tego zadania będziemy potrzebować ilości produktów z kolumny D i ceny jednostkowej w dolarze z kolumny E oraz kursu waluty, który wprowadzimy w dalszej części poradnika.
Zacznijmy od wymnożenia ilości x cena jednostkowa.
Ten zabieg wykonujemy dla wszystkich pozycji. Następnie poniżej wyniku wprowadzimy sumę.
Kurs Excel
Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.
3 Metody sumowania wartości komórek
Zsumowanie wartości poszczególnych komórek można wykonać co najmniej na 3 sposoby:
- pierwszym z nich jest ręczne dodanie wszystkich sumowanych komórek, stosując znak +, jak poniżej:
2. Drugim sposobem na zsumowanie wartości poszczególnych komórek jest zastosowanie funkcji SUMA().
Funkcja SUMA dodaje wartości. Możesz dodawać pojedyncze wartości, odwołania do komórek lub zakresów lub połączenie tych wszystkich trzech typów wyrażeń. Przykład zastosowania tej funkcji znajduje się poniżej:
3. Najszybszym sposobem na zsumowanie komórek, jest jednak skorzystanie z funkcji Autosumowanie.
Aby skorzystać z Autosumowania:
- kliknij w komórkę, w której ma pojawić się wynik sumowania. Powinna być to komórka bezpośrednio pod lub obok wartości do zsumowania.
- Następnie przejdź do karty Narzędzia główne > Edytowanie >Autosumowanie
- Po kliknięciu Autosumowanie Excel automatycznie wprowadzi funkcję SUMA() dla zakresu danych, który będzie inteligentnie zaznaczony przez Excel. Taki zakres danych do sumowania można w każdym momencie zmienić.
Jeśli jesteś osobą początkującą, powinieneś poznać również kilka funkcji, których użycie jest bardzo podobne do funkcji SUMA() argumentem tych funkcji jest zakres danych, są to funkcje: ŚREDNIA() do obliczenia średniej arytmetycznej; MAX() do znalezienia największej wartości z zakresu danych MIN() do znalezienia najmniejszej wartości z zakresu danych LIE.LICZB() zlicza ilość komórek w zakresie, które zawierają liczby
Przeliczenie wartości poprzez zablokowanie komórki z kursem dolara
Poniżej sumy wprowadzamy aktualny kurs dolara = 4,1 i wymnażamy sumę wartości razy kurs, dzięki czemu otrzymujemy:
Sumę w zł równą 15.500 x 4,1 = 63.550 zł
Teraz przystępujemy do obliczenia wartości w zł dla każdej pozycji. Zablokujemy odwołanie do komórki F15 – czerwone zaznaczenie (w której znajduje się kurs walut) za pomocą znaku $ przed literą i numerem wiersza komórki, a przykładowa formuła wygląda jak poniżej. Przekopiowanie komórek dla wszystkich pozycji w kolumnie spowoduje przeliczenie wartości na zł.
Ostatecznie suma wartości dla kolumny Wartość w zł wynosi 63.550 zł. Widzimy, że wartość jest identyczna z tą jaką uzyskaliśmy w kolumnie podsumowania wartości w $.
Po obliczeniu wartości w złotówkach na podobnej zasadzie obliczmy przychód. Przy założeniu, że marża sklepu wynosi 20% dla wszystkich produktów, formuła będzie się przedstawiała jak poniżej:
Aby wejść w komórkę, która zawiera w sobie wpisaną formułę, możesz nacisnąć na nią dwukrotnie przyciskiem myszy lub nacisnąć klawisz F2
Na koniec obliczymy dochód poprzez odjęcie przychodu od kosztów
i otrzymamy tabelę jak poniżej
Przykład jak zablokować komórkę w Excelu tylko dla wiersza lub tylko dla kolumny
Odwołanie bezwzględne umożliwia całkowite zablokowanie komórki lub zablokowanie jej wyłącznie dla wiersza lub kolumny.
Aby zablokować komórkę wyłącznie dla kolumny – w formule symbol $ powinien znajdować się jedynie przed jej oznaczeniem literowym kolumny np. $D6. Natomiast jeśli chcielibyśmy zablokować kopiowanie komórki tylko dla wiersza znak $ powinien znajdować się tylko przed oznaczeniem cyfry komórki np. D$6
Najlepszym przykładem, aby wytłumaczyć zasadę blokowania komórek tylko w wierszu/kolumnie jest tablica mnożenia wartości zakupu przez różne wartości kursów walut
Formuła w tym przypadku powinna wymnażać wartość zakupu (komórka niebieska D6) przez kurs dolara (komórka czerwona E5)
Ale aby móc przekopiować tę formułę zarówno dla wierszy jak i kolumn musimy zablokować odwołanie do komórek:
– dla niebieskiej zablokować ją w kolumnie, czyli $D6 (i dopuścić zmianę odwołania w wierszu)
– dla czerwonej zablokować ją w wierszu, czyli E$5 (i dopuścić zmianę odwołania w kolumnie)
Dzięki takiemu zabiegowi możemy przekopiować tę formułę dla wszystkich komórek i otrzymać prawidłowe wyniki mnożenia
Kurs Excel
Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.
