[자격증] 컴활 1급 실기 독학 4일 벼락치기 준비 (D-day 4) - 엑셀 기본작업

엑셀 기본작업 문제를 먼저 정리해볼 예정이다. 

1. 기본작업 (25점) - 외부 데이터 가져오기  . ACCDB 파일에서 조건과 정렬 기준으로 자료 가져오기  - 고급 필터  . AND와 OR조건을 입력하여 다른 위치에 자료 추출  - 조건부 서식  . 수식을 이용하여 전체 행에 조건부 서식 지정  - 시트 보호와 통합 문서 보호   . 셀 잠금, 시트 보호, 통합 문서(창, 구조) 보호  - 페이지 레이아웃   . 인쇄 영역 설정, 머리글/바닥글 편집, 페이지 가운데 인쇄  출처: https://gongstudyit.tistory.com/61 [Gongdile_Developer]

 

1) 외부 데이터 가져오기 

 

출제유형은 아래와 같다 

'외부 데이터 #' 시트에서 [외부 데이터 가져오기] 기능을 사용하여 <아무개.accdb>의 데이터를 

다음 조건에 따라 [##] 셀부터 표시하시오

-> 디비에서 데이터를 가져오는데 아래와 같이 정렬하라고 얘기한다.. 

-> 그럼 제일 먼저 해야할건 ## 셀을 클릭하고 외부 데이터 가져오기 기능을 이용해서 아무개 데이터를 

    문제에 맞게 가공해서 가져오는거다 

 

'외부데이터 1' 시트에서 [외부 데이터 가져오기] 기능을 사용하여 <성적.accdb>의 데이터를 

다음 조건에 따라 [B3] 셀부터 표시하시오

 

(1) <과정별 성적> 테이블의 데이터를 "과정명". "등록일"."수강생코드","학점평균"열의 순서로 가져오시오 

(2) "과정명"이 "5B"로 시작하거나 "6B"로 시작하고, '학점평균'이 3.5 이상 4.2 미만인 행만을 대상으로 설정하시오 

(3) "학점평균"을 기준으로 '내림차순' 정렬하고, '등록일'을 기준으로 '오름차순' 정렬하여 가져오시오 

 

아래와 같이 푼다 

 

(1) ##셀을 클릭한 상태에서  (##셀은 문제 마다 다르다) (요기서는 B3셀임)

(2)~(4) 데이터 > 기타원본 > Microsoft Query를 클릭한다 

 

(1)~(2) MS Access Database를 클릭하고 확인 버튼을 누른다 

(1)~(2) 문제에서 말한 성적 데이터를 가져온다 

<과정별 성적> 테이블의 데이터를 "과정명". "등록일"."수강생코드","학점평균"열의 순서로 가져오시오 

위 조건을 참고해서 데이터를 가져온다 

위 목록 조건에 포함되는 데이터를 더블클릭해서 쿼리에 포함 시키고 다음 버튼을 누른다 

 

 

이제 (2) 조건을 사용해서 문제를 풀 차례다.

(2) "과정명"이 "5B"로 시작하거나 "6B"로 시작하고, '학점평균'이 3.5 이상 4.2 미만인 행만을 대상으로 설정하시오 

조건 2가지를 아래와 같이 설정한다 (과정명에 대한 조건, 학점평균에 대한 조건)

 

**시트에 값 안나와서 뭐지하고 계속 보니까.. 과정명 가져올때 시작값으로 설정 안하고 "="으로 설정해서 

데이터 값이 안나온거였다 ㅡㅡㅋㅋ .. 

 조건 제대로 잘봐야한다~!! 

 

그리고 다음버튼을 누른다

 

이제 (3)의 조건을 활용해서 문제를 푼다.

"학점평균"을 기준으로 '내림차순' 정렬하고, '등록일'을 기준으로 '오름차순' 정렬하여 가져오시오 

 

문제에서 지정한 셀을 클릭하고 확인 버튼을 누른다. 

=> 제대로 출력되는거 확인~!~ 

 

결론: 데이터 가져오기 기능을 이용해서 문제에서 제시한대로 적절히 가져오면 되는 문제다 

        크게 어려울 건 없어보여서.. 기출을 더 풀어보기로 한다. 

 

 

---

2) 고급 필터/자동 필터 

 

