[컴활 2급 실기] 참조 ~ 데이터베이스함수

참조

상대참조

• 참조시 한칸 씩 이동함
• =A1
  • 아래로 참조시 행 변환 / 열 고정
  • 오른쪽으로 참조시 행 고정/ 열 변환

 

절대참조

• 참조시 절대값($)으로 고정시켜 절대 변하지 않음
• =$A$1

 

혼합참조

• 부분적으로 상대참조와 절대참조를 사용
• =A$1 / =$A1

 

---

논리함수

IF(조건문, 값1, 값2)

• 조건에 만족하면 값1 반환 / 만족하지 않으면 값2 반환
• ex) =IF(B2 >= 80, "합격", "불합격"

AND(조건, 조건2, 조건n)

• 조건이 모두 만족해야만 참(TRUE)값을 반환
• ex) =IF(AND(B2>=80,C2="여자"),"합격,"")

OR(조건, 조건2, 조건n)

• 조건이 둘 중 하나만 만족해도 참값 반환
• ex) =IF(OR(B2>=80,C2="여자"),"합격","")

IFERROR(수식, 값)

• 수식에 오류가 있으면 내가 지정한 값 반환하고, 없으면 수식결과 반환
• ex) =IFERROR(A2*B2,"에러발생")

중첩IF

• 조건에 따른 결과값이 두개일 때 (elif 느낌)
• if문을 연달아서 쓸 수 있음
• ex) = IF(AND(C28>=170,D28>=10), "우수", IF(AND(C28>=130,D28>=5), "평균", ""))

---

필터

자동필터

• 데이터를 자동으로 걸러주는 역할
• 데이터필드 - 데이터 탭 - 자동필터 클릭
• 각 칼럼별로 따로 필터 지정할 수 있음 (숫자필터 등)

고급필터

• AND조건, OR조건을 사용할 수 있고, 내가 원하는 곳에 데이터 표시 가능
• 원하는 칼럼만 추출하고 싶은 경우, 미리 해당 칼럼을 붙여넣기 한 후 해당 범위에 필터표시

고급필터 조건식 예시

와일드카드 문자

• 하나 이상의 다른 문자들을 대표적으로 상징하는 특수 문자

* : 모든 문자를 의미	김* : 김으로 시작하는 값 모두 (김종국, 김치, …)  *타자 : 타자로 끝나는 값 모두 (1번타자, 4번타자, …)   *프* : 프를 포함하는 값 모두 (프랑스, 스프라이트, 코스프레)
? : 글자수 제한 의미	??차 : 앞이 두글자로 제한되고, 차로 끝나는 값 모두 (국화차, 보리차, 매실차)

---

조건부 서식

조건부 서식

• 조건에 만족하는 데이터 값을 눈으로 잘 식별할수 있게 내가 지정한 서식으로 변경
• ex) '아메리카노'만 파랑으로 변경

사용방법

• 블록을 씌운 후 (칼럼명은 제외)
• [홈] - 조건부 서식 - 새 규칙 - 수식을 사용하여 서식을 지정할 셀 결정
• 아래로 참조하며 비교하기 위해서는 행 앞에 절대참조($) 기호 없애주어야 함 (F4 두번)

예시 (음료명이 청포도에이드 이면서 수량이 10개 이상인 것)

=AND($B10="청포도에이드",$D10>=10)

자동서식

• 빠른도구모음에서 자동서식 추가하여 간편하게 표 서식 변경 가능

시험에 출제되는 조건부서식 함수

함수	설명
LEFT(텍스트, 문자 수)	텍스트의 맨 왼쪽에서 문자 수만큼 추출
RIGHT(텍스트, 문자 수)	텍스트의 맨 오른쪽에서 문자 수 만큼 추출
MID(텍스트,시작위치,문자수)	텍스트의 시작위치에서 문자 수 만큼 추출
TODAY()	현재 시스템 상의 날짜 추출
NOW()	현재 시스템 상의 날짜와 시간 추출
YEAR(날짜)	날짜의 연도만 추출
MONTH(날짜)	날짜의 월만 추출
DAY(날짜)	날짜의 일만 추출

 

---

데이터 정렬

• 블록씌운 후 - [데이터] - 정렬
• 오름차순(ㄱ~ㅎ) / 내림차순 기본
• 정렬 버튼을 누르면 세세한 설정 가능

[데이터] 정렬버튼

사용자 지정 목록

