HAKINA SUN

엑셀 REPLACE 함수, SUBSTITUTE 함수는 유사한 기능을 가지고 있습니다. 셀 안에 들어있는 데이터의 일부 문자 혹은 숫자를 다른 값으로 대체할 수 있는 기능을 가지고 있습니다. 두 함수의 차이점이라고 한다면, 데이터에 무엇이 써있든지 상관없이 몇번째 자리에 있는 값을 다른 값으로 대체할건지, 아니면, 내가 찾아서 바꾸고자 하는 문자를 입력해서 대체할 것인지 입니다. 전자가 REPLACE 함수, 후자가 SUBSTITUTE 함수라고 할 수 있습니다.

우선, REPLACE 함수를 먼저 알아보도록 합시다.

1. REPLACE 함수

1) REPLACE 함수란 무엇인가!?

REPLACE 함수는 셀안에 들어있는 데이터중 특정 위치에 있는 문자나 숫자를 다른 문자나 숫자로 대체할 수 있도록 하는 함수 입니다.

2) REPLACE 함수 사용방법!!

=REPLACE(old text, start_num, num_chars, new_text)

=REPLACE(값을 대체하고자하는 셀, 대체하고자 하는 문자 혹은 숫자의 시작 자리수, 시작위치에서 몇번째까지 데이터를 바꿀것인지 자리수, 대체하고자 하는 문구)

글만 봐서는 도통 감이 오질 않네요.. 실제로 한번 적용해보도록 하겠습니다.

"오늘은 2019년 7월 2일 화요일 입니다." 문자중 '2019'를 '십구'로 대체해보도록 합시다.

=REPLACE(B4,5,4,"십구")

첫번째 인수는 대체할 셀 'B4', 두번째 인수는 '2019'를 다른 문자로 대체할 것이므로 '2'가 시작되는 자리수인 '5'를 기입, 세번째 인수는 '2019' 네자리 모두를 다른 문자로 대체할 것이므로 '4', 네번째 인수는 대체할 문자인 '"십구"'를 기입 합니다. 그러면 결과가 "오늘은 십구년 7월 2일 화요일 입니다."로 나오게 됩니다.

2. SUBSTITUTE 함수

1) SUBSTITUTE 함수란 무엇인가!?

SUBSTITUTE 함수는 앞서 설명한 REPLACE 함수와 기능은 비슷하지만, 접근하는 방법이 다릅니다. SUBSTITUTE 함수는 바꾸고자 하는 문자를 직접 찾아내어 다른 문자나 숫자로 대체할 수 있습니다.

2) SUBSTITUTE 함수 사용법!!

=SUBSTITUTE(text, old_text, new_text, [instance_num])

=SUBSTITUTE(바꾸고자하는 문자 혹은 숫자가 있는 셀, 바꾸고자하는 문자 혹은 숫자, 대체할 새로운 문자 혹은 숫자, [몇번째 인지 숫자]

아래와 같이 바로 적용해보도록 합시다.

"오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다." 에서 '2019'를 '2020'으로 대체해도록 합시다.

=SUBSTITUTE(B4,"2019","2020")

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입합니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2020년 7월 2일 화요일 입니다. 내일은 2020년 7월 3일 수요일 입니다."

→ 네번째 인수를 기입하지않으면 '2019' 모두를 '2020'으로 변경 합니다.

=SUBSTITUTE(B4,"2019","2020",1)

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입, 네번째 인수에는 '1'을 기입해보겠습니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2020년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

→ 두개의 '2019'중 첫번째 '2019'만 '2020'으로 변경한것을 확인할 수 있습니다.

=SUBSTITUTE(B4,"2019","2020",2)

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입, 네번째 인수에는 '2'을 기입해보겠습니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2020년 7월 3일 수요일 입니다."

→ 두개의 '2019'중 두번째 '2019'만 '2020'으로 변경한것을 확인할 수 있습니다.

=SUBSTITUTE(B4,"2019","2020",3)

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입, 네번째 인수에는 '3'을 기입해보겠습니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

→ 세번째 '2019'는 없으므로 기존과 동일한 결과를 나타냅니다.

REPLACE 함수와 SUBSTITUTE 함수에 대해서 알아보았습니다. 기능은 유사하지만, 사용하는 측면에서 접근방법이 다른 두 함수입니다. 상황에 맞게 적절한 함수를 사용한다면 큰도움이 될 것입니다.

LEFT, RIGHT, MID 함수는 셀안에 포함된 데이터의 일부를 추출하여 표현할 수 있는 함수 입니다. 예를 들어, 한 셀 안에 "오징어"라는 문구가 적혀 있으면, "오", "징", "어", "오징", "징어" 와 같이 "오징어"의 글자중 일부를 추출하여 표현할 수 이는 것이죠.

1. LEFT, RIGHT, MID 함수란!?

1) LEFT 함수 : 셀안에 기입된 데이터의 왼쪽에서부터 지정한 숫자의 갯수만큼 추출하여 표시.

