Macierz… Brzmi jak powrót do czasów liceum i przywołuje w pamięci naukę do matury. W artykule jednak (pomimo że to maj 2019) będzie dużo praktyczniej o macierzach oraz ich zastosowaniach, a także dlaczego warto przenieść się z mnożeniem macierzy do Power Query zamiast pozostać w Excelu. Oraz najważniejsze: jak wymnożyć macierze w Power Query.
Jednak najpierw odrobina teorii … o macierzach
Macierz to prostokątna tablica zawierająca liczby, symbole i wyrażenia, które ułożone są w wiersze oraz kolumny. Służą do zapisywania dużych zbiorów danych, które są we wzajemnej relacji. Szczególnie ważną rolę pełnią macierze w informatyce oraz statystyce, gdzie często występują ogromne zbiory danych.
Gdzie można wykorzystać macierze?
Są wykorzystywane do rozwiązywania skomplikowanych układów równań liniowych. Bardzo ciekawym jest wykorzystanie do modelowania ekranu komputera, który składa się z pikseli ułożonych w wiersze oraz kolumny.
Macierze wykorzystuje oprogramowanie graficzne takie jak Adobe Photoshop, do przetwarzania transformacji linowych w celu renderowania obrazów.
W fizyce macierze znajdują zastosowanie w badaniach obwodów elektrycznych, mechanice kwantowej i optyce. Macierze wykorzystywane są do dokonywania dokładanych obliczeń pogrzebnych w pracy mechaniki kwantowej.
Macierze wykorzystywane są również do kodowania i szyfrowania wiadomości.
W robotyce i automatyce macierze tworzą podstawowe ruchy robota.
Macierze to również struktury do przechowywania danych śledzenia informacji o użytkownikach, służą do kompresji informacji elektronicznych, czy przechowywania danych biometrycznych, ale także informacji o populacji.
Transformacje macierzy, które dają doskonale skalibrowane obliczenia, wykorzystuje się w sieciach elektronicznych, samolotach, statkach kosmicznych oraz inżynierii chemicznej.
Dla mnie osobiście najbardziej interesującym zastosowaniem macierzy są wyszukiwarki internetowe np. Google. W tym konkretnym przypadku zastosowany jest algorytm PageRank (PR), który wykorzystuje macierze oraz ich wartości i wektory własne. Wyszukiwarka przyporządkowuje stronom internetowym pewien wskaźnik, którego początkiem był PageRank. Im jest o wyższy tym strona wyżej pojawia się w wyszukiwarce. Wartość PR powstaje w oparciu o jakość i ilość linków prowadzących do danej strony www.
Oczywiście nie są to wszystkie zastosowania, ponieważ macierze dotykają wielu dziedzin, a wymienione tu to tylko przykłady.
Na czym polega mnożenie macierzy?
Na mnożeniu wierszy pierwszej macierzy przez kolumny drugiej macierzy. By wymnożyć macierze musi być spełniony warunek : pierwsza macierz ma tyle kolumn ile druga wierszy.
Dlaczego mnożenie macierzy w Power Query?
W Excelu do mnożenia macierzy można wykorzystać wbudowaną funkcję MACIERZ.ILOCZYN, czyli podać zakresy dwóch tablic, które między siebie miałyby być wymnożone. Niestety jeżeli zmienią się wymiary tablic, wówczas formułę należy zaktualizować oraz ponownie przeliczyć przy pomocy Ctrl + Shift + Enter (ponieważ jest to formuła tablicowa).
Jednak operowanie na Power Query pozwala zapomnieć o konieczności aktualizacji działania procedury, gdy zmieni się zakres poszczególnych macierzy.
Mnożenie macierzy w Power Query
Potrzebuję dwóch macierzy A i B jako zdefiniowane zakresy w Excel. Zacznę od pobrania i modyfikacji pierwszej macierzy do Power Query: Dane > Pobieranie i przekształcanie > Z tabeli
Kolejne transformacje tabeli doprowadzą ją do postaci trzykolumnowej: wiersz, kolumna oraz wartość.
Wybieram Dodaj kolumnę > Dodaj kolumnę indeksu > od 1.
Zostanie dodana nowa kolumna z numerem wiersza. Wybieram ją i prawym przyciskiem myszy rozwijam menu podręczne z którego wybieram Anuluj przestawienie innych kolumn.
Operacja ta powoduje przekształcenie macierzy w wyglądu zbliżony do oczekiwanego. Należ jeszcze oczyścić kolumnę Atrybut do form liczbowej.
Kolejne kroki do wykonania to :
- Zamiana w drugiej kolumnie słowa ColumnX na X : PP myszy > zamień wartości > w polu Wartość do znalezienia wpisuję Column, Zamień na pozostawiam puste
- Po powyższej operacji kolumna ma format tekstowy, więc zmieniam go na liczbowy : PP myszy > Zmień typ > Liczba całkowita
- Zmieniam nazw dwóch pierwszych kolumn na: Wiersz, Kolumna co też kończy całą operację zmian na tej tablicy.
Tworzenie funkcji
Na początku zmieniam nazwę zapytania na PrzygotowanieMacierzy. Następnie zmieniam ciąg wykonanych operacji na funkcję. Wchodzę do Edytora zaawansowanego i dodaję w pierwszej linii wyrażenie (TableName) =>
Usuwam cały wiersz „Zmieniono typ” , który jest zbędny (pozostawienie go spowoduje, że funkcja nie będzie uniwersalna, ponieważ zawiera odwołania do nazw poszczególnych kolumn). W kolejnym wierszu #”Zmieniono typ” zmieniam na #”Źródło”(ponieważ wcześniej usunąłem krok Zmieniono typ).
W miejscu deklaracji źródła danych (Excel.CurrentWorkbook) zmieniam nazwę „MacierzA” na TableName > Gotowe. Poniżej widok finalnego polecenia.
Dzięki wykonanym zmianom po prawej stronie, w oknie nawigatora jest widoczna funkcja. Po wybraniu jej z listy pojawia się okno do wprowadzenia parametru dla funkcji (wcześniej wprowadzonego TableName). Wprowadzam nazwę zakresu, który ma być przekształcony w wyniku działania funkcji : MacierzA > Wywołaj.
Dzięki funkcji powstaje nowe zapytanie, zmieniam jego nazwę na MacierzA. Takie same kroki wykonuję dla drugiej macierzy, zmieniam nazwę zapytania na MacierzB.
Tworzenie tabeli połączonej
W następnej kolejności utworzę tabelę połączoną gdzie dla kolejnych kolumn Macierzy A dopasuję wiersze z Macierzy B. Zaznaczam na liście zapytań MacierzA > Narzędzia główne >Połącz > Scal zapytania > Scal zapytania jako nowe. Z Macierzy A zaznaczam Kolumnę zaś z MacierzyB zaznaczam Wiersz. Jako rodzaj połączenia tabel wybieram Lewe zewnętrze > OK.
W wyniku operacji pojawia się nowa kolumna, której nazwę zmieniam na B.
Klikam a podwójną strzałkę w kolumnie B i z listy wyboru oznaczam Wiersz, pozostawiam zaznaczoną opcję Użyj oryginalnej nazwy kolumny jako prefiksu (co pozwoli mi później odnaleźć się w danych).
Powstaje tablica zawierająca wszystkie kombinacje Macierzy A oraz B, które niezbędne są do wymnożenie między siebie macierzy. Znajdują się one w kolumnie Wartość oraz B.Wartość. By wymnożyć macierze musimy wymnożyć między siebie kolumny Wartość oraz B.Wartość. W tym celu stawiam nową kolumnę Dodaj kolumnę > Kolumna niestandardowa.
Nazywam ją AB oraz wprowadzam formułę wymnożenia kolumn między siebie.
Wynikiem mnożenia macierzy jest macierz o ilości wiersz z Macierz A oraz ilości kolumn z Macierzy B. Zaznaczam kolumnę Wiersz oraz B.Kolumna Przekształć > Grupowanie według
W okna wprowadzam wybory jak powyżej, grupuję dane według numery wierszy oraz kolumn, w nowej kolumnie. Cała operacja powoduje ogromne zmniejszenie się wielkości tabeli.
Dane nie są jednak jeszcze w ostatecznej, oczekiwanej formie. Zaznaczam kolumnę B.Kolumna Przekształć > Dowolna kolumna > Kolumna przestawna
Wskazuję która kolumna jest kolumną z wartościami oraz jaka operacja ma być wykonana na danych, tym przypadku Suma.
Na koniec powstaje mała macierz o spodziewanych rozmiarach.
Pobierz plik
Jeżeli chcesz sam przejść wszystkie kroki samodzielnie, pobierz plik Mnożenie macierzy w Power Query.
Źródło pomysłu na artykuł
Autor pomysłu: Bill Szysz
