Ian w Nashville codziennie otrzymuje dane z pobierania systemu. Kolumna daty zawiera datę i godzinę. To sprawia, że tabela przestawna ma wiele wierszy dziennie zamiast podsumowania jednej komórki dziennie.
W tym artykule przedstawiono trzy sposoby rozwiązania problemu.
Poniższy rysunek przedstawia kolumnę Data z datą i godziną.
Jednym z rozwiązań jest naprawienie tego w tabeli przestawnej.
- Przeciągnij pole Data do obszaru Wiersze. Pojawia się z datą + czasem.
- Wybierz dowolną komórkę zawierającą datę i godzinę.
- Na karcie Analiza narzędzi tabeli przestawnej wybierz opcję Pole grupy.
- W oknie dialogowym Grupowanie wybierz Dni. Odznacz miesiące. Po kliknięciu OK w tabeli przestawnej będzie wyświetlany jeden wiersz dziennie.
Innym żmudnym rozwiązaniem jest dodanie nowej kolumny po pobraniu. Jak pokazano na tym rysunku, formuła w kolumnie J to =INT(D2).
Data + czas to liczba całkowita, po której następuje godzina dziesiętna. Używając funkcji INT, aby uzyskać część całkowitą daty i godziny, izolujesz datę.
To zadziała dobrze, ale teraz musisz dodawać nową kolumnę za każdym razem, gdy pobierasz dane.
Moim preferowanym rozwiązaniem jest użycie dodatku Power Query. Dodatek Power Query działa tylko w wersjach programu Excel dla systemu Windows. Nie można używać dodatku Power Query w systemie Android, IOS ani na komputerze Mac.
Dodatek Power Query jest wbudowany w program Excel 2016. W przypadku programu Excel 2010 i 2013 dodatek Power Query można pobrać od firmy Microsoft.
Najpierw przekonwertuj pobrane dane na tabelę za pomocą Ctrl + T. Następnie na karcie Dane wybierz opcję Z tabeli / zakresu.
W siatce Power Query wybierz kolumnę Data. Przejdź do zakładki Transform. Otwórz menu rozwijane Typ daty i wybierz opcję Data. Jeśli pojawi się komunikat o opcji Zamień krok lub Nowy krok, wybierz opcję Zamień krok.
Na karcie Narzędzia główne dodatku Power Query wybierz pozycję Zamknij i załaduj.
Twój wynik pojawi się na nowym arkuszu. Utwórz tabelę przestawną z tego arkusza.
Następnym razem, gdy otrzymasz nowe dane, wykonaj następujące kroki:
- Wklej nowe dane do oryginalnego zestawu danych.
- Przejdź do arkusza z wynikiem dodatku Power Query. Poszerz panel Zapytanie, aby zobaczyć Odśwież. Kliknij Odśwież.
- Odśwież tabelę przestawną.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2203: Skróć datę i godzinę do zwykłej daty.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie Iana podczas mojego seminarium w Nashville Power Excel. Spójrz na to: Ian codziennie pobiera pliki ze swojego systemu i ma - w kolumnie Data - zawiera datę i godzinę, a to wszystko schrzania, ponieważ kiedy Ian tworzy tabelę przestawną i umieszcza daty razem po lewej stronie, zamiast tylko jednego wiersza na datę, otrzymuje jeden wiersz na każdą datę i godzinę.
Teraz można by tu podejść i powiedzieć „Pole grupowe” i zgrupować to na dzień, kliknąć OK. I wiesz, to kilka dodatkowych kliknięć, których chyba nie musimy robić. Dlatego w przypadku tego podcastu, kilku następnych podcastów, przyjrzymy się Power Query. Teraz jest on wbudowany w program Excel 2016 w obszarze Pobieranie i przekształcanie danych. Jeśli masz 2010 lub 2013 uruchomione w systemie Windows, a nie na komputerze Mac, możesz bezpłatnie pobrać dodatek Power Query z programu Excel. Dodatek Power Query chce pracować z tabelą lub zakresem.
Więc mam zamiar wybrać jedną komórkę danych, nacisnąć Ctrl + T dla tabeli, idealnie. Następnie na karcie Power Query, Data, From Table / Range, wybiorę kolumnę Data i Transform, i powiem, że nie chcę, aby była to data i godzina, ale tylko data. Wybiorę Zastąp bieżący, a następnie Strona główna, Zamknij i załaduj, a otrzymamy zupełnie nowy formularz arkusza z przekonwertowanymi danymi. Teraz, oczywiście, możemy podsumować za pomocą osi i będzie idealnie.
Myślę, że zaletą tej metody, Power Query, jest to, że Ian pobiera więcej danych… Mam więc nowe dane, wezmę je, skopiuję i przejdę do moich starych danych i wklej tuż poniżej. Zwróć uwagę, że znacznik końca tabeli znajduje się teraz w wierszu 474. Kiedy wklejam, znacznik końca tabeli przesuwa się w dół na dół nowych danych. W porządku? A to zapytanie jest napisane na podstawie tabeli. W porządku? Tak więc, gdy stół rośnie.
Problem polega na tym, że jeśli używasz dodatku Power Query po raz pierwszy, dodatek Power Query zaczyna się tak szeroko - tylko w połowie - i nie widać ikony Odśwież. Musisz więc przeciągnąć to tutaj w ten sposób i uzyskać tę wspaniałą ikonę odświeżania, a następnie kliknąć Odśwież. W tej chwili mamy załadowane 473 wiersze, a teraz załadowano 563 wiersze. Utwórz tabelę przestawną, a życie będzie wspaniałe.
Teraz, hej, słuchaj, wiem, że byłoby możliwe dodanie tutaj nowej kolumny = INT (D2) - - daty - skopiuj to, skopiuj i wklej wartości specjalne. Ale wtedy jesteś na haku, robiąc to każdego dnia. W porządku? Więc chociaż w tym filmie omówiliśmy co najmniej trzy różne sposoby rozwiązania tego problemu, dla mnie Power Query i po prostu możliwość kliknięcia przycisku Odśwież jest drogą do zrobienia.
Power Query, mówię o tym w mojej nowej książce MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Podsumowanie dzisiejszego odcinka: Ian w Nashville codziennie pobiera dane z systemu, kolumna Data irytująco zawiera zarówno Data, jak i Godzina. funkcja INT, kopiuj, wklej wartości; po drugie, pozostaw to jako datę i godzinę, a następnie pogrupuj tabelę przestawną według daty; lub lepsze rozwiązanie, Power Query, przekształca pobrany zestaw danych w tabelę za pomocą Ctrl + T, a następnie na karcie Dane wybierz opcję Z tabeli, wybierz kolumnę Data i godzina, przejdź do karty Przekształcenia w dodatku Power Query, przekonwertuj do Date, Close & Load, zbuduj z tego tabelę przestawną. Następnym razem, gdy pobierzesz dane, wklej je do oryginalnej tabeli, przejdź do panelu Zapytanie, kliknij Odśwież i gotowe.
Hej, aby pobrać skoroszyt z dzisiejszego filmu, odwiedź adres URL w opisie YouTube.
Chcę podziękować Ianowi za przybycie na moje seminarium w Nashville, chcę podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: date-time-to-date.xlsx
Aby jeszcze bardziej ulepszyć proces dodatku Power Query:
- Upewnij się, że skoroszyt pobierania jest zawsze zapisywany w tym samym folderze o tej samej nazwie.
- Zacznij od pustego skoroszytu. Zapisz jako pod nazwą taką jak DownloadedFileTransformed.xlsx
- Z pustego skoroszytu, danych, z pliku, ze skoroszytu. Wskaż plik z kroku 1.
- Wybierz Wyczyść dane. Przekształć datę / godzinę w datę. Zamknij i wczytaj.
- Kliknij prawym przyciskiem myszy panel zapytań i wybierz Właściwości. Wybierz opcję Odśwież dane podczas otwierania pliku.
- Codziennie idź do pracy. Weź swoją kawę. Upewnij się, że w folderze znajduje się nowy plik nr 1.
- Otwórz skoroszyt DownloadFileTransformed.xlsx. Będzie miał nowe dane, a daty będą prawidłowe.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Walidacja jest Twoim przyjacielem”.
Duane Aubin
