Excel에서 중복 항목을 강조하는 방법: 완벽한 단계별 가이드

방법 1: 입력된 목록에서 간단한 드롭다운 리스트 만들기

적합한 경우: 거의 바뀌지 않는 짧고 고정된 리스트.

엑셀에서 드롭다운 리스트를 만들기 위해, 리스트를 원하는 셀을 선택하고 데이터 탭으로 이동하여, 데이터 검증을 클릭하고 허용 상자에서 리스트를 선택하고 소스 범위를 지정하거나 항목들을 쉼표로 구분하여 수동으로 입력하십시오. 엑셀에서 드롭다운 리스트를 위해 항목을 수동으로 입력하거나 셀 범위를 참조하는 것이 가능합니다.

단계:

1. 드롭다운을 원하는 셀 또는 셀 범위를 선택하십시오.
2. 리본의 데이터 탭을 클릭하십시오.
3. 데이터 도구 그룹에서 데이터 검증을 클릭하십시오. 데이터 검증 대화 상자가 열립니다.
4. 허용 상자에서 드롭다운 메뉴에서 리스트를 선택하십시오.
5. 셀 내 드롭다운 체크 박스가 나타납니다. 셀이 드롭다운 메뉴를 활성화하려면 셀 내 드롭다운 옵션에 체크해야 합니다.
6. 소스 상자를 클릭하고, 옵션을 쉼표로 구분하여 입력하십시오. 예: 전자 상품, 가구, 소프트웨어, 서비스
7. 확인을 클릭합니다.

이제 셀에 작은 화살표가 나타납니다. 이를 클릭하면 드롭다운 메뉴가 열려 모든 드롭다운 옵션을 보여줍니다. 사용자는 드롭다운이 있는 셀을 복사하여 다른 곳에 붙여 넣어 드롭다운 기능을 유지할 수 있으며, 이는 동일한 리스트를 다른 셀에 빠르게 적용하는 방법입니다.

쉼표로 구분된 리스트에서 쉼표 뒤에 공백을 남기지 않도록 주의하십시오, 라벨 앞에 공백을 원하지 않는 경우에만. "북미, EMEA"는 " EMEA"로 앞에 공백과 함께 표시됩니다.

스크린샷 추천: 설정 탭이 활성화된 데이터 검증 대화 상자를 보여주고, 허용 상자의 리스트와 소스 상자의 쉼표로 구분된 리스트를 보여주십시오. 주문 양식 데모 파일을 사용하여 백그라운드에서 대상 셀이 선택되어야 합니다.

방법 2: 셀 범위에서 드롭다운 리스트 만들기

적합한 경우: 항목이 많은 리스트, 또는 동일한 소스 데이터가 여러 셀에 재사용될 때.

소스 데이터로 셀 범위를 참조하는 것이 항목을 입력하는 것보다 유연합니다. 소스 범위를 업데이트할 때, 연결된 모든 셀의 드롭다운 옵션도 업데이트됩니다.

1. 별도의 시트(예: "리스트")에 열에 리스트 항목을 입력하십시오.
2. 드롭다운을 원하는 셀을 선택하십시오.
3. 데이터 탭 > 데이터 도구 그룹 > 데이터 검증 선택으로 이동하십시오.
4. 데이터 검증 창에서, 설정 탭에서, 허용 상자에서 리스트를 선택하십시오.
5. 소스 상자를 클릭하고 리스트 시트에서 셀 범위를 선택하십시오. 엑셀은 예를 들어 =리스트!$A$3:$A$7 형태로 범위 참조를 자동으로 입력합니다.
6. 확인을 클릭합니다.

검증 기준은 이제 해당 소스 범위의 유효한 데이터로만 입력을 제한합니다. 이름 상자는 왼쪽 상단에 현재 셀 주소를 표시하고 올바른 참조가 선택되었는지 확인하도록 도움을 줍니다.

소스 범위에 항상 $ 기호와 함께 절대 참조를 사용하십시오. 달러 기호 없이 =A3:A7를 사용하면 위에 행이 삽입될 경우 변경될 수 있습니다.

스크린샷 추천: 소스 상자에 범위 참조가 있는 데이터 검증 대화 상자를 보여주고 리스트 시트 탭이 아래에 보이도록 하십시오. 주문 양식 데모 파일을 사용하십시오.

방법 3: 소스로 이름 범위 사용

적합한 경우: 여러 위치에서 사용되는 드롭다운 리스트, 또는 소스 데이터가 다른 시트에 있을 때.

이름 범위는 셀 그룹에 기억하기 쉬운 이름을 할당하여 셀 주소 대신 소스 상자에 사용할 수 있습니다.

1. 리스트 항목을 선택한 다음 이름 상자를 클릭하고 이름을 입력하십시오(예: ProductCategories), 그런 다음 엔터를 누르십시오.
2. 드롭다운을 원하는 셀을 선택하고 데이터 탭 > 데이터 검증으로 이동하십시오.
3. 설정 탭에서 허용 상자에 리스트를 선택하고 소스 박스에 =ProductCategories를 입력하십시오.
4. 확인을 클릭하십시오.

이것은 검증 리스트를 관리하기 쉽게 만듭니다. 또한 이름 범위는 INDIRECT 함수와 결합되면 종속 드롭다운 리스트를 활성화합니다(방법 5에서 다룸).

스크린샷 추천: 이름 상자에 범위 이름이 입력되고, 데이터 검증 소스 박스에 =ProductCategories가 표시되도록 하십시오.

방법 4: 입력 메시지 및 오류 경고 추가

적합한 경우: 다른 사람들이 채워넣는 공유 워크북 또는 양식.

사용자는 드롭다운 리스트가 있는 셀을 클릭할 때 표시되는 입력 메시지를 추가하여 사용자가 무엇을 선택해야 하는지 안내할 수 있습니다. 엑셀에서는 데이터 검증 대화 상자 내에서 사용자 정의 오류 메시지를 구성할 수 있습니다.

입력 메시지:

1. 데이터 탭 열기 > 데이터 검증.
2. 데이터 검증 대화 상자에서 입력 메시지 탭을 클릭하십시오.
3. 셀이 선택될 때 입력 메시지 표시를 선택하고 제목과 메시지를 아래 필드에 입력하십시오.
4. 확인을 클릭하십시오.

누군가가 해당 셀을 클릭하면, 입력 메시지가 툴팁으로 나타나 선택을 안내합니다.

오류 경고:

1. 데이터 검증 대화 상자에서 오류 경고 탭을 클릭하십시오.
2. 오류 경고 표시 체크 박스 선택(유효하지 않은 데이터 입력 후 오류 경고, 또는 유효하지 않은 데이터 후 경고 라벨 될 수도 있음).
3. 스타일 선택: 오류는 유효하지 않은 데이터를 차단하고, 경고는 프롬프트와 함께 허용하며, 정보는 메모만 표시합니다.
4. 제목과 오류 메시지를 입력하십시오.
5. 확인을 클릭합니다.

허용된 리스트 외부에서 유효하지 않은 데이터가 입력되면 오류 경고가 발생합니다. 엄격한 양식을 위해서는 오류를 사용하여 유효한 데이터만 허용되도록 하십시오. 오류 경고 표시 설정과 입력 메시지 탭은 독립적으로 제어되므로, 하나만 활성화할 수 있습니다.

빈 셀이 허용되도록 하려면 설정 탭에서 빈 무시 체크 박스를 선택하십시오. 이렇게 하면 빈 셀에서 오류 경고가 발생하지 않도록 방지합니다.

스크린샷 추천: 데이터 검증 대화 상자의 오류 경고 탭을 보여주고 오류가 선택되었으며 사용자 정의 오류 메시지가 입력된 상태를 보여주십시오. HR 양식 데모 파일을 사용하십시오.

방법 5: 종속 드롭다운 리스트 만들기

적합한 경우: 첫 번째에 따라 두 번째 리스트가 달라지는 다단계 선택.

엑셀의 종속 드롭다운 리스트는 두 개 이상의 드롭다운 리스트를 연결하여 첫 번째 리스트의 선택이 두 번째 리스트에서 사용 가능한 옵션을 제어합니다. 예를 들어, 사용자가 첫 번째 드롭다운 리스트에서 피자를 선택하면 두 번째 드롭다운 리스트는 특정 피자 항목으로 채워지고, 중국 요리를 선택하면 두 번째 리스트에 중국 음식이 표시됩니다. 종속 드롭다운 리스트를 생성하면 사용자가 이전 선택에 기반하여 관련 옵션만 보도록 보장하여 데이터 입력 효율성과 정확성을 향상시킬 수 있습니다.

설정:

1. 각 카테고리당 한 열씩, 열에 소스 데이터를 만드십시오. 각 열의 헤더 행은 반드시 첫 번째 드롭다운 리스트에 나타나는 것과 정확히 일치해야 합니다.
2. 항목 열을 이름 상자를 사용하여 이름을 지정하십시오. 첫 번째 리스트 옵션과 정확히 일치하도록 합니다(예를 들어 피자 열을 "피자"로, 중식 열을 "중식"으로 이름 지정).
3. 첫 번째 드롭다운 리스트를 열 A에 만들기(방법 1 또는 방법 2 사용).
4. 두 번째 드롭다운(열 B)에 대한 셀을 선택하십시오. 데이터 탭 > 데이터 검증 리스트 선택으로 이동하여 설정 탭에서 허용 상자에서 리스트를 선택하고 소스 상자에 =INDIRECT(A4)를 입력하십시오.
5. 확인을 클릭합니다.

첫 번째 드롭다운 리스트에서 값이 선택되면 INDIRECT가 같은 이름을 가진 이름 범위를 찾아 두 번째 드롭다운의 선택 리스트로 사용합니다. 두 번째 셀의 검증 기준은 첫 번째 선택에 따라 동적으로 업데이트됩니다.

스크린샷 추천: 종속 드롭다운 데모 파일을 보여주고, 열 A에 피자가 선택되고 열 B 드롭다운에 피자 메뉴 항목이 보이도록 하십시오. 메뉴 리스트 시트가 백그라운드에 보여야 합니다.

방법 6: 동적 드롭다운 리스트 만들기

적합한 경우: 시간이 지나며 성장하고 새 항목이 추가될 때 자동으로 업데이트가 필요할 때.

OFFSET 함수를 사용하여 엑셀에서 동적 드롭다운 리스트를 생성할 수 있으며, 이 함수는 소스 범위에 새 항목이 추가될 때 리스트가 자동으로 업데이트되도록 합니다. 드롭다운 리스트의 소스로 엑셀 테이블을 사용하는 것은 새 행이 추가될 때 자동으로 확장되도록 하여, 동적 리스트에 대한 훌륭한 옵션이 됩니다.

옵션 A: 엑셀 테이블

1. 소스 항목을 선택하고 Ctrl + T(삽입 탭에서)를 눌러 엑셀 테이블을 만드십시오. 테이블 스타일을 선택하고 헤더 행 옵션을 확인하십시오.
2. 데이터 검증 소스 박스에서 테이블 열을 참조하십시오: =Table1[Category].

엑셀 테이블에 새 행이 추가되면, 드롭다운 옵션이 자동으로 업데이트되어 검증 규칙에 대한 변경이 필요 없습니다.

옵션 B: OFFSET 공식

동적 드롭다운 리스트를 만들기 위해, 공식 =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)을 사용할 수 있으며, 이는 소스 열의 비어 있지 않은 셀 수에 따라 범위를 조정합니다.

데이터 검증 창의 소스 박스에 이것을 입력하십시오. 소스 열에 항목이 추가되면, 소스 범위가 확장되고 드롭다운 옵션이 자동으로 업데이트됩니다.

스크린샷 추천: 소스 상자에 OFFSET 공식이 입력된 데이터 검증 소스 박스를 보여주고 리스트 시트를 보이도록 하십시오. 주문 양식 데모 파일을 사용하십시오.

방법 7: 드롭다운 항목 추가, 편집, 제거

소스 범위의 항목 관리:

기존 드롭다운 리스트에 항목을 추가하려면, 리스트가 있는 원래 시트로 가서, 추가하고 싶은 셀 아래에 있는 셀을 마우스 오른쪽 버튼으로 클릭하고 삽입을 선택하고 셀을 아래로 이동시키고 새 항목을 입력하십시오. 드롭다운 옵션은 자동으로 업데이트됩니다. 동일한 워크시트 또는 시트 간에, 소스 범위가 새 셀을 포함하는 한 동일한 단계가 적용됩니다.

드롭다운 제거:

엑셀에서 드롭다운 리스트를 제거하려면, 드롭다운이 있는 셀을 선택하고 데이터 탭으로 가서, 데이터 검증을 클릭하고, 그런 다음 목록을 제거하기 위해 삭제(모두 제거)를 클릭하십시오. 검증 규칙을 제거하지 않고 셀 값만 지우려면 셀을 선택하고 삭제를 누르십시오. 규칙을 완전히 제거하려면 데이터 검증 창의 모든 제거를 사용하십시오.

셀의 내용만 삭제하고 검증을 유지하려면 안전하게 할 수 있습니다. 내용이 삭제된 후에도 검증 리스트는 셀에 남습니다.

검증된 셀 비밀번호 보호:

드롭다운 설정을 다른 사람이 수정하지 못하게 하려면 검토 > 시트 보호로 이동하여 비밀번호를 입력하십시오(워크시트 비밀번호 보호). 사용자는 여전히 드롭다운을 사용하여 데이터를 입력할 수 있지만, 검증 기준을 수정하거나 규칙을 제거할 수 없습니다.

스크린샷 추천: 규칙을 제거하기 위해 모두 제거 버튼이 보이는 데이터 검증 대화 상자를 보여주십시오. 비밀번호 필드가 활성화된 시트 보호 대화 상자도 보여주십시오.

일반적인 문제 및 문제 해결

드롭다운 화살표가 나타나지 않음

설정 탭에서 셀 내 드롭다운 체크 박스가 선택되어 있는지 확인하십시오. 체크 해제된 경우, 검증은 여전히 작동하지만 셀에 화살표가 나타나지 않습니다. 드롭다운이 사라진 것처럼 보일 때 가장 흔히 놓치는 설정입니다.

유효한 데이터에 오류 경고가 발생

대개는 형식 불일치가 원인입니다. 쉼표로 구분된 목록 항목의 소스 상자에 있는 쉼표 주위의 가지치기 공간을 확인하거나, 소스 데이터에서 빈 셀 또는 불일치 대문자가 있는지 확인하십시오. 검증 기준은 값을 정확히 비교합니다.

동적 리스트가 자동으로 업데이트되지 않음

소스가 정적 셀 범위인 경우, 해당 범위 밖에 추가된 새로운 항목은 나타나지 않습니다. 소스 범위가 자동으로 확장되도록 엑셀 테이블이나 OFFSET 공식을 사용하십시오. 데이터 검증 리스트는 현재와 미래 항목을 모두 포함하는 범위를 참조해야 합니다.

종속 리스트가 참조 오류를 표시

이것은 이름 범위가 첫 번째 드롭다운 선택과 정확히 일치하지 않음을 의미합니다. 수식 > 이름 관리자 열고 이름이 첫 번째 드롭다운 리스트의 옵션과 대문자를 포함하여 정확히 일치하는지 확인하십시오. 일치하지 않는 경우 INDIRECT가 빈 셀을 반환하거나 종속 규칙을 사용하는 모든 셀에서 오류를 반환합니다.

빠른 참조: 드롭다운 리스트 방법

개발자를 위한: IronXL로 데이터 검증 추가

귀하의 .NET 응용 프로그램이 양식이나 데이터 입력 템플릿이 있는 Excel 워크북을 생성한다면, IronXL은 서버에 Microsoft Office를 설치하지 않고도 C#에서 데이터 검증 드롭다운 리스트를 프로그래밍적으로 적용할 수 있습니다. 권장되는 접근 방법은 AddFormulaListRule()로, 셀 범위를 소스로 참조하여 리스트 항목을 직접 문자열로 전달할 때 적용되는 255자 문자열 제한을 피합니다.

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은 .NET 6 및 이후 버전에서 실행되며, Windows, Linux, macOS, Docker 및 Azure와 호환됩니다. 전체 세부 사항은 DataValidation API 참조를 참조하십시오.

시작: NuGet에서 Install-Package IronXL.Excel로 설치하세요. 무료 체험은 30일 동안의 전체 기능과 신용 카드가 필요 없는 상태로 제공됩니다.

추가 읽기:

• IronXL DataValidation API reference
• IronXL로 .NET에서 Excel 파일 작성
• IronXL 시작 가이드

마무리

엑셀 셀에 드롭 다운 리스트를 넣는 것은 어디에 가야 하는지만 알면 1분도 채 걸리지 않습니다. 고정된 선택지가 몇 개 있는 셀 내 드롭다운을 빠르게 사용하려면, 데이터 검증 대화 상자의 소스 상자에 쉼표로 구분된 리스트를 직접 입력하는 것이 가장 빠른 방법입니다. 시간이 지나면서 규모가 커지거나 업데이트가 필요한 모든 경우에, 이름 범위 또는 엑셀 테이블을 참조하면 드롭다운 옵션이 성장할 수 있는 공간을 제공합니다. 데이터 검증 리스트는 소스 데이터와 연결된 상태로 남아 있어, 소스 범위에 새로운 항목을 추가하면 연결된 모든 셀에서 자동으로 사용할 수 있게 됩니다.

같은 스프레드시트를 여러 사람이 작성할 때, 입력 메시지 탭의 입력 메시지는 선택하기 전에 그들을 안내하고, 오류 경고 탭의 정지 수준 오류 경고는 유효하지 않은 데이터가 검증된 셀에 닿지 않도록 보장합니다. INDIRECT와 이름 범위로 구축된 종속 드롭다운 리스트는 첫 번째 드롭다운 리스트가 자동으로 두 번째를 제어하여 사용자가 추가 작업 없이 옵션 세트를 관련성 있게 유지합니다.

.NET 응용 프로그램에서 검증된 엑셀 파일을 생성해야 하는 개발자에게, IronXL은 소스 범위, 오류 경고, 입력 프롬프트, 공식 리스트 규칙 등 데이터 검증 워크플로 전체를 다루며, 서버에 Office가 설치되어 있지 않아도 됩니다. 자신의 프로젝트에서 데이터 검증을 테스트하려면 무료 체험으로 시작하십시오.

이 가이드에서 다루지 않은 드롭다운 시나리오가 있습니까? 아래에 댓글을 남기거나 엑셀 데이터 입력, 검증, 스프레드시트 자동화에 대한 더 많은 가이드를 위해Iron Software 블로그를 방문하십시오.