좌우 데이터가 좀 달라야 하는데 동일하게 만들었습니다. 다른 점은 형식이 왼쪽은 주계좌 및 서브계좌에 -이 있고, 오른쪽의 날짜가 날짜 형식이 아니라 문자로 되어 있어 변환이 필요한 상황입니다.
데이터 건수가 17951로 매우 큽니다.
1. 서브계좌 구하기
이와 같은 경우에 사용하는 함수가 VLookup입니다.
주계좌의 형식이 다르기 때문에 형식을 통일해야 하는데, 오른쪽 검색 범위는 바꿀 수 없으니 왼쪽 것을 Substitute 함수를 이용해 바꾸면
=substitute(a4,"-","")이 됩니다. 다시 말해 하이픈(-)을 공백으로 바꾸는 것입니다.
이제 Vlookup 함수와 결합하면
=vlookup(substitute(a4,"-",""),$a$4:$i$17951,2,0)이 됩니다.
그런데, 이상하게 일치할 것 같은데도 #N/A라고 나오네요.
그래서 E4셀에 =substitute(a4,"-","")=F4라고 입력했는데,
여전히 False라고 나옵니다.
따라서, 수식 탭의 수식 분석 그룹의 수식 계산 명령을 눌러
수식 계산 과정을 살펴보니 왼쪽은 큰따옴표 표시가 있고, 오른쪽은 없습니다.
그렇다면 문자를 숫자로 바꾸는 Value함수를 Substitute 함수 앞에 넣으면 됩니다.
True로 바뀌었습니다. E4셀의 수식을 지웁니다.
이제 Vlookup 수식도
=VLOOKUP(VALUE(SUBSTITUTE(A4,"-","")),$F$4:$I$17951,2,0)라고 바꾸니
표시 형식이 000-00-0000로 설정되어 있어서
값이 없는데ㅗ 000-00-000000로 표시됩니다.
(1) 해결책 1 - 실패
따라서, 표시 형식을 000-00-000000에서 숫자가 있을 때만 표시하는 ###-##-######로 바꾸면 될까 하고 바꿨더니 --로 표시됩니다.
(2) 해결책 2
그렇다면 숫자가 있을 때만, 다시 말해 공백이 아닐 경우만
숫자를 표기하도록 하면 됩니다.
따라서,
=IF(VLOOKUP(VALUE(SUBSTITUTE(A4,"-","")),$F$4:$I$17951,2,0)<>"",VLOOKUP(VALUE(SUBSTITUTE(A4,"-","")),$F$4:$I$17951,2,0),"")
라고 검색된 값이 공백이 아니면 검색될 값을 표시하고, 아니면 ""로 표시하면 됩니다.
그리고, B4셀의 채우기 핸들을 더블 클릭하면 맨 아랫줄까지 수식이 복사되고, 값이 있는 것만 "000-00-000000" 표시형식이 적용돼서 표시됩니다.
2. 등록일 구하기
오른쪽에는 날짜인데 중간에 하이픈 또는 / 표시가 없으므로
왼쪽 등록일에 넎을 때 날짜 형식으로 표시되도록 하겠습니다.
그러러면 -을 넣은 다음 DateValue함수를 사용하면 됩니다.
따라서 수식은
=IF(VLOOKUP(VALUE(SUBSTITUTE(A14,"-","")),$F$4:$I$17951,3,0)<>"",DATEVALUE(TEXT(VLOOKUP(VALUE(SUBSTITUTE(A14,"-","")),$F$4:$I$17951,3,0),"####-##-##")),"")
이 됩니다.
VLookup으로 값을 구한 다음 Text함수를 이용해 중간에 하이픈을 넣고, DateValue함수를 이용해 날짜로 바꾸는 것입니다.
이때 Text 팜수의 포맷을 "YYYY-MM-DD"라고 하면 VLookup을 찾은 값이 날짜가 아니라 20231026로 숫이기 떼문에 #VALUE! 에러가 발생합니다.
3. 금액 구하기
금액을 아래 수식으로 구하면
=VLOOKUP(VALUE(SUBSTITUTE(A4,"-","")),$F$4:$I$17951,4,0)
회계 표시형식이기 때문에 -으로 표시되기는 하는데 특별한 문제는 없으므로 그냥 넘어갈 수 있습니다.
이제 D4셀의 채우기 핸들을 더블 클릭하면 맨 아랫줄까지 수식이 복사되고 원하는 값이 잘 구해졌습니다.
이상 VLookup 함수를 이용해 데이터 형식이 다른 경우 VLookup함수를 사용하는 것에 대해 알아봤습니다.
4.Let함수로 수식을 변수에 저장
Let함수를 이용하면 이름에 수식을 대입할 수 있으르로 아래와 같이 VLookup함수를 두번 썼을 때 수식을 간결하게 만들 수 있습니다.
=LET(a,VLOOKUP(VALUE(SUBSTITUTE(A14,"-","")),$F$4:$I$17951,2,0),IF(a<>"",a,""))
위 수식은
a에 VLOOKUP(VALUE(SUBSTITUTE(A14,"-","")),$F$4:$I$17951,2,0)라는 수식을 대입해서
이후는 a라는 변수를 사용해서 복잡한 수식을 간결하게 만들 수 있는 것입니다.
B14셀의 수식만 바꿨기 때문에 주변 수식과 달라서 느낌표 에러 표시가 표시되었습니다.
Let함수는 아래 버전에서만 사용 가능합니다,.
Microsoft 365용 Excel Mac용 Microsoft 365용 Excel 웹용 Excel Excel 2021 Mac용 Excel 2021
'EXCEL - VBA' 카테고리의 다른 글
| While 문 - VB와 VBA의 차이점 (0) | 2023.12.17 |
|---|---|
| VBA에서 SumProduct 사용하기 (2) | 2023.11.30 |
| 좌표를 이용해 엑셀에 도면 표시하기(3) (0) | 2023.11.16 |
| 좌표를 이용해 엑셀에 도면 위치 표시하기(2) (0) | 2023.11.15 |
| 좌표를 이용해 엑셀에 도면 위치 표시하기(1) (0) | 2023.11.13 |