컴활1급실기독학 - 03회 기출문제 풀어보기!! 엑셀&액세스

03회.zip

0.24MB

03회 기출문제 연습화일입니다. ^^;

 

지난 한주동안은 엑셀과 액세스 기초를 수업을 해서 어찌보면 대개 오랜만이라는 느낌입니다. 

이제부터 다시 기출문제를 하나씩 하나씩 풀어보도록 하겠습니다. 

바램이 있다면, 앞에 기출문제 01, 02회에서 이해가 안되고 어려웠다고 생각되는 것이,

스스로 조금 "아~ 이래서 그렇구나!!" 라는 생각이 생기셨으면 좋겠는데... 

 

여튼!! 다시 기출문제를 이어서 풀어보도록 하겠습니다. 

동일하게, 엑셀과 액세스 기출문제 하나씩 풀어볼 예정이고, 03회에서 10회까지 차례대로 풀어보도록 하겠습니다.

 

스프레드시트의 계산문제는

▶ HLOOKUP, MATCH 함수 사용

▶ SUM, TEXT 함수를 사용한 배열 수식으로 작성

▶ INDEX, MATCH, MAX 함수를 사용한 배열 수식으로 작성

▶ COUNTIFS 함수와 & 연산자 이용

 

분석작업의 매크로 문제는 자동필터에 관한 문제가 나왔습니다.

 

스프레드시트 3회 문제는 다음과 같습니다. 더보기를 눌러서 문제를 보시면 됩니다.

 

문제1. 기본작업(25점)

1. ‘기본작업-1’ 시트에서 [외부 데이터 가져오기] 기능을 사용하여 <매출현황.accdb>의 데이터를 다음의 조건에 따라 [A2] 셀부터 표시하시오. (10점)

▶ <서울지역> 테이블의 데이터를 ‘고객명’, ‘대리점명’, ‘매출금액’, ‘받은금액’, ‘미수금’ 열의 순서로 가져오시오.

▶ ‘대리점명’이 “합정” 또는 “목동”이고 ‘미수금’이 20,000 이하인 행만을 대상으로 하시오.

▶ ‘대리점명’과 ‘미수금’을 기준으로 내림차순 정렬하시오.

▶ 가져온 데이터의 표를 ‘범위로 변환’을 실행하여 정상 범위로 변환하시오.

 

2. ‘기본작업-2’ 시트에서 다음과 같이 고급필터를 수행하시오. (5점)

▶ ‘매출금액’이 50,000 이상이고 ‘담당자’가 “강석희” 또는 “김민국”이거나, ‘매출금액’이 70,000 이상이고 ‘대리점명’이 “신림”인 데이터를 표시하시오.

▶ 조건은 [A22:G27] 영역 내에 알맞게 입력하시오.

▶ 결과는 [A28] 셀부터 표시하시오.

 

3. ‘기본작업-2’ 시트의 [A3:G20] 영역에 대해 다음과 같이 조건부 서식을 설정하시오. (5점)

▶ ‘고객코드’의 첫 번째 글자가 ‘1’이면서 ‘담당자’가 “민상주”가 아닌 전체 행에 대해서 글꼴 스타일을 ‘굵게’, 글꼴 색을 ‘표준 색 - 빨강’으로 적용하는 조건부 서식을 작성하시오.

▶ 단, 규칙 유형은 ‘수식을 사용하여 서식을 지정할 셀 결정’을 이용하시오.(AND, LEFT 함수 이용).

 

4. ‘기본작업-2’ 시트에서 다음과 같이 페이지 레이아웃을 설정하시오. (5점)

▶ 인쇄 용지가 가로로 인쇄되도록 용지 방향을 설정하시오.

▶ 인쇄될 내용이 페이지의 가로·세로 가운데에 인쇄되도록 페이지 가운데 맞춤을 설정하시오.

▶ 매 페이지 하단의 오른쪽 구역에는 페이지 번호가 [표시 예]와 같이 표시되도록 바닥글을 설정하시오.

[표시 예 : 현재 페이지 번호가 1이고, 전체 페이지 번호가 3인 경우 → 3페이지 중 1페이지]

▶ [A1:H21] 영역을 인쇄 영역으로 설정하고, 눈금선이 인쇄되도록 설정하시오.

 

 

문제2. 계산작업(30점)

1. 사용자 정의 함수 ‘fn비고’를 작성하여 비고[H3:H20]를 계산하여 표시하시오. (6점)

▶ ‘fn비고’는 고객코드를 인수로 받아 비고를 계산하는 함수이다.

▶ ‘fn비고’는 고객코드의 다섯 번째 글자가 1~3이면 “우수고객”, 4~6이면 “신규고객”, 그렇지 않으면 공백으로 표시하시오.

