5장 수식과 함수 사용 실용컴퓨터 엑셀

수식과 함수 사용 미리 알아야할 내용 이 절을 마치면 데이터를 처리하여 원하는 값을 얻는 방법 셀을 삽입하거나 삭제할 수 있어야 합니다. 목록을 편집할 수 있어야 합니다. 셀 서식을 지정할 수 있어야 합니다. 이 절을 마치면 수식을 사용할 수 있습니다. 함수를 사용할 수 있습니다. 중첩 함수의 사용법을 알 수 있습니다. 데이터를 처리하여 원하는 값을 얻는 방법 직접 수식을 입력 함수를 사용하는 방법 수식은 반드시 = 기호를 먼저 입력해야 함 5장 수식과 함수 사용 실용컴퓨터

강신희의 중간고사 점수와 기말고사 점수를 더해서 총점 계산 수식 I 간단한 수식 사용하기 자동 합계 버튼 사용하기 강신희의 중간고사 점수와 기말고사 점수를 더해서 총점 계산 =C3+D3 현재 셀의 위에 수치가 들어있는 셀을 추천 합계에 사용할 셀 범위를 드래그하여 입력 5장 수식과 함수 사용 실용컴퓨터

수식이 입력된 셀을 더블클릭하면 수식입력줄과 현재 셀에 수식이 나타남 수식 II 수식의 복사 수식이 들어있는 셀의 채우기 핸들을 드래그하여 수식을 복사 E4(=SUM(C4:D4))의 채우기 핸드을 E5로 드래그하여 수식 복사 복사된 수식은 =SUM(C5:D5)로 자동으로 변경됨 수식이 복사될 때 셀 참조가 자동으로 바뀌는 것은 상대참조이기 때문 수식의 수정 수식 입력줄과 현재 셀에서 수정 가능 수식이 입력된 셀을 더블클릭 수식이 입력된 셀을 더블클릭하면 수식입력줄과 현재 셀에 수식이 나타남 5장 수식과 함수 사용 실용컴퓨터

수식 III 수식 입력줄 수식에서 사용되는 연산 기호 수식 취소 버튼( ) : 현재 입력한 수식의 실행을 취소할 때 사용 수식 취소 버튼( ) : 현재 입력한 수식의 실행을 취소할 때 사용 수식 확인 버튼( ) : 입력한 수식을 실행할 때 사용 수식 입력 기호( ) : 수식이나 함수를 입력할 때 사용 수식에서 사용되는 연산 기호 산술 연산자 : *, /, +, -, %, ^ 비교 연산자 : =, <>, <=, <, >=, > 텍스트 연산자 : &(24 & “명”은 하나의 텍스트 값 “24명”이 됨) 참조 연산자 “:” 인접한 여러 개의 셀을 나타낼 때(A1 : A5는 A1, A2, A3, A4, A5를 의미함) “,” 두 범위를 하나의 범위로 결합(A1:A3, A5:A7) 5장 수식과 함수 사용 실용컴퓨터

=A1(왼쪽으로 두번째 열의 같은 행의 셀) + B1(왼쪽으로첫번째 열의 같은 해의 셀) 수식 IV 상대 참조와 절대 참조 상대 참조 열 머리글과 행 머리글을 조합하여 표시 규칙을 나타내므로 셀을 참조하는 위치에 따라 달라짐 A1, K25, … A B C 1 7 2 9 5 14 3 12 11 23 =A1 + B1 규칙 =A1(왼쪽으로 두번째 열의 같은 행의 셀) + B1(왼쪽으로첫번째 열의 같은 해의 셀) C1의 수식을 복사하면 규칙이 복사되므로 =A2 + B2 5장 수식과 함수 사용 실용컴퓨터

수식 V 절대 참조 참조하는 셀의 위치와 상관없이 참조되는 셀의 위치가 항상 고정 셀의 열 머리글과 행 머리글 앞에 $ 기호를 함께 사용하여 나타냄 F2의 수식 =E2/E7 F2의 수식을 복사 =E2/E8 E8에는 값이 없으므로 0이 되어 에러 발생 F2의 E7을 절대참조로 변환 =E2/$E$7 상대 참조를 절대 참조로 변환하는 키 : F4 E7$E$7 E$7$E7 5장 수식과 함수 사용 실용컴퓨터

함수 상자의 아래쪽 화살표를 클릭하면 함수 목록이 표시됨 함수 사용하기 I 함수 동일한 목적으로 사용되는 수식을 미리 만들어서 특정한 이름으로 기억시켜둔 것 호출할 때는 정해진 위치에 필요한 인수를 정확하게 입력해야 함 수식 편집 상자, 함수 마법사를 이용하여 쉽게 함수를 사용할 수 있음 함수 사용 수식 입력 기호를 클릭하면 이름 상자가 함수 상자로 변경 함수 상자의 아래쪽 화살표를 클릭하면 함수 목록이 표시됨 함수 추가를 클릭하면 함수 마법사 실행 5장 수식과 함수 사용 실용컴퓨터

