엑셀 SUMIFS 함수에 INDIRECT 함수 적용하여 자동 서식 활용

엑셀 SUMIFS 함수에 INDIRECT 함수를 활용하여 엑셀 자동화 서식을 만드는 방법을 알아보도록 하겠습니다. 엑셀 자동화 서식이라는 말이 대단하게 들리실 수도 있지만, 한번 작성한 수식 및 논리적 구조를 바탕으로 원본데이터의 교체 및 추가 만으로 원하는 데이터를 추출하는 것을 의미합니다. 앞으로 더 나아가 RPA에 대한 활용까지 더한 다면, 원본데이터의 교체 및 추가 역시 로봇이 대신해 주게 됩니다 

 

 

엑셀 SUMIFS 함수와 INDIRECT 함수

엑셀 SUMIFS 함수

엑셀 SUMIFS 함수의 정의 및 활용법에 대해서는 아래 링크에서 확인해 보실 수 있습니다. 

엑셀 SUMIFS 함수 정의 및 구문, 활용 방법 (엑셀 더하기 함수)

 

엑셀 INDIRECT 함수

엑셀 INDIRECT 함수의 정의 및 활용법에 대해서는 아래 링크에서 확인해 보실 수 있습니다. 

엑셀 INDIRECT 함수 사용 (엑셀 범위 이름 지정 및 테이블 기능 사용)

 

엑셀 SUMIFS 함수에 INDIRECT 함수 적용하여 엑셀 자동화 서식

엑셀 SUMIFS 함수와 INDIRECT의 조합을 활용하여 엑셀 자동화 서식을 만드는 방법을 공유드리도록 하겠습니다.

 

옵션 1) 아래와 같은 여러 개의 데이터가 각각의 시트에 있는 엑셀 파일이 있습니다. 

총 5일의 월, 화, 수, 목, 금의 각각의 시트에 판매수치에 대한 결과가 3가지 항목인 판매자, 상품, 판매수로 입력되어 있습니다. 

월, 화, 수, 목, 금 총 5개의 시트에 각각의 판매 수치가 있습니다.

 

옵션 2) 각각의 시트의 데이터를 하나의 대표 시트에서 SUMIFS와 INDIRECT 함수를 통해 원하는 값을 확인하겠습니다.  

SUMIFS&INDIRECT라는 이름의 시트를 만들어 판매자 항목과 상품 항목을 리스트에서 선택하였을 때 월, 화, 수, 목, 금의 판매 수치를 5개의 시트에서 불러와 출력하도록 작성하였습니다. 

엑셀에 시트를 추가 하여 5개의 서로다른 시트의 데이터를 출력 할 수 있도록 만들었습니다.

작성된 시트는 요일별 시트의 변화 수치에 바로 적용되며, 시트의 추가에도 수식의 변화 없이 적용 가능합니다. 

항목 LIST 작성에 대해 궁금하신 분은 아래 링크를 확인 부탁 드립니다. 

 

엑셀 데이터 유효성 검사의 리스트 기능 사용 하여 셀에서 선택하기

 

엑셀 SUMIFS 함수와 INDIRECT 함수의 조합에 사용된 엑셀 함수 구문

일반적인 SUMIFS 함수를 사용하여 D5 셀에 판매자와 상품의 항목을 넣어 두 조건의 총합을 구할 때는 일반적인 엑셀 SUMIFS 함수 구문을 사용합니다. 

 

D5 CELL  =SUMIFS(월!C:C,월!A:A,'SUMIFS&INDIRECT'!B5,월!B:B,'SUMIFS&INDIRECT'!C5)

 

하지만 해당 엑셀 함수 구문은 5개의 연속된 시트에서 원하는 데이터를 불러오기 위해 계속적인 범위 설정이 필요합니다. 

이제 아래 그림에 사용된 엑셀 SUMIFS 함수와 엑셀 INDIRECT 함수에 대해 알아보겠습니다.

 

