Dodawaj, usuwaj i zmieniaj nazwy kolumn w R za pomocą Dplyr
W tym samouczku omówimy pakiet dplyr, który umożliwia sortowanie, filtrowanie, dodawanie i zmianę nazw kolumn w języku R.
3 proste sposoby na odejmowanie dat w Excelu
W pewnym momencie podczas pracy w Excelu będziesz musiał znaleźć liczbę dni między dwiema datami.
Na szczęście Excel oferuje kilka sposobów odejmowania dat, aby uzyskać różnicę w dniach między nimi.
Istnieją trzy proste sposoby odejmowania dat w programie Excel, są to:
- Proste odejmowanie
- Korzystanie z funkcji DNI().
- Korzystanie z funkcji NETWORKDAYS().
Istnieje jeden starszy sposób, funkcja DATEDIF(), której firma Microsoft już nie obsługuje. Jednak wymienione trzy metody są niezawodne i dokładne. Szybko znajdziesz różnice między terminami zadań takich jak harmonogramy projektów czy analizy biznesowe.
Pod koniec tego artykułu będziesz mógł zastosować każdą metodę we własnej pracy, aby odjąć daty, aby z łatwością obliczyć liczbę dni.
Wejdźmy w to.
Spis treści
- Zrozumienie dat w Excelu
- 1. Używając prostego odejmowania, aby uzyskać liczbę dni
- 2. Wykorzystanie funkcji DNI do obliczenia liczby dni
- 3. Korzystanie z funkcji NETWORKDAYS
- Starsza funkcja DATEDIF (niezalecana)
- Korzystanie z bieżącej daty w obliczeniach
- Jak odjąć miesiące lub lata, aby znaleźć dzień
- Co jeśli data początkowa jest późniejsza niż data końcowa?
- Wskazówki dotyczące dokładnego odejmowania daty
- Typowe błędy i rozwiązywanie problemów
- Wniosek
Zrozumienie dat w Excelu
Daty w programie Excel mogą czasami być mylące, ponieważ działają zarówno jako liczby, jak i tekst. Ma to większy sens, gdy zrozumiesz, że program Excel przechowuje daty jako numery seryjne.
System daty programu Excel zaczyna liczyć od 1 stycznia 1900 r., któremu przypisano numer kolejny 1. Każdemu następnemu dniu przypisywana jest kolejna liczba.
Na przykład 2 stycznia 1900 ma numer seryjny 2.
Prawdopodobnie widzisz, że dzięki temu łatwiej jest odjąć dwie daty i znaleźć dni między nimi. Przeanalizujmy zalecane sposoby, aby to zrobić.
Sugeruję uruchomienie nowego skoroszytu. Każdy przykład, który podam, jest krótkim samouczkiem.
Mój arkusz używa komórki B2 i komórki C2 dla przykładowych dat. Możesz użyć przykładów jako szablonów do swojej pracy.
1. Używając prostego odejmowania, aby uzyskać liczbę dni
Pierwszą metodą znalezienia liczby dni między dwiema datami w programie Excel jest użycie prostego odejmowania (znak minus). To jest format:
=Koniec-początek
Wykonaj następujące kroki:
- Wpisz dwie daty w osobnych komórkach.
- W nowej komórce napisz taką formułę:
=C2-B2 - Naciśnij enter.
Wynikowa wartość będzie liczbą pełnych dni między dwiema datami.
W naszym przykładzie wpisałem „3 marca 2023” w komórce B2 i „20 marca 2023” w komórce C2. Wynik obliczeń to 17.
Komórka A2 na poniższej ilustracji to po prostu opis zastosowanej metody.
Dodanie niestandardowej etykiety
Jeśli chcesz wyświetlić wynik z niestandardową etykietą, użyj następującej formuły:
=C2-B2 & "dni"
Ta formuła łączy obliczoną różnicę dat odwołań do komórek z tekstem „dni”. Daje to bardziej opisowy wynik.
Na przykład, jeśli między dwiema datami jest 17 dni, wynik zostanie wyświetlony jako „17 dni”.
2. Wykorzystanie funkcji DNI do obliczenia liczby dni
Funkcja DNI programu Excel jest wbudowaną funkcją daty, która oblicza liczbę dni między datami. To jest format:
=DAYS(koniec; początek)
Wykonaj następujące kroki:
- Wpisz dwie daty w osobnych komórkach.
- W nowej komórce napisz taką formułę:
=DAYS(C2, B2) - Naciśnij enter.
Wynikowa wartość będzie liczbą pełnych dni między dwiema datami.
Użyłem tych samych dat z poprzedniego przykładu, aby przetestować funkcję. Wynikowa liczba była taka sama.
Wynik zawsze będzie liczbą całkowitą. Jeśli wolisz, aby była wyświetlana jako ułamek dziesiętny (z końcowymi zerami), użyj karty formatowania, aby zmienić format liczb.
3. Korzystanie z funkcji NETWORKDAYS
Funkcja daty NETWORKDAYS oblicza liczbę dni roboczych między dwiema datami. Ponieważ są to dni robocze, obliczenia wykluczają weekendy i określone święta.
To jest format:
= NETWORKDAYS(początek; koniec; [święta])
Trzeci parametr (Święta) jest opcjonalny. Jeśli go nie użyjesz, formuła wykluczy weekendy.
Wykonaj następujące kroki, aby użyć najprostszej formuły bez określania dni wolnych:
- Wpisz dwie daty w osobnych komórkach.
- W nowej komórce wpisz następującą formułę:
=NETWORKDAYS(B2, C2) - Naciśnij enter.
Biorąc pod uwagę daty, których użyłem w poprzednim przykładzie (3 stycznia i 20 stycznia 2023 r.), wynikiem tej funkcji Excela jest 14.
To o trzy dni mniej niż przy użyciu prostego odejmowania. To dlatego, że w tym przedziale czasowym są dwa weekendy (sprawdziłem dwukrotnie).
Określanie świąt
Możesz nadać formule listę dni wolnych do wykluczenia z obliczeń.
Robisz to, wprowadzając każde święto do zakresu komórek i umieszczając zakres w parametrze Święta.
Łatwiej to zrozumieć na działającym przykładzie.
- Dwie daty znajdują się w komórkach B2 i C2.
- Pierwsza data święta znajduje się w komórce D2, a druga w komórce E2.
- W komórce F2 użyj formuły:
=NETWORKDAYS(B2, C2, D2:E2) - Naciśnij klawisz Enter, aby zobaczyć wynik.
W naszym przykładzie wynikiem jest 12. To o dwa dni mniej niż w poprzednim obliczeniu, ponieważ formuła znalazła listę dat w podanym przez nas zakresie komórek.
( Nie pokazuję komórek A1 i A2 na obrazku. Komórka A1 jest pusta, a komórka A2 zawiera opis. ).
Nawiasem mówiąc, jeśli masz długą listę dat świątecznych i nie jesteś pewien, czy zawiera duplikaty, poniższy film pokazuje, jak sprawdzić dane.
Pokazuje również, jak usunąć duplikaty!
Określanie różnych dni dla weekendów
Zwróć uwagę, że funkcja NETWORKDAYS używa soboty i niedzieli jako domyślnych dni weekendowych do swoich obliczeń.
Jeśli Twoje weekendy są inne, możesz użyć funkcji NETWORKDAYS.INTL, aby dostosować dzień weekendu.
Jeśli chcesz dowiedzieć się więcej o tym formacie, oto dokumentacja firmy Microsoft .
Starsza funkcja DATEDIF (niezalecana)
Funkcji JEŻELI DATA nie znajdziesz na liście funkcji wbudowanych w bieżącej wersji programu Microsoft Excel.
Nadal jednak można z niego korzystać. Microsoft nie wymienia go, ponieważ firma nie obsługuje już tej funkcji, z wyjątkiem szczególnych przypadków.
Problem polega na tym, że nie zawsze jest to dokładne. Dlatego zalecam unikanie korzystania z tej funkcji.
Opiszę to na wypadek, gdybyś korzystał ze starszej wersji oprogramowania. Możesz go również znaleźć w starszych szablonach, z którymi pracujesz. Składnia to:
= DATEDIF(początek; koniec; „d”)
Użycie „d” w trzecim parametrze powoduje, że funkcja oblicza w dniach. Pamiętaj, aby umieścić „d” w podwójnym cudzysłowie.
Jako alternatywne użycie, „m” wykonuje obliczenia jako liczbę miesięcy, podczas gdy „y” podaje liczbę lat.
Oto kroki:
- Wprowadź dwie daty w sąsiednich komórkach.
- W nowej komórce wpisz następującą formułę:
=DATEDIF(Start_Date, End_Date, "d"). - Naciśnij enter.
Spowoduje to zwrócenie dni między dwiema datami.
Korzystając z komórek z poprzednich przykładów, funkcja wygląda następująco: DATEDIF(B2; C2; „d”).
W przeciwieństwie do innych funkcji, ta pokazuje #NUM! błąd, jeśli pierwsza data jest późniejsza niż druga.
Korzystanie z bieżącej daty w obliczeniach
Jeśli chcesz, aby jedna z dat w obliczeniach była datą bieżącą, program Excel udostępnia przydatną funkcję Dzisiaj.
Po prostu zamieniasz jedno z odwołań do komórek na DZISIAJ(). W moim arkuszu potrzebowałbym tylko kolumny B do przechowywania dat. Oto przykład:
=DNI(DZISIAJ(), B2)
Nieco mylące, równoważna funkcja w VBA ma inną nazwę. Użyj funkcji Date(), jeśli kodujesz makro.
Jak odjąć miesiące lub lata, aby znaleźć dzień
Jeśli szukasz daty sprzed dokładnie jednego miesiąca, możesz pokusić się o odjęcie 30 dni od danych wyjściowych funkcji DZIŚ().
Będzie to jednak dokładne tylko dla podzbioru miesięcy, więc nie jest to dobra metoda.
Funkcja EDATE służy właśnie do tego celu. Składnia to:
= EDATE(początek; miesiące)
Dla mojego konkretnego przykładu dokładnie miesiąc temu użyłbym:
=EDATA(dzisiaj(); -1)
To daje mi datę pełny miesiąc w przeszłości. To najlepszy sposób na odejmowanie miesięcy.
Jeśli musisz odjąć lata, funkcja ROK oblicza lata od podanej daty.
Co jeśli data początkowa jest późniejsza niż data końcowa?
Jeśli pierwsza data jest większa niż druga, zalecane metody nie uznają tego za błąd. Zrobią obliczenia i podają liczbę ujemną.
Na przykład, jeśli omyłkowo zamieniłem daty w przykładach, wynikiem jest -17.
Zawsze warto zapobiegać popełnianiu błędów. Możesz użyć funkcji JEŻELI, aby sprawdzić nieprawidłowy zakres dat i zamiast tego wyświetlić komunikat o błędzie.
Oto przykład włączenia funkcji JEŻELI do funkcji DNI:
=JEŻELI(B2 > C2; „Nieprawidłowe daty”; DNI(B2; C2; „d”))
Ta formuła programu Excel sprawdza, czy pierwsza data jest większa niż druga. W takim przypadku wyświetlany jest tekst „Unieważnij daty”.
W przeciwnym razie obliczy różnicę w dniach, jak poprzednio.
W poniższym przykładzie zamieniłem dwie daty. Funkcja wyświetla tekst określony przeze mnie dla funkcji JEŻELI().
Wskazówki dotyczące dokładnego odejmowania daty
Podczas pracy z datami w programie Excel ważne jest zapewnienie dokładności obliczeń.
Oto moje najlepsze wskazówki, które pomogą Ci korzystać z obliczeń dat.
Wskazówka 1. Użyj właściwej formuły
Zalecam, aby nie używać funkcji DATEDIF(). Nie zawsze jest niezawodny.
Zamiast tego wybierz jedną z trzech innych metod opisanych w tym artykule.
Wskazówka 2. Sformatuj daty poprawnie
Upewnij się, że zarówno komórki daty początkowej, jak i końcowej są sformatowane jako daty.
Aby to zrobić, wykonaj następujące kroki:
- kliknij komórkę prawym przyciskiem myszy.
- wybierz „Formatuj komórki” (lub naciśnij ctrl + 1, aby otworzyć okno dialogowe).
- wybierz „Data” w zakładce „Liczba”.
Pomoże to uniknąć potencjalnych błędów w obliczeniach.
Wskazówka 3. Uwzględnij lata przestępne
Należy pamiętać, że lata przestępne mogą wpływać na liczbę dni między dwiema datami.
Program Excel automatycznie uwzględnia lata przestępne w swoich obliczeniach dat, więc jeśli zastosujesz poprawną formułę i formatowanie, powinieneś uzyskać dokładne wyniki.
Listę lat przestępnych znajdziesz tutaj .
Wskazówka 4. Wyraźnie wyświetlaj daty i wyniki
Aby poprawić czytelność danych i wyników, rozważ użycie niektórych funkcji formatowania programu Excel:
- Dostosuj szerokości kolumn, aby dostosować je do szerokości zawartości.
- Wyrównaj dane daty na środku każdej komórki.
- Zastosuj style, takie jak pogrubienie lub kursywa, aby nagłówki kolumn były wyraźniejsze.
Typowe błędy i rozwiązywanie problemów
Podczas pracy z datami w programie Excel możesz napotkać kilka typowych błędów. Oto kilka wskazówek dotyczących rozwiązywania problemów, które pomogą Ci rozwiązać te problemy:
Błąd 1: #WARTOŚĆ!
Ten błąd występuje, gdy wartości komórek nie są rozpoznawane jako prawidłowe daty.
Aby rozwiązać ten problem:
- Upewnij się, że obie komórki daty są sformatowane jako daty.
- Sprawdź ustawienia regionalne, aby poprawnie interpretować daty.
Różne języki mają różne układy dat (np. dd/mm/rr lub mm/dd/rr).
Błąd 2: Nieprawidłowe wyniki
Jeśli otrzymujesz nieprawidłowe wyniki, rozważ następujące kwestie
- Sprawdź dokładnie, jak wpisałeś daty. Pomieszanie miesiąca i dnia może spowodować błędy w obliczeniach.
- Użyj dni sieciowych, jeśli chcesz liczyć tylko dni robocze między dwiema datami, używając funkcji NETWORKDAYS().
- Konsekwentnie używaj tego samego sposobu formatowania dat w całym arkuszu, aby uniknąć nieporozumień i błędów.
- Unikaj ręcznych obliczeń, korzystając z metod opisanych w tym artykule.
Wniosek
W tym artykule zbadaliśmy różne metody odejmowania dat w programie Excel i obliczania różnicy w dniach między dwiema datami.
Do tej pory powinieneś mieć pewność, że używasz funkcji DNI, DNI.ROBOCZY i prostego odejmowania, aby uzyskać dokładne wyniki.
Pamiętaj, aby zawsze dokładnie sprawdzać swoje dane, ponieważ nieprawidłowe lub nieprawidłowe formaty mogą prowadzić do nieoczekiwanych rezultatów.
Stosując dowolną z omówionych metod, upewnij się, że zarówno data początkowa, jak i data końcowa mają odpowiedni format, aby program Excel je rozpoznał.
Teraz, gdy jesteś wyposażony w tę wiedzę, możesz skutecznie śledzić czas trwania projektów, analizować trendy i zarządzać terminami, między innymi zadaniami.
Ćwicz i eksperymentuj z różnymi formatami dat i funkcjami, aby wykorzystać pełny potencjał możliwości dat programu Excel. Miłego liczenia!
Gotowy, aby przenieść swoje umiejętności Excela na wyższy poziom? Sprawdź nasze
Funkcje zbierania w Microsoft Power Automate
Odkryj różnorodne funkcje zbierania, które można wykorzystać w Power Automate. Zdobądź praktyczne informacje o funkcjach tablicowych i ich zastosowaniu.
Oceń wydajność kodu DAX w DAX Studio
Z tego samouczka dowiesz się, jak ocenić wydajność kodów DAX przy użyciu opcji Uruchom test porównawczy w DAX Studio.
Czym jest self w Pythonie: przykłady z życia wzięte
Czym jest self w Pythonie: przykłady z życia wzięte
Jak zapisać i załadować plik RDS w R
Dowiesz się, jak zapisywać i ładować obiekty z pliku .rds w R. Ten blog będzie również omawiał sposób importowania obiektów z R do LuckyTemplates.
Powrót do pierwszych N dni roboczych — rozwiązanie języka kodowania DAX
Z tego samouczka języka kodowania DAX dowiesz się, jak używać funkcji GENERUJ i jak dynamicznie zmieniać tytuł miary.
Zaprezentuj spostrzeżenia przy użyciu wielowątkowej techniki dynamicznych wizualizacji w usłudze LuckyTemplates
W tym samouczku omówiono sposób korzystania z techniki wielowątkowych wizualizacji dynamicznych w celu tworzenia szczegółowych informacji na podstawie dynamicznych wizualizacji danych w raportach.
Wprowadzenie do filtrowania kontekstu w usłudze LuckyTemplates
W tym artykule omówię kontekst filtra. Kontekst filtrowania to jeden z głównych tematów, z którym każdy użytkownik usługi LuckyTemplates powinien zapoznać się na początku.
Najlepsze wskazówki dotyczące korzystania z aplikacji w usłudze online LuckyTemplates
Chcę pokazać, jak usługa online LuckyTemplates Apps może pomóc w zarządzaniu różnymi raportami i spostrzeżeniami generowanymi z różnych źródeł.
Analizuj zmiany marży zysku w godzinach nadliczbowych — Analizuj za pomocą LuckyTemplates i DAX
Dowiedz się, jak obliczyć zmiany marży zysku przy użyciu technik, takich jak rozgałęzianie miar i łączenie formuł języka DAX w usłudze LuckyTemplates.