엑셀 수식 오류 값 한번에 지우는 방법

엑셀 함수 실행 시 오류가 발생한 셀을 한 번에 모두 지정하고, 지우는 방법에 대한 포스팅입니다. 백테스팅시 수식 오류는 심심치 않게 발생하는 문제입니다. 

 

엑셀-수식오류-삭제-#N/A지우기

 

엑셀 함수 실행 중 오류 값이 나오는 경우 

엑셀 함수 실행 중에 오류가 나는 경우는 많이 있겠지만, 제가 겪은 경험을 바탕으로 글을 쓰고 있으므로, Vlookup 사용 중 에러에 대해서 말해보려 합니다. 이전 포스팅에서 Vlookup 함수를 이용해 다른 범위에 존재하는 값을 기준값과 매칭 시켜서 가져오는 방법에 대해서 다룬 적이 있습니다. 

 

백테스트에 필요한 엑셀 Vlookup 함수 활용

 

제가 실제로 겪었던 시행착오에 대해서 설명하겠습니다. 

 

원달러환율-Kospi-일별데이터

 

위에 표에는 2개의 데이터가 있습니다. 왼쪽은 원달러 환율의 일별 시가, 종가 데이터이고, 오른쪽은 코스피의 일별 시가, 종가 데이터입니다. 제가 지금부터 하려는 작업은 이 둘의 상관관계를 분석하고, 일별로 어떤 차이점을 보이는지 확인하려 합니다.

 

하지만 날짜를 보면 같은 행에 날짜가 맞지 않는 것을 볼 수 있습니다. 원달러 환율의 최초 기록은 1981년 04월 13일부터이고, 코스피의 최초 기록일은 1980년 01월 04일부터입니다.  같은 날짜에 값의 흐름을 파악해야 하는 것이 목적이기 때문에 일단 두 데이터의 날짜를 맞춰줘야 합니다. 

 

수동으로 40년의 날짜 데이터를 일일이 맞출 수는 있겠으나, 정말 힘듭니다. 힘들 겁니다가 아니라, 힘듭니다.라고 표현한 이유는 제가 해봤기 때문입니다. 비웃을지 모르지만, 엑셀 함수에 대해서 모를 때 저걸 일일이 다 손으로 맞춰본 경험이 있습니다. (지식이 없으면.. 몸이 고생하지만..근성으로 버팁니다..)

 

데이터의 양 말고도, 날짜를 일일이 맞추기 힘든 또 한 가지 이유는 원달러 환율의 값이 없는 날과, 코스피 데이터의 값이 없는 날이 다르게 존재하기 때문입니다. 코스피 시장은 우리나라 공휴일이 휴장일이라 값이 없지만, 환율은 공휴일이어도 값이 있습니다. 그래서 중간중간 서로 날짜에 맞지 않는 빈 데이터들이 발생합니다. 

 

Vlookup 함수를 이용한 날짜 매칭 후 데이터 불러오기 

이런 경우 데이터 클렌징을 해주기 위해, Vlookup 함수를 사용합니다. 자세한 건 이전 포스팅에 기록해 뒀으니 복습의 의미로 간단히 이야기하겠습니다.  =Vlookup(기준이 되는 날짜, 가져 올 데이터 범위, 데이터 범위에서 데이터가 존재하는 열의 숫자, 기준이 되는 것과 완전히 일치한 데이터를 가져올지 여부)  이렇게 함수를 입력하면 아래와 같이 불러와집니다.

 

환율-날짜에-맞춘-코스피-지수

 

이렇게 Vlookup 함수를 사용해서, 위의 표처럼 원달러 환율 데이터 날짜를 기준으로 그 날짜에 해당하는 Kospi 지수 값을 자동으로 매칭 해서 불러올 수 있습니다. 

 

하지만 한 가지 문제가 있어 보입니다. 1981년 05월 05일은 어린이 날로 코스피 시장은 운영을 안해서 값이 없기 때문에 #N/A 에러가 발생했습니다. 40년간의 자료를 정리하다 보면 저런 오류가 굉장히 여러 번 발생할 것입니다. 

 

엑셀에서 #N/A 에러가 발생하면 함수도 에러가 납니다. 만약 저 상황에서 2행부터 18행까지 코스피 데이터의 평균값을 구하는 함수를 입력한다면 #N/A 때문에 에러가 납니다. 저런 에러가 발생할 셀을 공백으로 만들어줘야 함수가 제대로 작동할 것입니다. 

 

엑셀 에러난 셀을 손쉽게 지우는 방법

제가 40년의 데이터 날짜를 일일이 수동으로 맞췄던 것처럼, 오류가 발생한 셀을 일일이 찾아서 지우려고 하시는 분은 없어야 합니다... 너무 고통스럽습니다. 이렇게 오류가 발생한 셀을 지우는 방법은 여러가지 있겠지만, 저는 두 가지를 설명하려 합니다. 

 

1. 애초에 에러가 나지 않게끔 함수를 사용합니다. 

첫 번째 방법은 Vlookup 함수를 처음 사용할 때부터 "에러가 나면 공백으로 남겨둬"라는 함수를 넣어주는 것입니다. 이럴 때 IFERROR 함수를 사용합니다. =IFERROR(vlookup(A, B, C, D), "")  이런 식으로 함수를 짜면 Vlookup 함수가 정상값이면 반환하고, 에러가 발생하면 공백("") 으로 입력하라는 뜻이 됩니다. 

 

에초에 =iferror 함수를 써서 에러 발생시 공백으로 만들어 버리면 해결되지만, 매번 이렇게 iferror를 모든 함수에 넣어주기에는 귀찮습니다. 오류가 날지 안날지도 모르는 상황이니 말입니다. 그리고 나중에 오류가 나는것을 보고 아차 싶어서 다시 수식을 전부 바꿔주기에는 너무 많은 공을 들여야 될 수 도 있습니다. 

 

2. 오류가 난 셀을 모두 자동 지정해서 지워준다. 

두번째 방법은 이 포스팅의 주제이기도 한 오류가 난 셀만 자동 지정해서 지워주는 방법입니다. 에러가 난 셀만 지정해주는 옵션을 사용해서 그 셀만 지워주면 됩니다. 구글 스프레드시트 같은경우에는 찾기 버튼(control+f)을 눌러서 바꿔주거나 지워주면되는데, 엑셀은 #N/A 가 찾기 및 바꾸기로 찾아지지 않습니다. 아래 사진과 같이 문자로 인식하기보다는 고유의 에러로 인식하는듯 해서 Control+F 키로는 오류값을 찾아서 바꾸거나 지울수가 없습니다. (제가 못하는걸수도...?) 

 

#N/A-값을-못찾는-엑셀

 

엑셀 오류를 찾아서 한번에 모두 선택하기 위해서는 Control+G 를 눌러서 이동 옵션을 이용합니다. 이동 명령을 실행시킨 후 옵션에 들어가서 수식 - 오류에만 체크를 한 후 확인을 눌러주면 모든 오류 셀들이 선택된것을 확인할 수 있습니다. 그리고나서 Del 키를 눌러주면 에러가 발생한 모든 셀에 수식이 지워진것을 확인 할 수 있습니다. 

 

엑셀-이동옵션-사용-오류-셀-지우기

 

더 좋은 방법이 있을 수도 있겠지만, 오류가난 셀을 쉽게 제거하는 방법 중 제가 사용하는 방법은 여기까지 입니다. 엑셀로 백테스팅하는데 수고를 덜어주는 포스팅이되길 바라봅니다. 성공투자를 기원합니다.