남익의 생활

[엑셀 함수] INDEX + MATCH 조합 사용법 – 모든 버전에서 되는 왼쪽 조회·가로세로 조회

환경 : Microsoft 365 / Excel 2010 이상XLOOKUP을 쓸 수 없는 Excel 2019 이하에서도 VLOOKUP의 한계(왼쪽 조회 불가, 열 번호 세기)를 피하는 방법이 INDEX + MATCH 조합입니다. 두 함수를 따로 이해하면 조합은 어렵지 않습니다.1. 각 함수의 역할=INDEX(array, row_num, [column_num])=MATCH(lookup_value, lookup_array, [match_type])함수하는 일예INDEX범위에서 n번째 값을 가져옴=INDEX(C3:C7, 3) → C3:C7의 3번째 값MATCH찾는 값이 범위에서 몇 번째인지 알려 줌=MATCH("A03", B3:B7, 0) → 3MATCH의 세 번째 인수 0은 정확히 일치입니다. 생략하면 유사..

환경 : Microsoft 365 / Excel 2021 이상고급 필터(123번 글)는 원본이 바뀔 때마다 다시 실행해야 했습니다. Microsoft 365의 FILTER, SORT, UNIQUE는 수식 하나로 추출·정렬·중복 제거를 하고, 원본이 바뀌면 결과도 자동으로 바뀝니다.1. 먼저 알아둘 점 : 스필(분산)이 함수들은 결과가 여러 칸입니다. 첫 칸에만 수식을 입력하면 아래·옆으로 결과가 자동으로 펼쳐지며, 이를 스필이라고 합니다. 펼쳐진 범위는 F2#처럼 첫 셀 뒤에 #을 붙여 다른 수식에서 참조할 수 있습니다.2. 예제 데이터 (가계부 사용내역) ABCD1일시금액구분카테고리22026-10-0112,000생활비식비32026-10-0245,000내용돈쇼핑42026-10-038,500생활비교통520..

