Notas sobre Excel 00

Notas sobre Excel 00.................................................................................................................. 1Lista desplegable con ajuste automático en Excel ........................................................................ 1

Pasar de hojas Excel a tablas en HTML.......................................................................................... 2Posts Relacionados:................................................................................................................. 3

viernes, julio 17, 2009 .......................................................................................................... 3Transferir datos de Excel a Access - tercera nota......................................................................... 3

miércoles, julio 22, 2009....................................................................................................... 5Números grandes en Excel – primera nota.................................................................................. 5

lunes, julio 27, 2009............................................................................................................. 6Números grandes en Excel – segunda nota ................................................................................... 6Contar condicional de números con celdas en blanco ...................................................................... 7Determinar colores en el formato personalizado de números............................................................ 7

jueves, agosto 06, 2009........................................................................................................ 7martes, agosto 18, 2009 .................................................................................................... 8

Valores más frecuentes en una matriz .......................................................................................... 8Cómo convertir un PDF a Excel ................................................................................................... 10

Redondeo condicional de horas en Excel....................................................................................11domingo, septiembre 06, 2009 ................................................................................................... 11

Mostrar y ocultar líneas de división en Excel 2003 ......................................................................11Grá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 convalidación de datos.

Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacerpara 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 rangoC6:C19 si han sido pagadas (señalado con la palabra “pagada).

En nuestro ejemplo, queremos crear una lista desplegable que sólo muestreaquellas facturas que no han sido pagadas

Por supuesto, queremos queesta lista sea dinámica, esdecir que cuando pongamos“pagada” al lado de unafactura, ésta desaparezca dela lista.La solución consiste en creardos columnas auxiliares yuna tercera que será la quecontenga los valores válidospara la lista.

Empezamos por crear laprimer columna auxiliar en elrango E6:E19.

1

En la celda E6 ponemos esta fórmula=SI(LARGO(C6)=0,FILA(),"") y la copiamos a todo elrango.

Esta columna nos da un “número de orden” que es dehecho un número entero único (el número de fila) queidentifica a las facturas que aún no han sido pagadas.

Ahora agregamos una segunda columna auxiliar en elrango 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 facturaspendientes aparecen en la lista.Tenemos un pequeño problema y es quecuando una factura ha sido pagada el valor dela fórmula es #NUM!.Si usáramos el rango F6:F19 para crear lalista de los valores, también el error #NUM!aparecería en ella lo cual es poco elegante.

Una de las soluciones es crear una terceracolumna 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 desplegableen la celda C2

Si quitamos la palabra “pagada” en alguna celda, la facturacorrespondiente aparecerá automáticamente en la lista; siagregamos “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, demanera que podamos insertarla en alguna presentación abierta en el navegador o similar.

2

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 ultimaralgunos 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: TableizerVia: 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 datosde 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) nopodemos 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 queusar 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 nuestrosdatos 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 bieneste 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 hojasExcel 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 lasmacros que mostraré más adelante estánconstruidas para el ejemplo específico de estanota y hay que editarlas para adaptarlas aotros usos.

En nuestro ejemplo tenemos un cuadernoExcel con tres hojas. Cada hoja contiene datosde tres distintos años (2005, 2006 y 2007)que queremos consolidar en una única hojapara construir a partir de ella una tabladinámica.La rutina para crear el archivo “.mdb” con unatabla 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

3

Dim new_table As ADOX.Table

'crear la base de datosSet catalog = CreateObject("adox.catalog")catalog.Create dataSource

' crear la tabla para contener los datosSet new_table = CreateObject("adox.table")new_table.Name = tableNamenew_table.Columns.Append "mes", adDatenew_table.Columns.Append "pais"new_table.Columns.Append "suma", adDoublecatalog.Tables.Append new_tableEnd Sub

En la parte superior del módulo, antes de la rutina “crearDB” hemos definido dos constantes. Lo hacemos de esta maneraya 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 lahoja activa al la tabla “datos_export”, lo quehacemos con esta macro:

Sub AgregarDatos()' exporta los datos de la hoja activa a una tabla deAccess' este procedimiento es especifico para el ejemplo.' Para otros usos hay que adaptarlo

Dim cn As ADODB.Connection, rs AsADODB.Recordset, fila As Long

Set cn = New ADODB.Connectioncn.Open dataSource

Set rs = New ADODB.Recordsetrs.Open tableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable' all records in a tablefila = 2 ' la primer fila en la hoja despues de los encabezamientos

Do While Len(Cells(fila, 1)) > 0With rs.AddNew.Fields("mes") = Range("A" & fila).Value.Fields("pais") = Range("B" & fila).Value.Fields("suma") = Range("C" & fila).Value.UpdateEnd Withfila = fila + 1 ' next rowLooprs.CloseSet rs = Nothingcn.CloseSet cn = NothingEnd 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 queremosconsolidar. A esta altura de los acontecimientos tenemos un archivo Access con un tabla que contiene los datos de lashojas (podemos ver que el tamaño del archivo se ha incrementado)

