여러 시트에서 원하는 값 구해서 합하기(1)

여러시트에서 조건에 맞는 합계수량 구하기.xlsx

0.46MB

 

1. 문제

 

아래와 같이 1호기부터 5호기까지 일자별 작업 내용이 있고, 

 

 

작업일자 중 마지막 날짜의 작업량 합계를 주, 야간을 구분해서 구하고자 하는 문제입니다.

 

2. 해결 방법

가. 호기별로 최종 작업 일자 구하기

LOT NO(K 열)이 있고, 작업량(P열)이 0보다 큰 것을 구하므로 배열 수식을 이용해야 합니다.

수식은 =INDEX(A:A,MAX(IF((K:K<>"")*(P:P>0),ROW(K:K)))) 인데,

배열 수식이므로 동적 배열 수식을 지원하는 경우는 CSE(Ctrl + Shift + Enter)키를 입력할 필요 없이 Enter키만 누르면 되며, 지원하지 않는 경우(레거시 배열 수식)는 수식을 작성한 후 CSE키를 눌러야 합니다.

 

(수식의 이해)

 

=INDEX(A:A,MAX(IF((K:K<>"")*(P:P>0),ROW(K:K))))

 

index함수의 구문은 INDEX(array, row_num, [column_num])로 첫 번째 인수가 배열이고, 두 번째 인수가 행 수, 세 번째 인수가 열 수인데 세 번째 인수는 생략가능합니다.

 

위에서 A:A이 A열이라는 범위 배열이고, MAX(IF((K:K<>"")*(P:P>0),ROW(K:K)))가 row_num에 해당합니다.

 

MAX(IF((K:K<>"")*(P:P>0),ROW(K:K))) 에서

Max는 최댓값을 구하는 함수이고,

 

IF((K:K<>"")*(P:P>0),ROW(K:K))에서

 

(K:K<>"")*(P:P>0)는 K열이 빈칸이 아니고 P열이 0보다 크다는 And 조건이고,

 

ROW(K:K)는 K열의 행 수를 반환하는 것으로 1부터 맨 아래 행 수까지의 배열입니다.

 

따라서, 위의 1호기 예를 보면 LOT NO가 공백이 아니고 작업량이 0보다 큰 행중 가장 큰 행에 해당하는 날짜를 반환해 줍니다.

 

12/21이 LOT NO가 있고, 작업량이 있는 최종 날짜이므로 45281이 구해지는데, 일반 형식이라 그러므로 

 

홈 탭 > 표시 형식 그룹에셔 일반을 간단한 날짜 형식으로 바꾸면 

 

날짜로 표시됩니다.

 

나. 호기별로 최종 작업 일자의 작업량 구하기

이제 최종 작업일자가 구해졌으므로 해당 일자에 대한 작업량의 합계를 구하면 되는데,

SumIf, SumIfs 둘 다 가능합니다.

 

다만 구문이 SumIf의 경우에는 더할 범위가 마지막에 나오고, SumIfs함수의 경우에는 첫 번째로 지정해야 합니다.

따라서 수식은 =SUMIFS(P:P,A:A,AA128)가 되며,

 

12/21이 하나뿐이 없으므로 385가 구해집니다.

 

 

다. 모든 호기 중에서 가장 큰 최종 작업 일자 구하기

 

요약장표 시트로 이동합니다.

 

1호기의 최종일자와 그 날짜의 생산량 합계를 구해보겠습니다.

 

