엑기스 함수 마스터 챌린지 2일차_스터디 노트

1. LET/LAMBDA 함수로 사용자 함수를 등록하는 방법

1. LAMBDA 함수 작성: 먼저 워크시트 셀에서 LAMBDA 함수의 구문을 테스트하고 원하는 결과를 얻는지 확인합니다.

   • 구문: =LAMBDA(매개변수1, [매개변수2, ...], 계산식)
   • 예시: 두 숫자를 더하는 간단한 LAMBDA 함수는 =LAMBDA(x, y, x+y) 입니다.

2. 이름 관리자 열기:

   • 엑셀 리본 메뉴에서 수식 탭으로 이동합니다.
   • 정의된 이름 그룹에서 이름 관리자를 클릭합니다.

3. 새 이름 만들기:

   • 이름 관리자 대화상자에서 새로 만들기 버튼을 클릭합니다.
   • 새 이름 대화상자가 나타나면 다음 정보를 입력합니다.
     
     • 이름: 사용자 정의 함수의 이름을 입력합니다 (예: MYADDITION). 함수 이름 지정 규칙을 따릅니다 (공백 없음, 숫자로 시작 불가 등).
     • 참조 대상: 여기에 1단계에서 작성한 LAMBDA 함수 전체를 복사하여 붙여넣습니다 (예: =LAMBDA(x, y, x+y)).
     • 설명 (선택 사항): 함수에 대한 설명을 추가할 수 있습니다.

4. 확인 및 닫기:

   • 새 이름 대화상자에서 확인을 클릭합니다.
   • 이름 관리자 대화상자에서 닫기를 클릭합니다.

5. 사용자 정의 함수 사용:

   • 이제 워크시트의 셀에서 =MYADDITION(값1, 값2) 와 같이 직접 입력하여 방금 만든 사용자 정의 함수를 사용할 수 있습니다.
   • 예시: =MYADDITION(10, 5)를 입력하면 결과로 15가 표시됩니다.

LET 함수는 LAMBDA 함수 내에서 중간 계산 결과나 값을 명명하여 수식을 더 명확하게 만들 때 유용합니다.

• 예시: 할인율을 적용한 최종 가격을 계산하는 함수를 만든다고 가정해 보겠습니다.
  
  • LAMBDA 함수 내에서 LET 사용:
    
    Excel

    =LAMBDA(가격, 할인율, LET(할인액, 가격*할인율, 최종가격, 가격-할인액, 최종가격))
    

  • 위 LAMBDA 함수를 이름 관리자에 CALCULATEFINALPRICE 와 같은 이름으로 등록하면, 셀에서 =CALCULATEFINALPRICE(10000, 0.1) 와 같이 사용할 수 있으며, 결과는 9000이 됩니다.

요약하자면, LAMBDA 함수로 로직을 정의하고, 이를 엑셀의 '이름 관리자'에 등록하여 사용자 정의 함수처럼 사용할 수 있습니다. LET 함수는 이 LAMBDA 함수 내부에서 수식의 가독성과 구조를 개선하는 데 도움을 줍니다.

2. 동적 배열에서 2가지 규칙에 대해 간략하게 정리하기

엑셀 배열 계산의 핵심 규칙

동적 배열을 포함한 엑셀의 배열 수식은 내부적으로 숫자 연산을 통해 논리 조건을 처리합니다. 이 과정에서 다음 두 가지 규칙이 중요하게 작용합니다.

1. 논리값의 숫자 변환: TRUE = 1, FALSE = 0

• 엑셀은 수식 내에서 논리값 TRUE와 FALSE가 산술 연산(덧셈, 곱셈 등)에 사용될 때 자동으로 숫자 값으로 변환합니다.
  
  • TRUE는 숫자 1로 변환됩니다.
  • FALSE는 숫자 0으로 변환됩니다.
• 활용: 이 특성은 특정 조건을 만족하는 항목을 세거나 합계를 구할 때 유용합니다. 예를 들어, (A1:A10 > 50)이라는 조건 배열이 있을 때, 이 배열은 TRUE 또는 FALSE 값들로 채워집니다. 이 배열을 다른 숫자 배열과 곱하면, TRUE(1)인 위치의 값만 남고 FALSE(0)인 위치의 값은 0이 되어 필터링 효과를 낼 수 있습니다.

2. 논리 연산의 산술적 구현: AND = 곱셈, OR = 덧셈

배열 수식에서 여러 조건을 결합할 때 산술 연산자를 사용하여 논리 연산(AND, OR)을 구현할 수 있습니다. 이는 위 1번 규칙(TRUE=1, FALSE=0)을 기반으로 합니다.

• AND 조건 (모든 조건을 만족): 곱셈 (*) 사용

  • 여러 조건 배열을 곱하면, 모든 해당 위치의 요소가 TRUE(1)일 때만 결과가 1(TRUE)이 됩니다. 하나라도 FALSE(0)가 포함되면 결과는 0(FALSE)이 됩니다.
  • 예시: (조건1배열) * (조건2배열)
    • 1 * 1 = 1 (TRUE and TRUE -> TRUE)
    • 1 * 0 = 0 (TRUE and FALSE -> FALSE)
    • 0 * 1 = 0 (FALSE and TRUE -> FALSE)
    • 0 * 0 = 0 (FALSE and FALSE -> FALSE)

• OR 조건 (하나 이상의 조건을 만족): 덧셈 (+) 사용

  • 여러 조건 배열을 더하면, 해당 위치의 요소 중 하나라도 TRUE(1)이면 결과가 1 이상(즉, 0이 아닌 값, 논리적으로 TRUE로 해석 가능)이 됩니다. 모든 해당 위치의 요소가 FALSE(0)일 때만 결과가 0(FALSE)이 됩니다.
  • 예시: (조건1배열) + (조건2배열)
    • 1 + 1 = 2 (TRUE or TRUE -> 결과가 0이 아니므로 TRUE로 간주)
    • 1 + 0 = 1 (TRUE or FALSE -> TRUE)
    • 0 + 1 = 1 (FALSE or TRUE -> TRUE)
    • 0 + 0 = 0 (FALSE or FALSE -> FALSE)
  • 주의: OR 조건을 덧셈으로 구현한 결과가 정확히 1 또는 0이 되게 하려면, 합계가 0보다 큰지를 확인하는 ( (조건1배열) + (조건2배열) ) > 0 과 같은 추가적인 논리 비교를 통해 TRUE/FALSE 배열로 만들 수 있습니다.

이 두 가지 규칙을 이해하면 SUMPRODUCT, FILTER, SUM, IF 함수 등과 결합하여 복잡한 조건의 데이터를 효율적으로 집계하고 분석하는 강력한 동적 배열 수식을 작성할 수 있습니다.

3. 가장 인상 깊었던 함수 3가지 설명

TAKE 함수는 배열 또는 범위에서 지정된 수의 연속적인 행 또는 열을 처음 또는 끝에서부터 추출하여 반환하는 동적 배열 함수입니다. 이를 통해 원본 데이터의 일부만 간편하게 가져와 사용할 수 있습니다.

구문

TAKE(array, rows, [columns])

인수

• array: (필수) 데이터를 가져올 원본 배열 또는 범위입니다.
• rows: (필수) 가져올 행의 수입니다.
  
  • 양수: 배열의 처음부터 지정된 수의 행을 가져옵니다.
  • 음수: 배열의 끝에서부터 지정된 수의 행을 가져옵니다.
  • 예를 들어, rows가 3이면 처음 3개 행을, -3이면 마지막 3개 행을 반환합니다.
• [columns]: (선택) 가져올 열의 수입니다. 생략하면 array의 모든 열을 반환합니다.
  
  • 양수: 배열의 왼쪽(처음)부터 지정된 수의 열을 가져옵니다.
  • 음수: 배열의 오른쪽(끝)에서부터 지정된 수의 열을 가져옵니다.
  • 예를 들어, columns가 2이면 처음 2개 열을, -2이면 마지막 2개 열을 반환합니다.

주요 특징

• 동적 배열 반환: TAKE 함수는 결과로 동적 배열을 반환하므로, 충분한 빈 셀이 있는 경우 결과가 자동으로 확장되어 표시됩니다 (스필 기능).
• 유연한 데이터 추출: 행과 열의 시작 또는 끝에서 원하는 만큼 데이터를 쉽게 가져올 수 있습니다.
• 인수 값의 유효 범위:
  
  • rows 또는 columns 인수로 지정한 값이 원본 array의 실제 행/열 수보다 크면, array 전체를 반환합니다. 예를 들어, 5개 행이 있는 배열에 rows를 10으로 지정하면 5개 행 전체가 반환됩니다.
  • rows 또는 columns 인수로 0을 지정하면 #CALC! 오류가 발생할 수 있습니다. (빈 배열을 반환해야 하는 상황을 의도했다면 다른 접근 방식이 필요합니다.)

DROP 함수는 배열 또는 범위에서 지정된 수의 연속적인 행 또는 열을 처음 또는 끝에서부터 제외(삭제)하고 나머지 부분을 반환하는 동적 배열 함수입니다. TAKE 함수와 반대되는 개념으로 생각할 수 있습니다.

구문

DROP(array, rows, [columns])

인수

• array: (필수) 데이터를 제외할 원본 배열 또는 범위입니다.
• rows: (필수) 제외할 행의 수입니다.
  
  • 양수: 배열의 처음부터 지정된 수의 행을 제외합니다.
  • 음수: 배열의 끝에서부터 지정된 수의 행을 제외합니다.
  • 0: 행을 제외하지 않습니다.
  • 예를 들어, rows가 2이면 처음 2개 행을 제외하고, -2이면 마지막 2개 행을 제외합니다.
• [columns]: (선택) 제외할 열의 수입니다.
  
  • 양수: 배열의 왼쪽(처음)부터 지정된 수의 열을 제외합니다.
  • 음수: 배열의 오른쪽(끝)에서부터 지정된 수의 열을 제외합니다.
  • 0 또는 생략: 열을 제외하지 않습니다.
  • 예를 들어, columns가 1이면 첫 번째 열을 제외하고, -1이면 마지막 열을 제외합니다.

주요 특징

• 동적 배열 반환: DROP 함수는 결과로 동적 배열을 반환하며, 충분한 빈 셀이 있는 경우 결과가 자동으로 확장되어 표시됩니다 (스필 기능).
• 유연한 데이터 제외: 행과 열의 시작 또는 끝에서 원하는 만큼 데이터를 쉽게 제외하고 나머지 데이터를 가져올 수 있습니다.
• 결과가 빈 배열인 경우:
  
  • rows 또는 columns 인수로 지정한 값이 원본 array의 모든 행 또는 열을 제외하게 되면 (예: 5개 행이 있는 배열에 rows를 5 또는 -5로 지정), #CALC! 오류가 반환됩니다. 이는 결과 배열이 비어있게 되기 때문입니다.
• 0 값 사용: rows 또는 columns 인수에 0을 사용하면 해당 차원에서는 아무것도 제외하지 않습니다.

• HSTACK 함수는 여러 배열을 열 방향으로 추가하여 하나의 배열로 만듭니다.
• 각 배열 인수를 함수에 전달하면, 그 배열들이 왼쪽에서 오른쪽 순서로 연결됩니다.
• 결과 배열의 행 수는 연결되는 배열 중 가장 많은 행 수를 가지는 배열의 행 수가 됩니다. 만약 연결되는 배열 중 행 수가 적은 배열이 있다면, 빈 셀은 #N/A 오류로 채워집니다.
• 결과 배열의 열 수는 연결되는 모든 배열의 열 수를 합한 값이 됩니다.

<span class="token operator">=</span><span class="token fx">HSTACK</span><span class="token">(</span>A1:C2<span class="token">,</span> D1:E2<span class="token">)</span><span class="token annotation"><span class="anno-symbol">/ /</span> A1:C2<span class="token">,</span> D1:E2 범위를 수평으로 결합합니다.</span>

<span class="token operator">=</span><span class="token fx">HSTACK</span><span class="token">(</span>'1분기:4분기'!A1:D10<span class="token">)</span><span class="token annotation"><span class="anno-symbol">/ /</span> 1분기~4분기 시트의 A1:D10 범위에 작성된 데이터를 수평으로 결합합니다.</span>

<span class="token operator">=</span><span class="token fx">HSTACK</span><span class="token">(</span>"<span class="token bracket">{</span>"딸기"<span class="token">;</span>"사과"<span class="token">;</span>"귤"<span class="token">;</span>"포도"<span class="token bracket">}</span><span class="token">,</span>'1분기:4분기'!A1:D10<span class="token">)</span><span class="token annotation"><span class="anno-symbol">/ /</span> 1분기~4분기까지 취합된 범위 왼쪽에 '딸기<span class="token">,</span> 사과<span class="token">,</span> 귤<span class="token">,</span> 포도'로 구성된 머리글을 추가합니다.</span>

<span class="token operator">=</span><span class="token fx">VSTACK</span><span class="token">(</span><span class="token bracket">{</span>"제품명"<span class="token">,</span>"1분기<span class="token">,</span>"2분기"<span class="token">,</span>"3분기"<span class="token">,</span>"4분기"<span class="token bracket">}</span><span class="token">,</span><span class="token fx">HSTACK</span><span class="token">(</span>"<span class="token bracket">{</span>"제품명"<span class="token">;</span>"사과"<span class="token">;</span>"귤"<span class="token">;</span>"포도"<span class="token bracket">}</span><span class="token">,</span>'1분기:4분기'!A1:D10<span class="token">)</span><span class="token">)</span><span class="token annotation"><span class="anno-symbol">/ /</span> HSTACK 함수로 결합된 범위 위로 '제품명<span class="token">,</span>1분기<span class="token">,</span>2분기<span class="token">,</span>3분기<span class="token">,</span>4분기'로 구성된 머리글을 추가합니다.</span>