Grupowanie danych tekstowych z wielu wierszy – co zrobić, by znalazły się w jednej komórce?

Tytuł jest nieco skomplikowany, więc najlepiej zacząć od omówienia przykładu. Wyobraźmy sobie, że mamy duuużą tabelę zawierającą setki lub tysiące rekordów. Dla przykładu będzie to lista celów podróży dla samochodów służbowych. Chcielibyśmy utworzyć zestawienie maksymalnie zwarte (powiedzmy w jednym wierszu znajdą się dane odnośnie jednego samochodu i celów tras oddzielonych przecinkiem, do których udawał się pracownik).

Dane źródłowe prezentują się następująco (oczywiście jest to fragment):

Trasy dane źródłowe

A oczekiwany wynik tak:

Trasy wersja finalna

Trasy pola tabeli przestawnej

Obróbkę danych rozpocznijmy od utworzenia tabeli przestawnej na bazie naszej tabeli (Wstawianie => Tabela przestawna). W obszarze wierszy wybieramy najpierw kolumnę, wg której chcemy pogrupować dane tekstowe (marka samochodu) oraz komórki tekstowe (cel wyjazdu), które chcemy złączyć, by trafiły do jednej komórki. Dodatkowo, jeżeli chcemy zaprezentować liczbę kilometrów dla danego samochodu, upuszczamy liczbę kilometrów w obszarze Wartości.

W naszym przypadku pojawia się zgrabna tabela. Jednak, gdy flota liczy kilkadziesiąt pojazdów, a liczba podróży to już tysiące, sprawa nie przedstawia się tak optymistycznie – stąd moja propozycja przedstawiona na wstępie tej notki. Rozpocznijmy od edycji opcji tabeli przestawnej (dostępne po kliknięciu prawym przyciskiem myszy). Najważniejsze do dalszej obróbki jest przywrócenie klasycznego układu tabeli przestawnej, znanego z Excela 2003:

Trasy opcje pivot 1

Trasy opcje pivot 2

Pozostaje ukryć jeszcze sumy częściowe, aby nasza tabela nie była „zakłócona” zbędnymi wierszami.

Trasy pivot sumy częściowe

Powstała trzykolumnowa tabela przestawna. Dalszą część pracy wykonałem za pomocą formuł.

Trasy pivot z formułami

 

W pierwszym wierszu zawierającym dane (w naszym przykładzie jest to wiersz 5) wprowadzamy formuły warunkowe:

D5: =JEŻELI(A5=””;ZŁĄCZ.TEKSTY(D4&”, „&B5);B5)

E5: =JEŻELI(A5=””;E4;A5)

F5: =JEŻELI(LUB(ORAZ(A5<>””;A6<>””);ORAZ(A5=””;A6<>””));E5;””)

G5: =SUMA.JEŻELI(E:E;F5;C:C)

Kopiujemy formuły aż do ostatniego wiersza tabeli przestawnej. Dodatkowo, w celu zapewnienia prawidłowego funkcjonowania formuły z kolumny F, dopisałem w komórce A16 dowolny ciąg znaków (Ostatni wiersz).

Jak widzimy, oczekiwane dane pojawiają się w trzech wierszach: 8, 12 i 15. W kolumnie D obejmują one wszystkie cele dla danej marki samochodu, w kolumnie F – markę samochodu, a w kolumnie G – łączną długość tras. Następnie kopiujemy te trzy kolumny do nowego arkusza (bez formuł!) w kolejności, w jakiej chcemy zaprezentować dane.

Trasy tabela przed obróbką

Dopisujemy treść nagłówków, zaznaczamy cały obszar, na którym znajdują się dane i zakładamy filtr. W omawianym przypadku, odfiltrowałem wszystkie puste wiersze w kolumnie A (czyli te pozbawione marki samochodu), zaznaczyłem je i usunąłem te wiersze prawym przyciskiem myszy.

Po drobnych poprawkach edycyjnych uzyskujemy oczekiwany efekt:

Trasy wersja finalna

Excel na iPadzie – czy darmowa wersja jest warta instalacji?

