fbpx

Szybkie blokowanie komórek

09.09.2014 | Analizy sprzedaży, ECP1, Fundamenty Excela, HR, Triki, Usprawnienia

Zapewne w wielu plikach, na których pracujesz spotkałeś się z tajemniczymi znakami $, np. w zapisie: A$1.

Albo tak: $A1

Albo jeszcze tak: $A$1 (to z pewnością najczęściej).

Co to znaczy? Po co to?

Zapewne zapis $A$1 jest Ci bliski – blokuje on komórkę. Ale te dwa poprzednie?

Wszystkie powyższe zapisy to różne rodzaje adresowania komórek. Stosujemy je wbrew pozorom po to, aby ułatwić sobie życie. Można wyróżnić dwa zastosowania, w którym adresowanie komórek jest szczególnie przydatne:

  1. Powtarzalność pojedynczej wartości – masz dane, pozornie stałe, ale mogące się zmieniać, np. kurs CHF czy EUR. Na tych wartościach opierasz swoje obliczenia.
  2. Powtarzalność formuł – tworzysz formuły niemal identyczne, np. plan handlowców na poszczególne miesiące.

Powtarzalność wartości, to np. sytuacja, gdy chcemy ustalić ceny cennikowe produktów. Potrzebujemy do tego cenę zakupu i narzut. Cena zakupu jest wyrażona w EUR, a potrzebujemy przeliczyć ją na PLN. Narzut jest procentem – my chcemy dodać konkretną wartość złotówkową do ceny zakupu, aby powstała nam cena cennikowa. Zarówno kurs EUR jak i narzut są u nas wspólne, czyli takie same dla wszystkich produktów.

Tak wygląda ta sytuacja:

Ustalanie ceny cennikowej

Ustalanie ceny cennikowej

Zauważ, że zarówno kurs EUR, jak i narzut to wartości używane przez inne formuły (takie samych dla nich wszystkich!). Na tę chwilę wynoszą 4,50 zł i 25%, jednak jutro ta sytuacja może się zmienić. Jeśli się zmieni – nie ma problemu – po prostu podmienimy wartości w żółtych komórkach i już! Oparte na nich formuły same się przeliczą :).

Powtarzalność formuł, to np. sytuacja, gdy planujemy sprzedaż miesięczną dla przedstawicieli handlowych. Znamy plan roczny dla każdego przedstawiciela, znamy sezonowość sprzedaży (mówi nam o tym współczynnik) – ustalamy plan miesięczny, o tak:

Ustalanie planu sprzedaży miesięcznej

Ustalanie planu sprzedaży miesięcznej

Można sobie pomyśleć, że w powyższym przykładzie, dla każdego handlowca, w styczniu stałą wartością jest współczynnik – 1%, dla lutego – 2%, dla marca – 4% itd. Daje nam to łącznie 12 oddzielnych, prawie identycznych formuł (plan roczny * miesięczny_współczynnik%), obliczających plan handlowców dla każdego miesiąca. Wygląda na to, że potrzebujemy oddzielnych 12 formuł, ponieważ współczynniki miesięczne są różne. Podobnie, jakbyśmy spojrzeli na tę macierz w drugą stronę: każdy plan roczny handlowca jest różny.

Nie ma jednej wspólnej stałej wartości, ale jest wspólny wzór/schemat obliczania. Czyli formuła. Wzór jest taki: plan_roczny * współczynnik_miesięczny. Korzystając z odpowiedniego adresowania komórek napiszemy jedną formułę w komórce D8 i skopiujemy ją do pozostałych komórek. (Oczywiście nad taką tabelą trzeba będzie jeszcze popracować z zaokrągleniami, ale podstawę już mamy).

Teraz zastanowimy się, którego adresowania komórek użyć w każdej z tych sytuacji i wreszcie: wytłumaczę Ci na czym ono polega.

Kluczowe w zrozumieniu adresowania, jest uświadomienie sobie co tak naprawdę Excel robi podczas kopiowania formuł (w końcu będziemy kopiować formuły!): zachowuje on pewien schemat. Schemat ten (cały!) jest przenoszony identycznie zgodnie z kierunkiem kopiowania. Nadaje się to idealnie, gdy mamy do czynienia z sytuacją, kiedy mamy różne wartości komórek, np.:

Różne wartości - brak dolarów

Różne wartości – brak dolarów

Tutaj nie mamy dolarów, ponieważ wartości ceny zakupu i narzutu są różne dla każdego produktu. Formuła w kolejnym wierszu (wiersz 9), będzie więc korzystała z komórek z tego kolejnego wiersza (wiersza 9). Kolumny D i E, w których są cena zakupu i narzut, pozostaną bez zmian, ponieważ kopiujemy formułę w dół, więc schemat (dodawanie dwóch sąsiednich komórek) przesunie się w dół. I wszyscy zadowoleni: my i Excel. Prościzna.

Powtarzalność wartości

Sytuacja jednak zaczyna komplikować się, gdy mamy już pewną stałą wartość, czyli chcemy dopiero obliczyć cenę zakupu w PLN, na podstawie ceny w EUR i stałego dla wszystkich formuł kursu EUR. Gdybyśmy zastosowali proste kopiowanie formuł mnożącej cenę w EUR i kurs EUR (C8*C3) otrzymalibyśmy taki wynik:

Skutek braku dolarów

Skutek braku $…

Pierwsza wartość jest OK, ale kolejne… nie :). Zobaczmy dlaczego:

Przyczyna błędnych wyników

Przyczyna błędnych wyników

Schemat się przesunął… Konkretnie: przesunął się w dół, czyli pobiera wartość ze złego wiersza dla czerwonej komórki (kursu EUR). Powinien nadal czerpać go z wiersza nr 3. Dla każdego produktu.

I to trzeba jakoś Excelowi powiedzieć.

Dochodzimy więc do sedna sprawy: to właśnie dolary ($) informują Excela co ma zostać zablokowane (nie mają nic wspólnego z walutą USD). A zasada jest taka, że dolary blokują to, przed czym stają. A mogą stać przed kolumną ($A1) lub przed wierszem (A$1). Lub czyli jest jeszcze trzecia opcja: przed wierszem i kolumną jednocześnie $A$1, czyli ta najczęściej spotykana opcja.

Najczęściej spotykana, ponieważ w większości sytuacji wystarczająca i dodatkowo… łatwiej wstawić 2 dolary, niż jeden. Czemu? Ponieważ do wstawiania znaczka $ służy klawisz F4 na klawiaturze (uwaga na laptopy, gdzie może być konieczność użycia dodatkowo klawisza FnFn+F4). Działa on tak:

  • 1 x F4 → $A$1
  • 2 x F4 → A$1
  • 3 x F4 → $A1
  • 4 x F4 → A1

Użycie dwóch dolarów blokuje całą komórkę, czyli zarówno wiersz jak i kolumnę. Dlatego w naszym przykładzie, zarówno kurs EUR, jak i narzut (C4: 25%) możemy tak właśnie zablokować.

Choć oczywiście nie ma aż takiej potrzeby, ponieważ obie nasze formuły – obliczającą cenę zakupu w PLN i narzut – kopiujemy tylko w dół, zatem zmieniają się tylko wiersze, a nie kolumny. Natomiast szybciej nam wstawić dwa dolary niż jeden (wystarczy raz nacisnąć F4), więc jest to bardzo powszechna praktyka w przypadku, gdy mamy do czynienia ze stałą, powtarzającą się wartością. Takie formuły zatem tutaj pokażę.

Formuła na obliczenie ceny cennikowej PLN (D8) może być taka:

=C8*$C$3

A na obliczenie narzutu (E8) – taka:

=D8*$C$4

Powtarzalność formuł

Sytuacja jednak komplikuje się, gdy nie ma wspólnej wartości. Np. we wspomnianej wcześniej sytuacji ustalania miesięcznych planów sprzedaży dla handlowców:

Ustalanie planu sprzedaży miesięcznej

Ustalanie miesięcznego planu sprzedaży

Aby obliczyć plan miesięczny dla handlowca (np. Nowak), należy pomnożyć jego plan roczny (C8: 7 400 zł) przez współczynnik sezonowości (D5: 1%). Ten schemat należałoby powtórzyć dla każdego handlowca.

Jak jesteśmy sprytni, to zauważymy, że jeśli napiszemy 12 formuł (dla każdego miesiąca), to w sumie można zablokować w każdej z nich komórkę ze współczynnikiem (np. $D$5, potem $E$5, $F$5 itd.).

