통계 함수 : COUNTIFS

1. 통계함수: COUNTIFS • 여러 조건을 만족하는 셀의 수를 구하는 함수 COUNTIFS( range1, criteria1 , range2, criteria2 , range3, criteria3 , …) • 각 범위rangei마다동일 위치의 셀이 해당 criteriai조건을 모두 만족하는 셀의 수를 구하는 함수 즉 i개의 조건을 모두 만족하는 셀의 개수를 구함 • 주) 각 범위마다 행과 열 수가 같아야 한다. • 예: 1사분기 실적이 30이상 40미만인 사람의 수 = COUNTIFS( C2:C8, “>=30”, C2:C8, “<40”) → 1 • 예: 1사분기와 2사분기 모두 실적이 45이상인 사람의 수 = COUNTIFS(C2:C8, “>=45”, D2:D8, “>=40”) → 2 • p.39의 countif 함수의 뺄셈을counitfs로간단히 해결 가능 = 10~19까지의 수 = 10이상 수 – 19초과 수 = COUNTIF( $C$2:$C$21, ">="&A33) – COUNTIF($C$2:$C$21, ">"&B33) = COUNTIFS( $C$2:$C$21, ">="&A33, $C$2:$C$21, ">"&B33)

2. 수학 함수: SUMIF • 특정 조건을 만족하는 경우에만 지정된 셀의 합을 구하는 함수  ‘조사범위’ 셀 중 ‘조건’을 만족하는 셀을 찾았다면 → ‘더할셀범위’ 중 동일 위치의 셀들을 합한 값 구하기 • 예) 합격자들의 총점 = SUMIF( C2:C6, “=합격”, B2:B6 ) • 예) 합격자들의 평균은? = SUMIF(C2:C6, “=합격”, B2:B6) / COUNTIF(C2:C6, “=합격”) • 예) 세 번째 인수의 생략 예 range, criteria, [sum_range] SUMIF( 조사범위 , 조건, 더할셀범위) 실제 덧셈을 할 셀들의 범위 조사범위와 동일하면 생략가능 “ “안에 조건 지정 뒤의조건 만족 여부를 조사할 셀 범위

3. 수학함수: SUMIFS • 여러 조건을 만족하는 경우에만 지정된 셀의 합을 구하는 함수  각 ‘조사범위’마다 해당 ‘조건’을 모두 만족하는 셀위치를 찾았다면 → ‘더할셀범위’ 중 동일 위치의 셀들을 합한 값 구하기 • 예)1사분기 실적이 40이상인 여자들의 2사분기 실적의 합계 = SUMIFS (D2:D8, B2:B8, “=여”, C2:C8, “>=40” ) sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] SUMIFS( 더할셀범위, 조사범위1, 조건1, 조사범위2, 조건2, …) “ “안에 조건 지정 실제 덧셈을 할 셀들의 범위 뒤의조건 만족 여부를 조사할 셀 범위