J1셀에서 =index(까지 입력한 후

 

1호기 시트의 A열을 클릭하면 1행이 셀 병합이 되어 있어 '1호기'!A:Y라고

시트명이 작은따옴표 안에 있고, 열 이름 앞에! 가 붙습니다.

마우스로는 A열을 지정할 수 없으니, A:Y에서 Y를 A로 바꾸고, 쉼표(,)를 입력합니다.

 

그리고, max(if((한 다음 k열을 클릭해야 하는데 마찬가지로 '1호기'!A:Y라고 입력되므로 A:Y를 k:k로 수정하고,

 

 

조건 <>""를 입력하고 괄호를 닫은 다음 And 조건이므로 *를 입력하고

1호기 시트의 P열이 0보다는 크다는 조건을 ('1호기 '!p:p>0)라고 입력합니다.

 

그리고, 참일 때의 값을 row('1호기'!k:k)라고 입력하고 거짓일 때의 값을 입력할 필요 없이 if함수의 괄호를 닫고, max함수의 괄호와 index함수의 괄호, 총 3개의 괄호를 닫습니다.

 

 

그리고 엑셀 버전에 따라 엔터 키 또는 CSE 키를 누르면 

 

값 12/21이 구해집니다.

 

조건에서 K열이 공백이 아니라는 조건은 정확성을 기하고자 한다면 있어야 하지만, 간단하게 하려고 하면 빼도 됩니다.

 

이렇게 호기별로 최종일을 구하는 수식을 5번 입력한 다음 그중에서 최댓값을 구해야 비로소 호기 중 최종일이 됩니다.

따라서, 수식은 =MAX(INDEX('1호기'!A:A,MAX(IF(('1호기'!K:K<>"")*('1호기'!P:P>0),ROW('1호기'!K:K)))),INDEX('2호기'!A:A,MAX(IF(('2호기'!K:K<>"")*('2호기'!P:P>0),ROW('2호기'!K:K)))),INDEX('3호기'!A:A,MAX(IF(('3호기'!K:K<>"")*('3호기'!P:P>0),ROW('3호기'!K:K)))),INDEX('4호기'!A:A,MAX(IF(('4호기'!K:K<>"")*('4호기'!P:P>0),ROW('4호기'!K:K)))),INDEX('5호기'!A:A,MAX(IF(('5호기'!K:K<>"")*('5호기'!P:P>0),ROW('5호기'!K:K)))))이 됩니다. 

수식 입력줄이 1줄이라서 수식이  다 보이지 않으면 수식 입력 줄의 아랫부분에 커서를 놓고 양쪽 화살표가 됐을 때 끌어서 내리면 됩니다.

 

라. 최종 작업일에  해당하는 작업량의 합계 구하기

1호기 시트에서 작업할 때는 '1호기'!가 필요 없는데 요약장표 시트에서 작업하기 때문에 '1호기'!를 SumIfs 함수 안에 넣어줘야 합니다.

 

그리고, 주, 야간이 구분되어 있는데, 주, 야간 구분은 호기별로 C열에 있는데, '주' 또는 '야' 한 글자로 되어 있으므로 요약장표의 j열에서 주간, 야간 중 왼쪽 한 글자를 가져오면 됩니다.

 

따라서, 1호기의 최종일과 주간에 해당하는 생산량의 합계를 구하는 수식은

=SUMIFS('1호기'!P:P,'1호기'!A:A,$J$1,'1호기'!C:C,LEFT(J3,1))이 됩니다.

 

세 번째 인수가 날짜인데, J1셀에 있고, 수식을 아래에 복사할 때 변하면 안 되므로 F4키를 눌러 절대참조 형식으로 바꿨습니다.

 

이제 호기의 총합계를 구하려면 마찬가지로 1호기부터 5호기까지의 생산량을 구한 후 Sum을 구해야 합니다.

 

따라서, 수식은  =SUM(SUMIFS('1호기'!P:P,'1호기'!A:A,$J$1,'1호기'!C:C,LEFT(J3,1)),SUMIFS('2호기'!P:P,'2호기'!A:A,$J$1,'2호기'!C:C,LEFT(J3,1)),SUMIFS('3호기'!P:P,'3호기'!A:A,$J$1,'3호기'!C:C,LEFT(J3,1)),SUMIFS('4호기'!P:P,'4호기'!A:A,$J$1,'4호기'!C:C,LEFT(J3,1)),SUMIFS('5호기'!P:P,'5호기'!A:A,$J$1,'5호기'!C:C,LEFT(J3,1))) 이 됩니다.

 

이제 K3셀의 채우기 핸들을 K4셀까지 끌고, K5셀에는 홈 탭의 편집 그룹에서 자동 합계 명령을 누르면 완성됩니다.

 

너무 복잡하죠?

 

여러시트에서 조건에 맞는 합계수량 구하기(1편 완성).xlsx

0.46MB

 

그래서 2편에서는 Lambda함수를 이용해서 사용자 지정 함수를 만들어서 간단하게 해 보겠습니다.