Y&S 두 여자의 말랑말랑 생활공감

엑셀함수강좌 26강

동적인 셀 참조

ROW, COLUMN

ROW, COLUMN 함수에 대해 설명한 강의입니다.

▶ ROW(셀주소)

참조셀의 행번호를 반환한다.

▶ COLUMN(셀주소)

참조셀의 열번호를 반환한다.

아래의 연습파일을 다운로드해서 연습하세요.

indirect,row,column함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 25강

동적인 셀 참조

INDIRECT

INDIRECT 함수에 대해 설명한 강의입니다.

▶ NDIRECT("셀주소", [옵션])

셀 주소는 텍스트 문자열 형태로 A1 스타일 또는 R1C1 스타일의 셀주소를 의미한다.

"A3" - A3를 셀 주소로 사용한다.

A3 - A3에 입력된 값을 셀 주소로 사용한다.

옵션

생략 or TRUE -  A1 스타일을 사용한다는 의미

FALSE - R1C1 스타일을 사용한다는 의미

아래의 연습파일을 다운로드해서 연습하세요.

indirect,row,column함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 24강

문자 함수

FIND, SEARCH, REPLACE, SUBSTITUTE

FIND, SEARCH, REPLACE, SUBSTITUTE 함수에 대해 설명한 강의입니다.

▶ FIND(찾을텍스트, 문자열, [시작위치])

찾으려는 문자가 시작되는 위치

단, 대소문자 구별함, 와일드카드 문자인 물음표와 별표 사용 불가

▶ SEARCH(찾을텍스트, 문자열, [시작위치])

찾으려는 문자가 처음 발견되는 문자개수

단, 대소문자 구별 안함, 와일드카드 문자인 물음표와 별표 사용 가능

▶ REPLACE(문자열, 시작위치, 교체문자개수, 새로운문자)

문자의 일부를 다른 문자로 교체함

▶ SUBTITUTE(대상셀, 교체될문자, 새로운문자, K)

대상셀의 문자 중에 교체될 문자를 새로운 문자로 교체함

교체될 문자가 여러개일 경우 k번째 문자를 교체한다.

대소문자를 구별하므로 대소문자를 정확히 입력해야 한다.

아래의 연습파일을 다운로드해서 연습하세요.

문자열함수(FIND,REPLACE,SEARCH,SUBSTITUTE).xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 23강

수학/삼각 함수

SUBTOTAL

SUBTOTAL 함수에 대해 설명한 강의입니다.

▶ SUBTOTAL( Function_num, 참조1, 참조2...)

목록이나 데이터베이스의 부분합을 구함

=SUBTOTAL(1, A1:A10)

A1:A10 범위의 숫자의 평균값을 구한다.

숨겨진 셀값까지 모두 계산한다.

단, 필터를 이용해 추출된 데이터는 표시된 데이터만 계산한다.

=SUBTOTAL(101, A1:A10)

A1:A10 범위의 숫자의 평균값을 구한다.

수동으로 숨겨진 셀값은 제외하고 계산한다.

아래의 연습파일을 다운로드해서 연습하세요.

subtotal 함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 22강

논리 함수

IFERROR

IFERROR 함수에 대해 설명한 강의입니다.

▶ FEFFOR(값1, 값2)

값1이 에러일 경우 값2를 반환하고, 에러가 아닐경우 값1을 반환함

아래의 연습파일을 다운로드해서 연습하세요.

iferror.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 21강

정보 함수

ISBLANK, ISERROR

ISBLANK, ISERROR 함수에 대해 설명한 강의입니다.

▶ ISBLANK(셀)

셀이 공백셀이면 TRUE, 아니면 FALSE

=IF(ISBLANK(F5),"증정안함","증정")  => F5셀이 빈셀이면 '증정안함', 아니면 '증정' 표시

▶ ISERROR(검사대상)

검사대상이 에러면 TRUE, 아니면 FALSE

=ISERROR(4/0)  =>  TRUE   (숫자를 0으로 나누면 '#DIV/0!' 에러가 난다. 에러이므로 TRUE)

아래의 연습파일을 다운로드해서 연습하세요.

정보함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 20강

찾기/참조 함수

OFFSET

OFFSET 함수에 대해 설명한 강의입니다.

▶ OFFSET(기준, 행수, 열수, [높이], [폭])

기준으로부터 행 또는 열 수만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시

