실전에서 바로 쓰는 엑셀 기능과 단축키 - 필수 함수 및 유용한 함수

미국 마이크로소프트에서 만든 MS Office 프로그램의 하나인 엑셀(Excel)은 전세계의 수많은 학생들과 직장인들이 이용하고 있는 MS의 킬러 컨텐츠입니다.

 

 

우리나라에서도 대학생이나 직장인, 사업자 분들에게는 데이터를 관리할 때 거의 필수적으로 이용하는 소프트웨어라고 할 수 있는데요,

 

저는 엑셀을 처음 익힐 때 엑셀을 알려주는 시중 서적은 양이 너무 방대하고 인터넷에 올라와있는 정보들은 이미 알고있는 기초적인 내용만 되풀이하는 경향이 있어 답답했던 경험이 있습니다.

 

 

이에 엑셀을 쉽고 빠르게 만들어주는 여러 단축키와 기능들, 특히 실전에서 당장 써먹고 과제시간을 줄여주거나 퇴근시간을 앞당겨줄 수 있는 꿀팁들을 알려 드리려고 합니다.

 

 

오늘은 엑셀을 다룰 때 필수적으로 익혀두어야 할 함수들을 정리해 보겠습니다.

 

 

* 필수적으로 알아야 하는 기초 함수

 

 

[sum 함수]

 

입력 : =SUM(범위)

특정 범위의 총합을 구할 때 이용합니다.

 

 

[average 함수]

 

입력 : =AVERAGE(범위)

특정 범위의 (산술)평균값을 구할 때 이용합니다.

 

 

[if 함수]

 

입력 : =IF(조건, 조건이 참인 경우의 결과값, 조건이 거짓인 경우의 결과값)

특정 조건의 참 거짓에 따라 두 결과값 중 하나를 보여주는 함수입니다.

 

위의 세 함수는 가장 기본적인 함수이므로 무조건 기억해두는 것을 추천드립니다.

 

 

 

* 알아두면 정말 유용한 실전 함수

 

[vlookup 함수]

 

입력 : = VLOOKUP(참조 값, 참조 데이터베이스, 열 번호, [선택])

([선택] 란에는 TRUE(유사한 값) 혹은 FALSE(일치하는 값) 를 넣을 수 있는데 보통 빈 칸으로 비워둡니다.)

흔히 '참조'함수라고 하는데요, 특정 참조 값에 해당하는 결과값을 반환합니다. 보통 한 세트의 데이터베이스가 이미 있는 상태에서, 새로운 테이블(표)을 다시 만들 때 이용합니다.

 

위의 예시처럼 보통 함수를 입력한 후 아래쪽으로 <Ctrl + D>나 <F2> & <Shift + Enter>를 이용하여 복사하는 경우가 많으므로 "참조 데이터베이스"는 <F4>키를 눌러 절대참조로 변환시켜줍니다.

 

다만, vlookup 함수의 주의할 점은

1) 참조할 값이 데이터베이스의 가정 왼쪽 열에 있어야 하고(위 예시에선 "출석번호"에 해당)

2) 하나의 참조 값에 대해 서로 다른 결과값이 있는 경우 가장 위의 행에 있는 결과값을 보여줍니다.

 

 

[index, match 함수]

 

입력 : = INDEX(결과값이 있는 열,MATCH(참조 값, 참조할 값이 있는 열))

"참조 값이 가장 왼쪽에 있어야 한다"는 vlookup함수의 단점을 보완해주는 함수입니다. 위의 예시의 경우에는 출석번호가 학번보다 오른쪽에 있는 경우에 vlookup 함수로는 출석번호 참조를 걸기 힘들지만, index match 함수로는 가능합니다.

 

"결과값이 있는 열"과 "참조할 값이 있는 열"은 <F4>키를 이용하여 습관적으로 절대참조를 걸어주시는게 편리합니다.

 

 

[sumif 함수] (유사함수 : averageif, countif)

 

입력 : = SUMIF(참조할 값이 있는 열, 참조 값, 합계를 낼 열)

조건부 합계 함수입니다. 1:1대응되는 참조값이 아닌, 어떤 범주의 총합을 구할 때 이용합니다.

 

