메뉴 건너뛰기

XEDITION

Study

Day04

    셀 서식 불러 오기 CTRL + 1

    수 표현에서 #,### 와 #,##0의 표현은 다른다. 

        #은 값이 없으면 보여주지 않는다. 

    Sheet 복사 : CTRL + Drag

    날짜 기한 체크 =IF(G6>=5,"O","X") ----  5 이상이면 O  표시

 

Day06

    데이터 유효성 검사 : 데이터를 직접 입력하지 않고 선택하는 기능

            --> 데이터 오류를 방지. 

 

    카메라 기능 : 그림 복사 기능(엑셀의 캡쳐 기능) - 퀵 메뉴 버튼에서 카메라 기능 추가. 

            --> 복수 + 붙여넣기 ->그림형식 -> 연결하여 붙여넣기) 하면 같은 효과.. 

 

    조건부 서식 : 조건에 따라서 데이터에 서식을 지정하는 기능. 

             --> 홈 --> 조건부 서식

              F4 : 참조를 변경할 수 있다. (절대참조, 혼함참소, 수식참조)

 

Day07

    * 수식은 반드시 등호(=)로 시작

    * 참조 (단축키 F4)

        상대참조 : 수식을 복사할 때 주소가 자등올 변경 예)C6

        절대참조 : 수식을 복사해도 주소가 바뀌지 않음 예)$C$6

        혼함참조 : 수식을 복사할 때 행이나 열만 고정 예)$C6 , C$6

 

 

Day08

    * 함수 

        복잡하고 반복적인 계산 작업을 쉽고 간단하게 처리힐 수 있도록 미리 르로그램으로 정의한 것. 

        =함수명(인수, 인수, ...)

        =IF(H5>=5,"개설", "폐강")

        =비교연산자 

        =if(조건1, 참, if(조건2, 참, if(조건3, 참, 거짓)))

 

Day09

    P125~P133 다시 연습. 

    코드의 A이미면 영유아, B 아동, 그외에는 성인

    IF(조건, 참, IF(조건, 참, 거짓))

    

    _ IT점수.xlm

    선물 : 이름의 첫 글자가 "박" 이면 "책"

    이름의 첫 글자가 "강"이면 "연필"  그렇지 않으면 "노트" 

    순위 : IT 점수의 순위

 

Day13. 

    데이터 베이스  : 방대한 양의 데이터를 특정한 용도에 맞게 체계적으로 정리해 노은것. 

    정렬, 자동필터, 고급 필터, 부분합, 피벗 테이블

    필터 : 특정한 조건에 만족한 데이터만을 추출하는 기능(자동, 고급)

    P175~P188

    사용자 지정 목록 -> 지출 방법

    자동이체 현금, 체크카드, 신용카드

 

수학 / 삼각 함수

함수명 인수 설명 활용예
SUM SUM(수1, 수2…), SUM(범위) 합계 =SUM(10,20,30) 60
ROUND ROUND(수, 자릿수) 반올림 =ROUND(1234.5678,1)
=ROUND(1234.5678,-2)
=ROUND(1234.5678,0)
1234.6
1200
1235
ROUNDUP ROUNDUP(수, 자릿수) 올림 =ROUNDUP(1234.5678,1)
=ROUNDUP(1234.5678,-2)
=ROUNDUP(1234.5678,0)
1234.6
1300
1235
ROUNDDOWN ROUNDDOWN(수, 자릿수) 내림 =ROUNDDOWN(1234.5678,1)
=ROUNDDOWN(1234.5678,-2)
=ROUNDDOWN(1234.5678,0)
1234.5
1200
1234
PRODUCT PRODUCT(수1, 수2…),
PRODUCT(범위)
=PRODUCT(1,2,3) 6
SUMPRODUCT SUMPRODUCT(배열1, 배열2...) 배열의 해당 요소를 모두 곱하여 합한다. =SUMPRODUCT(A1:A5,C1:C5) [A1:A5], [C1:C5]에서 대응되는 요소의 곱한 결과의 합계 계산
SUMIF SUMIF(조건범위, 조건, [합계범위]) 조건범위에서 조건에 맞는 합계범위의 값을 합한다. =SUMIF(A1:A5,"경기",F1:F5) A1:A5 범위에서 경기 데이터가 있을 경우 F1:F5 범위에 대응하는 곳에 있는 데이터의 합계 계산
ABS ABS(수) 절대값 =ABS(6-9) 3
MOD MOD(수, 나누는수) 나머지 =MOD(12,5) 2
INT INT(수) 정수 =INT(5.68)
=INT(-5.68)
5
-6
TRUNC TRUNC(수, [자릿수]) 버림 =TRUNC(5.68)
=TRUNC(-5.68)
5
-5

 

 통계함수

