Excel: Użyj tabeli jako listy rozwijanej do sprawdzania poprawności danych

Walidacja danych: jak sprawić, by komórka wybierała wartości z listy rozwijanej

Tworzenie tabeli w MS Excel może być bardzo frustrujące, jeśli będziesz powtarzać te same dane wejściowe.

Jednocześnie jest to bardzo wygodne, jeśli po prostu wybierzesz zawartość wejściową z określonej listy rozwijanej.

Zobaczmy, jak użyć „reguły walidacji”, funkcji, która to umożliwia.

Poniższa tabela to lista wydatków dla osoby fizycznej. Data, rodzaj wydatku i wartość są wprowadzane za każdym razem, gdy wydawane są jakieś pieniądze.

Korzystając z tej tabeli, ustawmy kolumnę B tak, aby typy wydatków można było wybierać z listy rozwijanej i tylko z niej, aby uniknąć ich ustawiania za każdym razem i uniknąć błędów we wprowadzaniu danych.

Rozwiązanie 1: Utwórz listę przez bezpośrednie wprowadzenie

Zacznijmy od wybrania komórki, dla której chcemy ustawić listę rozwijaną.

Wybierz opcję „Sprawdzanie poprawności danych” z zakładki „Dane” w programie Excel, może być wyświetlana tylko jako mała ikona.

Na karcie ustawień wyświetl rozwijaną opcję zezwalania i wybierz opcję Lista. Umożliwi to utworzenie lub wybranie listy dozwolonych wartości dla wybranych komórek danych.

W polu źródłowym po prostu wprowadź wartości, na które chcesz zezwolić, oddzielone przecinkami.

Po prostu zatwierdź, klikając OK, a lista rozwijana weryfikacji zostanie utworzona dla wybranych komórek.

Jeśli wybierzesz jedną z tych komórek, na końcu komórki pojawi się ikona, a po jej kliknięciu zostanie wyświetlona rozwijana lista sprawdzania poprawności, umożliwiająca wybranie wartości, która ma zostać automatycznie wprowadzona do komórki na podstawie lista bezpośredniego wprowadzania danych.

Rozwiązanie 2: Wybierz zakres istniejących wartości

Podobnie, zamiast ręcznie wpisywać wartości, możesz określić zakres dozwolonych wartości, wybierając listę, która już istnieje w skoroszycie.

Najlepszą praktyką podczas tworzenia kilku rozwijanych list sprawdzania poprawności w programie Microsoft Excel jest posiadanie jednego lub więcej określonych arkuszy, które zawierają tylko różne listy weryfikacji, aby ułatwić ich znajdowanie i aktualizowanie.

Po utworzeniu listy dozwolonych wartości, w naszym przykładzie w tym samym arkuszu, aby była bardziej dostępna wizualnie, wybierz komórki, na których ma zostać wymuszona weryfikacja danych.

Następnie w podobny sposób otwórz menu walidacji danych z zakładki danych, z rozwijanego menu wybierz typ listy, a dla źródła zamiast wpisywać własne wartości kliknij przycisk po prawej stronie pola.

Zostaniesz przekierowany do arkusza roboczego i wszystko, co musisz zrobić, to kliknąć pierwszą komórkę tabeli walidacji danych, przesunąć kursor myszy do ostatniej komórki zawierającej jedną z prawidłowych wartości, przytrzymując kliknięcie i zwolnić kliknięcie dopiero po osiągnięciu ostatniej komórki wartości sprawdzania poprawności danych.

Wartości, które wybrałeś w ten sposób, będą przywoływane na liście walidacji danych i możesz po prostu dodać wartości do walidacji danych - lub je usunąć - aktualizując tę ​​listę.

Ustaw wpisy sprawdzania poprawności danych w innym arkuszu

Zostań profesjonalistą Excel: Dołącz do naszego kursu!

Podnieś swoje umiejętności z nowicjusza do bohatera dzięki naszemu kursowi podstawowe programu Excel 365, zaprojektowane tak, abyś był biegły w kilku sesjach.

Zapisz tutaj

Aby lepiej zorganizować skoroszyt, może być łatwiej utworzyć listy sprawdzania poprawności danych w określonym skoroszycie, aby można było je łatwo znaleźć i uzyskać do nich dostęp, bez zastanawiania się, gdzie są ustawione.

Umieść tam swoje dane, wybierz tabelę i użyj tabel opcji: tabela na karcie wstawiania, aby przekształcić listę wartości w tabelę, która zostanie automatycznie zmieniona podczas dodawania lub usuwania wartości - tak samo będzie z walidacją danych.

Następnie kliknij w dowolnym miejscu stylizowanej tabeli i wprowadź nazwę tabeli na karcie projektu tabeli.

Umożliwi to odwołanie się do tej tabeli w dowolnym miejscu, w tym na liście sprawdzania poprawności danych, po prostu wpisując nazwę tabeli zamiast zakresu!

Jak zezwolić na puste komórki na liście sprawdzania poprawności danych

Jeśli chcesz zezwolić na puste wartości w polu, które ma włączoną walidację danych, musisz wybrać jedną z komórek, dla których aktywowano walidację danych i przejść do zakładki danych, aby otworzyć menu sprawdzania poprawności danych (patrz powyżej, gdzie przycisk jest ).

Tam upewnij się, że opcja ignoruj ​​puste jest zaznaczona i kliknij „Zastosuj te zmiany do wszystkich innych komórek z tymi samymi ustawieniami” przed kliknięciem OK.

Będziesz wtedy mógł opróżnić komórkę, zaznaczając ją i usuwając zawartość, bez otrzymywania komunikatu o błędzie określającego, że zawartość komórek jest niedozwolona.

Jak wyłączyć puste wartości na liście sprawdzania poprawności danych

Jeśli nie chcesz, aby puste wartości były dozwolone na liście rozwijanej sprawdzania poprawności danych i za każdym razem, gdy ją wybierzesz, otrzymujesz komunikat o błędzie „wartość w tej komórce jest nieprawidłowa lub brakuje”, zacznij od wybrania zakresu komórek, w których znajdują się dane ma zastosowanie walidacja lub dowolna komórka na tej liście.

Następnie otwórz menu sprawdzania poprawności danych na karcie danych i odznacz pole „ignoruj ​​puste”. Jeśli wybrałeś tylko jedną komórkę, zaznacz „Zastosuj te zmiany do wszystkich innych komórek z tymi samymi ustawieniami” przed kliknięciem OK.

Wracając do listy rozwijanej sprawdzania poprawności danych, pojawi się błąd - i nie będzie już można pozostawić pustej komórki.

Jak usunąć listę rozwijaną sprawdzania poprawności danych

Po zakończeniu ograniczania zakresu dozwolonych wartości na liście wybierz dowolną komórkę z tej listy.

Następnie otwórz menu sprawdzania poprawności danych z zakładki danych - zobacz powyżej w pierwszej sekcji, jak to zrobić - i upewnij się, że opcja „zastosuj te zmiany do wszystkich innych komórek z tymi samymi ustawieniami” jest zaznaczona, w przeciwnym razie usuniesz tylko dane walidacja na aktualnie wybranych komórkach.

Następnie kliknij wyczyść wszystko, a wybrana walidacja danych zostanie usunięta ze skoroszytu MS Excel!

VBA: Dodaj listę weryfikacji

Dodanie listy sprawdzania poprawności za pomocą VBA jest tak proste, jak użycie poniższej funkcji, gdzie A1 to komórka, w której dane zostaną sprawdzone względem listy sprawdzania danych przechowywanej w komórkach B1 do B5.

Zakres („a1”). Validation _ .Modify xlValidateList, xlValidAlertStop, "= $ B $ 1: $ B $ 5"

Korzystając z zaawansowanych właściwości funkcji listy sprawdzania poprawności danych VBA, możliwe jest tworzenie wielu zależnych list rozwijanych w Excel VBA, z których każda weryfikuje różne pola na różnych listach sprawdzania danych i ma własne tytuły, tytuły błędów, komunikaty i komunikaty o błędach .