동적 배열은 Excel365에서만 지원되므로, 동적 배열 수식은 Excel을 Excel365와 기존 Excel(2019 이하)로 구분하고 Excel 수식에 도입된 가장 중요한 기능입니다. 동적 배열 기능을 사용하면 단일 셀의 수식에서 여러 결과 값을 반환하여 해당 값을 수식 셀에 인접한 셀에 채울 수 있습니다. 결과가 여러 셀로 분할되므로, 분할 범위 기능이라고도 합니다. 기존 Excel에서는 수식을 배열 수식으로 인식하도록 하려면 사용자가 명시적으로 Ctrl+Shift+Enter를 사용해야 했습니다. 그렇지 않으면 수식 결과가 단일 값만 반환했습니다. 여러 값을 반환하는 수식을 이제 동적 배열 수식이라고 합니다.

Excel에서는 동적 배열 함수라는 새로운 함수 6개도 도입했습니다.

1. FILTER - 사용자/개발자가 정의한 기준에 따라 데이터를 필터링합니다.

2. RANDARRAY - 임의의 수로 구성된 배열을 생성합니다.

3. SEQUENCE - 일련 번호 배열을 생성합니다.

4. SORT - 지정된 열을 기준으로 셀 범위를 정렬합니다.

5. SORTBY - 다른 셀 범위 또는 배열을 기준으로 셀 범위를 정렬합니다.

6. UNIQUE - 셀 범위에서 고유한 항목을 추출합니다.

GcExcel v4.2에서는 이제 동적 배열 수식 기능을 코드로 지원합니다. 즉, MS Excel이 시스템에 설치되어 있지 않거나 Excel365를 구독하지 않더라도 코드를 통해 동적 배열 수식을 셀에 추가할 수 있고 수식이 모두 포함된 Excel 파일을 저장할 수 있습니다. 이 블로그에서는 GcExcel API를 사용하여 C#으로 동적 배열 수식을 구현하는 방법에 대해 설명합니다.

동적 배열 함수

GcExcel에서는 위에 나열된 6가지 함수를 모두 지원합니다. 여기서는 위에서 설명한 동적 배열 함수 6개를 C#으로 사용하는 방법을 알아봅니다. GcExcel에서 동적 배열 수식에 대한 지원을 활성화하려면 Workbook 클래스의 AllowDynamicArray 속성을 true로 설정하고 동적 배열 수식을 셀의 Formula 속성으로 할당합니다.

아래 표에는 지원되는 모든 함수와 구문, 설명 및 코드 예제가 나와 있습니다. 아래 코드 조각은 AllowDynamicArray 속성이 True로 설정되어 있다는 가정 하에 작성되었습니다.

위 코드 조각은 수식을 적용하는 데 중점을 두고 있습니다. 스크린샷에 표시된 출력을 전체적으로 구현한 내용을 살펴보려면 샘플을 다운로드하거나 다음 데모를 참조하세요.

동적 배열 수식을 사용할 때 상당히 유용한 연산자 2개가 동적 배열과 함께 도입되었습니다. 다음 섹션에서는 이러한 연산자에 대해 자세히 알아보겠습니다.

@ 연산자

암시적 교집합 연산자라고도 하는 @ 연산자는 일련의 값을 단일 값으로 줄여주는 암시적 교집합으로 알려진 수식 동작을 구현합니다. 이는 여러 값을 반환하는 배열 수식에 적용되는데, 해당하는 경우 단일 값은 셀 위치(행 및 열)에 따라 반환됩니다. 이는 기존 Excel의 기본적인 동작이었으며 명시적 연산자가 필요하지 않았습니다. 그러나 Excel365에서는 모든 수식이 배열 수식이므로 배열 수식이 분할되지 않기를 원하는 경우 수식 앞에 접두사로 @ 연산자를 추가하면 단일 값만 반환합니다.

아래 코드 조각에서는 Formula 속성을 설정하여 코드에서 이 연산자를 사용하는 방법을 보여 줍니다.

static void ImplicitIntersection()
{
  //Initialize worksheet
  var sheet = workbook.Worksheets[6];
  sheet.Name = "IMPLICIT INTERSECTION";
​
  //Add sample data
  sheet.Range["$D$3:$D12"].Value = new object[] { "Products", "Apple", "Grape", "Pear", "Banana", "Apple", "Grape", "Pear", "Banana", "Banana"};
  sheet.Range["$E$3"].Value = "Unique Products";  
​
  //Apply dynamic array formula with implicit intersection operator
  sheet.Range["$E$4"].Formula = "=@UNIQUE(D4:D12)";
}

아래 스크린샷에서는 위 코드 조각에서 구현한 것처럼 암시적 교집합 연산자를 사용하여 동적 배열 수식을 적용한 결과를 보여 줍니다.

이제 기존 Excel을 사용하여 만든 Excel 파일을 Excel365에서 열면 수식에서 이 연산자가 더 자주 보입니다. 이 연산자는 이전 수식 동작이 영향을 받지 않도록 이전 버전과의 호환성을 위해 제공됩니다.

분할 범위 참조(#)

동적 배열 수식이 여러 값 결과를 분할하는 셀 범위를 분할 범위라고 합니다. 분할 범위 내 아무 셀을 클릭할 때마다 분할 범위가 파란색 테두리로 강조 표시되어 해당 범위의 모든 값이 범위 왼쪽 상단 셀의 수식으로 계산되었음을 나타냅니다.

분할 범위 참조 연산자는 분할 범위를 참조하는 데 사용됩니다. 분할 범위를 참조하려면 분할 범위 내 왼쪽 상단 셀의 주소 뒤에 해시태그 또는 우물정자(#)를 붙이세요.

예를 들어, 셀 E4에서 적용된 UNIQUE 수식에서 추출한 고유한 값의 개수를 확인하려면 아래 코드에서 보여 주는 것처럼 COUNTA 함수에 분할 범위 참조를 제공합니다.

static void SpillReference()
{
  //Initialize Worksheet
  var sheet = workbook.Worksheets[7];
  sheet.Name = "SPILL REFERENCE";
​
  //Add sample data
  sheet.Range["$D$3:$D12"].Value = new object[] { "Products", "Apple", "Grape", "Pear", "Banana", "Apple", "Grape", "Pear", "Banana", "Banana" };
  sheet.Range["$E$3"].Value = "Unique Products";
  sheet.Range["F3"].Value = "Unique Products Count";  
​
  //Apply dynamic array formula
  sheet.Range["$E$4"].Formula = "=UNIQUE(D4:D12)";
  //Apply formula using spill range reference
  sheet.Range["$F$4"].Formula = "=COUNTA(E4#)";
}

아래 스크린샷에서는 위에서 정의한 코드 조각에서 구현한 것처럼 분할 범위 참조 연산자를 적용한 후 관찰한 결과를 보여 줍니다.

이제 GcExcel에서 C#으로 동적 배열 함수를 지원하는 방식을 알아봤으므로 배운 내용을 작업에 적용하여 동적 배열 수식을 어떻게 활용할지 보여 주는 실제 시나리오를 구현해 보겠습니다.

사용 사례: 동적 배열을 사용하여 Excel에서 대화형 차트 만들기

셀 범위에서 사용 가능한 표 형식 데이터를 사용하여 세로 막대형 차트를 만드는 시나리오에 대해 생각해 보겠습니다. 표는 일반적으로 행과 열이 많은 데이터의 상세 양식을 제공하는 데 사용됩니다. 그러나 차트에서 데이터를 도표로 나타내는 경우 표에서 데이터를 선택할 수 있는 방법이 있어야 합니다. 필요한 데이터를 추출하려면 몇 가지 기준에 따라 데이터를 필터링해야 합니다. 바로 여기서 동적 배열 수식이 등장합니다. FILTER 함수를 사용하여 기준을 정의하고 표 형식 데이터를 필터링하고 필터링된 데이터가 검색되면 해당 데이터를 사용하여 세로 막대형 차트를 만들어 보겠습니다.

세로 막대형 차트는 차트 계열에 바인딩되고 미리 정의된 셀 범위의 데이터를 도표로 나타냅니다. 이 경우 사용자가 Show 열의 값을 변경하면 바로 필터링되는 행의 수가 바뀝니다. Show 열의 변경으로 인해 데이터를 필터링하는 데 사용되는 FILTER 수식이 다시 계산되므로 필터링되는 데이터 셀 범위가 바뀝니다. 하지만 차트 계열이 항상 동일한 셀 범위를 참조하므로 계열 셀 범위에 속하지 않은 필터링된 데이터의 새 행은 차트에 표시되지 않습니다. 그러나 차트의 목적은 Show 열에서 값이 1인 제품을 모두 표시하는 것이기 때문에 필터링된 모든 데이터가 차트에 표시될 것으로 예상합니다. 또한 이 측면을 살펴보고 도표 영역을 새로 고쳐 필터링된 데이터의 모든 값을 도표에 표시하도록 간단한 정적 차트를 대화형 차트로 변환하는 방법을 살펴보겠습니다.

아래 단계를 따르면서 위에서 정의한 시나리오를 구현하기 위한 자세한 단계를 알아보겠습니다.

1단계: 소스 데이터 로드

GcExcel 통합 문서에서 차트를 그리는 데 사용될 소스 데이터를 로드하는 것으로 시작합니다. 소스 데이터는 'SalesReport_sourcedata.xlsx'라는 Excel 파일에 저장되어 있습니다. 이 Excel 소스 데이터 파일은 여기서 다운로드하여 볼 수 있습니다. 아래 코드 조각은 Workbook 클래스의 Open 메서드를 사용하여 Excel 파일을 GcExcel 통합 문서로 로드하는 방법을 보여 줍니다.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
​
var fileStream = GetResourceStream("xlsx\\SalesReport_sourcedata.xlsx");
workbook.Open(fileStream);

다음은 소스 데이터를 로드한 후 통합 문서의 모습입니다.

2단계: GcExcel 통합 문서에 대한 동적 배열 수식 지원 활성화

다음으로, 아래 코드에서 보여 주는 것처럼 Workbook 클래스의 AllowDynamicArray 속성을 true로 설정하여 GcExcel 통합 문서에서 동적 배열 지원을 활성화합니다.

//Enable Dynamic Arrays Support in Workbook
workbook.AllowDynamicArray = true;

3단계: 동적 배열 수식을 사용하여 차트 데이터 추출

이제 차트에 그릴 관련 데이터를 추출해 보겠습니다. 차트의 제품을 기준으로 매출 도표를 그려 봅니다. 따라서 차트 데이터에는 Product 열과 Amount 열의 값이 포함되어야 합니다. 두 열에서 특정 값을 추출하는 기준은 Show 열입니다. Show 열의 값이 1인 각 행에서 제품 및 금액 값을 추출하고 필터링된 데이터를 차트에 그릴 것입니다.

FILTER 함수는 Product 열에서 데이터를 필터링하는 데 사용됩니다. 필터링된 데이터는 SORTBY 함수를 사용하여 Amount 열의 값을 내림차순으로 정렬됩니다. 두 개의 동적 배열 함수 즉, FILTER 및 SORTBY를 연결하여 동적 배열 수식을 만들어 보겠습니다.

VLOOKUP 함수를 사용하고 FILTER 함수의 유출 범위를 매개 변수로 전달하면 Amount 열의 값이 추출되어 필터링된 각 제품에 해당하는 금액 값을 가져옵니다.

다음은 동일한 내용을 구현하는 코드 조각입니다.

//Filter data based on Show Column
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G3"].Value = "Product";
worksheet.Range["H3"].Value = "Sales";
worksheet.Range["G3"].Font.Bold = true;
worksheet.Range["H3"].Font.Bold = true;
//Apply chained dynamic array formula to fetch Product column values
worksheet.Range["G4"].Formula = "=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)";
//Use Spill Range reference to fetch Amount column values
worksheet.Range["H4"].Formula = "=VLOOKUP(G4#,B4:D13,3,FALSE)";

다음은 열 G 및 H의 필터링 및 정렬된 데이터가 포함된 워크시트의 모습으로, 분할 범위가 강조 표시되어 있습니다.

4단계: 필터링된 데이터를 사용하여 간단한 차트 추가

위 단계에서는 차트에 그릴 데이터를 생성했습니다. 여기서는 Product 열을 참조하는 범위 하나와 필터링된 데이터에서 Sales 열을 참조하는 범위 하나, 이렇게 두 개의 명명된 범위를 워크시트에 추가하여 매출을 보여 주는 간단한 차트를 추가해 보겠습니다. 그런 다음 명명된 범위는 차트 계열을 만드는 데 사용됩니다. 아래 코드 조각은 이 작업을 수행하는 방법을 보여 줍니다.

//Add named ranges to refer to chart data
workbook.Names.Add("Product", "=Sheet1!$G$4:$G$8");
workbook.Names.Add("Sales", "=Sheet1!$H$4:$H$8");
//Add Chart
IShape chartShape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 600, 170, 500, 300);
IChart chart = chartShape.Chart;          
//Adding series to SeriesCollection
ISeries series = chart.SeriesCollection.NewSeries();
series.Formula = "=SERIES(\"Sales\", Product, Sales, 1)";

다음은 차트와 함께 생성된 Excel 파일의 모습입니다.

위 단계에서는 동적 배열 수식으로 생성된 필터링된 데이터를 사용하여 차트를 만들었습니다. 이제 앞의 섹션에서 설명한 동적 배열의 다른 측면을 사용하여 이 차트를 대화형 차트로 만들어 보겠습니다.

5단계: 대화형 차트 만들기

차트를 대화형으로 만들려면 무엇이 필요한지 파악하는 것부터 시작해 보겠습니다. 아래 GIF를 살펴보면 Show 열의 값이 바뀌고, 동적 배열 수식이 다시 계산되고, 필터링된 데이터 셀 범위가 업데이트됩니다. 그러나 이러한 변경은 새로 필터링된 데이터가 계열 셀 범위인 셀 범위 G3:H8에 속하는 경우에만 차트에서 볼 수 있습니다. 필터링된 데이터가 셀 범위 G3:H8에 속하지 않는 경우 이 예에서 필터링된 데이터 범위가 G3:H10으로 확장되지만 차트는 G3:H8의 데이터만 렌더링하므로 차트에 표시되지 않습니다.

이 차트에는 필터링된 모든 데이터가 표시되어야 합니다. 이렇게 하려면 분할 범위 참조를 사용하여 계열 셀 범위를 업데이트해야 합니다. 그러면 계열 셀 범위에 필터링된 데이터를 포함한 전체 셀 범위가 항상 포함됩니다. 다음은 분할 범위 참조를 사용하도록 명명된 범위를 업데이트하여 동적 배열 수식의 결과에 따라 참조되는 셀 범위를 동적으로 만드는 샘플 코드 조각입니다. 이러한 동적 명명 범위는 차트 계열을 생성하는 데 사용되어 차트를 대화형으로 만듭니다.

//Add named ranges to refer to chart data
workbook.Names.Add("Product", "=Sheet1!$G$4#");
workbook.Names.Add("Sales", "=Sheet1!$H$4#");
//Add Chart
IShape chartShape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 600, 170, 500, 300);
IChart chart = chartShape.Chart;
//Adding series to SeriesCollection
ISeries series = chart.SeriesCollection.NewSeries();
series.Formula = "=SERIES(\"Sales\", Product, Sales, 1)";

필터링되는 모든 데이터를 렌더링하는 대화형 차트의 작동을 보여 주는 다음 GIF를 참조하세요.

위 시나리오가 실제로 어떻게 작동하는지 보려면 다음 데모를 참조하세요. 자세한 내용은 데모와 문서를 참조하세요.

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

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