안녕하세요. 행아아빠입니다.
오늘 설명드릴 엑셀 내용으로는 여러개의 시트에 작성된 데이터에서 조건 합계, 개수, 구간별 조건등과 같이 조건에 따라 합계 또는 개수를 계산하는 수식을 작성하는 내용으로 설명 드려 보려고 합니다.
오늘 엑셀 내용에서 사용될 주요 함수는 sumifs, countifs, indirect함수이고 sumifs함수와 indirect함수의 결합 형태, countifs함수와 indirect함수의 결합된 형태로 수식을 작성하는 내용으로 데이터를 시트별로 작성하고 시트별로 작성된 데이터를 한개의 시트에 주요 내용을 취합하고자 할때 활용할 수 있는 유용한 수식 작성 방법이 될것같습니다.
■ 행아아빠의 Youtube 채널.
주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠의 Youtube 채널 후원.
Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
Youtube 영상 댓글 후원 - 각 영상 댓글에 "Super Thanks" 구입 후 댓글 작성.
■ 목차
1. 시간대별로 각 시트에 작성된 데이터의 합계와 개수를 계산하는 샘플 예제 서식 주요 작성 내용.
1-1. 샘플 예제 서식 데이터 편집.
2. 각 시트별 데이터의 시간대별로 주문건수와 가액(매출액)합계 수식 작성.
2-1. indirect함수를 활용한 여러개의 시트 합계와 개수를 계산하는 수식 작성.
3. 샘플 예제 서식 다운로드.
1. 시간대별로 각 시트에 작성된 데이터의 합계와 개수를 계산 하기 위한 샘플 예제 주요 작성 내용.
여러개의 시트에 작성된 데이터의 합계와 개수, 그리고 시간대별 구간의 조건에 따라 결과값을 계산 하기 위한 샘플 예제 서식을 먼저 살펴보도록 하겠습니다.
기본적으로 데이터가 입력된 시트는 31일,1일,2일,3일,4일 5개 시트에 데이터가 입력되어 있고, 취합시트에서는 시간대별로 각 시트에 입력된 데이터의 주문 건수의 합계, 가액의 합계를 계산하도록 합니다.
그런데 여기서 중요한것이 각 시트별로 입력된 시간의 입력 형식이 텍스트 형식으로 입력되어 있고, 2일 시트에만 시간이 입력된 열이 C열이라는점이 되겠습니다.
단순하게 생각하면 주문건수는 countifs함수로 각 시트에 작성된 데이터의 범위를 참조하도록 하여 각각 더해주면 되겠고, 가액합계는 sumifs함수로 수식을 작성해 줄 수 있습니다.
예제서식에서는 시트가 5개이기 때문에 어느정도 수식을 간단하게 작성할 수 있지만, 시트가 일자로 되어 있기 때문에 실제 사용하는 서식에서는 1일~31일까지 시트가 있을수 있고, 예제와 다른 서식의 경우에는 시트가 굉장히 많아질 수 있습니다.
또한 각 시트별로 입력된 시간값이 텍스트 형식으로 입력된 값이기때문에 취합시트에 작성한 시간 구분 조건과 동일한 값으로 인식하지 않기 때문에 단순한 countifs함수와 sumifs함수로 수식을 작성해서 원하는 결과값을 얻을 수는 없습니다.
1-1. 샘플 예제 서식 데이터 편집.
샘플 예제 서식에서 각 시트별로 입력된 데이터값의 열이 일정하지 않고, 시간값이 텍스트 형식으로 입력되어 있기 때문에 수식을 작성했을때 통일된 열을 참조하고 시간값을 계산할수 있도록 원본 데이터를 편집해야 합니다.
하지만 수많은 원본 데이터를 일일이 수정하고 편집하는데는 시간이 오래 걸릴 수 있기 때문에 보조열을 사용해서 원본 데이터에서 필요한 데이터값으로 결과값을 동일한 열에 결과값으로 표기 될 수 있도록 하는것이 간단하게 편집하는 방법이라고 할 수 있습니다.
따라서 각 시트별로 참조해야 하는 범위가 시간이 입력된 데이터로 시간 입력값이 텍스트 형식이기 때문에 D, E, F, G열을 보조열로 D열은 시간, E열은 분, F열은 초, G열은 D열~F열의 시,분,초를 시간으로 결과값이 표기 될 수 있도록 수식을 작성하고, 수식 복사로 필요한 행까지 복사하면 간단하게 보조열에 텍스트로 입력된 데이터값을 시간 형식으로 G열에 모든 시트에서 동일하게 작성할 수 있습니다.
수식은 다음과 같이 각 셀에 작성하고 수식으로 계산되어 결과값을 표기 해야하는 행까지 복사해 주시면 됩니다.
| 1. D열 - 시간 결과값. =HOUR(B3) 2. E열 - 분 결과값. =MINUTE(B3) 3. F열 - 초 결과값. =SECOND(B3) 4. G열 - 시,분,초 "HH:MM" 형식으로 결과값 표기. =TIME(D3,E3,F3) |
만약 보조열을 1개만 사용해서 HH:MM형식으로 시간 결과값을 표기하고자 한다면 G열 기준으로 다음과 같이 수식을 작성 할 수 있습니다.
| =TIME(HOUR(B3),MINUTE(B3),SECOND(B3)) |
그리고 표시 형식이 HH:MM형식으로 표기 되지 않는다면 셀서식 → 사용자지정 → 형식 입력란에 hh:mm으로 입력해주게 되면 hh:mm형식으로 시간 결과값을 표기 할 수 있습니다.
2. 각 시트별 데이터의 시간대별로 주문건수와 가액(매출액)합계 수식 작성.
각 시트별로 입력된 원본 데이터중에서 텍스트로 입력된 시간값을 수식을 작성해서 시간 형식으로 인식할 수 있는 결과값을 작성했고, 참조 하는 열도 통일 되도록 수정을 했습니다.
이제 취합 시트에서 각 시간대별로 모든시트의 주문건수 합계와 가액(매출액) 합계를 계산하는 수식을 작성해야 합니다.
간단하게 주문건수는 각 시간대별로 개수를 카운트를 해야 하기 때문에 countifs함수로 수식을 작성해서 각 시트별 개수를 합산해 주는것으로 결과값을 계산할 수 있고, 가액(매출액) 합계는 시간대별로 가액(매출액) 합계를 합산해줘야 하기 때문에 sumifs함수로 수식을 작성해서 시간대별로 해당하는 값을 각 시트별로 합산해줘서 결과값을 표기 할 수 있습니다.
단순하게 countifs함수와 sumifs함수만으로 수식을 작성하게 되면 다음과 같이 취합시트 F2셀과 G2셀에 작성하고 수식을 복사해서 사용할 수 있습니다.
| 1. F2셀 수식 - 조건 시간대에 해당하는 시트별 주문 건수 합계 계산. =SUM(COUNTIFS('31일'!$G:$G,">="&취합!$A2,'31일'!$G:$G,"<="&취합!$B2), COUNTIFS('1일'!$G:$G,">="&취합!$A2,'1일'!$G:$G,"<="&취합!$B2), COUNTIFS('2일'!$G:$G,">="&취합!$A2,'2일'!$G:$G,"<="&취합!$B2), COUNTIFS('3일'!$G:$G,">="&취합!$A2,'3일'!$G:$G,"<="&취합!$B2), COUNTIFS('4일'!$G:$G,">="&취합!$A2,'4일'!$G:$G,"<="&취합!$B2)) 2. G2셀 수식 - 조건 시간대에 해당하는 시트별 가액(매출액) 합계 계산. =SUM(SUMIFS('31일'!$A:$A,'31일'!$G:$G,">="&취합!$A2,'31일'!$G:$G,"<="&취합!$B2), SUMIFS('1일'!$A:$A,'1일'!$G:$G,">="&취합!$A2,'1일'!$G:$G,"<="&취합!$B2), SUMIFS('2일'!$A:$A,'2일'!$G:$G,">="&취합!$A2,'2일'!$G:$G,"<="&취합!$B2), SUMIFS('3일'!$A:$A,'3일'!$G:$G,">="&취합!$A2,'3일'!$G:$G,"<="&취합!$B2), SUMIFS('4일'!$A:$A,'4일'!$G:$G,">="&취합!$A2,'4일'!$G:$G,"<="&취합!$B2)) |
위 수식으로 작성해도 원하는 결과값은 계산해서 표기 할 수 있습니다.
다만 시트가 추가 되거나 없어지는 시트가 있다면 취합시트에서 계산된 결과값을 확인할때마다 수식을 수정해야 하는 번거로움이 생기게 되고, 참조해야할 시트가 많아지게 된다면 수식은 계속해서 길어지게 되기 때문에 복잡해 보이는 수식이 될 수 있습니다.
이렇게 여러개의 시트안에 입력된 데이터를 참조해서 조건에 따른 합계, 개수등을 계산하는 수식을 단순화해서 작성할때 필요한 함수가 indirect함수입니다.
그림에서 보는것과 같이 sumifs함수와 countifs함수 자체만으로 수식을 작성한 결과값과 indirect함수를 결합해서 작성한 수식의 계산 결과값 차이는 없습니다.
다만 sumifs함수와 countifs함수 자체만으로 수식을 작성하게 되면 시트가 추가 되거나 삭제 됐을 경우의 변경사항이 있을때마다 수식을 수정해야만 결과값을 표기 할 수 있고, 시트가 늘어날수록 수식이 굉장히 길어질 수 있기 때문에 수식을 작성한 본인도 헷갈릴수 있는 수식이 될 수 있습니다.
2-1. indirect함수를 활용한 여러개의 시트 합계와 개수를 계산하는 수식 작성.
indirect함수를 활용해서 수식을 작성하게 되면 여러개의 시트 합계와 개수를 계산하는 수식을 단순화 시킬 수 있는데 indirect함수는 텍스트 문자열로 지정한 셀 주소를 반환하는 함수로 정의하고 있습니다.
따라서 통합문서 내에서 참조할 시트별 이름을 별도로 지정해 줄 필요가 있습니다.
저는 취합시트 뒤에 "참조"라는 이름의 시트를 하나 추가 해서 B2셀부터 B6셀까지 참조해야할 데이터가 포함된 시트 이름을 작성해 주었습니다.
샘플 서식에서는 31일~4일까지 5개의 시트만 있지만 여러분들이 실제 적용할 서식에서 참조해야할 데이터가 포함된 시트를 모두 작성해 주시면 됩니다.
여기까지 시트 이름을 작성해 주고 나면 수식탭 → 이름관리자 → 새로만들기 → 이름 : 일자별시트 → 범위 : 통합문서 선택 → 참조 대상 - 시트이름을 입력한 셀주소 범위순으로 표이름을 정의해 주도록 합니다.
여기까지 정리가 되었다면 sumifs함수와 indirect함수로 수식을 작성하고, countifs함수와 indirect함수를 결합한 형식으로 수식을 C2셀과 D2셀에 작성하면 다음과 같이 작성할 수 있습니다.
| 1. C2셀 - 조건 시간대에 해당하는 시트별 주문 건수 합계 계산. =SUMPRODUCT(COUNTIFS(INDIRECT("'"&일자별시트&"'!g:g"),">="&$A2, INDIRECT("'"&일자별시트&"'!g:g"),"<="&$B2)) 2. D2셀 - 조건 시간대에 해당하는 시트별 가액(매출액) 합계 계산. =SUMPRODUCT(SUMIFS(INDIRECT("'"&일자별시트&"'!a:a"), INDIRECT("'"&일자별시트&"'!g:g"),">="&$A2,INDIRECT("'"&일자별시트&"'!g:g"),"<="&$B2)) |
indirect함수를 활용하게 되면 수식을 단순화 해서 작성할 수 있고, 결과값 또한 오류 없이 잘 계산되어 표기 되는것을 비교해서 확인해 볼 수 있습니다.
작성된 수식들을 살펴보면 사소하지만 중요한 부분이 있는데 이미 알고 계신분들도 계시거나 눈치 채신분들도 계실텐데 참조 범위 안에서 찾고자 하는 조건을 작성하는 부분에서 크거나 같을경우 또는 작거나 같은경우의 조건을 참조 하는 셀을 수식으로 ">="&조건참조셀로 작성되는것을 볼 수 있습니다.
보통 다중조건의 합계 함수 sumifs함수와 다중조건의 개수를 카운트 하는 함수 countifs함수에서 찾고자 하는 조건이 일치하는 값을 찾는경우가 대부분이기 때문에 크거나 같은경우 또는 작거나 같은경우의 조건을 작성하지 못하는경우를 종종 볼 수 있습니다.
이제 여러분들은 이러한 경우에도 조건을 수식에서 직접 입력하지 않고 특정 셀에 찾고자 하는 조건을 작성해서 참조하는 셀로 수식을 작성하실수 있게 되신겁니다.
오늘 설명드린 내용이 전체적으로 실제 업무에서도 활용될 수 있는 부분이기 때문에 중요하다고 하겠지만 조금 더 중요하게 기억해주실 부분으로는 indirect함수를 활용할 수 있는 부분과 다중 조건의 합계 수식을 작성할 수 있는 sumifs함수와 다중 조건의 개수를 카운트 할 수 있는 countifs함수에서 찾고자 하는 조건이 크거나 같을경우, 또는 작거나 같은경우에 조건을 특정 셀을 참조하는 형태로 수식을 작성할때 크거나 작을경우, 그리고 작거나 같은경우 기호 앞뒤에 따옴표를 붙이고 &(앰퍼스트) 다음에 특정셀 주소를 작성한다는 부분을 기억해 주시면 여러분들이 실제 업무에서 사용하는데 조금 더 활용 범위를 넓혀서 사용하실 수 있을것 같습니다.
3. 샘플 예제 서식 다운로드.
오늘 내용도 많은분들에게 도움이 될 수 있기를 바라면서 행아아빠의 내일은엑셀왕 Youtube영상도 좋아요, 구독 많이 많이 부탁 드리겠습니다. 감사합니다.
'엑셀' 카테고리의 다른 글
| 엑셀 기초 배우기 2편 - 데이터입력 및 수식 입력. (0) | 2023.02.09 |
|---|---|
| 엑셀 기초 배우기 1편 - 엑셀 문서 구조 이해와 시작하기. (0) | 2023.02.09 |
| 엑셀 로또 선택한 번호 조합이 과거 당첨 내역중 1,2,3등 번호 조합과 중복되는지 체크할 수 있는 서식과 수식 작성. (0) | 2023.01.16 |
| 엑셀 BOM 관리 가능한 생산관리 생산일보 서식과 자동 계산 수식 작성 방법. (0) | 2022.12.08 |
| 2023년 엑셀 급여 계산 서식과 수식 작성. (1) | 2022.12.06 |
댓글