Nowy adres bloga

Hej!

ten blog w pełnej swojej krasie, aktualizowany 2 razy w tygodniu, znajduje się pod adresem

http://malinowyexcel.pl/

Zachęcam Cię do wizyty właśnie tam :)

Do zobaczenia!

Malina

Mediana w tabeli przestawnej?

Ostatnio na szkoleniu jedna z uczestniczek szkolenia z Excela dla HR zadała mi pytanie, którego jeszcze nikt wcześniej mi nie zadał. Miałam więc bardzo wysoką motywację, aby szybko wymyślić odpowiedź ;) Pytaniem tym było: jak obliczyć medianę w tabeli przestawnej? Chodziło konkretnie o ustalenie przeciętnego wynagrodzenia na danym stanowisku w danym regionie firmy w Polsce.

Tabela przestawna oferuje nam wiele funkcji agregujących, taki jak oczywiście suma czy średnia, ale też maksimum czy odchylenie standardowe. Jest nawet wariancja, natomiast nie ma mediany. Szkoda - to by załatwiło sprawę ;) Pola obliczeniowe też nie na wiele się zdadzą, ponieważ operują na zagregowanych danych, a my chcemy na pojedynczych wynagrodzeniach. Pozostaje więc tylko zabawa z danymi źródłowymi. Tak też zrobiłam.

Czyli z takich danych:

Chcę takie:

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 w moim zainteresowaniu są wartości znajdujące się w co którejś kolumnie, 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, póki co 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 ;)):



Czytaj dalej -->

Ceny kończące się na 9 groszy

Jakiś czas temu jeden z czytelników bloga poprosił mnie o pomoc w bardzo nietypowym zadaniu. Skoro nietypowe, to na pewno ciekawe :) I faktycznie. Chodziło bowiem o to, 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 dostać 1,09 zł itd. Dla pełnych liczb, czyli np. ceny 8 zł, chcemy mieć 7,99 zł. To jest całą filozofia tego ćwiczenia i obrazek dla zilustrowania:

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

Czytaj dalej -->

Funkcja PRZESUNIĘCIE pomaga liczyć podsumy

Ostatnio napisał do mnie Radek z ciekawym pytaniem. Miał o bardzo dużą, dość specyficznie zbudowaną tabelę, w której chciał zastosować podsumowania części danych (coś a la sumy częściowe). Na koniec chciał mieć sumę wszystkiego. Przyznam, żę rozwiązanie do oczywistych nie należy i jest bardzo ciekawe, bo zawiera funkcję PRZESUNIĘCIE...

Formatka wygląda tak:

W żółtej komórce F2 ma się znaleźć suma wszystkich wartości, a te wartości mają być takie:

1. tam, gdzie są szare pola, a w kolumnie B jednostką jest "ryczałt", należy podsumować wszystkie dane pod tym ryczałtem (aż do następnego ryczałtu),
2. tam,gdzie jednostka jest inna niż "ryczałt", należy pomnożyć wartości z kolumn C i D.

W kolumnie E są formuły wstawione ręcznie w odpowiednie komórki, które sumują tam, gdzie jest "ryczałt", a w pozostałe komórki wstawione jest proste mnożenie (plik z rozwiązaniem jest do pobrania pod wpisem). Ponieważ jest to mrówcza praca, to w całym ćwiczeniu chodzi o to, aby zrobić to za pomocą jednej formuły. Do dzieła :)

Czytaj dalej -->

Transpozycja danych za pomocą funkcji

Nie uwierzycie, ale temat tego wpisu wymyśliłam 3 kwietnia 2015 roku. Od tego czasu pisałam na blogu o milionie innych wskazówkach, które były z różnych powodów "pilniejsze". Powiem Wam, że mam takich pomysłów cały segregatorek formatu A6 (ze Statuą Wolności na okładce - taki akurat był w sklepie;)). Co jakiś czas dorzucam tam pomysły, ale prawda jest taka, że i tak wciąż dochodzą mi nowe, właśnie te "pilniejsze" i segregatorek sobie leży prawie zapomniany...

Ale dziś się wkurzyłam i stwierdziłam, że go odkurzę i napiszę o czymś, co tam się znajduje. Przeglądając go znalazłam ciekawy temat, o którym kiedyś (bardzo dawno) już trochę pisałam. Chodzi o temat transpozycji, czyli jak zamienić kolumny na wiersze lub odwrotnie. Używałam do tego metody kopiowania i wklejania specjalnego jako transpozycja. Metoda świetna i skuteczna, sama bardzo często jej używam. Niestety w niektórych sytuacjach jest niewystarczająca. Jej głównym założeniem jest to, że zadziała zgodnie z oczekiwaniami tylko, jeśli wkleimy wartości (na formuły nie zadziała tak, jakbyśmy tego chcieli). Można więc w uproszczeniu powiedzieć, że jest statyczna. Czasem jednak chcemy, aby taka transpozycja dokonywana była dynamicznie, czyli działa się za pomocą formuły. Z moich obserwacji wynika, że wtedy użytkownicy po prostu ręcznie wpisują w pionie odwołania do odpowiednich komórek w poziomie lub odwrotnie. Karkołomne zadanie, ale działa :)

Mało osób bowiem wie, że istnieje funkcja, która umie to zrobić. Ma ona pewien haczyk, więc trzeba wiedzieć jak ją zastosować, ale zobaczycie sami, że jest prościutka.

Tradycyjnie zacznijmy od formatki:

Czytaj dalej -->

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? Myślę, że odpowiedź na to pytanie zaciekawi wieeelu z Was, dlatego postanowiłam napisać o tym post (i nagrać filmik - pod wpisem). Przykład z życia wzięty dopasowałam do tego taki:

Jest to tabelka pokazująca ceny za m2 mieszkań, znajdujących się na określonym piętrze budynku oraz o określonym metrażu. Metraż mamy w kolumna, 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 PODAJ.POZYCJĘ (pod koniec wpisu też ją pokazuję).

Czytaj dalej -->

Alternatywa dla funkcji JEŻELI – o MIN i MAX coś jeszcze…

Ostatnio opisywałam użycie funkcji MIN i MAX jako alternatywę dla funkcji JEŻELI. Funkcje te działają szybciutko i pozwalają uniknąć powtarzania formuły w funkcji JEŻELI. Aczkolwiek, w porównaniu do niej, mają pewne ograniczenie, które w "normalnym" ich użyciu jest zbawienne, natomiast w tym, które opisałam ostatnio - może powodować nieoczekiwane wyniki. Dlatego właśnie o tym dziś napiszę.

Kiedy używamy funkcji MIN i MAX do sprawdzenia pewnych granic czy limitów, np. limit roczny kosztów uzyskania przychodów czy wyświetlanie zera zamiast ujemnego podatku, sytuacja jest prosta: dla podatku wybieramy zawsze większą wartość (zero lub podatek) - funkcja MAX, a dla kosztów - zawsze mniejszą (poniesiony koszt lub limit kosztów) - funkcja MIN. Schemat formuł wygląda tak:

=MAX(0; Podatek)

=MIN(LimitKUP; KUP)

To jak najbardziej działa, jednak ma pewne ograniczenie: w takiej formie nie zadziała poprawnie, gdy zmienne podatek lub KUP będą puste. Kiedy to może wystąpić? Załóżmy, że będziemy liczyli koszty uzyskania przychodu (pusty podatek raczej nie wystąpi, eh). Przyjrzyjmy się sytuacji, gdy przygotowujemy do tego uniwersalną formatkę.  Oto przykład:

W żółtych komórkach w kolumnie E mamy limity KUP - z definicji zawsze uzupełnione. W kolumnie K uzupełniamy poniesione koszty - nie wszystkie musimy ponieść, ale miejsce jest przygotowane. I w ramce w białych komórkach kolumny K chcemy uzyskać koszty, które możemy sobie odliczyć od przychodu, z uwzględnieniem limitów oczywiście.
Na powyższej formatce mamy sytuację, że człowiek pracował tylko na umowę o pracę z normalnymi kosztami. Po naszej formule spodziewamy się, że formuła wyświetli nam koszty do odliczenia tylko w przypadku tej umowy, a dla pozostałych - zero. No i tutaj jest zonk. Dotychczasowa formuła, wyświetli poprawną wartość tylko dla umowy o pracę z normalnymi kosztami, czyli dla tej pozycji, dla której użytkownik podał koszty. Dla pozostałych wyświetli... wartość limitu!!! Kompletnie nie tak, jak tego chcemy.Dlaczego? Excel pominie bowiem wartość z pustych żółtych komórek Poniesione koszty. Jest na szczęście prosty sposób, aby tego uniknąć. O nim w dalszej części wpisu oczywiście.

Czytaj dalej -->

Zero zamiast ujemnego podatku - alternatywa dla funkcji JEŻELI

Nowy rok nadszedł, a wraz z nim rozliczenia roczne podatków, wypełnianie PIT-ów itp. Pisałam już na blogu o funkcji, która może pomóc w rozliczeniu PIT-u, kiedy mamy wiele różnych PIT-ów 11, czyli uzyskujemy przychody z kilku źródeł/umów. Dziś napiszę o kolejnej takiej funkcji. Przy okazji poruszę techniczny temat, jakim jest zastępowanie liczby ujemnej zerem. W sytuacji podatkowej ma to zastosowanie, gdy z rozliczenia wyjdzie nam ujemny podatek. Takiego oczywiście nie płacimy, więc przy uzupełnianiu PIT-u będziemy wpisujemy zero. Pierwszym rozwiązaniem które się nasuwa jest funkcja JEŻELI. Oczywiście funkcja zadziała, jednak powiem Wam, że strasznie mnie ona denerwuje w tym zastosowaniu, ponieważ muszę dwa razy pisać to samo. W tym wpisie przedstawię więc alternatywne rozwiązanie: co zrobić, aby zamiast liczby ujemnej wpisać zero bez użycia funkcji JEŻELI.

Poniżej uproszczona tabelka przedstawiająca przychody, koszty, podstawę podatku oraz należny podatek. Wszędzie tam, gdzie podatek jest ujemny - chcę wyświetlić zero. Jeśli jest dodatni - chcę wyświetlić wartość tego podatku.

Czytaj dalej-- >

Pobieranie danych z arkusza wskazanego na liście rozwijanej

Dziś miało być o tabelach przestawnych, ale Arek zapytał mnie o to, jak napisać formułę, która będzie pobierała dane z arkusza wskazanego w jakiejś komórce. Temat ten chodził za mną już od dawna, więc postanowiłam przełożyć wpis o tabelach przestawnych (które lubię używać, ale nie lubię o nich mówić i pisać ;)) i zająć się tym właśnie przypadkiem. Dorzuciłam też coś od siebie, czyli wybór arkusza z listy rozwijanej - zawsze to jakiś dodatkowy bajerek :)

A więc dziś mamy podaną sprzedaż (ilość i wartość) handlowców w styczniu i lutym, a chcemy mieć raport, w którym zdecydujemy, z którego miesiąca dane chcemy oglądać i dodatkowo - jaka jest średnia cena sprzedanych przez handlowców produktów. W Excelu mamy więc 2 arkusze z danymi: sty i lut, oraz arkusz Raport, w którym chcemy wyświetlić dane z odpowiedniego arkusza dla wybranych osób. Tak wygląda ta sytuacja:



Czytaj dalej -->