1. 데이터와 셀 참조하기

1. 수식 연산자 및 오류 메시지

 

  1) 수식에 사용되는 연산자

 

    ○ 산술 연산자

▷ 기본적인 수학 연산을 실행

▷ 더하기(+), 빼기(-), 곱하기(*), 나누기(/), 지수(^)

 

 

    ○ 비교 연산자

      ▷ 두 값의 크기를 비교하여 TRUE  또는 FALSE 값으로 표시

      ▷ 같다(=), 같지않다(>), 크다(>), 작다(<), 크거나같다(>=), 작거나같다(<=)

 

 

 

    ○ 텍스트 연결 연산자

      ▷ 좌우의 값을 하나의 값으로 연결

      ▷ 문자열 결합(&), 두 셀의 값을 텍스트 형식으로 차례로 표시

 

 

    ○ 참조 연산자

      ▷ 계산에 필요한 셀 범위를 결합

      ▷ 범위 연산(:), 결합 연산(,), 교정 연산(공백)

 

연산자	의미
콜론(:)	두 참조 사이의 모든 셀을 참조  예) A1:C5 → [A1]셀부터 [C5]셀까지의 모든 셀 참조
쉼표(,)	여러 참조를 하나의 참조로 결합  예) A2,B3,C5 → [A2]셀, [B3]셀, [C5]셀 참조
공백( )	두 개의 참조에서 공통되는 셀에 대한 참조  예) A1:D7 C5:F10 → [A1:D7]과 [C5:F10]의 공통 범위 [C5:D7] 참조

 

    ○ 연산자 우선순위

 

구분	연산자
참조 연산자	콜론(:) → 공백( ), → 쉼표 (,)
산술 연산자	음수(-) → 백분율(%) → 지수(^) → 곱하기 및 나누기 (*,/) →   더하기 및 빼기 (+,-)
텍스트 연결 연산자	&
비교 연산자	모든 비교 연산자는 우선 순위가 동일

 

 

  2) 수식 오류 메시지의 종류와 원인

오류 메시지	발생 원인	대처 방법
DIV/0!	숫자를 0으로 나누는 경우 나타나는 오류 DIV는 DIVIDE의 약자	나누는 숫자를 0이 아닌 다른 숫자로 변경
#N/A	함수나 수식에 값을 사용할 수 없는 경우 주로 LOOKUP 함수 계열 등의 함수식에서 찾을 값이 없으면 나타나는 오류	찾는 값을 바꾸거나 참조 범위의 값을 변경
#NAME?	수식의 텍스트를 인식하지 못하는 경우 주로 함수명을 잘못 입력하거나 인수가 문자인데 겹따옴표("") 표시를 하지 않은 경우 발생	사용된 함수나 이름의 오타 여부 확인
#NUM!	함수의 인수나 수식이 잘못된 형식으로 입력된 경우	함수의 형식을 확인하여 알맞은 형식으로 수정
#REF!	셀 참조가 유효하지 않은 경우 주로 참조했던 셀이 삭제되었을 때 표시	참조된 셀의 삭제 여부 또는 공백이 아닌지 확인
#NULL!	교차하지 않는 두 영역을 교차하는 것으로 지정한 경우	참조 범위를 다시 지정
#VALUE!	인수나 피연산자의 형식을 잘못 사용한 경우 논리값 또는 숫자가 필요한 수식에 텍스트를 입력한 경우 배열 수식 입력 후, [Ctrl]+[Shift]+[Enter]를 누르지않은 경우	인수의 데이터 형태와 함수의 종류 등을 확인 후 수정
#####	열 너비가 셀에 있는 값보다 좁은 경우	열 너비 확대

 

 

2. 셀 참조

 

  1) 셀 참조

 

    ○ 셀 참조란?

 ▷ 워크시트의 다른 셀에 있는 값을 가져다 수식을 계산할 때, 셀 값을 직접 수식에 입력하지 않고 'A1'과 같이 열 명과 행 번호로 이루어진 셀 주소를 사용하는 것

 

    ○ 다른 시트, 다른 통합 문서의 셀 참조

      ▷ 다른 워크시트의 셀 참조 : 셀 참조 앞에 워크시트의 이름과 느낌표를 입력 

Sheet1!C5

 

▷ 다른 통합 문서의 셀 참조 : 대괄호 안에 통합 문서 이름, 워크시트 이름과 느낌표를 셀 참조 앞에 입력

[자료정리.xlsx]Sheet1!C5

 

  2) 셀 참조의 유형

 

    ○ 상대참조

     ▷ 가장 기본적인 형태, 유동적으로 셀 주소를 참조하는 방식

      ▷ 수식을 다른 곳으로 복사할 경우 상대 위치에 따라 자동으로 셀 주소 변경

 

    ○ 절대참조

     ▷ 가장 기본적인 형태, 유동적으로 셀 주소를 참조하는 방식

      ▷ 수식을 다른 곳으로 복사하더라도 셀 주소는 변경되지 않음  

 

    ○ 혼합참조

     ▷ 상대참조와 절대참조가 섞인 방식

      ▷ 열 명이나 행 번호 둘 중 하나의 앞에만 '$' 기호를 붙여서 변하지 않게 하는 참조 방식

  

    ○ 참조변환

     ▷ 셀 주소 직접 입력 시 : '$' 문자를 입력

      ▷ 자동 변환 : [F4]키 사용 (A1→$A$1→A$1→$A1→A1 순으로 변경)

 

 

3. 중첩 함수 및 배열 수식

 

  1) 중첩 함수의 사용

 

    ○ 함수 목록을 사용하는 방법

▷ [이름 상자]가 함수·수식 입력 시 [함수 목록 상자]로 변경
▷ 수식 입력 중, [함수 인수] 대화상자가 나타난 상태에서 다른 함수를 중첩으로 사용할 경우 → [함수 목록 상자] 사용

 

    ○ 함수 인수 대화상자에서 표시 함수 변경

▷ [수식 입력줄]에서 클릭하는 함수의 [함수 인수] 대화상자 표시

 

  2) 배열 수식

    ○ 배열 수식이란?

▷ 하나 이상의 값 집합에 대해 여러 가지 계산을 수행하고, 하나 또는 여러 개의 결과를 반환하는 수식([Shift]+[Ctrl]+[Enter] 키를 눌러야 하므로 SCE 함수라고도 한다)

   ○ 배열 수식 입력 시 주의 사항

▷ 배열 수식의 앞뒤에는 중괄호가 표시

▷ 수식 입력 후 [Shift]+[Ctrl]+[Enter] 키를 누르면 자동으로 나타남

▷ 배열 수식에는 표준 수식 구문 사용 가능

▷ 수식은 모두 등호로 시작

▷ 임의의 기본 제공 Excel 함수를 배열 수식에 사용 가능

▷ 한꺼번에 구한 배열 수식은 일부만 수정하거나 삭제 불가

▷ 일정 범위에 입력한 배열 수식을 수정·삭제 → 해당 범위를 지정한 후 수정·삭제

 

  3) 배열 수식의 사용 예

▷ 사용 수식 및 설명

- 배열 수식 : {=B2:B9+B2:B9*C1:H1}

- 설명 : 각 셀마다 '원가+원가*마진율'을 계산하는 수식. 즉, [C2]셀에 적용되는 실제 수식은 =B2+B2*C1 임

▷ 작성 순서

① 배열 수식을 입력할 셀 범위 선택 

 

② 사용할 수식 입력 

 

③ [Shift]+[Ctrl]+[Enter]키를 눌러 배열 수식으로 입력 확정