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

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

Początek zdania wielką literą

Mimo, że Excel to głównie maszynka do liczenia, to zdarza się (i to często!), że coś trzeba w nim napisać. Choćby opisy komórek, czy jakieś wyjaśnienie dla odbiorcy. Załóżmy, że te opisy zawierają w sobie kropkę, która nie oznacza końca zdania, np: odp. na maila. Po napisaniu tej kropki piszemy sobie dalej... ale... zaraz zaraz: co zrobił Excel z pierwszą literą słowa po kropce? Zamienił ją na wielką!!! O tak: odp. Na maila. No nie!!!! Trzeba teraz to skasować i napisać małą literą. Grrr!!! Gorzej, jak coś takiego musimy robić codziennie po kilka razy. Można się wkurzyć!

 

Macie tak? Na pewno jedno z was ma - Mycha - która zadała mi takie pytanie ;) A wystarczy jedna mała opcja, by życie stało się łatwiejsze...

Czytaj dalej -->

Przechodzenie między oknami Excela, czyli pokaż wszystkie okna na pasku zadań

Pierwszy wpis w nowym roku postanowiłam napisać o pewnej bardzo przydatnej opcji. Zwróciła ona moją uwagę, ponieważ nie dość, że ostatnio  na jednym z forów ktoś o to pytał, to na moim szkoleniu jeden z uczestników miał dokładnie ten sam problem :) Uznałam to za sygnał, że temat jest warty opisania. Ok, a co chodzi?

Zdarzyło Ci się kiedyś, że otworzyłeś kilka plików Excela, a na pasku zadań Windowsa miałeś otwarte tylko jedno okno Excela? Wygląda to tak:

 

i jest niesamowicie denerwujące, bo zamiast przechodzić między oknami za pomocą skrótu klawiszowego Alt + tab, trzeba użyć nietypowo Ctrl + tab, lub w ogóle wybierać z menu Widok/ Okno/ Przełącz okna. Masakra.

Na wspomnianym forum jedni radzili, żeby poszperać coś w rejestrze (co?), bo pewnie coś się zepsuło. Inni mówili, że najlepiej przeinstalować Windowsa, a przynajmniej Office'a (pewnie by zadziałało;)). Nic z tych rzeczy :) Co więc należy zrobić, by było "normalnie"?

Czytaj dalej -->

Jak zamienić kropki na przecinki za pomocą makra (VBA)?

Nie wiem czy wiecie, ale artykuł o zamianie kropek na przecinki jest najczęściej czytanym artykułem na moim blogu. Szczerze mówiąc, jak go pisałam, to nawet do głowy mi wtedy nie przyszło, że tak będzie! Niewątpliwie jest to super trik, a wręcz niezbędny dla użytkowników np. SAPa (pozdrawiam! ;)), gdzie jako separator dziesiętny używana jest kropka, a nie przecinek. Oczywiście Excel liczby zapisane w takiej postaci: 1,234.56 traktuje jak tekst, co doprowadza nas - użytkowników - do szewskiej pasji :)

W artykule Jak zamienić kropki na przecinki pokazuję doskonałą (łatwą i szybką) metodę na pozbycie się tych nieszczęsnych kropek i zamianę ich na przecinki.

Co jednak w sytuacji, gdy po kilka razy dziennie musimy dokonywać takiej zamiany? Mimo, że metoda jest genialna, to przydałoby się jeszcze szybsze rozwiązanie. No i tutaj przychodzi nam z pomocą Norbert (pozdrawiam cię :) ), który zapytał mnie jak to zrobić za pomocą... makra! Tadam! Super pytanie, więc postanowiłam, że napiszę o tym artykuł, aby więcej osób mogło poznać ten wspaniały sposób :)

A rozwiązanie nie jest bynajmniej takie oczywiste, o czym się za chwilę przekonacie...

Czytaj dalej -->

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.

Lista rozwijana w prostym formularzu

W tym artykule pokażę jak stworzyć listę rozwijaną w komórce w prostym formularzu. Efekt będzie następujący:

Dodatkowo, gdy użytkownik zaznaczy komórkę, by uzupełnić dane - pojawi się komunikat z informacją, jakie dane ma uzupełnić (komunikat wejściowy). Gdy zaś wpisze błędną wartość - pojawi się komunikat o błędzie (alert o błędzie) i użytkownik zostanie poproszony o wpisanie/wybranie poprawnej wartości.

Zrób zdjęcie w Excelu, czyli aparat fotograficzny

Uważam, że to narzędzie powinno raczej nazywać się kamera niż aparat. Umożliwia ono bowiem oglądanie wycinka arkusza w innym miejscu w skoroszycie. Narzędzie to jest o tyle pomocne, że pokazuje zawsze aktualne wartości w wycinku arkusza, które jest na zdjęciu.

Czyli przykładowo:

1. w arkuszu DEF zdefiniowane są wartości, np. kursy walut, które chcemy jedynie pokazać użytkownikowi, jednak nie chcemy by je modyfikował,

