VLookup 오류 - Escape 문자

1. 문제

vlookup 오류.xlsx

0.08MB

 

 

일위대가시트에서 해당되는 공종의 총액과 노무비를 찾는데,

 

분명히 일위대가시트의 F41셀과 H41셀에 금액이 있는데도 #N/A 에러가 납니다.

 

 

2. 해결

 

가. 와일드 카드 *와 ?의 의미

*는 0개 이상, 다시 말해 없어도 되고 여러 개의 문자가 올 수 있는 것이고,

?는 1개의 문자를 가르킵니다.

 

아래와 같은 데이터가 있을 때

 

"번"이 들어가는 것은 모두 합계를 내고 싶은 경우는

=SUMIFS(P4:P10,O4:O10,"*번")

라고 하면 번 앞에 글자가 하나이거나 두개이거나 모두 합산을 하므로 95+66+95=256이 되는 것입니다.

 

그러나 "번"앞에 글자가 하나만 있는 것만 더하고 싶다고 하면 *이 아니라 ?를 사용해야 합니다.

=SUMIFS($P$4:$P$10,$O$4:$O$10,"?번")

 

"번"앞에 글자가 2개 들어가는 것만 더하고 싶으면 ?를 두번 써서 "??번"이라고 표시해야 합니다.

=SUMIFS($P$4:$P$10,$O$4:$O$10,"??번")

 

나. *나 ?가 포함된 글자의 합계 구하기 - 실패

아래와 같이 *가 들어간 경우의 숫자를 더하기 위해

예를 들어 =SUMIFS($P$4:$P$12,$O$4:$O$12,"*")라고 하면

*가 들어가 있는12행의 78만 더하는 것이 아니라, 모든 경우의 수를 더하기 때문에 750이 구해집니다.

 

또한 ?가 들어간 것의 합계를 구하려고

=SUMIFS($P$4:$P$12,$O$4:$O$12,"?")

라고 입력해도 88이 구해지는 것이 아니라 한 글자인 것의 합계를 구하기때문에 한글자짜리가 없어서 0이 구해집니다.

 

다. *나 ?가 포함된 글자의 합계 구하기 - 성공 : Escape 문자 사용

그렇다면 *나 ?를 찾을 때는 어떻게 해야 할까요?

이 때 필요한 것이 Escape 문자인 ~입니다.

~자체를 찾을 때도 ~가 Escape문자이기 때문에 ~를 앞에 붙여야 합니다.

 

*가 들어간 것의 합계만 구하려면 "*~*"를 사용해서

=SUMIFS($P$4:$P$12,$O$4:$O$12,"*~*")

라고 입력해야 합니다.

 

"*~*"에서 앞의 *는 뒤 *앞에 문자가 0개 이상 있어야 한다는 의미이고,

~다음의 *는 *문자 자체를 의미합니다.

따라서, *가 포함된 "일반*"의 값 78이 구해진 것입니다.

 

마찬가지로 ?가 포함된 숫자의 합을 구하려면

=SUMIFS($P$4:$P$12,$O$4:$O$12,"*~?")

라고 해야 합니다.

 

나. 수식 수정

VLookup함수를 이용해 ~가 포함된 문자열의 경우도

~앞에 ~가 들어가야 합니다.

 

다시 말해 Substitute 함수를 이용해 ~를 ~~로 바꾼 다음 일위대가시트에서 해당 공종을 찾으면 됩니다.

=VLOOKUP(SUBSTITUTE($A11,"~","~~"),일위대가!$A$5:$H$86,6,FALSE)

 

이제 수식을 E열과 F열에 복사해서 다른 셀에도 붙여넣습니다.

 

배경색을 유지하려면 붙어넣기로 하면 안되고,

fx가 붙어있는 '수식으로 붙여넣기'를 해야 합니다.

 

 

vlookup 오류(수정).xlsx

0.08MB