Zapisz się na newsletter i odbierz DARMOWY EBOOK
10 najprzydatniejszych porad excelowych

piątek, 18 listopada 2016

Ile jest aktywnych polis ubezpieczeniowych?



Jakiś czas temu jedna z czytelniczek bloga zapytała mnie, w jaki sposób obliczyć ile polis ubezpieczeniowych z jej listy jest aktywnych. O każdej polisie wiemy kiedy się zaczęła i jaka jest jej data ważności. Interesuje nas: ile polis na dany dzień (dziś) jest aktywnych? Pokazaną metodę możemy zastosować w milionie innych sytuacji: czy pracownik pracował w interesującym cię okresie, data ważności produktu/faktury (choć tutaj wystarczy tylko data do - zobacz tutaj), realizacja projektu w terminie itd...

Korci mnie, żeby od razu wyliczyć ile czasu zostało do przeterminowania polisy i żeby, jeśli termin jest bliski, na tej podstawie wyświetlać jakiś komunikat lub kolorować zbliżające się daty... Ale to w kolejnych wpisach :)

Oto formatka:


wtorek, 15 listopada 2016

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:


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:


czwartek, 22 września 2016

Obliczanie czasu pracy: godziny nocne REAKTYWACJA

Dziś chciałam napisać o czymś innym, ale Łukasz skutecznie skłonił mnie, abym znowu zajęła się tematem godzin nocnych;) Łukasz zauważył, że w poprzednim wpisie, a co za tym idzie - w poprzedniej formule - jest błąd. Nieprawidłowo liczyła ona bowiem czas pracy nocny, gdy pracownik zaczynał pracę o 6:00 (godzina graniczna godzin nocnych), a kończył o 23:00 (już w trakcie godzin nocnych). Czyli przepracował 1 godzinę nocną. Łukasz - dzięki wielkie za uwagę i wytrwałość w przypominaniu mi o temacie ;)

Poprzednia formuła wyglądała tak:

=JEŻELI(ORAZ(A6>=nocna_do;A6<nocna_od;B6>nocna_do;B6<=nocna_od;D6<=nocna_od);0;JEŻELI(ORAZ(C6>=nocna_od;D6<=nocna_do+1);D6-C6;JEŻELI(ORAZ(C6<nocna_od;D6<=nocna_do+1;C6>nocna_do);D6-nocna_od;JEŻELI(ORAZ(LUB(C6>=nocna_od;C6<nocna_do);D6>nocna_do+1);nocna_do+1-C6;JEŻELI(ORAZ(C6<nocna_od;D6>nocna_do+1);nocna_do+1-nocna_od;0)))))

Przyznam, że perspektywa analizy formuły-tasiemca wcale mi się nie widziała... Zabierałam się więc do tego jak pies do jeża. Nie mogłam jednak odkładać tego w nieskończoność, więc w końcu zasiadłam do pracy. Oczywiście, jak zobaczyłam formułę (dla przypomnienia wyklejam ją powyżej), to się przeraziłam! Analiza jej zajęłaby mi wieki i z pewnością poszarpałabym sobie na niej nerwy, a tego chciałam za wszelką cenę uniknąć ;) Stwierdziłam więc, że napiszę ją od nowa. Oczywiście oznaczało to dla mnie wgryzanie się w temat na nowo i wymyślanie wszystkich możliwych opcji na nowo... Ech, no cóż. Do dzieła!

Czytaj dalej -->

wtorek, 9 sierpnia 2016

Jak przyporządkować dane (WYSZUKAJ.PIONOWO)?

Częstym pytaniem, jakie mi zadajecie w mailach, na szkoleniach jest: Jak zrobić, aby na podstawie określonych danych znaleźć inne dane? Albo inna wersja: Co zrobić, gdy mam tabelę z danymi i na ich podstawie, chcę do niej dokleić dane z innej tabeli?

Żeby lepiej zobrazować o co chodzi, weźmy przykład: wybrałam kilku pracowników (imię i nazwisko) i chcę przyporządkować do nich datę zatrudnienia. Potrzebne dane mam w innej tabeli. Dla uproszczenia formuły - wszystkie dane będą w tym samym arkuszu.

Oto formatka:


Czytaj dalej-->

niedziela, 3 lipca 2016

Rozstrzygnięcie konkursu o tabelach przestawnych


W zeszłym tygodniu ruszył konkurs o tabelach przestawnych. Bardzo dziękuję wszystkim, którzy wzięli udział w tym konkursie. Konkurs trwał do piątku, 1 lipca 2016, a więc nadszedł czas na wyłonienie zwycięzcy. Przyznam, że zadanie miałam trudne, bo wiele Waszych odpowiedzi na pytanie konkursowe mi się baaaardzo podobało. Wiele pokazywało, jak świetnym narzędziem są tabele przestawne i jak mogą usprawnić pracę. Niestety nagroda jest tylko jedna i musiałam się zdecydować. Przyznam, że wybór był dla mnie na tyle trudny, że poprosiłam o pomoc męża :) Wspólnymi siłami wybraliśmy zwycięzcę.

