Foro Excel

Estimados colegas de foro:

Debo crear una ficha de datos de consulta en la que los selectores sean el campo "Nombre" y el campo "Apellidos", de los que luego se extrae datos de registros coincidentes (Un registro único por consulta) de una base de datos personales, con varios campos adicionales, hasta un máximo de once o doce por registro. (Tipo: 'dirección -varios campos- teléfono, edad' etc...)

No hay definido un código personal disponible para restringir la búsqueda mediante "BUSCARV".

Hasta ahora, el selector de la ficha de consulta lo hago a través de validación por "Apellidos" -"Nombre" no tiene problema (Validación principal por 'Apellidos' y secundaria asociada por 'Nombres'). Aunque esto también podrí­a ser objeto de optimización, otra cosa me preocupa más:

Estructura de dos hojas:
En la hoja "Personal" habrá una tabla de unos 15.000 registros, y en la de "Consulta", la ficha que el usuario consultará para extraer la docena de datos consultados.

Tengo esta fórmula definida en la "Consulta":
=INDICE( Personal!$A:$E;SUMAPRODUCTO(--($D$5&$C$8=Personal!$A$4:$A$10&Personal!$B$4:$B$10);FILA(Personal!$A$4:$A$10 ));1)

[Espero que no salgan "caritas"] El indicador final '1' cambiarí­a hasta el '12' del último campo del registro consultado.

Pero como no se me ocurre otra, no sé cómo optimizarla, que es el tema central de esta consulta. Esta vez el "crono" sí­ manda, ya que la tabla consultada puede tener unas doce columnas por quince mil filas.

Hola xpelos

Te adjunto una posibilidad de busqueda doble usando matriciales (tan sólo va en doce campos o así­, según dices, así­ que no deberí­as tener problema por su uso).

Es simplemente una posibilidad, ahora bien no sé cuán rápida puede ser (o si lo es más que la que tú propones). Supongo que usando el programilla ese que usa KL podrás verlo.

Un saludo desde Vitoria

Estimado ioyama:

Muchí­simas gracias por el aporte, aunque desgraciadamente, se prevé que sean 12 campos de información por cada registro y unos 15.000 registros a controlar.

Como no habí­a adjuntado nada, asumo que el malentendido lo he provocado yo mismo. Ahora sí­ que publico el adjunto, por lo que el abanico de puntos a optimizar se multiplica.

He estado avanzando un poco, hasta encontrar otra posibiildad, más sencilla.
=COINCIDIR(Consulta!C2;Apellidos;0)+2

Perdona, ioyama

A veces, se me va la olla... Me he complicado hasta lo indecible.

Un tema complejo es localizar la primera validación y, a partir de aquí­, la segunda. Se asume que hay posibilidades de repetición de la secuencia "Apellidos" -miembros o o de una misma familia- pero que "Apellidos"+"Nombre" sólo va a darse una vez.

En este caso, el adjunto acierta, pero no si hay repeticiones, con lo que la opción mencionada
=SUMAPRODUCTO(--($C$2&$C$3=INDIRECTO($B$2)&INDIRECTO($B$3));FILA(INDIRECTO(B$2)))
parece la 'adecuada', y es la que pretendo optimizar.
Vuelvo a emitir el adjunto con esta opción.

Espero que esto sea lo que estas buscando.

Saludos

Hola de nuevo xpelos

Te habí­as explicado perfectamente y yo te habí­a entendido lo que solicitabas. El que no se ha explicado bien he sido yo.

Me referí­a a que realmente sólo tienes que usar doce funciones matriciales para realizar la busqueda, da igual el número de registros que tengas en tu base de datos. Yo hací­a esta aclaración porque (como seguro habrás comprobado en alguna ocasión) si una fórmula matricial la repites muchas veces (digamos que "veces igual a 100 ó más") el cálculo se hace muy pesado.

