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

Uso de Nombres (NAMES) en MS Excel

En varias de mis notas anteriores he mencionado el uso de "nombres" en la composición de fórmulas. En este contexto he utilizado "nombres" para señalar rangos o celdas (en definitiva, una celda es un rango de una sola celda).
En cuanto descubrí esta funcionalidad que ofrece Excel comencé a utilizarla profusamente, y siempre me ha llamado la atención que usuarios de nivel intermedio e inclusive alto, no la empleen. Así que vamos a hablar un poco sobre el uso de nombres en Excel.
Por lo general hablamos de rangos nominados ("named ranges" en inglés) pero enseguida veremos que los nombres en Excel son en realidad otra cosa.
El ejemplo más sencillo es de este tipo: en una hoja de Excel tenemos una tabla con las ventas mensuales de un agente; en alguna otra celda tenemos el porcentaje de comisión que le corresponde:


En lugar de calcular la comisión que le corresponde a nuestro agente con la fórmula =SUMA(B2:B13), podemos nominar el rango B2:B13 como "ventas" y la celda B15 "porcentaje". Nuestra fórmula se verá entonces: =SUMA(ventas)*porcentaje. En este ejemplo sencillo las ventajas de usar nombres no parecen gran cosa, pero imagínense una fórmula complicada con referencias a otras hojas. Esta forma "auto-explicatoria" de escribir las fórmulas puede ser de gran ayuda. Y esto no es todo, pero las demás ventajas las veremos más adelante.
Hay varias formas de crear nombres. La más sencilla es usar el "cuadro de nombres"



La técnica es sencilla: primero marcamos el rango, luego escribimos el nombre en el cuadro de nombre y pulsamos Enter.



Hacemos lo mismo con la celda B15, y ya podemos escribir nuestra fórmula con nombres:



Para ver cómo interpreta Excel los nombres, abrimos el menú Insertar--->Nombre--->Definir



Aquí podemos ver que la definición del rango va precedida por un signo "=": =Hoja1!$B$2:$B$13, lo que nos dice que Excel interpreta los nombres como fórmulas y no precisamente como rangos. Por ejemplo, podemos crear un nombre que se llame "total_de_ventas" cuya definición es =SUMA(Hoja1!$B$2:$B$13). Podemos entonces rescribir la fórmula en la celda B16 de la siguiente manera: =total_de_ventas*porcentaje



Esto es muy importante porque podemos definir no solo rangos sino también fórmula los que nos permitirá hacer algunos "pases mágicos". Estos serán el tema de mi próxima entrada.
Por ahora me queda sólo agregar que no se pueden borrar nombres en el cuadro de nombres, como parecería lógico pensar. Para borrar nombres hay que activar el menú Insertar--->Nombre--->Definir, elegir el nombre que queremos borrar y pulsar el botón "Eliminar".
En la próxima entrada veremos cómo usar nombre para crear rangos dinámicos y como crear listas para validación de datos.


Categorías: Funciones&Formulas_, Varios _

Technorati Tags: NAMES, Nombres en Excel