Podział kolumny po ilości znaków — PowerQuery #3
Pobrałeś dane ze źródła i jest z nimi poważny problem – są złączone razem. Nie ma w nich żadnego ogranicznika.
Ten ciąg potrzebujesz podzielić po ilości znaków. W Excelu najszybszym rozwiązaniem byłoby skorzystanie z polecenia Tekst jako kolumny z karty Dane (innym rozwiązaniem byłoby korzystanie z funkcji FRAGMENT.TEKSTU).
Czyli musimy zaznaczyć kolumnę danych, a następnie w pierwszym kroku polecenia wybrać opcję podziału po stałem szerokości (po ilości znaków).
W drugim kroku musimy zaznaczyć miejsca, w których chcemy dokonać podziału (kliknąć myszką po odpowiedniej ilości znaków, żeby wstawił się linie podziału na kolumny. Podziału na kolumny chcemy dokonać, najpierw po 10, później po 2, 4, 6, 3 i 3 znakach).
W trzecim ostatnim kroku możemy wybrać jak mają być sformatowane poszczególne kolumny. W tym przykładzie dla wszystkich kolumn wystarczający jest format ogólny — poprawnie zinterpretuje wszystkie wartości – nawet datę z pierwszej kolumny. Będziemy musieli tylko zmienić miejsce docelowe. Zamiast do kolumny A2 chcemy, żeby dane zaczęły się wpisywać od komórki B2.
Jeśli mamy taki podział w Excelu i wystarczy, że dokonamy go raz to sprawa jest jasna. Ale jeśli dane pochodzą z innego źródła (np.: pliku tekstowego, .csv, czy bazy danych) to chcielibyśmy rozwiązanie bardziej dynamiczne. Rozwiązaniem jest dodatek PowerQuery (dostępny od Excela 2010).
Dla ułatwienia przykładowe dane przechowujemy w tabeli Excela, żeby móc skorzystać polecenia PowerQuery – Z tabeli (w Excelu 2016 znajduje się ono na karcie dane, wcześniej na osobnej karcie dodatku PowerQuery).
Trafimy do edytora zapytań (PowerQuery), gdzie potrzebujemy rozwinąć polecenie Podziel kolumny, by odnaleźć możliwość dzielenia po ilości znaków. (Możemy usunąć domyślnie dodany krok Zmieniono typ, gdyż nic nam w tym momencie nie daje).
Tutaj niestety nie jest tak prosto, bo podziału możemy dokonać po ilości znaków z lewej bądź prawej strony, albo po powtarzającej się ilości znaków, ale nie ma takiej możliwości jak w polecenie Tekst jako kolumna, gdzie sami klikaliśmy w miejsca podziału.
Na razie ustawmy liczbę znaków na 10 i podział z lewej strony.
Prawdopodobnie znowu dodał się krok Zmieniono typ, ale tym razem go zostawiamy, żeby daty były poprawnie interpretowane. Dla nas jednak jest ważniejszy wcześniejszy krok Podzielono kolumnę według położenia. Klikamy na niego i patrzymy na formułę, która pokazuje się w pasku formuły:
= Table.SplitColumn(Źródło,"Połączona kolumna",Splitter.SplitTextByPositions({0, 10}, false),{"Połączona kolumna.1", "Połączona kolumna.2"})
Jeśli nie widzisz paska formuły przejdź na kartę Widok i zaznacz pole wyboru (checkbox) Pasek formuły.
Jest to formuła w języku M (języku PowerQUery). Prawdopodobnie w większości jest dla Ciebie mało zrozumiała, ale wystarczy, że skupimy się na jej fragmentach {0, 10} oraz {"Połączona kolumna.1", "Połączona kolumna.2"} . Są to odpowiednio ilości znaków, po których następuje podział po kolumnach (pierwsze zero jest istotne, gdyż odpowiada za pierwszą kolumnę, że zaczyna się ona od początku).
Czego nie widać na pierwszy rzut oka jest to, że liczba znaków jest zawsze od początku tekstu. Czyli jeśli chcemy dokonać podziału najpierw po 10, a potem po 2 znakach, to argument wpisany w formule musimy mieć odpowiednio postać {0, 10, 12, 16, 22, 25, 28}, a nie {0, 10, 2}. Oznacza to podział na trzy kolumny, który w drugim omawianym przez na argumencie musimy nadać nazwy. Jeśli napiszesz mniej nazw kolumn niż wynika to z podziału po ilości znaków, to dalsze kolumny się nie wyświetlą. Dla uproszczenia przykładu kolumny nazywamy "k1", "k2", itd.
Mamy mało kolumn, więc jesteśmy w stanie sami policzyć sobie kolejne ilości znaków, ale poniżej znajdziesz sposób na ułatwienie tego procesu w Excelu. Czyli funkcja PowerQuery dzieląca tekst tak jak wcześniej polecenie Tekst jako kolumny powinna mieć postać:
= Table.SplitColumn(Źródło,"Połączona kolumna",Splitter.SplitTextByPositions({0, 10, 12, 16, 22, 25, 28}, false),{"k1", "k2", "k3", "k4", "k5", "k6"})
Niestety taka zmiana formuły spowoduje błąd w kolejnym kroku (domyślnej zmianie typów danych), gdyż zmieniliśmy nazwy kolumny. Najlepiej go usunąć i samemu odpowiednio pozmieniać typy kolumn korzystając np.: z polecenia z karty Narzędzia główne.
Mamy odpowiedni podział kolumn, więc możemy je załadować do Excela. Pamiętaj przy tym, żeby rozwinąć polecenie Zamknij i załaduj by zobaczyć możliwość załadowania do, zamiast domyślnego ładowania danych zapytania do nowego arkusza.
Wynik zapytania PowerQuery jest identyczny jak wynik polecenia Tekst jako kolumny:
Jest jednak duża różnica, ponieważ tabelę wynikową z PowerQuery można odświeżyć klikając na nią np: prawym przyciskiem myszy i wybierają polecenie Odśwież z podręcznego menu. Można nawet korzystając z polecenia Połączenia na karcie Dane ustawić, żeby zapytanie odświeżało się automatycznie przy otwieraniu pliku.
Jak już wspominałem w tym przykładzie jest mało kolumn, ale w pracy miałem sytuację wielokrotnie, że liczba kolumn wynosiła kilkadziesiąt i łatwo byłoby się pomylić przy liczeniu. Dlatego korzystałem z pomocy Excela przy tworzeniu ciągów liczbowy do argumentów funkcji PowerQuery.
Załóżmy sytuację, że mamy podane liczby co ile musi nastąpić podział kolumny, czyli w tym przykładzie 10, 2, 4, 6, 3, 3. Musimy pamiętać, że PowerQuery potrzebuje jeszcze początkowego 0 oraz, że ciąg ma być ilością znaków od początku tekstu, a długości poszczególnych kolumn. Dlatego musimy odpowiednio zsumować wartości:
=SUMA(I$1:I1)
Pierwsze odwołanie jest zablokowane, żeby się nie ruszało, a drugie jest odblokowane, żeby przeciągając kolumnę w dół odpowiednio poszerzał się zakres, po którym sumujemy.
Jeśli w Twojej wersji Excela jest dostępna funkcja POŁĄCZ.TEKSTY, to wystarczy, że z niej skorzystasz by połączyć wszystkie liczby:
=POŁĄCZ.TEKSTY(", ";;J1:J7)
Jeśli jej nie masz, to albo wstawiasz dużo argumentów do funkcji ZŁĄCZ.TEKSTY, albo w każdym kolejnym wierszy dołączasz przecinek i kolejną liczbę do komórki powyżej:
=K2&", "&J3
Pierwsze 0 wpisujemy ręcznie.
Wtedy końcowy tekst możemy skopiować i wkleić do zapytania PowerQuery.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
