Creación constante de Excel, trabajo de datos, eliminación

Excel es una herramienta bastante poderosa que le permite trabajar con grandes matrices de datos, realizando una serie de operaciones con ellos. Se puede argumentar que las tablas de Excel son bases de datos relacionales con las que puede trabajar sin poseer las habilidades de un programador. Pero si el programa se usa profesionalmente, las funciones básicas no son suficientes para generar informes de diferentes estructuras. Para estos fines, el procesador de tablistas tiene una herramienta más funcional y potente: tablas de resumen.

¿Por qué Excel necesita tablas de resumen?

En el original, este término suena como una tabla de pivote, y la traducción de la frase es algo nublada: "una tabla que se puede cambiar, retorcerse, demostrar en diferentes proyecciones". Qué quiere decir esto? Interpretación de los datos tabulares: la tarea no es fácil. Esto generalmente se hace usando fórmulas, recibiendo los resultados finales al final de la tabla.

Si necesita crear informes complejos usando filtros y cambiar los datos de origen en la secuencia correcta, haga esto con Excel estándar significa, si es posible, es muy difícil. Pero gracias a las tablas consolidadas, tales informes son mucho más fáciles de crear, como varios diagramas, permitiendo solo con la ayuda de un mouse a las líneas y columnas, cambiarlos en algunos lugares, usar varios controles.

La mayoría de los usuarios regulares de Excel ni siquiera son conscientes de la presencia de una herramienta tan poderosa. Para mayor claridad, damos un ejemplo pequeño e indicativo del uso de las tablas consolidadas de Excel "para tontos".

Supongamos que es un gerente de la empresa y es responsable de mantener la documentación de ventas. Todas las transacciones se ingresan en la tabla que indican todos los detalles (vendedor, comprador, cantidad, cantidad, fecha de venta, dirección del comprador, etc. D.). El líder solicita un informe sobre ventas por región.

Cómo lo hacemos? Creamos un diseño de una nueva tabla, en el límite de la cual indicamos toda la gama de bienes vendidos, y en las líneas: los nombres de las regiones. Para hacer esto, copiamos de la tabla fuente una columna con bienes, eliminando los duplicados. Usando el modo de un inserto especial, transformamos esta columna en un sombrero de la página consolidada. Venimos de la misma manera con las regiones, pero dado que se encuentran en la tabla consolidada verticalmente, simplemente copiamos sin transponer.

En cada celda, resumimos los ingresos utilizando la función de la suma. Finalmente, agregue los resultados finales a la parte inferior y envíe el informe al gerente. El líder pide a las regiones que organicen en la parte superior, y los bienes, en las líneas. Hace un nuevo informe en 5 minutos y obtiene una nueva tarea: proporcionar datos de ganancias, no ingresos, es decir, teniendo en cuenta los costos. Bueno, cambiamos un poco la fórmula, y nuevamente el resultado está listo en minutos. Finalmente, hay una solicitud para preparar dichos informes para cada vendedor para evaluar su efectividad. Y este no es un problema para una tabla de resumen, si sabe dónde se encuentran los datos iniciales necesarios!

Mientras tanto, cualquier usuario de Excel sabe lo difícil que es realizar tales manipulaciones dentro del marco de la tabla de origen.

Creación y trabajo con tablas consolidadas en Excel

Supongamos que tenemos la tabla fuente de ventas:

Considere cómo crear una tabla consolidada sobre la base de estos datos tabulares. Para hacer esto, seleccione cualquier celda, después de lo cual seleccionamos la pestaña "Insertar" en el panel principal. Aparecerán dos pestañas adicionales: "Tabla de resumen" y a la izquierda - "Tablas de resumen recomendadas". Si es un principiante y no comprende cómo puede organizar los datos de origen, recomendamos usar el segundo botón. Este es un maestro que le ofrecerá opciones de informes preparados. Solo tiene que elegir la opción apropiada y obtener la tabla consolidada terminada a su disposición. Solo queda para corregirla si, según algunos criterios, ella no te satisface.

La segunda forma principal de crear tablas consolidadas es "desde cero", para la cual presionamos el primer botón.

