Mamy problem, gdyż nasze dane rozdzielone są na wiele kolumn przechowujących te same informacje.
Takie dane bardzo trudno analizować, więc potrzebujemy je złączyć w pojedyncze kolumn. Wykorzystamy rozwiązanie Oza du Soleil, które opiera się o użycie dodatku do PowerQuery do Excela.
https://www.youtube.com/watch?v=hW9eVTbRbgI
Najpierw będziemy musieli zrobić pewną operację na danych – będziemy musieli połączyć informacje z dwóch kolumn do jednej. Tylko potrzebujemy dodać pomiędzy nimi ogranicznik, po którym później będziemy mogli podzielić je ponownie. Ważne, żebyśmy byli pewni, że ten ogranicznik nie występuje w naszych danych. Mogą to być np.: 2 symbole ^^
=A3&"^^"&B3
Następnie oprócz komórki z formułą zaznaczamy pustą komórkę po prawej stronie i przeciągamy je do końca danych. Następnie tak stworzony wiersz kopiujemy w dół do końca wszystkich danych. Musimy jeszcze dodać (skopiować) do naszych danych nagłówek, ponieważ dane do PowerQuery w Excelu należy pobierać z tabeli Excela.
Teraz możemy pobrać dane do zapytania PowerQuery. Korzystając z odpowiedniego polecenia z karty dodatku lub z karty Dane od Excela 2016.
Po wczytaniu danych do PowerQuery musimy wykonać następujące kroki:
1. Musimy przenieść nagłówki do pierwszego wiersza
2. Transponować dane
3. Odfiltrować wszystkie wiersze, które zawierają słowo Ilość (w naszym przykładzie), czyli te wiersze, które dla których kolumny były puste. Ważne, że nie możemy po prostu odznaczyć ich w filtrze, tylko musimy skorzystać z filtrów tekstowych i wybrać kryterium nie zawiera.
Ważne, że PowerQuery zwraca uwagę na wielkość liter, więc tutaj musimy odpowiednio wypełnić pole zgodnie z zapisanym przez nas tekstem.
4. Anulujemy przestawienie wszystkich kolumn poza pierwszą
5. Usuwamy środkową kolumnę, bo jej nie potrzebujemy
6. Dzielimy kolumnę po użytym przez nas ograniczniku
7. Odfiltrowujemy po jednej z kolumn wynikowych wiersze puste lub z wartością null. Możemy tak teraz postąpić, gdyż PowerQuery zapisze to jako filtr wartości różne od pustych (null).
8. Na koniec zmieniamy nazwy kolumn i wczytujemy dane do Excela.
Trzeba zwrócić uwagę, że wartości w pierwszej kolumnie nie są takie jakbyśmy chcieli ponieważ w pierwszym kroku skopiowaliśmy nagłówki bezpośrednio skopiowane z danych, które przekształcamy, a one nie przechowują dodatkowej informacji o produktach. W tym przykładzie powinniśmy zamiast nich wstawić jako nagłówki dane naszych sprzedawców, a następnie odświeżyć tabelę wynikową zapytania PowerQuery.
Niestety spowoduje to błąd :(, ale klikając dwukrotnie w oknie zapytań wrócimy do edycji zapytania i będziemy mogli je poprawić. Przy okazji PowerQuery podpowiada nam, gdzie wystąpił błąd.
Błędny okazuje się drugi krok ponieważ w pobieranych danych nie istnieje już kolumna Produkt itp. Najprostszym rozwiązaniem jest tutaj usunięcie tego kroku ponieważ mamy proste dane i jego usunięcie nie wpłynie negatywnie na wynik końcowy.
Teraz wystarczy ponownie załadować dane zapytania i w naszej tabeli wynikowej zapytania PowerQuery mamy wszystkie istotne dla nas dane.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP