컴쌤

[태그:] 엑셀오류코드

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

    엑셀 수식은 모두 등호(=)로 시작하고, 함수에 넣는 값은 괄호 안에 쉼표로 나눠 적는다. 실무에서 반복되는 엑셀 수식은 합계·조건·조회·정리 네 가지고 함수 이름으로는 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
    #엑셀오류코드
    #직장인엑셀
    #엑셀실무

  • 엑셀 수식 안됨, 수식 그대로 살려 결과 되찾기

    엑셀 수식 안됨은 화면에 보이는 증상에 따라 원인이 세 가지로 나뉜다. 시트 전체에 수식이 보이면 수식 표시 모드가 켜진 것이라 Ctrl+`를 다시 누르면 되고, 한두 셀만 =SUM(A1:A10)처럼 글자로 남으면 그 셀 서식이 텍스트다. 값을 바꿔도 결과가 이전 숫자에 멈춰 있으면 계산 옵션이 수동이다.

    계산 옵션부터 바꾸라는 안내가 많다. 계산 옵션부터 보는 순서가 거꾸로다. 데스크톱 엑셀에서 계산 옵션을 바꾸면 지금 파일 하나가 아니라 열려 있는 통합 문서 전부에 영향을 준다고 Microsoft가 밝히고 있다. 증상을 구분하지 않고 먼저 바꾸면 멀쩡하던 옆 파일의 계산 옵션까지 수동으로 바뀐다.

    1. 수식 표시·텍스트 셀·수동 계산부터 구분

    구분하는 기준은 두 개다. 여러 셀이 한꺼번에 그런지 한두 셀만 그런지. 그리고 수식이 보이는지, 결과가 안 바뀌는지.

    증상별 원인

    시트 전체에 수식이 보임: 수식 표시 모드

    특정 셀만 수식이 글자로 보임: 셀 서식이 텍스트

    값을 바꿔도 결과가 그대로: 계산 옵션이 수동

    오류 없이 0만 나옴: 텍스트로 저장된 숫자 또는 순환 참조

    #VALUE! 같은 코드가 뜸: 계산이 아니라 수식 내용

    여기서 파일이 망가졌다고 볼 근거는 하나도 없다. 다섯 가지 모두 설정과 입력 형식 문제다.

    2. 시트 전체에 수식이 보이면 Ctrl+`

    수식 셀들이 한꺼번에 =B2*C2 같은 모습으로 바뀌고 열 너비까지 넓어졌다면 수식 표시 모드다. Ctrl+`를 다시 누르면 결과 보기로 돌아온다. 억음 악센트 키는 숫자 1 왼쪽, Tab 키 위쪽에 있다.

    단축키가 안 들으면 수식 탭 → 수식 분석 그룹 → 수식 표시 버튼을 누른다. 수식 표시 버튼은 계산을 멈추는 기능이 아니라 셀에 무엇을 보여줄지 정하는 보기 설정이다. 수식 표시를 끄면 원래 계산 결과가 그대로 나온다.

    3. 텍스트 셀은 일반으로 바꾸고 F2·Enter

    셀 서식이 텍스트면 엑셀은 등호로 시작하는 내용도 문자로 저장한다. Ctrl+1을 열어 표시 형식을 일반으로 바꾼다. 표시 형식을 일반으로만 바꾸면 화면은 그대로다.

    서식 변경은 앞으로 입력할 내용에만 적용된다. 이미 문자로 저장된 수식은 F2를 눌러 편집 상태로 들어갔다가 Enter로 다시 확정해야 계산된다. 이 한 단계가 빠지면 기존 수식은 문자로 남는다.

    셀이 수십 개라면 하나씩 F2를 누를 일이 아니다. 범위를 잡고 데이터 → 텍스트 나누기 → 마침을 바로 누르면 엑셀이 각 셀을 다시 읽는다. 텍스트 나누기는 열 단위로 작동하니 옆 칸에 데이터가 있으면 덮어쓸 수 있다.

    서식이 일반인데도 글자로 보이면 수식 입력줄을 본다. '=SUM(A1:A10)처럼 등호 앞에 작은따옴표가 붙었거나 공백이 들어간 경우다. 등호가 첫 글자가 아니면 수식이 아니다. 외부 시스템이나 CSV에서 붙여 넣은 데이터에서 자주 나온다.

    4. 계산 옵션은 자동, F9는 임시 재계산

    수식 탭 → 계산 그룹 → 계산 옵션 → 자동. 파일 → 옵션 → 수식 → 통합 문서 계산에도 같은 설정이 있다.

    F9를 누르면 값이 맞게 나온다. 그런데 F9는 고치는 단축키가 아니다.

    다시 계산 단축키별 대상

    F9: 열려 있는 통합 문서에서 변경된 수식 다시 계산

    Shift+F9: 현재 워크시트만 다시 계산

    Ctrl+Alt+F9: 변경 여부와 상관없이 열린 통합 문서 전부 다시 계산

    셋 다 수동 설정 자체는 그대로 둔다. 그래서 F9로 한 번 맞춰 놓아도 다음 입력에서 또 멈춘다. 남이 보낸 파일을 열었을 때 내 파일까지 수동이 되는 이유도 계산 옵션이 열린 통합 문서 전부에 영향을 주기 때문이다. 웹용 엑셀은 다르다. 계산 옵션을 바꿔도 현재 통합 문서에만 걸리고 브라우저에 열린 다른 문서는 건드리지 않는다.

    5. SUM이 0이면 텍스트 숫자와 순환 참조

    수식은 살아 있는데 SUM이 0을 내놓으면 더할 숫자가 문자로 저장돼 있을 확률이 높다. 셀 왼쪽 위 초록 삼각형과 왼쪽 정렬로 알 수 있다. 범위를 잡고 경고 아이콘 → 숫자로 변환을 누르면 된다. 원본을 못 건드리는 표라면 =VALUE(A2)로 따로 뽑거나 =SUMPRODUCT(–A1:A10)으로 합산한다.

    SUM이 0이 되는 다른 원인은 순환 참조다. D2에 =D2+B2*C2를 쓴 직접 순환뿐 아니라, D2가 C2를 보고 C2가 다시 D2를 보는 간접 순환도 같다. F3에 =SUM(A3:F3)처럼 합계 범위에 자기 셀이 들어간 것도 순환 참조에 해당한다. 수식 → 오류 검사 → 순환 참조에서 셀 주소를 확인한다.

    순환 참조 하나를 고쳐도 경고가 남으면 같은 시트의 다른 셀, 다른 워크시트, 함께 열어 둔 다른 통합 문서에 순환 참조가 더 있다. 상태 표시줄에서 순환 참조 문구가 사라져야 끝난 것이다. 반복 계산 사용을 켜는 것은 해결이 아니다. 정해진 횟수까지 순환 계산을 허용하는 설정이라, 재무나 공학 모델처럼 일부러 쓰는 경우가 아니면 잘못된 참조를 먼저 고친다.

    6. 오류 코드별 원인과 되살리는 방법

    #VALUE!, #REF!, #DIV/0!, #NAME?가 보이면 엑셀이 계산을 안 한 것이 아니다. 계산을 하다가 문제를 찾은 것이다. 오류 코드가 나온 상태에서 계산 옵션을 자동으로 바꿔 봐야 아무것도 달라지지 않는다.

    오류 코드별 원인

    Q#VALUE!: 숫자 계산에 텍스트가 섞였는지
    Q#REF!: 수식이 보던 행·열·시트가 삭제됐는지

    #DIV/0!: 나누는 셀이 0이거나 빈 셀인지

    #NAME?: 함수 이름, 따옴표, 정의된 이름의 철자

    ####: 오류가 아니라 열 너비 부족

    #REF!는 원래 어느 셀을 가리켰는지 엑셀이 복원해 주지 않는다. 방금 지웠다면 Ctrl+Z가 가장 확실하고, 저장하고 닫은 뒤라면 파일 → 정보 → 통합 문서 관리에서 이전 버전을 찾거나 클라우드 저장소의 버전 기록을 연다. 시트 전체를 삭제한 경우는 되살릴 방법이 없다.

    IFERROR는 오류 원인을 고친 뒤에 쓴다. IFERROR로 빈칸을 만들면 실적이 0이라 비어 있는지 수식이 사라져 비어 있는지 구별이 안 된다. 분모가 0인 게 문제라면 =IF(C10=0,0,D10/C10)처럼 조건을 세우는 쪽이 낫다.

    설정과 입력 형식 때문에 수식이 안 됐다면 정상 결과로 돌아온다. 메뉴 이름과 단축키 동작은 엑셀 버전과 운영체제에 따라 달라질 수 있으니, 단축키가 안 들으면 리본의 수식 탭에서 버튼으로 확인한다. 웹 버전과 모바일 앱은 수식 분석 명령이 제한되고, 데스크톱 앱에서는 순환 참조 셀 주소를 찾을 수 있다.

    #엑셀수식안됨
    #엑셀자동계산
    #수식표시
    #셀서식텍스트
    #순환참조
    #엑셀오류코드
    #F9재계산
    #텍스트나누기
    #엑셀실무