[컴퓨터활용능력 1급 실기] 시험 후기 및 핵심 정리 #12 (2020, 2022 시나공 기출문제집 풀이)

  4번째 도전마저 실패로군요. 3회차 때보다 안정적으로 합격선에 안착할 줄 알았더니 제 오산이었습니다. 지난 시험에 비해 점수는 10점이 올랐지만, 분석 10점과 차트, 그리고 계산 1문제에서 아쉬움이 남습니다.  

 

  탈락한 성적표를 마주하니 기분이 썩 좋지 않습니다. 그래도 현실을 무시해선 성장할 수 없겠죠. 더 단단하게 단련해서 기필코 손에 넣도록 하겠습니다. 즉흥적인 풀이에서 전략적인 접근으로 시험 전략도 수정하겠습니다. 이건희 선배 말처럼 처자식빼고 다 바꿔보겠습니다. 더 이상은 자존심이 허락지 않는군요.

---

  <스프레드시트 점수 구성 및 시간 할애>

 

1. 기본작업 (15점) - 고급 필터, 조건부 서식, 페이지 수정(보호, 레이아웃) - 5/45 - 목표 : 15점

2. 계산작업 (30점) - 15/45 (사용자 정의 함수를 먼저 풀었으나 이것도 마지막으로) - 목표 : 30점

3. 분석작업 (20점) - 피벗 테이블, 데이터 유효성 검사, 시나리오 등 - 10/45 - 목표 20점

4. 기타작업 (35점) - 매크로, 차트 수정, 프로시저 작성 - 10/45 : 목표 25점

 

* 1, 3, 4를 다 풀고 2번 들어갈 때, 20분이 남으면 예상 범위.

* 단, 시간에 주의하되 쫓겨서는 안 됨.

* 나머지 5분은 검토 및 부족한 부분에 할당하고 총 90점으로 합격.

 

---

 

 

Index

• [스프레드시트]
• [액세스]

[스프레드시트]

 

- 기본작업 - 

 

• 고급서식에서 and와 or의 적용 범위가 모호할 때는 쉼표의 위치를 찾자
• FIND( 찾을 문자, 문자열, [시작 위치])는 해당 문자의 위치를 반환한다. 따라서 1보다 크다고 설정해야 find로 해당 문자열에서 존재하는지 찾을 수 있다.
• 조건부 서식에 'and'없이 두 개 이상의 조건을 만족시키려면, 조건 1과 조건 2를 괄호로 묶고 *로 이어주면 된다.
• Countif(범위, 조건) 입력 시, 만족하는 개수 추출

=AND(OR(B3="문예창작과",B3="문헌정보학과"),COUNTIF(F3:H3,">=80")=3)

 

• Rank.EQ 활용
  • 6월 이전, 10위 이내

 

• 페이지 나누기 : 나누는 기준의 다음 셀에서 페이지 나누기 삽입 클릭

 

- 계산작업 - 

 

• 오답 방지를 위해 기본적으로 함수의 작은 범위에서부터 풀이를 시작한다.
• Dcounta와 같은 데이터베이스 함수는 조건을 기입해야 하고, 컬럼명과 조건을 세로로 적는다. 

 

• Sumproduct, Match, Offset (고난이도)
  • Offset(범위, 행, 열, 높이, 너비) : 선택한 범위에서 지정한 행과 열만큼 떨어진 곳의 데이터 반환

오답 : =SUMPRODUCT(G3:J3,OFFSET($F$32:$J$36, MATCH(E3,$F$33:$F$36,0) + 1, 4))

정답 : =SUMPRODUCT(G4:J4,OFFSET($F$32, MATCH(E4,$F$33:$F$36,0),1,1,4))

 

• Search 함수
  • 해당 문자가 있는 시작 위치를 반환

출처 :&nbsp;https://www.oppadu.com/%EC%97%91%EC%85%80-search-%ED%95%A8%EC%88%98/

 

• Index, Match
  • index 함수는 셀의 데이터를, match함수는 셀의 위치를 출력한다.
    • index함수의 parameter로 컬럼을 제외한 데이터의 범위를 선택한다.
  • Case1 : Match로 Min 값을 찾을 때는 0을 넣는다. (정확히 찾기!)
  • Case2 : Index 범위에는 컬럼을 넣지 않는다! 

case1

case2. Index의 범위로 컬럼이 있는 A열이 아닌 B열부터 들어가야 함.

• Index, Match
  • Case 3 : Index, Match, Max
  • Max도 Sum, Average처럼 조건를 정하고, 최대값을 구할 범위를 정하면 된다.

오답 : =INDEX($A$3:$G$20,MATCH(MAX($G$3:$G$20),$G$3:$G$20),1)

정답 : =INDEX($A$3:$G$20,MATCH(MAX(($D$3:$D$20=$A24)*$G$3:$G$20),($D$3:$D$20=$A24)*$G$3:$G$20,0),1)

 

• 배열수식1
  • Average, If, Find, Iferror (고난이도)

오답 : =AVERAGE(IF(($D$3:$D$34=$A38)*(FIND("정보",$A$3:$A$34)>=1),$G$3:$G$34))

 

정답: =AVERAGE(IF(($D$3:$D$34=$A38)*IFERROR(FIND("정보",$A$3:$A$34)>=1,FALSE),$G$3:$G$34))

• • iferror를 넣어서 오류가 발생하는 경우, False값을 주어 연산이 가능하게 하는 듯?
• 배열수식2
  • Average, If, Round
  • 배열수식 조건에서 Or에 해당하는 기호는 +
  • 나눗셈을 하고자 하는 범위를 전체 선택

오답 : =ROUND(AVERAGE(IF(($D$3:$D$29=$B33)*(OR($E$3:$E$29 = "사원", $E$3:$E$29 = "대리")),$G$3:$J$3)), 1)

정답: =ROUND(AVERAGE(IF(($D$3:$D$29=$B33)*(($E$3:$E$29 = "사원")+($E$3:$E$29 = "대리")),$G$3:$J$29)), 1) 

 

• 배열수식3
  • Large, if
  • 위와 마찬가지로 범위 전체를 지정해야 함.

오답 : =LARGE(IF(($D$3:$D$29=$B33),$G$3:$J$3),1)

정답 : =LARGE(IF(($D$3:$D$29=$B33),$G$3:$J$29),1)

 

• Hlookup, Match 활용 (초고난이도)
  • 하나의 요소와 나머지 요소의 대표값 (ex - 합정과 기타)로 분류된 경우 
  • 내림차순의 첫째값, 둘째값으로 범위 지정 후, -1 옵션 선택

오답 : = HLOOKUP(C3,$E$23:$I$25,MATCH(E3,$F$23:$I$23,1),0)

정답 : = E3*HLOOKUP(E3,$E$23:$I$25,MATCH(C3,{"합정","신촌"},-1)+1)

 

• Countifs : 조건의 표기에 주의

오답 : =COUNTIFS($D$3:$D$20,"=강석희",$E$3:$E$20,G23:I23)&"건"

정답 : =COUNTIFS($D$3:$D$20,"강석희",$E$3:$E$20,"<="&G23)&"건"

 

• If, Left, Countif (초고난이도)
  • M으로 시작할 때와 C로 시작할 때, 그리고 M으로 시작하지 아니할 때에서 C로 시작할 때를 차감(M 시작도 아니고 C 시작도 아님).
  • Countif는 누적 개수를 구해야 하기 때문에 셀의 범위를 하나가 아닌 여러 개로 지정.

= IF(LEFT(B15, 1) = "M", "뮤지컬(" & COUNTIF($B$15:B15,"M*") & ")", IF(LEFT(B15, 1) = "C", "콘서트(" & COUNTIF($B$15:B15,"C*") & ")", "그외(" & COUNTIF($B$15:B15,"<>M*") - COUNTIF($B$15:B15,"C*")& ")"))

 

• Averageifs
  • 조건이 두개 이상일 확률이 높으므로 항상 주의할 것!

=AVERAGEIFS($F$15:$F$40, $A$15:$A$40, A3,$E$15:$E$40,">=5")

 

• ALT + F11로 VBE에 빠르게 들어갈 수 있다.
• 사용자 정의 함수에서 Select문을 요구하면, 첫줄에 Select Case로 변수 지정하고, 다음 줄은 들여쓰기하여 Case is 조건과 출력값을 지정한다.

 

• 사용자 정의 함수 1
  • Select 활용
  • 비교 연산자와 To, 모두 활용 가능함.

Public Function fn비고(고객코드)

    Select Case Mid(고객코드, 5, 1)
        Case Is <= 3
            fn비고 = "우수고객"
        Case Is <= 6
            fn비고 = "신규고객"
        Case Else
            fn비고 = ""
    End Select
    
End Function

Public Function fn비고(고객코드)

    Select Case Mid(고객코드, 5, 1)
        Case 1 to 3
            fn비고 = "우수고객"
        Case 4 to 6
            fn비고 = "신규고객"
        Case Else
            fn비고 = ""
    End Select
    
End Function

 

• 사용자 정의 함수 2 
  • VBE가 상태가 안 좋으면, 제대로 입력해도 적용이 안되는 듯? 뭐가 문제임?

