새로운 Excel LET 함수로 계산 성능 향상 > 블로그 & Tips

본문 바로가기

Spread.NET

블로그 & Tips

새로운 Excel LET 함수로 계산 성능 향상

페이지 정보

작성자 GrapeCity 작성일 21-01-27 09:15 조회 596회 댓글 0건

본문

최근에 Microsoft Excel에 추가된 새로운 LET 함수는 아주 단순해 보이지만 특정 수식에 대한 계산 성능을 높이는 데 중심적인 역할을 합니다.


때로는 수식 계산 시 동일한 계산을 두 번 이상 반복해야 합니다. 계산 과정에 몇 가지 개별 사례가 포함되는 경우 대개 그렇습니다. 각 사례에 대해 일부 계산을 반복해야 합니다.

이러한 수식의 경우 새로운 LET 함수가 계산 성능을 향상합니다. 또한 LET 함수는 수식에 유용한 이름을 할당한 다음, 이 이름을 사용해 가독성이 더 좋은 새로운 수식을 만듦으로써 복잡한 수식의 로직을 명료화합니다.


이처럼 강력한 기능을 Spread. NET v14에 도입하게 되어 무척 기쁩니다.

이 블로그에는 다음과 같은 섹션이 포함되어 있습니다.


  1. LET 함수 정의

  2. 기본 용도

  3. 사용자 정의 이름

  4. 중첩된 LET 함수

  5. 복잡한 수식 단순화

  6. LET 함수를 이용한 집계

  7. LET 함수를 이용한 성능 향상

  8. LET 함수 데모 샘플


LET 함수 정의

다음과 같이 LET 함수는 최종 인수에서 사용되는 한 개 이상의 로컬 이름을 정의하여 평가할 수식을 지정합니다.

LET(name1, name1expression, [name2, name2expression, ...] namesExpression) 
name1: Local name1 used in namesExpression 
name1expression: Value of name1
name2: Local name2 used in namesExpression name2expression: Value of name2 
namesExpression: Formula expression using name1, name2, etc.


namesExpression에서 사용될 이름의 개수를 정의하거나 LET 함수를 재귀적으로 사용해 이름에 대해 새로운 로컬 범위를 정의합니다.

통합 문서 및 워크시트 이름은 이름 정의 식(name1expression, *name2expression* 등) 및 *namesExpression* 안에서 사용할 수 있습니다.

로컬 이름(name1, *name2* 등)은 더 높은 범위의 중첩 LET 함수에서 정의된 통합 문서 및 워크시트 이름 또는 로컬 이름을 재정의합니다.


기본 용도

새로운 Excel LET 함수로 계산 성능 향상

그림 1 *LET 함수 기본 용도* 예시



몇 가지 간단한 예시를 통해 새로운 LET 함수의 기본 용도를 알 수 있습니다.


첫 번째 예시인 단순 변수 쌍에서는 x라는 이름을 2로 정의하고 Result 셀에서 x+3 = 2+3 = 5를 반환하는 간단한 LET 함수를 보여줍니다.

=LET(x,2,x+3)


두 번째 예시인 여러 변수 쌍에서는 x, y, z라는 여러 개의 이름을 정의하고 이 이름을 사용해 값을 계산하는 LET 함수를 보여줍니다.

=LET(x,1,y,2,z,3,x+y+z=x*y*z)


세 번째 예시인 호출된 수식에서 LET 함수xy라는 여러 이름을 정의하고 이 이름을 SUM이라는 다른 함수와 함께 사용해 계산을 수행합니다.

=LET(x,1,y,2,SUM(x,y))


LET 함수 안에 정의된 이름을 사용하는 식은 복잡할 수 있으므로 인접한 빈 셀로 채워지는 동적 배열로 여러 값을 반환하는 식을 사용하십시오.


네 번째 예시인 동적 배열에서는 C18:E18에 대한 범위 참조를 사용해 C19:E19로 동적 배열을 반환하는 OFFSET 함수와 함께 사용되는 LET 함수를 보여줍니다.

=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))


사용자 정의 이름

새로운 Excel LET 함수로 계산 성능 향상

그림 2 *LET 함수 사용자 정의 이름* 예시


이 예시에서는 통합 문서 또는 워크시트에 정의된 사용자 정의 이름이 LET 함수 내에 정의된 로컬 이름과 어떤 방식으로 상호 작용하는지 보여줍니다. 이 예시에서는 C5 셀의 수식에서 참조되는 "Michael" 값을 사용할 *user* 이름을 정의합니다.

=user


대신에 C9 셀의 수식에서는 아래와 같이 LET 함수를 사용해 "Ivy" 값을 사용할 *user* 이름을 다시 정의합니다.

=LET(user,"Ivy","The actual user is: "&user)


그 결과로 C9는 텍스트를 표시합니다. 실제 사용자는 Ivy입니다. 왜냐하면 로컬에서 정의된 *user*가 워크시트 및/또는 통합 문서에서 정의한 *user*를 재정의하기 때문입니다.


로컬 이름이 재정의하지 않는 경우 *nameExpression* 내부의 통합 문서 또는 워크시트에 정의된 사용자 정의 이름이나 *namesExpression* 내의 모든 이름을 사용할 수 있습니다. 아래와 같이 C13 셀의 수식에서는 워크시트 이름 *user*를 사용해 로컬 이름 *user*를 정의합니다. 그러면 결과는 다시 "Michael"로 표시됩니다.

=LET(user,user,"The actual user is: "&user)


중첩된 LET 함수

새로운 Excel LET 함수로 계산 성능 향상

그림 3 *중첩된 LET 함수* 예시


이 예시에서는 다른 LET 함수*namesExpression* 내에서 재귀적으로 사용될 때 LET 함수의 범위 지정 규칙을 보여줍니다. 이와 동일한 범위 지정 규칙이 중첩된 LET 함수에서 준수되며, 외부 LET 함수에서 정의된 이름은 내부 LET 함수*nameExpressions**namesExpression* 내에서 사용할 수 있습니다. 이러한 이름은 내부 LET 함수에 정의된 새 로컬 이름으로 재정의될 수 있습니다.


첫 번째 예시에서는 외부 LET 함수에서 *var*"First Scope"로 정의한 다음, 내부 LET 함수에서var"Second Scope"로 다시 정의합니다.

=LET(var,"First Scope",LET(var,"Second Scope",var))


결과는 Second Scope로 표시됩니다. 중첩된 LET 함수가 외부 LET 함수를 재정의하기 때문입니다. 이는 *var*라는 이름이 워크시트 또는 통합 문서 이름으로 정의된 경우와 마찬가지입니다.


두 번째 예시에서는 외부 LET 함수에서 *var* 이름을 "First Scope"로 정의한 다음, 내부 LET 함수*var* 이름(외부 LET 함수에서 "First Scope"로 지칭됨)을 사용해 *var* 이름을 다시 정의합니다.

=LET(var"First Scope",LET(var,var,var&"[from the second scope]"))


결과는 First Scope[from the second scope]로 표시됩니다.


복잡한 수식 단순화

새로운 Excel LET 함수로 계산 성능 향상

그림 4 *LET* 함수 *단순화* 예시


이 예시에서는 LET 함수를 사용해 일부 복잡한 수식을 단순화하고 최적화하는 방법을 보여줍니다. 이 예시에서는 수식에서 반복되는 식을 로컬 이름으로 대체하여 계산을 최적화하는 방법을 보여줍니다.


첫 번째 예시에서는 LET 함수를 사용해 *filterCriteria* 이름을 셀 참조 H7로 정의하고 *filteredRange* 이름을 정의하여 B7:E13 범위에서 FILTER 함수를 사용합니다. B7:B13의 값이 "Fred"인 행을 반환하려면 다음과 같이 IFISBLANK 함수를 사용해 실선 대시로 결과의 빈 값을 대체하십시오.

=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange)," - ", filteredRange))


filteredRange는 두 번 사용되며 이미 계산된 배열 결과를 재사용해 배열에서 각 셀의 IF 함수를 평가합니다. LET 함수를 사용하지 않는 경우 이 수식에서는 다음과 같이 IF 함수 내부의 *filteredRange*를 재계산해야 합니다.

=IF(ISBLANK(FILTER(B7:E13,B7:B13=H7))," - ",FILTER(B7:E13,B7:B13=H7))


다음 예시에서는 LET 함수를 사용해 SEQUENCE 함수를 사용하는 *dates*라는 이름을 정의함으로써 C18의 시작 날짜에서 C19의 종료 날짜까지 날짜 값의 세로 동적 배열을 반환합니다. 그런 다음, FILTER 함수를 사용해 WEEKDAY 함수가 해당 주의 해당 날짜가 월요일에서 금요일 중 어느 하나에 해당함을 나타내는 값을 반환하는 경우 해당 날짜만 반환합니다.

=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))


dates는 두 번 사용되며 이미 계산된 배열 결과를 재사용해 배열에서 각 셀의 FILTERWEEKDAY 함수를 평가합니다. LET 함수를 사용하지 않는 경우 이 수식에서는 다음과 같이 FILTERWEEKDAY 함수의 날짜를 재계산해야 합니다.

=FILTER(SEQUENCE(C19-C18+1,1,C18,1),WEEKDAY(SEQUENCE(C19-C18+1,1,C18,1),2)<6)


LET 함수를 이용한 집계

새로운 Excel LET 함수로 계산 성능 향상

그림 5 *LET* 함수 *집계* 예시


이 예시에서는 LET 함수ComboBoxCellType과 함께 사용해 수식 계산을 위한 집계 함수를 선택할 수 있는 드롭다운 목록을 만드는 방법을 보여줍니다.


ComboBoxCellType에서는 EditorValue.ItemData를 사용해 드롭다운 목록에서 선택된 집계 함수와 연결된 숫자를 사용하는 C9를 업데이트하고, 목록에서 선택이 이루어진 후 셀이 즉시 재계산될 수 있도록 StopEditingAfterDropDownItemSelected를 사용하여 편집 모드를 중단합니다.


D9의 수식에서는 LET 함수를 사용해 Table2Salary 열에 대한 구조화된 참조를 사용하는 이름 데이터를 정의하고, 또한 C9 참조에 대한 이름 집계를 정의한 다음, SUBTOTAL을 사용해 값을 계산합니다.

=LET(data,Table2[Salary],aggregate,C9,SUBTOTAL(aggregate,data))


LET 함수를 이용한 성능 향상

새로운 Excel LET 함수로 계산 성능 향상

그림 6 *LET* 함수 *성능* 예시


이 예시에서는 LET 함수를 사용해 동적 배열 값을 계산하고 수식에서 이 배열을 반복적으로 참조할 때 성능을 높이는 방법을 보여줍니다.


두 가지 스프레드시트 컨트롤 모두 500개의 이름 및 주소가 나열된 동일 목록으로 초기화되며, 두 스프레드시트 모두 N2 셀에서 이 수식을 사용해 동적 배열에서 고유 상태 정렬 목록을 반환합니다.

=SORT(UNIQUE(Table1[state]))


두 가지 스프레드시트 컨트롤 모두 O2:O48 셀의 수식을 사용해 테이블의 행 개수를 계산하고, 그 결과에 따라 중첩된 IF 함수에서 문자열 값을 반환합니다.


다음과 같이 왼쪽에서는 LET 함수를 사용해 이 계산을 최적화하고 IF 함수 내에서 동적 배열 결과를 재사용합니다.

=LET(cities,ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,""))),IF(cities<10,"Less than 10.",IF (cities<20,"Between 10 and 19.",IF(cities<30,"Between 20 and 29","30 or more."))))


오른쪽에서는 LET 함수를 사용하지 않고 대신 IF 함수 내에서 도시(city)에 대한 식을 반복합니다.

=IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))<10,"Less than 10.",IF(ROWS(UNIQUE(FILTER(Table1  
[city],Table1[state]=$N2,"")))<20,"Between 10 and 19.",IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"")))  
<30,"Between 20 and 29","30 or more.")))


재계산을 위해 재계산 메뉴 항목을 활성화하는 작업에서는 고유 코드를 사용해 CalculationEngine에서 CalculationOnDemand를 비활성화합니다. 이 작업을 통해 모든 셀이 재계산됩니다. 결과는 각 스프레드시트 컨트롤 상단에 있는 TitleInfo에 표시됩니다.


왼쪽에서는 LET 함수를 사용해 LET 함수를 사용하지 않는 오른쪽보다 2~4배 빠른 속도로 계산합니다.


LET 함수 데모 샘플

위 예시에 나와 있는 전체 소스 코드가 포함된 LET 함수의 데모 샘플은 아래와 같이 C#VB에 대해 사용할 수 있습니다.

  • 페이스북으로 공유
  • 트위터로  공유
  • 구글플러스로 공유
  • 카카오톡으로 보내기

댓글목록

등록된 댓글이 없습니다.

그레이프시티 홈페이지를 통해 제품에 대해서 더 자세히 알아 보세요!
홈페이지 바로가기

태그

그레이프시티 홈페이지를 통해 제품에 대해서 더 자세히 알아 보세요!
홈페이지 바로가기
이메일 : sales-kor@grapecity.com | 전화 : 1670-0583 | 경기도 안양시 동안구 시민대로 230, B-703(관양동, 아크로타워) 그레이프시티(주) 대표자 : 허경명 | 사업자등록번호 : 123-84-00981 | 통신판매업신고번호 : 2013-경기안양-00331 Copyright ⓒ 2021 GrapeCity inc.