학습(공부)하는 블로그

1. 데이터 유형 검사하기

  1) IS 함수의 개요

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

○ 구문 =함수명(Value) 

○ 인수

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

  2) IS 함수의 종류

<사용 예시>

- [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($A$3,0,0,COUNTA($B:$B),8)'을 입력한 후, <확인> 단추를 클릭합니다.

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

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

1. 참조 표에서 찾기

  1) HLOOKUP 함수

○ 개요 : 테이블의 첫 행에 있는 값 또는 값의 배열을 검색한 다음 테이블이나 배열에서 지정한 행으로부터 같은 열에 있는 값을 반환하는 함수 

○ 구문 =HLOOKUP(Lookup_value, Table_array, Row_index_num, [Range_lookup])

○ 인수

▷Ÿ Lookup_value : 필수 요소, 테이블의 첫 행에서 찾을 값, 참조, 텍스트 문자열

▷Ÿ Table_array : 필수 요소, 데이터를 찾을 정보 테이블, 범위에 대한 참조나 범위 이름을 사용

▷Ÿ Row_index_num : 필수 요소, 반환하려는 값이 있는 Table_array의 행 번호

▷Ÿ Range_lookup : HLOOKUP을 사용하여 정확하게 일치하는 값을 찾을지 아니면 근사값을 찾을지를 지정하는 논리값

- TRUE, 값을 생략 → 근사값 반환

- FALSE → 정확하게 일치하는 값 반환

  2) VLOOKUP 함수

○ 개요 : 테이블의 첫 열에 있는 값 또는 값의 배열을 검색한 다음 테이블이나 배열에서 지정한 열으로부터 같은 행에 있는 값을 반환하는 함수 

○ 구문 =VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])

○ 인수

▷ Lookup_value : 필수 요소, 테이블의 첫 열에서 찾을 값, 참조, 텍스트 문자열

▷ Table_array : 필수 요소, 데이터를 찾을 정보 테이블, 범위에 대한 참조나 범위 이름을 사용

▷ Col_index_num : 필수 요소, 반환해야 하는 값이 있는 Table_array 인수의 열 번호

▷ Range_lookup : VLOOKUP을 사용하여 정확하게 일치하는 값을 찾을지 아니면 근사값을 찾을지를 지정하는 논리값

- TRUE, 값을 생략 → 근사값 반환

- FALSE → 일치하는 값 반환

<사용 예시>

- [B5]셀의 경우 : [A5]셀 값을 표의 첫 열인 [H5:H9]셀에서 검색

→ [H5]셀 → 동일 행의 두 번째 열 값인 '아현점'표시

- [F4]셀의 경우 : 표 범위인 [K4:O9]셀 범위에서 6행과 3열이 교차하는 위치

→ [M9]셀 값인 '40000' 표시

2. 행 · 열의 위치를 검색하여 찾기

  1) INDEX 함수

○ 개요 :  테이블이나 범위에서 값 또는 값에 대한 참조를 반환하는 함수

○ 형식 : 배열형과 참조형

(1) 배열형 : 행과 열 번호 인덱스로 선택한 배열이나 테이블 요소의 값을 반환

▷ 구문 =INDEX(Array, Row_num, [Column_num])

▷ 인수

- Array : 필수 요소, 배열 상수나 셀 범위

-Ÿ Row_num : 값을 반환할 배열의 행 선택

-Ÿ Column_num : 값을 반환할 배열의 열 선택

-Ÿ Row_num 인수와 Column_num 인수는 둘 중 하나는 반드시 지정해야함

(2) 참조형 : 특정 행과 열이 교차되는 위치의 셀 참조를 반환하며, 참조가 인접하지 않은 영역으로 이루어진 경우에는 찾아볼 영역 선택 가능

▷ 구문 =INDEX(Reference, Row_num, [Column_num], [Area_num])

▷ 인수

-Ÿ Reference : 필수 요소, 한 개 이상의 셀 범위에 대한 참조

-Ÿ Row_num : 참조 범위에서 참조를 반환할 행 번호

-Ÿ Column_num : 참조 범위에서 참조를 반환할 열 번호

-Ÿ Area_num : 선택 요소, row_num과 column_num이 교차하는 셀을 반환할 참조 범위 선택

  2) MATCH 함수

○ 개요 : 셀 범위에서 지정된 항목을 검색한 다음 범위 내에서 해당 항목의 상대 위치를 반환하는 함수

○ 구문=MATCH(Lookup_value, Lookup_array, [Match_type])

○ 인수

▷Ÿ Lookup_value :  필수 요소, Lookup_array에서 찾으려는 값

            숫자, 텍스트, 논리값 등의 값 또는 숫자, 텍스트, 논리값에 대한 셀 참조

▷Ÿ Lookup_array : 필수 요소, 검색할 셀 범위

▷Ÿ Match_type 

- 선택 요소, 숫자 -1, 0, 1

- Lookup_array의 값을 사용하여 Lookup_value를 찾는 방법을 지정하는 숫자

- 기본값은 1

<사용 예시>

- [D4]셀의 경우 : [C4]셀의 값을 [J4:J9]셀 범위에서 검색 → [J9]셀

 → 6번째에 위치 → 6

- [E4]셀의 경우 : [B4]셀의 값을 [K3:O3]셀 범위에서 검색 → [M3]셀

 → 3번째에 위치 → 3

- [F4]셀의 경우 : 표 범위인 [K4:O9]셀 범위에서 6행과 3열이 교차하는 위치

 → [M9]셀 값인 '40000' 표시

3. 행 · 열 정보를 이용해 배열로 찾기

  1) COLUMN 함수