출제유형은 아래와 같다 

'고급필터 #' 시트에서 다음의 지시사항을 처리하시오 

(1) [A3:H38] 영역에서 '등록일'의 연도가 2020년이고 '3월' 또는 '4월'의 학점이 3.5이상인 행만을 대상으로 표시하시오 

(2) 조건은 [A40:A41] 영역 내에 알맞게 입력하시오 (YEAR, AND, OR 함수 사용)

(3) 결과는 [A43P 셀부터 표시하시오 

 

=> (2)에 제시된 영역에서 YEAR, AND, OR함수를 이용해서 (1)조건을 충족하는 결과값을 (3)에 표시하면된다. 

 

이제 문제를 한번 풀어보겠다 먼저 (2)의 조건을 이용한다 

조건은 [A40:A41] 영역 내에 알맞게 입력하시오

A:40영역에 조건이라 기재하고, A:41에 (1)의 조건을 만족하는 식을 입력한다. 

 

(1)의 조건을 이용하자 ( (2)의 조건 참고) 

[A3:H38] 영역에서 '등록일'의 연도가 2020년이고 '3월' 또는 '4월'의 학점이 6.5이상인 행만을 대상으로 표시하시오 

(YEAR, AND, OR 함수 사용)

 

 

(2) 조건에 만족하는 식을 A:41에 입력하고, 상단 데이터 탭 > 필터에서 고급을 클릭한다

(3)의 조건을 참고해서 A:43위치에 조건을 충족하는 목록을 표시한다. 

그리고 확인 버튼을 누른다. 

 

**여기서 주의 내가 실수했던 건데 목록 범위가 조건범위를 포함할수있는 전체가 되어야한다 

나는 아까 실수로 A:53~B:38이 아니라 A:53 ~ A:38만 지정해서 값이 제대로 안나왔다. 

 

=>여기서 중요하게 봐야하는건 필터를 걸때 조건식을 어떻게 작성하냐인데 그외에 다른케이스에 대해서도 

정리를 좀해본다.. 

 

(예1) [A3:H38] 영역에서 '등록일'의 연도가 2020년이고 '3월' 또는 '4월'의 학점이 3.5이상인 행만을 대상으로 표시하시오 (YEAR, AND, OR 함수 사용)

=> =AND(YEAR(등록일셀)=2020, OR(3월셀>=3.5, 4월셀>=3.5))

 

(예2) 판매금액이 전체 판매금액 평균 이상이거나, 물품코드의 앞 두글자가 JJ인 행만을 대상으로 표시해라 

  (OR, LEFT, AVERAGE 함수 사용)

=> =OR(판매금액셀 >=AVERAGE(판매금액 전체셀), LEFT(물품코드셀, 2) ="JJ)

 

(예3) [A2:G30] 영역에서 '생년월일'의 연도가 1987 이상이고, 진료시간이 12시 이상인 데이터의 '성명', '생년월일','진료과목', '진료시간' 필드만 순서대로 표시하시오 (AND, YEAR 함수 사용)

=> =AND(YEAR(생년월일셀)>=1987, 진료시간셀 >= 0.5)

 

**근데 왜 12시간 이상이 0.5임?? 이거 정리해서 써놓기.. 

 

---

3) 조건부 서식 

정리하다보니까 너무 뻘쭘하게 많이 캡처해서 지저분하게 정리한거같다.. 좀 간단하게 정리해보자면.. 

 

조건부 서식 문제에서는 조건을 수식으로 처리한다. 

뭔말이냐면 문제를 예로들겠다 문제는 아래와 같다 

 

'조건부서식 #'시트에서 다음의 지시사항을 처리하시오 

[B3:H30] 영역에서 대해서 환자코드의 끝자리가 1로 끝나고 진료과목의 전체글자수가 네글자인 행전체에 대해서 글꼴 스타일으 굵게, 글꼴 색을 표준색 - 연한 파랑으로 적용하시오 

(1) 단, 규칙 유형은 '수식을 사용하여 서식을 지정할 셀 결정'을 사용하고, 한 개의 규칙으로만 작성하시오

(2) AND, RIGHT, LEN함수 사용 

 

=> 위 문제는 결국에 뭘까? 조건식을 엑셀에서 기능을 잘찾아서 함수로 표현할수있냐?? 이거 물어보는거다 

그럼 여기서 첫번째는 조건부서식 기능을 사용하는것이고 2번째는 서식기능 안에 함수로 수식을 표현하는거다 

 

 

조건에서 표시한 영역을 셀로 긁은 상태에서 홈 > 조건부서식> 새규칙을 클릭한다. 

 

**여기서 내가 놓친게 있엇는데 

내가한거: =AND(RIGHT($B$3,1)="1",LEN($F$3)=4)

정답: =AND(RIGHT($B3,1)="1",LEN($F3)=4)

차이보임?? B3셀에서만이아니라 B3전체라인이라서 주의해야한다 이거 놓치면 안됨 

 

$가 B앞에 붙으면 수식의 연속적인 사용에 열을 고정한다는 거고 

3앞에 붙으면 행을 고정한다는거임.. 

"$B$3" =>이거는 행열이 모드 고정된다는거임 

 

그래서 내가 조건식에 표현된거 전체를 반영하려면 행은 고정하지 말고 열만 고정해야함 

그래서 $B3이어야함

 

변경은 셀클릭하고 F4를 누르면 바뀜 (행,열 고정) 한번 더 누르면 행 고정 한번더 누르면 열고정 

 

 

---

조건부 서식에 자주 사용되는 함수 알고있기..

 

- AND(조건, 조건)

 . 두 조건이 참이여야 참을 반환함 

- OR(조건, 조건)

 . 두 조건중 하나만 참이면 참을 반환 

 

!!문자열 관련 함수!!

 

- LEFT(셀, 정수) 

 . 왼쪽에서 정수 숫자만큼의 문자열을 반환

- RIGHT(셀, 정수)

 . 오른쪽에서 정수 숫자만큼의 문자열을 반환 

- MID(셀, 정수1, 정수2)

 . 정부1번째 문자에서 정수2 숫자만큼의 문자열 반환

- LEN(셀)

 . 문자열의 길이를 반환 

 

 

---

4) 시트 보호와 통합 문서 보호 

 

이건 문제를 바로보자.. 기출로 풀어보기로 그냥 이런게 있다정도 

 

(1) (영역)에 셀잠금과 수시 숨기기를 적용한 후 잠김 셀의 내용과 워크시트를 보호하시오 

(2) 차트에 잠금을 적용한 후 차트를 편집할 수 없도록 보호하시오 

(3) 잠긴 셀 선택과 잠기지 않은 셀의 선택, 정렬은 허용하시오

(4) 시트 보호 암호는 지정하지 마시오 

 

(1) 조건 충족 범위 클릭하고 오른쪽 버튼 > 셀 서식> 보호 > 숨김 

(2) 확인 

 

 

(2) ~(4) 시트 보호 및 (3)에서 지정한 조건대로 보호 설정  

 

 

---

5) 페이지 레이아웃  

 

페이지 번호는 문제 잘읽어서 해달라는대로 표시해주면 되고 페이지번호 표시하는 방법만 알면 된다 

이거는 문제 풀면서 서술하겠다.. 

 

 

 

 

 

졸리다.. 문제 유형... 하

남은건 오늘오후에 하기로.. 벌써 새벽 다섯시다..ㅋㅋ