2) RIGHT 함수 : 셀안에 기입된 데이터의 오른쪽에서부터 지정한 숫자의 갯수만큼 추출하여 표시.

3) MID : 셀안에 기입된 데이터의 지정한 지점에서부터 지정한 숫자의 갯수만큼 추출하여 표시.

2. LEFT, RIGHT, MID 함수의 사용 방법!!

1) LEFT 함수 : =LEFT(텍스트 포함된 셀, 좌측에서부터 몇번째까지 값을 추출할지에 대한 숫자)

2) RIGHT 함수 : =RIGHT(텍스트 포함된 셀, 우측에서부터 몇번째까지 값을 추출할지에 대한 숫자)

3) MID 함수 : =MID(텍스트 포함된 셀, 시작지점, 시작지점으로부터 몇번째까지 값을 추출할지에 대한 숫자)

LEFT, RIGHT, MID 함수에 포함되는 인수에 대해서 알아보았습니다. 다음은 구체적인 사용 방법에 대해서 알아보겠습니다.

코끼리, 거북이, 호랑이, 나무늘보, 강아지의 주민등록번호가 기입된 표가 있습니다. 모두가 알다시피 주민등록번호에는 많은 개인정보가 포함되어 있죠. 왼쪽에서부터 여섯째자리는 생년월일, 뒷부분 7자리는 성별 1자리, 지역코드 및 출생신고 5자리, 검증번호 1자리로 구성되어 있습니다. 여기에서는 주민등록번호로부터 간단하게 생년월일, 성별, 검증번호를 추출해 보도록 하겠습니다. (검증번호는 가장 우측의 번호 한자리로 자세한 설명은 넘어가도록 하겠습니다.)

코끼리 생년월일 : =LEFT(C3,6)

첫번째 인수는 코끼리 주민등록번호 'C3'셀, 왼쪽 6번째 자리가 생년월일 이므로 두번째 인수는 '6'을 기입합니다.

코끼리 성별 : =MID(C3,8,1)

첫번째 인수는 코끼리 주민등록번호 'C3'셀, 두번째 인수는 8번째 자리가 성별을 의미하는 수이므로 '8', 세번째 인수는 8번째 자리의 숫자 하나만 필요로 하므로 '1'을 기입합니다.

코끼리 검증번호 : RIGHT(C3,1)

첫번째 인수는 코끼리 주민등록번호 'C3'셀, 두번째 인수는 오른쪽에서 첫번째 자리가 검증번호 이므로 '1'을 기입합니다.

코끼리의 생년월일, 성별, 검증번호를 기입한후 아래로 드래그 하면 아래와 같은 결과가 나옵니다.

원하는 값들이 모두 표현되었습니다. 그러나, 성별이 '남자', '여자'가 아닌 '1'과 '2'로 표현되어 있어 이를 '남자', '여자'로 표현해보고자 합니다. 조건 IF 함수를 이용해 '1'이면 '남자', '2'이면 '여자'로 나타내봅시다.

코끼리의 성별 : =IF(MID(C3,8,1)="1","남자","여자")

'MID(C3,8,1)' 값이 '1'이면 '남자' 아니면 '여자' 입니다. 단 여기서 주의해야할 점은 MID(C3,8,1)을 통해서 추출한 값은 숫자가 아닌 텍스트 이므로 '1'을 큰따옴표 안에 넣고 수식을 완성해야 합니다.

나열되어 있는 데이터에서 값을 더해야하는 경우는 아주 빈번하게 발생합니다. 예를들어, 한달간 총지출한 금액의 합계를 구하거나 또는 구매해야할 자재들의 값의 합계를 구해 내가 가지고 있는 예산에 부합하는지를 검증해볼 수 있겠죠. 엑셀에서 합계를 구하는 방법은 수식에 '+' 또는 '-' 기호를 이용할 수 있습니다만, 이 기호를 이용하게 되면 셀을 하나하나 지정을 해줘야하는 큰 단점이 있습니다. 그러나, 'SUM'이라는 함수를 이용하게 되면, 값을 드래그하여 손쉽게 합산할 수 있습니다. 또한, 'SUMIF' 함수를 이용해 조건에 맞는 값을 합산할 수도 있죠.

1. SUM, SUMIF, SUMIFS 함수란!?

1) SUM 함수 : 숫자로 구성된 데이터의 값을 합산해주는 함수.

2) SUMIF 함수 : 조건에 맞는 값을 합산해주는 함수.

3) SUMIFS 함수 : 다중 조건에 맞는 값을 합산해주는 함수.

2. SUM, SUMIF, SUMIFS 함수 사용법!!

1) SUM 함수 : =SUM(합산범위)

2) SUMIF 함수 : =SUMIF(조건범위, 조건, 합산범위)

3) SUMIFS 함수 : =SUMIFS(합산범위, 조건범위1, 조건1, 조건범위2, 조건2, 조건범위3, 조건3, .....)

아래 그림의 'A창고 재고'의 표에서 '재고 총수량', '셔츠 총수량', '파란색 셔츠의 수량'에 대한 합산을 한번 해보도록 하겠습니다.

재고 총 수량 : =SUM(F5:F12)

합산할 데이터인 셀 F5 ~ 셀 F12까지 드래그하여 괄호안을 채워줍니다.

셔츠 수량 : =SUMIF(D5:D12,"셔츠",F5:F12)

첫번째 인수에는 조건에 대한 범위인 셀 D5 ~ 셀 D12까지 드래그하고, 두번째 인수에는 조건인 "셔츠"를 기입합니다. 그리고 합산할 데이터인 셀 F5 ~ 셀 F12까지 드래그하여 괄호안을 채워줍니다.

파란색 셔츠 수량 : =SUMIFS(F5:F12,D5:D12,"셔츠",E5:E12,"파랑")

SUMIFS 함수는 SUMIF 함수와 인수 구성이 다릅니다. SUMIFS 함수의 첫번째 인수에는 합산할 데이터 셀 F5 ~ 셀 F12까지 드래그하여 기입을 하고, 두번째 인수에는 종류에 해당하는 항목 셀 D5 ~ 셀 D12까지 드래그하여 기입하고, 세번째 인수에는 두번째 인수 조건의 범위에 해당하는 조건인 "셔츠"를 기입합니다. 네번째 인수에는 색상에 해당하는 항목 셀 E5 ~ 셀 E12까지 드래그하여 기입하고, 다섯번째 인수에는 네번째 인수 조건의 범위에 해당하는 조건인 "파랑"을 기입합니다.

위와 같이 기입을 하면 아래와 같은 결과가 나오게 됩니다. 

재고 총 수량 : 497

셔츠 수량 : 258

파란색 셔츠 수량 : 133

SUM, SUMIF, SUMIFS 함수에 대해서 알아보았습니다. 엑셀에서 합계를 구하고자 할때 SUM, SUMIF, SUMIFS 함수를 이용하여 데이터 합산을 효과적으로 처리해봅시다.

엑셀에서 나열되어 있는 데이터의 개수를 파악해야될 때가 있습니다. 그럴때 사용하는 엑셀 함수가 바로 'COUNT' 입니다. 'COUNT'라는 의미가 '~을 세다' 인데, 말그대로 데이터 수를 세어줍니다. 사실 수식을 기입하지 않고, COUNT 함수 보다 더 간단하게 데이터 수를 파악하는 방법도 있습니다. 셀에 기입된 데이터들을 모두 드래그하면 우측 하단에 개수, 합계 등의 정보를 한번에 파악할 수 있습니다.

위 그림의 우측 하단을 보시면 평균, 개수, 합계가 간단하게 표현이 되어 있는것을 볼 수 있습니다. 함수를 굳이 사용하지 않아야되는 경우에는 데이터들을 드래그하여 엑셀의 우측하단에서 쉽게 개수를 파악하는 것이 업무 효율이 더 좋습니다. 그러나 데이터개수를 COUNT 함수를 통해서 처리해야하는 경우에는 위와같은 임시방편적인 방법은 효율이 떨어질 수 있습니다. 때에 따라서 함수를 사용할지, 아니면 드래그를 해서 간단하게 정보를 파악할지를 선택해서 사용하시면 되겠습니다.

1. COUNT 함수의 종류?

COUNT 함수는 숫자 데이터만 셀 수 있습니다. 그러나 숫자 외의 문자값이 들어있는 셀도 세고 싶을때는 COUNT 함수를 쓸수 없습니다. 또, 빈칸인 셀을 세고 싶을때도 그냥 COUNT 함수를 사용해서는 개수를 세지 못합니다. 그래서 COUNT 함수는 다양한 형태가 존재 하는 것이죠. 아래에서 함수의 종류를 살펴 보겠습니다.

1) COUNT 함수 : 숫자가 들어있는 셀 세기

2) COUNTA 함수 : 숫자 뿐만 아니라 어떠한 데이터라도 들어있는 셀 세기

3) COUNTBLANK 함수 : 비어있는 셀 세기

4) COUNTIF : 조건에 맞는 셀 세기

2. COUNT 함수 사용법!!

이렇게 다양한 형태의 COUNT 함수가 존재 합니다. 4개의 COUNT 함수중 COUNTIF 함수를 제외하고 모두 1개의 인수를 가집니다. COUNTIF 함수의 인수는 조건이 추가가되어 2개 입니다.

1) COUNT 함수 : =COUNT(범위)

2) COUNTA 함수 : =COUNTA(범위)

3) COUNTBLANK 함수 : =COUNTBLANK(범위)

4) COUNTIF 함수 : = COUNTIF(범위, 조건)

아래의 연한 노란색부분에 기입된 값들을 COUNT 함수, COUNTA 함수, COUNTBLANK 함수, COUNTIF 함수를 적용시켜 보겠습니다.

1) "=COUNT(C2:C10)"

2) "=COUNTA(C2:C10)"

3) "=COUNTBLANK(C2:C10)"

4) "=COUNTIF(C2:C10, "바나나")"

1), 2), 3), 4)에 차례대로 위와 같이 기입해봅니다.

1) COUNT 함수는 숫자 '1', '2', '3' 3개의 데이터를 세어 '3'이라는 값을 나타냅니다.

2) COUNTA 함수는 숫자뿐만 아니라 문자도 세어주므로 '사과', '감자', '바나나', '1', '2', '3', '바나나' 이렇게 총 7개의 데이터를 세어 '7'이라는 값을 나타냅니다.

3) COUNTBLANK 함수는 빈칸 2개를 세어 '2' 값을 나타냅니다.

4) COUNTIF 함수는 조건이 바나나 이므로 바나나의 개수를 세어 '2'라는 값을 나타냅니다.

COUNT 함수에 대해서 알아보았는데, 실무에서도 자주 사용되는 함수이므로 위 네가지 'COUNT', 'COUNTA', 'COUNTBLANK', 'COUNTIF'를 숙지하고 때에 맞게 사용한다면 효율적으로 업무를 수행할 수 있을 것입니다.

엑셀(EXCEL) IF 함수는 조건함수로 조건에 따른 값을 지정해야할 때 사용되는 함수 입니다. 조건대로 내가 원하는 값을 삽입할 수 있죠. 일상생활에서도 우리가 계획을 세우거나 어떤 판단을 해야할때, 주어진 상황에 따라 행동을 달리하듯이 말입니다. A상황에서는 A대로 일을 처리하고, B상황에서는 B대로 일을 처리해야하죠. 엑셀에서도 이와 동일하게 주어진 상황, 조건에 따라 어떤 데이터를 처리해야할 때 IF 함수를 사용한다면 아주 효과적입니다.