2. w arkuszu Tabela znajduje się tabela z produktami, a użytkownik ma uzupełnić w niej ceny w PLN. Na podstawie tego i kursów walut w arkuszu "DEF" wyliczą się ceny tych produktów w innych walutach. Użytkownik widzi kursy walut.

A wygląda to tak:

Jak rozdzielić imię i nazwisko?

Często pojawiającym się problemem, który zgłaszają użytkownicy Excela jest rozdzielanie imienia od nazwiska, czyli mówiąc bardziej ogólnie: rozdzielanie ciągów tekstowych. W tym artykule pokażę prostą metodę na oddzielenie imienia od nazwiska. Metodę tę można stosować oczywiście również do innych danych tekstowych.

Kreator tabel przestawnych z Excela 2003

Tęsknisz za kreatorem tabel przestawnych z Excela 2003? Zwłaszcza, gdy tworzysz tabelę oparta o zewnętrzne źródło danych?

Aby tego kreatora użyć w nowszych wersjach Excela - trzeba trochę poszperać. W tym artykule pokażę gdzie.

Jak zablokować wiersz?

Gdy mamy do czynienia z dużymi zestawami danych, najczęściej chcemy, by nagłówek danych był widoczny podczas przewijania danych. Czasem zdarza się, że zależy nam, by widoczna była również pierwsza kolumna danych lub nawet kilka pierwszych kolumn. Aby to osiągnąć powinniśmy posłużyć się opcją Excela, która nazywa się blokowanie okienek.

Jak się pozbyć tych denerwujących zielonych trójkącików?

No właśnie... Zielone trójkąciki - zmora większości użytkowników Excela. Chodzi o te zielone trójkąciki:

Zamiana kolumn na wiersze lub odwrotnie

Zapewne zdarzyła Ci się sytuacja w Twojej pracy z Excelem, że trzeba było zmienić kolumny na wiersze lub odwrotnie - wiersze na kolumny, jak na obrazku poniżej:

Pokażę teraz jak łatwo i szybko to zrobić. Opcja, której będę używać nazywa się transpozycja.

Wyświetlanie wartości w tysiącach

Kiedy mamy do czynienia z dużymi liczbami, np. podczas podsumowania półrocza sprzedaży, warto wyświetlać liczby w tysiącach lub nawet w milionach. Wartości pokazane w taki sposób są bowiem bardziej czytelne. Dodatkowo, przy kwotach rzędu kilkudziesięciu tysięcy, mało kto zainteresowany jest wartością groszy. Odbiorcę interesuje raczej orientacyjna wartość: prawie 31 tysięcy czy zaledwie 5,5 tysiąca. Dobrym pomysłem więc jest wyświetlanie tak dużych wartości w tysiącach. Przykładowo 5 670 to 5,7.

Teraz pokażę Ci, jak to zrobić.

"Wyrównaj zaznaczenie do środka" a "scalanie komórek"

Często gdy chcemy nadać kilku kolumnom ten sam nagłówek - korzystamy ze scalania komórek. Scalanie komórek może i ładnie wygląda, jednak jest niepraktyczne. Uniemożliwia lub ogranicza funkcjonalność korzystanie z wielu możliwości Excela, takich jak sortowanie czy chociażby funkcji WYSZUKAJ.PIONOWO(). Aby uzyskać taki sam efekt, jak przy scalaniu komórek, należy użyć opcji formatowania komórki, która nazywa się wyrównaj zaznaczenie do środka.

Szybkie wprowadzanie formuł z zachowaniem formatowania

Mając sformatowaną ładnie tabelkę chcesz wprowadzić do niej formułę i skopiować do całej kolumny. Dwuklikasz lub przeciągasz, aby skopiować tę formułę i co się dzieje z pięknym formatowaniem twojej tabeli? Psuje się ;(. Na przykład tak:

Można tego w prosty sposób uniknąć, korzystając ze skrótu klawiszowego Ctrl enter. Metoda ta pozwala na szybkie wprowadzanie wartości/formuł do komórek w zakresie, niekoniecznie ciągłym (mogą to być komórki nieprzylegające do siebie).

Jak zamienić kropki na przecinki?

Bardzo często, zwłaszcza gdy eksportujemy dane z zewnętrznych systemów (np. SAP), a następnie  importujemy je do Excela, zdarza się, że liczby zapisywane są w innym formacie, np: 1,234.56. Czyli jako separator tysięcy używany jest przecinek, a jako symbol dziesiętny - kropka. Natomiast w standardowym - polskim - ustawieniu, liczby w Excelu zapisywane są tak: 1 234,56. Problem w tym, że podczas importu danych do Excela, format zostaje źródłowy. Krótko mówiąc, do arkusza trafiają nam liczby, wyglądające tak:

Edycja bezpośrednio w komórce

W komórce, której wyrównanie jest ustawione na środkowe i włączone jest zawijanie wierszy, prawie niemożliwa staje się edycja formuły - zwłaszcza długiej. Wyglądać to może na przykład tak:

Data w polskim formacie

Ciekawe jest to, że w Excelu nie ma zdefiniowanego formatu daty, który jest najczęściej używany w Polsce, czyli np:

16.01.2013

Na szczęście można samemu stworzyć taki format. W tym celu należy: