• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Excel w nieruchomościach: cena za m2 na podstawie piętra i metrażu

Niedawno Zbyszek zapisał się na newsletter i przy okazji zadał ciekawe pytanie: jak wyświetlić wartość z określonej kolumny, na podstawie jej nazwy (w nagłówku)? Myślę, że odpowiedź na to pytanie zaciekawi wieeelu z Was, dlatego postanowiłam napisać o tym artykuł (i nagrać filmik – pod wpisem). Przykład z życia wzięty dopasowałam do tego taki:

Excel w nieruchomościach -formatka

Formatka

Jest to tabelka pokazująca ceny za m2 mieszkań znajdujących się na określonym piętrze i o określonym metrażu. Metraż mamy w kolumnach, piętra – w wierszach. W żółtych polach obok każdego piętra chcemy wybrać metraż z listy rozwijanej i na tej podstawie ma nam się wyświetlić cena za m2 (w kolumnie Wartość). To jest zadanie na dziś i jednocześnie klasyczny przykład wykorzystania funkcji INDEKS i PODAJ.POZYCJĘ. Można byłoby tutaj wykorzystać też WYSZUKAJ.POZIOMO z funkcją PODAJ.POZYCJĘ (pod koniec wpisu też to pokazuję).

Czytaj dalej

Data najbliższego przelewu

Dzisiaj będzie rozbudowanie tematu z poprzedniego wpisu, w którym opisywałam jak poznać datę np. pierwszego czwartku miesiąca. Wykorzystałam do tego dość skomplikowaną formułę (ale formułę! nie makro ;)), zawierającą aż 4 funkcje. Dziś sobie to rozszerzymy, ponieważ założenia są takie:

W naszej firmie przelewy wychodzą tylko w pierwszy albo trzeci czwartek miesiąca. Jeśli faktura się “nie załapie” na pierwszy czwartek – jest płacona w trzeci czwartek, jeśli na niego się “nie załapie: – dopiero w pierwszy czwartek następnego miesiąca. Itd…

Straszne 🙂

Czyli, z danych do zadania potrzebujemy na pewno termin płatności faktury i kilka komórek roboczych (można byłoby je wrzucić bezpośrednio do formuły – jak chcecie). Formatka do tego zadania wygląda tak:

Formatka

Formatka

Czytaj dalej

Pierwszy czwartek miesiąca

Dziś temat, który miałam z tyłu głowy już dość długo. Pierwszy raz zaczęłam się zastanawiać na nim, kiedy tworzyłam brytyjski grafik czasu pracy. Anglicy mają kilka świąt, które wypadają zawsze w pierwszy poniedziałek miesiąca. Akurat nie było konieczności, abym wprowadzała taki warunek do arkusza, jednak zastanowił mnie on. Ponieważ od razu nie wymyśliłam rozwiązania, zostawiłam temat. Niestety nadal siedział mi on w głowie. 🙂

Niedawno odezwała się do mnie Marta, która pracuje w księgowości, i odgrzebała kwestię spod gruzów innych pomysłów. Marta ma bardziej skomplikowany problem, jednak zagadnienie, które dzisiaj omówię, jest niezbędne do jego rozwiązania. Pokażę dziś, jak się ustala datę np. pierwszego czwartku miesiąca. Napisałam “np.”, ponieważ zaprezentowaną metodą można ustalić dowolny numer dowolnego dnia tygodnia (oczywiście bez przesady – nie będziemy ustalać 15. poniedziałku miesiąca, bo taki przecież nie istnieje). 😉

Aby formuła była uniwersalna, będziemy potrzebowali następujących danych:

  1. Który z kolei dzień nas interesuje (np. pierwszy).
  2. Jaki dzień tygodnia (np. czwartek).
  3. W jakim miesiącu (np. październik).
  4. W którym roku (np. 2016).

Formatka jest prościutka i wygląda tak:

Pierwszy czwartek miesiaca-formatka

Formatka

Czytaj dalej

Przedszkolne usprawnienie pracy w Excelu

Pewnie niewielu z was wie, ale jestem mamą dwojga dzieci. Moja córeczka chodzi do przedszkola, a synek do żłobka.

Dla tych z was, którzy nie mają dzieci (zwłaszcza w wieku przedszkolnym), wyjaśnię, że pobyt dziecka w państwowym przedszkolu jest teoretycznie darmowy. Praktycznie wygląda to tak, że płaci się za wyżywienie i za tzw. świadczenie, czyli za pobyt dziecka w przedszkolu po godzinie 13:00.

I tak, w zeszłym roku moja córeczka chodziła do przedszkola, gdzie nie było żadnego systemu ewidencjonującego pobyt dzieci. Zaczęłam się więc zastanawiać, jak to jest rozliczane? Nie mogłam się powstrzymać i poszłam do sekretariatu, żeby o to zapytać. Odpowiedź, jaką otrzymałam, zmroziła mi krew w żyłach: ręcznie. AAAAA! Czyli wyobraźcie sobie, że na koniec miesiąca pani intendentka (jakby miała mało pracy…) ręcznie liczyła: ile czasu dane dziecko było w przedszkolu. Potem liczyła, ile pieniędzy powinni zapłacić za to jego rodzice (1 zł za godzinę, naliczanie minutowe). Dostawali oni karteczkę z numerem konta, na które mieli przelać pieniądze i oczywiście kwotą – napisaną ręcznie.

I tak co miesiąc. Na szczęście były tylko 2 grupy po ok. 20 dzieci.

W tym roku są 3 większe grupy.

No ręce mi opadły. Przyszłam do domu i stworzyłam plik w Excelu, który liczy czas pobytu dzieci w przedszkolu oraz należną opłatę. Potem to już było dopracowywanie szczegółów z panią intendentką.

Cały zeszły rok szkolny, dyżur wakacyjny oraz ten rok do tej pory, liczone są na stworzonych przeze mnie plikach (zrobiłam też plik do rozliczania posiłków, ale o tym innym razem). Pani intendentce wyliczenie tego zajmuje teraz nie cały dzień z kawałkiem, jak do tej pory, tylko godzinę (trzeba wpisać dane:)). Różnica jest.

Postanowiłam, że pokażę wam jak wygląda ten cudowny plik i krótko omówię jego funkcjonalność. Mimo że ogromnie ułatwia pracę pani intendentce i oszczędza jej mnóstwo czasu – jest stosunkowo prosty. Zobaczcie.

Czytaj dalej

Szybkie blokowanie komórek

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.

Czytaj dalej