○ 개요 : 참조의 열 번호를 반환하는 함수

○ 구문 =COLUMN([Reference])

○ 인수

▷Ÿ Reference : 선택 요소로 열 번호를 반환하려는 셀 또는 셀 범위

                     생략하면 현재 셀의 열 번호를 반환

  2) ROW 함수

○ 개요 : 참조의 행 번호를 반환하는 함수

○ 구문 =ROW([Reference])

○ 인수

▷Ÿ Reference : 선택 요소로 행 번호를 반환하려는 셀 또는 셀 범위

                     생략하면 현재 셀의 행 번호를 반환

  3) LOOKUP 함수

○ 개요 : 한 개의 행이나 한 개의 열로 이루어진 범위에서 값 또는 값에 대한 참조를 반환하는 함수

○ 형식 : 벡터형과 배열형

(1) 벡터형 : 한 개의 행이나 한 개의 열로 이루어진 범위에서 값을 찾고, 한 개의 행이나 한 개의 열로 이루어진 두 번째 범위의 같은 위치에서 값을 반환

▷ 구문 =LOOKUP(Lookup_value, Lookup_vector, [Result_vector])

▷ 인수

-Ÿ Lookup_value : 필수 요소, LOOKUP 함수를 사용하여 첫 번째 벡터에서 검색하려는 값

                        숫자, 텍스트, 논리값, 값을 참조하는 이름이나 값

-Ÿ Lookup_vector : 필수 요소, 행이나 열을 한 개만 포함하는 범위로 텍스트, 숫자 또는 논리값

- Result_vector : 선택 요소, 행이나 열을 한 개만 포함하는 범위, Lookup_vector와 크기가 같아야 함

(2) 배열형 : 배열의 첫 번째 행이나 열에서 지정된 값을 찾고, 배열의 마지막 행이나 열의 같은 위치에서 값을 반환, 찾으려는 값이 배열의 첫 번째 행이나 열에 있는 경우 사용(일반적으로 LOOKUP함수의 배열형 대신 HLOOKUP 또는 VLOOKUP 함수를 사용)

▷ 구문 =LOOKUP(Lookup_value, Array)

▷ 인수

-Ÿ Lookup_value : 필수 요소, 배열에서 찾으려는 값으로 숫자, 텍스트, 논리값, 값을 참조하는 이름이나 참조

-Ÿ Array : 필수 요소, Lookup_value와 비교할 텍스트, 숫자 또는 논리값을 포함하는 셀 범위

<사용 예시>

- [A4]셀의 경우 : [B4]셀의 값을 사원명부 성명란[B4:B15]셀 범위에서 검색

                  → [B4]셀확인 → 일련번호 확인 → 1001

- [C4~E4]셀의 경우 : [A4]셀의 값을  사원명부[A4:H15]셀 범위에서 검색

 → 해당셀이 있는 열번호를 확인하여 열번호(3, 4, 5)만큼 오른쪽에 있는 데이터를 가져옴 → 인사팀, 사원, 2

- [B7~D7]셀의 경우 : 위와 같은 방법으로 데이터 검색

 → 해당셀이 있는 열번호를 확인하여 열번호+3 만큼 오른쪽에 있는 데이터를 가져옴 → 1200000, -, 80000

1. 데이터 형식 및 단위 변환하기

  1) ROMAN 함수

○ 개요 : 아라비아 숫자를 텍스트인 로마 숫자로 변환하는 함수

○ 구문 =ROMAN(Number, [Form])

○ 인수

▷ Nunmber : 필수 요소, 변환할 아라비아 숫자

▷ Form : 선택 요소, 로마 숫자의 스타일을 지정하는 값.

- 고전 스타일에서 단순 스타일에 이르는 다양한 값 사용 가능

- form 값이 커질수록 스타일이 간결해짐

○ Form 인수에 따른 스타일 비교

  2) CONVERT 함수

○ 개요: 다른 단위 체계의 숫자로 변환하는 함수

예) 마일 단위의 거리 → 킬로미터 단위

○ 구문 =CONVERT(Number, From_unit, To_unit)

○ 인수

▷ Nunmber : 필수 요소, 변환할 From_unit의 값

▷ From_unit : 필수 요소, 숫자의 단위. 인용 부호로 묶은 텍스트 값 사용

▷ To_unit : 필수 요소, 결과 단위. 인용 부호로 묶은 텍스트 값 사용

○ From_unit 및 To_unit에 사용할 수 있는 단위 텍스트

<사용 예시>

- E13셀은 왼쪽 출시일을 기준으로 순위를 정해서 로마 숫자로 변환하라는 수식이다.

- I13셀부터 K13셀까지는 F13셀부터 H13셀까지의 제품크기의 단위가 cm인 것을 inch로 변환하라는 수식이고, M13셀은 L13셀의 Kg 중량을 온스로 변환하라는 수식이다.

2. 나눗셈으로 몫과 나머지 구하기

  1) MOD 함수

○ 개요: 숫자를 제수로 나눈 나머지를 반환하는 함수, 결과는 제수와 같은 부호를 가짐

○ 구문 : =MOD(Number, Divisor)

○ 인수

▷ Nnmber : 필수 요소, 나머지를 구할 숫자

▷ Divisor : 선택 요소, number를 나눌 제수

  2) QUOTIENT 함수

○ 개요 : 나눗셈 몫의 정수 부분을 반환하는 함수, 나눗셈을 하고 나머지를 버릴 때 사용

○ 구문 =QUOTIENT(Numerator, Denominator)

○ 인수

▷ Numerator : 필수 요소, 피제수 (나눠지는 수)

▷ Denominator : 필수 요소, 제수 (나누는 수)

<사용 예시>

- H15셀은 포인트 합계를 1,000으로 나누었을 때 몫을 가져오라는 수식이다. 

- I15셀은 위와는 달리 포인트 합계를 1,000으로 나누었을 때 남는 나머지를 가져오라는 수식이다.

- 포인트 합계 = 쿠폰 지급 + 예상 잔액

3. 정수로 변환하기

  1) INT 함수

○ 개요 : 가장 가까운 정수로 내림하는 함수

○ 구문 =INT(Number)

○ 인수

▷ Number : 필수 요소, 정수로 내림할 실수

  2) 짝수 또는 홀수를 반환하는 함수

○ 개요

▷ EVEN 함수 : 가장 가까운 짝수로 올림한 수를 반환

▷ ODD 함수 : 가장 가까운 홀수로 올림한 숫자를 반환

※ 두 함수 모두 음수가 대상일 경우 내림하여 반환하게 됨

○ 구문 =함수명(Number)

○ 인수

▷ Number : 필수 요소, 올림 또는 내림할 값. Number의 부호와 상관없이 0에서 먼 방향으로 올림

  3) 특정 배수로 올림 또는 내림하는 함수

○ 개요

▷ CEILING 함수 : 양수 → 지정한 수의 배수로 올림을 하여 가장 가까운 수 반환

                             음수 → 지정한 수의 배수로 내림을 하여 가장 가까운 수 반환

▷ FLOOR 함수 : 양수 → 지정한 수의 배수로 내림을 하여 가장 가까운 수 반환 

                          음수 → 지정한 수의 배수로 올림을 하여 가장 가까운 수 반환

○ 구문 =함수명(Number, Significance)

○ 인수

▷ Number : 필수 요소, 올림 또는 내림할 값

▷ Significance  필수 요소, 배수의 기준이 되는 수

<사용 예시>

- D13셀은 풀어서 보면 int(2800-2800*6.81%)=int(2800-190.68)=int(2609.32)=2609가 된다.

- E13셀 역시 풀어서 보면 2800-even(2800*6.81%)=2800-even(190.68)=2800-192=2608이 된다.

- I13셀은 H13에 있는 숫자를 500의 배수로 올림을 하라는 식이므로 4,821,410은 4,821,500으로 변하게 된다.

1. 날짜와 시간 입력 및 정보 분리하기

  1) 인수가 없는 TODAY, NOW 함수

○ 개요

▷ TODAY 함수 : 현재 날짜의 일련 번호를 반환하는 함수. 결과값은 날짜 서식으로 변경됨

▷ NOW 함수 : 현재 날짜와 시간의 일련 번호를 반환하는 함수. 결과값은 제어판의 '국가별 날짜 및 시간 설정'과 동일한 형식으로 변경

○ 구문 =함수명()

  2) 날짜 정보를 분리하는 함수

○ 개요

▷ YEAR 함수 : 일련 번호로 나타낸 날짜에서 연도를 반환하는 함수. 연도는 1900~9999 사이의 정수로 제공

▷ MONTH 함수 : 일련 번호로 나타낸 날짜에서 월을 반환하는 함수. 월은 1(1월)~12(12월) 사이의 정수로 제공

▷ DAY 함수 : 제공된 날짜에서 일을 반환하는 함수. 일은 1~31 사이의 정수로 제공

○ 구문 =함수명(Serial_number)

○ 인수

▷ Serial_number : 필수 요소, 년 · 월 · 일을 구할 날짜

▷ 날짜 : DATE 함수, 다른 수식, 함수의 결과로 입력

<사용 예시>

- I2셀은 오늘이 속하는 달을 추출해내는 수식이다.

- 아래 D14, E14, F14셀은 B14셀의 날짜에서 생년, 생월, 생일을 추출해내고, G14셀은 오늘이 속하는 연도에서 생년을 뺀 나이를 계산하는 수식이다.

  3) 시간 정보를 분리하는 함수

○ 개요

▷ HOUR 함수 : 시간 값의 시를 반환하는 함수, 시간은 0(오전 12:00)~23(오후 11:00)사이의 정수로 제공

▷ MINUTE 함수 : 시간 값의 분을 반환하는 함수, 분은 0~ 59사이의 정수로 제공

▷ SECOND 함수 : 시간 값의 초를 반환하는 함수, 초는 0~ 59사이의 정수로 제공

○ 구문 =함수명(Serial_number)

○ 인수

▷ Serial_number : 시, 분, 초를 계산할 시간 값 

▷ 따옴표로 묶은 텍스트 문자열(예: "6:45 PM") ,  십진수(6:45 PM을 나타내는 0.78125) 다른 수식이나 함수의 결과(예: TIMEVALUE("6:45 PM")) 입력 가능

<사용 예시>

- F18셀은 퇴근시간에서 출근시간을 뺀 뒤에 시간만 가져오는 수식이다.

- I9셀은 왼쪽 표에서 '한지연'의 근무시간만 추출하여 합해라는 수식이다.

2. 날짜 간격 계산하기

  1) DATEDIF 함수

○ 개요 : 두 날짜 사이의 간격을 계산하는 함수

○ 구문 =DATEIF(시작일,종료일,"단위")

○ 인수

▷ Y → 년, M → 월, D → 일 

▷Ÿ YM : 년을 빼고 월만 구하는 경우

▷ MD : 월까지 빼고 일자 사이의 간격만 구하는 경우

<사용 예시>

- D14셀과 E14셀은 입사일부터 B3셀에 있는 기준일까지의 년수와 개월수를 구하는 수식이다. 여기서 주의할 점은 개월수의 경우 년수를 고려하지 않은 숫자다.

- F14셀의 수식을 보면 인수에 "YM"과 "MD"를 사용하고 있다. 이는 월 단위를 계산할 때 년수를 뺀 나머지 월만 구하고, 일 단위 역시 월까지는 뺀 나머지 일자를 구하라는 수식이다. 연결 연산자 &를 사용한 것에 주목하자.

  2) NETWORKDAYS 함수

○ 개요 : Start_date와 End_date 사이의 전체 작업 일수를 반환하는 함수. 작업 일수에 주말과 휴일은 포함되지 않음

○ 구문 =NETWORKDAYS(Start_date, End_date, [Holidays])

○ 인수

▷Ÿ Start_date : 필수 요소, 시작 날짜

▷Ÿ End_date : 필수 요소, 끝 날짜

▷Ÿ Holidays : 선택 요소, 국경일·공휴일·임시 휴일 등 작업 일수에서 제외되는 한 개 이상의 날짜 목록

<사용 예시>

- C15셀은 작업착수일부터 2011-6-30 까지 작업일수를 구하라는 수식이다. 여기서 주의할 점은 두 날짜 사이에 있는 주말(토요일, 일요일)은 자동으로 제외하지만, 국경일·공휴일·임시 휴일 등은 제외할 대상으로 지정해줘야 한다는 점이다.

  3) WORKDAY 함수

○ 개요 : 특정 일(시작 날짜)로부터 지정된 작업 일수의 이전 또는 이후에 해당하는 날짜를 반환하는 함수.  작업 일수에 주말과 휴일은 포함되지 않음

○ 구문 =WORKDAY(Start_date, Days, [Holidays])

○ 인수

▷Ÿ Start_date : 필수 요소, 시작 날짜

▷Ÿ Days : 필수 요소, Start_date 전이나 후의 주말이나 휴일을 제외한 날짜 수

▷Ÿ Holidays : 선택 요소, 국경일·공휴일·임시 휴일 등 작업 일수에서 제외되는 한 개 이상의 날짜 목록

<사용 예시>

- F11셀은주문일자(2011-9-7)부터 배송일수(3일) 마감일이 언제인지 계산하는 수식이다. 여기서도 마찬가지로 주말은 자동으로 제외하지만 제외해야 할 국경일 등은 따로 지정해야 한다.

- 9월 7일부터 3일 이내라면 주말과 공휴일을 감안하지 않는다면 9월 10일이 되어야 한다. 하지만 주말(9월10일 토요일)과 공휴일(추석연휴)이 있어 3일이 늘어난 9월 13일이 되는 것이다.

3. 날짜, 주, 요일의 일련번호 구하기

  1) 날짜를 나타내는 일련 번호를 반환하는 함수

○ DATE 함수

▷ 개요 : 특정 날짜를 나타내는 일련 번호를 반환하는 함수

▷ 구문 =DATE(Year,Month,Day)

▷ 인수

- Year : 필수 요소, 년도를 나타내는 한 자리에서 네 자리 숫자

- Month : 필수 요소, 1월에서 12월 사이의 월을 나타내는 양의 정수나 음의 정수

-Ÿ Day : 필수 요소, 1일에서 31일 사이의 일을 나타내는 양의 정수나 음의 정수

○ DATEVALUE 함수

▷ 개요 : 텍스트로 저장된 날짜를 Excel에서 날짜로 인식할 수 있는 일련 번호로 변환하는 함수 

▷ 구문 =DATEVALUE(Date_text)

▷ 인수

-Ÿ Date_text :필수 요소

· 날짜를 Excel 날짜 형식으로 표현하는 텍스트

· 날짜를 Excel 날짜 형식으로 표현하는 텍스트가 들어 있는 셀 참조

<사용 예시>

- C13셀은 주민등록번호에서 생년월일을 텍스트로 추출한다.

- G13셀은 C13셀에 추출한 생년월일 텍스트를 날짜로 인식할 수 있도록 변환한 수식이다.

- H13셀은 사원번호에서 입사일을 추출해내는 수식이다.

  2) 주 번호와 요일 번호를 반환하는 함수

○ WEEKNUM 함수

▷ 개요 : 특정 날짜의 주 번호를 반환하는 함수

▷ 구문 =WEEKNUM(Serial_number,[Return_type])

▷ 인수

- Serial_number : 필수 요소, 해당 주에 속하는 날짜

· 날짜는 DATE 함수를 사용하거나 다른 수식 또는 함수의 결과로 입력

-Ÿ Return_type : 선택 요소, 주의 시작 요일을 결정하는 숫자

· 1 또는 생략하면 일요일, 2이면 월요일

○ WEEKDAY 함수

▷ 개요 : 날짜에 해당하는 요일을 반환하는 함수, 기본적으로 요일은 1(일요일)~7(토요일) 사이의 정수로 제공

▷ 구문 =WEEKDAY(Serial_number,[Return_type])

▷ 인수

-Ÿ Serial_number : 필수 요소, 찾을 날짜를 나타내는 일련 번호

· 날짜는 DATE 함수를 사용하거나 다른 수식 또는 함수의 결과로 입력

- Return_type : 선택 요소, 반환 값의 형식을 결정하는 숫자

§ 1 또는 생략 : 1(일요일)에서 7(토요일) 사이의 숫자

§ 2 : 1(월요일)에서 7(일요일) 사이의 숫자

§ 3 : 0(월요일)에서 6(일요일) 사이의 숫자

