Czyli przyporządkowanie ceny i kodu produktu, na podstawie jego kolekcji i modelu

Załóżmy, że sprzedajemy ubrania. Dzielimy je sobie na kolekcje, które mają różne modele. Wybieramy sobie kolekcję i model i na tej podstawie ma nam się wyświetlić indeks i cena danego ubrania. To jest zadanie na teraz, przy czym formatka wygląda tak:

Formatka

Czyli wybieramy najpierw kolekcję z listy rozwijanej w komórce A2 (tak, wiem, że wygląda na to, że nic w niej nie ma, a to dlatego, że zastosowałam do niej takie formatowanie ;)), a następnie model w komórkach kolumny Model. Wpisujemy ilość, a kod produktu i cena same mają się pojawić.

Jak sugeruje tytuł tego posta, użyję do tego dwóch funkcji: WYSZUKAJ.PIONOWO i PODAJ.POZYCJĘ. Natomiast powiem Wam, że najfajniejszym trikiem będzie ukrycie zer (zwracanych przez formuły). Nie użyję do tego bowiem pustego ciągu tekstowego, czyli dwóch cudzysłowów obok siebie („”), tylko formatowania niestandardowego… Warto więc doczytać do końca 🙂

Zrobienie list rozwijanych opisuję tutaj, więc przejdę dalej. Istotne są w naszym ćwiczeniu tabele z danymi: kodami/inddeksami i z cenami. Zauważcie, że nagłówkami kolumn są modele, a nagłówkami wierszy – kolekcje. Kolejność nie ma znaczenia, można zrobić odwrotnie, ale ważne jest, aby nazwy modeli i kolekcji były dokładnie takie same, jak na listach rozwijanych na formatce.

Jak już to jest, to możemy przejść do formuł.

Wyświetlanie kodu

Szukać będziemy kolekcji w tabelce z kodami. Będą to też nasze pierwsze dwa argumenty naszej kochanej WYSZUKAJ.PIONOWO. Szukać będziemy dokładnie, więc w ostatnim argumencie wpiszemy zero (0). Problem pojawia się jedynie przy trzecim argumencie, ponieważ nie wiemy, którą kolumnę wyświetlić.

Tutaj z pomocą przyjdzie nam właśnie funkcja PODAJ.POZYCJĘ, której każemy znaleźć nasz model w wierszu nagłówkowym tabeli. Funkcja ta wyświetli jedynie numer komórki, w której znajduje się nasz model, a będzie to niejednoczenie numer kolumny, którego potrzebuje od nas WYSZUKAJ.PIONOWO. Tadam!

Formułą jest następująca (C4):

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO($A$2;$A$16:$D$19;PODAJ.POZYCJĘ($A4;$A$15:$D$15;0);0);0)

Wszystko wrzuciłam jeszcze w funkcję JEŻELI.BŁĄD, jeśli jakiś błąd się pojawi. A takiż może się pojawić, gdy user nie wybierze jeszcze modelu. W przypadku błędu chcę, aby funkcja wyświetliła zero. I tak wiem, że będzie to głupio wyglądało, natomiast poradzę sobie z tym za chwilę :).

Wyświetlanie ceny

Praktycznie identyczna formułkę zastosujemy do wyświetlenia ceny, z tą tylko różnicą, że każemy przeszukiwać tabelkę z cenami, a nie kodami. Oto ona (D4):

=JEŻELI.BŁĄD(INDEKS($B$23:$D$26;PODAJ.POZYCJĘ($A$2;$A$23:$A$26;0);PODAJ.POZYCJĘ(A4;$B$22:$D$22;0));0)

Wartość

Wartość sprzedaży jest już prosta, czyli (E4):

=B4*D4

Choćby dlatego wolę wyświetlać zero w przypadku błędu WYSZUKAJ.PIONOWO: formuły w stylu tej liczącej wartość ładnie się policzą. Gdybym kazała wyświetlać pusty tekst („”) w przypadku błędu – formuła licząca cenę wyświetliłaby błąd (#ARG!) i tak w koło Macieju… A Excel umie pomnożyć jakąś liczbę razy zero, wiec jeden problem mniej :).

Efekt tego jest na razie taki (wpisałam kilka modeli):

Po wpisaniu formuł…

Widać brzydkie zera, ale przynajmniej wszystko się liczy. To pozbądźmy się tych zer…

Alternatywna formuła z INDEKS i PODAJ.POZYCJĘ

To samo można też zrobić wymienionymi wyżej funkcjami. Formuła dla ceny wyglądałaby tak:

=JEŻELI.BŁĄD(INDEKS($B$23:$D$26;PODAJ.POZYCJĘ($A$2;$A$23:$A$26;0);PODAJ.POZYCJĘ(A4;$B$22:$D$22;0));0)

Jak ukryć zera?

Należy do tego użyć formatowania niestandardowego.

1. Czyli zaznaczmy wszystkie komórki, w których chcemy ukryć zera. Ja na razie zaznaczę ceny i wartości (D4:E12), ponieważ przy okazji dorzucę separator tysięcy (nie potrzebuję miejsc dziesiętnych, ale oczywiście można byłoby je też tutaj dorzucić).

2. Następnie wchodzimy do formatowania komórki (skrót klawiszowy Ctrl +1)

3. Z zakładki Liczby wybieramy kategorię Niestandardowe. A w nim wpisujemy następujący typ formatowania:

# ##0;-# ##0;

W okienku wygląda to tak:

Formatowanie niestandardowe

Zapis ten oznacza, że liczby dodatnie mają być wyświetlone z separatorem tysięcy, ujemne też, ale dodatkowo ze znakiem minus, a zera jako nic. Czyli zera niewyświetlane.

Dla kolumny z kodami z kolei, również w formatowaniu niestandardowym, skorzystam z takiego formatu:

Standardowy;-Standardowy;

To s kolei oznacza, że dodatnie i ujemne mają zostać wyświetlone w formacie ogólnym (nie chcę w żaden sposób ich formatować), a zera, jak poprzednio, nie będą wyświetlane.

W takim zapisie istotna jest kolejność: najpierw definiujemy jak mają wyglądać liczby dodatnie, potem ujemne, potem zero, a na końcu tekst. Ale tekstu akurat teraz nie wykorzystuję.

Wszystko razem wygląda tak:

Wynik

I tyle :). Jeśli zaś chcesz dowiedzieć się więcej o formatowaniu niestandardowym zrozumieć te wszystkie krzaczki – zobacz webinar o formatowaniu niestandardowym, który prowadziłam w lutym 2019:

I wersja wideo tego wpisu (pokazuję na nim alternatywę z funkcjami INDEKS i PODAJ.POZYCJĘ):