▶ Select문 이용

Public Function fn비고(고객코드) End Function

2. [표1]의 대리점명, 매출금액, 그리고 [표3]을 이용하여 [I3:I20] 영역에 대리점명과 매출금액에 따른 할인금액을 계산하여 표시하시오. (6점)

▶ 할인금액 = 매출금액 × 할인율

▶ 할인율은 [표3]을 참조하여 계산

▶ HLOOKUP, MATCH 함수 사용

 

3. [표1]의 담당자와 매출금액을 이용하여 [표2]의 [B24:B27] 영역에 담당자별 매출금액의 합계를 계산하여 표시하시오. (6점)

▶ 표시 예 : 319,000원

▶ SUM, TEXT 함수를 사용한 배열 수식으로 작성

 

4. [표1]을 이용하여 [표2]의 [C24:C27] 영역에 담당자별 미수금이 가장 많은 고객의 고객명을 표시하시오. (6점)

▶ INDEX, MATCH, MAX 함수를 사용한 배열 수식으로 작성

 

5. [표1]의 담당자와 매출금액을 이용하여 [표3]의 [G26:I26] 영역에 매출금액별 ‘담당자’가 “강석희”인 거래건수를 계산하여 표시하시오. (6점)

▶ 매출금액에 따른 거래건수

매출액	거래건수
25,000 60,000 90,000	25,000 이하인 거래건수 60,000 이하인 거래건수 90,000 이하인 거래건수

▶ 거래 건수 뒤에 “건” 표시(표시 예 :5건)

▶ COUNTIFS 함수와 & 연산자 이용

 

 

문제3. 분석작업(20점)

1. ‘분석작업-1’ 시트에서 다음의 지시사항에 따라 피벗 테이블 보고서를 작성하시오. (10점)

▶ 피벗 테이블 보고서의 레이아웃과 위치는 <그림>과 같이 설정하고, 보고서 레이아웃을 개요 형식으로 표시하시오.

▶ ‘받은금액’과 ‘미수금’을 열 합계 비율을 기준으로 나타나도록 작성하시오.

▶ 값 영역의 표시 형식은 ‘값 필드 설정’의 셀 서식의 사용자 지정 서식을 이용하여 값이 양수면 0.0%, 0이나 음수면 “*”가 표시되도록 설정하시오.

▶ ‘피벗 스타일 밝게 21’을 적용한 후 ‘줄무늬 행’ 옵션을 지정하시오.

※ 작업이 완성된 그림이며 부분 점수는 없음

 

2. ‘분석작업-2’ 시트에서 다음과 같은 기능을 수행하는 매크로를 현재 통합문서에 작성하고 실행하시오. (각 5점)

① 자동 필터 기능을 이용하여 ‘대리점명’이 ‘신림’과 ‘목동’인 데이터만을 표시하는 매크로를 생성하고, 매크로 이름을 ‘지동필터’로 정의하시오.

② ‘양식 컨트롤’의 ‘단추( )’를 [F1:G2] 영역에 생성한 후 텍스트를 ‘매크로 실행’으로 입력하고, 단추를 클릭하면 ‘자동필터’ 매크로가 실행되도록 설정하시오.

※ 셀 포인터의 위치에 관계없이 매크로가 실행되어야 정답으로 인정됨

 

 

문제4. 기타작업(25점)

1. ‘기타작업-1’ 시트에서 다음의 지시사항 따라 차트를 수정하시오.(각 2점)

※ 차트는 반드시 문제에서 제공한 차트를 사용하여야 하며, 신규로 차트 작성시 0점 처리됨

① ‘미수금’ 계열의 차트 종류를 ‘표식이 있는 꺽은선형’으로 변경한 후 보조 축을 지정하시오.

② ‘미수금’ 계열의 표식을 ‘원( )’으로 표시하시오.

③ 범례에 ‘강한 효과 - 파랑, 강조 1’ 스타일을 지정하시오.

④ 기본 세로 주 눈금선을 표시하고 가로(항목) 축의 세로 축 교차의 축 위치를 ‘눈금’으로 지정하시오.

⑤ 차트 영역에 테두리 스타일은 ‘둥근 모서리’, 그림자는 ‘안쪽 가운데’로 표시하시오.

 

2. ‘기타작업-2’ 시트에서 다음과 같은 작업을 수행하도록 프로시저를 작성하시오. (각 5점)

① <고객관리> 버튼을 클릭하면 <고객관리> 폼이 나타나고, 폼이 초기화되면 결제방식이 나타나는 목록 상자(lst결제방식)의 목록에는 ‘현금’, ‘카드’, ‘포인트’가 표시되도록 프로시저를 작성하시오.

