알아두면 좋은 Excel 함수 - 논리 함수
엑셀로 Logic을 작성해보자
며칠 전 함수 몇 개만 알아도 금방 끝날 일에 시간을 쓰는 동료를 도와주다가, 이런 어려움을 겪는 사람들이 꽤 있을 것 같아 브런치에도 적어본다.
엑셀의 함수는 무궁무진하고 나는 그중 일부밖에 알지 못한다. 엑셀을 전문가처럼 잘 다룰 수 있다면 좋겠지만, 일반 사무직에게 그 정도의 수준의 엑셀 실력이 요구될 거라고는 생각하지 않는다. 요새는 데이터를 다루는 프로그램들도 워낙 많아지기도 했고, 애초에 방대한 데이터를 다뤄야 하는 직무에서는 SAS 프로그램이나 별도의 데이터 툴을 사용하여 처리하니 말이다.
하지만 그럼에도 불구하고 엑셀은 여전히 중요하다. 데이터를 검토해야 하는 직무라면 더더욱 그렇다. 간단한 조작만으로 수천~수만 개의 데이터 중 원하는 목록만 골라낼 수 있고, 또 데이터를 원하는 형태로 편집할 수도 있다. 그러므로 모든 사용법을 익히지는 못하더라도 어떤 함수가 있는지 정도는 알아두길 바란다. 필요한 순간 그 존재를 떠올릴 수 있다면 구체적인 사용법은 그때그때 인터넷 검색을 통해 해결할 수 있다.
Excel 함수 시리즈는 사용 목적에 따라 크게 7개의 목차로 구성하였다.
1. Logic을 위한 함수
2. 계산을 위한 함수
3. 날짜를 구하기 위한 함수
4. 데이터 개수를 구하기 위한 함수
5. 참조/검색을 위한 함수
6. 텍스트 추출을 위한 함수
7. 텍스트 편집을 위한 함수
이번 글에서는 그 첫 번째인 "Logic을 위한 함수"에 대해 알아보도록 하자.
1. 로직(Logic)을 위한 함수
데이터 검토 목적으로 엑셀을 사용하는 경우라면, 간단한 로직 정도는 작성해보았을 것이다. 만약 로직이 어렵게 느껴지더라도 아래 예시 정도는 해본 적이 있을 거라 생각한다.
=A1=B1
간단하지 않은가? 사실 하나하나씩 뜯어보면 그리 어렵지만도 않다. 길게 늘어진 수식도 함수 별로 쪼개 놓으면 모두 이해 가능한 수준이 된다. 대부분은 기본적인 함수 여러 개를 뭉쳐놓은 것뿐이고, 누구나 인터넷과 충분한 시간만 있다면 해석할 수 있다. 다만, 한눈에 읽기 힘들다 보니 막연한 두려움에 지레 겁먹고 포기하게 되는 것이다.
아래 함수들은 Logic을 작성하기 위해 알아두길 추천하는 논리 함수들이다. 무척 간단하지만 긴긴 로직의 가장 기본이 되는 함수들이니 기회가 되면 따라치며 연습해보면 좋겠다.
1) IF
조건 부분의 수식이 참인지 거짓인지를 확인 후 설정한 값 반환
IF함수의 기본 사용법은 =IF(조건, 참일 때 반환할 값, 거짓일 때 반환할 값)이다.
아래 예시에서는 IF함수 조건으로 '두 셀의 값이 같은가?'를 의미하는 A1=B1, 참일 때 반환할 값으로 "같음", 거짓일 때 반환할 값으로 "다름"을 작성해보았다.
<Tip> 엑셀에서는 문자열을 표현할 때 "" 큰따옴표를 사용한다.
만약, B1셀의 값을 코끼리가 아닌 토끼로 변경한다면, A1셀과 B1셀의 값이 달라지게 되고, 조건인 A1=B1이 거짓이 되므로 "다름"이라는 문자가 반환된다.
<Tip> 조건을 'A1셀과 B1셀이 같지 않다'로 변경하고 싶은 경우, 부등호 <>를 사용하면 된다.
2) IFNA
셀의 수식 결과가 #N/A (값을 찾을 수 없음)인지 확인 후 설정한 값 반환
IF함수에서는 조건 부분을 사용자가 작성할 수 있었지만, IFNA함수는 조건이 '#N/A 오류가 있는가?'로 정해져 있으므로, 참인 경우 반환할 값만 작성하면 된다.
만약, 엑셀의 참조 함수를 사용해서 데이터를 가져왔다고 가정해보자. 그러나 몇몇 값들은 해당하는 값을 찾을 수 없어 #N/A 오류 값이 반환된 상태이다. 편의를 위해 오류 값이라고 적었지만, #N/A 오류는 실질적인 값이 아니므로 셀끼리 계산하거나 참조할 수 없다.
하지만 셀들의 합계를 구해야 하거나 오류가 있음을 표시하기 위해 때때로 #N/A 오류를 특정 숫자 값이나 문자로 표현해야 하는 경우가 발생할 수 있는데, 이를 가능케 하는 함수가 바로 IFNA함수이다.
IFNA함수의 사용법은 =IFNA(확인할 셀(또는 수식), #N/A오류 시 반환할 값)이며, 선택한 셀에 #N/A 오류가 없는 경우, 셀의 원래 값이 반환된다.
3) IFERROR
셀의 수식 결과가 오류인지 확인 후 설정한 값 반환
IFERROR함수는 IFNA함수와 그 쓰임이 무척 유사하다. 다만, IFNA함수의 조건이 '#N/A 오류가 있는가?'인 반면, IFERROR함수의 조건은 '#N/A 오류를 포함한 그 밖의 오류가 존재하는가?'라고 생각하면 된다.
=IFERROR(확인할 셀(또는 수식), 오류 시 반환할 값)
IFNA함수와 IFERROR함수가 비슷하게 느껴지겠지만, 데이터를 점차 다루다 보면 #N/A 오류와 타 오류들을 구분해야 하는 경우가 발생할 수 있다. 예를 들어, 발생한 오류를 구분하지 않고 모두 IFERROR로 처리하는 경우, 아예 참조 수식을 잘못 걸어서 오류난 경우와 계산이 잘못되어 오류난 경우를 구분하기 어려울 수 있다. (그러니, 당장 쓸모는 없더라도 이런 게 있구나 정도로 알고 넘어가자.)
4) AND
두 개의 조건을 모두 충족하는지 확인
AND함수를 사용하는 목적은 단어가 내포하는 뜻 그대로 두 개의 조건을 '그리고'로 연결하기 위해서다.
만약, 2개 이상의 조건을 주고 싶다면 AND 또는 OR함수를 사용하여 조건을 작성해야 한다.
=AND(조건1, 조건2)
두 조건이 모두 참인 경우 TRUE를 반환하고, 두 조건이 모두 참이 아니면(하나만 참이거나 둘 다 참이 아닌 경우) FALSE를 반환한다.
<Tip> 3개 이상의 조건을 묶고 싶다면, AND(AND(AND(조건1, 조건2), 조건3), 조건4...)
5) OR
두 개의 조건 중 하나라도 충족하는지 확인
AND함수가 두 조건을 모두 만족해야 TRUE를 반환했다면, OR함수는 두 조건 중 한 가지만 만족하여도 TRUE를 반환한다. 사용법은 AND와 동일하지만 앞의 함수명만 다르다.
=OR(조건1, 조건2)
<Tip> 3개 이상의 조건을 묶고 싶다면, OR(OR(OR(조건1, 조건2), 조건3), 조건4...)
6) 응용하기
위에서 IF, IFNA, IFERROR, AND, OR 함수에 대해 알아보았다. 데이터가 단순하다면 하나의 함수로도 원하는 데이터를 얻을 수 있겠지만, 안타깝게도 우리가 일반적으로 다루게 될 데이터는 최소 2~3가지의 함수를 묶어서 사용해야 하는 경우가 적지 않다.
아래의 예시 목록을 살펴보자.
위 사진에서의 A1~A9 셀 중 우리가 찾고 싶은 값이 오로지 "사과"뿐이라고 가정할 때, 우리는 아래와 같이 IF함수를 사용하여 선택한 셀이 "사과"인지 판단하는 로직을 작성할 수 있다.
=IF(A1="사과", "사과 맞음", "사과 아님")
A1~A9까지 IF함수 적용그러나 위 목록에는 문자뿐 아니라 #N/A 및 #REF 오류도 포함되어 있다. 회사의 프로세스나 상사의 스타일에 따라 다르겠지만, 오류가 발생한 상태 그대로 누군가에게 검토를 요청하는 경우 원래 그렇게 나와야 하는 데이터일지라도 실수라고 오해받을 수도 있고 보기에 그리 좋지도 않다.
이럴 때 아까 소개한 IFNA함수와 IFERROR함수를 사용한다면, 오류 값을 원하는 형태로 표시되게끔 변경할 수 있다.
=IFERROR( IFNA( IF(A1="사과","사과 맞음","사과 아님") , "NA 오류") , "그밖의 오류")
<참고>
*IFNA(선택한 셀 또는 수식, #N/A오류 시 반환할 값)
*IFERROR(선택한 셀 또는 수식, 오류 시 반환할 값)
#N/A 및 #REF 오류를 원하는 값으로 처리여기까지 잘 따라왔는가?
위 수식만으로도 원하는 값을 어느 정도 얻을 수 있지만 한걸음 더 나아가 이번에는 선택한 셀이 비어있는지(blank)도 확인해보자. 본 글에서 다루지 않았지만 엑셀에는 'ISBLANK'라는 함수가 있다. 이 함수는 선택한 셀이 비어있는 경우 TRUE를, 비어있지 않은 경우 FALSE를 반환한다. 먼저 작성했던 수식에 IF함수와 ISBLANK함수를 하나씩 더 추가하여 수식을 응용해보자.
<참고>
=IF(조건1, 참일 때 반환값, IF(조건2, 참일 때 반환값, 거짓값))
=ISBLANK(선택한 셀)
=IFERROR( IFNA( IF(ISBLANK(A1), "빈 셀", IF(A1="사과","사과 맞음","사과 아님") ) ,"NA 오류") ,"그밖의 오류")
빈 셀, 오류, 원하는 조건을 모두 확인할 수 있다위 수식을 통해 #N/A 및 기타 오류, 비어있는 셀까지 모두 확인하는 로직을 작성해 보았다. 본 글에서는 IF를 2번 중첩하여 사용하였지만, 필요시 IFS함수를 사용하여 다중 조건을 걸어서 수식의 길이를 단축할 수도 있다. 또한 소개되지 않은 그 밖의 함수들을 섞어서 응용하는 경우 작성할 수 있는 로직은 무궁무진해진다.
엑셀의 수식도 결국엔 프로그래밍과 맥락이 같다고 볼 수 있다. 코드를 처음부터 작성하지 않아도 되게끔 함수를 만들어놓았다 뿐이지 결국 논리구조를 이해해야 한다는 사실에는 변함이 없다.
하지만 서두에서 강조했듯, 아무리 복잡한 수식도 쪼개 놓고 보면 모두 짧은 함수의 모음일 뿐이다. 그러니 긴 수식을 마주하게 되더라도 너무 낙담하지 말자. 천리길도 한걸음부터이고, 요새는 교재를 구입하지 않아도 유튜브나 블로그에서 유용한 노하우들을 쉽게 발견할 수 있으니 취향에 맞는 방식으로 학습해나가면 된다.
