[4일차] 함수(기초통계함수, 집계함수, 논리함수, VLOOKUP함수,TEXT 가공

진짜쓰는 실무엑셀
작성자
@인사이트파인딩
작성일
2022-03-27 03:20
조회
133

1. 열심히 공부한 흔적이 담긴 사진을 남겨주세요! (책과 모니터 화면이 함께 보이면 좋습니다!)

 

 

 

2. 이번 스터디를 진행하면서 특별히 좋았던 점이나 어려웠던 점이 있다면 자유롭게 남겨주세요. 다음 스터디를 진행하는 후배 스터디원에게 큰 도움이 됩니다. (200자 이내로 작성)

자세한 설명, 철저한 사전 준비 늘 감사합니다.

늘 다음수업이 더 기대되는 시간입니다.

 

 

3. 열심히 학습한 결과파일이나 스크린샷, 코드가 있다면 자유롭게 올려주세요.

 

1. 1,~3주차 배운 내용 Review


1~2주차: 셀 참조방식, 자동채우기, 셀서식, 데이터유효성 검사

3주차: 셀서식, 데이터 정열&필터링, 단축키(셀병합과 해제, 빈칸 채우기)

2. 엑셀에서의 함수사용과 "데이터 구조"의 중요성

1) 엑셀 함수, 외우지 말고 방법만 알아도 충분합니다!

- 모든 함수의 개수 현재기준 550개, 계속 추가되고 있음.

- 업무에서의 상용함수: 5개미만(Vlookup, Sumif, If....정도)

- 자격증 시험에서는 55~100개

* 기초계산함수: SUM, AVERAGE, MAX

* 기본함수:VLOOKUP, SUMIF, IF....

2) 함수보다 더 중요한, "데이터 구조"

- 함수를 사용하는데는 "정답"이 없음.

- 적절한 방법을 상황에 따라 사용. 함수는 가능한 적게 사용하는 것이 좋다.

- 함수보다는 피벗테이블, 필터링으로 대부분 업무 해결 가능. (데이터 구조의 중요성)

3) 함수보다 데이터 구조가 중요한 이유 및 데이터 구조의 핵심-"세로방향 블록쌓기"

- 데이터 구조가 정열, 계산 등 다양하게 문제를 일으킬 수 있음.

- 데이터 구조가 올바르면 함수 없이도 많은 무제를 해결할 수 있음.

3. 기초 계산 함수: SUM, AVERAGE, COUNTA

3-1.재고관리 예제 - 기초 계산 함수

- 사용하게 될 함수: SUM함수, COUNTA함수, AVERAGE함수

- COUNNA함수 VS. COUNT함수

COUNTA함수

(COUNT ALL)

COUNT함수

(COUNT)

비어있지 않은 모든 셀의 개수

숫자가 입력된 셀의 개수만 통계

COUNTA(범위1,[범위2],[범위3],......)

COUNTA(범위1,[범위2],[범위3],......)

 

- 기초계산함수:

COUNTA함수

=COUNTA(범위)

비어있지 않는 셀의 개수 합계

SUM함수

=SUM(범위)

합계를 구함

ALT - H - U - S

빠른 실행 단축키 Σ

AVERAGE함수

=AVERAGE(범위)

산수평균을 구함

한 항목(택스트셀) 개수 합계 상태표시줄에서 개수, 합계, 평균 등 정보를 쉽게 파악할 수 있음.

- 영문 입력기에서는 ALT + - ☞ 합계

한글 입력기에서는 ALT - H - U - S (ALT-후스)

3-2.실무에서 자주 발생하는 평균의 함정:가중평균과 산술평균(AVERAGE&피벗테이블)

4. 기초집계함수: COUNTA, IF, VLOOKUP,

만족도 집계 - 기초 집계 함수: MAX, MIN, LARGE, SMALL

MAX

=MAX(범위)

범위 내의 최대값 구함

MIN

=MIN(범위)

범위 내의 최소값 구함

LARGE

=LARGE(범위,순번)

범위 내 n번째 큰 값을 구함

SMALL

=SMALL(범위,순번)

범위 내 n번째 작은 값을 구함

 

- 제일 큰/작은 값을 찾아낼 때: =MAX/MIN(범위)

 

MAX/MIN(범위):범위내 가장 큰/작은 값 계산

- n번째 큰/작은 값을 찾아낼 때:=LARGE/SMALL(범위, 순번)

- 순위를 구할 때, LARGE/SMALL함수 이용, LARGE($범위, 순번_순위 표기셀)

5. 논리함수: IF함수, =IF(조건, 참일 때 결과, 거짓일 때 결과)

체육평가 계산 - IF 함수 기초 : 합격 불합격의 통과기준: 평균>=140

 

6. 찾아주는 함수-VLOOKUP함수

- 한눈에 들어오지 않는 많은 양의 데이터를 다룰 때

- 찾고자 하는 값이 중복될 때는 대응되는 고유값(사번, 주민번호 등)이 있어야 함.

- 찾고자 하는 데이터가 표의 제일 왼쪽 열에 위치해야 함.

- 쓸수 있는 함수: ① VLOOKUP함수

② INDEX-MATCH함수 (난이도가 있는 함수)

③ XLOOKUP함수

재고현황 관리 - VLOOKUP 함수

VLOOKUP 열 순서 문제 해결 방법:

- 범위 또는 열선택 후 테두리에 커서를 대고 십자모양을 만들어 +SHIFT함께 누르고 이동하기

- 전체 열 선택하여 옮기는 것이 편함. 단 병합된 셀에서는 안됨.

=> 병합된 셀 해제 후, 셀 서식(Ctrl +1)에서 맞춤을 "선택한 영역의 가운데"로 맞춤.

VLOOKUP 함수, 4가지만 기억하세요!

- 찾을 값(고유번호, 첫열에 위치함)

- 범위(전체 데이터 범위, 절대참조)

- 열번호(첫열 기준 N번째)

- 일치옵션은 99.9%가 완전일치, (0.1%의 1의 경우는, 찾을 값이 범위로 표시될 때)

가로세로 VLOOKUP - MATCH 함수

- MATCH함수: 찾고자 하는 데이터가 위치한 셀을 찾아주는 함수

=MATCH(찾을 값, 범위_절대참조, 0_완전일치)

7. 집계함수: SUMIF, AVERAGEIF, COUNTIF

-> SUM/AVERAGE/COUNT 는 통계함수, IF는 조건함수

즉, SUMIF, AVERAGEIF, COUNTIF함수는 일정한 조건(IF, 단일조건)을 만족하는 합계,평균, 집계함수

-> 이 집계함수들을 이용하기 전에 먼저 IF기준에 맞는 항목을 찾아내야 함. (열복사-중복데이터 제거-기준항목 추출)

7-1. 온라인 주문 집계 - SUMIF 함수: =SUMIF(기준열_절대참조참조셀통계항목 열_절대참조)

지역별-기준으로 지역열 복사-중복데이터 제거 => 서울, 경기, 전라, 경상 항목 추출

지역별 주문금액 합계 => =SUMIF($D$4:$D$60,L5,$I$4:$I$60)

=AVERAGEIF($D$4:$D$60,L5,$I$12:$I$60)

=COUNTIF($F$4:$F$60,L19)

7-2. 월별합계 구하는 방법: SUMIF함수 사용(엑셀프레소#63)

 

7-3. 여러개 조건항의 OR조건의 합계: SUMPRODUCT함수

- IF조건 항목이 여러개 일 경우:

조건항이 AND조건의 경우는 SUMIFS, AVERAGEIFS, COUNTIFS함수 사용

조건항이 OR조건의 경우는

=SUMPRODUCT(SUMIFS(합계범위_절대참조, 조건범위_절대참조, OR조건))

참고자료: 교재 pp.365

동영상 엑셀고급4강_SUMPRODUCT함수

 

※ 7-4. 집계 함수 사용시, "검산" 중요합니다!

- 피벗테이블의 경우, 잘못 집계되는 경우는 없지만,

- 함수의 경우, 오타, 범위등으로 인해 잘못 집계되는 경우가 종종 발생하므로,

반드시 상태표시줄과 대조하여 검산을 해야 함. 항목수가 맞는지, 평균치,합계가 일치한지 등

8. 텍스트 가공

8-1. 텍스트 가공 - LEFT, RIGHT, MID 함수 : 고정된 자릿수만 가져올 수 있음.

LEFT함수

LEFT(셀,[문자개수])

왼쪽부터 원하는 개수만큼 문자추출

예) 성씨추출, 주민등록번호 추출

빠른 채우기

Ctrl + E

RIGHT함수

RIGHT(셀,[문자개수])

오른쪽부터 원하는 개수만큼 문자추출

예) 사번추출,

MID함수

MID(셀,시작위치,[문자개수])

문자열의 중간부터 원하는 개수만큼 문자추출

예) 주민등록번호 성별숫자 추출

8-2. 함수보다 더욱 편리한 빠른채우기

※ 왼쪽 또는 오른쪽으로 부터 불러오는 문자의 개수가 같을 때에는 LEFT/RIGHT함수 사용가능하나,

추출할 문자수가 다를 경우에는 빠른 채우기가 더 편리함.

- 빠른 채우기는 더 쉽고 편하게 텍스트를 추출할 수 있음.

단, 연속된 셀에서만 가능함. 예제와 같은 경우, 중간에 비어있는 셀에 임의로 내용 채워줌

- 패턴 파악이 용이하지 않을 경우, 첫 2개 행에는 수동으로 입력하여 패턴을 인식할 수 있게 해줌

- 성별을 문자로 넣어줄 때는 IF(ISODD(셀), "참의 결과", "거짓의 결과") 함수로 입력

8-3. 텍스트 가공 자동화 - FIND 함수

- 찾고자 하는 고정된 문자나 부호가 있을 때는 FIND함수로 해당 문자가 위치한 자리를 찾을 수있음.

예) FIND("찾을 값", 셀) _ 찾을 값이 위치한 자리를 반환함.

- 왼쪽/오른쪽으로부터 연속된 문자를 추출할 때 사용하거나

또는 중간의 특정 문자를 추출할 때 유용함.

- LEFT(셀, FIND("찾을 값", 셀)-1)

LEFT(,FIND())함수를 묶어서 "@"바로 앞의 위치까지만 추출함(메일주소에서 이름추출)

※중간의 특정 문자열을 추출하는 방법

FIND함수로 "Kim"포함한 항목 추출 => ISNUMBER(FIND("Kim", 셀))로

- SEARCH함수로 FIND함수 대체(대소문자 구분안함)

SEARCH함수를 통해 "찾을 값"을 지정셀로 변경(찾을 값 변경해도 자동화 가능케 함)

 

8-4. TEXT 함수의 올바른 사용방법 & 문장에 줄바꿈을 추가하는 CHAR 함수

TEXT("2021-03-26", "yyyy-mm-dd") & " " _ 날짜를 셀서식을 지정해 텍스트로 묶어줌+ 빈칸 추가

& C6 _ 제품명(텍스트)를 연결해줌.

& CHAR(10)_ 줄바꿈 추가

&"[" _ 꺽쇠괄호로 가격표시(좌,열어줌)

&TEXT(D6, "#,##0) _ 금액을 셀 서식을 지정하여 텍스트로 연결해줌.(좌우 꺽쇠괄호로 묶어줌)

&"]" _ 꺽쇠괄호로 가격표시(우, 닫아줌)

블로그 스터디메모:https://blog.naver.com/insight_finding/222683947803 

 

 

 

전체 0

전체 420
번호 카테고리 제목 작성자 작성일 추천 조회
알림
📌 -- VBA 기초-활용 4주 완성, 특별 스터디 모집을 시작합니다! ~7/8(금) 까지 -- 📌 (89)
오빠두엑셀 | 2022.06.23 | 추천 101 | 조회 15582
오빠두엑셀 2022.06.23 101 15582
공지사항 진짜쓰는 실무엑셀
✍ 진짜쓰는 실무엑셀 스터디 (1기) 스터디 노트 작성을 마감합니다. 🎉
오빠두엑셀 | 2022.05.03 | 추천 3 | 조회 300
오빠두엑셀 2022.05.03 3 300
공지사항 진짜쓰는 실무엑셀
[📌Wrap-up] 8주 핵심 복습 & 우수 수강생 발표 (전체 공개, PPT 포함) (3)
오빠두엑셀 | 2022.05.01 | 추천 5 | 조회 311
오빠두엑셀 2022.05.01 5 311
33574 진짜쓰는 실무엑셀
[8주차] 엑셀차트 시각화_실전차트 만들기
haaappy y.w. | 2022.05.02 | 추천 0 | 조회 277
haaappy y.w. 2022.05.02 0 277
33520 진짜쓰는 실무엑셀
[4주차] 실무 필수 함수
hoho | 2022.04.30 | 추천 0 | 조회 191
hoho 2022.04.30 0 191
33519 진짜쓰는 실무엑셀
[3주차] 보고서 작성 핵심 스킬 - 조건부서식
hoho | 2022.04.30 | 추천 1 | 조회 110
hoho 2022.04.30 1 110
33518 진짜쓰는 실무엑셀
[2주차] 엑셀 핵심 규칙 - 셀 서식 & 데이터 관리
hoho | 2022.04.30 | 추천 0 | 조회 71
hoho 2022.04.30 0 71
33515 진짜쓰는 실무엑셀
[1주차] 엑셀 필수 동작 원리
hoho | 2022.04.30 | 추천 0 | 조회 128
hoho 2022.04.30 0 128
33492 진짜쓰는 실무엑셀
[8일차] 데이터 시각화 첨부파일
ajs**** | 2022.04.30 | 추천 0 | 조회 107
ajs**** 2022.04.30 0 107
33491 진짜쓰는 실무엑셀
[7일차] 피벗테이블 슬라이서 활용 첨부파일
ajs**** | 2022.04.30 | 추천 0 | 조회 71
ajs**** 2022.04.30 0 71
33490 진짜쓰는 실무엑셀
[6일차] 데이터 관리의 핵심!! 세로 첨부파일
ajs**** | 2022.04.30 | 추천 0 | 조회 70
ajs**** 2022.04.30 0 70
33481 진짜쓰는 실무엑셀
진짜쓰는 실무엑셀 8주 스터디 #8 첨부파일
넘사르 | 2022.04.29 | 추천 3 | 조회 78
넘사르 2022.04.29 3 78
33477 진짜쓰는 실무엑셀
[5주차] 배열, 다중조건 VLOOKUP(=INDEX+MATCH), 동적범위(OFFSET)
Bae-Um | 2022.04.29 | 추천 0 | 조회 56
Bae-Um 2022.04.29 0 56
33471 진짜쓰는 실무엑셀
[5일차]엑셀 필수 공식, 5개만 기억하세요! 첨부파일
츠바키 | 2022.04.29 | 추천 0 | 조회 58
츠바키 2022.04.29 0 58
33470 진짜쓰는 실무엑셀
[4일차]엑셀 중요 함수 첨부파일
츠바키 | 2022.04.29 | 추천 0 | 조회 55
츠바키 2022.04.29 0 55
33463 진짜쓰는 실무엑셀
[4주차]엑셀 주요 함수
mama | 2022.04.29 | 추천 0 | 조회 61
mama 2022.04.29 0 61
33462 진짜쓰는 실무엑셀
[3주차] 조건부 서식, 정렬, 필터
mama | 2022.04.29 | 추천 0 | 조회 37
mama 2022.04.29 0 37
33461 진짜쓰는 실무엑셀
[2주차]셀 서식&데이터 관리
mama | 2022.04.29 | 추천 0 | 조회 36
mama 2022.04.29 0 36
33459 진짜쓰는 실무엑셀
[1주차]엑셀 데이터 유형 및 채우기
mama | 2022.04.29 | 추천 0 | 조회 46
mama 2022.04.29 0 46
33430 진짜쓰는 실무엑셀
[7일차] 피벗 테이블 기초_220416
누쿠 | 2022.04.28 | 추천 0 | 조회 49
누쿠 2022.04.28 0 49
33429 진짜쓰는 실무엑셀
[3일차] 조건부서식&실전TIP총정리_220312
누쿠 | 2022.04.28 | 추천 0 | 조회 74
누쿠 2022.04.28 0 74
33428 진짜쓰는 실무엑셀
[2일차] 핵심규칙-서식&데이터관리/당신은 Key person입니까?_220306 첨부파일
누쿠 | 2022.04.28 | 추천 0 | 조회 61
누쿠 2022.04.28 0 61
33426 진짜쓰는 실무엑셀
[1일차] 엑셀 필수 동작 원리_220226 첨부파일
누쿠 | 2022.04.28 | 추천 0 | 조회 92
누쿠 2022.04.28 0 92
글쓰기