0
0 Produkty w koszyku

No products in the cart.

Często dostaje dane, które są zapisany w wygodny dla człowieka sposób, ale bardzo niewygodny dla Excela.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-01

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.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-02

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.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-03

W kolejnym oknie, które się pojawi musisz wybrać polecenie Puste i zatwierdzić wybór przyciskiem OK.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-04

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.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-05

Następnie potrzebujemy skopiować nagłówki i wkleić je jako wartości.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-06

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

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-07

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)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-08

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)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-09

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)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-10

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