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

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

Formatka

Czytaj dalej

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

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

Jak ustalić ostatni dzień miesiąca?

Hehe, no właśnie. Niedawno się nad tym głowiłam, a to taka prosta sprawa (choć nie na pierwszy rzut oka). Potrzebowałam tego w wielu sytuacjach: liczenie średnich kosztów z określonych miesięcy, uniwersalny grafik czasu pracy, w którym chciałam, aby na podstawie podania miesiąca, wyświetlały się wszystkie dni danego miesiąca i takie tam.

Pierwszy dzień miesiąca łatwo ustalić: zawsze dzień = 1. Z ostatnim dniem jest gorzej, ponieważ miesiące mają różną liczbę dni, a luty to już w ogóle (właśnie, ile dni ma w tym roku? Odpowiedź na końcu artykułu:))!

Dziś pokażę wam 2 sposoby na rozwiązanie tego problemu. Oczywiście jest ich więcej, jednak moje są dość łatwe. Zobaczycie :)

Będę chciała ustalić pierwsze dni następujących miesięcy (na formatce):

Ostatni dzień miesiąca 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

Blokowanie okienek na wydruku

Na szkoleniach często pokazuję ten trik i zawsze uczestnicy są zachwyceni. Stwierdziłam więc, że z pewnością i wam się to przyda i spodoba. Zwłaszcza, jeśli zdarza się wam drukować w Excelu duże tabele, a wieeelu z nas się to zdarza nawet bardzo często :)

O co chodzi?

Chodzi o efekt blokowania okienek na wydruku. Jeśli w arkuszu zablokujecie okienka, to od tego momentu np. nagłówek tabeli będzie zawsze widoczny, niezależnie od miejsca, w którym stoicie (np. środek ogromniastej tabeli). Niestety jak zechcecie wydrukować tę cudną tabelę – nagłówek widoczny będzie tylko na pierwszej stronie wydruku. Kiszka trochę, zobaczcie:

Tak wygląda wydruk bez powtarzania nagłówków na każdej drukowanej stronie

Tak wygląda wydruk bez powtarzania nagłówków na każdej drukowanej stronie

A dalej dowiesz się, co zrobić, aby nagłówek widać było na wszystkich drukowanych stronach :)

Czytaj dalej

Tabela przestawna pokazuje w filtrach elementy usunięte ze źródła danych!

Jeśli często pracujecie z tabelami przestawnymi, to pewnie też natknęliście się na ten problem. Macie tabelę opartą na źródle danych w arkuszu, usuwacie jakieś dane z tego źródła, odświeżacie tabelę i… w filtrze w tabeli przestawnej te usunięte dane cały czas są!!! Grrr! Oczywiście tabela ich nie wyświetla, bo ich nie ma, ale na filtrach są. To jak w końcu? Są, czy ich nie ma? No…i są, i nie ma :)

Oczywiście przydałoby się, aby ich na dobre nie było! W tym wpisie pokażę, jak to zrobić. A jest to jedna bardzo prosta opcja :)

Czytaj dalej

Obliczanie ceny za usługę w zależności od czasu jej trwania

Ile klient powinien zapłacić za usługę (np. konsultację księgowej), która trwa 1 godzinę i 15 minut? A ile w przypadku, gdy za pierwszą godzinę płaci więcej niż za każde kolejne? A ile, jeśli ma płacić za każdą rozpoczętą godzinę?

Z takimi pytaniami zgłosił się do mnie jeden z czytelników bloga. Podobne pytania zadają sobie wszystkie te osoby, które mają ustalić cenę za usługę w zależności od czasu jej trwania. Oczywiście tego typu sprawy można dość szybko wyliczyć ręcznie, jednak na blogu piszę o usprawnieniach pracy, a ten przypadek aż się prosi, żeby go usprawnić :)

Ponieważ mogą być różne zasady rozliczania usług, w tym artykule rozpatrzę dwa ich przypadki.

wariant 1: klient płaci proporcjonalnie za każdą minutę kolejnej godziny,
wariant 2: klient płaci za każdą rozpoczętą godzinę pełną stawkę.

W obu przypadkach:

1. pierwsza godzina ma inną stawkę niż kolejne,
2. za pierwszą godzinę płaci się pełną stawkę.
Czytaj dalej

Jak zaokrąglić w górę – ustalanie planu sprzedaży

Do zaokrąglania liczb w Excelu służy bardzo dobrze znana funkcja ZAOKR. Opisywałam ją już na blogu np. tutaj. Funkcja ta świetnie sprawdza się w sytuacjach, gdy chcemy zaokrąglić jakąś liczbę tak, jak w matematyce. Np. obliczyliśmy cenę brutto, znając cenę netto i stawkę VAT, jednak wartość wynikowa (brutto) wyszła nam z 4 miejscami po przecinku. Używamy funkcji ZAOKR z drugim argumentem równym 2 i działa. W ten sposób zaokrągliliśmy liczbę do 2 miejsc po przecinku.

Problem pojawia się jednak wtedy, gdy wyliczyliśmy np. plan sprzedaży dla handlowców i wyszedł on tak: 25 813,41 zł. Nie damy przecież człowiekowi takiego planu: nie dość, że z groszami, to jeszcze trudno zapamiętać. Może więc zaokrąglić do pełnych setek, czyli 25 800 zł? To się nawet da zrobić funkcją ZAOKR (drugi argument równy -2), jednak wyjdzie nam wtedy wartość mniejsza niż wyliczony plan. Z punktu widzenia biznesu: lepiej dać większy plan niż mniejszy. Wtedy jest większa szansa na zabezpieczenie budżetu. Najlepiej więc będzie zaokrąglić do pełnych setek zawsze w górę (wyjdzie: 25 900 zł). Tego jednak funkcja ZAOKR nie umie…

Czytaj dalej

Początek zdania wielką literą

Mimo, że Excel to głównie maszynka do liczenia, to zdarza się (i to często!), że coś trzeba w nim napisać. Choćby opisy komórek, czy jakieś wyjaśnienie dla odbiorcy. Załóżmy, że te opisy zawierają w sobie kropkę, która nie oznacza końca zdania, np: odp. na maila. Po napisaniu tej kropki piszemy sobie dalej… ale… zaraz zaraz: co zrobił Excel z pierwszą literą słowa po kropce? Zamienił ją na wielką!!! O tak: odp. Na maila. No nie!!!! Trzeba teraz to skasować i napisać małą literą. Grrr!!! Gorzej, jak coś takiego musimy robić codziennie po kilka razy. Można się wkurzyć!

Co chcesz mieć, a co robi Excel to dwie różne sprawy...

Excel robi tak…

Macie tak? Na pewno jedno z was ma – Mycha – która zadała mi takie pytanie 😉

A wystarczy jedna mała opcja, by życie stało się łatwiejsze…

Czytaj dalej