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

Dla zainteresowanych: wielkanocna promocja na szkolenia wideo

Jakbyście byli zainteresowani, to właśnie dostałam informację od firmy Videopoint, że w dniach 10-12 kwietnia 2017 mają wielkanocną promocję:

Przy zakupie wybranych kursów wideo dostaniecie gratis odpowiadające im tematycznie e-booki.

Informację o tym, czy i jakiego e-booka dostaniecie, znajdziecie na stronie danego kursu pod hasłem: „Promocja Pakiet Plus!„. Na fakturze kwota zakupu będzie podzielona na kurs wideo i e-book.

Link do sklepu to: Videopoint.pl.

 

Redakcja językowa: Aleksandra Wasiak
www.kulturajezyka.pl

Mediana w tabeli przestawnej?

Ostatnio pewna uczestniczka szkolenia z Excela dla HR zadała mi pytanie, którego jeszcze nikt wcześniej mi nie zadał. Miałam więc bardzo dużą motywację, aby szybko jej odpowiedzieć. 😉 Pytanie brzmiał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:

Mediana w tabeli przestawnej - dane źródłowe

Dane źródłowe (fragment)

Chcę takie:

Mediana w tabeli przestawnej - wynik

Wynik

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

Funkcja PRZESUNIĘCIE pomaga liczyć podsumy

Ostatnio napisał do mnie Radek z ciekawym pytaniem. Miał on bardzo dużą, dość specyficznie zbudowaną tabelę, w której chciał zastosować podsumowania części danych (coś á la sumy częściowe). Na koniec chciał mieć sumę wszystkiego. Radek się zastanawiał, czy da się to zrobić za pomocą jednej formuły. Przyznam, że rozwiązanie do oczywistych nie należy i jest bardzo ciekawe, bo zawiera funkcję PRZESUNIĘCIE…

Formatka wygląda tak:

Przesunięcie formatka

Formatka

W żółtej komórce F3 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 znajdziesz na końcu artykułu). Sam przyznasz, że jest to mrówcza praca, a w całym ćwiczeniu chodzi o to, aby użyć 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…

 

Segregatorek

Tak wygląda mój segregatorek. 🙂

Ale dziś się zdenerwował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 opcji wklej specjalnie jako transpozycja. Metoda świetna i skuteczna – sama bardzo często jej używam. Niestety w niektórych sytuacjach jest niewystarczająca. Głównym założeniem wklejania specjalnego jest to, że zadziała ono zgodnie z oczekiwaniami tylko wtedy, gdy ręcznie wkleimy wartości (np. na formuły już nie zadziała tak, jakbyśmy tego chcieli). Można więc w uproszczeniu powiedzieć, że jest statyczna. A przecież czasem chcemy, by taka transpozycja się aktualizowała, była dynamiczna, 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. Trochę to karkołomne, ale działa. 🙂

Mało osób jednak wie, że istnieje funkcja, która umie sprawić, aby transpozycja była dynamiczna. Ma ona pewien haczyk, więc trzeba wiedzieć, jak ją zastosować, ale zobaczycie sami, że jest prościutka. Pokażę wam!

Tradycyjnie zacznijmy od formatki:

TRANSPONUJ_Formatka

Formatka

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

Excel w nieruchomościach -formatka

Formatka

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

MIN i MAX ograniczenie - formatka

Formatka

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.

Alternatywa dla JEŻELI - formatka

Formatka

Czytaj dalej