성장

엑셀,

조금 더 효율적이고 간편하게 사용할 순 없을까?🤷‍♀️ 

엑셀로 보고서를 작업하거나 자료 분석을 해야 할 때 사용하는 함수!

실무에서 유용하게 쓰이는 함수들을 이용하여 엑셀 스킬을 올려보는 시간입니다.

추천 스마트 블록 더보기
스마트 카드

엑셀만 잘해도 시간적 여유가 생깁니다!

엑셀 고급 함수 익히면서 소중한 나의 시간을 지켜보아요😉

 

 

오류가 발생할 경우 사용되는 iferror 함수, 이외에도 자주 사용하는 고급 함수 몇 가지 추가적으로 알려 드릴 테니 제대로 익혀서 업무에 적용해 보세요! 함수만 봐도 의욕을 잃어버리는 분들을 위해 쉽~게 설명해 드릴게요❗ 

😊 엑셀 고급자를 위한 함수 3가지 😊

 

IFERROR

DATEDIF

OFFSET


📌 오류가 발생할 경우 지정 값을 반환하는 IFERROR 함수! 

✔ 요약 : 수식이 오류로 평가되는 경우 지정한 값을 반환하는 함수입니다.

✔ 수식 : =iferror(값, 수식이 오류로 평가되는 경우 반환할 값)

엑셀에서 함수를 사용하는 데 오류를 접하는 건 당연한 일❗

단순하게 수식을 잘못 입력했다면 수식을 수정하면 되지만, 수식을 바꿀 수 없다면 오류 값을 추출하는 대신 다른 문자로 출력하도록 하는 iferror 함수를 사용하시면 되는데요!

#DIV/0! 과 같은 오류가 어쩔 수 없이 발생할 상황이라면 보고서에 #DIV/0! 오류가 있는 채로 제출하는 것보다는 “-” 표시나 (공란) 등의 값을 추출하도록 하는 게 보기가 좋겠죠?

 

위의 예시처럼 E3의 매출을 0으로 나누면 당연히 오류를 추출하게 되는 상황!

이때 iferror 함수를 사용하여 오류를 발생할 경우 (–) 를 추출하게 한다면 보고서가 한결 깔끔해지게 됩니다! 어떤 계산식이든 오류가 발생하면 우리가 원하는 다른 값으로 대체되어 표시되기 때문에 오류 처리에 아주 유용하게 사용할 수 있습니다!

👉 여기서 잠깐! 엑셀에서 발생하는 오류의 종류를 알아볼까요?


1. #DIV/0! = 숫자를 0으로 나눈 경우

2. #N/A = VLOOKUP 등의 함수에서 찾을 값이 없는 경우

3. ##### = 열 너비가 충분하지 않거나 날짜가 맞지 않을 경우

4. #NAME? = 함수 이름을 잘 못 썼거나 셀이 존재하지 않을 경우

5. #REF! = 참조하던 셀 주소가 잘못되거나 셀 삭제로 인해 오류가 생기는 경우

6. #VALUE! = 함수의 인수로 값이 제대로 입력되지 않은 경우

7. #NUM! = 수식이나 함수의 인수가 잘못 입력된 경우

8. #NULL! = 범위 연산자를 잘못 사용하거나 교차하지 않는 영역을 참조할 경우


실무에서 사용하는 iferror 함수 예제

👉[지점별 매출분석 (연간)]

 

지점별 연간 매출을 분석할 수 있는 서식입니다. 월별 실적을 입력하면 최고 매출, 최저 매출, 달성률 최고, 달성률 최저 지점을 각각 파악하고, 지점별 목표금액 대비 매출실적 자료를 비교할 수 있습니다. 

 

달성률, 전체 매출 대비, 목표-실적 금액에 입력된 iferror 수식을 사용해 보세요!


📌 날짜 사이의 일, 월 또는 연도를 계산하는 DATEDIF 함수! 

✔ 요약 : 두 날짜 간의 차이를 계산하는 함수입니다.

✔ 수식 : =datedif(시작 날짜, 종료 날짜, “계산 단위”)

엑셀을 사용하다 보면 달력, 근속연수 등 날짜를 계산해야 하는 경우가 생기죠?

일자만 계산하고자 한다면 간단하게 +, - 를 사용해서 계산하면 되지만 연수, 월수까지 계산해야 하는 상황이 생긴다면 datedif 함수를 사용하시면 편합니다!

 

