Hasta ahora hemos visto como agrupar (y desagrupar) datos en una tabla dinámica en base a campos de fechas o texto. Existe también la posibilidad de agrupar de acuerdo a valores numéricos.
Volviendo a al ejemplo de las tiendas, supongamos que queremos agruparlas por el monto de ventas en intervalos de 150000, es decir, un grupo con tiendas con ventas de 0 a 149999, otro grupo de 150000 a 299999 y así sucesivamente.
Antes de agrupar nuestra tabla dinámica se ve así



Como sólo podemos agrupar y desagrupar por campos de filas o de columnas, agregamos el campo Ventas (que ya aparece en el área de datos) al área de filas



Seleccionamos el campo Ventas del área de filas (en nuestro ejemplo la celda B2) y abrimos el menú Agrupar y mostrar detalle-Agrupar



Como Excel a detectado que el campo contiene números (y éstos no son fechas) abre automáticamente el formulario Agrupar-Automático.
Excel detecta automáticamente los valores mínimo (Comenzar en) y máximo (Terminar en) del rango, los cuales pueden ser cambiados de ser necesario. En nuestro caso ponemos 0 como mínimo y 500000 como máximo; en la ventanilla Por del formulario ponemos 150000. Apretamos Aceptar



Ahora tenemos que arrastrar el campo Ventas del área de filas y ponerlo a la izquierda del campo Tienda. Al hacerlo obtenemos este resultado



Podemos obtener el mismo resultado creando un campo adicional en la base de datos, pero este método es más eficiente.

Technorati Tags: MS Excel

Ayer vimos como agrupar datos en tablas dinámicas de Excel basándonos en campos de texto.
Ahora veremos como agregar o quitar líneas a los campos agrupados. Excel no tiene un menú para editar campos agrupados. Podemos agrupar (como mostramos ayer) o desagrupar usando la opción Desagrupar del asistente de tablas dinámicas.
Hay situaciones en las cuales queremos agregar o quitar un miembro a un grupo ya existente. En esta nota mostraré las técnicas para hacerlo.
Volvamos sobre nuestro ejemplo de ayer donde hemos creado el grupo Norte



Si queremos agregar la Tienda 12 a este grupo empezamos por seleccionar todos los miembros del grupo y también Tienda 12 (apretando Ctrl mientras hacemos clic con el mouse)



Abrimos el menú Agrupar y mostrar detalle del asistente de tablas dinámicas y apretamos agrupar



Tienda 12 aparece ahora incluida en el grupo Norte



Nótese que el nombre del grupo ha cambiado al nombre por defecto, Grupo1.

Para quitar alguno de los miembros empezamos por seleccionar todos los miembros del grupo, menos aquel que queremos quitar (en nuestro ejemplo Tienda 19)



Ahora volvemos a usar Agrupar y mostrar detalle-Agrupar, lo que hará que Tienda 10 quede fuera del grupo



Podemos combinar ambas técnicas en una sola. Por ejemplo, si queremos quitar del grupo las tiendas 13 y 16 y agregar la tienda 2, seleccionamos todas las tiendas del grupo excepto 13 y 16 y seleccionamos también la tienda 2. Luego usamos Agrupar y mostrar detalle-Agrupar, obteniendo el resultado deseado en una sola operación.

Technorati Tags: MS Excel

En la nota Tablas Dinámicas en Excel – Agrupar datos vimos como agrupar datos en una tabla dinámica de Excel basándonos en un campo de fechas.
Pero esta funcionalidad no se limita sólo a fechas. Podemos agrupar datos basándonos también en campos de texto.
Supongamos, una vez más, esta tabla que muestra las ventas de cada tienda de una cadena



Empezamos creando la tabla dinámica



Nuestro objetivo es agrupar las tiendas por zonas: Norte, Sur, Este y Oeste. El problema es que la tabla de datos no incluye un campo que nos permita agrupar las tiendas por zonas. Una solución posible es agregar el campo, pero esto no es necesario.
Empezamos por seleccionar todas las tiendas que pertenecen a la zona Norte. No es necesario que el rango sea continuo de manera que después de seleccionar la primer tienda apretamos Ctrl al seleccionar las restantes



En nuestro caso hemos seleccionado las tiendas 1, 13, 16, 19 y 3 que corresponden a la zona Norte. En el asistente de Tablas Dinámicas abrimos el menú Agrupar y mostrar detalle--Agrupar.



Excel crea un nuevo campo de filas (Tienda2) y agrupa las tiendas que hemos seleccionado en Groupo1.



Cambiamos el nombre del grupo a Norte



Repetimos el proceso para el resto de las zonas



Ahora podemos cambiar el nombre del campo de Tienda2 a Zonas y arrastrar el campo Tienda fuera de la tabla



Un detalle a tomar en cuenta es que Excel crea el nuevo campo sin subtotales



Es recomendable cambiar la definición a Automático, de manera que cuando abrimos el detalle o agregamos el campo Tienda, podamos ver el total por zona