1. IF 함수란 무엇인가!?

위에서도 언급했듯이 IF 함수란 조건에 따라 값을 해당 셀에 표현할 수 있도록 도와주는 함수 입니다. 예를들어 값이 '5'보다 크거나 같다면 '값이 크다', '5'보다 크지 않으면 '값이 작다'를 표현해봅시다. 아래의 그림에서 숫자 '7'은 '5'보다 크므로 '값이 크다', 숫자 '4'는 5보다 크지 않으므로 '값이 작다'로 표현할 수 있죠. 처리해야할 데이터량이 많지 않으므로 함수를 사용하지 않고도 간단하게 표현할 수 있습니다.

그러나 처리해야할 데이터량이 많아진다면 일일이 조건을 비교해가며 값을 집어넣는 것은 피곤한 일이 될 것입니다. 더군다나 하나의 조건이 아닌 여러개의 조건이 있다면, 몸이 힘들뿐만 아니라 머리도 아파올지도 모릅니다. 그렇다면 이를 효과적으로 데이터를 처리하기 위해서는 어떻게 해야할까요?

2. IF문 사용법!!

우선 IF문을 사용하기 위해, 사용되는 인수부터 살펴보도록 하겠습니다. IF문에는 3개의 인수를 넣어야 합니다. '조건', '참일때의 값', '거짓일때의 값' 이렇게 3개의 인수로 구성되어 있죠.

=IF(logical_test, [value_if_true], [value_if_false])

1) logical_test : 조건

2) value_if_true : 해당 조건이 참일때의 값

3) value_if_false : 해당 조건이 거짓일때의 값

IF 함수를 적용해서 위 그림에서 7과 4의 값의 크기에 대해 표현해보도록 하겠습니다. (해당 값이 '5'보다 크거나 같으면 '값이 크다', '5'보다 작으면 '값이 작다')

"=IF("를 먼저 기입해주세요. 그런다음 조건을 넣어야 합니다. 첫번째 인수는 숫자 '7'이 '5'보다 크거나 같다를 수식으로 표현하면 "B4>=5"와 같이 조건을 표현할 수 있습니다. 두번째 인수는 참일때 나타내는 값이므로 " "값이 크다" "로 기입합니다. 여기서 '값이 크다'는 숫자가 아닌 문자이므로, 문자를 나타낼때는 큰따옴표를 붙여주어야 합니다. 세번째 인수는 거짓일때 나타내는 값이므로 " "값이 작다" "를 기입하면 됩니다. 그러면 "=IF(B4>=5,"값이 크다","값이 작다")"로 함수값을 완성할 수 있습니다.

완성한 IF함수를 아래로 드래그하여 확장하면 숫자'7'은 '값이 크다', 숫자'4'는 '값이 작다'가 표현됩니다. 간단하게 사용법을 알아보았는데요, 아래에서 조건에서 나타낼 수 있는 몇가지 기호에 대해서 알아보도록 합시다.

3. 기호

조건을 표현할 수 있는 '크다', '작다', '다르다', '같다', '크거나 같다', '작거나 같다'를 표현하는 방법에 대해서 살펴보겠습니다.

1) 크다 : >

2) 크거나 같다 : >=

3) 작다 : <

4) 작거나 같다 : <=

5) 다르다 : <>

참과 거짓일때 나타내는 값에서는 숫자뿐만 아니라 글자, 수식으로도 표현이 가능합니다. 글자를 표현할때는 큰따옴표로 문구를 기입할 수 있습니다.

=IF(조건,"참일때","거짓일때")  

조건에 따라 '참일때' 또는 '거짓일때' 라는 문구를 셀에다가 표현하게 됩니다.

또한 참과 거짓일때 값에 수식 또는 함수를 기입 할 수 있습니다. 그래서 IF문에 또 IF문을 확장시켜 다중 IF문을 구사할 수 있는 것이죠.

