Microsoft Excel에서 종속된 항목 목록 또는 계단식 드롭다운은 한 목록의 항목이 다른 목록에 따라 변경되는 두 개 이상의 목록을 나타냅니다.

 종속된 항목 목록은 보통, 예를 들어 학업 평가표의 학급-학생 목록, 지역별 판매 보고서의 지역-국기 목록, 인구 대시보드의 연도-지역 목록, 생산 요약 보고서의 단위-라인-제품 목록과 같은 Excel 기반 비즈니스 보고서에 사용됩니다.

이 블로그에서는 Document Solutions for Excel(DsExcel) for C# .NET에서 Excel의 데이터 유효성 검사 및 동적 배열 함수 UNIQUE, CHOOSECOLS, FILTER를 사용하여 마스터 및 종속된 항목 드롭다운 목록을 프로그래밍 방식으로 만드는 방법을 알아봅니다.

사용 사례

고객의 주문 행동을 연구, 비교 및 분석하기 위해 Excel 보고서를 준비한다고 가정하겠습니다. 판매 부서에서 고객의 주문 내역을 아래와 같은 서식으로 가져옵니다.

Excel 보고서에 특정 고객의 주문 세부 정보를 표시하려고 합니다. 보고서에 잘못된 데이터가 표시되지 않도록 Customer Name과 Order ID, 두 필드에 드롭다운을 추가하려고 합니다.

이러한 드롭다운의 값은 위에 표시된 주문 내역 데이터에서 가져와야 합니다.

하지만 Order ID 드롭다운에는 아래 표시된 것처럼, 선택된 고객과 관련된 값만 표시하려고 합니다.

Excel의 디자인 접근 방식

MS Excel에서 다음과 같은 여러 가지 방법으로 스마트한 대화형 종속된 항목 목록을 만들 수 있습니다.

• OFFSET, INDEX, MATCH와 같은 일반 내장 함수 또는 FILTER, UNIQUE 등과 같은 동적 배열 함수와 함께 데이터 유효성 검사 목록을 사용
• 양식 컨트롤 및 연결된 셀을 사용
• VBA 등을 사용 

이 블로그에서는 데이터 유효성 검사 목록 접근 방식을 동작 배열 함수와 함께 사용하여 위의 사용 사례에 설명된 종속된 항목 목록을 만들어 보겠습니다.

Excel에서는 목록 옵션과 데이터 유효성 검사 기능을 함께 사용하여 아래와 같이 드롭다운을 만들 수 있습니다.

하지만 UNIQUE, FILTERS 등 동적 배열 함수는 분할 동작으로 인해 데이터 유효성 검사 소스로 사용할 수 없습니다.

동적 배열 함수는 배열을 반환하지만, 목록 기반 데이터 유효성 검사는 워크시트 또는 하드 코딩된 쉼표로 구분된 목록 내 실제 범위를 참조해야 합니다.

결과적으로, 이러한 함수는 워크시트에서 별도로 평가해야 하며, 평가하는 셀의 참조는 목록의 소스로 사용해야 합니다.

DsExcel을 사용하여 프로그래밍 방식으로 구현

개발자는 DsExcel을 통해 Excel의 문서 개체 모델에서 모델링된 인터페이스 기반 API에 액세스하여 Microsoft Excel 호환 스프레드시트를 쉽게 만들고, 조작하고, 변환하고 공유하기 위한 완벽한 도구 모음을 이용할 수 있습니다.

이를 통해 쉽고 효율적으로 데이터를 관리하고 나만의 요구 사항에 맞게 조정된 솔루션을 구축할 수 있습니다.

DsExcel을 사용하여 원하는 마스터(Customer Name) 및 종속된 항목(OrderID) 드롭다운 목록을 프로그래밍 방식으로 만드는 단계를 살펴보겠습니다.

문서에서 C# 응용 프로그램에서 DsExcel을 시작하는 방법을 확인하십시오.

1단계 - 통합 문서 초기화

DsExcel API를 사용하는 첫 번째 단계는 통합 문서의 인스턴스를 초기화하는 것입니다.

그런 다음 기존 Excel 문서를 열거나 비즈니스 요구 사항에 따라 새 통합 문서를 만들 수 있습니다.

이 블로그의 경우, 아래 설명된 대로 Open 메서드를 IWorkbook 인터페이스와 함께 사용하여 기존 Excel 문서를 고객의 주문 내역과 함께 로드합니다.

Workbook workbook = new Workbook();
workbook.Open("CustomerOrderHistory.xlsx");

2단계 - 워크시트 가져오기 

다음으로, 필요한 보고서를 만들기 위해 워크시트를 가져와야 합니다. DsExcel을 사용하면 IWorkbook 인터페이스에서 워크시트 컬렉션을 사용하여 워크시트를 가져올 수 있습니다.

 새 워크시트를 생성하도록 선택할 수도 있습니다. 하지만, 보고서에 단순한 수식을 사용하기 위해 여기서는 아래 설명된 것처럼 주문의 내역을 저장하는 것과 동일한 워크시트에 보고서를 만듭니다.

IWorksheet worksheet;
worksheet = workbook.Worksheets["data"]; //OR workbook.Worksheets[0];

참고: 보고서의 레이아웃 및 기타 필요한 구성은 이 블로그의 범위를 벗어나기 때문에 이미 Excel 파일에 생성된 상태입니다. 보고서는 아래 표시된 대로 $L$2 위치에서 시작됩니다.

3단계 - 고객 이름의 고유한 목록 가져오기(마스터 드롭다운의 경우) 

초기화 후, 보고서의 "고객 이름 선택" 섹션에 추가할 마스터 드롭다운을 위해 고유한 고객 이름의 목록을 가져와야 합니다.

이를 위해, 워크시트에서 아래쪽에 공간이 있는 셀을 선택하여 데이터를 세로로 분할합니다. 여기서는 T3 셀을 사용합니다.

그리고 필요한 Customer Name 데이터 범위에 UNIQUE 함수를 사용합니다.

DsExcel을 사용하면, Range 속성을 IWorksheet 인터페이스와 함께 사용하여 하나의 셀 또는 셀 범위를 가져오고 아래 설명된 것처럼 IRange 인터페이스의 Formula2 속성을 사용하여 셀에 동적 함수를 적용할 수 있습니다.

IRange rngUniqueCustomerNames;
rngUniqueCustomerNames = worksheet.Range["T3"]; //dummy cell to get unique list of customer names
rngUniqueCustomerNames.Formula2 = "=UNIQUE($B$2:$B$2156)";

4단계 - 마스터 드롭다운 만들기 

고객 이름의 목록을 가져온 후, 목록에서 데이터 유효성 검사를 사용하여 만든 마스터 드롭다운의 소스로 사용합니다. 이 블로그 샘플에서는 L3 셀에서 이 마스터 드롭다운이 생성됩니다.

DsExcel을 사용하면, IRange의 Validation 속성을 사용하여 하나의 범위에 대한 데이터 유효성 검사가 구성됩니다. IValidation 인터페이스의 Add 메서드를 사용하여 하나의 범위에 대한 새로운 유효성 검사 규칙 인스턴스를 추가합니다.

목록 유형 데이터 유효성 검사를 위해 ValidationType.List 옵션을 선택하고 UNIQUE 수식을 사용하여 셀에 수식을 설정합니다.

 여기서는 아래 설명된 대로 T3가 사용됩니다.

IValidation listValidation = worksheet.Range["L3"].Validation;
listValidation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal,"=$T$3#");

동적 배열 함수의 결과 범위를 가져오기 위해 셀 참조의 뒤에 #가 붙습니다.

5단계 - 고유한 OrderID 목록 가져오기(종속된 항목 드롭다운의 경우)

마스터 드롭다운을 준비한 후, 마스터 드롭다운에서 선택된 고객 이름의 고유한 OrderID 목록을 가져옵니다.

이를 위해, 다시 워크시트에서 임의의 셀을 선택합니다(이 샘플에서는 $V$2입니다).

이 셀에서 다음 수식을 사용하여 원하는 OrderID 목록을 가져옵니다.

=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, 
CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1).

자세한 수식은 다음과 같습니다.

• 정의된 nameCustomerName의 참조 대상은 마스터 드롭다운이 포함된 셀의 값(이 샘플의 경우 =$L$3)입니다.
  

• 정의된 nameUnique_Cus_Order_combo의 참조 대상은 주문 ID와 고객 이름의 고유한 조합의 범위입니다. 범위 A와 B에 각각 OrderID와 Customer Names가 포함된 수식 =UNIQUE(data!$A$2:$B$2156)이 저장됩니다. 이 함수는 아래와 같은 데이터를 반환합니다.
  

• 내부 CHOOSECOLS 함수는 Unique_Cus_Order_combo로 나타내는 범위에서 FILTER 함수의 CustomerName과 일치하는 고객 이름을 제공합니다.
  

• FILTER 함수는 아래 표시된 대로, Unique_Cus_Order_combo에서 선택된 고객 이름에 해당하는 데이터를 제외합니다.
  

• 마지막으로 외부 CHOOSECOLS 함수는 아래 표시된 대로, 필터링된 범위에서 원하는 OrderID의 목록을 반환합니다.
  

DsExcel을 사용하여 정의된 이름 및 동적 수식을 설정하려면 아래 샘플 코드를 따르십시오.

workbook.Names.Add("CustomerName", "=$L$3");
 workbook.Names.Add("Unique_Cus_Order_combo", "=UNIQUE(data!$A$2:$B$2156)");
​
IRange rngUniqueOrderIds;
rngUniqueOrderIds = worksheet.Range["V2"]; //dummy range to get unique list of customer names
rngUniqueOrderIds.Formula2 = "=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1)";

6단계 - 종속된 항목 드롭다운 채우기

다음 단계는 이전 단계에서 가져온 목록을 사용하여 OrderID 드롭다운(이 샘플에서는 L6)을 채우는 것입니다.

이를 위해, 목록 유형의 데이터 유효성 검사를 추가하고(마스터 드롭다운을 위해 추가된 것과 동일함) 소스 값 앞에 #를 붙여 이전 단계의 수식(즉, =$V$2)이 포함된 셀 값으로 설정합니다.

IValidation orderIdList = worksheet.Range["L6"].Validation;
orderIdList.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$v$2#");

7단계 - 드롭다운에 기본값을 설정하고 통합 문서 저장 

마지막으로, 아래 코드 조각에 설명된 대로 IRange 인터페이스의 Value 속성을 사용하여 드롭다운에 기본값을 설정하고 IWorkbook 인터페이스의 Save 메서드를 사용하여 통합 문서를 저장합니다.

worksheet.Range["L3"].Value = "Paul Henriot";
worksheet.Range["L6"].Value = 10248;
​
workbook.Save("CustomerOrderHistoryReport.xlsx");

스마트한 종속된 항목 목록을 사용하여 생성된 Excel 파일은 아래 gif와 같이 표시됩니다.

전체 샘플을 다운로드합니다.

결론

결론적으로, 스마트한 종속된 항목 목록을 만들기 위한 Excel의 다양한 전략은 Document Solutions for Excel API(DsExcel)의 기능으로 추가 보완됩니다.

이 강력한 도구는 Excel과 완벽하게 호환되며, 450개 이상의 기본 제공 Excel 함수를 포함하여 확장됩니다. 이 기능을 활용하여, 사용자는 종속된 항목 목록을 쉽게 생성하고, C#에서 복잡한 스프레드시트 계산을 원활하게 실행할 수 있습니다.

지금 바로 DsExcel .NET을 다운로드하여 직접 테스트해 보세요!

• DsExcel .NET 공식 홈페이지
• DsExcel .NET 튜토리얼 및 샘플
• DsExcel .NET Q&A
• DsExcel .NET FAQ