Analiza czasu w Excelu – 3 popularne scenariusze

Jakiś czas temu pisałem o pomocnych funkcjach w Exelu przy analizie daty i czasu. Tym razem skupię się na kilku dodatkowych rzeczach i zobaczymy, jak wygląda analiza czasu w Excelu. Ogólnie świat na czasie stoi 😊 więc wcześniej czy późnej powinno się przydać 😊 Zobaczymy zatem, jak „wyjmować” czas z komórek, jak radzić sobie z odejmowaniem/dodawaniem określonych przedziałów czasu (godzina,minuta, sekunda). A na koniec pokażę, jak za pomocą funkcji logicznych ORAZ i LUB sprawdzić, czy dana godzina znajduje się w określonym przedziale czasowym. Zapraszam do lektury!      

Czas, czas, czas

Jeśli jesteś tu pierwszy raz, to zapraszam również do zapoznania się z wpisem nt. przydatnych funkcji do analizy daty i czasu. (link). Szczególnie zwróć proszę uwagę na to, czy czas jest czasem również dla Excela. W przypadku problemów, trzeba będzie sprawdzić ustawienia regionalne. Piszę o tym więcej we wspomnianym powyżej wpisie.

A co dziś na tapecie? W tym artykule koncentruję się na kilku popularnych zagadnieniach, jeśli chodzi o pracę wyłącznie z czasem. Pokażę przydatne funkcje oraz pewne kombinacje.

Dla ułatwienia załączam też plik, gdzie znajdziecie poniższe przykłady, także bawcie się dobrze 😊 Przy okazji zachęcam również do zapisu na newsletter (na końcu artykułu).

MOD czyli „odłącz” czas 😊

Pierwsza z dzisiejszych funkcji zasadniczo nie dotyczy stricte jednostek czasowych, ale jak najbardziej pomaga w analizie czasu w Excelu. Otóż zdarza się, że mamy komórki gdzie występuje data wraz z czasem:

Natomiast my potrzebujemy tylko czas. Tu z pomocą przychodzi funkcja MOD. Co robi? Zwraca ona wartość reszty po podzieleniu liczby przez dzielnik. Posiada zatem tylko 2 argumenty: liczbę i dzielnik. W naszym przypadku jednak „zrozumiemy” ją trochę inaczej. Pierwszy argument będzie to komórka z datą. Drugi zaś to dzielnik, który na te potrzeby przyjmie wartość „1”. W przypadku typowego („nie-czasowego”) użycia tej funkcji czyli dla liczb, dzielnik wpływałby na rezultat końcowy. Tu jednak nie może być zerem, reszta zadziała tak samo, więc można założyć, że wstawiamy zawsze „1”.

Wynik będzie jak poniżej. Oczywiście, samą komórkę z czasem możemy dowolnie formatować, żeby otrzymać pożądaną postać godziny 😉

Pamiętajcie również, aby komórkę sformatować jako czas, inaczej rezultat może być nieco pokraczny 😉

Innym razem mamy sytuację, jak poniżej, gdzie na pierwszy rzut oka widać tylko datę. U góry na pasku formuły widać jednak, że mamy datę jak i czas. Wówczas funkcja również zadziała, chociaż może wydawać się, ze czas bierzemy „ znikąd ” 😉

CZAS czyli Operacje na czasie (dodawanie/odejmowanie)

Jedną z najbardziej popularnych operacji przy analizie czasu w Excelu jest odejmowanie i dodawanie różnych jednostek czasu. Jeśli mamy dwie różne godziny to sprawa jest dość prosta. Nieco bardzo komplikuje się sytuacja, jeśli chcemy dodać określoną „część” czasu, jak np. jedna godzina czy 20 min. W tym przypadku z pomocą przychodzi formuła CZAS.

Jak widać poniżej, przyjmuje ona trzy argumenty, które odpowiednio oznaczają godziny, minuty, sekundy. W ten sposób możemy „zbudować” określoną ilość czasu, którą chcemy np. dodać lub odjąć.

I tak przykładowo poniższe wartości argumentów (1,0,0) oznaczają 1 godzinę, 0 minut, 0 sekund.

Zatem jeśli wykonamy poniższą operacje w kolumnie C (A + B), to uzyskamy wynik, jak poniżej.

W ten sposób możemy analogicznie zbudować bardziej precyzyjnie czas używając także minut i sekund. Pozostałe przykłady widać w kolejnych dwóch wierszach.

Tu jeszcze mały tip 😉 Czas, który w ten sposób konstruujemy możemy również sformatować jako godzinę. Wystarczy wejść do ustawień formatowania komórki i wybrać jakiś format czasu w sensie godziny:

I dostajemy godzinę:

Operacje na czasie (dodawanie/odejmowanie) – alternatywna formuła

Te same rezultaty można uzyskać jeszcze inaczej. Zobaczmy zatem na pewną alternatywę, jeśli chodzi o analizę czasu w Excelu. Możemy bowiem posłużyć się zwykłymi operacjami dzielenia odpowiadającymi jednostkom czasu (np.1/24 to jedna godzina). W tym przypadku czas do dodania z poniższego przykładu (kolumna B), to wynik prostego dzielenia. Pewnym „mankamentem” jest nieco dziwna liczba widoczna w komórce B2. Natomiast z drugiej strony może to być bardziej przydatna metoda, jeśli czas który dodajemy/odejmujemy jest wynikiem matematycznej operacji w innych komórkach.

