Introducción
Excel dentro de su ficha Datos te presenta tres herramientas esenciales que te permiten analizar, integrar, combinar y esquematizar la información contenida en distintas hojas de cálculo y libros, o bien, en varias hojas de cálculo de un mismo libro, esto con la finalidad de presentar un informe automatizado, agrupado y organizado. Las herramientas que conocerás y aprenderás a utilizar son: quitar datos duplicados, consolidar y esquematizar la información.
Explicación
Quitar duplicados
Utiliza esta herramienta que se localiza en la ficha Datos para eliminar aquellos datos o información duplicada. Esto se da cuando todos los valores de una fila coinciden con los de otra fila, este fenómeno también se puede dar entre columnas. Se te recomienda que antes de eliminar los valores duplicados copies en otra hoja de cálculo el rango de datos original, esto con la finalidad de que estos datos no sufran cambios o elimines por equivocación un dato. La ruta para encontrar esta herramienta en Excel es: ficha Datos > grupo Herramienta de datos > Quitar duplicados.
Cuando se quitan los valores duplicados en un rango de datos sólo se afectan los valores que fueron seleccionados, los valores que se encuentran fuera del rango de celdas no se alteran o se quitan. Cuando se abre el cuadro de diálogo Quitar duplicados, debes de considerar los siguientes puntos:
- Todas las columnas pertenecientes al rango de celdas seleccionado de forma predeterminada aparecen seleccionadas.
- Según la operación de eliminación que deseas realizar, puedes seleccionar sólo algunas columnas para buscar los datos duplicados.
- Sólo aparecerán una vez aquellos valores de las celdas que previamente estaban duplicados, después de aplicar esta herramienta.
Las formas más comunes de eliminar los valores duplicados son a través de:
- “Filtro avanzado”.
- Comando “Quitar duplicados”.
Ejemplo:
Del siguiente inventario de libros elimina los valores duplicados utilizando las dos opciones: “Filtro avanzado” y “Quitar duplicados”.
En una hoja nueva de Excel ingresa la siguiente información:
Opción 1: Filtro avanzado
Esta opción la encuentras en la ficha Datos > grupo Ordenar y filtrar > Avanzadas. Una vez ejecutada te permite notar la diferencia de una tabla de datos con datos duplicados y una con datos únicos:
- Coloca el cursor en cualquier celda de la tabla o información para seleccionar los posibles valores duplicados existentes.
- Te aparece el cuadro diálogo filtro avanzado, encontrarás diversas opciones:
- Filtrar la lista sin moverla a otro lugar: esta opción te permite hacer la operación de eliminación de valores duplicados alterando la información original.
- Copiar a otro lugar: esta opción te permite mover o elegir en qué parte de la hoja de Excel deseas colocar la “nueva información” sin duplicados.
- Rango de lista: en este campo debes de agregar el rango de datos, la tabla o la información que deseas utilizar en la operación de eliminación.
- Rango de criterios: esta opción la utilizas cuando deseas restringir qué es lo que quieres filtrar o buscar en específico.
- Copiar a: en esta opción debes especificar el rango de celdas en la cual la información se va a copiar o ingresar una vez que la operación de eliminación se haya llevado a cabo.
- Sólo registros únicos: esta opción permite la eliminación de la información que se encuentra repetida.
- Configura de la siguiente manera el cuadro de diálogo filtro avanzado:
Haz clic en Aceptar.
- Al realizar el punto anterior, observa cómo es posible notar la diferencia de una tabla con datos duplicados y otra tabla con valores únicos:
Opción 2: Comando Quitar duplicados
- Coloca el cursor en cualquier celda de la tabla.
- Selecciona la ficha Datos > grupo Herramientas de datos > Quitar duplicados.
- Excel selecciona toda la tabla y aparece el cuadro de diálogo Quitar duplicados el cual contiene los siguientes elementos:
- Seleccionar todo: esta opción te permite en un clic seleccionar todas las columnas que componen tu tabla de datos, rango o información.
- Anular selección: te permite ir quitando de la selección de columnas aquellas que no quieres alterar.
- Mis datos tienen encabezados: esta opción, al estar activada, te permite que los nombres de las columnas aparezcan, de lo contrario el nombre de las columnas será “Columna 1”, “Columna 2”, y así sucesivamente. Se te sugiere que esta opción se encuentre activada.
- Columnas: corresponde a todas las columnas que contiene la tabla de datos, rango o información en donde se pretende realizar la eliminación de datos duplicados.
- En la sección Columnas verifica que todas se encuentren seleccionadas:
Haz clic en Aceptar.
- Observa que Excel automáticamente realiza la operación eliminación de valores duplicados y te muestra un cuadro de diálogo con la confirmación del número de registros eliminados:
Es importante que para que esta herramienta funcione, debes contar con información repetida dentro de la tabla de datos, rango o información. Cuando ésta no contenga valores duplicados, Excel desplegará en un cuadro de diálogo la confirmación de que no existen valores duplicados.
Consolidar datos
Utiliza esta herramienta cuando necesites unir toda la información que tienes distribuida en distintas hojas de un libro activo en otros libros abiertos o cerrados para, de esta forma, presentar los resultados de la operación a través del operador “Suma”. Esta información es colocada en una nueva hoja de Excel llamada de “Consolidación” o “Consolidada”.
A continuación, la ruta para acceder a la herramienta “Consolidar”: Ficha Datos > grupo Herramientas de datos > Consolidar.
Existen tres técnicas de consolidación: por posición, por categoría y por referencia 3D.
Haz clic en cada botón para conocer las diferentes técnicas.
Esta técnica la puedes utilizar cuando los datos de todas las áreas de origen tienen la misma estructura, es decir, el mismo orden y ubicación.
Los pasos que debes seguir son los siguientes:
- Haz clic en una celda de la hoja activa o bien en otro libro para incluir la información consolidada.
- Haz clic en el comando Consolidar que se encuentra en la ficha Datos.
- En el cuadro de diálogo Consolidar, en la sección Función elige el operador Suma.
- En la sección Referencia, ingresa el origen de la información que deseas consolidar y presiona el botón Agregar. Por cada referencia ingresada debes hacer clic en el botón Agregar.
- Una vez hecho el proceso de los puntos anteriores, haz clic en el botón Aceptar y de esta forma se crea la hoja consolidada.
Ejemplo:
En una empresa de transporte de mensajería, carga regular y pesada, se te ha pedido un informe para conocer las ventas de cuatro días del mes de Julio, esto para tomar una decisión respecto al aumento de las tarifas para los siguientes días en la ruta MEX-GDL y GDL-MEX:
- En un nuevo libro de Excel llamado SUC-MEX introduce la siguiente información y cambia el nombre a la hoja como MEX:
- En otro libro de Excel llamado SUC-GDL, introduce la siguiente información y cambia el nombre a la hoja como GDL:
- En otro libro de Excel llamado “Consolidación” coloca el cursor en la celda A1, ya que aquí se mostrará el informe consolidado.
- Ve a la ficha Datos > Consolidar y configura el cuadro de diálogo Consolidar como se muestra a continuación:
- Al hacer clic en Aceptar en el libro llamado “Consolidación” en la hoja “Consolidar”, observa cómo la información contenida en los libros SUC-MEX y SUC-GDL se une en esta hoja creada para este propósito.
- Tu informe no cuenta con un formato adecuado y la columna “Fecha” por el momento no es relevante, por lo que será necesario eliminar esta columna. El aspecto final de tu informe es el siguiente:
Debes considerar que cuando se hace la consolidación por posición, Excel no copiará los rótulos de categoría. Si deseas rótulos en la hoja de cálculo destino (Consolidar) debes copiarlos o ingresarlos. Esto sucede cuando no activas la opción “Fila” superior del cuadro de diálogo “Consolidar”.
Utiliza esta técnica cuando las hojas de cálculo no contengan la misma estructura. Excel toma los encabezados de título y de columna como guía para realizar la unión.
Los pasos que debes seguir son los siguientes:
- Utiliza los mismos libros del ejemplo anterior; SUC-GDL y SUC-MEX y en el mismo libro llamado “Consolidación” agrega una hoja nueva y edita su nombre por “Consolidación”-“Categoría”. Deja el cursor en la celda A1 para empezar la consolidación a partir de aquí.
- Ve a la ficha Datos > Herramientas de datos > Consolidar y configura el cuadro de diálogo de la siguiente manera:
- Una vez que has configurado el cuadro de diálogo Consolidar y hacer clic en Aceptar, el resultado que obtienes es el siguiente:
Observa cómo Excel incluye a la izquierda de la tabla en la parte superior (columnas) 1 y 2. En la parte que corresponde a las filas les asigna un sigo de “+”, esto significa que, al dar clic en él, tendrás disponible la información relacionada sobre el nombre del libro que contiene la información que se está consolidando. Incluye como título del encabezado en la celda A1 el texto Ciudad.
- Al hacer clic en la opción de la columna dos, observa cómo la información se expande para darte a conocer toda la información de los libros SUC-MEX y SUC-GDL:
- También observa que la información consolidada no tiene un formato presentable. En la siguiente imagen se muestra esta información como un informe con formato:
Al utilizar esta técnica debes de considerar los siguientes aspectos:
- No pide una restricción en particular puesto que su uso aplica cuando las hojas de cálculo de origen tienen un diseño distinto, cuando deseas crear un diseño personalizado o bien utilizar fórmulas personalizadas.
- Cuando los datos de origen se modifican las referencias 3-D se actualizan automáticamente.
- Puedes emplear rangos de nombres previamente definidos siempre y cuando todas las hojas de cálculo de origen tengan el mismo diseño o estructura.
Los pasos que debes seguir son los siguientes:
- En la hoja que vas a consolidar, debes copiar o introducir los encabezados de columna que deseas para los datos consolidados.
- Debes de hacer clic en la celda donde deseas incluir los datos consolidados.
- Introduce una fórmula que incluya referencias a las celdas de origen en cada hoja de cálculo que contenga los datos que se desean consolidar.
Continuando con el ejemplo anterior, ahora utiliza este tipo de referencia para consolidar la información que te interesa incluir en tu reporte:
- Crea un nuevo libro de Excel y guárdalo como “Consolidación-1”.
- En las hojas del libro creado pega en la hoja 1 la información correspondiente a Suc-GDL y cambia el nombre de esta hoja a GDL. En la hoja 2 pega la información correspondiente a Suc-MEX y cambia el nombre de esta hoja a MEX.
- En la hoja 3 cambia su nombre por Consolidar-3D, aquí es donde la información quedará consolidada de acuerdo a tus requerimientos.
- En la hoja Consolidar-3D ingresa la siguiente información, la cual corresponde a la estructura de las hojas MEX y GDL, nota que el encabezado de columna [Fecha] no fue incluido:
- En la celda B2 ingresa la función “Suma”. Posteriormente, da clic sobre la hoja MEX y selecciona el rango de celdas C2:C5 y presiona la tecla Enter. La función te queda de la siguiente forma:
=SUMA(MEX!C2:C5)
- En la celda B3 ingresa la función “Suma”. Posteriormente, da clic sobre la hoja GDL y selecciona el rango de celdas C2:C5 y presiona la tecla Enter. La función te queda de la siguiente forma:
=SUMA(GDL!C2:C5)
- En la celda B4 ingresa la función “Suma” y selecciona el rango B2:B3. La función es la siguiente:
=SUMA(B2:B3)
- Ingresa la función “Suma” para el resto de las columnas (Valor Cajas y Valor Tarimas).
- En la celda E2, ingresa la función “Suma” para el rango B2:D2 y en la celda E3, ingresa la función “Suma” para el rango B3:D3.
- Revisa que los resultados sean los que se muestran a continuación:
- Finalmente, puedes darle el formato deseado para una mejor presentación.
Si vas a incluir en la función “Suma” o en otra función más valores de las otras hojas, debes apoyarte en la tecla Mayús para ir haciendo una selección múltiple.
Esquema de datos
Cuando utilizas la herramienta Subtotales automáticos, la lista de valores aparecerá totalmente esquematizada para poder interpretar mejor su estructura. Su representación es a través de la aparición de ocultar detalle (signo “-“) y mostrar detalle (signo “+”). De esto se interpreta que Excel provee que la información se agrupe por temas y sea más fácil su análisis.
Los esquemas se encuentran en la ficha Datos > grupo Esquema > Agrupar > Autoesquema:
Ejemplo:
Deseas elaborar un reporte esquematizado de las ventas de autos efectuadas al público, para realizarlo te apoyas en la información que proporciona INEGI:
- En una nueva hoja de Excel ingresa la siguiente información:
- Ve a la ficha Datos, grupo Esquema, Agrupar, Autoesquema y observa que la información queda esquematizada para un mejor análisis:
- Al dar clic en el nivel 1 de la fila, observa que la información se agrupa para presentar únicamente las ventas totales de los años 2018 y parte del 2019:
Si deseas eliminar el esquema creado sólo debes ir a la ficha Datos > grupo Esquema > Desagrupar > Borrar esquema.
Un esquema puede tener un máximo de ocho niveles de detalle, en donde cada uno proporciona datos sobre el nivel inmediatamente superior.
Las anteriores pantallas se obtuvieron directamente del software, para fines educativos.
Checkpoint
Asegúrate de:
- Saber quitar duplicados y usar filtros avanzados.
- Saber consolidar información contenida en varias hojas de cálculo.
- Saber generar un reporte esquematizado con subtotales.
Para reforzar el contenido de esta lección, te invitamos a revisar la sección de explicación interactiva, en donde encontrarás más detalles de las explicaciones y ejemplos guiados.