<사용 예시>

- A11셀은 B11셀의 날짜가 몇 주에 해당되는 지 알려주는 수식이다.

- C11셀은 B11셀의 날짜에 해당되는 요일을 추출하는 수식인데, 2번 타입으로 했기 때문에 일요일인 7번으로 반환된다.

1. 조건에 따라 다른 계산하기

  1) IF 함수

○ 개요 : 지정된 조건이 TRUE일 때와 FALSE일 때 각각 다른 값을 반환하는 함수

○ 구문 =IF(logical_test, [Value_if_true], [Value_if_false])

○ 인수

▷ Logical_test : TRUE나 FALSE로 평가될 수 있는 임의의 값 또는 식

▷ Value_if_true : Logical_test 인수가 TRUE인 경우에 반환할 값

▷ Value_if_false : Logical_test 인수가 FALSE인 경우에 반환할 값

  2) IFERROR 함수

○ 개요 : 수식에서 오류가 발생하면 사용자가 지정한 값을 반환하고, 오류가 발생하지 않는 경우에는 수식 결과를 반환하는 함수

○ 구문 =IFERROR(Value, Value_if_error)

○ 인수

▷ Value : 오류를 검사할 인수

▷ Value_if_error: 수식에서 오류가 발생할 경우에 반환할 값

<사용 예시>

- D18 셀은 상품코드에서 맨 오른쪽 숫자가 1이면 "A형"으로 분류하고, 2이면 "B형"으로 분류하고 1과 2가 아니면 "C형"으로 분류하하는 수식이다.

- G18셀은 옆에 있는 입하수×단가의 계산 값이 구해지면 그 값을 표시하고, 계산 값이 구해지지 않고 애러가 나면 입하수에 있는 셀의 내용을 가져오도록 하는 수식이다.

2. 여러 조건을 한꺼번에 비교하기

  1) AND 함수

○ 개요 : 모든 인수가 TRUE인 경우만 TRUE를 반환하고 인수 중 하나라도 FALSE가 있으면 FALSE를 반환하는 함수

○ 구문 =AND(Logical1, [Logical2], ...)

○ 인수

▷ Logical1 : 테스트를 통해 TRUE 또는 FALSE로 판가름할 수 있는 첫째 조건

▷ Logical2, logical3, ... : 테스트를 통해 TRUE 또는 FALSE로 판가름할 수 있는 추가 조건

▷ 논리식(Logical) : 1~255까지 지정 가능한 논리값, 배열, 참조 등

  2) OR 함수

○ 개요 : TRUE인 인수가 하나라도 있으면 TRUE를 반환하고 모든 인수가 FALSE인 경우만 FALSE를 반환

○ 구문 :=OR(Logical1, [Logical2], ...)

○ 인수 : AND 함수와 동일

<사용 예시>

- I13셀은 친절도가 A 또는 B인 경우에만 "양호"라고 하고 나머지는 모두 '불량"으로 표시하라는 함수다.

- J13셀은 남풉기한(일)이 15일 미만이고, 일일납품수는 200이상, 그리고 불량률이 10%미만인 경우에는 "통과"라고 하고, 그 나머지는 모두 "미달"로 표시하라는 수식이다.

3. 번호에 따라 다른 계산하기

  1) CHOOSE 함수

○ 개요 : Index_num을 사용하여 인수 목록에서 값을 반환하는 함수

○ 구문 =CHOOSE(Index_num, Value1, [Value2], ...)

○ 인수

▷ Index_num : 선택할 값 인수 지정. 1 ~ 254 사이의 숫자, 수식 또는 1~ 254 사이의 번호가 들어 있는 셀에 대한 참조

▷ Value1 : 필수 요소. 1~ 254개까지 지정 가능한 숫자, 셀 참조, 정의된 이름, 수식, 함수, 텍스트

▷ Value2, ... : 선택 요소

<사용 예시>

- B13셀은 제품코드에서 맨 끝의 숫자를 기준으로 1은 "WHITE", 2는 "BLACK", 3은 "GRAY", 4는 "RED", 그리고 5는 "BLUE"로 표기하라는 수식

1. 조건에 맞는 셀의 개수 구하기

  1) COUNTIF 함수

○ 개요 : 지정된 단일 조건에 부합하는 범위 내의 셀 개수를 세는 함수
○ 구문 =COUNTIF(Range, Criteria)
○ 인수

▷ Range : 필수 요소, 개수를 세려는 한 개 이상의 셀 (숫자, 숫자가 들어 있는 이름, 배열, 참조 포함) → 빈 셀과 텍스트 값은 무시됨
▷ Criteria : 필수 요소, 어떤 셀의 개수를 셀지 정의하는 숫자, 식, 셀 참조, 텍스트 문자열

  2) COUNTIFS 함수

○ 개요 : 여러 범위에 걸쳐 있는 셀에 조건을 적용하고, 모든 조건이 충족되는 횟수를 계산하는 함수
○ 구문 =COUNTIFS(Criteria_range1, Criteria1, [Criteria_range2, Criteria2], ...)

○ 인수

▷ Criteria_range1 : 필수 요소, 관련 조건을 평가할 첫 번째 범위

▷ Criteria1 : 필수 요소, 숫자·식·셀 참조·텍스트 형식의 조건, 개수를 계산할 Criteria_range1 인수의 셀을 정의
▷ Criteria_range2, Criteria2 등 : 선택 요소, 추가로 지정할 범위 및 관련 조건

<사용 예시>

- D14 셀은 지역(B3:B12)에서 "서울"이라고 되어 있는 셀의 갯수를 파악하라는 함수다.

