1. 다른 시트 참조
데이터는 Sheet1에 있고,
검색은 Sheet2에서 한다고 할 때
Shee2의 B2셀에 커서를 넣고,
=vlookup(a2, 까지 입력한 후
아래 부분의 Sheet1 탭을 클릭하고, 마우스 왼쪽 버튼을 누른 후 B2셀에서 D16셀까지 끈 다음 왼쪽 버튼을 뗍니다.
그러면 Sheet1!B2:D16이 입력되는데, 절대 참조형식으로 바꾸기 위해 F4키를 누릅니다.
=VLOOKUP(A2,Sheet1!$B$2:$D$16까지 입력됐는데, 지점명을 구해야 하므로 열 순번 2를 입력하고,
정확히 일치하는 값을 찾기 위해 False 또는 0을 입력하고, (괄호를 닫은 다음) 엔터 키를 누릅니다.
그러면 A가 구해지고 B3셀로 이동하는데, B2셀을 클릭하면 수식은 아래와 같습니다.
수식 A2에서 A는 변하면 안 되므로 A왼쪽에 $기호를 붙입니다.
그리고, 2는 2 열이므로 column(b2)로 바꿉니다.
이제 B2셀을 복사한 후 B2셀부터 C4셀까지 마우스로 끌어서 선택한 후 fx라고 표시된 수식 아이콘을 누르면 수식이 붙여 넣어집니다.
그러면 B2셀부터 C4셀까지 해당 날짜에 대한 지점명과 판매금액이 구해집니다.
B2셀의 채우기 핸들을 C2셀까지 끌고,
다시 C2셀의 채우기 핸들을 C4셀까지 끌어도 됩니다.
C2셀의 수식을 확인하면 =VLOOKUP($A2,Sheet1!$B$2:$D$16,COLUMN(C2),0)으로 column(b2)가 오른쪽으로 이동하면서 column(c2)로 오른쪽으로 한 칸 이동해서 b2가 c2로 자동으로 변경됐습니다.
이와 같이 셀의 상대적인 위치에서 따라 셀의 주소가 변경되는 것이 엑셀의 특징이며 장점입니다.
따라서, 상대적인 위치에 따라 셀 주소가 변경되므로 변경되지 않을 것에는 $기호를 붙여, 예를 들어 $A2에서 A는 변경되지 않도록 하고, $B$2:$D$16는 전체가 변경되지 않도록 수식을 작성하는 것이 중요합니다.
[column함수와 columns함수 비교]
column은 열에 해당하는 숫자를 구해주는 함수이고,
columns는 범위 내 열의 개수를 구해주는 함수입니다.
따라서, column(c2)라고 하면 3을 반환하고,
columns(b:d)라고 해도 b열에서 d열까지 열의 개수가 3개이므로 3을 반환합니다.
columns(b:b)는 1이고, columns(b:c)는 2가 됩니다.
그런데 오른쪽으로 이동할 때 b는 고정이어야 하므로 columns($b:c)라고 앞의 열을 절대 참조형식으로 지정해서 변하지 않고 뒤의 c만 변하도록 하면 오른쪽으로 이동할 때 d, e, f 식으로 변경되므로 순서대로 오른쪽 값을 구할 수 있습니다.
B2셀의 수식은 =VLOOKUP($A2,Sheet1!$B$2:$D$16,COLUMNS($B:C),0)으로 columns($b:c)인데 B2셀의 채우기 핸들을 오른쪽으로 끌면
C2셀의 수식은 =VLOOKUP($A2,Sheet1!$B$2:$D$16,COLUMNS($B:D),0)으로 $B는 변하지 않고 C만 D로 자동으로 변경돼서 columns($b:d)의 값 3이 구해집니다.
이렇게 column 또는 columns함수를 이용하는 방법은 오른쪽으로 많이 떨어져 있는 값을 구할 때 유용합니다.
예를 들어 찾을 값은 B열에 있는데 P열에 있는 값을 찾는다고 하면
col_index_num은
column(p2)-1 또는
columns(b:p)로 구할 수 있습니다.
위 예에서는 찾을 값이 B열에 있고 지점명은 C열에 있으므로
column(c2)-1이 되는데, column(b2)와 값이 2로 같으므로 column(b2)라고 입력했고,
columns($b:c)는 동일합니다.
2. 에러 발생 시 조치
지점별 판매금액을 구하는데 에러가 발생하도록 세 번째 지점명을 데이터에는 없는 D라고 지정했습니다.
판매금액을 구하는 수식은 =VLOOKUP(A8,Sheet1!$C$2:$D$16,2,0)인데,
B8셀의 채우기 핸들을 더블 클릭하면 A와 B지점의 판매금액은 구해지는데, D지점은 없으므로 #N/A(값없음)이라고 에러가 표시됩니다.
과거에는 if함수와 iserror함수를 결합해서 사용해서 아래와 같이 복잡하게 사용했는데,
=IF(ISERROR(VLOOKUP(A10,Sheet1!$C$2:$D$16,2,0)),0,VLOOKUP(A10,Sheet1!$C$2:$D$16,2,0))
위 의미는 에러면 0, 아니면 vlookup함수로 구한 값을 반환하라는 것입니다.
에러이기 때문에 0이 반환됐습니다.
iferror함수를 사용하면 에러가 아닐 때는 vlookup함수로 구한 값을 반환하고, 아니면 0을 반환하므로
=IFERROR(VLOOKUP(A10,Sheet1!$C$2:$D$16,2,0),0)
라고 vlookup함수가 중복되는 부분이 없어져서 수식이 엄청 간단해졌습니다.
3. 찾을 범위를 표로 지정
Sheet1을 클릭한 다음 데이터가 있는 셀 어디든 클릭하고
삽입 탭 > 표 그룹에서 표를 클릭합니다. 단축키는 Ctrl + T입니다.
그러면 자동으로 범위가 A1에서 D16으로 지정되고, 아래 '머리글 포함'에 체크됩니다. 머리글이란 NO부터 판매금액까지입니다. 확인 버튼을 누르면
자동으로 표 스타일이 지정되어 표시됩니다.
이제 Sheet2를 클릭하거나 Ctrl + PgDn키를 눌러 Sheet2로 이동한 다음
※ Ctrl + PgUp(Page Up)은 왼쪽 시트로 이동, Ctrl + PgDn(Page Down)은 오른쪽 시트로 이동하는 단축키
B8셀을 클릭하고, vlookup 함수를 입력하면 찾을 범위가 표1[[지점명]:[판매금액]]으로 표시가 바뀝니다.
[행을 추가하는 경우]
Sheet1에서 17셀에 지점명을 D로 해서 데이터를 추가로 입력하면
Sheet2의 C10셀의 수식에서 Sheet1!$C$2:$D$16에서 $D$16가 $D$17로 자동으로 변경되는데
B8셀의 채우기 핸들을 더블 클릭해서 B10셀까지 수식을 복사한 후 B10셀의 수식을 확인해보면 표1[[지점명]:[판매금액]]은 변화가 없습니다.
4. 찾을 범위를 이름으로 지정
가. 표를 범위로 변환
이번에는 Sheet1을 선택한 다음 표 안 어느 셀이든 클릭한 후 테이블 디자인 탭 > 도구 그룹에서 '범위로 변환'명령을 눌러 표를 범위로 변환합니다.
그러면 '표를 정상 범위로 변환하시겠습니까?'라고 묻는데 '예(Y)'를 클릭합니다.
그러면 표 스타일은 변화가 없는데 Power Pivot 오른쪽에 있던 테이블 디자인 탭이 없어졌습니다.
표가 없어지니까 B8셀의 수식이 기존처럼 셀 주소로 변경되었습니다.
나. 이름 정의
C2셀에서 D17셀까지 범위를 지정한 후 수식 탭 > 정의된 이름 그룹에서 이름 정의 명령을 누릅니다.
'새 이름'창이 표시되면서 이름에 A라고 입력되어 있고, 범위는 통합 문서, 설명은 비어 있고, 참조 대상은 Sheet1!$C$2:$D$17로 지정되어 있습니다.
이름을 지점별판매금액이라고 수정하고 확인버튼을 누릅니다.
B8셀의 수식에서 Sheet1!$C$2:$D$17 부분을
지점별판매금액으로 수정하고 엔터키를 누르면 맞는 값이 구해집니다.
B9셀을 클릭한 후 =vlookup(a9,라고 입력한 다음 C2셀에서 D17셀까지 선택하면 셀 주소가 입력되는 것이 아니라 '지점별판매금액'이란 이름으로 입력됩니다. ,2,0)를 추가로 입력하고 엔터키를 누르면 값이 제대로 구해집니다.
또는 C2셀에서 D17셀까지 선택할 필요 없이 수식 탭 > 정의된 이름 그룹 > 수식에서 사용을 누른 다음 지점별판매금액을 클릭해도 됩니다.
그러면 아래와 같이 수식에 지점별판매금액이 들어가는데, 위와 달리 셀 선택이 표시되지 않는 점이 다른 점입니다.
,2,0를 입력하고 엔터 키를 누르면 B9셀에 원하는 값이 구해집니다.
다. 한 줄 데이터 추가하기
18행에 데이터를 추가한 후
수식 탭 > 정의된 이름 그룹 > 이름 관리자를 클릭하면
이름 관리자 창이 나타나는데, 편집 버튼을 눌러 범위를 확인해면
C2셀에서 D17셀로 D18셀로 자동으로 변하지 않습니다.
이것이 표와 범위 또는 이름의 차이점입니다.
중간에 삽입하거나 삭제할 때는 3개 모두 범위가 조정되는데, 끝에 추가할 때는 표는 늘어나는데, 범위와 이름은 변화가 없습니다.
14행을 복사한 후 마우스 오른쪽 버튼을 누른 후 '복사한 셀 삽입'메뉴를 누른 다음
지점별판매금액의 참조 대상을 확인하니 D17에서 D18로 늘어났습니다.
'Excel' 카테고리의 다른 글
| Large(큰 수), Small(작은 수) 함수 (2) | 2023.01.03 |
|---|---|
| Aggregate(옵션 적용 집계) 함수(1) - 구문, 함수, 오류 값 (0) | 2023.01.02 |
| Vlookup 함수(1) - 구문, 정확한 값, 유사값 찾기 (0) | 2022.12.27 |
| 텍스트로 인식되는 날짜 데이터 피벗 만들기 (2) | 2022.12.26 |
| 수식이 텍스트로 입력될 때 (0) | 2022.12.22 |