엑셀 함수 정리 마무리
어제 블로그에 엑셀 관련 글을 쓰면서 다음 게시글에 소개하겠다고 했던 함수들이 있었다. 그 함수들을 마지막으로 엑셀 함수 설명은 끝내려고한다. 여전히 수많은 함수가 남았지만, 잘쓰지 않는 함수이다.
저번 시간에 소개하기로 했던 함수 중 행번호를 출력하는 ROW와 열번호를 출력하는 COLUMN이라는 함수가 있는데, 이 함수들은 너무 간단하다.
1. ROW, COLUMN
사용법
=ROW() -> 함수를 입력한 셀의 행번호 출력
=ROW(C10) -> C10의 행번호 출력(10이 출력됨)
=COLUMN() -> 함수를 입력한 셀의 열번호 출력
=COLUMN(C10) -> C10의 열번호 출력(3이 출력됨)
※열번호를 출력할 때 A = 1, B = 2... Z = 26 이런식이다.
1번 함수들에 대해서는 부가설명이 필요할꺼 같지 않아 다음 파트로 넘어가겠다.
2. VLOOKUP, HLOOKUP
2-1. VLOOKUP
VLOOKUP은 테이블에서 첫 열값을 기준으로 찾고자 하는 값과 같은 행에 있는 조회 값을 이용하여 탐색한 뒤 찾고자하는 값을 반환받고자할 때 사용한다.
사용법은
=VLOOKUP(조회 값, 조회 값과 찾고자하는 값을 포함한 범위, 찾고자 하는 값의 순서(몇 번째에 있는지), (TRUE나 공란 -> 대략적 일치, FALSE -> 완전 일치))
예를들어 그림에 있는 테이블에서 '이민서'라는 이름을 가진 직원의 직급을 G4에 반환시키는 법은 두가지가 있다.
=VLOOKUP("이민서", B2:D7, 3, FALSE) -> "이민서"와 일치하는 이름을 범위의 첫번째 열의 범위인 B2:B7에서 탐색을 하고 탐색에 성공하면(실패할 경우 N/A에러를 반환하는데 이 때 저번에 배운 IFERROR나 IFNA로 감싸서 해당 셀을 참조하는 다른 셀들이 마비되는 것을 방지한다.) "이민서"의 값이 있는 행에서 3번째(B부터 시작해서 첫번째, C가 두번째, D가 세번째)에 있는 값을 반환한다.("과장" 반환)
=VLOOKUP(G3, B2:D7, 3, FALSE) -> G3에 있는 값(="이민서")와 일치하는 이름을 범위의 첫번째 열의 범위인 B2:B7에서 탐색을 하고 탐색에 성공하면 "이민서"의 값이 있는 행에서 3번째에 있는 값을 반환한다.("과장" 반환)
결국 첫번째 인수에 어떤 방식으로 넣든 찾고자 하는 값이 무엇인지만 전달된다면, 정상적으로 구동된다.
2-2. HLOOKUP
HLOOKUP은 VLOOKUP의 정의에서 행과 열이 반대되었다는 것을 제외하면 똑같은 함수이다.
앞서,
VLOOKUP은 테이블에서 첫 열값을 기준으로 찾고자 하는 값과 같은 행에 있는 조회 값을 이용하여 탐색한 뒤 찾고자하는 값을 반환받고자할 때 사용한다. 라고 언급하였다.
HLOOKUP은 이 정의에서 행과 열만 반대니까
HLOOKUP은 테이블에서 첫 행값을 기준으로 찾고자 하는 값과 같은 열에 있는 조회 값을 이용하여 탐색한 뒤 찾고자하는 값을 반환받고자할 때 사용한다. 라고 정의내릴 수 있을 것이다.
사용법은 VLOOKUP과 마찬가지로
=HLOOKUP(조회 값, 조회 값과 찾고자하는 값을 포함한 범위, 찾고자 하는 값의 순서(몇 번째에 있는지), (TRUE나 공란 -> 대략적 일치, FALSE -> 완전 일치)) 이다.
그림에서 '이민서'라는 이름을 가진 직원의 직급을 C7에 반환시키는 법은 두가지가 있다.
=HLOOKUP("이민서", B2:G4, 3, FALSE) -> "이민서"와 일치하는 이름을 범위의 첫번째 행의 범위인 B2:G2에서 탐색을 하고 탐색에 성공하면 "이민서"의 값이 있는 열에서 3번째(2행부터 시작해서 첫번째, 3행이 두번째, 4행이 세번째)에 있는 값을 반환한다.("과장" 반환)
=HLOOKUP(C6, B2:G4, 3, FALSE) -> C6에 있는 값(="이민서")와 일치하는 이름을 범위의 첫번째 열의 범위인 B2:G2에서 탐색을 하고 탐색에 성공하면 "이민서"의 값이 있는 열에서 3번째에 있는 값을 반환한다.("과장" 반환)
3. INDEX, MATCH
MATCH는 VLOOKUP이나 HLOOKUP과 같이 값을 탐색하는 함수긴 하지만, 위치에 대한 상대적 순서를 반환하기 때문에, 값을 반환하기 위해서는 INDEX라는 함수와 같이 사용되어야한다.
INDEX는 범위에서 상대적인 행과 열에 있는 값을 반환한다.
위 그림에서 =MATCH(E4,A2:A7)(E4대신 "박진태"를 넣어도 동일한 결과가 나온다.)을 할 경우 박진태는 A2부터 시작해서 3번째에 위치하기 때문에 3을 반환한다.
하지만, 사용자 입장에서 3을 반환해봤자 딱히 쓸모가없다.
그래서 우리는 INDEX라는 함수를 사용하는 건데, INDEX(범위,상대적인 행, 상대적인 열)을 입력하면 해당 셀에 있는 데이터를 출력한다.
예를들어
=INDEX(A2:B7,3,2) -> A2를 1행 1열로 가정했을 때 3행 2열이므로 B4에 있는 35를 출력한다.
=INDEX(A2:C7,2,3) -> A2를 1행 1열로 가정했을 때 2행 3열이므로 C3에 있는 "부장"을 출력한다.
이를 이용해 숫자만 출력하는 MATCH함수로 행과 열을 반환시키고 INDEX로 반환된 행과 열의 위치를 찾아 해당 셀의 값을 반환시킬 순 없을까?
물론 있다.
예를들어 그림처럼 박진태의 나이를 출력하고 싶다면 코드는
=INDEX(A2:C7, MATCH(E4, A2:A7), MATCH(F3, A1:C1))이다.
하나씩 뜯어보자면, MATCH(E4, A2:A7)은 A2를 1행으로 하여 E4가 몇 행에 있는지 반환하는 코드이고 E4인 "박진태"는
A2로부터 3번째 행에 있으므로 3을 출력한다.
그리고 MATCH(F3, A1:C1)은 A1을 1열로 하여 F3가 몇 열에 있는지 코드이고 F3인 "나이"는 A1으로부터 2번째 열에 있으므로 2를 출력한다.
그러면 처음 입력한 코드는
=INDEX(A2:C7,3,2)와 같은데, A2를 1행 1열로 두었을 때 3행 2열은 B4를 의미하고 B4의 값인 '35'를 반환한다.
4. XLOOKUP
XLOOKUP은 VLOOKUP과 마찬가지로 열에서 값을 탐색하여 해당 행에 있는 반환받고자하는 값을 반환한다.
하지만, XLOOKUP은 VLOOKUP이 하지못했던 기능들을 보완한다.
※
VLOOKUP을 사용했을 때 불가능한 것 1. 반환하고자 하는 값이 탐색하는 값의 왼쪽에 있으면 반환하지 못했다.
VLOOKUP을 사용했을 때 불가능한 것 2. 하나의 값만 반환하고 같은 행에 있는 여러개의 데이터를 반환하지 못했다.
XLOOKUP의 사용법은,
=XLOOKUP(탐색할 값, 탐색할 범위, 반환할 범위, 유용한 값을 못찾을 경우 대체 반환 텍스트(선택), 0 -> 정확히 일치하는 값 반환 -1 -> 못찾을 경우 다음 작은 값 반환 1 -> 못찾을 경우 다음 큰 값 반환 2 -> 와일드카드(참조 1) 일치(*,?,~)(선택), 사용자 검색 모드 1 -> 첫 번째부터 탐색 -1 -> 마지막 항목부터 탐색, 2 -> 오름차순으로 정렬된 데이터를 이진탐색(알고리즘 시간에 다루겠다.)으로 탐색, -2 -> 내림차순으로 정렬된 데이터를 이진탐색으로 탐색(선택))
일단 저게 정의긴한데 그냥 필수 인수만 입력하면,
=XLOOKUP(탐색할 값, 탐색할 범위, 반환할 범위) 정도이고 다음 인수인 탐색을 실패했을때 대체 반환 텍스트 정도 추가될꺼같다.
어쨌든,
XLOOKUP은 탐색할 범위와 반환할 범위를 따로 둠으로 부터 VLOOKUP의 단점을 보완하였고 위의 그림4에서 "박진태"의 나이와 직급을 동시에 출력하는 코드는
=XLOOKUP(E4(또는 "박진태"), A2:A7, B2:C7)이고,
반환하고자 하는 범위를 나이가 입력되어있는 B행과 직급이 입력되어있는 C행으로 잡았기 때문에 A4에서 "박진태"를 발견할 경우 동일한 행인 B4와 C4가 나란히 반환된다.
다음시간에는 피벗테이블과 필터, 차트, 조건부서식에 대해서 다루겠습니다.
설명이 조금 미숙한점에 대해 개선하고있으며, 피벗테이블, 필터, 차트, 조건부서식에 대한 설명이 끝나면 VBA와 PYTHON을 이용하여 간단하고 유용한 프로그램 만드는 글을 업로드할 것이고 프로그래밍의 기초, 프로그래밍 언어들, 알고리즘, 자료구조, 이산수학 등에 대해서도 업로드 할 것입니다. 그리고 그 때는 설명을 조금 더 다듬어서 더 쉽게 이해할 수 있도록 만들어보겠습니다. 항상 감사합니다.
참조 1. 와일드 카드, 마이크로소프트, https://support.microsoft.com/ko-kr/office/%EA%B2%80%EC%83%89%EC%97%90%EC%84%9C-%EC%99%80%EC%9D%BC%EB%93%9C%EC%B9%B4%EB%93%9C-%EB%AC%B8%EC%9E%90-%EC%82%AC%EC%9A%A9-ef94362e-9999-4350-ad74-4d2371110adb