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 :).
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.
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 robimy checklistę z zadaniami. May na niej informację o zadaniu, osobie odpowiedzialnej, przewidywanej kwocie przychodu z danego zadania i komentarz. Jak to na checkliście – będziemy zadania oznaczać jako gotowe. Do tego też jest oddzielna kolumna Wykonane, w której zadania gotowe oznaczamy jako “TAK”.
I wtedy też chcemy, aby zadanie zostało przekreślone. Czyli ustawiamy formatowanie warunkowe, uzależnione od wartości komórki w kolumnie Wykonane. Natomiast po zastosowaniu zwykłego przekreślenia wartości komórki otrzymujemy dokładnie taki efekt: przekreślenie wartości.
Mi się to przekreślenie nie za bardzo podoba, ponieważ przekreśla tylko wartość, a nie całą komórkę od początku do końca. Zobacz o co mi chodzi na poniższym obrazku (dolna tabela zawiera przekreślenie, które chcę uzyskać):
Wystarczy trochę pobawić się z formatowaniem niestandardowym i da się taki efekt uzyskać :). Do dzieła więc!
Zadanie na dziś to stworzenie listy rozwijanej, zawierającej nazwy arkuszy w pliku. Powiedzmy, że oddziały naszego sklepu są w różnych miastach, każde miasto ma swój arkusz. My chcemy do głównego arkusza pobrać sumę sprzedaży z wybranego miasta. Interesujące nas miasto (arkusz) chcemy wybrać z listy rozwijanej, i na podstawie naszego wyboru ma się obliczyć suma sprzedaży z tego miast. O tak:
Jak już będziemy mieli nazwę arkusza to dalej pikuś – kiedyś już to opisywałam. Natomiast samo uzupełnienie listy – tutaj bez VBA się nie obejdzie – będą pętle, liczniki i inne bajerki, więc do dzieła!
Chcemy porównać ze sobą sprzedaż i plan z 2 lat. Przedstawienie tego na jednym wykresie będzie nieczytelne, a na 2 oddzielne wykresy nie mamy miejsca i ochoty. Chcemy więc stworzyć jeden wykres, ale taki, aby użytkownik mógł decydować, czy chce zobaczyć na nim porównanie sprzedaży czy planu. O tak:
Wygląda groźnie i skomplikowanie, ale zobaczysz, że do zrobienia tego zadania potrzebujemy sprytnego ułożenia danych i jednej funkcji… :). Let’s go!
Na podstawie wyboru z listy rozwijanej do formatki mają się wpisać określone dane. Jedni użyją tego mechanizmu do pomocy przy tworzeniu świadectw pracy, inni do pracy z umowami, jeszcze inni – do tworzenia ofert dla klientów.
Najlepsze jest to, że niezależnie od zastosowania – potrzebujemy tego samego mechanizmu, aby osiągnąć ten sam efekt:
A tym mechanizmem jest nic innego, jak ukochana przez wszystkich (no… prawie wszystkich) funkcja WYSZUKAJ.PIONOWO! I o niej dzisiaj 🙂
Ostatnio prowadziłam szkolenie, pod koniec którego jeden z uczestników zapytał mnie jak zaznaczyć cały wiersz tabeli, jeśli w wybranej kolumnie występuje wybrany wpis. Ponieważ nie starczyło już czasu na pełną odwiedź na to pytanie – skierowałam go na blog, żeby na spokojnie o tym doczytał. Zaczęłam szukać konkretnego wpisu, bo byłam przekonana, że taki jest, a tutaj patrzę: nie ma! Jest kilka innych, wykorzystujących tę samą technikę (np. wartości w 2 kolumnach musiały już kiedyś wystąpić, część wspólna warunków, lub inna wersja części wspólnej warunków), natomiast nie było takiego stricte najprostszego przykładu!
Pomyślałam: no nie, tak nie może być! Przecież to klasyka!
Ten wpis będzie nadrabiał tę niedoróbkę i omówię w nim jak zrobić, aby po wybraniu z listy rozwijanej działu – w tabeli z danymi podświetlały się całe wiersze, w których ten dział występuje. Czyli chcę uzyskać taki efekt:
Tworząc formularze w Excelu zazwyczaj chcemy je później ochronić, aby użytkownicy nic w nich nie napsuli. W tym celu korzystamy oczywiście z ochrony arkusza. Wszystko ładnie pięknie, natomiast gdy tę ochronę zakładamy, to… formularz nagle przestaje nam działać i pojawia się komunikat o chronionej komórce! Pokazuje to poniższy obrazek:
Ale czy to na pewno jest tak, że to formularz, a konkretnie formanty formularza, przestają działać? Dlaczego w takim razie Excel krzyczy, że komórka jest chroniona? Jest na to bardzo prosta odpowiedź, jednocześnie pokazująca, że często problem tkwi zupełnie gdzieś indziej, niż nam się wydaje.
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 ->
