단원명 Excel 2010 등급 Essential 강 12강 소단원명 간단한조건식으로함수논리력쌓기, 조건 / 논리함수 목차 1. IF, SUMPRODUCT 함수 2. 중첩 IF 사용하기 3. AND, OR 함수 4. IFERROR 함수 방법 1. 캠타시아 2. 캠타시아 3. 캠타시아 4. 캠타시아 학습목표 1. IF 함수를이해하고사용할수있다. 2. IF 함수에사용되는조건에 AND, OR함수를사용할수있다. 3. IF 함수를중첩된형태로사용할수있다. 4. IFERROR 함수의기능을이해할수있다. <before> <after> 오늘의미션 [ 평가표 ] 시트에서 1. 반영비율을고려한총점을구하고, 2. 합격여부를구하고, 3. 주민등록번호를참조하여성별을구합니다.
<before> <after> [ 일람표 ] 시트에서 1. 주민등록번호를참조하여성별을구하고, 2. 등급을나누어구하고, 3. 성적우수자를표시하고, 4. 재학습자를표시하도록합니다. 본강의 세부목차 1. IF, SUMPRODUCT 함수제작방식캠타시아 IF 함수 - IF 함수는비교연산을통해해서얻은참이나거짓의결과값으로참일때와거짓일때다른결과를돌려주는함수 - 구문 : =IF(logical_test, value_if_true, value_if_false) - logical_test: 조건식으로 TRUE나 FALSE로평가할수있는값이나조건검사를수행할수식 - value_if_ture: 조건식의결과가 TRUE(1) 일때출력할값이나수식 - value_if_false: 조건식의결과가 FALSE(0) 일때출력할값이나수식 - 예 )
[B6] 의수식 : A2셀의값이 100보다작거나같으면 예산내 를아니면 예산초과 를표시함 [B7] 의수식 : A2셀의값이 100보다작거나같으면 A2:A3의합계를아니면 ( 널 ) 을표시함 [B8] 의수식 : B2셀의값이 C2셀의값보다크면 예산초과-초과금액 을아니면 승인 을표시함 [B9] 의수식 : B3셀의값이 C3셀의값보다크면 예산초과 아니면 승인 을표시함 SUMPRODUCT 함수 - 구문 : SUMPRODUCT(array1, [array2], [array3],...) - 주어진배열에서해당요소를모두곱하고그곱의합계를반환함 - Array1 : 필수요소, 계산하려는배열의첫번째인수 - Array2, array3,... : 선택요소입니다. 계산하려는배열의인수로서 2개에서 255개까지지정할수있음 - 인수로사용하는배열의차원은모두같아야함. 차원이같지않으면 #VALUE! 오류값이반환됨 - 숫자가아닌배열항목은 0으로처리됨 * [ 평가표 ] 에다음과같이총점, 합격여부, 성별을표시하시오. 1. 총점은반영비율을고려하여면접, 전공이론, 전공실습의합으로구합니다. 2. 총점이 85점이상이면합격, 85점미만일때는불합격이라고표시합니다. 3. 성별은주민등록번호의 8번째글자가 1 이면 남, 아니면 여 로표시합니다.
1) [F4:F8] 영역블록설정후, =SUMPRODUC(C4:E4:$C$10:$E$10) 입력후, [Ctrl]+[Enter] 를누름 2) [G4:G8] 영역블록설정후, =IF(F4>=85," 합격 ", " 불합격 ") 입력후, [Ctrl]+[Enter] 를누름 3) [H4:G8] 영역블록설정후, =IF(MID(B4, 8,1)="1", " 남 ", " 여 ") 입력후, [Ctrl]+[Enter] 를누름 세부목차 2. 중첩 IF 사용하기제작방식캠타시아 중첩 IF - 중첩 IF: IF문의인수에 IF문을반복하여사용하는것 - 조건이여러개발생되는경우사용함 - 직접입력시, 닫는괄호의개수에주의할것, IF 를사용한개수만큼닫기괄호를표시해야함 - 예 ) 점수가 90점이상이면참잘했어요, 70점이상이면잘했어요, 70점미만이면노력하세요표시 * [ 일람표 ] 시트에대해서아래의작업을 IF 의중첩으로처리하시오. 1. 주민등록번호의 8 번째글자가 1 또는 3 이면남자, 아니면여자를표시하는수식을작성하시오. 2. 평균이 90 이상이면, 상, 89 ~70 이면 중, 69 ~0 은 하 를등급필드에표시하시오. 풀이 ) 1) [D3] 셀클릭후, =IF(MID(C3, 8, 1)= 1, 남, IF(MID(C3, 8, 1)= 3, 남, 여 )) 를입력후, [Enter] 를누른뒤, [D3] 셀의채우기핸들을더블클릭함 2) [I3] 셀클릭후, =IF(H3>=90, 상, IF(H3>=70, 중, 하 )) 를입력후, [Enter] 를누른뒤, [I3] 셀의채우기핸들을더블클릭함
세부목차 3. AND, OR 함수제작방식캠타시아 AND 함수 - 조건이여러개일때, 모든조건이참이면 TRUE 값을돌려줌 - 구문 : =AND( 조건 1, 조건 2, ) OR 함수 - 조건이여러개일때, 조건중에참이하나라도있으면 TRUE 값을돌려줌 - 구문 : =OR( 조건1, 조건2, ) - 일반적으로 AND와 OR는다른조건함수 (IF 등 ) 의인수로사용됨 예 ) * [ 일람표 ] 시트에대해서아래의작업을 IF와 AND, OR 함수의사용으로의처리하시오. 1. 성별은주민등록번호의 8번째글자가 1 또는 3인경우남자아니면여자로처리하시오. 2. Excel, PPT, Word 모든성적이 90점이상이사람을 우수자 로표시하시오. 3. Excel. PPT, Word 중하나라도 50점미만인사람이있다면 재학습자 로표시하시오. 풀이 ) 1) [J3:J12] 영역을블록설정후, =IF(OR(MID(C3,8,1)="1", MID(C3,8,1)="3"), " 남 ", " 여 ") 를입력후, 2) [K3:K12] 영역을블록설정후, =IF(AND(E3>=90, F3>=90, G3>=90), " 우수자 ", "") 를입력후, 3) [L3:L12] 영역을블록설정후, =IF(OR(E3<50, F3<50, G3<50), " 재학습자 ", "") 를입력후,
세부목차 4. IFERROR 함수제작방식캠타시아 IFERROR 함수 - 수식에서오류가발생할경우사용자가지정한값을반환하고, 그렇지않으면수식결과를반환함 - 구문 : IFERROR(value, value_if_error) - Value: 오류를검사할인수 - value_if_error: 수식에서오류가발생할경우에반환할값, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? 또는 #NULL! 오류유형이평가됨 [ 주의사항 ] - value 또는 value_if_error 가빈셀이면빈문자열값 ("") 으로처리됨 - value 가배열수식이면 value 에지정된범위의각셀에대한결과가들어있는배열이반환됨 - 예 ) IFERROR 함수 * 이번 IFERROR 에대한실습은없습니다. 지원자평가결과표를작성해보도록하겠습니다. 12 차시따라하기 이미지 상태설명 학습자에게제시될진행요구사항 Self-001 초기화면 총점을구하기위해 [F4:F8] 영역을드래그하여블록설정합니다. Self-002 [F4:F8] 영역을선택한화면 수식의시작을위해 = 을입력하고, 총점은반영비율을고려하여면접, 전공이론, 전공실습의합으로구해야하므로 SUMPROCUCT 함수를입력합니다. 질문 ) 수식의시작을위해 = 을입력하고, 총점은반영비율을고려하여면접, 전공이론, 전공실습의합으로구하기위한함수를입력하세요. 답 ) =SUMPRODUCT 입력유도 Self-003 =SUMPRODUCT를입력한화면 함수의인수입력을위해열기괄호를입력후, 점수영역인 [C4:E4] 영역을입력하고콤마를입력합니다. Self-004 (C4:E4, 를입력한화면 반영비율이입력되어있는 [C10:E10] 영역을드래그하여블록설정합니다. Self-005 C10:E10 을드래그하여선택한화면 상대참조상태의주소를절대참조로변환하기위해사용되는 [F4] 키를누릅니다. 질문 ) 상대참조상태의주소를절대참조로변경
하기위한키보드의키를누르세요. 답 ) 키보드에서 [F4] 키를누르도록유도 Self-006 키보드에서 [F4] 키를누른화면 입력을위해 Self-007 [Ctrl]+[Enter] 를누른화면 합격여부를구하기위해 [G4:G8] 영역을드래그하여블록설정합니다. Self-008 [G4:G8] 영역을선택한화면 수식입력을시작하기위해 = 을입력후, 조건에따라다른값을반환받기위한함수인 IF 를입력합니다. 질문 ) 수식입력을시작하기위해 = 을입력후, 조건에따라다른값을반환받기위한함수를입력하세요. 답 ) =IF 입력유도 Self-009 =IF를입력한화면 F4셀에있는값이 85이상인경우합격, 아닌경우불합격을출력하기위해 IF의인수로 (F4>=85, 합격, 불합격 ) 을입력합니다. Self-010 (F4>=85, 합격, 불합격 ) 을입력한화면 입력을위해 Self-011 [Ctrl]+[Enter] 를누른화면 성별을구할영역인 [H4:H8] 영역을드래그하여블록으로설정합니다. Self-012 [H4:H8] 영역을선택한화면 수식입력을시작하기위해 = 을입력후, 조건에따라다른값을반환받기위한함수인 IF 를입력합니다. Self-013 =IF를입력한화면 인수입력을시작하기위해열기괄호를입력후, 문자열을특정위치에서지정된글자수만큼의문자를추출해오기위한함수인 MID를입력합니다. Self-014 (MID를입력한화면 주민등록번호에서 8번째글자를 1 과비교하여같으면 남 아니면 여 를표시하기위한인수인 (B4, 8, 1)= 1, 남, 여 를입력후, IF함수를닫기위해 (B4,8,1)= 1, 남, 여 ) 를입력합니다. Self-015 (B4,8,1)= 1, 남, 여 ) 를입력한화면 입력을위해 Self-016 [Ctrl]+[Enter] 를누른경우 성별이표시된것을확인합니다. 정리하기 1. IF, SUMPRODUCT 함수 IF 함수 - IF 함수는비교연산을통해해서얻은참이나거짓의결과값으로참일때와거짓일때다른결과를돌려주는함수 - 구문 : =IF(logical_test, value_if_true, value_if_false) - logical_test: 조건식으로 TRUE 나 FALSE 로평가할수있는값이나조건검사를수행할수식 - value_if_ture: 조건식의결과가 TRUE(1) 일때출력할값이나수식 - value_if_false: 조건식의결과가 FALSE(0) 일때출력할값이나수식 SUMPRODUCT 함수 - 구문 : SUMPRODUCT(array1, [array2], [array3],...) - 주어진배열에서해당요소를모두곱하고그곱의합계를반환함 - Array1 : 필수요소, 계산하려는배열의첫번째인수
- Array2, array3,... : 선택요소입니다. 계산하려는배열의인수로서 2 개에서 255 개까지지정할수있음 - 인수로사용하는배열의차원은모두같아야함. 차원이같지않으면 #VALUE! 오류값이반환됨 - 숫자가아닌배열항목은 0 으로처리됨 2. 중첩 IF 사용하기 - 중첩 IF: IF 문의인수에 IF 문을반복하여사용하는것 - 조건이여러개발생되는경우사용함 - 직접입력시, 닫는괄호의개수에주의할것, IF 를사용한개수만큼닫기괄호를표시해야함 3. AND, OR 함수 AND 함수 - 조건이여러개일때, 모든조건이참이면 TRUE 값을돌려줌 - 구문 : =AND( 조건 1, 조건 2, ) OR 함수 - 조건이여러개일때, 조건중에참이하나라도있으면 TRUE 값을돌려줌 - 구문 : =OR( 조건 1, 조건 2, ) - 일반적으로 AND 와 OR 는다른조건함수 (IF 등 ) 의인수로사용됨 4. IFERROR 함수 - 수식에서오류가발생할경우사용자가지정한값을반환하고, 그렇지않으면수식결과를반환함 - 구문 : IFERROR(value, value_if_error) - Value: 오류를검사할인수 - value_if_error: 수식에서오류가발생할경우에반환할값, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? 또는 #NULL! 오류유형이평가됨 [ 주의사항 ] - value 또는 value_if_error 가빈셀이면빈문자열값 ("") 으로처리됨 - value 가배열수식이면 value 에지정된범위의각셀에대한결과가들어있는배열이반환됨 // 실습 * [IF] 평균이 85 점이상이면합격, 85 점미만일때는불합격이라고출력하는함수식을작성해보세요. 1) [F4:F8] 영역블록설정후, =SUMPRODUC(C4:E4:$C$10:$E$10) 입력후, [Ctrl]+[Enter] 를누름 2) [G4:G8] 영역블록설정후, =IF(F4>=85," 합격 ", " 불합격 ") 입력후, [Ctrl]+[Enter] 를누름 3) [H4:G8] 영역블록설정후, =IF(MID(B4, 8,1)="1", " 남 ", " 여 ") 입력후, [Ctrl]+[Enter] 를누름 * [ 일람표 ] 시트에대해서아래의작업을 IF 의중첩으로처리하시오. 1. 주민등록번호의 8 번째글자가 1 또는 3 이면남자, 아니면여자를표시하는수식을작성하시오. 2. 평균이 90 이상이면, 상, 89 ~70 이면 중, 69 ~0 은 하 를등급필드에표시하시오. 풀이 )
1) [D3] 셀클릭후, =IF(MID(C3, 8, 1)= 1, 남, IF(MID(C3, 8, 1)= 3, 남, 여 )) 를입력후, [Enter] 를누른뒤, [D3] 셀의채우기핸들을더블클릭함 2) [I3] 셀클릭후, =IF(H3>=90, 상, IF(H3>=70, 중, 하 )) 를입력후, [Enter] 를누른뒤, [I3] 셀의채우기핸들을더블클릭함 * [ 일람표 ] 시트에대해서아래의작업을 IF 와 AND, OR 함수의사용으로의처리하시오. 1. 성별은주민등록번호의 8 번째글자가 1 또는 3 인경우남자아니면여자로처리하시오. 2. Excel, PPT, Word 모든성적이 90 점이상이사람을 우수자 로표시하시오. 3. Excel. PPT, Word 중하나라도 50 점미만인사람이있다면 재학습자 로표시하시오. 풀이 ) 1) [J3:J12] 영역을블록설정후, =IF(OR(MID(C3,8,1)="1", MID(C3,8,1)="3"), " 남 ", " 여 ") 를입력후, 2) [K3:K12] 영역을블록설정후, =IF(AND(E3>=90, F3>=90, G3>=90), " 우수자 ", "") 를입력후, 3) [L3:L12] 영역을블록설정후, =IF(OR(E3<50, F3<50, G3<50), " 재학습자 ", "") 를입력후, 다음미션예고다음시간에는다른표에있는내용을참조하는함수와조건에해당하는것에대한통계값을구하는함수들에대해서학습하도록하겠습니다. TIP IS 함수 - IS 함수라고하는이러한각함수는값의유형을검사하고그결과에따라 TRUE 또는 FALSE 를반환함 - 예 ) ISBLANK 함수는값이빈셀에대한참조이면논리값 TRUE 를반환하고, 그렇지않으면 FALSE 를반환함 - IS 함수의값인수는변환되지않음 - 예 ) 숫자가필요한대부분의다른함수에서텍스트값 "19" 는숫자 19 로변환되지만 ISNUMBER("19") 수식에서텍스트값 "19" 는변환되지않고 FALSE 를반환함 - IS 함수는식에서계산결과를검사할때유용함 - 구문 : ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) - Value: 검사할값, 값으로는공백 ( 빈셀 ), 오류값, 논리값, 텍스트, 숫자, 참조값또는이러한항목을참조하는이름을사용할수있음 - 식에서계산결과가원하는형태 (Data Type) 인지검사할수있음 - 특히 ISBLANK() 함수의경우사용빈도가높음, ISERROR() 함수도사용빈도가높은편이지만 2007 Excel 2007 버전부터 IFERROR 함수가제공되어사용빈도가낮아진편임