문제의 답은 맨 아래에...
해당 콘텐츠는 '탈잉'에서 제공하는 Sophie 쏘피' 튜터의 '실무에 진짜 필요한 엑셀 노하우' 강의와 '패스트캠퍼스'에서 제공하는 '엑셀 실무 마스터 올인원 패키지 Online' 강의 내용을 바탕으로 재구성하여 제작한 콘텐츠임을 알립니다.
1. 남들보다 30분 일찍, 프로퇴근러로 만들어주는 <엑셀 함수>
안녕~ 취알男이야!
이번 포스팅에서는 사회초년생은 물론 취준생들도 알아두면 매우 요긴한 필수스킬인 'EXCEL'에 대해 다루어 보려고 해. 엑셀은 직장 내 여러 업무에서 뿐만 아니라 취준생들 역시도, 인턴 혹은 현장실습 기간 중에 자주 다루게 되는 툴 이라 활용도가 무척 높은 편이야.
워드, 파워포인트, 한컴오피스 등등 여러가지 문서 작성 프로그램들이 있지만, 그중에서도 엑셀은 가장 중요하다고 할 수 있어. ‘숫자’를 다루는데 적합할 뿐 아니라, ‘함수’만 잘 활용해도 빠르고 효과적으로 결과를 뽑아내 퇴근시간을 단축할 수 있거든.
자, 그럼 지금부터 가장 많이 쓰이는 TOP 3 엑셀 함수를 실제 어떤 업무에서 사용되고 또 어떻게 효율을 높일 수 있는지 하나하나 살펴보도록 하자.
2. VLOOKUP, SUMIF(S), COUNTIF(S)
주요 함수만 잘 써도 칼퇴근이 보장되는 엑셀
Top 1. 기준 범위의 데이터를 찾아 값을 추출해주는 <참조함수 VLOOKUP>
VLOOKUP
회사생활을 안해봤어도 VLOOKUP이라는 말을 많이 들어봤을거야. 그만큼 많이 쓰이는 함수이기 때문에 꽤 유명한 함수지. VLOOKUP은 Vertical Lookup의 약자로 '수직 또는 세로 형태로 있는 데이터들 중 맞는 것을 찾아서 매칭시켜줘'라는 함수야. 원하는 데이터를 자동으로 추출할 수 있기 때문에 빠른 일 처리를 위해서는 필수로 알아두어야 하는 함수 중 하나라고 할 수 있지.
왜 중요한 걸까?
우리가 자료를 만들 때 처음부터 끝까지 스스로 작업하는 경우도 있지만, 보통 기존에 있는 자료들을 참조하고 데이터를 가공해서 활용하는 경우가 훨씬 많거든. 그래서 이미 있는 데이터들 중 내가 원하는 것만 '찾아서 매칭해 주는' 함수가 그만큼 중요한거야. 또 Raw Data라 불리우는, 가공하기 위한 원본 데이터의 기본 형태가 수직의 형태 (세로)로 정리되어 있는 경우가 대부분이라 자주 쓰이는 함수이기도 하고.
그럼 사례를 한 번 봐 볼까?
“OO씨, 기존 사무용품 구입 단가에 맞춰서 부서별 최종 리스트 완성해 주세요.”
위처럼 업무지시가 내려온다면 어떻게 해야할까? '볼펜' 하나의 단가만 찾고 싶은 상황이라면 사실 함수를 쓸 필요가 없어. 그냥 '눈으로 찾는게' 더 빠르니까. 하지만 만일 사무용품의 종류가 훨씬 많다면? 게다가 신청부서마다 필요한 용품이 다 제각각이라면? 하나하나 확인하고 타이핑 하는 시간만 해도 한 세월 걸릴거라고. 이처럼 반복 작업으로 인한 시간과 노동력 낭비를 줄이는 것이 일잘러들의 방식이니 기억해두도록 하고. 어찌됐건 위와 같은 상황이라면, 각 부서마다 구입할 용품의 해당 단가를 자동으로 추출할 수 있다면 간단하게 끝마칠 수 있겠지?
VLOOKUP을 사용하기 위해 체크해야 할 4가지 항목
① 찾고자 하는 값이 무엇인지
② 참조할 데이터/표의 범위는 어디인지
③ 참조할 표에서 몇 번째 열에 있는 항목을 매칭할 것인지
④ 정확하게 혹은, 유사하게 일치하는 것을 찾을 것인지를 정해서 입력
참, 쉽지? 4가지만 정해주면 VLOOKUP을 바로 사용할 수 있어. 아래 예시를 보면 조금 더 이해가 쉬울거야.
기존에 가지고 있는 정보를 이용하여 찾아서 수직의 정보를 매칭시키는 함수
= VLOOKUP (lookup_value, table_array, col_index_number, range_lookup)
= VLOOKUP (찾고자 하는 값, 참조할 범위, 몇 번째 열, 일치 여부)
= VLOOKUP ('볼펜'에 해당하는 걸 찾아줘, 이 범위에서, '단가' 열의 값을, 정확하게)
= VLOOKUP (E4, $A$3:$B$8, 2, 0)
| 찾고자 하는 값 | '볼펜' |
| 참조할 범위 | 기존 '사무용품 구입단가' 표인 'A3:B8' |
| 몇 번째 열 | '단가' 열 |
| 일치 여부 | 정확하게 |
그런데 잠깐! 여기서 [참조할 범위], [몇 번째 열], [일치 여부]를 잠시 짚고 넘어가보도록 하자.
참조할 범위
엑셀에서 참조할 범위를 선택할 때는 'A3:B8'이 아니라 '$A$3:$B$8' 이렇게 지정해야 '복사+붙여넣기'를 할 때도 데이터가 꼬이지 않을 수 있어. 내가 참조할 범위는 계속 변하는게 아니라 고정되어 있기 때문에 절대참조인 'F4'버튼을 눌러서 잊지말고 꼭 고정시켜주어야 해.
[몇 번째 열]
VLOOKUP은 '참조할 범위(=A3:B8)' 내에서 '찾고자 하는 값(=볼펜)'이 포함된 열을 기준점 1로 삼고, 오른쪽으로 숫자를 세는 방식이야. 매칭하고자 하는 항목이 '단가'이기 때문에 2를 입력하면 돼.
[일치 여부]
정확하게 일치한 값을 찾고 싶다면 0을, 유사하게라도 일치한 값을 찾고 싶다면 1을 입력하면 되는데, 대부분 '정확한' 값을 찾고 싶어하기 때문에 항상 '0'을 입력한다고 생각하는게 기억하기 편할거야.
자, 그럼 정리해보면 아래와 같이 되겠지?
VLOOKUP 실수 5가지
혹시 VLOOKUP을 사용했는데, 뭔가 데이터가 안 맞거나 내가 생각한대로 나오지 않았다?
그럼 아래 5가지 중에 그 원인이 있을 확률이 높으니 한 번 확인해보록.
|
VLOOKUP 사용 시, 실수하는 5가지 |
|
| 참조할 범위 부분에 절대참조를 걸었는지? | 절대참조를 걸지 않았다면 복사+붙여넣기를 하는 도중 범위가 계속 바뀌게 되므로 원하는 값이 안 나올 수 있음. |
| 참조범위를 잡을 때, 기준점이 되는 기준 1이 항상 맨 왼쪽열에 와 있는지? | 내가 찾아야 할 것이 있는 곳이 기준 열이 되어서 참조할 범위의 가장 왼쪽으로 와 있는지 확인. |
| 찾고자 하는 값을 참조범위 안에서 선택하지 않았는지? | 아무 데서나 찾고자 하는 값을 선택하는 것이 아니라 내가 채워야 하는 표에서 찾고자 하는 값의 셀을 지정해야함. |
| 참조범위 안에 Lookup_value가 동일하게 중복되는 값이 2개 이상 있지 않는지? | VLOOKUP은 같은 열 안에 동일한 데이터가 있다면 가장 위에 있는 값만 찾을 수 있음. 즉, 중복된 값이 하나의 열에 2개 이상 있다면 VLOOKUP을 써서 매칭시킬 수 없음. |
| 수를 합하거나 계산하는 경우에 VLOOKUP을 사용하고 있지는 않은지? | 대개 숫자는 중복되는 값이 많기 때문에 숫자의 합이 필요할 때는 VLOOKUP 대신 SUMIF 함수를 활용함. |
Top 2. 범위 내에서 조건에 맞는 값을 도출하는 <통계함수 SUMIF(S)>
SUMIF
SUMIF는 '~에 해당되면 찾아서, 특정 범위의 합계를 더해줘' 라는 함수야. VLOOKUP 만큼 회사에서 정말 많이 사용하게 되는 함수인데, 특히 금액, 수량 등 숫자들의 합계를 알고 싶을 때 유용하게 쓰일 수 있지.
팀장님께 다음과 같은 지시가 왔다고 가정해보자.
“ㅇㅇ씨, 저희 부서 지출결의서에서 식비만 합산하면 얼마인가요?
설마... '구분'에서 '식비'만 따로 찾아 하나하나 더하고 있을건 아니지...? 함수는 항상 단순한 작업을 조금 더 빨리, 효율적으로 하기 위해서 사용한다는 것을 잊지말자.
지정한 조건에 맞는 범위의 값을 더하는 함수
= SUMIF (range, criteria, [sum_range])
= SUMIF (조건 범위, 찾고 싶은 조건, 합계를 구할 범위)
= SUMIF (이 범위에 있는, '식비'를 찾아서, '지출액' 열에 있는 숫자를 더해줘)
= SUMIF ($A$3:$A$9,E4,$C$3:$C$9)
SUMIF 실수 3가지
SUMIF 함수도 자주 틀릴 수 있는 요소들이 있으니 알아봅시다.
|
SUMIF 사용 시, 실수하는 3가지 |
|
| 참조할 범위 부분에 절대참조를 걸었는지? | VLOOKUP과 마찬가지로 절대참조를 걸지 않았다면 복사+붙여넣기를 하는 도중 범위가 계속 바뀌게 되므로 원하는 값이 안 나올 수 있음. |
| 조건범위와 합계를 구할 범위의 높낮이가 같은지? | 조건범위와 합계범위의 시작하는 지점과 끝나는 지점이 똑같아야 정확한 숫자를 나타내 줌. 만일 높낮이가 다르더라도 문제가 없는 것처럼 에러 표시가 안 뜨기 때문에 더욱 주의해야 함. |
| sum_range와 range를 헷갈리진 않았는지? | range는 그냥 범위이고 sum_range가 더할 range이니까 숫자가 들어가야 하는 항목이 sum_range. |
조건이 한 개가 아닐 때 (SUMIFS)
같이 알아두면 좋은 함수는 바로 SUMIFS. 영어에서 복수인 명사에는 S가 붙지? 함수도 마찬가지야. 조건이 복수라면 SUMIF 뒤에 S를 붙인 SUMIFS를 쓰면 돼.
자, 이제 퇴근을 앞두고 있는데 팀장님이 다시 한 번 부르는 거지.
Oh...My...God...!!!
"ㅇㅇ씨, 식비 중에 F카페에서 사용한 내역도 같이 알려주세요.”
여러 조건을 만족하는 범위의 합을 구하는 함수
= SUMIFS (sum_range, criteria_range1, creteria, criteria_range2, criteria2,...)
= SUMIFS (합계를 구할 범위, 조건범위1, 찾고 싶은 조건1, 조건범위2, 찾고 싶은 조건2,...)
= SUMIFS ('지출액' 열의 숫자를 더해줘, 1번 범위에서, '식비'를 찾고, 2번 범위에서, 'F카페'를 찾아서,...)
= SUMFIFS
조건이 하나이더라도, SUMIFS를 사용할 수 있기 때문에 함수 두 개를 외우기 헷갈린다면, SUMIFS만 외워도 괜찮아.
Top 3. 범위 내에서 조건에 맞는 값을 도출하는 <통계함수 COUNTIF(S)>
COUNTIF
마지막으로 COUNTIF는 '~에 해당되면 찾아서 몇 개인지 세어줘'라고 하는 의미로 쓰이는 함수야. '어떤 범위에서 무엇을 찾을 거야?' 라고 물어본 뒤, 그거를 세어달라고 하는 거지.
“OO씨, 거래처 리스트 보고 제조업 업종에 해당하는 업체가 몇 곳인지 알아봐주세요.”
이전과 마찬가지로 눈으로 보고 일일히 세지말고 함수를 써서 편하게 찾아보자. COUNTIF는 앞의 두 함수보다 훨씬 간단한 함수니까 쉽게 사용할 수 있을거야.
지정한 범위 내에서 조건에 맞는 셀의 개수를 구하는 함수
= COUNTIF (range, criteria)
= COUNTIF (범위, 찾고 싶은 것)
조건이 한 개가 아닐 때 (COUNTIFS)
SUMIF와 똑같이 조건이 여러개라면 S를 붙여서 COUNTIFS로 써주면 돼.
COUNTIFS 역시, 조건이 하나일 때도 사용할 수 있으니 COUNTIFS를 먼저 손에 익히는 걸 추천할게.
"ㅇㅇ씨, 가까운 곳 위주로 먼저 방문할거라서 제조업체 중에 서울 구로구에 있는 것만 따로 정리해주세요.”
여러 조건을 만족하는 셀의 개수를 구하는 함수
= COUNTIFS (criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3,...)
= COUNTIFS (조건범위1, 찾고 싶은 것1, 조건범위2, 찾고 싶은 것2, 조건범위3, 찾고 싶은 것3...)
VLOOKUP, SUMIF(S), COUNTIF(S) 이 3가지만 알아도 실무에서 여러 상황에 활용할 수 있는 만큼 꼭 손에 익혀놓도록 하자.
혹시 아직도 눈으로 찾고... 머릿속으로 계산하고... 모니터에 손가락으로 가리켜가며 세어보고 있다면? 엑셀의 주요 함수 기능을 활용해서 다들 프로퇴근러의 길로 접어들기를.
썸네일 정답 : ②번
※ 함께 보면 좋은 컨텐츠 바로가기 ▼
employment-ability-test.tistory.com/13
API를 쏴서.. 뭐라고요?? 문돌이들을 위한 개발지식!
해당 콘텐츠는 '최원영' 저자의 '비전공자를 위한 이해할 수 있는 it 지식' 도서 내용을 바탕으로 재구성하여 제작한 콘텐츠임을 알립니다. 1. 우린 모두 한 때, 수포자이자 컴알못이었다. (Feat. 코
employment-ability-test.tistory.com
취알男은 전문 취업컨설턴트나 코치가 아닙니다.
입사 능력 고사는 취린이들을 위한 취업준비 방법과 꿀팁을 제공하는 정보 공유성 블로그입니다.
잘 알고 있기에 쓰는 게 아니라 몰랐을 때 배운 점들을 묵히지 않고 공유하기 위한 채널입니다.
혹시라도 전문적으로 알고 계신 분들이 포스팅을 보신다면 "뭘 모르면서 글을 썼네"라고 생각하기보다는
"저 정도 수준의 지식을 배웠나 보구나"라고 넓은 마음으로 이해해주시면 감사하겠습니다.
지극히 개인적이고 사적인 취업솔루션
입사 능력 고사
-Fin-
'일잘러의 지식창고' 카테고리의 다른 글
| API를 쏴서.. 뭐라고요?? 문돌이들을 위한 개발지식! (0) | 2021.03.07 |
|---|
