jueves, 14 de junio de 2012

Simulando un Texto predictivo en una celda de Excel.

En una entrada anterior vimos como mediante un ComboBox y con su Propiedad MatchEntry y algo más de código VBA conseguimos que dicho Control funcionara como un reconocimiento de texto según escribíamos en él, de acuerdo a un listado de elementos de un rango definido. En la entrada de hoy simularemos igualmente ese Texto predictivo directamente en una celda de la hoja de cálculo de Excel.
Se trata de un truco, como otras tantas veces, para engañar a Excel (y a nosotros mismos), y que sólo sería válido bajo ciertas condiciones y ciertos tipos de estructura de informes.

Nos aprovecharemos de la opción de Habilitar Autocompletar para valores de celda que permite para cada celda reconocer textos o valores de otras celdas contiguas:

Simulando un Texto predictivo en una celda de Excel.


Por ejemplo, con esta opción habilitada, si escribimos a continuación de un rango de celdas rellenas, se autocompletará con el texto más cercano reconocido, y también podremos presionar Alt+flecha abajo para obtener una lista desplegable de todos los elementos del rango contiguo. Aunque lo que nos interesa es la primera propiedad, podemos ver un ejemplo del segundo:

alt=


Pero volvamos a lo que nos ocupa. Con esta propiedad de Texto predictivo del Habilitar Autocompletar para valores de celda podemos replicar en nuestra hoja de cálculo de Excel el comportamiento deseado. Como necesitamos un rango o listado contigua, deberemos incorporarlo al informe.

Trabajemos sobre un ejemplo; deseamos obtener un valor asociado a dos variables 'Producto' y 'Concepto'. de acuerdo a una Tabla de referencia cruzada, según rellenamos ciertos registros.
Para ello, construímos la siguiente estructura de informe:

Simulando un Texto predictivo en una celda de Excel.


He tenido que incluir en el rango A2:A7 la lista de elementos a ser reconocidos en las celdas amarillas situadas en esa misma columna; igualmente en el rango B5:B7 para otro reconocimiento diferente. Por último, en las celdas C8:C17 he incluido la fórmula:
=SI.ERROR(INDICE($E$1:$H$7;COINCIDIR($A8;$E$1:$E$7;0);COINCIDIR($B8;$E$1:$H$1;0));"")
que encontrará el valor correspondiente en la tabla de datos E1:H7.

En principio es así de sencillo, pero si además queremos fortalecer el informe, y que funcione algo mejor, asignaremos una Validación de datos a las celdas amarillas, una validación de celdas tipo lista sobre los rangos arriba indicados, esto es, para las celdas A8:A17 sobre la lista A2:A7; y para las celdas B8:B17 sobre la lista B2:B7.

Simulando un Texto predictivo en una celda de Excel.



Recapitulemos, necesitamos insertar el listado a reconocer encima de las celdas a autocompletar y que requieran de ese Texto predictivo; y para evitar errores Validamos las celdas a rellenar con una Validación de datos.
Estamos en disposición de probar el informe, iremos a la celda A8 y comenzaremos a escribir; vemos en la imagen como reconoce y propone (autocompleta) el primer texto más similar encontrado en el listado A2:A7:

Simulando un Texto predictivo en una celda de Excel.


De igual forma para la celda B8 con lo que llegariamos en C8 al resultado esperado.

Simulando un Texto predictivo en una celda de Excel.


Para dejar el informe algo más visual, podemos ocultar las filas o columnas que no aportan nada al informe, dejándolo como sigue; pero con toda la funcionalidad predictiva:

Simulando un Texto predictivo en una celda de Excel.


Evidentememte este truco tiene sus limitaciones, pero es cierto, que siempre podremos adaptar nuestros informes de Excel, de la forma explicada para conseguir este Autocompletado.

