각 행을 기준으로 매월 시간의 합계를 계산하는 스프레드시트를 만들려고 합니다. 예제를 좀 더 명확하게 하기 위해 아래 Excel의 경우 클라이언트 1에 대해 28, 클라이언트 3에 대해 16의 값을 얻어야 합니다.
SUMIF 함수를 사용하려고 하는데 합계 범위가 여러 셀에 포함될 수 없습니다.
그리고 이것이 제가 정보를 보고 싶은 방법입니다:
답변1
간단한 수식을 만들 수 있습니다.
=SUM(IF(B5:B21="Client 1",G5:AK21))
이는 2단계로 배열 계산을 수행하는 것입니다.
- 열 B = "클라이언트 1"인 경우 전체 행의 숫자(공백 포함)를 반환합니다.
- 모든 행(대부분 공백이 포함됨)이 있으면 해당 값을 합산하면 됩니다.
"Client 1"원하는 경우 수식을 외부 셀에 대한 참조로 바꿀 수 있습니다 . 또한 수식의 두 번째 부분에서 해당 월의 전체 범위가 정확한지 확인하세요 G5:AK21.
답변2
탁월한 =SUM(IF())접근 방식을 기반 으로 구축@데이브을 사용하는 것이 가능하다OFFSET1년의 12개월 각각에 해당하는 열을 선택하는 기능입니다.
이 OFFSET함수는 높이와 너비를 통해 직사각형 범위를 정의합니다. 범위의 왼쪽 위 모서리는 지정된 워크시트 셀의 오른쪽 아래 행 수와 오른쪽 열 수로 정의됩니다. 아래 접근 방식에서는 노란색으로 강조 표시된 셀을 지정된 셀(cell E17)로 사용합니다.
범위 A2:E14(아래 스크린샷 참조)는 일부 정보를 제공합니다.메타데이터, 강조 표시된 셀을 기준으로 1년 중 12개월 각각에 대한 일일 값 열의 위치에 대한 정보입니다. 유용한 값은 제목이 붙은 열에 있습니다.Col_Offset그리고열예를 들어, 1월의 일일 값은 노란색 셀 오른쪽의 첫 번째 열에서 시작하여 31개의 열을 차지하고, 2월의 일일 값은 노란색 셀의 오른쪽에 있는 32번째 열에서 시작하여 29개의 열을 차지합니다(변경됨). 셀에 윤년이 아닌 연도가 있으면 B1셀의 값이 E428로 변경됩니다. 등.월#그리고누적계산하는 데 필요한 중간 값입니다.Col_Offset그리고열.
월별, 고객별 데이터 합계를 요약하는 공식은 다음과 같은 분석 형식으로 작성할 수 있습니다.
=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)
또는 완전한 공식으로
=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))
여기서 메타데이터와 일일 데이터는 워크시트에 포함되어 있다고 가정합니다 Sheet1.
이 수식은 1월/클라이언트 1에 적용되며 아래 스크린샷에 부분적으로 표시된 것처럼 다른 달 및 클라이언트의 나머지 셀에 복사할 수 있습니다. #N/A결과 오류를 방지하려면 메타데이터와 결과 테이블에 사용된 월 이름이 정확히 일치하는지 확인하세요 .
데이터가 노란색으로 강조 표시된 셀 뒤의 첫 번째 행에서 시작하고 표시된 예에는 6개의 데이터 행이 있으므로 함수 의 두 번째 및 네 번째 인수는 OFFSET1과 6입니다. 행 수가 다른 경우 올바른 값을 대체하면 됩니다.
세 번째와 다섯 번째 인수는 단순히 함수를 사용 VLOOKUP하여Col_Offset그리고열해당 월의 메타데이터 값입니다.