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

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

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

Kierunek grupowania danych

Tytuł tego wpisu brzmi dość enigmatycznie - wiem :) Chodzi o to, żeby zamiast tego:

 

mieć to:

Już tłumaczę, o co chodzi.

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):

 

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:

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

Czytaj dalej -->

Zależna lista rozwijana w komórce

Już od jakiegoś czasu chodziło mi po głowie, aby napisać o tym post, a dodatkowo dziś zapytał o to Waldkorg. Mówię o zagadnieniu, które już wielokrotnie wykorzystywałam przy tworzeniu formatek uzupełnianych później przez inne osoby. Chodziło o to, by maksymalnie przyspieszyć im pracę.

Kiedyś na przykład tworzyłam formularz dla handlowców, którzy uzupełniali dane do zamówienia. Chodziło o to, aby wypisać w tym formularzu wszystkie produkty, które handlowiec zamierzał sprzedać. Najpierw określał grupę produktową, a następnie konkretny produkt z tej grupy. Na formularzu miała się znaleźć cała nazwa grupy oraz konkretny indeks produktu. Ponieważ wpisywanie tego z palca byłoby zbyt czasochłonne i denerwujące, zaproponowałam bardzo sprytne i proste rozwiązanie: 2 listy rozwijane. Pierwsza to lista wszystkich kategorii produktów, druga to lista podkategorii, czyli w tym wypadku lista wszystkich produktów, które się w wybranej kategorii znajdują. Stworzyłam więc listę rozwijaną zależną od wyboru na poprzedniej liście.

Taki sam efekt chce uzyskać Waldkorg (mam nadzieję, że już uzyskał po przeczytaniu tego posta :) - Waldkorg daj znać), który do budżetu domowego potrzebuje kategorii i podkategorii wydatków. Przykładowe dane znajdują się na poniższym obrazku:

 

Lista kategorii i podkategorii[/caption] Czyli np. jeśli wybierzemy kategorię Rozrywka, to na liście podkategorii powinno pokazać się: Kino, Teatr, Basen. Bardzo sprytne rozwiązanie, jeśli w swoim budżecie domowym chcecie analizować więcej szczegółów. Przyznam, że ja w swojej propozycji budżetu domowego ograniczam się tylko do kategorii, dlatego że taki podział wydatków w zupełności mi wystarcza. Jeśli jednak potrzebujecie podzielić to na podkategorie - metoda, którą opiszę dalej będzie idealna. Korzystajcie śmiało!

Czytaj dalej -->

Jak się pozbyć znaku gwiazdki (*) z komórki?

Na jednym ze szkoleń pokazywałam grupie w jaki sposób zamienić kropki na przecinki (możesz o tym przeczytać tutaj lub tutaj - z użyciem VBA). Zaraz potem Jarek, jeden z uczestników, zadał mi pytanie jak usunąć znak kreski/ myślnika z komórki. Wszystko robi się w taki sam sposób, więc z łatwością odpowiedziałam na to pytanie. Proste. Ale Jarek nie dawał za wygraną i pytał dalej: a jak usunąć gwiazdkę (*) z komórki?

I tutaj pojawiły się schody... Yyyyy, eee, nie wiem!

Jarek potrzebował tego do usuwania zbędnych znaków, jakie klienci dopisywali do numerów towarów w jego zestawieniach. Nie może jednak być tak, że nie wiem jak coś zrobić w Excelu, więc zaczęłam szukać odpowiedzi.

A okazała się ona bardzo prosta...

Czytaj dalej -->

Uruchamianie makra za pomocą przycisku w arkuszu (VBA)

W poprzednim artykule opisywałam makro, które usuwa wiersze z pustymi danymi. Omawiałam też kilka sposobów na uruchomienie makra: skróty klawiszowe oraz ikonki na wstążce i pasku narzędzi. Były to sposoby, które sąwygodne dla twórcy makra. Klawisz F5 naciskamy, gdy jesteśmy w edytorze VBA, w kodzie. Użytkownik nie będzie z tego korzystał - gwarantuję :) Ze skrótu klawiszowego Alt + F8 też nie skorzysta. Ze wstążki też nie - nie będzie wiedział, gdzie szukać. Trzeba mu trochę uprościć życie. Właśnie - jemu uprościć, a tobie - jako twórcy - zrobić trochę PR-u profesjonalisty ;) Na szczęście istnieje jeszcze milion innych sposobów, żeby makro odpalić...

Na przykład przyciskiem z poziomu arkusza, co wygląda tak:

 

Efektownie, prawda? I nie wszyscy umieją to zrobić ;) A ty po przeczytaniu tego artykułu będziesz umiał :)

Czytaj dalej -->

Ograniczanie rozmiaru arkusza

Ostatnio opublikowałam artykuł opisujący kalkulator przewalutowania na złotówki kredytu we frankach szwajcarskich. Jednym z arkuszy tego kalkulatora jest arkusz Nowa rata, w którym można wyliczyć wysokość raty w złotówkach. Tak wygląda formatka:

 

Formatka pływająca w arkuszu[/caption] Zauważcie, że formatka jest bardzo mała. Dobrze jest więc, aby w arkuszu była widoczna tylko ona. Która formatka według was wygląda lepiej? Ta powyżej (PRZED), czy poniżej (PO)?

 