Ahora podemos crear la tabla dinámica usando la opción Fuente dedatos Externa

4

En el segundo paso creamos una nueva fuentede 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 seguimoslos 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 enforma 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 losnú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 ejemplodonde 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.

5

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 lohemos 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 comoGeneral (el formato por defecto de Excel). Si queremos evitar que Excel ponga el formato científico tenemos que dar unformato de número a la celda antes de ingresar el número. Si el número tiene menos de 15 cifras, podemos cambiar elformato 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” talcomo son; en la columna B vemos los mismos números introducidos en celdas previamente formadas con formatonumérico

Ahora veamos una “curiosidad” de Excel cuando se trata de númerosgrandes

En las celdas A1:A2 hemos puesto el número 123456789123 queExcel exhibe como 1,23457E+11. En la barra de fórmulas podemosver que sólo se trata de exhibición; Excel sigue “viendo” el númerotal como la introducimos en la celda. En el rango B1:B2 ponemos elmismo número pero previamente hemos formado las celdas comoNúmero.

Ahora usaremos el icono de aumentar decimales para agregar undecimal en las celdas A1 y A2.

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

B1:B2, es 123456800000!

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 (18cifras)

Sin embargo, el número que aparece en Excel después de apretarEnter 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. Estose 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 elformato 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 estosproblemas 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úmerode más de 15 cifras. Veamos qué pasa cuando introducimos el número 123456789123456789 (18 cifras) en una celda a lacual 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 necesitamosoperar 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 de4.1

6

Aparentemente el resulta es el correcto: 0.1. Veamos qué pasa cuandoformamos 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”, esdecir, 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 enblanco

La fórmula =SUMA(B2:B10) da como resultado 150. Es decir, las celdas en blanco sonconsideradas 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 elrango. =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 decero hay el rango, todo lo que tenemos que hacer es usar la función CONTAR.SI de estamanera

=CONTAR.SI(B2:B9,"<>0")

Esta fórmula da como resultado 9. Este resultado es un tanto extraño. Veamos qué pasa sicreamos una columna auxiliar con la fórmula =B2<>0 (que copiamos a los largo del rango)

Vemos que obtenemos cincoVERDADERO y cuatro FALSO. Es decir,

podríamos esperar que el resultado de CONTAR.SI en nuestro caso sea5, pero como vemos es 9.

En nuestro caso, es muy fácil percibir que el resultado no es elesperado, pero si analizamos un rango grande, digamos mil celdas, esmás que probable que aceptemos el resultado como correcto.

En caso de tener que contar condicionalmente números en un rango quecontiene o puede contener celdas en blanco, podemos usar alguna deestas soluciones:

# usar la función CONTAR.BLANCO para “corregir el resultado deCONTAR.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áneamenteCtrl+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 denú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úmerosmenores de 500 aparezcan en rojo y los restantes en negro. podemos usar este formatopersonalizado

[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

7

Otra solución es usar el número de índice delcolor en la paleta en lugar del nombreexplícito del color. De esta manera tenemostotal control del color de la fuente.

El único problema a resolver es saber cuál esel número de índice del color que queremosusar. Para averiguar el número de índice delcolor podemos usar la técnica que mostramoscon las macrofunciones de XLM o una macrocomo ésta:

Sub nr_color()Dim cell As Range, rngNrColor As Range

Set rngNrColor =Application.InputBox(prompt:="select range", Type:=8)

For Each cell In rngNrColorcell.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 yobtenemos 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 lassoluciones.

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)

8

si incluimos el rango de la matriz en el nombre “matriz”, usamos estafó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 segundovalor más frecuente, o el tercero, etc.

Excel no tiene una función nativa para estecálculo. Mi primera aproximación al problemafue usar columnas auxiliares (técnica quesiempre recomiendo ya que hace que losmodelos sean más legibles y fáciles decontrolar).

Creamos un rango auxiliar que contiene todoslos valores únicos que aparecen en la matriz (en nuestro caso, de 1 al 10) y paralelamenteun rango auxiliar que calcula la frecuencia decada valor

Vemos que, efectivamente, el 10 aparece 9 veces y elsegundo valor más frecuente es el 1 con 8 apariciones.

Incluimos el rango B14:B23 en el nombre “valores” y elrango C14:C23 en el nombre “frecuencia”. En la celda I5ponemos el número de orden de frecuencia buscado (2 porejemplo) 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.MAYORbusca el número más grande del rango“frecuencia” de acuerdo al parámetrointroducido en la celda I5; luego busca en quefila del rango se encuentra usando la funciónCOINCIDIR y este resultado es usado por lafunción INDICE para dar el valor adecuado delrango “valores”.

