• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

Funkcja PRZESUNIĘCIE pomaga liczyć podsumy

Ostatnio napisał do mnie Radek z ciekawym pytaniem. Miał on bardzo dużą, dość specyficznie zbudowaną tabelę, w której chciał zastosować podsumowania części danych (coś á la sumy częściowe). Na koniec chciał mieć sumę wszystkiego. Radek się zastanawiał, czy da się to zrobić za pomocą jednej formuły. Przyznam, że rozwiązanie do oczywistych nie należy i jest bardzo ciekawe, bo zawiera funkcję PRZESUNIĘCIE…

Formatka wygląda tak:

Przesunięcie formatka

Formatka

W żółtej komórce F3 ma się znaleźć suma wszystkich wartości, a te wartości mają być takie:

  1. Tam, gdzie są szare pola, a w kolumnie B jednostką jest „ryczałt”, należy podsumować wszystkie dane pod tym ryczałtem (aż do następnego ryczałtu),
  2. Tam, gdzie jednostka jest inna niż „ryczałt”, należy pomnożyć wartości z kolumn C i D.

W kolumnie E są formuły wstawione ręcznie w odpowiednie komórki, które sumują tam, gdzie jest „ryczałt”, a w pozostałe komórki wstawione jest proste mnożenie (plik z rozwiązaniem znajdziesz na końcu artykułu). Sam przyznasz, że jest to mrówcza praca, a w całym ćwiczeniu chodzi o to, aby użyć jednej formuły. Do dzieła! 🙂

Formuła z funkcją PRZESUNIĘCIE

Nasze zadanie jest więc takie: w żółtych komórkach F4:F21 należy wpisać jedną formułę, która – gdy napotka jednostkę = „ryczałt” – podsumuje wartości z następnych komórek aż do kolejnego słowa „ryczałt”. W innym przypadku pomnoży wartości z komórek obok (kolumny C i D).

Formuła będzie dość skomplikowana. Wykorzystam w niej aż 6 funkcji: JEŻELI,  SUMA, PRZESUNIĘCIE, JEŻELI.BŁĄD, PODAJ.POZYCJĘ i ILE.NIEPUSTYCH.

Funkcja JEŻELI sprawdzi, czy jednostka jest słowem „ryczałt”. Jeśli tak – będzie sumowała komórki poniżej, jeśli nie – pomnoży wartości z komórek obok. Do określenia zakresu komórek „poniżej” posłużą funkcje PRZESUNIĘCIE, JEŻELI.BŁĄD, PODAJ.POZYCJĘ i ILE.NIEPUSTYCH. Funkcja PRZESUNIĘCIE zwróci nam adres zakresu. Zakres ten będzie ustalony dzięki trzem pozostałym funkcjom.

Zobaczcie formułę:

=JEŻELI(B4="ryczałt";SUMA(PRZESUNIĘCIE(F5;0;0;JEŻELI.BŁĄD(PODAJ.POZYCJĘ("ryczałt";B5:$B$21;0)-1;ILE.NIEPUSTYCH(B5:$B$21));1));C4*D4)));C4*D4)

Najtrudniej jest ustalić wysokość zakresu sumowania, czyli czwarty argument funkcji PRZESUNIĘCIE. Wysokość ta jest liczbą komórek pod wierszem, w którym aktualnie jesteśmy, do najbliższego wystąpienia kolejnego „ryczałtu”. Najłatwiej jest to określić funkcją PODAJ.POZYCJĘ, która ma sprawdzić, gdzie jest „ryczałt” w zakresie od komórki poniżej wiersza z formułą do końca zakresu. Funkcja PODAJ.POZYCJĘ zwróci pozycję pierwszego napotkanego „ryczałtu”, czyli dokładnie to, o co nam chodzi. A ponieważ chcemy, aby wysokość zakresu sumowania kończyła się tuż przed napotkaniem słowa „ryczałt”, od wyniku PODAJ.POZYCJĘ odejmujemy 1.

I tak naprawdę to nam prawie rozwiązuje problem. Nie zakłada jednak opcji, że zakres się kiedyś skończy i to nie słowem „ryczałt”. W takiej sytuacji, PODAJ.POZYCJĘ zwróci błąd. Żeby się przed tym zabezpieczyć, wrzucimy wszystko do funkcji JEŻELI.BŁĄD i jako jej drugi argument, czyli co ma się stać w przypadku błędu, wrzucamy funkcję ILE.NIEPUSTYCH, która po prostu policzy wysokość zakresu jako liczbę niepustych komórek z jednostkami. Dokładnie to w takim przypadku będzie wysokość sumowanego zakresu.

Megaformuła, ale działa. 🙂

Małego komentarza wymaga również dynamiczny zakres B5:$B$21. Jest on po to, aby ustalać zakres sumowania – ma on się zaczynać zawsze pod wierszem, w którym znajduje się formuła. Dynamikę tego zakresu osiągamy zwykłym adresowaniem. Mówimy w ten sposób Excelowi, że ma zmieniać pierwszą komórkę zakresu (adres względny to brak dolarów), ale zostawić w spokoju ostatnią (adres bezwzględny to dwa dolary). Dzięki temu uzyskujemy efekt dynamicznego zakresu.

Dobrze widać to na obrazkach (fioletowy zakres) – zakres się zmniejsza wraz z przesuwaniem formuły w dół:

Przesunięcie: Dynamiczny zakres 1

Dynamiczny zakres 1

Przesunięcie: Dynamiczny zakres 1

Dynamiczny zakres 2

Formuła licząca TOTAL

Na koniec już najłatwiejsze, czyli podsumowanie wszystkiego. To zrobimy już zwykłą funkcją SUMA.JEŻELI. Kryterium sumowania, będzie oczywiście słowo „ryczałt”. Formuła w komórce F3 wygląda więc tak:

=SUMA.JEŻELI(B4:B21;"ryczałt";F4:F21)

Nic tutaj nie trzeba blokować, ponieważ opisywana funkcja jest tylko w tym jednym miejscu.

A oto efekt końcowy:

MalinowyExcel Przesunięcie Wynik

Wynik

Mam nadzieję, że podany przykład okaże się przydatny w tej czy zbliżonej formie. Zachęcam też do udostępnienia tego artykułu znajomym, za co będę bardzo wdzięczna.

I wersja wideo:

 

Redakcja językowa: Aleksandra Wasiak
www.kulturajezyka.pl

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o najnowszych wpisach (i tylko powiadomienia: zero spamu).
Dodatkowo otrzymasz DARMOWY EBOOK pt.:

10 najprzydatniejszych porad excelowych


Tagi , , , , , , , .Dodaj do zakładek Link.

2 odpowiedzi na „Funkcja PRZESUNIĘCIE pomaga liczyć podsumy

  1. Radek mówi:

    Malina, jesteś niesamowita!
    Dokładnie to miałem na myśli. Straciłem na sumowanie ilości cały dzień, po czym okazało się że i tak się pomyliłem w jednym zakresie…
    Baaardzo dziękuję za pomoc w rozwiązaniu problemu!
    Mam nadzieję że ten wpis pomoże większej ilości osób 😉
    Wbrew obawom – formuła nie jest tak obciążająca jak inne których używam, a zdecydowanie usprawnia cały proces 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *