9. 데이터 유형 및 참조 영역 변환하기

1. 데이터 유형 검사하기

  1) IS 함수의 개요

○ 정의 : IS로 시작되는 정보 함수. 값의 유형을 검사하고 그 결과에 따라 TRUE · FALSE를 반환하는 함수

○ 구문 =함수명(Value) 

○ 인수

▷ Value : 필수 요소, 테스트 할 값, 빈 셀, 오류, 논리값, 텍스트, 숫자, 참조 값 또는 이러한 항목을 가리키는 이름

  2) IS 함수의 종류

함수	TRUE를 반환하는 경우
ISBLANK	값이 빈 셀을 참조하는 경우
ISERR	값이 #N/A(사용할 수 없는 값)를 제외한 오류 값을 참조하는 경우
ISERROR	값이 임의의 오류 값을 참조하는 경우
ISLOGICAL	값이 논리값을 참조하는 경우
ISNA	값이 #N/A(사용할 수 없는 값) 오류 값을 참조하는 경우
ISNONTEXT	값이 텍스트가 아닌 항목을 참조하는 경우   빈 셀을 참조하는 경우 → TRUE를 반환
ISNUMBER	값이 숫자를 참조하는 경우
ISREF	값이 참조를 참조하는 경우
ISTEXT	값이 텍스트를 참조하는 경우

<사용 예시>

- [G4]셀의 경우 : [F4]셀 데이터 유형이 문자인지 비교 → False → D4/F4 → 259.1

※ G4셀의 함수 구문은 F4가 텍스트면 그대로, 텍스트가 아니면 D4/F4를 가져오라는 수식

- [I4] 셀의 경우 : [H4]셀 데이터가 있는 지 확인 → True → "A" 또는 "B"인지 확인 → True

→ "통과"

※ I4셀은 친절도열 조사(비어있으면 "재조사", A나 B는 "통과", 나머지는 "미달"로 표시하라는 수식)

2. 파생된 참조 영역 사용하기

  1) OFFSET 함수

○ 정의 : 셀 또는 셀 범위에서 지정된 수의 행과 열로 구성되는 범위에 대한 참조를 반환하는 함수 

- 반환되는 참조는 단일 셀 또는 셀 범위 

- 반환할 행 및 열 수 지정 가능

○ 구문 =OFFSET(Reference, Rows, Cols, [Height], [Width])

○ 인수

▷ Reference : 필수 요소, 기본 참조 영역으로 셀 또는 인접한 셀 범위

▷ Rows : 필수 요소, 기본 참조 영역의 첫 행과 출력할 영역의 첫 행 사이의 간격

양수 → 아래로 지정, 음수 → 위로 지정 가능

예) Rows 인수로 5 사용 : 기본 참조 영역보다 5행 아래로 지정됨 

▷ Cols : 필수 요소, 기본 참조 영역의 첫 열과 출력할 영역의 첫 열 사이의 간격

양수 → 오른쪽으로 지정, 음수 →왼쪽으로 지정

예) Cols 인수로 5 사용 : 기본 참조 영역보다 5열 오른쪽으로 지정됨

▷ Height : 선택 요소, 반환되는 참조의 높이(행 수), 양수만 지정 가능

▷ Width : 선택 요소, 반환되는 참조의 너비(열 수), 양수만 지정 가능

○ 특징

▷ Rows 및 Cols 오프셋이 워크시트 가장자리 위를 참조하는 경우 #REF! 오류 값을 반환

▷ Height 또는 Width를 생략하면 높이나 너비가 Reference와 같은 것으로 간주

▷ OFFSET은 셀을 실제로 이동하거나 선택을 변경하지 않으며 단지 참조를 반환

<사용 예시>

- [B19]셀의 경우 : 기준이 되는 A2셀에서 행으로(아래로) 1칸, 열로(오른쪽으로) 1칸 이동한 B3셀부터 높이(아래) 6칸, 폭이(오른쪽) 5칸이 되는 범위를 모두 더하라는 식이다.

3. 행과 열을 바꾸어 표시하기

  1) TRANSPOSE 함수

○ 개념 : 세로 셀 범위 → 가로 범위, 가로 셀 범위→ 세로 범위로 바꾸어 반환하는 함수 

배열 수식으로 입력해야함.

이를 입력하는 범위의 행 및 열 수는 각각 범위의 열 및 행 수와 동일해야 함

워크시트에서 배열의 가로와 세로 방향 변환 가능

○ 구문 =TRANSPOSE(Array)

○ 인수

▷ Array : 필수 요소, 행과 열을 바꿀 워크시트의 셀 범위 또는 배열 

▷ 배열의 첫 번째 행 → 새 배열의 첫 번째 열

▷ 배열의 두 번째 행 → 새 배열의 두 번째 열

<사용 예시>

- [B14:H15]셀의 경우 : [F4:G10] 셀 범위 → 행/열 변환

  

4. 응용하기

○ 동적 범위 이름 지정하기   이름 정의에서 OFFSET 함수를 사용하면 됩니다.    이 때, 포인트가 되는 부분은 셀 범위의 전체 행 수를 지정하는 Height 인수입니다.    여기서는 A열에 제목이 입력되어 있으므로 B열에 데이터가 입력된 셀의 개수를 COUNTA 함수를 사용해서 구한 후 인수로 사용하면 됩니다.    따라서 Reference 인수는 데이터 목록이 시작되는 '$A$3', 시작할 행과 열 위치는 [A3]에서 변경이 없으므로 Rows와 Cols 인수값은 둘 다 '0'이 됩니다. 데이터의 행 수인 Height는 COUNTA($B:$B)로 구하고, Width는 데이터 목록의 열이 모두 8개이므로 '8'이 됩니다.    이 수식은 이름 정의에서 사용할 것이므로 셀 참조는 모두 절대 번지로 지정해야 합니다. 따라서 이름 정의에서 참조 대상으로 사용할 수식은 '=OFFSET($A$3,0,0,COUNTA($B:$B),8)'이 됩니다.

① [수식] 탭을 선택한 후, [정의된 이름] 그룹에서 [이름 정의] 아이콘을 클릭합니다.

② [새 이름] 대화상자에서 '이름'에 '데이터'를 입력하고 '참조 대상'에 '=OFFSET($A$3,0,0,COUNTA($B:$B),8)'을 입력한 후, <확인> 단추를 클릭합니다.

③ 데이터베이스 함수를 사용한 [J9]셀에서 데이터베이스 범위로 입력된 'A3:H16' 부분을 정의된 이름인 '데이터'로 변경합니다.

④ 다음과 같이 15번 데이터를 추가하면 이름으로 정의된 범위가 자동으로 변경되어 추가된 데이터가 수식에 적용되는 것을 확인할 수 있습니다.