“엑셀 함수 제대로 쓰자!” 계산과 일반적인 작업을 위한 15가지 팁
PCWorld
엑셀은 그 깊고 정교한 공식 기능을 알아갈수록 빠져들 수밖에 없는 프로그램이다. 원하는 결과를 얻는 방법이 여러 가지이므로 현재 상황에 가장 적합한 것을 결정할 수 있다. 예를 들어 삼수를 입력하고 숫자를 계산하는 방법에도 여러 가지가 있다.
함수를 입력하는 5가지 방법
1. 수동으로 엑셀 함수 입력하기
목록이 길 때: =SUM(B4:B13)
목록이 짧을 때: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7).
또는 목록 맨 아래의 첫 번째 빈 셀에 커서를 올리고(사실 아무 셀이나 상관없음) + 기호를 누른 다음 B4를 클릭한다. 다시 + 기호를 누르고 B5를 클릭한다. 이런 식으로 끝까지 한 다음 Enter를 누른다. 이렇게 하면 마우스로 지정한 목록의 총계를 구할 수 있다. =+B4+B5+B6+B7.
2. 함수 삽입 버튼 클릭하기
수식 탭 아래에 있는 함수 삽입을 사용하면 엑셀 메뉴 목록에서 함수를 선택할 수 있다.
=COUNT(B4:B13)는 범위 내에 있는 수의 개수를 센다(빈 셀은 무시함).
=COUNTA(B3:B13)는 범위 내에 있는 모든 문자의 수를 센다(빈 셀은 무시함).
3. 그룹에서 함수 선택하기(서식 탭)
재무, 논리 또는 날짜/시간과 같은 함수 라이브러리를 이용하면 검색 범위를 좁힐 수 있다.
=TODAY() 오늘 날짜를 삽입한다.
4. 최근에 사용한 함수
‘최근에 사용한 함수’를 클릭하면 버튼 이름 그대로 최근 사용한 함수가 표시된다. 특히 복잡한 스프레드시트로 작업할 때 이 기능을 사용하면 많은 시간을 절약할 수 있다.
=AVERAGE(B4:B13)는 목록의 수를 더하고 값의 개수로 나눈 다음 평균을 표시한다.
5. 자동 합계 버튼 아래의 자동 함수
자동 함수는 아주 빠르다. 필자의 편집 담당자가 가장 좋아하는 기능이다. 셀 범위와 함수를 선택하면 결과가 바로 나온다. 몇 가지 예를 보자.
=MAX(B4:B13)은 목록에서 가장 높은 값을 반환한다.
=MIN(B4:B13)은 목록에서 가장 낮은 값을 반환한다.
자동 합계 버튼을 사용해 SUM, AVERAGE, COUNT 등의 기본 함수를 계산한다.
참고: 숫자 범위 바로 아래 빈 셀에 커서가 위치한 경우, 엑셀은 이를 사용자가 계산하고자 하는 범위라고 판단하고 자동으로 범위를 선택하거나 해당 대화 상자에 범위 셀 주소를 입력한다.
보너스 팁: 기본적인 함수의 경우 자동 합계 버튼을 사용하는 방법이 가장 좋다. 자동 합계 > 합계를 클릭하고(범위는 엑셀이 알아서 선택해줌) Enter를 입력하는 편이 속도가 빠르다.
일반적인 작업에 유용한 5가지 함수
아래 5가지 삼수는 다소 이해하기 어려울 수도 있지만 일상적인 작업에서 시간을 절약하고 데이터 입력 작업을 줄이는 데 유용하다.
참고: 일부 함수의 경우 계산하고자 하는 단일 셀, 또는 값이나 텍스트의 범위 주소를 입력해야 한다. 엑셀에 여러 가지 셀/범위 대화 상자가 표시되는 경우 셀/범위 주소를 수동으로 입력하거나 커서로 지정하면 된다. 커서로 지정한다는 것은 워크시트에서 먼저 필드 상자를 선택한 다음 해당 셀을 클릭함을 의미한다. 셀 범위를 계산하는 공식에 대해 이 과정을 반복한다(예: 시작 날짜, 끝 날짜 등).
1. =DAYS
두 날짜 사이의 일 수를 계산할 때 유용하다. (즉, 범위가 두 달에 걸쳐 있을 때 총 며칠인지 고민할 필요가 없다.)
예: 끝 날짜 2015년 10월 12일 – 시작 날짜 2015년 3월 31일 = 195일
함수: =DAYS(A30,A29)
2. =NETWORKDAYS
지정된 기간 내의 평일 수를 계산한다(즉, 주당 5일). 총계에서 공휴일을 뺄지 여부도 선택할 수 있으며 반드시 날짜 범위로 입력해야 한다.
예: 시작 날짜 2015년 3월 31일 – 끝 날짜 2015년 10월 12일 = 140일
함수: = NETWORKDAYS(A33,A34)
3. =TRIM
데이터베이스, 웹 사이트, 워드 프로세서 또는 기타 텍스트 기반 프로그램의 텍스트를 엑셀에 가져오거나 붙여 넣는 작업을 자주 한다면 TRIM이 아주 유용하다. 가져온 텍스트에 불필요한 공백이 여기저기 들어가 경우가 많은데, TRIM은 간단히 이런 공백들을 제거해준다. 공식을 한 번만 입력하고 목록 끝까지 복사하면 된다.
예: =TRIM + 괄호 안의 셀 주소
함수: =TRIM(A39)
4. =CONCATENATE
엑셀로 데이터를 가져오는 경우가 많은 사람을 위한 또 하나의 필수 도구다. 이 공식은 두 필드/셀의 내용을 하나로 합친다. 예를 들어 데이터베이스에는 날짜, 시간, 전화 번호 및 기타 여러 데이터 레코드가 별도의 필드로 입력되어 있는 경우가 많아 무척 불편하다. 필드 사이에 단어나 구두점을 추가하려면 따옴표로 그 데이터를 감싸기만 하면 된다.
예: = CONCATENATE + (월,“공백”,일,”쉼표 공백”,연도). 여기서 월, 일, 연도는 모두 셀 주소이며 따옴표 안의 정보는 실제로 공백 또는 쉼표다.
함수: 날짜의 경우 다음과 같이 입력: =CONCATENATE(E33,” “,F33,”, “,G33)
함수: 전화 번호의 경우 다음과 같이 입력: =CONCATENATE(E37,”-“,F37,”-“,G37)
5. =DATEVALUE
DATEVALUE는 위의 공식을 엑셀 날짜로 변환한다. 이 날짜를 계산에 사용하려는 경우 필요한 함수다. 사용 방법은 쉽다. 공식 목록에서 DATEVALUE를 선택한다. 대화 상자에서 Date_Text 필드를 클릭하고 스프레드시트에서 해당 셀을 클릭한 다음 확인을 클릭하고 아래로 쭉 복사한다. 결과는 엑셀 일련 번호이므로 서식>셀 서식>숫자>날짜를 선택한 다음 목록에서 서식을 선택해야 한다.
함수: =DATEVALUE(H33)
세 가지 추가 공식 팁
공식을 다룰 때 아래 세 가지 보너스 팁을 참고하면 혼동을 피할 수 있다.
팁1: 공식을 텍스트나 숫자로 변환하기 위해 또 다른 공식을 사용할 필요는 없다. 공식의 범위를 복사한 다음 기타>값으로 붙여 넣으면 된다. 공식을 값으로 변환하는 이유는 무엇일까? 변환하지 않으면 데이터를 이동하거나 조작할 수 없기 때문이다. 이러한 셀은 전화 번호처럼 보이지만 사실은 공식이며, 숫자나 텍스트처럼 편집할 수 없다.
팁2: 날짜에 대해 복사하여 붙여넣기>기타>값을 사용할 경우 결과는 텍스트이며 이를 실제 날짜로 변환할 수 없다. 날짜가 실제 날짜로 기능하려면 DATEVALUE 공식이 필요하다.
팁3: 공식은 항상 대문자로 표시된다. 소문자로 입력하면 자동으로 대문자로 변환된다. 또한 공식에는 공백이 없다. 공식에서 오류가 발생하면 공백을 찾아보고 제거하라.
함수를 입력하는 5가지 방법
1. 수동으로 엑셀 함수 입력하기
목록이 길 때: =SUM(B4:B13)
목록이 짧을 때: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7).
또는 목록 맨 아래의 첫 번째 빈 셀에 커서를 올리고(사실 아무 셀이나 상관없음) + 기호를 누른 다음 B4를 클릭한다. 다시 + 기호를 누르고 B5를 클릭한다. 이런 식으로 끝까지 한 다음 Enter를 누른다. 이렇게 하면 마우스로 지정한 목록의 총계를 구할 수 있다. =+B4+B5+B6+B7.
2. 함수 삽입 버튼 클릭하기
수식 탭 아래에 있는 함수 삽입을 사용하면 엑셀 메뉴 목록에서 함수를 선택할 수 있다.
=COUNT(B4:B13)는 범위 내에 있는 수의 개수를 센다(빈 셀은 무시함).
=COUNTA(B3:B13)는 범위 내에 있는 모든 문자의 수를 센다(빈 셀은 무시함).
3. 그룹에서 함수 선택하기(서식 탭)
재무, 논리 또는 날짜/시간과 같은 함수 라이브러리를 이용하면 검색 범위를 좁힐 수 있다.
=TODAY() 오늘 날짜를 삽입한다.
4. 최근에 사용한 함수
‘최근에 사용한 함수’를 클릭하면 버튼 이름 그대로 최근 사용한 함수가 표시된다. 특히 복잡한 스프레드시트로 작업할 때 이 기능을 사용하면 많은 시간을 절약할 수 있다.
=AVERAGE(B4:B13)는 목록의 수를 더하고 값의 개수로 나눈 다음 평균을 표시한다.
5. 자동 합계 버튼 아래의 자동 함수
자동 함수는 아주 빠르다. 필자의 편집 담당자가 가장 좋아하는 기능이다. 셀 범위와 함수를 선택하면 결과가 바로 나온다. 몇 가지 예를 보자.
=MAX(B4:B13)은 목록에서 가장 높은 값을 반환한다.
=MIN(B4:B13)은 목록에서 가장 낮은 값을 반환한다.
자동 합계 버튼을 사용해 SUM, AVERAGE, COUNT 등의 기본 함수를 계산한다.
참고: 숫자 범위 바로 아래 빈 셀에 커서가 위치한 경우, 엑셀은 이를 사용자가 계산하고자 하는 범위라고 판단하고 자동으로 범위를 선택하거나 해당 대화 상자에 범위 셀 주소를 입력한다.
보너스 팁: 기본적인 함수의 경우 자동 합계 버튼을 사용하는 방법이 가장 좋다. 자동 합계 > 합계를 클릭하고(범위는 엑셀이 알아서 선택해줌) Enter를 입력하는 편이 속도가 빠르다.
일반적인 작업에 유용한 5가지 함수
아래 5가지 삼수는 다소 이해하기 어려울 수도 있지만 일상적인 작업에서 시간을 절약하고 데이터 입력 작업을 줄이는 데 유용하다.
참고: 일부 함수의 경우 계산하고자 하는 단일 셀, 또는 값이나 텍스트의 범위 주소를 입력해야 한다. 엑셀에 여러 가지 셀/범위 대화 상자가 표시되는 경우 셀/범위 주소를 수동으로 입력하거나 커서로 지정하면 된다. 커서로 지정한다는 것은 워크시트에서 먼저 필드 상자를 선택한 다음 해당 셀을 클릭함을 의미한다. 셀 범위를 계산하는 공식에 대해 이 과정을 반복한다(예: 시작 날짜, 끝 날짜 등).
1. =DAYS
두 날짜 사이의 일 수를 계산할 때 유용하다. (즉, 범위가 두 달에 걸쳐 있을 때 총 며칠인지 고민할 필요가 없다.)
예: 끝 날짜 2015년 10월 12일 – 시작 날짜 2015년 3월 31일 = 195일
함수: =DAYS(A30,A29)
2. =NETWORKDAYS
지정된 기간 내의 평일 수를 계산한다(즉, 주당 5일). 총계에서 공휴일을 뺄지 여부도 선택할 수 있으며 반드시 날짜 범위로 입력해야 한다.
예: 시작 날짜 2015년 3월 31일 – 끝 날짜 2015년 10월 12일 = 140일
함수: = NETWORKDAYS(A33,A34)
3. =TRIM
데이터베이스, 웹 사이트, 워드 프로세서 또는 기타 텍스트 기반 프로그램의 텍스트를 엑셀에 가져오거나 붙여 넣는 작업을 자주 한다면 TRIM이 아주 유용하다. 가져온 텍스트에 불필요한 공백이 여기저기 들어가 경우가 많은데, TRIM은 간단히 이런 공백들을 제거해준다. 공식을 한 번만 입력하고 목록 끝까지 복사하면 된다.
예: =TRIM + 괄호 안의 셀 주소
함수: =TRIM(A39)
4. =CONCATENATE
엑셀로 데이터를 가져오는 경우가 많은 사람을 위한 또 하나의 필수 도구다. 이 공식은 두 필드/셀의 내용을 하나로 합친다. 예를 들어 데이터베이스에는 날짜, 시간, 전화 번호 및 기타 여러 데이터 레코드가 별도의 필드로 입력되어 있는 경우가 많아 무척 불편하다. 필드 사이에 단어나 구두점을 추가하려면 따옴표로 그 데이터를 감싸기만 하면 된다.
예: = CONCATENATE + (월,“공백”,일,”쉼표 공백”,연도). 여기서 월, 일, 연도는 모두 셀 주소이며 따옴표 안의 정보는 실제로 공백 또는 쉼표다.
함수: 날짜의 경우 다음과 같이 입력: =CONCATENATE(E33,” “,F33,”, “,G33)
함수: 전화 번호의 경우 다음과 같이 입력: =CONCATENATE(E37,”-“,F37,”-“,G37)
5. =DATEVALUE
DATEVALUE는 위의 공식을 엑셀 날짜로 변환한다. 이 날짜를 계산에 사용하려는 경우 필요한 함수다. 사용 방법은 쉽다. 공식 목록에서 DATEVALUE를 선택한다. 대화 상자에서 Date_Text 필드를 클릭하고 스프레드시트에서 해당 셀을 클릭한 다음 확인을 클릭하고 아래로 쭉 복사한다. 결과는 엑셀 일련 번호이므로 서식>셀 서식>숫자>날짜를 선택한 다음 목록에서 서식을 선택해야 한다.
함수: =DATEVALUE(H33)
세 가지 추가 공식 팁
공식을 다룰 때 아래 세 가지 보너스 팁을 참고하면 혼동을 피할 수 있다.
팁1: 공식을 텍스트나 숫자로 변환하기 위해 또 다른 공식을 사용할 필요는 없다. 공식의 범위를 복사한 다음 기타>값으로 붙여 넣으면 된다. 공식을 값으로 변환하는 이유는 무엇일까? 변환하지 않으면 데이터를 이동하거나 조작할 수 없기 때문이다. 이러한 셀은 전화 번호처럼 보이지만 사실은 공식이며, 숫자나 텍스트처럼 편집할 수 없다.
팁2: 날짜에 대해 복사하여 붙여넣기>기타>값을 사용할 경우 결과는 텍스트이며 이를 실제 날짜로 변환할 수 없다. 날짜가 실제 날짜로 기능하려면 DATEVALUE 공식이 필요하다.
팁3: 공식은 항상 대문자로 표시된다. 소문자로 입력하면 자동으로 대문자로 변환된다. 또한 공식에는 공백이 없다. 공식에서 오류가 발생하면 공백을 찾아보고 제거하라.
댓글 없음:
댓글 쓰기