Choć „stacjonarnie” korzystam – czy to w pracy, czy w domu – ze sprzętów z zainstalowanym systemem operacyjnym Microsoftu, to w zakresie urządzeń mobilnych przeniosłem się na iOS zainstalowany na iPadzie i iPhonie. Poza zaletami, jak dopracowanie sprzętu, stabilność oprogramowania, długi czas wsparcia dla urządzeń i mnogość aplikacji, problemem była chociażby dostępność porządnej przeglądarki do plików MS Office. W roku 2014 Microsoft naprawił ten błąd, wydając oficjalną wersję Microsoft Office for iOS.

Oczywiście zainstalowałem wersję darmową programu na swoim iPadzie mini 2 (pod względem procesora kuzyn iPhone’a 5S).

PLUSY:

  • interfejs dość podobny do pełnej aplikacji, a jednak dobrze dostosowany do ekranu dotykowego
  • w znanych mi przypadkach, arkusze wyglądają dokładnie tak, jak powinny i działają niemal (to słowo może robić jednak kluczową różnicę!) wszystkie funkcje i narzędzia. Co stanowi o kluczowej przewadze nad bezpłatnym Numbers (które nie radzi sobie nawet z wyborem czcionki Microsoftu czy formatowaniem warunkowym), a także innymi alternatywnymi aplikacjami. Przykład poniżej: Excel vs Numbers.

iOS Excel formatowanie warunkowe

IOS numbers formatowanie warunkowe

MINUSY:

  • brak obsługi makr
  • nie najlepsza optymalizacja i stabilność – otwieranie pliku ok. 20MB zajmuje prawie minutę, przy większych pojawiają się problemy z otwarciem pliku (czasami plik „lubi się” nie otworzyć). Być może najnowsze modele iPada radzą sobie lepiej
  • koszt subskrypcji wersji pełnej – konieczny jest pakiet MS Office 365 – w wersji Personal 69,99$ rocznie (choć na Allegro znajdziemy lepsze oferty), brak możliwości „odblokowania” wyłącznie Excela
  • okrojone do niemal zera możliwości edytowania w bezpłatnej wersji. Przykład: tabela przestawna. Filtrowanie jeszcze działa, ale jakakolwiek modyfikacja już nie (co może skłonić do wyboru wersji komercyjnej)

Podsumowanie jest krótkie.

Czy warto płacić za subskrypcję Office 365 dla samego faktu posiadania nie do końca pełnego Excela na iPadzie? Chyba nie.

Czy tablet zastąpi komputer w obsłudze „poważniejszych” arkuszy kalkulacyjnych? To oczywiste, że nie.

Czy Microsoft Excel for iOS to najlepsze mobilne narzędzie do arkuszy kalkulacyjnych? Raczej tak 🙂

Filtrowanie danych w poziomie – jak zrobić w Excelu?

Choć Excel nie jest oprogramowaniem bazodanowym, lecz arkuszem kalkulacyjnym, wiele narzędzi, jak filtrowanie czy tabele przestawne działa poprawnie tylko wtedy, gdy wprowadzone dane mają postać przypominającą tabelę w bazie danych. W pierwszym wierszu musimy określić, jakie dane znajdują się w danej kolumnie, a w kolejnych wierszach wprowadzamy kolejne dane.

Jak ominąć to ograniczenie i stworzyć tabelkę łatwą w odbiorze nawet dla ludzi, dla których Excel jest „czarną magią”, a którą możemy filtrować zarówno w pionie, jak i w poziomie? W tym wpisie pokażę, w jaki sposób uzyskałem taki efekt:

Tabela ukrywanie kolumn

Tabela - północ

Oprócz wspomnianego filtrowania (możemy wybrać z listy rozwijalnej jeden region, dla którego dane chcemy zobaczyć lub wszystkie regiony), działa również funkcja przypominająca sumy częściowe, obliczająca sumę w poszczególnych wierszach dla widocznych kolumn.

Na początek pokażę obszary, w którym nie znajdują się żadne formuły, czyli dane są najzwyczajniej w świecie wprowadzone ręcznie:2 obszary wypelniane recznie