12 comentarios:

  1. Me gustaria que me indicaras donde esta la publicacion en la que utilizas el ComboBox para hacer la predicción.

    ResponderEliminar
  2. Saludos!
    Qué puedo hacer para que al escribir el producto me prediga el precio?
    Intenté basarme en la formula que has usado en tu post pero no he conseguido hacer que funcione.

    Aquí dejo una imagen http://imageshack.com/a/img89/3876/5jga.png

    ResponderEliminar
    Respuestas
    1. Hola Archistol,
      lo primero sería convertir en Tabla el rango A1:B203, y añadir en la columna B una fórmula de búsqueda.. quizá con un rango corrido, algo así en B204
      =BUSCARV(A204;$A$1:$B203;2;0)

      debería funcionar...
      Saludos

      Eliminar
  3. Hola podrias incluir el archivo de excel por favor asi nos serviria a todos tener esa excelente referencia..muchas gracias y muy util tu aporte, gracias

    ResponderEliminar
    Respuestas
    1. Muchas gracias!
      lamentablemente perdí el fichero en un cambio de equipo
      :'-(
      Pero sólo tienes que seguir las indicaciones para replicar el ejemplo.

      Un cordial saludo

      Eliminar
  4. Hola,

    Tengo una tabla con los siguentes datos:

    CODIGO- REFERENCIA- COSTO1- COSTO2- COSTO3

    Y quisiera que si ingreso el código, por ejemplo, los otros campos de la fila se autocompleten.

    No se como hacerlo, valoro mucho su ayuda.

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      supondremos tienes la información asociada a cada código en otro rango o tabla, si es así en las columnas de
      REFERENCIA- COSTO1- COSTO2- COSTO3
      incluirías la función
      =BUSCARV(celda código a rellenar;Tabla donde buscar;2;0)
      un 2 para devolver la Referencia, un 3 para el Costo1, un 4 para el Costo2 y un 5 para el Costo 3.

      Espero te oriente.
      Slds

      Eliminar
  5. INTRODUZCO EN UNA CELDA 1267-UDV-2014 ESTE ES UN CODIGO DE MI TRABAJO Y SOLO CAMBIA EN LOS PRIMEROS 4 NUMEROS, QUIERO QUE ME RECONOZCA -UDV-2014 EN CADA CELDA A LO LARGO DE UNA COLUMNA PARA NO TENER Q ESCRIBIRLOS PERO COMO HAGO GRACIAS AGRADEZCO TU APORTE AMIGO

    ResponderEliminar
    Respuestas
    1. Hola, buenos días.
      todo dependerá si necesitas 'verlo' o 'fijarlo' para luego trabajar sobre el código completo (con sus 13 caracteres).
      En el primer caso bastaría definieras un formato personalizado:
      0000"'UDV-2014"
      de esta forma en la celda, en realidad solo tendría el valor de los primeros cuatro caracteres, siendo el resto algo visible pero inexistente a efectos de cálculos, búsquedas, etc.

      Para el segundo caso tendrías que incluir una segunda celda paralela que concatenara los cuatro primeros caracteres con el resto de la cadena, por ejemplo en A1 introduces 1267 y en B1 la fórmula =A1&"'UDV-2014"

      Saludos

      Eliminar
  6. Hola tengo una duda, como hago para que en una celda que tiene una validación de datos, los cuales están en otra hoja y cuando yo intente escribir en dicha algún producto que está en la otra hoja este prediga el el producto o que me dé una posible lista de opciones.
    Por ejemplo que yo en la otra hoja tengo diferentes alimentos, entre ellos diferentes tipo de pan cuadrado, entonces cuando yo en la celda con la validación de datos escriba pan (o lo vaya escribiendo) él ya prediga pan cuadrado o que me despliegue una lista como pan cuadrado integral, pan cuadrado blanco, etc.
    No sé si me entiende.

    ResponderEliminar
    Respuestas
    1. Hola!
      no creo posible, salvo con trucos como el expuesto en el post, conseguir el texto predictivo en Excel.. y menos, en mi opinión, si los datos vienen de hojas distintas.
      Un saludo!

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.