4. 통계 함수: AVERAGEIF, AVERAGEIFS • 특정 조건을 만족하는 경우에만 지정된 셀의 평균을 구하는 함수  ‘조사범위’셀 중 ‘조건’을 만족하는 셀을 찾았다면 ‘평균구할범위’중 동일 위치의 셀들의 평균 구하기 : sumif()/countif() 와동일한 결과를 구함 • 예) 합격자들의 평균은? = AVERAGEIF( C2:C6, “=합격”, B2:B6 ) = SUMIF(C2:C6, “=합격”, B2:B6) / COUNTIF(C2:C6, “=합격”) • 여러 조건을 만족하는 경우에만 지정된 셀의 평균을 구하는 함수 • 예) 1사분기 실적이 40이상인 여자들의 2사분기 실적 평균 = AVERAGEIFS( (D2:D8, B2:B8, “=여”, C2:C8, “>=40” ) range, criteria, [sum_range] AVERAGEIF( 조사범위 , 조건, 평균구할범위) 실제 평균을 구할 셀들의 범위 “ “안에 조건 지정 뒤의조건 만족 여부를 조사할 셀 범위 average_range, criteria_range1, criteria1, [criteria_range2, criteria2] AVERAGEIFS( 평균구할범위, 조사범위1, 조건1, 조사범위2, 조건2,…) “ “안에 조건 지정 실제 평균을 구할 셀 범위 뒤의조건 만족 여부를 조사할 셀 범위

5. 활용예: Countif, Sumif, Countifs • 응답번호별 인원 및 평균나이 구하기 • 전체 응답번호 중 1([A3])인개수 • = COUNTIF($B$2:$B$21, A33) • (응답번호가 1인 사람들의 나이 합)/ (응답번호가 1인 사람 수) • = SUMIF(응답번호, A33, 나이) / B33 • = AVERAGEIF(응답번호, A33, 나이) • 응답번호 : [B2:B21] 범위에지정한 이름 • 나이 : [C2:C21]에 지정한 이름 • → 이름 대신 직접 범위를 지정해도 된다 (이름정의는 p.62 참고). • 피벗 테이블 이용하기(p.73참고)

6. 활용예: Countifs • 응답번호별 연령대별 인원 구하기 • 1이라고 응답한 10살 이상 20살 이전 사람의 수 • [A42] [B42] [B42] + 10 • =COUNTIFS(응답번호, A42, 나이, ">="&B42, 나이, "<"&B42+10) • 피벗 테이블 이용하기(p.73참고)

7. 통계 함수 • 최댓값 MAX(값1, 값2, …) • 인수 중 최댓값 • 최솟값 MIN(값1, 값2, …) • 인수 중 최솟값 • 평균 AVERAGE(값1, 값2, …) • 인수들의평균값 • 중위치 MEDIAN(값1, 값2, …) • 인수를 오름차순으로 나열했을 때 가장 중간 위치에 있는 값 • 인수가 짝수개라면 중간 두 값의 평균이 결과값 • 최빈치 MODE.SNGL(값1, 값2, …) • 인수 중 가장 많이 발생한 값 1개 • 최빈치들 MODE.MULT(값1, 값2, …) • 인수 중 가장 많이 발생한 값 여러 개를 세로 배열로 반환 • 배열수식이여야 함 <Ctrl + Shift+ Enter> • 분산VAR.S(값1, 값2, …) • 인수들(표본집단)의 분산 • VAR.P 함수는 모집단인 경우의 분산을 구한다. • 표준편차 STDEV.S(값1, 값2, …) • 인수들(포본 집단)의 표준 편차 • STDEV.P 함수는 모집단인 경우의 표준 편차를 구한다.

8. 활용예: 통계치 구하기 최빈치들을 표시할 셀 위치 [B24:B25]를 범위로 잡기 = MODE.MULT(B2;B11) 수식입력 후 <Ctrl + Shift + Enter> 눌러배열 수식 완성하기

9. 텍스트 함수: LEFT, MID, RIGHT • 숫자가 아닌 텍스트(“ “로 묶은 것)로 부터 정보를 추출하는 함수 • LEFT(text, num_chars) • text의 왼쪽 끝에서 num_chars개의 문자들 • LEFT(“Sale Price”, 4)  “Sale” • MID(text, start_num, num_chars) • 텍스트인 text의 start_num번째부터 num_chars개의 문자들 • MID(“Sale Price”, 6, 3)  “Pri” • RIGHT(text, num_chars) • text의 오른쪽 끝에서부터 num_chars개의 문자들 • RIGHT(“Sale Price”, 5)  “Price” • 예) • [A1]의 급여번호 첫 글자가 “I”라면 “인턴”, 아니라면 “직원”이 되게 하려면? = IF( LEFT(A1, 1) = “I”, “인턴”, “직원”) • [B1]의 8째 문자로부터 성별 구하기 = IF( OR( MID(B1, 8, 1) = “1”, MID(B1, 8, 1) = “3”) , “남”, “여”) • 주) 실제 [B1]에입력된 주민등록번호에 ‘-’가 실제로포함된경우가 아니고 셀 서식의 표시형식에서 “-”가 표시되게 한 것이라면 7번째부터 1개의 문자 즉 MID(B1, 7, 1)로 구한 문자로부터 남녀를 구분해야 한다. LEFT 함수는 텍스트 함수로서 결과가 텍스트므로 반드시 “”로 묶어야 한다.

10. 날짜 및 시간 함수: TODAY, NOW • 엑셀의 날짜 데이터에 대한 일련(serial) 번호 개념 • 1900년 1월 1일 일련번호 1 • 1900년 1월 3일 일련번호 3 • 2009년 1월 1일 일련번호 39814 • 날짜는 위와같이 일련번호로 관리하며 사용자가 원하면 년월일로 계산하여날짜형식으로표시한다. • TODAY( ) • 오늘 날짜의 일련번호를 구해줌(파일을 열 때마다 현재의 날짜를 구함) = TODAY( )  이 셀의 표시형식을 “날짜”로 지정하면 일련번호로부터 계산하여 “2012-02-14”와 같이 표시한다. ※ <Ctrl + ;>은 입력 당일의 날짜로 고정된다. • NOW( ) • 현재 날짜와 시간을 구해줌 =NOW( )  표시형식에 따라 2008-05-25 14:52와 같이 표시할 수 있다. • 날짜로부터년, 월, 일 구하기 • 지나간일 수 구하기  365 ([A1]~[A2]까지지나간 일) •  [A1]~오늘까지지나간 일: 매일 변함 Tip) 셀에결과값이 1900-10-07로 표시되면 셀 표시형식을 숫자로 바꾸어주면 지나간 일 수가 표시된다. 즉 수식은 정확하지만 엑셀이 수식에 포함된 함수나 셀 내용에 따라 자동으로 표시형식을 지정하여 이상한 결과가 나온다면 표시형식을 확인하고 원하는 형태로 지정한다.

11. 활용 예: 텍스트와 날짜 함수 • 납품 물건의 코드명으로부터 물품명과 납품까지 남은 날짜를 입력하기 • 품명은 코드가 ON으로 시작하면 노트, OP로 시작하면 복사지 = IF(LEFT(C4, 2) = "ON", "노트", "복사지") • 남은 일은 ‘납품예정일 - 오늘’ = B4 - TODAY() ※ 표시형식에 주의한다. → 결과가 날짜 형태로 표시되면 일반 서식으로 변경한다.

12. 찾기/참조 영역 함수: VLOOKUP • VLOOKUP 함수의 편리성 • 표($B$13:$D$16)에 명시한 기준에 따라 각 직원의 직위별로 가산점과 합격점을 다르게 지정하고자 한다면? • 손으로 직접 표를 참고하여 하나씩 입력? (Ⅹ) → 자동으로 표에서 맞는 조건을 찾아 해당 값을 자동으로 셀에 표시할 수 있다면 편리할 것이다. 그리고 기준이 달라질 경우 표의 내용을 수정하면 셀의 값도 자동으로 수정되면 편리할 것이다. → VLOOKUP 함수로해결 (O) • - [B2]의 값 “과장”을 • - [$B$13:$D$16]표의첫 열에서 찾았다면 • - 찾은 값이 있는 행의3째 열에 있는 값 82를결과값으로 • - FALSE: 표의 첫 열에서 “과장”과 일치하는 것만 찾기만일 [B15]의 과 앞이나 장 뒤에 빈칸이 있다면 완전히 일치하는것이 없으므로 결과로 ‘#N/A’가 표시된다. •  = VLOOKUP(B2, $B$13:$D$16,3, FALSE) • 직원의가산점을 구하기 위한 [D2] 셀의 식은? = VLOOKUP(B2, $B$13:$C$16, 2, FALSE) 1열 2열 3열

13. 찾기/참조 영역 함수: VLOOKUP 함수의 형식 • ‘표’에서 조건에 맞는 결과값을 수직(vertical, 열)으로 찾기 • 표범위 • 검색값과 함수 결과값을 포함하고 있는 표의 범위로서 표는 반드시 2개 이상의 열을 가져야 한다. • 주) 무조건 표의 첫 열이 ‘검색값’을 검색하는 데 사용되므로 표의 범위를 잡을 때 주의해야 한다. • 결과값의열번호 • 첫 열에서 ‘검색값’과 일치하는 셀을 찾았을 때 이 셀이 포함된 행의 열 중에서 결과 값이 될 열의 번호 • 2이면 표의 둘째 열, 3이면 표의 셋째 열에 있는 값이 결과가 됨 • 범위검색여부 • 표에서 맞는 값을 찾을 때 정확한 값을 찾을 지 근사값을 찾을 지를 결정 • FALSE라면 • 정확하게 일치하는 값만 찾음 • 표의 첫째 열에 있는 값을 정렬할 필요가 없다. • 일치하는 값이 두 개 이상 있으면 먼저 발견된 값이 사용된다. • TRUE이거나 생략하면 • 표의 첫 열에서 정확하게 일치하는 값을 찾을 수 없다면 ‘lookup_value’보다 작으면서 가장 큰 값을 일치하는 값으로 결정한다.  표의 첫 열이 범위를 나타내는 효과를 얻을 수 있다. [이 셀의 값~ 다음 셀의 값 이전]까지 • 주) 표의 첫 열에 있는 값은 반드시 오름차순으로 정렬되어 있어야 한다. lookup_value, table_array, col_index_num, [range_lookup] VLOOKUP( 검색값 , 표범위, 결과값의열번호, 범위검색여부) 검색값을 표에서 찾았을 때 실제 함수의 최종 결과값이 들어있는 열의 번호 둘째 인수인 표의첫 열에서 찾을 값 검색값과 결과값이 있는 참조할 표의 범위 범위를 이용한 검색 여부(True/False)를 선택

14. 찾기/참조 영역 함수: VLOOKUP 함수의 범위(근사치) 이용 • 실습 점수에 대해 다음 기준에 따라 상, 중, 하, 재시로 평가하기 【평가 기준】 15이상 → 상 10이상 ~ 15미만 → 중 5이상 ~ 10미만 → 하 나머지 5미만 → 재시 - [B2]의 점수 5를 - [$E$3:$F$6]표의 첫 열에서 찾았다면 - 찾은 행의2째 열에 있는 값 ‘하’가 결과값 검색값 11을 표 첫 열에서 찾지 못했지만 넷째 인수가 TRUE이므로 검색값(11)보다 작은 값(0, 5, 10) 중 최댓값(10)을 유사 일치 값으로 간주하므로 2째 열의 ‘중’이 함수의 최종 결과값이 된다. 1열 2열 • ※ [E5] 값의의미 • 실습점수가 11, 12, 13, 14일 때 모두 검색할 값보다 작은 값(0, 5, 10) 중에서 가장 큰 값이 10이므로 [E5]의 10과 유사 일치하는 것으로 간주한다. 즉 [E5] 의 10은 검색할 값이 ‘10~15이전까지의 범위에 속한다면’ 의미를 가진다. 1열 2열 • ※ VLOOKUP에서 표의 중요성 • 범위를 이용하는 Vlookup 함수를 제대로 사용하려면 정보를 가진 표를 직접 만들 수 있어야 한다. 주어진 위의 기준으로부터 표의 첫 열이 오름차순이 되도록 작성해야 한다. • 표 첫 열의 값은 ‘값~’ 즉 0은 0부터, 5는 5부터, …로 생각하면 쉽게 표를 작성할 수 있다.

15. 찾기/참조 영역 함수: VLOOKUP 함수의 정보 검색 • 예2) 수강자 명부로부터 학생의 정보 검색하기 • =VLOOKUP($C$20, $C$2:$F$16, 2, FALSE) • 표의 첫 열에서 이름을 찾을 수 있도록 표의 시작 열은 C가 되어야 한다.

16. 활용 예: Vlookup 함수 • 표에서 코드를 검색하여 해당 품명과 단가를 입력하기 • - [B3]의 코드 OP-A4를 • - [$I$3:$K$7] 표의 첫 열에서 찾았다면 • - 찾은 값이 있는 행의2째 열에 있는 것이결과값 • FALSE: 표의 첫 열에서 OP-A4와 일치하는 것만 찾기 1열 2열 3열 • 판매액을 근거로 등급 매기기 • 판매수가 30개 이상이면 "A" • 20~30개 미만이면 "B" • 20개 미만이면 "C" • - [E7]의 판매수32를 • - [$I$11:$J$13]표의 첫 열에서 찾았다면 • - 찾은 값이 있는 행의2째 열에 있는 등급이결과값 • 생략(TRUE): 일치하는 값이없다면 근사치를 찾기 즉 32보다 작은 것 중 최댓값인 30이 근사치로 선택됨 판매수 1열 2열

17. HLOOKUP 함수의 형식 • ‘표’에서 조건에 맞는 결과값을 수평(horizontal, 행)으로 찾기 • 예) 정해진 기준에 맞는 가산점과 합격점을 입력하기 lookup_value, table_array, row_index_num, [range_lookup] HLOOKUP( 검색값 , 표범위, 결과값의행번호, 범위검색여부) 범위를 이용한 검색 여부(True/False)를 선택 표의 첫 행에서 찾을 값 검색값과 결과값이 있는 참조할 표의 범위 검색값을 찾았을 때 실제 함수의 최종 결과값이 들어있는 행의 번호 • - [B2]의 값 “과장”을 • - [$C$20:$G$22]표의첫 열에서 찾았다면 • - 찾은 열의3째 행에 있는 값 82를결과값으로 • - FALSE: 표의 첫 열에서 “과장”과 일치하는 것만 찾기. 만일 [E20]의 과 앞이나 장 뒤에 빈칸이 있다면 완전히 일치하는것이 없으므로 결과로 ‘#N/A’가 표시된다. •  =HLOOKUP(B2, $C$20:$F$22,3, FALSE) 1행 2행 3행 가산점의 경우 =HLOOKUP(B2, $C$20:$F$21, 2, FALSE)

18. 활용 예: Hlookup 함수 • 표에서 코드를 검색하여 해당 단가를 입력하기 • 실습 점수에 대해 기준에 따라 상, 중, 하, 재시로 평가하기 - [B2]의 OP-A4를 - [$B$11:$F$13]표의 첫행에서 찾았다면 - 찾은 값이 있는 열의 3째 행에 있는 값 19000이 결과값 - FALSE: 표의 첫 행에서 OP-A4와 일치하는 것만 찾기 1행 2행 • [B3]의 점수 11을 • [$F$2:$I:$4]표의 첫 행에서 찾았다면 • 찾은 값이 있는 열의2째 행에 있는 값이 결과값 • TRUE : 검색값 11과 같은 값이 표의 첫 행에 없다면 검색값(11)보다 작은 값(0, 5, 10) 중 최댓값(10)을 유사 일치 값으로 간주하므로 2째 행의 ‘중’이 함수의 최종 결과값이 된다. 1행 2행 3행

19. rate, nper, pmt, [pv], [type] FV( 이율, 납입기간, 납입액, 현재가치액, 납입유형) 재무 함수: FV 현재 납입한 금액, 0이라면 생략 가능 생략 시 뒤에 다른 인수가 있으므로 ,가 필요 • 고정된 ‘이율’의 투자상품에 ‘납입기간’ 동안 정기적으로 ‘납입액’을 투자했을 때 미래 가치를 계산 →적금 만기일에 찾는 금액을 계산 ㈜이율과 납입기간의 단위를 동일하게 맞춰야 한다. 둘 다 월단위 또는 연단위 ㈜ 내게서 나가는 돈은 -, 내가 갖고 있는 또는 내게 들어오는 돈은 +값으로 구분한다. • 예) • 매달 4만원씩 10개월 동안 연금리 5%의 적금을 든다면 만기시 찾는 금액은? • 예) • 연이율이 5%이고, 매달 초 20만원씩 적금을 들면 24개월 후 찾는 금액은? = FV(5%/12. 24, 200000, , 1) 기간 초는 1, 기간 말은 0(생략 가능) • 매달 월이율이 5%/12일 때 • 10개월동안 • 40000원씩 • 매월 초에 납입했을 때 만기일에 찾는 적금액 ㈜ 납입 기간이 월단위이므로 연이율을 월이율로 변환하기 위해 12로 나누어야 한다. ㈜ 납입액은 투자자에게서 나가는 돈이므로 –가 되어야 한다. 만일 납입액 [B3]을 단순히 40000으로 하고 싶다면 인수로 –B3을 사용하면 된다.

20. 재무 함수: PMT • ‘대출금’을 고정된 ‘이율’로 빌렸을 때 ‘납입기간’ 동안 정기적으로 갚을 상환액 계산하기 ㈜이율과 납입기간의 단위를 동일하게 맞춰야 한다. 둘 다 월단위 또는 연단위 ㈜ 내게서 나가는 돈은 -, 내가 갖고 있는 또는 내게 들어오는 돈은 +값으로 구분한다. • 예) • 오백만원을 대출받아 연금리 7%로 10개월동안 갚아갈 때 매달 상환액은? • 예) • 연이율이 10%일 때 100만원을 대출한 후 24개월 분할하여 매달 말마다 상환할 금액은? = PMT(10%/12. 24, 1000000) rate, nper, pv, [fv], [type] = PMT( 이율, 납입기간, 대출금, 미래대출잔액, 납입유형 ) 기간 초는 1 기간 말은 0(생략가능) 기간 만료후 남은 대출금(미래 가치) 모두 상환한다면 0(생략 가능) 대출 이자율 현재 가치에 해당 • 매달 월이율이 7%/12일 때 • 10개월동안 • 5000000원을 나누어 갚을 때 • 매달 말에 납입할 상환액 ㈜ 상환액은 대출자에게서 나가는 돈이므로 –이다. 상환액 [B4]가 516,182로 표시되길 원한다면 셋째 인수로 –B3을 사용하거나 함수를 = -PMT(B1/12, B2, B3)로하면 된다.

21. 목표값 구하기 수식에 의해 계산된결과값(목표값)은 알고 있는 상황에서수식에 이용되는 입력값을 정확히 모를 때 사용한다. • PMT 함수에 의해 계산된 현재의 상환액(수식)이 759,731원인데상환액을 100만원(목표값)으로 늘이면 대출기간(입력값)이 얼마가 될지 구할 수 있다. • f(x) = x * 3 / (x+1) 일때 f(x)가 75가 되는 x를구하는 것과 같다. • 예 1) 셀[C11]에 대출 상환액을 계산하는 수식이 있다. 한달에 90만원씩(목표값) 갚아가려면 대출기간은 얼마가 되어야 할까? 목표값 수식이 있는 셀 [C11]에서 [데이터-가상분석] 그룹  [목표값 찾기] 도구 선택 • 예 2) [C23]의순이익이 20만원이 되려면 몇 개([C22])를 팔아야 할까? 수식 목표값 입력값 찾는 값이 목표값임 『주의』 지출해야 하는 값이라면 ‘–’를 붙여야 함

22. 이름 정의하기 2 셀 또는 셀 범위에 대해 구분하기 쉬운 문자열로 이름을 지정하면 수식에서 편리하게 사용할 수 있다. • 방법 1) 이름 상자에서 이름 입력하기 • AVERAGE(F2:F16)  AVERAGE(중간) 으로 사용 가능 • 방법 2) 선택 영역에서 만들기 이용하기 DB의 필드명(범위의 첫 행)을 자동으로 영역 이름으로 정의할 수 있다. • 이름 관리자를 이용한 이름 정의 및 편집 • [수식 - 정의된 이름] 그룹 → [이름 관리자] 도구 →이름 정의, 변경, 삭제 가능 • 위의 방법 1,2는 통합문서 전체에서 유일하게 사용되는 이름을 정의하는 방법이다. 특정 시트에서만 사용가능한 이름으로 설정하려면 범위 선택 후 [수식 – 정의된 이름] 그룹 → [이름 정의]를 선택하여 이름을 정의해야 한다. 1 범위 지정하기 이름 상자에서 클릭한 후 ①에서 지정한 범위에 대한 이름을 입력한 후 <Enter>키를누른다. 2 3 1 • [A1:I16]가지범위 지정 • 반드시 이름으로 사용할 셀 [A1:I1] 도 범위에 포함한다. 4 5 [A2:A16]은 ‘순번’ [B2:B16]은‘소속’ [C2:C16]은‘학번’ : [I2:I16]은 ‘총점’이란 이름이 지정된다.

23. 단순 조건부서식 • 조건부 서식 판매수가 300이상이면 초록색으로 채우기, 셀의 값이 “F”이면 빨간색 글자로 표시하기, 재이수 학생의 모든 정보 셀을 노랑색으로 채우기 등과 같이 특정 조건을 만족하는 셀에 대해 자동으로 서식이 적용되게 할 수 있다. 1. 셀 자신의 값을 활용한 조건부 서식 판매수가 300이상이면 초록색으로 채우기, 셀의 값이 “F”이면 빨간색 글자로 표시하기와 같이 셀 자신의 값을 이용한 특정 조건을 만족할 때 서식이 적용되게 한다. • 예1) 셀의 값 크기를 쉽게 비교할 수 있도록 데이터 막대 넣기 • 조건부 서식을 지정할 셀 범위 설정 후 • [홈 – 스타일] 그룹[조건부 서식] 도구에서 조건 및 서식 지정 범위 설정 2 3 4 1 지정한 조건부 서식 제거 지정한 조건부 서식 수정 다양한 수식을 이용한 조건부 서식 지정

24. 단순 조건부서식 • 예2) 전년도 판매수량이 400을 초과한 셀만 파랑색으로 채우기 • 조건부 서식을 지정할 셀 범위 설정 후 • [홈 – 스타일] 그룹 [조건부 서식] 도구에서 조건 및 서식 지정 2 범위 지정 1 4 3 단추를 눌러 [사용자 지정 서식]선택 5 채우기 색을 지정한 후 [확인] 단추를 누른다. 조건을 만족하는 셀만 파랑색이 자동으로 칠해진다.

