엑셀 VLOOKUP과 SUMIF 함수를 사용해 조건에 맞는 값과 합 구하기

오늘 알려드릴 팁은 VLOOKUP 함수와 SUMIF 함수를 사용해 보고서나 엑셀 작업 시, 해당 조건에 맞는 값을 출력하고 그 합계를 내는 방법입니다. 저희가 보고서를 작성할 때 혹은 매출 등을 관리할 때, 사용하시는 전산에서 Raw 데이터를 엑셀 파일로 내려받습니다. 물론 좋은 전산을 사용하시면 자동으로 합계까지 구해주겠지만, 그렇지 못한 경우에는 Raw 데이터에서 수동으로 작업을 해주어야 합니다.

 

만약 정기적으로 매주 월요일에 지난주 매출합을 구하시거나 혹은 매월 초, 지난달의 매출합을 구하실 때, 특정 일에 특정 기간 동안의 매출 합을 구하시는 분이 있다면 보고서 한 칸에 해당 함수를 고정으로 박아놓고 Raw 데이터만 매달 바꿔주는 형태의 계산기를 만들어, 업무량을 줄이실 수도 있을 겁니다. 그럼 시작하겠습니다.

 

 

 

 

 

 

가격표와 판매 목록, 판매 수익 순으로 나열된 임의의 보고서 양식입니다.

먼저 맨 왼쪽에는 가격표, 가운데에는 판매 목록, 마지막엔 판매 수익이 있습니다. 저희가 해볼 작업은 가격표를 보고 판매 목록에서 판매된 품목의 가격을 입력한 후, 판매 목록의 품목들에 판매 수익을 품목 별로 구해보는 것입니다.

 

먼저 가격표에 나온 품목 별로 판매 목록에 가격을 기재해주어야 하는데 이때 VLOOKUP 함수를 사용하시면 쉽게 기재하실 수 있습니다.

 

 

또한 사용하시는 모든 함수는 함수 마법사를 사용하면 편리하고 이해하기 쉽게 작업하실 수 있습니다.

https://just1minute.tistory.com/2

엑셀 함수마법사로 함수작업 1분컷(오리엔테이션)

 

 

첫 번째로 VLOOKUP입니다.

 

VLOOKUP함수 사용법 입니다.

A의 값을 B영역에서 찾고 만약 A값을 찾았으면 n열번 째 값을 출력해주는 것이 VLOOKUP함수입니다. 그 열은 자기 자신을 기준으로 시작합니다. 즉 A값을 B영역에서 찾고 찾으면 첫 번째 열을 출력하는 것으로 설정하면 A, 즉 자기 자신을 출력합니다. 이를 사용해 수많은 값들 중 누락 값을 찾는 틀린 그림 찾기도 가능합니다.

다시 돌아와, 만약 n1번째 열을 출력하고 싶을 땐 2, n2번째 열을 출력하고 싶을 땐 3... 이런 식으로 자기 자신을 포함하여 출력을 해줍니다.

 

 

위의 표를 보면 이해가 안 가실 수도 있으니 실제 작업을 통해 구해보겠습니다. 먼저 함수 마법사로 VLOOKUP을 실행해주세요.

 

먼저 Lookup_value입니다. 찾고자 하는 값입니다. 저희가 보고자 하는 가격은 어떤 품목입니다. 즉 판매 목록에 나열되어 있는 A, B, A, C, F... 순서로 이어지는 품목 값을 가격표에서 찾고자 하는 것이죠. 즉 D3셀의 A가 저희가 찾고자 하는 값이 되는 겁니다. 참고로 D3셀로 지정을 해준 후, 밑으로 스크롤을 하면 자동으로 Lookup_value가 바뀌기 때문에 D3셀로 지정해주시면 됩니다.

 

 

 

 

 

 

Lookup_value에 D3 설정 후 Table_array를 설정합니다.

다음은 Table_array입니다. 이 곳에는 Lookup_value에서 지정한 값을 어느 범위에서 찾을 것인지에 대해 결정하는 부분입니다. 참고로 Table_array에는 꼭 Lookup_value값이 첫 번째 열에 기재되어 있어야 합니다. 저희는 가격표를 보고 찾을 것이기 때문에 제목을 빼고 A3부터 B9를 블록 지정해줍니다. 이때, 주의하실 사항이 있습니다.

 

바로 $을 이용한 고정입니다. 이 부분도 간략하게 설명하고 넘어가겠습니다. 저희가 함수를 사용하고 셀의 오른쪽 하단의 채우기를 이용합니다. 채우기는 일반적으로 셀의 이동을 똑같이 감지하고 따라가기 때문에 만약 가격표처럼 범위를 고정해야 할 때는 F4키를 눌러 고정을 해주어야 합니다.

 

