niedziela, 15 lipca 2018

Szukajcie, a znajdziecie

"Jeśli się chce coś znaleźć, trzeba po prostu szukać (...) Rzeczywiście, kto szuka, ten najczęściej coś znajduje, niestety czasem zgoła nie to, czego mu potrzeba."
R. R. Tolkien, Hobbit

Ostatnio zakończyliśmy nasze eksperymenty na etapie sprawdzania poprawności danych. Zastosowaliśmy kolumnę tabeli pomocniczej, jako źródła elementów listy do wyboru. W tym i kolejnych wpisach postaram się przybliżyć niektóre sposoby przeszukiwania tabel i łączenia danych w nich się znajdujących.
Zacznijmy od pliku, który utworzyliśmy. Jeśli nie zapisałeś sobie tego skoroszytu - możesz pobrać go stąd: Tables-03.xlsx
W poprzednim poście, kiedy wspominałem o stawkach podatku, uprościliśmy tabelę tylko do podstawowej krótkiej listy wartości procentowych. Co jednak jeśli chciałbyś dodać również stawki specjalne, takie jak np. zwolniony? Spróbujmy dopisać do listy takie wartości.
  • Przejdź do arkusza [Wymiary] i dopisz do tabeli stawek podatku nowe pozycje:
    Zwolniony
    Nie podlega
  • Jak wrócisz do listy faktur, będziesz mógł już wybrać nowe stawki podatku z listy. Pojawia się jednak problem z formułą, która oblicza nam wartość brutto faktury. Wybranie stawki, która nie jest liczbowa, spowoduje błąd w obliczeniach:
Skąd wziął się błąd? Czy pamiętacie jeszcze, jak wyglądała formuła licząca kwotę brutto? Pod koniec pierwszego posta miała ona taki zapis:
=[Kwota netto]+[Kwota netto]*[Vat%]
Jeśli podstawimy do tej formuły wartości, które przed chwilą wpisaliśmy otrzymamy:
[Kwota netto] = 250
[Vat%] = "Zwolniony"
[Kwota brutto] = 250 + 250 * "Zwolniony"
No cóż, nie uda się przecież wykonać mnożenia liczby przez tekst. Stąd i nasz błąd wyrażenia. Jak zatem sobie poradzić?

4. Łączenie i wyszukiwanie danych

Nasze specjalne stawki Zwolniony i Nie podlega odpowiadają 0% stawce podatku. My jednak chcemy zapisywać faktury w podziale na te 3 różne stawki. Wypróbujemy zatem różne podejścia do problemu: z użyciem formuł oraz przy zastosowaniu narzędzia Get & Transform (znanego też jako Power Query). Zanim jednak to zrobimy, dodajmy naszym stawkom podatku mnożnik, który pozwoli nam na prawidłowe mnożenie kwoty brutto.
  • Wróć do tabeli stawek podatku i kliknij w komórce, która znajduje się po prawej stronie nagłówków tabeli (obok komórki [Stawka]). Wpisz nazwę kolejnej kolumny "Procent". W tabeli pojawi się dodatkowa kolumna.
    Wpisywanie wartości bezpośrednio po prawej stronie lub bezpośrednio pod tabelą strukturalną, zawsze automatycznie zwiększa jej zakres, tak aby obejmował nowe komórki. Czynność ta jest zapamiętywana jako dodatkowa akcja możliwa do cofnięcia. Jeśli przez przypadek dopisałeś wartości, ale nie chciałeś zmieniać zakresu tabeli, możesz cofnąć jedną czynność używając skrótu CTRL+Z. Tabela wróci do poprzednich rozmiarów pozostawiając wpisaną przez Ciebie wartość poza jej obszarem.
  • Wpisz w nowej kolumnie prawidłowe wartości stawek procentowych dla każdego kodu podatku.
Teraz możemy już zabrać się do łączenia danych z tych dwóch tabel.

Metoda 1: Funkcja WYSZUKAJ.PIONOWO

