Niniejszy odcinek mojego poradnika poświęcę jednemu z najczęściej występujących w pracy problemów, a mianowicie: jak najprościej podsumować dane z tabeli.
Jako przykład posłuży nam sprzedaż sieci piekarni zebrana w tabeli poniżej.
Oczywiście pierwszym pytaniem szefa będzie: „No to ile zarabiamy na chlebie a ile na bułkach?”
W swojej długoletniej praktyce spotkałem się u swoich Klientów z wieloma rozwiązaniami tego problemu od ręcznego spisywania pozycji i sumowania danych po zaawansowane tabele przestawne. Niestety każde z tych rozwiązań miały swoje wady. Te proste przestawały działać po dowolnej zmianie w tabeli np. pojawieniu się w jednej z piekarń rogalików i totalnie nie sprawdzały się przy dużych tabelach zaś zaawansowane tabele przestawne mówiąc wprost przerastały odbiorców.
Bazując na tych doświadczeniach chciałbym zaproponować Wam zastosowanie funkcji, których wpisanie jedynie w 2 komórki arkusza rozwiąże skutecznie nasz problem.
Jak to osiągnąć ?
Wybierzmy dowolny obszar obok lub pod tabelą i wpiszmy tam nagłówki naszego podsumowania
Następnie w komórce poniżej „Produkt” dodajmy funkcję =Unikatowe( zaznaczmy w naszej tabeli kolumnę „Produkt” i zamknijmy nawias funkcji )
W moim arkuszu komórka ma następującą formułę =UNIKATOWE(Tabela1[Produkt])
Natychmiast wyświetli nam się lista unikatowych pozycji z kolumny „Produkty” jak poniżej
Największą zaletą tego rozwiązania jest fakt że lista jest dynamiczna. Zamiana jednej z pozycji w tabeli źródłowej „Rogalik” np. na „Chałka” spowoduje automatyczne dodanie do naszej listy pozycji „Chałka”. Analogicznie jeżeli z głównej tabeli znikną np. wszystkie pozycje „Bułka” nasza nowa lista również się zmniejszy. Skoro już mamy taką fajną listę dobrze jest nadać jej jakąś nazwę. Stańcie w komórce w której wpisaliście formułę i zmieńcie jej nazwę np. na „Produkty”
Ciekawą konsekwencją tej operacji będzie fakt, iż wpisanie w dowolnej komórce arkusza formuły =Produkty# spowoduje iż nasza lista pojawi się również w tym miejscu. Fajne prawda ?
Ale wróćmy do naszego problemu. Mamy już listę elementów unikatowych, ale jak zrobić taką samą listę dla sum wartości. Również ta lista powinna być dynamiczna żebyśmy nie musieli przeciągać formuł lub ich kasować w zależności od ilości rodzajów produktów. W tym celu zacznijmy od nazwania komórki z prawej strony od naszej formuły „Unikatowe”. Nazwijmy ją np. „Wyniki”
Teraz musimy spowodować aby w tym miejscu powstała lista o długości równej naszej liście unikatowych produktów z sumami kolumny „Miesięczna sprzedaż”. Zrobimy to następująco. W komórce „Wyniki” wpiszemy =MAKEARRAY(ILE.WIERSZY( zdefiniowana przez nas nazwa Produkty#);1;LAMBDA(W;K;SUMA.JEŻELI( tu zaznaczymy naszą kolumnę „Produkty” i dalej ;PRZESUNIĘCIE( zdefiniowana przez nas druga nazwa Wyniki;W-1;-1); tu zaznaczymy naszą kolumnę „Miesięczna sprzedaż” i zamkniemy wszystkie nawiasy )))
Jeżeli dobrze wszystko wpisaliście formuła będzie wyglądała jak poniżej
=MAKEARRAY(ILE.WIERSZY(Produkty#);1;LAMBDA(W;K;SUMA.JEŻELI(Tabela1[Produkt];PRZESUNIĘCIE(Wyniki;W-1;-1);Tabela1[Miesięczna sprzedaż])))
A Waszym oczom od razu ukaże się następujący widok
Po zmianie formatowania liczb na Walutowe:
Wszelkie zmiany w tabeli głównej od razu są przeliczane w naszych listach bez potrzeby jakiejkolwiek ingerencji.
Voila ! W 2 komórkach zmieściliśmy rozwiązanie, które może dla dużych tabel obejmować kilkadziesiąt pozycji.
Jeżeli Wam się spodobało i chcielibyście podyskutować o szczegółach bądź macie inne potrzeby których rozwiązanie od lat spędza wam sen z powiek lub wymaga dużego nakładu pracy zapraszam do kontaktów na adres konrad.pogodz@itch.pl
Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.
