VLOOKUP은 Excel에서 가장 유용한 기능 중 하나이며 가장 잘 이해되지 않은 기능 중 하나입니다. 이 기사에서는 실제 예제를 통해 VLOOKUP을 정리합니다. 우리는 쓸만한 것들을 만들 것입니다 송장 템플릿 가상의 회사.
VLOOKUP은 Excel입니다 함수. 이 기사에서는 독자가 이미 Excel 함수에 대해 잘 알고 있으며 SUM, AVERAGE 및 TODAY와 같은 기본 기능을 사용할 수 있다고 가정합니다. 가장 일반적인 사용법에서 VLOOKUP은 데이터 베이스 함수, 즉 데이터베이스 테이블에서 작동한다는 의미입니다. 기울기 Excel 워크 시트에있는 것들. 어떤 종류의 것들? 잘, 어떤 말하자면. 직원, 제품, 고객 또는 CD 컬렉션의 CD 또는 밤하늘 별 목록이 들어있는 워크 시트가있을 수 있습니다. 중요하지 않습니다.
다음은 목록 또는 데이터베이스의 예입니다. 이 경우 가상 회사에서 판매하는 제품 목록입니다.
일반적으로 이와 같은 목록에는 목록의 각 항목에 대해 고유 한 식별자가 있습니다. 이 경우 고유 식별자는“품목 코드”열에 있습니다. 참고 : V VLOOKUP 기능이 데이터베이스 / 목록과 작동하도록하려면 해당 목록 곰팡내 나게 하다 고유 식별자 (또는 “키”또는 “ID”)를 포함하는 열이 있고 해당 열은 테이블의 첫 번째 열이어야합니다.. 위의 샘플 데이터베이스는이 기준을 충족합니다.
VLOOKUP 사용의 가장 어려운 부분은 정확히 무엇을 이해하는 것입니다. 우리가 먼저 그것을 분명히 할 수 있는지 봅시다 :
VLOOKUP은 제공된 고유 식별자의 인스턴스를 기반으로 데이터베이스 / 목록에서 정보를 검색합니다.
위의 예에서 VLOOKUP 함수를 품목 코드가있는 다른 스프레드 시트에 삽입하면 원본에 설명 된대로 해당 품목의 설명, 가격 또는 재고 수량 ( “재고”수량)이 반환됩니다. 명부. 다음 중 어떤 정보가 다시 전달됩니까? 수식을 만들 때 결정해야합니다.
데이터베이스에서 하나의 정보 만 있으면 VLOOKUP 함수가 포함 된 수식을 작성하는 데 많은 어려움이 따릅니다. 일반적으로 이러한 종류의 기능은 템플릿과 같은 재사용 가능한 스프레드 시트에서 사용합니다. 누군가가 유효한 품목 코드를 입력 할 때마다 시스템은 해당 품목에 대한 모든 필요한 정보를 검색합니다.
이것의 예를 만들어 봅시다 : 송장 템플릿 가상의 회사에서 계속해서 재사용 할 수 있습니다.
먼저 Excel을 시작하고 빈 송장을 만듭니다.
voice 송장 템플릿을 사용하는 사람은“A”열에 일련의 품목 코드를 입력하고 시스템은 제품 데이터베이스에서 각 품목의 설명과 가격을 검색합니다. 이 정보는 유효한 수량을 입력한다고 가정 할 때 각 품목의 총계를 계산하는 데 사용됩니다.
이 예제를 단순하게 유지하기 위해 동일한 통합 문서의 별도 시트에 제품 데이터베이스를 배치합니다.
실제로 제품 데이터베이스가 별도의 통합 문서에있을 가능성이 높습니다. 데이터베이스가 동일한 시트, 다른 시트 또는 완전히 다른 통합 문서에 있는지 상관하지 않는 VLOOKUP 함수와 거의 차이가 없습니다.
따라서 다음과 같은 제품 데이터베이스를 만들었습니다.
작성하려는 VLOOKUP 수식을 테스트하기 위해 먼저 빈 송장의 A11 셀에 유효한 항목 코드를 입력합니다.
다음으로 VLOOKUP이 데이터베이스에서 검색 한 정보를 저장하려는 셀로 활성 셀을 이동합니다. 흥미롭게도, 이것은 대부분의 사람들이 잘못하는 단계입니다. 추가 설명 : cell 셀 A11의 품목 코드에 해당하는 설명을 검색 할 VLOOKUP 수식을 작성하려고합니다. 우리가이 설명을 얻을 수있는 곳은 어디입니까? 물론 B11 셀에서. BLOOKUP 공식을 셀 B11에 작성합니다. 셀 B11을 지금 선택하십시오.
VLOOKUP을 선택하고 수식을 완성하는 데 도움을 줄 수 있도록 Excel에서 제공해야하는 모든 사용 가능한 함수 목록을 찾아야합니다. 이것은 먼저 방식 탭을 클릭 한 다음 삽입 기능:
Excel에서 사용할 수있는 기능을 선택할 수있는 상자가 나타납니다.
원하는 검색어를 찾으려면 '조회'와 같은 검색어를 입력하면됩니다. 관심있는 기능이 조회 함수). 시스템은 모든 조회 관련 함수 목록을 Excel로 반환합니다. VLOOKUP 목록에서 두 번째입니다. 클릭 한 번으로 선택 확인.
그만큼 함수 인수 상자가 나타나고 인수 (또는 매개 변수)은 VLOOKUP 기능을 완료하는 데 필요합니다. 이 상자를 다음과 같은 질문을하는 함수로 생각할 수 있습니다.
- 데이터베이스에서 어떤 고유 식별자를 찾고 있습니까?
- 데이터베이스는 어디에 있습니까?
- 고유 식별자와 관련된 데이터베이스에서 어떤 정보를 검색하겠습니까?
처음 세 개의 인수가 표시됩니다 대담하게임을 나타내는 필수 인수 (VLOOKUP 함수는 인수가 없으면 불완전하며 유효한 값을 리턴하지 않습니다). 네 번째 주장은 굵은 글씨가 아니며 선택 사항입니다.
위에서 아래로 순서대로 인수를 완료합니다.
우리가 완성해야 할 첫 번째 주장은 조회 _ 값 논의. 이 함수는 고유 식별자를 어디에서 찾을 수 있는지 알려줘야합니다. 아이템 코드 이 경우)의 설명을 반환해야합니다. 앞서 입력 한 품목 코드를 선택해야합니다 (A11).
첫 번째 인수 오른쪽의 선택기 아이콘을 클릭하십시오.
그런 다음 품목 코드 (A11)가 포함 된 셀을 한 번 클릭하고 시작하다:
“A11″값이 첫 번째 인수에 삽입됩니다.
이제 우리는 값을 입력해야합니다 Table_array 논의. 다시 말해 VLOOKUP에 데이터베이스 / 목록을 찾을 위치를 알려야합니다. 두 번째 인수 옆에있는 선택기 아이콘을 클릭하십시오.
이제 데이터베이스 / 목록을 찾아 전체 목록을 선택하십시오. 헤더 행을 포함하지 않음. 이 예에서 데이터베이스는 별도의 워크 시트에 있으므로 먼저 해당 워크 시트 탭을 클릭하십시오.
다음으로 헤더 행을 포함하지 않고 전체 데이터베이스를 선택합니다.
… 그리고 누르십시오 시작하다. 데이터베이스를 나타내는 셀 범위 (이 경우“제품 데이터베이스”! A2 : D7”)는 두 번째 인수에 자동으로 입력됩니다.
이제 세 번째 인수를 입력해야합니다. Col_index_num. 우리는이 인수를 사용하여 ALO의 품목 코드와 관련된 데이터베이스의 정보를 VLOOKUP에 지정합니다. 이 특정 예에서는 항목을 기술 우리에게 돌아 왔습니다. 데이터베이스 워크 시트를 보면 '설명'열이 둘째 데이터베이스의 열. 즉, 우리는 “2”값을 Col_index_num 상자:
“설명”열이 “2”에 있으므로 “2”를 입력하지 않는다는 점에 유의해야합니다. 비 해당 워크 시트의 열. 데이터베이스가 열에서 시작된 경우 케이 “설명”열이 “Table_array”를 지정할 때 선택한 셀 집합의 두 번째 열이기 때문에 워크 시트의 “2”를 계속 입력합니다.
마지막으로 최종 VLOOKUP 인수에 값을 입력할지 여부를 결정해야합니다. Range_lookup. 이 인수는 진실 또는 그릇된 값이거나 비워 두어야합니다. 데이터베이스와 함께 VLOOKUP을 사용하는 경우 (시간의 90 % 임)이 인수에 넣을 항목을 결정하는 방법은 다음과 같이 생각할 수 있습니다.
데이터베이스의 첫 번째 열 (고유 식별자가 포함 된 열)이 알파벳 / 숫자로 오름차순으로 정렬 된 경우 값을 입력 할 수 있습니다 진실 이 논쟁에 들어가거나 비워 두십시오.
데이터베이스의 첫 번째 열이 아니 정렬하거나 내림차순으로 정렬하면 곰팡내 나게 하다 값을 입력하십시오 그릇된 이 논쟁에
데이터베이스의 첫 번째 열은 아니 정렬, 우리는 입력 그릇된 이 주장에 :
그게 다야! VLOOKUP이 필요한 가치를 반환하는 데 필요한 모든 정보를 입력했습니다. 클릭 확인 버튼을 클릭하고 품목 코드 “R99245″에 해당하는 설명이 셀 B11에 올바르게 입력되었는지 확인하십시오.
우리를 위해 만들어진 공식은 다음과 같습니다.
우리가 입력하면 다른 셀 A11에 항목 코드를 입력하면 VLOOKUP 함수의 기능을 확인할 수 있습니다.
아이템을 얻기 위해 비슷한 단계를 수행 할 수 있습니다. 가격 셀 E11로 리턴되었습니다. 새 수식은 E11 셀에 작성해야합니다. 결과는 다음과 같습니다.
… 공식은 다음과 같습니다.
두 수식의 유일한 차이점은 세 번째 인수 (Col_index_num)가 '2'에서 '3'으로 변경되었습니다 (데이터베이스의 3 번째 열에서 데이터를 검색하기 때문입니다).
이러한 품목 중 2 개를 구매하기로 결정한 경우 D11 셀에 “2”를 입력합니다. 그런 다음 F11 셀에 간단한 수식을 입력하여 총계를 얻습니다.
= D11 * E11
… 이것은…
송장 템플릿 작성
지금까지 VLOOKUP에 대해 많이 배웠습니다. 실제로이 기사에서 배울 내용을 모두 배웠습니다. VLOOKUP은 데이터베이스 이외의 다른 환경에서도 사용할 수 있습니다. 이것은 덜 일반적이며 향후 How-To Geek 기사에서 다룰 수 있습니다.
인보이스 템플릿이 아직 완성되지 않았습니다. 이를 완료하기 위해 다음을 수행합니다.
- 셀 A11에서 샘플 품목 코드를 제거하고 셀 D11에서 “2”를 제거합니다. 새로 만든 VLOOKUP 수식에 오류 메시지가 표시됩니다.
Excel을 신중하게 사용하여이 문제를 해결할 수 있습니다. 만약() 과 ISBLANK () 기능. 우리는이 공식을 다음과 같이 바꾼다… = VLOOKUP (A11, '제품 데이터베이스'! A2 : D7,2, FALSE)…이에…= IF (ISBLANK (A11),””, VLOOKUP (A11, '제품 데이터베이스'! A2 : D7,2, FALSE))
- 셀 B11, E11 및 F11의 수식을 송장의 나머지 항목 행으로 복사합니다. 이렇게하면 결과 수식이 더 이상 데이터베이스 테이블을 올바르게 참조하지 않습니다. 데이터베이스의 셀 참조를 다음과 같이 변경하여이 문제를 해결할 수 있습니다. 순수한 세포 참조. 대안으로 – 더 나은 – 우리는 범위 이름 전체 제품 데이터베이스 (예 : “제품”)에 대해 셀 참조 대신이 범위 이름을 사용하십시오. 공식은 이것에서 변경 될 것입니다… = IF (ISBLANK (A11),””, VLOOKUP (A11, '제품 데이터베이스'! A2 : D7,2, FALSE))…이에… = IF (ISBLANK (A11),””, VLOOKUP (A11, 제품, 2, FALSE))…과 그때 수식을 나머지 송장 항목 행으로 복사하십시오.
- 우리는 아마도 우리의 공식을 포함하는 세포를 “잠글”것입니다 터놓다 그만큼 다른 누군가가 인보이스를 작성할 때 신중하게 구성된 수식을 실수로 덮어 쓰지 않도록 워크 시트를 보호하십시오.
- 우리는 파일을 주형회사의 모든 사람이 재사용 할 수 있도록
우리가 느끼고 있었다면 정말 현명하게, 우리는 다른 워크 시트에 모든 고객의 데이터베이스를 만든 다음 F5 셀에 입력 한 고객 ID를 사용하여 B6, B7 및 B8 셀에 고객의 이름과 주소를 자동으로 입력합니다.
VLOOKUP으로 연습하거나 결과 송장 템플릿을 보려면 여기에서 다운로드하십시오.