25. 수식을 사용한 조건부서식 / 1 2. 특정 수식(다른 셀의 값도 활용 가능)을 만족하는 경우에만 서식 지정하기 • 다음과 같이 재이수학생의 정보 셀에만 파랑 색으로 채우기를 하고 싶다면? • 문제) • [B2]셀은 자신의 값이 아니라 [E2]의값에 의해 노랑색 채우기가 되므로 앞의 단순 조건부 서식으로는 해결할 수 없다. • 해결) • 수식을 사용한 조건부 서식 이용하기 • 셀 자신의 값뿐 아니라 다른 셀 값을 이용한 특정 수식을 만족할 때 서식이 적용되게 할 수 있다. • 일반적으로 특정 셀의 값에 따라 한 행/열 전체에 서식을 지정할 때 많이 사용된다. • 예) 재이수 학생의 모든 정보 셀을 파랑색으로 칠하기 • 예) 결석시수가12이상인 학생의 모든 정보를 빨강색으로 표시하기 • 조건 수식을 지정할 때의 주의점 • 먼저 조건부 서식을 지정할 셀 범위를 설정할 때 기준이 되는 셀(마우스로 셀 범위를 드래그할 때의 시작셀)에 대해서만 수식을 지정하며,셀 범위 안의 다른 셀들은 자동으로 기준 셀의 수식 조건이 복사되으로셀 주소에 대해 상대 주소와 절대 주소 지정 여부를 반드시 확인해야 한다.

26. 수식을 사용한 조건부서식 / 2 • 수식 지정 시 상대 주소와 절대 주소 주의 • 문제) [B2:F6]의 각 셀에 들어갈 수식은 아래 그림의 각 셀에 있는 수식과 같다.이 조건 수식을 모든 셀에 직접 지정하기는 곤란하다. • 엑셀의 수식을 사용한 조건부 서식 지정 원리 조건부 서식을 지정할셀 범위 [B2:F6]를 드래그할 때의 시작셀인 [B2]에만 만족 여부를 검사할 조건 수식을 지정하면나머지 셀들에 시작 셀 [B2]의 조건 수식을 자동으로 복사해준다. • [B2]셀의수식을 어떻게 지정해야 나머지 셀에도 정확한 수식이 들어가게 될까? • 시작셀[B2]의 수식을 한 행 아래로 한 열 오른쪽으로 복사했을 때 수식이 제대로 표현될 수 있도록 포함된 셀 주소에 대해 상대와 절대주소를 정확히 확인하여 지정한다. =E2>20000 =E2>20000 =E2>20000 =E2>20000 =E2>20000 =E3>20000 =E3>20000 =E3>20000 =E3>20000 =E3>20000 =E4>20000 =E4>20000 =E5>20000 =E5>20000 =E6>20000 =E6>20000 =E6>20000 블록(B2:F6)의 각셀에 들어가야 할 조건 수식 즉 각 셀은 각자의 조건 수식이 참일 때 지정한 서식이 적용된다.

27. 수식을 사용한 조건부서식 / 3 • 문제점) [B2] 셀의 수식을 상대주소만 이용하여 입력한다면? • 해결) 상대주소와 절대주소의 혼합 • [B2] 셀의 수식이 오른쪽으로 복사될 때 열머리글이 변하지 않도록 절대주소로 • “ 아래쪽으로 복사될 때는 행머리글이 변하도록 상대주소로 복사 결과 =E2>20000 =G2>20000 =H2>20000 =I2>20000 =F2>20000 복사 결과 =E3>20000 =F3>20000 =G3>20000 =H3>20000 =I3>20000 =F4>20000 =G4>20000 =E4>20000 • 블록 [B2:F6]의 기준셀인 [B2] 셀의 조건이 나머지 셀들에 자동으로 복사되면서상대주소가 변하여 잘못된 수식이 된다. •  • [B2]셀의수식이 복사될 때 행머리글은 변하되 열머리글은 변하지 않아야 하므로 행머리글은 상대, 열머리글은 절대주소로 지정해야 한다. 복사 결과 =$E2>20000 =$E2>20000 =$E2>20000 =$E2>20000 =$E2>20000 복사 결과 =$E3>20000 =$E3>20000 =$E3>20000 =$E3>20000 =$E3>20000 =$E4>20000 =$E4>20000 =$E4>20000 =$E4>20000 =$E4>20000

28. 수식을사용한 조건부 서식 활용: 재이수 학생의 정보만 노랑색으로 채우기 2 3 1 조건부 서식을 지정할 셀 범위 설정 8 5 4 4 6 7 시작 셀 [B2]가 만족해야할 수식을 입력 9 ⑨에서 지정된 셀 서식 10

29. 데이터 정렬: 단일 필드 기준 • 정렬할 기준이 단일 필드일 때 • 셀 포인터를 정렬 대상 셀 중 한 셀에 두기『주의』 연속된 사각형 영역을 하나의 데이터베이스로 간주하므로 중간에 빈 줄/행이 없도록 해야 함 • [데이터 - 정렬 및 필터]  오름차순/내림차순정렬 단추 이용 또는 [홈-편집] 그룹 [정렬 및 필터] 도구  오름차순/내림차순 정렬 이용 또는 정렬할 셀에서팝업 메뉴 <정렬>  오름차순/내림차순 정렬 이용 2 1 동일 기능 여러 필드 기준 정렬에 이용 동일 기능 1 2

30. 데이터 정렬: 여러 필드 기준 1 5 4 6 정렬할 기준이 여러 필드일 때는 [정렬] 대화상자를 띄운다. • 예) ‘소속’ 별로 ‘학번’ 순으로 정렬하기 • 셀 포인터를 데이터베이스 안의 한 셀에 두기 • [데이터 - 정렬 및 필터] 그룹  [정렬] 도구 또는 [홈-편집] 그룹  [정렬 및 필터]  [사용자 지정 정렬] • [기준 추가] 단추를 눌러 하위 정렬기준을 추가로 지정 2 3 단순히 가나다 순이 아닌 심리학부, 경영학부, 기계공학부…와 같이 사용자가 원하는 순서대로 정렬하고 싶다면 이 단추를 눌러 [사용자 지정 목록…]을 선택한다. 데이터베이스의 첫 행(여기선 [A1:J1])은 각 열의 데이터에 대한 머리글(즉 필드명)으로 간주된다. 그러므로 데이터 목록의 첫 행이 머리글(필드명)이라면 정렬 대상에서 제외해야 한다. 만일 첫 행이 필드명이 아니라 실제 정렬 대상 학생 데이터라면 체크 표시를 해제해야 한다. 즉 첫 행이 머리글이 아님을 명시해야 한다.

31. 틀 고정 2 1 • 틀 고정 • 시트 화면을 스크롤 하더라도DB의 필드명과 같이 시트의 특정 부분(위쪽 행과 왼쪽 열)이 화면에 고정되어 표시되면 편리할 때가 많다. • 예) 화면에 셀 [E2] 왼쪽과 상단 내용이 언제나 표시되도록 틀 고정하기 • 셀 [E2]에 셀 포인터를 두기 • [보기-창] 그룹  [틀 고정] 도구 이용 • 틀 고정 취소 • 시트 어디서든[보기 – 창] 그룹 [틀 고정] 도구 [틀 고정 취소] 3 무조건 시트의 첫 행 / 첫 열을 고정할 때 사용

32. 그룹 범위가 축소된 상태에서 윤곽 기호를 클릭하면 범위가 확대되어 위의 그림 처럼 다시 [B], [C] 열이 나타난다. • 범위별로 그룹화를 통해 범위의 확장(보이기)/축소(숨기기)를 편리히게 할 수 있다.  DB의 많은 필드 중 필요한 부분만 보며 작업할 수 있다. • 그룹 설정하기 • 그룹 범위 설정 후  [데이터 – 윤곽선] 그룹  [그룹 해제] 단추 • 범위 축소/확장 • 그룹 해제 • 해제할 범위 설정 후  [데이터 – 윤곽선] 그룹  [그룹 해제] 단추 윤곽 기호를 클릭하면 범위가 축소되어 숨겨진다. 열머리글을 드래그하여 [B], [C]열을 범위 설정 1 2 1 드래그하여 [B], [C]열을 범위 설정 1 2

33. 피벗 테이블 대량의 데이터로부터 원하는 데이터만 뽑아서 특정 정보를 얻고 재배치할 수 있다.  부분합과 필터링 기능을 쉽고 편하게 이용 • 피벗 테이블 보고서 작성 • 셀 포인터를 데이터베이스 임의의 셀에 두기 • [삽입 – 표] 그룹  [피벗 테이블] 도구 • [피벗 테이블 만들기] 대화상자에서피벗 테이블을 만들 DB와피벗 테이블 시작 위치를 지정한다.주) 피벗테이블 시작위치는 위로 빈 두 행이 여유분으로 있어야 함 • [피벗 테이블 필드 목록]에서원하는 필드명을 아래 영역의 원하는 곳으로 드래그&드랍하여 적절히 배치한다. 필드명을 아래의 해당 영역으로 드래그&드랍한다. ‘∑값 영역’이 ‘1차’ 필드의 평균과 ‘2차’필드의 합계임을 표시한다. 여자이면서 2팀인 사원들의 1차 점수의 평균

34. 피벗 테이블 만들기 / 1 1 2 3 4 5 1. 피벗 테이블을 만들 원본 DB 범위와 피벗 테이블 위치 지정하기 • 1. 피벗 테이블의 원본 데이터베이스에 셀 포인터 둔 후 • 2. [삽입 – 표] 그룹 [피벗 테이블] [피벗 테이블] 선택 • 3. 피벗 테이블의 원본 데이터 범위 • 피벗 테이블의 위치 지정 • [위치] 칸에 커서 둔 후 워크시트의 [M3] 셀을클릭하면 주소가 입력된다. • 의 셀 포인터를 포함한 연속된 사각영역이 자동으로 선택되지만 다른 범위를 지정하고 싶다면 기존의 범위를 지운 후 시트에서 드래그하여 범위를 새로 지정할 수 있다. • 『주의』범위를 잡을 때 반드시 필드명(여기선 [1]행)을 포함하도록 해야 한다.

35. 피벗 테이블 만들기 / 2 2. [피벗 테이블 필드 목록] 창에서 ‘부서’, ‘팀’, ‘성별’, ‘1차’ 필드를 해당 영역으로 드래그&드랍하여 피벗 테이블을 만든 결과 • 해당 필드명을 원하는 영역으로 드래그&드랍하여 배치한다. • 값 영역의 값이 ‘1차’ 필드의 ‘합계’임을 표시 ‘남’이면서 ‘2’팀인 사원들의 1차 점수의 합계

36. 피벗 테이블 만들기 / 3 3. ‘2차’ 필드를 추가로 피벗 테이블에 넣은 결과 4. 값 영역의 두 필드값들이 행으로 배치되도록 조정한 결과 2 1 2 • ‘∑값’영역에 ‘2차’ 필드를 배치하면 다음과 같이 열 레이블 영역에 ‘∑값’단추가 자동으로 생긴다. 그 결과 피벗 테이블에서 ‘합계:1차’, ‘합계:2차’가 열 레이블에 포함된다. • 위의 화면에서 열 레이블 영역의‘∑ 값’ 단추를 행 레이블 영역으로 드래그&드랍한 결과

