Excel samouczek - Lista Rozwijana

Listy rozwijane wykorzystują mechanizm poprawności danych. Dzięki niemu jesteśmy w stanie zarówno ograniczyć liczbę błędów wprowadzanych przez końcowego użytkownika, jak też pomóc mu wprowadzać poprawne dane do konkretnych komórek. 

Jak wyświetlić dni tygodnia w Excelu?

Poprawność danych możemy wykorzystać np. Jeżeli chcemy pomóc użytkownikom wpisywać poprawne nazwy dni tygodnia.

1. Aby je wstawić w arkuszu powinniśmy wpisać nazwy dni tygodnia (np. od komórki A1 do A7).
2. Później należy zaznaczyć komórki, w których chcemy wykorzystać opcję sprawdzania danych (w naszym przypadku właśnie - od A1 do A7).
3. Następnie należy przejść do karty Dane, grupy Narzędzia danych i wybierać opcję Poprawność danych.
4. Zobaczymy okno takie jak na rys. 1 

 

Rys. 1. Sprawdzanie poprawności danych

 

5. Z dozwolonych kryteriów poprawności wybieramy Listę i wklejamy listę dni tygodnia, którą zrobiliśmy w osobnym arkuszu (Rysunek 2).

 



Rys. 2. Dni tygodnia

6. Po zaakceptowaniu reguły można wprowadzać w odpowiedni sposób dane. Jeśli aktywujemy komórkę, która ma wprowadzone kryteria poprawności danych (w tym przypadku dni tygodnia), to pojawi się charakterystyczny znak  , 
7. Po kliknięciu na niego pojawi się lista, z której możemy wybierać poprawne dane (rys. 3).

Rys. 3. Dostępne opcje wstawiania danych

Jeśli użytkownik wprowadzi coś  innego do komórki i zaakceptuje wprowadzoną zawartość, to otrzyma komunikat błędu (rys. 4).

Rys. 4. Wyświetlenie informacji o wprowadzeniu nieprawidłowych danych

Tworzenie list niestandardowych - akceptowanie tylko wyrazów kończących się na a

Wyobraźmy sobie, że powinniśmy ustalić taką regułę sprawdzania poprawności danych, że każdy tekst, który kończy się na a, powinien zostać wpisany do komórki, natomiast wpisanie innego tekstu powinno wyświetlić błąd: „Wprowadź tekst kończący się na literę a”.

W takim przypadku musimy skorzystać z niestandardowej listy.

Lista przyjmuje formuły oraz tylko te wartości, których sprawdzenie wprowadzonych wartości daje w wyniku prawdę.

Wykonanie tego zadania polega:

1. na zaznaczeniu komórki (niech to będzie komórka np. B2), w którym chcemy zastosować ww. regułę,
2. następnie z karty Dane z grupy Narzędzia danych wybieramy opcję Poprawność danych. 
3. W wyświetlonym polu wprowadzamy porównanie końcowej litery z literą „a” (rys. 5).

Rys. 5. Reguła poprawności sprawdzająca, czy końcowa litera to "a"

Tworzenie list wielopoziomowych

Prześledźmy to zagadnienie na przykładzie. Naszym zadaniem jest - zrobienie listy dwupoziomowej w taki sposób, aby wybranie elementów z pierwszej listy miało wpływ na wybierane elementy z drugiej listy (rys. 6)

Rys. 6. Po wybraniu kategorii, chcemy mieć dostęp do wybranej części produktów

Produkty i kategorie, z których zostanie skonstruowana lista znajduje się poniżej:

Na początku należy zrobić listę dla kategorii.

W tym celu należy skopiować listę kategorii w inne miejsce, usunąć duplikaty oraz zrobić listę dla kategorii. Kolejnym krokiem będzie zrobienie listy dla produktów. Do źródła listy z produktami podajemy następującą formułę:

=PRZESUNIĘCIE($B$3;PODAJ.POZYCJĘ(J3;$A$3:$A$1048576;0)-

1;0;LICZ.JEŻELI($A$3:$A$1048576;J3);1)

Celem funkcji PRZESUNIĘCIE jest zwrócenie pewnego obszaru.

Punktem początkowym tego obszaru jest początek listy produktów (komórka B3). Drugim argumentem jest liczba komórek, o które chcemy przesunąć nasz obszar. W związku z tym, że liczba dla poszczególnych kategorii będzie zmienna (dla Fiata będzie to liczba zero – ponieważ nie chcemy przesuwać komórek, dla Toyoty - pięć, a dla Audi - osiem), to należy wykorzystać  funkcję zwracającą pozycję poszczególnych kategorii.

Położenie poszczególnych kategorii otrzymamy korzystając z funkcji PODAJ.POZYCJĘ.

Trzecim argumentem jest liczba kolumn na prawo lub lewo, o które  będziemy przesuwać, aby znaleźć się na wyznaczonym obszarze. W związku z tym, że obszar, który będzie pokazywał kategorie znajduje się w tej samej kolumnie co komórka początkowa, wartość tego argumentu wynosi zero.

Kolejnymi argumentami funkcji są wartości, które określają wielkość pobieranego obszaru (liczba wierszy oraz liczba kolumn pobieranego obszaru). Liczba wierszy zmienia się w zależności od wybranej kategorii, dlatego określono je z wykorzystaniem funkcji LICZ.JEŻELI, natomiast liczba kolumn jest stała i wynosi jeden.

Automatyczne sprawdzanie poprawności numeru - na przykładzie wprowadzanych numerów dowodu osobistego

Jak będzie wyglądał sposób konstrukcji najprostszej reguły poprawności sprawdzającej czy wprowadzony tekst jest numerem dowodu osobistego?

Zakładamy, że numer dowodu składa się z trzech liter oraz sześciu liczb. W związku z tym założeniem musimy sprawdzić, czy:

1. Pierwsze trzy litery to tekst (dla uproszczenia zadania zakładamy, że np. G12 to tekst)
2. Sześć ostatnich znaków to liczby
3. Długość tekstu to 9
4. Jeśli trzy powyższe warunki są spełnione to zakładamy, że wprowadzony numer jest poprawny

Sprawdzenie, czy tekst ma długość dziewięciu znaków wymaga wykorzystania funkcji DŁ:

=DŁ(B2)=9

Sprawdzenie, czy trzy pierwsze znaki są tekstem wymaga wykorzystania funkcji tekstowej i logicznej:=NIE(CZY.BŁĄD(WARTOŚĆ(LEWY(B2;3))))

Funkcja LEWY odpowiada za wycięcie pierwszych trzech znaków. Po wyciągnięciu tych trzech znaków zastosowanie funkcji CZY.TEKST mija się z celem, ponieważ funkcja LEWY zawsze zwraca wartość tekstową. W związku z tym, należy wykorzystać funkcję konwertującą te litery na coś, co nie jest tekstem. Sprawdzenie czy trzy pierwsze litery jest wartością logiczną mija się z celem, ponieważ wycięcie pierwszym trzech liter z wartości logicznej daje PRA lub FAŁ, co daje w konsekwencji tekst w związku z tym jedyną rozsądną opcją jest wykorzystanie funkcji WARTOŚĆ, która konwertuje liczby w postaci tekstu na liczby, natomiast konwersja pozostałych wartości kończy się błędem #ARG!, co tłumaczy zastosowanie funkcji CZY.BŁĄD. Jeśli mamy do czynienia z tekstem, to funkcja zwróci wartość PRAWDA, w innym przypadku zwróci wartość FAŁSZ

Sprawdzenie, czy sześć ostatnich znaków to liczby jest bardzo podobne do funkcji sprawdzenia tekstu:

=NIE(CZY.BŁĄD(WARTOŚĆ(PRAWY(B2;6))))

Sposób jest identyczny jak w przypadku trzech pierwszych liter z tą różnicą, że wykorzystujemy inny ciąg znaków (skorzystanie z funkcji PRAWY) oraz musimy dołączyć funkcję NIE (w przypadku tekstu otrzymujemy wynik PRAWDA, co nie jest zgodne z naszymi założeniami)Jeśli mamy już te wszystkie warunki to łączymy je za pomocą funkcji ORAZ i otrzymujemy kompletny regułę sprawdzania poprawności:

=ORAZ(DŁ(B2)=9; CZY.BŁĄD(WARTOŚĆ(LEWY(B2;3))); NIE(CZY.BŁĄD(WARTOŚĆ(PRAWY(B2;6)))))

Funkcje znajdujące się w artykule

DŁ(tekst) – funkcja zwracająca długość tekstu (liczbę całkowitą nieujemną).

WARTOŚĆ(tekst) – konwertuje podany tekst na liczbę. Funkcja zwraca liczbę lub błąd (jeśli podany tekst nie może zostać zamieniony na wartość liczbową np. „erzsc”)

CZY.BŁĄD(wyrażenie) – sprawdza, czy podane wyrażenie jest błędem. Jeśli tak, to zwraca wartość logiczną PRAWDA. Jeśli nie – zwraca wartość logiczną FAŁSZ.

LEWY(tekst, ile_znaków) – pobiera określoną ilość znaków od lewej strony.

Autorem artykułu i ilustracji jest Trener Cognity - Grzegorz Plak. 

Możesz być również zainteresowany:

• Szkolenie Excel Przekrojowy Podstawowy
• Szkolenie Analiza Statystyczna w Excelu - zobacz prezentację
• Jak używać funkcji SUMA.JEŻELI

Excel samouczek - Lista Rozwijana by Cognity is licensed under a Creative Commons Uznanie autorstwa-Użycie niekomercyjne 4.0 Międzynarodowe License.