3일차
----------------수식
Ctrl + ~ : 수식을 볼 수 있도록 해줌
참조형식(1-72쪽) 참조형식을 바꿀때 F4키를 누름
상대참조 : C7 채우기 핸들로 드래그 할 경우 열문자와 행번호가 하나씩 증가함.
절대참조 : $C$7 채우기 핸들로 드래그 할 경우 $ 표시 때문에 행과 열이 고정됨.
혼합참조 : C$7 채우기 핸들로 드래그할 경우 행 번호는 고정되고 열문자만 변경된다.
$C7 채우기 핸들로 드래그할 경우 열 문자는 고정되고 행번호만 1씩 증가한다. (조건부서식, 고급필터의 계산필드 때 사용함)
-수식은 등호(=) 나 더하기(+)로 시작해야 된다
-문자열을 입력할 때는 큰 따옴표(" ")로 묶는다.
-문자열을 연결할 때 사용하는 연산자 &
-참조할 셀 영역을 지정할 때 사용하는 연산자 : 콜롬(:)연속적인 셀 영역을 지정, 쉼표(,)연속적이지 않은 셀 영역을 지정, 공백(두 범위가 교차하는 셀 영역을 지정함)
-다른 워크시트의 셀 참조 : 셀 주소 앞에 워크시트 이름을 표시하고 워크시트 이름과 셀 주소 사이는 느낌표(!)로 구분함.(예 : =A5*Sheet2!A5)
-워크시트 이름이 공백을 포함하는 경우 워크시트 이름을 작은 따옴표(' ')로 감쌈.(예 : =A5*'성적 일람'!A5)
-다른 통합 문서의 셀 참조 : 통합 문서의 이름을 대괄호([])로 둘러싸고, 워크시트 이름과 셀 주소를 입력함.(예 : =A5*[성적일람표.xlsx]Sheet1!A5)
통합 문서의 이름이 공백을 포함하는 경우 통합 문서와 시트 이름을 작은 따옴표(' ')로 감쌈
(예 : =A5*'[성적 일람표.xlsx]Sheet1'!A5)
----------------------수식의 오류값
#### : 숫자 데이터의 길이가 셀보다 길 때
#NULL! : 교차하지 않는 두 영역의 교점을 지정했을 때
#DIV/0 : 특정한 숫자를 0으로 나누는 수식을 입력했을 때
#VALUE! : 잘못된 인수 또는 피연산자를 사용했을 때
#REF! : 유효하지 않은 셀 참조를 지정했을 때
#NAME : 인식할 수 없는 문자열을 수식에 사용했을 때 또는 참조하고 있는 이름을 삭제했을 때
#NUM! : 수식이나 함수에 숫자와 관련된 문제가 있을 때
#N/A : 함수나 수식에 사용할 수 없는 값을 지정했을 때(차트에서도 사용됨)
-함수 마법사 (SHIFT + F3)
합계 : =Sum(범위) 예) =sum(A1:C1) 떨어져 있을 때는 =sum(a1,c1,f1)
합계(Sum)
=SUM(1,2,"사랑") 으로 입력하면 #VALUE! 에러가 표시됨
=SUM(1,2,사랑) 으로 입력하면 #NAME! 에러가 표시됨.
평균 : =Average(범위)
최대값 : =max(범위)
최소값 : =min(범위)
등수 : =Rank(값, 값의 전체범위, 정렬방식)
정렬방식을 생략하면 큰값이 1등이되는 내림차순정렬
정렬방식에 1을 쓰면 작은값이 1등이되는 오름차순정렬
숫자가 있는 셀 개수 : =count(범위)
공백을 제외한 채워져있는 셀 개수 : =counta(범위)
공백 개수 : =countblank(범위)
연결연산자 : &
인원을 '9명' 으로 표시되도록 하시요.
=COUNT(B2:B14)&"명"
="총 인원 "&E2&"명에서 "&E3&"명 납부하였습니다."
----날짜함수1-77쪽
엑셀 오늘날짜 =today() VBA : =DATE() / 엑세스/VBA =date()
오늘날짜와 현재시간 =now() / =now()
표시된 숫자를 날짜로 표시 =date(년, 월, 일) / =dateserial(년, 월, 일)
표시 예 : =DATE(2015,5,1) / =DATESERIAL(2015,5,1)
결과 : 2015-05-01 / 결과 : 2015-05-01
요일 =weekday(날짜)
1이 일요일을 뜻함.
1(일),2(월),3(화),4(수),5(목),6(금),7(토)
오늘날짜 : ctrl + ;
현재시간 : ctrl + shift + ;
DATEDIF(시작일, 종료일, “단위”) 시작일부터 종료일까지 기간이 얼마나 지났는지를 알려주는 함수
단위
Y : 년를 계산
M : 월을 계산
D : 일을 계산
YM : 연도를 제외한 개월 수를 계산
YD : 연도를 제외한 일 수를 계산
MD : 연과 월을 무시한 일 수를 계산
예를들어 2010-5-10(A1)일에 입사하여 2018-1-15(B1)일에 퇴사하였다. 이사람이 제직한 근무 년수는 얼마나 되는가?
=DATEDIF(A1,B1,“Y”)&“년 ”&DATEDIF(A1,B1,“YM”)&“개월 ”&DATEDIF(A1,B1,“MD”)&“일간”
엑세스에서는 =DATEDIFF(단위, 시작일, 종료일)
----1-83쪽
논리함수
IF함수
1. 결과로 표시해야 될 것이 몇 개 인지 파악할것.
결과로 나타내야되는 것보다 IF문은 한개 적개쓰면 됨
예를들어 "합격","불합격" 두개이면 IF문은 한번
=IF(조건식, 참, 거짓)
"상","중","하"이면 IF문은 2개
=IF(조건식, 참, IF(조건식,참,거짓))
"수","우","미","양","가"이면 IF문은 4개쓰면 됨
=IF(조건식, 참, IF(조건식,참,IF(조건식,참,IF(조건식,참,거짓))))
2. 문제를 나눠줄 부분을 구분할 줄 알아야됨
=IF(조건식, 참, 거짓)
1. 점수가 70이상이면 "합격", 그렇지 않으면 "불합격"으로 표시하세요.
말로풀기 : =IF(점수>=70,"합격","불합격")
정답 : =IF(C3>=70,"합격","불합격")
2. 최종성적이 90점 이상이면 “기획부”, 80점이상이면 “총무부”, 나머지는 “영업부”로표시
말로풀기 : =IF(최종성적>=90,“기획부”,IF(최종성적>=80,“총무부”,“영업부”))
정답 : =IF(H3>=90,"기획부",IF(H3>=80,"총무부","영업부"))
3. 근무년수가 15년 이상이고 판매부수가 100000이상이면 “승진” 그렇지 않으면 공란으로 표시
말로풀기 : =IF(AND(근무년수>=15,판매부수>=100000),“승진”,“”)
정답 : =IF(AND(B15>=15,C15>=100000),"승진","")
4. 컴일과 시트 두 과목 중 하나라도 85점 이상인 학생의 경우에 평가에 “이수” 그 외에는 공백으로 처리
말로풀기 : =IF(OR(컴일>=85, 시트>=85),“이수”,“”)
정답 : =IF(OR(H15>=85,I15>=85),"이수","")
5. ‘인원’에 100000원을 곱하여 금액에 표시하고 오류가 있을 때에는 ‘입력오류’라고 표시
말로풀기 : =IFERROR(인원*100000,“입력오류”)
정답 : =IFERROR(C26*100000,"입력오류")
1-98쪽
문자열 함수
엑셀 소문자 =LOWER(텍스트) 엑세스 소문자 =LCASE(필드명)
엑셀 대문자 =UPPER(텍스트) 엑세스 대문자 =UCASE(필드명)
=LEFT(텍스트, 추출할 개수) =RIGHT(텍스트, 추출할 개수) =MID(텍스트, 시작위치번호, 추출할 개수)
생일 주민등록번호 앞 6자리를 이용해서 생년월일 구하기
=DATE(년, 월, 일) => =DATE(주민등록번호앞2자리, 주민등록번호3~4번째자리, 주민등록번호5~6번째자리)
=DATE(LEFT(주민번호,2),MID(주민번호,3,2),MID(주민번호,5,2))
만나이=현재연도-태어난연도
=YEAR(TODAY())-YEAR(생년월일) => 날짜-날짜 뺀형식이라서 결과가 날짜로 표시됨. 반드시 셀서식-표시형식-일반 범주로 변경해 줄 것.
나이=현재연도-주민등록번호의 왼쪽 2자리-1900
=YEAR(TODAY())-LEFT(C3,2)-1900
성별 주민등록번호의 8번째 숫자를 이용하여 “남자”,“여자”로 표시하시오.
=IF(주민등록번호의8번째 숫자가=“1”,“남자”,“여자”)
=IF(MID(주민등록번호,8,1)=“1”,“남자”,“여자”)
주민등록번호의 뒷자리 첫글자가 1또는 3이면 “남자” 그렇지 않으면 “여자”로 표시하시오.
=IF(OR(MID(C3,8,1)="1",MID(C3,8,1)="3"),"남자","여자")
3일차.hwp