D5 CELL  

=SUMIFS(INDIRECT("'"&D4&"'"&"!$C:$C"),INDIRECT("'"&D4&"'!$A:$A"),'SUMIFS&INDIRECT'!$B5,INDIRECT("'"&D4&"'!$B:$B"),'SUMIFS&INDIRECT'!$C5)

 

SUMIFS와 INDIRECT 함수를 사용하여 D5 셀의 수식을 화,수,목,금에 복사하여 엑셀 자동화 서식을 만들었습니다.

해당 엑셀 SUMIFS, INDIRECT를 사용하여 위와 같이 자동화 서식을 만들었습니다.

 

엑셀 SUMIFS, INDIRECT 함수 구문 상세 설명

엑셀 INDIRECT의 자세한 설명은 위의 링크에서 확인하실 수 있기 때문에, 반드시 이해가 필요한 사항에 대해서만 말씀드리겠습니다.

엑셀 INDIRECT의 기능은 "문자"를 "범위"로 변환해 주는 기능이 있습니다. 엑셀에서 문자는 문자로만의 기능이 있습니다. 

 

예를 들어 월! C:C를 만들기 위해서 "월"이라는 문자가 있는 D4 셀과 연결하여 D4&"!C:C" 로 함수구문을 만들어도 문자를 범위로 변환하는 기능을 사용 안 했기에 범위로써 작동되지 않습니다. 

 

INDIRECT 함수를 활용하여 문자를 범위로 변환하였습니다.

위에 사용된 INDIRECT의 구문을 이해하기 위해서는 엑셀에서의 TEXT 서식에 대해 어느 정도 지식이 있으셔야 합니다. 

아래의 링크는 TEXT 서식에 대한 몇 가지 활용법입니다. 

 

엑셀 2개 VLOOKUP 값을 TEXT 함수로 서식 유지 하며 붙이는 방법

 

엑셀에서 서식의 조합은 &를 활용하게 됩니다. &는 셀과 셀을 직접적으로 연결해 주지만, 문자와 문자를 연결하기 위해서는 각 문자를 큰따옴표("문자")로 감싸줘야 연결이 가능해집니다. 

 

= A1&A1 (셀과 셀의 연결은 &로 직접 가능합니다.) 

="문자"&"문자" (문자와 문자의 연결은 " " 큰따옴표로 문자를 감싼 뒤 & 로 연결합니다.)

 

위의 내용이 이해가 가셨다면, SUMIFS 함수에 사용된 INDIRECT 함수 구문을 살펴보겠습니다. 

 

월!C:C = INDIRECT("'"&D4&"'"&"!$C:$C")

월!A:A = INDIRECT("'"&D4&"'!$A:$A")

 

위의 2가지의 서로 다른 INDIRECT 함수 구문은 동일한 결과를 의미합니다. &의 활용을 2번 할 경우와 1번 할 경우를 보여드리기 위해서 2가지의 방법을 공유드렸습니다. 

여기서 D4 셀에서는 ' ' 작은따옴표가 빨간색으로 있는 것을 확인하실 수 있습니다. 앞서 설명드린 INDIRECT의 기능처럼 문자를 범위를 변환해 주는 기능이기 때문에, 사용된 ' ' 작은따옴표는 셀값의 문자 변환으로 이해하시면 되십니다. 

 

INDIRECT 함수와 TEXT 함수에 대한 추가설명입니다.

 

결론

 

엑셀 SUMIFS 함수에 INDIRECT 함수를 활용하여 엑셀 자동화 서식을 만드는 것에 설명드렸습니다. 엑셀 sumif 함수와 엑셀 sumifs 함수는 여러 함수로 대체할 수 있는 함수 중 하나입니다. 대체 가능한 함수인 SUMPRODUCT 함수에 대해서 링크하였습니다. 

 

엑셀 SUMPRODUCT 함수 사용하기(SUMIFS, COUNTIFS 대체 함수)