컴쌤

[태그:] IFERROR

  • 엑셀 오류 없애기, 합계가 바로 나오는 표

    엑셀 오류 없애기는 방법마다 셀에 남는 값이 다르다. IFERROR로 오류를 0으로 바꾸면 셀에 숫자 0이 남아서 합계가 다시 나온다. 조건부 서식으로 오류를 흰 글꼴로 칠하면 #DIV/0! 같은 오류 값이 셀에 그대로 남아서 합계 칸도 오류로 남는다.

    그래서 방법을 고르는 기준은 보기 좋으냐가 아니다. 그 셀의 값을 다른 수식이 다시 가져다 쓰느냐다.

    1. IFERROR는 값 변경, 흰 글꼴은 오류 유지

    엑셀 오류 없애는 방법별 셀에 실제로 남는 값

    IFERROR(수식,0): 숫자 0

    IFERROR(수식,""): 길이 0인 글자

    IFERROR 0에 사용자 지정 서식(;;;) 추가: 숫자 0, 화면에만 안 보임

    조건부 서식 흰색 글꼴: 원래 오류 값

    오류 검사 끄기: 원래 오류 값, 초록 삼각형만 사라짐

    IFERROR를 쓴 세 가지는 값을 바꾸고, 흰 글꼴과 오류 검사 끄기는 보이는 모양만 바꾼다. 합계가 다시 나오는 건 IFERROR를 쓴 세 가지뿐이다.

    2. IFERROR 0은 합계 계산, 평균은 낮아짐

    =IFERROR(A2/B2,0)은 오류가 나면 0을 넣는다. 0은 숫자라서 SUM도, 셀을 하나씩 짚어 더하는 =C2+C3도 그대로 계산된다.

    대신 평균이 내려간다. 마이크로소프트 지원 문서의 예에서 숫자 4개의 평균은 22.75인데, 빈 셀 하나가 0으로 바뀌면 18.2가 된다. 매출 합계처럼 더하기만 하는 열이면 0이 가장 탈이 없다. 단가·달성률처럼 평균을 내는 열에는 0을 넣지 않는 편이 낫다.

    오류 원인이 나눗셈의 빈 분모 하나라면 IFERROR보다 =IF(B2,A2/B2,0)이 낫다. B2가 0이거나 비었을 때만 0을 넣고, 다른 오류는 그대로 보여 준다. IFERROR는 #N/A부터 #NULL!까지 오류 7가지를 모두 0으로 바꾼다. 열을 지워서 생긴 #REF!까지 조용히 0이 된다.

    3. IFERROR 빈칸은 SUM 가능, + 계산은 #VALUE!

    =IFERROR(A2/B2,"")는 셀을 비어 보이게 한다. 하지만 셀 안에는 길이 0인 글자가 들어 있다. SUM과 AVERAGE는 범위 안의 글자를 건너뛰니 합계는 나오고, 평균에서도 그 행은 빠진다.

    범위 대신 셀을 +로 더하면 결과가 다르다. =C2+C3에서 C3이 ""이면 결과는 #VALUE!다. 오류 하나를 지웠는데 옆 열에 새 오류가 생긴다.

    인쇄해서 넘기는 표의 마지막 열이면 ""가 깔끔하다. 다른 수식이 가져다 쓰는 열이면 0이 안전하다.

    4. 조건부 서식은 오류를 숨겨도 합계는 오류

    수식을 건드리지 않아서 가장 빠르다.

    홈 → 조건부 서식 → 규칙 관리 → 새 규칙 → 다음을 포함하는 셀만 서식 지정 → 오류 → 서식 → 글꼴 흰색

    셀 안에는 #DIV/0!이 그대로 있다. 이 오류 셀을 더한 합계는 여전히 오류이고, 원인 셀은 흰 글씨라 눈에 안 띈다. 표에서 사라진 오류가 합계 칸에 다시 뜬다. 다섯 가지 중 가장 권하지 않는 방법이다.

    피벗 테이블은 수식 없이 옵션에서 바꾼다. 피벗 테이블 분석 → 옵션 → 레이아웃 및 서식 → 오류 값 표시에 체크하고 대신 보일 값을 적는다. 칸을 비워 두면 빈 셀로 보인다.

    5. 초록 삼각형은 숫자 변환, #####는 열 너비 조정

    셀 왼쪽 위 초록 삼각형은 오류 값이 아니라 오류 표시기다. 텍스트로 저장된 숫자나 옆 수식과 모양이 다른 수식처럼, 엑셀이 의심하는 셀에 붙는다. 셀 하나에서만 없애려면 옆에 뜨는 느낌표에서 오류 무시를 고르면 된다. 모두 끄는 순서는 이렇다.

    파일 → 옵션 → 수식 → 오류 검사 → 백그라운드 오류 검사 사용 체크 해제

    텍스트로 저장된 숫자에 붙은 삼각형은 끌 대상이 아니다. 숫자로 바꿔야 하는 셀이다. 그 숫자는 SUM 범위 안에 있어도 합계에서 조용히 빠진다.

    #####는 수식 오류가 아니다. 열이 좁아서 숫자가 안 들어가거나, 날짜·시간 값이 음수일 때 뜬다. 열 머리글 경계선을 두 번 누르면 너비가 맞춰진다. 넓혀도 남는다면 과거 날짜에서 미래 날짜를 뺀 수식이다.

    6. AGGREGATE로 오류 값 빼고 합계 계산

    원본 오류를 아직 못 고쳤는데 합계부터 필요하면 AGGREGATE가 있다.

    =AGGREGATE(9,6,B2:B10)

    9는 SUM, 6은 오류 값 무시다. 원본은 그대로 두고 합계 칸 하나만 바뀐다. 엑셀 2016 이상과 마이크로소프트 365에서 된다.

    오류 셀이 그대로 남아 있으니 나중에 고칠 목록도 그대로다. 합계는 급하지만 원본 오류를 아직 고치지 못했을 때 가장 알맞다.

    시트 안의 오류 셀은 이 순서로 한꺼번에 선택된다.

    Ctrl+G → 옵션 → 수식 → 오류만 체크 → 확인

    7. #N/A는 차트에서 숨기고 합계는 AGGREGATE

    차트로 그리는 열이면 #N/A는 남겨 둔다. 엑셀 차트는 #N/A를 그리지 않는다. 0으로 바꾸면 아직 값이 없는 달이 바닥에 붙은 선으로 그려지고, 값이 들어온 달에서 선이 치솟는다. 이 열의 #N/A를 0으로 바꾸면 차트가 실제와 달라진다.

    값이 아직 없는 칸은 =NA()로 채워 두고, 실제 값이 들어오면 그때 바꾼다. 이 열의 합계를 =AGGREGATE(9,6,범위)로 내면 차트와 합계가 둘 다 맞는다.

    #엑셀오류없애기
    #IFERROR
    #엑셀오류
    #AGGREGATE
    #조건부서식
    #엑셀합계

  • 엑셀 오류, 코드별로 계산 결과 되살리기

    엑셀 오류는 #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NULL!, #NUM! 일곱 가지가 기본이고 여기에 #SPILL!, #CALC!, #####도 들어 있다. 열 가지가 저마다 원인이 다르고 고치는 방법도 다르다. 마이크로소프트는 #NAME? 오류를 IFERROR 같은 오류 처리 함수로 가리지 말라고 분명히 밝혔다.

    오류가 뜬 표는 그래도 나은 편이다. 정말 곤란한 표는 오류 하나 없이 틀린 숫자만 들어 있는 표다.

    1. 엑셀 오류 코드 10가지와 뜻

    엑셀에서 뜨는 오류 코드 10가지와 뜻

    #DIV/0!: 0이거나 빈 셀로 나눴다

    #N/A: 찾는 값이 조회 범위에 없다

    #VALUE!: 인수의 데이터 형식이 맞지 않는다

    #REF!: 참조하던 셀이나 시트가 사라졌다

    #NAME?: 함수 이름이나 정의된 이름을 못 알아본다

    #NULL!: 교차하지 않는 두 범위를 공백으로 이었다

    #NUM!: 계산이 불가능하거나 엑셀이 다루는 숫자 범위를 벗어났다

    #SPILL!: 결과가 표시될 셀에 다른 값이 들어 있다

    #CALC!: 동적 배열 함수가 돌려줄 값이 없다

    #####: 열 너비가 좁거나 날짜가 음수다

    #####는 수식 오류가 아니다. 열 머리글 사이를 두 번 클릭하면 사라진다. 다만 지난 날짜에서 앞선 날짜를 뺀 것처럼 결과가 음수 날짜면 너비를 아무리 늘려도 그대로다. 엑셀은 음수 날짜를 표시하지 않는다.

    #SPILL!과 #CALC!는 Microsoft 365와 Excel 2021부터 나온다. 2019 이하를 쓴다면 이 두 개는 볼 일이 없다.

    2. 값 입력 전 #DIV/0!·#N/A, 수식 문제 5가지

    입력을 기다리는 중이라 뜨는 오류

    #DIV/0!: 분모 셀이 아직 비어 있다

    #N/A: 조회 목록에 아직 등록이 안 됐다

    수식이나 참조를 잘못 써서 뜨는 오류

    #VALUE!: 숫자 자리에 글자가 들어갔다

    #REF!: 참조를 지웠다

    #NAME?: 철자가 틀렸다

    #NULL!: 콜론 대신 공백을 넣었다

    #NUM!: 수식에 $1,000처럼 서식이 붙은 숫자를 넣었다

    앞의 둘은 값이 채워지면 저절로 없어진다. 뒤의 다섯은 수식이나 참조를 고치기 전까지 그대로다. IFERROR는 이 열 가지를 구분 없이 전부 잡아낸다. 그래서 표 전체를 IFERROR로 감싸면 데이터가 아직 없어서 뜬 것인지 수식을 잘못 쓴 것인지 알 방법이 사라진다.

    나눗셈이라면 =IFERROR(A2/A3,0)보다 =IF(A3,A2/A3,0)이 낫다. A3에 값이 있을 때만 계산하라는 뜻이라 나눗셈에서 생긴 문제만 처리한다. 조회 실패만 걸러 내려면 IFNA를 쓴다. 작업하는 동안은 오류를 그대로 두고, 원인을 다 찾은 다음 마지막 정리에서만 씌우는 편이 안전하다.

    3. 되돌리기 말고는 복구가 없는 #REF!

    행이나 열을 지운 뒤 #REF!가 보이면 바로 Ctrl+Z를 누른다. 원래 참조하던 셀 주소는 영영 사라지기 때문이다. 시트를 지운 경우는 더 심각하다. 삭제된 워크시트는 복구할 수 없다고 마이크로소프트가 밝혔다.

    처음부터 #REF!가 안 나오게 하려면 수식을 다르게 써야 한다. =SUM(B2,C2,D2)에서 C열을 지우면 수식은 =SUM(B2,#REF!,C2)가 된다. =SUM(B2:D2)로 적어 두면 가운데 열을 지워도 엑셀이 알아서 범위를 줄인다. 그래서 마이크로소프트는 함수에 셀을 하나씩 나열하지 말라고 권한다.

    VLOOKUP에서도 자주 난다. =VLOOKUP(A8,A2:D5,5,FALSE)는 범위가 A부터 D까지 4열인데 5번째 열을 달라고 한 수식이다. 범위를 A2:E5로 늘리거나 열 번호를 4로 줄이면 된다.

    4. VLOOKUP은 FALSE, MATCH는 0으로 정확히 조회

    VLOOKUP의 마지막 인수를 TRUE로 두면 정확히 일치하는 값이 아니라 비슷한 값을 찾는다. 조회 목록에 Banana가 없으면 #N/A가 뜬다. 그런데 같은 표에서 Pear는 오류도 없이 엉뚱한 가격을 가져온다. 알파벳순으로 Pear가 Peach 앞에 오기 때문이다. FALSE로 바꾸면 Pear는 제 가격을 찾아오고 Banana만 #N/A로 남는다. MATCH 함수라면 match_type을 0으로 둔다.

    마지막 인수를 비워 두는 습관이 제일 위험하다. 오류가 안 뜨면 틀린 값을 발견하기 어렵다.

    값이 분명히 있는데도 #N/A가 뜬다면 셋 중 하나다. 앞뒤에 공백이 붙었거나, 한쪽은 숫자인데 다른 쪽은 텍스트로 저장됐거나, 배열 수식의 참조 범위가 서로 행 수가 다르다. 공백은 TRIM으로 벗겨 낸다. 형식은 셀을 고른 뒤 Ctrl+1로 확인하고, 열 전체를 한꺼번에 바꿔야 하면 데이터 탭의 텍스트 나누기로 마침을 누른다.

    5. 눈에 안 보이는 공백이 만드는 #VALUE!

    공백 하나가 들어간 셀은 빈 셀처럼 보인다. 그 셀이 =E2+E3+E4+E5 안에 있으면 합계 전체가 #VALUE!가 된다. 어느 셀인지 찾으려면 수식 탭의 수식 계산을 눌러 한 단계씩 계산해 본다. 문제가 된 값이 따옴표 두 개 사이의 공백으로 드러난다. ISTEXT를 옆 열에 넣어 TRUE가 나오는 셀을 찾는 방법도 있다.

    더하기 빼기 대신 함수를 쓰면 이 오류 자체가 잘 안 난다. =A2+B2+C2를 =SUM(A2:C2)로, =A2*B2를 =PRODUCT(A2,B2)로 바꾼다. 함수는 텍스트 값을 건너뛰고 숫자만 계산한다.

    윈도우에서 가장 기본적인 뺄셈에도 #VALUE!가 뜬다면 엑셀이 아니라 윈도우 지역 설정이 원인이다. 새 통합 문서를 열고 A1에 2, B1에 4를 넣은 다음 C1에 =B1-A1을 입력해 본다. 여기서도 오류가 나면 제어판의 지역 설정에서 추가 설정으로 들어가 목록 구분 기호를 확인한다. 그 값이 빼기 기호로 설정돼 있으면 쉼표나 세미콜론으로 바꾼다.

    6. 파일 전체에서 오류 셀 한 번에 찾기

    현재 시트는 Ctrl+G를 누르고 옵션에서 수식과 오류를 고른 뒤 확인을 누른다. 오류가 있는 셀이 한꺼번에 선택되고 Tab으로 하나씩 옮겨 다닐 수 있다. 하나도 없으면 찾는 셀이 없다는 메시지가 뜬다.

    이 방법은 현재 시트 하나에서만 오류를 찾는다. 파일 전체에서 오류를 찾으려면 Ctrl+F를 누르고 이렇게 넣는다.

    통합 문서 전체 오류 검색 설정

    찾을 내용: #*!

    범위: 통합 문서

    찾는 위치: 값

    여기서 모두 찾기를 누르면 시트 이름이 들어 있는 오류 목록이 나온다. 별표는 아무 글자나 뜻하는 기호라서 #으로 시작해 !로 끝나는 값을 모두 찾는다.

    한 가지가 빠진다. #N/A는 !로 끝나지 않아서 이 방법으로는 안 잡힌다. #N/A만 따로 한 번 더 찾아야 한다. 조회 함수가 많은 표일수록 #N/A가 더 많이 나온다.

    오류 코드마다 틀린 원인이 다르다. #DIV/0!이면 분모, #N/A면 조회 값과 데이터 형식, #REF!면 방금 지운 행과 열, #NAME?이면 철자. 코드에 맞는 원인만 고치면 된다. IFERROR는 원인을 다 찾은 다음에 붙여도 늦지 않다.

    #엑셀오류
    #엑셀오류해결
    #REF오류
    #NA오류
    #VALUE오류
    #DIV0오류
    #NAME오류
    #IFERROR
    #IFNA
    #엑셀함수
    #엑셀실무
    #VLOOKUP

  • 엑셀 수식 정리, 매일 계산 한 번에 끝내기

    엑셀 수식은 모두 등호(=)로 시작하고, 함수에 넣는 값은 괄호 안에 쉼표로 나눠 적는다. 실무에서 반복되는 엑셀 수식은 합계·조건·조회·정리 네 가지고 함수 이름으로는 20개 안쪽이다. XLOOKUP은 Microsoft 365와 Excel 2021부터 쓸 수 있고 Excel 2016과 2019에는 아예 없다.

    함수를 몰라서 막히는 경우는 생각보다 적다. 하고 싶은 계산에 어떤 함수를 써야 하는지 몰라서 막힌다. 지점별로 묶어 합계를 내고 싶다는 생각이 SUMIFS까지 안 넘어가는 것이다. 그래서 이름순으로 정리하면 다음 날 절반은 기억나지 않고, 하고 싶은 일 순으로 정리해야 기억에 남는다.

    1. 합계·조건·조회·정리 네 묶음

    매일 쓰는 엑셀 수식 묶음

    합계와 개수: SUM, AVERAGE, MAX, MIN, COUNT, COUNTA

    조건: IF, IFS, SUMIF, SUMIFS, COUNTIF, COUNTIFS

    조회: XLOOKUP, VLOOKUP, INDEX와 MATCH

    정리: IFERROR, TRIM, ROUND, TEXT, TODAY

    실제 수식은 이렇다.

    =SUM(D2:D20)

    =IF(E2="완료","지급","확인")

    =SUMIFS(D2:D20,B2:B20,"영업",E2:E20,"완료")

    =XLOOKUP(G2,A2:A20,C2:C20,"없음")

    =IFERROR(XLOOKUP(G2,A2:A20,C2:C20),"확인필요")

    글자를 조건으로 넣을 때는 큰따옴표로 감싼다. 30만원을 넘는 것만 더할 때도 부등호를 따옴표 안에 넣어 ">300000"으로 적는다. 이 따옴표를 빼먹으면 결과가 0으로 나오거나 #NAME? 이 뜬다.

    2. SUMIF는 더할 범위가 뒤, SUMIFS는 앞

    이름은 S 한 글자 차이인데 더할 범위가 들어가는 자리는 반대다.

    =SUMIF(조건범위, 조건, 더할범위)

    =SUMIFS(더할범위, 조건범위1, 조건1, 조건범위2, 조건2)

    SUMIF를 써 둔 수식 뒤에 조건을 하나 더 적으면 값이 틀어진다. 오류 표시도 안 뜬다. 수식 형식은 맞기 때문에 엑셀도 오류라고 알려 줄 수 없다.

    조건이 하나뿐일 때도 SUMIFS로 쓰는 편이 낫다. SUMIFS 형식으로 통일하면 조건이 늘어날 때 뒤에 두 칸씩 붙이기만 하면 된다. COUNTIF와 COUNTIFS는 더할 범위가 없어서 이 문제가 안 생긴다.

    3. Excel 2016·2019에서는 XLOOKUP 대신 VLOOKUP

    VLOOKUP은 찾는 값이 표의 맨 왼쪽 열에 있어야 하고, 가져올 값이 몇 번째 열인지 숫자로 세어 넣는다. 표 중간에 열이 하나 끼어들면 세어 둔 숫자가 밀려서 다른 열의 값이 따라온다.

    XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정하니 표 중간에 열이 추가돼도 가져오는 값은 바뀌지 않는다. 못 찾았을 때 대신 넣을 문구도 네 번째 자리에 같이 적는다. 왼쪽이든 오른쪽이든 상관없이 가져온다.

    문제는 이 함수가 Excel 2016과 2019에는 없다는 것이다. XLOOKUP으로 짠 파일을 2019 이하에서 열면 함수 이름을 못 알아봐서 #NAME? 이 뜬다. 혼자 쓰는 파일이면 XLOOKUP이 낫고, 여러 사람에게 보내는 파일이면 VLOOKUP이나 INDEX와 MATCH 조합으로 두는 편이 안전하다. 파일을 받는 사람의 버전을 물어보기 어려운 경우가 대부분이라 결국 그 버전에 맞춰 함수를 고른다.

    4. 오류 코드 다섯 개와 바로 고칠 원인

    오류 코드가 떴다는 건 엑셀이 계산을 안 한 게 아니다. 계산 중에 오류가 난 것이다. 계산 옵션이나 서식을 뒤지기 전에 코드부터 읽는다.

    수식 셀에 뜨는 오류와 원인

    #DIV/0!: 나누는 셀이 0이거나 비어 있다

    #VALUE!: 숫자가 들어갈 자리에 글자가 섞였다

    #REF!: 참조하던 행·열·시트가 지워졌다

    #NAME?: 함수 이름이나 따옴표, 시트 이름 표기가 틀렸다

    #N/A: 조회 범위에 찾는 값이 없다

    #N/A는 보고서에 그대로 낼 수 없으니 IFERROR로 감싸 빈칸이나 문구로 바꾸게 된다. 다만 IFERROR는 원인을 고치는 함수가 아니라 화면에서 가리는 함수다. 진짜로 값이 빠진 행까지 같이 가려지니, 원인을 한 번 확인한 표에만 씌운다.

    5. 내 파일 수식을 모아 다음 달에도 다시 쓰기

    이미 사용 중인 파일에 어떤 수식이 들어 있는지부터 볼 수 있다. 홈 탭에서 찾기 및 선택 → 이동 옵션 → 수식을 고르면 수식이 든 셀이 한꺼번에 선택된다. 셀 하나만 선택하고 실행하면 시트 전체의 수식 셀이 선택되고, 범위를 잡아 두고 실행하면 그 범위 안의 수식 셀만 선택된다.

    시트 전체의 수식을 보고 싶으면 Ctrl과 백틱(`)을 같이 누른다. 결과 대신 수식이 보이고, 다시 누르면 원래대로 돌아온다. 계산을 끄는 기능이 아니라 보기만 바꾸는 것이라 파일이 상하지 않는다.

    여기서 나온 수식을 빈 시트에 함수 이름, 실제로 쓴 수식, 어디에 썼는지 세 칸으로 옮겨 적는다. 검색해서 저장해 둔 정리표를 다시 안 보는 이유는 분량이 아니라 남의 표라서다. 30줄짜리라도 내가 지난달에 쓴 수식만 모인 표는 다음 달에도 그대로 다시 쓴다.

    #엑셀수식정리
    #엑셀함수
    #SUMIFS
    #XLOOKUP
    #IFERROR
    #엑셀오류코드
    #직장인엑셀
    #엑셀실무