Validación de direcciones de e-mail en Excel

Validación de datos es una herramienta excelente, indispensable diría, en todo modelo de Excel que requiera ingresar datos. Pero, como todo en este mundo, tiene sus limitaciones. Por ejemplo, ¿cómo comprobamos que el usuario ingrese direcciones válidas de correos electrónicos?

Si se tenemos una lista de direcciones válidas, nos basta con usar la opción Lista de validación de datos. Pero, ¿qué hacemos si no contamos con una lista de direcciones pero queremos asegurarnos que la sintaxis de la dirección es correcta? Es decir, abc123@xyz.com es una dirección válida, independientemente de que exista o no; pero abc..123@xyz no lo es).

La solución es usar expresiones regulares (regular expresions). La expresión regular es un patrón que nos permite describir un conjunto de texto sin enumerar sus elementos. No voy entrar en los detalles técnicos, que pueden leer en la nota del enlace, pero si señalar que estos patrones pueden ser usados en Vba. De esta manera podemos crear una función definida por el usuario para validar direcciones de correo electrónico.

La función para validar la sintaxis de una dirección de correo electrónico es

Public Function blnEmailValid(ByVal strEmailAdd As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        blnEmailValid = .Test(strEmailAdd)
    End With
End Function

Este código fue publicado por Vishesh en ExcelExperts.com.

En este ejemplo, ponemos la función en la celda D2 para evaluar la dirección ingresada en la celda C2; si la dirección es válida el valor en la celda D2 es VERDADERO, en caso contrario FALSO

También podemos usarla en Validación de Datos, usando el valor de la celda D2 en la opción Personalizada (en este ejemplo, ponemos la función en A2 que podamos hemos ocultado)

JLD Excel – balance del sexto año y un gráfico interesante

Este blog cumple su sexto año de existencia. A lo largo de los años el número de lectores ha ido creciendo lo cual me llena de satisfacción. 2012 ha sido el año record en visitas y páginas vistas, más de 1,5 millón y más de 2.3 millones respectivamente.

Buscando como representar las estadísticas encontré en el excelente blog de Chandoo una nota sobre sobre la representación gráfica del precipicio fiscal de los Estados Unidos. Basándome en esa nota y en una posterior sobre un tema similar, he desarrollado este gráfico que muestra dinámicamente el número de páginas vistas en JLD Excel en los años 2007-2012

Este tipo de gráfico, permite una rápida comparación entre los años y ver cómo se ha ido desarrollando la popularidad del blog. La barra de desplazamiento permite señalar cada mes en particular, mostrando al mismo tiempo el número de páginas vistas.

Para crear este gráfico empecé por descargar la información sobre el número de visitantes y páginas vistas en el blog que llevo en el sitio StatCounter

Como puede apreciarse se tratan de datos diarios. Para poder crear nuestro gráfico tenemos que transformar estos datos a datos mensuales y anuales. Como ya habrán intuido la forma más eficiente de hacerlo es con tablas dinámicas

Ahora copiamos todo el contenido de la hoja y usamos Pegar Especial-Valores, para cancelar la tabla dinámica dejando sólo los valores.

El próximo paso es separar con una fila en blanco los años (para crear la separación en el gráfico) y agregar una serie de datos adicional en la columna D con esta fórmula

=SI(B2=$G$1,C2,NOD())

La celda $G$1 contiene el nombre del mes vinculado al valor de la barra de desplazamiento e identifica el mes a señalar (en la imagen el mes elegido es diciembre, por eso la celda D13 exhibe el valor de ese mes y el resto aparece con el valor #N#A; este valor de error no es representado en los gráficos).

Creamos un gráfico de área donde los valores del eje X (categorías) están en las columnas Ay B; los valores del área de cada año en la columna C y los valores del mes elegido en la barra de desplazamiento en la columna D.

Los valores de la serie de la columna D están representados con un gráfico de líneas.

La línea vertical que une el punto al eje de la X es una barra de error definida de esta manera

La barra de desplazamiento está ligada a la a la celda F2 en la hoja “motor”. Esta hoja contiene todos los datos del gráfico y todos los cálculos necesarios

Para calcular los acumulados en forma dinámica, tal como aparecen en el rango G5:G10 de la hoja “motor”, usamos esta fórmula

=SUMA(DESREF(INDICE($C$2:$C$78,COINCIDIR(F5,$A$2:$A$78,0)),,0,$F$2))

Finalmente creamos un cuadro de texto para cada año en el gráfico y lo ligamos a la celda con el acumulado

Este tipo de gráfico puede aplicarse a un sinnúmero de situaciones (ventas, eficiencia de servicios, etc.).

El archivo puede descargarse aquí.

Mis mejores de deseos de salud, paz y prosperidad en este nuevo año para todos mis lectores.

Cálculo de mínimos con criterios con tablas dinámicas.

En una nota de hace varios años atrás mostré los problemas que pueden surgir cuando queremos calcular el mínimo en un rango de valores, sin incluir los ceros. Esto sucede, por ejemplo, cuando queremos extraer el mínimo de una lista bajo algún criterio.

Veamos este ejemplo: tenemos una lista de órdenes con sus fechas de entrega. Cada orden tiene distintas fechas de entrega y queremos encontrar la fecha de la primera entrega.

Podemos vernos tentados a usar esta fórmula matricial

=MIN((A2:A101=D2)*B2:B101)

Pero veremos que el resultado es 00/01/1900 (que es cero con formato de fecha).

Para evitar que MIN evalúe los ceros que aparecen en la matriz del resultado, podemos usar esta otra fórmula matricial

=MIN(SI(($A$2:$A$101=D2)*$B$2:$B$101=0,"",($A$2:$A$101=D2)*$B$2:$B$101))

Para esta misma tarea podemos usar tablas dinámicas en lugar de usar fórmulas matriciales.


Creamos la tabla a partir de la lista; luego usamos la función MIN para resumir los valores; cambiamos el formato de los valores a fecha y finalmente quitamos los totales por columna.

Una de las ventajas de este método es que no tenemos que ir complicando nuestra fórmula a medida que agregamos criterios, por ejemplo, región.

Esta tabla dinámica muestra las fechas por orden y región