이번 게시글에서는 엑셀의 INDEX/MATCH 함수 공식과 FILTER 함수를 활용하여 재고 현황을 실시간으로 확인하는 방법을 준비했습니다. 엑셀 모든 버전에서 사용할 수 있는 INDEX/MATCH 공식부터 최신 버전에서 제공되는 FILTER 함수까지, 누구나 손쉽게 따라할 수 있도록 단계별로 정리했습니다.
엑셀 기본 함수만으로 실시간 재고 현황 보고서를 만들어보세요!✨
특히 2019 이전 버전을 사용하는 분들도 복사/붙여넣기로 바로 적용할 수 있도록 완성 공식까지 정리했으니, 이 자료로 재고 관리 업무를 개선해보시기 바랍니다.😊
1️⃣ 엑셀 2021 이후 / M365 버전
· 가용재고 : =FILTER(재고범위,수량범위>0)
· 품절재고 : =FILTER(재고범위,(품목범위<>"")*(수량범위=0))
2️⃣ 엑셀 2019 이전 버전
: 범위를 절대참조(F4)로 작성한 후, 수식을 목록에 맞춰 자동채우기합니다.
: 수식은 배열수식(Ctrl + Shift + Enter)로 입력합니다.
· 가용재고 : =IFERROR(INDEX($재고목록, SMALL(IF(($수량범위>0),MATCH(ROW($수량범위), ROW($수량범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
· 품절재고 : =IFERROR(INDEX($재고목록, SMALL(IF(($수량범위=0)*($품목범위<>""),MATCH(ROW($수량범위), ROW($수량범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
공식의 각 범위를 채워서 입력하면 재고 현황 보고서가 완성됩니다!✨
- 엑셀 2021 이후 버전 : 엑셀 2021 이후 버전에서 제공되는 FILTER 함수를 사용하면 실시간 재고 현황을 편리하게 관리할 수 있습니다. 예제파일을 실행한 후, '가용 재고'를 출력할 D3 셀에 다음과 같이 FILTER 함수를 작성합니다.
=FILTER(A3:B19,B3:B19>0)
/ / 재고 범위(A3:B19) 중, 수량이 0보다 큰 경우만 필터링합니다.
재고 범위에서 수량이 0보다 큰 항목을 필터링합니다.
- 수식을 입력하면 가용 재고 목록이 출력됩니다.
가용 재고 목록이 출력됩니다.
- 작성한 FILTER 함수를 SORT 함수로 묶어서 가용재고를 수량이 많은 제품부터 내림차순으로 정렬할 수 있습니다. D3셀의 수식을 SORT 함수로 묶어서 다음과 같이 작성합니다.
=SORT(FILTER(A3:B19,B3:B19>0),2,-1)
/ / 범위의 2번째 열 기준으로 내림차순 정렬합니다.
수량이 많은 제품부터 내림차순 정렬합니다.
- 이제 수량 범위를 선택한 후, [홈] 탭 - [조건부서식] - [데이터 막대] 에서 원하는 서식의 막대를 적용하면 가용재고 영역이 완성됩니다.
조건부서식의 데이터 막대로 가용재고 목록을 시각화합니다.
- 동일한 과정으로 품절된 재고 목록을 출력할 G3셀을 선택한 후, 다음과 같이 FILTER 함수를 작성하면 품절된 재고 목록이 표시됩니다.
=FILTER(A3:B19,B3:B19=0)
/ / 재고 범위의 수량이 0인 경우만 필터링합니다.
수량이 0인 재고 목록을 출력합니다.
- 만약 재고 범위에서 빈 항목은 제외하고 수량이 0인 경우를 필터링하려면, 다음과 같이 다중 조건 FILTER 함수를 작성합니다.
=FILTER(A3:B19,(B3:B19=0)*(A3:A19<>""))
/ / 수량이 0이고 제품명이 비어있지 않은 항목을 필터링합니다.
FILTER 함수를 다중 조건으로 작성하면, 목록을 더욱 깔끔하게 출력할 수 있습니다.
오빠두Tip : 각 상황별 여러 개의 조건으로 수식을 작성하는 방법은 아래 기초 입문 영상 강의에서 알기 쉽게 정리했으니 참고해주세요!😊
- 엑셀 2019 이전 버전 : 엑셀 2019 이전 버전 사용자는 아래 INDEX/MATCH 공식을 사용해서 재고 현황 보고서를 만들 수 있습니다.
2019 이전 버전에서는 INDEX/MATCH 공식을 사용합니다.
오빠두Tip : 단, SORT 함수의 정렬 기능은 구현이 어렵습니다. 만약 정렬이 필요할 경우, 원본 데이터를 정렬해주세요!😊
- 가용 재고 범위의 시작셀을 선택한 후, 다음과 같이 INDEX/MATCH 공식을 작성하고 Ctrl + Shift + Enter로 수식을 입력합니다.
=IFERROR(INDEX($A$3:$B$19, SMALL(IF(($B$3:$B$19>0),MATCH(ROW($B$3:$B$19), ROW($B$3:$B$19)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
/ / 재고범위 : $A$3:$B$19 와 수량범위 : $B$3:$B$19 는 절대참조로 입력하는 것을 주의하세요!
가용 재고 목록을 출력하는 INDEX/MATCH 공식을 작성합니다.
- 작성한 수식을 출력 범위에 맞춰서 자동채우기하면 가용재고 목록이 완성됩니다.
- 동일한 방법으로 품절된 재고를 출력할 범위 시작셀에 아래 공식을 Ctrl + Shift + Enter로 입력한 후 자동채우기 하면 품절 재고 목록이 완성됩니다.
=IFERROR(INDEX($A$3:$B$19, SMALL(IF(($B$3:$B$19=0)*($A$3:$A$19<>""),MATCH(ROW($B$3:$B$19), ROW($B$3:$B$19)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
/ / 제품명이 빈칸이 아니고 수량이 0인 항목들만 출력합니다. Ctrl + Shift + Enter로 입력합니다.
INDEX/MATCH 공식으로 수량이 0인 재고 목록을 출력합니다.
- 만약 가용재고 목록을 내림차순으로 정렬해야 할 경우, 원본 데이터에서 수량의 시작셀인 B3셀을 선택한 후 [데이터] 탭 - [내림차순 정렬]을 적용하면 수량이 많은 순서대로 정렬할 수 있습니다.
정렬이 필요할 경우, 원본 데이터를 정렬해주세요!😊