지정 버튼: 수식 편집 상자가 한 줄의 입력줄로 변경 수식 편집 상자가 숨겨지고 한 줄의 인수 입력 줄로 변경 함수 사용하기 II AVERAGE()함수 지정 버튼: 수식 편집 상자가 한 줄의 입력줄로 변경 C3:D3 영역을 마우스로 드래그 하면 입력줄에 내용이 입력됨 지정 버튼을 클릭하여 수식 편집 상자로 되돌아감 수식 편집 상자가 숨겨지고 한 줄의 인수 입력 줄로 변경 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 III MAX()와 MIN() 함수 함수 목록에서 찾을 수 없으면 함수 추가를 클릭 함수 마법사를 실행하고 함수 범주 상자에서 종류를 선택한 후 함수 이름 상자에서 함수 클릭 최대값을 찾고자하는 범위를 입력 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 IV VLOOKUP() 함수 별도의 테이블을 이용하여 값을 찾을 때 이용하는 함수 별도로 만들어진 Table_array 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 V VLOOKUP 함수의 인수 Lookup_value : 값을 찾을 때 사용할 값 Table_array : 찾고자 하는 값이 있는 별도의 테이블, Lookup_value에서 입력한 값이 가장 왼쪽의 열이 되도록 범위를 지정, 수식을 복사하여 사용할 때는 절대참조로 입력 Col_index_num : Table_array에서 Lookup_value와 일치하는 행에서 가 지고올 값이 입력된 열의 위치 Range_lookup : Table_array에서 값을 찾는 방법, 정렬된 테이블에서는 비슷하게 일치하는 것을 찾는 방법(생략 혹은 0 아닌 값), 정렬되지 않은 테이블에서는 정확하게 일치하는 것을 찾은 방법(0, FALSE)을 입력 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 VI 실습 C3:C8영역에는 아래쪽의 가격료에서 코드번호를 찾아서 넣고, D3:D8 영역에는 가격을 찾아서 넣어보세요. 힌트 가격표의 첫번째 열의 값 이 정렬되지 않았으므로 Range_lookup 상자에는 정확하게 일치하는 값을 찾 기 위하여 0을 입력 코드번호와 가격은 가격표 에서 두번째 열과 세번째 열에 있으므로 Col_index_num 인수의 값만 달리 입력 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 VII RANK() 함수 순위를 정하는 방법 함수 마법사 대화상자에서 통계 범주에서 RANK 선택 오름차순 : 작은 값이 먼저 나타나도록, 사전에서 나타나는 순서대로 내림차순 : 큰 값이 먼저 나타나도록, 사전에서 나나타는 역순으로 함수 마법사 대화상자에서 통계 범주에서 RANK 선택 인수 Number : 순위를 구하고자 하는 수 Ref : 순위를 구할 수가 포함되어 있 는 범위, 수식을 복사하여 다른 셀의 Order : 정렬 방법, 생략하거나 0을 입력하면 내림차순으로 정렬되고, 0 이 아닌 수를 입력하면 오름차순으 로 정렬됨 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 VIII IF() 함수 어떤 값을 논리적으로 테스트하여 참/거짓에 따라 다른 값을 갖게 하는 함수 평균 점수가 80점 이상이면 합격이고, 미만이면 불합격이 되도록 규칙 평균값이 80점 이상인지 논리적으로 테스트 평균값이 80점 이상이면 “합격”을 입력 평균값이 80점 미만이면 “불합격”을 입력 인수 내에 IF() 함수를 중첩(7번까지)할 수 있음 if 평균 >= 80 then “합격” else “불합격” 5장 수식과 함수 사용 실용컴퓨터

Value_if_false : “불합격” 함수 사용하기 IX 함수 마법사 대화상자에서 논리값 범주에서 IF를 선택 Logical_test : 어떤 기준에 의해서 참인지 거짓인지를 판단하게 될 수식 Value_if_true : 판단 결과가 참일 때 정해지는 값, 혹은 또 다른 판단을 위한 수식 Value_if_false : 판단 결과가 거짓일 때 정해지는 값, 혹은 또 다른 판단을 위한 수식 Logical_test : F3 Value_if_true : “합격” Value_if_false : “불합격” 5장 수식과 함수 사용 실용컴퓨터

과락은 아니더라도 평균 점수가 60점 미만이면 “불합격” 함수 사용하기 X 중첩 IF() 함수I 하나 이상의 조건식 각 과목의 점수가 40점 미만이면 “과락” 과락은 아니더라도 평균 점수가 60점 미만이면 “불합격” 5장 수식과 함수 사용 실용컴퓨터

Value_if_false 인수 상자에 커서를 표시한 후 함수 상자(IF)를 클릭 함수 사용하기 XI 중첩 IF() 함수II 새로운 IF 함수를 위한 편집 상자가 나타남 Value_if_false 인수 상자에 커서를 표시한 후 함수 상자(IF)를 클릭 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XII 중첩 IF() 함수III 두 과목에 대한 과락 여부를 테스트 평균 점수가 합격 불합격인지를 테스트하기 위하여 Value_if_false 인수 상자에 커서를 나타낸 후 함수 상자(IF)를 클릭 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XIII 중첩 IF() 함수IV 마지막 IF 편집 상자의 인수 상자에 인수 입력 5장 수식과 함수 사용 실용컴퓨터

