Jakiś czas temu jedna z czytelniczek bloga zapytała mnie, w jaki sposób obliczyć ile polis ubezpieczeniowych z jej listy jest aktywnych. O każdej polisie wiemy kiedy się zaczęła i jaka jest jej data ważności. Interesuje nas: ile polis na dany dzień (dziś) jest aktywnych? Pokazaną metodę możemy zastosować w milionie innych sytuacji: czy pracownik pracował w interesującym cię okresie, data ważności produktu/faktury (choć tutaj wystarczy tylko data do – zobacz tutaj), realizacja projektu w terminie itd…

Korci mnie, żeby od razu wyliczyć ile czasu zostało do przeterminowania polisy i żeby, jeśli termin jest bliski, na tej podstawie wyświetlać jakiś komunikat lub kolorować zbliżające się daty… Ale to w kolejnych wpisach 🙂

Oto formatka:

Formatka

W żółtej komórce B3 należy wpisać dzisiejszą datę (można ją też oczywiście zaszyć w formule, jednak myślę, że wrzucając ją do oddzielnej komórki, mamy więcej możliwości działania). Możemy to zrobić na 2 sposoby:

1. Skrótem klawiszowym Ctrl + ; (wtedy wpiszemy datę na sztywno)
2. Funkcją DZIŚ() (wtedy data będzie się codziennie aktualizowała), czyli formuła:

=DZIŚ()

Najprostszy sposób

Samo sprawdzenie, czy polisa jest jeszcze aktywna, polega na sprawdzeniu, czy dzisiejsza data mieści się w przedziale data od i data do. Jeśli się mieści – polisa jest aktywna, jeśli nie – nieaktywna. Logikę tego prezentuje poniższy rysunek:

Czyli nasza data (zaznaczona różową kreseczką na osi liczbowej), musi wpaść jednocześnie do 2 przedziałów, czyli spełnić 2 warunki. Musi być:

1. większa lub równa od daty od i
2. mniejsza lub równa od daty do

Aby sprawdzić te 2 warunki, użyjemy funkcji ORAZ i JEŻELI. Do komórki D8 wpiszmy następującą formułę:

=JEŻELI(ORAZ(B8<=$B$3;C8>=$B$3);1;0)

Czyli, jeśli polisa jest aktywna – otrzymamy 1, jeśli jest nieaktywna – 0. Policzenie aktywnych polis będzie teraz bardzo proste – zwykła funkcja SUMA w komórce D6:

=SUMA(D8:D10)

A poniżej wersja wideo, przedstawiająca opisaną wyżej metodę:

Alternatywne sposoby

Liczbę aktywnych polis można oczywiście policzyć na wiele innych sposobów. Omówię tutaj 2, które na szybko przychodzą mi do głowy. W pierwszym użyję funkcji SUMA.WARUNKÓW, w drugim – SUMA.ILOCZYNÓW.

Sposób 1, w komórce C5:

=SUMA.WARUNKÓW(D8:D10;B8:B10;"<="&B3;C8:C10;">="&B3)

Sposób 1, w komórce C6:

=SUMA.ILOCZYNÓW(--(B8:B10<=$B$3);--(C8:C10>=$B$3))

Mi się bardziej podoba sposób 2, ponieważ jest w nim mniej pisania ;), ale szybciej zadziała nam sposób 1. Choć i tak najbardziej do gusty przypadł mi sposób najprostszy, czyli ten opisany na początku. Myślę, że daje on nam najwięcej informacji. Oprócz samej sumy aktywnych polis, możemy w nim dowiedzieć się, które polisy są jeszcze aktywne, a jestem przekonana, że to będzie następne pytanie 🙂 Dwa kolejne sposoby tego nie mówią – informują nas jedynie o sumie. Mało rozwojowo 😉

To tyle w temacie badania, czy jakaś data mieści się w przedziale. Jeśli macie jakieś jeszcze pomysły, kiedy można użyć tego sposobu – dajcie znać w komentarzach, żeby inni też mogli to wykorzystać.