행/열 - 행은 아래로, 열은 오른쪽으로 지정된 숫자(행/열)만큼 떨어진 위치의 참조 영역을 돌려주게 된다.

높이/너비 - 참조 영역의 높이(행 수)와 너비(열 수)를 지정할 수 있다.

아래의 연습파일을 다운로드해서 연습하세요.

offset 함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 19강

찾기/참조 함수

INDEX, MATCH

INDEX, MATCH 함수에 대해 설명한 강의입니다.

▶ INDEX(범위, 행번호, [열번호]) 

범위나 배열에서 행번호, 열번호에 해당하는 값을 구한다.

▶ MATCH(검사값, 검사범위, [검사옵션])

검사값을 검사범위에서 검색하여 그 위치를 구하는 함수

검사옵션

1 : 검사값보다 작거나 같은 값 중에서 최대값을 찾음

0 : 검사값과 같은 첫째 값을 찾음

-1 : 검사값보다 크거나 같은 값 중에서 최소값을 찾음

아래의 연습파일을 다운로드해서 연습하세요.

index,match 함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 18강

찾기/참조 함수

CHOOSE

CHOOSE 함수에 대해 설명한 강의입니다.

▶ CHOOSE(인덱스번호, 값1, 값2, 값3, 값4......)

값들 중에서 인덱스 번호에 해당하는 값을 선택하는 함수

인덱스 번호란? 

1부터 시작하는 작은 정수를 의미한다. 음수나 0은 포함되지 않는다.

인덱스 번호와 '값' 다음에 나오는 숫자가 일치하는 값이 표시된다. 

즉, 인덱스 번호가 2라면 '값2'가 표시되고, 3이라면 '값3'이 표시된다.

▶ CHOOSE 함수를 사용할 경우 유의할 점

1. IF 함수와 혼동하여 인덱스 번호가 들어갈 자리에 조건을 쓰면 안된다.

예) 응시번호(B4셀)가 '1'이면 'A'를 표시하고 '2'이면 'B'를 표시하시오.

틀린 경우

=CHOOSE(B4=1,"A",B4=2,"B")

=CHOOSE(B4,1,"A",2,"B")

맞는 경우

=CHOOSE(B4, "A", "B")

2. 인덱스 번호가 6까지 나올 가능성이 있다면 값도 6개를 입력해줘야 한다. 

예를 들어, 인덱스 번호가 4인데, 값3까지만 입력하면 '#VALUE!' 에러 표시가 나타난다.

틀린 경우

=CHOOSE(3, "A", "B")

맞는 경우

=CHOOSE(3, "A", "B", "C")

아래의 연습파일을 다운로드해서 연습하세요.

choose함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 17강

찾기/참조 함수

VLOOKUP, HLOOKUP

VLOOKUP, HLOOKUP 함수에 대해 설명한 강의입니다.

▶ HLOOKUP(검색값, 참조범위, 행번호, [옵션])

옵션

TRUE 또는 생략 : 검색값과 유사한 값을 찾음

FALSE : 검색값과 정확히 일치하는 값을 찾음

▶ VLOOKUP(검색값, 참조범위, 열번호, [옵션])

옵션 - HLOOKUP과 같음

※ 검색값이 찾는 값보다 왼쪽 열에 있어야함

아래의 연습파일을 다운로드해서 연습하세요.

찾기참조함수(2).xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 16강

찾기/참조 함수

LOOKUP

LOOKUP 함수에 대해 설명한 강의입니다.

▶ LOOKUP함수

LOOKUP(검사값, 검사범위, 대응범위)

검사범위에서 검사값을 찾아 동일한 행에 위치한 대응범위의 값을 결과로 표시

※ 검사범위가 반드시 오름차순이어야 함

아래의 연습파일을 다운로드해서 연습하세요.

찾기참조함수(1).xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 15강

데이터베이스 함수

데이터베이스 함수에 대해 설명한 강의입니다.

▶ 데이터베이스 함수(데이터베이스, 계산필드, 조건범위)

    데이터베이스에서 조건에 맞는 셀을 계산한다.

▶ 조건범위 작성법

1. 필드 제목을 무조건 옆으로 나란히 작성한다. (지점코드, 단가)

2. 조건을 모두 만족해야 하는 경우 조건을 옆으로 나란히 작성한다.(JL*, <800000)

3. 조건 중에 하나만 만족해도 되는 경우 조건을 대각선으로 작성한다.(대륙전자, >=80)

