Calculator Tools
모든 전문가가 알아야 할 필수 스프레드시트 공식
VLOOKUP, INDEX/MATCH, SUMIF, 피벗 로직, 데이터 정리 기법 등 Excel과 Google Sheets의 가장 강력한 공식을 마스터하세요.
7분 읽기
스프레드시트는 세계에서 가장 많이 사용되는 프로그래밍 환경입니다. 수백만 명의 전문가들이 3년 전 튜토리얼에서 어렴풋이 기억해 둔 공식을 바탕으로 중요한 결정을 내리고 있습니다. 핵심 공식을 제대로 익히면 스프레드시트가 단순한 표에서 강력한 데이터 분석 도구로 탈바꿈합니다.
공식의 구조
모든 공식은 =으로 시작하며 다음을 참조할 수 있습니다:
- 셀 참조:
A1,B2:D10(상대 참조) 또는$A$1,$B$2:$D$10(절대 참조) - 이름 범위:
=SUM(B2:B100)대신=SUM(Revenue) - 함수:
SUM,VLOOKUP,IF,TEXT등 - 상수: 숫자, 따옴표 안의 텍스트,
TRUE/FALSE
상대 참조 vs. 절대 참조:
공식을 복사하면 상대 참조는 위치에 따라 이동합니다. $가 붙은 절대 참조는 고정됩니다.
=B2*C2 → 아래로 복사하면 =B3*C3, =B4*C4...로 변경됨
=B2*$C$1 → C1은 고정(예: 세율), B2는 이동
조회 공식
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
테이블의 첫 번째 열에서 값을 찾아 다른 열의 값을 반환합니다:
=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)
A2— 검색할 값$E$2:$G$100— 테이블 범위 ($로 고정)2— 테이블의 2번째 열 반환FALSE— 정확히 일치 (조회 시 항상FALSE사용)
VLOOKUP의 한계: 왼쪽에서 오른쪽으로만 조회할 수 있으며, 열을 삽입하면 인덱스 번호가 바뀝니다.
INDEX / MATCH (더 나은 대안)
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
VLOOKUP보다 유연하며 모든 방향으로 조회 가능합니다:
=INDEX($G$2:$G$100, MATCH(A2, $E$2:$E$100, 0))
E열이 A2와 일치하는 행의 G열 값을 반환합니다. 열을 삽입해도 공식이 깨지지 않습니다.
XLOOKUP (최신 Excel / Google Sheets)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
두 함수를 모두 대체하는 현대적인 방식:
=XLOOKUP(A2, $E$2:$E$100, $G$2:$G$100, "Not found")
구문이 간결하고, 값을 찾지 못한 경우를 우아하게 처리하며, 모든 방향으로 작동합니다.
조건부 공식
IF
=IF(condition, value_if_true, value_if_false)
=IF(B2>1000, "High", "Low")
=IF(C2="", "Missing", C2)
중첩 IF (IFS 사용 권장)
=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))
-- IFS로 더 깔끔하게:
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")
조건에서 AND / OR 사용
=IF(AND(B2>100, C2="Active"), "Priority", "Normal")
=IF(OR(D2="VIP", E2>10000), "Discount", "")
집계 공식
SUMIF / SUMIFS
조건을 충족하는 값의 합계를 구합니다:
=SUMIF(range, criteria, sum_range)
=SUMIF(B:B, "Electronics", C:C) -- B = "Electronics"인 행의 C 합계
-- 여러 조건:
=SUMIFS(C:C, B:B, "Electronics", D:D, ">2025-01-01")
COUNTIF / COUNTIFS
=COUNTIF(A:A, "Active") -- A = "Active"인 행 수
=COUNTIF(B:B, ">1000") -- B > 1000인 행 수
=COUNTIFS(A:A, "Active", C:C, "US") -- 두 조건 모두 충족
AVERAGEIF
=AVERAGEIF(B:B, "Electronics", C:C) -- B = "Electronics"인 행의 C 평균
텍스트 공식
텍스트 결합
=CONCATENATE(A2, " ", B2) -- 구식 문법
=A2 & " " & B2 -- 현대적 문법 (권장)
=TEXTJOIN(", ", TRUE, A2:A10) -- 구분자로 결합, 빈 셀 건너뜀
텍스트 추출
=LEFT(A2, 3) -- 앞에서 3글자
=RIGHT(A2, 4) -- 뒤에서 4글자
=MID(A2, 3, 5) -- 3번째 위치부터 5글자
=LEN(A2) -- 전체 문자 수
=FIND("@", A2) -- 문자열에서 @의 위치
텍스트 정리
=TRIM(A2) -- 앞뒤 공백 제거
=UPPER(A2) / =LOWER(A2) -- 대소문자 변환
=PROPER(A2) -- 첫 글자 대문자
=SUBSTITUTE(A2, " ", "_") -- 공백을 밑줄로 교체
=CLEAN(A2) -- 인쇄 불가 문자 제거
날짜 및 시간 공식
=TODAY() -- 오늘 날짜
=NOW() -- 현재 날짜 및 시간
=YEAR(A2) / =MONTH(A2) / =DAY(A2) -- 날짜 구성 요소 추출
=DATEDIF(A2, B2, "D") -- 두 날짜 사이의 일 수
=DATEDIF(A2, B2, "M") -- 두 날짜 사이의 완전한 개월 수
=DATEDIF(A2, B2, "Y") -- 완전한 연 수 (나이 계산에 유용)
=NETWORKDAYS(A2, B2) -- 두 날짜 사이의 근무일 수 (주말 제외)
=EDATE(A2, 3) -- A2로부터 3개월 후 날짜
=EOMONTH(A2, 0) -- A2가 속한 달의 마지막 날
데이터 처리: 실전 패턴
UNIQUE로 중복 제거 (최신 기능)
=UNIQUE(A2:A100) -- 고유 값 목록
FILTER로 동적 배열 구성
=FILTER(A2:C100, B2:B100="Active") -- B = "Active"인 행
=FILTER(A2:C100, (B2:B100="Active")*(C2:C100>1000)) -- 두 조건 모두
SORT로 동적 정렬
=SORT(A2:C100, 2, -1) -- 2번째 열 기준 내림차순 정렬
데이터 변환 및 내보내기
스프레드시트 작업이 끝나고 다른 시스템으로 데이터를 옮겨야 할 때:
- Excel to CSV 변환 도구로 범용 호환성을 위한 CSV 내보내기
- CSV Merger로 여러 CSV 파일 병합
- JSON to Excel로 API 활용을 위한 JSON 변환 (양방향 지원)
자주 하는 실수
- 공식에 값을 직접 입력 — 셀 참조를 사용하세요.
=B2*0.08은 나중에 무슨 값인지 알 수 없지만, "세율"이라고 레이블된 D1을 참조하는=B2*$D$1은 자체적으로 설명이 됩니다. - 복사할 때 참조를 고정하지 않음 —
$를 빠뜨리면 공식이 잘못된 셀을 참조하게 됩니다. - VLOOKUP에서
TRUE사용 (근사 일치) — 데이터가 정렬되어 있어야 하며, 정렬되지 않은 경우 예상치 못한 결과가 나옵니다. - 순환 참조 — 자기 자신(또는 자신을 참조하는 셀)을 참조하는 공식입니다. Excel이 경고를 표시합니다.
- 데이터 형식 혼용 — 숫자를 텍스트로 저장하면(셀 왼쪽 정렬 상태) SUM과 정렬이 제대로 작동하지 않습니다.
스프레드시트 숙련도는 강력한 무기입니다. 위의 공식들로 실무 데이터 작업의 90%를 처리할 수 있습니다. 실제 데이터셋으로 직접 연습하다 보면 어느새 복잡한 공식도 자연스럽게 활용하게 될 것입니다.