Plan działania:
  1. Dodać kolumnę, w której umieścimy procent podatku, wyszukane z wykorzystaniem wybranego kodu podatku.
  2. Poprawić formułę w kolumnie [Kwota brutto], tak aby używała wyszukanego procentu.
Wróćmy zatem do arkusza z naszymi fakturami:
  • Zmień nazwę nagłówka [Vat%] na [Kod podatku]
  • W komórce obok nagłówka [Kwota brutto] (H2) wpisz "Procent". Powstanie dodatkowa kolumna tabeli.
  • Kliknij w pierwszej komórce w kolumnie [Procent] (H3) i kliknij przycisk wstawiania funkcji. Będziesz mógł wtedy dokładniej zaobserwować jak działa interesująca nas formuła.
  • Wybierz kategorię funkcji: Wyszukiwania i adresu, a następnie użyj funkcji WYSZUKAJ.PIONOWO
  • Wybierzemy teraz poszczególne argumenty tej funkcji. Argument Szukana_wartość to [Kod podatku] - kliknij w odpowiedniej komórce tego samego wiersza.
  • Kolejny argument - Tabela_tablica - to nasza pomocnicza tabela tblStawkiVAT. Można wpisać nazwę z klawiatury, albo wskazać kliknięciem. Kliknij w ikonę zaznaczania zakresu i przejdź do arkusza [Wymiary]
  • Najedź myszką na lewy górny narożnik tabeli, aż wskaźnik zmieni kształt na czarną strzałkę skierowaną w prawy dół. Kliknij w tym miejscu.
  • Nazwa tabeli pojawi się okienku formuły w polu drugiego argumentu. Zauważ też, że tuż obok pojawiła się lista wartości
    {0,23\0,23;0,08\0,08;0,05\0,05;0\0;"Z..
    Jest to podgląd zawartości tabeli ze stawkami podatku. Poszczególne kolumny są tu rozdzielone ukośnikiem "\", a wiersze średnikiem ";". Nagłówki nie zostały uwzględnione i tak miało być.
  • Trzeci argument to Nr_indeksu_kolumny. Wpisz 2 w tym polu. Funkcja będzie szukała w pierwszej kolumnie tabeli tblStawkiVAT pasującej wartości, a na wyjściu poda wartość z kolumny wskazanej tym numerem. W tabeli stawek interesujący nas mnożnik znajduje się w kolumnie 2 - stąd wpisana liczba.
  • Pozostał nam ostatni argument - Przeszukiwany_zakres. Wpisz tutaj "FAŁSZ" lub 0. Ten parametr decyduje o tym, czy funkcja ma wyszukać tylko przybliżoną wartość (4 argument = PRAWDA lub 1), czy dokładnie taki sam kod podatku jaki jest szukany (4 argument = FAŁSZ lub 0).
    W większości przypadków używa się ostatniego argumentu = FAŁSZ. Jeśli używamy argumentu = PRAWDA - wartości w pierwszej kolumnie powinny być posortowane rosnąco. Inaczej funkcja może zwrócić wartości mocno odbiegające od najbliższych.
  • Po zatwierdzeniu formuły przyciskiem [OK], zobaczysz poprawne mnożniki podatku w każdym wierszu, również tam gdzie wybrałeś tekstowe kody podatku. Zobaczmy to na animacji:
  • Jedyne co nam pozostało to poprawienie formuły liczącej wartość brutto. Kliknij w dowolnej komórce z wartościami brutto i zamień nazwę kolumny na zawierającą faktyczny procent znaleziony powyżej ([Procent]).
Tym sposobem udało nam się połączyć informacje z dwóch różnych tabel. Oczywiście są też inne sposoby. Jedno z ciekawych narzędzi to Power Query (w najnowszej wersji Excela znany też jako Get & Transform). Za tydzień pokażę, jak można łączyć tabele używając tego narzędzia. Do zobaczenia!

Brak komentarzy:

Prześlij komentarz

Najczęściej czytane