Teraz pewnie zapytacie, jak zapisać minuty lub sekundy ? :)Tu potrzeba pewnych innych operacji „dzielenia”. Przykładowo jedna minuta to 1/1440, gdyż w ciągu dnia mamy 1440 minut (24 godziny x 60 minut). Idąc dalej mamy 86400 sekund, a więc jedną sekundę zapiszemy jako 1/86400.

Jeśli chcemy zapisać w jednej komórce np. jedną godzinę i jedną sekundę, to będzie to wyglądać jak poniżej. Można też je obliczyć w innych komórkach i później dodać wynik.

W przypadku jednej godziny, jednej minuty i jednej sekundy będzie to wyglądać w ten sposób.

Sprawdź przedział czasu za pomocą ORAZ i LUB

Analiza czasu w Excelu to także weryfikacja określonych kryteriów. Kolejnym popularnym zdarzeniem jest sytuacja, gdzie sprawdzamy, czy dana godzina „wpada” w określony przedział czasowy. W takim przypadku możemy zastosować kombinacje logiczne i funkcje ORAZ a także LUB. W poniższym przykładzie dodatkowo zastosuję funkcję JEŻELI, aby wynik przybierał formę w zależności do tego, czy warunek został spełniony. Uff…łatwiej to pokazać na przykładzie 😊

ORAZ i LUB jak działają?

Krótka informacja na temat obu funkcji. Przyjmują one od jednego do wielu argumentów. Każdy jest oddzielnym sprawdzeniem, czy warunek logiczny jest spełniony. Następnie na końcu funkcja zwraca jeden z dwóch wyników: PRAWDA lub FAŁSZ. Zasadnicza różnica pomiędzy LUB a ORAZ polega na tym, że w przypadku LUB wystarczy, aby jeden warunek był prawdziwy, aby cały wynik był PRAWDĄ. W przypadku ORAZ wszystkie argumenty logiczne wewnątrz funkcji muszą być PRAWDĄ.

Przykładowo, poniższa funkcja ORAZ:

zwróci wynik PRAWDA

Jest tak dlatego, że 5 jest jednocześnie większe od 0 ale mniejsze od 10

Natomiast poniża funkcja pokaże już FAŁSZ, gdyż dodatkowy argument jest fałszywy (5 nie jest większe od 100), a wszystkie muszą być PRAWDĄ.

Te same warunki logiczne zwrócą jednak PRAWDĘ jako wynik końcowy, jeśli zastosujemy funkcję LUB, gdyż w tym przypadku wystarczy, aby co najmniej jeden warunek był prawdziwy.

Mam nadzieję, że to wystarczy przed przykładem 😊

Sprawdzamy przedział za pomocą ORAZ i LUB

A teraz zobaczmy, jak wygląda analiza czasu w Excelu dla tych funkcji w praktyce. Weźmy przypadek, gdzie mamy dwie taryfy w ciągu dnia. Jedna zaczyna się o północy i trwa do 8:00 rano (zasadniczo do 7:59:59), a druga od 8:00 rano do końca dnia.

Następnie mamy godziny rozpoczęcia (pracy lub czegokolwiek 😊) i chcemy zobaczyć, czy ta godzina zaczyna się w taryfie 1 czy taryfie 2.

Moglibyśmy to rozpisać, jak poniżej w kolumnie D. Nasz warunek logiczny to spełnienie JEDNOCZEŚNIE dwóch warunków. Godzina startu (kolumna A) musi być jednocześnie powyżej północy( >B1) i poniżej godziny 8:00 rano (<B2). W tym wypadku funkcja ORAZ zwraca wynik FAŁSZ lub PRAWDA. Dodatkowo użyłem funkcji JEŻELI, która w przypadku, jeśli funkcja ORAZ zwróci PRAWDĘ (oba kryteria spełnione) to końcowy wynik będzie „taryfa 1”. Każda inna opcja to „taryfa 2”.

ORAZ i LUB

Pewnym skomplikowaniem, ale jak najbardziej życiowym, będzie dodanie więcej przedziałów. Jak w takim przypadku wyglądałaby analiza czasu w Excelu? W scenariuszu poniżej dodałem dodatkowy godzinny przedział dla taryfy 1. Czyli teraz każdy start pomiędzy północą a godziną 8:00 rano LUB każdy start pomiędzy godziną 9:00 a 10:00 wieczorem oznacza wystąpienie taryfy 1. Aby sprawdzić taki warunek wprowadzam funkcję logiczną LUB. Oznacza ona, że wystarczy aby jeden z warunków ORAZ był prawdziwy, a całość będzię PRAWDĄ, co w konswekwencji będzie oznaczać, że wystąpi „taryfa1”.

W tym wypadku możemy powyższą funkcję „przetłumaczyć” na zdanie, jak: „Sprawdź czy godzina startu wpada w przynajmniej jeden z przedziałów czasowych”. Jeśli jakikolwiek z nich jest PRAWDĄ to całość jest PRAWDĄ i wówczas cała funkcja zwraca wartość „tarfya1”.

Początkowo może wydawać się to trochę zawiłe, ale praktyka czyni mistrza 🙂

To tyle tym razem. Życzę wytrwałości i powodzenia w czasowych zmaganiach 🙂 a w razie pytań piszcie i komentujcie. I dzielcię się ze światem! 🙂

Pozdrawiam,

Michał