저는 지난 몇 년 동안 LibreOffice Calc에서 겪었던 데이터 입력 및 요약 스프레드시트의 혼란을 해결하는 데 도움을 받고 싶습니다. 스프레드시트 관련 부분의 일반적인 레이아웃은 다음과 같습니다.
시트: "데이터 입력"
|A |B |... |L |
|-----|-----|-----|-----|
|Date |Name |... |Value|
시트: "요약"
|A |... |E |G |
|-----|-----|------------|------------------------|
|Name |... |Total Values|Values from last 90 days|
지금은 모든 것이 작동하지만 요약 시트에 G열이라는 새 함수를 추가하고 싶습니다. E열은 요약 열 A의 셀이 데이터의 셀과 일치하는 모든 행에 대해 데이터 입력 시트를 검색하는 복잡한 수식을 실행합니다. 항목 열 B. 해당 공식은 다음과 같습니다.
=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A1)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)))
이것은 정말 혼란스러운 혼란입니다. 나도 알고 있습니다. 아래 공식을 단순화해 보겠습니다.
=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))
제가 궁금한 것은 검색이 지난 90일로만 제한되는 요약 열 G에 대해 이 수식을 어떻게 수정할 수 있는지였습니다.
내가 봐야 할 것의 예:
시트: "데이터 입력"
|A |B |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|
시트: "요약"
|A |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|
편집: 다음은 502 오류를 발생시킵니다.
=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A6)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91))
답변1
질문은 두 가지 문제를 설명합니다. 하나는 긴 수식을 디버깅하려고 합니다. 일반적으로 이에 대한 핵심은 공식을 구성 요소 조각으로 나누고 각 조각을 테스트하여 수행되는 작업을 확인하는 것입니다. 수식의 어느 부분이 작동하지 않는지 식별하기 위해 논리적 덩어리(즉, 함께 작동하는 여러 표현식)를 그대로 유지하는 것부터 시작하십시오. 오류가 명확하지 않은 경우 섹션을 구성 요소 부분으로 나눕니다.
각 부분을 복사하여 붙여 넣은 다음 붙여넣은 부분을 조정하여 독립형 공식으로 만듭니다(예: 등호 추가). 문제가 불균형한 괄호인 경우 모든 괄호를 포함하는 전체 표현식을 복사한 다음 더 쉽게 찾을 수 있도록 다른 내부 표현식과 해당 괄호를 삭제합니다. 이 연습을 통해 남은 부분의 불균형 괄호가 분명해질 수 있습니다. 복사하여 붙여넣으면 테스트 대상에 문제가 포함됩니다. 수식을 다시 입력하면 실수를 수정할 수 있으며 테스트할 때 모든 것이 작동하므로 진단 목적으로 사용되지 않습니다.
또 다른 문제는 결과를 지난 90일로 제한하는 것이었습니다. 이는 수식이 이미 결과를 일치하는 이름으로 제한하는 방법과 유사하게 수행할 수 있습니다. SUMPRODUCT의 표현식 목록에 테스트를 추가합니다. 작업을 단순하게 유지하기 위해 INDIRECT를 사용하여 범위를 작성하고 고정된 범위만 표시하는 복잡성을 생략하겠습니다. 문맥상 추가 인수는 다음과 같을 수 있습니다.
=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )
(L1:L100)은 질문의 단순화된 예에서 위치를 표시하기 위한 것입니다.
날짜는 일수로 저장되므로 값은 일 단위입니다. 날짜를 직접 추가하거나 뺄 수 있습니다. 이 식은 >TODAY()-91오늘보다 90일 이내로 오래된 날짜를 테스트합니다. 이는 데이터에 미래 날짜가 포함될 수 없다고 가정합니다. 이를 제한하기 위해 수식을 확장하지 않으면 해당 날짜도 포함되기 때문입니다. 전체 표현식은 TRUE( 1) 또는 FALSE( 0)를 반환하는 논리 테스트입니다. SUMPRODUCT는 배열 결과의 나머지 부분에 해당 값을 곱하여 0이 되거나 SUMPRODUCT의 다른 인수 결과가 무엇이든 됩니다.