Często dostaje dane, które są zapisany w wygodny dla człowieka sposób, ale bardzo niewygodny dla Excela.
Na podstawie danych nie da się stworzyć Tabeli Przestawnej i innych analiz danych dostępnych w Excelu. Trzeba je najpierw przekształcić . W poradzie 280 opisałem jak to można zrobić za pomocą Power Query, ale ponieważ nie wszyscy mogą zainstalować ten dodatek poniżej znajdziesz opisany sposób jako możesz to zrobić za pomocą formuł w Excelu. Specjalnie dlatego nagrałem ten film na Excelu 2007, żeby pokazać, że jest to możliwe.
W pierwszej kolejności musimy usunąć scalanie nagłówków, żeby późniejsze formuły były prostsze.
Później musimy zaznaczyć tylko puste komórki z nagłówków. Najprościej to zrobisz najpierw naciskając skrót klawiszowy Ctrl + G, a następnie z okna, które się pojawi wybierzesz przycisk Specjalnie.
W kolejnym oknie, które się pojawi musisz wybrać polecenie Puste i zatwierdzić wybór przyciskiem OK.
Teraz musisz wpisać znak = w komórkę i kliknąć na komórkę obok i zatwierdzić formułę skrótem Ctrl + Enter. Dzięki temu formuła odpowiednio przesunięta zostanie wstawiona we wszystkie puste komórki.
Następnie potrzebujemy skopiować nagłówki i wkleić je jako wartości.
Teraz możemy się zająć pisaniem formuł do tabeli. Zaczniemy od numeru entomologa. Wiemy, że jest ich 20 i muszę się systematycznie powtarzać, żeby dla każdego entomologa (jego numeru) w wierszu znalazł się jeszcze miesiąc i typ owada. Ponieważ mamy 20 entomologów, 3 typu owadów i 12 miesięcy, to w sumie będziemy potrzebować 720 wierszy.
W takim razie jak zapewnimy sobie cykliczne powtarzanie numerów entomologów? W pierwszej kolejności musimy liczyć wiersze (ILE.WIERSZY($A$2:A2)), a następnie wyciągać z nich resztę z dzielenia przez 20 (funkcja MOD), bo tyle w przykładzie mamy numerów.
Musimy tylko dokonać drobnych korekt, żeby numery powtarzały się od 1 do 20.
=MOD(ILE.WIERSZY($A$2:A2)-1;$H$1)+1
Teraz możemy przejść do następnej kolumny, musimy dla każdej serii numerów wstawić miesiąc z kolejnej kolumny, czyli dla pierwszej serii numerów mamy wstawić miesiąc z pierwszej kolumny, dla następnej z drugiej itd.
Będzie nam do tego przydatna funkcja INDEKS, która będzie wskazywała na nagłówki z miesiącami. Będziemy wyciągać zawsze informację z pierwszego wiersza, więc jedynym problemem będzie wybranie odpowiedniej kolumny.
Na szczęście funkcja INDEKS nie patrzy na część dziesiętną liczby, dlatego wystarczy, że odrobinę zmodyfikujemy formułę z numerów entomologów — zamiast wyciągać resztę z dzielenia, będziemy dzielić przez ilość entomologów.
=INDEKS(Dane!$B$3:$AK$3;1;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)
W analogiczny sposób wybieramy typy owadów. Tylko patrzymy na wiersz niżej.
=INDEKS(Dane!$B$4:$AK$4;1;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)
Uff. Została nam ostatnia formuła. Tym razem musimy pobrać dane z tabeli, czyli w naszym przykładzie zmyśloną ilość owadów, które złapał entomolog, w danym miesiącu, danego typu.
Znów pomoże nam funkcja INDEKS, ale będziemy musieli wybierać różne numery wiersza, ale to już mamy w pierwszej kolumnie (numery entomologów), a numer kolumny wybieramy tak jak wcześniej, czyli nasza formuła przybierze postać:
=INDEKS(Dane!$B$5:$AK$24;A2;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)
Nareszcie konie pracy 😀 Uważam, że to jest skomplikowany proces i jeśli tylko możesz skorzystać z dodatku Power Query zobacz poradę 280 gdzie jest opisany proces konwersji danych za pomocą właśnie Power Query.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
