안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
엑셀(excel)을 사용하면서 여러개의 시트에 일자별 또는 월별로 데이터를 정리해서 입력하는 경우 있으실겁니다.
이렇게 여러개의 시트로 나눠서 일자별, 월별로 정리된 데이터를 하나의 시트에 취합하고자 할 경우 다중 조건 합계 함수 sumifs와 다중 조건 카운트 함수 countifs를 사용하면 각각의 시트에 작성된 데이터를 하나의 시트로 데이터를 취합하는데 굉장히 유용한 함수로 수식을 작성할 수 있습니다.
오늘은 이렇게 여러개의 시트에 정리된 데이터를 하나의 시트로 모으는데 시트개수가 너무 많아서 sumifs함수와 countifs함수를 사용하는데 수식이 길어지거나 복잡해지는것을 단순화 할 수 있는 수식 작성 방법을 기록해 보고자 합니다.
바로 sumifs함수 또는 countifs함수와 indirect함수를 각각 결합한 형태로 수식을 작성해 주면 되는것인데, 어떻게 수식을 작성하고 활용하는지 글쓰는이작가에서 준비한 샘플 예제 서식에서 함께 살펴 보기로 하겠습니다.
1. 샘플 예제 서식 주요 작성 내용.
샘플 예제 서식에서는 여러개의 시트에서 정리된 데이터를 한개의 시트에 내용을 취합하고자 하기 위해서 31일, 1일, 2일, 3일, 4일의 시트에 각각 가액(매출액)과 주문시간을 정리해 두었고, 주문시간은 텍스트의 형태로 입력이 되어 있습니다.
그리고 취합 시트에서는 30분단위의 시간대별로 주문건수 합계와 가액(매출액) 합계를 31일, 1일, 2일, 3일, 4일 시트에서 정리된 데이터를 참조해서 결과값이 표기 될 수 있도록 수식을 작성하려고 합니다.
다만 2일 시트에 입력된 주문시간 데이터만 다른 시트와 다른열에 작성되어 있어 서식의 형식이 모두 통일되어 있다고 보기 어렵습니다.
또한 주문시간의 표기형식이 텍스트 형식이기 때문에 시간으로 인식하지 않고 문자로 인식하고 있다는점이 우리가 수식을 작성하고 올바른 결과값을 가져오기 위해서 고민해야할 부분이 되겠습니다.
1-1. 수식을 작성하기 위한 샘플 예제 서식 원본 데이터 편집.
취합시트에 주문건수 합계와 가액(매출액) 합계를 각 시트별로 그리고 30분단위 시간대별로 계산하기 위한 수식을 작성하기 위해서는 다중 조건 개수를 카운트 할 수 있는 함수 countifs함수와 다중조건의 합계를 계산하는 함수 sumifs함수로 수식을 작성하면 해결될것으로 생각이 되죠.
하지만 문제가 각 일자별에 정리된 시간 데이터값이 텍스트 형식이고 2일자 시트에 시간이 입력된 열이 C열에 입력되어 있기 때문에 각 일자별 원본 데이터에서 보조열을 사용해서 텍스트로 입력된 시간을 시간형식으로 인식할 수 있도록 재입력해주고, 참조 하는 열을 통일 시켜주도록 하면 수식을 조금 편하게 작성할 수 있을것 같습니다.
따라서 그림에서 보는것과 같이 D열은 시간, E열은 분, F열은 초를 나누어 입력하고 G열에는 또다시 D열~F열을 참조해서 시간 형식으로 시,분,초를 입력해주게 되면 시간을 참조하는 열은 G열이고 텍스트로 입력된 시간 역시 시간형식으로 인식할 수 있도록 바꿔줄 수 있습니다.
다만 정리된 데이터의 양이 많을 경우에는 이렇게 재입력해주는 시간이 굉장히 오래 걸릴수 있기 때문에 수식을 작성한다음 수식을 복사해주는것이 빠르게 데이터를 편집하는 방법이라고 볼 수 있습니다.
수식을 작성하면 다음과 같이 작성하고 필요한 행까지 수식을 복사해서 사용할 수 있습니다.
| 1. D열 - 시간 =HOUR(B3) 2. E열 - 분 =MINUTE(B3) 3. F열 - 초 =SECOND(B3) 4. G열 - 시,분,초 형식의 시간 =TIME(D3,E3,F3) |
그런데 이렇게 수식을 작성하게 되면 보조열을 4개를 사용해야 하기 때문에 보조열 1개만 사용하게 되면 다음과 같이 수식을 작성할수도 있겠습니다.
| =TIME(HOUR(B3),MINUTE(B3),SECOND(B3)) ※ 수식 작성 후 결과값이 시간형식이 아니라면 마우스 우클릭 → 셀서식 → 사용자지정 → 형식 : hh:mm 입력 후 확인. |
보조열에 텍스트로 입력된 시간값을 수식을 작성해서 시간형식으로 시간값을 입력해주게 되면 원본 데이터를 편집하는 시간이 굉장히 빨라지겠습니다.
2. 시트별 데이터의 시간대별 주문건수 합계와 가액(매출액) 합계 계산 수식.
시트별로 정리된 원본 데이터에서 텍스트 형식으로 입력된 시간을 수식을 이용해서 시간 형식으로 재입력해주고, 시간이 입력된 열이 통일되었기 때문에 취합시트에서 작성할 수식이 참조하는 열이 통일되었기 때문에 조금 수월하게 수식을 작성할 수 있습니다.
수식을 작성하는 방법에는 2가지 방법이 있는데 다중 조건 개수를 카운트 하는 countifs함수와 다중조건 합계를 계산하는 sumifs함수 자체만을 활용해서 작성하는 방법과 오늘 내용중에서 중요한 부분으로 indirect함수를 추가적으로 활용해서 수식을 작성하는 방법으로 작성해 볼 수 있습니다.
indirect함수를 추가적으로 활용해서 수식을 작성했을때 어떻게 다른지 직접 확인해 보시길 바라겠습니다.
다중 조건 개수를 카운트 하는 함수 countifs함수와 다중 조건 합계를 계산 하는 함수 sumifs함수 자체만으로 원하는 결과값을 작성하면 다음과 같이 수식을 작성할 수 있습니다.
| 1. F열 시간대별로 시트별 주문건수 합계. =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. G열 시간대별로 시트별 가액(매출액) 합계. =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)) |
sumifs함수와 countifs함수 자체만으로도 원하는 결과값을 표기 할 수 있는 수식을 작성 할수 있지만, 만약 데이터가 정리된 시트가 많을 경우에는 해당 수식이 각 시트별로 참조해야 하기 때문에 굉장히 길어질 수 있고, 길어진 수식은 수식을 작성한 본인조차 복잡해 보일 수 있기 때문에 수식의 수정이 필요한경우 헷갈릴수 있는 단점이 있을 수 있습니다.
또한 일자별 또는 월별로 정리되는 데이터일경우 시트가 추가 되거나 삭제될경우 해당 수식에서 참조 영역이 추가 되는 시트 또는 삭제되는 시트의 참조 영역을 계산하는 수식 범위는 수정해줄 필요가 있기 때문에 매번 수식을 수정해줘야 하는 불편함이 발생할 수 있습니다.
이러한 수식의 단점을 보완하기 위해서 오늘의 주요내용으로 sumifs함수와 countifs함수에 indirect함수를 인수로 사용해서 수식을 작성해 주게 되면 추가 되는 시트 또는 삭제되는 시트가 생기더라도 수식을 수정해줄 필요가 없고 수식 또한 간결하게 작성할 수 있게 됩니다.
indirect함수를 활용해서 수식을 작성하기 위해서는 indirect함수가 참조하는 표를 먼저 작성해 줘야 하는데 복잡하게 작성해야 하는 표는 아니고 일자별 시트의 이름을 작성해주는 표를 작성해 주는것이 필요 합니다.
저는 참조 시트를 추가해서 시트별 이름을 작성해 주었고 여러분들이 실제 업무에 사용하는 일자별, 월별과 같은 시트의 이름을 작성해 주고 표의 이름을 정의해 주시면 됩니다.
이름정의는 수식탭 → 이름관리자 → 새로만들기 → 이름 : 일자별시트 → 범위 : 통합문서 선택 → 참조 대상 : 시트 이름을 입력한 표 범위로 설정을 해 주시면 됩니다.
여기까지 준비가 되었다면 indirect함수를 sumifs함수와 countifs함수를 결합해서 수식을 작성하면 다음과 같이 작성하고 결과값을 확인해 볼 수 있습니다.
| 1. C열 - 각 시트별 데이터에서 시간대별 구간 조건에 일치 하는 주문건수 합계 계산 수식. =SUMPRODUCT(COUNTIFS(INDIRECT("'"&일자별시트&"'!g:g"),">="&$A2, INDIRECT("'"&일자별시트&"'!g:g"),"<="&$B2)) 2. D열 - 각 시트별 데이터에서 시간대별 구간 조건에 일치 하는 가액(매출액) 합계 계산 수식. =SUMPRODUCT(SUMIFS(INDIRECT("'"&일자별시트&"'!a:a"), INDIRECT("'"&일자별시트&"'!g:g"),">="&$A2,INDIRECT("'"&일자별시트&"'!g:g"),"<="&$B2)) |
여기까지 sumifs + indirect함수로 수식을 작성하는 방법, countifs + indirect함수로 수식을 작성하는 방법과 시간대별 구간의 조건과 일치하는 결과값을 표기하기 위한 수식을 작성해 보았습니다.
여기서 하나 여러분들이 주의깊게 또 하나 기억해야 할 부분으로는 눈치 채신분들도 계실텐데 보통 sumifs함수와 countifs함수를 사용해서 수식을 작성하고자 할때 찾고자 하는 조건과 정확히 일치하는 값을 찾는경우가 많습니다.
그런데 샘플 예제 서식에서는 시간이라는 구간에 일치하는 값을 계산하는것이기 때문에 비교 구간을 조건으로 표기 해야 합니다. 따라서 조건값과 일치하거나 작은경우, 크거나 같은경우를 생각해야 하는데 찾고자 하는 구간 조건값을 특정 셀에 작성해 두었다면 비교값 기호는 >= 또는 <=와 같이 작성해 주고 조건값이 입력된 셀주소를 작성해 주어야 하는데 &(앰퍼스트)기호로 부등호 기호와 연결시켜 주어야 한다는것입니다.
특히 부등호 기호는 앞뒤로 ""(따옴표)를 붙여주고 &(앰퍼스트) 기호 다음에 셀주소를 입력해 주게 되면 된다는것을 기억해 주시길 바라겠습니다.
오늘 sumifs함수, countifs함수, indirect함수의 활용 방법을 참고 하셔서 여러분들의 실제 업무에서도 활용해 보시길 바라겠습니다. 오늘도 글쓰는이작가의 긴 글을 읽어주셔서 감사합니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"엑셀(Excel) 여러 시트의 시간대별 조건 합계, 조건 개수 계산 수식 작성 방법." 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ 글쓰는이작가 샘플 예제 - 시간 구간별 시트별 조건 개수 합계 계산 예제
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 속도가 느려질때 확인하고 해결해줘야 하는것들. (1) | 2022.12.07 |
|---|---|
| 엑셀(Excel) 급여 계산 자동 서식과 수식 작성 그리고 2023년 4대보험 요율 최저시급 변경을 확인해 보자. (0) | 2022.12.06 |
| 엑셀(Excel) 가계부 서식과 자동 계산 수식 작성을 해보자. (0) | 2022.11.29 |
| 엑셀(Excel) 네트 급여 세후 급여로 세전 급여 추정 계산 수식 작성을 해보자. (0) | 2022.11.28 |
| 엑셀(Excel) 숫자로 보이는 문자 데이터 숫자로 변환하는 수식을 작성해보자. (1) | 2022.11.25 |
댓글