Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

내가 스프레드시트를 잘못 다루고 있다는 것을 깨달았던 순간이 여전히 기억난다. 2019년, 중간 규모의 물류 회사에서 나의 팀과 함께 회의실에 앉아 있었고, CFO가 분기 보고서를 생성하기 위해 세 개의 서로 다른 Excel 파일 간에 수동으로 데이터를 복사하고 붙여넣는 모습을 지켜보았다. 이 과정은 그녀에게 네 시간이 걸렸다. 매 분기마다 그녀의 시간당 150달러를 소모한 네 시간, 왜냐하면 아무도 VLOOKUP을 배우려 하지 않았기 때문이다.

그날은 나에게 모든 것을 변화시켰다. 나는 사라 첸이며, 지난 11년간 재무 운영 컨설턴트로 일하며 200개 이상의 회사와 협력하여 데이터 작업 흐름을 간소화해왔다. 나는 많은 기업들이 단 몇 개의 공식을 사용하여 자동화할 수 있는 스프레드시트 작업에 엄청난 시간을 낭비하는 것을 보았다. 아이러니하게도, 대부분의 사람들은 "엑셀을 잘하려면 수백 개의 함수를 마스터해야 한다"고 생각하지만, 실제로는 그렇지 않다. 내 경험에 따르면, 20개의 공식이 실제 세계의 비즈니스 시나리오의 약 95%를 다룬다.

이 책은 지금까지 만들어진 모든 스프레드시트 함수의 포괄적인 백과사전이 아니다. 이것은 내 클라이언트가 수작업으로 절약할 수 있었던 약 12,000시간을 아끼기 위해 사용했던 전투 검증된 무기고다. 장부를 마감하거나, 판매 데이터를 분석하거나, 재고 수치가 일치하지 않는 이유를 알아내기 위해 사실상 중요한 공식들이다.

기초: 모든 것을 지탱하는 기본 산수

멋진 이야기를 시작하기 전에, 내가 지금까지 만들어본 재무 모델의 뼈대를 형성하는 세 가지 공식에 대해 이야기해보자. 이들은 분명하게 보일 수 있지만, 나는 고위 분석가들이 이들로 인해 실제 돈을 잃는 실수를 하는 것을 많이 보았다.

SUM은 작업을 맡고 있는 공식이다. 구문은 매우 간단하다: =SUM(A1:A10)은 해당 범위 내의 모든 것을 더한다. 하지만 대부분의 사람들이 모르는 사실: 여러 개의 비연속 범위와 함께 SUM을 사용할 수 있다. =SUM(A1:A10, C1:C10, E5)는 완벽하게 작동하며, 개별 셀 참조를 작성하는 것보다 훨씬 깔끔하다. 내가 한 금융 모델을 감사할 때, 누군가가 =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10을 작성한 대신 SUM을 사용했을 때를 보았다. 그들이 행을 삽입해야 할 때, 공식이 망가졌다. 그런 사람이 되지 마라.

AVERAGE 역시 간단하지만 놀랍도록 강력하다. =AVERAGE(B2:B50)은 데이터셋의 평균을 제공한다. 여기에 대한 핵심 통찰력은 AVERAGE와 AVERAGEIF를 언제 사용할지 이해하는 것이다(후에 다룰 것이다). 내 컨설팅 작업에서, 나는 사람들이 AVERAGE를 사용할 때 약 60%의 경우 조건부 평균이 필요하다는 것을 발견했다. 평균 판매량을 계산하였지만, 0이나 이상치를 제외하고 싶다면, 일반 AVERAGE는 혼동을 줄 것이다.

COUNT와 COUNTA는 성격이 다른 쌍둥이다. COUNT는 숫자가 있는 셀만 세고, COUNTA는 비어 있지 않은 모든 셀을 센다. 이 구분은 생각보다 더 중요하다. 내가 데이터 품질을 감사할 때, =COUNTA(A:A)-COUNT(A:A)를 사용하여 A 열에서 숫자가 아닌 텍스트를 포함한 셀 수를 즉시 찾아낸다. 그 수치가 예상보다 높으면 데이터 입력에 문제가 있음을 의미한다. 이 간단한 점검으로 내 클라이언트의 회계 오류를 수천 달러 단위로 잡아냈다.

조회 함수: s

내가 누군가에게 세 가지 공식을 가르쳐줄 수 있다면, 그 중 두 개는 조회 함수가 될 것이다. 이 함수들은 수동으로 데이터를 30분 동안 일치시키는 것과 30초 만에 이를 즉시 수행하는 공식을 작성하는 것의 차이를 만든다.

"200개 이상의 기업과 작업한 내 경험에 따르면, 20개의 공식이 실제 비즈니스 시나리오의 약 95%를 처리한다. 대부분의 사람들은 사용하지 않을 수백 개의 함수를 마스터하려고 낭비한다."