F4를 한 번 누르면 행열 모두 고정, 두 번 누르면 열만 고정, 세 번 누르면 행만 고정입니다. 열만 고정한다면 스크롤을 내려 채우기를 할 때, 열은 바뀌지 않고 행만 바뀌고, 행만 고정한다면 열만 바뀌고, 모두 고정한다면 스크롤을 아무리 해도 열과 행은 바뀌지 않습니다.

 

다시 돌아와 Table_array에서 처음 범위를 지정하면 그냥 A3:B9로 나오죠. 그 상태에서 F4를 한 번 눌러보세요. 그럼 $A$3:$B$9으로 변한 것을 알 수 있습니다. 그럼 열과 행이 모두 고정된 상태에서 D3값을 찾을 준비가 됐습니다. 대개 VLOOKUP을 할 때는 행열 모두 고정합니다.

 

 

 

 

Col_index_num는 열을 찾는 질문입니다.

다음은 Col_index_num입니다. 이 부분은 Table_array에서 Lookup_value 값을 찾으면 Table_array의 몇 번째 열의 값을 가져올지에 대한 질문입니다. 저희는 가격표의 가격 부분, 즉 Table_array의 2번째 열의 값이 필요하기 때문에 2를 기입해줍니다.

 

 

 

 

 

마지막으로 Range_lookup은 정확한 값을 찾을 건인지 유사한 값을 찾을 것인지에 대한 질문입니다.

마지막으로 Range_lookup은 True or False를 이용해 정확한 값을 찾거나 아니면 그 유사한 값을 찾을 것인지 결정하는 항목입니다. 숫자로 -1, 1을 입력하면 유사 값, 0을 입력하면 정확한 값을 찾게 되는데, 저희는 정확한 값을 찾아야 하므로 0을 입력해줍니다. 이 부분도 대개 0을 입력하시면 됩니다.

 

 

 

 

 

정확하게 값을 찾았으니 스크롤을 내려밑에도 적용해줍니다.

그럼 정확하게 A의 가격 100을 찾은 것을 확인할 수 있습니다. 이제 E3셀의 오른쪽 하단에 스크롤을 밑으로 내려주어 D15의 품목까지 값을 모두 찾아주시면 됩니다.

 

 

 

 

이제 SUMIF를 사용해 판매 수익을 채우면 됩니다.

이제 판매 수익 부분에서 판매 목록에 기재된 품목에 따라 가격의 합을 구해주면 됩니다. 이때, SUMIF를 사용하면 빠르게 구할 수 있습니다.

 

 

 

 

함수 마법사에서 SUMIF함수를 켜주세요.

 

SUMIF는 VLOOKUP때와 다르게 범위를 먼저 지정합니다.

SUMIF 함수는 먼저 어떤 값을 찾을지 정했던 VLOOKUP과는 다르게 찾을 범위를 먼저 지정해줍니다. 저희는 판매 목록 부분에서 판매 수익의 품목 값을 찾을 것이니 판매 목록 부분을 블록 지정해줍니다. VLOOKUP때와 마찬가지로 F4를 한 번 눌러주어 범위를 행열 모두 고정해줍니다.

 

 

 

 

 

다음은 어떤 값을 찾을 지 결정합니다

Criteria에서는 어떤 값을 찾을지 결정하는 부분입니다. 저희는 판매 수익의 품목 즉 G3값을 찾을 것이기 때문에 G3셀을 입력해줍니다.

 

 

 

 

 

Sum_range는 어떤 셀의 합을 구할 지 범위 지정해주는 부분입니다.

마지막으로 Sum_range는 Range에서 어떤 부분의 합을 구할지 결정하는 부분입니다. VLOOKUP은 열 번호를 입력해주면 됐지만, SUMIF는 직접 열을 범위 지정해주어야 합니다. 저희는 가격 부분의 합을 구할 것이기 때문에 가격 부분만 열 범위지정을 해주고 마찬가지로 F4를 눌러 행열 고정을 해줍니다. 그리고 확인을 누른 뒤 마찬가지로 H3의 우측 하단의 스크롤을 H9까지 늘려주어 자동 채우기를 해줍니다.

 

 

 

보고서가 완성되었습니다.

이러면 가격표에 따라 판매 목록의 가격을 각각 채우고, 그 판매 목록에서 판매 수익의 합을 자동으로 구해주는 보고서가 완성되었습니다.

 

 

 

 

VLOOKUP과 SUMIF는 정말 많이 쓰이는 함수들입니다. 익혀두시고 상황에 맞게 사용하시거나 혹은 정기적으로 하시는 일이 있다면 한 편이나 혹은 새로운 Sheet에 고정으로 박아놓고 매 번 할 필요 없이, 자동으로 출력되도록 설정해두셔도 됩니다. 오늘의 팁은 여기까지입니다. 감사합니다!