Naprawa błędnych dat w Excelu
Excel
Zarządzanie projektami i produktywność
Spis treści:
Przyczyny błędnych dat
Naprawa błędnych dat w Excelu to problem, z którym prędzej czy później spotka się każdy użytkownik arkuszy kalkulacyjnych. Czasem winny jest plik CSV, w którym daty zapisane są w innym formacie niż oczekiwany. Innym razem problem pojawia się po zmianie ustawień regionalnych, gdy dzień i miesiąc „zamieniają się miejscami” (Rys. 1) albo Excel przestaje rozpoznawać wartość jako datę. Zdarza się też, że daty są przechowywane jako zwykły tekst, co uniemożliwia ich sortowanie, filtrowanie czy używanie w obliczeniach. W tym artykule pokażę, jak rozpoznać źródło problemu i skutecznie naprawić błędne daty – krok po kroku, wykorzystując zarówno wbudowane narzędzia Excela, jak i sprytne triki ułatwiające pracę.
Możliwe do zastosowania rozwiązania
Czasami, gdy w arkuszu pojawiają się błędne daty, wystarczy wykonać prostą zamianę znaków – na przykład zamienić „/” na „.” (kropkę) – lub ustawić odpowiedni format komórki. Niestety, takie podejście nie zawsze działa, szczególnie w przypadku przestawionych dni i miesięcy albo gdy Excel traktuje daty jako zwykły tekst. W takich sytuacjach potrzebne są skuteczniejsze narzędzia.
Excel oferuje kilka efektywnych sposobów na uporządkowanie i poprawienie niepoprawnych dat – w zależności od tego, jakiego rodzaju problem napotkaliśmy. W tym artykule skupimy się na trzech uniwersalnych narzędziach, które sprawdzą się w większości przypadków:
- Tekst jako kolumny – funkcja dostępna na karcie Dane, która pozwala w prosty sposób rozdzielić dane według wybranego separatora (np. kropki, ukośnika, spacji) oraz wskazać prawidłowy format daty. To idealne rozwiązanie przy imporcie z plików CSV, gdzie kolejność elementów daty różni się od ustawień regionalnych Excela.
- Wypełnianie błyskawiczne – narzędzie, które „uczy się” na podstawie wpisanego przez nas wzorca. Wystarczy wpisać jedną lub dwie poprawne daty obok błędnych wartości, a Excel samodzielnie uzupełni pozostałe wiersze, przekształcając dane do właściwego formatu. Sprawdza się zwłaszcza w sytuacjach, gdy daty są zapisane w nietypowy sposób, np. z dodatkowymi znakami lub skrótami nazw miesięcy.
- Power Query – zaawansowane narzędzie do importu i przekształcania danych, które pozwala automatycznie wykrywać i konwertować daty, ustawić kolejność elementów daty, a nawet masowo poprawić błędy w wielu kolumnach naraz. Co ważne, po skonfigurowaniu Power Query umożliwia powtarzanie całego procesu jednym kliknięciem, co jest dużym ułatwieniem przy pracy z cyklicznie aktualizowanymi plikami.
Opis konkretnych metod
Tekst jako kolumny
Narzędzie Tekst jako kolumny w tym przypadku posłuży do naprawy błędnych dat przedstawionych w pierwszej tabeli (Rys. 1). Aby rozwiązać problem, należy wykonać następujące kroki:
- Zaznacz komórki z błędnymi datami (np. C2:C24).
- Na karcie Dane uruchom narzędzie Tekst jako kolumny.
- W kreatorze przejdź od razu do kroku 3 (pozostawiając domyślne ustawienia w dwóch poprzednich krokach).
- W sekcji Format danych w kolumnie wybierz opcję Data, a z rozwijanej listy wskaż odpowiedni układ – w tym przykładzie MDR (miesiąc-dzień-rok), czyli taki, jaki odpowiada aktualnemu zapisowi daty Rys. 2).
- Kliknij Zakończ, aby zatwierdzić zmiany.
Po wykonaniu tych czynności daty zostaną poprawnie rozpoznane i zapisane w formacie zgodnym z ustawieniami Excela. Co ciekawe, mimo swojej nazwy, narzędzie Tekst jako kolumny w tym scenariuszu nie dokonuje podziału danych na osobne kolumny – pełni tu funkcję konwertera, który potrafi naprawić błędne daty. To praktyczna, a przy tym stosunkowo mało znana możliwość tego narzędzia.
Wypełnianie błyskawiczne
Narzędzie Wypełnianie błyskawiczne to uniwersalne rozwiązanie, które potrafi automatycznie wyodrębniać lub przekształcać dane na podstawie podanego wzorca. W tym przypadku wykorzystasz je do naprawy błędnych dat z drugiej tabeli (Rys. 1).
Aby skorygować daty:
- W komórce B2 wpisz datę poprawną, odpowiadającą tej z komórki A2 (np. 04.09.1990).
- W kolejnych wierszach wpisz poprawną wartość dla nietypowego przypadku, np. w komórce B4 wpisz 18.12.1998, jeśli w A4 dzień i miesiąc są zamienione miejscami.
- Ustaw kursor w pustej komórce kolumny z poprawionymi datami (np. B5) i wybierz Narzędzia główne 🠒Wypełnij 🠒 Wypełnianie błyskawiczne (Rys. 3).
- To samo polecenie znajdziesz również na karcie Dane w sekcji Narzędzia danych lub możesz skorzystać ze skrótu klawiszowego [Ctrl+E].
Excel, analizując podany wzorzec, automatycznie uzupełni pozostałe wiersze, przekształcając błędne daty w prawidłowy format. Dzięki temu szybko poprawisz nawet dużą liczbę rekordów bez używania formuł czy dodatkowych narzędzi.
Power Query
Power Query to potężne narzędzie Excela, które pozwala automatyzować pobieranie, czyszczenie i przekształcanie danych. Wymaga ono jednak odrobiny znajomości interfejsu i zasad działania, ale w zamian oferuje dużą elastyczność i możliwość powtarzania całego procesu jednym kliknięciem. W tym przykładzie wykorzystasz Power Query do naprawy błędnych dat.
Aby to zrobić:
- Ustaw się w pierwszej tabeli (Rys. 1).
- Na karcie Dane wybierz Pobierz dane 🠒 Z innych źródeł 🠒 Z tabeli/zakresu (Rys. 4).
- W edytorze Power Query zaznacz kolumnę Data zamówienia i wybierz Narzędzia główne 🠒 Podziel kolumny 🠒 Według ogranicznika.
- Jako ogranicznik wskaż znak „/” i zatwierdź.
- Zaznacz w tej kolejności kolumny: Data zamówienia.2 (dzień), Data zamówienia.1 (miesiąc) i Data zamówienia.3 (rok). Następnie na karcie Przekształć w grupie Kolumna tekstu wybierz Scal kolumny.
- W oknie scalania ustaw separator Niestandardowy i wpisz kropkę (.). Od razu nadaj nowej kolumnie nazwę Data zamówienia i zatwierdź (Rys. 5).
- Obok nazwy nowej kolumny kliknij w ikonę typu danych („ABC”) i wybierz odpowiedni typ daty (Rys. 6).
- Wybierz Plik 🠒 Zamknij i załaduj lub Zamknij i załaduj do. Pierwsza opcja utworzy nowy arkusz z poprawioną tabelą, a druga pozwoli dodatkowo wskazać dokładne miejsce wstawienia wyników.
Dzięki temu błędne daty zostaną przekształcone do poprawnego formatu, a raz przygotowany proces będzie można łatwo powtórzyć dla nowych danych – bez wykonywania wszystkich kroków od początku.
Główna różnica pomiędzy narzędziami 1 i 2 a Power Query polega na tym, że Tekst jako kolumny oraz Wypełnianie błyskawiczne dokonują poprawek bezpośrednio w istniejącym zakresie danych, natomiast Power Query umieszcza wynik swojej pracy w osobnej, nowej tabeli. Dodatkowym atutem Power Query jest ogromna elastyczność – narzędzie to potrafi rozwiązać praktycznie każdy problem związany z przekształcaniem i naprawą danych źródłowych, w tym również błędnych dat.
Podsumowanie metod naprawy błędnych dat w Excelu
1. Tekst jako kolumny
Zalety: szybkie działanie, prosta obsługa, idealne przy imporcie z plików CSV i konwersji formatu daty.
Ograniczenia: działa tylko w miejscu danych; ograniczone możliwości przekształcania; wymaga, aby dane miały jednorodny separator.
2. Wypełnianie błyskawiczne
Zalety: bardzo intuicyjne, nie wymaga znajomości formuł; radzi sobie z nietypowym zapisem dat, dodatkowym tekstem czy skrótami miesięcy.
Ograniczenia: działa w miejscu danych; opiera się na wzorcu – przy dużej zmienności danych może popełniać błędy.
3. Power Query
Zalety: ogromna elastyczność, możliwość tworzenia zautomatyzowanych procesów, działa także na dużych i zróżnicowanych zbiorach danych.
Ograniczenia: wymaga podstawowej znajomości narzędzia; wyniki są umieszczane w osobnej tabeli (nie nadpisują oryginału).
Każda z metod ma swoje zastosowania – od szybkich poprawek w miejscu, po kompleksowe przekształcenia dużych zbiorów.
Wybór odpowiedniego narzędzia zależy od skali problemu, rodzaju błędu oraz tego, czy proces ma być jednorazowy, czy powtarzalny.
