안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
엑셀(excel) 데이터 작업을 하면서 left, right, min등의 함수를 사용하여 문자를 결과값으로 반환하는 함수를 사용하여 텍스트를 제외하고 숫자 데이터만 추출하는 경우가 발생합니다.
무슨말인지 예를 들면 A1의 데이터를 입력했을때 left, right, mid등과 같은 함수를 사용해서 텍스트 A는 제외 하고 1의 값만 결과값으로 추출하여 가져왔을때 결과값 1은 눈으로 보이기에는 숫자1이지만 실제로는 문자1을 결과값으로 반환한것입니다.
따라서 숫자로 보이는 문자 데이터와 일치 하는 참조 범위 영역안에서 결과값을 가져오고자 할때는 1의 데이터는 숫자로 보이지만 실제로는 문자 1이기 때문에 결과값은 오류가 나온다는것이죠.
이럴때 문자1의 데이터를 숫자1로 변환을 해 주어야 하는데 애초에 문자1의 데이터는 수식에 의한 결과값이기 때문에 문자1을 숫자1로 직접 입력할 경우 수식을 작성한 그 의미가 무의미하고 시간낭비에 불과하는것이 되는것입니다.
따라서 애초에 문자를 결과값으로 반환하는 left, right, mid등의 함수와 같이 문자를 결과값으로 반환하는 함수를 결과값이 숫자라면 숫자의 결과값을 결과값으로 반환하는 수식을 작성해야 할 필요가 있습니다.
이러한 이번 내용을 다음의 예제와 함께 확인해 보기로 합니다.
1. 숫자로 보이는 문자 결과값 예제.
숫자로 보이는 문자를 표현하기 위해 샘플 예제 서식을 작성해 보았습니다.
이미지에서 보이는것처럼 E열의 데이터 값은 숫자로 보이는 데이터값이지만 right함수와 mid함수로 작성된 수식의 결과값으로 문자를 결과값으로 반환한것입니다.
E열 작성 수식은 다음과 같습니다.
| 1. E2셀 / right함수 사용 =RIGHT(D2,1) 2. E3셀 / mid함수 사용 =MID(D3,2,2) |
그리고 F열은 Vlookup함수 수식을 작성하여 A,B열을 참조 하여 E열의 데이터가 A열과 일치 하는 경우 B열의 데이터를 결과값으로 반환하는 수식을 작성했습니다.
F열의 수식은 다음과 같습니다.
| =VLOOKUP($E$2,$A$2:$B$6,2,FALSE) |
어떤가요? E열의 데이터느 1~5까지 숫자로 입력되어 있기 때문에 F열에 =VLOOKUP($E$2,$A$2:$B$6,2,FALSE)수식을 작성하여 B열의 데이터를 결과값으로 표기 해야 하는데 이상하게도 그 결과값은 #N/A 오류값을 반환하여 표기 하고 있습니다.
계속해서 반복적으로 말씀 드리지만 E열의 데이터는 보이기에는 숫자로 보이지만 수식에 의한 결과값으로 문자를 결과값으로 반환하여 표기 한것이기 때문에 참조 비교가 되는 범위 A열의 숫자와 E열의 숫자는 동일하지만 결과값을 수식으로 계산 하는 과정에서 E열은 문자, A열은 숫자 데이터이기 때문에 눈으로 보기에는 동일한 데이터값일지라도 애초에 문자와 숫자를 비교 하는것으로 동일한 조건을 갖는 데이터는 존재 하지 않기 때문에 오류값을 반환하게 되는것입니다.
애초에 left, right, mid와 같은 함수는 문자를 결과값으로 반환하는 함수라는것을 기억하셔야 합니다.
보이기에는 숫자일지라도 결과값은 문자 데이터라는 것입니다.
2. 숫자로 보이는 문자 데이터를 숫자로 변환 하는 수식 작성.
1번에서 예제를 확인해 봤듯이 보이는 데이터는 숫자이지만 실제로는 문자 데이터 이기 때문에 조건 비교 결과값을 반환 하는 수식을 작성 할 경우 오류값을 반환하게 됩니다.
따라서 해결 방법은 참조 범위 영역의 숫자 데이터를 문자 데이터로 수정하는 방법과 E열의 숫자로 보이는 문자 데이터를 숫자 데이터로 변환 하여 결과값을 표기 해야 한다는것이겠죠.
참조 범위 영역의 데이터를 변환 하는 방법에 대해서는 직접 입력하는 데이터로 표시형식을 변경하는 방법으로 설명은 생략하도록 하고, E열의 숫자로 보이는 문자 데이터를 숫자로 변환 하여 결과값을 반환 하는 수식 작성 방법에 대해 확인해 보기로 합니다.
이미지를 보면 1번에서 볼 수 있는 예제와 동일한 예제 서식이지만 F열의 =VLOOKUP($E2,$A$2:$B$6,2,FALSE)수식에 의한 결과값이 올바른 결과값을 반환 하여 표기 하고 있습니다.
E열의 데이터가 보이기에도 숫자 데이터이고 결과값 역시 숫자 데이터를 반환하는 결과값이라는것이겠죠.
E열의 문자 데이터를 숫자 데이터로 반환 하는 수식은 다음과 같이 작성 할 수 있습니다.
| 1. E2셀 / right함수 사용 =VALUE(RIGHT(D2,1)) 2. E3셀 / mid함수 사용 =VALUE(MID(D3,2,2)) |
1번의 수식과 차이점은 value함수 사용 차이만 있을뿐 실제 수식은 차이가 없죠.
앞서도 말씀 드렸지만 left, right, mid등과 같은 함수는 결과값을 문자를 반환 하는 함수이고, value함수는 문자를 숫자로 결과값을 반환하여 표기 하는 함수입니다.
따라서 문자로 결과값이 반환되는 결과값을 value함수로 숫자 데이터로 결과값을 반환 할 수 있도록 메인 함수로 작성해 주는것입니다.
아주 간단하게 사용되는 함수 이지만 함수의 사소한 부분의 이해 부족으로 무분별하게 사용할 경우 원하지 않았던 결과값을 반환할 수도 있는 예제를 살펴 볼 수 있었습니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"숫자로 보이는 문자 데이터 숫자로 변환하는 수식 작성 방법" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ 숫자로 보이는 문자 데이터 숫자로 변환하는 수식 작성 방법 샘플 예제 서식 다운로드.
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 가계부 서식과 자동 계산 수식 작성을 해보자. (0) | 2022.11.29 |
|---|---|
| 엑셀(Excel) 네트 급여 세후 급여로 세전 급여 추정 계산 수식 작성을 해보자. (0) | 2022.11.28 |
| 엑셀(Excel) 퇴직금 계산 방법 및 서식과 퇴직소득세 계산 서식 및 퇴직금 지급 금액 계산 수식 작성. (1) | 2022.11.25 |
| 엑셀(Excel) 현재 날짜와 시간 바로 입력 하는 단축키. (0) | 2022.11.25 |
| 엑셀(Excel) 행과 열을 추가 하는데 다른 행과 열의 표 또는 테이블에는 영향을 주지 않고 추가 또는 삭제 하는 방법. (0) | 2022.11.25 |
댓글