[컴활2급필기 절대족보] 30 찾기/참조 함수와 데이터베이스 함수

이기적 영진닷컴
21 팔로워
1 비디오 조회수·2024. 10. 24.
이 동영상은 [이기적 컴퓨터활용능력 2급 필기 절대족보] 도서 내용을 바탕으로 제작되었습니다.
🔹 도서 자세히 보기 : https://www.yes24.com/Product/Goods/125004279
🔹 이 동영상이 포함된 재생목록 : https://youtube.com/playlist?list=PL6i7rGeEmTvrbeNHSCcF0djpltSVE6bby&si=_TZjEh5si8sndRC-
🔹 모르는 문제? [이기적 스터디 카페] 질문하기 : https://cafe.naver.com/yjbooks
🔹 이기적 홈페이지 : https://license.youngjin.com
🔹 인스타그램 : https://www.instagram.com/license.youngjin_official/
Vlookup 같은 경우는 이 V가 Vertical의 약자에요. Vertical은 무슨 뜻이에요?
열이라는 뜻이거든요. 그래서 얘는 범위의 중요한 것은 바로 얘에요.
첫번째 열에서 찾을 값을 검색합니다.
Vlookup은 첫번째 열 이걸 명심하셔야 돼요.
범위에서 첫번째 열에서 찾을 값을 찾는다. 이거 정말 중요한 거에요. Vlookup은.
그래서 지정한 열 번호와 같은 행에 있는 데이터를 가져온다는 거.
그래서 Vlookup, V는 Vertical 열을 뜻한다는 거.
그래서 이 찾을 값을 범위에서 특히 첫번째 열에서 찾는다는 거.
찾아가지고 가져오는 거는 열 번호를 갖다가 이렇게 줘야 돼요.
그리고 옵션, 옵션 같은 경우는 0을 주게 되면 정확하게 일치하는 값을 갖고 온다는 거에요.
0이라는 거는 펠스하고 같은 거고 0을 줘도 되고 펠스라고 입력을 해도 되고 정확하게 일치.
그 다음에 1을 주게 되면 True에요. TRUE. 얘는 아래로 근사치.
정확하게 일치하는 값을 가져와야 된다. 그때는 0을 주면 되는 거에요.
그래서 찾을 값, 그 다음에 범위, 범위. 첫 열에서 찾는다라는 거 정말 중요해요.
그 다음에 열 번호를 준다라는 거. 직접 보도록 하겠습니다.
지금 지역명 대전 요금이 15,000원이에요.
근데 여기 보면 지금 지역명이 서울, 천안, 대전, 양산, 부산.
지금 입력되어 있는 형태가 얘가 지금 열 개념이잖아요. 이렇게 열 개념이잖아요.
이렇게 열 개념으로 데이터가 입력되어 있을 때 찾고자 한다. 그럼 Vlookup에요.
Vertical. 여기서 첫 번째 열었을 찾습니다.
첫 번째, 이 범위에서 첫 번째 열에서 찾는 거에요. 이게 정말 중요한 거에요.
이 열에서 찾습니다. 이게 Vlookup에요. 그러면 대전 그러면 15,000원이 오는 거고
그 다음에 여기다가 서울 그러면 5,000원. 이렇게 바뀌는 예제에요.
Vlookup. Vvertical. 열에서 찾아라. 여기서 첫 번째 열에서 찾아라.
Vlookup. V의 원을, V의 원은 뭐에요? 서울을. 그럼 서울을 여기서 찾는 거에요.
D의 원에서 E의 욕, D의 원에서 E의 욕. 이 범위. 색깔 보시면 돼요.
그럼 V의 원에 서울이 입력되어 있잖아요. 그러면 지역명 서울 아니죠. 서울 찾았네요. 서울 찾았어요.
그리고 어떻게 하라고요? 그 다음에 뒤에다가 E. E는 뭘 뜻하는 거냐면
지역명 여기가 1열이 되는 거고 요금이 2열이 되는 거에요. 바로 이겁니다.
저기서 얘기하는 E는 이 2열을 가리키는 거에요. 그러니까 서울을 찾았다. 찾았어요.
그러면 같은 행에 있는 2열의 값을 가져와라. 5,000원을 가져와라. 그 뜻이에요.
그 다음에 0은 반드시 서울하고 똑같은, 일치하는 경우. 엔터.
그러면 서울을 찾아서, 서울을 찾았잖아요.
같은 행에 있는 2열의 값을 가져와라라는 거.
그 다음에 여기다가 이제 대전이라고 입력하면 대전을 찾아서 15,000원을 가져온다라는 거죠.
이게 바로 Vlookup이에요. Vlookup.
마찬가지로 여기도 추루, 유사일치. 방금 전에 말씀드린 대로 1.
펠스, 정확히 일치. 펠스는 0을 뜻하는 거에요.
됐네요. 그 다음에 또 갈까요? Vlookup.
이번에는 뭐냐면 평균 평가에요.
그래 놓고 이 평균이 0, 60, 70, 80, 90 올라가는 순서대로 정렬이 되어 있고
그 다음에 90 이상일 때는 A, 학점을 얘기하는 거죠.
평가, 80 이상이면 B, 70, C, 60 이상이면 D, 그렇지 않으면 전부 다 F에요.
어떻게 주냐면 마찬가지에요.
얘도 마찬가지로 첫 번째 열에서 점수를 찾는 거에요.
그래 놓고 이 둘을 여기서 찾습니다.
그 다음에 절대 참조를 준 이유는 채우기 핸들을 드래그해서 수식을 복사할 거기 때문에 절대 참조 준 것 뿐이고
그 다음에 2열에 있는 값을 가져와요.
그래 놓고 마지막에 뭐에요? 1 줬잖아요. 1, 추루.
아래로 근사치 값을 가져오는 경우 이런 예제에서 정확히 일치하는 값이 아닌
이렇게 숫자 같은 경우 아래로 유사한 값.
예를 들어 평균이 50이잖아요.
50이면 지금 정확히 일치하는 값을 갖는 게 아니고 근사치.
그러니까 50은 평균 60이 아니잖아요.
그러니까 이 밑에 있는 거, 아래로 근사치.
0에 해당되는 F를 갖고 와라.
이게 바로 D에 1을 준, 옵션을 1을 준 이유에요.
이렇게 숫자적인 개념에서 아래로 근사치를 갖고 와야 된다.
이번에는 H루크업인데요.
여기서 얘기하는 H는 horizontal 행을 뜻하는 거에요.
그러니까 얘는 행에서 검색하라 이런 뜻입니다.
범위의 첫 번째 행에서 바로 이거에요.
범위의 첫 번째 행에서 찾을 값을 검색하라는 거고
지정한 행번호에 같은 열에 있는 데이터를 가져와라.
그래서 H루크업, 찾을 값, 범위.
그 다음에 얘는 행번호가 나와요.
행이기 때문에 행번호, 그 다음에 마찬가지로 옵션.
0이면 정확하게 일치하는 값, 1이면 유사일치, 생략 가능하고
찾을 값, 범위, 첫 행에서 찾는다는 거 조심하셔야 되고.
직접 보겠습니다.
지금 H루크업은요.
지역명 요금이 이렇게 행개념으로 생겼잖아요.
바로 이겁니다.
이렇게 행개념으로 생긴 경우는 H루크업이다라는 거고
그 다음에 이미 우리가 배웠지만
이렇게 열개념으로 되어있는 거는 V루크업이에요.
이걸 잘 보셔야 됩니다.
그래서 시험문제에서도 이렇게 생긴 거는 V루크업이고
그 다음에 이렇게 생긴 거는 H루크업이에요.
열개념, 행개념을 정확하게 아셔야 돼요.
H루크업, 비해원을, 부산을 어디서 찾아라?
A4에서 F5, A4에서 F5 이 범위에서 찾는 거예요.
대신 첫 번째 행에서 찾습니다.
H루크업은 V루크업과 이 차이점이에요.
마찬가지로, 그리고 이 E는 뭘 뜻하는 거예요?
여기서는 행번호예요.
그러니까 지금 지역, 지역명 여기가 일행이 되는 거고
요금이 이 행이 되는 거예요.
여기서는 이제 이 행입니다.
요 E가 바로 행을 뜻하는 거고
마찬가지로 0은 뭘 뜻한다?
정확히 일치하는 값을 찾아라 라는 뜻이죠.
그러면 엔터를 치게 되면
부산을 찾아서, 부산을 찾아요.
부산을 찾았잖아요.
그리고 같은 열에 있는 이 행의 값, 3만원을 가져온다.
그거예요.
여기도 마찬가지로 대전
그러면 대전을 찾은 다음에
15,000원을 가져오는 경우고
또 갈까요?
지금 얘 같은 경우도 보세요.
평가가 어떻게 생겼어요?
행 개념으로 생겼잖아요.
그러니까 H루크업이에요.
V루크업과 쓰는 방법은 똑같은 거고
H루크업, E에 투 평균 점수
그 다음에 평가
달러 A, 달러 B, 달러 F, 달러 C
채우기, 핸들 드래그 할 거기 때문에
이 범위를 절대 참조로 줘서
변하지 않게끔 한 거고
E는 뭐예요?
E행 마찬가지로
그래 놓고
D에 뭘 생각했어요?
옵션을 생각했잖아요.
TRUE
TRUE라고 줘도 되고
E를 줘도 되고
생각해도 되고
여기서는 생략을 했다라는 거죠.
이게 바로 H루크업이에요.
CHOS
추수함수
사전적인 의미가
고르다, 선택하다
라는 뜻이 있거든요.
그래서 얘는 뭐냐면
추수
찾을 인수의 위치 번호
이 번호가 1이면
인수 1로 가고요
2면 인수 2로 가고
간단해요.
그래서 얘가 1이면
얘를 선택하라는 거고
2면 인수 2를 선택하라
이런 식으로
추수
굉장히 간단한 함수에요.
찾을 인수의 위치 번호에 따라
값이 선택된다
그 값은 인수 1, 인수 2
요게 되는 거고
말씀드린 대로
인수 위치가 1이면
인수 1이고
2가 되면
인수 2를 찾는다
라는 거
그 다음에
찾을 인수의 위치 번호가
1보다 작거나
목록의 최대 값보다 크게 되면
밸류에다가 떨어져요.
그러니까 숫자를 잘못 쓴 거죠.
샵 밸류 느낌표가 떨어지는 거고
그 다음에
찾는 인수 번호를 갖다가
정수로 줘야 되는데
분수로 줬어요.
소유첨으로 줬어요.
그럼 소유첨 위아래를
자기가 잘라가지고
정수로 변환해 줍니다.
얘도 직접
지금 추조함수
여기 보면요.
우리가 주민등록번호
여덟 번째 짜리가
하나, 둘, 셋, 넷, 다섯, 여섯
대시까지 일곱
그 다음에 여덟 번째 짜리
여기가
1 남자
2 여자
2000년도서부터 태어난 경우는요.
3이 남자고
4가 여자예요.
주민등록번호 여덟 번째 짜리
규칙이라는 건 뭐예요?
1이면 남자
2면 여자
3이면 남자
4면 여자잖아요.
바로 그겁니다.
그 예를 추조함수를 써서
사용하게 되면
어마어마한 좋은 수식이 돼요.
추수는 추수
그 다음에 주민등록번호
여덟 번째 짜리에서
하나를 가져오려면
미드함수를 써야 돼요.
부디하게 추수하고 미드를 섞어야 됩니다.
미드 비에투
이 번호가
1이면 남자
2면 여자
3이면 남자
4면 여자
요렇게 가져오는
수식이 바로
추수하고 미드를 섞어버린 거예요.
이 추수하고 미드를 섞어서
성별을 구하는 이 수식은요
이 수식은 시험을 떠나서라도
우리가 실생활에서
실무에서 요긴하게 사용될 수 있는 기능이에요.
수식입니다.
이걸 2프문으로 하려고 하면
굉장히 길어지거든요. 복잡하고
하지만 추수 미드함수를 섞어버리면
요렇게 간단한
수식이 돼요.
이게 바로 추수함수다라는 거고
그래서
채우기 핸들을 갖다가 드래그하게 되면
드래그하게 되면
1이니까 남자
2 여자
1 1 남자 남자
4니까 여자
3 남자
구해진다라는 거죠.
그리고 또 하나
지금 여기 3.3이잖아요
추수
J1이 3.3이에요.
그러면요
자기가 알아서
3으로 바꿔서
1 2 3
3을 가져온다라는 거죠.
요렇게
그 다음에 또 하나
지금
추수
3을 줬는데
하나
둘
3개를 줘야 되는데
2개만 줬어요.
밸류에다가
떨어진다라는 거죠.
인덱스함수
인덱스함수는
범위에서
이 범위에서
행과 열에 해당되는
값을 가져오는 함수에요.
간단합니다.
그래서 범위에서
지정한 행번호
열번호가
교차하는 곳에
값을 가져온다라는 거고
범위에서 행이나 열이
하나만 있을 때
행번호나
열번호 인수는
생략할 수 있다라는 거고
인덱스함수도
직접 보도록 하겠습니다.
지금
인덱스
A1에서 C에 2
여기 색깔나는 부분은
보시면 되는 거죠.
여기가 범위가 되는 거고
뒤에 이 콤마 2가
뭘 뜻한다?
이행
이열을 뜻하는 거에요.
이행
이열
그럼 여기가
일행
그죠?
여기가
이행이 되는 거고
그 다음에
일열
이열이 되는 거잖아요.
그죠?
그러면은
이행
이열에 해당되는 값은
뭐에요?
망고잖아요.
이 망고가 온다
그 얘기에요.
엔털 치게 되면
망고가 온다
라는 거고
또 바꿀까요?
이번엔
일행
일열 가겠습니다.
일행 일열
그럼 뭐가 와요?
사과가 오는 거죠.
일행에서
일열이니까
사과
이게 바로 인덱스함수구요.
또 시험제에서
나온게 뭐냐면
얘가 나왔어요.
지금 여기 같은 경우
보면
인덱스
F8에서
G에 10
색깔나는 부분
보시면 되요.
F8에서 G에 10
첫번째 있는 영역이고
그 다음에
콤마
F12에서
G에 13
요 바로 요 부분이잖아요.
색깔나는 부분
요렇게
두개 영역을 줍니다.
영역을 주고 난 다음에
바로 닫고
E콤마
E
이러면요.
앞에 있는 E콤마 E는
이행
이열을 뜻해요.
이행
이열
행열
그 다음에
맨 마지막에 있는
이 E는
영역이에요.
얘가
첫번째 영역이고
얘가
두번째 영역이 되요.
그러니까
여기 E는
요 두번째 영역에서
이행
이열
그러니까
일행
이행
그죠?
그리고
이열이니까
일열
이열
그럼 뭐에요?
만원이 온다
라는거죠.
바로 그거에요.
엔털 치게 되면
요렇게
만원이 온다
그래서
영역형
인덱스
요렇게 두개를
줘버리고 나서
몇행
몇열
그 다음에
마지막에는
영역을 뜻한다라는거
그럼 여기로
영역을
일로
바꾸게 되면
여기서
이행 이열
일렬
이열
일렬
이열
요 값을 가져온다라는거죠.
바로 요렇게요.
네
요번에는 데이터베이스 함수인데요.
우리가 데이터베이스의 D를 따서
D함수라고도 불러요.
그래서 맨 앞에 D가 다 붙습니다.
그리고 이미 우리가 알고 있는
맥스
뭐하는 함수에요?
최대값이잖아요.
그래서
범위에서
조건에 맞는
이열 위치에 해당되는
결론적으로 필드죠.
그 필드의 맥스
최대값을 구하는거에요.
범위에서
조건에 맞는
필드값의
최대값을
지정한 열위치에서
구한다.
D맥스
최대값
요렇게 알아두시면 되는거고
그 다음에
D민
그러면 최소값
이미 우리가 배운
함수 그대로 생각하시면 되는거에요.
그 다음에 D썸
뭐에요?
합계를 구하는거죠.
그 다음에
D카운트
숫자 포함
셀 개수를 갖다가
카운트한다라는거고
그 다음에
D카운트
A올
비어있지 않는 셀의 개수를 갖다가
구한다라는거
D에버리지
뭐에요?
평균
산술평균
그 다음에
DVAR
분산
분산값을 구한다라는거
DSTDV
표준편차
표준편차를 구한다.
D갯
갯이라는건
가져온다라는 뜻이에요.
그 값을
그 다음에
D프로덕트
프로덕트 곱하는거잖아요.
필드값을 곱한다라는거
그래서
D함수도
직접 한번
살펴보도록 하겠습니다.
성명 부서명
직책
연금이
입력이 되어있구요.
아래쪽에는
바로 이것들이
조건이에요.
직책이 과장
연금이 16만원 이상
성명은 김선이고
직책은 대리
요런식으로
가겠습니다.
지금 과장의
연금의 합을
구하고자
한다라는거죠.
과장의
연금의 합
그러면
과장이라는거는
직책과장
이게 조건이잖아요.
A9에서 A10이
조건이 해당되는거고
연금은 여기잖아요.
D열
연금
또는 D의 원
또는
1열
2열
3열
4열
보겠습니다.
합이니까
뭐에요
D섬
D섬이고
데이터베이스 범위는
A1에서부터
D의 7이에요.
A1에서
D의 7
그 다음에
D의 원
D의 원은 뭘 뜻하는거에요.
연금
필드
합을 구할 필드잖아요.
그래 놓고
조건은
A9에서 A10
직책이 과장
직책이
과장인
연금의
합
D섬
이렇게 구하는거에요.
데이터베이스 범위는
A1에서
D의 7이 되는거고
엔터를 치게 되면
과장의 연금의
합이 구해지는거고
그 다음에
이번에는
과장연금의
평균이에요.
그럼 D의 원이지
D의 원이지
평균을 구하는거니까
마찬가지로
데이터베이스 범위를 주고요.
그 다음에
조건은
A9에서 A10
과장이니까
직책과장
조건범위가
여기가 되고
그 다음에
아까는 D의 원이었는데
지금 4로 표현을 했어요.
이 4는
4열을 뜻합니다.
성명이 1열
부세명 2열
직책 3열
연금 4열
이렇게 해줘도 돼요.
D의 원이라고 해도 되고
4라고 해도 되고
그 다음에
큰 따옴표
연금 큰 따옴표
이렇게 해도 돼요.
잠시 후에 나오니까
이번에는 연금이
16만
이상인
인원수를 구한대요.
인원수
그건 뭐에요?
디카운트잖아요.
디카운트
마찬가지로 데이터베이스 범위
보세요
큰 따옴표 연금 큰 따옴표
이렇게 줘도 돼요.
그 다음에
연금이 16만원
이상이니까
조건범위가
B의 9에서 B의 10
이렇게 바뀌잖아요.
연금
16만원 이상
이렇게
D의 원
4
큰 따옴표 연금
큰 따옴표
이렇게 줘도
다 같은 효과에요.
조심하시고
그 다음에 이번에는
과장 직책의 총원
디카운트 A
마찬가지로
데이터베이스 범위
A1에서 D의 7이고요.
그 다음에
직책
C의 1
직책이 여기잖아요.
그 다음에
조건은
A9에서 A10
직책이 과장인
경우만 카운트하라
과장이
1명
2명
3명이
카운트가 된다는 거죠.
아무튼
AA에서
3명
그 다음에 이번에는
과장 중에서
최고연금액
최고연금액이니까 뭐에요?
D맥스죠
D맥스
그 다음에
연금이니까 마찬가지로
D의 1
그 다음에
과장이에요.
과장이니까
직책 과장
조건은
A9에서 A10이 계속 되는거죠.
요렇게
이번에는
최저
최저연금액
그럼 뭐에요?
D민으로 구하고
나머지는 똑같다라는 거죠.
그리고 이번에는
분산
분산 구할 때
DVAR
그 다음에
표준 변차
DSTDV
함수 이름만
혼돈하지 않을 정도로
알아두시면 되고요.
요번에는
김선의 연금을
추출하겠다
추출을 가져온다
라는 뜻이거든요.
그러면
DGET
가져온다
데이터베이스 범위
그 다음에 연금이니까
D의 1
D의 1이고
그 다음에
김선
사원이니까
김선이 들어가요.
성명이 김선
요렇게
조건범은
A12에서 A13이 된다라는 거죠.
요렇게
그리고 엔터를 치게 되면
김선의
연금을
가져온다라는 거고
그 다음에
요번에는
마지막으로
D프로덕트
곱하는거죠.
그래서
대리연금의 곱
대리연금의 곱은
D프로덕트를
갖고 구한다
D프로덕트를 갖고 구한다
라는 거고
대리니까
직책
대리
조건범위가
B의 12에서
B의 13으로
바뀐다라는 거죠.
그리고
연금의 곱이니까
연금 D의 1
요 부분
그 다음에 곱이니까
D프로덕트
됐네요.
네
D함수
마치도록 하겠습니다.
