Excel

Si tenemos en Excel celdas con valores en la forma: - viernes 1 de enero de 2010 - sábado 2 de enero de 2010 - domingo 3 de enero de 2010 - … y deseamos transformarlo en fecha podemos hacer lo siguiente: =TEXTO(IZQUIERDA(EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30);2) &EXTRAE(EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30);6;4)&DERECHA(EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30);4 );"dd/mm/aaaa")*1 ¡Toma función! Vamos a contar paso por paso como funciona: necesitamos 1 de enero de 2010 de viernes 1 de enero de 2010 , supongamos que está en la celda A2. Eso lo conseguimos encontrando el primer espacio en blanco con la función ENCONTRAR(" ";A2;1) en este caso el 6, luego debemos empezar por el siguiente, por eso debemos sumar 1. Con EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30) nos quedamos con 1 de enero de 2010 ese texto tiene que pasar a otro texto con el formato dd/mm/aaaa. El día lo cogemos con IZQUIERDA(EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30);2) el mes es más complejo porque queda entremedias EXTRAE(EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30);6;4) y el año se hace de forma análoga al día DERECHA(EXTRAE(A2;ENCONTRAR(" ";A2;1)+1;30);4 ) y hemos creado el texto 1 ener2010 por último empleamos la función TEXTO para transformar esa cadena de caracteres a otra cadena de caracteres con el formato deseado dd/mm/aaaa y todo ello lo multiplicamos por 1 para crear el número 40179 al que podemos dar el formato de fecha que queramos y con el que podemos realizar operaciones Espero que esta fórmula os sirva y os ayude en vuestro trabajo con Excel cuando tengáis que cambiar textos a fechas (o al contrario). También podemos emplear la función FECHANUMERO pero esa me la guardo para otro día.

¿Curva ROC y Excel? ¡Si no tiene nada que ver! No del todo. En ocasiones tenemos que pintar las curvas ROC y empleamos las herramientas específicas para ello, sin embargo es habitual que nuestros resultados sean presentados en Excel (demasiado habitual). En ese caso creamos nuestros datos para llevarlos a Excel y realizamos nuestro gráfico. Ya tenemos nuestra tabla y hacemos un algo muy parecido a lo que tenemos más arriba. La curva ROC es un mecanismo para evaluar nuestro modelo y compara la especificidad que es la probabilidad de clasificar mal un caso negativo frente a la sensibilidad que es la probabilidad de clasificar correctamente un caso positivo, es decir, falsos positivos frente a verdaderos positivos. El área bajo la curva ROC será un valor entre 0,5 y 1. Cuanto más próximo a 1 mejor será nuestro modelo. Queda pendiente una revisión «en condiciones» de las curvas ROC y de los gráficos lift , mucho mejores cuando tienes que presentar resultados en un área de negocio. ...

Pongo a vuestra disposición un archivo Excel que nos permite la realización del siguiente gráfico: En este link podéis descargaros el archivo. He elegido formato Excel 2003 para el archivo, de este modo lo podréis utilizar muchos de vosotros. Si alguien desea el formato 2007 que lo diga. De momento es una primera versión a la que iré añadiendo un mayor número de funcionalidades. Se trata de un gráfico en el que podemos variar el color de cada una de las provincias mediante la siguiente macro: ...

Puede resultarnos útil tener todos los archivos de un directorio en una tabla de excel. Si estamos documentando un proceso, si nos dan un gran número de ficheros y tenemos que realizar procesos repetitivos sobre ellos, si queremos tener inventariados nuestros programas,… Para esto os planteo una macro bien sencilla que recorre un directorio y nos escribe los elementos que encuentra en él. El código visual basic para la macro en Excel no puede ser más sencillo: ...

Quería plantear hoy un truco Excel que no es un truco como tal, si no más bien una idea para que podáis hacer más vistosos vuestros informes con gráficos de caracteres, sobre todo para aquellos dinosaurios que hagáis muchos ranking o puntuaciones. La idea es muy sencilla se trata de emplear la función REPETIR de Excel pero «con talento». =REPETIR(«|»;E12) nos repite el pipe | tantas veces como le indiquemos en la casilla E12. Pero hay algo que si tiene importancia, la fuente que elijamos para el pipe: ...

Vamos a mejorar muchos de nuestros informes con mapas de España realizados a través de Excel. Os contaré la metodología que empleo para realizarlos y a partir de ahí vosotros podréis mejorarlos. Voy a realizar un mapa de España por Comunidades Autónomas y le vamos a modificar en función del gasto en prestaciones por desempleo (http://www.tt.mtin.es/periodico/laboral/201006/mayo.pdf).Todo empieza por conocer los archivos SVG y pasa por agradecer a los usuarios de la wikipedia que distribuyan todo tipo de mapas realizados por ellos mismos y de uso libre. En este caso buscamos un mapa de España por Comunidades Autónomas . Necesitamos una aplicación libre para manejar estos mapas y la mejor, más adecuada y la que yo uso es Inkscape la conocí ayer por la mañana, después me harté de botellines, me fui a sembrar unas guindillas y a día de hoy ya hago mis pinitos con el portátil del trabajo, porque en el mío no funciona. Da problemas en Win 64 bits (¡si!, uso win, como tú que lees esto ya que es muy poco probable que pertenezcas al 3% que usa Mac o al 3% que usa Linux). Con esta aplicación tenemos a nuestro alcance ver perfectamente que es lo que hace el XMLy que espero termine en un paquete de R para hacer mapas de España.Bien, tenemos Inkscape y el mapa por Comunidades de Wikipedia y ahora viene un trabajo muy sencillo, muy aburrido y muy en la línea de un tipo mediocre como el que escribe ahora mismo. Voy a copiar en Inkscape y pegar en Excel cada Comunidad Autónoma, lo transformo en un objeto de Ms-Office para que me sea más sencillo modificarle las propiedades y le doy un nombre a cada figura. Tras 5 minutos haciendo un puzle tengo algo parecido a esto en Excel: ...

Con Visual Basic podemos modificar la configuración regional. Podemos crear macros en Excel que nos realicen esta tarea. De este modo si trabajamos con aplicaciones que tienen configuración americana podemos cambiar con una macro, pegar los valores y volver a cambiar la configuración. Para poner separador decimal «.» y separador de miles «,» tendremos que emplear el siguiente código: Sub formato_americano()'' formato_americano Macro' With Application .DecimalSeparator = "." .ThousandsSeparator = "," .UseSystemSeparators = False End With End Sub Es un excelente ejemplo de uso de Application. De forma análoga si deseamos volver a la configuración europea solo debemos emplear los separadores del sistema: ...

A la hora de referenciar en Excel celdas de tablas dinámicas es muy molesto encontrarnos con referencias del tipo «+IMPORTARDATOSDINAMICOS("CLIENTES"; F3;"POTENCIAL";2)» El IMPORTARDATOSDINAMICOS puede resultarnos muy molesto para trabajar con fórmulas que normalmente arrastramos. Para evitar este problema hemos de colocar en la barra de herramientas el botón «Generar GetPivotData». Esto lo hacemos ubicándonos en una barra de herramientas y con el botón derecho nos vamos a Personalizar y entre los botones de datos tenemos el Generar GetPivotData, como vemos en la figura adjunta. Lo seleccionamos y lo pulsamos cuando deseemos no tener la dichosa fórmula del IMPORTARDATOSDINAMICOS y podemos hacer fórmulas más habituales. ...

Una de las tareas más comunes en Excel es la de transponer filas. En ocasiones hemos de transformar columnas en filas o viceversa: Es muy habitual copiar y pegar transponiendo pero esta labor es muy manual cuando manejamos hojas con gran cantidad de fórmulas y que pueden generar informes automáticos. Para transponer contamos con la ayuda de la función INDIRECTO de Excel. En la ayuda se define como: “Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia formula” ...

Hay operaciones que pueden ser muy habituales de hacer con Excel y necesitaríamos una función propia que la realizara. A continuación vamos a plantear una situación en la que es especialmente útil crear una función específica y guardarla en el libro de macros personal para utilizarla en nuestras sesiones SAS. En este caso concreto vamos a crear una función que nos redondeé un número a 0 o 0.5, es decir, 6,3 valdría 6 y 6,7 valdría 6,5. Para realizarlo sin una función específica emplaríamos múltiples funciones de Excel, por ejemplo: ...