• 최초 작성일: 2022-05-20
  • 최종 수정일: 2022-05-20
  • 조회수: 694 회
  • 작성자: 엑셀러 권현욱
  • 강의 제목: 일부 문자열을 이용하여 원하는 정보 찾기

엑셀러 권현욱

들어가기 전에ㅡ시작된 모든 것에는 끝이 있다

'일상 다반사'라는 말이 있습니다. 여기서 '다반사(茶飯事)'란 '차 마시고 밥 먹는 일'을 말합니다. 그만큼 일상으로 일어나는 일이라는 얘기지요. 코로나19는 같이 모여서 밥먹고 차마시는 일상을 일상이 아닌 것으로 만들어 버렸습니다.

로딩 중...

시작된 모든 것에는 끝이 있는 법! 길고 지루했던, 그래서 도무지 오지 않을 것 같던 일상이 서서히 다가오고 있습니다. 언젠가 또 다른 블랙 스완이 우리 일상을 흐트러 놓을 수 있겠지만 그건 그때 고민하기로 하고 지금은 다시 일상으로 돌아오고 있는 '다반사'를 즐겨보려 합니다.

지금 그 강의 이름은 잊었지만 그 언젠가 강의에서 '3차원 참조를 할 때 와일드카드 문자'를 이용하는 방법에 대해 살펴본 적이 있었습니다. 이번 시간에는 이것을 조금 응용하여 수식에 적용하는 방법에 대해 알아보겠습니다.



이 강의는 아래 동영상 강의 내용과 보완 관계에 있습니다. 동영상을 먼저 보시면 이해하기 더욱 쉽습니다(유튜브 접속 후 '구독'과 '알림 설정' 해 두면 각종 포스트 글을 비롯하여 영상 업로드 정보를 가장 빨리 볼 수 있다는 소문이 있습니다 ㅎㅎ).

[단계 1] 수식 작성

그림과 같이 4자리로 구성된 제품 코드의 일부만 입력하면(I2 셀) 그에 해당하는 데이터의 단가와 재고 수량 정보를 불러옵니다. 이것만 하면 심심하니까 입력한 코드에 해당하는 참조 테이블에서 해당 데이터가 있는 곳에 노란색으로 표시되도록 해보겠습니다.

로딩 중...

어떤 영역에서 원하는 정보를 검색하고자 할 때 VLOOKUP 함수를 이용하면 쉽게 해결할 수 있습니다. 일반적인 사용 형태는 이렇습니다.

VLOOKUP(찾을 값, 참조 영역, 열 번호, 찾기 옵션)

그런데 문제는, 참조 값(위 예제에서는 I2 셀)이 B열에 들어있는 값의 일부라는 사실입니다. 강의 도입 부분에서 '와일드카드 문자를 3차원 참조에 적용'이라는 단서를 달았으니 뭘 말하고자 하는지 이해한 눈치 빠른 분들도 있을 법합니다.^^

단가를 구할 I3 셀에는 이런 수식이 사용되었습니다.

=VLOOKUP("*"&I2, B3:F22, 2, FALSE)

'코드' 끝자리가 I2 셀에 들어 있으므로 *와 I2 셀 값을 & 연산자로 연결해 주었습니다. 이 경우처럼 자릿수가 정해져 있는 경우라면 ?를 사용하여 아래와 같이 표현해도 됩니다.

=VLOOKUP("??"&I2, B3:F22, 2, FALSE)

만약 '코드' 가운데에 찾을 값이 들어 있다면 와일드카드 문자 *를 I2 셀 값 앞뒤로 붙여주면 됩니다.

=VLOOKUP("*"&I2&"*", B3:F22, 2, FALSE)

와일드카드 문자에는 *와 ?가 있으며, 자릿수를 어떻게 취급하느냐에 따라 구분해서 사용합니다. 참고로 와일드카드 문자에는 흔히 '물결 모양'이라고도 부르는 ~틸드: tilde기호도 있습니다. 틸드는 다른 와일드카드 문자 자체에 접근할 때 사용합니다.

[단계 2] 참조 테이블에 서식 지정

1. B3:F22 영역을 범위로 지정하고 [홈] 탭 - [스타일] 그룹에서 [조건부 서식] - [새 규칙] 명령을 선택합니다.

2. [새 서식 규칙] 대화상자에서 [규칙 유형 선택] - [수식을 사용하여 서식을 지정할 셀 결정]을 선택합니다. [규칙 설명 편집]란에 아래 수식을 입력합니다.

=VALUE(RIGHT($B3, 3))=$I$2

B3 셀 값의 오른쪽 3자리를 추출하고, VALUE 함수를 사용하여 완전한 형태의 숫자값으로 바꾼 값을 I2 셀 값과 비교한 겁니다. VALUE는 숫자 형태를 한 문자열 데이터를 실제 숫자로 바꿔주는 함수입니다. 문자열 함수의 결과는 문자열이므로 VALUE 함수를 사용해야 제대로 된 결과를 얻을 수 있습니다.

로딩 중...

3. [서식] 버튼을 클릭하고 [셀 서식] 대화상자의 [채우기] 탭에서 적당한 색을 선택한 다음, [확인] 버튼을 클릭합니다.

4. [새 서식 규칙] 대화상자에서 [확인] 버튼을 클릭하면 지정한 서식이 입혀집니다.

로딩 중...

크게 까다롭거나 복잡한 부분은 없으리라 생각합니다.

오늘은 여기까지...