Início / computer / Perguntas / 1472657
Accepted
hiigaran
Asked: 2019-08-19 11:57:38 +0800 CST

Exibindo valores em uma planilha até X dias a partir da data atual

  • 772

Estou procurando obter ajuda para desvendar a bagunça que é uma planilha de entrada de dados e resumo que tive nos últimos anos no LibreOffice Calc. O layout geral das partes relevantes da planilha é o seguinte:

Folha: "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 todas as linhas em que as células da coluna de resumo A correspondem às células de 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. Vou tentar simplificar a fórmula abaixo:

=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))

O que eu queria saber é como eu poderia modificar esta fórmula para a coluna Resumo G, onde sua busca seria limitada apenas aos últimos 90 dias.

Um exemplo do que devo ver:

Folha: "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))
worksheet-function spreadsheet

1 respostas

  1. Best Answer

    A questão descreve duas questões. Um está tentando depurar uma longa fórmula. 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 os blocos lógicos intactos (ou seja, várias 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 autônoma (como adicionar o sinal de igual). Caso o problema seja parênteses desbalanceados, copie toda a expressão que contém todos os parênteses, depois apague 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ê testar; redigitar a fórmula pode corrigir erros e tudo funciona quando você testa, para que não sirva a um propósito de diagnóstico.

    A outra questão foi limitar os resultados aos últimos 90 dias. Isso pode ser feito de maneira semelhante à forma como a fórmula já limita os resultados ao nome correspondente -- adicione um teste à lista de expressões em SUMPRODUCT. Para manter as coisas simples, 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) é 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 não mais de 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 VERDADEIRO ( 1) ou FALSO ( 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.

    • 1

relate perguntas