Pracę „koncepcyjną” rozpocząłem od przygotowania makra odpowiedzialnego za ukrywanie i pokazywanie wybranych kolumn. Działanie w skrócie polega na tym, że makro „sprawdza” zawartość komórki A2 i ukrywa komórki, których nazwa nie znalazła się w komórce A2. Jeżeli chcemy pozostawić widoczne wszystkie kolumny, w komórce A2 powinien znaleźć się tekst „Wszystkie regiony”. Wówczas wszystkie kolumny zostaną odkryte.

Sub PokazUkryjKolumny()

Dim liczkol As Integer
For liczkol = 2 To 8
If Cells(4, liczkol) = Cells(2, 1) Or Cells(2, 1) = „Wszystkie regiony” Then
Columns(liczkol).Select
Selection.EntireColumn.Hidden = False
Else
Columns(liczkol).Select
Selection.EntireColumn.Hidden = True
End If
Next liczkol
Cells(2, 1).Select
End Sub

Ale co zrobić, by każdorazowo nie wypełniać komórki A2? Ja wybrałem sposób chyba najczytelniejszy dla końcowego odbiorcy – mianowicie wstawiłem pole kombi. Możemy ją dodać poprzez opcję we wstążce „Deweloper”.3 pole kombi

4 słownikPole kombi „czerpie” dane ze słownika znajdującego się w osobnym arkuszu i podaje numer wybranej pozycji w komórce B2. By tak się stało, należy najpierw zdefiniować słownik, w którym będą oznaczone nasze regiony (patrz obok).
Następnie przechodzimy prawym przyciskiem myszy do
właściwości pola kombi, zaznaczamy zakres słownika oraz wskazujemy komórkę, w której pole kombi będzie przechowywać numer kolejny wybranego regionu.

5 właściwości pola kombi

Ostatnim krokiem jest wprowadzenie w komórce A2 funkcji, która będzie odszukiwać tekst w słowniku wg numeru ze słownika:

=WYSZUKAJ.PIONOWO(B2;SŁOWNIK!A:B;2;0)

Teraz wystarczy już tylko kliknąć prawym przyciskiem myszy na pole kombi i przypisać temu polu nasze wcześniej zdefiniowane makro. Funkcja ukrywania kolumn powinna zadziałać.

Pozostaje nam tylko dopisać funkcję sumującą dane liczbowe wskazane w odkrytych kolumnach. Oto moja propozycja:

=JEŻELI($A$2=”Wszystkie regiony”;SUMA(B6:H6);SUMA.JEŻELI($B$4:$H$4;$A$2;B6:H6))

Po raz kolejny zostaje zweryfikowana zawartość komórki A2. Jeżeli wybrane zostały wszystkie regiony, podsumowanie obejmuje wszystkie dane w danym wierszu. Jeżeli nie, dzięki użyciu funkcji SUMA.JEŻELI zostaną podsumowane tylko te kolumny, dla których został wskazany w wierszu 4. region północny lub południowy.

Dane w kolumnach możemy podsumować za pomocą podstawowych funkcji SUMA lub SUMY.CZĘŚCIOWE, w zależności od tego, czy chcemy filtrować dane w pionie za pomocą standardowej funkcji Excela, czy też nie.

Budżet w Excelu – co robić, by się nie zniechęcić?

Do usystematyzowania budżetu domowego miałem kilka podejść. Jednak dopiero od początku 2015 roku udaje mi się prowadzić go sumiennie, pomimo, iż jego zapisywanie to jedno z moich postanowień noworocznych, o których się zapomina zazwyczaj już w Trzech Króli. Przedstawię kilka banalnych przesłanek (nie związanym z Excelem), dzięki którym osiągnąłem sukces:

1. Powtórzę za chyba większością poradników. Reguła KISS, czyli prostota, głupcze! Codzienne notowanie realizacji budżetu domowego nie powinno zająć dłużej niż trzy minuty, w innym przypadku po kilku dniach się zniechęcimy. Nie warto tworzyć skomplikowanych analityk, jeżeli nie jesteśmy pewni celu, jakiemu mają one przyświecać.