재직증명서 등 서류 발급을 담당하시는 분들이라면 재직일수, 재직월수, 재직연수 계산하기 힘드셨던 기억이 있으실 거예요! 이때 간단하게 datedif 함수를 사용한다면 쉽게 계산 가능하답니다!!😉

 

시작 일자, 종료 일자에는 날짜가 입력되어 있는 특정 셀을 지정해도 되지만, “2023-07-05”의 방식으로 직접 날짜를 입력하셔도 되고, 오늘 날짜를 추출하는 today 함수를 사용하여 today() 를 입력하셔도 됩니다! 

계산 단위에는 “Y”=연도, “M”=월, “D”=일자가 주로 사용되며, 조금 더 알아보자면 “YD”는 연도 차이를 생략한 두 날짜 사이의 일자 수, “MD“는 연도, 월 차이를 생략한 두 날짜 사이의 일자 수, “YM”은 연도 차이를 생략한 월 차이를 계산합니다.

 

정말 간단하고 활용성 있는 함수지만 datedif를 처음 들어보시거나, 사용방법을 모르시는 분들을 위해 알려드렸습니다!

 

 

👉 만 나이 계산도 datedif 함수로!! “달라진 만 나이 제도 A to Z” 알아보고, 관련 서식도 보고 가세요!😉


📌 셀이나 셀 범위에 대한 참조가 필요한 경우 OFFSET 함수! 

✔ 요약 

셀 또는 셀 범위를 기준으로 지정한 행, 열만큼 떨어진 위치에서 주어진 높이와 너비의 셀 범위를 구하는 함수입니다.

✔ 수식

1) 이동만 적용할 때 =offset(기준 셀, 행, 열)

2) 이동한 지점을 기준으로 새로운 영역을 설정할 때 =offset(기준 셀, 행, 열, 행, 열)

offset 함수는 지정된 범위에서 특정 위치로 이동하여 값을 가져오는 역할을 하는 함수입니다. 셀 위치를 기준으로 이동하여 데이터를 추출하거나 범위를 동적으로 조정할 수 있습니다.

 

위의 예시처럼 B3의 값 [김○○]을 기준으로 0, 0 으로 행/열 이동을 하지 않음을 의미하기 때문에 B3에 그대로 있겠죠? 또한 아래로 3개 영역, 1열의 넓이를 참조하겠다고 수식을 입력한다면, 추출 값에는 B3~B5까지의 영역을 설정하는 값을 보이게 됩니다!

 

 

 

💡 offset 함수를 이용한 드롭다운 박스 만들기, 어렵지 않아요! 같이 따라 해보세요!!

 

사실 offset 함수는 혼자 있을 때에는 크게 기능을 하지 못하는데요!

비즈폼 서식에서는 counta 함수와 offset 함수를 혼합하여 자주 사용하는데, 이는 드롭다운 박스를 만들 때 주로 활용합니다.

급여내역 작성 시 직원관리 시트에만 입력해두면 급여내역에서 드롭다운 박스로 직원을 선택 가능하게 하는 기능! 천천히 따라 해보세요!🙋‍♀️

👉 여기서 잠깐! 드롭다운 박스 만드는 순서를 익혀봅시다!


1) 엑셀 상단의 [수식]-[이름 관리자]를 열어줍니다. (단축키 : Ctrl + F3)

2) [새로 만들기]를 클릭합니다.

3) 이름명 설정 후 수식을 입력합니다.

    수식 : =offset(Sheet1!$B$3,0,0,counta(Sheet1!$B$3:$B$1048576),1) 4) 확인을 클릭합니다.

5) 드롭다운 박스를 만들 셀을 클릭합니다.

6) 엑셀 상단의 [데이터] – [데이터 유효성 검사]를 클릭합니다.

7) 제한 대상 : 목록, 원본 : 3)에서 설정한 이름을 입력합니다.

8) 확인을 클릭합니다.

매입과 매출을 한 번에 관리할 수 있는 매입매출 자금관리 프로그램입니다. 매입 지급 내역, 매출 수금 내역을 입력하여 일계표, 월계표, 총계정원장 등을 조회할 수 있습니다.

[매입매출 자금관리 프로그램]은 offset 함수를 활용하여 이름 관리자를 등록한 프로그램으로 통장 내역, 거래처, 계정과목 등을 편하게 드롭다운 박스로 선택할 수 있는 서식입니다! 천천히 사용해 보면서 offset 함수를 어떻게 응용할지 익혀보세요😉

엑셀TIP 스마트 블록 전체보기
최근 본 콘텐츠
1건