Se abre una nueva ventana, en la que es necesario establecer dos parámetros: el rango inicial de la celda y la ubicación del ST. Dado que seleccionamos una celda antes de crear una tabla de pivote, el rango se determinará automáticamente, el valor predeterminado para el segundo parámetro es una nueva hoja. Para que no cambiemos nada y haga clic en Aceptar. Como resultado, se abrirá una nueva hoja con un diseño incompleto.

Para configurarlo, use el panel correcto titulado "Campo de la tabla de resumen". Aquí en la unidad superior se enumera una lista de los campos disponibles correspondientes a la columna de la tabla de origen. Puede agregar campos de dos maneras: colocando una marca de verificación opuesta al valor deseado, y luego se meterá automáticamente en el diseño. Pero lejos de siempre al lugar que necesitas. En tales casos, solo necesita arrastrar el campo a la posición necesaria del diseño. Eliminar una posición en el diseño se lleva a cabo de manera similar, eliminando la marca de verificación o arrastrando el panel derecho.

Contiene cuatro regiones a continuación, sobre la base de la cual se formará una tabla de resumen. Considérelos con más detalle:

• El área de valores es la parte principal del ST, ya que es aquí donde está indicado cómo procesar columnas y líneas. Por lo general, este es un resumen: este método de agregación se establecerá de forma predeterminada, siempre que todas las celdas del rango acepten valores numéricos. En presencia de al menos una celda vacía o con datos en un formato que sea diferente de uno numérico, en lugar de la suma de los valores de la celda, su número se calculará, por lo que este hecho debe tenerse en cuenta. Para nuestro ejemplo, se llevará a cabo la suma para cada producto en la región correspondiente. Además de la suma, puede establecer cualquier otro método de cálculos, que tengan aproximadamente dos docenas (calculando el valor promedio, compartir, etc.). La forma más fácil de hacerlo es directamente en la tabla de resumen, haciendo clic en PKM para cualquier celda y eligiendo el método de cálculos necesario;
• El área de las líneas: consiste en valores únicos de celdas ubicadas en la columna izquierda. Si inserta varios campos aquí, la tabla de resumen ya no será plana, sino de nivel múltiple;
• El área de la columna también contiene valores de campo, pero ya en relación con la línea superior de la tabla inicial;
• El área del filtro le permite imponer restricciones a los términos de cálculo de los valores de las celdas. Por lo general, se coloca un campo aquí, según el cual se debe filtrar una muestra en otras tres áreas.

Gracias a la presencia de estas áreas, puede configurar una muestra de la tabla inicial de casi cualquier complejidad, pasando unos minutos.

Para dejarlo más claro, explicamos lo anterior utilizando un ejemplo descrito en la sección anterior.

Entonces, las instrucciones del paso por paso sobre cómo hacer una tabla consolidada en Excel:

• Desde la lista de campos en la tabla de resumen, arrastramos el campo "ingresos" al área de valores;
• La "región" de campo (la región del comprador) se arrastra a las líneas;
• "Producto" de campo puesto en el área de la columna.

Solo tres acciones, y una tabla de resumen adecuada para enviar a las autoridades, está lista! Nos tomó unos 10 segundos compilarlo.

Cómo cumplir con los otros deseos del director?

Para obtener ganancias en lugar de ingresos, es suficiente colocar el campo correspondiente en el área de valores en lugar de los ingresos, los cálculos correspondientes en el resumen se realizarán automáticamente.

La segunda tarea también se realiza en segundos: para cambiar las regiones y los bienes, simplemente arrastiéndolos de un área a otra (lugares de cambio).

Para obtener los resultados para cada administrador, es suficiente colocar el campo "Administrador" en el área de filtro, luego aparecerá un selector en la parte superior en la que debe especificar el apellido deseado para recibir un informe. Existe la oportunidad de combinar un informe sobre varios vendedores.

Por supuesto, no hemos traído el más difícil, sino un ejemplo muy indicativo, que le permite comprender cómo crear tablas de resumen en Excel. El método para calcular los valores en las celdas puede ser más intrincado, es posible que deba usar los campos calculados, así como aplicar formateo condicional: las posibilidades a este respecto en Excel son impresionantes.

Sobre la fuente de datos

Ya hemos mencionado que se imponen ciertos requisitos sobre los datos iniciales utilizados para crear correctamente tablas consolidadas en el programa de Excel. Vamos a enumerarlos:

• La restricción principal se asocia con la presencia obligatoria de nombres sobre columnas involucradas en los cálculos. Dichos identificadores son necesarios para la formación de informes resultantes: al agregar nuevos registros (cadenas) a la tabla inicial, el formato no es necesario para cambiar el formato ST, y los resultados se recalcularán automáticamente;
• Asegúrese de que en las celdas de las líneas y columnas que participan en la muestra, se introducen parámetros numéricos. Si están vacíos o contienen valores de texto, estas líneas caerán de los cálculos, lo que distorsionará los resultados de los cálculos;
• Siga la correspondencia de los formatos de las líneas y el contenido de las celdas. Si se define como una fecha, entonces todos los valores en la columna deben tener el mismo formato, de lo contrario, filtrar y errores de cálculo estarán mal.

Si se adhiere a estas reglas, con la construcción de problemas de la tabla resumida no deberían surgir.

Actualización de datos en la tabla de conjuntos de Excel

Aunque hablamos sobre el recalculación automático de las tablas consolidadas al hacer ajustes a los datos de origen o en la estructura de ST, de hecho, por razones para ahorrar recursos informáticos, debe realizar ciertas acciones.

Puede actualizar los datos en nuestra tabla de exposición Excel de dos maneras:

• Haga clic con el botón derecho del mouse en cualquier celda y seleccione el elemento "Actualizar" en el menú contextual;
• Uso de la pestaña "Datos" y el botón "Actualizar todo" ".

El almacenamiento en caché de las tablas está especialmente justificado si tienen un tamaño grande, en este caso la velocidad del programa aumentará significativamente, especialmente si ingresa nuevas líneas regularmente y con una alta frecuencia.

Agregar una columna o línea a Excel

En general, la operación es trivial, si el parámetro deseado está en la tabla de origen. Solo tienes que arrastrar el campo derecho al área correcta. Si no hay cadena o columna en la tabla inicial, agrégela, ingrese, si es necesario, valores. A continuación, haga clic en la pestaña "Análisis" y abra nuestra tabla cambiada. Solo queda para actualizar los datos (ver. La sección anterior), como resultado, la lista de campos cambiará en el panel derecho del ST: se le agregará un nuevo parámetro.

A menudo, la tabla resumida no tiene especies de visualización. Por ejemplo, cuando hay varios campos en las líneas. Entonces uno de ellos se muestra en su totalidad, y los valores del otro deben indicarse en la parte superior de la tabla. Si hay pocos valores de estos (digamos, en nuestro ejemplo de regiones de ventas solo 6), entonces tiene sentido agregar una columna "regiones" de la tabla de pivote al área correspondiente, transfiriéndolo desde el área del líneas.

Cambiar el procedimiento para seguir las columnas también es fácil de lograr, simplemente transfiriendo la columna que se dirige a la posición deseada con el mouse.

Formación de una tabla de resumen de varios archivos (tablas) Excel

Esta es una tarea más complicada pero bastante completa. Use el Master ST, que debe agregarse al panel de acceso rápido.

Entonces, a la izquierda, cerca del elemento de "archivo", haga clic en la flecha dirigida hacia abajo, seleccione el elemento "Otros comandos". Se abrirá una nueva ventana, en el panel central hacemos clic en las opciones "Todos los equipos". Encontramos en la lista del "maestro de las tablas consolidadas" en la lista que se abre, seleccione y haga clic en el botón "Agregar". El icono correspondiente aparecerá en el panel superior.

Lanzamos el maestro. Supongamos que necesitamos combinar datos de dos hojas, mayo y en junio. La condición principal es que deben coincidir en la estructura. Si es así, en la primera ventana del maestro seleccionamos la opción "Tabla de sustancia" y el siguiente parámetro: "Varios rangos de consolidación".

