업무의 효율을 높이는 엑셀(Excel)의 7가지 함수와 단축키는?

Frank So
4일 전
3분 분량

엑셀(Excel)을 제대로 알지 못한채로 사용하면 아까운 시간을 낭비할 수 있습니다. VLOOKUP, 데이터 필터링 및 텍스트 조작과 씨름하는 데 지쳤다면 주요 엑셀 기능과 단축키를 배워 사용해 보는 것이 어떨까요? 이 글에서는 복잡한 데이터 조회 및 동적 필터링에서 빠른 텍스트 조작 및 신속한 재무 계산에 이르기까지 스프레드시트 작업을 즉시 간소화하도록 설계된 강력한 기능을 선별했습니다. 그리고 이 글의 마지막에는 부록으로 시간을 절약해 주는 키보드 단축키 모음도 공유해 보았습니다.

XLOOKUP 함수

XLOOKUP은 제가 사용하는 대부분의 케이스에서 INDEX/MATCH 및 VLOOKUP을 대체했습니다. VLOOKUP과는 달리 열의 개수를 계산할 필요가 없습니다. 그리고, 조회 및 반환 배열을 직접 지정할 수 있습니다. 게다가, VLOOKUP함수가 할 수 없는 열의 왼쪽의 값을 조회할 수 있습니다.

=XLOOKUP(검색할 값, 검색할 범위, 반환할 범위)

온라인 스토어의 판매 데이터베이스를 관리하고 있으며 스프레드시트에 '제품 디테일' (제품 ID, 제품 이름, 카테고리 및 가격 열)과 '판매 데이터' (주문 ID, 제품 ID 및 판매 수량 열)라는 두 개의 테이블이 있다고 가정해 보겠습니다. 이제 ‘제품 이름’ 및 ‘가격’을 ‘판매 데이터’ 테이블에 추가하려고 합니다.

'판매 데이터' 테이블에서 ‘제품 이름’ 및 ‘가격’에 대한 두 개의 열을 만들고 XLOOKUP 함수를 사용하여 조회 배열에서 제품 ID를 찾고 해당 제품 이름을 반환하고 판매된 각 제품의 가격을 가져올 수 있습니다.

PMT 함수

PMT 함수는 대출에 대한 정기 상환액을 계산하도록 도와줍니다 (일정한 상환액과 이자율의 가정하에). 이 함수는 Excel에서 재무 계산을 위한 기본 도구입니다. 대출 상각을 만들거나, 투자를 계획하거나, 재무 분석을 수행하거나, 임대료를 계산할 때 PMT를 사용하여 작업을 수행합니다.

연간 이자가 5%인 30년 주택담보대출을 위해 2억을 대출받았다고 가정해 보겠습니다. 아래 공식을 사용하여 월별 지불액을 계산할 수 있습니다. 월별 지불액이므로 연간 이자와 대출 기간을 연단위에서 월단위로 (연간 이자는 월별이자를 셈하기 위해12로 나누고 대출 기간은 총 개월의 수를 셈하기 위해12를 곱함) 환산하게 되죠.

=PMT(0.05/12, 30*12, 200000000)

참고로 계산의 결과값은 음수로 나타내어집니다 (지출이므로).

FILTER 함수

FILTER 기능은 보고 및 데이터 분석을 위한 강력한 도구입니다. INDEX, MATCH 및 IF의 복잡한 조합보다 사용하기 쉬운 경우가 많습니다. 여러 조건이 있는 복잡한 필터링 논리를 처리할 수 있으며 구문은 비교적 읽고 이해하기 쉽습니다.

Excel을 사용하여 팀의 작업을 추적하는 프로젝트 관리자라고 가정해 봅시다. 태스크, 담당자, 상태 및 기한과 같은 열이 있는 데이터베이스를 생성했습니다. 이제 현수가 어떤 작업을 수행하고 있는지 확인하려고 합니다.

=FILTER(A2:D10, B2:B10="현수")

Excel은 담당자 열이 현수인 행만 포함하는 테이블을 표시합니다. 마찬가지로 '진행 중' 작업, '완료' 작업을 필터링하고 아무 작업도 없을 때 메시지를 표시할 수 있습니다.

TEXTBEFORE 와 TEXTAFTER 함수

이 두 Excel 함수를 사용하면 지정된 구분 기호 앞이나 뒤에 표시되는 텍스트를 추출할 수 있습니다. 이메일 주소에서 이름, 성 및 도메인 이름을 추출할 수 있으며 확장자가 없는 파일 이름도 추출할 수 있습니다.

TEXTBEFORE = TEXT + BEFORE (지정된 텍스트 이전의 값을 반환)
TEXTAFTER = TEXT + AFTER (지정된 텍스트 이후의 값을 반환)

name@email.com 을 포함하는 셀이 있고 name을 추출하려는 경우, =TEXTBEFORE(B1, "@")를 실행하면 name이라는 값이 반환됩니다. 마찬가지로 =TEXTAFTER(B1, "@")를 실행하여 email.com의 값을 추출할 수 있습니다.

SEQUENCE 함수

SEQUENCE 함수는 배열에 일련의 숫자를 생성합니다. 이 함수는 동적 리스트를 만들고, 날짜를 생성하고, 반복적인 작업을 자동화하는 데 매우 유용합니다.

=SEQUENCE(행의 수, [열의 수], [시작 값], [건너 뛰는 값])

예를 들어, 1에서 20까지의 숫자가 있는 행을 만들려면 =SEQUENCE(20)를 입력하기만 하면 작업을 완료할 수 있습니다. =SEQUENCE(5, 5)를 입력하여 5x5 숫자 테이블을 만들 수도 있습니다. 이 함수의 가능성은 무한하지요?

[SEQUENCE 예제 3] 5x5 테이블을 숫자 2부터 4씩 건너 뛰어 채우기

UNIQUE 함수

UNIQUE 함수를 사용하여 범위 또는 배열에서 고유 값 목록을 추출할 수 있습니다. 중복을 제거하고 고유한 목록을 만들려는 경우에 매우 유용합니다. 한 열에 수백 개의 고객 이름이 있는 거대한 Excel 스프레드시트가 있다고 가정합니다. 이제 목록에서 고유 고객을 추출하려고 합니다.

고유 목록을 표시할 빈 셀(C1)을 선택한 후, =UNIQUE(A1:A100)를 사용하면 Excel에서 C1 아래 셀에 고유한 고객 이름을 반환합니다.

SWITCH 함수

SWITCH는 값 목록에 대해 단일 값을 평가하고 그에 따라 결과를 반환하는 강력한 논리 함수입니다. A1 셀에 숫자 월(1-12)이 있다고 가정합니다. 이제 아래 수식을 사용하여 월 이름으로 변환할 수 있습니다.

=SWITCH(A1, 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "오류 값")