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

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

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

  1. Radek komentarz:

    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 🙂

  2. sufferwell1 komentarz:

    Cześć!
    Od kopię powyższy temat :).
    Chciałem wykorzystać formułę stworzona przez Ciebie jednak co w przypadku, jeżeli nie mam w podsumowaniu zawsze tego samego słowa (w twoim przypadku “ryczalt”) tylko do każdego rodzaju wykonanych robót jest przyporządkowane inne nazwisko pracownika.
    Wtedy nie mogę wykorzystać funkcji podaj.pozycję do określenia szerokości zakresu. Proszę o pomoc 🙂

    • Malina komentarz:

      Hmmm… to już trudniej będzie. A może w ogóle pójść na łatwiznę i skorzystać z tabeli przestawnej? Ona sama robi podsumowania automatycznie :). Tutaj mielibyśmy grupę produktów jako jedno pole na wierszach, a sam produkt jako drugie pole. Może tak? Najprościej 😉

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *