[RESUELTO] Múltiples consultas BUSCARV con tres condiciones

Discute sobre la aplicación de hojas de cálculo

[RESUELTO] Múltiples consultas BUSCARV con tres condiciones

Notapor Pakiao » Jue Jul 05, 2018 10:10 am

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
Pakiao
 
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Notapor PepeOooSevilla » Jue Jul 05, 2018 1:04 pm

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.0.7 (estable) en Windows 7 / 10
Por favor, utiliza el Foro para tus consultas, no los mensajes privados
Avatar de Usuario
PepeOooSevilla
 
Mensajes: 942
Registrado: Sab Abr 04, 2009 6:10 pm
Ubicación: Sevilla (España)

Re: Multiples consultas BUSCARV con tres condiciones

Notapor Pakiao » Vie Jul 06, 2018 11:05 am

Te adjunto fichero
Adjuntos
Personal foro.ods
(58.36 KiB) 26 veces
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
Avatar de Usuario
Pakiao
 
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Notapor mriosv » Vie Jul 06, 2018 11:04 pm

Con una formula como:
Código: Seleccionar todo   Expandir vistaContraer vista
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) 24 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice 5 (Win10x64)
mriosv
 
Mensajes: 2141
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Notapor Pakiao » Lun Jul 09, 2018 10:13 am

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)
Avatar de Usuario
Pakiao
 
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Notapor mriosv » Lun Jul 09, 2018 6:14 pm

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   Expandir vistaContraer vista
=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) 18 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice 5 (Win10x64)
mriosv
 
Mensajes: 2141
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Notapor Pakiao » Mar Jul 10, 2018 8:55 am

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

Código: Seleccionar todo   Expandir vistaContraer vista
=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)
Avatar de Usuario
Pakiao
 
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Notapor mriosv » Mar Jul 10, 2018 9:47 am

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 5 (Win10x64)
mriosv
 
Mensajes: 2141
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Notapor Pakiao » Mar Jul 10, 2018 11:15 am

Aquí tienes:
Adjuntos
Personal foro.ods
(42.24 KiB) 27 veces
Windows 7 Prof. SP1 y 64 bits
LibreOffice 5.2 o 6 (a veces)
Avatar de Usuario
Pakiao
 
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

Re: Multiples consultas BUSCARV con tres condiciones

Notapor mriosv » Mié Jul 11, 2018 11:36 pm

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 5 (Win10x64)
mriosv
 
Mensajes: 2141
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Multiples consultas BUSCARV con tres condiciones

Notapor Pakiao » Jue Jul 12, 2018 8:17 am

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)
Avatar de Usuario
Pakiao
 
Mensajes: 6
Registrado: Jue Jul 05, 2018 9:41 am

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

Notapor mriosv » Jue Jul 12, 2018 9:30 pm

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 5 (Win10x64)
mriosv
 
Mensajes: 2141
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)


Volver a Calc

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 5 invitados