직장/연구실에서 쓰는 기초적인 필수 엑셀(Excel) 함수 3개로 모든 업무 처리하기

안녕하세요. 이번에는 여태껏 알려준 함수 중 가장 자주 쓰이는 함수 3가지를 알려드릴까 해요. 이 3가지만 알아도 웬만한 업무는 처리할 수 있으니 알아두면 좋겠죠?

---

VLOOKUP, COUNTIF, SUMIF 함수 + 피벗 테이블

---

오늘의 Tip!

같은 셀에 여러 문장 넣기

Alt+Enter 를 하면 다음 셀로 넘어가지 않고,위와 같이 같은 셀 다음 문장에 글을 쓸 수 있다. (줄바꿈)


▶ 보통 업무를 하면 첫 번째 사진처럼 Data 테이블과 기준 정보 테이블을 받게 될 거예요.그럼 이제 빈칸들을 채워볼까요?

 

1. VLOOKUP 함수를 이용해 Data 테이블에 기준 정보의 값을 가져오자.

VLOOKUP 함수

각 메뉴에 해당하는 금액을 일일이 적기보다는 VLOOKUP 함수를 통해 그 값들을 가져오려고 합니다.

1번 인수 : 메뉴명에 해당하는 설렁탕(G6) (메뉴명을 이용해 정보 테이블의 단가(원)를 가져오는 방식)
2번 인수 : 기준 정보인 메뉴 정보를 범위로 지정해줍니다. (이때 범위 지정 후 F4를 눌러 범위를 고정해줍니다. 이유는 금액(원)을 순번에 따라 드래그할 때 범위도 같이 이동하기 때문!!)
3번 인수 : 범위의 1열 값인지 2열 값인지 물어보는 것인데, 이때 단가(원)의 값을 쓸 것이기 때문 것 ‘2’ 를 써줍니다.
4번 인수 : 1번 인수(메뉴명)가 정확히 일치할 경우 ‘FALSE’를, 유사 일치할 경우 ‘TRUE’를 기재합니다.

드래그를 통해 아래의 값까지 자동 계산

설렁탕은 8,000원이기 때문에, VLOOKUP 함수를 통해 8000 의 값이 나와요.
다음으로 위 그림처럼 우측 하단에 굵은 초록 점을 아래로 드래그하면 해당 메뉴에 따른 금액(원)이 나와요.
(이때 순번 3 부대찌개 금액(원)을 눌러보면 정보 테이블($I$6:$J$9) 이 고정된 것을 확인할 수 있어요)

2. COUNTIF 함수를 이용해 해당 메뉴의 개수를 세자.

COUNTIF 함수

각 메뉴에 해당하는 총금액을 일일이 카운트해 적기보다는 COUNTIF 함수를 통해 그 값들을 카운트 하려고 해요.

1번 인수 : Data 테이블의 메뉴명을 범위로 지정
(각 사람마다 메뉴가 다르기 때문, 고정 범위를 설정해줘야 2번 인수가 내려갈 때처럼 내려가지 않는다)
2번 인수 : 해당 메뉴를 지정

드래그를 통해 아래의 값까지 자동 계산

설렁탕을 시킨 사람은 3명이므로 COUNTIF 함수를 통해 3 의 값이 나와요. 우측 하단에 굵은 초록 점을 아래로 드래그하면 해당 수량이 나와요.

3. SUMIF 함수를 이용해 소속별 점심 금액이 얼마가 나오는지 알아보자.

SUMIF 함수

각 메뉴마다 소속별 총금액을 일일이 더해주기보다는 SUMIF 함수를 통해 그 값들을 더해주려고 해요.

1번 인수 : Data 테이블의 소속을 고정 범위로 지정
2번 인수 : 해당 소속을 지정
3번 인수 : 소속별 해당 금액(원)을 고정 범위로 지정

드래그를 통해 아래의 값까지 자동 계산

행정팀 3명이 주문한 점심 메뉴는 합 26,000원이므로 SUMIF 함수를 통해 2600 의 값이 나와요. 우측 하단에 굵은 초록 점을 아래로 드래그하면 해당 수량이 나와요.

이러한 작업을 간단하게 해주는 직장 업무 처리의 끝판왕 [피벗 테이블]에 대해 간단하게 소개해볼게요.

피벗 테이블 실행

[삽입]에서 [피벗 테이블]을 클릭

피벗 테이블 위치 설정

Data 테이블을 분석 데이터 범위로, 피벗 테이블이 위치할 빈 공간을 보고서 위치로 지정

1번 문제를 풀기위해 피벗 테이블 데이터 설정

행에 메뉴명, 값에 소속을 지정해 1번 정답을 바로 출력

2번 문제를 풀기위해 피벗 테이블 데이터 설정

행에 소속, 값에 금액(원)을 지정해 2번 정답을 바로 출력



이상으로 직장/실험실 업무에 필수이면서, 엄청 간단한 함수 3가지 및 피벗 테이블에 대해 알아봤어요.
이 글이 엑셀 업무 과정에서 도움이 되길 바랍니다. 감사합니다.

 

 

---

관련글

1. 엑셀(Excel)에서 자주 쓰는 함수 정리 1편 (SUM, SUMIF, AVERAGE, COUNTA)
2. 엑셀(Excel)에서 자주 쓰는 함수 정리 2편 (COUNTIFS, VLOOKUP, CONCATENATE, IF)
3. 엑셀(Excel)에서 자주 쓰는 함수 정리 3편 (SEARCH, FIND, SUBSTITUTE, MATCH, INDEX)
4. 엑셀(Excel)에서 자주 쓰는 함수 정리 4편 (TODAY, RANK, SLOPE, INTERCEPT)
번외편. 직장/연구실에서 쓰는 기초적인 필수 엑셀(Excel) 함수 3개로 모든 업무 처리하기

엑셀(Excel)에서 데이터를 선형, n차 근사 및 피팅 값 구하기(slope, intercept, index, linest)