1. Let과 Switch 함수
둘 다 Microsoft 365용 Excel, Mac용 Microsoft 365용 Excel, 웹용 Excel , Excel 2021, Mac용 Excel 2021에서만 사용 가능한 최신 함수입니다.
가. Let 함수
(1) 정의 및 구문
Let함수는 이름에 값을 할당해서 계산 후 결과 값을 돌려주는 것으로 계산식을 이름에 할당하고, 다시 계산할 수 있는 유용한 함수이며,
구문은 아래와 같이 이름, 값, 계산식 또는 이름2 식을 여러 번 지정할 수 있으며, 마지막 인수는 반드시 계산식으로 끝나야 합니다.
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
(2) 사용 예
① x에 2를 할당하고, y에 3을 할당한 후 x*y의 값을 반환하므로 6이 됩니다.
② 아래 파일을 연 후 작업하기 바랍니다.
아래 수식
=LET(loc,FIND("*",A5),loc)
는 loc에 Find("*",A5), 다시 말해 A5셀에서 *의 위치인 2를 대입한 후 세 번째 인수로 loc의 값을 반환하는 것입니다.
2가 반환됐습니다.
나. Switch 함수
(1) 정의 및 구문
Switch 함수는 조건에 해당하는 값에 따라 결과를 달리하는 것으로, 중첩 if문을 대체할 수 있습니다.
구문은 아래와 같이 조건식, 값, 결과1 식으로 여러 가지 경우에 따라 다른 처리를 할 수 있습니다.
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
(2) 사용 예
아래는 오늘에 해당하는 요일의 일련번호에 따라 1이면 '일', 2면 '월' 식으로 요일을 반환하게 하는 수식입니다.
2. 텍스트로 된 수식의 값 구하기
가. 연산자의 위치 구하기
B5셀에서는 *문자가 있는 경우만 가정하고 수식을 작성했는데,
연산자가 *, +, /, - 4개가 있으므로 이를 가정해서 수식을 작성해야 하며,
연산자를 검색할 때 없으면 에러가 발생하므로 ifError 함수를 이용해 에러일 때의 값을 지정해야 하고,
연산자의 위치를 찾은 값 중 최댓값(Max)이 연산자의 위치가 됩니다.
따라서 max, iferror를 연결해서 곱하기(*) 연산자의 위치를 구하는 것만 해보면
=LET(loc,MAX(IFERROR(FIND("*",A5),0)),loc)입니다.
이 수식은 A5셀에서 *의 위치를 구하는데, 에러가 나면 0을 반환하도록 하고 최댓값을 구하는 것인데, 일단 *의 경우만 한 것입니다.
이제 +, /, -의 경우를 추가하려면
IFERROR(FIND("*",A5),0)을 , 다음에 복사한 후
*를 +로 수정하고,
이런 것을 두 번 더 해서 아래와 같이 수식을 만들면 됩니다.
=LET(loc,
MAX(
IFERROR(FIND("*",A5),0),
IFERROR(FIND("+",A5),0),
IFERROR(FIND("/",A5),0),
IFERROR(FIND("-",A5),0)
)
,loc)
B5셀의 채우기 핸들을 더블 클릭하면 B8셀까지 수식이 복사되는데,
연산자의 위치가 2, 3, 4, 4로 맞게 구해집니다.
나. 연산자 구하기
연산자는 위에서 구한 loc와 Mid 함수를 이용해서
=mid(a5,loc,1)로 구할 수 있는데,
B5셀의 수식 입력줄에 커서를 넣고 수식을 복사한 후 Esc키를 누르고, C5셀에 붙여 넣은 후
loc를 mid(a5,loc,1)로 수정하면 됩니다.
=LET(loc,MAX(IFERROR(FIND("*",A5),0),IFERROR(FIND("+",A5),0),IFERROR(FIND("/",A5),0),IFERROR(FIND("-",A5),0)),MID(A5,loc,1))
loc변수를 이용해서 훨씬 이해하기 쉽습니다.
C5셀의 채우기 핸들을 더블 클릭해서 C8셀까지 복사합니다.
다. 연산자의 종류에 따른 수식의 값 구하기 1
연산자의 종류에 따라 처리를 달리해야 하므로 Switch함수를 사용해야 하며,
연산자 왼쪽의 숫자는 loc를 기준으로 보면 -1까지 이고, 오른쪽의 숫자는 loc+1부터 10개로 구하면 됩니다.
(1) 곱하기(*) 일 경우만 처리
따라서, 전체 수식은 곱하기일 때만 만들면 아래와 같습니다.
=LET(loc,MAX(IFERROR(FIND("*",A5),0),IFERROR(FIND("+",A5),0),IFERROR(FIND("/",A5),0),IFERROR(FIND("-",A5),0)),SWITCH(MID(A5,loc,1),"*",LEFT(A5,loc-1)*MID(A5,loc+1,10)))
SWITCH(MID(A5,loc,1),"*",LEFT(A5,loc-1)*MID(A5,loc+1,10))에서
MID(A5,loc,1)은 연산자이고,
"*"는 연산자가 *인 경우가 되고,
LEFT(A5,loc-1)*MID(A5,loc+1,10)에서
LEFT(A5,loc-1)은 A5셀에서 왼쪽 loc-1까지의 텍스트를 가져오는 것이고,
*는 곱하기 연산자이고,
MID(A5,loc+1,10)은 A5셀에서 loc+1부터 10개의 텍스트를 가져오는 것입니다.
따라서, 1*2가 되고, 2란 값이 반환됩니다.
(2) 연산자가 +, /, - 일 경우의 처리
Switch문에서
,LEFT(A5,loc-1)*MID(A5,loc+1,10)을 복사한 후
복사한 범위의 다음에 붙여 넣고,
* 2개를 +로 수정합니다.
다시 ,LEFT(A5,loc-1)*MID(A5,loc+1,10)을 두 번 복사한 후
*를 /와 -로 수정합니다.
"텍스트로 된 수식의 값 구하기(2) - 사칙 연산자 적용"에서
만들었던 아래 수식에 비해 훨씬 깔끔해졌습니다.
=IF(MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0)),1)="*",
LEFT(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))-1)
*
MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))+1,10),
IF(MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0)),1)="+",
LEFT(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))-1)
+
MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))+1,10),
IF(MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0)),1)="/",
LEFT(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))-1)
/
MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))+1,10),
IF(MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0)),1)="-",
LEFT(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))-1)
-
MID(A4,MAX(IFERROR(FIND("+",A4),0),IFERROR(FIND("*",A4),0),IFERROR(FIND("/",A4),0),IFERROR(FIND("-",A4),0))+1,10))
)
)
)
D5셀의 채우기 핸들을 더블 클릭하면
정확한 수식의 결괏값이 구해집니다.
라. 연산자의 종류에 따른 수식의 값 구하기 2
(1) 연산자, 왼쪽의 숫자와 오른쪽 숫자를 변수에 대입하기
Let함수를 이용해서 연산자의 위치를 구한 후 loc에 대입했는데,
이번에는 연산자 왼쪽의 숫자를 left_loc, 오른쪽의 숫자를 right_loc, 연산자를 oper라고 변수명을 정하겠습니다.
그러면 아래 수식
LET(loc,MAX(IFERROR(FIND("*",A5),0),IFERROR(FIND("+",A5),0),IFERROR(FIND("/",A5),0),IFERROR(FIND("-",A5),0)) 에서
끝의 괄호 앞에 변수명과 수식을
left_loc,LEFT(A5,loc-1),
oper,MID(A5,loc,1),
right_loc,MID(A5,loc+1,10)
라고 입력하고,
계산식을 left_loc*right_loc라고 입력하면 1*2는 2가 구해집니다.
(2) 연산자의 종류에 따라 수식의 값 구하기
이제 Switch문까지 결합해서 수식을 입력할 때
left까지 입력하면 left_loc가 변수명으로 나열되므로 탭키를 이용해 쉽게 입력할 수 있습니다.
완성된 수식은 아래와 같이 보기 좋게 표시할 수 있습니다.
=LET(
loc, MAX(IFERROR(FIND("*",A5),0),IFERROR(FIND("+",A5),0),IFERROR(FIND("/",A5),0),IFERROR(FIND("-",A5),0)),
left_loc,LEFT(A5,loc-1),
oper, MID(A5,loc,1),
right_loc,MID(A5,loc+1,10),
SWITCH(oper,"*",left_loc*right_loc,"+",left_loc+right_loc,"/",left_loc/right_loc,"-",left_loc-right_loc)
)
loc, MAX(IFERROR(FIND("*",A5),0),IFERROR(FIND("+",A5),0),IFERROR(FIND("/",A5),0),IFERROR(FIND("-",A5),0)),
left_loc,LEFT(A5,loc-1),
oper, MID(A5,loc,1),
right_loc,MID(A5,loc+1,10)
이라는 구문은
loc에 연산자의 위치 값을 할당하고,
left_loc에는 연산자 왼쪽 숫자를 대입하고,
oper에는 연산자,
right_loc에는 연산자 오른쪽 숫자를 대입하는 것입니다.
SWITCH(oper,"*",left_loc*right_loc,"+",left_loc+right_loc,"/",left_loc/right_loc,"-",left_loc-right_loc)
는
oper의 값에 따라
*인 경우는 왼쪽과 오른쪽의 숫자를 곱하고,
+인 경우는 왼쪽과 오른쪽의 숫자를 더하고,
/인 경우는 왼쪽 숫자를 오른쪽의 숫자로 나누고,
-인 경우는 왼쪽 숫자에서 오른쪽의 숫자를 뺍니다.
수식이 깔끔해져 이해하기 쉽습니다.
E5셀의 채우기 핸들을 더블 클릭하면 수식이 복사되는데 D열의 결괏값과 같습니다.
'Excel' 카테고리의 다른 글
| 여러 가지 조건을 만족하는 값을 찾을 때(4) - 데이터를 결합한 열 생성 후 Vlookup (0) | 2023.07.28 |
|---|---|
| 여러 가지 조건을 만족하는 값을 찾을 때(1) - VLookup, Index+Match, XLookup함수 (0) | 2023.07.24 |
| 텍스트로 된 수식의 값 구하기(2) - 사칙 연산자 적용 (0) | 2023.07.17 |
| RandArray 함수 (0) | 2023.07.15 |
| RandBetween 함수 (0) | 2023.07.14 |