37. 피벗 테이블 만들기 / 4 3 4 5.‘1차’ 필드의 합계가 아닌 평균을 구하기 • 피벗 테이블의 레이블 영역에 표시된 ‘합계 : 1차’를 더블클릭하여 [값 필드 설정] 대화상자를 띄운다. • 피벗 테이블에 나타난 ‘1차’ 필드의 합계 데이터 중 한 개를 선택한 후 팝업메뉴 <값 필드 설정>을 선택하거나 • [피벗 테이블 필드 목록] 창의 ‘∑ 값’ 영역의 ‘합계 : 1차’ 단추를 클릭하여 나타난 메뉴에서 [값 필드 설정]을 선택해도 된다. • [값 필드 설정] 대화상자에서 원하는 계산 유형(‘평균’)을 선택한다. 1 더블클릭 2

38. 피벗 테이블 만들기 / 5 6. 완성: 피벗 테이블의 레이블명 변경 및 값의 표시 형식 지정하기 셀을 선택 후 직접 ‘성별’, ‘팀’을 입력하여 변경한다. 셀에서 직접 입력할 수 없는 경우 수식 입력줄에서 입력하여 변경할 수 있다. 표시 형식을 지정하여 원하는 형식 (. 아래 1자리)으로 표시할 수 있다. • 개수 활용 예: 소속 학부별 학점 개수 구하기 사실 이 예에서 값 영역의 필드명은 다른 것을 사용해도 사용함수가 ‘개수’로 동일하다면 결과는 동일하다. 개수는 행과 열 레이블에 의해 그룹화된 레코드의 개수를 의미하기 때문에 다른 필드명을 사용해도 개수는 동일하다. (기계공학부이면서 B학점인 레코드의 개수는 5개))

39. 피벗 테이블 예: 학점 별 해당 학생 수 구하기 / 1 • 필터링 기능 • 필드 함수로 개수 활용 필터링 기능이 있으므로 여기를 눌러 특정 학부명만 선택함으로써 해당 학부 자료만으로 작성된 피벗 테이블로 변경할 수 있다.

40. 피벗 테이블 예: 학점 별 해당 학생 수 구하기 / 2 • 피벗 테이블의 값에 사용된 실제 레코드 정보 보기 값이 있는 셀을 더블클릭하거나 셀에서 팝업메뉴 <자세한 정보 표시>를 선택하면 새 시트에 해당 정보가 나타난다. • 피벗 테이블의 원본 데이터 변경 시 피벗 테이블도 고치기 • 원본 데이터를 변경해도 피벗 테이블은 자동으로 수정되지 않는다. • 피벗 테이블의 특정 셀에 셀 포인터를 둔 후 • [피벗 테이블 도구 :옵션 – 데이터] 그룹  [새로고침] 도구 선택또는 팝업메뉴 <새로고침> 이미 만들어진 피벗 테이블의 원본 데이터를 변경 피벗 테이블의 제거

41. 시트 보호 활용 예: 학생이 자신의 점수만 확인하기 / 1 • 작업 내용 다른 학생의 정보를 볼 수 없도록 [A]~[E]열을 숨긴 상태에서학번을 입력하는 [G5]셀에서만 정보를 입력할 수 있으며다른 셀에서는 다른 작업을 할 수 없으며 숨기기 취소 같은 작업도 하지 못하도록 시트 보호하기 • [A]~[E] 열머리글을드래그한 후 팝업메뉴 <숨기기>를 선택하여 [A]~[E] 열을 숨기기 VLOOKUP함수로 작성 학번 입력하는 곳, 이곳만 수정 가능

42. 시트 보호 활용 예: 학생이 자신의 점수만 확인하기 / 2 • [G5] 셀만 보호가 되지 않도록 • [G5] 셀에서팝업메뉴 <셀 서식> 선택 • [셀 서식] 대화상자의  [보호] 탭  [ 잠금]을 클릭하여 해제한다. • [검토-변경 내용] 그룹  [시트 보호] 도구  [차트 보호] 대화상자에서암호를 입력하여 사용자가 잠금된 셀에서는 작업을 하지 못하게 한다. 『주의』기본적으로 시트의 모든 셀은 잠금이 설정되어 있다. 그러나 시트가 보호되지 않는 한 효과가 발생하지 않으므로 지금까지 모든 셀에서 작업을 할 수 있었다. 그러므로 특정 셀에서 작업을 하지 못하게 하려면 반드시 시트 보호를 설정해야 한다. 1 2 3 클릭하여 체크를 해제한다. 1 1 암호를 입력한다. 잊지 않도록 주의한다. 2 4 3 5

43. 시트 보호 활용 예: 학생들이 자신의 점수만 확인하기 / 3 • 보호된 시트에서 ‘잠금’이 해제된 [G5]외의셀에서 자료 입력을 시도하면 다음과 같이 에러 메지시가 나타난다. • 시트의 보호 해제하기 • [검토 - 변경 내용] 그룹  [시트 보호 해제] 도구 선택 • 시트를 보호할 때 입력한 암호를 입력한다.(암호 분실시 해제 불가능) 1 2 3