스프레드시트를 효율적으로 검색하는 4가지 Excel 조회 기능

스프레드시트를 효율적으로 검색하는 4가지 Excel 조회 기능

검색하는 경우가 대부분입니다 마이크로 소프트 엑셀 스프레드시트는 매우 쉽습니다. 행과 열을 검색할 수 없는 경우 Ctrl + F를 사용하여 검색할 수 있습니다. 정말 큰 스프레드시트로 작업하는 경우 이 네 가지 조회 기능 중 하나를 사용하면 많은 시간을 절약할 수 있습니다.





조회를 사용하여 Excel에서 검색하는 방법을 알게 되면 스프레드시트의 크기에 상관없이 Excel에서 항상 무언가를 찾을 수 있습니다!



1. VLOOKUP 기능

이 함수를 사용하면 열과 값을 지정할 수 있으며 다른 열의 해당 행에서 값을 반환합니다(이가 이해가 되지 않으면 잠시 후 명확해질 것입니다). 이를 수행할 수 있는 두 가지 예는 직원 번호로 직원의 성을 조회하거나 성을 지정하여 전화 번호를 찾는 것입니다.





다음은 함수의 구문입니다.

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [조회_값] 이미 가지고 있는 정보입니다. 예를 들어, 도시가 어떤 주에 있는지 알아야 하는 경우 도시 이름이 됩니다.
  • [테이블_배열] 함수가 조회 및 반환 값을 찾을 셀을 지정할 수 있습니다. 범위를 선택할 때 배열에 포함된 첫 번째 열이 조회 값을 포함할 열인지 확인하십시오!
  • [col_index_num] 반환 값을 포함하는 열의 번호입니다.
  • [범위 조회] 선택적 인수이며 1 또는 0을 사용합니다. 1을 입력하거나 이 인수를 생략하면 함수는 입력한 값이나 다음으로 가장 낮은 숫자를 찾습니다. 따라서 아래 이미지에서 SAT 점수 652를 찾는 VLOOKUP은 646을 반환합니다. 목록에서 652보다 작은 가장 가까운 숫자이고 [range_lookup] 기본값은 1이기 때문입니다.

이것을 어떻게 사용할 수 있는지 살펴보겠습니다. 이 스프레드시트에는 ID 번호, 성과 이름, 도시, 주 및 SAT 점수가 포함되어 있습니다. 이름이 'Winters'인 사람의 SAT 점수를 찾고 싶다고 가정해 보겠습니다. VLOOKUP을 사용하면 쉽습니다. 사용할 수식은 다음과 같습니다.



=VLOOKUP('Winters', C2:F101, 4, 0)

SAT 점수는 성 열에서 네 번째 열이기 때문에 4는 열 인덱스 인수입니다. 텍스트를 찾을 때 [range_lookup]을 0으로 설정하는 것이 좋습니다. 그것 없이는 나쁜 결과를 얻을 수 있습니다.

결과는 다음과 같습니다.



651, 92행(위 삽입에 표시됨)에 있는 Kennedy Winters라는 학생의 SAT 점수를 반환했습니다. 구문을 빠르게 입력하는 것보다 이름을 검색하는 데 훨씬 더 오래 걸렸을 것입니다!

VLOOKUP에 대한 참고 사항

VLOOKUP을 사용할 때 기억해야 할 몇 가지 사항이 있습니다. 범위의 첫 번째 열이 조회 값을 포함하는 열인지 확인하십시오. 첫 번째 열에 없으면 함수가 잘못된 결과를 반환합니다. 열이 잘 구성되어 있으면 문제가 되지 않습니다.



또한 VLOOKUP은 하나의 값만 반환한다는 점에 유의하십시오. 조회 값으로 'Georgia'를 사용했다면 조지아에서 온 첫 번째 학생의 점수를 반환했을 것이고 실제로 조지아에서 온 두 명의 학생이 있다는 표시는 제공하지 않았을 것입니다.

2. HLOOKUP 함수

VLOOKUP이 다른 열에서 해당 값을 찾는 경우 HLOOKUP은 다른 행에서 해당 값을 찾습니다. 일반적으로 올바른 항목을 찾고 필터를 사용하여 원하는 항목을 찾을 때까지 열 제목을 스캔하는 것이 가장 쉽기 때문에 HLOOKUP은 스프레드시트가 정말 크거나 시간별로 구성된 값으로 작업할 때 가장 잘 사용됩니다. .

다음은 함수의 구문입니다.

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [조회_값] 는 알고 있고 해당 값을 찾고자 하는 값입니다.
  • [테이블_배열] 검색하려는 셀입니다.
  • [row_index_num] 반환 값을 가져올 행을 지정합니다.
  • [범위 조회] VLOOKUP에서와 동일하므로 가능한 경우 가장 가까운 값을 얻으려면 비워 두거나 정확히 일치하는 항목만 찾으려면 0을 입력하십시오.

이 스프레드시트에는 2000-2014년의 SAT 점수와 함께 각 주에 대한 행이 포함되어 있습니다. HLOOKUP을 사용하여 2013년 미네소타의 평균 점수를 찾을 수 있습니다. 방법은 다음과 같습니다.

=HLOOKUP(2013, A1:P51, 24)

아래 이미지에서 볼 수 있듯이 점수가 반환됩니다.

미네소타 주의 2013년 평균 점수는 1014점입니다. 2013년은 문자열이 아니라 숫자이기 때문에 따옴표로 묶지 않았습니다. 또한 24번째 행은 미네소타주에서 24번째 줄에 있습니다.

여기 Excel에서 가중 평균을 계산하는 방법 .

HLOOKUP에 대한 참고 사항

VLOOKUP과 마찬가지로 조회 값은 테이블 배열의 첫 번째 행에 있어야 합니다. 이것은 일반적으로 조회 값에 열 제목을 사용하므로 HLOOKUP에서는 거의 문제가 되지 않습니다. HLOOKUP은 또한 단일 값만 반환합니다.

3-4. INDEX 및 MATCH 함수

INDEX와 MATCH는 서로 다른 두 가지 기능이지만 함께 사용하면 큰 스프레드시트를 훨씬 빠르게 검색할 수 있습니다. 두 기능 모두 단점이 있지만 이를 결합하여 두 기능의 장점을 구축할 것입니다.

하지만 먼저 두 함수의 구문은 다음과 같습니다.

=INDEX([array], [row_number], [column_number])
  • [정렬] 검색할 배열입니다.
  • [행 번호] 그리고 [열 번호] 검색 범위를 좁히는 데 사용할 수 있습니다(잠시 후에 살펴보겠습니다.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [조회_값] 문자열 또는 숫자가 될 수 있는 검색어입니다.
  • [검색_배열] Microsoft Excel이 검색어를 찾는 배열입니다.
  • [일치 유형] 1, 0 또는 -1이 될 수 있는 선택적 인수입니다. 1은 검색어보다 작거나 같은 가장 큰 값을 반환합니다. 0은 정확한 용어만 반환하고 -1은 검색어보다 크거나 같은 가장 작은 값을 반환합니다.

이 두 기능을 함께 사용하는 방법이 명확하지 않을 수 있으므로 여기에 배치하겠습니다. MATCH는 검색어를 가져와 셀 참조를 반환합니다. 아래 이미지에서 열 F에서 값 646을 검색하면 MATCH가 4를 반환하는 것을 볼 수 있습니다.

반면에 INDEX는 반대 작업을 수행합니다. 즉, 셀 참조를 가져와 값을 반환합니다. 여기에서 City 열의 6번째 셀을 반환하라는 지시를 받았을 때 INDEX가 6행의 값인 'Anchorage'를 반환하는 것을 볼 수 있습니다.

우리가 할 일은 둘을 결합하여 MATCH가 셀 참조를 반환하고 INDEX가 해당 참조를 사용하여 셀의 값을 조회하도록 하는 것입니다. 성이 Waters인 학생이 있었고 이 학생의 점수가 무엇인지 알고 싶다고 가정해 보겠습니다. 사용할 공식은 다음과 같습니다.

삼성 스마트 TV 2016에서 앱을 다운로드하는 방법
=INDEX(F:F, MATCH('Waters', C:C, 0))

여기에서 일치 유형이 0으로 설정되어 있음을 알 수 있습니다. 문자열을 찾을 때 사용하고 싶을 것입니다. 다음은 해당 함수를 실행할 때 얻는 것입니다.

삽입된 이미지에서 볼 수 있듯이 Owen Waters는 1720을 기록했습니다. 이 숫자는 함수를 실행할 때 표시되는 숫자입니다. 이것은 단지 몇 개의 열을 살펴볼 수 있을 때 그다지 유용하지 않은 것처럼 보일 수 있지만, 만약 당신이 그것을 50번 해야 한다면 얼마나 많은 시간을 절약할 수 있을지 상상해 보십시오. 대용량 데이터베이스 스프레드시트 수백 개의 열이 포함되어 있습니다!

Excel 검색 시작

마이크로소프트 엑셀에는 많은 매우 강력한 기능 데이터를 조작하는 데 사용되며 위에 나열된 4개는 표면을 긁는 것뿐입니다. 그것들을 사용하는 방법을 배우면 당신의 삶이 훨씬 더 쉬워질 것입니다.

Microsoft Excel을 정말 마스터하고 싶다면 Essential Excel Cheat Sheet를 가까이에 두는 것이 정말 큰 도움이 될 것입니다!

이미지 크레디트: 시코/ 셔터스톡

공유하다 공유하다 트위터 이메일 Canon vs. Nikon: 어느 카메라 브랜드가 더 낫습니까?

Canon과 Nikon은 카메라 업계에서 가장 큰 두 회사입니다. 그러나 어느 브랜드가 더 나은 카메라와 렌즈 라인업을 제공합니까?

다음 읽기 관련 항목
  • 생산력
  • 스프레드시트
  • 마이크로 소프트 엑셀
  • 트릭 검색
저자 소개 이안 버클리(216건의 출판물)

Ian Buckley는 독일 베를린에 거주하는 프리랜스 저널리스트, 음악가, 공연자 및 비디오 프로듀서입니다. 글을 쓰지 않거나 무대에 오르지 않을 때는 미친 과학자가 되기 위해 DIY 전자 제품이나 코드를 만지작거리고 있습니다.

이안 버클리가 참여한 작품 더보기

뉴스레터 구독

뉴스레터에 가입하여 기술 팁, 리뷰, 무료 전자책 및 독점 거래를 확인하십시오!

구독하려면 여기를 클릭하세요.