2. Dobranie takich kategorii, by z prowadzonego budżetu dało się wyciągnąć wnioski i zastosować je w praktyce. Ja zrealizowałem to poprzez podział każdego wydatku na kategorię, wskazującą ogólnie, jaki jest przedmiot wydatku oraz podkategorię, na podstawie której możemy wyciągać wnioski co do zarządzania daną kategorią wydatków. Brzmi tajemniczo, ale dla pokazania idei podam przykład budżetu jedzenia w danym miesiącu. Podzieliłem wydatki na następujące podkategorie:

  • bufet pracowniczy – w moim prywatnym przypadku, kategoria najmniej „zarządzalna”. Z powodu kiepskiej jakościowo oferty, do bufetu chodzę z przymusu na śniadania, gdy zbyt późno wstanę i nie mam czasu zjeść w domu lub zrobić zakupy w sklepie spożywczym oraz na obiad, gdy nie mam alternatywy. Z uwagi na „przymusowy” charakter tego wydatku i mały udział w budżecie, nie ma sensu nim zarządzać.
  • zakupy artykułów spożywczych – tutaj z „zarządzalnością” jest już lepiej. Choć w każdym miesiącu w gruncie rzeczy kupujemy to samo, jest już pole do refleksji i zaoszczędzenia stówki lub dwóch miesięcznie. Bo czy warto robić zakupy „na zapas” w osiedlowym sklepiku? Czy nawet niewielkiej części zakupów nie wyrzucamy? Czy zawsze drożej oznacza lepiej?
  • i wreszcie „jedzenie na mieście”. Tym wydatkiem zarządza się najprościej – łatwo obliczyć „na oko”, ile zaoszczędzimy rezygnując z połowy wizyt w modnych barach czy restauracjach.

3. Nie traktować oszczędzania pieniędzy jako celu „samego w sobie” prowadzenia budżetu, bo to w domyśle oznacza obniżenie jakości życia, co nas raczej zniechęci do systematyczności. Raczej jako efekt uboczny, który w praktyce się i tak się pojawi 🙂

A jaka jest w tym rola Excela?

Do notowania wpływów i wydatków w Excelu wystarcza mi skoroszyt zawierający kilka kolumn (choć i tak poradziłbym sobie z ich mniejszą ilością). A oto one:budżet domowy excel

Pierwsze dwie są oczywiste – data i rodzaj zdarzenia. Wpływy i wydatki ewidencjonuję w kolumnach WN (wydatki) i MA (wpływy). Można by co prawda robić to w jednej kolumnie, a wydatki rozróżnić od wpływów znakiem minus. Jednak dzięki temu rozróżnieniu, po późniejszym podsumowaniu danych w tabeli przestawnej mamy jasny obraz nie tylko „wyniku” miesiąca (czy jesteśmy na plusie, czy na minusie), ale sumy wpływów i wydatków.

Kolejne kolumny to omawiane przeze mnie wyżej kategoriepodkategorie. Ostatnie dane, jakie rejestruję (choć nie są najbardziej niezbędne), to typ płatności (karta, gotówka, czy przelew z rachunku bankowego) oraz miesiąc transakcji, obliczany za pomocą formuły przekształcającej datę na format „rok miesiąc”:

=JEŻELI(A2=””;””;TEKST(A2;”RRRR MM”)), gdzie A2 to komórka z kolumny zawierającej datę transakcji.

Ten ostatni zabieg ten nieco ułatwia życie przy filtrowaniu dużej liczby zdarzeń, nie musimy też korzystać z grupowania w tabeli przestawnej. Aby sprawdzić, jak się przestawiała realizacja wydatków w danym miesiącu w stosunku do poprzednich miesięcy i do zakładanego przez nas budżetu, zaznaczamy całą tabelkę i tworzymy tabelę przestawną. Jak to zrobić, odsyłam do strony Microsoftu.

W przyszłości pomyślę o dodatkowych formularzach i tym podobnych gadżetach. Jednak póki notowanie wpływów i wydatków nie zajmuje mi więcej niż kilka minut dziennie, opisane rozwiązanie jest optymalne (no chyba, że dojdę do ostatniego – milion któregoś wiersza w Excelu 🙂 )

Alternatywne narzędzia do prowadzenia budżetu domowego ze strony banków…

