C# .NET에서 Excel 2019 기능을 사용하여 Excel 파일 만들기 - Part 1
페이지 정보
작성자 GrapeCity
작성일 2021-08-17 16:13 조회 3,112회
댓글 0건
본문
첨부파일
관련링크
이 블로그는 Excel 2019 기능을 강조하고 GcExcel API를 사용하여 대기질지수 보고서(AQI)를 작성하는 방법을 보여주는 시리즈 중 1부입니다.
오늘날 기업에서 사용할 수 있는 데이터가 풍부해짐에 따라, 데이터를 의미 있는 방법으로 분석하고 처리할 수 있는 효율적인 방법을 찾는 것이 더욱 중요해졌습니다. 스프레드시트는 이를 관리하는 강력한 방법입니다. 특히, 함수를 사용하여 복잡한 계산을 생성하여 시간과 비용을 절약하고 궁극적으로 대규모 데이터 집합에 대한 강력한 분석을 제공할 수 있습니다.
Microsoft Excel 2019 릴리즈에서는 다음과 같은 6가지 새로운 기능이 도입되어 일반적인 계산을 간소화합니다.
CONCAT 함수는 셀 범위의 값을 포함하여 여러 문자열 값을 결합하는 데 사용할 수 있습니다.
TEXTJOIN 함수는 셀 범위의 값을 포함하여 여러 문자열 값을 결합하는 데도 사용할 수 있습니다. 또한 구분 기호를 지정하고 빈 셀을 무시할지 여부를 선택할 수 있습니다.
IFS 함수는 여러 조건을 평가하여 첫 번째 TRUE 조건에 해당하는 값을 반환할 수 있습니다.
SWITCH 함수를 사용하여 값-결과 쌍 목록을 기준으로 하나의 값(식이라고도 함)을 평가하여 첫 번째 일치 값에 해당하는 결과를 반환할 수 있습니다.
MAXIFS 함수는 주어진 조건 또는 기준 집합에 의해 지정된 셀 중에서 최댓값 반환에 사용할 수 있습니다.
MINIFS 함수는 주어진 조건 또는 기준 세트로 지정된 셀 중 최솟값 반환에 사용할 수 있습니다.
위의 모든 기능은 GcExcel이라고 하는 에서 지원됩니다.
GcExcel은 Microsoft Excel 호환 스프레드시트를 빠르게 만들고 조작하고 변환 및 공유할 수 있는 포괄적인 API를 개발자에게 제공하는 고성능 스프레드시트 솔루션입니다. GcExcel을 하는 방법은 이 빠른 자습서를 참조하세요.
실제 시나리오를 달성하기 위해 GcExcel에서 이런 기능들의 구현 방법을 살펴보겠습니다.
사용 사례: 대기질 지수 보고서
대기질 지수(AQI)는 특정 영역이나 지역의 대기질을 측정하는 지표입니다. 이 블로그에서는 GcExcel에 새롭게 도입된 기능을 활용한 AQI Report를 제작하여 미국 4대 도시와 국가별 10개 주요 도시의 대기질 지수를 표시합니다.
데이터 소스
아래 데이터는 GcExcel을 이용하여 엑셀 파일로 저장된 워크북의 "데이터" 워크시트에 추가됩니다.
다음은 GcExcel에 위 데이터를 추가하기 위한 샘플 코드 조각입니다.
//Define source data
object[,] sourceData = new object[,] {
{ "Current AQI", "City", "Country"},
{ 31, "Oklahoma City","US"},
{ 5, "New York City","US"},
{ 101, "Philadelphia","US"},
{ 76, "Washington", "US"},
{ 25, "London", "United Kingdom"},
{ 148, "New Delhi", "India"},
{ 35, "Tokyo", "Japan"},
{ 174, "Beijing", "China"},
{ 45, "Canberra", "Australia"},
{ 56, "Rome", "Italy"},
{ 305, "Hanoi", "Vietnam"},
{ 161, "Doha", "Qatar"},
{ 209, "Dhaka","Bangladesh"}
};
//Add new worksheet and name it as 'Data'
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Data";
//Add source data
worksheet.Range["A1:C14"].Value = sourceData;아래 섹션에서 설명한 것처럼 AQI 보고서를 만드는 데이터 소스로 사용됩니다.
AQI 보고서
AQI 보고서는 위에서 언급한 데이터를 사용하고 이 기능을 구현하여 다음을 표시합니다.
위치 열은 TEXTJOIN/CONCAT 함수를 사용하여 "City"와 "Country" 열의 조합을 표시합니다.
현재 AQI 열. '현재 AQI' 열에서 AQI 값을 바로 표시합니다.
건강 염려 수준 열은 IFS/SWITCH 함수를 사용하여 건강 염려 심각도를 표시합니다.
위의 내용과 별개로 AQI 보고서는 각각 MAXIFS 및 MINIFS 함수를 사용하여 AQI가 가장 나쁜 미국 도시와 가장 좋은 미국 도시를 표시합니다. 최종 AQI 보고서가 어떻게 표시되는지 간단히 살펴보겠습니다.
위의 이미지에서 AQI 보고서에 적용된 색 구성표는 조건부 서식이라고 하는 GcExcel의 또 다른 유용한 기능을 사용하여 수행됩니다. 자세한 내용은 GcExcel 문서의 을 참조하세요.
참고: 동일한 출력을 달성하는 유사한 기능의 사용을 보여주기 위해 동일한 AQI 보고서가 "TEXTJOIN&IFS" 및 "CONCAT&SWITCH"라는 두 개의 개별 워크시트에 생성됩니다.
위치 목록 생성
AQI 보고서의 "위치" 열 값은 TEXTJOIN 또는 CONCAT 함수를 사용하여 생성됩니다.
TEXTJOIN 함수
TEXTJOIN 함수는 여러 범위 및/또는 문자열의 텍스트를 결합합니다. 또한 'delimiter' 및 'ignore empty' 인수도 제공합니다.
구문: TEXTJOIN(delimiter, ignoreempty, text1, [text2], ...)
"TEXTJOIN&IFS" 워크시트에 TEXTJOIN 함수를 적용하여 "Data" 시트의 "도시" 및 "국가" 열을 결합했습니다. 여기서 쉼표는 구분자로 사용되며 빈 셀 무시가 true로 설정됩니다.
다음은 GcExcel에서 TEXTJOIN 함수를 사용하기 위한 샘플 코드 조각입니다.
//Apply TEXTJOIN function worksheet1.Range["A4"].Formula = "=TEXTJOIN(\", \", true, Data!B2, Data!C2)"; worksheet1.Range["A5"].Formula = "=TEXTJOIN(\", \", true, Data!B3, Data!C3 )"; worksheet1.Range["A6"].Formula = "=TEXTJOIN(\", \", true, Data!B4, Data!C4 )"; worksheet1.Range["A7"].Formula = "=TEXTJOIN(\", \", true, Data!B5, Data!C5 )"; worksheet1.Range["A8"].Formula = "=TEXTJOIN(\", \", true, Data!B6, Data!C6 )"; worksheet1.Range["A9"].Formula = "=TEXTJOIN(\", \", true, Data!B7, Data!C7 )"; worksheet1.Range["A10"].Formula = "=TEXTJOIN(\", \", true, Data!B8, Data!C8 )"; worksheet1.Range["A11"].Formula = "=TEXTJOIN(\", \", true, Data!B9, Data!C9 )"; worksheet1.Range["A12"].Formula = "=TEXTJOIN(\", \", true, Data!B10, Data!C10 )"; worksheet1.Range["A13"].Formula = "=TEXTJOIN(\", \", true, Data!B11, Data!C11 )"; worksheet1.Range["A14"].Formula = "=TEXTJOIN(\", \", true, Data!B12, Data!C12 )"; worksheet1.Range["A15"].Formula = "=TEXTJOIN(\", \", true, Data!B13, Data!C13 )"; worksheet1.Range["A16"].Formula = "=TEXTJOIN(\", \", true, Data!B14, Data!C14 )";
TEXTJOIN 함수를 적용한 후 출력은 다음과 같습니다.
CONCAT 함수
CONCAT 함수는 여러 범위 및/또는 문자열의 텍스트를 결합합니다.
구문: CONCAT(text1, [text2], ...)
TEXTJOIN 함수와 동일한 출력을 얻기 위해 "CONCAT&SWITCH" 워크시트에 CONCAT 함수를 적용했습니다. 이미 언급한 것처럼 CONCAT 함수는 구분 기호를 지정하는 옵션을 제공하지 않습니다. 따라서 구분 기호는 다른 텍스트 값처럼 연결될 텍스트 인수로도 제공됩니다.
다음은 GcExcel에서 CONCAT 함수를 사용하기 위한 샘플 코드 조각입니다.
//Apply CONCAT function worksheet2.Range["A4"].Formula = "=CONCAT(Data!B2, \", \", Data!C2)"; worksheet2.Range["A5"].Formula = "=CONCAT(Data!B3, \", \", Data!C3 )"; worksheet2.Range["A6"].Formula = "=CONCAT(Data!B4, \", \", Data!C4 )"; worksheet2.Range["A7"].Formula = "=CONCAT(Data!B5, \", \", Data!C5 )"; worksheet2.Range["A8"].Formula = "=CONCAT(Data!B6, \", \",Data!C6 )"; worksheet2.Range["A9"].Formula = "=CONCAT(Data!B7, \", \", Data!C7 )"; worksheet2.Range["A10"].Formula = "=CONCAT(Data!B8, \", \", Data!C8 )"; worksheet2.Range["A11"].Formula = "=CONCAT(Data!B9, \", \",Data!C9 )"; worksheet2.Range["A12"].Formula = "=CONCAT(Data!B10, \", \", Data!C10 )"; worksheet2.Range["A13"].Formula = "=CONCAT(Data!B11, \", \", Data!C11 )"; worksheet2.Range["A14"].Formula = "=CONCAT(Data!B12, \", \", Data!C12 )"; worksheet2.Range["A15"].Formula = "=CONCAT(Data!B13, \", \", Data!C13 )"; worksheet2.Range["A16"].Formula = "=CONCAT(Data!B14, \", \", Data!C14 )";
어떤 것을 선택할까요?
TEXTJOIN은 CONCAT 기능보다 유연성과 기능을 더 많이 제공하므로 더 나은 후보입니다. TEXTJOIN은 delimiter와 ignoreempty 인수를 추가 옵션으로 제공합니다. 다음은 이를 더 잘 이해하기 위한 예입니다.
건강 염려 수준을 평가합니다.
AQI 보고서의 "건강 염려 수준" 열 값을 IFS 또는 SWITCH 함수를 사용하여 평가할 수 있습니다.
아래 표는 현재 AQI의 값이 특정 범위에 포함되는 위치를 기준으로 "건강 염려 수준"을 평가 방법을 보여줍니다. 이러한 범위 값은 IFS 및 SWITCH 함수에 전달되는 조건 또는 식에 대한 기준 역할을 합니다.
IFS 함수
하나 이상의 조건이 충족 여부를 확인하고 첫 번째 TRUE 조건에 해당하는 값을 반환합니다.
구문: IFS(condition1, truevalue1, [condition2, truevalue2], ...)
"TEXTJOIN&IFS" 워크시트에 IFS 함수를 적용하여 현재 AQI 값이 속하는 범위를 식별하고 해당 결과를 반환하는 여러 조건을 평가했습니다.
다음은 GcExcel에서 IFS 함수를 사용하기 위한 샘플 코드 조각입니다.
//Apply IFS function worksheet1.Range["C4"].Formula = "=IFS(B4>300, \"Hazardous\", B4>200, \"Very Unhealthy\", B4>150, \"Unhealthy\", B4>100, \"Moderate\", B4>50, \"Satisfactory\", B4<=50, \"Good\")"; worksheet1.Range["C5"].Formula = "=IFS(B5>300, \"Hazardous\", B5>200, \"Very Unhealthy\", B5>150, \"Unhealthy\", B5>100, \"Moderate\", B5>50, \"Satisfactory\", B5<=50, \"Good\")"; worksheet1.Range["C6"].Formula = "=IFS(B6>300, \"Hazardous\", B6>200, \"Very Unhealthy\", B6>150, \"Unhealthy\", B6>100, \"Moderate\", B6>50, \"Satisfactory\", B6<=50, \"Good\")"; worksheet1.Range["C7"].Formula = "=IFS(B7>300, \"Hazardous\", B7>200, \"Very Unhealthy\", B7>150, \"Unhealthy\", B7>100, \"Moderate\", B7>50, \"Satisfactory\", B7<=50, \"Good\")"; worksheet1.Range["C8"].Formula = "=IFS(B8>300, \"Hazardous\", B8>200, \"Very Unhealthy\", B8>150, \"Unhealthy\", B8>100, \"Moderate\", B8>50, \"Satisfactory\", B8<=50, \"Good\")"; worksheet1.Range["C9"].Formula = "=IFS(B9>300, \"Hazardous\", B9>200, \"Very Unhealthy\", B9>150, \"Unhealthy\", B9>100, \"Moderate\", B9>50, \"Satisfactory\", B9<=50, \"Good\")"; worksheet1.Range["C10"].Formula = "=IFS(B10>300, \"Hazardous\", B10>200, \"Very Unhealthy\", B10>150, \"Unhealthy\", B10>100, \"Moderate\", B10>50, \"Satisfactory\", B10<=50, \"Good\")"; worksheet1.Range["C11"].Formula = "=IFS(B11>300, \"Hazardous\", B11>200, \"Very Unhealthy\", B11>150, \"Unhealthy\", B11>100, \"Moderate\", B11>50, \"Satisfactory\", B11<=50, \"Good\")"; worksheet1.Range["C12"].Formula = "=IFS(B12>300, \"Hazardous\", B12>200, \"Very Unhealthy\", B12>150, \"Unhealthy\", B12>100, \"Moderate\", B12>50, \"Satisfactory\", B12<=50, \"Good\")"; worksheet1.Range["C13"].Formula = "=IFS(B13>300, \"Hazardous\", B13>200, \"Very Unhealthy\", B13>150, \"Unhealthy\", B13>100, \"Moderate\", B13>50, \"Satisfactory\", B13<=50, \"Good\")"; worksheet1.Range["C14"].Formula = "=IFS(B14>300, \"Hazardous\", B14>200, \"Very Unhealthy\", B14>150, \"Unhealthy\", B14>100, \"Moderate\", B14>50, \"Satisfactory\", B14<=50, \"Good\")"; worksheet1.Range["C15"].Formula = "=IFS(B15>300, \"Hazardous\", B15>200, \"Very Unhealthy\", B15>150, \"Unhealthy\", B15>100, \"Moderate\", B15>50, \"Satisfactory\", B15<=50, \"Good\")"; worksheet1.Range["C16"].Formula = "=IFS(B16>300, \"Hazardous\", B16>200, \"Very Unhealthy\", B16>150, \"Unhealthy\", B16>100, \"Moderate\", B16>50, \"Satisfactory\", B16<=50, \"Good\")";
다음은 IFS 함수를 적용한 후의 데이터입니다.
SWITCH 함수
"CONCAT&SWITCH" 워크시트에 SWITCH 함수를 적용하여 IFS 함수와 동일한 결과를 얻었습니다.
구문: SWITCH(expression, value1, result1, [default or value2, result2] ...)
SWITCH 함수는 값 목록에 대해 하나의 값(식이라고도 함)을 평가하고 첫 번째 일치 값에 해당하는 결과를 반환합니다. 아래의 코드 조각을 통해 알 수 있듯이 SWITCH 함수는 식의 값을 "True"로 지정합니다.
또한 여러 조건을 평가하여 Current AQI 값이 속하는 범위를 식별하고 True로 표시되는 첫 번째 조건에 해당하는 결과를 반환합니다(식 참조).
다음은 GcExcel에서 SWITCH 함수를 사용하기 위한 샘플 코드 조각입니다.
//Apply SWITCH function worksheet2.Range["C4"].Formula = "=SWITCH(true, B4>300, \"Hazardous\", B4>200, \"Very Unhealthy\", B4>150, \"Unhealthy\", B4>100, \"Moderate\", B4>50, \"Satisfactory\", B4<=50, \"Good\")"; worksheet2.Range["C5"].Formula = "=SWITCH(true, B5>300, \"Hazardous\", B5>200, \"Very Unhealthy\", B5>150, \"Unhealthy\", B5>100, \"Moderate\", B5>50, \"Satisfactory\", B5<=50, \"Good\")"; worksheet2.Range["C6"].Formula = "=SWITCH(true, B6>300, \"Hazardous\", B6>200, \"Very Unhealthy\", B6>150, \"Unhealthy\", B6>100, \"Moderate\", B6>50, \"Satisfactory\", B6<=50, \"Good\")"; worksheet2.Range["C7"].Formula = "=SWITCH(true, B7>300, \"Hazardous\", B7>200, \"Very Unhealthy\", B7>150, \"Unhealthy\", B7>100, \"Moderate\", B7>50, \"Satisfactory\", B7<=50, \"Good\")"; worksheet2.Range["C8"].Formula = "=SWITCH(true, B8>300, \"Hazardous\", B8>200, \"Very Unhealthy\", B8>150, \"Unhealthy\", B8>100, \"Moderate\", B8>50, \"Satisfactory\", B8<=50, \"Good\")"; worksheet2.Range["C9"].Formula = "=SWITCH(true, B9>300, \"Hazardous\", B9>200, \"Very Unhealthy\", B9>150, \"Unhealthy\", B9>100, \"Moderate\", B9>50, \"Satisfactory\", B9<=50, \"Good\")"; worksheet2.Range["C10"].Formula = "=SWITCH(true, B10>300, \"Hazardous\", B10>200, \"Very Unhealthy\", B10>150, \"Unhealthy\", B10>100, \"Moderate\", B10>50, \"Satisfactory\", B10<=50, \"Good\")"; worksheet2.Range["C11"].Formula = "=SWITCH(true, B11>300, \"Hazardous\", B11>200, \"Very Unhealthy\", B11>150, \"Unhealthy\", B11>100, \"Moderate\", B11>50, \"Satisfactory\", B11<=50, \"Good\")"; worksheet2.Range["C12"].Formula = "=SWITCH(true, B12>300, \"Hazardous\", B12>200, \"Very Unhealthy\", B12>150, \"Unhealthy\", B12>100, \"Moderate\", B12>50, \"Satisfactory\", B12<=50, \"Good\")"; worksheet2.Range["C13"].Formula = "=SWITCH(true, B13>300, \"Hazardous\", B13>200, \"Very Unhealthy\", B13>150, \"Unhealthy\", B13>100, \"Moderate\", B13>50, \"Satisfactory\", B13<=50, \"Good\")"; worksheet2.Range["C14"].Formula = "=SWITCH(true,B14>300, \"Hazardous\", B14>200, \"Very Unhealthy\", B14>150, \"Unhealthy\", B14>100, \"Moderate\", B14>50, \"Satisfactory\", B14<=50, \"Good\")"; worksheet2.Range["C15"].Formula = "=SWITCH(true,B15>300, \"Hazardous\", B15>200, \"Very Unhealthy\", B15>150, \"Unhealthy\", B15>100, \"Moderate\", B15>50, \"Satisfactory\", B15<=50, \"Good\")"; worksheet2.Range["C16"].Formula = "=SWITCH(true,B16>300, \"Hazardous\", B16>200, \"Very Unhealthy\", B16>150, \"Unhealthy\", B16>100, \"Moderate\", B16>50, \"Satisfactory\", B16<=50, \"Good\")";
어떤 것을 선택할까요?
IFS 및 SWITCH 함수의 사용은 일반적으로 아래에 설명한 사용 시나리오에 따라 다릅니다.
조건-값 쌍이 각 조건에 대해 서로 다른 식과 연산자를 포함하는 경우 IFS 함수를 사용해야 합니다.
SWITCH 함수는 평가할 모든 조건에서 식과 연산자가 동일한 경우에 사용해야 합니다. 또한 지정된 조건과 일치하는 항목이 없을 경우 반환되는 SWITCH 함수(아래 예의 경우 'Invalid')에 기본값을 지정할 수도 있습니다.
최고, 최악의 AQI 도시 표시
이 섹션은 "TEXTJOIN&IFS", "CONCAT&SWITCH" 워크시트의 MAXIFS 및 MINIFS 함수를 사용하여 최고, 최악의 AQI 도시를 평가하는 방법을 이해합니다. 이러한 함수는 원하는 결과를 표시하기 위해 MATCH, INDEX 함수와 중첩됩니다.
MAXIFS 및 MINIFS 함수
MAXIFS 및 MINIFS 함수는 주어진 조건 또는 기준에 의해 지정된 셀 간의 최댓값과 최솟값을 검색합니다.
구문: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
구문: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MAXIFS와 MINIFS 함수를 적용하여 "국가" 값이 "미국"인 "데이터" 시트에서 AQI 최댓값, 최솟값을 찾았습니다.
다음은 GcExcel에서 MAXIFS와 MINIFS 함수를 사용하기 위한 샘플 코드 조각입니다. 보시다시피, 우리는 또한 MATCH 및 INDEX 함수를 사용하여 AQI가 최고, 최악인 도시의 값을 찾았습니다.
//Apply MAXIFS and MINIFS functions worksheet1.Range["B20"].Formula = "=INDEX(Data!B2:B14, MATCH(MAXIFS(Data!A2:A14,Data!C2:C14,\"US\"),Data!A2:A14,0))"; worksheet1.Range["B21"].Formula = "=INDEX(Data!B2:B14, MATCH(MINIFS(Data!A2:A14,Data!C2:C14,\"US\"),Data!A2:A14,0))";
출력은 MAXIFS와 MINIFS 함수를 다른 중첩 함수와 함께 적용한 후 다음과 같이 표시됩니다.
최종 AQI 보고서
//Save workbook
workbook.Save("AQI_Report.xlsx");두 워크시트에서 최종 AQI 보고서가 다음과 같이 표시됩니다.
“TEXTJOIN&IFS” 워크시트: 여기서 TEXTJOIN, IFS, 중첩 MAXIFS, MINIFS 함수가 사용됩니다.
“CONCAT&SWITCH” 워크시트: 여기서 CONCAT, SWITCH, 중첩 MAXIFS, MINIFS 함수가 사용됩니다.
위에서 설명한 모든 코드 조각이 포함된 을 다운로드하여 이 사용 사례를 직접 해보세요.
블로그의 파트 2에서는 Excel 2019에서 소개된 차트와 이를 GcExcel에서 사용하는 방법에 대해 다룰 예정입니다.
지금 바로 GcExcel .NET Core를 다운로드하여 직접 테스트해보세요!
댓글목록
등록된 댓글이 없습니다.