Criteria : 개수를 구하고자 하는 조건 함수 사용하기 XIV COUNTIF() 함수 조건에 맞는 셀의 개수를 구할 때 사용하는 함수 예제에서 A+를 받은 학생의 수를 구함 함수 마법사 대화상자에서 통계 범주에서 COUNTIF를 선택 찾을 조건이 텍스트이므로 조건 상자에는 텍스트 “A+”로 입력해야 함 인수 Range : 개수를 구할 범위 Criteria : 개수를 구하고자 하는 조건 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XV COUNT() 함수 개수를 구하고자 하는 범위에서 수치 데이터의 셀의 개수를 구함 Value1에 개수를구할 범위 입력 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XVI 다른 종류의 COUNT 함수 COUNTA : 지정된 범위에서 값이 들어있는 모든 셀의 개수를 구함 COUNTBLANK : 지정된 범위에서 비어있는 셀의 개수를 구함 COUNTA 와 COUNTBLANK 함수는 E열이나 F열을 범위로 지정 COUNT 함수는 C열이나 D열을 범위로 지정 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XVII 텍스트 함수 텍스트의 일부분을 원하는 길이만큼 추출하는 함수 LEFT() 함수 : 주민번호에서 출생년도 추출 MID() 함수 : 주민번호에서 출생월을 추출 RIGHT() 함수 : 주민번호에서 출생일을 추출 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XVIII LEFT() 함수 텍스트 결합 텍스트 연산자 “&”를 사용하여 두 개의 텍스트를 하나의 텍스트로 만듬 수식과 텍스트를 결합 출생년도를 추출하는 수식 : =LEFT(D3, 2) 수식 앞에 19를 결합하고, 수식 뒤에 “년” 결합 = 19 & LEFT(D3, 2) & “년” 인수 Text : 주민번호가 입력된 셀 Num_Chars : 추출할 텍스트 의 개수 5장 수식과 함수 사용 실용컴퓨터

함수 사용하기 XVIII MID() 함수 텍스트의 중간에서 추출 어디서부터 추출할 것인지, 몇 개의 텍스트를 추출할 것인지 지정 인수 Text : 주민번호가 입력된 셀 Start_num : 어디서부터 추출 한 것인지 시작 위치 지정 Num_Chars : 추출할 텍스트 의 개수 지정 5장 수식과 함수 사용 실용컴퓨터

IF() 함수를 실행하고 Logical_test 인수 상자를 클릭한 후 MID() 함수 실행 함수 사용하기 XIX IF() 함수와 MID() 함수의 중첩 사용 주민번호 중 8번째 텍스트를 추출하여 남자인지 여자인지 판단 먼저 IF()함수를 사용하고 첫번째 인수에 MID() 함수 사용 IF() 함수를 실행하고 Logical_test 인수 상자를 클릭한 후 MID() 함수 실행 5장 수식과 함수 사용 실용컴퓨터

MID() 함수의 인수를 입력한 후 수식입력줄의 IF 단어를 클릭하여 IF 함수의 편집 상자로 복귀 함수 사용하기 XX MID() 함수에서 IF() 함수로 복귀 MID() 함수의 인수를 입력한 후 수식입력줄의 IF 단어를 클릭하여 IF 함수의 편집 상자로 복귀 5장 수식과 함수 사용 실용컴퓨터

주민번호 중 여덟번째 텍스트를 추출하여 남자와 여자를 판단 함수 사용하기 XXI MID() 함수의 인수를 입력한 후 IF() 함수로 복귀 주민번호 중 여덟번째 텍스트를 추출하여 남자와 여자를 판단 5장 수식과 함수 사용 실용컴퓨터

수식과 함수 사용 실습 학번을 이용하여 학과 판단 LEFT() 함수를 이용하여 학번 에서 처음 두 문자를 추출 IF() 함수를 이용하여 학과 를 판단 41 이면 컴퓨터학과, 42 이 면 멀티미디어학과, 43이면 영상정보학과 5장 수식과 함수 사용 실용컴퓨터

수식과 함수 사용 이 절의 체크 포인트 엑셀에서 사용되는 연산자의 종류와 그 의미를 알고 있습니까? 수식을 사용하여 값을 계산할 수 있습니까? 수식을 복사하여 값을 계산할 수 있습니까? 수식을 복사할 때 인수로 사용된 범위 주소를 절대참조로 수정해야 하는 경우를 알고 있습니까? 함수를 사용하여 값을 계산할 수 있습니까? 수식 편집 상자를 이용하여 중첩 IF() 함수를 입력하는 방법을 알고 있습니까? 수식 편집 상자를 이용하여 다른 종류의 함수들을 중첩시키는 방법을 알고 있습니까? 이 절에서 다룬 함수들의 올바른 인수 사용법을 알고 있습니까? 5장 수식과 함수 사용 실용컴퓨터