Otra opción fácil pero que no sé porqué tratamos de evitar (bueno en tu caso quizás por el elevado número de registros, lo que puede hacer que el fichero "engorde" al usar esta opción) es que uses una columna auxiliar (a continuación de nombre por ejemplo) que contenga apellidos & nombre, luego la búsqueda podrí­as hacerla con
=INDICE(base;COINCIDIR($C$2&$C$3;auxiliar;0);4))
donde base es el rango de datos y auxiliar la columna auxiliar
(del tipo propuesto por Sailepaty, aunque él evita la columna auxiliar)

Con esta columna auxiliar también podrí­as usar BuscarV.

Por cierto, ayer no me fijé, muy logrado el sistema de validación.

Un saludo desde Vitoria

Estimado ioyama:

He realizado la adaptación de tu fórmula por esta otra
{=SI(ESNOD(COINCIDIR($C$2&$C$3;Apellidos&Nombre;0));"Desconocido";INDICE(base;COINCIDIR($C$2&$C$3;Apellidos&Nombre;0);3))}

Aunque seguro que la primera parte, 'SI(ESNOD(COINCIDIR($C$2&$C$3;Apellidos&Nombre;0));"Desconocido";...', por la estructura de los selectores, no creo que sea necesaria. Aparte, he adaptado tu rango "Base" a:

Base =INDIRECTO("Personal!$A$2:$N$"&Personal!$P$2)

de manera que se amplí­e según el contador de registros de "P2" (uy! dse me han escapado...) y sea variable. Funciona de maravilla.

Cuando tenga la hoja original con unos 15.000 registros, tendré que poner los contadores en la hoja de trabajo...
Tal como están las cosas -hoy por hoy- no podrí­a alterar la estructura de la hoja original con la columna auxiliar, que serí­a lo más cómodo. (Normalmente, cuando diseño las hojas, ya me cubro las espaldas con una de í­ndices. Claro que entonces, la problemática se reducirí­a un montón, y no hubiera aprendido tanto con vuestras aportaciones)

Estimado sailepaty:
Un gustazo de saber de nuevo de tí­.
Muy, pero que muy interesante la opción de "INDICE".
Desde luego,
'...COINCIDIR(1;INDICE((Personal!$A$3:$A$152=$C$2)*(Personal!$B$3:$B$152=$C$3);0;1);0);... '
Ha sido un descubrimiento por mi parte: no sabí­a que admití­a esa combinación de condiciones... Eso sólo está al alcance de maestros...
Muchas gracias.

Mi gozo en un pozo:
El selector, basado en listas desplegables, tiene limitaciones, pero no sé hasta dónde llegan... Ni cómo suplirlas.
La "simulación" se 'ahoga' cuando los registros legan a 5.000 (y quizá -seguro- antes).

Hola xpelos

Revisa el adjunto

He probado las validaciones con más de 5000 registros y funciona.

Habrí­a que evitar que salgan los repetidos en los apellidos (para el lunes).

Un saludo desde Vitoria

Perdon...Me enganche tarse, asi que probablemente esta versión no tenga en cuenta cuestiones que no he logrado incorporar....pero bueno ahi esta....

No sé ...son muchos registros, pense que VBA.....

GALI

Amigo Gali:
Aún no he entrado en detalles, pero funcionar, a la mil maravillas.
Muchas gracias.

Fijate que la lista de apellidos se actualiza cada vez que salis de la Hoja Personal, ya que se suspone que una vez hechas las altas bajas o modificaciones, debe estar disponible en el DropDown para la seleccion.

GALI

Amigo Gali:
Más de lo que podí­a pedir.
Ahora estoy en una "pelea" con otro asunto, que me trae de cabeza... Planificación de actividades para el año que viene (Plan de Marketing, Organización, Informatiquilla, Presupuestos...) que es donde preveo usar el material que estáis aportando. Ahora mismo, durante unos dí­as, me ahogo en un vaso de agua y no doy abasto.
Desde luego, esto es un aviso serio para mí­ de que debo de entrar de una vez en temas VBA para Excel por lo menos; aunque cojeo mucho en otras cosas, ahí­ es que me falta hasta lo más mí­nimo.
Un saludo, y gracias.