Consolidar Datos en Excel con Subtotales

En una nota anterior explicaba cómo consolidar datos usando el menú Datos--->Consolidar. Esta funcionalidad es conveniente cuando queremos consolidar datos que se encuentran en varias hojas.
Otro aspecto de la consolidación de datos es el uso del menú Subtotales. Supongamos que tenemos en una hoja los datos de ventas por productos, zonas y mes del año.

El menú Subtotales nos permite agrupar los totales, por ejemplo, por producto. Para poder utilizar esta herramienta, debemos ordenar los datos del campo correspondiente en orden ascendente o descendente.
Una vez hecho esto, abrimos el diálogo de Subtotales



Elegimos "producto", "suma" y "Ventas" en las opciones correspondientes y pulsamos "Aceptar"

Los botones nos permiten presentar el informe en distintos grados de resumen. Si pulsamos el botón 2 veremos solamente las líneas de totales por producto.



En este ejemplo vemos que tenemos 3 grados de resumen. Qué pasa si queremos agregar un cuarto grado, por ejemplo, subtotales por área.
Para hacer esto tenemos que asegurarnos que todos los campos relevantes estén ordenados en forma ascendente o descendente.
Para reordenar los campos primero debemos cancelar los subtotales. Esto se hace en el mismo diálogo



Luego abrimos el menú de ordenar



y pulsamos "aceptar"


Ahora producimos los subtotales para productos, como en el primer paso, y luego volvemos a abrir el menú para producir subtotales por área. Pero esta vez quitamos la marca de la opción "reemplazar subtotales actuales"



Ahora veremos 4 niveles de resumen



Puede verse que Excel agrega una segunda e innecesaria línea de Total. Esta línea puede ser borrada.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: Subtotales, Consolidar Datos

Categorías: Manejo de Datos_

Importar archivos TXT o CSV

El fin de semana se anuncia lluvioso. Así que trataré de aprovecharlo para ir completando el contenido de este blog con las notas publicadas en el blog en inglés.

Una tarea bastante corriente para muchos de los usuarios de Excel es importar archivos originados del sistema central a Excel. Por lo general los sistemas centrales (como los sistemas ERP) tienen la capacidad de transformar los informes que producen a archivos textuales (con terminación ".txt" o ".csv").
Abrir estos informes en hojas de Excel nos permite analizar los datos con mayor flexibilidad y también darles una presentación más elaborada.
Importar archivos de este tipo de Excel es sencillo. Para hacerlo existen dos métodos que a primera vista parecen idénticos, pero que tienen entre si diferencias importantes.

Los dos métodos son:

1 - Archivo ---> Abrir (de la misma manera que abrimos cualquier archivo .xls)
2 - Datos ---> Obtener Datos Externos ---> Importar datos

La diferencia entre ambos métodos es que cuando usamos Archivo ---> Abrir, Excel importa los datos a una nueva hoja. Cuando usamos Datos ---> Obtener Datos Externos, tenemos la posibilidad de ubicar los datos en cualquier lugar de una hoja existente o en una nueva hoja.

Supongamos que tenemos el siguiente archivo de tipo txt, con datos que queremos importar a Excel:



No importa que método usemos, Excel abre un diálogo



En el primer paso del diálogo le decimos a Excel como dividir los campos (columnas), en nuestro caso elegimos "ancho fijo"



En el segundo paso vemos como Excel propone dividir los campos. Si la división no es la deseada, podemos cambiarla



En el tercer, y último paso, podemos establecer el tipo de datos en el campo. En nuestro caso, cambiaremos el tipo de dato del campo "Fecha" de "general" a "fecha".
Pulsamos "Aceptar" y se abrirá un nuevo cuadernos ventas.txt.



En caso de usar el método Datos ---> Obtener Datos Externos ---> Importar datos, se abrirá el mismo diálogo. Al pulsar "aceptar" se abrirá un nuevo diálogo dónde podemos decidir la ubicación de los datos



Pero la ventaja, y la diferencia más importante con el primer método, es que Excel registra todos los parámetros de la importación. Esto significa que Excel mantiene una especie de conexión con el archivo original.
Para ver esto, podemos abrir el menú Datos---> Obtener datos externos


Si elegimos "propiedades" podemos controlar propiedades como la actualización de los datos. Por ejemplo que cada vez que abrimos el archivo, que hemos guardado como archivo xls, los datos se actualicen.

Si guardamos el archivo con la propiedad de actualización, al abrir nuevamente el archivo veremos este diálogo


De esta manera podemos asegurarnos datos actualizados permanentemente.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: Importar Archivos TXT, TXT, CSV

Categorías: Manejo de Datos_

Evitando duplicados con Validación de Datos en Excel

La novedad de hoy es que finalmente he instalado el paquete castellano de Office. Así que inauguro la "nueva era" del blog con una nota sobre como evitar los duplicados en una lista.
Este tema está relacionado con los que traté en mis notas "Cómo construir una lista de valores únicos en Excel" y "Cómo comparar dos listas en Excel".
Tanto aquí como en mi blog en inglés he visto muchas entradas relacionadas con el tema de comparación de listas. Muchas veces lo que buscamos es descubrir si hay valores duplicados. Un enfoque distinto es preguntarse cómo evitar la duplicación de valores.
Supongamos que tenemos una hoja de Excel en la cual registramos los números de catálogo de los productos en un recuento de inventario. Nuestro objetivo es evitar que el mismo producto sea anotado en dos líneas. O sea, que en el momento de anotar un número de catálogo, Excel nos advierta en caso que ya exista una línea con este número.
Para lograr esto usamos la funcionalidad de Datos ---> Validación. Supongamos que tenemos esta hoja dónde anotaremos el recuento de inventario

Para descargar el archivo del ejemplo pulsar aqui.

Marcamos el rango de celdas en la columna A dónde anotaremos los número de catálogo y abrimos el menú Datos ---> Validación

Allí elegimos la opción "personalizada" y en la ventanilla escribimos la siguiente fórmula: =CONTAR.SI($A$4:$A$30,A4)=1

El rango $A$4:$A$30 incluye todas las celdas dónde anotaremos los números de catálogo. Es recomendable utilizar Nombres para señalar el rango. Cada vez que registremos un número de catálogo en la columna A, Excel contará cuántas veces aparece en el rango señalado. Si aparece más de una vez, el resultado de la función sera 2 o más y por lo tanto no se cumplirá la condición de la fórmula =CONTAR.SI($A$4:$A$30,A4)=1
Si intentamos registrar un valor ya existente recibiremos una advertencia

Este método tiene un inconveniente. Si copiamos el número de catálogo, por ejemplo de otra hoja, en lugar de escribirlo manualmente, Excel no reconocerá la duplicación y aceptará el registro.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: CONTAR.SI, COUNTIF, Evitar Duplicados, Validación de Datos en Excel

Categorías: Funciones&Formulas_, Manejo de Datos_