Technorati Tags: MS Excel

A la encuesta ¿Qué versión de Excel usas? han respondido 650 lectores y estos son los resultados



Vemos que 59% de mis lectores usan el Excel clásico (versiones 93 hasta 2003) y 41% usan el nuevo Excel (Excel2007).



Al lanzar la encuesta presuponía que la vasta mayoría de mis lectores no había adoptado la versión 2007 y seguían usando alguna de las versiones clásicas de Excel.
El sitio Exceluser de Charley Kyd hay una encuesta similar a la cual han respondido 1500 lectores. En esta encuesta se contemplaba también la posibilidad de usar ambas versiones de Excel (clásica y nueva). Los resultados son



Resultados ciertamente distintos, donde sólo el 22% de los lectores usan Excel 2007. Si eliminamos a los que usan ambas versiones, vemos que el 71% de los usuarios siguen con las versiones clásicas de Excel y un poco más de un cuarto, el 29%, se ha pasado a la nueva versión. También podemos considerar las respuestas “ambas versiones” como dos respuestas, y en ese caso vemos que el 68% usa las versiones clásicas y un 32% la nueva versión



Ambas encuestas coinciden en que las versiones clásicas de Excel siguen siendo las predominantes. Pero en lo que se refiere a Excel 2007, las respuestas de mis lectores tienden a mostrar que la nueva versión de Excel está ganando terreno y acercándose a la mitad del mercado mientras que las conclusiones de Exceluser son radicalmente distintas.
En una conclusión coincidimos: al instalar la nueva versión de Excel es recomendable dejar instalada la versión anterior.

Technorati Tags: MS Excel

Ha llegado el momento de desearles a mis lectores un feliz año nuevo y de presentarles las estadísticas del año que acaba de terminar.
Dice el conocido adagio que una imagen vale más que cien palabras, así que aquí pueden ver la cantidad de visitantes por mes, comparada con la del año anterior



y su respectiva tabla



Este blog ha crecido en relación al 2007 en un 82%, notándose que en los últimos meses la tasa de crecimiento es menor.
Es interesante ver estas estadísticas en forma continua a lo largo de los dos años:



Podemos ver claramente tres etapas:
# - de enero a mayo del 2007 el promedio de visitantes por mes es 9310
# - de junio del 2007 a marzo del 2008 el promedio de visitantes mensuales es 24110, un crecimiento cercano al 160%
# - de abril a diciembre de este año, con un promedio de 34657 visitantes por mes, lo que significa un crecimiento del 44% en relación a la etapa anterior
En cuanto al número de páginas vistas, los 387284 visitantes del 2008 vieron 814677 páginas, un promedio de 2.1 páginas vistas por visitantes. En el 2007 los 212275 visitantes consultaron 483518 páginas, 2.28 por visitante



Podemos ver que en las dos primeras etapas señaladas más arriba el promedio de páginas vistas por visita es 2.3, mientras que en la tercer etapa (abril-diciembre 2008), si bien el número de visitantes ha seguido creciendo, el promedio de páginas por visita es 2.04.
También el número de lectores que siguen el blog a través del feed ha ido creciendo. En el 2007 había llegado a 257 suscriptores. En el 2008 este blog cuenta con 803 suscriptores.




El balance del 2008 es positivo y el reconocimiento que recibo, casi diariamente, de mis lectores no sólo me reconforta sino que me impulsa a seguir adelante.
Durante el 2008 he seguido con mi tradición de no llevar a cabo todos los proyectos que me había propuesto. Lo que espero poder realizar en el 2009:
# - crear mi propio sitio que contendrá entre otras cosas los ejemplos de las notas para descargar y guías sobre temas de Excel
# - agregar animaciones a los ejemplos
# - seguir disfrutando de la tarea de publicar las notas en este blog y de las cálidas respuestas de mis lectores.

A todos, muchas gracias y mis mejores deseos para el año que comienza

Technorati Tags: MS Excel

Hagamos la siguiente prueba: en la celda A7 ponemos la fecha 01/01/1900 (el primero de enero del 1900); luego arrastramos el valor con el mouse hasta la celda A1. Supuestamente tendríamos que ver la siguiente sucesión:

A6 = 31/12/1899
A5 = 30/12/1899
A4 = 29/12/1899
A3 = 28/12/1899
A2 = 27/12/1899
A1 = 26/12/1899

Veamos que pasa en la práctica



Extraño, no? Hagamos otro experimento. En una celda ponemos esta fórmula

=FECHA(1899,12,31)

¿Cuál será el resultado? Sería de esperar 31/12/1899, sin embargo el resultado será 31/12/3799



Es decir Excel ha calculado el año en la función agregando 1900 al número 1899. Esto se debe a que Excel no sabe, o mejor dicho, no ha sido programado para calcular fechas anteriores al primero de enero del 1900. Como ya hemos explicado en más de una nota Excel usa una serie de números para las fechas donde el número 1 representa el 01/01/1900, el 2 la fecha 02/01/1900 y así sucesivamente. Es decir que si queremos calcular la edad de alguien nacido antes del 1900 o si queremos manejar una base de datos genealógicos con Excel tendremos que encontrar algún rodeo.

