|
4일차
필기에 나올수 있는 것. 알아두기
데이터 유효성 검사
-데이터 유효성 검사를 통해 데이터의 형식을 제어하거나 사용자가 셀에 입력하는 값을 제어할 수 있다.
- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.필기에 나올수 있는 것. 알아두기
데이터 유효성 검사
-데이터 유효성 검사를 통해 데이터의 형식을 제어하거나 사용자가 셀에 입력하는 값을 제어할 수 있다.
- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.
데이터 유효성 검사
-데이터 유효성 검사를 통해 데이터의 형식을 제어하거나 사용자가 셀에 입력하는 값을 제어할 수 있다.
- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.데이터 유효성 검사
-데이터 유효성 검사를 통해 데이터의 형식을 제어하거나 사용자가 셀에 입력하는 값을 제어할 수 있다.
- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.-데이터 유효성 검사를 통해 데이터의 형식을 제어하거나 사용자가 셀에 입력하는 값을 제어할 수 있다.
- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.- 입력할 수 있는 데이터를 특정 범위의 날짜로 제한하건, 목록을 사용하여 선택 항목을 제한하거나, 양의 정수만 입력 가능하도록 할 수 있다.
- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.- 셀에 입력할 수 있거나 입력해야 할 데이터에 적용되는 제한 사항을 정의하는 데 사용 할 수 있는 기능이다.
-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.-유효하지 않은 데이터를 사용자가 입력하지 못하도록 데이터 유효성 검사를 구성할 수 있다.
- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.- 셀에 필요한 입력 데이터가 무엇인지 정의하여 사용자가 셀을 선택하면 메시지를 표시하고 오류를 수정하는데 도움이 되는 내용을 표시하도록 할 수 있다.
- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.- 데이터 유효성 검사 전에 입력된 데이터에 대해 유효성 검사를 설정하는 경우 유효성 조건에 맞지 않는 데이터는 삭제되지 않고 그대로 존재한다.
- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.- 유효성 검사 대화상자의 설정탭의 제한 대상으로는 [정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자지정]이있다.
- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.- 데이터 유효성 검사의 제한 대상을 목록으로 설정한 경우 원본에 직접 내용을 입력할 때는 쉼표스타일(,)로 구분하여 입력해주고, 이름정의나 셀의 값을 가지고 올 때는 등호(=)로 시작한다.
- 오류메시지 스타일은 중지, 경고, 정보가 있다.
텍스트 나누기(필기 출제)
-워크시트의 한 셀에 입력되어 있는 데이터를 여러 셀로 분리시키는 기능
-범위에 포함되는 행 수는 제한을 두지 않지만, 열은 반드시 하나만 포함해야 된다.
-선택한 열의 오른쪽에는 빈 열이 한 개 이상 있어야 되며, 업는 경우 선택한 열의 오른쪽에 있는 데이터가 덮어 써진다.
중복된 항목 제거
선택된 범위 내에서 중복된 레코드에 대하여 하나를 제외하고 중복된 레코드를 모두 제거한다.
-[데이터]탭-[데이터 도구]그룹-[중복된 항목 제거]를 클릭하여 중복 내용을 제거한다.
-마우스 오른쪽 클릭-하이퍼링크 삽입/편집/삭제
----데이터베이스 함수 1-65쪽
1. 조건을 먼저 입력해야 됨.(고급필터처럼)
조건을 입력하지 않는 경우도 있음. 데이터원본의 첫행에 조건이 입력되어있으면 조건을 입력하지 않을 수도 있다. 문제에서 조건을 입력하라는 셀 주소를 알려줌. 안알려주면 데이터원본에서 조건의 범위를 지정할 것.
2. 특정한 조건에 맞는 집계함수를 구함.
3. 집계함수 : 합계:Sum, 평균:Average, 숫자개수:Count, 공백제외한 개수:COUNTA, 최대값:Max, 최소값:Min, 곱:Product, 표준편차:Stdev, 분산:Var
4. =D______(제목포함데이터전체범위, 계산할 필드번호 또는 셀주소, 조건의범위)
데이터들을 곱함 : =PRODUCT(범위1,범위2)
같은위치에 있는 것들끼리 곱한 후 더함 : =SUMPRODUCT(범위1, 범위2)
1-70수학과 삼각 함수
행력식에 대한 식(자주출제되지않아요. 지금까지 1번나왔음)
=ROUND(인수, 소수점자리수)
조건이 1개일 때의 개수 구하는 함수
=COUNTIF(조건의 범위, 조건) ※조건을 입력할 때는 큰따옴표 안에 써준다. 셀주소를 조건에 넣어 줄 수도 있다.
조건이 1개일 때의 합계 구하는 함수
=SUMIF(조건의 범위, 조건, 합계를 구하는곳 범위)
조건이 여러개 일때의 개수구하기
=COUNTIFS(조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3,,,,,)
조건이 2개이상일 때의 합계구하기
=SUMIFS(합계를구하는곳 범위,조건1의범위, 조건1, 조건2의범위, 조건2, 조건3의범위, 조건3)
-------------통계함수 1-81쪽
MIN(인수1,인수2...), COUNT(인수1,인수2...), COUNTA(인수1,인수2..), COUNTIF(조건의 범위, 조건), COUNTIFS(조건의범위1, 조건1, 조건의범위2, 조건2...)
LARGE(범위, N번째), SMALL(범위, N번째), FREQUENCY(데이터배열, 구간배열)
평균 연속 =AVERAGE(A1:C1) 비연속 =AVERAGE(A1,C1,E1)
평균값을 가지고 등수구하기
큰값 1등(내림차순) : 성적표
=RANK(평균첫번째 값이, 전체 평균값에서)
작은값 1등(오름차순) : 달리기 등등
=RANK(평균첫번째 값이, 전체 평균값에서,1)
큰값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
작은값이 1등인 경우
=RANK.AVG(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 평균 순위가 반환됨.
=RANK.EQ(평균첫번째 값이, 전체 평균값에서,1) : 등수가 같으면 값 집합에서 가장 높은 순위가 반환 됨.
조건부 서식 식 : =$C11>=LARGE($C$11:$C$17,3)
컴활2급 실기에서 자주 출제되었던 문제.
조건이 1개 일때의 평균 구하기
=AVERAGEIF(조건의 범위, 조건, 평균을 구하는 곳 범위)
조건이 2개 이상일때의 평균 구하기
=AVERAGEIFS(평균을 구할곳범위, 조건1의 범위, 조건1, 조건2의 범위, 조건2)
C://2021컴활1급\스프레드시트\함수사전\통계.XLSX 파일 열기
=FREQUENCY(데이터배열, 구간배열)은 먼저
1.값을 구할곳 범위를 블럭지정한 후
2. 수식을 입력
3.CTRL + SHIFT + ENTER를 눌러 수식을 완성한다. 결과는 배열수식 형태로 표시된다.
배열수식은 데이터 전체를 가지고 작업한 것을 말하며, 결과를 보면 수식의 맨앞과 맨뒤에 중괄호'{ }' 가 표시된다.
결과 : {=FREQUENCY($C$4:$C$11,$F$4:$F$8)}
배열수식으로 한 것은 배열의 일부분만 수정할 수 없음.
p.1-87 찾기와 참조 함수
찾기 참조함수에서 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 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
1-90 [표4] 직위와 직위표를 이용하여 직위별 수령액을 구하시오.
▶ 수령액 : 기본급+직위수당+가족수당
1) 기본급 : =HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
=HLOOKUP(H14,$G$25:$J$27,2,0) 또는 =HLOOKUP(H14,$G$25:$J$27,2,False)
2) 직위수당
+HLOOKUP(H14,$G$25:$J$27,3,0) 또는 +HLOOKUP(H14,$G$25:$J$27,3,False)
3) 가족수당
+I14
4) 정답 : =HLOOKUP(H14,$G$25:$J$27,2,0)+HLOOKUP(H14,$G$25:$J$27,3,0)+I14
LOOKUP 함수를 할때는 데이터가 오름차순으로 정렬이 되어있는 상태에서 사용하세요. 그리고 찾는값이 들어있는 범위 높이와 결과를 표시할 범위의 높이가 같아야 됨.
=LOOKUP(찾는값, 찾는값이 들어있는 범위, 결과를표시할 범위)
리스트안에서 값을 찾아서 가져오는 함수
=CHOOSE(숫자값, 값1, 값2, 값3,...) 숫자값에 써있는 번호로 가서 그곳의 값을 표시해줌.
예를들어 =CHOOSE(3, 10, 20, 30, 40) 결과 : 30
평가점수별로 상위2명은 “성과급”, 중간3명은 공란, 하위 1명은 “감봉”으로 결과를 표시
=CHOOSE(평가점수의등수, “성과급”,“성과급”,“”,“”,“”,“감봉”)
=CHOOSE(RANK(평가점수첫번째값이, 평가점수전체범위안에서),“성과급”,“성과급”,“”,“”,“”,“감봉”)
요일을 구하는 시험문제 형태 3가지
1. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오.
2. CHOOSE, WEEKDAY 함수를 이용하여 요일을 구하시오. Return_type은 기본값으로 하시오.
1. 2. 번은 모두 =CHOOSE(WEEKDAY(C2),"일","월","화","수","목","금","토")
3. CHOOSE, WEEKDAY 함수를 이용하여 월요일부터 표시되도록 구하시오.
=CHOOSE(WEEKDAY(C2,2),"월","화","수","목","금","토","일")
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
=INDEX(범위, 행, 열) =INDEX(범위, 위치번호)
=COLUMN() 열번호를 반환
=ROW() 행번호를 반환
=COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROWS(범위) 범위 안에 사용된 행 개수를 구함.
|