제공된 그림에 표시된 것과 유사한 수천 개의 값이 있습니다.
K열의 시간 값을 합산하려고 합니다. 그러나 M열에 "flag" 값이 있고 연속된 값만 있는 경우에만 해당됩니다.
예를 들어 셀 M467-M477은 모두 플래그가 지정되어 있으며 함께 추가하면 ~0.017시간이 됩니다.
그런 다음 셀 M478-M480이 무시되고 셀 M481-M483이 ~0.0036시간으로 합산되는 식입니다. 이를 수행하는 자동화된 Excel 수식은 무엇입니까?
스프레드시트 값의 예
답변1
도우미 열이 있는 옵션입니다.
Q2의 공식은 다음과 같습니다.
=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))
그리고 R2에서는
=IF(Q3="",Q2,"")
아래로 복사하세요.
답변2
합계가 각 그룹의 첫 번째 행에 있도록 하려면 다음 공식을 사용할 수 있습니다.
=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")
분석:
두 사람은 INDEX(K:K,MATCH(1E+99,K:K)+1)숫자가 있는 열 K의 마지막 셀을 찾아 이를 참조 범위의 범위로 설정합니다. 이러한 방식으로 수식은 동적이며 새 행이 추가되면 수식이 자동으로 조정됩니다.
그런 다음 Sum 내부에서 동일한 행에 있는 셀로 시작합니다. K2는 상대적이며 수식이 아래로 이동함에 따라 참조도 마찬가지입니다. 범위의 끝 셀은 다른 INDEX/MATCH로 설정됩니다.
이번에는 수식이 있는 행의 첫 번째 셀이 M 열에서 비어 있는지 찾고 있습니다. 해당 행 -1을 SUM()의 마지막 행으로 설정합니다.
IF()는 단순히 합계를 구하고 나머지는 빈 문자열로 만듭니다.
그룹의 맨 아래에 위치시키려면 다음 배열 수식을 사용하십시오.
=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")
배열 수식이므로 편집 모드를 종료할 때 Ctrl-Shift-Enter로 확인해야 합니다. 올바르게 수행되면 Excel에서 {}수식을 입력합니다.
이번에는 첫 번째를 설정하기 위해 현재 행 위의 M에서 마지막 빈 공간을 찾고 있습니다.
IFERROR는 바로 아래 행에 "플래그"가 있는 경우 제목 행을 처리하는 것입니다.
