ITQ엑셀 자격증 기출문제풀이[제1작업: 함수 및 조건부 서식]알아보기

ITQ엑셀 자격증 제1작업의 "함수 및 조건부 서식"에 대해 알아보겠습니다. 이 전 글에서는 가장 기본적인 데이터 입력과 다양한 서식을 적용하여 풀이를 해보았는데요. 이어서 오늘 총 6개의 함수와 1개의 조건부 서식을 작업하면 제1작업이 마무리됩니다.

 

먼저 ITQ엑셀 자격증 시험을 준비하기 위해서 2024년 1월 [2024101회차] 한글엑셀 문제를 다운로드하시고 이전에 작성했던 "답안작성요령" 알아보기와 "제1작업의 데이터작성 및 서식" 알아보기 글을 읽고 작업을 해 주시기 바랍니다.

ITQ엑셀기출(2024.01.13).zip

0.77MB

 

 

1. 성별 

※ 주민번호 8번째 값이 1이면 '남', 2이면 '여', 3이면 '남', 4이면 '여'로 구하시오(CHOOSE, MID 함수).

 

• CHOOSE : 숫자(번호)를 지정하면 인수 목록 중 해당 값을 불러와 주는 함수(즉, 숫자 1이 오면 값 1'남'을, 2가 오면 값 2 '여'를, 3이 오면 값 3'남'을, 4가 오면 값 4'남'을 불러와 주는 함수!!)

• MID : 중간에 있는 글자를 추출해 주는 함수(즉, 주민번호 중간에 있는 8번째부터 1글자 추출)

• =Choose(입력하고 fx함수마법사를 클릭합니다.

 

 

• 함수 마법사 창이 뜨면 Index_num에 숫자가 오는데, 이때 숫자는 Mid함수를 이용하여 주민번호의 8번째에서 1글자 가져옵니다.

• =Mid(입력하고 수식입력줄의 MID를 클릭합니다.

 

 

• 함수 마법사 창이 Mid함수로 바뀌면 Text에 주민번호(C5)셀 클릭, Start_num에 추출할 글자위치(8), Num_chars에 추출할 글자수(1)를 입력합니다. 즉, 주민번호에서 몇 번째부터 몇 글자 가져올 거죠?(주민번호에서 8번째부터 1글자)를 생각하면 됩니다.

 

• 다시 수식입력줄의 Choose함수를 클릭하면 함수마법사 창이 Choose함수로 바뀝니다

• 나머지 Value값을 차례로 입력합니다. 

 

 

• [확인] 버튼을 클릭하고 I5셀에 채우기 핸들 사각점에서 클릭 드래그하여 나머지 값을 채워줍니다.

 

 

• 자동 채우기를 하면 굵은 바깥 테두리 서식까지 복사되기 때문에 [자동 채우기 옵션] +버튼을 클릭하여 [서식 없이 채우기]를 선택합니다.

 

2. 치료부위

※ 관리번호 첫 번째 글자가 S이면 '어깨', K이면 '무릎', 그 외에는 '허리'로 구하시오(IF, LEFT 함수).

• IF : 조건식에 만족하면 참값을 불러오고 조건에 만족하지 않으면 거짓값을 불러와 주는 함수(즉, 조건식, 참값, 거짓값으로 구성!!)
• LEFT : 왼쪽에서부터 글자수만큼 문자를 추출해 주는 함수(즉, 문자를 추출할 셀, 추출할 글자수로 구성!!)
• =if(입력하고 fx함수마법사를 클릭합니다.

 

 

• 함수 마법사 창이 뜨면 Logical_test에 조건식을 입력해야 하는데, 이때 left함수를 이용하여 관리번호의 왼쪽에서 1글자를 추출하여 조건식을 만들어 줍니다.
• =Left(입력하고 수식입력줄에서 Left함수를 클릭합니다.

 

 

• 함수 마법사 창이 Left함수로 바뀌면 Text에 관리번호(B5)셀 클릭, Num_chars에 추출할 글자수(1)를 입력합니다. 즉, 관리번호에서 몇 글자를 가져올 거죠? (관리번호 왼쪽에서 1글자)를 생각하면 됩니다.

 

 

• 다시 수식입력줄에서 if함수를 클릭하면 함수 마법사 창이 if함수로 바뀝니다.
• Logical_test에 입력된 Left함수식에 ="S"입력하여 조건식을 완성시킵니다.(S는 문자기 때문에 큰따옴표를 붙임)
  조건식 해석→ 관리번호의 왼쪽에서 1글자를 추출하여 "S"와 같다면 이라는 뜻입니다.
• Value_if_true에 참값인 "어깨"를 입력합니다.
• Value_if_false에 거짓값이 입력되어야 하는데 또 다른 조건이 주어졌기 때문에 if함수를 한번 더 입력하여 두 번째 조건을 입력합니다.

 

 

• Value_if_false에 if(를 이력하고 수식입력줄에서 두 번째 if함수를 클릭합니다.
• Logical_test에 입력된 Left함수식에 ="K"입력하여 조건식을 완성시킵니다.
  조건식 해석→ 관리번호의 왼쪽에서 1글자를 추출하여 "K"와 같다면 이라는 뜻입니다.
• Value_if_true에 참값인 "무릎"를 입력합니다.
• Value_if_false에 거짓값이 "허리"입력합니다.

 

 

• [확인] 버튼을 클릭하고 J5셀에 채우기 핸들 사각점에서 클릭 드래그하여 나머지 값을 채워줍니다.

 

•  자동 채우기를 하면 굵은 바깥 테두리 서식까지 복사되기 때문에 [자동 채우기 옵션] +버튼을 클릭하여 [서식 없이 채우기]를 선택합니다.

 

3. 도수치료 치료횟수(1주) 평균

※ 도수치료 치료횟수(1주)평균을 구하시오 → 단, 조건은 입력데이터를 이용하시오(DAVERAGE 함수).

 

• DAVERAGE : 전체범위의 해당열에서 조건범위에 만족하는 평균을 구해주는 함수(즉, 전체범위, 열번호, 조건범위로 구성됨!!)
• =Daverage(입력하고 fx함수 마법사를 클릭합니다.

 

 

• Database에 제목행을 포함한 전체범위 입력합니다.
• Field에 평균을 구할 값의 열번호를 입력합니다(치료횟수의 평균을 구해야 하니 치료횟수가 몇 번째 열에 있는지 세어서 번호를 적어줌!!).
• Criteria에 치료구분이 도수치료인 것이 조건이므로 입력한 표 데이터에서 조건범위를 잡아줍니다. 

 

 

4. 가장 많은 치료횟수(1주) 

※ 가장 많은 치료횟수(1주)를 구하시오 → 정의된 이름(치료횟수)을 이용하여 구하시오(MAX 함수).

 

• MAX : 지정한 범위에서 최댓값을 구해주는 함수(가장 큰 값 구해줌)
• =Max(입력하고 치료횟수(1주)범위 H5:H12영역을 클릭 드래그 하여 범위를 지정해 줍니다. 그러면 이름정의를 해놨기 때문에 정의된 이름(치료횟수)으로 자동 입력됩니다. 괄호 닫고 엔터를 누릅니다(간단한 함수기 때문에 fx함수마법사를 이용하지 않았음).

 

 

5. 운동치료 환자 수 

※ 운동치료 환자 수 → 결과값에 '명'을 붙이시오(COUNTIF 함수 & 연산자)(예 : 1명).

• COUNTIF : 조건에 만족하는 셀의 개수를 세어줍니다(즉, 조건이 들어있는 범위, "조건"으로 구성됨).
• =Countif(를 입력하고 fx함수마법사를 클릭합니다.

 

 

• Range에 조건이 들어있는 범위를 지정해 줍니다(조건이 '운동치료'이므로 치료구분 범위를 지정!!). 
• Criteria에 조건 "운동치료"를 입력합니다(조건은 큰따옴표로 묶어서 입력하기!!).
  → 조건이 들어있는 범위(E5:E12)에서 "운동치료"가 몇 개인지 셀의 갯수를 세어줍니다.

 

 

• [확인] 버튼을 누르고 나와서 수식입력줄에 입력되어 있는 =COUNTIF(E5:E12,"운동치료")식에 &"명"을 입력합니다.

 

 

6. 치료시작일 

※ 「H14」 셀에서 선택한 관리번호에 대한 치료시작일을 구하시오(VLOOKUP 함수)(예 : 2024-01-01)

 

• VLOOKUP : 열에서 원하는 값을 찾아 나타내고자 하는 값을 추출해 주는 함수(즉, 찾을 값, 찾을 값이 시작되는 범위, 나타내고자 하는 값의 열번호, 정확한 값 0으로 구성)
• =Vlookup(를 입력하고 fx함수마법사를 클릭합니다.

 

 

• Lookup_value에 찾을 값(H14) 셀을 입력합니다.
• Table_array에 찾을 값이 관리번호(H14)이므로 관리번호 B5부터 나타내고자 하는 치료시작일 F12열까지 범위지정합니다. 
• Col_index_num에 나타내고자 하는 값(치료시작일)이 몇 번째 열에 있는지 열 번호를 세어 적어줍니다.
• Range_lookup에 정확한 값을 찾을 것이기 때문에 0을 입력합니다.
  → Vlookup함수는 항상 나오는 함수기 때문에 반드시 알고 넘어가기!! 

 

 

7. 조건부 서식

※ 조건부 서식의 수식을 이용하여 1회비용이 '85,000' 이상인 행 전체에 다음의 서식을 적용하시오(글꼴 : 파랑, 굵게).

 

• 문제에 행 전체에 적용하라고 했기 때문에 B5:J12영역을 클릭 드래그하여 범위를 지정합니다. [홈]탭- [조건부 서식]명령을 클릭하여 [새 규칙] 메뉴를 선택합니다.

 

 

• 규칙 유형을 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 식을 작성합니다.
• =입력하고 1회 비용의 첫 번째 값(G5셀)을 클릭하고 F4키를 두 번 눌러 열만 고정($G5)하는 혼합참조를 만들어 줍니다. 그리고 이상(>=) 크거나 같다 기호를 입력하고 '85000'을 입력하여 조건식을 입력합니다.
• 조건식이 잘 들어갔으면 클릭합니다.

 

 

• [글꼴]탭 - 글꼴 스타일을 굵게, 색을 표준색에 파랑을 선택하고 [확인] 버튼을 클릭합니다.

 

 

• 자 그러면 1회 비용 값이 85,000원 이상인 셀에 해당하는 행 전체의 값에 서식이 적용되었습니다. 

 

 

8. 마무리

지금까지 제1작업의 함수 및 조건부 서식을 적용해 보았습니다. 처음 보는 함수는 당연히 어렵게 느껴질 수 있지만 천천히 읽어보고 여러 번 반복해서 풀다 보면 자주 나오는 함수들은 금방 익숙해질 것입니다. 

다음 시간에는 제2작업 목표값 찾기 및 필터(80점)에 대해서 알아보겠습니다.