0
0 Produkty w koszyku

No products in the cart.

SUMA.ILOCZYNÓW mnożenie filtrowanych kolumn lub kolumn z ukrytymi wierszami — sztuczki #34

Jak pominąć przefiltrowane i ukryte wiersze w obliczeniach?

Zobacz, jak się pomnożyć, a następnie dodać dwie kolumny, z filtrem lub ukrytymi wierszami korzystając z funkcji SUMY.CZĘŚCIOWE, PRZESUNIĘCIE i funkcji SUMA.ILOCZYNÓW:


SUMA.ILOCZYNÓW mnożenie filtrowanych kolumn lub kolumn z ukrytymi wierszami — sztuczki #34

      1. Funkcja PRZESUNIĘCIE wewnątrz funkcji SUMY.CZĘŚCIOWE utworzy filtrowaną kolumnę wartości (wiersze ukryte) 
      2. SUMA.ILOCZYNÓW pomnoży dwie filtrowane kolumny 
      3. PRZESUNIĘCIE funkcja z argumentem podanym jako tablica 
      4. Stała tablica 
      5. WIERSZ funkcja do tworzenia tablicy wartości 

P.S.

Wpis na podstawie Excel Magic Trick 1010

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — SUMA.ILOCZYNÓW mnożenie filtrowanych kolumn lub kolumn z ukrytymi wierszami — sztuczki #34

Wyszukaj 3 macierzy z 3 tabel WYSZUKAJ PIONOWO WYBIERZ INDEKS — sztuczki #33

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 WYSZUKAJ PIONOWO WYBIERZ INDEKS — 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

P.S.

Wpis na podstawie Excel Magic Trick 1009

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Wyszukaj 3 macierzy z 3 tabel WYSZUKAJ PIONOWO WYBIERZ INDEKS — sztuczki #33

Funkcja SPŁAC.ODS Porównaj odsetki od pożyczki spłacanej 2 razy i raz w miesiącu — sztuczki #32

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

Link do serii filmów o tabelach amortyzujących po angielsku:
https://www.youtube.com/course?list=EC1C040B9C3D6B319B

P.S.

Wpis na podstawie Excel Magic Trick 1008

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Funkcja SPŁAC.ODS Porównaj odsetki od pożyczki spłacanej 2 razy i raz w miesiącu — sztuczki #32

Pożyczka PMT opłata 2 razy w miesiącu kilka sztuczek z finansami i datami — sztuczki #31

Jak obliczyć należną ratę za pożyczkę?

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ń 🙂

P.S.

Wpis na podstawie Excel Magic Trick 1007

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Excel Pożyczka PMT opłata 2 razy w miesiącu kilka sztuczek z finansami i datami sztuczki #31

Podział długiej listy na mniejsze — widzowie #9

Jak podzielić długą listę na wiele małych list?

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

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

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)

=INDEKS($A$1:$A$100000;WIERSZ(A1)+(NR.KOLUMNY(A1)-1)*100)

Prawidłowa formuła dla przykładowej listy

Prawidłowa formuła dla przykładowej listy

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Podział długiej listy na mniejsze — widzowie #9