함수명 인수 설명 활용예
AVERAGE AVERAGE(수1, 수2…)
AVERAGE(범위)
평균 =AVERAGE(10,20,30) 20
AVERAGEA AVERAGEA(수1, 수2…)
AVERAGEA(범위)
문자열과 논리값을 포함하여 평균 계산 =AVERAGEA(10,20,30,TRUE) 15.25
MAX MAX(값1, 값2…), MAX(범위) 최대값 =MAX(10,20,30) 30
MIN MAX(값1, 값2…), MAX(범위) 최소값 =MIN(10,20,30) 10
LARGE LARGE(범위, 순위) 범위에서 순위번째로 큰 값 =LARGE(A1:A10,2) A1:A10 범위에서 2번째 큰 값
SMALL SMALL(범위, 순위) 범위에서 순위번째로 작은 값 =SMALL(A1:A10,2) A1:A10 범위에서 2번째 작은 값
MEDIAN MEDIAN(값1, 값2…), MEDIAN(범위) 중간값 =MEDIAN(10,15,20,30,35) 20
RANK RANK(수, 범위, [옵션])
옵션
0 또는 생략 : 큰 수가 1위(내림차순)
1 : 작은 수가 1위(오름차순)
범위에서 수의 순위를 구함 =RANK(A1,$A$1:$A$10) A1:A10 범위에서 A1 셀의 순위를 구함
VAR VAR(수1, 수2…), VAR(범위) 분산 =VAR(A1:A10) A1:A10 범위의 분산을 구함
STDEV STDEV(수1, 수2…), STDEV(범위) 표준편차 =STDEV(A1:A10) A1:A10 범위의 표준편차를 구함
MODE MODE(값1, 값2…), MODE(범위) 최빈값 =MODE(10,12,10,12,12) 12
FREQUENCY FREQUENCY(데이터 배열, 구간 배열) 빈도 분포 값을 수직 배열로 구함 =FREQUENCY(A1:A5,B1:B5) A1:A5 범위에서 B1:B5 간격에 해당하는 분포수를 구함
COUNT COUNT(값1, 값2…), COUNT(범위) 수치 데이터의 개수 =COUNT(10,20,30) 3
COUNTA COUNTA(값1, 값2…), COUNTA(범위) 공백이 아닌 데이터의 개수 =COUNTA(가,나,다) 3
COUNTBLANK COUNTBLANK(값1, 값2…)
COUNTBLANK(범위)
공백 셀의 개수 =COUNTBLANK(A1:A10) A1:A10 범위에서 공백 셀의 개수를 구함
COUNTIF COUNTIF(조건 범위, 조건) 조건에 맞는 셀의 개수 =COUNTIF(A1:A10,"부산") A1:A10 범위에서 '부산' 문자열이 입력된 셀의 개수

 

날짜/ 시간 함수

함수명 인수 설명 활용예
TODAY TODAY() 시스템의 현재 날짜 =TODAY() 2013-06-21
NOW NOW() 시스템의 현재 날짜와 시간 =NOW() 2013-06-21 12:13
YEAR YEAR(날짜) 년도 =YEAR("2013-6-21") 2013
MONTH MONTH(날짜) =MONTH("2013-6-21") 6
DAY DAY(날짜) =DAY("2013-6-21") 21
HOUR HOUR(시간) =HOUR("12:13:24") 12
MINUTE MINUTE(시간) =MINUTE("12:13:24") 13
SECOND SECOND(시간) =SECOND("12:13:24") 24
DATE DATE(년, 월, 일) 새로운 날짜 만들기 =DATE(2013,6,21) 2013-06-21
TIME TIME(시, 분, 초) 새로운 시간 만들기 =TIME(13,5,30) 1:05 오후
WEEKDAY WEEKDAY(날짜, [옵션])
옵션
1 또는 생략 : 일요일을 1로 시작
2 : 월요일을 1로 시작
3 : 월요일을 0으로 시작
요일 =WEEKDAY("2013-6-21")
=WEEKDAY("2013-6-21",2)
=WEEKDAY("2013-6-21",3)
6
5
4
DAYS360 DAYS360(시작날짜, 종료날짜, [옵션])
옵션
TRUE : 유럽식
FALSE 또는 생략 : 미국식
경과 일수 =DAYS360("2013-1-31","2013-6-20") 140

 

