Czyszczenie danych zawierających wartości dat
Dane, które uzyskujesz z różnych źródeł, mogą zawierać wartości dat. W tym rozdziale dowiesz się, jak przygotować dane zawierające wartości danych do analizy.
Dowiesz się o -
- Formaty dat
- Data w formacie seryjnym
- Data w różnych formatach miesiąc-dzień-rok
- Konwersja dat w formacie seryjnym na format miesiąc-dzień-rok
- Konwersja dat w formacie miesiąc-dzień-rok na format seryjny
- Uzyskiwanie dzisiejszej daty
- Znajdowanie dnia roboczego po określonych dniach
- Dostosowywanie definicji weekendu
- Liczba dni roboczych między dwoma podanymi datami
- Wyodrębnianie roku, miesiąca, dnia z daty
- Wyodrębnianie dnia tygodnia z daty
- Uzyskanie daty z roku, miesiąca i dnia
- Obliczanie liczby lat, miesięcy i dni między dwiema datami
Formaty dat
Excel obsługuje Date wartości na dwa sposoby -
- Format seryjny
- W różnych formatach rok-miesiąc-dzień
Możesz konwertować -
ZA Date w formacie seryjnym do Date w formacie rok-miesiąc-dzień
ZA Date w formacie rok-miesiąc-dzień do pliku Date w formacie seryjnym
Data w formacie seryjnym
ZA Date w formacie szeregowym to dodatnia liczba całkowita reprezentująca liczbę dni między podaną datą a 1 stycznia 1900 r. Zarówno bieżąca Datei 1 stycznia 1900 są wliczane do liczby. Na przykład 42354 to plikDate co oznacza 12/16/2015.
Data w formatach miesiąc-dzień-rok
Excel obsługuje różne pliki Date Formaty oparte na Locale(Lokalizacja), którą wybierzesz. Dlatego musisz najpierw określić zgodność swojegoDateformatów i analizy danych. Zauważ, że pewneDate formaty są poprzedzone * (gwiazdką) -
Date formaty zaczynające się od * (gwiazdka) reagują na zmiany regionalnych ustawień daty i godziny określonych dla systemu operacyjnego
Date formaty bez * (gwiazdka) nie mają wpływu na ustawienia systemu operacyjnego
Dla zrozumienia można przyjąć Stany Zjednoczone jako ustawienia regionalne. Znajdziesz następująceDate formaty do wyboru dla Date- 8 th czerwca 2016 -
- * 6/8/2016 (dotyczy ustawień systemu operacyjnego)
- * Środa, 8 czerwca 2016 r. (Dotyczy ustawień systemu operacyjnego)
- 6/8
- 6/8/16
- 06/08/16
- 8-Jun
- 8-Jun-16
- 08-Jun-16
- Jun-16
- June-16
- J
- J-16
- 6/8/2016
- 8-Jun-2016
Jeśli wpiszesz tylko dwie cyfry reprezentujące rok i jeśli -
Cyfry to 30 lub więcej, program Excel zakłada, że cyfry oznaczają lata dwudziestego wieku.
Cyfry są mniejsze niż 30, program Excel zakłada, że cyfry oznaczają lata w XXI wieku.
Na przykład 1/1/29 jest traktowany jako 1 stycznia 2029 r., A 1/1/30 jest traktowany jako 1 stycznia 1930 r.
Konwersja dat w formacie seryjnym na format miesiąc-dzień-rok
Aby przekonwertować daty z formatu seryjnego na format miesiąc-dzień-rok, wykonaj czynności podane poniżej -
Kliknij Number kartę w Format Cells Okno dialogowe.
Kliknij Date pod Category.
Wybierz Locale. DostępneDate formaty zostaną wyświetlone jako lista pod Type.
Kliknij plik Format pod Type aby spojrzeć na podgląd w polu obok Sample.
Po wybraniu formatu kliknij OK.
Konwersja dat w formacie miesiąc-dzień-rok na format seryjny
Możesz konwertować daty w formacie miesiąc-dzień-rok na format seryjny na dwa sposoby -
Za pomocą Format Cells Okno dialogowe
Korzystanie z programu Excel DATEVALUE funkcjonować
Korzystanie z okna dialogowego Formatowanie komórek
Kliknij Number kartę w Format Cells Okno dialogowe.
Kliknij General pod Category.
Korzystanie z funkcji DATEVALUE programu Excel
Możesz użyć programu Excel DATEVALUE funkcja do konwersji Date do Serial Numberformat. Musisz załączyć plikDateargument w „”. Na przykład,
= DATEVALUE („6/8/2016”) daje 42529
Uzyskiwanie dzisiejszej daty
Jeśli chcesz wykonać obliczenia na podstawie dzisiejszej daty, po prostu użyj funkcji programu Excel DZISIAJ (). Wynik odzwierciedla datę użycia.
Poniższy zrzut ekranu Dzisiaj () Wykorzystanie funkcji została podjęta w dniu 16 th maja 2016 -
Znajdowanie dnia roboczego po określonych dniach
Konieczne może być wykonanie pewnych obliczeń na podstawie dni roboczych.
Dni robocze nie obejmują dni weekendowych i świąt. Oznacza to, że jeśli możesz zdefiniować swój weekend i święta, wszelkie obliczenia, które wykonasz, będą oparte na dniach roboczych. Na przykład możesz obliczyć termin płatności faktur, przewidywany czas dostawy, datę następnego spotkania itp.
Możesz użyć programu Excel WORKDAY i WORKDAY.INTL funkcje do takich operacji.
| S.No. | Opis funkcji |
|---|---|
| 1. | WORKDAY Zwraca liczbę kolejną daty przed określoną liczbą dni roboczych lub po niej |
| 2. | WORKDAY.INTL Zwraca liczbę kolejną daty przed lub po określonej liczbie dni roboczych za pomocą parametrów wskazujących, które i ile dni są dniami weekendowymi |
Na przykład, można określić 15 th dzień roboczy od dzisiaj (zrzut ekranu poniżej zostanie podjęta w dniu 16 th maja 2016) za pomocą funkcji dziś i roboczym.
Załóżmy, 25 th maja 2016 i 1 st czerwca 2016 są święta. Wtedy twoje obliczenia będą następujące -
Dostosowywanie definicji weekendu
Domyślnie weekend to sobota i niedziela, czyli dwa dni. Możesz również opcjonalnie zdefiniować swój weekend za pomocąWORKDAY.INTLfunkcjonować. Możesz określić swój własny weekend za pomocą numeru weekendu, który odpowiada dniom weekendu, jak podano w poniższej tabeli. Nie musisz zapamiętywać tych liczb, ponieważ gdy zaczniesz wpisywać funkcję, otrzymasz listę liczb i dni weekendowych na liście rozwijanej.
| Weekendowe dni | Numer weekendu |
|---|---|
| Sobota niedziela | 1 lub pominięty |
| Niedziela poniedziałek | 2 |
| Poniedziałek wtorek | 3 |
| Wtorek środa | 4 |
| Środa Czwartek | 5 |
| Czwartek piątek | 6 |
| Piątek sobota | 7 |
| Tylko niedziela | 11 |
| Tylko w poniedziałek | 12 |
| Tylko we wtorek | 13 |
| Tylko w środę | 14 |
| Tylko w czwartek | 15 |
| Tylko w piątek | 16 |
| Tylko w sobotę | 17 |
Załóżmy, że jeśli weekend przypada tylko w piątek, musisz użyć liczby 16 w funkcji DZIEŃ.ROBOCZY.INTL.
Liczba dni roboczych między dwoma podanymi datami
Może zaistnieć potrzeba obliczenia liczby dni roboczych między dwiema datami, na przykład w przypadku obliczania płatności na rzecz pracownika kontraktowego, który otrzymuje wynagrodzenie za dzień.
Liczbę dni roboczych między dwiema datami można znaleźć za pomocą funkcji programu Excel NETWORKDAYS i NETWORKDAYS.INTL. Podobnie jak w przypadku DNI.ROBOCZY i DNI.ROBOCZE.INTL, DNI.ROBOCZE i DNI.ROBOCZE.INTL umożliwiają określenie dni wolnych, aw przypadku DNI.ROBOCZY.INTL można dodatkowo określić weekend.
| S.No. | Opis funkcji |
|---|---|
| 1. | NETWORKDAYS Zwraca liczbę pełnych dni roboczych między dwiema datami |
| 2. | NETWORKDAYS.INTL Zwraca liczbę pełnych dni roboczych między dwiema datami przy użyciu parametrów wskazujących, które i ile dni przypadają na weekendy |
Możesz obliczyć liczbę dni roboczych między dniem dzisiejszym a inną datą za pomocą funkcji DZIŚ i DNI.ROBOCZE. W podanym poniżej zrzut ekranu, dzisiaj jest 16 th maja 2016, a data zakończenia jest 16 th czerwca 2016. 25 th maja 2016 i 1 st czerwca 2016 są święta.
Ponownie zakłada się, że weekend przypada na sobotę i niedzielę. Możesz mieć własną definicję weekendu i obliczyć liczbę dni roboczych między dwiema datami za pomocą funkcji NETWORKDAYS.INTL. Na poniższym zrzucie ekranu tylko piątek jest zdefiniowany jako weekend.
Wyodrębnianie roku, miesiąca, dnia z daty
Możesz wyodrębnić z każdej daty na liście dat, odpowiadający jej dzień, miesiąc i rok, korzystając z funkcji programu Excel DZIEŃ, MIESIĄC i ROK.
Weźmy na przykład pod uwagę następujące daty -
Z każdej z tych dat można wyodrębnić dzień, miesiąc i rok w następujący sposób -
Wyodrębnianie dnia tygodnia z daty
Możesz wyodrębnić z każdej daty na liście dat, odpowiadający jej dzień tygodnia za pomocą funkcji WEEKDAY programu Excel.
Rozważmy ten sam przykład podany powyżej.
Uzyskanie daty z roku, miesiąca i dnia
Twoje dane mogą mieć osobno informacje o roku, miesiącu i dniu. Musisz uzyskać datę łączącą te trzy wartości, aby wykonać jakiekolwiek obliczenia. Aby uzyskać wartości dat, możesz użyć funkcji DATA.
Weź pod uwagę następujące dane -
Użyj funkcji DATA, aby uzyskać wartości DATA.
Obliczanie lat, miesięcy i dni między dwiema datami
Być może trzeba będzie obliczyć czas, który upłynął od określonej daty. Możesz potrzebować tych informacji w postaci lat, miesięcy i dni. Prostym przykładem byłoby obliczenie obecnego wieku osoby. To faktycznie różnica między datą urodzenia a dniem dzisiejszym. W tym celu możesz użyć funkcji Excel DATEDIF, TODAY i CONCATENATE.
Dane wyjściowe są następujące -
