엑셀 / 함수 / 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)
엑셀 / 함수 / MAX, MIN, MAXIFS, MINIFS / 최댓값, 최솟값 구하는 함수
MAX, MIN 개요 MAX는 최댓값을 구하는 함수, MIN은 최솟값을 구하는 함수입니다. 구문 MAX(number1, , ...) MIN(number1, , ...) number1, number2, ... : number1은 필수 요소이고, 이후의 number는 선택 요소입니다. 함수를 입력하지 않고 의 오른쪽 끝에 있는 메뉴를 이용할 수도 있습니다. 예제 참고 k번째로 큰 값을 구하고 싶다면 LARGE 함수를 사용합니다. k번째로 작은 값을 구하고 싶다면 SMALL 함수를 사용합니다. 메뉴에는 최대값, 최소값이라고 되어 있지만, 표준말는 최댓값, ...
엑셀 / 중복된 값 찾는 방법, 중복 항목 제거하는 방법
엑셀로 자료를 취합하고 정리하다보면 중복된 값들이 생기기도 합니다. 그런 중복 항목을 찾기 위해 눈으로 검토할 필요는 없습니다. 엑셀에는 중복된 항목을 찾거나, 찾아서 제거하는 기능이 있기 때문입니다. 중복 값 찾기 중복 값을 찾을 열을 선택합니다. 을 클릭합니다. 을 클릭하면... 중복된 값이 강조됩니다. 조건부 서식에서 를 ...
엑셀 / 함수 / RADIANS, DEGREES / 도를 라디안으로, 라디안을 도로 변환하는 함수
개요 RADIANS는 도를 라디안으로, DEGREES는 라디안을 도로 변환하는 함수입니다. 구문 RADIANS(angle) angle : 필수 요소입니다. 변환할 도 단위의 각도를 입력합니다. DEGREES(angle) angle : 필수 요소입니다. 변환할 라디안 단위의 각도를 입력합니다. 파이는 PI() 함수를 이용합니다. 예제
개요 NPV 함수는 주기적인 현금흐름에 대한 순현재가치(NPV : Net Present Value)를 계산하는 함수입니다. 현금흐름은 정기적이어야 하지만, 금액은 일정할 필요가 없습니다. 구문 NPV(rate,value1,,...) rate 필수 요소로 할인율입니다. value1, value2, ... value1은 필수 요소이고, value2 부터는 선택 요소입니다. 각 시점의 현금흐름으로, 1개에서 254개까지 지정할 수 있습니다. 현금유입은 양수로, 현금유출은 음수로 입력합니다. value1은 1기간 말의 현금흐름, value2는 2기간 말의 현금흐름, value3은 3기간 말의 현금흐름입니다. ...
엑셀 / 피벗 테이블 / 여러 범위로 다중 피벗 테이블 만드는 방법
피벗 테이블은 보통 하나의 범위 또는 표로 만듭니다. 따라서 여러 개의 범위 또는 표로 피벗 테이블을 만들기 위해서는 하나로 합치는 과정을 거치는데, 그 과정이 번거롭다면 여러 범위(다중 통합 범위)로 피벗 테이블을 만들 수도 있습니다. 어떤 방식이 편한지는 데이터에 따라 다르겠죠. 피벗 테이블/피벗 차트 마법사 메뉴 추가 다중 통합 범위 피벗 테이블을 만들기 ...
엑셀 / 함수 / INT, TRUNC / 소수를 정수 만드는 함수
개요 소수를 정수로 만드는 함수에는 INT 함수와 TRUNC 함수가 있습니다. 정수로 만든다는 점은 같지만, 정수로 만드는 방식에는 차이가 있으므로 사용에 주의를 해야 합니다. 차이점 1 INT 함수는 작거나 같은 정수로 만듭니다. 예를 들어 INT(2.6) 은 2.6보다 작은 정수인 2가 되고, INT(-2.6) 은 -2.6보다 작은 정수인 -3이 됩니다. TRUNC 함수는 소수점 아래의 수를 지워버립니다. 예를 들어 TRUNC(2.6) 은 소수점 아래의 수 6을 ...
예를 들어 B2 셀에 있는 abc@manualfactory.net에서 @ 앞 부분을 추출하고 싶다면 다음과 같이 합니다. =LEFT(B2,FIND("@",B2)-1)
기본 검색 대상 엑셀은 찾기 기능이 있습니다. Ctrl+F로 찾기 창을 연 후 검색어를 입력하고 검색하면 됩니다. 검색을 하면 그 대상이 필요한데, 기본 설정은 현재 워크시트입니다. 현재 워크시트에 검색어가 있으면 그 위치로 이동하고, 없다면 찾을 수 없다고 알려줍니다. 다른 워크시트에 그 검색어가 있어도 검색 대상이 현재 워크시트이므로 없다고 나옵니다. 모든 워크시트를 대상으로 검색하기 검색 대상을 변경해서 ...
셀 안에서 줄바꿈을 하고 싶다면 Alt + Enter를 누릅니다. 줄바꿈한 내용을 수식입력줄에서 보고 싶다면, 오른쪽 끝에 있는 아이콘을 클릭합니다.
엑셀 / 함수 / 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 ...