5주차 학습정리

1차시 : 배열 이해하기, INDEX/MATCH함수, 다중조건 VLOOKUP

 

복습!

필터 단축키

Ctrl Shift L → Ctrl 방향키 → Alt 방향키 → E (검색)

 

직장인 평균 5개 함수 (VLOOKUP, if, sumif)

 

함수는 되도록 적게 쓰는 것이 좋다.

데이터 구조가 잘못되어서 그렇다.

 

① 원본 데이터 유지

파워쿼리를 사용해 데이터 정규화 후 함수나 피벗테이블

 

② 여러 공식을 사용한다.

 

고급공식

VLOOKUP, IF, MATCH, INDEX, OFFSET

 

배열 이해하기

범위를 한 번에 적용하는 방법

 

범위 > F2 수식편집 > Ctrl Shift Enter

=SUM(IF(LEFT(B5:B9,1)="김",C5:C9,0))

 

INDEX/MATCH함수

VLOOKUP 은 맨 왼쪽에서 이동한다.

 

열이 맨 왼쪽에 없는 경우 INDEX/MATCH함수를 사용해서 찾는다.

 

INDEX/MATCH는 ①MATCH로 순번을 반환한다. ②INDEX로 특정 순번에 값을 출력한다.

 

다중 조건 VLOOKUP

1가지 기억하자 : 배열의 연산

 

계산이 필요한 범위 > F2 > 배열 > Ctrl Shift Enter

 

ex) {(범위1=값1)*(범위2=값2)}

배열의 위치가 같은 곳끼리 계산 됨에 유의한다.

* 는 And 조건 → {FALSE, FALSE, TRUE}*{FALSE, TRUE, TRUE} → {0, 0, 1}

+ 는 or 조건 → {FALSE, FALSE, TRUE}*{FALSE, TRUE, TRUE} → {0, 1, 2}

 

TIP) 계산된 값 미리보기 F9

TIP) --(1=1)

      --(TRUE)

논리값을 숫자로 반환

 

다중조건 공식 :

{=IFFERROR(INDEX(출력범위, MATCH(1, --(조건1=조건범위1)*(조건2=조건범위2), 0)),"없음") }

2차시 : ISNUMBER/SEARCH 함수, 다중조건 IF함수,

OFFSET함수 (목록상자 자동화), VLOOKUP 값 여러개

 

ISNUMBER/SEARCH 함수

텍스트가 포함된 여부 확인하는 법

(SEARCH함수는 대소문자 구분 안 함, FIND함수는 대소문자 구분)

 

SEARCH(찾을 텍스트, 텍스트) 

시작지점을 찾아 숫자로 반환

 

ISNUMBER (값)

숫자면 TRUE 반환

 

=IF(ISNUMBER(SEARCH(찾을 텍스트, 텍스트 )), "포함", "미포함")

다중조건 IF함수

IF함수 내 and와 or을 사용하여 다중 조건을 표현한다.

 

And(조건1, 조건2)

or(조건1, 조건2)

OFFSET함수 (목록상자 자동화)

데이터 누적

① 표 (파워쿼리 편의성) 단, 제한사항이 셀병합, 연속된 데이터

 

② 동적범위 단, 용량이 커져 속도 저하 발생

 

=OFFSET(시작지점, 행이동, 열이동,  [높이], [너비])

 

아래로

=OFFSET(시작 셀, , , COUNTA(셀 범위))

 

First 수식 > 이름관리자 > 이름 & 참조대상 입력 =OFFSET(시작 셀, , , COUNTA(셀 범위))

second 유효성 검사 > 원본에 F3 > 이름 관리자에 생성한 동적범위 선택

VLOOKUP 값 여러개

(2021ver)

=FILTER(배열, ( 찾을 배열1 = 조건1 ) * ( 찾을 배열2 = 조건2 ))

 

(VLOOKUP 공식)

VLOOKUP 여러 필드 출력 함수 :

COLUMNS 함수를 이용하여 여러 열을 순서대로 반환해준다. (2021 FILTER함수 기능)

= INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

'본 수식은 배열수식이므로 CTRL + SHIFT + ENTER 로 입력합니다.

 

VLOOKUP 한 열의 여러개 값을 찾아 가로로 반환하는 식:

= INDEX($출력범위,SMALL(IF($찾을값=$찾을범위,ROW($찾을범위)-MIN(ROW($찾을범위))+1, ""),COLUMN(A1)))

 

VLOOKUP 한 열의 여러개 값을 찾아 세로로 반환하는 식:

= INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)))

 

출처 :

https://www.oppadu.com/vlookup-%ED%95%A8%EC%88%98-%EC%97%AC%EB%9F%AC%EA%B0%9C-%EA%B0%92-%EC%B6%9C%EB%A0%A5/

VLOOKUP 함수 여러개 값 가져오기 :: 엑셀 공식 - 오빠두엑셀