Officefull.es

Excel, word, powerpoint, android

Formatos Bases de datos – Buscar Valores junio 10, 2009

Buscar Valores

Formatos Tablas y Buscar Valores Excel

En el artículo de hoy, os mostraremos distintas formas de buscar valores/componentes en una base de datos estructurada por Columnas o bien si la base de datos esta estructurada por Filas. Mediante Fórmulas, Combos de Formulario, Rótulos, Formatos Condicionales, Nombre Definidos Estáticos/Dinámicos y BuscarV.

Archivo Tablas.xls – Hoja Combos

Mediante los Combos de Formulario al seleccionar un valor de la lista se pueden Identificar “a simple vista” los componentes en nuestra base de datos y Evitar (y corregir en su caso) “Repeticiones” ó duplicados de los valores aparecidos en la lista del Combo de formulario.

Combos de formulario

Base de datos

Al seleccionar en los combos un Elemento, Tipo o Suptipo en la Base de datos se refleja su resultado.

Formato base de datos y buscar valores

Combo Elemento

Rango de Entrada Nombre Dinámico [Elementos]

=DESREF(Combos!$A$13,0,0,CONTARA(Combos!$A:$A)-1)

Vinculado con la celda [ Elemento ]

Combo Vinculado a celda Elemento

Combo Tipo

Rango de Entrada Nombre Dinámico [Tipos]

=DESREF(Combos!$L$2,0,0,CONTARA(Combos!$L$2:$L$12)-1)

Vinculado con la celda [ Tipo]

Combo vinculado a Celda Tipo

Combo Subtipo

Rango de Entrada Nombre Dinámico [Subtipos ]

=DESREF(Combos!$M$2,0,0,CONTARA(Combos!$M$2:$M$12)-1)

Vinculado con la celda [ Subtipo]

Combo Vinculado a Celda Subtipo

Resultado

Podemos observar el resultado en la celda G7 y visualizar su valor mediante formatos condicionales en la propia base de datos.

=DESREF(A12,Elemento,(Tipo*3)-3+SubTipo)

Valor Resultado

Formatos condicionales

Fila Elemento – Fila()-12=Elemento

Columna Tipo – Y(Columna()-1>(Tipo-1)*3,Columna()-1<(Tipo*3)+1)

Columna SubTipo – Columna()-1=(Tipo*3)-3+SubTipo

Celda Dato – Y(Fila()-12=Elemento,Columna()-1=(Tipo*3)-3+SubTipo)

Alternativa BuscarV

=BuscarV(ElementoBuscado,BaseDeDatos,DevolverColumna)

Fórmula BuscarV

Archivo Tablas.xls – Hoja Rótulos

Mediante Rótulos en las fórmulas, se busca un resultado “aprovechando” la opción de:

Aceptar rótulos en las fórmulas” ? [Herramientas -> Opciones -> Calcular -> Opciones del libro]

En la nueva Versión de Office 2007, se elimina la característica de “aceptar rótulos en las formulas” la alternativa es la opción de “crear nombres” (no “definir”, si no CREAR) por los medios de cada versión, seleccionando todo el rango (incluyendo títulos en fila y columna) y especificar que los nombres a crear se definen usando las “cabeceras” tanto de fila como de columna.

En la celda Unión [ C20 ] podemos insertar [ =Elemento_3 Tipo1_S2 ] o bien podemos insertar un valor de la base de datos. Si existen datos “repetidos”, se “verán” varios datos “coincidentes” con el mismo “Formato Condicional”.

Rotulos Base de datos

Se utiliza el carácter de “intersección” de Excel -UN ESPACIO EN BLANCO para “separar | unir” los Rangos/Nombres

NO espacios “intermedios” NI en las filas NI en las columnas “base” { Rótulos }

NO “se puede” usar FUERA de esta misma hoja.

Formatos Condicionales

Fila Elemento – No(EsError(Coincidir(CeldaUnión,B4:P4,0)))
Columna Tipo – No(EsError(Coincidir(CeldaUnión,B4:B13,0)))
Celdas Datos – B4=CeldaUnión

Archivo Tablas.xls – Hoja Supuesto

Supuesto de base de datos estructurada por filas. Los Combos de esta hoja, “toman” los “Rangos de entrada” DE LA MISMA “fuente” Rangos “dinámicos” de la hoja “Combos”.

=DESREF(E11,SubType+(Type*3)-3+(Element*12)-12,0)

Buscar Componentes - Base de datos

Formatos Condicionales

Celda Elemento – Y(ContarA($B$12:B12)=Element,No(EsBlanco(B12)))
Celda Tipo – Y(ContarA($C$12:C12)=Type+(Element*4)-4,No(EsBlanco(C12)))
Celda SubTipo – ContarA($D$12:D12)=SubType+(Type*3)-3+(Element*12)-12
Celda Dato – ContarA($E$12:E12)=SubType+(Type*3)-3+(Element*12)-12

Archivo Tablas.xls – Hoja X_Hoja

“Toma” los datos de “las otras” hojas. Los Combos usan LAS MISMAS “Referencias” que las Bases “Originales”, por lo tanto, las Bases de datos “también” se actualizan al “accionar” los combos de esta hoja.

  • Usando el “Formato” de “Base de datos” de la hoja “Combos”

=DESREF(Combos!A12,Elemento,(Tipo*3)-3+SubTipo)

  • Usando el “Formato” de “Base de datos” de la hoja “Supuesto”

=DESREF(Supuesto!E11,SubType+(Type*3)-3+(Element*12)-12,0

Formatos Bases de datos - Buscar Valores

Archivo Tablas Formatos -ScreenShots-.xls

Hoja Combos

Combos Base de datos - Buscar Valores

En este segundo archivo nos muestra el mismo proceso que el archivo [Tablas.xls – Hoja Combos ], variando sus formatos condicionales:

Fila Elemento – Fila()-12=Elemento
Columna Tipo – Y(Columna()-1>(Tipo-1)*3,Columna()-1<(Tipo*3)+1)

Columna del SubTipo

Condición 1 

Columna()-1=(Tipo*3)-3+SubTipo

Condición 2 

Y(Columna()-1>(Tipo-1)*3,Columna()-1<(Tipo*3)+1,Columna()-1<>(Tipo*3)-3+SubTipo)

Celda Dato

Condición 1 – Y(Fila()-12=Elemento,Columna()-1=(Tipo*3)-3+SubTipo)
Condición 2 – Y(Fila()-12=Elemento,Columna()-1<(Tipo*3)-3+SubTipo)
Condición 3 – Y(Columna()-1=(Tipo*3)-3+SubTipo,Fila()-12<Elemento)

Hoja Rótulos

Rótulos Base de datos - Buscar valores

Nos muestra el mismo proceso que el archivo [Tablas.xls – Hoja Rótulos] variando sus formatos condicionales:

Fila Elemento – No(EsError(Coincidir(CeldaUnión,B4:P4,0)))
Columna Tipo – No(EsError(Coincidir(CeldaUnión,B4:B13,0)))

Celdas Datos

“Hablando de” Datos que son valores “crecientes”

Condición 1 – B4=CeldaUnión
Condición 2 – Y(No(EsError(Coincidir(CeldaUnión,B4:P4,0))),B4<CeldaUnión)
Condición 3 – Y(No(EsError(Coincidir(CeldaUnión,B4:B13,0))),B4<CeldaUnión)

Descarga de los Archivos para pruebas

 Formatos tablas (63.04 kB)

Enlaces de Interés

Agregar, cambiar o borrar formatos condicionales (Excel 2007)

http://office.microsoft.com/es-es/excel/HP100739393082.aspx

Agregar, cambiar o quitar formatos condicionales (Excel 2003)

http://office.microsoft.com/es-es/excel/HP051992783082.aspx

Rótulos y nombres de las fórmulas

http://office.microsoft.com/es-es/excel/HP051983473082.aspx

Tipos de controles y sus propiedades

http://office.microsoft.com/es-es/excel/HP052036043082.aspx

Categorías: Sin categoría

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *