[Excel] VLOOKUP 사용해보기

운영팀으로 옮긴 이후, 인벤토리 관리 등 엑셀 함수 사용할 일이 생겨 적어둔다...

 

VLOOKUP 함수 적용 방식

Sheet 취약점 리스트, 서버 리스트는 서로 각기 다른 Excel 파일이다.

Sheet A에 각 IP를 가진 서버의 서비스명과 hostname을 Sheet B를 참고하여 추가하고자 한다.

 

[Sheet 취약점 리스트]

진단 현황	IP
취약점 대상	10.20.30.40
취약점 비대상	20.30.40.50
취약점 대상	30.40.50.60

 

[Sheet 서버 리스트]

hostname	Private DNS	Private IP	OS	서비스명
server-a	ip-10-20-30-40-internal	10.20.30.40	Amazon Linux2	서비스 A
server-e	ip-60-70-80-90-internal	60.70.80.90	RHEL 8	서비스 D
server-b	ip-20-30-40-50-internal	20.30.40.50	Cent OS	서비스 B
server-c	ip-30-40-50-60-internal	30.40.50.60	RHEL 8	서비스 C

 

필요한 데이터만 뽑아 정렬

[Sheet 취약점 리스트]

	A	B	C	D
1	진단 현황	IP	서비스	hostname
2	취약점 대상	10.20.30.40	=VLOOKUP($C2,서버 리스트!$A1:C5,3,False)	=VLOOKUP($C2,서버 리스트!$A1:C5,2,False)
3	취약점 비대상	20.30.40.50
4	취약점 대상	30.40.50.60

 

[Sheet 서버 리스트 필요한 데이터만 정렬]

	A	B	C
1	Private IP	hostname	서비스명
2	10.20.30.40	server-a	서비스 A
3	60.70.80.90	server-e	서비스 D
4	20.30.40.50	server-b	서비스 B
5	30.40.50.60	server-c	서비스 C

 

VLOOKUP 형식
=VLOOKUP(취약점리스트의 첫 IP 행, 서버리스트의 IP 포함 전 범위, 알고자하는 열 넘버, False)

 

예시

=VLOOKUP(H4, Sheet2!A1:C2412, 2, FALSE)

1. H4 데이터와 동일한 데이터를 Sheet2!A1:C2412 에서 찾아라
2. 찾은 데이터 중 오른쪽 Sheet 기준으로 1, 2, 3 중 2번째 데이터 가져와라
3. FALSE: 데이터 동일 / TRUE: 데이터 유사
4. VLOOKUP 함수 적용된 행 복사 > 전체 행 선택 > 붙여넣기

 

꿀팁
Command + Shift + ↓ : 전체 선택 (맨아래에서부터 올라오는 걸 추천)
행 내용이 모두 비워져있는 경우, 맨 아래 행에 . 으로 채워서 행의 끝 지정

 

 

피벗 테이블(Pivot Table) 생성

서버가 몇 대이고 어떤 서비스인지 식별하고자 한다.

 

[Sheet 서버 리스트]

	A	B	C
1	Private IP	hostname	서비스명
2	10.20.30.40	server-a	서비스 A
3	60.70.80.90	server-e	서비스 D
4	20.30.40.50	server-b	서비스 B
5	30.40.50.60	server-c	서비스 C

 

1. Sheet 서버 리스트의 C열을 전체 클릭한다.
2. C열을 전체 클릭한 다음, 삽입 탭 클릭
3. 표 또는 범위 선택 확인 후, 확인 버튼 클릭
4. 피벗 테이블 필드에 뜨는 서비스를 아래 영역 '행' 필드와 '값' 필드에 끌어 놓기

피벗 테이블 완성!