Estoy buscando ayuda para desenredar el desorden que es la entrada de datos y la hoja de cálculo de resumen que he tenido durante los últimos años en LibreOffice Calc. El diseño general de las partes relevantes de la hoja de cálculo es el siguiente:
Ficha: "Ingreso de datos"
|A |B |... |L |
|-----|-----|-----|-----|
|Date |Name |... |Value|
Hoja: "Resumen"
|A |... |E |G |
|-----|-----|------------|------------------------|
|Name |... |Total Values|Values from last 90 days|
Por el momento, todo funciona, pero deseo agregar una nueva función a la hoja de Resumen, que es la columna G. La columna E ejecuta una fórmula compleja para buscar en la hoja de Entrada de Datos cada fila donde las celdas de la columna A de Resumen coincidan con las celdas de Datos Columna de entrada B. Aquí está la 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)))
Este es un lío absolutamente confuso, lo sé. Intentaré simplificar la fórmula a continuación:
=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))
Lo que me preguntaba es cómo podría modificar esta fórmula para la columna G de Resumen, donde su búsqueda solo estaría limitada a los últimos 90 días.
Un ejemplo de lo que debería ver:
Ficha: "Ingreso de datos"
|A |B |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|
Hoja: "Resumen"
|A |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|
EDITAR: Lo siguiente me da un error 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))
Respuesta1
La pregunta describe dos cuestiones. Se trata de depurar una fórmula larga. Generalmente, la clave para esto es dividir la fórmula en sus componentes y probar cada pieza para ver qué está haciendo. Empiece por mantener intactos los fragmentos lógicos (es decir, varias expresiones que funcionan juntas) para identificar qué sección de la fórmula no funciona. Si el error no es obvio, divida la sección en sus partes componentes.
Haga esto copiando y pegando cada parte, y luego ajuste la pieza pegada para convertirla en una fórmula independiente (como agregar el signo igual). En caso de que el problema sea el desequilibrio de los paréntesis, copie la expresión completa que contiene todos los paréntesis, luego elimine otras expresiones internas y sus paréntesis, lo que será más fácil de detectar; Los paréntesis desequilibrados en lo que queda pueden resultar obvios simplemente con este ejercicio. Copiar y pegar garantizará que el problema se incluya en lo que pruebe; volver a escribir la fórmula puede corregir errores y todo funciona cuando se realiza la prueba, por lo que no tiene un propósito de diagnóstico.
El otro problema fue limitar los resultados a los últimos 90 días. Esto se puede hacer de manera similar a cómo la fórmula ya limita los resultados al nombre coincidente: agregue una prueba a la lista de expresiones en SUMPRODUCT. Para simplificar las cosas, dejaré de lado la complejidad de usar INDIRECTO para construir los rangos y solo mostraré rangos fijos. En contexto, el argumento adicional podría verse así:
=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )
(L1:L100) es solo para mostrar la ubicación en el ejemplo simplificado de la pregunta.
Las fechas se almacenan como recuentos de días, por lo que los valores están en unidades de días. Puedes sumar o restar días directamente. La expresión >TODAY()-91busca fechas anteriores a hoy por no más de 90 días. Esto supone que sus datos no pueden contener fechas futuras, ya que se incluirían si no expande la fórmula para limitar eso también. La expresión completa es una prueba lógica que devuelve VERDADERO ( 1) o FALSO ( 0). SUMPRODUCTO multiplica el resto del resultado de la matriz por esos valores, lo que da como resultado un cero o el resultado de los otros argumentos en SUMPRODUCTO.