En esta solución hemos usado dos columnasauxiliares (“valores” y “frecuencia”).

El estimado Xisco propone una solución queuse sólo una columna auxiliar (o ninguna, silos números vienen ordenados en una sola filao columna). Dado que en nuestro ejemplo losnúmeros están dispuestos en una matriz,debemos transformarlos primero en unacolumna. Para esto podemos usar la técnicaque hemos mostrado en la nota sobre cómoconvertir datos de matriz a columna o fila enExcel.

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 unrango 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:

9

=INDICE(valores2,COINCIDIR(K.ESIMO.MAYOR(FRECUENCIA(valores2,valores2),I5),FRECUENCIA(valores2,valores2),0))

La bastante obvia pregunta es: ¿se puedehacer el mismo cálculo sin columnasauxiliares? Por supuesto, con una UDF(función definida por el usuario). Pero, ¿puedehacerse 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 Excelprobablemente no suene taninteresante, pero la herramientaque vamos a comentar esrealmente útil, porque la mayoríade archivos PDF que contienentablas difícilmente pueden sercopiados y pegados en una hojaexcel, como si sucede con word.

En la mayoría de los casos, alhacer un copiar y pegar, no serespetan las columnas, filas o elinicio y final de las tablas. Y sitenemos una tabla de datosrealmente compleja en PDF yqueremos pasarla a Excel, eltrabajo manual que tenemos querealizar para acomodar los datoses realmente incomodo.

Afortunadamente podemos usarPDF to Excel para crearnuestros archivos XLS, y podereditarlo en MS Excel, OpenOffice,Google Docs y WordPerfectOffice. Y lo mejor de todo, esque es gratis...

Algunos de los programas paraconvertir ficheros PDF a Excel quehay en internet no mantienen elaspecto de la tabla original. En elejemplo, que hemos visto arriba,PDF to Excel si mantiene el lookde la tabla, incluyendo tamaño defuentes y estilos, bordes, coloresde celda y espacio de filas ycolumnas.

Entonces, lo que debemos haceres lo siguiente:

1. Seleccionar el PDF aconvertir, escribirnuestro correoelectrónico y pulsar elbotón Convert

2. La aplicación seencargará de procesarel archivo (identificar las tablas,descartar el contenido adicional yempaquetar las tablas dentro de unarchivo XLS) y luego enviarlo poremail.

3. Verificar nuestro correo, que elarchivo xls nos llegará en minutos.

4. Y listo.

Enlace | PDF to Excel

sábado, agosto 29, 2009

10

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 intervalospor 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 reglageneral 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 lafunció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 oMULTIPLO.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 minuto16 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.SUPERIOR(MINUTO(A1),60),0))

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 AMEtiquetas: 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.

11

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.

Si usamos con frecuencia esta opción en Excel clásico podemos crear un atajo con una macro sencilla que luego podemosasociar 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 todocuaderno de Excel

Sub lineas_division()

If ActiveWindow.DisplayGridlines = True ThenActiveWindow.DisplayGridlines = FalseElseActiveWindow.DisplayGridlines = TrueEnd If

End Sub

El lugar adecuado para poner un icono ligado a la macro es la barra de herramientas de Formato. Para hacerlo seguimoslos 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 laopción Asignar Macro y elegimos la macro apropiada

12

3- Volvemos a abrir el menú contextual, ponemos un texto adecuado en Nombre y cambiamos la imagen del icono

13

A partir de ahora tenemos un nuevo icono en la barra de herramientas Formato que nos permite poner o quitar las líneasde 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 eleje 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

14

Como puede observarse aparecen en el gráfico de las X valores que no figuran explícitamente en los datos de origen (enla columna A). Esto se debe a que Excel reconoce los datos en la columna A como fechas. Si nos fijamos en lasdefiniciones 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 unidadprincipal es un mes.

Dado que las fechas en los datos no se distribuyen en intervalos regulares, no podemos crear el efecto deseado. Notenemos forma de ocultar las fechas para las cuales no hay datos. Para lograr el efecto deseado tendremos quetransformar los datos.

Empezamos por crear datos auxiliares en las columnas D y E

15

En la columna D ponemos todas fechas, día por día, comprendidas entre la primer y la última fecha. En la columna Eponemos 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áficopor 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 enlas 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

16

El resultado es que en los puntos en los que no hay valores, la fecha no aparece. Ahora podemos usar esta serie de datosen 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 decategorí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

17

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 alprincipio 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 lospuntos 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 deMínimo y Máximo

18

Abrimos el menú formato de serie de datos activamos la pestaña Barras de Error Y, elegimos la opción Ambas y en ValorFijo 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 tramapunteada para la línea y en Marcador la opción a la derecha

Con esto termina nuestra tarea

19

Technorati Tags: MS Excel

Compilado por Luar ArevTerminado el 18 de septiembre de 2009

20