015_컴활_엑셀 매크로(6) : 데이터표

컴퓨터 활용능력 시험은 다른 자격증 시험과 다르게 문제은행이라 하지만, 출제문제도 다양하고 깊이도 있어, 쉽게 합격하기 어려운 시험인데요, 특히 저와 같은 6학년에 가까운 사람에게는 눈도 돋보기를 써야하고, 그리고 남들 보다 타이프도 늦어 절대적으로 시험시간이 부족하기 때문에 저의 경우는 나중에는 100점을 목표로 하지 않고 77점을 목표로 하였습니다.

 

기본작업 4문항이 만점이 25점인데, 다 맞는 경우는 드물고 그래서 한문제는 틀린다고 생각해서 20점으로, 피벗테이블과 매크로의 분석작업 문제 2문항 만점 20점에서 20점을 다 맞고, 기타작업에서 차트와 프로시저를 합쳐 25점 만점에 20점, 모두 합하면 65점인데, 계산문제 5문제 30점에서 2문제인 12점으로 합하여 77점을 목표로 시험공부를 하였습니다.

 

실제 처음 1~2번 시험은 문제유형이 익숙하지 않아 당황을 많이 하였고, 시간도 절대적으로 부족하였기 때문에 3~4번째부터 이런 전략으로 시험으로 보아 5번째 시험, 6번째 시험 모두 합격하였는 데요, 아마도 어려운 문제는 포기하고, 쉬운 문제는 확실히 맞는 수험이 전략이 정말 컴활 자격증에는 도움이 되었던 것 같습니다.

 

오늘은 엑셀 매크로와 함께 하는 6번째 데이터표 기능에 대하여 소개하고자 합니다

 

 

 

01. 데이터표란

 

워크시트에서 특정 데이터를 변화시켜 수식의 결과가 어떻케 변화하는 지 보여주는 셀 범위를 말합니다.

 

 

02. 데이터표 작성방법

 

데이터 표의 수식은 데이터 표를 작성하기 위해 필요한 변수가 하나인지 두 개인지에 따라 수식의 작성위치가 달라집니다.

 

01) 변수가 열 또는 행 방향으로 한쪽에만 입력되었을 경우 : 수식은 표 범위의 두 번째 행이나 두 번째 열에 입력합니다

 

① B2에 변수 A1*10%을 입력하고 ② 행방향으로 C2에 A1*50%을 입력합니다.

 

③ 데이터 표를 수행하기 위한 범위를 [A2:C6]영역을 지정합니다. ④ 데이터탭 - ⑤ 데이터도구 그룹 - ⑥ 가상분석을 클릭한 후 ⑦ 데이터표 매뉴를 실행됩니다.

 

⑧ 데이터 표가 표시되면 ⑨ 열입력셀에 $A$1을 입력하고 ⑩ 확인을 누르게 되면, ⑪데이터표가 완성되어 표시됩니다.

 

 

 

02) 변수가 열과 행방향으로 양쪽에 입력되었을 경우 : 수식은 표가 시작되는 셀에 입력합니다. 여기에 사용되는 예제는 다음의 문제 01)을 참조하십시오.

 

문제 01 (컴퓨터활용능력 기출문제 2019-A) 매크로를 현재 통합문서에 작성하고 실행하시오(각 5점)   ① 데이터표 기능을 이용하여 국어와 수학 점수별 총점을 [C10:L19]영역에 구하는 ‘데이터표’ 매크로를 작성하시오. * 데이터표를 실행하기 위해 입력하는 수치도 매크로로 기록되도록 작성하시오   ② ‘기본도형’의 ‘빗면‘을 [I3:K5] 영역에 생성한 후 텍스트를 “데이터 표 실행”으로 입력하고, 텍스트 맞춤의 가로 ’가운데‘, 세로 ’가운데‘ 로 설정하며, 도형을 클릭하면 ’데이터표‘ 매크로가 실행되도록 설정하시오

 

   

03. 데이터 표 매크로 작성

 

① 데이터표 기능을 이용하여 국어와 수학 점수별 총점을 [C10:L19])영역에 구하는 ‘데이터표’ 매크로를 작성하시오. * 데이터표를 실행하기 위해 입력하는 수치도 매크로로 기록되도록 작성하시오

 

데이터 표 작성을 엑셀 화면에서 실행하면, 실행하는 그대로 컴퓨터에서 ‘데이터표’ 라는 매크로란 이름으로 저장됩니다. 매크로 기록이 시작되면, 상단의 메뉴도 ‘매크로 기록’에서 ‘기록중지’로 바뀌게 됩니다

 

01) 매크로 기록준비 

 

① 엑셀의 데이터에 있지 않은 임의의 셀을 선택한 후 시작합니다. 데이터 범위내에서 매크로를 작성하게 되면, ‘원본 참조가 대상영역과 중복되었습니다’는 오류메시지가 뜨게 되기 때문에, 반드시 셀포인터를 데이터가 있지 않은 임의의 빈 셀을 선택한 후 시작해야 합니다.  

 