Główny rachunek bankowy mam w ING Banku Śląskim, zaś kartę kredytową w Citibanku. Oba te banki oferują narzędzia do zarządzania finansami osobistymi – w ING nazywa się to „Finansometr”, a w Citibanku „Menedżer wydatków kartą płatniczą”.

Narzędzie Citi jest ubogie. Przypisuje ono na sztywno zakupy dokonane za pomocą karty do kategorii ustalonej na podstawie punktu sprzedaży. Do każdej z kategorii można przypisać budżet. Nie można w żaden sposób edytować przypisania danego zakupu do kategorii, nie mówiąc o dodawaniu własnych nazw. Danych nie można powiązać z wpływami na ROR i wydatkami gotówkowymi.  Brak zatem zarządzalności, o której pisałem w punkcie 2. Zaletą jest prostota – wszystko dzieje się zupełnie automatycznie, a jedynym naszym wkładem pracy jest wprowadzenie budżetów dla poszczególnych kategorii. Tylko w jakim celu, skoro dane te niewiele wniosą w zarządzaniu naszym budżetem domowym?

W ING jest nieco lepiej. Narzędzie obsługuje nie tylko wydatki kartą, ale wszystkie transakcje na naszych rachunkach. Korzystamy z predefiniowanych kategorii. Choć nie możemy dodać nowych rodzajów zdarzeń, to jeżeli uznamy, że system źle ocenił kategorię danej transakcji, możemy wybrać inną z listy. ING prezentuje dane w bardziej „bajerancki” sposób niż Citi, na animowanych wykresach. Jednak znów brakuje zarządzalności, o której pisałem. Ponadto, wszelkie transfery między naszymi rachunkami, nie mające znaczenia z punktu widzenia budżetu wprowadzają niepotrzebny chaos (np. przelewy na rachunek karty kredytowej w obcym banku czy na rachunek maklerski). Nie sposób też panować nad gotówką, która nadal ma się dobrze (przynajmniej w moim portfelu 😉 ).

Podsumowując, dla mnie Excel jest bezkonkurencyjny wobec opisanych narzędzi oferowanych przez banki. Mam bowiem możliwość dowolnego kategoryzowania wpływów i wydatków, nie mam problemów z dodaniem przepływów gotówkowych, których banki nie rejestrują. Można również podzielić jedną transakcję na ograniczoną tylko wyobraźnią ilość części. Zwizualizujmy sobie sytuację, że w Auchan kupujemy mopa i kilka butelek szampana. Jaką kategorię dobrać w systemie bankowym?

Na koniec, kilka wpisów z blogów speców od oszczędzania, które mnie zainspirowały, choć nie w 100% się z nimi zgadzam:

http://jakoszczedzacpieniadze.pl/prosty-budzet-domowy

http://metafinanse.pl/budzet-domowy-narzedzia/

Data w Excelu – co zrobić, by format był prawidłowy?

W pracy z danymi pochodzącymi z zewnątrz często mam problem z prawidłowym formatowaniem danych, które pozwoli na dalszą ich analizę czy obróbkę. W tym wpisie chciałbym poruszyć temat dat. Załóżmy, że z jakiegoś zewnętrznego źródła otrzymujemy arkusz zawierający daty, które z pozoru są prawidłowo sformatowane (jak na poniższym obrazku), ale pojawia się niezidentyfikowany kłopot.

daty1

Po pierwsze sprawdźmy, czy na tych danych można przeprowadzić proste operacje, np. dodawanie i odejmowanie. Sprawdźmy w komórce D13, ile czasu upłynęło od 16 stycznia 2015 roku do dzisiaj.

daty2

daty3Operacje arytmetyczne zatem działają. Ale czy działa wyszukiwanie, tak istotne przy analizie dużej ilości danych? Spróbujmy sprawdzić, jaką wartość miał dokument z datą 7 stycznia 2015 r. za pomocą funkcji WYSZUKAJ.PIONOWO, co przedstawiłem poniżej.

 

daty4

daty5Nie znaleziono wiersza zawierającego taką datę. Można jednak zauważyć, że wybrany jest format liczb „Ogólne”. Zmieńmy zatem formatowanie komórek zawierających daty na „Data krótka” (Wstążka -> Narzędzia główne -> Liczba). Nic się nie zmieniło, nadal pojawia się błąd.

