[컴활 1급 실기] 스프레드시트 수식 함수 및 꿀팁 정리

컴퓨터활용능력 1급 실기 스프레드시트 과목 문제를 풀이하기 위해 필요한 함수 및 꿀팁들을 정리하고 있습니다. 

 

함수

---

함수	설명	비고
YEAR(셀)	해당 날짜 셀의 연도 반환	YEAR(C4) = C4 셀의 연도 반환
RIGHT(셀, 글자 수)	해당 셀의 오른쪽 글자를 글자 수만큼 반환	RIGHT(A1, 1) = A1 셀의 오른쪽 1글자 반환 이때, RIGHT의 반환은 '문자'
LARGE(범위, 숫자)	해당 범위에서 '숫자'번째 큰 값 반환	LARGE(A1:A12, 3)는 A1부터 A12까지 3번째로 큰 값 반환
RANK.AVG(기준 셀, 비교 범위, 정렬)	비교범위 내에서 기준 셀의 순위 반환	동점이 있는 경우 평균 값을 반환 (2등 2명인 경우 2.5로 표기)
RANK.EQ(기준 셀, 비교 범위, 정렬)	비교범위 내에서 기준 셀의 순위 반환	동점은 같은 순위로 처리 (2등 2명인 경우 3등이 존재 X)
CHOOSE(인덱스, 값1, 값2, ...)	값1, 값2, ...의 값들 중 인덱스 값 반환	CHOOSE(1, "일", "이")의 경우 [일] 반환 CHOOSE는 반드시 1부터 시작
MID(셀, 시작 인덱스, 오프셋)	셀의 시작 인덱스부터, 오프셋만큼의 문자를 반환	MID(123456, 2, 3)의 경우 [234] 반환
VLOOKUP(값, 참조 테이블, 반환 열, 참조방법)	값을 참조 테이블에서 찾아서 반환 열의 값을 반환	유사 참조 - 근접한 값 반환 정확히 참조 - 정확한 값만 반환
MATCH(값, 영역, 찾기 방법)	값을 영역에서 찾아 인덱스 반환	MATCH(1, 영역, 0), 영역 = [0, 1, 2]인 경우 MATCH 함수는 1의 인덱스인 2를 반환
INDEX(범위, 행번호, 열번호)	해당 범위의 행과 열 번호에 맞는 값반환	INDEX(영역, 1, 2), 영역 = [1, 2, 3]인 경우 INDEX 함수는 1행 2열의 2를 반환
VALUE(값)	해당 값을 숫자로 반환	VALUE 함수를 사용할 수 없다면 *1도 가능
LOOKUP(값, 찾을 범위, 반환 범위)	찾을 범위에서 값을 찾아, 반환 범위에서 반환	LOOKUP(1, {0, 1, 2}, {3, 4, 5})라면 1의 값을 {0, 1, 2}에서 찾으면 2이고, 반환 범위의 2번째 값은 4이므로 4가 반환
SUMPRODUCT(값 배열, 대응 값 배열)	값 배열의 원소들과, 대응 값 배열의 원소들을 곱하여 반환(내적 느낌)	SUMPRODUCT({1, 2, 3}, {4, 5, 6})의 경우 (1×4) + (2×5) + (3×6)의 값 반환
WORKDAY(시작일, 날짜, [공휴일])	시작일에서 날짜만큼의 휴가 후 출근날짜를 구함	공휴일은 제외됨
NETWORKDAYS(시작일, 마감일, [공휴일])	시작일에서 마감일까지의 전체 작업날짜를 구함	공휴일은 제외됨
TEXT(값, 형식)	해당 값을 형식에 맞도록 출력	TEXT(7, "000")의 경우 007로 출력됨
EDATE(날짜, 개월수)	해당 날짜에서 월수가 지난 날짜 반환	EDATE(2021-04-01, 1)은 21년 5월 1일 반환 EDATE(2021-04-01, -1)은 3월 1일 반환
PROPER(문자)	문자의 첫 문자를 대문자로 변환	PROPER("dddd")는 Dddd 반환
SEARCH(찾을 문자, 텍스트, 시작 값)	텍스트의 시작 값에서부터 찾을 문자를 찾아 인덱스 반환	대소문자 구분 X
TRIM(문자)	문자의 양 끝 공백을 제거	TRIM("  DD  ")의 경우 DD를 반환
REPLACE(이전 텍스트, 시작 인덱스, 변환할 개수, 새로운 텍스트)	이전 텍스트의 시작 인덱스에서, 변환 개수만큼 새로운 텍스트로 치환	변환할 개수를 0으로 지정하면 삽입
IFERROR(조건, 오류시 반환값)	조건이 오류인 경우 반환값을 반환	조건이 오류가 아닌 경우는 해당 조건의 반환값을 그대로 출력
COUNTIF(범위, 조건)	조건에 맞는 범위 내 개수 반환	COUNTIF($A$3:A3, "T*")와 같이 주로 사용 앞의 절대참조는 시작점(누적개수), "T*"는 T로 시작하는 문자를 의미

 

꿀팁

---

시간

시간은 00:00부터 23:59까지 24시간을 0부터 1까지의 숫자로 환산하여 저장됩니다. (일반 형식 기준)

예를들어 12:00의 경우 0과 1의 중간인 0.5로 계산될 수 있습니다.

따라서 12:00가 입력되어 있는 셀을 A1이라고 하면, =(A1 = 0.5)라는 수식을 입력하면 TRUE가 반환됩니다.

 

 

문자 처리

엑셀에서 문자 처리는 큰따옴표(" ")를 활용합니다. A1셀에 201이 입력되어 있다고 가정하겠습니다.

RIGHT나 LEFT함수는 글자를 반환하므로, RIGHT(A1, 1) = 1이라는 함수는 FALSE를 반환합니다.

RIGHT(A1, 1) = "1"이 TRUE를 반환합니다.

 

절대 참조 변경

단축키 F4를 활용하면 $F$4, $F4, F$4, F4와 같이 절대참조의 형태를 변경할 수 있습니다.

 

수식에 문자 입력하기

=으로 시작하는 수식의 경우, 문자를 입력하기 위해 &(앰퍼센드, ampersand) 기호를 활용합니다.

'[결과 값]매'와 같이 수식의 결과 뒤에 "매"라는 글자를 입력하고 싶은 경우 =[수식]&"매"와 같이 작성합니다.

 

같지 않다

엑셀에서 같다는 등호('=')로 표현하며, 같지 않다는 부등호를 서로 마주보게('<>') 표현합니다.

예를들어 '대리와 같지 않다'라는 조건을 표현할 때에는 "<>대리" 로 표현합니다.

 

함수 내부의 [조건] 자리에 함수 중첩하기

COUNTIF(범위, 조건) 함수의 [조건] 자리에 AVERAGE 함수를 중첩하고, 이 값보다 큰 값을 찾는다고 가정하겠습니다.

COUNTIF(범위, ">="&AVERAGE(범위))와 같이 표현해주어야 하는데,

>=는 큰 따옴표를 활용해 문자로, 이에 적용되는 함수는 &로 이어주어야 올바른 표현입니다.

 

배열 수식

배열 수식은 CTRL+SHIFT+ENTER를 이용해 완성하며, SUM((조건)*(조건))등과 같이 표현됩니다.

*는 AND와 유사한 역할을 하며, 개수를 세는 경우는 (조건 )*1와 같이 표현할 수 있습니다.

(조건)의 결과가 참이면 1, 거짓이면 0이 나온다는 사실을 이용한 것입니다.

또한 SUM(IF(조건, 참값))등과 같이 표현하기도 합니다.