Uso de NOMBRES (NAMES) en Excel – Validación de Datos

En una nota anterior he hablado de las ventajas de usar NOMBRES en Excel. Hemos mostrado que los NOMBRES no sólo pueden servir para identificar rangos (lo que he llamado "nominar" rangos) sino que también pueden contener fórmulas.
Una demostración sobre este tipo de uso de NOMBRES puede verse en esta nota sobre Formato Condicional, y también en esta nota sobre valores #N/A.
Hoy veremos otro uso de como esta funcionalidad nos permite sobreponernos a algunas limitaciones de Excel.
En muchos de los modelos que desarrollo para mis clientes uso Validación de Datos. Esta funcionalidad permite controlar el tipo de datos permitidos.





Por ejemplo podemos establecer que sólo valores enteros entre 10 y 100 sean válidos para un rango determinado.



La variante que yo utilizo en muchos de mis modelos es "Lista"



Cuando usamos esta variante, en cada celda del rango se abre una lista de valores de la cual. Cualquier valor que no figure en la lista no será aceptado por Excel.
El problema con esta variante reside en que Excel aceptará solamente listas que sen encuentren en la misma hoja de la celda que queremos formar. Por ejemplo, no podemos usar una lista que se encuentre en Hoja2 para formar las celdas en Hoja1.
La solución es definir el rango de la lista como NOMBRE. Veamos esto con un ejemplo.
Supongamos que en Hoja2 tenemos una lista de precios:


Y en Hoja1 un formulario para calcular ofertas:



La idea es utilizar Validación de Datos para generar una lista de la cuál se elegirán los productos. El problema, como ya dijimos, es que Excel no permite utilizar rangos que se encuentren en otras hojas. Y nuestra lista de precios se encuentra en la Hoja2.
Para solucionar este problema hacemos lo siguiente:
1 – entramos en la hoja dos y seleccionamos el rango con los números de catálogo de los productos. En el cuadro nombres introducimos el nombre "productos".


2 – En la Hoja1 seleccionamos el rango A7:A12 y entramos en el menú de Validación de Datos. Elegimos la opción "lista".



3 – Pulsamos la ventanilla "origen" e inmediatamente el botón F3, lo que nos permitirá elegir uno de entre los nombre que hemos creado. Elegimos "producto" y pulsamos "aceptar".



4 – A partir de este momento cada vez que seleccionemos una celda en el rango A7:A12, veremos un pequeño triángulo. Si pulsamos este triángulo se abriera una lista que refleja los productos que se encuentran en la Hoja2.



En las celdas del rango B7:B12 utilizamos la fórmula

=SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0))

de esta manera, cuando elegimos una valor de la lista, automáticamente aparecerá el precio correspondiente.


Como ven, también hemos creado un NOMBRE que contiene el rango A2:B12 en la Hoja2, es decir el catálogo de productos con sus precios.
La función ESBLANCO nos sirve para evitar valores #N/A cuando no hay números de catálogo en la columna A de la Hoja1.


Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_


Technorati Tags:
NOMBRES, Nombres en Excel

Reemplazando la tilde "~" con "Texto en Columnas" en MS Excel

Hoy vino Norman, compañero de trabajo, a verme con el siguiente problema. En una tabla de Excel hay una lista de cuentas contables con la siguiente forma: 11~2222~3333~44444. Por algún motivo había que reemplazar el tilde "~" por el símbolo "-". Sencillo, no? Todo lo que hay que hacer es apretar Ctrl+L (o Ctrl+H, si tienen la versión inglesa), y reemplazar le tilde por el guión.

Como ven, Excel se niega a reconocer la tilde. Si presionan "buscar" en lugar de "reemplazar", Excel los llevará a la última celda de la hoja (IV65536). No tengo claro por qué sucede esto, pero mi amigo tenía que realizar su tarea así que había que encontrar una solución (la lista contenía más de 5000 cuentas).
Este problema se puede resolver de varias maneras, incluido escribir una función UDF. Pero la solución más sencilla es utilizar el menú Datos--->Texto en Columnas



Estos son los pasos:
1 – Seleccionar el rango y abrir el menú Datos--->Texto en Columnas



2 – elegir la opción "delimitados", pulsar "siguiente"; en la casilla "otro" poner la tilde "~".



3 – Pulsar "finalizar". Excel pone cada grupo de número en una columna.



4 – Ahora tenemos que "rearmar" el número, para lo cual usaremos el operador "&". La fórmula será la siguiente =A2&"-"&B2&"-"&C2&"-"&D2



5 – Antes de eliminar las columnas innecesarias, convertimos las fórmulas en la columna E en números (Edición--->Pegado Especial--->Valores)

Categorías: Manejo de Datos_, Varios_

Technorati Tags: Texto en Columnas

Usar Nombres en Excel para Formato Condicional

Ayer hablamos sobre el uso de "nombre" en Excel. En esa nota decía que el uso de nombres nos permite realizar operaciones que de otra manera no serían posibles.
Veamos un ejemplo. Una de las funcionalidades que empleo a menudo en mis modelos es el "Formato Condicional".

Un ejemplo puede verse en esta nota sobre comparación entre dos listas. En ese ejemplo usamos una fórmula para condicionar el formato.
Ahora supongamos que queremos condicionar el formato de una celda de acuerdo al resultado de una celda que se encuentra en otra hoja. En este ejemplo tenemos en la Hoja1 los totales de ventas para el primer trimestre del año de nuestros agentes de ventas.

En la Hoja2 tenemos las ventas por mes y por agente

Para verificar que no falten datos mensuales queremos hacer un formato condicional en la Hoja1 que de un fondo rojo cuando falte algún dato de ventas de algún agente. Para hacer esto tenemos que detectar si hay alguna celda vacía en el rango B2:D6. La forma de verificar esto es con la función CONTAR. Por ejemplo, si CONTAR(B2:D2) da un resultado menor de 3, señal que falta algún dato. Ahora lo que tenemos que hacer es aplicar un formato condicional en la celdas con los nombre de los agentes, de manera que si falta algún dato reciban un fondo rojo.

Como veremos Excel no nos permitirá hacer esto:
Seleccionamos la celda A2 en la Hoja1, entramos en el menú Formato--->Formato Condicional

En al ventanilla de la condición escribimos la formula =CONTAR(Hoja2!B2:D2)<3>

Elegimos la trama roja, pero cuando apretemos "aceptar" recibiremos esta nota

Excel no nos permite usar referencias a otras hojas en el formato condicional.

La forma de superar este problema es usando nombres. Veamos primero la variante sencilla. Seleccionamos la Hoja2, seleccionamos el rango B2:D2 y en el cuadro de nombres escribimmos el nombre "agente1".

Ahora volvemos a la Hoja1, abrimos el menú Formato--->Formato Condicional, pero usaremos la fórmula =CONTAR(agente1)<3>

Como pueden ver, Excel acepta esta notación y podemos ver que para el Agente 1 nos faltan datos de ventas en la Hoja2.

Una variante un poco más compleja es incluir toda la fórmula en el nombre:

Esta misma técnica la podemos utilizar para superar un problema similar que existe en la Validación de Datos. Tampoco aquí Excel permite referencias a otras hojas, excepto que estas referencias estén incluidas en nombres.


Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags:Formato Condicional, Nombres en Excel