Z pomocą przychodzi narzędzie Tekst jako kolumny, które z opisu nie wydaje się mieć wiele wspólnego z formatowaniem dat, gdyż służy do rozdzielania danych zawartych w jednej kolumnie na kilka kolumn. Zaznaczamy kolumnę zawierającą daty, wybieramy narzędzie (Wstążka -> Dane -> Tekst jako kolumny) i przeklikujemy. Tak naprawdę, wybór niestandardowych opcji nie jest konieczny.

daty6 daty7 daty8 daty9 daty10

Et voila, funkcja WYSZUKAJ.PIONOWO zaczęła działać.

Wykres kolumnowy i różne skale – co zrobić, żeby „wyglądał”?

Choć osobiście, z uwagi na czytelność i intuicyjność nie jestem zwolennikiem tworzenia wykresów złożonych z osiami pomocniczymi, prezentujących serie danych wyrażone w różnych jednostkach (na przykład procenty i złotówki), czasami zachodzi taka potrzeba i trudno powiedzieć, że się nie da 🙂

Dane źródłoweZałóżmy, że chcemy zaprezentować na jednym wykresie kolumnowym dane, których dwie serie różnią się o dwa rzędy wielkości. Mój przykład przedstawiłem na obrazku obok. Intuicyjna ścieżka postępowania jest następująca: zaznaczamy dane, klikamy na wstążce WSTAWIANIE -> Polecane wykresy.i próbujemy dobrać najodpowiedniejszy typ wykresu. W moim przypadku to wykres Kombi, niestandardowy złożony, z obiema seriami typu Kolumnowy grupowany.

Wybór typu wykresu

Jak widać na podglądzie, napotkałem problem polegający na nakładaniu się kolumn z danymi za dany rok zawartymi w obydwu seriach. Mój pomysł na „rozdzielenie” kolumn to spreparowanie danych źródłowych, by dla każdego roku wartość z każdej serii znajdowała się w odrębnym wierszu. Zabieg ten wymusi wyświetlenie się kolumn zawierających wartości z poszczególnych lat obok siebie.

 

Dane źródłowe po spreparowaniuDane powinny prezentować się jak na obrazku. Gdy ponownie spróbujemy przygotować wykres – już na bazie zmodyfikowanej tabeli i otworzymy okno Wstawianie wykresu lub Zmienianie typu wykresu, okaże się, że konieczne są prace kosmetyczne (patrz podgląd na obrazku poniżej). Dostosowania wymagają:

  • szerokości kolumn (klikamy prawym przyciskiem na kolumnie i wybieramy Formatuj serię danych…),
  • granice i jednostki osi głównej i pomocniczej (klikamy prawym przyciskiem na osi i wybieramy Formatuj oś…),
  • różne inne aspekty wizualne, zależnie od potrzeb
  • last but not least, dostosowanie etykiet danych, czyli w moim przykładzie lat.

Typ wykresu - po spreparowaniu danych

Na obrazku obok widać, że kolejne etykiety lat się po prostu powielają (mamy ciąg: 2006, 2006, 2007, 2007 itd.). Należy zatem wymusić, by dla każdego roku pojawiała się tylko jedna etykieta. W tym celu, klikamy prawym przyciskiem myszy na etykietach lat na gotowym wykresie, wybieramy opcję Formatuj oś…, następnie Opcje osi -> Etykiety. W polu Interwał między etykietami należy wybrać ręcznie ilość serii danych, jakie mamy na wykresie.

Ponieważ etykiety lat zostaną domyślnie rozmieszczone niesymetrycznie (bo skąd Excel ma się „domyślić”, że dane z kolejnych wierszy dotyczą tych samych lat, skoro wcześniej go „oszukaliśmy” 🙂 ), trzeba ręcznie spreparować lata, by rozmieściły się symetrycznie. Ja poradziłem sobie w ten sposób, że w tabeli źródłowej, przed kolejnymi latami, dodałem apostrof wskazujący na tekstowy typ danych i wcisnąłem kilka razy spację:

Edycja roku

 

Końcowy efekt prezentuje się następująco:

Gotowy wykres kolumnowy złożony