Czyli trochę o nowych dynamicznych tablicach

Już w lipcu 2020 wszyscy posiadacze subskrypcyjnej wersji Office’a (Office 365) będą mogli korzystać z nowej funkcjonalności Excela: dynamicznych tablic. Powiem tak: to jest REWOLUCJA. Od tej pory mega-skomplikowane formuły będzie można zamienić na kilka przyjemnych i łatwych w użyciu funkcji. Również to, co do tej pory było wręcz niemożliwe do wykonania – przy pomocy dynamicznych funkcji tablicowych będzie bardzo proste!

Nie trzeba daleko szukać – niedawno pokazywałam sposób na stworzenie przeszukiwalnej listy rozwijanej, wykorzystujący formularz użytkownika i makra oczywiście (VBA). Artykuł ten możesz przeczytać tutaj. Opisane w nim rozwiązanie bardzo mi się podoba – sama stosuję je w swoich plikach, jak również w plikach, które tworzę dla klientów.

Wiem jednak, że nie wszyscy chcą korzystać z makr. W takiej sytuacji potrzebne jest rozwiązanie oparte np. na formułach. I takie właśnie dzisiaj pokażę. Efekt będzie taki:

Pokażę Wam rozwiązanie wzorowane na propozycji Leili Gharani, której kanał na YT bardzo Wam polecam.

Zaczynamy!

Formatka

Potrzebuję 2 tabel (u mnie w dwóch arkuszach): jedną nazwałam tbFaktury (arkusz Dane), drugą – tbKlienci (arkusz Dane).

Formatka

W tbFaktury użytkownik wpisuje do tabeli numer faktury, a następnie klienta, dla którego fakturę tę tworzy. Naszym celem jest to, aby po wpisaniu fragmentu nazwy klienta  pojawiła się lista rozwijana ze wszystkimi możliwymi klientami, których nazwa zawiera wpisany fragment.

Formuła dla przeszukiwalnej listy rozwijanej dla jednej komórki

Najpierw stworzę formułę dla jednej komórki (sedno sprawy!), która będzie listowała wszystkie możliwe wartości (zrobię to poza tabelą główną).

Czyli jeśli użytkownik wpisze do komórki fragment nazwy klienta – formuła wyświetli wszystkie nazwy klienta, które zawierają wpisany fragment. Za jednym razem tych klientów będzie mniej, za innym więcej. Jednak dla dynamicznych formuł tablicowych nie jest to problem :).

Załóżmy, że fragment nazwy będę wpisywała do komórki G7, którą nazwę jako Robocza, a wyniki mają się wyświetlać od komórki H7 w dół. Właśnie w komórce H7 wpiszę formułę.

Skorzystam z przecudownej funkcji filtrującej: FILTRUJ. Jest to dynamiczna funkcja tablicowa. Potrzebuje ona ode mnie informacji o:

1. zakresie, który ma przefiltrować (u nas będą to wszyscy klienci, czyli kolumna Klient w tabeli tbKlienci).

2. kryteriów filtrowania (ma zawierać fragment nazwy wpisany przez użytkownika).

Istnieje funkcja tekstowa SZUKAJ.TEKST, która umie sprawdzić na której pozycji w tekście znajduje się inny tekst (jeśli nie znajdzie tego tekstu – zwróci błąd). Tej funkcji tutaj użyję (nie bierze pod uwagę wielkości liter).

=SZUKAJ.TEKST(G7;tbKlienci[Klient])

Zauważ, że w drugim argumencie tej funkcji podałam zakres wielu komórek – jest to informacja dla funkcji, że ma do czynienia z operacją tablicową. W wyniku otrzymam tablicę liczb i błędów:

Wyniki funkcji SZUKAJ.TEKST

Teraz sprawdzę, czy wyniki tej funkcji są liczbami. Jeśli tak – znaleźliśmy klienta, który w nazwie ma tekst wpisany przez usera:

=CZY.LICZBA(SZUKAJ.TEKST(G7;tbKlienci[Klient]))

To z kolei konwertuje nam wszystkie liczby na PRAWDA, a wszystkie błędy #ARG! na FAŁSZ. O tak:

Wyniki funkcji CZY.LICZBA

Te wartości logiczne to właśnie coś, czego potrzebuje od nas funkcja FILTR, aby przefiltrować podany jej zakres (czyli tbKlienci[Klient]).  To wrzućmy wszystko w tę funkcję:

=FILTRUJ(tbKlienci[Klient];CZY.LICZBA(SZUKAJ.TEKST(G7;tbKlienci[Klient])))

Efekt będzie następujący:

Wynik funkcji FILTRUJ

Wszystko można jeszcze posortować funkcją SORTUJ (u mnie akurat tabela źródłowa już była posortowana, więc nie zobaczymy efektu):

=SORTUJ(FILTRUJ(tbKlienci[Klient];CZY.LICZBA(SZUKAJ.TEKST(G7;tbKlienci[Klient]))))