- D15셀은 1차 수량(E3:E12)이 50 이상이 되는 셀의 갯수를 구하라는 함수다. 여기서 주의할 점은 조건을 줄 때 겹따옴표("")로 묶어주고 연결연산자(&)를 사용한다는 점이다.

  즉, '50이상'이라면 '50보다 크거나 같다'에 해당되므로 ">="&50 으로 조건을 준 점에 주목하자.

- I15셀은 두 가지 조건을 만족하는 셀의 갯수를 구하는 함수다. 지역이 서울이고, 구분이 A인 두 가지 조건을 모두 만족하는 셀의 갯수를 구하는 방법이다.

2. 조건에 맞는 셀의 평균 구하기

  1) AVERAGEIF 함수

○ 개요 : 범위에서 지정한 조건을 만족하는 셀의 산술 평균을 반환하는 함수

○ 구문 =AVERAGEIF(Range, Criteria, [Average_range])

○ 인수

▷ Range :  평균 계산할 셀 범위 (숫자, 이름, 배열, 숫자가 들어 있는 참조 포함)
▷ Criteria : Criteria : 평균 계산할 셀의 조건 (숫자, 식, 셀 참조, 텍스트 형식의 조건)

▷ Average_range : 평균 계산하는 데 사용할 실제 셀 집합, 생략 시 Range 범위의 셀을 계산

  2) AVERAGEIFS 함수

○ 개요: 여러 조건을 충족하는 범위의 셀의 산술 평균을 반환하는 함수
○ 구문 : =AVERAGEIFS(Average_range, Criteria_range1, Criteria1, [Criteria_range2, criteria2], ...)
○ 인수

▷ Average_range : 숫자, 이름, 배열, 숫자가 들어 있는 참조를 포함하여 평균을 계산할 하나 이상 셀 범위
▷ Criteria_range1 : 관련 조건을 평가할 첫 번째 범위
▷ Criteria1 : 숫자, 식, 셀 참조, 텍스트 형식의 조건, 평균을 계산할 Criteria_range1 인수의 셀을 정의
▷ Criteria_range2, Criteria2 등: 추가로 지정할 범위 및 관련 조건

<사용 예시>

- D14셀은 지역이 "국내"인 온라인판매 셀을 전부 찾아서 그 평균을 구하는 함수다.

  수식에서 $B14와 D$4:D$12 라는 혼합참조를 사용한 이유는 아래(D15셀)로 복사하고 오른쪽으로 마우스로 복사하기 위해서다.

- G18셀은 합계에 있는 값 중 담당부서가 영업1부이고 지역이 국내인 셀만 찾아서 그 평균을 구하는 수식이다. 

3. 데이터베이스 함수 활용하기

  1) 함수 종류 및 설명

○ 함수 설명 

   
  2) 공통 구문 및 인수

○ 구문 =함수명(Database, Field, Criteria)

○ 인수

▷ Database : 데이터베이스, 목록으로 지정할 셀 범위

▷ Field : 함수에 사용되는 열을 지정

- "나이" 또는 "수확량"처럼 열 레이블을 큰따옴표로 묶어 텍스트로 지정

- 첫째 열을 1, 둘째 열을 2 등 목록 내의 열 위치를 나타내는 숫자로 지정 가능

▷ Criteria : 지정하는 조건이 포함된 셀 범위

- 적어도 하나의 열 레이블이 있고, 열 레이블 아래에 열 조건을 지정할 셀이 하나 이상 포함된 범위를 criteria 인수로 사용

  3) 조건

○ 텍스트 검색

▷ 텍스트나 값에 대한 동등 비교 연산자를 나타내려면 조건 범위의 적절한 셀에 문자열 식으로 조건 입력

▷ 텍스트 데이터를 필터링할 때 대/소문자가 구분되지 않음

○ 두 개 이상의 조건 관계

▷ AND 조건 → 동일 행에 입력

▷ OR 조건 → 서로 다른 행에 입력

○ 수식의 결과로 만들어지는 조건

▷ 수식은 TRUE나 FALSE여야 함
▷ 수식을 사용하고 있으므로 평상시대로 수식을 입력
▷ 조건 레이블에 열 레이블을 사용 불가
▷ 첫 번째 행의 해당 셀을 참조하려면 조건에 사용되는 수식에서 상대 참조
▷ 수식의 다른 참조는 모두 절대 셀 참조

<사용 예시>

- 데이터베이스 함수는 아래와 같이 조건식의 범위를 별도로 주어야 한다. 조건식은 고급필터에서 사용하는 방법과 같다.

- 아래 예시를 보면서 함수를 이해해보도록 하자.

- 디베이스함수(디베이스 범위, 함수를 실행한 필드, 조건범위)

여러 페이지에 걸쳐있는 긴 표를 작성하다보면 각 페이지 상단에 제목 줄이 자동으로 나오면 참 편리하겠죠.

한글에서 [제목 줄 자동반복]이라는 기능입니다.

그럼 어떻게 하는 지 알아볼까요.

엑셀 단축키 모음.hwp

아래 파일은 첨부된 파일로 참고하시면 됩니다.

① 첫 행에서 마우스 오른쪽 키를 클릭하면 빠른 메뉴가 뜨죠

② 이중 가운데 있는 [표/셀 속성]을 선택합니다.

③ [표/셀 속성] 대화상자가 나오면 [표] 탭을 선택하고

④ 쪽 경계에서를 [나눔]으로 선택하고

⑤ 오른쪽에 있는 [제목 줄 자동 반복]을 체크 해줍니다.

