Sposób, który opisuję w tym artykule to genialny pomysł członka mojej grupy na Facebooku – Marcina. Chodzi o to, aby z zakresu danych usunąć co drugą kolumnę. Można to wykonać w kilku bardzo prostych krokach…
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 tego nie robią.
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, a za tyle nie mamy kasy. Przekaz miał być jak najprostszy i najbardziej zrozumiały – na pierwszy rzut oka miało być wiadomo, o co chodzi. 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) i posłużyłam się czerwonym i zielonym kolorem. Dorzuciłam miniraport dla bardziej dociekliwych. 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 też 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.
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 ->