=IF(조건, 참인 값, IF(조건, 참인 값, IF(조건, 참인 값, IF( ........

이렇게 조건에 참이면 참인 값을 표현하고 거짓이면 또다른 IF문으로 계속 확장시켜 나갈 수 있습니다.

일상생활에서도 상황에 따라 행동과 생각을 달리하는 것처럼, 엑셀에서도 다양한 조건에 따라 값을 달리 처리 할 수 있습니다. 그만큼 자주 사용되는 함수이니만큼, IF 함수를 잘 숙지해서 효과적으로 데이터를 처리해보세요!

엑셀 VLOOKUP 함수를 알고 있다면, HLOOKUP 함수도 들어보셨을 겁니다. VLOOKUP 함수와 마찬가지로 HLOOKUP 함수는 나열되어 있는 데이터에서 찾고자 하는 값을 나타낼 수 있습니다. 다만, VLOOKUP 함수의 경우 항목이 가로로 구성되어 세로로 데이터가 기입된 표에서 사용한다면, HLOOKUP 함수의 경우에는 항목이 세로로 구성되어 데이터가 가로로 기입된 표에서 활용할 수 있다는 차이점이 있죠. 참고로 VLOOKUP 함수의 V는 VERTICAL(수직), HLOOKUP 함수의 H는 HORIZONTAL(수평)을 의미합니다. V와 H 알파벳만으로 두 함수를 쉽게 구분할 수 있습니다. 그러면, HLOOKUP을 어떻게 사용할 수 있을지 한번 알아보도록 합시다.

1. 'HLOOKUP 함수' 란 무엇인가!?

위에서도 언급을 했지만, 간단히 말해 가로로 구성된 표의 수많은 데이터 집단 속에서 내가 원하는 데이터를 찾아내어주는 함수 입니다. 말로만으로는 와닿지 않기때문에 아래 그림과 함께 보겠습니다.

위 그림의 [표1]에 데이터가 수평으로 나열되어 있는 것을 볼 수가 있습니다. 여기서 [표2]에서 보이는 것과 같이 'BBB-222'와 'EEE-555' 품번에 대한 가격을 알고 싶습니다. 해당 데이터는 수량이 적기때문에 [표2]에서 찾고자 하는 품번의 가격을 금방 찾을 수 있습니다. 하지만, 방대한 데이터 속에서 찾아야 한다면 시간이 아주 많이 걸릴지도 모릅니다. 그래서 [표1]과 같이 데이터가 수평으로 나열되어 있는 표에서는 HLOOKUP 함수를 통해 찾고자 하는 값들을 쉽고 빠르게 찾아낼 수가 있습니다. 그러면 HLOOKUP 함수를 통해 [표2] 품번에 해당하는 가격을 찾아보도록 하겠습니다.

2. 'HLOOKUP 함수' 사용법!

HLOOKUP함수의 인수는 4개로 구성되어 있습니다.

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

1) lookup_value : 찾고자 하는 값의 항목

2) table_array : 찾고자 하는 데이터가 포함된 표

3) row_index_num : 나타내고자 하는 데이터의 행

4) [range_lookup] : 찾고자 하는 값의 항목을 유사값으로 찾을지, 정확한 값으로 찾을지를 구분지음 (true:유사값/false:정확한값)

위의 인수를 한번 집어넣어 보도록 하겠습니다.

품번 'BBB-222'에 해당하는 가격을 찾아보겠습니다.

"=HLOOKUP( "을 먼저 기입하고, 첫번째 인수에 품번 'BBB-222'에 해당하는 "C8"을 기입합니다. 두번째 인수에는 어떤 표 안에서 그 값을 찾을 건지를 의미하므로 [표1]에 해당하는 "$C$4:$H$5"를 기입합니다. 여기서 $를 의미하는 절대참조로 인수를 넣어준 이유는 품번 'EEE-555'에 해당하는 값을 품번 'BBB-222'로 나타낸 값으로 확장시켜서 나타내어주기 위함입니다. 절대참조로 하지 않고 값을 확장시키면 [표1]에 해당하는 범위를 벗어나게 되어 값을 찾아내지 못할 수 있습니다. 세번째 인수에는 품번을 나타내는 1행과 가격을 나타내는 2행중에서 찾고자하는 값이 가격이므로 2행을 나타내는 숫자 "2"를 기입합니다. 네번째 인수에서는 정확한 값을 찾을 것이므로 "FALSE"값을 기입합니다. 그리고 마지막으로 괄호를 닫아주면 "=HLOOKUP(C8, $C$4:$H$5, 2, FALSE)"가 완성됩니다. 그러면 품번 'BBB-222'에 해당하는 가격 '4,200'이 표시되어 질 것입니다. 품번 'EEE-555'는 품번 'BBB-222'에서 만든 함수를 확장시켜 기입하면, 찾고자하는 [표2]의 값을 쉽게 구할 수 있습니다.

