Bill hizo la pregunta de esta semana sobre datos redundantes de Excel.
Construyo una lista de transacciones mensuales en Excel. Al final del mes, necesito eliminar los datos redundantes y obtener un total por código de cuenta. Cada código de cuenta puede aparecer varias veces. Luego, Bill describió su metodología actual de Excel, que es similar al método 1 a continuación, con el fin de obtener una lista única de códigos de cuenta, con planes para usar una matriz de fórmulas CSE para obtener los totales. Él pregunta, ¿hay alguna manera más fácil de llegar a una lista única de códigos de cuenta con totales para cada cuenta?
Esta es una pregunta perfecta para las vacaciones. Siendo un usuario de Lotus durante 15 años, reconozco el método de Bill como el método clásico para la manipulación de datos "rápida y sucia" de los buenos viejos tiempos de la versión 2.1 de Lotus. Esta es una temporada para contar nuestras bendiciones. Cuando piensa en esta pregunta, se da cuenta de que la gente de Microsoft realmente nos ha otorgado una serie de herramientas a lo largo de los años. Si está utilizando Excel 97, existen al menos cinco métodos para realizar esta tarea, todos los cuales son mucho más fáciles que el método clásico descrito por Bill. Ofreceré un tutorial sobre los cinco métodos esta semana.
Mi conjunto de datos simplificado tiene números de cuenta en la columna A y montos en la columna B. Los datos van de A2: B100. No está ordenado al principio.
Método 1
Utilice declaraciones If creativas junto con Pegar valores especiales para encontrar la respuesta.
Dadas las nuevas herramientas que ofrece Excel, ya no recomiendo este método. Solía usar esto mucho antes de que aparecieran cosas mejores y todavía hay situaciones en las que resulta útil. Mi nombre alternativo para esto es el método "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM". Estos son los pasos.
- Ordene los datos por columna A.
- Invente una fórmula en la columna C que mantendrá un total acumulado por cuenta. La celda C2 es
=IF(A2=A1,C1+B2,B2). - Invente una fórmula en D que identifique la última entrada de una cuenta en particular. La celda D2 es
=IF(A2=A3,FALSE,TRUE). - Copie C2: D2 en todas sus filas.
- Copia C2: D100. Haga una edición - PasteSpecial - Values de nuevo en C2: D100 para cambiar las fórmulas a valores.
- Ordenar por columna D descendente.
- Para las filas que tienen un VERDADERO en la columna D, tiene una lista única de números de cuenta en A y el total acumulado final en C.
Ventajas: es rápido. Todo lo que necesita es un gran sentido para escribir declaraciones IF.
Contras: hay mejores formas.
Método 2
Utilice Filtro de datos - Filtro avanzado para obtener la lista de cuentas únicas.
La pregunta de Bill era realmente cómo obtener una lista única de números de cuenta para poder usar las fórmulas de CSE para obtener los totales. Este es un método para obtener una lista de los números de cuenta únicos.
- Resalte A1: A100
- En el menú, elija Datos, Filtro, Filtro avanzado
- Haga clic en el botón de opción "Copiar en otra ubicación".
- Haga clic en la casilla de verificación "Sólo registros únicos".
- Elija una sección en blanco de la hoja de trabajo donde desea que aparezca la lista única. Ingrese esto en el campo "Copiar a:". (Tenga en cuenta que este campo está atenuado hasta que seleccione "Copiar en otra ubicación".
- Haga clic en Aceptar. Los números de cuenta únicos aparecerán en F1.
- Ingrese cualquier manipulación de línea descendente, fórmulas de matriz, etc. para obtener sus resultados.
Ventajas: Más rápido que el método 1. No se requiere clasificación.
Contras: Las fórmulas de CSE requeridas después de esto harán que su cabeza dé vueltas.
Método 3
Utilice la consolidación de datos.
Mi calidad de vida mejoró cuando Excel ofreció Data Consolidate. ¡Esto fue GRANDE! Se tarda 30 segundos en configurarlo, pero significa la muerte para DSUM y otros métodos. Su número de cuenta debe estar a la izquierda de los campos numéricos que desea sumar. Tienes que tener encabezados encima de cada columna. Debe asignar un nombre de rango al bloque rectangular de celdas que incluye los números de cuenta en la columna de la izquierda y los títulos en la parte superior. En este caso, ese rango es A1: B100.
- Resalte A1: B100
- Asigne un nombre de rango a esta área haciendo clic en el cuadro de nombre (a la izquierda de la barra de fórmulas) y escribiendo un nombre como "TotalMe". (Alternativamente, use Insertar - Nombre).
- Coloque el puntero de la celda en una sección en blanco de la hoja de trabajo.
- Seleccionar datos: consolidar
- En el campo de referencia, escriba el nombre del rango (TotalMe).
- En la sección Usar etiquetas en, marque la fila superior y la columna izquierda.
- Haga clic en Aceptar
Pros: Este es mi método favorito. No se requiere clasificación. El acceso directo es alt-D N (nombre de rango) alt-T alt-L enter. Es fácilmente escalable. Si su rango incluye 12 columnas mensuales, la respuesta tendrá totales para cada mes.
Contras: si realiza otra consolidación de datos en la misma hoja, debe borrar el nombre del rango anterior del campo Todas las referencias con el botón Eliminar. El número de cuenta debe estar a la izquierda de sus datos numéricos. Es un poco más lento que las tablas dinámicas, lo que se nota en conjuntos de datos con más de 10,000 registros.
Método 4
Utilice subtotales de datos.
Esta es una característica interesante. Debido a que es extraño trabajar con los datos resultantes, los uso con menos frecuencia que Data Consolidate.
- Ordenar por columna A ascendente.
- Seleccione cualquier celda del rango de datos.
- Elija Datos - Subtotales en el menú.
- De forma predeterminada, Excel ofrece el subtotal de la última columna de sus datos. Esto funciona en este ejemplo, pero a menudo tiene que desplazarse por la lista "Agregar subtotal a:" para elegir los campos correctos.
- Haga clic en Aceptar. Excel insertará una nueva fila en cada cambio de número de cuenta con un total.
Después de tener los subtotales, verá aparecer un pequeño 123 debajo del cuadro de nombre. Haga clic en 2 para ver solo una línea por cuenta con los totales. Lea Copiar subtotales de Excel para obtener una explicación de los pasos especiales necesarios para copiarlos en una nueva ubicación. Haga clic en 3 para ver todas las líneas. Ventajas: Cool Feature. Excelente para imprimir informes con totales y saltos de página después de cada sección.
Contras: Los datos deben ordenarse primero. Lento para muchos datos. Tienes que usar Goto-Special-VisbileCellsOnly para obtener los totales en otro lugar. Tiene que usar Data-Subtotals-RemoveAll para volver a sus datos originales.
Método 5
Utilice una tabla dinámica.
Las tablas dinámicas son las más versátiles de todas. No es necesario ordenar sus datos. Las columnas numéricas pueden estar a la izquierda oa la derecha del número de cuenta. Puede hacer que los números de cuenta bajen o crucen la página fácilmente.
- Seleccione cualquier celda del rango de datos.
- Elija Datos - Tabla dinámica en el menú.
- Acepte los valores predeterminados en el paso 1
- Asegúrese de que el rango de datos en el paso 2 sea correcto (generalmente lo es)
- Si está utilizando Excel 2000, haga clic en el botón Diseño en el paso 3. Los usuarios de Excel 95 y 97 van automáticamente al diseño como en el paso 3.
- En el cuadro de diálogo de diseño, arrastre el botón Cuenta desde el lado derecho del cuadro de diálogo y suéltelo en el área Fila.
- Arrastre el botón Cantidad desde el lado derecho del cuadro de diálogo y suéltelo en el área de Datos.
- Los usuarios de Excel 2000 hacen clic en Aceptar, los usuarios de Excel 95/97 hacen clic en Siguiente.
- Especifique si desea obtener los resultados en una nueva hoja o en una sección específica de una hoja existente. Obtenga más información sobre las tablas dinámicas en Trucos avanzados de tablas dinámicas de Excel.
- Las tablas dinámicas ofrecen una funcionalidad increíble y facilitan esta tarea. Para copiar los resultados de la tabla dinámica, necesita hacer un Edit-PasteSpecial-Values, de lo contrario Excel no le permitirá insertar filas, etc.
Ventajas: Rápido, Flexible, Potente. Rápido, incluso para muchos datos.
Contras: Algo intimidante.
Bill ahora tiene cuatro métodos nuevos para eliminar los datos redundantes. Si bien estos métodos no han estado disponibles desde el principio de los tiempos, tanto Lotus como Excel han sido grandes innovadores para brindarnos formas más rápidas de lograr esta tarea mundana.