⑥ 다음에 [셀] 탭을 선택합니다.

⑦ 중간에 보시면 아래와 같이 [제목 셀]이라고 있습니다. 옆에 체크 표시를 합니다.

⑧ 자 다 끝났습니다. 오른쪽 상단의 [설정] 단추를 클릭하면 다 되었습니다.

그런데 한 가지 문제가 생겼습니다.

바로 아래와 같이 F8에 대한 설명 부분의 표가 두 페이지로 나눠져 버렸네요.

이렇게 표가 나눠지지 않게 하는 방법은 없을까요? 당연히 있겠죠.

아까 [표]탭에서 쪽 경계에서를 [나눔]으로 설정했었죠.

이것을 아래와 같이 [셀 단위로 나눔]으로 변경하시고, 오른쪽 상단의 설정을 클릭합니다.

어떻게 변했나 다시 볼까요?

아래와 같이 앞 페이지 하단에 여백이 다른 페이지보다 많죠.

맞습니다. 셀을 나누지 않기 위해 전부 다음 페이지로 넘겼기때문입니다.

1. 합계와 곱 구하기

  1) SUM 함수

○ 개요 : 범위 또는 인수의 합을 구하는 함수

○ 구문 =SUM(Number1, [Number2], ……)

○ 인수

▷ Number1 : 필수 요소, 합계를 구하려는 첫째 숫자·셀 참조·범위

▷ [Number2], …… : 선택 요소, 합계를 구하려는 추가 숫자·셀 참조·범위, 최대 255개 지정 가능

  2) PRODUCT 함수

○ 개요 : 인수의 곱을 구하는 함수

○ 구문 =PRODUCT(Number1, [Number2], ……)

○ 인수

▷ Number1 : 필수 요소, 곱을 구하려는 첫째 숫자·셀 참조·범위

▷ [Number2], …… : 선택 요소, 곱을 구하려는 추가 숫자·셀 참조·범위, 최대 255개 지정 가능

  3) SUMPRODUCT 함수

○ 개요 : 배열·범위의 대응되는 값끼리 곱해서 그 합을 구하는 함수

○ 구문 =SUMPRODUCT(Array1, [Array2], [Array3], ……)

○ 인수

▷ Array1 : 필수 요소, 곱한 후 더할 값이 들어 있는 첫 번째 배열 인수

▷ [Array2], [Array3], …… : 선택 요소, 곱한 후 더할 겂이 들어 있는 배열 인수, 최대 255개 지정 가능

<사용 예시>

- 업체별 납품량의 합계(=SUM)

- 불량개수=납품량×불량률

- 청구금액=실납품수×단가×(100-할인율)%

- 할인액 합계=Σ(실납품수×단가×할인율)

2. 조건에 맞는 셀의 합계 구하기

  1) SUMIF 함수

○ 개요 : 지정한 조건에 맞는 범위의 값을 더하는 함수

○ 구문 =SUMIF(Range, Criteria, [Sum_range])

○ 인수

▷ Range : 지정한 조건에 맞는 지 검사할 셀 범위

▷ Criteria : 더할 셀의 조건을 지정하는 수, 식, 텍스트

▷ Sum_range : 합을 구할 실재 셀 범위, 생략 시 Range 범위의 셀을 계산

  2) SUMIFS 함수

○ 개요 : 지정한 조건에 맞는 범위의 값을 더하는 함수

○ 구문 =SUMIFS(Sum_range, Criteria_range1, Criteria1, [Criteria_range2, Criteria2], ……)

○ 인수

▷ Sum_range : 합계를 계산할 범위인 한 개 이상의 셀

▷ Criteria_range1 : 관련 조건에 맞는지 검토할 첫 번째 범위

▷ Criteria1 : Criteria_range1에서 찾을 조건

▷ Criteria_range2, Criteria2, …… : 추가로 지정할 범위 및 조건

<사용 예시>

- SUMIF는 조건식이 한 개일 경우에 사용하고, SUMIFS는 조건식이 두 개 이상일 경우에 사용

- 아래 부서별 합계의 D16셀은 부서(B4:B13)에서 구하고자하는 조건인 B16셀을 찾아서 09-26(월)에 있는 데이터 값을 더하라는 것.(부서는 절대참조, 부서별과 합계는 상대참조임을 확인)

- SUMIFS 함수 식을 해석하면 범위(F4:F13)에 있는 데이터 중 부서는 B19와 같고, 지역은 C19와 같은 데이터만 더하라는 것.(여기도 절대참조, 상대참조를 적절히 사용한 것을 확인)

- 절대참조나 상대참조를 사용하는 이유는 수식을 오른쪽으로 복사해서 사용하기 위함임. 그렇지 않으면 일일이 데이터 범위를 수정해줘야 하는 불편이 따름.

3. 함수로 자리수 제한하기

  1) ROUND 함수

○ 개요 : 숫자를 지정한 자릿수로 반올림하는 함수

○ 구문 =ROUND(Number, Num_digits)

○ 인수

▷ Number : 반올림할 숫자

▷ Num_digits : 반올림하여 소수점 아래 나타낼 자리수

  2) ROUNDDOWN, ROUNDUP 함수

○ 개요 : 숫자를 지정한 자릿수로 강제로 올리거나 내리는 함수

○ 구문

=ROUNDDOWN(Number, Num_digits)

=ROUNDUP(Number, Num_digits)

○ 인수 : ROUND 함수와 동일

<사용 예시>

- 금액은 납품수량에 단가를 곱한 것인데, 각 함수마다 다름을 알 수 있다.

- ROUND 함수는 나머지 자리수가 500이상일 경우에는 반올림을 하고, 그 미만일 경우에는 버린다.