• 정렬버튼 - 제일 오른쪽 '정렬'에서 사용자 지정 목록 선택
• 정렬 시 사용자가 설정한 순서대로 정렬시킬 수 있음
• (ex. 사원, 대리, 과장, 차장, 부장)

그림 붙여넣기

스크린샷이 아니다

• 오른쪽 클릭 - 붙여넣기
• [홈] - 붙여넣기

---

외부데이터 가져오기 & 하이퍼링크

• 외부에서 활용한 데이터를 서식에 맞게 엑셀로 가져오는 기능
• [데이터] - [데이터 가져오기] - [레거시 마법사] - [텍스트에서(레거시)]
  • 1단계 : 파일 유형 선택

  • 

    * [내 데이터에 머리글 표시] 체크하면 맨 윗줄이 칼럼으로 설정됨
  • 2단계 : 구분기호 선택

  • 

  • 3단계 : 열데이터 서식 선택 (열 선택하고 열 가져오지 않음 선택하면 그 열을 빼고 가져옴)

  • 

• 데이터 삽입 전 속성에서 데이터에 맞게 열 너비 조정 가능 (열 너비 조정 체크)

하이퍼링크

• 문서 내에서 동영상, 음악, 그림, 프로그램, 인터넷주소 등을 바로 연결
• 연결시킬 항목 선택 - [삽입] - 링크 - 링크 삽입
• 외부주소인지, 내부주소인지 확인하기

이와 같이 도형에도 링크 삽입이 가능하다

---

날짜함수

 

기본 날짜함수

today()	오늘 날짜를 표시
now()	오늘 날짜 + 시간을 표시
year(날짜)	날짜의 연도만 표시
month(날짜)	날짜의 월만 표시
day(날짜)	날짜의 일만 표시
hour(시간)	시간의 시간단위만 표시
minute(시간)	시간의 분단위만 표시
second(시간)	시간의 초단위만 표시
DATE(년, 월, 일)	날짜 형식으로 변환
TIME(시, 분, 초)	시간 형식으로 변환

 

고급 날짜함수

함수	설명	옵션
WEEKDAY(날짜, [옵션])	날짜에 해당하는 요일의 번호를 표시	1 : 일요일을 1로 시작 2 : 월요일을 1로 시작 3 : 월요일을 0으로 시작
WORKDAY(시작날짜, 날짜 수, [휴일])	시작 날짜에서 휴일을 제외한 날짜 수 만큼 지난 날을 구함 (주말 자동제외 + 지정 휴일)
DAYS369(시작일, 종료일)	1년을 360일로 가정하여 시작일부터 종료일까지의 일수를 구함
EDATE(날짜, 월)	날짜에서 월을 더한 날짜의 일련번호를 반환
EOMONTH(날짜, 월)	날짜에서 월을 더한 마지막 날짜의 일련번호를 반환
YEARFRAC(시작일, 종료일, [옵션])	시작일에서 종료일의 일수를 1년 중 차지하는 비율로 반환	0(또는 생략) : 한달의 일수를 30, 1년의 일수를 360으로 계산 1 : 한달의 일수는 실제, 1년의 일수도 실제로 계산 2 : 한달의 일수는 실제, 1년의 일수는 360 3 : 한달의 일수는 실제, 1년의 일수는 365 4 : (유럽) 한달의 일수는 30, 1년의 일수는 360

---

데이터베이스 함수

• 데이터베이스 내 조건에 만족하는 합계,평균,표준편차 등을 계산하는 함수
• 괄호 인자는 모두 동일 --> (데이터베이스 범위, 값을 구할 필드, 조건 범위)

함수	설명
DSUM	조건에 만족하는 합계를 구함
DAVERAGE	조건에 맞는 데이터 평균을 구함
DCOUNT	공백을 제외하고 조건에 맞는 숫자 데이터 개수를 구함
DCOUNTA	공백을 제외하고 조건에 맞는 숫자 + 문자 데이터 개수를 구함
DMAX	조건에 맞는 데이터 최대 값을 구함
DMIN	조건에 맞는 데이터 최소 값을 구함
DSTDEV	조건에 맞는 데이터 표준편차를 구함
DVAR	조건에 맞는 데이터 분산을 구함

예시

• 필드 첫내용이 목적과 다르다면 다른 셀에 입력한뒤 그곳을 범위로 잡으면 됨
• 고급필터의 형식처럼 조건 정할 수 있음 (AND, OR 조건)