4. 같은 필드에서 모두 만족해야 하는 두 개 이상의 조건일 경우 필드 이름을 여러번 쓰고 조건을 나란히 작성한다.(무게(Kg), 무게(Kg)), (>=80, <=90)

5. 같은 필드에서 '이거나'라는 말이 나오면 위아래로 작성하면 된다. (DR*, JL*)

4. 조건에 식을 사용하여 'TRUE', 'FALSE' 등이 표시될 경우 필드 제목을 비워놓거나 존재하지 않는 내용을 써야한다.

① 조건 필드 제목을 비워놓은 경우 

14 앞뒤에 큰 따옴표("")를 앞뒤에 넣은 이유는

left, right, mid 등의 문자열 함수로 추출한 숫자는 문자형 숫자이기 때문이다.

② 조건 필드 제목에 기존의 필드 제목과 상관없는 내용을 적은 경우(8월 판매, 평균 이상)

평균 범위를 절대참조로 지정하지 않으면 제대로 추출되지 않는다.

▶ 데이터베이스 함수 종류

DSUM - 조건에 맞는 셀의 합계를 구한다.

DAVERAGE - 조건에 맞는 셀의 평균을 구한다.

DCOUNT - 조건에 맞는 셀의 개수를 구한다.(숫자가 있는 열)

DCOUNTA - 조건에 맞는 셀의 개수를 구한다.(문자, 숫자가 있는 열)

DMAX - 조건에 맞는 셀의 최대값을 구한다.

DMIN - 조건에 맞는 셀의 최소값을 구한다.

DSTDEV - 조건에 맞는 셀의 표준편차를 구한다.

DVAR - 조건에 맞는 셀의 분산을 구한다.

DGET - 조건에 맞는 셀값을 구한다.

DPRODUCT - 조건에 맞는 셀의 곱을 구한다.

아래의 연습파일을 다운로드해서 연습하세요.

데이터베이스함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 14강

IF 함수의 활용

IF 함수의 활용에 대해 설명한 강의입니다.

▶ IF(조건식, [값1], [값2])

조건식이 참이면 값1, 아니면 값2를 표시

1. 평균이 90이상이면 '합격', 아니면 '불합격'

=IF(J5>=90,"합격","불합격")

2. 평균이 90이상이면 '최우수', 80이상이면 '우수', 70이상이면 '보통', 아니면 '미달'

=IF(J5>=90,"최우수",IF(J5>=80,"우수",IF(J5>=70,"보통","미달")))

3. 모든 점수가 '80'이상이면 '적성우수직원', 아니면 공백

=IF(AND(F5>=80,G5>=80,H5>=80,I5>=80),"적성우수직원","")

4. 점수 중에 하나라도 70 이하면 '적성미달직원', 아니면 공백

=IF(OR(F5<=70,G5<=70,H5<=70,I5<=70),"적성미달직원","")

아래의 연습파일을 다운로드해서 연습하세요.

 IF문.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 13강

논리함수 2 - IF, AND, OR

IF, AND, OR 함수에 대해 설명한 강의입니다.

▶ IF(조건식, [값1], [값2])

조건식이 참이면 값1, 아니면 값2를 표시

기본함수 : =IF(A1>=70,"합격","불합격")

중첩IF : =IF(A1>=90,"A",IF(A1>=80,"B","C"))

AND, IF : =IF(AND(A1>=90,B1>=90),"우수","")

OR, IF : =IF(OR(A1="해당",B1>=60),"승진","")

▶ AND(논리식1, 논리식2…)

논리식이 모두 참이면 TRUE, 아니면 FALSE

▶ OR(논리식1, 논리식2…)

논리식 중에서 하나라도 참이면 TRUE, 모두 거짓이면 FALSE

아래의 연습파일을 다운로드해서 연습하세요.

 IF문.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 12강

논리함수 1 - AND, OR

AND, OR 함수에 대해 설명한 강의입니다.

▶ AND(논리식1, 논리식2…)

논리식이 모두 참이면 TRUE, 아니면 FALSE

▶ OR(논리식1, 논리식2…)

논리식 중에서 하나라도 참이면 TRUE, 모두 거짓이면 FALSE

아래의 연습파일을 다운로드해서 연습하세요.

IF문.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 11강

COUNTIF함수 / SUMIF함수

COUNTIF/SUMIF 함수에 대해 설명한 강의입니다.

