[RESUELTO] Múltiples consultas BUSCARV con tres condiciones

Discute sobre la aplicación de hojas de cálculo
Responder
Avatar de Usuario
Pakiao
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

[RESUELTO] Múltiples consultas BUSCARV con tres condiciones

Mensaje por Pakiao »

Hola. Tengo un problema con unas búsquedas en Cal (LibreOffice 5.2 o 6 y Windows 7). Me explico.
Es un libro de control de incidencias de personal.
- En una hoja (entradas) introduzco incidencias: cada fila es una incidencia, con fecha de inicio, fecha de fin, código de la persona (es un número) y clave de incidencia.
- En otra hoja (informe mensual) tengo una tabla con filas para cada persona y columnas para cada día del mes. En cada celda de cruce (día del mes/nombre) tengo que buscar la incidencia en la hoja de entradas con tres condiciones: el día buscado tiene que ser mayor o igual a la fecha de inicio; tiene que ser menor o igual a la fecha de fin; y además tiene coincidir el código de la persona. El resultado debe ser la clave de incidencia.

Creo que no es mucho lío, pero si no lo veis, puedo mandar un fichero pequeño de ejemplo.
Y gracias por anticipado por vuestro tiempo.
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
Avatar de Usuario
PepeOooSevilla
Mensajes: 1480
Registrado: Sab Abr 04, 2009 6:10 pm
Ubicación: Sevilla (España)

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por PepeOooSevilla »

Hola.
Pakiao escribió:... puedo mandar un fichero pequeño de ejemplo.
Creo que es lo mejor, así vemos cómo tienes organizados los datos por si pudiera haber otras soluciones. No pongas datos personales que este es un Foro público.

Saludos cordiales.
LibreOffice 6.4.6. Windows 10. Java 8 rev. 261 (64 bits)
Por favor, utiliza el Foro para tus consultas, no los mensajes privados
Si usas OpenOffice/LibreOffice trabaja y guarda en ODT, ODS, ODP, ... Y haz copias de seguridad.
Avatar de Usuario
Pakiao
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por Pakiao »

Te adjunto fichero
Adjuntos
Personal foro.ods
(58.36 KiB) Descargado 126 veces
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por mriosv »

Con una formula como:

Código: Seleccionar todo

InformeMensual.D16 =SI.ERROR(INDICE($'ENTRADA DE FECHAS'.$F$1:$F$20;SUMA.PRODUCTO(MES('ENTRADA DE FECHAS'.$A$3:$A$20)=$B$3;DIA('ENTRADA DE FECHAS'.$A$3:$A$20)<=D$9;DIA('ENTRADA DE FECHAS'.$B$3:$B$20)<D$9;FILA('ENTRADA DE FECHAS'.$A$3:$A$20));1);"")
parece funcionar.
Con SUMA.PRODUCTO calculamos la fila que cumple las condiciones y la extraemos del rango en INDICE.

Adjunto el fichero
Personal foro.ods
(50.81 KiB) Descargado 102 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
Pakiao
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por Pakiao »

He metido la fórmula tal y como la pones y no me funciona.
He realizado varios cambios: 1) La comparación de la 2ª fórmula DIA() la he puesto >=, porque es la fecha máxima y puede ser igual a la primera 2) El rango de la fórmula FILA() lo he puesto D3:D20 y lo he comparado con B17(la referencia de cada persona) 3) He eliminado ";1" referencia a la columna, puesto que sólo es una y es una variable opcional.
Tampoco me funciona. Te lo copio:
=SI.ERROR(INDICE($'ENTRADA DE FECHAS'.$F$3:$F$20;SUMA.PRODUCTO(MES($'ENTRADA DE FECHAS'.$A$3:$A$20)=$B$3;DIA($'ENTRADA DE FECHAS'.$A$3:$A$20)<=D$9;DIA($'ENTRADA DE FECHAS'.$B$3:$B$20)>=D$9;FILA($'ENTRADA DE FECHAS'.$D$3:$D$20)=$B16));"")
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por mriosv »

En el fichero que adjunte creo que funciona.

Pero puliendo un poco la formula, comparamos por la fecha en vez de por el día y nos evitamos con ello comparar los meses, creo que haciendo un poco mas legible la formula.
También tenemos que evitar el valor cero en los argumentos de INDICE() pues tiene significado, con 1/(1/SUMPRODUCT)) así generamos un error de dividir por cero que podemos controlar.

Código: Seleccionar todo

=SI.ERROR(INDICE('ENTRADA DE FECHAS'.$F$1:$F$20;1/(1/SUMA.PRODUCTO('ENTRADA DE FECHAS'.$D$3:$D$20=$B16;'ENTRADA DE FECHAS'.$A$3:$A$20<=FECHA($B$2;$B$3;D$9);'ENTRADA DE FECHAS'.$B$3:$B$20>=FECHA($B$2;$B$3;D$9);FILA('ENTRADA DE FECHAS'.$A$3:$A$20)));1);"")
Personal foro-1.ods
(53.72 KiB) Descargado 88 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
Pakiao
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por Pakiao »

Ya casi casi está conseguido!!!
Te repito mi fórmula tal y como está (por si he metido alguna pata).

Código: Seleccionar todo

=SI.ERROR(INDICE($'ENTRADA DE FECHAS'.$F$3:$F$20;1/(1/SUMA.PRODUCTO($'ENTRADA DE FECHAS'.$D$3:$D$20=$B16;$'ENTRADA DE FECHAS'.$A$3:$A$20<=FECHA($B$2;$B$3;D$9);$'ENTRADA DE FECHAS'.$B$3:$B$20>=FECHA($B$2;$B$3;D$9);FILA($'ENTRADA DE FECHAS'.$A$3:$A$20)));1);"")
Las celdas donde deben aparecer claves de la columna F lo tienen, en efecto. eso va de perlas.
El problema es que la clave que aparece, siendo de la columna F, no es el introducido. Así que cambié los códigos por números correlativos, para saber que pasaba. Y me encuentro que muchos de los resultados que aparecen están desplazados +2 (dos filas más abajo), algunos +3 y uno incluso +9. No tiene que ver con el número de días del intervalo entre fechas.
Y muchas gracias. Vamos por buen camino.
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por mriosv »

Pues sube el fichero en que lo haces, en el que adjunte creo que funciona correctamente.
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
Pakiao
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por Pakiao »

Aquí tienes:
Adjuntos
Personal foro.ods
(42.24 KiB) Descargado 120 veces
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por mriosv »

El rango de la función INDICE es:
$'ENTRADA DE FECHAS'.$F$ 1 :$F$20
no
$'ENTRADA DE FECHAS'.$F$ 3 :$F$20,
ten en cuenta que con SUMA.PRODUCTO obtenemos el número absoluto (real) de la fila no de su posición relativa en el rango.
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
Pakiao
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Mensaje por Pakiao »

RESUELTO!
Fantástico, muchas gracias, funciona de maravilla.
Antes de cerrar el hilo, si no te importa, me gustaría que me dijeras de algún manual con casos prácticos, porque no consigo razonar :crazy: como ha funcionado la orden SUMA.PRODUCTO, ni lo que le aporta la orden FILA.
Y gracias mil. :bravo: :bravo: :bravo:
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: [RESUELTO] Múltiples consultas BUSCARV con tres condicio

Mensaje por mriosv »

En el punto relativo a las guías dentro de calc, creo que puedes encontrar información.

Una de las cosas que siempre aconsejo es hacer una lectura del la ayuda que viene con el programa, casi todas las funciones tienen ejemplos de como usarlas.

La función FILA nos permite obtener en que fila se encuentra el dato buscado, de forma que lo podemos extraer del rango de INDICE.

SUMA.PRODUCTO(), es una función de matriz, que en su formato básico multiplica los valores obtenidos en cada uno de sus argumentos y después los suma.
Una de sus grandes utilidades es poder utilizar matrices sin tener que introducir la formula como tal. Es un gran comodín.
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Responder