No to jest. Teraz tylko trzeba wrzucić to do listy rozwijanej, czyli sprawdzania poprawności danych.

Formuła ma działać dla wielu komórek

W tabeli tbFaktury dodam sprawdzanie poprawności do kolumny Klient. Interesuje mnie lista rozwijana, zatem Dane/Poprawność danych:

Tworzenie listy rozwijanej

Zauważ, że w Źródle odwołałam się tylko do komórki H7 i za nią postawiłam znak #. Jest to zapis odwołania do dynamicznej tablicy. Zauważ, że formuła w H7 niejako w niej „żyje”. Ja jej nie kopiowałam, w dół (do pozostałych dwóch komórek), aby uzyskać nazwy dwóch innych firm, spełniających kryteria. Sama się skopiowała. Excel sam wiedziała, do jakiego miejsca ma ją skopiować. I od tej pory my, kiedy chcemy odwołać się do dynamicznej tablicy, również nie musimy wiedzieć, gdzie ona się kończy. Wystarczy nam informacja o tym, gdzie się zaczyna :).

Od razu na tym etapie możemy określić, abyśmy mogli wpisywać do komórek niepoprawne wartości (niepoprawne, czyli takie, których nie ma na liście). Powiem o tym dalej, natomiast na tę chwilę uwierz mi na słowo :). W zakładce Alert o błędzie odznacz chceckbox Pokazuj alerty po wprowadzeniu nieprawidłowych danych:

Tworzenie listy rozwijanej – alert o błędzie

Zadziała pięknie, jednak jest jedno „ale”…

… jest pewien problem

Aby skorzystać z listy, użytkownik nadal musi wpisywać szukany tekst do komórki G7. Bardzo to niewygodne. Chciałabym, aby mógł wpisywać szukaną wartość bezpośrednio w tabeli z fakturami.

Możnabyłoby pomyśleć: w takim razie co za problem – skopiujmy formułę, którą przed chwilą napisaliśmy do sprawdzania poprawności, odwołajmy się do komórki z klientem i sprawa załatwiona!

No właśnie nie do końca.

Niestety, może na tę chwilę?, sprawdzanie poprawności nie przyjmie dynamicznej tablicy jako źródła ;(.

Mi przychodzi do głowy tylko VBA. A konkretnie zdarzenia arkusza Worksheet_Change, które na każdą zmianę komórki (najlepiej tylko tych z klientami) wpisywało wartość do komórki Robocza, a na tej podstawie utworzy się źródło listy rozwijanej.

Zatem przejdźmy do VBA.

Potrzebujemy trochę makr (VBA)

Utwórzmy zatem zdarzenie arkusza Worksheet_Change i wpiszmy następujący kod:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo Obsluga
    
    If Not Intersect(Me.Range("Zakres"), Target) Is Nothing Then
        Application.EnableEvents = False
        Me.Range("Robocza").Value = Target.Value
    End If

Obsluga:
    Application.EnableEvents = True
    
End Sub

Zadanie powyższego kodu jest bardzo proste: wpisuje do komórki Robocza tą samą wartość, którą wpisał użytkownik. A na tej podstawie formuła tablicowa już robi swoje :).

Efekt jest taki:

Fajne, prawda? 😉

Na koniec mam do Ciebie prośbę: pomożesz mi dotrzeć z tym artykułem do szerszego grona? Jeśli zechcesz udostępnić ten artykuł na Facebooku – będę Ci bardzo wdzięczna! Bardzo się starałam, aby ten artykuł był dla Ciebie na tyle wartościowy, by wart był wzmianki ;). Oczywiście śmiało podeślij go innym osobom mailem lub Messengerem. Będzie mi bardzo miło:)

Powiązane produkty

• O obiekcie tabela słów kilka – podczas tej lekcji pokazuję czym jest obiekt tabela w Excelu i dlaczego warto z niego korzystać. Daje nam on ogromne możliwości dynamizowania naszych arkuszy, a wiele nowych funkcjonalności Excela wręcz wymaga przechowywanie danych w tymże obiekcie (np. Power Query). Must have każdego użytkownika Excela!
• Listy rozwijane – podczas tej lekcji pokazuję jak zrobić listę rozwijaną zarówno taką, jak we wpisie (czyli sprawdzanie poprawności), jak i formant formularza, oraz jak je wykorzystać np. na wykresach.
• Nagrywanie makr – podczas tej dwugodzinnej lekcji dowiesz się jak w sprytny sposób nagrywać całkiem inteligentne makra

Pliki do pobrania:

Jedna lista rozwijana: MalinowyExcel Jedna Lista z podpowiedziami 365.xlsx

Wiele list rozwijanych w tabeli: MalinowyExcel Lista z podpowiedziami 365 dw.zip

I film na Youtubie, gdzie pokazuję tworzenie jednej listy rozwijanej (przykład inny niż w artykule):

A tutaj film pokazujący rozwiązanie dla tabeli, czyli z VBA: