Notas sobre Excel 00 by Universidad Tesla

Notas sobre Excel 00 Notas sobre Excel 00 .................................................................................................................. 1 Lista desplegable con ajuste automático en Excel ........................................................................ 1 Pasar de hojas Excel a tablas en HTML.......................................................................................... 2 Posts Relacionados:................................................................................................................. 3 viernes, julio 17, 2009 .......................................................................................................... 3 Transferir datos de Excel a Access - tercera nota ......................................................................... 3 miércoles, julio 22, 2009 ....................................................................................................... 5 Números grandes en Excel – primera nota.................................................................................. 5 lunes, julio 27, 2009............................................................................................................. 6 Números grandes en Excel – segunda nota ................................................................................... 6 Contar condicional de números con celdas en blanco ...................................................................... 7 Determinar colores en el formato personalizado de números............................................................ 7 jueves, agosto 06, 2009........................................................................................................ 7 martes, agosto 18, 2009 .................................................................................................... 8 Valores más frecuentes en una matriz .......................................................................................... 8 Cómo convertir un PDF a Excel ................................................................................................... 10 Redondeo condicional de horas en Excel.................................................................................... 11 domingo, septiembre 06, 2009 ................................................................................................... 11 Mostrar y ocultar líneas de división en Excel 2003 ...................................................................... 11 Gráfico Excel con fecha.............................................................................................................. 14

Lista desplegable con ajuste automático en Excel Esta es una variante de la técnica que mostré en la nota sobre cómo agregar valores únicos en una lista desplegable con validación de datos. Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacer para que las facturas abonadas vayan desapareciendo de la lista desplegable? Supongamos que esta es la lista de las facturas: En el rango B6:B19 aparecen los números de las facturas y en el rango C6:C19 si han sido pagadas (señalado con la palabra “pagada). En nuestro ejemplo, queremos crear una lista desplegable que sólo muestre aquellas facturas que no han sido pagadas Por supuesto, queremos que esta lista sea dinámica, es decir que cuando pongamos “pagada” al lado de una factura, ésta desaparezca de la lista . La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista. Empezamos por crear la primer columna auxiliar en el rango E6:E19.

En la celda E6 ponemos esta fórmula =SI(LARGO(C6)=0,FILA(),"") y la copiamos a todo el rango. Esta columna nos da un “número de orden” que es de hecho un número entero único (el número de fila) que identifica a las facturas que aún no han sido pagadas. Ahora agregamos una segunda columna auxiliar en el rango F6:F19 con esta fórmula

=INDICE($B$6:$B$19,COINCIDIR(K.ESIMO.MENOR($E$6:$E$19,FILA()-5),$E$6:$E$19)) Como puede verse, sólo las facturas pendientes aparecen en la lista. Tenemos un pequeño problema y es que cuando una factura ha sido pagada el valor de la fórmula es #NUM!. Si usáramos el rango F6:F19 para crear la lista de los valores, también el error #NUM! aparecería en ella lo cual es poco elegante. Una de las soluciones es crear una tercera columna en el rango G6:G19 con la fórmula =SI(ESERROR(F6),"",F6) Este rango es el que usamos para definir la lista de valores válidos. Creamos el nombre "lista"

Ahora usamos validación de datos para crear la lista desplegable en la celda C2 Si quitamos la palabra “pagada” en alguna celda, la factura correspondiente aparecerá automáticamente en la lista; si agregamos “pagada”, la factura desaparecerá.

Pasar de hojas Excel a tablas en HTML Tableizer es un servicio online que nos permite pasar nuestras hojas en Excel a tablas en formato HTML, de manera que podamos insertarla en alguna presentación abierta en el navegador o similar.

El servicio es nuevo y sencillo de manejar, solo consiste en copias las celdas de tu hoja Excel al área que te proporcionan. Luego clic en Tableize it! y listo. çLa salida nos proporciona un código generado en HTML, con sus correspondientes definiciones en CSS para ultimar algunos detalles de color u otros, el cual puede ser incluido en alguna entrada del blog o página web. También nos muestra una vista previa de la salida generada en la parte inferior del código. Tableizer es nuevo y aún no permite la carga de documentos Excel, pero esperemos que sigan con el proyecto. Alguna vez necesité hacer algo así y tuve que pasar mis celdas Excel a una imagen para no complicarme. Enlace: Tableizer Via: Enlazando Web

Posts Relacionados: • • • • •

Styleneat: Dar formato a hojas de estilo CSS Cómo armar una PC Descargar emulador de NEO GEO con juegos Crear modelo de base de datos online Descargar mapa Dota All Stars 6.61b

viernes, julio 17, 2009

Transferir datos de Excel a Access - tercera nota En las notas anteriores vimos cómo transferir datos de Excel a Access manualmente y programáticamente. La necesidad de usar Access surge como una de las soluciones posibles cuando queremos, por ejemplo, consolidar datos de varias hojas en una única base de datos para generar a partir de ella una tabla dinámica. Si el total de filas a consolidar supera el límite de una hoja de Excel (hasta la versión 2003 incluida, 65536 filas) no podemos almacenar los datos consolidados en una única hoja. Si trabajamos con Excel 2007 podemos almacenar más de un millón de filas en una hoja, lo cual nos exime de tener que usar herramientas externas a Excel. Pero como más del 50% de los usuarios usa versiones anteriores a Excel 2007, almacenar datos en Access es una buena solución. En las soluciones que muestro en las notas mencionadas suponemos que Access está instalado en la máquina del usuario. Por supuesto, éste no es siempre el caso y en esta nota mostraremos como usar Access como recipiente para nuestros datos aún cuando no esté instalado en nuestra máquina. Empecemos por aclarar que los archivos con formato .mdb no requieren la presencia de Access para poder ser utilizados. Estos archivos están asociados con el Microsoft Jet Database Engine, que está incluido en el paquete de Office. Si bien este componente ha sido abandonado por Microsoft, sigue siendo vigente por motivos de compatibilidad. Nuestra meta es crear dos rutinas, una para crear el archivo “.mdb” y otra para almacenar los datos de la hoja u hojas Excel en la tabla. Nuestro primer paso es crear una referencia a * Microsoft Ext. ADO 2.5 for DDL Security (o posteriores). * Microsoft ActiveX Data Object 2.5 Library (o posteriores). en el editor de VB, en el menú Tools—References

Un detalle a tomar en cuenta es que las macros que mostraré más adelante están construidas para el ejemplo específico de esta nota y hay que editarlas para adaptarlas a otros usos. En nuestro ejemplo tenemos un cuaderno Excel con tres hojas. Cada hoja contiene datos de tres distintos años (2005, 2006 y 2007) que queremos consolidar en una única hoja para construir a partir de ella una tabla dinámica. La rutina para crear el archivo “.mdb” con una tabla para almacenar los datos es la siguiente:

Option Explicit Const dataSource As String = "provider=microsoft.jet.oledb.4.0;" _ & "data source=d:\BaseDeDatos.mdb" Const tableName As String = "datos_export" Sub crearDB() Dim catalog As ADOX.catalog

Dim new_table As ADOX.Table 'crear la base de datos Set catalog = CreateObject("adox.catalog") catalog.Create dataSource ' crear la tabla para contener los datos Set new_table = CreateObject("adox.table") new_table.Name = tableName new_table.Columns.Append "mes", adDate new_table.Columns.Append "pais" new_table.Columns.Append "suma", adDouble catalog.Tables.Append new_table End Sub En la parte superior del módulo, antes de la rutina “crearDB” hemos definido dos constantes. Lo hacemos de esta manera ya que también en la segunda rutina haremos uso de estas constantes. Esta macro crea una base de datos Access (BaseDeDatos.mdb) con una tabla (datos_export). Ahora tenemos que exportar los datos de la hoja activa al la tabla “datos_export”, lo que hacemos con esta macro: Sub AgregarDatos() ' exporta los datos de la hoja activa a una tabla de Access ' este procedimiento es especifico para el ejemplo. ' Para otros usos hay que adaptarlo Dim cn As ADODB.Connection, rs As ADODB.Recordset, fila As Long Set cn = New ADODB.Connection cn.Open dataSource Set rs = New ADODB.Recordset rs.Open tableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table fila = 2 ' la primer fila en la hoja despues de los encabezamientos Do While Len(Cells(fila, 1)) > 0 With rs .AddNew .Fields("mes") = Range("A" & fila).Value .Fields("pais") = Range("B" & fila).Value .Fields("suma") = Range("C" & fila).Value .Update End With fila = fila + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub Esta rutina es la misma que hemos usado en la segunda nota de la serie, adaptada a nuestro ejemplo. Ahora activamos la hoja cuyos datos queremos exportar y corremos la macro para cada una de las hojas que queremos consolidar. A esta altura de los acontecimientos tenemos un archivo Access con un tabla que contiene los datos de las hojas (podemos ver que el tamaño del archivo se ha incrementado) Ahora podemos crear la tabla dinámica usando la opción Fuente de datos Externa

En el segundo paso creamos una nueva fuente de datos, que en nuestro ejemplo llamaremos “consolidarMdb” usando el driver de Access

Después de apretar Connect, apretamos OK con lo cual hemos creado la nueva conexión. De aquí en adelante seguimos los pasos del asistente tal como hemos mostrado en esta nota. El resultado es:

Technorati Tags: MS Excel

miércoles, julio 22, 2009

Números grandes en Excel – primera nota Un lector me consulta cómo hacer en Excel para que cuando introducimos un número grande éste no se convertido en forma automática a la notación científica. Por ejemplo, si introducimos el número 123456789123 en una celda, lo que veremos en pantalla es 1.23457E+11

Esta consulta involucra en realidad dos temas: # formato automático de números en Excel (cómo exhibe Excel los números en la pantalla) # cálculos con números de más de 15 cifras significativas. En esta nota tocaremos el primer tema, y dejaremos el segundo para una futura nota. Empecemos por ver un ejemplo donde nuestro número tiene por lo menos 12 cifras, pero menos de 15. Volviendo a nuestro ejemplo (que tiene 12 cifras) veamos qué significa 1.23568E+11.

La notación E+11 es una forma abreviada de escribir el número. Esta notación nos dice que hemos tomado el número y lo hemos divido por 10 elevado la potencia de 11 (es decir 100,000,000,000). Cuando se trata de números grandes, Excel pone el formato científico automáticamente si la celda está formada como General (el formato por defecto de Excel). Si queremos evitar que Excel ponga el formato científico tenemos que dar un formato de número a la celda antes de ingresar el número. Si el número tiene menos de 15 cifras, podemos cambiar el formato después de haber introducido el número. En esta imagen podemos ver que si bien los números en la columna A aparecen en notación científica, Excel los “ve” tal como son; en la columna B vemos los mismos números introducidos en celdas previamente formadas con formato numérico Ahora veamos una “curiosidad” de Excel cuando se trata de números grandes

En las celdas A1:A2 hemos puesto el número 123456789123 que Excel exhibe como 1,23457E+11. En la barra de fórmulas podemos ver que sólo se trata de exhibición; Excel sigue “viendo” el número tal como la introducimos en la celda. En el rango B1:B2 ponemos el mismo número pero previamente hemos formado las celdas como Número. Ahora usaremos el icono de aumentar decimales para agregar un decimal en las celdas A1 y A2.

B1:B2, es 123456800000!

Como puede observarse, se ha producido una pérdida de precisión. Ahora el número en las celdas, tanto en el rango A1:A2 como en Hasta aquí hemos usado un número de menos de 15 cifras. ¿Qué paso cuando usamos un número de más de 15 cifras? En la celda A1 introducimos el número 123456789123456789 (18 cifras)

Sin embargo, el número que aparece en Excel después de apretar Enter es 123456789123456000 Es decir, las últimas tres cifras han sido reemplazadas por ceros!. Toda cifra después de la posición 15, será convertida a cero. Esto se debe a una limitación, no solamente de Excel, que será explicada en la próxima nota sobre el tema. Lo que debemos saber a esta altura de los acontecimientos es que: # Excel tiene una limitación de precisión si el número excede las 15 cifras significativas # para introducir números de más de 15 cifras debemos convertirlos en texto, ya sea anteponiendo un ' o dando el formato Texto a la celda antes de introducir el número. Esto genera un serio problema si tenemos que realizar cálculos con números grandes. Como enfrentarnos a estos problemas será el tema de la próxima nota.

lunes, julio 27, 2009

Números grandes en Excel – segunda nota Excel guarda los números con un límite de precisión de 15 cifras. Esto se hace evidente cuando introducimos un número de más de 15 cifras. Veamos qué pasa cuando introducimos el número 123456789123456789 (18 cifras) en una celda a la cual le hemos de antemano el formato de número para evita que Excel ponga el formato científico automáticamente Al apretar Enter todas las cifras a la derecha de la posición 15 son convertidas en ceros. El mismo problema existe cuando la parte fraccional de un número supera las 15 cifras. Por lo general esta limitación no es significativa, en especial si usamos Excel para cálculos financieros. Si necesitamos operar con mayor precisión de la que permite Excel dos posibilidades son: # - descargar el complemento xlPrecision # - utilizar los conceptos esbozados por Tushar Metha en esta nota La ventaja del complemento xlPrecision es que también soluciona el problema precisión y redondeo que existe en Excel. Es este el problema de la conversión binaria (no sólo de Excel) que se hace evidente con este ejemplo: restamos 4.0 de 4.1

Aparentemente el resulta es el correcto: 0.1. Veamos qué pasa cuando formamos la celda para que muestre más de 15 decimales

Contar condicional de números con celdas en blanco En ciertas situaciones Excel puede llevarnos a cometer errores difíciles de detectar. Este ejemplo es la “vida real”, es decir, de un compañero de trabajo. Supongamos que tenemos un rango de nueve celdas, cinco de las cuales contienen números y las cuatro restantes en blanco La fórmula =SUMA(B2:B10) da como resultado 150. Es decir, las celdas en blanco son consideradas ceros o ignoradas por la función SUMA. Lo mismo sucede si usamos la función CONTAR para contar cuántos números hay en el rango. =CONTAR(B2:B10) da como resultado 5. Lo mismo con función CONTARA. Todo esto nos puede llevar a pensar que si queremos contar cuántos números distintos de cero hay el rango, todo lo que tenemos que hacer es usar la función CONTAR.SI de esta manera =CONTAR.SI(B2:B9,"<>0") Esta fórmula da como resultado 9. Este resultado es un tanto extraño. Veamos qué pasa si creamos una columna auxiliar con la fórmula =B2<>0 (que copiamos a los largo del rango) Vemos que obtenemos cinco VERDADERO y cuatro FALSO. Es decir, podríamos esperar que el resultado de CONTAR.SI en nuestro caso sea 5, pero como vemos es 9. En nuestro caso, es muy fácil percibir que el resultado no es el esperado, pero si analizamos un rango grande, digamos mil celdas, es más que probable que aceptemos el resultado como correcto. En caso de tener que contar condicionalmente números en un rango que contiene o puede contener celdas en blanco, podemos usar alguna de estas soluciones: # usar la función CONTAR.BLANCO para “corregir el resultado de CONTAR.SI =CONTAR.SI(B2:B10,"<>0")-CONTAR.BLANCO(B2:B10) # usar la fórmula matricial ={SUMA(--(B2:B10<>0))} (recordemos una vez más que las fórmulas matriciales deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter) # usar la función SUMAPRODUCTO: =SUMAPRODUCTO(--(B2:B10<>0))

Determinar colores en el formato personalizado de números jueves, agosto 06, 2009 Uno de los blogs que sigo, Bacon Bits, trae una nota sobre cómo determinar el color de un formato personalizado de números. En notas anteriores he explicado que podemos determinar el color de un formato de número condicionalmente. Por ejemplo, si queremos que la fuente de todo número superior a 1000 sea verde, números menores de 500 aparezcan en rojo y los restantes en negro. podemos usar este formato personalizado [Verde][>1000]General;[Rojo][<500]general;[negro]general Convengamos que nuestra idea del verde es diferente de la que parece tener Microsoft. Una solución es renunciar al verde y usar el azul

Otra solución es usar el número de índice del color en la paleta en lugar del nombre explícito del color. De esta manera tenemos total control del color de la fuente. El único problema a resolver es saber cuál es el número de índice del color que queremos usar. Para averiguar el número de índice del color podemos usar la técnica que mostramos con las macrofunciones de XLM o una macro como ésta: Sub nr_color() Dim cell As Range, rngNrColor As Range Set rngNrColor = Application.InputBox(prompt:="select range", Type:=8) For Each cell In rngNrColor cell.Offset(0, 1).Interior.ColorIndex = cell.Value Next End Sub En una hoja creamos un rango con una serie de números del 1 al 56 (por ejemplo A1:A56), corremos la macro y obtenemos esta lista (para facilitar la visualización he divido el rango en tres columnas) Para usar el verde cuyo número de índice (en mi paleta) es el 10, modificamos el formato personalizado de esta manera [Color10][>1000]General;[Rojo][<500]general;[negro]general>

Es decir, usamos “Color” y el número de índice. Technorati Tags: MS Excel

martes, agosto 18, 2009

Valores más frecuentes en una matriz Esta nota es producto de la consulta de uno de mis lectores, Xisco, quien también, al final de cuentas, aportó una de las soluciones. Dados un rango o matriz de valores, Excel permite calcular el valor más frecuente con facilidad usando la función MODA. Por ejemplo, dada esta matriz de valores (5 columnas X 10 filas = 50 valores aleatorios entre 1 y 10)

si incluimos el rango de la matriz en el nombre “matriz”, usamos esta fórmula para calcular el valor más frecuente (10, que aparece 9 veces) =MODA(matriz)

El valor de la celda I3 lo calculamos con =CONTAR.SI(matriz,I2). La pregunta es cómo calculamos el segundo valor más frecuente, o el tercero, etc. Excel no tiene una función nativa para este cálculo. Mi primera aproximación al problema fue usar columnas auxiliares (técnica que siempre recomiendo ya que hace que los modelos sean más legibles y fáciles de controlar). Creamos un rango auxiliar que contiene todos los valores únicos que aparecen en la matriz ( en nuestro caso, de 1 al 10) y paralelamente un rango auxiliar que calcula la frecuencia de cada valor Vemos que, efectivamente, el 10 aparece 9 veces y el segundo valor más frecuente es el 1 con 8 apariciones. Incluimos el rango B14:B23 en el nombre “valores” y el rango C14:C23 en el nombre “frecuencia”. En la celda I5 ponemos el número de orden de frecuencia buscado (2 por ejemplo) y en la celda I4 ponemos esta fórmula

=INDICE(valores,COINCIDIR(K.ESIMO.MAYOR(frecuencia,I5),frecuencia,0)) En esta fórmula la función K.ESIMO.MAYOR busca el número más grande del rango “frecuencia” de acuerdo al parámetro introducido en la celda I5; luego busca en que fila del rango se encuentra usando la función COINCIDIR y este resultado es usado por la función INDICE para dar el valor adecuado del rango “valores”. En esta solución hemos usado dos columnas auxiliares (“valores” y “frecuencia”). El estimado Xisco propone una solución que use sólo una columna auxiliar (o ninguna, si los números vienen ordenados en una sola fila o columna). Dado que en nuestro ejemplo los números están dispuestos en una matriz, debemos transformarlos primero en una columna. Para esto podemos usar la técnica que hemos mostrado en la nota sobre cómo convertir datos de matriz a columna o fila en Excel. En la celda B13 ponemos esta fórmula =DESREF(matriz,RESIDUO(FILA()-14,FILAS(matriz)),TRUNCAR((FILA()-14)/FILAS(matriz)),1,1) que copiamos hasta la celda B63, es decir 50 filas. Este rango lo incluimos en el nombre “valores2”. El resultado es un rango de 1 columna por 50 filas que contiene todos los calores que aparecen en las 50 celdas de la matriz. Ahora reemplazamos la fórmula de la celda I6 por la siguiente:

=INDICE(valores2,COINCIDIR(K.ESIMO.MAYOR(FRECUENCIA(valores2,valores2),I5),FRECUENCIA(valores2,valores2),0)) La bastante obvia pregunta es: ¿se puede hacer el mismo cálculo sin columnas auxiliares? Por supuesto, con una UDF (función definida por el usuario). Pero, ¿puede hacerse sin usar Vba (macros)? Toda sugerencia será bienvenida.

