엑셀 / 함수 / VLOOKUP, HLOOKUP, XLOOKUP
VLOOKUP
문법
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
- lookup_value : 찾을 값
- table_array : 조회할 범위
- col_index_num : 반환하려는 값이 있는 열 번호
- range_lookup : TRUE면 근사 일치, FALSE면 정확히 일치. 기본값은 TRUE
예제
=VLOOKUP(E2,A2:C16,2,FALSE)
- A2:C16의 1열(A열)에서 E2의 값을 찾고, 그 행의 2열(B열)의 값을 출력합니다.
- FALSE는 정확히 일치하는 것을 찾으라는 뜻입니다.
- E2에 6이 있으니, 6에 해당하는 이름 F를 출력합니다.
=VLOOKUP(E2,A2:C16,3,FALSE)
- 가격을 출력하고 싶다면 2를 3으로 변경하면 됩니다. 선택 범위의 3열이 Price니까요.
일치하는 값이 없다면
- 일치하는 값이 없다면 #N/A를 출력합니다.
데이터 유효성 검사 활용
데이터 유효설 검사를 활용하면, 조회할 값을 제한할 수 있어 에러를 방지할 수 있습니다. No 목록을 만들고 싶다면...
- [데이터]의 [데이터 유효성 검사]를 클릭합니다.
- 제한 대상을 [목록]으로 정하고, 원본을 선택하고, [확인]을 클릭합니다.
- 이제 지정한 값만 목록으로 나오고, 그 중 하나를 선택하면 그에 맞는 Name과 Price가 출력됩니다.
HLOOKUP
문법
HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
- lookup_value : 찾을 값
- table_array : 조회할 범위
- row_index_num : 반환하려는 값이 있는 행 번호
- range_lookup : TRUE면 근사 일치, FALSE면 정확히 일치. 기본값은 TRUE
예제
HLOOKUP은 VLOOKUP과 거의 같습니다. 다른 점은 조회 방향이 가로라는 것입니다.
=HLOOKUP(B5,B1:I3,2,FALSE)
XLOOKUP
문법
XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
- lookup_value : 찾을 값
- lookup_array : 찾을 값이 있는 범위
- return_array : 반환할 값이 있는 범위
- if_not_found : 일치하는 값이 없을 경우 반환할 값. 기본값은 #N/A
- match_mode
- search_mode : 기본값은 1로, 첫 번째 항목부터 검색
예제
- No가 E2인 데이터의 Name을 출력합니다.
- 세 개의 인수만으로 값을 조회할 수 있습니다.
=XLOOKUP(E2,A2:A16,B2:B16)
- 하나의 수식으로 여러 개의 값을 가져올 수 있습니다.
=XLOOKUP(E2,A2:A16,B2:C16)
- 가로 방향 조회도 가능합니다.
=XLOOKUP(B5,B1:I1,B2:I2)
간단한 예제로, VBA로 매크로를 만들고 실행하는 과정을 살펴보겠습니다.(개발 도구 메뉴가 없는 경우 옵션 변경으로 추가할 수 있습니다.) 을 클릭합니다.(단축키는 Alt+F11입니다.) 다음과 같은 에디터 창이 나오는데... 을 클릭합니다. 코드를 넣을 수 있는 창이 나오는데... 다음 코드를 입력합니다. Sub SayHello() Sheet1.Range("A1").Value = "HELLO" End ...
엑셀 / 함수 / AVERAGE, AVERAGEIF, AVERAGEIFS / 산술평균 구하는 함수
개요 AVERAGE는 산술평균을 구하는 함수이다. 특정 조건에 맞는 값들의 산술평균을 구하고 싶다면 AVERAGEIF 또는 AVERAGEIFS 함수를 사용한다. AVERAGE 구문 AVERAGEA(value1, , ...) value1은 필수 요소이고, 이후의 value는 선택 요소이다. 평균을 구하려는 셀, 셀 범위 또는 값으로, 1개에서 255개까지 지정할 수 있다. 예제 예제 1 1, 2, 3의 산술평균을 구한다. 예제 2 1, 2의 산술평균을 구한다. 범위에 문자가 포함된 경우 무시한다. AVERAGEIF 구문 AVERAGEIF(range, criteria, ) range ...
엑셀 / VBA / 여러 시트의 내용을 하나의 시트에 모으는 방법
동일한 형식에 내용만 다른 여러 시트의 데이터를 하나로 합치는 방법을 알아본다. 예를 들어 다음과 같은 엑셀 문서가 있다고 하자. 첫 번째 시트는 합쳐진 데이터가 모일 시트이고, 나머지 세 개는 데이터가 있는 시트이다. 을 클릭한다.(만약 개발 도구 메뉴가 없다면 여기의 안내대로 개발 도구를 추가한다.) 을 클릭한다. 다음과 같이 코드를 입력한다. Sub Merge() ...
엑셀에서 검색을 하면 검색어를 포함한 셀을 찾습니다. 예를 들어 AB로 검색을 하면 AB가 제일 앞에 있든, 중간에 있는, 마지막에 있는 다 찾습니다. 만약 AB로 시작하는 셀을 찾고 싶을 때, A로 시작해서 B로 끝나는 셀을 찾고 싶을 때, AB로 끝나는 셀을 찾고 싶을 때는 어떻게 할까요? 그럴 때 사용하는 것이 와일드카드입니다. 와일드카드는 *과 ...
엑셀 / 메모 삽입하는 방법, 메모 삭제하는 방법, 메모 일괄 삭제하는 방법
엑셀의 메모 기능을 이용하면 셀에 대한 설명을 남길 수 있습니다. 복잡한 계산을 했거나 공동 작업을 할 때 유용한 기능입니다. 어떻게 메모를 삽입하고 삭제할 수 있는지 알아보겠습니다. 메모 삽입하는 방법 메모를 삽입하고자 하는 셀에 마우스를 올리고 우클릭을 합니다. 팝업 메뉴에서 을 클릭합니다. 메모 삽입 단축키는 입니다. 포스트잇처럼 생긴 작은 창이 생기고, 그 ...
행, 열, 워크시트 숨기기 자료를 입력하고 분석할 때, 너무 많은 데이터가 화면에 있으면 불편합니다. 또는 분석에는 필요하지만 화면에는 굳이 보이지 않아도 될 것들도 있습니다. 그럴 땐 숨기기 기능을 이용해서 화면에서 감출 수 있습니다. 어떻게 숨기는지, 숨겨놓은 것을 어떻게 다시 보이게 하는지 알아보겠습니다. 행과 열 숨기는 방법 행 숨기기 숨기려는 행을 선택한 후 마우스 우크릭합니다. 그리고 를 ...
엑셀에서 작업 후 저장할 때 여러 가지 형식의 파일로 저장할 수 있다. 기본값은 보통 인데... 로 설정 매크로를 많이 사용하여 기본 저장 파일 형식을 로 기본값을 설정 하고 싶을 수 있다. 이러한 설정은 에서 한다. 왼쪽에서 을 클릭한다. [다음 ...
페이지 나누기 표시 엑셀에서 인쇄 미리보기를 하거나 인쇄를 한 후 다시 워크시트로 돌아오면 점선이 생긴 걸 볼 수 있습니다. 인쇄되는 영역을 표시해주는 것으로, 페이지 나누기 표시라고 합니다. 인쇄를 목적으로 만들 때는 유용한 표시이지만, 경우에 따라서는 불편하거나 필요하지 않을 수 있습니다. 페이지 나누는 점선을 없애는 방법을 알아보겠습니다. 페이지 나누기 표시 없애기 페이지 나누기 표시는 옵션에서 표시 ...
엑셀 / 함수 / DATEDIF / 두 날짜 사이의 일수, 월수, 년수 등을 계산하는 함수
개요 DATEDIF는 두 날짜 사이의 일수, 월수, 년수 등을 계산하는 함수입니다. 구문 DATEDIF(start_date,end_date,unit) start_date : 시작하는 날짜입니다. end_date : 끝나는 날짜입니다. unit : 반환할 정보의 형식입니다. Y, M, D, YM, YD 등을 사용할 수 있습니다. Y는 년수, M은 월수, D는 일수, YM은 년도를 무시한 월수, YD는 년도를 무시한 일수를 반환합니다. start_date가 end_date보다 이후라면 #NUM!를 반환합니다. 예제 다음은 2018년 1월 15일부터 2019년 6월 5일까지의 ...
엑셀 / 삼각함수(사인, 코사인, 탄젠트 등)의 값 계산하기
개요 삼각함수에는 사인 함수, 코사인 함수, 탄젠트 함수, 코시컨트 함수, 시컨트 함수, 코탄젠트 함수가 있습니다. 엑셀에서 각 함수의 값을 구할 수 있는데, 함수 이름이 삼각함수의 표기법과 같습니다. 예를 들어 사인은 sin으로 나타내는데, 사인 값을 구하는 엑셀 함수도 SIN입니다. 구문 사인 함수 SIN(number) 코사인 함수 COS(number) 탄젠트 함수 TAN(number) 코시컨트 함수 CSC(number) 시컨트 함수 SEC(number) 코탄젠트 함수 COT(number) 각도의 단위 number에는 각도를 입력하는데, 단위는 라디안입니다. 45도에 대한 ...