hogar › Servicios › Análisis de datos en Excel. Complemento de análisis de datos en Excel Herramientas de Excel para análisis de datos y resolución de problemas de optimización.

Análisis de datos en Excel. Complemento de análisis de datos en Excel Herramientas de Excel para análisis de datos y resolución de problemas de optimización.

Los datos de origen para el análisis se pueden presentar en una hoja de trabajo como una lista de valores. Para identificar una matriz de valores, se utilizan nombres de columnas (etiquetas) y se crean bloques con nombres.

Para procesar datos numéricos, utilice el paquete de análisis. Se debe configurar primero

en Excel 2003: dé el comando Herramientas -> Complementos y marque la casilla junto a Paquete de análisis. Ahora el comando Análisis de datos aparecerá en el menú Herramientas.

en Excel 2007: haga clic en el botón Office, luego haga clic en el botón Opciones Sobresalir elegirComplementos, en la parte inferior de la ventana en el campo Control, seleccione Complementos Sobresalir, haga clic en el botón Ir, marque la casilla junto a Paquete de análisis yu En la pestaña Datos, en el grupo Análisis, aparecerá el comando Análisis de datos

Al ejecutar el comando Análisis de datos abre el cuadro de diálogo Análisis de datos, en el que se selecciona el modo Estadísticas descriptivas (Fig. 23); En el cuadro de diálogo del mismo nombre, se especifican las siguientes configuraciones:

arroz. Cuadro de diálogo del modo Estadísticas Descriptivas.

Las opciones en el cuadro de diálogo Estadísticas Descriptivas tienen el siguiente significado.

El rango de entrada es un bloque de celdas que contiene los valores del indicador en estudio. Debe ingresar un enlace a las celdas que contienen los datos analizados. Una referencia debe constar de al menos dos rangos de datos contiguos, organizados como columnas o filas.

La agrupación determina la orientación del bloque de datos de origen en la hoja de trabajo. Para determinarlo, debe configurar el interruptor en Por columnas o Por filas, según la ubicación de los datos en el rango de entrada.

Etiquetas: la presencia de nombres en un bloque de celdas. Para definirlo, debe configurar el interruptor en Etiquetas en la posición de la primera fila (columna), si la primera fila (columna) en el rango de entrada contiene los nombres de las columnas. Si el rango de entrada no contiene etiquetas, los encabezados necesarios en el rango de salida se crearán automáticamente.

El nivel de confiabilidad indica el porcentaje de confiabilidad de los datos para calcular el intervalo de confianza. Para definirlo, debe marcar la casilla e ingresar el valor requerido en el campo. Por ejemplo, un valor del 95% calcula el nivel de confiabilidad de la media con una significancia de 0,05.

Kth mayor: el número de serie del mayor después del valor máximo. Seleccione esta casilla de verificación si desea que la tabla de salida incluya una fila para el k-ésimo valor más grande para cada rango de datos. Ingrese el número k en la ventana correspondiente. Si k es 1, esta fila contendrá el máximo del conjunto de datos.

Kth más pequeño: el número de serie del más pequeño después del valor mínimo. Seleccione esta casilla de verificación si desea que la tabla de salida incluya una fila para el k-ésimo valor más pequeño para cada rango de datos. Ingrese el número k en la ventana correspondiente. Si k es 1, esta fila contendrá el mínimo del conjunto de datos.

Las estadísticas descriptivas se muestran en la ubicación indicada en el campo Rango de salida. Aquí debe ingresar un enlace a la celda superior izquierda del rango de salida. Esta herramienta de análisis muestra dos columnas de información para cada conjunto de datos. La columna de la izquierda contiene etiquetas de datos estadísticos; la columna de la derecha contiene datos estadísticos. Se generará un rango de estadísticas de dos columnas para cada columna (fila) del rango de entrada, dependiendo de la posición del interruptor de agrupación.

Para cambiar dónde se muestran los resultados, puede seleccionar el botón de opción Nueva hoja de trabajo para abrir una nueva hoja de trabajo y pegar los resultados comenzando en la celda A1. Puede ingresar el nombre de la nueva hoja en el campo ubicado frente a la posición del interruptor correspondiente. Si selecciona el botón de opción Nuevo libro, se abre un nuevo libro y los resultados se insertan en la celda A1 de la primera hoja de ese libro.