Przypomnę pytanie konkursowe:

Dlaczego chcesz nauczyć się tabel przestawnych i jak zamierzasz je wykorzystać w swojej pracy?

Czytaj dalej --> 

czwartek, 23 czerwca 2016

Tabele przestawne – konkurs z nagrodami!!!


Dziś przyszedł czas na obiecany konkurs!

Ostatnio pytałam was na Facebooku i w newsletterze, jaki konkurs chcecie najpierw: o tabelach przestawnych czy o makrach? Zdania były oczywiście podzielone, ale większość z was najpierw chciała konkurs o… tabelach przestawnych! Przyznam, że strzelałam, że będziecie woleli makra ;)

Tak więc dziś robimy konkurs o tabelach przestawnych, a kolejny konkurs będzie o makrach.

Pytanie konkursowe prościutkie, a do wygrania kurs wideo o tabelach przestawnych wydawnictwa Videopoint (grupa Heliona).


Weź udział w konkursie -->

wtorek, 14 czerwca 2016

Suma tylko wartości dodatnich lub ujemnych


Przy robieniu różnego rodzaju zestawień, często zachodzi potrzeba podsumowania tylko wartości dodatnich lub tylko ujemnych. Np. gdy tworzymy podsumowanie przychodów i rozchodów w firmie czy choćby w projekcie - często chcemy znać sumę tychże wpływów i wydatków. Jedną z metod jest stworzenie oddzielnych kolumn dla wpływów i oddzielną dla wydatków (tak się robi przy raportach kasowych), a potem posumowanie każdej z nich zwykłą funkcją  SUMA. To oczywiście działa i jest świetnym i prostym rozwiązaniem, jednak nie zawsze tak chcemy/możemy.

Pokażę wam dzisiaj również bardzo prostą metodę, która potrzebuje jednej kolumny z wartościami, gdzie wpływy będą dodatnie, a wydatki ujemne. Dane wyglądają tak:



W komórce B14 chcę mieć sumę wpływów na konto, a w B15 - sumę wydatków.

Czytaj dalej -->

poniedziałek, 30 maja 2016

Jak obliczyć średnią bez skrajnych wartości

Ostatnio jeden z wiernych czytelników bloga (pozdrawiam cię Piotrek:)) potrzebował obliczyć wartość średnią, jednak bez skrajnych wartości. Wymyślił, że można to zrobić funkcją MalinowyExcel_new_małeŚREDNIA.WARUNKÓW. Oczywiście można, jednak nie jest to takie oczywiste, jak może się wydawać. Pomyślałam, że jest to temat warty wspomnienia na blogu: komuś z was też może się przydać. Jak nie ten konkretny przykład, to choćby ciekawy/nie do końca intuicyjny sposób podawania kryteriów tej funkcji. Tej i innych z grupy COŚTAM.JEŻELI, COŚTAM.WARUNKÓW. Oczywiście mam tutaj na myśli np. SUMĘ.JEŻELI CZY SUMĘ.WARUNKÓW :) W nich wszystkich kryteria wpisujemy dokładnie tak samo, czyli... tekstowo.

A żeby temat urozmaicić, kolorem zaznaczę jeszcze wartości skrajne. Mój przykład będzie liczył średnią ocenę zawodników.

Jest 6 sędziów, każdy daje swoją notę, 2 skrajne się odrzuca, resztę uśrednia i wychodzi wynik końcowy. Formatka wygląda tak:

Średnia bez skrajnych - formatka

Czytaj dalej -->

wtorek, 10 maja 2016

Przeterminowane faktury

Pokażę wam dziś jak sprawdzić, czy faktura jest przeterminowana. Temat szczególnie przydatny księgowym, ale nie tylko. Możemy bowiem sprawdzać termin wykonania zadania zleconego pracownikowi (to np. dla Project Managerów) czy termin ważności produktu spożywczego, czy jakiegoś kosmetyku. Zastosowań mnóstwo, a formuła i sposób sprawdzenia tego w Excelu - taki sam, niezależnie co sprawdzamy: faktury, deadline, szampon do włosów, jabłka czy gruszki :) We wpisie będę sprawdzać faktury. Zobaczcie, tak wygląda mój arkusz:

Przeterminowane faktury formatka

Przeterminowane faktury formatka[/caption] Czyli będę chciała określić status faktury (czy jest przeterminowana, czy nie) i dodatkowo określę o ile dni jest przeterminowana. Na koniec, żeby dodać trochę grafiki, którą tak wszyscy kochamy :), zaznaczymy kolorem te wiersze, w których występuje faktura przeterminowana.

Czytaj dalej -->

środa, 24 lutego 2016

Kierunek grupowania danych

Tytuł tego wpisu brzmi dość enigmatycznie - wiem :) Chodzi o to, żeby zamiast tego:

Grupownie domyślne: PRZED 

mieć to:

Grupowanie: PO

Już tłumaczę, o co chodzi.

Czytaj dalej -->