En esta nota veremos algunos rodeos conocidos para superar este problema.

La solución oficial de Microsoft es una UDF (user defined function – función definido por el usuario). Para usar la función (en realidad son dos funciones) deben copiar el código en la hoja del enlace y pegarlo a un módulo normal de Vba (preferentemente en el cuaderno Personal.xls).

La función tiene dos variables: fecha de comienzo (startdarte) y fecha final (enddate). Fechas anteriores a 01/01/1900 deben ser texto. Por ejemplo, si nuestro abuelo nació el 18/02/1877 y falleció el 25/04/1963, la función da como resultado 86.
El problema con esta macro es que funciona con la notación americana, mes/día/año. Si tratamos de usarlo con la notación corriente en la mayoría de los países, d+ia/mes/año, el resultado será “invalid date”.


Otra solución es descargar e instalar el complemento XDATE (eXtended Date) desarrollado por John Walkenbach. Una vez descargado e instalado, una serie de funciones son incorporadas al grupo de funciones de fechas del asistente de funciones



Ahora podemos volver al ejemplo de mi abuelito y utilizar XDATEDIF para calcular cuántos días vivió: 31476



Para calcular cuantos años usamos XDATEYEARDIF.



Este complemento agrega otras funciones como por ejemplo XDATEDOW que calcula el día de la fecha (1 = domingo).

Un problema similar existe con fechas posteriores al 31/12/9999. En otras palabras, para Excel el mundo fue creado el 1 de enero del 1900 (en la versión para McIntosh, en 1904) y tendrá su fin el 31 de diciembre del 10000.

Technorati Tags: MS Excel

Allá por el verano del 2006 (o el invierno, dependiendo de donde resida el lector) expliqué como crear un gráfico que se actualice automáticamente a medida que le agregamos datos.

La técnica consistía en usar rangos dinámicos en la función SERIES del gráfico. La ventaja de esta técnica es que es consistente con todas las versiones de Excel. Pero si usamos la versión 2003 o 2007 de Excel podemos usar una técnica mucho más sencilla.

Supongamos que tenemos esta tabla de ventas por mes a partir de la cual creamos un sencillo gráfico de columnas

Queremos que al agregar los próximos meses el gráfico se actualice automáticamente. Para hacerlo sacaremos provecho de la funcionalidad Datos--Listas.

Empezamos por seleccionar alguna de las celdas de la tabla, por ejemplo A1 y abrimos el menú Datos-Lista-Crear Lista



Excel selecciona automáticamente todo el área de la tabla



Después de controlar que la selección es la deseada, apretamos Aceptar. Excel ha creado ahora una lista y expandirá el área de la tabla automáticamente cada vez que agreguemos (o quitemos) una fila. Excel también abre la barra de herramientas de listas donde tenemos todo tipo de herramientas para administrarla



Para agregar una fila a la lista seleccionamos alguna celda del área. Veremos que aparece un marco azul alrededor del área de la lista y una estrella azul en la primer celda libre, donde debemos poner los datos



Todo los que nos queda por hacer es agregar los datos y el gráfico se actualizará automáticamente



En Excel 2007 esta técnica funciona de la misma manera pero con, como no podía ser de otra manera, algunas diferencias funcionales:

# - La funcionalidad no se llama Lista sino Tablas y no se encuentra en la pestaña Datos como en Excel 2003 sino en la pestaña Insertar



# - Al seleccionar alguna celda de la lista/tabla no aparece una nueva línea en blanco en la tabla. De todas maneras si agregamos un mes inmediatamente debajo del último registro de la tabla, Excel expande la lista en forma automática. Otra técnica en Excel 2007 es usar la tecla Tab. Por ejemplo, si después de introducir el dato de setiembre en la celda B10 apretamos TAB, Excel selecciona automáticamente la celda A11 y expande la tabla.

Microsoft ha agregado muchas otras herramientas y funcionalidades a las tablas en Excel 2007, pero esto será tema de una futura nota.

Technorati Tags: MS Excel

Hace ya más de dos años publiqué la nota sobre como insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX. Eran los días previos a la aparición de Excel 2007 y los controles estaban ligados a dos barras de herramientas, Formularios y Cuadros de Controles (controles ActiveX).
En Excel 2007 no hay barras de herramientas y los controles están ubicados en la pestaña Programador de la cinta de opciones

Nótese que todas las herramientas necesarias están a nuestra vista en la misma sección de la pestaña (Modo diseño, Propiedades, etc.).

Como pueden ver, al apretar Insertar vemos de hecho las mismas barras de herramientas a las que estábamos acostumbrados en las versiones anteriores de Excel.

También el modelo de validación de fechas programando un evento que muestre el calendario si se cumplen ciertas condiciones podemos usarlo en Excel 2007. Si bien la interfaz del editor de Visual Basic no ha cambiado algunos lectores me comentan que no encuentran el botón de controles adicionales. Al igual que en las versiones anteriores podemos usar el menú Herramientas-Controles Adicionales o hacer un clic con el botón derecho del mouse en cuadro de herramientas del UserForm

También podemos crear un calendario permanente en una hoja de Excel sin usar controles. Esto puede hacerse con fórmulas matriciales, como en este modelo desarrollado por John Walkenbach



Este modelo usa fórmulas matriciales de rango, es decir una fórmula que da el resultado en varias celdas simultáneamente. Para crear este modelo empezamos por introducir en la celda B2 el mes y el año del calendario. Podemos hacer esto poniendo en B2 la fórmula =HOY() y dando un formato “mmmm,aaaa” a la celda. Luego seleccionamos el rango B4:H9 asegurándonos que la celda activa sea B4, introducimos esta fórmula en la celda activa

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1);"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1)

y apretamos simultáneamente Ctrl+Mayúsculas+Enter

Podemos usar una fórmula más sencilla,

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-1)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

pero que mostrará las fechas en todas las casillas del rectángulo

Como ven, estos calendarios son “gringos” con la semana comenzando en domingo. Si queremos que la primer columna de la semana sea el lunes, modificamos levemente la fórmula

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7});"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7})

O esta fórmula

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-2)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

para el modelo sencillo

Technorati Tags: MS Excel

En el pasado ya he mencionado la barra de auditoría de fórmulas. Vimos, por ejemplo, como localizar valores repetidos en una tabla o como analizar el funcionamiento de una fórmula.
Otra herramienta útil y poco conocida en esta barra es la ventana de inspección.

En Excel 2007 la ventana de inspección se encuentra en la pestaña de Fórmulas



El uso de esta ventana es muy sencillo. Supongamos un modelo con el cual calculamos descuentos en función de la cantidad. En la Hoja1 calculamos los descuentos



Como pueden ver, el descuento se calcula dinámicamente en base a una tabla de descuentos que se encuentra en la Hoja2



Si queremos investigar como influyen las distintas tasas de descuentos al resultado, tenemos que navegar a la Hoja2, cambiar las tasas, y luego volver a la Hoja1 para ver el resultado.

Una alternativa es crear referencias a las celdas de la Hoja1 en la Hoja2. Una alternativa más elegante y eficiente es usar la ventana de inspección.

En nuestro caso vamos a la Hoja1 y abrimos la ventana de inspección apretando el icono en la barra de auditoría de fórmulas

Ahora seleccionamos las celdas que queremos inspeccionar, por ejemplo B3, B4 y B5 y apretamos "agregar inspección"



Apretamos agregar. Las celdas aparecerán en la ventana.

Podemos adaptar la ventana a nuestras necesidades ocultando campos que no nos interesan y ampliando el ancho de campos relevantes. Todo esto lo hacemos arrastrando los límites del campo con el mouse.



Navegamos a la Hoja2 y vemos que la ventana sigue flotando sobre la hoja



Todo cambio que ocurra en las celdas de la ventana de inspección se reflejará inmediatamente en la ventana.

También podemos usar esta funcionalidad con celdas en hojas de otros cuadernos.

Si las celdas a inspeccionar están definidas en nombres podemos hacer que éstos aparezcan en la ventana de inspección, facilitando de esta manera la lectura de los resultados

Technorati Tags: MS Excel

Cuando queremos que una macro esté disponible para todo cuaderno abierto en una sesión de Excel, el método más directo de hacerlo es guardar el código en el cuaderno Personal.xls. En el pasado he mostrado como crear el cuaderno Personal.xls cuando este no existe.
En Excel 2007 el proceso es similar con algunas pequeñas diferencias funcionales que mostraré en esta nota.

Como en las versiones anteriores a Excel 2007, si el cuaderno Personal.xls (en Excel 2007 el cuaderno es Personal .xlsb) no existe podemos crearlo grabando una macro y ligándola a este cuaderno. Para comenzar a grabar una macro en Excel 2007 activamos la pestaña Programador y apretamos el icono de grabar




También podemos usar el icono de grabar macros que se encuentra en el ángulo inferior izquierdo de la hoja



Como en Excel 2003 (y versiones anteriores) elegimos la opción de guardar la macro en el libro de macros personal



Para saber dónde está guardado el cuaderno, o si existe, podemos hacer una búsqueda en las carpetas del computador o usar esta técnica:

1 – Abrimos el editor de Vb (con Alt+F11 o con Visual Basic de la cinta de opciones)
2 – Agregamos la ventana Inmediate con Ctrl+G o View—Inmediate Window
3 – En la ventana Inmediate ponemos este código y apretamos Enter: ?Application.StartupPath
4 – SI el resultado es una fila en blanco, el cuaderno no existe. En ese caso empezamos la grabación, como indiqué más arriba y la cerramos inmediatamente (no hay necesidad de grabar alguna acción).