Estadísticas resumidas: salida completa de estadísticas descriptivas. Para definirlo, debe marcar la casilla si en el rango de salida necesita obtener un campo para cada uno de los siguientes tipos de datos estadísticos: Media, Error estándar (de la media), Mediana, Moda, Desviación estándar, Varianza muestral , Curtosis, Asimetría, Intervalo, Mínimo, Máximo, Importe, Cuenta, Más alto (#), Más bajo (#), Nivel de confiabilidad.

Un paquete de análisis es un complemento, es decir, un programa que está disponible al instalar Microsoft Office o Excel. Para utilizar un complemento en Excel, primero debe descargarlo. Cómo descargar este paquete para Microsoft Excel 2013, Microsoft Excel 2010, Microsoft Excel 2007.

Usando el paquete de análisis de Microsoft Excel 2013

Al realizar análisis estadísticos o de ingeniería complejos, puede simplificar el proceso y ahorrar tiempo utilizando el complemento Analysis Package. Para realizar análisis utilizando este paquete, debe proporcionar datos de entrada y seleccionar parámetros. El cálculo se realizará utilizando una función macro estadística o de ingeniería adecuada y el resultado se colocará en el rango de salida. Algunas herramientas le permiten presentar los resultados de su análisis gráficamente.

Las funciones de análisis de datos solo se pueden utilizar en una hoja. Si el análisis de datos se realiza en un grupo que consta de varias hojas, los resultados se mostrarán en la primera hoja y los rangos vacíos que contienen solo formatos se mostrarán en las hojas restantes. Para analizar datos en todas las hojas, repita el procedimiento para cada hoja por separado.

Las herramientas incluidas en el paquete de análisis se describen a continuación. Para acceder a ellos, haga clic en el botón Análisis de los datos en grupo Análisis en la pestaña Datos. si el equipo Análisis de los datos no está disponible, debe descargar el complemento Analysis Pack.

Descargue y active el paquete de análisis

Abre la pestaña Archivo, presiona el botón Opciones y selecciona una categoría Complementos.
En la lista desplegable Control seleccione un artículo complementos de excel y presione el botón Ir.
En la ventana Complementos revisa la caja Paquete de análisis y luego haga clic en el botón DE ACUERDO.

Si Paquete de análisis no en la lista de campos Complementos disponibles, presiona el botón Revisar para realizar una búsqueda.
Si recibe un mensaje que indica que el paquete de análisis no está instalado en su computadora, haga clic en Sí para instalarlo.

Descargar el paquete de análisis de Microsoft Excel 2010

Un paquete de análisis es un complemento (Complemento. Un programa auxiliar que se utiliza para agregar comandos o capacidades especiales a Microsoft Office). Microsoft Excel, es decir, un programa que está disponible cuando instala Microsoft Office o Excel. Sin embargo, para utilizar el complemento en Excel, primero debe descargarlo.

Abre la pestaña Archivo y seleccione Opciones.
Selecciona un equipo Complementos y luego al campo Control seleccione un artículo complementos de excel.
Clic en el botón Ir.
En la ventana Complementos disponibles revisa la caja Paquete de análisis y luego haga clic en el botón DE ACUERDO.

Consejo. Si el complemento Paquete de análisis no en la lista de campos Complementos disponibles, presiona el botón Revisar para encontrarla.
Si aparece un mensaje indicando que el paquete de análisis no está instalado en su computadora, haga clic en Sí para instalarlo.

Análisis en la pestaña Datos Análisis de los datos.

Nota. Para incluir funciones de Visual Basic para Aplicaciones (VBA) en un paquete de análisis, puede descargar el complemento Analysis Pack - VBA. Para ello deberás seguir los mismos pasos que para descargar el paquete de análisis. En la ventana Complementos disponibles revisa la caja Paquete de análisis - VBA y luego haga clic en el botón DE ACUERDO.

Descargue el paquete de análisis estadístico de Microsoft Excel 2007

Un paquete de análisis es un complemento (Complemento. Un programa auxiliar que se utiliza para agregar comandos o capacidades especiales a Microsoft Office), es decir, un programa que está disponible cuando instala Microsoft Office o Excel. Para utilizar un complemento en Excel, primero debe descargarlo.

Selecciona un equipo Complementos y en la ventana Control seleccione un artículo complementos de excel.
Clic en el botón Ir.
En la ventana Complementos disponibles revisa la caja Paquete de análisis y luego haga clic en el botón DE ACUERDO.

Consejo. Si Paquete de análisis no en la lista de campos Complementos disponibles, luego para buscar, haga clic en el botón Revisar.

Si aparece un mensaje indicando que el paquete de análisis estadístico no está instalado en su computadora y le solicita que lo instale, haga clic en el botón Sí.

Después de descargar el paquete de análisis en grupo. Análisis en la pestaña Datos el comando está disponible Análisis de los datos.

Nota. Para incluir funciones de VBA en su paquete de análisis, puede descargar el complemento Analysis ToolPak - VBA. Para ello, siga los mismos pasos que para descargar el paquete de análisis. En la ventana Complementos disponibles revisa la caja Paquete de herramientas de análisis - VBA y luego haga clic en el botón DE ACUERDO.

Comprar productos

Al realizar tareas analíticas complejas en estadística (por ejemplo, análisis de correlación y varianza, cálculos utilizando el algoritmo de Fourier, creación de un modelo predictivo), los usuarios suelen estar interesados en cómo agregar análisis de datos a Excel. El paquete de funciones designado proporciona herramientas analíticas versátiles útiles en diversas áreas profesionales. Pero no se aplica a las herramientas incluidas en Excel de forma predeterminada y que se muestran en la cinta. Descubramos cómo habilitar el análisis de datos en Excel 2007, 2010, 2013.

Para Excel 2010, 2013

Veamos el análisis de datos en Excel: cómo habilitarlo y en qué se diferenciará el procedimiento de activación para otras versiones. En la mayoría de las versiones del programa, el procedimiento se realiza de la misma forma. Por lo tanto, la secuencia de acciones descritas en esta sección es adecuada para la mayoría de las versiones, incluidas las de 2013 y 2016.

Habilitar la caja de herramientas

El paquete en cuestión pertenece a la categoría de complementos, es decir, complementos analíticos complejos. En consecuencia, para habilitar el paquete, vaya al menú de complementos. Este procedimiento se realiza de la siguiente manera:

vaya a la pestaña "Archivo" ubicada en la parte superior de la cinta de la interfaz;
en el lado izquierdo del menú de apertura, busque la sección "Opciones de Excel" y haga clic en ella;
mire el lado izquierdo de la ventana, abra la categoría de complementos (la segunda desde abajo en la lista), seleccione el elemento apropiado;
en el menú de diálogo que aparece, busque el elemento "Administración", haga clic en él con el mouse;
al hacer clic aparecerá un cuadro de diálogo, seleccione la sección de complementos, si el valor está establecido en algo distinto a “Complementos de Excel”, cámbielo al indicado;
Haga clic en el botón de pantalla "Ir" en la sección de complementos. Una lista de complementos que instala el programa aparecerá en el lado derecho.

Activación

Veamos cómo activar las funciones analíticas proporcionadas por el complemento del paquete:

En la lista de complementos que aparece después de la ejecución secuencial de operaciones anteriores, el usuario debe marcar la casilla junto a la sección "Paquete de análisis".
Habiendo elegido activar el paquete, deberás presionar el botón “Aceptar” ubicado en la parte superior derecha del cuadro de diálogo.
Después de hacer clic en el botón, el paquete aparece en la cinta de funciones. Para acceder a él, seleccione la pestaña "Datos" en la interfaz del programa. En el lado derecho del menú está la "Sección de Análisis". Allí el usuario encontrará el ícono de la opción “Análisis de datos”.

Lanzamiento de las funciones del grupo “Análisis de datos”

El paquete analítico opera con un gran conjunto de herramientas que optimizan la solución de problemas estadísticos. Algunos de los números:

operaciones con muestras;
construir un histograma: un tipo de gráfico de barras que demuestra la distribución de diferentes valores de un determinado parámetro en forma de columnas, cuyas áreas se correlacionan entre sí de la misma manera que los pesos específicos de diferentes grupos en el considerado elección;
generación de números aleatorios;
clasificación ordinal y porcentual;
variaciones de regresión, dispersión, correlación, análisis de covarianza;
análisis mediante el algoritmo de Fourier;
El suavizado exponencial es un método de transformaciones matemáticas con el objetivo de identificar una determinada tendencia o tendencia en una serie de tiempo. El método se utiliza para hacer pronósticos.

Para aplicar una u otra opción, siga el siguiente algoritmo:

Haga clic en el botón analizar en la cinta.
Haga clic en el nombre de la función requerida por el usuario.
Presione el botón "Aceptar" ubicado al lado de la esquina superior derecha de la ventana.
En el cuadro de diálogo, especifique las matrices de datos utilizadas para resolver el problema actual.

Las funciones incluidas en el paquete están diseñadas para utilizar números de una sola hoja de Excel. Si los valores estadísticos requeridos se colocan en varias hojas, primero deberá crear una tabla dinámica copiando allí los parámetros requeridos.

Para Excel 2007

El algoritmo para habilitar el análisis de datos en Excel 2007 se diferencia de los demás en que al principio (para acceder a las opciones de Excel), en lugar del botón "Archivo", el usuario hace clic en el símbolo de cuatro colores de Microsoft Office. Por lo demás, la secuencia de operaciones es idéntica a la indicada para otras versiones.

Tablas dinamicas

Para analizar listas de datos (tablas de datos) en Excel 2007 que tienen muchas filas o registros, a menudo se utilizan herramientas como tablas dinámicas. Las tablas dinámicas hacen que sea mucho más fácil ver, manipular y resumir datos en listas de Excel 2007.

Para que Excel trate los datos ingresados como una tabla de datos en lugar de un simple conjunto de datos, debe formatearlos como una tabla. Para hacer esto, haga clic en el botón Tabla ubicado en la pestaña Insertar. En el cuadro de diálogo Crear tabla (Figura 1), especifique el rango esperado en el que se colocará la tabla y seleccione la casilla de verificación Tabla con encabezados.

Arroz. 1.

El formato se aplicará a este rango, es decir. Se aplica el estilo de tabla express predeterminado y se activan las herramientas contextuales bajo el nombre general "Trabajar con tablas", que se incluyen en la pestaña Diseño contextual. También puede utilizar la herramienta Formatear como tabla en la pestaña Inicio para formatear una tabla.

Arroz. 2.

Para crear un informe sobre esta tabla, es recomendable utilizar la potente herramienta Tabla dinámica. Para aplicar esta herramienta a listas de datos o tablas de datos, debe activar una de las celdas de la tabla de datos, por ejemplo, la celda de la tabla "Artículos en stock en almacén". Luego haga clic en el botón "Tabla dinámica", que se encuentra en la pestaña "Insertar" en el grupo "Tabla" (Figura 3).

Arroz. 3.

En el cuadro de diálogo "Crear tabla dinámica" que se abre, debe seleccionar (seleccionar) una tabla o rango e indicar dónde colocar el informe (preferiblemente en una hoja nueva), luego hacer clic en Aceptar. Se abrirá un asistente especial para tablas dinámicas (Figura 4).

Arroz. 4.

El lado izquierdo de la hoja de trabajo muestra una imagen del informe (Tabla dinámica 1) y el lado derecho de la hoja contiene herramientas para crear una tabla dinámica: cuatro áreas vacías y una lista de campos. Para crear un informe, debe arrastrar los campos obligatorios del lado derecho a las áreas correspondientes de la tabla dinámica: "Filtro de informe", "Nombres de columnas", "Nombres de filas" y "Valores".

Por ejemplo, si selecciona los campos: Número de almacén, Nombre, Precio (UAH) y los arrastra a las áreas correspondientes: “Nombres de columnas”, “Nombres de filas” y “Valores”, se mostrarán en el lado derecho. en estas áreas. En este caso, se creará una tabla dinámica o un informe en el lado izquierdo de la hoja de trabajo (Fig. 5).

Arroz. 5.

Cabe señalar que en el área "Valores" se realizan algunos cálculos matemáticos, por ejemplo, la suma (campo Suma por Precio). Para cambiar el tipo de cálculos, en el área “Valores”, haga clic izquierdo en el campo “Campo Suma por precio” y en el menú que se abre, seleccione el comando “Opciones de campo de valor”, luego en el campo “Opciones de campo de valor” cuadro de diálogo, seleccione la función requerida y haga clic en el botón Aceptar.

Para cambiar la estructura de la tabla dinámica, debe arrastrar los campos del lado derecho de la hoja a otra área de la tabla dinámica o eliminarlos. Cabe señalar que para eliminar un campo, debes arrastrarlo fuera de la tabla.

Herramientas de Excel para análisis de datos y problemas de optimización.

Las poderosas herramientas de análisis de datos de Excel 2007 son:

análisis “qué pasaría si”, que incluye: selección de parámetros y administrador de escenarios;
complemento "Buscar una solución" (complemento Solver).

Las herramientas de análisis What-if se encuentran en la pestaña Datos del grupo Herramientas de datos y la Búsqueda de soluciones se encuentra en la pestaña Datos del grupo Análisis.

La selección de parámetros proporciona una solución al problema de la selección de parámetros para una función de un argumento. El Administrador de escenarios está diseñado para crear escenarios (primero agregando varios escenarios para diferentes casos) y luego revisar los escenarios para predecir el proceso y generar un informe sobre el escenario.

El programa Búsqueda de Soluciones está diseñado para resolver sistemas complejos de ecuaciones, problemas de optimización lineal y no lineal. El complemento Solver se basa en métodos iterativos.