Spis treści
W profesjonalnej pracy z arkuszami kalkulacyjnymi nie jest niczym niezwykłym interakcja z datami i godzinami. Bez tego nie będziesz mógł się obejść. Dlatego sam Bóg nakazał nauczyć się pracować z tego typu danymi. Dzięki temu zaoszczędzisz mnóstwo czasu i unikniesz wielu błędów podczas pracy z arkuszami kalkulacyjnymi.
Niestety wielu początkujących nie wie, jak przetwarzane są dane. Dlatego przed rozważeniem tej klasy operacji konieczne jest przeprowadzenie bardziej szczegółowego programu edukacyjnego.
Jak data jest reprezentowana w Excelu
Informacje o dacie są przetwarzane jako liczba dni od 0 stycznia 1900 r. Tak, nie mylisz się. Rzeczywiście, od zera. Ale jest to konieczne, aby istniał punkt wyjścia, aby 1 stycznia był już uważany za numer 1 i tak dalej. Maksymalna obsługiwana wartość daty to 2958465, co z kolei wynosi 31 grudnia 9999.
Ta metoda umożliwia wykorzystanie dat do obliczeń i formuł. Tak więc Excel umożliwia określenie liczby dni między datami. Schemat jest prosty: druga jest odejmowana od jednej liczby, a następnie wynikowa wartość jest konwertowana na format daty.
Dla większej przejrzystości poniżej znajduje się tabela przedstawiająca daty wraz z odpowiadającymi im wartościami liczbowymi.
Aby określić liczbę dni, które minęły od daty A do daty B, musisz odjąć pierwszy od ostatniego. W naszym przypadku jest to formuła = B3-B2. Po jego wprowadzeniu wynik jest następujący.
Należy pamiętać, że wartość jest wyrażona w dniach, ponieważ wybraliśmy inny format komórki niż data. Gdybyśmy początkowo wybrali format „Data”, wynik byłby taki.
Ważne jest, aby zwracać uwagę na ten punkt w swoich obliczeniach.
Oznacza to, że aby wyświetlić prawidłowy numer seryjny, który w pełni odpowiada dacie, należy użyć dowolnego formatu innego niż data. Z kolei, aby zamienić liczbę na datę, należy ustawić odpowiedni format.
Jak czas jest reprezentowany w Excelu
Sposób, w jaki czas jest reprezentowany w Excelu, różni się nieco od daty. Za podstawę przyjmuje się dzień, a godziny, minuty, sekundy są jego częściami ułamkowymi. Oznacza to, że 24 godziny to 1, a każda mniejsza wartość jest uważana za jej ułamek. Tak więc 1 godzina to 1/24 dnia, 1 minuta to 1/1140, a 1 sekunda to 1/86400. Najmniejsza jednostka czasu dostępna w programie Excel to 1 milisekunda.
Podobnie jak daty, ten sposób reprezentacji umożliwia wykonywanie obliczeń w czasie. To prawda, że jest tu jedna niewygodna rzecz. Po obliczeniach otrzymujemy część dnia, a nie liczbę dni.
Zrzut ekranu przedstawia wartości w formacie liczbowym oraz w formacie „Czas”.
Sposób obliczania czasu jest podobny do daty. Konieczne jest odjęcie wcześniejszego czasu od późniejszego czasu. W naszym przypadku jest to formuła = B3-B2.
Ponieważ komórka B4 miała najpierw format ogólny, a pod koniec wprowadzenia formuły natychmiast zmienia się na „Czas”.
Excel, pracując z czasem, wykonuje zwykłe operacje arytmetyczne na liczbach, które następnie są tłumaczone na znany nam format czasu.
Format daty i godziny
O ile nam wiadomo, daty i godziny mogą być przechowywane w różnych formatach. Dlatego musisz wiedzieć, jak je poprawnie wprowadzić, aby formatowanie było poprawne.
Oczywiście podczas wprowadzania daty i godziny można użyć numeru seryjnego dnia lub części dnia, ale takie podejście jest bardzo niewygodne. Ponadto będziesz musiał stale stosować określony format do komórki, co tylko zwiększa dyskomfort.
Dlatego Excel pozwala określić czas i datę na różne sposoby. Jeśli zastosujesz jeden z nich, program natychmiast zamieni informacje na odpowiednią liczbę i zastosuje poprawny format do komórki.
Poniższa tabela zawiera listę metod wprowadzania daty i godziny obsługiwanych przez program Excel. Lewa kolumna zawiera listę możliwych formatów, a prawa kolumna pokazuje, jak będą wyświetlane w programie Excel po konwersji. Należy zauważyć, że jeśli rok nie zostanie określony, bieżący, ustawiony w systemie operacyjnym, zostanie przypisany automatycznie.
W rzeczywistości istnieje znacznie więcej sposobów wyświetlania. Ale to wystarczy. Ponadto konkretna opcja zapisu daty może się różnić w zależności od kraju lub regionu, a także ustawień systemu operacyjnego.
Formatowanie niestandardowe
Podczas pracy z komórkami użytkownik może określić, jaki będzie format. Może sprawić, że będzie wyświetlany tylko czas, miesiąc, dzień itd. Możliwe jest również dostosowanie kolejności formułowania daty oraz separatorów.
Aby uzyskać dostęp do okna edycji, musisz otworzyć zakładkę „Liczba”, w której znajduje się okno opcji „Formatuj komórki”. W otwartym oknie dialogowym pojawi się kategoria „Data”, w której możesz wybrać poprawny format daty.
Jeśli wybierzesz kategorię „Czas”, pojawi się odpowiednio lista z opcjami wyświetlania czasu.
Aby zastosować określoną opcję formatowania do komórki, musisz wybrać żądany format i kliknąć OK. Następnie wynik zostanie zastosowany. Jeśli nie ma wystarczającej liczby formatów oferowanych przez program Excel, możesz znaleźć kategorię „Wszystkie formaty”. Istnieje również wiele opcji.
Jeśli żadna opcja nie jest odpowiednia, zawsze można stworzyć własną. Bardzo łatwo to zrobić. Wystarczy wybrać wstępnie ustawione formaty jako próbkę i wykonać następujące kroki:
- Wybierz komórkę, której format chcesz zmienić.
- Otwórz okno dialogowe "Formatuj komórki" i znajdź kartę "Liczba".
- Następnie otwiera się kategoria „Wszystkie formaty”, w której znajduje się pole wejściowe „TYP”. Tam musisz podać kod formatu liczb. Po wprowadzeniu kliknij „OK”.
- Po wykonaniu tych czynności komórka wyświetli informacje o dacie i godzinie w niestandardowym formacie.
Korzystanie z funkcji z datami i godzinami
Podczas pracy z datami i godzinami użytkownik może korzystać z ponad 20 różnych funkcji. I choć ta kwota może być dla kogoś za duża, to wszystkie można wykorzystać do osiągnięcia określonych celów.
Aby uzyskać dostęp do wszystkich możliwych funkcji, należy przejść do kategorii „Data i godzina” w grupie „Biblioteka funkcji”. Rozważymy tylko niektóre z głównych funkcji, które umożliwiają wyodrębnienie różnych parametrów z dat i godzin.
ROK()
Zapewnia możliwość uzyskania roku odpowiadającego określonej dacie. Jak już wiesz, ta wartość może wynosić od 1900 do 9999.
Komórka 1 pokazuje datę w formacie DDDD DD.MM.RRRR gg:mm:ss. To format, który stworzyliśmy wcześniej. Weźmy jako przykład formułę, która określa, ile lat minęło między dwiema datami.
Jednocześnie, jeśli przyjrzymy się dokładniej, okaże się, że funkcja nie obliczyła całkowicie poprawnego wyniku. Powodem jest to, że w swoich obliczeniach używa tylko dat.
MIESIĄC()
Dzięki tej funkcji możesz podświetlić numer miesiąca odpowiadającego konkretnej dacie. Zwraca wynik od 1 do 12. Ta liczba z kolei odpowiada numerowi miesiąca.
DZIEŃ()
Podobnie jak poprzednie funkcje, ta zwraca numer dnia w danym dniu. Wynik obliczeń może wynosić od 1 do 31.
CZAS()
Jak sama nazwa wskazuje, funkcja ta zwraca numer godziny z zakresu od 0 do 23.
MINUTY()
Funkcja zwracająca liczbę minut w określonej komórce. Możliwe wartości, które są zwracane to od 0 do 59.
SEKUNDY()
Ta funkcja zwraca te same wartości, co poprzednia, z wyjątkiem sekund.
DZIEŃ()
Dzięki tej funkcji możesz sprawdzić numer dnia tygodnia, który jest używany w tej dacie. Możliwe wartości to od 1 do 7, ale pamiętaj, że odliczanie zaczyna się od niedzieli, a nie od poniedziałku, jak to zwykle robimy.
Jednak przy użyciu drugiego argumentu funkcja ta pozwala dostosować format. Na przykład, jeśli jako drugi parametr przekażesz wartość 2, możesz ustawić format tak, aby liczba 1 oznaczała poniedziałek, a nie niedzielę. Jest to znacznie wygodniejsze dla użytkownika domowego.
Jeśli w drugim argumencie wpiszemy 2, to w naszym przypadku funkcja zwróci wartość 6, co odpowiada soboty.
DZISIAJ()
Ta funkcja jest bardzo prosta: do jej działania nie są wymagane żadne argumenty. Zwraca numer seryjny daty ustawionej na komputerze. Jeśli zostanie zastosowany do komórki, dla której ustawiony jest format ogólny, zostanie automatycznie przekonwertowany na format „Data”.
TATA ()
Ta funkcja również nie wymaga żadnych argumentów. Działa tak samo jak poprzedni, tylko z datą i godziną. Jest używany, jeśli konieczne jest wstawienie do komórki aktualnej daty i godziny ustawionej w komputerze. I podobnie jak w poprzedniej funkcji, przy zastosowaniu tej, komórka jest automatycznie konwertowana na format daty i czasu, o ile wcześniej został ustawiony format „Ogólny”.
Zarówno poprzednia, jak i ta funkcja są automatycznie zmieniane przy każdym przeliczaniu arkusza, dzięki czemu możliwe jest wyświetlenie najbardziej aktualnego czasu i daty.
Na przykład taka formuła może określać aktualny czas.
=DZIŚ()-DZIŚ()
W takim przypadku formuła określi ułamek dnia w formacie dziesiętnym. To prawda, że będziesz musiał zastosować format czasu do komórki, w której zapisana jest formuła, jeśli chcesz wyświetlić dokładnie godzinę, a nie liczbę.
DATA()
Ta funkcja ma trzy argumenty, z których każdy należy wprowadzić. Po obliczeniach ta funkcja zwraca numer seryjny daty. Komórka jest automatycznie konwertowana na format „Data”, jeśli wcześniej miała format „Ogólny”.
Argument Dzień lub Miesiąc może być dodatni lub ujemny. W pierwszym przypadku data wzrasta, aw drugim maleje.
Możesz także użyć operacji matematycznych w argumentach funkcji DATA. Na przykład ta formuła dodaje 1 rok, 5 miesięcy i 17 dni do daty w komórce A1.
A taka formuła umożliwia przekształcenie ciągu tekstowego w pełnoprawną datę roboczą, którą można wykorzystać w innych funkcjach.
CZAS()
Podobnie jak funkcja DATA(), funkcja ta ma trzy wymagane parametry – godziny, minuty i sekundy. Po jego użyciu w wynikowej komórce pojawi się liczba dziesiętna, ale sama komórka zostanie sformatowana w formacie „Czas”, jeśli wcześniej miała format „Ogólny”.
Zgodnie z zasadą działania funkcja CZAS() и DATA() wiele podobnych rzeczy. Dlatego nie ma sensu się na tym skupiać.
Należy zauważyć, że ta funkcja nie może zwrócić czasu większego niż 23:59:59. Jeśli wynik jest większy niż ten, funkcja jest automatycznie resetowana do zera.
Funkcje DATA() и CZAS() można nakładać razem.
Na tym zrzucie ekranu komórka D1, która korzysta z obu tych funkcji, ma format daty i godziny.
Funkcje obliczania daty i czasu
W sumie dostępne są 4 funkcje, które umożliwiają wykonywanie operacji matematycznych na dacie i czasie.
DANYCH()
Korzystając z tej funkcji, możesz znaleźć numer porządkowy daty, która jest opóźniona o określoną liczbę miesięcy (lub przed daną). Ta funkcja przyjmuje dwa argumenty: datę rozpoczęcia i liczbę miesięcy. Drugi argument może być dodatni lub ujemny. Pierwszą opcję należy określić, jeśli chcesz obliczyć datę przyszłą, a drugą – jeśli poprzednią.
EOMIESIĄC()
Funkcja ta umożliwia określenie liczby porządkowej ostatniego dnia miesiąca poprzedzającego lub poprzedzającego daną datę. Ma takie same argumenty jak poprzedni.
DZIEŃ ROBOCZY()
Tak samo jak funkcja DANYCH(), tylko opóźnienie lub wyprzedzenie następuje o określoną liczbę dni roboczych. Składnia jest podobna.
Wszystkie trzy z tych funkcji zwracają liczbę. Aby zobaczyć datę, musisz przekonwertować komórkę do odpowiedniego formatu.
JASNE()
Ta prosta funkcja określa liczbę dni roboczych między datą 1 a datą 2.