품번 'BBB-222'의 가격 '4,200', 품번 'EEE-555'의 가격 '40,000'을 HLOOKUP 함수를 통해 찾아보았습니다. 위의 예시는 데이터가 많지 않아 HLOOKUP 함수를 굳이 사용하지 않아도 찾고자 하는 값들을 찾아낼 수 있습니다. 하지만 데이터량이 셀수 없을 정도로 많은 경우에는 HLOOKUP 함수 또는 VLOOKUP 함수의 사용이 손쉽게 데이터를 처리하는데 아주 큰 도움을 줄 것입니다.

엑셀 VLOOKUP 함수란 표로 구성된 데이터에서 내가 찾고자 하는 값을 손쉽게 찾을 수 있게 해주는 함수 입니다. 단, 표는 세로로 구성이 되어 있어야 합니다. 아래 그림의 좌측 표와 같이 말이죠.

(참고로 가로로 구성된 표는 HLOOKUP 함수를 이용합니다. VLOOKUP의 V는 VERTICAL, HLOOKUP의 H는 HORIZONTAL을 의미하니 참고하세요!)

위 그림과 같이 구성된 표에서 A자재, D자재에 대한 금액을 찾고 싶을때 VLOOKUP 함수를 통해 찾고자 하는 금액을 손쉽게 찾을 수 있습니다. 물론, 위 데이터량이 많지 않아 한눈에 A자재는 56,000, D자재는 100,000 이라는 값을 금방 찾을 수 있지만, 데이터를 눈으로 헤아릴 수 없을 정도라면 일일이 찾는건 시간이 꽤나 걸릴 것입니다.

먼저, VLOOKUP 함수에 필요한 인수를 살펴보겠습니다.

=VLOOKUP(①lookup_value, ②table_array, ③col_index_num, ④[range_lookup])

① lookup_value : 찾고자 하는 항목

② table_array : 찾고자 하는 데이터가 들어있는 표

③ col_index_num : 찾고자 하는 데이터가 들어있는 표에서 몇번째 열인지

④ [range_lookup] : 유사한 이름이 포함된 데이터를 찾을지(TRUE), 완전하게 동일한 데이터를 찾을지(FALSE)

인수 값만 봐서는 아무래도 무슨 말인지 알기 어려운거 같네요. 아래 그림으로 살펴보겠습니다.

=VLOOKUP(E6,$B$3:$C$7,2,FALSE)

여기서 E6은 찾고자하는 항목인 'A자재' /

$B$3:$C$7은 찾고자 하는 데이터가 들어있는 표 /

2는 찾고자 하는 데이터가 들어있는 표에서 몇번째 열인지를 나타내는 숫자 (1은 항목열, 2는 금액열) /

FALSE는 'A자재'와 동일한 글자의 데이터를 찾겠다는 의미 입니다.

※ 여기서 유의해야할 점은 두번째 인수를 절대값으로 표현해야한다는 것입니다. 절대값으로 표현하지 않으면 해당 수식을 D자재 영역으로 드래그시 아래와 같이 표 영역이 밀려나게 됩니다.

2번째 인수를 절대참조로 지정하지 않았을 경우

따라서 반드시 =VLOOKUP(E6,$B$3:$C$7,2,FALSE)와 같이 두번째 인수를 절대참조로 넣어주어야 다른 셀에 데이터 확장시 편리합니다.

VLOOKUP 함수는 다른 함수들에 비해 인수가 많아 어렵게 보이지만, 직접 값을 하나하나 기입해보면 간단하고 쉽게 느껴질 것입니다. 수많은 데이터 중에서 내가 원하는 데이터 값을 뽑아내고 싶을때 VLOOKUP 함수를 한번 사용해보세요.