5 – Ahora que hemos creado el cuaderno, podemos ver dónde Excel lo ha guardado

Technorati Tags: MS Excel

En Excel existen varios métodos para activar una macro. Podemos asignarle una combinación de teclas, podemos ligar la macro a un objeto y en las versiones anteriores a Excel 2007 podíamos ligarlo a un icono en alguna barra de herramientas (o crear una barra de herramientas con el icono) e inclusive incluirla en un menú.

En Excel 2007 podemos, como en las versiones anteriores, ligarla la macro a objetos o asignarle una combinación de teclas. Pero no tenemos barras de herramientas donde poner el icono o menús donde incluir la macro.

Sin embargo tenemos la alternativa de poner un icono ligado a la macro en la barra de acceso rápido.

Supongamos que queremos poner un icono para correr una macro que agrega hojas a un cuaderno a partir de los valores de una lista. Los pasos son los siguientes (la macro tiene que estar en un cuaderno abierto o en Personal .xls):

1 – abrimos el menú de Opciones de Excel – Personalizar (o apretando la flecha en el extremo derecho de la barra de acceso rápido). En la ventanilla ”Comandos disponibles en” elegimos Macros




2 – En la ventanilla inferior veremos una lista de macros disponibles, ordenadas alfabéticamente



En caso de no ver el nombre completo, podemos apuntar con el señalador del mouse y después de unos instantes veremos el nombre completo de la macro. Seleccionamos la macro y apretamos el botón “Agregar”



3 – Para cambiar el icono que Excel pone por defecto seleccionamos la macro que acabamos de agregar y apretamos el botón “Modificar”



Elegimos un icono adecuado. En la ventanilla “Nombre para mostrar” cambiamos el texto que Excel a puesto por defecto (el nombre de la macro) por un texto descriptivo. Finalmente apretamos el botón “Aceptar”.

Excel agrega el icono a la barra de acceso rápido y al apuntar con el señalador del mouse podemos ver la descripción de la acción que ejecuta la macro.



Si tenemos una varias macros que usamos con frecuencia, podemos agregarlas a la barra de acceso rápido con facilidad.

Technorati Tags: MS Excel

En las versiones anteriores a Excel 2007 era relativamente fácil personalizar la interfaz del programa. Podíamos crear barras de herramientas personales, agregar iconos a barra existentes e inclusive modificar los elementos de los menús. Las barras de herramientas podías ser flotantes, es decir, podían ser ubicadas en cualquier área de la hoja y también movidas a discreción.

Toda, o casi toda, esta flexibilidad ha desaparecido. Pero los programadores de Excel han dejado cierto margen de maniobras al usuario promedio (los usuarios avanzados pueden modificar la cinta de opciones, lo que dista de ser trivial). Para esto han agregado la barra de herramientas de acceso rápido

Se puede personalizar la barra de acceso rápido agregando aquellos comandos que usamos frecuentemente.

Para agregar comandos apretamos la flecha de personalización de la barra

Al hacerlo se abre un menú con las distintas opciones.



La parte superior del formulario muestra los comandos más populares (según Microsoft). Si queremos agregar el icono Abrir, hacemos un clic sobre el ítem



Si apretamos la opción Más comandos veremos todas las otras alternativas. En esta ventana tenemos varias alternativas.



Empezamos por elegir la colección de comandos de la cual queremos agregar el icono a la barra apretando al flecha en la ventanilla Comandos disponibles en:



Luego elegimos el icono en la ventanilla inferior. Podemos hacer un clic apretar el botón Agregar o hacer un doble clic sobre el nombre del comando.

Para quitar un comando lo elegimos en la ventanilla derecho y apretamos el botón Quitar. Una vez que hemos terminado de agregar y quitar los comandos, apretamos el botón Aceptar.
La barra de acceso rápido sólo muestra los iconos, pero si posamos el señalador del mouse unos segundos se abre una nota con una descripción del comando

Si queremos quitar algún icono de la barra podemos también señalarlo con el mouse y hacer un clic con el botón derecho. En el menú que se abre elegimos la opción eliminar de la barra

Finalmente, para eliminar todos los cambios que hayamos introducido en la barra de acceso rápido, apretamos el botón Restablecer en el menú de opciones.

Technorati Tags: MS Excel

En Office 2007 los menús e iconos de las versiones anteriores han sido reemplazados por la cinta de opciones. Algunos de los métodos abreviados (atajos de teclado) han desaparecido o han sido reemplazados por otras combinaciones.

A quien esté acostumbrado a los métodos de Excel 2003 (o versiones anteriores) y empiece a trabajar con Excel 2007, le resultará útil leer esta serie de instrucciones de esta introducción a la cinta de opciones.

Al hablar de métodos abreviados (o atajos) debemos distinguir entre métodos de acceso a la cinta de opciones y combinaciones de teclas.

Los métodos de acceso nos permiten operar con la cinta de opciones, como antes lo hacíamos con la barra de menús, desde el teclado. Para acceder a la cinta de opciones sin el mouse todo lo que tenemos que hacer es apretar la tecla ALT. Una de las mejoras en Office 2007 es que cuando apretamos ALT para acceder a la cinta de opciones aparecen en cada elemento de ella la tecla que debemos apretar para acceder a la opción.

Antes de apretar ALT la cinta de opciones aparece así

Al apretar TAB veremos



Al lado de cada elemento de la cinta de opciones han aparecido las letras del teclado que debemos apretar para realizar la acción.

Al apretar alguna de las teclas de opción, veremos las teclas opcionales de los elementos de la pestaña elegida.




Otra alternativa de navegación en la cinta de opciones sin usar el mouse es apretar la tecla TAB y luego usar las flechas del teclado y la tecla TAB para desplazarse.

Cuando usamos una combinación de teclas no activamos la cinta de opciones sino que aplicamos la acción directamente. Por ejemplo, como en las versiones anteriores, para aplicar el formato de negrita a una celda basta con apretar Ctrl+N (o Ctrl+2). En esta página de Microsoft pueden ver todas las combinaciones de teclas de Excel 2007.

Technorati Tags: MS Excel

En esta página de Microsoft pueden encontrar vínculos a guías interactivas para ubicar los comandos de las distintas aplicaciones del Office 2003 en el Office 2007.

En la misma hoja encontrar vínculos para descargar las guías, lo que permite usarlas también cuando no estamos conectados a la Internet.

Technorati Tags: MS Excel

Una de las funcionalidades que más han mejorado en Excel 2007 es el formato condicional. Entre las mejoras mencionaremos:

# - no hay límite al número de reglas que se pueden definir. Anteriormente sólo era posible definir tres condiciones.

# - en las versiones anteriores no era posible usar referencias a celdas de hojas remotas al definir las condiciones (en esta nota vimos como se podía superar ese inconveniente). En Excel 2007 no existe esta restricción.

# - En Excel 2007 se puede condicionar el formato de números.

# - Además de fondos y bordes también se pueden aplicar iconos como flechas, puntos de color y barras de color. Estas últimas permiten generar gráficos "instant" basados en valores de celdas.

# - Se puede aplicar más de un formato condicional a una misma celda. Por ejemplo, si una regla pone un fondo de color y cambia el tamaño de la fuente, al cumplirse ambas condiciones los dos formatos serán aplicados. En las versiones anteriores sólo la primer condición se aplicaba.


La posibilidad de usar iconos y barras es particularmente atractivo por los efectos que se pueden lograr. Supongamos que tenemos esta tabla de ventas




Si queremos poner resaltar con flechas de color aquellos departamentos que han vendido más de 30000 (superaron el plan), los que están entre 15000 y 30000 (cumplen el plan) y aquellos que no han superado los 15000, seleccionamos el rango de las celdas a formar, en la pestaña Inicio de la cinta apretamos Formato condicional-Nueva Regla





El resultado



Es de notar que sólo definimos dos valores, 30000 y 15000 y Excel define los límites de las tres condiciones mostrándolas en la parte izquierda del formulario.

También podemos usar barras de color para dar una idea gráfica del tamaño relativo de cada número. Seleccionamos el rango de número y aplicamos Formato Condicional - Barra de datos. Al señalar alguna de las opciones Excel nos muestra como se verán las barras en el rango formado



La barra es aplicada como fondo a la celda que contiene el valor. Podemos mejorar el efecto visual de esta opción aumentado el ancho de las columnas creando así este este efecto



Una opción mejor es crear una tercera columna con una referencia las celdas y usar la opción "mostrar sólo la barra"

Technorati Tags: MS Excel

Hasta hoy no he tratado temas relacionados con Excel 2007 a pesar de tenerlo instalado ya hace algunos meses. Esto se debe, principalmente, a dos motivos:

1 - en mis tareas cotidianas sigo usando el Excel 2003 (y llevará un buen tiempo hasta que en mi empresa se decidan a pasar al Office 2007);

2 - era mi impresión que la gran mayoría de mis lectores seguían usando las versiones anteriores de Excel, en particular XL2003.

Tal vez tendría que confesar que aún recuerdo los "dolores de parto" del pasaje de una versión a otra, en particular de Excel 5 al Excel 97.

Pero en este caso la situación parece ser distinta. La encuesta que llevo adelante en el blog, la cual ha recibido hasta ahora 347 respuestas, muestra esta situación

Personalmente no creo que el pasaje de Excel 2003 a Excel 2007 sea difícil o requiera un tiempo prolongado de aprendizaje. Sin lugar a dudas hay que acostumbrarse a la cinta de opciones en lugar de los menúes de las versiones anteriores. Además la barra de acceso rápido donde se pueden poner los iconos de las funcionalidades que usamos frecuentemente.

En general se ha conservado la agrupación lógica de los comandos y se han agregado varios que encuentro muy útiles, como la opción "quitar duplicados" en el menú Datos

