엑셀은 끊임없이 진화하며 새로운 함수와 기능이 추가되고 있습니다.
엑셀 365 같은 최신 버전은 XLOOKUP, FILTER 등 혁신적인 기능으로 무장했지만, 여전히 많은 기업이나 사용자들이 구형 엑셀 버전(예: 엑셀 2010, 2013, 2016)을 사용하고 있죠.
이처럼 다양한 엑셀 버전 간에 파일을 공유하고 작업할 때, 호환성 문제는 예상치 못한 오류와 작업 지연을 유발할 수 있습니다.
엑셀의 주요 통계 및 논리 함수의 구형 버전과 그 대체 함수들을 알아봅니다. IF 함수의 옛 모습인 중첩 IF부터 STDEV, VAR의 구형 버전까지, 과거와 현재의 최적 함수를 함께 알아봅니다.
왜 호환성 함수를 알아야 할까요?
최신 버전의 엑셀 함수들은 강력하고 편리하지만, 구형 버전에서는 작동하지 않습니다.
이는 다음과 같은 문제로 이어질 수 있습니다.
- 수식 오류: 최신 함수가 포함된 파일을 구형 엑셀에서 열면 #NAME? 등의 오류가 발생합니다.
- 보고서 공유의 어려움: 여러 사용자가 다른 버전의 엑셀을 사용할 때 보고서가 제대로 보이지 않을 수 있습니다.
- 레거시 파일 유지보수: 과거에 작성된 복잡한 엑셀 파일을 수정하거나 분석할 때, 당시 사용되던 함수들을 이해해야 합니다.
이 시리즈는 버전 간의 장벽을 이해하고, 호환성을 고려한 효율적인 엑셀 작업을 수행하며, 과거의 파일들을 능숙하게 다룰 수 있는 역량을 제공합니다.
주요 함수들
이번 1부에서는 다음 호환성 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- IF (중첩 IF): IFS 함수가 없던 시절의 다단계 조건 처리
- MATCH: XMATCH의 구형 버전 (정확히 일치, 근사 일치)
- STDEV, VAR: STDEV.S, STDEV.P, VAR.S, VAR.P의 구형 버전 (표본/모집단 구분 없음)
- AVERAGEA, COUNTA: 비어있지 않은 셀을 세는 함수 (텍스트 0, TRUE 1)
실무 예제로 배우는 엑셀 호환성 함수 마스터하기
각 함수를 실제 버전 호환성 시나리오에 적용하여 구형 함수를 이해하고 대체 함수를 활용하는 방법을 보여드리겠습니다.
1. IF (중첩 IF): 다단계 조건의 과거와 현재
IF 함수는 가장 기본적인 논리 함수입니다. 하지만 여러 조건을 순차적으로 처리해야 할 때, 엑셀 2019 및 Microsoft 365 이전 버전에서는 여러 개의 IF 함수를 겹쳐 사용하는 중첩 IF 방식을 사용해야 했습니다.
이는 길고 복잡하며 가독성이 떨어지는 단점이 있습니다. 최신 버전에서는 IFS 함수가 이 문제를 해결했습니다.
- 중첩 IF 기본 형식: =IF(조건1, 값1, IF(조건2, 값2, IF(조건3, 값3, ... 거짓일_때_최종_값)))
- IFS 기본 형식 (최신 버전): =IFS(조건1, 값1, [조건2, 값2], ...)
실무 예제:
직원들의 월별 매출액에 따라 인센티브 등급을 부여해야 합니다.
(2000만 원 이상: "최우수", 1000만 원 이상: "우수", 500만 원 이상: "보통", 그 외: "개선 필요")
| 담당자 |
월별 매출액 (만 원)
|
| 김대리 | 1200 |
| 이사원 | 750 |
| 박과장 | 2100 |
| 최주임 | 400 |
예제 목표: 구형 엑셀에서 사용 가능한 중첩 IF 함수로 인센티브 등급을 부여하고, 최신 엑셀 사용자를 위해 IFS 함수로도 동일한 결과를 구현하세요.
해결 방법 (중첩 IF):
C2 셀에 다음 수식을 입력하고 아래로 채웁니다.
=IF(B2>=2000, "최우수", IF(B2>=1000, "우수", IF(B2>=500, "보통", "개선 필요")))
해결 방법 (IFS - 엑셀 2019/M365 이상):
D2 셀에 다음 수식을 입력하고 아래로 채웁니다.
=IFS(B2>=2000, "최우수", B2>=1000, "우수", B2>=500, "보통", TRUE, "개선 필요")
결과: 두 함수 모두 동일한 인센티브 등급을 반환합니다.
| 담당자 | 월별 매출액 (만 원) | 중첩 IF 결과 | IFS 결과 |
| 김대리 | 1200 | 우수 | 우수 |
| 이사원 | 750 | 보통 | 보통 |
| 박과장 | 2100 | 최우수 | 최우수 |
| 최주임 | 400 | 개선 필요 | 개선 필요 |
최상급 실무 활용:
IFS 함수는 중첩 IF보다 가독성이 훨씬 뛰어나고 오류 발생 가능성이 낮습니다. 최신 엑셀을 사용하는 환경에서는 가급적 IFS를 사용하는 것이 좋습니다.
하지만 구형 엑셀 사용자들과 파일을 공유해야 하거나, 기존의 중첩 IF로 작성된 파일을 수정해야 할 때는 중첩 IF에 대한 이해가 필수적입니다.
2. MATCH: XMATCH의 구형 버전
MATCH 함수는 지정된 값과 일치하는 항목의 상대적인 위치(순서)를 반환합니다.
엑셀 365 및 엑셀 2019에는 더 강력한 XMATCH 함수가 있지만, MATCH는 여전히 광범위하게 사용됩니다.
- MATCH 기본 형식: =MATCH(찾을_값, 찾을_범위, [일치_유형])
일치_유형: 0 (정확히 일치), 1 (작거나 같음), -1 (크거나 같음).
실무 예제:
제품 코드 목록에서 특정 제품 코드의 위치(순서)를 찾아야 합니다.
| 제품 코드 | 제품명 |
| P001 | 노트북 |
| P002 | 태블릿 |
| P003 | 스마트폰 |
예제 목표: 'P002'가 제품 코드 목록에서 몇 번째에 있는지 정확히 찾으세요.
해결 방법:
C2 셀에 =MATCH("P002", A2:A4, 0)
결과: 2
최상급 실무 활용:
MATCH 함수는 INDEX 함수와 결합하여 VLOOKUP의 한계를 극복하는 데 필수적으로 사용됩니다.
XMATCH는 MATCH의 모든 기능을 포함하고 역방향 검색, 오류 처리 등을 추가 제공하므로, 최신 버전에서는 XMATCH를 선호하는 것이 좋습니다. 하지만 MATCH는 여전히 널리 사용되므로, 구형 엑셀과의 호환성을 위해 알아두어야 합니다.
3. STDEV, VAR: 표본/모집단 구분 없는 구형 통계 함수
엑셀 2010 이전 버전에서는 STDEV와 VAR 함수가 표본(Sample)과 모집단(Population)을 명확히 구분하지 않고 사용되었습니다. 엑셀 2010부터는 STDEV.S (표본 표준 편차), STDEV.P (모집단 표준 편차), VAR.S (표본 분산), VAR.P (모집단 분산)처럼 명확히 구분된 함수들이 도입되었습니다.
- 구형 형식: =STDEV(숫자1, [숫자2], ...), =VAR(숫자1, [숫자2], ...)
- 현대 형식: =STDEV.S(숫자1, [숫자2], ...), =VAR.S(숫자1, [숫자2], ...) 등
실무 예제:
팀원들의 월별 판매 실적 데이터가 아래와 같습니다. (이 데이터가 '표본'이라고 가정)
| 판매 실적 |
| 120 |
| 150 |
| 130 |
| 110 |
| 140 |
예제 목표: 구형 STDEV와 VAR 함수로 판매 실적의 표준 편차와 분산을 계산하고, 현대 함수(STDEV.S, VAR.S)와 비교하세요.
해결 방법:
- 구형 STDEV: C2 셀에 =STDEV(A2:A6)
- 현대 STDEV.S: D2 셀에 =STDEV.S(A2:A6)
- 구형 VAR: C3 셀에 =VAR(A2:A6)
- 현대 VAR.S: D3 셀에 =VAR.S(A2:A6)
결과:
STDEV와 VAR 함수는 기본적으로 표본 통계량을 계산하므로, STDEV.S 및 VAR.S와 동일한 결과가 반환됩니다.
| A열 | B열 | C열 (구형) | D열 (현대) |
| 판매 실적 | |||
| 120 | STDEV | 15.81139 | 15.81139 |
| 150 | VAR | 250 | 250 |
| 130 | |||
| 110 | |||
| 140 |
최상급 실무 활용:
구형 STDEV와 VAR는 표본 표준 편차/분산을 계산합니다. 따라서 엑셀 2010 이상 버전에서 STDEV.S와 VAR.S를 사용하는 것이 더 명확하고 혼동을 줄일 수 있습니다.
하지만 이전 버전에서 작성된 파일을 분석할 때는 이 함수들이 여전히 사용될 수 있음을 인지해야 합니다. 통계 분석의 정확성과 명확성을 위해 항상 현대 버전의 함수(STDEV.S/.P, VAR.S/.P)를 사용하는 것을 권장합니다.
4. AVERAGEA, COUNTA: 비어있지 않은 셀을 세는 함수 (텍스트/논리값 포함)
AVERAGEA와 COUNTA 함수는 범위 내에서 비어있지 않은 셀의 평균 또는 개수를 계산합니다.
이때 TRUE는 1로, FALSE는 0으로, 텍스트는 0으로 간주하여 계산에 포함한다는 특징이 있습니다.
일반적인 AVERAGE와 COUNT 함수는 숫자만 계산합니다.
- 기본 형식: =AVERAGEA(값1, [값2], ...), =COUNTA(값1, [값2], ...)
실무 예제:
고객 만족도 설문조사 결과가 아래와 같이 혼합된 형태로 입력되어 있습니다.
| 만족도 점수 |
| 5 |
| 높음 |
| TRUE |
| 4 |
| FALSE |
| (공백) |
예제 목표: AVERAGEA와 COUNTA를 사용하여 평균 점수와 비어있지 않은 응답 개수를 구하고, 일반 함수와 비교하세요.
해결 방법:
- AVERAGEA: C2 셀에 =AVERAGEA(A2:A7)
- AVERAGE: D2 셀에 =AVERAGE(A2:A7)
- COUNTA: C3 셀에 =COUNTA(A2:A7)
- COUNT: D3 셀에 =COUNT(A2:A7)
결과:
최상급 실무 활용:
AVERAGEA와 COUNTA는 텍스트나 논리값이 포함된 데이터에서 평균이나 개수를 구해야 할 때 사용합니다. 예를 들어, 설문조사에서 '예/아니오' 응답(TRUE/FALSE)이나 '미응답'(공백)까지도 통계에 포함해야 할 경우 유용합니다.
하지만 텍스트를 0으로 간주하여 계산하므로, 데이터의 의미를 정확히 이해하고 사용해야 합니다. 대부분의 경우 숫자 데이터만 계산하는 AVERAGE와 COUNT가 더 적절합니다.