② <고객관리> 폼의 <입력(cmd입력)> 버튼을 클릭하면 폼에 입력된 데이터가 시트의 표에 입력되어 있는 마지막 행 다음에 연속하여 추가되도록 프로시저를 작성하시오.

▶ 고객등급은 해당 항목(고급, 실버, 일반)이 선택되는 경우에 따라 ‘고급’, ‘실버’, ‘일반’으로 입력하시오.

▶ 할인금액은 결제방식이 ‘현금’이면 매출금액의 10%, ‘카드’이면 매출금액의 5%, ‘포인트’이면 0으로 입력하시오.

▶ 입력되는 데이터는 워크시트에 입력된 기존 데이터와 같은 형식의 데이터로 입력하시오.

③ <고객관리> 폼의 <종료(cmd종료)> 버튼을 클릭하면 [D2] 셀에 현재 날짜를 표시하고, 글꼴 스타일을 ‘굵게’로 지정한 후 폼이 종료되도록 구현하시오.

▶ 현재 날짜는 FORMAT 함수를 이용하여 ‘2019년 11월 08일 금요일’과 같은 형식으로 표시하시오.

 

 

다음은 스프레드시트_03회 문제 풀이 동영상입니다.

 

 

데이타베이스 문제는

▶ 엑셀파일의 데이터 가져오기 및 콤보상자 조회설정하기

▶ 매크로 작성 및 데이터 엑셀파일로 내보내기

▶ 매크로 함수 사용하기 등

 

데이타베이스 3회 문제는 다음과 같습니다. 더보기를 눌러서 문제를 보시면 됩니다.

 

문제1. DB구축(30점)

1. 학생에 대한 상세 내역을 관리할 수 있도록 데이터베이스를 구축하였다. 다음의 지시사항에 따라 <학생>, <과목>, <성적> 테이블을 완성하시오. (각 4점)

<학생> 테이블

① ‘학과코드’ 필드는 ‘A-101’ 형식으로 입력되도록 다음과 같이 설정하시오.

▶ 앞의 1자리 문자와 뒤의 3자리 숫자는 반드시 입력되도록 설정

▶ ‘-’도 저장되도록 설정

② ‘성명’ 필드에는 값이 반드시 입력되도록 설정하시오.

③ ‘학년’ 필드는 1자리만 입력할 수 있도록 설정하시오.

<과목> 테이블

④ ‘과목코드’ 필드는 기본키는 아니지만 중복되지 않도록 설정하시오.

<성적> 테이블

⑤ ‘성적’ 필드에는 100 이하의 숫자가 입력될 수 있도록 가장 적절한 데이터 형식과 필드 크기를 설정하시오.

 

2. ‘신입생현황.xlsx’ 파일을 테이블 형태로 가져오시오. (5점)

▶ ‘신입생’으로 이름이 지정된 범위를 이용할 것

▶ 첫 번째 행은 필드 이름임

▶ ‘학번’을 기본키로 지정하고 테이블 이름을 ‘신입생’으로 할 것

 

3. <성적> 테이블의 ‘학번’ 필드는 <학생> 테이블의 ‘학번’ 필드를 참조하며, 테이블 간의 관계는 M:1이다. 두 테이블에 대해 다음과 같이 관계를 설정하시오. (5점)

▶ 관계 설정 시 필요한 기본키를 설정하시오.

▶ 테이블 간에 항상 참조 무결성을 유지하도록 설정하시오.

▶ <성적> 테이블에서 참조하고 있는 <학생> 테이블의 레코드를 삭제할 수 없도록 하시오.

 

 

문제2. 입력 및 수정 기능 구현(25점)

1. ‘성적정보입력’ 폼을 다음 지시사항에 따라 완성하시오. (각 3점)

① ‘시험날짜’와 ‘성적’의 순서를 <그림>과 같이 변경하고, 폼 머리글과 본문에 있는 컨트롤의 가로 간격을 모두 같게 설정하시오.

② 폼 머리글에 <그림>과 같이 레이블을 생성한 후 폼 제목을 입력하고, 이름은 ‘lab제목’, 글꼴은 ‘궁서’, 크기는 20으로 설정하시오.

③ 본문의 모든 컨트롤에 대해 특수 효과를 ‘새김(밑줄)’로 설정하시오.

④ ‘txt학번’과 ‘txt성적’ 컨트롤을 각각 ‘학번’과 ‘성적’ 필드에 바운드 시키시오.

⑤ ‘닫기(cmd닫기)’ 단추를 클릭하면 ‘성적정보입력’ 폼을 닫는 기능을 매크로 함수로 구현하시오.

 

2. ‘성적정보입력’ 폼의 ‘과목코드(cmb과목)’ 콤보 상자에 대해 1번 <그림>을 참조하여 다음과 같이 설정하시오. (5점)

▶ <과목> 테이블의 ‘과목코드’와 ‘과목명’을 표시하시오.

▶ 각 열의 너비는 1cm와 4cm로 설정하고, 목록 값 이외의 데이터는 추가할 수 없도록 설정하시오.

 

3. ‘과목별성적조회’ 폼에 삽입된 하위 폼의 본문 컨트롤에 대해 다음과 같이 조건부 서식을 설정하시오. (5점)

▶ 학과이름에 “정보”가 포함된 레코드에 대해 글꼴 스타일 ‘굵게’와 ‘밑줄’을 설정하시오.

 

 

문제3. 조회 및 출력 기능 구현(25점)

1. 다음의 지시사항 및 화면을 참조하여 ‘과목별시험성적’ 보고서를 완성하시오. (각 3점)

① ‘과목명’ 필드를 기준으로 내림차순으로 정렬하여 표시하되 그룹 바닥글 영역을 생성하고, 보고서 바닥글 영역의 모든 컨트롤을 그룹 바닥글 영역으로 이동시키시오.

② ‘txt학번’과 ‘txt학년’ 컨트롤에는 각각 ‘학번’과 ‘학년’ 필드를 바운드 시키시오.

③ ‘txt평가’ 컨트롤에는 ‘성적’이 90점 이상이면 “장학”, 80점 이상이면 “우등”, 그 밖에는 빈칸으로 표시하시오.(Switch 함수 이용).

④ ‘txt과목명’ 컨트롤의 값이 이전 레코드와 동일한 경우에는 표시되지 않도록 설정하시오.

⑤ ‘txt응시인원’ 컨트롤에는 그룹별 인원수, ‘txt평균’ 컨트롤에는 그룹별 성적의 평균이 표시되도록 설정하시오.

 

2. ‘학생정보’ 폼에서 ‘txt학과이름’ 컨트롤을 더블클릭하면 다음과 같은 기능을 수행하는 <학과선별> 매크로 함수를 작성한 후 지정하시오. (5점)

▶ ‘학과별성적’ 폼을 대화 상자 모드로 열 것

▶ ‘txt학과이름’ 컨트롤에 입력된 학과이름에 해당하는 레코드만 표시할 것

 

3. ‘과목별성적조회’ 폼의 ‘인쇄하기(cmd인쇄)’ 버튼을 클릭하면 다음과 같은 기능을 수행하도록 매크로 함수로 구현하시오. (5점)

▶ ‘과목별시험성적’ 보고서를 ‘인쇄 미리 보기’의 형태로 열 것

▶ ‘txt과목명’에 입력된 과목명에 해당하는 레코드만 표시할 것

 

 

문제4. 처리 기능 구현(20점)

1. ‘과별정보’ 쿼리를 이용하여 학생별 총 수강횟수와 성적의 최소값을 구하는 쿼리를 작성하시오. (10점)

▶ 쿼리 이름은 ‘학생별수강횟수’로 할 것

▶ 수강횟수가 5 이상인 데이터만 표시

▶ 결과의 필드명은 <그림>을 참조하여 지정

 

2. 다음과 같은 기능을 수행하는 쿼리를 작성하시오. (5점)

▶ <성적> 테이블의 ‘성적’ 필드 점수에 2점을 추가하는 쿼리를 작성할 것

▶ ‘시험날짜’가 2017년 12월 8일부터 2017년 12월 10일까지인 데이터만을 대상으로 할 것(Between 사용)

▶ 쿼리 이름은 ‘점수변경’으로 할 것

 

3. 다음과 같은 기능을 수행하는 <엑셀변환> 매크로를 작성하시오. (5점)

▶ <성적> 테이블을 ‘C:\문서’ 폴더에 ‘성적표.xlsx’로 내보낼 것

▶ 테이블을 내보낸 후 자동으로 실행되도록 할 것

 

 

다음은 데이타베이스_03회 문제 풀이 동영상입니다.

 

 

 

하루하루 해야 할 것들은 많은데, 미루고 있네요.

코로나19에 이번 한주는 휴원을 했는데, 이게 언제까지 지속될지도 모르겠고, 

여유로울 때 많이 만들어서 올려놓으면 좋은데, 생각처럼 진도가 팍팍팍 나가지는 않습니다. 

 

오히려 컴활1급보다는 컴활2급의 기출문제들이 더 많이 보실꺼 같기도 하고,

시나브로 하나씩 해결해 가면 되겠지요??!! 

한가지 고무적인 일은, 이제 구독자 900명을 돌파 1000명을 향해 가고 있는 것입니다. ^^; 

어서 가자 천명!! ㅎㅎ

 

화이팅입니다.