countif와 averageif는 수식의 구조는 동일하지만 결과값이 개수, 평균으로 구해집니다.

(countif는 참조할 값이 있는 열과 참조 값만 입력하면 됩니다.)

 

 

[sumifs 함수]

 

입력 : = SUMIFS(합계를 낼 열, 참조할 값 1이 있는 열, 참조할 값 1, 참조할 값 2가 있는 열, 참조할 값 2, .... )

여러 범주의 조건을 만족하는 값들의 총합을 구할 때 이용합니다. sumif 함수와 달리 합계를 낼 열이 맨 앞에 등장하는 것에 유의해주세요.

 

averageifs, countifs도 동일한 수식 구조를 지니고 있습니다.

 

 

[round, roundup, rounddown  함수] (tip : 백만자리 만들기)

 

입력 : = ROUND(셀, 자릿수)

특정 자릿수에서 반올림(round), 올림(roundup), 버림(rounddown)합니다.

 

자릿수는 소수점 기준입니다. 자릿수를 0으로 입력하면 소수점 이하 반올림/올림/버림, 1을 입력하면 소수점 첫째자리 미만 반올림/올림/버림, -1을 입력하면 십의자리 미만 반올림/올림/버림 입니다.

 

아래와 같이 큰 숫자를 천단위 / 백만단위로 반올림하여 나타낼 때 이용하기 편합니다.

 

 

 

[datedif 함수]

 

입력 : = DATEDIF(앞 날짜, 뒤 날짜, "d" or "m" or "y")

 

두 날짜 사이의 연수/월수/일수를 구하는 함수로, 감가상각비 등 기간에 기반한 계산이 필요할 때 많이 쓰이는 함수입니다.

 

 

 

* 함수 입력이 귀찮을 때 꿀팁

 

위의 함수들 중 vlookup, index/match와 sumif 시리즈는 예쁜 보고양식이 필요한 것이 아니라면 피벗테이블로 빠르게 확인할 수 있습니다.

 

데이터베이스를 블럭설정한 후, <Alt + N + V> 키를 눌러 피벗테이블을 생성합니다.

 

 

조건 범주를 "행"에 드래그, 합계를 낼 범주를 "값"에 드래그합니다.

(ex: 조건 범주 - 성별, 학번. 합계 범주 - 중간고사 점수)

 

sumifs를 이용하는 경우 =SUMIFS("중간고사 점수 열","성별" 열, 성별, "학번" 열, 학번)을 각각 입력해야 하지만, 이처럼 피벗 테이블로 범주 위치만 드래그하여 지정하면 빠르게 값을 확인할 수 있습니다.

 

averageifs나 countifs 대신 이용하고 싶은 경우, "값"의 "합계 : 중간고사"를 클릭하여 "값 필드 설정"을 클릭한 후, "평균" 혹은 "개수"를 클릭하면 됩니다.

 

 

vlookup 함수를 피벗테이블 기능을 이용하여 빠르게 살펴보는 방법은 "행" 영역에 두 범주를 드래그하는 것입니다.

 

위 vlookup 함수에서 예시로 살펴보았듯이 "출석번호"를 참조하여 "학번"을 알고싶은 경우, 피벗테이블의 "행" 영역에 "출석번호"와 "학번"을 드래그하면 됩니다.

 

 

아래쪽에 배치되는 것이 보기 싫다면 "출석번호"를 클릭하여 "필드 설정", "부분합 및 필터"에서 부분합 "없음", "레이아웃 및 인쇄"에서 "테이블 형식으로 항목 레이블 표시"로 설정하면,

 

 

다음과 같이 vlookup 함수를 쓴 것처럼 피벗테이블이 출력됩니다.

 

 

이상으로 엑셀 필수 및 실전 함수들과 피벗테이블을 이용한 꼼수(?)에 대한 포스팅을 마치겠습니다.

 

긴 글 읽어주셔서 감사드리고, 독자 여러분들의 엑셀 능률 향상에 조금이나마 도움이 되었길 진심으로 기원합니다.

 

 

P.S. 예제파일을 함께 첨부드립니다. 함수가 적용된 셀 위에서 <F2>키를 눌러 확인하시면 좀 더 감을 잡으실 수 있습니다.

 

예제파일2.xlsx

0.13MB