W tym filmie przyjrzymy się, jak obliczyć i zaznaczyć daty ważności.
Załóżmy, że Twoja firma uruchomiła jakiś program członkowski, a Twój szef właśnie przesłał Ci zestaw danych. Dała ci listę 1000 osób, które odnowiły członkostwo w ciągu ostatniego roku i szuka kilku rzeczy.
Najpierw chce, abyś obliczył datę wygaśnięcia o rok do przodu, ostatniego dnia tego samego miesiąca, w którym zostało odnowione członkostwo.
Po drugie, chce sprawdzić, ile dni pozostało do wygaśnięcia.
Po trzecie, chce zobaczyć stan „Wygasłe” dla wszystkich członkostw, które już wygasły, oraz „Wkrótce wygaśnie” dla wszystkich, które wygasną w ciągu najbliższych 30 dni.
Wreszcie stwierdziła, że byłoby miło zobaczyć wygasłych członków podświetlonych na różowo, a tych, którzy wkrótce wygasną, podświetlonych na żółto.
A także - czy może to zjeść przed spotkaniem obiadowym w południe?
Umm, jasne.
Najpierw przekonwertujmy te dane na odpowiednią tabelę programu Excel. Ułatwi to wprowadzanie formuł, ponieważ program Excel automatycznie je kopiuje w miarę postępu.
Teraz obliczmy daty ważności. Mają się one pojawić pod koniec tego samego miesiąca rok później, ale najpierw użyjmy prostego hacka, aby dostać się na boisko. Jak widzieliście we wcześniejszych filmach, daty to tylko numery seryjne, więc możemy po prostu wprowadzić formułę, która dodaje 365 dni do daty odnowienia.
To dobry początek. Możemy skończyć zgrubnie opracowywać rozwiązanie i wrócić do tego później.
Podczas rozwiązywania bardziej złożonego problemu w programie Excel warto zweryfikować ogólne podejście, a następnie wrócić do szczegółów na koniec. Nie chcesz utknąć na małej rzeczy na samym początku, zwłaszcza jeśli podejście może się zmienić.
Teraz, gdy mamy datę wygaśnięcia, możemy teraz obliczyć pozostałe dni. To wymaga automatycznej aktualizacji w przyszłości, więc użyjemy funkcji DZIŚ, która zawsze zwraca dzisiejszą datę.
Formuła to po prostu E5 minus DZISIAJ (). Kiedy trafiam na powrót, mamy dni pozostałe do wygaśnięcia. Liczby ujemne wskazują, że członkostwo już wygasło.
W przypadku statusu użyjemy prostej zagnieżdżonej formuły JEŻELI. Jeśli pozostało mniej niż zero, członkostwo wygasa. W przeciwnym razie, jeśli pozostało mniej niż 30 dni, stan powinien mieć wartość „Wkrótce wygaśnie”. W przeciwnym razie status jest niczym.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Następnie musimy utworzyć reguły formatowania warunkowego, które wyróżnią te wartości.
Najpierw wybierz dane i ustaw aktywną komórkę w prawym górnym rogu. Następnie utwórz regułę formuły, która przetestuje aktywną komórkę pod kątem wartości „Wygasła”. Kolumna musi być zablokowana.
Teraz powtórz ten sam proces dla członkostwa, które „wkrótce wygasa”.
To wygląda dobrze. Musimy tylko ustalić daty wygaśnięcia, aby wylądować w ostatni dzień miesiąca.
Okazuje się, że istnieje fajna funkcja o nazwie EOMONTH (na koniec miesiąca), która pobiera ostatni dzień miesiąca w przeszłości lub w przyszłości.
Data rozpoczęcia to data odnowienia, a miesięcy to 12.
I masz to, wszystko, czego chciał twój szef, a wciąż masz czas na filiżankę kawy przed lunchem.
Kierunek
Podstawowa formułaPowiązane skróty
Wstaw tabelę Ctrl + T ⌃ + T Rozwiń lub zwiń wstążkę Ctrl + F1 ⌘ + ⌥ + R Wybierz tabelę Ctrl + A ⌘ + A Przesuń aktywną komórkę w prawo w zaznaczeniu Ctrl + . ⌃ + .
