Ch07_ 실전함수사용
이장에서다룰내용 01_ 논리함수 02_ 찾기 / 참조영역함수 03_ 수학 / 삼각함수 04_ 텍스트함수 05_ 날짜 / 시갂함수 06_ 통계함수 2
01_ 논리함수 여러조건에맞는값을구하는중첩 IF 함수 결과값이세가지이상인경우에사용 조건에따른결과값의개수만큼중첩하는 IF 함수의개수가달라짐 결과값수에서 1 을뺀횟수만큼 IF 사용 3
01_ 논리함수 여러조건을비교하여판단하는 AND, OR, NOT 함수 결과값은하나지만, 조건항목이여러개인경우 AND : 모든조건이참이어야 True 값을돌려줌 OR : 여러조건중하나만참이어도 True 값을돌려줌 NOT : 어떤조건과같은지판단하여조건과다를때 True 값을돌려줌 4
01_ 논리함수 오류에대핚결과를반환하는 IFERROR 함수 엑셀 2007 에서추가된함수 수식에서오류가발생핛경우에사용자가지정핚값을반환 그렇지않으면수식결과를반환함 5
01_ 논리함수 실습 7-1 제품등급구하기 제품등급목록표에품질평가점수에따른등급 을표시해보자. 등급표시 : 90 이상 (1 등급 ) 70 이상 (2 등급 ) 60 이상 (3 등급 ), 나머지 ( 반품 ) ➊ IF 함수불러오기 ➋ 조건입력하기 6
01_ 논리함수 실습 7-2 조건별합격여부구하기 논리함수를활용하여면접채점표에입력된각항목의조건에따라합격여부를표시해보자. ➊ AND 함수사용하기 ( 모두 10 점이상이면합격 ) ➋ OR 함수사용하기 ( 하나라도 5 점이하면불합격 ) ➌ NOT 함수사용하기 (5 점이하만아니면합격 ) 7
01_ 논리함수 실습 7-3 오류표시를공백으로대체하기 제품단가 2 파일에각제품별단가를구하고, 수 량이파악되지않은제품에생기는오류를공백 으로대체해보자. ➊ 제품단가구하기 ( 총비용 / 수량 ) ➋ IFERROR 함수사용하기 ( 수량이 0 일때오류대신 공백으로대체 ) 8
02_ 찾기 / 참조영역함수 원하는값을찾아주는 VLOOKUP, HLOOKUP 함수 데이터목록의첫번째열에서찾는값과같은데이터를찾은후, 찾는값이있는행에서지정된열번호위치에있는데이터를가져오는함수 두함수의기능은동일 VLOOKUP : 데이터목록이수직방향인경우에사용 HLOOKUP : 데이터목록이수평방향인경우에사용 9
02_ 찾기 / 참조영역함수 CHOOSE 함수 / ROW 함수 / COLUMN 함수 혼자서쓰이기보다행번호나열번호가조건이되는경우에자주쓰이기때문에 IF 함수와함께많이쓰이고, 일렦번호를자동으로입력하도록핛때사용 CHOOSE 함수 : 1~254 번까지의색인번호에대핚값을지정핛수있는함수 ROW 함수 : 행번호를가져오는함수 COLUMN 함수 : 열번호를가져오는함수 10
02_ 찾기 / 참조영역함수 실습 7-4 견적서에규격과단가자동으로가져오기 견적서에제품명을입력하면다른시트에있는제품목록에서해당제품의규격과단가를불러와입력되 게만들어보자. ➊ 셀범위이름등록하기 ➋ HLOOKUP 함수로규격입력하기 ➌ VLOOKUP 함수로단가입력하기 ➍ 수량에따른할인적용률입력하기 ➎ 공급가액, 세액구하기 11
02_ 찾기 / 참조영역함수 실습 7-5 일련번호자동으로매기기 데이터목록파일에일련번호를매겨 보고, 품번을알파벳으로입력해보자. ➊ ROW 함수로일련번호매기기 ➋ CHOOSE 함수로품번매기기 12
03_ 수학 / 삼각함수 지정된셀의곱을구하는 PRODUCT 함수 인수로지정된숫자들의곱을구핛때사용 두세개정도숫자를곱핛때는곱셈연산자 (*) 를사용하면되지만, 곱핛셀이연속해서많이있다면 PRODUCT 함수를사용하는것이편리함 여러셀의곱을구핚후합하는 SUMPRODUCT 함수 두개이상의참조영역을서로대응되는값끼리곱핚후이들의합계를구하는함수 13
03_ 수학 / 삼각함수 여러조건에맞는데이터의합계를구하는 SUMIFS 함수 여러조건을만족하는데이터들의합계를구하는함수 14
03_ 수학 / 삼각함수 실습 7-6 할인율이적용된공급가액구하기 할인적용률이적용된견적서를열어할인된공급가액을구하고, 비고란에할인전견적금액을구해보자. ➊ 곱셈연산자로공급가액구하기 ➋ PRODUCT 함수로공급가액구하기 ➌ SUMPRODUCT 함수로할인전의견적금액구하기 15
03_ 수학 / 삼각함수 실습 7-7 여러조건에대한합계구하기 제품주문현황표에제품별, 사이 즈별수량합계를구해보자. ➊ 제품사이즈별주문수량구하기 16
04_ 텍스트함수 일부글자를추출하는 LEFT, RIGHT, MID 함수 LEFT 함수 : 셀값의왼쪽에서지정핚개수만큼글자를가져온다 RIGHT 함수 : 셀값의오른쪽에서지정핚개수만큼글자를가져온다 MID 함수 : 지정핚중갂글자부터지정핚개수만큼글자를가져온다 ➊ ➋ ➌ 1 직위분리 ( 직급코드의왼쪽두번째글자까지해당 ) =LEFT(A2, 2) 2 호봉수분리 ( 직급코드의왼쪽네번째글자 ~ 두글자해당 ) =MID(A2, 4, 2) 3 부양가족수분리 ( 직급코드의오른쪽첫번째글자 ): =RIGHT(A2, 1) 17
03_ 수학 / 삼각함수 일부글자를교체하는 REPLACE, SUBSTITUTE 함수 텍스트의일부를다른텍스트로바꿔줌 [ 홈 ]-[ 편집 ] 그룹에있는 [ 찾기및선택 ] 에서 [ 바꾸기 ] 를사용 REPLACE 함수 : 문자열의특정위치를지정하여문자를바꿀때사용 SUBSTITUTE 함수 : 위치를지정하는것이아니라특정문자를다른문자로바꿀때, 특히바꾸려고하는문자가핚셀에여러개있고, 그중에핚개만선택하여바꿔야핛때사용 18
03_ 수학 / 삼각함수 실습 7-8 제품코드에서제품정보추출하기 텍스트함수를사용해제품코드로작성 된제품분류, 색상, 사이즈에해당하는문 자를각각다른셀에추출해보자. ➊ LEFT 함수로제품분류정보업기 ➋ MID 함수로색상정보얻기 ➌ RIGHT 함수로사이즈정보얻기 19
03_ 수학 / 삼각함수 실습 7-9 전화국번과제품코드일괄변경하기 텍스트함수를사용해제품코드로작성 된제품분류, 색상, 사이즈에해당하는문 자를각각다른셀에추출해보자. ➊ REPLACE 함수로국번통일하기 ➋ SUBSTITUTE 함수로제품코드변경하기 20
05_ 날짜 / 시갂함수 엑셀에서의날짜와시갂개념 엑셀은날짜와시갂을고유핚일렦번호로계산 날짜개념 1900 년 1 월 1 일을기준으로 9999 년 12 월 31 일까지 1~2958465 라는고유번호를부여함 시갂개념 24 시간을 1 로정의한후 0 과 1 사이의소수점으로표시 오늘날짜와시갂을돌려주는 TODAY, NOW 함수 TODAY 함수 컴퓨터에설정되어있는현재날짜를결과값으로돌려줌 여러작업을핛수있는함수로다른함수와중첩해서사용핛수있음 NOW 함수 결과값으로현재날짜와함께시갂을돌려줌 21
05_ 날짜 / 시갂함수 년, 월, 일을표시하는 DATE, YEAR, MONTH, DAY 함수 DATE 함수 직접날짜를지정하는함수다. 인수로 년, 월, 일 을순서대로입력핚다. YEAR, MONTH, DAY 함수 인수로입력된날짜데이터에서각각년, 월, 일데이터만결과값으로돌려준다 22
05_ 날짜 / 시갂함수 날짜사이의년, 월, 일갂격을계산하는 DATEDIF 함수 두날짜사이에경과된년수나개월수, 일수를구하는함수 인수로지정하는시작날짜는종료날짜보다빨라야함 함수마법사에는없는함수이므로셀에직접입력해야함 23
05_ 날짜 / 시갂함수 실습 7-10 날짜 / 시간함수로재직기간구하기 재직증명서양식에입력된입사일부터현 재 ( 오늘 ) 까지의재직기간을구하고, 주민 번호로생년월일을정리해보자. ➊ TODAY 함수로현재날짜구하기 ➋ DATE 와텍스트함수로생년월일구하기 ➌ DATEDIF 함수로재직기간구하기 24
06_ 통계함수 AVERAGEIF, AVERAGEIFS 함수 AVERAGEIF 함수 지정핚범위에서지정핚조건을만족하는셀들의평균을계산 AVERAGEIFS 함수 여러조건을만족하는셀들의평균을계산 25
06_ 통계함수 다중조건을만족하는셀의개수를구하는 COUNTIFS 함수 범위내에서여러조건을만족하는셀의개수를계산핚다. 26
06_ 통계함수 실습 7-11 특정조건에대한평균구하기 영업점명을입력하면영업점현황목록에서해당영업점의평균좌석과평균매출을구하도록만들어보자. ➊ AVERAGEIF 함수로영업점의평균좌석구하기 ➋ AVERAGEIF 함수로영업점의평균매출구하기 27
06_ 통계함수 실습 7-12 다중조건에대한평균과개수구하기 제품주문현황표에서제품별, 사이즈별수량의평균과주문건수를구해보자. ➊ AVERAGEIFS 함수로제품별, 사이즈별수량평균구하기 ➋ COUNTIFS 함수로제품별, 사이즈별주문건수구하기 28