Jak wyszukać i przemnożyć przez siebie 3 macierze z 3 tabel?
Zobacz formułę, która będzie wyszukiwać trzy macierze (1 x 4), a następnie pomnożyć dwie pierwsze macierze i wynik podzielić przez trzecią macierz:
Wyszukaj 3 macierzy z 3 tabel WYSZUKAJPIONOWOWYBIERZINDEKS — sztuczki #33
1. WYSZUKAJ.PIONOWO do wyszukiwania wielu kolumn przy użyciu stałej tablicy oraz argument funkcji umożliwiający pracę na tablicach
2. WYBIERZ funkcja do wyszukiwania jednej z trzech tablic stałych
3. INDEKS i funkcja PODAJ.POZYCJĘ do wyszukiwania w wartości w jednym z czterech wiersz
4. SUMA.ILOCZYNÓW funkcja do zebrania wszystkie trzech "wyszukanych tablic", a następnie pomnożenia i podzielenia ich, aby uzyskać ostateczny wynik.
5. WYSZUKAJ.PIONOWO, WYBIERZ i funkcja INDEKS wszystkie mogą zwracać tablice
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.
Jak obliczyć sumę należnych odsetek za pomocą funkcji SPŁAC.ODS?
Dowiedz się, jak użyć funkcji SPŁAC.ODS do porównania całkowitej wartości odsetek zapłaconych od 2 opcji kredytowych — płacenia raz lub 2 razy w miesiącu.
Funkcja SPŁAC.ODS Porównaj odsetki od pożyczki spłacanej 2 razy i raz w miesiącu — sztuczki #32
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.
Zacznijmy od tego, że chcesz wyliczyć ile wyniesie każda rata spłaty pożyczki w sytuacji kiedy masz aż 2 płatności w 1 miesiącu.
Excel Pożyczka PMT opłata 2 razy w miesiącu kilka sztuczek z finansami i datami sztuczki #31
Do tego należy wykorzystać funkcję PMT, po polsku ciężko ją skojarzyć, ale po angielsku to jest Payment i poszczególne litery pasują.
Do funkcji PMT potrzebujesz stopy procentowej, tylko nie rocznej, a takiej jaka przypada na dokładnie 1 okres płatności. Jeśli masz stopę roczną i ilość płatności w roku, wystarczy je podzielić (B2/B4).
Następnie potrzebujesz liczbę płatności w roku, to jest proste (B4).
Do danych wymaganych należy jeszcze wartość początkowa pożyczki (argument wa funkcji PMT). Tutaj zwróć uwagę, z której strony patrzysz na pożyczkę, czy ze strony banku czy pożyczkobiorcy. Od tego będzie zależał znak przy pożyczce i później analogicznie znak przy jej spłacaniu (-B1)
Pozostałe argumenty są opcjonalne:
wp — czyli wartość końcowa pożyczki, domyślnie 0.
typ — 0 albo 1 która wskazuje, kiedy płatność jest należna. 0 (wartość domyślna) na koniec okresu, 1 na początek.
Teraz chcemy wypisać wszystkie okresy płatności (daty). Mamy 2 możliwości, albo napiszemy je ręcznie wykorzystując sztuczki excela, by było szybko (daty będą wpisane na stałe), albo połączymy formułami z wartością początkową i uzyskamy dynamiczne daty.
Jeśli skopiujesz datę początkową, a następnie przeciągniesz ją w dół, to będzie się zwiększać tylko o 1 dzień, ale jeśli rozwiniesz pomocne menu przy kopiowaniu w dół, znajdziesz tam opcję wypełnienia miesiącami. Dzięki temu uzyskasz np: 15 dzień każdego miesiąca.
Jeśli chcesz uzyskać ostatni dzień miesiąca postępujesz analogicznie. Najpierw wpisujesz pierwszy ostatni dzień, następnie kopiujesz i wybierasz Wypełnij miesiącami.
Jeśli chcemy skorzystać z formuł, to do pierwszej komórki wpisujemy, że równa się wartości początkowej (=B5), a następnie wykorzystujemy funkcję
=NR.SER.DATY(A9;1)
by każda następna data była większa o dokładnie 1 miesiąc (1 jako drugi argument).
Żeby uzyskać daty z końca miesiąca wykorzystamy odwołanie do dat z 15 dniem miesiąca i odpowiednią funkcję:
=NR.SER.OST.DN.MIES(A9;0)
Drugi argument to 0, co oznacza, że chcemy ostatni dzień tego samego miesiąca co data początkowa, jeśli wpiszesz np: 1 to będzie to ostatni dzień następnego miesiąca, a ‑1 da Ci ostatni dzień poprzedniego miesiąca.
Daty zapisane formułami będą się zmieniać odpowiednio do ustawionej przez Ciebie daty początkowej, a daty wpisane przez wypełnienie pozostaną stałe.
Nasze dane są w 2 kolumnach (by te same miesiące były w tych samych wierszach),
trzeba je wypełnić wynikiem funkcji PMT, żeby zrobić to w jednym podejściu należy zaznaczyć 2 niesąsiadujące ze sobą obszary, żeby to zrobić wystarczy przytrzymać klawisz Ctrl, po zaznaczenie 1 obszaru, a przed zaznaczeniem drugiego. Teraz wpisujesz, że =$B$6 i naciskasz Ctrl + Enter, dzięki temu ta formuła wpisywana jest we wszystkie zaznaczone komórki.
Aby zsumować wszystkie płatności wykorzystamy funkcję SUMA i tu też analogicznie zaznaczamy 2 obszary w odpowiednim momencie przytrzymując Ctrl. Zwróć uwagę, że w funkcji niesąsiadujące obszary zostały rozdzielone średnikiem (;). Powstały w ten sposób 2 argumenty dla funkcji z 2 obszarów.
=SUMA(D9:D20;B9:B20)
Żeby wyliczyć sumę odsetek wystarczy, od przed chwilom obliczonej wartości odjąć wartość początkową pożyczki.
Na koniec jeszcze chcemy rozpisać całą pożyczkę na poszczególne okresy, ile wpłaciliśmy, ile spłaciliśmy odsetek, o ile zmniejszyliśmy balans pożyczki i jaki on aktualnie jest.
Wpłacamy zawsze taką samą wartość (wynik PMT).
Następnie żeby obliczyć wartość spłaconych odsetek mnożymy wartość bilansu z końca wcześniejszego okresu razu stopę procentową 1 okresu, czyli stopa_roczna/ilość_okresów_w_roku:
=E27*$B$2/$B$4
Wartość o jaką zmniejszyliśmy bilans uzyskujemy odejmując od PMT wartość spłaconych odsetek:
=B28-C28
a aktualny bilans uzyskujemy odejmując powyższą wartość od bilansu z wcześniejszego okresu.
=E27-D28
Z tych obliczeń możemy sobie sprawdzić, czy wcześniej poprawnie obliczyliśmy sumę odsetek.
Jak wielkie jest nasze zdziwienie kiedy okazuje się, że dokonaliśmy poprawnych obliczeń 🙂
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.
Dostałem pytanie, czy istnieje funkcja, którą da się przekształcić długą listę (tak 100 000 elementów) na mniejsze listy każda w osobnej kolumnie.
Wielka lista na małe listy
Podział długiej listy na mniejsze — widzowie #9
Pojedynczej funkcji nie znam, ale udało mi się stworzyć prostą formułę.
Zaczynamy od funkcji INDEKS. Pozwala ona łatwo wybrać element z listy, trzeba tylko podać odpowiedni numer tego elementu i to jest tutaj najtrudniejszą częścią, żeby dobrze zapisać kolejne kolumny.
Zacznijmy od numeru wiersza, czyli wykorzystuję funkcję WIERSZ:
=INDEKS($A$1:$A$100000;WIERSZ(A1))
Ta formuła zwróci 1 element z długiej listy, ponieważ odwołuje się do komórki (A1), która jest w pierwszym wierszu. Jak będziesz ją przeciągać w dół, to będzie się odpowiednio zwiększał numer wiersza i będą zwracane kolejne elementy, 2, 3, itd.
Wybieranie elementów do list tylko z funkcją WIERSZ
Problem polega na tym, że jak będę ją kopiował do kolejnych kolumn nie będzie się zmieniać lista elementów, które będą zwracane, dlatego przyda się liczenie kolumn. Tu też można zacząć od komórki A1. Funkcja NR.KOLUMNY zwróci dla tej komórki wartość 1, a jak będziesz tą formułę przesuwał w prawo będzie ta wartość będzie rosła, wraz z numerem kolumny:
kolumna B — 2
kolumna C — 3
itd.
Ponieważ w pierwszej kolumnie nowych list nie chce mieć jeszcze przesunięcia, to potrzebuje odjąć jedynkę, a wynik odejmowania pomnożyć przez wielkość list (np: 100)
Teraz tą formułę wystarczy przeciągnąć na odpowiedni obszar w dół i w prawo. Dla całej też dużej listy, będzie to obszar C1:ALN100
P.S.
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.
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.
Obliczenie sumy ubezpieczenia korzystając z 6 komórek
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).
Zasady mnożenia macierzy
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
\ — 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.
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.
Jak znaleźć k‑ty duplikat po nieposortowanej liście?
Żeby znaleźć k‑ty duplikat przy nieposortowanej liście możesz wykorzystać przynajmiej 2 drogi. Pierwsza wykorzystuje formuły tablicowe, a druga funkcje, które radzą sobie z tablicami.
Znajdź k‑ty duplikat przy nieposortowanej liście — sztuczki #29
1 rozwiązanie (formuły tablicowe)
W pierwszej kolejności trzeba znaleźć relatywne pozycje poszczególnych wierszy. Można to zrobić wykorzystując np taką formułę:
=WIERSZ(A2:A13)-WIERSZ(A2)+1
Czyli od numeru poszczególnych wierszy odejmujesz numer pierwszego wiersza, a następnie dodajesz jedynkę, żeby pozycje zaczynały się liczyć od 1.
Następnie przyda się sprawdzenie, gdzie znajduje się duplikat, którego aktualnie szukasz:
=A2:A13=E2
A żeby było lepiej to możesz wykorzystać do znalezienia tylko tych pozycji relatywnych wierszy, gdzie znajdują się nasze duplikaty. Przyda się nam do tego funkcja JEŻELI, którą skonstruuje się tak, żeby podawała relatywną pozycję wiersza, tylko dla szukanych duplikatów (konkretnego wyrażenia), a dla reszty zwracała fałsz:
Tylko kilka liczb i dużo wyrażenia FAŁSZ. Teraz gdy wykorzystasz funkcję MIN.K podając jej tą listę i numer k‑tego duplikatu uzyskasz numer wiersza, gdzie znajduje się szukany duplikat.
i oczywiście pamiętaj zatwierdzić ją Ctrl + Shift + Enter , bo to formuła tablicowa.
1 rozwiązanie — formuła tablicowa
2 rozwiązanie (funkcja AGREGUJ)
W tym rozwiązaniu zostanie wykorzystana funkcja AGREGUJ, z parametrami, które pozwolą jej działać na tablicach. Przydadzą nam się też pierwsze wyliczenia z wcześniejszego rozwiązania. Tym razem nie wykorzystamy dla nich funkcji JEŻELI, tylko odpowiednio je podzielimy:
=(WIERSZ(A2:A13)-WIERSZ(A2)+1)/(A2:A13=E2)
Ponieważ część druga tego równania może być zerem (FAŁSZ w działaniach matematycznych zamienia się na 0), wystąpi błąd dzielenia przez zero — #DZIEL/0! dlatego 2 parametr funkcji AGREGUJ ustawiamy na 6, by te błędy ignorował. 1 parametr to znana już wcześniej funkcja MIN.K (numer 15), która radzi sobie z tablicami.
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.