환경 : Microsoft 365 / Excel 2010 이상D-day, 근속 기간, 이번 달 말일, 근무일 수처럼 날짜 계산은 실무와 가계부 모두에서 자주 쓰입니다. 자주 쓰는 날짜 함수를 목적별로 정리합니다.1. 먼저 알아둘 점 : 날짜는 숫자엑셀은 날짜를 1900-01-01을 1로 하는 일련번호로 저장합니다. 그래서 날짜끼리 빼면 며칠 차이가 나오고, 날짜에 숫자를 더하면 며칠 뒤가 됩니다. 결과가 45000 같은 숫자로 보이면 셀 서식만 '날짜'로 바꾸면 됩니다.2. 함수 한눈에 보기함수하는 일예 (A2 = 2026-10-13)결과TODAY()오늘 날짜=TODAY()파일을 연 날짜YEAR / MONTH / DAY연·월·일 꺼내기=MONTH(A2)10DATE(연, 월, 일)날짜 만들기=DATE(20..

환경 : Microsoft 365 / Excel 2010 이상주문번호에서 연도만, 이메일에서 아이디만, 전화번호에서 뒷자리만 뽑아야 할 때 쓰는 기본 텍스트 함수 LEFT, RIGHT, MID, LEN을 정리합니다.1. 사용 방법함수형식하는 일LEFT=LEFT(text, [num_chars])왼쪽에서 n글자RIGHT=RIGHT(text, [num_chars])오른쪽에서 n글자MID=MID(text, start_num, num_chars)n번째 글자부터 m글자LEN=LEN(text)글자 수 (공백 포함)LEFT·RIGHT에서 글자 수를 생략하면 1글자만 가져옵니다.2. 예제 데이터 ABC1주문번호전화번호이메일22026-SE-0153010-1234-5678namik@example.com32025-BS-004..

환경 : Microsoft 365 / Excel 2021 이상VLOOKUP 사용법(124번 글)에서 VLOOKUP은 찾는 값이 첫 열에 있어야 하고, 열 번호를 세야 한다는 불편함이 있었습니다. XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정해 이런 제약이 없는 새 찾기 함수입니다.1. 사용 방법=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_mode0 = 정확히 일치(기본) / -1..

환경 : Microsoft 365 / Excel 2010 이상VLOOKUP으로 못 찾은 값, 0으로 나눈 계산처럼 수식 결과에 #N/A, #DIV/0! 같은 오류가 섞이면 표가 지저분해지고 합계도 오류가 됩니다. IFERROR는 오류가 날 때만 내가 정한 값으로 바꿔 주는 함수입니다.1. 사용 방법=IFERROR(value, value_if_error)인수의미value원래 계산할 수식value_if_error오류일 때 대신 보여 줄 값 (0, "", "없음" 등)수식이 정상이면 그 결과를 그대로, 오류면 두 번째 인수를 표시합니다.2. 엑셀 오류 종류오류원인#N/A찾는 값이 없음 (VLOOKUP, MATCH, XLOOKUP 등)#DIV/0!0 또는 빈 셀로 나눔#VALUE!숫자가 와야 할 곳에 텍스트, ..

환경 : Microsoft 365 / Excel 2021 이상 (Excel 2019 이하 입력법은 본문 3번 참고)세로로 정리된 데이터를 가로로, 가로 데이터를 세로로 바꿔야 할 때가 있습니다. TRANSPOSE는 범위의 행과 열을 바꿔 주는 함수로, 원본이 바뀌면 결과도 자동으로 바뀝니다.1. 사용 방법=TRANSPOSE(array)인수는 바꿀 범위 하나뿐입니다. 5행 1열 범위를 넣으면 1행 5열로, 1행 5열이면 5행 1열로 결과가 나옵니다.2. 예제예제 ① 세로 → 가로A1:A5에 이름이 세로로 있을 때, 결과를 놓을 첫 셀(예 : C1)에 입력합니다.=TRANSPOSE(A1:A5)Microsoft 365에서는 수식을 한 칸에만 입력해도 결과가 오른쪽으로 자동으로 펼쳐집니다. 이를 스필(분산)이라..

환경 : Microsoft 365 / Excel 2010 이상FIND 함수(131번 글)처럼 글자의 위치를 찾는 함수지만, SEARCH는 대소문자를 구분하지 않고 와일드카드(*, ?)를 쓸 수 있습니다. 이번에는 SEARCH와 MID를 조합해 주소에서 '동' 이름만 뽑아 보겠습니다.1. 사용 방법=SEARCH(find_text, within_text, [start_num])구분FINDSEARCH대소문자구분함구분 안 함와일드카드 *, ?사용 불가사용 가능못 찾으면#VALUE!#VALUE!2. 예제 : 주소에서 동 이름 추출D열 주소에서 '○○구' 다음에 오는 동 이름(예 : 금암동)을 꺼냅니다.[샘플 데이터][동 추출 결과]① 시작 위치 : '구 ' 다음 글자=SEARCH("구 *동", D3)'구 *동'은..

환경 : Microsoft 365 / Excel 2010 이상CHOOSE는 번호(1, 2, 3 …)에 따라 미리 나열한 값 중 하나를 골라 돌려주는 함수입니다. 요일 번호를 요일 이름으로 바꾸거나, 주민등록번호로 성별을 표시할 때 IF를 여러 번 쓰지 않고 간단히 처리할 수 있습니다.1. 사용 방법=CHOOSE(index_num, value1, [value2], …)인수의미index_num몇 번째 값을 고를지 (1 ~ 254)value1, value2, …고를 값 목록 (최대 254개)index_num이 1이면 value1, 2이면 value2를 돌려줍니다. 소수를 넣으면 소수점 아래를 버린 정수로 처리합니다. (2.7 → 2)2. 예제 : 주민등록번호로 성별 표시주민등록번호 뒷자리 첫 숫자가 1, 3이..

환경 : Microsoft 365 / Excel 2010 이상셀에 특정 글자가 들어 있는지 확인하고 싶을 때가 있습니다. FIND 함수로 글자의 위치를 찾고, ISNUMBER와 IF를 더해 '포함 / 불포함'처럼 보기 좋게 표시해 보겠습니다.1. 사용 방법=FIND(find_text, within_text, [start_num])인수의미find_text찾을 문자within_text찾을 대상 (셀 또는 텍스트)start_num몇 번째 글자부터 찾을지. 생략하면 1찾으면 처음 나오는 위치(숫자)를 돌려주고, 없으면 #VALUE! 오류가 납니다.2. 예제B열의 단어에 소문자 a가 들어 있는지 확인합니다.[샘플 데이터]① FIND로 위치 찾기=FIND("a", B3)[FIND 결과]'banana'라면 첫 번째 ..