Cómo convertir un PDF a Excel Sáb, 08/29/2009 - 13:33 : carlos Poder convertir un PDF a Excel probablemente no suene tan interesante, pero la herramienta que vamos a comentar es realmente útil, porque la mayoría de archivos PDF que contienen tablas difícilmente pueden ser copiados y pegados en una hoja excel, como si sucede con word. En la mayoría de los casos, al hacer un copiar y pegar, no se respetan las columnas, filas o el inicio y final de las tablas. Y si tenemos una tabla de datos realmente compleja en PDF y queremos pasarla a Excel, el trabajo manual que tenemos que realizar para acomodar los datos es realmente incomodo. Afortunadamente podemos usar PDF to Excel para crear nuestros archivos XLS, y poder editarlo en MS Excel, OpenOffice, Google Docs y WordPerfect Office. Y lo mejor de todo, es que es gratis... Algunos de los programas para convertir ficheros PDF a Excel que hay en internet no mantienen el aspecto de la tabla original. En el ejemplo, que hemos visto arriba, PDF to Excel si mantiene el look de la tabla, incluyendo tamaño de fuentes y estilos, bordes, colores de celda y espacio de filas y columnas. Entonces, lo que debemos hacer es lo siguiente: 1. Seleccionar el PDF a convertir, escribir nuestro correo electrónico y pulsar el botón Convert 2. La aplicación se encargará de procesar el archivo (identificar las tablas, descartar el contenido adicional y empaquetar las tablas dentro de un archivo XLS) y luego enviarlo por email. 3. Verificar nuestro correo, que el archivo xls nos llegará en minutos. 4. Y listo. Enlace | PDF to Excel

sábado, agosto 29, 2009

Redondeo condicional de horas en Excel Al trabajar con horas en Excel, por ejemplo en una plantilla horaria, suele surgir la necesidad de redondear los intervalos por múltiplos de minutos. Por ejemplo, si pagamos por medias horas o por intervalos de 15 minutos. En la nota sobre redondeo de horas en Excel mostré algunas técnicas para esta tarea con la función REDONDEAR. La regla general es =REDONDEAR(A1*(60/m*24),0)/(60/m*24) donde m es la cantidad de minutos en el múltiplo y la celda A1 contiene la hora a redondear. También podemos usar la función REDOND.MULT de esta manera =NSHORA(HORA(A1),REDOND.MULT(MINUTO(A1),m),0) Si necesitamos redondear hacia arriba o hacia abajo podemos usar las funciones MULTIPLO.SUPERIOR o MULTIPLO.INFERIOR respectivamente. Para redondear hacia arriba usamos la fórmula =NSHORA(HORA(A1),MULTIPLO.SUPERIOR(MINUTO(A1),m),0) Para redondear hacia abajo usamos =NSHORA(HORA(A1),MULTIPLO.INFERIOR(MINUTO(A1),m),0) Otra variante es redondear hacia arriba o hacia abajo condicionalmente. Por ejemplo, los primeros 15 minutos son ajustados hacia abajo; a partir del minuto 16 se ajusta hacia arriba. Supongamos que hasta los primeros 15 minutos queremos ajustar hacia la hora pasada más cercana y a partir del minuto 16 hacia la hora próxima. Para calcular este ajuste usamos una fórmula con la función SI, donde A1 contiene la hora a evaluar A2 el intervalo de ajuste (15 minutos en nuestro caso)

