Excel과 유사한 고급 계산 기능을 응용 프로그램에서 원했던 적이 있습니까?

수식과 함수는 데이터 분석에서 꽤 중요한 역할을 합니다. Microsoft Excel은 데이터 분석 중 주요 메트릭 추출을 간소화하는 다양한 함수를 제공합니다. 하지만, C# 응용 프로그램에서 이 기능을 복제하려면 사용자 정의 로직과 상당한 시간 투자가 필요합니다.

응용 프로그램에서 Excel 함수와 기타 Excel과 유사한 기능을 구현하기 위해, Mescius는 Document Solutions for Excel API(DsExcel)를 제공합니다. DsExcel은 Excel에 종속되지 않은 Excel 개체 모델에 기반한 종합적인 .NET 스프레드시트 API 라이브러리입니다.

이를 사용하면 Excel 파일을 생성, 편집, 로드, 저장할 수 있을 뿐만 아니라 Excel처럼 데이터에 대해 다양한 계산을 수행할 수 있습니다.

이 블로그에서는 다음 Excel 함수를 설명하고 DsExcel API로 이러한 함수를 활용하는 방법을 보여 줍니다.

• LOOKUP 및 Reference 함수

  • VLOOKUP/HLOOKUP 함수

  • LOOKUP 함수

  • XLOOKUP 함수

  • MATCH 함수

• FILTER 함수

• TRANSPOSE 함수

여기에서는 다음 JSON 판매 데이터를 사용해 위에 언급된 함수를 사용하는 방법을 확인하겠습니다.

도움말에 언급된 데이터 바인딩 기법 중 하나를 사용해 통합 문서에 이 데이터를 추가할 수 있습니다. 

다음은 시각적으로 관심을 끌기 위해 몇 가지 추가 서식 조정을 한 후 Excel 스프레드시트에 데이터를 표시하는 방법입니다.

LOOKUP 및 Reference 함수

Lookup 및 Reference 함수는 데이터 집합의 값이나 인덱스를 동적으로 검색합니다. 이러한 함수는 데이터 배열과 함께 작동하며 특정 기준에 따른 결과를 반환합니다.

이러한 카테고리의 함수는 데이터를 신속하게 분석하는 데 도움이 되며 반복적인 작업을 자동화하여 시간을 절약할 수 있습니다.

DsExcel에서 지원하는 이러한 Lookup 및 Reference 함수를 활용해 응용 프로그램을 강력하게 만들 수 있습니다. 예시를 통해 몇 가지 함수를 살펴 보겠습니다.

VLOOKUP/HLOOKUP 함수

VLOOKUP(Vertical LookUp의 준말)은 특정 범위의 가장 왼쪽 열에 있는 값을 찾고 같은 행의 다른 지정된 열에 값을 가져옵니다.

예시와 함께 이를 자세히 살펴봅시다. 예를 들어, 특정 데이터에서 특정 제품 ID를 판매한 영업 담당자를 찾기 위해 VLOOKUP 함수를 사용할 수 있습니다.

더 자세히 살펴보기 전에, 우선 이 함수의 구문과 매개 변수를 이해해 보겠습니다.

=VLOOKUP(lookup_value, table_array,column_index_num,[range_lookup])

• lookup_value - 표에서 가장 왼쪽 열의 지정된 값을 찾는 첫 번째 매개 변수입니다. 이 예시에서, 우리는 K8 셀에 지정된 특정 제품 ID를 찾고 있습니다.

• table_array - 이 매개 변수는 검색할 원본 데이터를 포함한 대상 표 또는 셀 범위를 나타냅니다. 가장 왼쪽에 있어야 하는 제품 ID 열로 시작하는 B4:E13을 대상 범위로 삼아 봅시다.

• column_index_num - 세 번째 매개 변수는 표의 대상 열 인덱스를 나타냅니다. 영업 담당자 열 [JS1]에서 찾는 중이므로, 이 매개 변수의 값은 4여야 합니다.

• range_lookup - 검색 유형을 지정하는 매개 변수(선택 사항)입니다. 유사 일치(기본)에 대해 TRUE로, 정확한 일치에 대해 FALSE로 이를 설정할 수 있습니다. 여기에서 이를 FALSE로 설정하겠습니다.

이 매개 변수 값을 사용하면 최종 VLOOKUP 함수는 아래와 같이 보입니다.

=VLOOKUP(K8,B4:E13,4,FALSE)

DsExcel에서 L8에 이 함수를 설정해 영업 담당자의 이름을 가져옵니다.

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=VLOOKUP(K8,B4:E13,4,FALSE)";

이전에 언급했던 대로, VLOOKUP은 가장 왼쪽 열에서 세로로만 데이터를 찾을 수 있습니다.

유사하게, 가로 데이터의 경우, 첫 번째 행의 데이터를 검색하는 HLOOKUP(Horizontal Lookup의 준말) 함수를 사용할 수 있습니다. 하지만, 이러한 함수는 모두 오름차순으로 정렬된 조회 데이터에서만 작동합니다. 

LOOKUP 함수

LOOKUP 함수는 데이터 내의 세로 및 가로 검색을 수행합니다.

세로 검색에서 이 함수는 특정 열의 값을 찾고 다른 지정된 열에서 동일한 열의 값을 반환합니다. 가로 검색의 경우에는 그 반대입니다.

전체 검색 범위가 필요한 VLOOKUP과 HLOOKUP 함수와는 달리, LOOKUP 함수는 검색과 결과를 열이나 행으로만 지정하여 결과를 제공합니다. LOOKUP 함수를 사용하면 성능이 개선될 뿐만 아니라 검색 열 위치가 변경되더라도 올바른 결과가 제공됩니다.

이 함수를 이해하기 위해, 특정 제품 ID의 판매 담당자 이름을 찾는 동일한 예시를 들어보겠습니다. 하지만 그전에, LOOKUP 함수의 구문과 매개 변수를 보십시오.

=LOOKUP(lookup_value, lookup_array,result_array])

• lookup_value - 표의 특정 열/행에 있는 지정된 값을 찾는 매개 변수입니다. 예시의 경우, K8 셀에 우리가 찾는 제품 ID가 있습니다.

• lookup_array - 두 번째 매개 변수는 검색할 원본 데이터를 포함한 열/행을 나타냅니다. 여기에서, B4:B13은 제품 ID를 포함한 열입니다.

• result_array - 세 번째 매개 변수는 이 경우 판매 담당자 열(E4:E13)인 대상 열/행을 나타냅니다.

모든 값을 할당하면, LOOKUP 함수의 결과는 다음과 같습니다.

=LOOKUP(K8,B4:B13,E4:E13)

아래의 DsExcel 코드는 LOOKUP 함수를 사용해 판매 담당자의 이름을 찾는 방법을 나타냅니다.

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = “=LOOKUP(K8,B4:B13,E4:E13)”;

VLOOKUP과 HLOOKUP 함수처럼, LOOKUP은 데이터가 오름차순으로 정렬된 경우에만 올바른 결과를 제공합니다. 하지만, 일치하는 것을 찾을 수 없는 경우 대처하지 않습니다.

XLOOKUP 함수

XLOOKUP은 Excel 365와 Excel 2019에 도입된 최고급 조회 함수입니다.

이 함수는 위에 언급된 함수의 모든 제한을 뛰어넘습니다. 또한, 검색 방향처럼 고급 일치 및 검색 기능 몇 가지도 함께 제공합니다.

마지막 예시에서 더 나아가, 특정 제품 ID를 판매한 지역과 판매 담당자를 찾아봅시다. 그리고 이번에는 아래에서 위까지 역방향으로 검색해 봅시다.

원하는 결과를 얻기 위해 XLOOKUP을 활용하는 방법을 살펴보기 전에 먼저 다음 함수의 구문과 매개 변수를 확인합니다.

=XLOOKUP(lookup_value,lookup_array,return_array,[not_found],[match_mode],[search_mode]))

• lookup_value - 이전의 조회 함수와 유사하게, 이 매개 변수는 표의 특정 열/행에 있는 지정된 값을 찾습니다. 이 예시에서는 K8입니다.

• lookup_array - 이 매개 변수는 lookup_value를 찾기 위한 열/행을 나타냅니다. 제품 ID는 B4:B13 범위 내에 있습니다.

• return_array - 세 번째 매개 변수는 대상 값의 범위를 나타냅니다. D4:E13은 판매 담당자와 판매 지역을 포함하는 범위입니다.

• not_found - 이 매개 변수는 일치하는 것을 찾을 수 없을 때 지정된 값을 반환합니다. “레코드를 찾을 수 없음”으로 설정합시다.

• match_mode - 다섯 번째 매개 변수는 일치 유형을 지정합니다. 정확한 일치에는 0, 정확한 일치나 다음 작은 항목은 -1, 정확한 일치 또는 다음으로 더 큰 항목에는 1을, 와일드카드 일치에는 2입니다. match. 정확한 값을 찾는 중이므로 0으로 설정합니다.

• search_mode - 마지막 매개 변수는 위에서 검색을 수행하는 경우 1, 아래의 경우 -1, 데이터가 오름차순인 경우 바이너리의 경우 2, 데이터가 내림차순인 경우 바이너리 검색에는 -2입니다. 본 사례의 경우, -1로 설정되어 있습니다.

이러한 모든 매개 변수를 XLOOKUP 함수에 넣으면 다음과 같이 표시됩니다.

=XLOOKUP(K8,B4:B13, D4:E13,”Record Not Found”,0,-1)

다음 DsExcel 코드는 다음과 같은 XLOOKUP 함수를 사용해 영업 담당자와 지역 이름을 가져오는 방법을 보여줍니다.

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=XLOOKUP(K8,B4:B13, D4:E13,”Record Not Found”,0,-1)";

MATCH 함수

MATCH 함수는 그 인덱스의 범위에 있는 지정된 항목을 검색하고 인덱스에 반환합니다.

이 함수를 사용해 데이터 구조가 바뀌더라도 원하는 결과를 자동으로 제공하도록 다른 함수를 만들 수 있습니다.

column_index_num 매개 변수가 하드 코딩된 값에 설정된 VLOOKUP 기능에 대한 이전의 사용 사례로 이를 이해해 보겠습니다. 데이터에 있는 영업 담당자 위치를 바꾸는 경우, VLOOKUP은 올바른 값을 반환하지 못합니다.

여기에서 MATCH 함수는 영업 담당자 열의 인덱스를 동적으로 가져올 수 있습니다.

하지만 넘어가기 전에, MATCH 함수의 구문과 매개 변수를 살펴봅시다.

=MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value - 첫 번째 매개 변수는 범위에서 지정된 값을 찾습니다. “영업 담당자”로 설정합니다.

• Lookup_array - 이 매개 변수는 항목 인덱스를 검색할 열/행을 정의합니다. 여기에서는, B3:E3 범위에서 검색을 수행합니다.

• Match_type - 이 매개 변수를 사용하면 정확한 일치에는 0, 조회 값 위의 가장 근사한 일치에는 -1, 조회 값 아래의 가장 근사한 일치에는 1을 사용하는 검색 유형을 지정할 수 있습니다. 이를 0으로 설정합니다.

모든 매개 변수를 입력하면 다음 함수가 형성됩니다.

=MATCH("Sales Person",B3:E3,0)

VLOOKUP 함수에서, column_index_num 매개 변수를 위의 MATCH 함수로 교체해 동적으로 만듭니다.

=VLOOKUP(K8,B4:E13,MATCH("Sales Person",B3:E3,0),FALSE)

이제 VLOOKUP 수식에서는 영업 담당자 열의 인덱스가 변경되더라도 동일한 결과를 제공합니다.

아래는 VLOOKUP 함수에서 MATCH 함수를 사용하는 것을 나타내는 최종 DsExcel 코드입니다.

//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = “=VLOOKUP(K8,B4:E13,MATCH("Sales Person",B3:E3,0),FALSE)”;

FILTER 함수

FILTER 함수를 사용하면 특정 범위를 기준으로 데이터를 추출할 수 있습니다. 이 함수는 특정 조건에 따라 큰 데이터 집합 중 데이터 일부가 필요한 경우 이상적입니다.

북부 지역에서 판매 레코드를 가져와 봅시다. 다음은 FILTER 함수의 구문입니다.

=FILTER(array,include,[if_empty])

• Array - 이 매개 변수는 필터링할 값의 범위를 정의합니다. A4:G13으로 설정하여 모든 레코드를 검색합니다.

• Include - 두 번째 매개 변수는 조건의 부울 배열입니다. 예시의 경우, D4:D13의 “북부” 지역을 찾고 있습니다. 즉, 이 매개 변수의 값은 D4:D13="North"가 되어야 합니다.

• If_empty - 이 매개 변수는 기준을 만족하는 항목이 없는 경우 특정 값을 반환합니다. 이를 “No Record Found”로 설정하겠습니다.

아래는 이러한 모든 매개 변수 값을 포함한 FILTER 함수입니다.

=FILTER(A4:G13,D4:D13=” North”,"No Record Found")

아래의 DsExcel 코드는 북부 지역에서 판매된 항목의 세부 정보를 가져오는 FILTER 함수를 사용하는 방법을 보여줍니다.

//Apply formula to K8 cell of the worksheet
worksheet.Range["K8"].Formula = “=FILTER(A4:G13,D4:D13=”North”,"No Record Found");

TRANSPOSE 함수

TRANSPOSE 함수는 지정된 데이터의 방향을 바꿉니다.

세로 데이터를 수평 레이아웃으로 바꾸거나 그 반대로도 바꿉니다. 이는 다른 관점에서 데이터를 분석하는 데 도움이 됩니다. 이 함수의 구문은 뒤바꿀 범위만 취하므로 상당히 단순합니다.

=TRANSPOSE(array)

월간 판매량을 분석하고자 한다고 가정해 봅시다. 데이터를 뒤바꾸고 월을 열 헤더로 만들면 각 월에 대한 데이터를 분석하기 쉬워집니다. 다음은 대상 범위를 유일한 매개 변수로 사용하는 TRANSPOSE 함수입니다.

=TRANSPOSE(A3:G13)

데이터를 뒤바꾸는 데 아래 DsExcel 코드를 사용하십시오.

//Apply formula to K3 cell of the worksheet
worksheet.Range["K3"].Formula = “=TRANSPOSE(A3:G13)”;

또한 샘플을 다운로드​하여 위에 설명된 모든 함수가 작동하는 것을 확인할 수 있습니다.

결론

이 블로그에서 사용자 정의 로직 없이 고급 Excel 함수를 결합하여 .NET 응용 프로그램의 데이터 분석 기능을 개선하는 방법에 대해 알아보셨습니다.

정교한 스프레드시트 API인 DsExcel은 필터링, 정렬, 피벗 테이블, 조건부 서식, 차트, 스파크라인 등을 비롯한 Excel과 비슷한 많은 기능을 제공해 이를 넘어섭니다.

Java 버전의 백엔드 Excel API를 찾고 계신가요?

- DsExcel Java API 바로가기