- ROUNDUP 함수는 나머지 자리수가 있으면 무조건 위로 1을 올린다.

- ROUNDDOWN 함수는 나머지 자리수가 있으면 무조건 버린다.

1. 데이터의 평균·개수 구하기

  1) 개수를 세는 함수

○ COUNT, COUNTA 함수

  ▷ 종류

- COUNT 함수 : 인수 목록에서 숫자의 개수를 세는 함수

- COUNTA 함수 : 인수 목록에서 데이터가 들어 있는 셀의 개수를 세는 함수

  ▷ 구문

=COUNT(value1, [value2], ……)

=COUNTA(value1, [value2], ……)

  ▷ 인수

- value1 : 범위 또는 셀주소, 필수 요소

- value2, …… : 선택 요소, 최대 255개까지 지정 가능

○ COUNTBLANK 함수

  ▷ 개념 : 범위 내에서 비어 있는 셀의 개수를 세는 함수

  ▷ 구문

=COUNTBLANK(범위)

  ▷ 인수 : 비어 있는 셀을 찾을 범위, 필수요소

<사용 예시>

- 아래 A1부터 C5까지 범위에 숫자는 10, 2, -15 로 3개이며, 데이터 개수는 숫자 3개와 TRUE, FALSE, 한글, 깨비형 이렇게 4개를 더하면 7개이며, 빈칸은 8칸이다.

  2) 평균을 구하는 함수

○ AVERAGE

  ▷ 개념 : 인수의 산술 평균을 계산하는 함수

  ▷ 구문

=AVERAGE(Number1, [Number2], ……)

  ▷ 인수

- Number1 : 평균을 구하려는 첫째 숫자·셀참조·범위, 필수 요소

- [Number2], …… : 선택 요소, 최대 255개까지 지정 가능

○ TRIMMEAN

  ▷ 개념 : 데이터 집합의 내부 평균을 구하는 함수

      (인수 값의 위·아래 끝에 있는 데이터를 일정 비율 만큼 제외하고 평균 계산하는 함수)

  ▷ 구문

=TRIMMEAN(Array, Percent)

  ▷ 인수

- Array : 필수 요소, 평균을 구하려는 데이터 배열·범위

- Percent : 필수 요소, 계산에서 제외할 데이터 요소 부분(예, 40%라면 상위 20, 하위 20% 제외)

<사용 예시>

- 각 행마다 상품 1부터 상품5까지의 산술 평균과 내부 평균을 구하는 예시

G셀은 산술 평균이므로 5개의 데이터를 모두 사용하지만, H열은 제외할 범위를 40%로 지정했기 때문에 맨 아래 행을 기준으로 보면 상위 20%에 해당되는 10과 하위 20%에 해당되는 8을 제외하고 남은 3 숫자만을 가지고 평균을 낸다. 

2. 최대, 최소, 중간값 구하기

  1) 최대, 최소 구하기

○ MAX, MIN 함수

  ▷ 종류

- MAX : 인수, 목록에서 최대값을 찾는 함수 

- MIN : 인수, 목록에서 최소값을 찾는 함수

  ▷ 구문

=MAX(Number1, [Number2], ……)

=MIN(Number1, [Number2], ……)

  ▷ 인수

- Number1 : 필수 요소

- [Number2], …… : 선택 요소, 255개까지 지정 가능

  2) 중간값 구하기

○ MEDIAN 함수

  ▷ 개요 : 인수 목록에서 중간값을 찾는 함수

  ▷ 구문=MEDIAN(Number1, [Number2], ……)

  ▷ 인수

- Number1 : 필수 요소

- [Number2], …… : 선택 요소, 255개까지 지정 가능

<사용 예시>

- 각 행마다 상품 1부터 상품5까지의 최대값, 최소값, 중간값을 구하는 예시

3. 순위, 순위값 구하기

  1) 순위를 구하는 RANK 함수

○ RANK 함수

  ▷ 개요 : 수 목록에서 지정한 수의 순위를 구하는 함수

  ▷ 구문=RANK(Number, Ref, [Order])

  ▷ 인수

- Number : 순위를 구하려는 수, 필수 요소

- Ref : 순자 목록의 배열 또는 참조 값, 필수 요소(숫자 이외의 값은 무시)

-Order : 순위 결정 방법을 지정하는 수, 선택 요소(0 또는 생략하면 내림차순, 그외는 오름차순)

  2) 지정한 순위를 찾는 함수

○ LARGE, SMALL 함수

  ▷ 개요

- LARGE : 데이터 목록에서 k번째로 큰 값을 찾는 함수

- SMALL : 데이터 목록에서 k번째로 작은 값을 찾는 함수

  ▷ 구문

=LARGE(Array, k)

=SMALL(Array, k)

  ▷ 인수

- Array : k번째 값을 찾을 범위, 필수 요소

- k : 찾을 순위, 필수 요소

<사용 예시>

- 일일 납품수 순위는 일일 납품수의 숫자를 기준으로 매긴 순위다.

- 하단에 두 번째로 큰 값과 세 번째로 작은 값은 위 기업의 범위 중에서 찾는다.

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

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

    ○ 산술 연산자

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

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

    ○ 비교 연산자

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

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

    ○ 텍스트 연결 연산자

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

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

    ○ 참조 연산자

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

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

    ○ 연산자 우선순위

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

2. 셀 참조

  1) 셀 참조

    ○ 셀 참조란?

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

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

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

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

  2) 셀 참조의 유형

    ○ 상대참조

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

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

    ○ 절대참조

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