En esta página de Microsoft hay una guía interactiva para ubicar los comandos del Excel 2003 en Excel 2007 (requiere tener instalado el Adobe Flas Player 7.0 o posterior). Una vez puesta a correr, la guía presenta una hoja con los menúes de Excel 2003. Al apretar un comando, aparece un comentario con la descripción de la ubicación del comando en el Excel 2007



Un segundo clic muestra la cinta de opciones de Excel 2007 señalando el comando con un recuadro



Dado que cerca de la mitad de mis lectores usa ya el Excel 2007, no entraré a detallar en esta nota las muchas diferencias entre ésta versión y su anterior. Pero si señalaré algunas diferencias, algunas obvias, otras menos, como:

# - el número de filas ha aumentado de 65,536 a 1,048,576

# - el número de columnas a crecido de 256 a 16,384

# - el número de colores de 56 a 4.3 miles de millones (billones)

# - no hay límite al número de formatos condicionales por celda (anteriormente sólo 3 condiciones eran posibles)

# - la lista desplegable de Autofiltro muestra ahora hasta 10000 valores de la columna en lugar de los 1000 en las versiones anteriores

# - es posible anidar hasta 64 niveles de funciones en una fórmula en lugar de los 7 niveles permitidos anteriormente

# - el número máximo de argumentos en una función es ahora 255 en lugar de 30

# - funciones que antes estaban disponibles sólo después de instalar el Analysis ToolPak son ahora parte integral de la colección de funciones de Excel

# - el aspecto de los gráficos ha mejorado notablemente (para mi gusta, por supuesto).

En las próximas notas encararemos cuestiones como el uso de las nuevas funciones y la ampliación del formato condicional y más.

Technorati Tags: MS Excel

