Ordenar tablas
Los datos que se ingresan en una hoja de cálculo a veces pueden aparecer en un orden que es complicado para contestar ciertas preguntas. De hecho, muchas veces es necesario mirar los mismos datos de diferentes maneras. La opción “Ordenar“ puede ayudar a reconfigurar los datos de manera tal que podamos usarlos más eficientemente.
Lo primero que tenemos que tener en cuenta antes de ordenar una tabla, es que ésta se encuentre dispuesta en columnas, y en lo posible utilizando la primera fila de la tabla para los títulos de cada columna. Hay básicamente dos maneras de organizar una tabla de datos:
a) Con los Botones de ordenación que aparecen en la barra Standard:
Con esta opción se peden ordenar tablas completas o simplemente las columnas seleccionadas, basando la ordenación en la primer columna de la selección.
Debemos tener cuidado con la ordenación, porque si sólo lo hacemos con una sola columna y la tabla está compuesta por varias de ellas, es posible que mezclemos los datos, por ejemplo, si tenemos una tabla de los nombres de nuestros amigos, el teléfono de cada uno, la dirección y la ciudad, y queremos organizar esta tabla de tal modo que aparezcan los nombres ordenados alfabéticamente en orden ascendente, y sólo señalamos esta columna, los teléfonos y las direcciones muy posiblemente no concuerden, para evitar esto, señalamos toda la tabla y ahora sí hacemos nuestra ordenación.
En el siguiente ejemplo se puede observar mejor:
TABLA ORIGINAL Y TABLA ORGANIZADA SÓLO POR LA PRIMERA COLUMNA
*** Puede descargar el libro de ejemplo desde este link***
Si miramos con atención las tablas anteriores, se notará que los teléfonos y direcciones del listado que aparece en el segundo cuadro son diferentes al primero, esto se debe a que se organizó sólo la primera columna del listado, y por esto se revolvieron los datos. Por ejemplo si miramos los datos de Andrea aparecerá lo siguiente:
Tabla original |
Tabla organizada sólo por la primera columna |
|---|---|
| Nombre: Andrea | Nombre: Andrea |
| Teléfono:2175764 | Teléfono: 4348616 |
| Dirección: Calle 2. | Dirección: Calle 10. |
| Ciudad: Cartago | Ciudad: Medellín. |
El siguiente es el resultado si señalamos toda la tabla antes de ordenar:
Como se puede observar en esta tabla, todos los datos corresponden a los que aparecen en la tabla original.
Pero que sucede si queremos que esta tabla aparezca ordenada por las ciudades y luego por los nombres?. Pues utilizamos la otra opción de ordenar que nos ofrece Excel:
b) Ingresar por el menú: “Datos”> “Ordenar…”
Usando esta función, podemos tener más opciones para ordenar la tabla, ya que es más flexible que hacerlo mediante los botones de la barra de herramientas. El diálogo nos va a permitir seleccionar cuáles columna(s) usar como base del ordenamiento.
Se puede establecer el orden en tres niveles. Excel ordenará primero todas las filas que se hayan seleccionado basadas en la primera columna que haya elegido.
A continuación, Excel ordena las filas que tengan los mismos valores que esa primera columna ordenada, usando la segunda columna que haya elegido. Por último Excel ordena las filas que tengan los mismos primeros y segundos valores de columna, usando la tercera columna que se haya elegido. Usando el ejemplo anterior, ordenaremos la tabla primero por ciudades, luego por nombres y por último por teléfonos. El cuadro de diálogo de ordenar quedaría de la siguiente manera:
Y la tabla quedaría así
Filtros:
Autofiltros , Filtros Avanzados
a) Autofiltros
Un filtro es una orden que damos a Excel para que extraiga de una lista de datos, aquellos que cumplan una o varias condiciones determinadas, aquellos registros que no cumplan con las condiciones serán ocultadas. Por ejemplo, podemos tener una lista con miles de artículos y saber qué artículos valen más de 10.000 pesos, o cuantos artículos se vendieron entre una fecha y otra.
Para probar los filtros automáticos, seguiremos con la lista que utilizamos en el segmento de “ordenar”.
*** Puede descargar el libro de ejemplo desde este link***
Primero seleccionamos la tabla completa, y luego accedemos a:
“Datos” > “Filtro” > “ Autofiltro”.
Aparecerán unas flechas desplegables en las cabeceras de las columnas. Si desplegamos una de las listas, y elegimos alguna opción, se verá que sólo se seleccionan los registros que cumplen esa opción (serán los únicos visibles). En nuestro caso, digamos que deseamos ver sólo las personas que aparecen en el listado de Medellín, y el listado quedará así:
Para volver a visualizar todos los registros, debemos abrir la lista que tengamos filtrada (su flecha aparecerá en color azul), y elegir la opción “Todas”.
Podemos también elegir dos o más criterios abriendo varias listas de varias columnas. Los datos se irán filtrando.
Autofiltro personalizado
Para mirar el funcionamiento del autofiltro personalizado, utilizaremos la misma tabla que hemos venido manejando, y le agregaremos un campo más para que quede claro: edad
Para utilizar el autofiltro personalizado, abrimos la lista de la columna edad, elegimos la opción “personalizar” y colocamos lo siguiente:
Con lo que lograremos el listado de las personas de nuestra tabla que está entre los 25 y los 30 años, quedándonos el listado de la siguiente manera:
Para desactivar las listas desplegables del autofiltro, debemos hacer lo mismo que hicimos al crearlo “Datos”> “Filtro”> “Autofiltro”.
b) Filtros avanzados.
A veces nos encontramos con opciones de filtrado que no podemos realizar simplemente con el autofiltro, para esto utilizamos los filtros avanzados, a los cuales tenemos acceso ingresando por el menú:
“Datos” > “Filtro” >¬ “Filtro Avanzado” (seleccionando primero la tabla que vamos a filtrar antes de ingresar a la opción).
Para comprender mejor su funcionamiento, vamos a mirar los siguientes ejemplos (todos basados en la tabla de teléfonos anterior):
Ejemplo I: Necesitamos ver las personas de Cartago entre 20 y 28 años, y las personas de Pereira entre 25 y 30 años en la misma tabla.
Hacemos lo siguiente:
En otra parte de la hoja que estamos manejando o preferiblemente en otra hoja de cálculo, escribimos los encabezados que vamos a filtrar (Ciudad y nombre), luego colocamos lo siguiente:
En la columna de ciudad colocamos las ciudades que queremos mirar (Pereira y Cartago).
Escribimos dos veces el encabezado edad (en las columnas B y C). Esto se hace con el fin de incluir dos condiciones para cada ciudad que hemos escogido, y colocamos en la celda B2: <25, con esto le indicamos a Excel que queremos los datos menores a 25. En la celda B3 colocamos >20, con esto Excel va a tener en cuenta la primera condición y esta al mismo tiempo, es como si le dijéramos a Excel que queremos las personas cuyas edades sean mayores que 20 y menores que 25 años. Luego hacemos lo mismo con las celdas de la fila 3, sólo que el rango va a ser entre 25 y 30 años.
Luego que hemos creado nuestra tabla de criterios, volvemos a nuestro listado telefónico, lo seleccionamos completo y accedemos al menú de filtro avanzado, con lo que nos va a mostrar el siguiente cuadro de diálogo:
Cuando activamos este cuadro, podemos observar que el campo “rango de la lista” ya tiene criterios, en este caso es la tabla que seleccionamos y que se encuentra en el rango “$A$1:$E$19”.
Luego en el campo “Rango de criterios”, vamos a seleccionar la tabla de criterios que hicimos anteriormente y luego damos clic en aceptar, con lo que el resultado será el siguiente:
Sólo aparecen tres nombres, que pertenecen a los registros que cumplen nuestras condiciones.
Nota: si damos clic al botón de opción ”copiar a otro lugar”, se activa el campo “copia a:”, donde seleccionamos una celda, donde al momento de dar aceptar, quedarán copiados los datos que cumplan con las condiciones que dimos.
Ejemplo II: Necesitamos ver las personas de Cartago o las personas menores de 21 años.
Hacemos lo siguiente:
En el rango de criterios que hicimos, vamos a hacer las siguientes modificaciones:
En la columna de ciudad colocamos sólo Cartago y en la columna de edad colocamos <21, pero no al frente del nombre de la ciudad, es como si le dijéramos a Excel que nos traiga todas las personas de Cartago ó todas la personas menores de 21 años; si hubiéramos colocado <21 al frente del nombre de la ciudad, Excel nos traería las personas que viven en Cartago y que son menores de 21 años, con lo cual, nos daría una tabla vacía, ya que las personas que viven en Cartago (en nuestro listado), son mayores de 21 años.
El resultado será el siguiente:
Ejemplo III: Necesitamos ver las personas menores de 22 años y las que estén entre 30 y 35.
En el rango de criterios que hicimos, vamos a hacer las siguientes modificaciones:
anto en la columna A, como en la columna B colocamos el rótulo “edad”, y colocamos las condiciones en la primera fila >=30 y <=35 para que nos incluya las personas entre estas edades, y en la segunda fila colocamos <22 para que además nos incluya a los menores de 22 años.
El resultado será el siguiente:
Subtotales
Los subtotales se utilizan en Excel cuando trabajamos con listas de datos ordenadas. Sirven para realizar cálculos totales y parciales de dichas listas. Con el siguiente ejemplo se comprenderá mejor:
Realizamos la siguiente tabla de empresas:
*** Puede descargar el libro de ejemplo desde este link***
Vamos a generar los subtotales de este listado por empresas, acumulando las ganancias debajo de la columna de Beneficios de forma que podamos ver los totales parciales y un total final. Hacemos lo siguiente:
Seleccionamos todo el rango de datos (A1:E11).
Accedemos al menú “Datos” > “Subtotales...”.
Aparecerá el siguiente cuadro de diálogo, donde colocaremos lo siguiente:
En el campo “Para cada cambio en:” elegimos EMPRESA (con esto agruparemos por empresas).
En el campo “Usar función:”, elegimos suma.
Y en el campo “Agregar subtotal a:”, seleccionamos BENEFICIOS.
Por último damos clic en aceptar.
El resultado será el siguiente:
Podemos observar que han aparecido los subtotales bajo la columna de Beneficios agrupados por países. A la izquierda aparecen unos signos que controlan el nivel de agrupación de los subtotales. Podemos aumentar o disminuir el nivel del subtotal pulsando en los signos + y - , o bien en los números que aparecen sobre estos signos.
A la tabla anterior le podemos asignar más de un subtotal, por ejemplo, vamos a calcular el promedio de gastos por empresas, sin quitar el subtotal anterior:
Seleccionamos la lista de datos y volvemos a la opción “Datos” > “Subtotales...”. Cambiamos el campo “Usar función:”, elegimos promedio.
Activamos la casilla “Agregar subtotal a:” y elegimos GASTOS.
Desactivamos la casilla “Reemplazar subtotales actuales” (si no la desactivamos, perderíamos los subtotales que hicimos anteriormente, y sólo aparecerán los nuevos que hagamos).
Por último damos clic en aceptar y el siguiente es el resultado:
Ahora hemos añadido los promedios de gastos a los subtotales anteriores de los Beneficios.
Con esta opción podemos conseguir niveles de desglose en grandes hojas de cálculo.
Si sólo queremos ver parte de la información que subtotalizamos, elegimos la opción que más se acomode a nuestras necesidades. En el listado que tenemos subtotalizado, elegimos el botón con el número 3, para que sólo se vean los subtotales de beneficio y los promedios de los gastos:
Si queremos quitar todos los subtotales, accedemos por el menú:
“Datos” > “Subtotales...”. y damos clic al botón “Quitar todos”.
Si queremos realizar subtotales de otro campo (por ejemplo por países), deberíamos primero ordenar la tabla por esa columna y luego generar los respectivos subtotales.
Tablas dinámicas
Una tabla dinámica nos permite modificar el aspecto de una lista de elementos de una forma más fácil, cómoda y resumida. Además, podemos modificar su aspecto y mover campos de lugar.
Para crear tablas dinámicas hemos de tener previamente una tabla de datos preparada y posteriormente acceder a “Datos”>”Informe de tablas y gráficos dinámicos.
Para comenzar a practicar, creamos la siguiente tabla de datos:
*** Puede descargar el libro de ejemplo desde este link***
Seleccionamos toda la tabla y accedemos a la opción de tablas dinámicas.
Aparece una ventana que representa el primer paso en el asistente para tablas dinámicas. Por omisión aparece activa la opción “Lista o base de datos de Microsoft Excel”, con lo cual le indicamos al programa que los datos que vamos a utilizar proceden de una hoja de cálculo; pero además contamos con otras opciones:
* Si los datos se encuentran en una base de datos externa, seleccionamos la opción “Fuente de datos externa”, y le indicamos al programa dónde se encuentra la base de datos, para esto se abre automáticamente el ayudante para importar datos de Microsoft Query.
* Si los datos que queremos ingresar en la tabla dinámica se encuentran en diferentes hojas, seleccionamos la opción “Rango de consolidación múltiples”.
* Si tenemos una tabla dinámica con los datos de la misma tabla, podemos seleccionar la opción “Otro informe de tabla dinámica o de gráfico dinámico”.
* Podemos definir también si queremos que nuestro informe aparezca en forma de tabla o de gráfico con las dos últimas opciones.
La siguiente es la ventana:
(Debe tener en cuenta que si está utilizando Excel 97, no contará con la opción de gráfico.)
Luego que hemos seleccionado nuestra opción, (en este caso elegiremos opción “Lista o base de datos de Microsoft Excel”), damos clic en Siguiente, con lo que aparece una ventana que nos pide la ubicación de la tabla de datos. Como ya hemos seleccionado la tabla anteriormente, bastará con dar clic en siguiente.
En la siguiente pantalla (paso 3 de 3 del asistente) definimos dónde queremos que quede ubicada la tabla dinámica que vamos a diseñar.
La opción que aparece por omisión es “Hoja de cálculo nueva”, pero también podemos definir dónde quedará.
Además contamos con la opción de diseño, donde indicamos de que forma quedarán ubicado los datos en nuestra tabla dinámica, pero podemos dar clic en finalizar y realizar el diseño en la hoja de cálculo.
Nota: debe tener en cuenta que si está trabajando con Excel 97, deberá realizar el diseño antes de terminar el asistente.
El siguiente gráfico muestra la opción de diseño. En él se observan los campos del origen de los datos, que están situados en la parte derecha del cuadro de diálogo. Estos campos tienen los mismos nombres que nosotros definimos en la tabla de origen. Basta con “arrastrar” los campos de la derecha hacia la posición deseada en el interior de la tabla.
Para terminar, damos clic en finalizar. En la hoja de cálculo aparecerá lo siguiente en caso de no haber realizado el diseño con el asistente:
lEn la parte izquierda aparecen los campos de la tabla que creamos inicialmente, lo único que tenemos que hacer es “arrastrar” los campos al sitio que necesitemos en la tabla.
Los siguientes ejemplos muestran cómo quedaría la tabla con los mismos datos, sólo moviendo los campos (botones grises):
Si modificamos algún dato de la tabla original, podemos actualizar la tabla dinámica desde la opción “Datos > Actualizar datos” siempre que el cursor esté en el interior de la tabla dinámica.
Al actualizar una tabla, Excel compara los datos originales. Pero si se han añadido nuevas filas, tendremos que indicar el nuevo rango, ingresando de nuevo al asistente.
Cuando diseñamos una tabla dinámica, no es posible editar los diferentes valores que aparecen en ella, pero nos da la posibilidad de crear campos de fórmulas, donde podemos indicar relaciones entre los campos que originalmente creamos en la tabla inicial.
Para saber como funciona, realicemos el siguiente ejercicio, teniendo como base la tabla anterior:
Supongamos que queremos saber cuál es el precio de los productos restándole el IVA del 16%. Para hacer esto, basta con colocar una fórmula fuera de la tabla que haga este cálculo, que sería más o menos así: =”producto”-(“producto”*16%), donde “producto sería la celda donde se encuentra el valor del producto; pero si aplicamos filtros, o cambiamos el estilo de la tabla, es posible que las fórmulas se borren o que no queden donde queremos y nos veamos en la necesidad de crear nuevamente la fórmula. Para evitar esto, creamos un nuevo campo, que va a hacer este cálculo. Para hacerlo, hacemos lo siguiente:
-Nos ubicamos en el campo de los datos de nuestra tabla dinámica y pulsamos las teclas “Ctrl.” y “+” al tiempo, con lo que aparece el siguiente cuadro de texto:
En este cuadro especificamos:
-Un nombre para el nuevo campo (por Ej. “precio sin Iva”).
-Escribimos la fórmula: =PRECIO-((PRECIO*16)/100), donde precio es un campo que podemos elegir en el cuadro “Campos”, sólo basta con elegir el campo que necesitamos (en este caso el campo “PRECIO”) y damos clic en “Insertar campo”.
-Por último damos clic en “Aceptar”, y el resultado es el siguiente:
Es posible que al terminar de diseñar la tabla dinámica nos interese ocultar algún subtotal calculado. Si es así, debemos pulsar doble clic en el campo gris que representa el nombre de algún campo, y en el cuadro de diálogo que aparece, elegir la opción Ninguno. Desde este mismo cuadro podemos también cambiar el tipo de cálculo.
Si queremos trabajar más fácilmente las tablas dinámicas, podemos hacerlo utilizando las diferentes opciones de la barra de herramientas “Tabla dinámica”. Si no está activa, podemos activarla por la opción:
“Ver” > “Barras de herramientas”> “Tablas dinámicas”
La siguiente es la barra de tablas dinámicas:
Desde aquí podemos realizar operaciones de actualización, selección de campos, ocultar, resumir, agrupar, etc.