[컴활2급 올인원] 26 상시 공략 문제 3회

상시 문제를 같이 실습하도록 하겠는데요. 예제 파일 불러와 주시고, 문제 1번의 기본작업 문제 보도록 하겠습니다. 기본작업-1번 시트에는 문제에 제시된 데이터를 입력하는 문제입니다. A3부터 시작하셔서 F12 영역에다가 데이터를 입력해 주시면 되고요. 오타 없이 입력할 수 있다면, 범위를 지정하고 데이터를 입력하셔도 됩니다. 다음은 기본작업-2번 시트 셀 서식을 지정하는 문제의 다섯 문항을 같이 실습하도록 하겠습니다. 첫 번째, A1부터 G1까지 범위를 지정하시고요. 병합하고 가운데 맞춤이라고 되어 있습니다. 홈탭에 가시면, 맞춤이라고 있고요. 병합하고 가운데 맞춤 클릭합니다. 글꼴 크기인데요. 글꼴 크기를 18포인트 선택하시고, 글꼴 스타일을 굵게 하라고 되어 있습니다. 밑줄인데요. 밑줄에 가셔서 첫 번째 있는 밑줄 선택해 주시면 되겠습니다. 다음은 A4부터 G4 영역을 선택하시고, 그 다음, 체육위 색깔을 표준색의 주황이라고 되어 있는데요. 홈에 가시면 글꼴이 있고요. 체육위 색에 가셔서 표준색의 주황을 선택하시고, 텍스트 맞춤을 범위 정한 상태에서 맞춤의 옵션이나 마우스 오른쪽 셀 서식에 가셔서 가로 균등분할 들여쓰기라고 되어 있습니다. 맞춤 탭에 가로에 가셔서 균등분할 들여쓰기를 선택하고 확인 버튼 누릅니다. 다음은 C5부터 C15 영역을 선택하시고, 컨트롤 키 누른 상태에서 E5부터 G15 영역을 선택합니다. 표시 형식을 회계 표시 형식으로 지정하시요라고 되어 있는데요. 범위를 지정하시고 홈 탭에 가시면 표시 형식의 회계 표시 형식이라고 하면요. 회계를 선택하셔도 되고요. 또는 표시 형식의 도구몸에 가셨을 때 회계 표시 형식이라고 있습니다. 클릭하셔도 됩니다. 또는 마우스 오른쪽 셀 서식에 가셔서 표시 형식 탭에 가셔서 회계를 선택하셔도 되겠습니다. 다음은 G3셀을 선택하시고 마우스 오른쪽 셀 서식 메뉴를 클릭하신 다음 사용자 지정에 가시면 날짜에 대한 서식인데요. 연도를 두자리로 표시하겠습니다. yy-mm-dd 라고 입력합니다. 연월일을 두자리로 표시할 수 있도록 yy-mm-dd 입력하시고 확인 버튼을 누릅니다. 다음은 A4부터요. G15 영역을 선택하시고요. 홈에 가시면 글꼴의 테두리에 모든 테두리 클릭하시고 다시 한번 굵은 바깥쪽 테두리 클릭하셔서 범위 정한 영역의 바깥쪽은 굵게 표시될 수 있도록 테두리 서식을 적용해 주시면 되겠습니다. 다음은 기본 작업 다시 3번 시트 클릭하셔서 조건부 서식을 보도록 하겠는데요. A4부터 F20 영역을 선택하시고 홈에 가셔서 스타일의 조건부 서식 새 규칙을 클릭합니다. 수식을 사용하여 서식을 지정할 새 결정 선택하시고요. 그 다음 대출 종류가 주택담보대출이면서 그 다음 대출기관이 24 이상이라는 조건입니다. 두 가지 조건에 만족한 데이터의 서식을 지정하겠습니다. 라고 하시면요. 함수 등호 입력하시고 AND란 함수를 입력합니다. AND라고 입력하시고요. 대출 종류가 있는 C4세를 클릭하되 클릭하면 절대 참조가 됩니다. 라고요. F4를 한번 두번 누르셔서 C10은 고정하시고 행위의 위치는 달라지게 하시고 그 값이 주택담보대출입니까? 라고 첫번째 조건을 주시고요. 그 다음 두번째 대출기관을 선택하시되 위치는 E4세를 클릭 F4를 한번 두번 누르셔서요. E10에 있지만 4, 5, 6, 7 행위의 위치는 달라집니다. 라고 고정해주시고 E10을 고정하시고 그 값이 크거나 같다 24라고 입력합니다. 24보다 크거나 같은 대출기관이 24 이상이라는 조건을 입력하시고 두 가지 조건에 모두 만족하면 우리는 서식을 클릭하셔서 글꼴 스타일을 굵게 라고 표시하시고요. 글꼴 색깔을 표준색의 파랑을 선택합니다. 그리고 나서 확인 다시 한번 확인 버튼 눌러주시면 되겠습니다. 범위정한 영역의 두 가지 조건에 모두 만족한 데이터에만 서식을 지정할 수가 있습니다. 다음은 문제 2번의 계산작업 다섯문항을 보도록 하겠는데요. 첫번째 문제입니다. 표 1에서 GECL을 기준으로 가입기간이 10년 이상이면 별표 5년 이상이면 하얀색 별표 그 다음 나머지는 공백으로 표시하는데 가입기간은 GECL에서 연도를 추출하시고요. 가입일의 연도를 뺀 값이 10년 이상입니까? 라고 조건을 줄 겁니다. 만약에 지금 주어진 함수가 if란 함수를 사용하면 if를 두 번 사용하셔야 되는데 ifs라고 되어 있으니까 ifs 한번 입력하셔서 조건을 나열해 주시면 되겠습니다. 등호를 입력하시고요. ifs 조건에 만족한 데이터에 값을 반환할 건데 s입니다. 조건이 여러개일 때 각각의 조건들을 나열해 주시면 돼요. 그 다음 카로 열고요. 첫번째 조건입니다. 첫번째 조건은 기준에서 가입일 연도를 뺀 이라고 계산을 할 겁니다. CECL을 클릭하시는데 연도를 추출할 거니까 이열 함수부터 먼저 입력할까요? YEAR CECL을 클릭하고 F4 눌러서 절대 참조 그 값에서 마이너스 다시 한번 이열 함수를 통해서 가입일의 날짜의 연도를 추출하겠습니다. 수식을 복사했을 때 CECL은 항상 같은 셀을 참조할 거라서 절대 참조해 주시고요. 두번째 가입일은 수식을 복사할 때마다 값의 변화가 있죠. 상대 참조할 거기 때문에 F4 누르지 않고요. 그 값이 크거나 같습니까? 10이라고 입력하시고 심표 그렇다면 미음 누르고요. 한자키 눌러서요. 검정색 별을 입력하시고 심표 첫번째 조건에 만족하면 검정색 별 그 다음 10년 이상이 아니라면 10년 미만에 해당한 데이터이고요. 그 데이터가 5년 이상입니까? 라고 조건을 주실 건데 IF란 함수를 사용하지 않고요. IFS란 함수를 사용했으니까 조건과 값만 나열하시면 돼요. 안쪽에 이열부터 심표까지만 범위 정하신 다음 컨트롤 C 심표 뒤에다가 컨트롤 V 하시는데 조금 전에 추출한 값 조금 전에 비교한 값 나머지 10년 미만 데이터들 중에서 혹시 5년 이상입니까? 그렇다면 저희는 미음 누르고 한자키 눌러서 하얀색 별을 넣으시고요. 그 다음 나머지에 대해서요. 첫번째 10년 이상도 아니고 5년 이상도 아니고 5년 미만이면 나머지가 추르라면 공백 처리하겠습니다. 라고 큰 단표 열고 닫고 하신 다음 IFS에 대한 가로를 닫으시고 아래까지 수식을 복사해 주시면 되겠습니다. 첫번째 계산식을 작성해 보셨고요. 다음은 두번째 비고를 작성하도록 하겠습니다. E4부터 E10 영역에다가 표시하실 거고요. 점수에 대한 순위를 구하고 비고에 표시하는 문제입니다. 순위를 일단 구할까요? 커서를 E4에 갖다 놓으시고요. 랭크점 EQ라고 입력하시고 점수에 대해서 바로 C4셀의 점수가 심표 본인을 포함한 데이터들 중에서 비교할 데이터는 수식을 복사하더라도 항상 같은 자리입니다. 라고 절대 참조해 주시고요. 그리고 나서 가로 닫고 엔터 누르시면 순위를 구할 수가 있습니다. 아래까지 순위를 복사하시면 1등부터 7등까지 순위가 구해지고요. 문제에서 1등부터 7등까지 값을 나열할 내용이 제시가 되어 있는데요. 추주함수를 사용하라고 되어 있습니다. 등호 다음에 커서를 갖다 놓고요. 추주라고 입력하시고 추주 안쪽에 커서를 두신 다음 함수 삽입 갑니다. 추주함수는 첫번째 인덱스넘버 숫자가 필요하고요. 그 숫자가 1이라면 합격이라고 표시하겠습니다. 2라면 다시 합격이라고 표시하겠습니다. 3, 4는 표시하지 않겠습니다. 공백 처리하시고 4도 공백 처리, 5등도 공백 처리 그 다음 6등에 대해서는 재수강이라고 할 거고요. 7등도 재수강이라고 하겠습니다. 라고 1등부터 7등까지 나열하는데 1등, 2등은 합격 그 다음에 6등과 7등은 재수강 그 사이에 있는 3등부터 5등까지는 공백 처리하시고 확인 버튼 눌러서 아래까지 수식을 복사해 주시면 되겠습니다. 두번째 계산작업 문제 살펴보셨습니다. 다음은 세번째 학과를 표시하도록 하겠는데요. 학생코드와 학과 코드표를 이용해서 학과를 표시하시오라고 되어 있습니다. 인덱스와 매치와 미드함수 사용할 건데요. 일단 미드함수부터 사용할까요? 등호 입력하시고 MID라고 입력합니다. 중간에 있는 글자를 추출할 건데요. 학생코드에 가서요. 신표 세번째부터 시작해서요. 두 글자를 가져오겠습니다. 학생코드의 세번째와 네번째 문자를 추출하기 위해서 세번째부터 두 글자를 추출하시면 첫번째는 PX라고 추출되고요. 아래까지 수식을 복사하시면 CS 또는 SS 해당된 데이터가 추출되어 있습니다. 이 추출한 값이 학과 코드표에 가서요. 코드 영역에서 몇번째 위치하는지를 매치함수를 통해서 상대적인 위치값을 구하도록 하겠습니다. J3에 등호 다음에 커서를 갖다 놓고요. 매치라고 입력하시고 룩업 밸류 조금 전에 찾았던 값이요. 신표 코드 영역에 가서 코드 영역은 수식을 복사하더라도 항상 같은 자리 그 영역에 가서요. 정확하게 일치하는 상대적인 위치값을 구하겠습니다라고 0이라고 입력하시고요. 매치에 대한 가로를 닫고 엔터 아래까지 수식을 복사해 주시면 되겠습니다. 이젠 이 상대적인 위치값을 통해서 학과에 가서 첫번째 행, 두번째 행, 세번째 행에 해당한 학과를 찾아다 표시하도록 하겠는데요. 다시 한번 J3에 커서를 갖다 놓으시고요. 인덱스, 학과 영역에 가서 F3 눌러서 절대창조, 신표, 그 다음 조금 전에 구하셨던 행의 위치값은 매치함수를 구해놓으셨으니까 맨 마지막에 가서 가로 닫고 엔터 눌러주시면 되겠습니다. 물론 열이 하나라서 맨 끝에 신표1을 넣으셔도 되고요. 생략하셔도 됩니다. 맨 끝에 지금 인덱스함수는 첫번째 범위, 두번째 행의 위치, 세번째 열의 위치인데 저는 지금은 생략을 했는데 만약에 열의 위치도 정확하게 넣고자 합니다. 라고 하시면 신표1 넣어주시면 첫번째 열입니다. 라고 넣어주시면 된다는 거고요. 수식을 복사하시면 결과는 동일합니다. 세번째 문제까지 살펴보셨고요. 다음은 네번째 문제 표3을 참조하도록 하겠습니다. 표3에 A25셀에서 선택한 대리점 데이터 유효성 검사를 이용해서 목록이 작성이 되어 있고요. 대리점과 P25셀에서 선택한 제품명 이렇게 선택을 하면 제품 해당한 판매량의 합계와 판매금액의 합계를 아래쪽에 A28과 P28 영역에 계산하시요. 라고 되어 있습니다. 합계를 구할 겁니다. SUM이고요. IF 조건에 만족한 데이터 합계 조건이 하나가 아니라 두 개의 조건이라서 SUMIFS란 함수를 이용해서 작성해 주시면 되겠습니다. SUMIFS라고 입력하시고요. SUMRANGE 합계를 구할 범위는요. 판매량에 대한 데이터 수식을 옆으로 복사했을 때 판매금액으로 바뀔 수 있도록 절대 참조하지 않으셔도 되고요. 그 다음 조건을 찾을 범위 우리는 대리점에 가서요. 대리점은 이따가 수식을 복사하더라도 대리점 영역은 동일합니다. F4 눌러서 절대 참조, 심표 대리점이 있는, 조건이 있는 데이터 A25셀을 선택하고요. 마찬가지로 조건도 수식을 복사하더라도 항상 같은 자리에 있습니다. 라고 F4 다시 심표 두 번째 조건의 범위는 제품명에 가서 범위 정하시고 F4 심표 조건은 건조기라는 셀을 B25셀을 선택하고 F4 눌러서 절대 참조합니다. 그리고 SUMIFS에 대한 가로를 닫으시고 엔터 누르시면 대리점에 해당한 판매량의 합계가 구해지고요. 수식을 복사하시면 판매금액의 합계도 구하실 수 있습니다. 일단 좀 바꿔볼까요? 대리점을 사당으로 바꿨을 때 또는 제품명을 건조기가 아니라 TV로 바꿨을 때 이렇게 값이 달라지는 걸 확인할 수가 있습니다. 다음은 두 곡의 건조기가 하나여서 값이 왜 똑같지? 라고 순간 확인했습니다. 다음은 다섯 번째 문제를 보도록 하겠습니다. 표 3에서 제품명이 건조기인 제품명에 가서 건조기를 찾을 거고요. 제품의 판매금액의 평균을 구하라고 했습니다. 물론 조건에 만족한 데이터의 평균을 구하겠습니다. AVERAGEIF 함수도 사용할 수 있는데요. 지금은 SUMIFS와 COUNTIF를 통해서 평균값을 구하라고 되어 있습니다. 등호 입력하셔서 먼저 평균을 구하기에서는 합계를 구하시고요. SUMIFS 네인지 조건을 찾을 영역에 가서 제품명에 가서 우리는 건조기를 찾겠습니다. 신표 합계를 구할 범위 판매금액에 가서 합계를 가져오겠습니다. 다시 맨 끝에 가셔서 슬래시 조건에 만족한 데이터의 개수를 구하겠다 COUNTIF 제품명에 가서 건조기를 찾아서 건조기 상 제품에 몇 건 있는지 개수를 구해서 나눈 평균을 구하고자 합니다. 라고 수식을 작성하시고요. 엔터 눌러주시면 평균값이 구해진 걸 확인할 수가 있습니다. 다섯 문항의 계산작업 문제를 같이 살펴 보셨습니다. 다음은 분석작업 첫 번째 피버테이블 문제를 같이 실습하도록 하겠는데요. 데이터 안쪽에 커서를 두시고요. 제품 생산 현황표의 데이터를 가지고 피버테이블을 작성하고자 합니다. 삽입해 가셔서 피버테이블 클릭하시고 A3부터 G19셀의 데이터를 가지고 기존 워크시트에 A25셀부터 표시하겠습니다. 라고 표시할 위치를 선택하시고요. 그리고나서 확인 버튼을 누릅니다. 오른쪽에 피버테이블 필드가 표시가 되어 있고요. 필드에 제품 코드를 선택하셔서 필터에 갖다 놓으시고요. 생산 일자를 선택하셔서 행에 갖다 놓으시고 생산 공장을 선택하셔서 열에다 갖다 놓으시고 갑에다가 생산수량의 합계와 불량수량의 평균이네요. 일단 갖다 놓으시고 갖다 놓으시면 합계가 구해지죠. 생산수량의 합계와 불량수량의 평균입니다. 합계의 불량수량에서 마우스 오른쪽 요약기준의 평균을 선택하시면 되겠죠. A25셀부터 시작해서 피버테이블을 표시할 수 있도록 작성하셨고요. 다음은 열의 총합계만 설정하시오라고 되어 있으니까 디자인 탭에 가셔서 총합계에 가셔서 열의 총합계만 설정하시면 세로쪽에 합계만 표시가 됩니다. 생산 일자는 월 단위로 그룹을 작성하시오라고 되어 있는데요. A28셀에서 마우스 오른쪽 그룹을 선택하시고 현재는 일과 월이 선택이 되어 있는데요. 일에 대한 선택을 해제하시고 월만 선택되고 확인 버튼을 누릅니다. 다음은 생산일자 월 단위로 하셨고 그 다음 값표시 형식은 합계의 생산수량에서요. 더블클릭 하시거나 또는 마우스 오른쪽 값필드 설정 누르셔서 표시 형식에 가셔서 숫자에 천 단위 구분 기호를 사용하겠습니다. 라고 체크하시고 확인 다시 한번 확인 버튼 누르시고요. 마찬가지로 평균 분량 수량에서 마우스 오른쪽 값필드 설정이나 더블클릭 하셔서 그 다음 표시 형식에 오셔가지고요. 숫자에 천 단위 구분 기호를 표시하겠습니다. 체크하시고 다시 한번 확인 버튼 눌러주시면 되겠습니다. 피버 테이블 문제를 같이 살펴 보셨습니다. 핵레이블 열 레이블 맞게 표시가 되어 있나 확인하시면 되겠습니다. 다음은 분석작업 다시 2번 문제 부분한 문제를 같이 보도록 하겠는데요. 부분한 문제가 나왔다라고 하시면 가장 먼저 하셔야 될 것은 정렬입니다. 정렬은 지점을 기준으로 오름차순으로 정렬하시요. 데이터 안쪽에 커서를 갖다 놓으시고요. 지점쪽에 암호셀 하나만 선택하시고 데이터에 가셔서 오름차순 정렬은 정렬미 필터에 텍스트 오름차순 정렬 클릭합니다. 다음은 데이터에 가셔서 개호의 부분합을 클릭하신 다음 그룹할 항목에 지점을 선택하시고 지점별 총 판매량에 최대값을 계산하겠습니다 라고 최대를 선택하시고 우리는 확인 버튼을 누릅니다. 그 다음 다시 한번 개호의 부분합을 클릭하셔서 두번째 평균을 계산하겠습니다 라고 선택하시고 총 판매량이 아니라 전반기 판매량 후반기 판매량 조금 전에 구하셨던 최대값도 표시하고 지금 구할 평균도 표시할 거라서 새로운 값으로 대치라는 체크를 해제하시고요 확인 버튼을 누릅니다. 다음은 서식을 적용하기 위해서 우리는 안쪽에 커서를 두신 다음에 폼에 가시면 표 서식이라고 있구요 연한 파랑에 표 스타일 파랑에 표 스타일 밝게 6 이라고 되어 있습니다. 선택하시고 범위가요 A3부터 E25 영역이 선택될 수 있도록 범위 수정이 필요하시면 드래그 하시고 아니면 처음부터 범위를 지정하고 오셔도 돼요. 그리고 나서 확인 버튼 눌러주시면 문제와 같이 표 서식을 적용할 수가 있습니다. 최대와 평균은 문제에 나와 있는 순서대로 작성을 해주시면 되겠죠. 분석작업의 두번째 문제 살펴 보셨구요 다음은 기타작업 첫번째 매크로 문제를 보도록 하겠습니다. 매크로를 기록한 다음 연결하실 내용은 첫번째 단추입니다. 개발도구에 가셔서 삽입에 가셔서 양식 컨트롤에 단추를 선택합니다. 그 다음 B15부터 C16 영역에다가 알트키 누른 상태에서 드래그 하시구요 매크로 이름을 총액이라고 입력하시고 기록을 클릭합니다. 매크로 기록 계화상자에서 총액이라고 표시되면 확인 버튼 누르시구요 커서를 F4에 갖다놓으시구요 진찰료 더하기 검사비 엔터 아래까지 수식을 복사합니다. 다음은 개발도구의 기록 중지 버튼 누르시구요 단추 1에서 마우스 오른쪽 텍스트 편집을 누르셔서 총액이라고 입력합니다. 다음 두번째 매크로는 사각형 빗면에다가 연결할 겁니다. 삽입에 가셔서 기본 도형의 사각형의 빗면을 선택하시고 E15부터 알트키 누른 상태에서 F16 영역에 드래그 하시구요 텍스트를 서식이라고 미리 입력하시구요 도형에서 마우스 오른쪽 매크로 지정을 클릭하신 다음 매크로 이름을 서식이라고 입력합니다. 기록을 클릭하시고 매크로 기록에 서식이라고 표시가 된다면 확인 버튼 누르시구요 이제 서식을 적용할 영역은 C4부터 C13 영역을 선택하시고 봄에 가시면 표시 형식의 날짜라고 되어 있는데요 간단한 날짜를 선택합니다. 그 다음 개발도구의 기록 중지 버튼 누르시면 서식이라는 매크로 기록을 종료할 수가 있습니다. 두문항의 매크로 기록과 연결 작업까지 하셨구요 다음은 차트 작업 문제를 보도록 하겠습니다. 차트 제목을 표시하기 위해서 차트를 선택하시구요 차트 요소에 가셔서 차트 제목을 클릭합니다. 그 다음 차트 제목에다가 진로 체험 학교라고 입력하시고 분야별 신청 인원이라고 입력합니다. 그 다음 차트 종류는 원형 대 가로 막대형이라고 되어 있습니다. 차트에서 마우스 오른쪽 차트 종류 변경에 가셔서 원형을 선택하시구요 원형 대 가로 막대형을 선택하고 확인 버튼을 누릅니다. 데이터 레이블입니다. 차트 요소에 가셔서 데이터 레이블에 가셔서 기타 옵션을 선택하시고 항목 이름과 갑에 대한 선택을 해제하시고 백분율, 그 다음 바깥쪽 끝에다가 표시하겠습니다. 라고 선택해 주시면 되겠습니다. 다음은 법내는 표시하지 않는다. 아래쪽에 법내를 선택하고 딜리트 누르셔도 되구요 그 다음 데이터 계열 분할 안쪽에 원형 차트 하나를 선택하시구요 데이터 계열 서식이 표시가 되구요 계열 분할에서 문제에서 백분율 값으로 되어 있습니다. 백분율 값을 선택하시면 되겠습니다. 차트 영역은 기본 설정 2로 설정하라고 되어 있습니다. 차트 영역 하얀색 부분을 선택하시구요 서식에 가셔서 도형 효과에 미리 설정에 기본 설정 2를 선택하시고 차트 영역 서식의 테두리에 가셔서 둥근 모서리 체크하셔서요 모서리를 둥글게 체크해 주시면 되겠습니다. 다섯문항의 차트에 대한 서식 작업하셔서 작성된 차트와 문제 제시된 차트를 비교해 보시면 되겠습니다. 기본 작업부터 기타 작업 문제를 같이 살펴 보셨습니다.