[엑셀 실무] 브이 룩업(VLOOKUP), 썸이 프스(SUMIFS) 함수를 이용한
퇴근 시간 앞당기기.
안녕하세요.
스베니르 입니다.
저번 블로그에서 예고한 대로 오늘은 실무에서 유용하게 쓰이지만, 많은 분들이 헷갈려하거나 모르시는
함수 VLOOKUP, SUMIFS에 대해 배워보겠습니다.
이 두 함수만 배워 두시면 웬만한 서류 처리업무에서 해방될 수 있다고 자신합니다.
상황 두가지를 산정해 두고 어떨 때 쓰이고 쉽게 활용하는 법을 공부해 보겠습니다.
상황 1) 광대한 양의 데이터에, 그에 맞는 값을 넣으려 할 때.
실무에 자주 나오는 상황으로 보통 아이템에 맞는 단가를 입력할 때, 자주 이용됩니다.
타입이 다른 아이템들이 순서 없이 뒤죽박죽 섞여 있으면, 브이룩업 (VLOOKUP) 함수를 모른다면
한 칸 한 칸씩 값을 넣어가야겠죠? 해결해 드리겠습니다.
순서대로 해보겠습니다.
1.구하고자 하는 값이 들어갈 칸을 클릭한다 (이 경우는 단가)
2. 수식 카테고리 클릭
3. 함수 삽입 클릭
4. 함수 검색 창에서 Vlookup 입력 후 검색
5. 확인 버튼 누름
1. Lookup_value는 결과값으로 우리는 “충전식”의 단가를 원하기에 D5를 눌러 결과값으로 잡습니다.
2. Table_array는 구하고자 하는 값을 가지고 올 영역을 선택하라는 뜻으로 사진의 2번 네모처럼 구하고자 하는 값을
범위를 선택해 주세요. (저희는 모두 끌어서 쓸 테니 아이템과 단가가 있는 모든 영역을 선택했습니다.)
3. Col_index_num는 2번에서 선택된 영역 중 우리가 원하는 부분을 정확히 어떤 부분을 원하는지 선택하는 부분입니다. 저희는 단가를 원하기에 선택한 영역에서 3번째에 있는 단가 즉 3을 입력해주시면 됩니다.
4. Range_lookup은 1과 0을쓰실수 있는데 1은 근사값을 0은 정확한 값을 나타내 줍니다.
저희는 정확한 값을 원하기에 0을 입력하고 확인 버튼을 클릭합니다.
짠 사진과 같이 충전식 무선 이어폰의 값이 단가 칸에 입력되었습니다.
우리는 이제 이 값을 끌어서 밑에 다른 아이템에도 적용시킬 텐데요.
* 여기서 잊지 말아야 할 것은 함숫값을 고정시키는 것입니다.
엑셀은 똑똑해서 끌어 쓰기를 하게 되면 밑에 값까지 같이 잡는 기능이 자동 탑재돼있습니다.
이렇게 되면 사진과 같이 선택범위가 밑으로 잡히는 불상사가 일어나게 됩니다.
그래서 꼭 함수 식을 구할 때는 고정을 습관화해주세요.
그러면 이렇게 사이사이에 달러 표시가 붙으면서 영역 범위가 고정이 됩니다.
꼭 하셔야 정확한 값을 얻을 수 있어요.
이제 밑으로 쭈욱 드래그해주시면 단가 값이 자동으로 입력된 걸 보실 수 있습니다.
예시는 20개 정도밖에 안 되는 양이라 그냥 입력할 수도 있지만, 4페이지 5페이지 넘어가는 양이
된다면 Vlookup기능을 통해 한번에 원하는 값을 입력해 최소 퇴근시간 몇 시간은 줄이고, 실수도 피할 수 있습니다.
상황 2) 견적서 작성을 위한 아이템 전체 수량을 확인하고 싶을 때.
품목이 다양화되어있고, 정보의 양이 너무 많아 수량 계산이 어려울 때에
무선 이어폰의 경우 아이템은 맞는데 충전식과 건전지형으로 나뉘고, 납품해야 할
업체별로도 나뉘어 있을 때 쉽게 전체 수량을 파악하는 함수 썸이프쓰 (SUMIFS)를 소개하겠습니다.
순서는 위의 Vlookup 함수와 동일합니다
1. 구하고자 하는 값이 들어갈 칸을 클릭한다 (이 경우는 전체 수량)
2. 수식 카테고리 클릭
3. 함수 삽입 클릭
4. 함수 검색 창에서 SUMIFS 입력 후 검색
5. 확인 버튼 누름
하나씩 풀어서 보겠습니다.
1. 무선 이어폰 충전식의 합계 수량 계산이 목표, 목표 값이 들어갈 자리 L5열의 지정.
2. Sum_range는 모든 아이템의 전체 수량(E5:E27)을 드래그로 잡아줍니다.
3. Critical_range 1는 구하고자 하는 목표 값에서 거르고자 하는 단어의 전체 영역입니다.
(아이템 카테고리의 전체를 드래그로 잡아 주시면 됩니다.)
3-1. Critical 1는 위에서 드래그해둔(3번) 영역에서 찾고자 하는 값입니다.
(목표 값인 무선 이어폰을 기입).
4. Critical_range 2는 구하고자 하는 목표 값에서 거르고자 하는 단어의 전체 영역입니다.
(사이즈 카테고리의 전체를 드래그로 잡아 주시면 됩니다.)
4-1. Critical 2는 위에서 드래그해둔(4번) 영역에서 찾고자 하는 값입니다.
(목표 값인 충전식을 기입).
확인 버튼 누르기
결과값으로 69가 나옵니다. (무선 이어폰이면서, 충전식인 아이템의 합은 69개)
이제 드래그로 아래의 아이템들의 채워주면 되는데, 위에서 배운 것처럼 수식이 밀리는 걸
방지하기 위해 F4버튼으로 함수를 고정시켜주겠습니다.
드래그를 하셨다면 남은 수식을 고치기만 하면 됩니다.
무선 이어폰, 충전식 -> 무선 이어폰, 건전지형으로
무선 이어폰, 충전식 -> 스티커로
등등
마지막으로 저번 시간에 배운 자동 합계 기능을 통한 검산하기
수량과 아이템별 전체 수량을 확인 192개로 동일하다면 문제없음!!
오늘은 실무에서 유용하게 쓰이는 VLOOKUP, SUMIFS 함수를 배워봤는데요.
이 기능들만 배워 두신 다면 퇴근 시간이 한 시간 아니 두 시간은 줄어드는 경험을 하실 수 있습니다.
연습이 필요하신 분들을 위해 사진으로 이용한 예제 엑셀 파일을 업로드 해 두었습니다.
많은 분들께 도움이 되었으면 합니다.
글이 마음에 드셨다면
♥좋아요와 ☞구독하기 눌러주세요.
감사합니다.
댓글