• Zapisz się na newsletter, aby otrzymywać powiadomienia o najnowszysch wpisach.

Niestandardowe zmniejszanie sumy w zależności od wpisu w kolumnie obok

Czyli kolejne wykorzystanie genialnej LICZ.JEŻELI

Dzisiaj króciutko o tym jak os dumy wartości w komórkach odjąć 1, za każdym razem, jak w komórce wystąpi określone słowo, np. “brak”. Artykuł ten jest odpowiedzią na pytanie Pawła, który właśnie miał taki case do rozwiązania. Jedyne co zmieniłam, to słowo: u Pawła było “b/ś”, a ja dałam “brak”, bo tak mi bardziej pasuje. Oczywiście słowo może być dowolne – trzeba je tylko wpisać do formuły 🙂

Oto formatka:

Formatka

Formatka

Do dzieła! Czytaj dalej

Zaokrąglanie cen za pomocą WYSZUKAJ.PIONOWO

Czyli nietypowe zastosowanie WYSZUKAJ.PIONOWO

O zaokrąglaniu cen pisałam już jakiś czas temu tutaj. Natomiast był to zupełnie inny przypadek niż ten, który opiszę dzisiaj. Celem dzisiejszego przykładu bowiem jest zaokrąglenie cen zgodnie ze schematem: ceny od 200 zł do 204,99 zł mają być równe 199 zł. Ceny od 205 zł do 209,99 zł – 209 zł. Ceny 210 zł i powyżej – 219 zł. Najlepiej pokazuje to poniższy obrazek, a na nim tabela zaokrągleń:

Formatka

Formatka

Ponieważ celem jest pewien rodzaj zaokrąglenia – od razu nasze myśli kierują się w stronę jakiejś funkcji zaokrąglającej. Pewnie dałoby się coś tutaj pokombinować, ale trzeba byłoby się nieźle natrudzić. Ja natomiast jestem zwolenniczką prostoty, więc pójdę na łatwiznę ;).

Czytaj dalej

Średnia niezerowych wartości z wybranych kolumn

Jakiś czas temu napisała do mnie Dorota z bardzo ciekawym problemem. Zobaczcie:

Mam dość duży zakres danych, z których interesują mnie wartości znajdujące się w co którejś kolumnie, a jest tych kolumn kilkadziesiąt (tych, które mnie interesują). Potrzebuję z tych danych zrobić średnią, z pominięciem zer. Średnia.jeżeli nie działa, ponieważ ma za dużo atrybutów (te wszystkie komórki, wpisane oddzielnie). Masz jakiś pomysł, jak można by to zrobić?
Dodam, że zależy mi na utrzymaniu tych danych w takim układzie, w jakim są, więc wyciąganie ich do oddzielnego arkusza, po to, żeby skorzystać z średniej jeżeli, odpada. No chyba, że się poddam. 😉

Czyli Dorota ma ogromniastą tabelę z “milionem” kolumn i z niektórych z nich chce wyciągnąć średnią. Jeśli w jakiejś z wybranych kolumn jest zero – ma nie zostać uwzględnione w liczeniu średniej. Czyli tak na prawdę mamy dwa warunki do spełnienia:
1 – liczby niezerowe,
2 – odpowiednia kolumna.

Formatka wygląda tak (oczywiście w moim przykładzie jest znacznie mniej danych, a przede wszystkim – kolumn ;)):

Średnia wartości nieujemnych z wybranych kolumn - formatka

Formatka

Czytaj dalej

Ceny kończące się na 9 groszy

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

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?

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

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

Zaokrąglanie liczby do wymaganej wielokrotności

Czytelnik mojego bloga – Dariusz, zadał następujące pytanie:

Jaka funkcja zaokrągla wpisaną liczbę do wielokrotności wymaganej? Np.: wielokrotność to 25. Jeśli wpiszę liczbę 1 to wyświetlone zostanie 25, jeśli 26 to 50, jeśli 0 to 0.

W tym artykule pokażę Wam jak to zrobić. Będę zaokrąglała następujące liczby:

Zaokrąglanie liczby do wymaganej wielokrotności - formatka

Czytaj dalej