안녕하세요 모닝수잔입니다 :)
VLOOKUP 함수를 사용하다보면 가끔씩 만나는 불청객이 있습니다. 바로 가져올 데이터가 존재하지 않을 때 발생하는 해당 없음(Not Applicable) 또는 이용할 수 없음(Not Available)을 뜻하는 #N/A 에러입니다. #N/A를 피하는 방법은 크게 2가지가 있는데요, IFERROR 함수를 사용해서 아예 처음부터 #N/A가 생기지 않도록 하거나, 바꾸기(Replace) 기능을 활용해서 이미 생긴 #N/A를 공백으로 바꾸는 방법입니다.
지난 포스팅에서 활용한 표를 다시 한번 예제로 사용해보겠습니다. 한가지 달라진 점이 있다면 아래 화면 빨간칸처럼 새로운 상품명을 하나 추가했습니다. "모닝배기 밴딩팬츠"라는 상품이 왼쪽 수량 표에는 존재하지만 오른쪽 가격 표에는 존재하지 않죠.
그/렇/다/면,
이 상태에서 왼쪽 수량 표의 D열(Column)에 VLOOKUP 함수를 사용해서 오른쪽 가격 표 G열의 값을 가져오게 된다면 어떻게 될까요?
맞습니다. 바로 아래 화면 D16 셀(Cell)의 빨간칸처럼 #N/A라는 에러(Error)가 발생하게 됩니다. "모닝배기 밴딩팬츠"의 가격이 오른쪽 가격 표 G열에는 없기 때문이죠.
※ 자동 채우기 옵션
아래 화면 D3열에는 =VLOOKUP(B3, F:G, 2, 0)이라는 VLOOKUP 함수를 적용한 상태입니다. 우리는 해당 함수를 D4 ~ D8셀까지 모두 적용해야하는데 하나하나 입력하려면 시간이 너무 오래걸리겠죠? 이럴 때는 대표적으로 하나의 셀에만 함수를 입력하고 자동 채우기 옵션을 사용해주시면 편리합니다. 아래 화면에 빨간칸으로 표기한 것처럼 셀의 오른쪽 아래에는 초록색 네모점이 있습니다. 함수를 입력한 대표 셀에서 해당 초록점을 누르신 상태에서 D8셀까지 아래 방향으로 당기면 D3셀과 동일한 함수가 D4 ~ D8셀까지 자동으로 적용되는데 이를 "자동 채우기 옵션"이라고 합니다.
IFERROR 함수
= IFERROR(수식, 에러일 경우 대체할 값이나 식)
IFERROR 함수는 에러 해결 함수입니다. 앞서 우리는 동일한 "상품명"을 기준으로 오른쪽 가격 표의 "가격"을 왼쪽 수량 표로 가져오려고 VLOOKUP 함수를 적용했지만, 오른쪽 가격 표에는 "모닝배기 밴딩팬츠"라는 "상품명"이 존재하지 않기 때문에 데이터를 가져올 수가 없어서 #N/A 에러가 발생했죠? 그게 문제가 되냐구요?
지금 예제는 단순히 5~6개의 상품명만을 다루고 있지만 만약에 상품명이 수천, 수만개라면 어떻게 될까요? #N/A로 가득차있는 엑셀파일을 상사에게 보내드린다면 "박대리, 잠깐 내자리로 좀 오겠나?"라는 메신저를 받을지도 모릅니다 :(
이때, 사용할 수 있는 함수가 바로 IFERROR 함수입니다. IFERROR 함수는 특히나 VLOOKUP 함수와 잘 어울리는 짝꿍인데요, 에러가 발생하는 부분을 #N/A가 아닌 공백으로 표현되도록 만들어줄 수 있습니다. 사용 방법도 매-우 쉬워서 바로 한번 적용해볼까요? 대표 셀인 D3셀을 기준으로 적용해보겠습니다.
= IFERROR(VLOOKUP(B3, F:G, 2, 0) , "")
IFERROR는 셀에서 =IFERROR를 입력 후 소괄호 "(" 를 열고 적용할 수식을 넣습니다. 지금 예제에서는 VLOOKUP 함수를 넣으면 되겠죠? =IFERROR(VLOOKUP(B3, F:G, 2, 0) 이 됩니다.
※ VLOOKUP(B3, F:G, 2, 0)의 의미는?
여기서 잠깐 VLOOKUP 함수를 짧게 복습해보겠습니다. 지금 우리의 대표 셀은 D3셀, "프릴 패턴 벨트원피스"의 "가격"이 입력되어야하는 부분으로 =VLOOKUP(B3, F:G, 2, 0)이라는 수식을 넣었습니다. B3셀, "프릴 패턴 벨트원피스"라는 상품명을 ▶ F열과 G열 전체의 범위 중 가장 왼쪽에서 찾고, ▶ 찾으면 해당 상품명의 2번째 열(즉, G열)에서 ▶ 0(즉, TRUE) 100% 일치하는 값을 가져오라는 뜻입니다.
= IFERROR(VLOOKUP(B3, F:G, 2, 0) , "")
그/리/고,
적용할 수식인 VLOOKUP 함수에 에러가 생긴다면 어떻게 해야할지 콤마 "," 다음에 입력해줍니다. 이때, 우리는 공백으로 만들어주기로 했기 때문에 아무 내용없이 쌍따옴표 ""를 입력해주시면 공백을 넣으라는 뜻이 됩니다. 여기까지 완료되셨다면, 마지막으로 소괄호 ")" 를 닫아줍니다.
아래 화면 빨간칸을 보시면 실제로 기존에는 #N/A로 표기되었던 D8 셀이 이제는 공백으로 표기됨을 확인하실 수 있습니다. 엑셀파일을 받아본 사람 입장에서도 훨씬 깔끔하게 정리된 자료를 받았다는 생각이 들겠죠?
※ "" 공백의 응용
데이터가 너-무 많은 경우, 에러가 발생한 부분을 공백으로 두는게 더 혼동될 것 같다고 하신다면 "" 부분에 다른 기호나 글자를 넣어서 응용하실 수도 있겠죠? 저는 주로 하이픈 "-" 표기를 사용합니다.
바꾸기(Replace) 기능
만/약/에,
IFERROR 함수를 사용하는게 복잡한 것 같다, 좀 더 쉬운 방법 없을까?라고 생각하시는 분들이라면 우리가 흔히 "찾기" 기능으로 알고 있는 Ctrl + F를 활용하실 수 있습니다.
1) 단축키로 Ctrl + F를 누르신 후 "바꾸기(Replace)" 탭을 클릭하시거나,
2) 상단 메뉴에서 홈 ▶ 찾기 및 선택 ▶ 바꾸기(Replace)를 클릭하시면 됩니다.
여기에서 "찾을 내용"에는 #N/A를 입력하고 "바꿀 내용"을 공백으로 두신 후에 "모두 바꾸기"를 클릭하시면 해당 시트(Sheet)에 있는 모든 #N/A가 사라진답니다. 굉장히 간단하죠?
오늘은 엑셀파일 결과물을 좀 더 깔끔하게 만들기 위해 에러를 해결할 수 있는 IFERROR 함수와 바꾸기(Replace) 기능에 대해 살펴봤습니다. 사실 IFERROR 함수가 혼동되신다면 바꾸기(Replace) 기능만 잘 사용하셔도 엑셀에서 활용도가 무궁무진하답니다.
그럼 오늘도 좋은 하루 되세요 :)
