EXCEL 데이터 정리 (VLOOKUP & HLOOKUP / SEQUENCE)

EXCEL 데이터 정리

목차
VLOOKUP 
- SEQUENCE 활용
HLOOKUP
- SEQUENCE 활용

728x90

VLOOKUP 

※ 특정 문자를 기준으로 오른쪽에 있는 셀의 값을 출력한다.
 * 영어의 경우 대소문자 구분 없이 동일하게 인식된다.

함수 구성 :  VLOOKUP("찾을 문자", 찾는 범위, 값을 가져올 열의 위치, 근삿값 or 일치)

e.g) VLOOKUP("제목 01", B:G,3, FALSE)

 - "찾을 문자" : 고유번호 및 순번, 날짜와 시간을 숫자로 표현한 값 등 중복되지 않는 값 또는 문자를 사용해야 한다.
  e.g) 제목01
 - 찾는 범위 : '찾을 문자'가 있는 셀이 가장 왼쪽에 있어야 한다.
  e.g) B:G
 - 값을 가져올 열의 위치 : 찾을 문자가 있는 열을 기준으로 오른쪽으로 몇 번째에 있는 값을 추출할지 정한다.
  e.g) 찾을 문자가 B열(1)에 있고 추출할 문자가 D(3)에 있다면, 숫자 3을 입력한다.

 - 근삿값 or 일치
   근삿값(TRUE) : 찾을 문자와 가장 가까운 값을 찾는다
   일치하는 값(FALSE) : 찾을 문자와 동일한 값을 찾는다

 

1) 데이터

2) 찾을 값 세팅
 - 주로 찾을 값이 있는 열을 복사해서 값만 붙여 넣는다.

3) 찾을 값 우측에 수식을 입력한다.
 - 수식 : =VLOOKUP(I2, B:G,2, FALSE)
 - 결과 : 저자01

 - FALSE 앞의 값을 수정하여 찾는 값 오른쪽에 있는 모든 값을 추출할 수 있다.
 e.g) =VLOOKUP(I2, B:G,5, FALSE) -> 3순위1

기타 활용 방법

• $ 를 활용해 복사 붙여 넣기 시 수식 고정
  수식 : =VLOOKUP($I2,$B:$G,2, FALSE)
• SEQUENCE를 활용해 숫자 자동 적용
  SEQUENCE(행의 수, 열의 수, 시작 숫자, 증감의 값)
  - 오른쪽으로 값이 늘어나야 하는 경우 열의 수가 증가해야 한다.
  수식 : =VLOOKUP($I3,$B:$G, SEQUENCE(1,5,2,1), FALSE)
  * SEQUENCE(1,5,2,1) -> 1개의 행, 5개의 열, 2부터, 1씩 증가

• 정리가 안된 데이터 정리 시 사용
  - Title 열에서 빈칸 및 ㅁ 등 필요 없는 부분은 다른 시트에 복사해서 붙여 넣은 후 필터를 이용하여 '제목' 종류만 추출할 수 있다.

 

HLOOKUP 

※ 특정 문자를 기준으로 아래쪽에 있는 셀의 값을 출력한다.
 * 영어의 경우 대소문자 구분 없이 동일하게 인식된다.

함수 구성 :  HLOOKUP(찾을 문자가 있는 항목의 명칭, 찾는 범위, 값을 가져올 행의 위치, 근삿값 or 일치)

e.g) HLOOKUP("Title", B:G,2, FALSE)

 - 찾을 문자가 있는 항목의 명칭 : 주로 1행의 항목 명칭을 사용한다.
  e.g) Title
 - 찾는 범위 : '찾을 문자'가 있는 셀이 가장 왼쪽에 있어야 한다.
  e.g) B:G
 - 값을 가져올 행의 위치 : 찾을 문자가 있는 행을 기준으로 아래쪽으로 몇 번째에 있는 값을 추출할지 정한다.
  e.g) 찾을 문자가 1행에 있고 추출할 문자가 3행에 있다면, 숫자 3을 입력한다.

 - 근삿값 or 일치
   근삿값(TRUE) : 찾을 문자와 가장 가까운 값을 찾는다
   일치하는 값(FALSE) : 찾을 문자와 동일한 값을 찾는다

 

1) 데이터

2) 찾을 문자가 있는 항목의 명칭 세팅
 - 주로 첫 번째 행의 항목 명칭을 사용한다.

3) 항목 명칭 아래쪽에 수식을 입력한다.
 - 수식 : =HLOOKUP(P2, B:G,2, FALSE)
 - 결과 : 제목01

 - FALSE 앞의 값을 수정하여 해당 열의 값을 모두 추출할 수 있다.
 e.g) =HLOOKUP(P2, B:G,3, FALSE) -> 제목02

기타 활용 방법

• $ 를 활용해 복사 붙여 넣기 시 수식 고정
  수식 : =HLOOKUP(P$2,$B:$G,2, FALSE)
• SEQUENCE를 활용해 숫자 자동 적용
  SEQUENCE(행의 수, 열의 수, 시작 숫자, 증감의 값)
  - 아래쪽으로 값이 늘어나야 하는 경우 행의 수가 증가해야 한다.
  수식 :=HLOOKUP(P$2,$B:$G, SEQUENCE(23,1,2,1), FALSE)
  * SEQUENCE(23,1,2,1) -> 23개의 행, 1개의 열, 2부터, 1씩 증가

 

반응형

첨부파일

엑셀 활용 예시_VLOOKUP, HLOOKUP.xlsx

0.02MB