Obliczanie ubezpieczenia MACIERZ ILOCZYN dodanie wierszy z macierzy sztuczki #30

Jak obliczyć sumaryczny koszt ubezpieczenia dla pracowników korzystając z obliczeń macierzy?

Zadaniem na dziś jest obliczenie sumy ubezpieczenie dla wszystkich pracowników w poszczególnych miesiącach. Warunkiem opłacenia ubezpieczenia jest przepracowanie przez pracownika ostatnich 3 miesięcy.

Obliczanie ubezpieczenia MACIERZ.ILOCZYN dodanie wierszy z macierzy sztuczki #30

Docelowo chcemy to zrobić w 1 komórce, ale na początek zrobimy to po kolei dla każdego pracownika, a potem zsumujemy wynik.

Żeby sprawdzić, czy dany pracownik pracował w ciągu ostatnich 3 miesięcy jest sprawdzenie jego pensji, jeśli jest większa od zera w danym miesiącu znaczy to, że pracował. 

Możemy wykorzystać funkcję LICZ.JEŻELI i sprawdzić za pomocą niej 3 poprzednie miesiące dla danego pracownika, 

=LICZ.JEŻELI(E2:G2;">0")=3

jeśli we wszystkie 3 pracował wynik będzie 3 i w takiej sytuacji ma zostać opłacone ubezpieczenie. Nadaje się to idealnie do funkcji JEŻELI, ponieważ wcześniejsza formuła zwraca wartość PRAWDA, albo FAŁSZ

=JEŻELI(LICZ.JEŻELI(E2:G2;">0")=3;$B$2;0)

Teraz wystarczy zsumować wartości dla wszystkich pracowników.

Jak zrobić to w 1 komórce? Przede wszystkim, trzeba wiedzieć jak mnoży się macierze i wykorzystać funkcje MACIERZ.ILOCZYN

Naszą 1 macierzą jest macierz z zarobkami z 3 poprzednich miesięcy dla 5 pracowników (5 wierszy i 3 kolumny), 2 macierzy nie znamy jeszcze, ale wiemy, że macierz wynikowa ma mieć 1 kolumnę i 5 wierszy, z czego wynika, że 2 macierz ma mieć 1 kolumnę i 3 wiersze (najlepiej wypełnione 1).

Naszą macierz wejściową z danymi, potrzebujemy zamienić na macierz z zerami i jedynkami, czyli sprawdzamy, czy pensje są większe od zera. Uzyskamy wtedy macierz

{PRAWDA\PRAWDA\PRAWDA;PRAWDA\PRAWDA\PRAWDA;FAŁSZ\FAŁSZ\FAŁSZ;
FAŁSZ\FAŁSZ\PRAWDA;FAŁSZ\FAŁSZ\FAŁSZ}

\ — oznacza kolejną kolumnę macierzy
; — oznacza kolejny wiersz macierzy

te wartości trzeba zamienić na liczby np: wykorzystując podwójną negację:

=–(E2:G6>0)

teraz możemy przemnożyć przez 2 macierz z samymi jedynkami. Możemy ją wpisać na stałe w formule. 

=MACIERZ.ILOCZYN(–(E2:G6>0);{1;1;1})

Da nam to podobny wynik do funkcji LICZ.JEŻELI, czyli macierz z 5 wierszami, które będą zawierać ilość miesięcy przepracowanych w sprawdzanym czasie:

{3;3;0;1;0}

Teraz wystarczy znaleźć 3 i wpisać wartość ubezpieczenia, a pozostałe pola wpisać 0. Można to łatwo uzyskać mnożąc formułę przez wartość ubezpieczenia ($B$2). Każda operacja matematyczna zamienia PRAWDA na 1, a FAŁSZ na 0. 

=(MACIERZ.ILOCZYN(–(E2:G6>0);{1;1;1})=3)*$B$2

Teraz, żeby poprawnie zsumować należy wykorzystać funkcję SUMA.ILOCZYNÓW:

=SUMA.ILOCZYNÓW((MACIERZ.ILOCZYN(–(E2:G6 większe od 0);{1;1;1})=3)*$B$2)

Teraz mamy formułę, która w 1 komórce robi to do czego wcześniej potrzebowaliśmy, aż 6 komórek.

P.S.

Wpis na podstawie Excel Magic Trick 1006

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Bezpośredni odnośnik do filmu na youtube — Obliczanie ubezpieczenia MACIERZ ILOCZYN dodanie wierszy z macierzy sztuczki #30