Nowa funkcja w Excelu, dostępna w subskrypcji (Microsoft365), świetnie nadaje się gdy w jednej formule potrzebujemy wielokrotnie użyć tego samego (albo tych samych, kilku) fragmentu formuły. Funkcja ta nazywa ów fragment i pozwala użyć już samej nazwy, a nie wciąż pisać w kółko to samo.
Jej użycie omówię na przykładzie wyodrębniania daty urodzenia z PESELu. Zarówno “nowego” (2000-2099) jak i “starego” (1900-1999).
Tak wygląda formatka z wynikiem:
Tytuł artykułu jest dość przewrotny. Ostatnio opisywałam wiele metod na stworzenie przeszukiwalnej listy rozwijanej: metoda używająca formularza VBA, metoda wykorzystująca dynamiczne funkcje tablicowe w Microsoft365. Teraz przyszedł czas na coś prostszego.
Tylko to tak na prawdę nie będą żadne metody, tylko po prostu… użycie Excela online i Google Sheets :).
Ten artykuł jest nietypowy – opisuje bowiem tworzenie praktycznego rozwiązania (a nie wybraną opcję, jak dotychczasowe na moim blogu).
Pokazuje, jak można wykorzystać Excela do (próby) zmian zachowań/działań. Opisywane rozwiązanie jest z życia wzięte. Zastosowałam je u jednego ze swoich klientów, który ma spore problemy ze ściągalnością płatności od swoich klientów. Klienci ci przyzwyczajeni są do tego, że otrzymują towar, a zapłacić mają później. I jakoś tak o tym drugim im się często zapomina, a podatki płacić trzeba.
Aby się z tym uporać mój Klient wprowadził zasadę sprzedawania na przedpłaty. Wszystko byłoby fajnie, gdyby jeszcze handlowcy się do niej stosowali.
Niestety, zbyt często się nie stosują.
Próbowaliśmy wszystkiego: tłumaczenie problemu, nagrody, premie – nic nie działa. Nadal umawiają się z klientami na przelewy…
Pomyślałam więc, że dorzucę swoją – raportową – cegiełkę do tej układanki. Handlowcy kilka razy w tygodniu korzystają z Excela, w którym to postanowiłam umieścić… biofeedback. Ta nazwa bardzo ładnie mi tutaj pasuje ;). Jednym słowem: unaocznić skalę problemu.
Chodziło mi o to, aby handlowcy zobaczyli: sprzedajecie tyle, za tyle nie mamy kasy. Przekaz miał być jak najbardziej prosty i zrozumiały na pierwszy rzut oka. Postanowiłam więc skorzystać z najbardziej powszechnych technik (niekoniecznie najlepszych).
I tak, wiem, spece od wizualizacji danych by mnie za to zlinczowali: zrobiłam wykres kołowy (jest powszechnie używany i zrozumiały), korzystając z czerwonego i zielonego koloru. Dorzuciłam mini-raport dla bardziej dociekliwych, i wyszło tak:
Aby Cię Czytelniku nie zanudzić (rozwiązanie jest długie) – w tym artykule wymieniam kluczowe fragmenty tego rozwiązania. Natomiast całość możesz obejrzeć na filmie, który znajdziesz poniżej.
Aby stworzyć zależną listę rozwijaną, trzeba było używać niezbyt prostych funkcji, np. PRZESUNIĘCIE. Opisywałam to już kiedyś na blogu (zobacz tutaj).
Teraz, gdy dla użytkowników subskrypcyjnej wersji Office’a (365) dostępne są dynamiczne formuły tablicowe – będzie to pestka!
Chodzi o coś takiego:
Będziemy chcieli z pierwszej listy rozwijanej wybierać markę samochodu, a z drugiej – model. Oczywiście będą nas interesowały modele tylko wybranej wcześniej marki.
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!
Jak dowiedziałam się o tej opcji – cieszyłam się jak małe dziecko! Ileż razy wstawiałam do danych sztuczne dane tylko po to, aby w tabeli przestawnej wyświetlić każdy miesiąc – nawet taki, w którym nie było sprzedaży… Jak się okazuje – wcale nie trzeba tak robić :). Wystarczy włączyć prostą opcję w tabeli przestawnej.
Jeśli tej opcji nie włączymy, wynik zarówno tabeli przestawnej i wykresu przestawnego może być taki:
Miesiące bez sprzedaży nie są wyświetlane…
A tak nie chcemy – łatwiej będzie nam analizować sprzedaż klientów po miesiącach, kiedy będziemy widzieli wszystkie 12 miesięcy, a nie tylko te, w których była sprzedaż. Brak sprzedaży w danych miesiącach jest ważną informacją!!!
A jak jeszcze dorzucimy sobie np. fragmentator, aby łatwo filtrować dane – wykres będzie brzydko “skakał”…
Ok, zobacz więc co to za opcja 🙂
W poprzednim wpisie pokazywałam jak zahasłować wybrane zakresy komórek w arkuszu tak, aby mogły je edytować jedynie osoby, które znają hasło. Dzisiaj pokażę kod VBA, który nie pozwoli edytować komórek, jeśli konkretna komórka została już uzupełniona.
W naszej sytuacji akceptowania warunków zamówień będzie to działać tak, że jeśli Kierownik Kontroli Kredytowej (KKK) zaakceptuje umowę (zaakceptuje = Tak), osoby wcześniej akceptujące (Dział Obsługi Klienta (DOK) i Dyrektor Sprzedaży (DS)) nie będą mogli już zmieniać swoich decyzji. Czyli, krótko mówiąc, zamówienie zostanie finalnie zaakceptowane.
Ma to działać tak:
Zabierzmy się więc za stworzenie takiego rozwiązania.
Załóżmy, że akceptacja sprzedaży w naszej firmie odbywa się trójetapowy: najpierw sprzedaż akceptuje kierownik Działu Obsługi Klienta (DOK), następnie Dyrektor Sprzedaży (DS), a na końcu kierownik Działu Kontroli Kredytowej (KKK). I ten ostatni ma decydujące zdanie: w końcu zależy nam na sprzedaży a nie dobroczynności tutaj.
Formatka z zamówieniami wygląda następująco:
Formatka
W ostatnich trzech kolumnach decydenci wpisują Tak lub Nie. I tylko decydenci mogą to zrobić.
Do rozwiązania tego problemu potrzebujemy listy rozwijanej i dodatkowo ochrony zakresów w arkuszu i oczywiście ochronę arkusza.
Do dzieła!
Załóżmy, że mamy określić daty, kiedy nasi pracownicy mogą przejść na emeryturę. Jedynym naszym kryterium w tym wypadku jest wiek i płeć pracownika. Czyli kobieta może przejść na emeryturę w wieku 60 lat, mężczyzna natomiast – w wieku 65 lat.
Dostajemy z systemu następujące informacje:
Na tej podstawie mamy określić kiedy pracownik może przejść na emeryturę. Chcemy dostać coś takiego:
Do tego obliczymy sobie jeszcze ile pełnych lat pracy zostało każdemu pracownikowi oraz zaznaczymy kolorem tych, którzy już osiągnęli wiek emerytalny.
Temat, o który już dawno mnie pytaliście, ale przyznaję szczerze: nie chciało mi się za niego zabierać :). W końcu jednak nadszedł czas! Obecnie, z nowym mechanizmem przeliczania w Excelu 365, zrobienie czegoś takiego za pomocą formuł nie jest już wielkim wyzwaniem. Natomiast wyzwaniem jest to, że nadal nie wszyscy mają Excela w tej wersji. I szybko to się nie zmieni… Dlatego chciałabym, aby rozwiązanie było możliwe w każdej wersji Excela.
Ok, ale o co w ogóle chodzi?
O przeszukiwalną listę. Chciałabym napisać rozwijaną, ale to za bardzo ograniczyłoby mi możliwości rozwiązania, choć na pewno byłoby najbardziej użyteczne.
Chodzi o zrobienie takiej listy wyboru np. klientów, aby po wpisaniu kilku liter (niekoniecznie początkowych), Excel wylistował klientów, zawierających wpisane litery. Z nich użytkownik wybiera klienta, który ma być wpisany do komórki.
Chodzi o coś takiego:
Czyli po dwukrotnym kliknięciu w kolumnie z klientami pojawia się formularz, na którym możemy wpisać szukaną frazę. Nasz wybór zostanie wpisany do aktywnej komórki arkusza.
Zadanie do najłatwiejszych nie należy, rozwiązanie będzie złożone i długie (dlatego tak długo zwlekałam ;)). Ale damy radę! Do dzieła!
Mam na imię Malina. Jestem trenerką i zagorzałą fanką Excela. Wierzę, że każdy może się nauczyć tego fantastycznego programu. Jak? Od tego własnie jestem :). Więcej o mnie ->
