Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Excel jak zliczyć ilość dni z zakresu dat pomijając nakrywające się

21 Lip 2014 12:48 2445 2
  • Poziom 2  
    Witam.
    Mam takie zadanie. Aby uzyskać informację czy dana osoba ma wymagane np. 5 lat doświadczenia, muszę zliczyć ilość dni (miesięcy) z podanego zakresu dat, pomijając te pokrywające się. Spróbowaliśmy opisać formułę, jak w załączniku w kolumnie K,L,M "obliczenia", jednak wydaje nam się, że jest błąd. Po naniesieniu dat na wykres są nadal okresy, które się nakładają. Może nasz tok myślenia jest błędny, dlatego proszę o pomoc.
  • Poziom 34  
    Dobry wieczór,
    Oczywiście, że w tak prosty sposób nie da się wyeliminować powtarzających się dni. Obliczenia muszą być bardziej złożone, ale być może jest prostszy sposób od tego, który ja wymyśliłem.
    Proponuję tak:
    1. Ustalamy zakres dat, który nas interesuje od najstarszej (MIN) do najpóźniejszej (MAX) i zapisujemy w komórkach np. P2 i Q2.
    2. Tworzymy listę numerów seryjnych dat tych dni od P2 do Q2. Ponieważ okres jest dość długi, niewygodnie byłoby umieszczać tę listę w arkuszu, więc umieścimy ją w pamięci jako formułę nazwaną dni według definicji:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    3. Tworzymy w pamięci dużą tablicę, której wiersze oznaczają dni z naszego zakresu, a każda kolumna reprezentuje jeden z okresów zatrudnienia. Jeżeli dany dzień należy do określonego okresu zatrudnienia w tablicy jest 1, jeśli nie to 0.
    Tablicę tę utworzymy za pomocą formuły tablicowej:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    Zakres K4:K19 to daty początkowe, a L4:L19 - daty końcowe okresów.
    Ponieważ te daty są w kolumnach, trzeba użyć funkcji TRANSPONUJ, aby znalazły się w wierszach i tablica przyjęła odpowiednią formę: pierwsza kolumna tablicy odpowiada datom z zakresu K4 do L4, ostatnia kolumna - datom z zakresu K19:L19.
    W niektórych wierszach tablicy może wystąpić kilka jedynek, a w innych same zera (jeśli osoba w tym dniu nie pracowała). Należy teraz ustalić, w ilu wierszach jest przynajmniej jedna jedynka. Nie można zastosować wprost funkcji SUMA, bo ona sumuje wszystko, a tu trzeba najpierw selektywnie zsumować wiersze.
    4. Aby uzyskać sumy w wierszach tablicy można wykorzystać mnożenie macierzowe (funkcja MACIERZ.ILOCZYN). Należy pomnożyć macierzowo naszą tablicę przez kolumnę złożoną z samych jedynek. Tych jedynek musi być tyle, ile kolumn tabeli, czyli tyle ile było wierszy w zakresach K4:K19 lub L4:L19. Ponieważ w tych zakresach były daty, czyli wartości <>0, więc można podnieść dane z tego zakresu do potęgi 0, aby uzyskać jedynki (wydaje się to trochę sztuczne, ale jest to najprostszy sposób uzyskania odpowiedniej liczby jedynek). Wynikiem mnożenia macierzowego jest kolumna liczb zawierająca liczby powtórzeń poszczególnych dni w okresach zatrudnienia.
    5. Należy teraz porównać poszczególne liczby z zerem lub jedynką (>0 lub >=1) i wyniki logiczne zamienić na liczby 0 lub 1 (*1,+0 lub podwójny minus) i wreszcie te zera i jedynki zesumować. Ostateczna formuła (tablicowa) wygląda tak:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    Formułę zatwierdzamy przez Ctrl+Shift+Enter.
    Zakresy z datami nie mogą zawierać błędów ani komórek pustych.
    6. Uprzedzając kolejne pytanie: co zrobić, gdy u różnych osób będzie różna liczba okresów zatrudnienia? - należy zakresy K4:K19 i L4:L19 zdynamizować zastępując je np. takimi formułami:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    Analogiczna formuła dla kolumny L.
    Proszę dopasować do swoich danych.
    Pozdrowienia.
  • Poziom 2  
    Dziękuję za tak szybką odpowiedź. Muszę troszkę przeanalizować te formuły, bo przyznam się, że jeszcze nie znam wszystkich użytych w tych obliczeniach. Pozdrawiam :)