VLOOKUP은 고전적인 함수이며, 더 새로운 대안에도 불구하고 여전히 믿을 수 없을 만큼 유용하다. 구문은 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])이다. 지난 달의 실제 예를 들자면: 나는 한 고객이 한 시트에 5,000개의 고객 기록과 다른 시트에 5,000개의 거래 기록을 가지고 있던 경우를 다뤘다. 그들은 고객 이름을 거래 ID에 일치시켜야 했다. =VLOOKUP(A2, Customers!A:D, 3, FALSE)는 수동으로 몇 시간 걸릴 작업을 삼초 만에 수행하였다.

모두가 잘못 이해하는 중요한 사항: 네 번째 인수. FALSE는 정확한 일치를 의미하고, TRUE는 근사치를 의미한다. 비즈니스 사용 사례의 99%에 대해 FALSE를 원한다. 누군가 그것을 TRUE로 두었거나, 생략했을 경우(기본값은 TRUE)는 전체 재무 보고서가 손상되는 상황을 보았다.

XLOOKUP은 현대적인 대체 수단으로, 솔직히 거의 모든 면에서 더 좋다. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])는 복잡하게 들리지만, 실제로는 더 깔끔하다. 결정적인 기능? VLOOKUP이 할 수 없는 왼쪽 검색이 가능하다. 나는 최근에 고객의 전체 보고 시스템을 VLOOKUP에서 XLOOKUP으로 마이그레이션하여 XLOOKUP이 누락된 데이터를 더 원활하게 처리하기 때문에 공식 오류를 약 40% 줄였다.

INDEX-MATCH는 파워 유저의 선택이다. 사실 이것은 두 개의 공식을 결합한 것이다: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). 이 조합은 VLOOKUP보다 더 유연하고 대규모 데이터셋에서는 더 빠르다. 나는 50,000행 이상의 스프레드시트로 작업할 때 INDEX-MATCH의 성능이 눈에 띄게 더 좋다. 배우는 곡선은 더 가파르지만, 한번 익히면 절대 돌아가지 않을 것이다. 나는 동시에 두 가지 기준에 따라 값을 조회해야 하는 복잡한 금융 모델에 사용한다.

조건부 논리: 스프레드시트가 생각하도록 하기

여기서부터 스프레드시트는 계산기를 넘어 의사결정 도구로 변화한다. 조건부 공식은 데이터에 논리를 구축할 수 있게 해주며, 이는 모든 종류의 자동화된 보고에 필수적이다.

IF는 스프레드시트 자동화의 입문 공식이다. =IF(logical_test, value_if_true, value_if_false)는 분기 논리를 생성할 수 있게 해준다. 간단한 예: =IF(B2>1000, "High Value", "Standard")는 구매 금액에 따라 고객을 분류한다. 하지만 진짜 힘은 IF를 중첩할 때 생긴다. 나는 여러 기준에 따라 영업사원의 보너스를 자동으로 결정하는 5개의 중첩된 IF 문이 있는 커미션 계산기를 구축했다. 핵심은 이를 읽기 쉽게 유지하는 것이다. 적절한 들여쓰기와 주석을 사용하라.

IFS는 새로운 스프레드시트 버전에서 사용할 수 있는 중첩 IF의 더 깔끔한 대안이다. =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F"))) 대신 =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")라고 쓴다. 이는 더 읽기 쉽고 괄호 오류에 덜 민감하다. 나는 약 3년 전 IFS로 전환했으며 내 공식 디버깅 시간이 절반으로 줄어들었다.

SUMIF와 SUMIFS는 조건부 합계의 강력한 도구이다. =SUMIF(range, criteria, [sum_range])는 조건을 만족하는 값의 합계를 계산한다. =SUMIFS는 여러 조건으로 확장한다. 실제 예: 나는 한 소매 고객이 지역 및 제품 카테고리별 총 판매량을 계산해야 하는 경우를 다뤘다. =SUMIFS(Sales!C:C, Sales!A:A, "West", Sales!B:B, "Electronics")는 정확히 그것을 제공했다. SUMIFS를 배우기 전에는 그들이 모든 것을 피벗 테이블로 처리하고 있었고, 이는 과도하고 업데이트 속도가 느렸다.

COUNTIF와 COUNTIFS는 동일한 방식으로 작동하지만 더하기 대신 세기만 한다. 나는 데이터 품질 점검을 위해 이를 지속적으로 사용한다. =COUNTIF(A:A, "Error")는 A열에 "Error"라는 단어가 포함된 셀 수를 알려준다. =COUNTIFS(A:A, ">100", B:B, "<50")는 A열이 100 초과 AND B열이 50 미만인 행을 센다. 이러한 공식은 수작업 데이터 검증에서 수많은 시간을 절약해주었다.