|
2일차
1-26쪽 고급필터
이고, 이면서, 모두 : and 조건 이거나, 또는, 둘중하나 : or 조건
1. 조건범위 안에 조건을 알맞게 입력해준다.
오타조심, 공백조심, 데이터 제목은 같은 행에 표시
그리고, 문제 부분에 결과로 나타내라고 하는 필드명이 있을 경우에는 결과로 나타내야되는 필드도 결과 위치에 입력하거나 복사해서 붙여넣기 해준다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
1-26쪽 고급필터
이고, 이면서, 모두 : and 조건 이거나, 또는, 둘중하나 : or 조건
1. 조건범위 안에 조건을 알맞게 입력해준다.
오타조심, 공백조심, 데이터 제목은 같은 행에 표시
그리고, 문제 부분에 결과로 나타내라고 하는 필드명이 있을 경우에는 결과로 나타내야되는 필드도 결과 위치에 입력하거나 복사해서 붙여넣기 해준다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
이고, 이면서, 모두 : and 조건 이거나, 또는, 둘중하나 : or 조건
1. 조건범위 안에 조건을 알맞게 입력해준다.
오타조심, 공백조심, 데이터 제목은 같은 행에 표시
그리고, 문제 부분에 결과로 나타내라고 하는 필드명이 있을 경우에는 결과로 나타내야되는 필드도 결과 위치에 입력하거나 복사해서 붙여넣기 해준다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
1. 조건범위 안에 조건을 알맞게 입력해준다.
오타조심, 공백조심, 데이터 제목은 같은 행에 표시
그리고, 문제 부분에 결과로 나타내라고 하는 필드명이 있을 경우에는 결과로 나타내야되는 필드도 결과 위치에 입력하거나 복사해서 붙여넣기 해준다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
오타조심, 공백조심, 데이터 제목은 같은 행에 표시
그리고, 문제 부분에 결과로 나타내라고 하는 필드명이 있을 경우에는 결과로 나타내야되는 필드도 결과 위치에 입력하거나 복사해서 붙여넣기 해준다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
그리고, 문제 부분에 결과로 나타내라고 하는 필드명이 있을 경우에는 결과로 나타내야되는 필드도 결과 위치에 입력하거나 복사해서 붙여넣기 해준다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
2. 데이터 안에 셀포인터 위치후 [데이터]탭-[고급]탭을 클릭
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
3. 다른장소에 복사 선택
4. 목록범위 : 데이터 전체 범위지정
조건위치 : 조건입력한 부분 모두 선택
복사위치 : 문제에서 제시한 곳 선택, 또는 드래그하여 범위지정
* : 모든것 표시
? : 물음표 한개가 한글자를 나타냄
tip : 계산필드일 경우에는 같은 이름의 필드명을 쓰면 절대로 안됨.등호(=)로 시작해야 한다.
비교하고자 하는 값은 그 필드의 첫번째 값을 열고정한다.
계산식 범위는 절대참조한다.
계산 필드의 결과는 true, false만 나온다.
예) 조건 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
수업때 설명했던 내용
1-26쪽 고급필터1
등록일의 연도가 2021년이고 ‘3월’ 또는 ‘4월’의 학점이 3.5 이상인 행만을 대상으로 표시하시오.
말로풀기
1) 등록일의 연도가 : =YEAR(등록일)=2021
2) 2021년이고 : =AND(YEAR(등록일)=2021, )
3) 3월 또는 4월 : =OR(3월>=3.5, 4월>=3.5)
4) =AND(YEAR(등록일)=2021, OR(3월>=3.5, 4월>=3.5))
정답 : =AND(YEAR(B4)=2021,OR(D4>=3.5,E4>=3.5))
1-28 고급필터2
생년월일의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 성명, 생년월일, 진료과목, 진료시간 필드만 순서대로 표시하시오.
말로풀기
1) 생년월일의 연도가 1987이상 : =YEAR(생년월일)>=1987
2) 진료시간이 12시 이상 : =진료시간>=0.5
3) =AND(YEAR(생년월일)>=1987, 진료시간>=0.5)
정답 : =AND(YEAR(C3)>=1987,G3>=0.5)
4) 결과로 표시해야 되는 내용도 I5셀부터 입력되어있어야됨.
---------------------------------------------------------------------------------
2021년 고급필터 상시문제예시
‘기본작업-1’시트의 [B3:N18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, ‘O’값이 50% 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’의 값을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTIF 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오. 조건식 : =AND(C3="O",E3="O",G3="O",COUNTIF(C3:N3,"O")>=6)
‘기본작업-1’시트의 [B3:J18]영역에 대해 다음과 같이 고급필터를 수행하시오.▶ ‘1월’, ‘3월’, ‘5월’의 값이 ‘O’이고, 국어, 영어, 수학의 평균이 전체 평균 이상인 데이터의 ‘1월’, ‘3월’, ‘5월’, ‘이름’, ‘평균’을 표시하시오.▶ 조건은 [B20:B21] 영역 내에 알맞게 입력하시오.(AND, COUNTA, AVERAGE 함수 사용)▶ 결과는 [D20] 셀부터 표시하시오.
조건식 : =AND(COUNTA(C3:E3)=3,I3>=AVERAGE($I$3:$I$18))
3. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 ID번호의 마지막 1글자가 짝수이고, 인공지능 점수가 90점 이상인 자료의 ‘ID번호’와 ‘성명’만 표시하시오. (ISEven, And, Right함수 사용)
▶ 조건은 [A28:A29] 영역에 입력하시오.
▶ 결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(ISEVEN(RIGHT(A2,1)),D2>=90)
4. 기본작업-1’시트의 [A1:H25]영역에 대해 다음과 같이 고급필터를 수행하시오.
▶ [A1:H25] 영역에서 결제구분이 체크카드가 아니면서 구입총액이 구입총액의 평균을 초과하는 값에 해당하는 데이터를 고급필터를 사용해서 표시하시오. (And, Average 함수 사용)
▶조건은 [A28:A29] 영역에 입력하시오.
▶결과는 [A31] 셀부터 표시하시오.
조건식 : =AND(B2<>"체크카드",H2>AVERAGE($H$2:$H$25))
---------------------------------------------------------------------------
조건부서식 영진교재 1-33쪽
조건부서식은 조건을 지정하는 셀이나 행전체에 서식을 지정할 때 사용하는 것임.
-특정한 규칙을 만족하는 셀에 대해서만 각종 서식, 테두리, 셀 배경색 등의 서식을 설정한다.
-여러 개의 규칙이 모두 만족될 경우 지정한 서식이 충돌하지 않으면 규칙이 모두 적용되며, 서식이 충돌하면 우선순위가 높은 규칙의 서식이 적용된다.
-규칙의 개수에는 제한이 없다.
-서식이 적용된 규칙으로 셀 값 또는 수식을 설정할 수 있다. 규칙을 수식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 한다.
- 규칙을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙 입력 시 열 이름 앞에만 '$'를 붙인다.
-피벗 테이블 보고서에서 값 영역의 필드에 조건부 서식을 적용하는 경우 [고유 또는 중복 값만 서식 지정]을 사용할 수 없음.
문제를 읽고 문제에서 서식을 적용할 범위가 어디인지 파악하는게 첫 번째임. 문제에서 서식을 적용할 셀 범위를 알려주면 그 범위를 블록지정하면 되고, 간혹 범위를 알려주지 않았을 때는 문제를 읽었을 때 문제에 ‘행 전체’라는 말이 있는지 확인할 것. ‘행 전체’ 또는 전체 행 이라는 말이 있으면 데이터 제목을 뺀 나머지를 모두 블록지정할 것.
단, 블록을 씌울 때 위에서 아래쪽 방향 또는 왼쪽위에서 오른쪽 아래쪽 방향으로 드래그하여 블록지정을 해 줘야됨.
[홈]탭-[스타일]그룹-[조건부서식]-[새규칙]또는 [규칙 관리]를 클릭
규칙 유형이 수식을 사용하여 서식을 지정할 셀 결정으로 되어있으면 그것을 선택하고 셀에 대한 내용이면 문제에서 요구한 규칙 유형을 선택하면 됨.
수식으로 작성할 경우 블록씌운 첫행을 가지고 작업할 것. 블록씌운 첫행을 클릭한 후에는 F4키를 두 번 눌러서 열문자앞에는 $ 표시가 있도록하고 행번호앞에는 $표시가 없도록 할 것.
추가문제
조건부서식(2)시트의 [D3:H15] 영역에 대해서 해당 열 번호가 홀수인 열 전체에 대하여 채우기 색을 ‘표준색-노랑’으로 적용하시오.
수식을 사용하여 서식을 지정할 셀 결정을 사용하시오. 함수는 COLUMN, ISODD 함수를 사용
TIP : COLUMN() 함수는 열 번호를 나타내는 함수, ISODD 홀수이면 참값을 나타내주는 함수
정답 : =ISODD(COLUMN())
짝수는 =ISEVEN(COLUMN())
열 : =COLUMN()
행 : =ROW()
홀수 : =ISODD(값)
짝수 : =ISEVEN(값)
외부데이터 가져오기 1-55쪽
외부데이터란? Excel 이외의 데이터(파일)을 가리키며 '외부데이터 가져오기'는 accdb형식의 엑세스 파일, txt 형태의 텍스트 파일 등을 엑셀 워크시트에 가져오는 작업을 의미한다.
-외부 데이터에 연결하면 매번 데이터를 복사할 필요가 없으며 주기적인 데이터의 분석이 가능하다.
외부데이터에서 가져올 수 있는 확장자
-쿼리 파일 : .dqy -텍스트 파일 : .txt -데이터베이스 파일 : .mdb, .accdb
실기시험 시 들어가는 방법
엑세스 파일은 [데이터]탭-[외부데이터 가져오기]그룹-[기타 원본]-[Microsoft Query]를 클릭
~이고, ~이면서, 모두 : 모두 만족할 때만 참인 경우 And 조건임.
~이거나, ~또는, 둘 중하나 : 둘 중에 하나만 만족해도 참인 경우인 OR조건이 됨.
= 같다 <> 같지 않다
> 크다, 초과 < 작다, 미만 >= 크거나 같다, 이상 <= 작거나 같다, 이하
예를들어, < 2017년 6월 20일전 > 2017년 6월 20일후
<=2017년 6월 20일이전>=2017년 6월 20일이후
외부데이터 가져오기 시험 유형 2가지
영진교재 1-55쪽 출제유형1번과 같은 유형과 다른 하나는 마지막부분에 ‘범위로 변환’ 하시오라는 문제가 출제되는 유형이 있음.
▶ 가지고온 데이터를 '범위로 변환'하세요.
상황별 도구탭인 [표도구]->[디자인탭]-[도구]그룹-'범위로 변환' 클릭
▶ 아래와 같은 화면이 뜨면 ‘확인’ 단추 클릭
▶ ‘확인’단추를 클릭하고 난 후에는 상황별 도구탭인 [표도구]가 표시되지 않음.
영진출판사교재 스프레드시트
조건부분만 문제 변경한 경우
▶ '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상인 행만을 대상으로 설정하시오.
1) '수강과목'이 "오피스" 또는 "인터넷"으로 시작하거나 "출석일수"가 10이상으로 했을 때와 마찬가지로 작업을 하고 ‘쿼리마법사-마침’ 부분에서 ‘Microsoft Query에서 데이터 보기 또는 쿼리 편집’을 클릭해서 쿼리를 수정해 준다.
2) 쿼리 편집 대화상자가 뜨면 두 번째 필드에 있는 조건의 내용을 잘라내기(ctrl+x)한 후 바로 아래칸에 ctrl+v로 붙여넣기 해준다.
3) 또는 줄로 수정을 한 후에는 Microsoft Query 대화상자의 오른쪽 부분의 x 표시를 눌러서 창을 닫고 나온다.
4) 데이터를 표시해줄 위치를 선택한 후 [확인]단추 클릭
메모(Shift + F2)
- 메모는 특정한 셀에 대한 설명을 입력할 수 있으며, 모든 셀에 설정할 수 있다.
- 데이터 정렬에 따라 셀의 위치가 변경되면 메모의 위치도 같이 변경된다.
- [검터]-[메모]-[새 메모] 또는 바로 가기 메뉴에서 [메모 삽입]을 실행해서 메모를 삽입한다.
- 메모가 삽입된 셀에는 빨간색의 표식이 표시되며 셀에 마우스 포인터를 위치시키면 메모의 내용이 표시된다.
- [검토]-[메모]-[메모 표시/숨기기]에서 메모 표시 유무를 설정할 수 있다.
- 셀에 입력된 데이터를 삭제해도 메모는 삭제되지 않으므로, 메모를 삭제하려면 바로 가기 메뉴에서 [메모 삭제]를 선택하거나 [검토]-[메모]-[삭제]를 실행한다.
ctrl + * 또는 ctrl + shift + 8 연속되어있는 글자들을 전체선택함.
메모는 기본적으로는 인쇄되지 않는다.
메모를 인쇄하고 싶으면 페이지설정의 [시트]탭의 메모에서 ‘시트에표시된 대로’ 또는 ‘시트 끝’을 선택한 후 인쇄할 수 있다. ‘시트 끝’을 선택할 경우 문서의 맨 마지막장에 한꺼번에 모아서 출력된다.
|