엑셀 중급자를 위한 함수 TIP
기본적인 함수 8가지를 마스터했다면
이제는 함수와 친해지는 시간!
어렵게만 느껴지던 함수와 조금 가까워지셨나요?
엑셀에 대한 감을 익히면 조금 더 어렵고 복잡한 함수도 쉽게 응용할 수 있습니다!
그러니 이제는 엑셀과 친해져봅시다!
스마트 카드
기본적인 함수는 이해했다면,
이제 조금 더 실무에 사용 가능한 함수에 도전해 볼 시간입니다!😉
엑셀 중급에서 알려드릴 함수는 4가지! 이 함수들만 알고 있어도 더 이상 엑셀을 무서워하지 않고, 어느 상황에 어떤 함수를 사용하면 되는지 감을 잡고 실무에 응용할 수 있습니다.
예제를 함께 따라 해보면 금방 이해하고 쉽게 익힐 수 있는 중급자를 위한 함수 4가지! 천천히 알아보도록 할게요!
? 엑셀 중급자를 위한 함수 4가지 ?
1. SUMIF 2. VLOOKUP
3. HLOOKUP 4. SUBTOTAL
업무의 효율성을 높여주고 시간을 절약해 주는 함수들,
실무에 자주 사용하는 함수들만 가져왔어요!??
VLOOKUP, HLOOKUP 등..
많이 들어 봤지만 사용하려고 하면 인터넷 검색창부터 찾게 되는 게 현실!
잘만 활용하면 업무 효율을 확실하게 올려줄 수 있는 함수들이고, 예제와 함께 실제로 연습해 보면 이거 생각보다 어렵지 않네?라고 생각할 만큼 쉬우니 잘 따라오세요!
눈으로만 보고 이해하는 것보다 엑셀로 직접 함수를 작성해 보면서 이해하시면 더욱 빠르게 습득할 수 있어요!
?? 조건에 맞는 값의 합계를 계산해 주는 SUMIF 함수!
? 요약 : 범위에서 조건을 만족하는 값의 합계를 계산하는 함수입니다.
? 수식 : =sumif(조건범위, 조건, 합계범위)
Sumif 함수는 합계를 나타내는 SUM 함수와 조건을 나타내는 IF 함수가 합쳐진 함수라고 생각하시면 이해하기 쉽습니다. 즉, Sumif 함수는 범위에서 하나의 조건을 만족하는 값의 합계를 계산하는 함수입니다.
함수의 조건으로는 연산자 (<,>,=), 와일드카드 (*, ?, ~)를 사용할 수 있으며, 합계 범위를 지정하지 않으면 조건 범위에서 조건을 만족하는 값의 합계를 계산하게 됩니다.
이때 조건 범위와 합계 범위는 보통 절대 참조로 나타냅니다.
위의 예시처럼 G6 셀에 D 열에 입력되어 있는 제품명을 입력하게 되면 자동으로 H6 셀에 해당 상품의 출고수량이 계산되게 됩니다.
EXPERT TIP!
조건에서 연산자나 텍스트를 사용할 때에는 반드시 큰따옴표(“”)로 묶어 사용해야 합니다!
실무에서 사용하는 함수 예제 1
입출금내역을 통해 자금일보/자금월보를 자동으로 추출할 수 있는 엑셀 프로그램입니다.
[자금일보,월보] 시트에 사용된 SUMIF 함수를 통해 실무를 익혀보세요.
* 시트해제 후 함수를 확인해주세요!
?? 데이터에 따른 특정 값을 추출하고 싶을 때 사용하는 VLOOKUP / HLOOKUP 함수!
? 요약 : 배열의 첫째 열(Vlookup)/행(Hlookup)을 찾아 행/열 쪽으로 이동하여 셀 값을 반환하는 함수입니다.
? 수식 : =Vlookup(찾을 값, 참조 범위, 열 번호, 일치 옵션) / =Hlookup(찾을 값, 참조 범위, 행 번호, 일치 옵션)
Vlookup 함수는 표의 맨 왼쪽에서 찾으려는 값을 검색한 후, 동일한 행에 위치한 다른 값을 출력하는 함수입니다. 가로로 입력된 자료에서 값을 찾아야 할 경우에 Hlookup 함수를 사용하고 사용방법은 동일합니다.
수식을 복사하기 위해서는 범위를 절대 참조로 바꾸어주어야 하며, 열 번호는 참조 범위에서 위치하는 열 번호를 입력하면 되는데, 위의 예시에서는 이름을 추출해야 하므로 2를 입력하면 됩니다.
일치 옵션은 기본값이 True가 되며, 이것은 유사 일치를 뜻합니다. 따라서 정확히 일치 옵션을 사용하고 싶으면 0 또는 false를 입력하면 되고, 일반적으로 0을 많이 사용합니다!
쉽게 정리해서 Vlookup을 이용하여 순위에 따른 이름을 추출하고자 할 때에는 찾을 값을 선택하고 값이 입력된 전체 범위를 드래그 후 절대 참조로 변환하고 출력할 값이 있는 열의 번호를 입력한 후 0 또는 false를 입력하면 됩니다!
EXPERT TIP!
Vlookup은 항상 찾으려는 값이 반드시 첫 번째 열에 입력되어 있어야 합니다!!
위의 예시처럼 순위에 따른 이름이 추출되길 원한다면 표의 첫 번째 열에 [순위]가 있어야 합니다!
실무에서 사용하는 함수 예제 2
입출고관리를 하는 자동화서식으로 품목코드, 품목, 거래처, 단가 등을 입력하고 입출고 일자와 수량등을 입력하면 일자별 품목별 재고량을 파악할 수 있는 엑셀 프로그램입니다.
입고현황, 출고현황 시트에 사용된 VLOOKUP 함수를 통해 실무를 익혀보세요.
* 시트해제 후 사용하시면 됩니다.
?? 부분합을 반환해 주는 Subtotal 함수!
? 요약 : 목록이나 데이터베이스의 부분합을 반환하는 함수입니다.
? 수식 : =subtotal(계산 방법, 범위)
Subtotal 함수, 어쩌면 처음 들으시는 분 들도 있을 것 같아요!
Sum, Average 등의 일반적인 함수를 사용하면 편하지만 필터 등을 사용해서 특정 조건에 해당하는 값의 합계만 구하고 싶을 땐 subtotal 함수를 사용하시면 됩니다!
즉, Subtotal 함수는 필터링, 숨겨진 셀은 제외하고 화면에 보이는 셀만 집계하여 원하는 값을 추출하는 함수라고 생각하시면 됩니다!
위의 예시와 같이 카테고리에 필터를 적용했을 때 SUM 과 SUBTOTAL의 추출 값이 달라지는 것 보이시죠?
단순히 SUM을 사용하면 필터로 추출한다고 해도 전체 합계 값이 그대로 추출되지만, SUBTOTAL을 사용하게 되면 필터링 된 값들의 합계가 반환되어 원하는 값을 얻을 수 있습니다!
필터를 자주 사용하는 인사관리나 재무관리 업무에 활용하실 수 있는 함수이기 때문에 잘 기억해두시면 유용하게 사용하실 수 있습니다.
Subtotal 함수에서 입력해야 하는 첫 인수, 계산 방법에 입력해야 하는 값은 위의 표를 참고해 주세요!
=subtotal(101, 로 시작하게 되면 Average 함수를 쓰겠다는 의미와 같고, 필터를 사용했을 때 숨겨진 행의 값은 포함하지 않겠다는 의미가 됩니다.
EXPERT TIP!
지금 읽고 있는 블록- 엑셀 중급자를 위한 함수 TIP
- 나만의 체크리스트 만들기
- 엑셀 기본기능 TIP
![비즈폼 파워포인트 활용 TIP [ 엑셀 연동 ]](https://www.bizforms.co.kr/renew/images/main/sm_103_4_202374153352.png)