=SI(MINUTO(A1)<=A2,NSHORA(HORA(A1),MULTIPLO.INFERIOR(MINUTO(A1),60),0),NSHORA(HORA(A1),MULTIPLO.SUPE En esta fórmula usamos la constante 60 ya que ajustamos por horas completas. Buen fin de semana! Technorati Tags: MS Excel Publicado por Jorge L. Dunkelman en 11:42 AM Etiquetas: Fechas y hora

domingo, septiembre 06, 2009 Mostrar y ocultar líneas de división en Excel 2003 Las líneas de división (o cuadrícula, como ha sido traducido en Excel 2007) aparecen por defecto en las hojas de Excel. Consideraciones de diseño y/o presentación pueden hacer necesario ocultar las líneas de división en la hoja. En Excel 2007 hacemos esto con un único clic en el icono correspondiente de la pestaña Diseño de Página de la cinta

En Excel clásico (versión 2003 y anteriores) la cuestión en más trabajosa. Tenemos que usar el menú Herramientas-Opciones, ir a la pestaña Ver y quitar la marca de la opción Líneas de División.

En Excel clásico (versión 2003 y anteriores) la cuestión en más trabajosa. Tenemos que usar el menú HerramientasOpciones, ir a la pestaña Ver y quitar la marca de la opción Líneas de División. Si usamos con frecuencia esta opción en Excel clásico podemos crear un atajo con una macro sencilla que luego podemos asociar a un icono que pondremos en alguna de las barras de herramientas existentes. La macro es muy sencilla y la ponemos en un módulo del cuaderno Personal.xls, de manera que esté disponible para todo cuaderno de Excel Sub lineas_division() If ActiveWindow.DisplayGridlines = True Then ActiveWindow.DisplayGridlines = False Else ActiveWindow.DisplayGridlines = True End If End Sub El lugar adecuado para poner un icono ligado a la macro es la barra de herramientas de Formato. Para hacerlo seguimos los siguientes pasos:

1 – Abrimos el menú Ver-Barra de Herramientas-Personalizar. En la pestaña de Comandos elegimos la categoría Macros

2 – Arrastramos el “smiley” al lugar indicado y abrimos el menú contextual con un clic del botón derecho. Activamos la opción Asignar Macro y elegimos la macro apropiada

Volvemos a abrir el menĂş contextual, ponemos un texto adecuado en Nombre y cambiamos la imagen del icono

A partir de ahora tenemos un nuevo icono en la barra de herramientas Formato que nos permite poner o quitar las líneas de división con un solo clic.

Technorati Tags: MS Excel

jueves, septiembre 17, 2009

Gráfico Excel con fecha Un lector me consulta cómo hacer este gráfico en Excel

Como pueden ver el eje de las X (categorías en Excel) es una escala de tiempo. Lo particular en este gráfico es que en el eje de las X sólo aparecen las fechas de los puntos de la serie de valores. Crear este gráfico en Excel no es trivial y es lo que mostraremos en esta nota. El resultado final será el siguiente

Empezamos por crear los datos básicos de grafico (fechas y valores) y el gráfico

Como puede observarse aparecen en el gráfico de las X valores que no figuran explícitamente en los datos de origen (en la columna A). Esto se debe a que Excel reconoce los datos en la columna A como fechas. Si nos fijamos en las definiciones del eje de la X

vemos que el valor mínimo es la primer fecha en el rango, el valor máximo es el último valor en el rango y la unidad principal es un mes. Dado que las fechas en los datos no se distribuyen en intervalos regulares, no podemos crear el efecto deseado. No tenemos forma de ocultar las fechas para las cuales no hay datos. Para lograr el efecto deseado tendremos que transformar los datos. Empezamos por crear datos auxiliares en las columnas D y E

En la columna D ponemos todas fechas, día por día, comprendidas entre la primer y la última fecha. En la columna E ponemos la fórmula =BUSCARV(D3,$A$2:$B$10,2,0) Esta forma pone el dato original en la fecha o #N/A si para la fecha no hay datos. Ahora cambiamos los datos en el gráfico por los del rango D1:E333

El segundo paso es cambiar el tipo de eje de las X de Automático a Categoría

Ahora tenemos que realizar una segunda transformación a los datos. En nuestro ejemplo creamos un rango de datos en las columnas G y H. En la columna G ponemos la fórmula =SI(ESNUMERO(E2),D2,"") y en la columna H sencillamente creamos una referencia a los datos en la columna E

El resultado es que en los puntos en los que no hay valores, la fecha no aparece. Ahora podemos usar esta serie de datos en el gráfico, con este resultado

Como puede verse todavía no hemos llegado al resultado final. Nuestros próximos retoques son los siguientes: # - abrimos el menú de formato del Eje de las X y en la pestaña Escala ponemos el valor 1 en la casilla de “Número de categorías entre rótulos de marcas de graduación

# - En la pestaña Tramas señalamos “Ninguna” en la opción Marca de graduación principal

El resultado “casi” final es

# - No queremos que el primer punto de la serie esté sobre el eje de la Y, para lo cual agregamos líneas en blanco al principio del rango y modificamos los datos de origen del gráfico de acuerdo

# - Para crear el efecto de retícula, cambiamos la trama de las líneas de división y definimos barras de error para los puntos de la serie. Este paso implica cambiar la definición de la escala del eje de las Y a valores fijos, quitando las marcas de Automático de Mínimo y Máximo

Abrimos el menú formato de serie de datos activamos la pestaña Barras de Error Y, elegimos la opción Ambas y en Valor Fijo ponemos el máximo del eje de las Y (400 en nuestro ejemplo)

Finalmente seleccionamos las barras de error y abrimos el menú de formato. En la pestaña tramas elegimos la trama punteada para la línea y en Marcador la opción a la derecha

Con esto termina nuestra tarea

Technorati Tags: MS Excel

Compilado por Luar Arev Terminado el 18 de septiembre de 2009