1급 5일차.hwp
5일차
데이터 유효성 검사
-데이터 유효성 검사를 통해 데이터의 형식을 제어하거나 사용자가 셀에 입력하는 값을 제어할 수 있다.
- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
p.123 찾기와 참조 함수
찾기 참조함수에서 VLOOKUP, HLOOKUP 함수는 참조표를 가지고 구분하면됨.
TRUE( 1 ) : 연결값과 참조의범위안에 연결된 값들중에서 유사일치한 값을 가지고 와야될때 사용됨.
대체적으로 참조의범위에서 숫자로 연결시켰을 경우에 옵션을 1(TRUE)로 많이 주는데 단, 찾는값 부분이 LEFT, RIGHT, MID, LARGE, SMALL, max, min 등과같이 함수로 연결시켜주게 되면 정확히 일치하는 것(FALSE)으로 찾아줘야됨.
FALSE( 0 ) : 찾는값과 참조의 범위의 연결된 부분이 문자형태로 되어있을 때 주로 이용함. 정확하게 일치한것만 찾아서 가지고 올때 사용.
참조표가 열단위로 되어있으면 VLOOKUP
=VLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 열의 번호, 검색유형)
검색유형은 정확하게 일치하면 FALSE(0)을 근사치한 값을 가져올때는 TRUE(1)을 쓰면 된다.
참조표가 행단위로 되어있으면 HLOOKUP
=HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
※ 검색 유형 부분은 찾는값 부분과 참조의 범위의 내용이 어떻게 되어있는지에 따라서 정해주면 되는데 찾는값 부분이 문자로 되어있으면 검색유형은 같은 글자에 대한 내용을 찾아와서 표시해주는게 되므로 정확히 일치(FALSE 또는 0) 를 입력해주면되고, 찾는값 부분이 숫자로 되어있을때는 유사일치(TRUE 또는 1)로 입력하는 경우가 많다. 단, 찾는값 부분이 LARGE, SMALL, MAX, MIN 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
129쪽
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
P. 130쪽
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열)
=INDEX(범위, 위치번호)
=INDEX(C4:G9, MATCH(C13,B4:B9,1),MATCH(C12,C3:G3,0))
132쪽
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.
----데이터베이스 함수 1-91쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
p.136 정보함수
ISBLANK(값) : 공백 셀이면 TRUE
ISERROR(값) : 에러값이면 TRUE
ISERR(값) : #N/A 오류를 제외한 오류의 값일 경우 TRUE
ISEVEN(값) : 숫자가 짝수이면 TRUE
ISODD(값) : 숫자가 홀수이면 TRUE
ISNUMBER(값) : 숫자값이면 TRUE
ISTEXT(값) : 텍스트값이면 TRUE
=IF(ISBLANK(C4)," ", VLOOKUP(C4,$I$4:$K$13,3,0))
=IF(ISERROR(E4*F4)," ",E4*F4)
규격, 단가 : 품명이 공백일 경우에는 공백으로 처리하고 품명이 공백이 아니면, vlookup 함수를 이용하여 제품목록에서 찾아 표시하시오.
1) =VLOOKUP(연결값, 참조의전체범위, 열 번호, 연결방법)
연결값은 값을 구하는 곳의 줄 중에 한곳을 이용하여야 한다.
참조의 전체범위는 데이터 제목을 포함해도 되고 안해도 되는데 연결값부분의 내용이 들어있는 곳부터 블록지정을 해야된다.
=VLOOKUP(C4,$I$4:$K$13,2,0)
2) 만약에 C4셀이 공백이면...공백으로 표시하고 그렇지 않으면 VLOOKUP함수의 내용을 표시
규격 =IF(ISBLANK(C4),"",VLOOKUP(C4,$I$4:$K$13,2,0))
단가 =IF(ISBLANK(C4),"",VLOOKUP(C4,$I$4:$K$13,3,0))
공급가액은 IF와 ISERROR 함수를 이용하여 수량*단가의 계산식에 오류가 발생하면 공백으로 처리하고, 오류가 없으면 수량*단가를 계산하여 표시하시오.
1) = 수량*단가를 한 후 채우기 핸들로 드래그해보면 오류가 나오는 것을 확인할 수 있다.
2) =IF(ISERROR(수량*단가),“”,수량*단가)처럼 하고 드래그하면 오류가 나오지 않음.
=IF(ISERROR(E4*F4),"",E4*F4)
배열수식 1-144
배열수식은 조건의 내용이 들어있는 데이터 전체를 가지고 수식을 입력하게됨.
p. 143
조건이 1개일 때 남자가 몇 명?
sum 함수 사용
=(조건1)*1
=(성별전체범위=“남”)*1
=SUM((조건1)*1)
=SUM((성별전체범위=“남”)*1) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*1)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,1)
=SUM(IF(성별전체범위=“남”,1)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",1))}
조건이 1개일 때의 합계구하기
성별이 남인 사람들의 금액 합계구하기
SUM 함수 이용 =SUM((조건1)*값을 구하는곳 범위)
=(성별전체범위=“남”)*값을 구하는곳 범위
=SUM((성별전체범위=“남”)*값을 구하는곳 범위) CTRL + SHIFT + ENTER
{=SUM((B2:B6="남")*C2:C6)}
SUM, IF 함수 이용
=IF(성별전체범위=“남”,금액 전체범위)
=SUM(IF(성별전체범위=“남”,금액 전체범위)) CTRL + SHIFT + ENTER
{=SUM(IF(B2:B6="남",C2:C6))}
P.147
1번
=average(if(조건식, 값을 구할 범위지정))
1) =IF(A4=$A$11:$A$33,$C$11:$C$33) 먼저 입력 후
2) =AVERAGE(IF(A4=$A$11:$A$33,$C$11:$C$33))
average 함수를 맨 앞에 넣어주면 괄호()를 빼먹는 일을 줄일 수 있어요
3) 수식을 다 입력한 후에 배열 수식은 반드시
ctrl + shift + enter를 눌러서 수식의 맨 앞과 뒤에 중괄호 ( { } )가 생기도록 해주세요
개수 구할 때
1) sum 과 if 함수 이용시
=SUM(IF(A4=$A$11:$A$33,1))
2) sum 함수만 이용시
조건1개
=SUM((조건1)*1)
조건2개
=SUM((조건1)*(조건2))