② 메뉴 상단의 개발도구를 클릭하고, ③ 코드탭에서 ④ 매크로 기록을 선택합니다. ‘매크로 기록’ 대화상자가 표시되면, ⑤ 매크로 이름을 ’데이터표‘ 로 작성합니다. ⑥ 바로가기 키를 지정하고자 할 경우에는 기본적으로 Ctrl이 지정되어 있어, 지정하고자 하는 키는 영문자만 입력하면 됩니다. 지정하지 않아도 됩니다. ⑦ 매크로 저장위치는 개인용 매크로 통합문서, 새통합문서, 현재 통합문서 등 3가지 방법으로 저장할 있으며, 이 문제에서는 ’현재통합문서‘로 작성하도록 하고 있습니다. ⑧ 확인을 누르면 매크로 기록이 진행됩니다

 

 

 

02) 매크로 생성  

 

데이터 표를 수행하기 전에 수식과 범위를 지정합니다. ② 문제에서 데이터 표 작성을 위한 변수가 열과 행방향으로 양쪽에 입력되었기 때문에 수식은 표가 시작되는 [B9] 셀에 =SUMPRODUCT(C4:F4,C5:F5) 수식을 입력합니다. ③ 수행하기 위한 범위로 [B9:L19] 영역을 지정한 후 ③ 데이터 - ④ 데이터 도구탭의 ⑤ 가상분석을 선택하게 되면 ⑥ 표시된 데이터표 매뉴를 클릭합니다.

 

클릭하게 되면 ⑦ 데이터 표 대화상자가 표시되며, ⑧ 행입력셀에 국어점수인 $C$4, ⑨ 열입력셀에 $E$4를 입력한 후 ⑩ 확인을 누릅니다. 

 

 

 

확인키를 누르게 되면 ⑪ 데이터 표가 완성됩니다. 매크로기록이 완료됩니다.

 

 

 

⑫ 상단 메뉴의 매크로 기록에 있던 자리에 매크로 중지를 클릭하게 되면 모든 매크로 기록이 끝나게 됩니다.

 

 

03) 매크로 실행단추 생성 : 도형을 매크로와 연결하는 단계

 

② ‘기본도형’의 ‘빗면‘을 [I3:K5] 영역에 생성한 후 텍스트를 “데이터 표 실행”으로 입력하고, 텍스트 맞춤의 가로 ’가운데‘, 세로 ’가운데‘ 로 설정하며, 도형을 클릭하면 ’데이터‘ 매크로가 실행되도록 설정하시오

 

① 도형범위인 [I3:K5]을 셀포인트로 미리 영역을 지정한 후 ② 삽입 ③ 일러스트레이션 탭의 도형을 선택하게 되면 ④ 사각형 그룹이 나타나며, ⑤ 이 사격형 그룹중에서 ‘빗면’을 선택합니다.

 

 

⑥ 도형인 빗면을 [I3:K5] 영역에 쉽게 맞추는 방법으로 ALT키를 누르면서 미리 지정한 영역인 I3에서 K5까지 드래그하게 되면 해당영역에 도형인 빗면이 생성되게 됩니다. 텍스트로 ‘데이터 표 실행’를 입력하고, 텍스트 맞춤의 가로 ’가운데‘, 세로 ’가운데‘ 로 설정하며 ⑦ 오른쪽 마우스를 클릭하여 매크로 지정을 클릭한 다음 ⑧ ‘데이터표’ 매크로를 지정합니다.

 

 

이상으로 엑셀 매크로 : 데이터표 작성과 관계된 설명이었습니다.

 

<컴활_실기1급_엑셀_15_매크로_데이터표> 파일을 첨부합니다, 앞의 예제와 기출문제2개를 추가하였습니다. 실습을 하시고 싶은 분은 다운로드 저장한 후 실습하시면 됩니다. 비밀번호는 앞의 블로그에 적힌 비밀번호를 사용하시면 됩니다.   ! 파일다운로드 → 컴활_실기1급_엑셀_15_매크로_데이터표.xlsx

 

 

문제 02 ) 매크로를 현재 통합문서에 작성하고 실행하시오(각 5점)

 

① 목표금액[B8]을 달성하기 위하여 정기적금을 가입하려 한다. [데이터]-[표]를 이용하여 (년)이자율[D9:D19] 변동에 따른 월 불입금액[E9:E19]을 계산하는 매크로를 생성하고, 매크로 이름은 ‘이자율계산’으로 정의하시오.

② ‘이자율계산’ 매크로는 [도형] - [기본도형]의 ‘빗면’ 에 지정하며, 동일시트 [B2:C3] 영역에 위치시키시오.

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

 

   

 자동필터, 시나리오와 함께 하는 매크로 실행에 대하여는 다음 블로그에 이어 계속 진행됩니다.