Public Function fn할인액(구분, 총금액)

    If Right(구분, 3) = "뮤지컬" And 총금액 >= 3000000 Then
        fn할인액 = 총금액 * 0.15
    ElseIf Right(구분, 3) = "콘서트" And 총금액 >= 3000000 Then
        fn할인액 = 총금액 * 0.1
    Else
        fn할인액 = ""
    End If
        
End Function

 

• 사용자 정의 함수3
  • 셀 주소를 넣을 때는 []로 묶음/
  • 같은 시트에서 넣을 때는 시트명없이도 동일 결과 출력.

Public Function fn할인액(판매일, 금액)

    If 판매일 <= [계산작업!J2] Then
        fn할인액 = 금액 * 0.15
    Else
        fn할인액 = 0
    End If
    
End Function

 

• 사용자 정의 함수4 (고난이도) 
  • For문 사용
  • mid와 len을 혼용하고 *1을 해주어 숫자 취급
  • a라는 변수를 1부터 K(mg)까지 반복

Public Function fn비고(제품명)

    If Mid(제품명, Len(제품명) - 4, 3) * 1 >= 100 Then
        For a = 1 To Mid(제품명, Len(제품명) - 4, 3) / 100
            fn비고 = fn비고 & "■"
        Next a
    Else
        fn비고 = ""
    End If

End Function

 

• 사용자정의함수5
  • int를 왜 넣지?
  • 정수 처리 안 해도 계산되는데다 불과 1차이인데? 이거 반올림 차이로 보이고...
  • 실제 시험에서도 이런 걸로 틀리다고 하나? 

 

 

- 분석작업 -

 

• 피벗 테이블 보고서 위치 주의 : 컬럼을 기준으로 생각.
• 개수, 합계 등으로 나타내려면 값 카테고리에 넣어주어야 함.
• 피벗 테이블 보고서에서 특정 그룹을 별도의 시트로 만들려면, 빈 셀에서 더블 클릭하면 된다.

국어 그룹의 상세 정보가 담긴 시트가 초록색으로 강조된 빈 셀을 더블 클릭하는 것으로 생성된다.

 

• 데이터 유효성 검사 - 데이터표
  • 데이터표
  • 1) 행과 열이 교차하는 표의 첫번째 셀에 총점을 입력.
  • 2) 표 전체를 범위로 잡고, 행에는 어학테스트의 점수, 열에는 학과성적의 점수를 입력.

데이터 표

• 데이터 유효성 검사 - 필터
  • 조건식 설정 : 제한대상을 사용자 지정으로 하고, 식 입력

= len(B4) = 6

 

• 데이터 유효성 검사 - 데이터 통합
  • 데이터 유효성 검사 매번 틀리게 나오는데 채점 프로그램의 문제로 추정
  • 빈 공간이 생기려면, 원본 데이터에 연결 옵션 체크

 

 

 

• 조건부 서식 - 데이터 통합
  • 데이터 통합
    • 1) 조건을 적고, 새로 들어갈 표를 모두 선택
    • 2) 참조할 영역을 컬럼명 포함해 모두 지정하고, 사용할 레이블에서 첫행, 왼쪽열 전부 체크
• 목표값 찾기
  • 1) 가상분석 - 목표값 찾기
  • 2) 수식셀과 찾는 값 (목표) + 값을 바꿀 셀 지정 (목표를 구하기 위해 변화될 값) 

 

- 기타작업 -

 

• 차트
  • 백분율로 나타내면 되는 건데... 이건 좀 억까네...

 

• 매크로 (채점프로그램이 잘 인식하지 못하는 듯)
  • Case1 : 숫자와 텍스트를 모두 표기해야할 때는 숫자, 텍스트 순으로 조건 부여.

[빨강][>=0.03]"검은별"0.0%;[파랑][>=0.025]"하얀별"0.0%;0.0%;"당구장"

• • Case2 : 괄호는 굳이 따옴표로 넣어주지 않아도 되는 것으로 보임. (동일 결과 출력)

[빨강][>=95]"A+("0.0")";[>=90]"A";""

[빨강][>=95]"A+"(0.0);[>=90]"A";""

• • Case3 : 특정 문자로 채우기 (공백도 가능, * 다음의 문자로 셀을 가득 채움)
  • Case4 : 양수, 음수, 0, 텍스트 => 순서대로 입력값을 넣자!

Case3

오답 : [>0]"남성";[<0]"여성";[=0]"공통";"반품" / 결과가 안 나옴....

정답 : "남";"여";"공";"반"

 

•  VBE
  • 폼이 열리지 않는 경우가 종종 발생.
  • Date : 시스템의 현재 날짜를 출력하는 명령어
  • Additem : 셀에 입력되지 않은 데이터를 직접 추가하는 경우에 사용.
  • 사용자 정의 폼이 메모리에서 제거되도록 작성 : 그냥 Unload me
  • [셀].Currentregion.Rows.Count : [셀]과 이어진 범위의 행수를 구함

 

• 타 시트에서 소스를 가져와 지정하는 경우

Private Sub UserForm_Initialize()

    cmb학과명.RowSource = "'기타작업-2'!i5:i8"
    opt1학년.Value = True
    
End Sub

 

• 해당 폼의 캡션 속성을 이용한 메시지 출력

Private Sub cmd종료_Click()

    MsgBox Me.Caption & "폼을 종료합니다."
    Unload Me
    
End Sub

 

• 워크시트 Change
  • 데이터 변경 시, 해당 셀로 셀 포인터 이동 및 폰트 변경

target.activate
target.font.name = "굴림"
target.font.size = 13

 

• With 구문 : 반복되는 변수를 지정하고, 뒤의 명령어만 따로 입력.

Private Sub UserForm_Initialize()

    With cmb분류
        .AddItem "사회과학"
        .AddItem "소설"
        .AddItem "취미/레저"
        .AddItem "컴퓨터"
    End With
    
End Sub

 

• 목록의 첫번째 데이터가 선택 (Initialize)
  • ooo.list(0,0) 

Private Sub UserForm_Initialize()

    lst강의목록.RowSource = "l4:l12"
    lst강의목록 = lst강의목록.List(0, 0)
    
End Sub

 

 

• 채점 프로그램 오류
  • 주문날짜.value로 안 써도 잘 들어갔는데 왜 틀리다고 하는거지?
  • 채점프로그램 매크로, 데이터 유효성 검사, VBE 하자 많은 듯

 

[액세스]

 

- DB구축-

 

•  데이터 가져오기 
  • 범위로 정의된 이름 '~~~'의 내용 : '이름 있는 범위 표시'체크

 

• 100이하의 숫자를 입력하는 가장 적절한 데이터 형식과 필드 크기 : 숫자 / 바이트

 

• 입력마스크 
  • 1자리 문자 + 3자리 숫자 반드시 입력, 연결 문자 저장 : L-000;0
  • 한글 선택적 입력 + 도우미 + * 표시 : ??"도우미";;*

 

• 공백이 입력되지 않도록 유효성 검사

=InStr([사번]," ")=0

 

• "Feb-Sat-2020" 과 같이 입력하기 위한 형식 지정 방법
  • mmm-aaa-yyyy로 하면, "월" (한글 요일)이 나온다.

mmm-ddd-yyyy

 

• 복수의 필드가 나오는 유효성 검사에서는 에러가 발생하는 것 같다.
  • 이 경우, 리본에 있는 속성 시트를 누르고, 유효성 검사 규칙에 입력해야 한다.

 

 

- 입력 및 수정 기능 구현 -

 

• "'포커스'를 이동시킬 수 없도록" : 탭 정지 - 아니오
• 일은 Date가 아닌 Day함수를 쓴다. 
• 메시지 박스 + 보고서 미리보기

• Davg 함수
  • Davg(계산할 필드명, 도메인(테이블 or 쿼리 / 문제에서 제시됨), 조건)

=Davg("성적", "과목별성적","과목명 = txt과목명")

 

• 목록상자 및 속성 변경
  • 1) 텍스트 상자 우클릭하여 변경을 클릭하고, 목록상자 선택
  • 2) 데이터에서 컨트롤 원본 설정하고 제시된 조건 설정
  • 3) 형식 탭에서 열 및 높이 조정

 

• 하위폼 옵션 설정 시, 하위폼의 테두리와 폼 선택기를 클릭할 때의 옵션이 다르다.

 

 

- 조회 및 출력 기능 구현 - 

 

• 조건에 따른 메시지 박스 출력 
  • if, elseif, else 문을 사용해 조건별 결과 표시
• 그룹화 및 정렬 설정 시, 머리글과 바닥글 표시 여부 확인

 

• 매 페이지마다 인쇄되도록  제목을 설정하려면?
  • 보고서 머리글에 있던 제목을 페이지 머리글로 옮긴다!

 

• Dlookup 함수

오답 : =DLookUp("기관명","봉사기관","txt기관명 = txt기관코드")) 

정답 : =DLookUp("기관명","봉사기관","기관코드=txt기관코드"))

 

- 처리 기능 구현 (쿼리) -

 

• 업데이트 쿼리 : 점수 추가라고 하면 '추가'가 아닌 '업데이트'를 선택해야 함.

 

• 크로스탭 쿼리는 문제에 별도 지시없이 나올 수 있다.

 

• 요약 기능을 부여해야 문제없이 표현 가능 (안하면 아래와 같은 에러 - "쿼리에 포함되지 않습니다" 발생)