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ć .
Niedawno tego samego dnia dwóch ekspertów od Excela zamieściło filmy, w których znalazło się również rozwiązanie mojego problemu za pomocą Power Query.
Więc jeśli masz taki sam problem jak ja zobacz jak go możesz rozwiązać za pomocą PowerQuery (jeśli nie możesz zainstalować u siebie tego dodatku do Excela zobacz porada 281, gdzie opisuję, jak to robię za pomocą formuł.
Pierwszą rzeczą, którą musimy zrobić to zamienić nasz zakres danych na tabelę, ale odznaczamy, że nasza tabela ma nagłówki. Ułatwi nam to później operacje.
Zwróć uwagę, że miesiące były wpisywane w scalonych komórkach, a teraz się rozdzieliły. W odpowiednim kroku szybko to naprawimy. Najpierw musimy wczytać naszą tabelę do Power Query. Ponieważ mam w końcu Excel 2016, to robię to z karty dane (wcześniej musiałem instalować dodatek i korzystać z karty Power Query).
Naszym danym nie jest potrzebna zmiana rodzaju danych, więc możemy ten krok usunąć.
Kolejnym krokiem będzie transponowanie danych.
Następnie możemy wykorzystać pierwszy wiersz danych jako nagłówki.
Kolejny krok to wypełnienie w dół kolumny miesiące, czyli wypełnianie pustych komórek wartościami, które znajdują się nad nimi (w pewnym momencie musi znaleźć się wypełniony wiersz ;))
Następnie musimy zaznaczyć 2 pierwsze kolumny i anulować przestawienie pozostałych kolumn.
Pozostaje jeszcze zmiana nazw kolumn (wystarczy, że klikniesz w nią dwukrotnie), żeby bardziej odpowiadały danym i już możesz je załadować do nowego arkusza Excela.
Dla naszych przykładowych danych powstało 720 wierszy, na podstawie których możesz już bez problemu stworzyć Tabelę Przestawną lub inaczej je analizować.
Dlaczego moja tabela się nie poszerza — porada #194
Kiedy Twoja tabela się nie poszerza to znaczy, że zmieniła się pewna opcja w autokorekcie Excela. Żeby przywrócić ją do poprzedniego stanu musisz wejść do menu Plik — Opcje — zakładka sprawdzanie — przycisk Opcje autokorekty — zakładka Autoformatowanie podczas pisania.
Tam są dwie opcje:
— Dołącz nowe wiersze i kolumny do tabeli
oraz
— Wypełnij formuły w tabelach w celu utworzenia kolumn obliczeniowych.
Pierwsza odpowiada za poszerzanie tabeli, a druga za automatyczne wypełnianie kolumny formułą do niej wpisaną.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić to napisz do mnie. Ja w miarę możliwości odpowiem na Twoje pytanie.
Powyższa formuła jest formułą tablicową i trzeba ją zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter.
Użyte funkcje: MAX MAX.K ILE.WIERSZY ILE.NIEPUSTYCH INDEKS LICZ.WARUNKI WIERSZ LICZBA.KOLUMN MIN.K JEŻELI JEŻELI.BŁĄD
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
a co w sytuacji gdy chcemy, żeby nasze kryterium było dynamiczne?
Potrzebujemy zmodyfikować test logiczny w funkcji JEŻELI $A$5:$A$20=$G$2
tak, żeby przesuwał się po kolumnach tabeli głównej w zależności od kryterium jakie wybierzemy.
wykorzystujemy funkcję PRZESUNIĘCIE do przesuwania się od pierwszej kolumny ($A$5:$A$20).
Drugi parametr (0) mówi nam, że nie chcemy się ruszać z pozycji startowej jeśli chodzi o wiersze.
Trzeci parametr (PODAJ.POZYCJĘ($G$1;Tabela1[#Nagłówki];0)-1) podaje nam o ile kolumn chcemy się przesunąć w zależności od rodzaju kryterium (nagłówka, dla którego ustaliliśmy kryterium).
Po prostu szukamy, go, a właściwie jego pozycji w nagłówkach tabeli. Potrzebujemy tutaj funkcji PODAJ.POZYCJĘ i przeszukiwania dokładnego.
Ważne, że od wyniku funkcji PODAJ.POZYCJĘ potrzebujemy odjęć jedynkę ponieważ, jeśli kryterium jest z 1 kolumny nie chcemy się przesuwać (0 kolumn), jeśli z 2 kolumny to chcemy się przesunąć o 1 kolumnę itd.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
stworzyliśmy 2 listy rozwijane, przy czym wartości na drugiej zależały od tego co zostało wybrane na pierwszej liście. Uzyskaliśmy to dzięki nazwaniu zakresów i odwołaniu za pomocą funkcji ADR.POŚR.
Ale tamto rozwiązanie miało wadę — dopisanie wartości do listy nie powodowało pojawieniem się na liście, ponieważ obszary były ograniczone tylko do już wpisanych wartości.
Można co prawdę Przypisać do nazwy większy obszar, a nawet całą kolumnę, ale sprawia to, że na krótszych listach pojawiają się na końcu puste pola, które ciągną się tak długo, aż dorównają ilościom pozycjom na najdłuższej liście.
Kolejnym rozwiązaniem byłoby stworzenie tabeli na podanym obszarze nazw. I tu mogą pojawić się 2 niedogodności — albo część nazw nie będzie się pokrywać z wysokościom kolumn w tabeli, sprawi to, że te nazwy nie będą uaktualniane,, albo stworzymy nazwy, które się odwołują do całych kolumn, wtedy znów będą pojawiać się puste pola na końcu, ale obszary będą dopasowywać się automatycznie tak jak rośnie tabela.
Najelegantszym rozwiązanie byłoby dla każdej nazwy/obszaru stworzyć oddzielną tabelę. Dzięki temu nie tylko zakresy będą dynamiczne rozszerzać się wraz z odpowiednimi tabelami, ale również unikniemy pustych wierszy na końcu list.
Można też usuną formatowanie wynikające z tabeli, żeby lista wyglądała tak jak sobie tego życzysz.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.
