MesANum no es una función que exista en Excel. Excel tiene muchas funciones para trabajar con fechas, y cada vez implementa más. Sin embargo, hay una acción que se utiliza con cierta frecuencia, y que todavía no está implementada en Excel, que es leer el nombre de un mes en texto y que devuelva su valor numérico (ejemplo, que lea Septiembre y devuelva 9).
Primera aproximación: fórmula
Lo primero que se nos ocurre a todos es anidar una serie de funciones SI que comprueben si en la celda origen tenemos escrito “enero”, y si no, que mire si hemos puesto “febrero”, etc. Quedaría algo así como:
=SI(E8="ENERO";1;SI(E8="FEBRERO";2; SI(E8="MARZO";3;SI(E8="ABRIL";4; SI(E8="MAYO";5;SI(E8="JUNIO";6; SI(E8="JULIO";7;SI(E8="AGOSTO";8; SI(E8="SEPTIEMBRE";9;SI(E8="OCTUBRE";10; SI(E8="NOVIEMBRE";11;SI(E8="DICIEMBRE";12;))))))))))))
En el ejemplo he introducido saltos de línea para que se lea mejor. Esto vale si lo vamos a utilizar una vez, pero ¿qué ocurre si lo tenemos que repetir muchas veces en diferentes informes? Pues sencillamente que tendremos que copiar una y otra vez la fórmula. ¿Y si queremos que también intercepte otras maneras de escribir los meses? Podría ser, por ejemplo, la aceptada SETIEMBRE, o abreviaturas (como ENE para enero), o los nombres en inglés, o…
Bien, para, para, que se nos escapa el burro. Si hacemos eso, sencillamente tendremos una fórmula demasiado larga para poder leerla correctamente. Ya utilicemos el operador O(), ya anidemos más SI (recuerda el límite de 256 SI anidados – aún estamos lejos, pero estas fórmulas crecen rapidísimo). Independientemente de los límites de Excel, que suelen estar suficientemente lejos, lo importante es que las futuras revisiones de la hoja, por ejemplo para comprobar que calcula bien o para buscar un posible error, nos llevarán demasiado tiempo. Y tiempo es lo que no tenemos, ¿verdad?
Bueno, pues para eso hay solución.
Segunda aproximación: un poquito de código
Cuando los programadores de Excel no nos han preparado una función que nos devuelva el resultado que nosotros queremos, podemos programarla nosotros con un poquito de maña (no hace falta demasiada), y con la ayuda del editor VBA que hay en el propio Excel.
Ya nos explicó Ignacio cómo crear funciones en Excel con VBA. Ahora simplemente vamos a implementar la que nos interesa, y la llamaremos, como no podía ser de otra forma, MesANum.
En el caso que nos ocupa vamos a utilizar la estructura SELECT CASE. Una estructura genérica sería:
SELECT CASE a
CASE 1
código1
CASE 2
código2
CASE ELSE
código3
END SELECT
Esto se puede traducir al español más o menos como:
Evalúa el valor de “a”. En caso de que a=1, ejecuta el código1; en caso de que a=2, ejecuta el código2; en caso de que “a” valga cualquier otro valor diferente a los anteriores, ejecuta el código 3.
Hay que saber que podemos poner varios casos diferentes para que se ejecute el mismo código. Por ejemplo, podríamos ejecutar el código1 cuando “a” vale 1 y cuando vale 15, simplemente separando los valores por comas. Sería algo así:
SELECT CASE a
CASE 1, 15
código1
.
.
.
Una vez ya tenemos esto, es muy fácil pasarlo a nuestros nombres de meses en texto y a nuestros resultados numéricos.
Aun así, todavía nos falta otra cosa: conocer qué es una función.
Subrutinas, funciones y otros males menores
En VBA el código no va todo junto. Se divide en rutinas y funciones (entre otras cosas). Una rutina es el código que se ejecuta cuando se lanza un evento (por ejemplo), y hace las cosas que tenga programadas. Una función es lo mismo, pero devuelve un resultado. Esa es la diferencia: devuelve un resultado.
Todo lo que utilizamos en la hoja de cálculo son funciones programadas así. Cuando escribimos =SUMA(A1; A3; A5) lo que estamos haciendo es llamar a una función que se llama SUMA, que admite varios parámetros separados por punto y coma (o por comas en otros idiomas) y que devuelve el resultado de sumar lo que encuentra. DEVUELVE el resultado.
Nosotros podemos programarnos una función que admitirá un parámetro en formato texto -el nombre del mes- y nos devolverá un resultado, que será un número. Bueno, en realidad nos devolverá un número si lo que encuentra está entre las opciones que nosotros manejamos, y si no devolverá un error #N/A o un error #VALOR según cómo sea el dato que le metemos. Pero no nos adelantemos, vamos paso a paso.
Tanto las rutinas (o subrutinas, que en este caso son lo mismo) como las funciones hay que definirlas. Excel tiene que saber dónde empiezan y donde terminan. Eso se hace escribiendo una primera línea que empieza por Sub o por Function según sea rutina o función. Y dónde termina se le dice escribiendo End Sub o End Function en cada caso.
A partir de ahora vamos a poner ejemplos de rutinas (son más sencillos) y luego de funciones para ir paso a paso.
Hemos dicho que la primera línea empieza por SUB, pero no hemos dicho cómo sigue. La estructura viene a ser algo así:
SUB NombreDeRutina(P1 AS string, P2 AS long, P3 AS variant)
El nombre de la rutina es, obviamente, NombreDeRutina. Después siempre lleva los parámetros que admite. En este caso admite 3 parámetros y dará error si le ponemos más o menos. Hay maneras de poner parámetros opcionales, pero no vamos a entrar en ello ahora.
El espacio para los parámetros hay que ponerlo SIEMPRE. En caso de que no necesitemos parámetros, pondremos el paréntesis de apertura y luego el de cierre, pero los paréntesis no pueden faltar.
En nuestro ejemplo, estamos admitiendo un primer parámetro que se llama P1 y que es una cadena de caracteres (string), el segundo se llama P2 y es un entero largo (long) y el tercero se llama P3 y es cualquier valor (variant admite cualquier tipo de valor). Si se omite esta parte (AS string, por ejemplo), Excel entiende por defecto que es de tipo variant.
En el caso de definir una función, la estructura será parecida:
FUNCTION NombreDeFuncion(P1 AS string, P2 AS long, P3 AS variant) AS date
Como ves, es exactamente igual, solo que en vez de empezar por SUB, lo hacemos por FUNCTION y terminamos con AS variant. Esto último nos dice qué tipo de valor es el resultado que devuelve la función. En este caso lo que devuelve es una fecha (date).
Dentro de la función haremos nuestros cálculos y finalmente devolveremos el resultado. Esto se hace con una simple asignación al nombre de la función, así:
NombreDeFuncion = valordevuelta
Cuando termine la ejecución de la función, devolverá el último valor que hayamos asignado al nombre de nuestra función.
Vamos al ejemplo concreto: la función MesANum
Una vez visto lo anterior, vamos a definir nuestra función. Vamos a definirla en un módulo para que nos funcione desde cualquier hoja del libro.
Function MesANum(mes As String) As Variant End Function
Como ya sabes, estamos definiendo una función que se llama MesANum (las mayúsculas y minúsculas cuentan), que admite un parámetro que se llama “mes” y que es una cadena de caracteres, y devolvemos un valor que puede ser de cualquier tipo. Esto lo haremos porque si el valor que introducimos es correcto, nos devolverá un número del 1 al 12 (que son números enteros que pueden ser del tipo integer o long), pero si no es correcto, devolverá un error (que no es un número, sino otro tipo de valor). Por eso hemos definido la respuesta como Variant.
Nuestra función terminará donde dice End Function. Aún no hemos introducido nada dentro. Lo que haremos será como lo que indiqué en el primer apartado:
Function MesANum(mes As String) As Variant Select Case UCase(mes) 'Select Case mes Case "ENERO", "ENE", "JANUARY", "JAN" MesANum = 1 Case "FEBRERO", "FEB", "FEBRUARY" MesANum = 2 Case "MARZO", "MAR", "MARCH" MesANum = 3 Case "ABRIL", "ABR", "APRIL", "APR" MesANum = 4 Case "MAYO", "MAY", "MAYO" MesANum = 5 Case "JUNIO", "JUN", "JUNE" MesANum = 6 Case "JULIO", "JUL", "JULY" MesANum = 7 Case "AGOSTO", "AGO", "AUGUST", "AUG" MesANum = 8 Case "SEPTIEMBRE", "SETIEMBRE", "SEP", "SET", "SEPTEMBER", "SEPT" MesANum = 9 Case "OCTUBRE", "OCT", "OCTOBER" MesANum = 10 Case "NOVIEMBRE", "NOV", "NOVEMBER" MesANum = 11 Case "DICIEMBRE", "DIC", "DECEMBER", "DEC" MesANum = 12 Case Else MesANum = CVErr(xlErrNA) End Select End Function
Aquí puedes ver cómo hemos extendido la función SELECT CASE para que admita cualquier manera de escribir los meses en español y en inglés. Además, el valor de entrada lo hemos convertido todo a mayúsculas con la función UCase, de manera que no importen las mayúsculas o minúsculas que tengamos en la celda.
Finalmente, en caso de que no se dé ninguna de las opciones que hemos predefinido (hay 43 alternativas válidas además de todas las combinaciones de mayúsculas y minúsculas), devolvemos un error con la función CVErr. En este caso estamos devolviendo un error #N/A al utilizar el parámetro xlErrNA. Esto último es prescindible, pero deja más fina nuestra función.
Y nos vamos a la hoja
Una vez hemos definido nuestra función como está en el apartado anterior, podremos utilizarla en la hoja como si fuera cualquier otra función:
Y el parámetro que utilizaremos será el valor que haya en la celda a la que apuntemos:
Y el resultado, por supuesto, será el esperado:
Como dijimos antes, nos dará un error #N/A si el valor de entrada es válido, pero no coincide con ninguna de las opciones que hemos preestablecido. Si el valor de entrada no es correcto, por ejemplo porque introducimos un rango o porque el valor de la celda es un error de cualquier tipo), nos dará un error de tipo #¡VALOR!
Y esto, ¿para qué es útil?
Esta mañana me he encontrado con una tabla en la que tenía introducidos los nombres del mes en una columna y el año en otra columna. Era un registro de facturas, y yo quería trabajar con un gráfico dinámico segmentado por fechas (ver Insertar Segmentación de Datos) de manera que pudiera seleccionar el rango de fechas que quería analizar en mi gráfico. Y para eso necesitaba una columna con fechas reales, no con meses escritos en formato texto. Para no hacer una fórmula muy larga en la hoja, me he programado esta función. El resultado ha sido algo parecido a esto:
De esta manera he podido segmentar por fecha mis gráficos, y el usuario ha quedado muy contento.
Bonus track
Como extra, te adjunto para que puedas cacharrear el archivo que he utilizado para escribir esta entrada. Lo tienes aquí:
Recuerda que para que funcione tendrás que tener habilitadas las macros.