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:
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
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 (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.
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:
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 AsLong
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
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.
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
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á.
Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.
Una de las mejores y más útiles funcionalidades de Excel son las tablas dinámicas. Si bien podemos generar tablas dinámicas basándonos en datos externos, por lo general los datos estarán en una de las hojas del cuaderno que contiene la tabla dinámica.
Si queremos distribuir nuestro archivo y queremos reducir su tamaño podemos sencillamente eliminar la hoja que contiene los datos. Esto sólo afectará la posibilidad de agregar o borrar datos de la base de datos, pero todas las otras funcionalidades seguirán vigentes.
Para demostrarlo construimos una base de datos de las ventas de una empresa imaginaria. Esta base de datos contiene las ventas de todos los días desde el 1ro. de enero de 1970 hasta el 30 de junio de 2009 (14423 días/líneas en la tabla). El tamaño del archivo, antes de construir la tabla dinámica es 1.2 MB.
El tamaño de nuestro archivo ha crecido y es ahora 1.63 MB.
Ahora borramos la hoja que contiene los datos y guardamos el cuaderno. El tamaño de nuestro archivo se ha reducido dramáticamente a 455 KB
La funcionalidad de la tabla dinámica sigue intacta. Por ejemplo, podemos arrastrar el campo de los trimestres del área de columnas al área de filas
A pesar de haber borrado los datos en que se basa la tabla dinámica, éstos no se han perdido. Para recuperarlos todos lo que tenemos que hacer es un doble clic en la celda de la tabla dinámica que contiene el total general
Esta acción genera una nueva hoja con todos los datos tal como aparecían en la hoja que borramos previamente.
Esto es posible dado que cuando creamos una tabla dinámica, Excel “fotografía” todos los datos y los guarda en una memoria especial llamada Cache. Este método hace que las tablas dinámicas sean tan eficientes en la velocidad de cálculo.
Un lector me consulta cómo crear un reporte que muestre cuántos de los artículos de un inventario tienen una antigüedad de hasta 3 meses, cuántos entre 3 y 6 meses, cuántos entre 6 y 9 y así sucesivamente.
Hay varias formas de hacer esto, pero cuando tenemos que enfrentarnos con listas de muchos artículos, digamos centenas o miles, la herramienta más eficiente son las tablas dinámicas.
De hecho vamos a mostrar cómo crear una tabla de frecuencias usando tablas dinámicas. EN nuestro caso lo aplicaremos al ejemplo del inventario.
Nuestro inventario se ve así
EL primer paso es agregar un campo (columna) donde calculamos la antigüedad de cada artículo en relación a la fecha corriente. Usamos la función SIFECHA de esta manera
=SIFECHA(C2,HOY(),"m")
Luego construimos una tabla dinámica, preferentemente en una hoja separada.
Ahora arrastramos el campo Antigüedad al área de campos de fila (si, a pesar de que Antigüedad es un campo de datos numéricos)
Luego arrastramos los campos Artículo y Descripción al área de filas.
El campo Artículo lo volvemos a arrastrar pero esta vez al área de datos. Inmediatamente después quitamos los subtotales de todos los campos.
Nuestra tabla dinámica debe verse así
Seleccionamos la celda A4 (Antigüedad) y apretamos el icono Ocultar detalle del asistente de Tablas Dinámicas
El resultado es
Ahora usamos Agrupar para crear los intervalos. En el formulario de agrupar ponemos 3 (o el tamaño de intervalo deseado) en la casilla Por
Volvemos a elegir la celda A4 (Antigüedad) y nuevamente apretamos Ocultar Detalle.
El resultado es una tabla dinámica agrupada por intervalos de 3 meses, que totaliza el total de artículos en cada intervalo (0-2 meses, 3-5 meses, etc).
Algunas observaciones:
# - Si queremos que el primer intervalo sea 1-3, deberemos modificar levemente la fórmula que calcula la antigüedad
=SIFECHA(C2,HOY(),"m")+1
Una vez modificada la fórmula, actualizamos la tabla dinámica
# - La función HOY() que usamos en la fórmula de antigüedad es volátil, lo cual puede influir negatívamente en el tiempo de recálculo en hojas con muchos datos. En lugar de HOY() podemos poner la fecha en relación a la cual queremos calcular la antigüedad en una celda fuera del rango de la lista o en un nombre.
