Archiwa tagu: Poziom średniozaawansowany

Jak dodać proste Formatowanie warunkowe w Excelu?

przez | | , ,
Dodaj komentarz

Czyli fragment malinowego kursu online MS Excel w codziennej pracy

Jestem w trakcie tworzenia kursu online “MS Excel w codziennej pracy“. Jest to kurs przeznaczony dla osób, które chcą uporządkować swoją wiedzę w Excelu, bo wiedzą, że Excel może więcej i że da się w nim pracować szybciej i sprawniej niż to robili dotychczas; jak również dla tych, którzy dopiero zaczynają pracę w Excelu. Obecnie prowadzę zapisy na listę osób zainteresowanych szkoleniem. Każdy, kto się na tę listę zapisze otrzyma ode mnie bezpłatny ebook o 10 najprzydatniejszych trikach excelowych.

A teraz Chciałabym pokazać Tobie lekcję wprowadzającą do formatowania warunkowego, gdyż często jest to narzędzie, które od zaraz może bardzo ułatwić pracę osobom, które np. tworzą różnego rodzaju zestawienia czy prowadzą listy kontrolne właśnie w Excelu.

Oto lekcja wprowadzająca do formatowania warunkowego:

Tutaj możesz zapisać się na listę osób zainteresowanych szkoleniem: http://bit.ly/excel-kurs-codziennie-me

A tutaj możesz pobierz plik, na którym pracuję podczas lekcji wprowadzającej do formatowania warunkowego:

MalinowyExcel Fragment kursu online Formatowanie warunkowe dw.xlsx

 

Zapisuj się śmiało, a jeśli znasz kogoś, o kim wiesz, że może mu się przydać takie szkolenie – przekaż mu proszę link do strony zapisów:

► http://bit.ly/excel-kurs-codziennie-me

Na pewno będzie Ci wdzięczny! Pomóż mi szerzyć excelowe dobro! 🙂 Dziękuję!!!

 

Jak wykryć duplikaty na podstawie 2 kolumn?

przez | |
16 komentarzy

Czyli formuła w formatowaniu warunkowym

Wyobraźmy sobie sytuację, w której prowadzimy spis projektów, przykładowo obiektów budowlanych, na budowę których sprzedajemy towary. Mamy więc listę, w której odnotowujemy projekty i uczestniczących w nich klientów. Zależy nam na tym, aby na tej liście każda para Projekt-Klient wystąpiła tylko raz. Nie chcemy powiem dublować danych. Chodzi o coś takiego:

Czyli jak dopisujemy do listy nowe dane: projekt i klienta, to Excel ma nam wykrywać, czy ich kombinacja już wcześniej nie wystąpiła. O tym jak to zrobić jest ten wpis.

Czytaj dalej →

Film: Przeliczanie walut z użyciem WYSZUKAJ.PIONOWO

przez | |
Dodaj komentarz

Wysyłacie mi mnóstwo pytań z różnymi Waszymi problemami i zmaganiami w Excelu. Bardzo się z tego cieszę, oby tak dalej! 🙂 Każdemu z Was chciałabym odpowiedzieć poprzez opublikowanie wpisu na blogu, ponieważ wiem, że taki problem na pewno ma gro innych osób. Problem jednak mam mam taki, że po prostu nie nadążam! Więc wreszcie (po kilku latach!) wpadłam na pomysł, że w miarę swoich możliwości będę nagrywała filmiki z odpowiedziami na Wasze pytania, a tutaj na blogu będę umieszczać skrótowe opisy i oczywiście formuły do skopiowania i pliki do pobrania.

Nadal oczywiście będę prowadziła bloga tak, jak do tej pory, czyli nowe “całe” wpisy będą się pojawiać.

Zatem dziś pierwszy film z tego cyklu. Pytanie zadała mi Alicja, która potrzebowała przeliczyć kwoty wyrażone w walutach na złotówki. Oto dokładny opis sytuacji i jej rozwiązanie:

Czytaj dalej →

Ceny kończące się na 9 groszy

przez | | ,
2 komentarze

Jakiś czas temu jeden z czytelników blogu poprosił mnie o pomoc w bardzo nietypowym zadaniu. Skoro nietypowe, to na pewno ciekawe! 🙂 I faktycznie. Czytelnik chciał, aby ceny produktów przekształcić tak, aby zawsze kończyły się na 9 groszy. Czyli np. jak mamy cenę 5,71 zł, to chcemy otrzymać 5,79. Dla 1,05 zł chcemy uzyskać 1,09 zł. Dla pełnych liczb, czyli np. 8 zł, chcemy mieć cenę o grosz niższą – 7,99 zł. To jest całą filozofia tego ćwiczenia i obrazek dla zilustrowania:

Ceny kończące się na 9 groszy - przykład

Przykład

Aby dokonać takiego cudu, posłużę się dwiema funkcjami zaokrąglającymi i oczywiście funkcją JEŻELI. Do dzieła!

Czytaj dalej →

Suma tylko wartości dodatnich lub ujemnych

przez | |
4 komentarze

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ą t

Suma tylko dodatnich wartości - formatka

Formatka

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

Czytaj dalej →

Jak obliczyć średnią bez skrajnych wartości?

przez | |
3 komentarze

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ą Ś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

przez | | ,
19 komentarzy

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:

Formatka

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.

Uwaga! Ten wpis został rozszerzony o dodatkowy status, jeśli faktura została już zapłacona. Artykuł i plik do pobrania znajdziesz tutaj.

Czytaj dalej →

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

przez | | , ,
2 komentarze

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

przez | | ,
2 komentarze

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 →