[Excel] 엑셀 vlookup 자동 열번호 생성 (2탄)

표 이름 참조 시 문제

지난 시간에 vlookup 사용 시 불편할 수 있을 열 번호 입력을 자동으로 하는 법을 소개해 드렸었는데요, 참조 표에 열 수가 많고 vlookup으로 당겨오고 싶은 값들도 많은 경우 수식을 드래그 복사해서 채울 수 있다면 편할 텐데 제가 수식에 표를 참조하는 방식으로 했기에 조금 문제가 있었습니다.

2020/06/09 - [MS 오피스 팁] - 엑셀 (Excel) vlookup 자동 열번호 (match 그리고 표)

엑셀 (Excel) vlookup 자동 열번호 (match 그리고 표)

 

절대 참조를 이용한 개선

오늘은 표를 이용하되 셀 참조(Cell reference)와 표이름 참조(Structured reference / Table reference)를 복합적으로 하여 그 문제를 해결하려고 합니다.

 

검은색 표를 참조하여 주황색 표에 결과를 표시하는 사례입니다.

수식의 [이름관리자]에서 '참조표'와 '결과표'로 각각 이름을 수정하였습니다.

제품 A에는 매우 일반적인 수식을 썼는데, 중량 값을 가져오려면 열 번호가 4여야 하지만 2를 넣어서 결과에 오류가 있을 것 같네요.

제품 B에는 지난 시간 강조한 표 이름을 참조하는 수식입니다. 수식 복사 시 참조 셀의 열이나 행을 고정시키는 게 마음대로 안되어 수식을 복사하면 엉망이 되는 문제가 있습니다.

제품 C에는 오늘 알려드릴 개선 수식인데요, 검은색 참조 표는 표 이름 참조로 했고 하고, 결과표 참조는 셀 주소 참조를 이용했습니다.

 

상대 참조(Relative reference) 와 절대 참조(Absolute reference)

수식 복사 시 셀의 상대적 위치가 함께 변하는 게 기본입니다. 아래 중간처럼요. 수식을 드래그해서 내리면 수식 복사가 되지만 참조 셀의 행이 같이 변하는 게 상대 참조입니다. 하지만 행 번호에 $ 표시를 넣어주면, 드래그로 수식 복사 시 행이 고정되게 됩니다. (수식의 셀 주소에 커서를 두고 F4를 반복해서 눌러주면 행렬 → 행 → 열 → 상대 참조의 순서로 $가 표시됩니다.)

상대 참조와 절대 참조의 차이

 

오늘 배운 수식의 힘!

상대 참조와 절대 참조를 익혔으니, 오늘의 수식들을 복사한 결과를 보도록 할게요!

아래와 같이 당겨오고 싶은 값(폭, 길이)을 추가하였습니다. 그리고 기존의 수식을 드래그하여 복사할 생각입니다.

결과는요!

글씨가 너무 작아서 잘 안 보이는군요.. ㅠ_ㅠ 일단 폭에 대한 수식만 점검해볼게요.

조금 낫네요. G열과 H열의 수식을 비교해봅니다.

그리고 계산 결과입니다. 오늘 새로운 수식을 제외하고 모두 제대로 결과가 나오지 않는군요.

A 제품에 대한 수식의 문제는, F열에 제품 이름에 고정되어야 하나 상대 참조로 인해 G열을 참조하고 말았군요. 열 번호가 바뀌지 않는 건 당연한 문제! 그리고 참조 범위도 제품~길이까지만 고정되어 전체 범위를 참조하지 않습니다.

B 제품에 대한 수식의 문제는, 셀 참조 시 드래그 복사는 상대 참조가 되어 버립니다. @제품을 참조해야 하지만 @중량을 참조하였고, 머리글은 원래 상대 참조가 바람직한데 다행히 잘 바뀌어 주었군요. (중량 -> 폭)

C 제품에 대한 수식은, F열 고정($)을 하였고 머리글 참조 시 1행을 고정($)을 하였습니다. 그래서 열 방향으로 드래그 수식 복사 시 문제없는 결과를 가져다주었습니다.

 

표의 위력

만약 제품이 D, E 계속 추가된다면요?

 

역시 문제없습니다! 아까 머리글 행인 1행을 고정하였기에 열 방향뿐만 아니라 행 방향으로 수식 복사도 문제가 없어요!

이제 모든 수식을 오늘의 수식으로 고쳐보겠습니다. C 제품 중량에서 수식 편집에 들어갔다 엔터를 쳐주면, fx라는 아이콘이 생깁니다. 여기로 커서를 옮기면 '이 수식이 있는 이 열의 모든 셀 덮어쓰기'라는 명령을 내릴 수 있게 됩니다. 그럼 기존의 수식들이 C 제품과 동일하게 적용되는 거예요.

A에 대한 중량 값에 오류가 있었지만, 수식 덮어쓰기 후에 제대로 된 값이 나왔습니다.

이제 옆의 수식들도 다 덮어쓰기 할 거예요.

그 결과 모든 값들이 제대로 당겨져 왔습니다.

이제 제품을 더 추가해 봅니다. D 제품과 사양을 입력하고요, 결과표에서는 C 제품 아래에 D를 입력할 겁니다.

D를 입력하고 엔터만 치면! 이것이 바로 지난 시간에 광고를 많이 했던 표의 위력입니다!

표의 범위에 한 행이 추가되었고요!

수식이 있던 열은 자동으로 수식 채움이 되었습니다!!

 

이상 오늘의 vlookup 활용법 2탄 마무리할게요~