▶ COUNTIF(조건 범위, 조건)

조건에 맞는 셀의 개수

▶ SUMIF(조건범위, 조건, [합계범위])

조건범위에서 조건에 맞는 합계범위의 값을 합한다.

아래의 연습파일을 다운로드해서 연습하세요.

COUNTIF_SUMIF.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 10강

수학/삼각 함수 2

ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC

ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC 함수에 대해 설명한 강의입니다.

▶ ROUND(수, 자리수), ROUNDUP(수, 자리수), ROUNDDOWN(수, 자리수), TRUNC(수, [자릿수])

자릿수가 양수/음수/0일 경우

자릿수가 양수일 경우 - 숫자를 반올림/올림/내림/버림해서 소수 이하 자릿수만큼 표시한다.

자릿수가 음수일 경우 - 숫자를 반올림/올림/내림/버림해서 0의 개수를 자릿수만큼 표시한다.

자릿수가 0일 경우 - 숫자를 반올림/올림/내림/버림해서 정수만큼 표시한다.

▶ INT(수)

수에서 소수 부분을 버리고 정수로 표시한다. 단, 인수를 넘지 않는 가장 가까운 정수를 구한다.

예) =INT(4.5) => 4, 4.5에서 가까운 정수는 4와 5인데, 4와 5 중에서 작은 정수인 4가 답으로 구해진다.

=INT(-4.5) => -5, -5에서 가까운 정수는 -5와 -4인데, -5와 -4 중에서 작은 정수인 -5가 답으로 구해진다.

즉, 수의 앞과 뒤에 해당하는 정수 중에서 더 작은 정수가 답으로 구해진다.

▶ TRUNC(수, [자릿수])

자릿수를 생략할 경우 수에서 소수 부분을 버리고 정수를 구한다.

예) =TRUNC(4.5) => 4, =TRUNC(-4.5) => -4

TRUNC 함수는 수에서 소수 부분을 버리면 곧 답이 된다.

아래의 연습파일을 다운로드해서 연습하세요.

 수학삼각함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 9강

수학/삼각 함수 1

ABS, MOD, PRODUCT, SUMPRODUCT

ABS, MOD, PRODUCT, SUMPRODUCT 함수에 대해 설명한 강의입니다.

▶ ABS(수)

수의 절대값(양수)을 구한다

▶ MOD(수, 나누는수)

수를 나누는수로 나누어서 나머지를 구한다

▶ PRODUCT(수1, 수2…), PRODUCT(범위)

수를 모두 곱한다

▶ SUMPRODUCT(배열1, 배열2...)

배열의 해당 요소를 모두 곱하여 합한다

아래의 연습파일을 다운로드해서 연습하세요.

수학삼각함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 8강

날짜/시간 함수 2 - DAYS360, DATEDIF

DAYS360, DATEDIF 함수에 대해 설명한 강의입니다.

▶ DAYS360(시작날짜, 종료날짜, [옵션]) 

옵션

TRUE : 유럽식

FALSE 또는 생략 : 미국식 (보통 생략)

▶ DATEDIF(시작날짜, 종료날짜,"옵션")

옵션

Y : 시작일과 종료일 사이의 경과년수

M : 시작일과 종료일 사이의 경과월수

D : 시작일과 종료일 사이의 경과일수

MD : 시작일과 종료일 사이에서 1개월 미만의 일수

YM : 시작일과 종료일 사이에서 1년 미만의 월수

YD : 시작일과 종료일 사이에서 1년 미만의 일수

아래의 연습파일을 다운로드해서 연습하세요.

 날짜시간함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 7강

날짜/시간 함수 1

(TODAY, NOW, YEAR, MONTH, DAY, HOUR, 

MINUTE, SECOND, DATE, TIME, WEEKDAY)

TODAY, NOW, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DATE, TIME, WEEKDAY 함수에 대해 설명한 강의입니다.

▶ TODAY() - 현재 시스템 날짜

▶ NOW() - 현재 시스템 날짜와 시간

▶ YEAR(날짜) - 날짜 중에서 연도만 추출

▶ MONTH(날짜) - 날짜 중에서 월만 추출

▶ DAY(날짜) - 날짜 중에서 일만 추출

▶ HOUR(시간) - 시간 중에서 시만 추출

▶ MINUTE(시간) - 시간 중에서 분만 추출

▶ SECOND(시간) - 시간 중에서 초만 추출

▶ DATE(년, 월, 일) - 년, 월, 일 인수를 묶어 날짜 데이터를 만들어 줌

▶ TIME(시, 분, 초) - 시, 분, 초 인수를 묶어 시간 데이터를 만들어 줌

▶ WEEKDAY(날짜, [옵션]) - 날짜의 요일을 구해줌

옵션

1 또는 생략 : 일요일을 1로 시작

2 : 월요일을 1로 시작

3 : 월요일을 0으로 시작

아래의 연습파일을 다운로드해서 연습하세요.

날짜시간함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 6강

문자열 함수2 - LOWER, UPPER, PROPER, LEN, TEXT, VALUE

LOWER, UPPER, PROPER, LEN, TEXT, VALUE 함수에 대해 설명한 강의입니다.

▶ LOWER(문자열)

소문자로 변환

▶ UPPER(문자열)

대문자로 변환

▶ PROPER(문자열)

단어의 첫 글자만 대문자로 변환

▶ VALUE(텍스트형 숫자)

텍스트형 숫자를 숫자형 숫자로 변환

▶ TEXT(값, 표시형식)

값에 형식을 적용한 후 텍스트로 변환

예) TEXT(B3,"#,###원")

▶ LEN(문자열)

문자열의 글자수(길이)를 구함

아래의 연습파일을 다운로드해서 연습하세요.

 문자열함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 5강

문자열 함수1 - &, LEFT, RIGHT, MID

&, LEFT, RIGHT, MID 함수에 대해 설명한 강의입니다.

▶ & 연산자

1. 앞과 뒤의 문자열을 연결해주는 연산자

2. ="가나다"&"79"  => 가나다79

3. ="20"&B3

4. =B2&"년"&B4&"월"&B5&"일"

▶ LEFT

LEFT(문자열, 추출할 문자수)

문자열의 추출할 문자수만큼 왼쪽에서 추출

▶ RIGHT

RIGHT(문자열, 추출할 문자수)

문자열의 추출할 문자수만큼 오른쪽에서 추출

▶ MID

MID(문자열, 시작 번호, 추출할 문자수)

문자열의 시작번호에서부터 문자수만큼 중간에서 추출

아래의 연습파일을 다운로드해서 연습하세요.

문자열함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 4강

통계 함수 3 - RANK

RANK 함수에 대해 설명한 강의입니다.

▶ RANK 함수

1. RANK(순위를 구하려는 수, 범위, [옵션]

범위 - 절대참조로 변경해야 함. 예)$A$5:$C$10

옵션

0 또는 생략 : 내림차순(큰 수가 1위)

1 : 오름차순(작은 수가 1위)

아래의 연습파일을 다운로드해서 연습하세요.

 통계함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 3강

통계 함수 2

COUNT, COUNTBLANK, COUNTA, LARGE, SMALL 함수에 대해 설명한 강의입니다.

▶ 통계 함수 2

1. COUNT - 숫자셀의 개수

2. COUNTBLANK - 빈셀의 개수

3. COUNTA - 숫자셀, 문자셀의 개수

4. LARGE(범위, k) - 범위에서 k번째로 큰 수

5. SMALL(범위, k) - 범위에서 k번째로 작은 수

아래의 연습파일을 다운로드해서 연습하세요.

통계함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 2강

통계 함수 1

통계 함수 중에서 사용법이 쉬운 함수에 대해 설명한 강의입니다.

▶ 통계 함수 1 

1. SUM - 합계

2. AVERAGE - 평균

3. MAX - 최대값

4. MIN - 최소값

5. MEDIAN - 중간값

6. VAR - 분산

7. STDEV - 표준편차

8. MODE - 최빈값

9. COUNT - 숫자셀의 개수

10. COUNTBLANK - 빈셀의 개수

아래의 연습파일을 다운로드해서 연습하세요.

통계함수.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.

엑셀함수강좌 1강

함수 입력 방법

함수 입력 방법에 대해 설명한 강의입니다.

▶ 함수 입력 방법

1. 직접 입력하기

2. 함수 마법사 - [수식입력줄] 왼쪽 [함수 삽입] 아이콘, [수식]-[함수 삽입]

▶ 함수 구조

1. =함수명(인수) 

예) =SUM(A1:B5)

아래의 연습파일을 다운로드해서 연습하세요.

기본함수연습.xlsx

아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.