4주차 학습정리

1차시 : 주요 함수

Starting 함수!

① VLOOKUP ② IF ③ SUMIF

 

함수를 사용하는 데에는 "정답"이 없다!

 

함수는 '적게' 사용하는 것이 좋다!

 

계산이 복잡해질수록 데이터 구조가 잘못되어 있을 가능성이 있다!

 

기초 함수

1) SUM(), COUNTA() (count all), Average()

 

tip) 개수나 합계는 상태표시줄로 빠르게 판단할 수 있다.

 

tip) 합계 단축키는 Alt + H + V + S

 

★ Average함수 주의점!

평균은 수량이 1개씩이라는 가정으로 계산한다. 즉, 산술평균이다.

 

만약, 단가를 계산할 경우 수량에 따라 비중을 고려하는 가중평균을 구해야 한다.

따라서, 단가는 따로 평균금액 / 평균수량으로 구해야 한다. Average함수 사용 NO!

 

2) MAX(), MIN(), SMALL(), LARGE()

 

 

+ 함수에 동적범위 적용하는 방법

SUM(OFFSET( 시작, 행이동, 열이동, [높이], [너비] ))

EX) SUM(OFFSET(A1,,,10000,0))

 

필수 함수

1) ★ IF함수

=IF(조건, TURE결과값, FALSE결과값)

 

2) VLOOKUP함수 : 찾아주는 함수

요건)

① 고유값

② 찾는 값이 범위의 첫번째 열

 

=VLOOKUP(찾을값, $범위, 열번호, 0)

 

단, 유사일치 시 찾을 값이 작성된 셀은 오름차순으로 작성

VLOOKUP은 유사일치의 경우 작거나 같은 값을 찾아준다.

 

+) 찾을 값이 중간에 있을 경우

1) 쉽게 : 잘라내기 이동 (범위 > 십자가 커서 > SHIFT 드래그) 으로 맨 왼쪽에 두고 VLOOKUP적용

 

2) 어렵게 : INDEX / MATCH "배열" (처리속도 ↓) OR XLOOKUP

 

+) VLOOKUP 열 번호 자동화하기

 

머리글 위 : =MATCH(검색값,범위,0)

머리글 : 데이터 유효성 검사 > 목록 >범위

값 : VLOOK( , , 열번호 , 0) 에서 열 번호를 MATCH가 입력된 셀 주소 넣기

 

3) SUMIF, AVERAGEIF, COUNTIF

 

SUMIF(조건 범위, 조건, 합계 범위)

AVERAGEIF(조건 범위, 조건, 합계 범위)

요건) ① 조건 셀이 필요하다 ② 범위는 길이가 일치해야 한다, 절대 참조

 

TIP) 범위 선택 : CTRL + SHIFT + ↓

 

COUNTIF(조건범위, 조건)

 

★★★ 함수는 반드시 "검산" 과정이 필요하다. 상태 표시줄로 확인하는 습관을 가지자

 

2차시 : 텍스트 추출 및 가공

텍스트 함수

1 ) LEFT(셀, 글자수) ; RIGHT(셀, 글자수) ; MID(셀, 시작, 글자수) ; ISODD() 홀수면 TRUE반환

 

TIP) 빠른 채우기 (단, 적용할 셀이 연결되어 있어야 한다)

 

텍스트 찾기 자동화 

FIND함수 (대소문자 구분) ↔ SEARCH함수 (대소문자 구분 안 함, 와일드 카드 가능)

 

EX) 이메일 주소

=LEFT(셀, FIND("@",셀)-1)

 

EX) 도메인 주소

=RIGHT(셀, LEN(셀)-FIND("@",셀))

 

TIP) 빠른 채우기! 편리하게 텍스트를 뽑아낼 수 있다!

 

EX) 이름으로 찾기

=IF(ISNUMBER(FIND("$이름셀,셀)), "포함", "미포함")

 

날짜 데이터 텍스트와 합치기 

=TEXT(셀, "셀 서식")

=TEXT(셀, "yyyy-mm-dd")&셀 

 

TIP) char(10) 은 줄바꿈 ( 홈 > 자동 줄바꿈 체크 )

 

부록

날짜 차이

DATEDIF(시작, 종료, "단위")

 

단위 : "Y" 년, "YM" 개월수. "MD" 일수

 

함수 구현하는 연습법

① 처음은 함수 마법사를 쓰며 함수 인자에 익숙해지자 (수식입력줄 fx버튼 or SHIFT + F3)

② 빈도 높은 함수를 익히기

 

TIP) VLOOKUP 여러개 강의 참고하기

https://www.youtube.com/watch?v=OHQx744ZmVk&ab_channel=%EC%98%A4%EB%B9%A0%EB%91%90%EC%97%91%EC%85%80l%EC%97%91%EC%85%80%EA%B0%95%EC%9D%98%EB%8C%80%ED%91%9C%EC%B1%84%EB%84%90 

 

중복되는 데이터 구분 세기

피벗테이블을 사용하자 (ALT + N + V)

ver 2021) unique()

=counta(unique(범위))

ver 이전EXCEL) sumproduct 응용 → 배열을 이용한 응용 수식인 듯 싶다

=sumproduct(1/countif(범위, 범위)) 단, 빈칸이 있으면 작동이 안 됨

 

+) 빈칸을 포함하는 고유값 개수

=sumproduct(1/countif(범위, 범위&""))

 

+) 빈칸을 제외한 고유값 개수

=sumproduct((범위<>"")/countif(범위, 범위&""))

초록모서리 해결방법

 

숫자로 일괄변경 : ALT + A + E + F

 

날짜로 일괄변경 : ALT + A + E + N + N + D + F 

 

 

출처 : #88회 4주차 라이브 강의 후 요약(recap)

https://support.microsoft.com/ko-kr/office/excel-%ED%95%A8%EC%88%98-%EC%82%AC%EC%A0%84%EC%88%9C-b3944572-255d-4efb-bb96-c6d90033e188

Excel 함수(사전순)

https://www.oppadu.com/%ec%97%91%ec%85%80-%ed%95%a8%ec%88%98-%eb%aa%a9%eb%a1%9d/

엑셀 함수 목록 :: 함수 모음 (항목별, 이름별) - 오빠두엑셀