Un lector me señala que he olvidado de incluir la versión 2002 de Excel en la encuesta que aparece en la columna izquierda del blog ("¿Qué versión de Excel usas?).

Efectivamente, mea culpa. Quien esté usando esa versión de Excel y quiera votar tendrá que votar por la versión 2000.

¿Cuáles son las versiones de Excel? La primera versión de Excel, 1.0, fue diseñada para Apple Macintosh en 1985. La primer versión para Windows apareció en 1987 como versión 2.0.

Las siguientes versiones para Windows fueron:

# - Versión 3.0 de 1990, que incluía muchos avances en relación a la versión anterior como barras de herramientas, agrupamiento de datos (outlining), complementos y más.

# - Versión 4.0 que apareció en 1992.

# - Versión 5.0 de 1994. En esta versión fue reemplazado el lenguaje macro XLM por el Vba (Visual Basic for Applications).

# - La versión 7.0 apareció en 1995 y es conocida como Excel 95. No existe una versión 6.0 de Excel y esto parece deberse a que Excel 95 forma parte del paquete Office 95 que incluía la versión 7 de Word.

# - Excel 97 (la versión 8) apareció en 1997 como parte del paquete Office 97. Esta versión introdujo cambios muy significativos tanto en la interfaz del usuario como en el número de filas disponibles en cada hoja.

# - En 1999 apareció la versión 9.0 de Excel bajo el nombre Excel 2000.

# - En el 2001 aparece la versión 10.0, Excel 2002, como parte del paquete Office XP.

# - La versión 11.0 de Excel (Excel 2003) aparece en el paquete Office 2003.

# - Finalmente en el 2007 aparece la versión 12.0, Excel 2007 como parte del Office 2007.

Technorati Tags: MS Excel

Si se fijan en la columna izquierda del blog, debajo de la casilla de búsqueda, podrán ver que he agregado una encuesta en línea: "¿Qué versión de Excel usas?".



En mi trabajo cotidiano uso Excel 2003. Supongo que también la mayoría de mis lectores, pero para despejar mis dudas he puesto la encuesta. Todo lo que hay que hacer es señalar una de las opciones y apretar el botón de votar.

En los próximos días empezaré a publicar algunas notas sobre Excel 2007.

Technorati Tags: MS Excel

Hemos visto diversos usos de la función RESIDUO en varias notas de este blog. Por ejemplo, para superar el problema de diferencias negativas en cálculos de horas o para generar fondos para facilitar la lectura de tablas.

En esta nota veremos algunos aspectos del uso de esta función. Empecemos por su definición. Según la ayuda en línea de Excel:

RESIDUO proporciona el residuo después de dividir un número por un divisor

Es decir RESIDUO(3,2) = 1

Si investigamos un poco más la ayuda de Excel vemos que esta función equivale a la expresión

n - d*ENTERO(n/d)

donde "n" es el número a dividir y "d" es el divisor

Veamos cuál será el residuo de dividir 30 por 4, considerando que ambos números pueden ser positivos o negativos:





Vemos que en todos los casos el resultado es el mismo, excepto por el signo que es el del divisor.

Pero si calculamos el residuo de 34 dividido por 6 vemos estos resultados



Estos resultados se deben a que el residuo se define como la diferencia entre el múltiplo entero más grande del divisor que no supera al dividendo. En el caso que estamos considerando, 30 es el múltiplo más grande (6 * 5) que no supera al dividendo (34) y la diferencia entre ellos es 4.
Pero cuando consideramos un dividendo negativo, como -34, el mayor múltiplo de 6 que no supera a -34 es -36 (6 * -6) y la diferencia es obviamente 2.

En Visual Basic existe el operador MOD cuya función es la misma que la de la función RESIDUO. Sin embargo el operador no siempre da resultados idénticos a los de la función RESIDUO.
MOD es usado como operador, es decir, para encontrar el residuo de 34 dividido por 6 escribimos en un módulo de Vba

34 Mod 6

Podemos usar la ventana Inmediate del editor de Vba para ver los resultados



Como pueden observar los resultados son inconsistentes. Para hacerlo más evidente podemos crear una función UDF (definida por el usuario) y usarla luego en la hoja para compara los resultados

Function residuo_vba(n As Double, d As Double)
residuo_vba = n Mod d
End Function


En esta tabla se puede ver que los resultados de las dos funciones son inconsistentes



Sin entrar a considerar cuál de los resultados es el correcto, tema que dejo librado a los matemáticos, podemos usar esta fórmula para obtener con funciones los mismos resultados como con el operador MOD de VBa

número - divisor *COCIENTE(número, divisor)

como podemos ver en esta tabla



Para usar la función COCIENTE (QUOTIENT en inglés) hay que tener instalado el complemento Analysis ToolPak.

Technorati Tags: MS Excel

Un lector dejó un comentario interesante en la nota Algo más sobre la función REDONDEAR tocando el tema de redondeo de sumas en las tiendas y supermercados. Decidí no publicar la nota ya que no estaba relacionada directamente con algún aspecto técnico de la nota y además el lector decidió quedar en el anonimato.

Nuestro anónimo lector comentaba sobre una cadena de supermercados que proponía a los clientes redondear los centavos y donar la diferencia a una institución de bien público. Por ejemplo, el monto de la compra era 99 pesos con 98 centavos, el cajero proponía al cliente redondear la suma a 100 pesos donando dos centavos. Esto parece loable, sólo que la donación era hecha a nombre de la empresa y no del cliente quien es el donante real. Otro aspecto interesante era que la cadena de supermercados podía descontar las donaciones de los impuestos generando de esta manera ganancias a cuenta del dinero de los clientes.

¿Cuál es el método de redondeo en Excel? Pues bien, hay varios y además existen diferencias entre el redondeo en Excel y en Vba, el lenguaje macro de Excel.
En la nota anterior mostrábamos que podíamos usar un redondeo negativo como

REDONDEAR(123,45;-2) = 100

El equivalente en Vba sería usar la función Round, pero esto genera un error

Sí podemos usar la función de Excel en Vba usando la propiedad WorksheetFunction de esta manera



El método común de redondeo (o simétrico o Round-Half-Up, de acuerdo a Wikipedia) que funciona de la siguiente manera:

1 - decidimos cuantos decimales queremos dejar;
2 - si el digito siguiente al que dejamos es mayor o igual a 5, redondeamos "hacia arriba" (por ejemplo, 123.45 redondeado a un decimal será 123.5);
3 - si el dígito siguiente es menor de 5 redondeamos "hacia abajo" (123.44 será 123.4).

Este es el método que usa Excel con la función redondear. La función TRUNCAR, en cambio, se limita a "cercenar" los dígitos no requeridos.

Existe un método de redondeo llamada, en inglés, Banker's rounding (o round to even, unbiased rounding, convergent rounding, statistician's rounding, Dutch rounding, Gaussian rounding, siempre de acuerdo a Wikipedia) y me disculparan el inglés pero no he encontrado nada sobre el tema en castellano.

Este método consiste en redondear siempre hacia el número par más cercano. Por ejemplo, 1.5 y 2.5 se redondean como enteros a 2; 3.5 y 4.5 se redondean a 4. La lógica de este método es minimizar el desvío que se produce al redondear siempre 0.5 "hacia arriba".
En este ejemplo

he usado la fórmula =REDONDEAR(A1,0) en la columna B. En las celdas A5 y B5 he usado la función SUMA. Como pueden ver el resultado es problemático. No existe ninguna función nativa de Excel que funcione con el método de Banker's rounding.

Pero en Vba podemos usar estas funciones para este tipo de redondeo: CByte(), CInt(), CLng(), CCur() y Round().
Por ejemplo, podemos escribir esta funcion UDF (definida por el usuario) para redondear a enteros

Function bankers_round(monto As Double)
bankers_round = CInt(monto)
End Function


Si aplicamos esta función para redondear a enteros, obtenemos estos resultados




Por supuesto, también este método puede generar desvíos. Podríamos pensar en un método de redondeo aleatorio, pero este método podría dar resultados diferentes para un mismo grupo de datos. Es decir, puede ser inconsistente.

Quien esté interesado en profundizar en el tema puede ver esta nota en la base de conocimientos de Microsoft..

Technorati Tags: MS Excel