Generowanie listy bestsellerów

by Marcin

W jednym skoroszycie („Produkty”) mam listę produktów wraz z ilością sprzedanych sztuk w określonym przedziale czasowym.

Top 10 - generowanie listy bestsellerów

Wszystko, co chciałbym zrobić to wygenerować, w drugim skoroszycie o nazwie „top 10”, listę dziesięciu najlepiej sprzedających się produktów. Chciałbym także, aby lista ta aktualizowała się automatycznie przy każdej zmianie ilości sprzedanych produktów w skoroszycie „Produkty”. No i nie chcę używać makr.

Pierwsze, co zrobiłem to, przy pomocy funkcji MAX.K posortowałem malejąco sprzedane ilości. W tym celu w skoroszycie „Top 10” zaznaczyłem zakres komórek I7:I16 i w zakres ten wpisałem formułę tablicową:

{=MAX.K(Produkty!C4:C19;WIERSZ(ADR.POŚR("1:"&ILE.WIERSZY(Produkty!C4:C19))))}

Mam więc już listę TOP 10 sprzedanych ilości. Pozostaje jeszcze tylko przypisać odpowiadające każdej ilości nazwy produktów. Nie można po prostu skorzystać z funkcji INDEKS i PODAJ.POZYCJĘ (rozwiązanie, które pierwsze przychodzi mi do głowy w takich sytuacjach). Może zresztą inaczej – można, gdybyś miał pewność, że ilości sprzedanych produktów nie będą się powtarzać. Jeżeli się one powtarzają, co jest przypadkiem bardziej realnym, użycie

=INDEKS(Produkty1!B4:B19;PODAJ.POZYCJĘ(I7;Produkty1!C4:C19;0);1)

dla każdej powtarzającej się ilości sprzedanych produktów zwracać będzie tę samą nazwę produktu – pierwszą na liście. Widać to ładnie na rysunku.

Top 10 - generowanie listy bestsellerów

Trzeba więc nieco inaczej.

W skoroszycie „Top 10”, w komórce H7 pozostawiam formułę

=INDEKS(Produkty1!B4:B19;PODAJ.POZYCJĘ(I7;Produkty1!C4:C19;0);1)

Dla pozostałych 9 towarów, w komórce H8 wpisuję formułę tablicową

{=ADR.POŚR("Produkty!"&ADRES(MIN.K(JEŻELI(Produkty!$C$4:$C$19=I8;WIERSZ(Produkty!$C$4:$C$19);65536);LICZ.JEŻELI($I$7:I8;I8));2))}

i kopiuję w dól na pozostałe komórki. Końcowy wynik widoczny na rysunku poniżej.

Top 10 - generowanie listy bestsellerów

Całość wydaje się działać bardzo sprawnie i generuje mi ładne i funkcjonalne podsumowania sprzedaży. Każda zmiana ilości sprzedanych produktów ( zmieniają się one codziennie) ustawia (sortuje) odpowiednio listę Top 10.

Oczywiście nie jest to prawdopodobnie jedyne możliwe rozwiązanie (jak to w Excelu). Jeżeli więc masz jakiś inny pomysł na TOP 10 – napisz.

{ 1 comment… read it below or add one }

Kamil

Bardzo mi pomógł ten post.
Wielkie dzięki.
Chyba najlepszy blog o excelu 🙂

Leave a Comment

Previous post:

Next post: