Jak wyróżnić duplikaty w Excelu: Kompletny przewodnik krok po kroku

Metoda 1: Utwórz prostą listę rozwijaną z wpisanej listy

Najlepsza dla: Krótkie, stałe listy, które rzadko się zmieniają.

Aby utworzyć listę rozwijaną w Excelu, wybierz komórkę, w której chcesz umieścić listę, przejdź do zakładki dane, kliknij walidację danych, wybierz Lista w polu zezwalaj, i określ zakres źródłowy lub ręcznie wpisz elementy oddzielone przecinkami. Możliwe jest wpisywanie elementów ręcznie lub odwołanie się do zakresu komórek w listach rozwijanych w Excelu.

Kroki:

1. Wybierz komórkę lub zakres komórek, gdzie chcesz listę rozwijaną.
2. Kliknij zakładkę dane na wstążce.
3. W grupie narzędzi danych kliknij kliknij walidację danych. Okno dialogowe walidacji danych otwiera się.
4. W polu zezwalaj wybierz Lista z menu rozwijanego.
5. W komórce pojawia się pole wyboru w komórce. Opcja rozwijania w komórce musi być zaznaczona, aby umożliwić menu rozwijane w komórce.
6. Kliknij pole źródłowe i wpisz swoje opcje jako lista oddzielana przecinkami, na przykład: Elektronika,Meble,Software,Uslugi
7. Kliknij OK.

W komórce teraz pojawia się mała strzałka. Klikając ją, otwiera się menu rozwijane pokazujące wszystkie opcje z listy rozwijanej. Użytkownicy mogą skopiować komórkę z listą rozwijaną i wkleić ją gdzie indziej, zachowując funkcjonalność listy rozwijanej, co jest szybkim sposobem na zastosowanie tej samej listy do innych komórek.

Unikaj spacji po przecinkach w liście oddzielanej przecinkami, chyba że chcesz, aby etykieta miała wiodącą spację. "Ameryka Północna, EMEA" pokaże " EMEA" z wiodącą spacją.

Propozycja zrzutu ekranu: Pokaż okno dialogowe walidacji danych z aktywną zakładką ustawienia, Lista w polu zezwalaj i lista oddzielana przecinkami w polu źródłowym. Docelowa komórka powinna być zaznaczona w tle za pomocą pliku demo formularza zamówień.

Metoda 2: Utwórz listę rozwijaną z zakresu komórek

Najlepsza dla: Listy z wieloma elementami lub gdy to samo źródło danych jest używane w wielu komórkach.

Odniesienie do zakresu komórek jako dane źródłowe jest bardziej elastyczne niż wpisywanie elementów. Kiedy aktualizujesz zakres źródłowy, opcje rozwijane we wszystkich połączonych komórkach aktualizują się również.

1. Wprowadź swoje elementy listy w kolumnie, najlepiej na osobnym arkuszu (na przykład arkusz o nazwie "Listy").
2. Wybierz komórkę, gdzie chcesz listę rozwijaną.
3. Idź do zakładki dane> grupa narzędzi danych> wybierz walidację danych.
4. W oknie walidacji danych, na zakładce ustawienia, wybierz Lista w polu zezwalaj.
5. Kliknij pole źródłowe i wybierz zakres komórek na swoim arkuszu list. Excel automatycznie wprowadza odniesienie zakresu, na przykład =Listy!$A$3:$A$7.
6. Kliknij OK.

Kryteria walidacji teraz ogranicza wpisy tylko do poprawnych danych z tego zakresu źródłowego. Pole nazwy w lewym górnym rogu pokazuje bieżący adres komórki i pomaga zweryfikować, że wybrano prawidłowe odniesienie.

Zawsze używaj odniesień absolutnych z znakami $ w zakresie źródłowym. Użycie =A3:A7 bez znaków dolara może przesunąć się, jeśli wiersze zostaną wprowadzone powyżej.

Propozycja zrzutu ekranu: Pokaż okno dialogowe walidacji danych z odniesieniem zakresu w polu źródłowym i widoczny arkusz Listy na dole. Użyj pliku demo formularza zamówień.

Metoda 3: Użyj zakresu o nazwie jako źródła

Najlepsza dla: Listy rozwijane używane w wielu lokalizacjach lub gdy dane źródłowe są na innym arkuszu.

Zakres o nazwie przypisuje zapamiętywalną nazwę grupie komórek, którą można następnie użyć w polu źródła zamiast adresu komórki.

1. Wybierz elementy listy, a następnie kliknij pole nazwy i wpisz nazwę (na przykład kategorieProduktow), a następnie naciśnij Enter.
2. Wybierz komórkę, w której chcesz listę rozwijaną, przejdź do zakładki dane> walidacja danych.
3. Na zakładce ustawienia wybierz Lista w polu zezwalaj, a następnie wpisz =KategorieProduktow w polu źródłowym.
4. Kliknij OK.

To ułatwia zarządzanie listą walidacji. Zakresy o nazwie również umożliwiają zależne listy rozwijane, gdy są łączone z funkcją INDIRECT (omówioną w Metodzie 5).

Propozycja zrzutu ekranu: Pokaż pole nazwy z wpisaną nazwą zakresu i pole źródłowe walidacji danych pokazujące =KategorieProduktow.

Metoda 4: Dodaj komunikaty wejściowe i alerty błędów

Najlepsza dla: Udostępnione skoroszyty lub formularze wypełniane przez inne osoby.

Użytkownicy mogą dodawać komunikaty wejściowe, które wyświetlają się, gdy użytkownik kliknie komórkę z listą rozwijaną, aby podpowiedzieć, co wybrać. Excel pozwala na konfigurację niestandardowych komunikatów błędów w oknie dialogowym Walidacji Danych.

Komunikat wejściowy:

1. Otwórz zakładkę dane> walidacja danych.
2. Kliknij zakładkę komunikatu wejściowego w oknie dialogowym walidacji danych.
3. Zaznacz pokaż komunikat wejściowy po zaznaczeniu komórki i wpisz tytuł oraz komunikat w polach poniżej.
4. Kliknij OK.

Kiedy ktoś kliknie tę komórkę, komunikat wejściowy pojawi się jako podpowiedź do ich wyboru.

Alert błędu:

1. Kliknij kartę alertu błędu w oknie dialogowym walidacji danych.
2. Zaznacz pole wyboru wyświetlania alertu błędu (oznaczone także jako alert błędu po wprowadzeniu błędnych danych lub alert po wprowadzeniu błędnych danych).
3. Wybierz Styl: Stop blokuje nieprawidłowe dane, Ostrzeżenie pozwala na nie z monitorem, Informacja pokazuje tylko notatkę.
4. Wprowadź tytuł i komunikat błędu.
5. Kliknij OK.

Kiedy nieprawidłowe dane zostaną wprowadzone poza dozwoloną listą, wywoływany jest alert błędu. Dla ścisłych formularzy, użyj Stop, aby zaakceptować tylko poprawne dane. Ustawienie alertu błędu i zakładka komunikatu wejściowego są kontrolowane niezależnie, więc można włączyć jedno bez drugiego.

Jeśli chcesz, aby puste komórki były dozwolone, zaznacz pole Ignoruj puste komórki na zakładce ustawienia. To uniemożliwia wywołanie alertu błędu na pustych komórkach.

Propozycja zrzutu ekranu: Pokaż kartę alertu błędu okna dialogowego walidacji danych z zaznaczoną opcją Stop i wpisanym niestandardowym komunikatem błędu. Użyj pliku demo formularza HR.

Metoda 5: Buduj zależne listy rozwijane

Najlepsza dla: Wybór wielopoziomowy, gdzie druga lista zależy od pierwszej.

Zależne listy rozwijane w Excelu łączą dwie lub więcej list rozwijanych, gdzie wybór pierwszej listy kontroluje dostępne opcje w drugiej liście. Na przykład, jeśli użytkownik wybiera Pizzę z pierwszej listy rozwijanej, druga lista rozwijana może być wypełniona konkretnymi elementami pizzy, podczas gdy wybór Chińskie pokaże dania chińskie na drugiej liście. Tworzenie zależnych list rozwijanych może poprawić efektywność i dokładność wprowadzania danych poprzez zapewnienie, że użytkownicy widzą tylko odpowiednie opcje na podstawie swoich wcześniejszych wyborów.

Konfiguracja:

1. Utwórz swoje dane źródłowe w kolumnach, jedna kolumna na kategorię. Wiersz nagłówkowy każdej kolumny musi dokładnie odpowiadać temu, co pojawia się na pierwszej liście rozwijanej.
2. Nazwij każdą kolumnę elementów, używając pola nazwy, dokładnie dopasowując pierwszą opcję listy (na przykład nazwij kolumnę Pizza "Pizza", a kolumnę Chińska "Chińska").
3. Utwórz pierwszą listę rozwijaną w kolumnie A używając Metody 1 lub Metody 2.
4. Wybierz komórkę dla drugiej listy rozwijanej (kolumna B). Idź do zakładki dane> lista walidacji danych wybierz w zakładce ustawienia, wybierz Lista w polu zezwalaj, a w polu źródłowym wpisz =INDIRECT(A4).
5. Kliknij OK.

Kiedy wartość jest wybrana z pierwszej listy rozwijanej, funkcja INDIRECT wyszukuje zasięg nazwany o tej samej nazwie i używa go jako listy wybierania dla drugiej listy rozwijanej. Kryteria walidacji dla drugiej komórki aktualizują się dynamicznie w oparciu o pierwsze zaznaczenie.

Propozycja zrzutu ekranu: Pokaż plik demo zależnej listy rozwijanej z wybraną Pizzą w kolumnie A i widocznymi elementami menu pizzy w rozwijanej kolumnie B. Arkusz Menu List powinien być widoczny w tle.

Metoda 6: Utwórz dynamiczną listę rozwijaną

Najlepsza dla: Listy, które zwiększają się z czasem i muszą się automatycznie aktualizować, gdy nowe elementy są dodawane.

Możesz utworzyć dynamiczną listę rozwijaną w Excelu za pomocą funkcji OFFSET, która pozwala, aby lista automatycznie się aktualizowała, gdy nowe elementy dodawane są do zakresu źródłowego. Użycie tabeli excel jako źródła dla listy rozwijanej pozwala, aby się automatycznie rozszerzała, gdy nowe wiersze są dodawane, co czyni ją świetną opcją dla dynamicznych list.

Opcja A: Tabela Excel

1. Wybierz swoje elementy źródłowe i naciśnij Ctrl + T (z zakładki Wstaw) aby utworzyć tabelę excel. Wybierz styl tabeli i potwierdź opcję wiersza nagłówkowego.
2. Odnieś się do kolumny tabelarycznej w polu źródłowym walidacji danych: =Tabela1[Kategoria].

Gdy nowy wiersz zostanie dodany do tabeli excel, opcje listy rozwijanej aktualizują się automatycznie bez potrzeby zmiany reguły walidacji.

Opcja B: formuła OFFSET

Aby utworzyć dynamiczną listę rozwijaną, można użyć formuły =OFFSET(Arkusz2!$A$1,0,0,COUNTA(Arkusz2!$A:$A),1), która dostosowuje zakres na podstawie liczby niepustych komórek w kolumnie źródłowej.

Wprowadź to w polu źródłowym okna walidacji danych. Gdy elementy są dodawane do kolumny źródłowej, zakres źródłowy rozszerza się, a opcje listy rozwijanej aktualizują się automatycznie.

Propozycja zrzutu ekranu: Pokaż pole źródłowe walidacji danych z wprowadzoną formułą OFFSET i widoczny arkusz Listy. Użyj pliku demo formularza zamówień.

Metoda 7: Dodawaj, Edytuj i Usuwaj elementy listy rozwijanej

Zarządzanie elementami w zakresie źródłowym:

Aby dodać element do istniejącej listy rozwijanej, przejdź do oryginalnego arkusza zawierającego listę, kliknij prawym przyciskiem myszy komórkę poniżej, gdzie chcesz wstawić, wybierz Wstaw, wybierz przesunięcie komórek w dół i wprowadź nowy element. Opcje listy rozwijanej aktualizują się automatycznie. Na tym samym arkuszu lub między arkuszami te same kroki mają zastosowanie, o ile zakres źródłowy obejmuje nową komórkę.

Usuwanie listy rozwijanej:

Aby usunąć listę rozwijaną w Excelu, wybierz komórkę z listą rozwijaną, przejdź do zakładki dane, kliknij na walidację danych, a następnie kliknij usuń (Wyczyść wszystko), aby usunąć listę. Aby wyczyścić tylko wartość komórki bez usuwania reguły walidacji, wybierz komórkę i naciśnij delete. Aby usunąć regułę całkowicie, użyj Wyczyść wszystko w oknie walidacji danych.

Zauważ, że jeśli chcesz nacisnąć delete tylko na zawartość komórki i zachować walidację, możesz to zrobić bezpiecznie. Lista walidacji pozostaje na komórce, nawet po wyczyszczeniu zawartości.

Ochrona hasłem komórek zweryfikowanych:

Aby powstrzymać innych przed edycją ustawień listy rozwijanej, przejdź do Recenzja > Chroń arkusz i wprowadź hasło (zabezpiecz hasłem arkusz). Użytkownicy nadal mogą używać list rozwijanych do wprowadzania danych, ale nie mogą modyfikować kryteriów walidacji ani usuwać reguł.

Propozycja zrzutu ekranu: Pokaż okno dialogowe walidacji danych z widocznym przyciskiem Wyczyść wszystko, gotowe do usunięcia reguły. Pokaż także okno dialogowe Chroń arkusz z aktywnym polem hasła.

Typowe problemy i rozwiązywanie problemów

Strzałka rozwijana nie pojawia się

Potwierdź, że pole rozwijane w komórce jest zaznaczone na zakładce ustawienia. Jeśli jest odznaczone, walidacja wciąż działa, ale żadna strzałka nie pojawia się w komórce. Jest to najczęściej pomijane ustawienie, gdy lista rozwijana wydaje się zniknąć.

Alert błędu wyzwala się na poprawnych danych

Zazwyczaj jest to błąd formatowania. Sprawdź pole źródłowe pod kątem zbędnych spacji wokół przecinków w liście oddzielanej wpisem przecinkowym lub sprawdź dane źródłowe pod kątem pustych komórek lub niespójnego kapitalizowania. Kryteria walidacji porównują wartości dokładnie.

Dynamiczna lista nie aktualizuje się automatycznie

Jeśli źródło jest statycznym zakresem komórek, nowe elementy dodane poza tym zakresem się nie pojawią. Przejdź do tabeli excel lub formuły OFFSET, aby zakres źródłowy rozszerzał się automatycznie. Lista walidacji danych musi odwoływać się do zakresu, który obejmuje wszystkie obecne i przyszłe elementy.

Zależna lista pokazuje błąd odwołania

Oznacza to, że zakres nazwany nie pasuje dokładnie do pierwszego wyboru listy rozwijanej. Otwórz Formuły > Menadżer Nazw i potwierdź, że nazwy pasują dokładnie do opcji w pierwszej liście rozwijanej, w tym kapitalizacja. Każda niezgodność powoduje, że INDIRECT zwraca pustosć lub błąd we wszystkich komórkach używających zależnej reguły.

Szybki przewodnik: metody list rozwijanych

Dla deweloperów: Dodaj walidację danych z IronXL

Jeśli twoja aplikacja .NET generuje skoroszyty Excel z formularzami lub szablonami wprowadzania danych, IronXL umożliwia stosowanie list rozwijanych walidacji danych programowo w języku C# bez Microsoft Office na serwerze. Zalecane podejście to AddFormulaListRule(), który odnosi się do zakresu komórek jako źródła i unika ograniczenia ciągu do 255 znaków, które ma zastosowanie przy przekazywaniu elementów listy jako ciągu bezpośrednio.

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet formSheet = workBook.DefaultWorkSheet;
formSheet.Name = "Order Form";

// Headers
formSheet["A1"].Value = "Order ID";
formSheet["B1"].Value = "Product Category";
formSheet["C1"].Value = "Region";

// Write source list values to a Lists sheet
WorkSheet listSheet = workBook.CreateWorkSheet("Lists");
string[] categories = { "Electronics", "Furniture", "Software", "Office Supplies", "Services" };
string[] regions = { "North America", "EMEA", "APAC", "Latin America", "Middle East" };

for (int i = 0; i < categories.Length; i++)
{
    listSheet[$"A{i + 1}"].Value = categories[i];
    listSheet[$"B{i + 1}"].Value = regions[i];
}

// Apply drop-down validation referencing the Lists sheet source range
var categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           // cell range to validate
    "Lists!$A$1:$A$5"  // source range on Lists sheet
);
categoryRule.ShowErrorBox = true;
categoryRule.ErrorBoxTitle = "Invalid Category";
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list.";
categoryRule.ShowPromptBox = true;
categoryRule.PromptBoxTitle = "Product Category";
categoryRule.PromptBoxText = "Choose a product category from the drop-down.";

var regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
);
regionRule.ShowErrorBox = true;
regionRule.ErrorBoxTitle = "Invalid Region";
regionRule.ErrorBoxText = "Select a valid region from the drop-down list.";

workBook.SaveAs("order-form-validated.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim formSheet As WorkSheet = workBook.DefaultWorkSheet
formSheet.Name = "Order Form"

' Headers
formSheet("A1").Value = "Order ID"
formSheet("B1").Value = "Product Category"
formSheet("C1").Value = "Region"

' Write source list values to a Lists sheet
Dim listSheet As WorkSheet = workBook.CreateWorkSheet("Lists")
Dim categories As String() = {"Electronics", "Furniture", "Software", "Office Supplies", "Services"}
Dim regions As String() = {"North America", "EMEA", "APAC", "Latin America", "Middle East"}

For i As Integer = 0 To categories.Length - 1
    listSheet($"A{i + 1}").Value = categories(i)
    listSheet($"B{i + 1}").Value = regions(i)
Next

' Apply drop-down validation referencing the Lists sheet source range
Dim categoryRule = formSheet.DataValidations.AddFormulaListRule(
    "B2:B20",           ' cell range to validate
    "Lists!$A$1:$A$5"   ' source range on Lists sheet
)
categoryRule.ShowErrorBox = True
categoryRule.ErrorBoxTitle = "Invalid Category"
categoryRule.ErrorBoxText = "Please select a valid category from the drop-down list."
categoryRule.ShowPromptBox = True
categoryRule.PromptBoxTitle = "Product Category"
categoryRule.PromptBoxText = "Choose a product category from the drop-down."

Dim regionRule = formSheet.DataValidations.AddFormulaListRule(
    "C2:C20",
    "Lists!$B$1:$B$5"
)
regionRule.ShowErrorBox = True
regionRule.ErrorBoxTitle = "Invalid Region"
regionRule.ErrorBoxText = "Select a valid region from the drop-down list."

workBook.SaveAs("order-form-validated.xlsx")

IronXL działa na .NET 6 i nowszych, kompatybilny z Windows, Linux, macOS, Docker i Azure. Aby uzyskać pełne szczegóły, zobacz dokumentację API walidacji danych.

Rozpoczęcie: Zainstaluj przez NuGet za pomocą Install-Package IronXL.Excel. Dostępny jest bezpłatny okres próbny z 30 dniami pełnej funkcjonalności i bez potrzeby posiadania karty kredytowej.

Dalsza lektura:

• Dokumentacja API walidacji danych IronXL
• Twórz pliki Excel w .NET z IronXL
• Poradnik wprowadzający do IronXL

Podsumowanie

Umieszczenie listy rozwijanej w komórkach Excel trwa mniej niż minutę, gdy wiesz, gdzie się udać. Dla szybkiej listy rozwijanej w komórce z kilkoma stałymi wyborami, wpisanie listy oddzielanej przecinkami bezpośrednio w polu źródłowym okna dialogowego walidacji danych jest najszybszą drogą. Dla wszystkiego, co musi się skalować lub aktualizować z czasem, odniesienie do zakresu o nazwie lub tabeli excel daje opcjom listy rozwijanej miejsce do rozwoju. Lista walidacji danych pozostaje połączona z danymi źródłowymi, więc dodanie nowego elementu do zakresu źródłowego automatycznie udostępnia go w każdej połączonej komórce.

Kiedy wiele osób wypełnia tę samą arkuszę kalkulacyjną, komunikat na zakładce komunikatu wejściowego pomaga im, zanim dokonają wyboru, a alert błędu na poziomie Stop na zakładce alertu błędu zapewnia, że żadne nieprawidłowe dane nigdy nie trafią do zweryfikowanej komórki. Zależne listy rozwijane zbudowane za pomocą INDIRECT i zakresów nazwanych pozwalają, aby pierwsza lista rozwijana automatycznie kontrolowała drugą, utrzymując zestaw opcji odpowiedni bez dodatkowej pracy użytkownika.

Dla deweloperów, którzy muszą generować zweryfikowane pliki Excel z aplikacji .NET, IronXL obejmuje pełny przepływ pracy walidacji danych: zakresy źródłowe, alerty błędów, monity wejściowe i reguły listy formuł, wszystko bez potrzeby instalowania Office na serwerze. Zacznij od bezpłatnej wersji próbnej, aby przetestować walidację danych w twoim własnym projekcie.

Czy masz scenariusz z listą rozwijaną, którego ten przewodnik nie obejmował? Zostaw komentarz poniżej lub odwiedź blog Iron Software po więcej poradników na temat wprowadzania danych Excel, walidacji i automatyzacji arkuszy kalkulacyjnych.