[스프레드시트] 수식 활용(배열 수식, 셀참조)

 

오늘 공부할 내용은

​

- 셀 참조

- 배열 수식

 

 

---

수식

​

입력된 전체 수식 보기

<Ctrl> + <~>

​

1) 수식은 등호(=) 나 +,- 기호로 시작

2) 문자열이 수식에 사용될 때에는 “쌍 따옴표” 로 묶음

3) 수식은 같은 워크시트의 다른 셀 / 다른 시트에 있는 셀 / 다른 통합 문서의 시트에 있는 셀을 참조 가능

4) 수식이 입력된 셀 = 수식의 결과 값 / 수식은 수식 입력줄에 표시

​

---

셀 참조

​

​

1) 상대 참조

- 수식을 입력한 셀의 위치가 변동되면 참조가 상대적으로 변경됨 => ex. A1

​

​

​

2) 절대 참조

- 수식을 입력한 셀의 위치와 관계없이 고정된 주소, 참조가 변경X => ex. $A$1

​

​

​

3) 혼합 참조

- 열 고정 혼합 참조 : 열만 절대 참조 적용 ($A1)

- 행 고정 혼합 참조 : 행만 절대 참조 적용 (A$1)

​

​

​

4) 다른 워크시트의 셀 참조

- 다른 워크시트에 있는 셀의 데이터를 참조할 경우 시트 이름과 셀 주소 사이를 느낌표(!) 구분

- 시트 이름에 문자가 있을 경우 ‘작은 따옴표’ 로 묶어줌 => ex. =Sheet!A5

​

​

​

5) 3차원 참조

- 여러 시트의 동일한 셀이나 셀 범위에 대한 참조

- 참조하는 시트가 연속적으로 나열, 셀 주소가 모두 동일할 때 : 첫번째 시트와 마지막 시트 이름을 콜론(;)으로 연결, 셀 주소를 한번만 지정

=> ex. =SUM(Sheet1:Sheet3!B2)

- 배열 수식 사용X

- SUM, AVERAGE, COUNT, MAX 등 함수 가능

​

​

​

6) 다른 통합 문서의 셀 참조

- 다른 통합 문서에 있는 셀의 데이터를 참조할 경우 통합 문서의 이름을 [대괄호] 로 묶어줌 => ex. =[매출현황]Sheet4

​

---

배열 수식

: 동일한 특성을 갖는 데이터들을 일정한 형식에 맞게 나열한 데이터들의 집합

​

<Ctrl> + <Shift> + <Enter> ⇒ {중괄호} 자동 입력

​

1) 배열 인수 각각은 동일한 개수의 행과 열을 가져야함

2) 여러 셀을 범위로 지정, 배열 수식을 입력하면 지정된 범위에 모두 동일한 수식 입력

3) 동시에 입력된 배열 수식은 전체를 이동/삭제 가능, 일부 수정/삭제/이동 불가능

​

​

# 배열수식 조건 입력 방법

1. (조건) * 값을 구할 범위

2. IF (조건, 값을 구할 범위)

​

​

# 배열 상수

- 정수, 실수, 지수형 숫자를 사용 가능

- 배열 상수는 {중괄호} 로 입력하여야함

​

1) 다른 종류의 값을 같은 배열의 상수로 사용 가능 ⇒ 배열 상수 값은 수식X, 상수O

2) 열의 구분 = 쉼표(,) / 행의 구분 = 세미콜론(;)

3 사용할 범위에서 빈칸은 0으로 취급

- 종류 : 숫자, 텍스트, True/False 등의 논리값, #N/A 등의 오류 값 등

- 사용할 수 없는 값 : $, 괄호, %, 길이가 다른 행이나 열, 셀 참조 등

​

​

# 배열 수식 활용

개수
* 조건이 한개	* 조건이 두개 이상
1) {=SUM ((조건) * 1 )} 2) {=SUM (IF (조건, 1) )} 3) {=COUNT (IF (조건, 1) )}	1) {=SUM ((조건) * (조건2) )} 2) {=SUM (IF ( (조건) * (조건2), 1) )} 3) {=COUNT (IF ( (조건) * (조건2), 1) )}
합계
* 조건이 한개	* 조건이 두개 이상
1) {=SUM ((조건) * (합계를 구할 범위) )} 2) {=SUM (IF (조건, 평균을 구할 범위) )}	1) {=SUM ((조건) * (조건2) * 합계를 구할 범위 )} 2) {=SUM (IF ((조건) * (조건2), 평균을 구할 범위) )}
평균
1) {=AVERAGE (IF (조건, 평균을 구할 범위) )}	1) {=AVERAGE (IF ( (조건) * (평균2), 평균을 구할 범위) )}
큰 값
1) {=LARGE ((조건) * K번째로 큰 값을 구할 범위, K) 2) {=LARGE (IF (조건, K번째 큰 값을 구할 범위, K)
INDEX/MATCH
{=INDEX (결과를 구할 범위, MATCH (MAX ( (조건) * 최대값을 구할 범위), (조건) * 최대값을 구할 범위, 열 번호) )}

 

---

 

​

컴퓨터활용능력 1급 필기,

2과목 스프레드시트 '입력 및 편집기능' 배우러가기

[스프레드시트] 입력 및 편집 기능(데이터, 메모삽입, 윗주삽입, 범위지정, 셀포인터, 조건부서식, 사용자지정서식) (tistory.com)

[스프레드시트] 입력 및 편집 기능(데이터, 메모삽입, 윗주삽입, 범위지정, 셀포인터, 조건부서식