Estou procurando ajuda para desembaraçar a bagunça que é uma planilha de entrada de dados e resumo que tenho usado nos últimos anos no LibreOffice Calc. O layout geral das partes relevantes da planilha é o seguinte:
Planilha: "Entrada de Dados"
|A |B |... |L |
|-----|-----|-----|-----|
|Date |Name |... |Value|
Folha: "Resumo"
|A |... |E |G |
|-----|-----|------------|------------------------|
|Name |... |Total Values|Values from last 90 days|
No momento, tudo funciona, mas desejo adicionar uma nova função à planilha de Resumo, que é a coluna G. A coluna E executa uma fórmula complexa para pesquisar na planilha de entrada de dados cada linha onde as células na coluna de Resumo A correspondem às células em Dados Coluna de entrada B. Aqui está a fórmula:
=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)))
Esta é uma bagunça absolutamente confusa, eu sei. Tentarei simplificar a fórmula abaixo:
=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))
O que eu queria saber é como poderia modificar esta fórmula para a coluna G de Resumo, onde sua pesquisa seria limitada apenas aos últimos 90 dias.
Um exemplo do que devo ver:
Planilha: "Entrada de Dados"
|A |B |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|
Folha: "Resumo"
|A |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|
EDIT: O seguinte me dá um erro 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))
Responder1
A pergunta descreve dois problemas. Um deles está tentando depurar uma fórmula longa. Geralmente, a chave para isso é dividir a fórmula em suas partes componentes e testar cada parte para ver o que está fazendo. Comece mantendo intactos os pedaços lógicos (ou seja, diversas expressões que funcionam juntas), para identificar qual seção da fórmula não está funcionando. Se o erro não for óbvio, divida a seção em suas partes componentes.
Faça isso copiando e colando cada parte e, em seguida, ajuste a parte colada para torná-la uma fórmula independente (como adicionar o sinal de igual). Caso o problema seja parênteses desequilibrados, copie toda a expressão que contém todos os parênteses, depois exclua as demais expressões internas e seus parênteses, que serão mais fáceis de localizar; parênteses desequilibrados no que resta podem se tornar óbvios apenas com este exercício. Copiar e colar garantirá que o problema será incluído no que você testa; redigitar a fórmula pode corrigir erros, e tudo funciona quando você testa, portanto não serve para fins de diagnóstico.
A outra questão foi limitar os resultados aos últimos 90 dias. Isso pode ser feito de forma semelhante à forma como a fórmula já limita os resultados ao nome correspondente – adicione um teste à lista de expressões em SUMPRODUCT. Para simplificar, deixarei de lado a complexidade de usar INDIRETO para construir os intervalos e mostrar apenas os intervalos fixos. No contexto, o argumento adicional poderia ser assim:
=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )
O (L1:L100) serve apenas para mostrar a localização no exemplo simplificado da pergunta.
As datas são armazenadas como contagens de dias, portanto os valores estão em unidades de dias. Você pode adicionar ou subtrair dias diretamente. A expressão >TODAY()-91testa datas anteriores a hoje em no máximo 90 dias. Isso pressupõe que seus dados não podem conter datas futuras, pois elas seriam incluídas se você não expandisse a fórmula para limitar isso também. A expressão inteira é um teste lógico que retorna TRUE ( 1) ou FALSE ( 0). SUMPRODUCT multiplica o restante do resultado da matriz por esses valores, resultando em zero ou qualquer que seja o resultado dos outros argumentos em SUMPRODUCT.