Ale to nadal jest 12 formuł…

Prawie identycznych formuł…

Słabiutko…

Trzy minut męczarni z szefem, który stoi nad Tobą i sapie… 😉

Nie ma co, trzeba szukać innego rozwiązania. I ono oczywiście jest :).

Zauważ, że tutaj tylko pozornie nie ma części wspólnej, ponieważ nie ma jednej wspólnej wartości. Ale część wspólna jest w samej formule, czyli w samym WZORZE matematycznym, który stosujemy. Zobacz:

Metoda krzyżyka

Metoda krzyżyka

Widzisz krzyżyk, który się utworzył?

Ja to tak nazywam: metodą krzyżyka w namierzaniu danych, które należy zablokować. Tutaj są to:

  • plan roczny, który zawsze, niezależnie od handlowca jest w kolumnie C.
  • współczynnik sezonowości, który zawsze, niezależnie od miesiąca jest w wierszu 5.

Są to zatem elementy, które są wspólne, więc należy je zablokować. Po wpisaniu takiej formuły do pierwszej komórki (D8):

=$C8*D$5

i skopiowaniu jej do pozostałych, otrzymujemy taki wynik:

Ustalanie planu sprzedaży miesięcznej WYNIK

Ustalanie planu sprzedaży miesięcznej: WYNIK

Oczywiście w metodzie krzyżyka nie zawsze będziemy szukać krzyżyka. Krzyżyk jest w macierzach, z jaką niewątpliwie mamy tutaj do czynienia. Ale może to być tylko jedna z jego ramion: wiersz albo kolumna. Zawsze zaś będą to elementy wspólne.

Metoda krzyżyka, moja ulubiona, świetnie się nadaje również w popularnej tabliczce mnożenia, która podobno jest często stosowana w testach sprawdzających excelowe umiejętności kandydatów do pracy:

Krzyżyk w tabliczce mnożenia

Krzyżyk w tabliczce mnożenia

I takich przykładów można byłoby mnożyć i mnożyć…

 

Jak widzisz temat adresowania komórek jest dość złożony. Jestem jednak pewna, że po przeczytaniu tego artykułu będzie Ci już łatwiej się w nim odnaleźć. Pamiętaj tylko, aby ćwiczyć i używać tego. Wiesz jak to jest, jak czegoś nie używamy… baaardzo szybko to zapominamy…

 

A żeby było Ci łatwiej ćwiczyć, pobierz plik z zadaniami z tego artykułu:

MalinowyExcel Szybkie blokowanie adresowanie komórek dw.xlsx

 

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie podstawowym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 1! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

13 komentarzy

  1. A jak zrobić to dla wszystkich odwołań w całej formule jednocześnie ?

    Reply
    • Należy wejść do edycji komórki (F2), zaznaczyć całą formułę i wcisnąć F4 😉

      Reply
      • a co z Excelem 2013 ?

        Reply
        • Działa tak samo.

          Reply
  2. Kupiłam nowy komputer, i nie mam pojęcia, dlaczego f4 nie działa, jest może zastępcza opcja blokowania komórki? Proszę o pomoc 🙂

    Reply
    • Czy to jest może komputer lenovo? Wiem, że w niektórych trzeba to zmeiniać w BIOSie. A jeśli inny – poszukaj w panelu sterowania 🙂
      Inna opcja to ręczne dodawanie dolarów…

      Reply
      • gdzie to jest w panelu sterowania? mam Lenovo

        Reply
        • Jeśli nie działa Ci F4 to wciskaj Fn + F4. Jeśli chcesz to zmeinić, żeby wciskać po prostu F4 – na Lenovo (wg mojej wiedzy), trzeba to zmeiniać w BIOS-ie (Hot keys czy coś takiego).

          Reply
      • gdzie to jest w panelu sterowania?

        Reply
  3. witam
    a jak zrobić jak formuła jest w w tabeli i musimy zatrzymać adresowanie ?

    Reply
    • Widzę, że ktoś już to pytanie zadał – też mnie to interesuje.

      Reply
  4. dzięki, pomocne

    Reply
    • Super, bardzo się cieszę!

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest