이 코딩 요령을 사용하여 엑셀 수식을 읽기 쉽게 만드세요

엑셀은 최근 몇 년 사이에 완전한 프로그래밍 환경으로 발전했지만, 많은 스프레드시트는 여전히 읽기 어려운 수식의 암호 같은 혼란으로 보입니다. 하지만 코딩 세계의 한 가지 요령을 차용하면 혼란스러운 그리드를 명확하고 논리적인 워크북으로 바꿀 수 있습니다.

비밀은 전역 변수와 지역 변수의 차이를 이해하는 데 있습니다. 이는 공식적인 엑셀 용어는 아니고, 컴퓨터 과학 101 강의처럼 들릴 수 있지만, 실제로는 현대 수식이 어떻게 구조화되는지 생각하는 데 유용한 방법입니다.

전역 변수: '이름이 지정된 범위' 접근 방법

통합 문서 전반에서 작동하는 변수를 정의하기

소프트웨어 개발에서 전역 변수는 프로그램 어디에서나 접근 가능한 데이터 조각입니다. 엑셀에서 가장 가까운 대응물은 일반적으로 상수나 재사용 가능한 참조를 저장하는 데 사용되는 이름 지정 범위입니다. 대부분의 사용자는 세율, 배송비 또는 환율을 무작위 셀에 묻어둡니다. 그러나 이렇게 하면 수식을 보고 *0.9가 무엇을 의미하는지 궁금해지게 됩니다. 요컨대, 이것은 문맥이 없는 값인 '매직 넘버'를 다루고 있는 셈입니다.

제품 목록이 있고 반환하기 전에 가격에 10% 할인을 적용하여 C열에 합계를 표시하고 싶다고 가정해 보겠습니다.

이것을 전역 변수로 설정하는 방법은 다음과 같습니다:

1. 빈 셀에 상수(예: 10% 할인에 대해 0.9)를 입력하세요.
2. 해당 셀을 선택한 상태에서, Name Box에 새 이름—예를 들어 const_Discount—을 입력합니다. 일반적인 관례로는 전역 이름을 const_와 같은 접두사로 시작하는 것입니다. 이렇게 하면 자동완성 메뉴에서 변수를 쉽게 찾을 수 있습니다.
3. 이제 공식에서는 10% 할인율을 하드코딩하지 않고 그 글로벌 변수를 참조합니다.

이것에는 두 가지 이점이 있습니다. 첫째, 요율이 변경되면 해당 명명된 셀 하나만 업데이트하면 됩니다. 둘째, 이 수식은 미래의 자신이나 처음으로 스프레드시트를 방문하는 사람에게 훨씬 더 이해하기 쉽습니다.

모든 셀에 이름이 필요하지는 않지만, '진실의 출처' 데이터에는 필요합니다—전체 워크북에서 일정하게 유지되는 할인율, 세금 구간, 환율과 같은 전역 변수를 워크북 수준의 명명된 범위를 사용하여 사용해야 합니다.

지역 변수: LET 함수

로컬 스코프를 사용하여 복잡한 수식 로직 정리하기

만약 명명된 범위가 스프레드시트 세계의 전역 변수라면, LET 함수는 수식 내에서 지역 변수를 정의합니다. 이는 범위 내에서만 존재하는 이름을 할당할 수 있게 해주며, 화면을 감싸고 감사하기 불가능하게 만드는 거대한 다중 줄 수식, 이른바 '수식 스파게티'를 없애는 궁극적인 방법입니다.

LET을 사용하면 이름을 정의하고, 값이나 계산을 할당하며 최종 출력을 반환할 수 있습니다. 이러한 변수들은 로컬 변수이기 때문에 워크북 전체의 이름 관리자에 혼잡을 주지 않으며, 전역 목록은 가장 중요한 상수들만을 위해 남겨둘 수 있습니다.

이 예제에서는 주문 금액이 1,000달러를 초과할 경우 10% 할인을 적용하고, 그 합계를 D열에 반환한다고 가정해 보세요.

각 항목이 할인 대상인지 확인하기 위해 소계 계산을 여러 번 반복하는 대신, 한 번 변수로 정의할 수 있습니다. 또한, 공식에 임의로 보이는 0.9를 사용하는 대신 의미 있는 이름을 사용하여 최종 계산을 더 쉽게 읽을 수 있습니다:

=LET(
var_Subtotal, [@Price] * [@Quantity],
var_Discount, 0.9,
IF(var_Subtotal > 1000, var_Subtotal * var_Discount, var_Subtotal)
)

지금 무슨 일이 일어나고 있는지 알려드리겠습니다:

• LET: Excel에 최종 계산을 수행하기 전에 하나 이상의 로컬 변수를 정의하려고 한다고 알립니다.
• var_Subtotal, [@Price] * [@Quantity]: 첫 번째 변수와 그 계산을 정의합니다. 엑셀은 이것을 한 번 계산하고 공식을 사용할 때 결과를 재사용합니다. 이러한 변수들은 해당 공식 내에서만 존재하지만, var_와 같은 접두사를 사용하면 전역 명명 범위와 구분하는 데 도움이 됩니다. 다음 섹션에서 이를 확인할 수 있습니다.
• var_Discount, 0.9: 두 번째 변수와 그 값을 정의합니다.
• IF(...): 정의한 이름을 사용하여 최종 결과를 생성합니다.

하이브리드 솔루션: LAMBDA

재사용 가능한 맞춤 로직 라이브러리 구축

LAMBDA 함수는 로컬 변수(LET을 통해)를 사용할 수 있고 전역 이름을 참조할 수 있는 재사용 가능한 논리를 만들 수 있게 해줍니다.

만약 복잡한 LET 수식을 20개의 서로 다른 열에 복사해서 붙여넣고 있다면, 너무 많은 일을 하고 있는 것입니다. 대신 이렇게 하세요:

1. 빈 셀에 전역 변수를 정의하세요. 제 경우에는 0.9가 들어 있는 셀의 이름을 const_Discount라고 지정할 예정인데, 이는 특정 조건이 충족될 경우 적용하려는 10% 할인율을 나타냅니다.
2. 수식 탭에서 이름 관리자를 클릭합니다.
3. 새로 만들기를 클릭하세요.
4. 이름 필드에 fn_BulkDiscount와 같이 관련 이름을 입력합니다. 사용자 정의 함수에 fn_를 접두어로 붙이면, 나중에 셀에 =fn_를 입력하기 시작할 때 Excel이 모든 관련 옵션을 한 곳에 표시하여 올바른 옵션을 찾기 쉽게 만듭니다.
5. 참조 필드에서 LET를 사용하여 계산을 처리하는 LAMBDA 수식을 정의합니다:

=LAMBDA(Price, Quantity, LET(var_Subtotal, Price * Quantity, IF(var_Subtotal > 1000, var_Subtotal * const_Discount, var_Subtotal)))

이걸 나눠서 살펴봅시다:

• LAMBDA(Price, Quantity): 매개변수를 정의합니다. 나중에 셀 참조로 채울 빈 통이라고 생각하세요.
• LET(var_Subtotal, Price * Quantity): 이것이 함수 로직입니다. 방금 정의한 버킷을 사용하는 엔진입니다.
• const_Discount: 전역 훅입니다. 이것은 사용자 정의 함수를 워크북의 '마스터' 설정에 연결합니다.

중요하게도, LAMBDA 안에 LET를 중첩하면 소계가 한 번만 계산되고 함수 전체에서 재사용됩니다. 이것이 없으면 Excel은 동일한 수식을 같은 수식 내에서 여러 번 다시 계산해야 할 수 있으며, 이는 복잡한 수식과 잠재적으로 느린 워크북으로 이어질 수 있습니다.

이제 이 LAMBDA 함수를 여러 테이블에 걸친 LET 계산과 함께 호출할 수 있습니다:

=fn_BulkDiscount([@Price], [@Qty])

LET이 LAMBDA 함수 안에서 반복해서 사용되는데 어떻게 '지역적(local)'일 수 있는지 궁금할 수 있습니다. 프로그래밍에서 '지역적'이라는 것은 변수가 특정 컨테이너에만 속하며 외부로부터는 숨겨진다는 의미입니다. 첫 번째 예제에서는 변수가 단일 셀에 국한됩니다. 이 예제에서는 함수의 로직에 국한됩니다. 두 경우 모두 변수는 워크북의 다른 부분에서는 '숨겨진' 상태로 유지되어 이름 관리자(Name Manager)를 깔끔하고 정리된 상태로 유지합니다.

단순히 '작동하는' 스프레드시트와 사용 및 유지 관리가 쉬운 스프레드시트의 차이는 변수를 다루는 방식에 달려 있습니다. 상수는 전역 범위로, 중간 단계는 지역 범위로 사용하면 더 이상 수식의 복잡한 혼란에 빠지지 않을 것입니다. 하지만 거기서 멈출 필요는 없습니다. 개발자급의 세 탭 구조를 채택하여 워크북에 깔끔하고 논리적인 레이아웃을 제공함으로써 프로그래밍 중심 마인드를 확장할 수 있습니다.