En el segundo paso, indicamos al maestro que necesitamos varios campos. En el tercero: seleccione el rango de celdas para sumar la primera hoja, haga clic en "Agregar" y haga lo mismo para la segunda hoja Excel. Ahora necesitas dar los nombres a dos campos. Hacemos esto primero para el primer campo (haga clic en el selector "1", en el campo de la columna "1" Ingresamos "May"), luego, por analogía, para la segunda. En este caso, será necesario indicar en la lista de rangos la línea correspondiente, la primera o la segunda.

Después de hacer clic en el botón "Listo", se abre una nueva ventana, en la que indicamos la "nueva hoja" y presionamos el botón "Ready" nuevamente.

ATENCIÓN. La recepción de tablas multidimensionales es inconveniente, ya que es difícil navegar con una gran cantidad de campos, y los cálculos necesarios deben llevarse a cabo, cada vez que indican los valores necesarios del área superior de la tabla.

Cómo eliminar el arte

El caso más fácil: cuando creó una tabla consolidada, envió los resultados al chef y ya no lo necesita. Si está seguro de esto, simplemente seleccione la tabla y haga clic en la tecla Eliminar. Simple y efectivamente.

Pero de repente la estructura de la mesa puede ser necesaria en el futuro? Excel tiene la oportunidad de eliminar solo los resultados o los datos de las celdas. Considere cómo se hace esto.

Para eliminar los resultados del cálculo, realizamos los siguientes pasos:

• Marcamos cualquier célula de ST;
• Seleccione la pestaña "Análisis" en el panel superior;
• De la lista de acciones disponibles, seleccione "Borrar";
• Haga clic en el punto "Borrar todo".

Pero qué hacer si desea guardar los resultados, pero no necesita datos usted mismo, es decir, desea liberar la tabla? Esta situación a menudo surge si el liderazgo solo necesita resultados. El algoritmo de las acciones:

• Nuevamente seleccione cualquier celda, haga clic en la pestaña "Análisis";
• Seleccione el elemento del menú "Acción", haga clic en "Elija", marque toda la tabla consolidada con el mouse;
• Haga clic en PCM dentro del área seleccionada;
• En el menú contextual, seleccione el elemento "Copiar";
• Vamos a la pestaña "Inicio", hacemos clic nuevamente en PKM y seleccionamos "Insertar";
• Elija la pestaña "Insertar valor", en ella notamos el parámetro "Insertar como un valor".

Como resultado, la tabla de resumen se borrará mientras se mantiene los resultados.

CONSEJO. Puede acelerar el procedimiento utilizando la combinación de clave. Para seleccionar la tabla, use Ctrl + A, para copiar - Ctrl + C. Luego presione Alt + E, Alt + S, Alt + V y complete el procedimiento presionando Enter.

Para eliminar las tablas consolidadas en Excel 2007/2010, debe usar otro algoritmo:

• Seleccione ST diseñado para la eliminación;
• Haga clic en la pestaña "Parámetros";
• En el "grupo de acciones", seleccione el elemento "limpio" y luego: "Limpiar todo".

Si a su jefe le encanta la visualización de datos, es obvio que tendrá que usar diagramas consolidados. Dado que ocupan mucho espacio en la mesa, después de su uso generalmente se eliminan.

En las versiones antiguas del programa, para esto debe seleccionar un diagrama, haga clic en la pestaña "Análisis", seleccione un grupo de datos y haga clic en secuencialmente "Limpiar" y "Clean todo".

Además, si el diagrama se asocia con la tabla más resumida, después de su eliminación, perderá todas las configuraciones de la tabla, sus campos y formateo.

Para versiones mayores que Excel 2010, debe seleccionar un diagrama, seleccionar "acciones" en la pestaña "Análisis" y hacer clic en "Limpiar" y "limpiar todo". El resultado será similar.

Esperamos que nuestras lecciones en tablas consolidadas le permitan descubrir esta funcionalidad suficientemente potente. Si aún tiene preguntas, pregúnteles en los comentarios, intentaremos responderlas.