No jasne, że PO :) Tu arkusz ograniczony jest do 10 wierszy i 6 kolumn (ostatnia kolumna to F). Wygląda to znacznie ładniej (i bardziej profesjonalnie!). Dodatkowo użytkownik wie, że poza tą formatką nic więcej w arkuszu nie ma. Poza tym, skoro formatka jest taka mała, to po co ma być widoczny cały wielki arkusz, który ma 1 048 576 wierszy i 16 384 kolumn...

Jak to zrobić? Jak ograniczyć arkusz do określonego obszaru? Takie pytanie zadał mi Karol (pozdrawiam Cię!). Odpowiedziałam mu szybko, ale potem pomyślałam, że lepiej pokazać ów sposób szczegółowo. Postanowiłam więc napisać o tym wpis, a dodatkowo - żeby wszystko na pewno było jasne - nagrałam filmik instruktażowy, który również znajdziecie w tym wpisie.

Czytaj dalej -->

Szybkie blokowanie komórek

Na jednym ze szkoleń z zaawansowanego Excela pokazywałam uczestnikom jak używać funkcji WYSZUKAJ.PIONOWO. W omawianym ćwiczeniu zawsze szukana była jedna komórka. Należało więc ją zablokować (użyć adresowania bezwzględnego), by potem móc kopiować formułę. Gdy omawiałam ten przypadek, jednocześnie blokowałam komórkę (wstawiałam znaki $). W tym momencie jeden z uczestników szkolenia przerwał mi, mówiąc: Jak pani to zrobiła tak szybko? Chodziło mu oczywiście o to, w jaki sposób zmieniłam adresowanie komórki na bezwzględne, czyli jak wstawiłam jednocześnie dwa znaki $. Byłam zaskoczona, że tego nie wie, ponieważ myślałam, iż każdy, kto pracuje na co dzień z Excelem zna ten „trik”. Okazało się jednak, że na sali było więcej osób, które go nie znały…

Czytaj dalej -->

Łączenie tekstów z kilku kolumn w jeden wpis w komórce

W tym artykule pokażę wam jak w łatwy i szybki sposób połączyć tekst z kilku kolumn w jeden wpis w komórce. Jest to zagadnienie, którego Szafran - jeden z czytelników mojego bloga - użył ostatnio w pracy.

W moim przykładzie dane wyglądają tak:

Chciałabym wszystkie dane z kolumny Produkt i Jednostka umieścić w jednej komórce tak, by jednostki były umieszczone w nawiasach kwadratowych "[]", a produkty oddzielone przecinkami. Chcę móc to "zdanie" wkleić np w treść maila czy pliku tekstowego.

Nazwa arkusza wyświetlana w komórce za pomocą formuły

Ostatnio potrzebowałam wyświetlić w komórce nazwę arkusza. Chciałam to zrobić bez pisania funkcji w VBA. W tym artykule pokażę Wam co wymyśliłam.

Oto formatka:

Jak się pozbyć odstępów, które nie są spacjami?

Jeden z czytelników mojego bloga - Szafran, napotkał następujący problem: zaimportował do Excela dane liczbowe, które Excel potraktował jak tekst. Zamiast separatora tysięcy został wstawiony odstęp. Odstęp ten ma kod ASCII 160, a nie 32 tak, jak spacja. Dodatkowo standardowo separatorem dziesiętnym jest kropka, a nie przecinek, a liczby ujemne są ujęte w nawiasy. Tak oto wyglądają zaimportowane dane:

W tym artykule pokażę  jak:

1. pozbyć się odstępów, które wyglądają jak spacje,

2. zamienić kropki na przecinki oraz

3. pozbyć się nawiasów i zastąpić je znakiem minus "-".

Wszystko to, aby zamienić zaimportowane liczby, które są dla Excela jedynie tekstem, na prawdziwe liczby. O tym, dlaczego warto tak zrobić piszę tutaj: Zamiana liczb z plików PDF na liczby zrozumiałe dla Excela.

Dwa wiersze, czyli enter w komórce

Baaardzo długi wpis, odsłona druga, czyli jak zrobić, aby w jednej komórce wyświetlane było kilka wierszy? Często zachodzi potrzeba, aby napisać coś w komórce w dwóch wierszach (np. przy długich nagłówkach tabel).

Oto efekt, który chcemy uzyskać:

Jak zapisać pierwiastek?

Zastanawialiście się kiedyś jak zapisać pierwiastek w Excelu? Zarówno kwadratowy jak i dowolnego stopnia? Jest na to jak zwykle kilka sposobów. Ja pokażę dwa z nich. Pierwszy to użycie funkcji wbudowanej, a drugi to potęgowanie. Pierwszy z nich - funkcja, pozwala na wyliczenie pierwiastka kwadratowego, drugi zaś - potęgowanie - nie ma ograniczeń.

Teraz pokażę jako obliczyć pierwiastek na oba te sposoby.

Jak wyświetlić baaardzo długi wpis w komórce?

Gdy mamy do czynienia z długimi wpisami w komórce, np. adres strony internetowej czy długie zdanie (!), nie zawsze chcemy widzieć co tam dokładnie jest napisane. Adres strony www i tak skopiujemy do przeglądarki, a zdanie - przeczytamy w pasku formuły. Takie długie wpisy, gdy są na końcu tabeli - wystają za komórkę i brzydko to wygląda (jak na rysunku poniżej). Często bywa i tak, że jak zwiększymy szerokość kolumny, to nie mieszczą się na stronie (co jest bardzo denerwujące!) i  nie można przeczytać zawartości inaczej niż w pasku formuły. Jest na to na szczęście sposób łatwiejszy niż ręczne zmniejszanie wielkości czcionki.