논리함수

함수명 인수 설명 활용예
IF IF(조건식, [값1], [값2]) 조건식이 참이면 값1, 아니면 값2를 표시 =IF(A1>=70,"합격","불합격") A1셀의 값이 70 이상이면 '합격', 그렇지 않으면 '불합격'을 표시
=IF(A1>=90,"A",IF(A1>=80,"B","C")) A1셀의 값이 90 이상이면 'A', 80 이상이면 'B', 아니면 'C'를 표시
=IF(AND(A1>=90,B1>=90),"우수","") A1셀의 값이 90 이상이고, B1 셀의 값이 90 이상이면 '우수', 아니면 공백을 표시
=IF(OR(A1="해당",B1>=60),"승진","") A1셀의 값이 '해당'이거나 B1셀의 값이 60 이상이면 '승진', 아니면 공백을 표시
AND AND(논리식1, 논리식2…) 논리식이 모두 참이면 TRUE, 아니면 FALSE =AND(10>6, 5=5, 9<10)
=AND(10<=6, 5=5, 9<10)
TRUE
FALSE
OR OR(논리식1, 논리식2…) 논리식 중에서 하나라도 참이면 TRUE, 모두 거짓이면 FALSE =OR(10<6,5=5,9>10)
=OR(10<6,5<>5,9>10)
TRUE
FALSE

 

장보함수

함수명 인수 설명 활용예
ISBLANK ISBLANK(검사대상셀) 검사대상셀이 공백이면 TRUE, 아니면 FALSE =ISBLANK(A1) A1셀이 공백셀이면 TRUE, 아니면 FALSE
ISERROR ISERROR(검사대상) 검사대상이 에러이면 TRUE, 아니면 FALSE =ISERROR(SUM(가,나,다))
=ISERROR(5/0)
TRUE
TRUE

 

찾기/참조 함수

함수명 인수 설명 활용예
HLOOKUP HLOOKUP(검색값, 참조범위, 행번호, [옵션])
옵션
TRUE 또는 생략 : 검색값과 유사한 값을 찾음
FALSE : 검색값과 정확히 일치하는 값을 찾음
참조범위의 첫 행을 검사하여 지정한 행에서 검색값에 해당하는 열의 셀 값을 구함 =HLOOKUP("김명철",A3:F10,3,FALSE) 3행에서 '김명철'을 찾아 5행에서 같은 열에 있는 값을 표시
VLOOKUP VLOOKUP(검색값, 참조범위, 열번호, [옵션])
옵션 - HLOOKUP과 같음
참조범위의 첫 열을 검사하여 지정한 열에서 검색값에 해당하는 행의 셀 값을 구함 =VLOOKUP("김명철",A3:F10,3,FALSE) A열에서 '김명철'을 찾아 C열에서 같은 행에 있는 값을 표시
LOOKUP LOOKUP(검사값, 검사범위, 대응범위) 벡터나 배열에서 값을 검색 =LOOKUP(A1,B1:B5,C1:C5) A1셀의 값을 B1:B5 범위에서 검색하고, 동일한 행에 위치한 C1:C5의 값을 결과로 표시
CHOOSE CHOOSE(인덱스번호, 값1, 값2, …) 값들 중에서 인덱스번호에 해당하는 값 선택 =CHOOSE(3,"가","나","다","라")
INDEX INDEX(범위, 행번호, [열번호])  범위나 배열에서 행번호, 열번호에 해당하는 값을 구한다. =INDEX(A1:F5,4,3) A1:F4 범위에서 4번째 행과 3번째 열에 위치한 값을 찾아 표시
OFFSET OFFSET(기준, 행수, 열수, [높이], [폭]) 기준으로부터 행 또는 열 수만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시 =OFFSET(A1,3,2) A1 셀에서 3행 아래로 이동하면 A4, A4 셀에서 2열 이동하면 C4셀이므로 C4셀의 값을 표시
MATCH MATCH(검사값, 검사 범위, [옵션])
옵션
0 또는 생략 : 검사값과 같은 첫번째 값을 찾음
1 : 검사값보다 작거나 같은 값 중에서 최대값을 찾음
-1 : 검사값보다 크거나 같은 값 중에서 최소값을 찾음
검사 범위에서 검사값에 해당하는 값의 위치를 구함 =MATCH("나",{"가","나","다","라"},0) 2
TRANSPOSE TRANSPOSE(배열) 배열의 행과 열을 바꿈 =TRANSPOSE(A1:C3) A1:C3 배열의 행과 열을 바꾸어 표시

 

문자열 함수

함수명 인수 설명 활용예
LEFT LEFT(문자열, 추출할 문자수) 문자열의 왼쪽에서 추출 =LEFT("아름다운 우리나라",2) 아름
RIGHT RIGHT(문자열, 추출할 문자수) 문자열의 오른쪽에서 추출 =RIGHT("아름다운 우리나라",2) 나라
MID MID(문자열, 시작 번호, 추출할 문자수) 문자열의 중간에서 추출 =MID("750923-*******",3,2) 09
LOWER LOWER(문자열) 소문자로 변환 =LOWER("ABC") abc
UPPER UPPER(문자열) 대문자로 변환 =UPPER("abc") ABC
PROPER PROPER(문자열 단어의 첫 글자만 대문자로 변환 =PROPER("I LOVE YOU") I Love You
LEN LEN(문자열) 문자열의 글자수(길이)를 구함 =LEN("I LOVE YOU") 10
TEXT TEXT(수, 표시형식) 수에 형식을 적용한 후 텍스트로 변환 =TEXT(35,"#명") 35명
VALUE VALUE(숫자형 문자열) 문자열을 수로 변환 =VALUE("1") 1

 

DB 함수

함수명 인수 설명 활용예
DSUM 함수명(데이터베이스범위, 필드 번호, 조건 범위)
※ 필드 번호는 필드명이나 문자열로 대체 가능

DSUM(A1:G20,3,A1:A2)
DSUM(A1:G20,C1,A1:A2)
DSUM(A1:G20,"점수",A1:A2)
세가지 모두 가능
조건에 맞는 값의 합계 =DSUM(A1:A20,3,A1:A2)

※ 다른 함수도 같은 방법으로 각자의 계산을 한다.
A1:A20 범위에서 A1:A2의 조건에 맞는 데이터를 찾아 3번째 열에서 합계를 구함
DAVERAGE 조건의 맞는 값의 평균
DCOUNT 조건에 맞는 값의 개수
DCOUNTA 조건에 맞는 값의 개수
DMAX 조건에 맞는 값의 최대값
DMIN 조건에 맞는 값의 최소값
DSTDEV 조건에 맞는 값의 표준편차
DVAR 조건에 맞는 값의 분산
DGET 조건에 맞는 값 추출
DPRODUCT 조건에 맞는 값의 곱

 

셀 서식을 활용한 단위 변경 

데이터 0 #,##0 # #,### #, #,###, #,, #,###,, #,###,_-
0 0 0              
1 1 1 1 1          
12 12 12 12 12          
123 123 123 123 123          
1234 1234 1,234 1234 1,234 1 1     1
12345 12345 12,345 12345 12,345 12 12     12
123456 123456 123,456 123456 123,456 123 123     123
1234567 1234567 1,234,567 1234567 1,234,567 1235 1,235 1 1 1,235
12345678 12345678 12,345,678 12345678 12,345,678 12346 12,346 12 12 12,346
123456789 123456789 123,456,789 123456789 123,456,789 123457 123,457 123 123 123,457
1234567890 1234567890 1,234,567,890 1234567890 1,234,567,890 1234568 1,234,568 1235 1,235 1,234,568
위로