EXCEL Intermedio

E x c e l Intermedio

Bienvenidos

Dra. Minerva Elizabeth Maya Yescas.

DATOS

CORREO ELÉCTRÒNICO minemayamx@yahoo.com.mx NÚMERO DE CELULAR 55 40 61 73 65

OBJETIVO

Los estudiantes aprendan conceptos intermedios utilizando Excel, se requiere una comprensión de los conceptos básicos, para que en el presente curso, aprendan a usar las funciones de la hoja de cálculo, con un enfoque en el análisis de datos, operaciones matemáticas y manejo avanzado de datos.

Definición. Aplicaciones

Dinámica de la clase

11:20 - 12:20

12:40 - 13:00

10:00 - 11:00

9:00 - 9:40

12:20 - 12:40

11:00 - 11:20

9:40 - 10:00

ÍNDICE

Fórmulas de aritmética en Excel

Objetivo y Definición

Dinámica de la clase

Libro de trabajo

Uso del teclado

Análisis de datos

SOLVER

Gestionar Datas

Repaso general

Conclusiones

Siguiente curso

10

12

Agradecimiento

11

Fórmulas de aritmética en Excel

Con el objetivo de adquirir experiencia en el empleo de ecuaciones en una hoja de cálculo, proceda a resolver los siguientes ejercicios de matemáticas.

Redondeo

Ordenar datos

MODA

Cálculo de porcentajes

uso del teclado

Abrir/ Cerrar un archivo

Inicializar Excel automáticamente

Borrar, Imprimir, Copiar, Pegar y Cortar

Selección de filas y columnas

Elección del formato de celdas

Corte de filas y su recuperación

Letras en cursiva

Letras en negrita

Subraya

Buscar o Reemplazar

11

Tacha lo seleccionado

10

Selección de datos

12

Copiar hoja de cálculo en el mismo libro

15

Guardar el archivo

13

Revisar ortografía

14

Libro de trabajo

Diseño de página

Tabla dinámica

Ejemplo 1

Ejemplo 2

Ejercicio 1

Ejercicio 2

Análisis de datos

Mediante el análisis de datos, tanto en empresas como en laboratorios o en investigación, se pueden obtener los datos necesarios para agilizar la toma de decisiones y crear estrategias basandose en criterios sólidos.

Activar opción

Valores duplicados

Histograma

Estadística descriptiva

Análisis de datos

Buscador Dinámico

Nombres duplicados

Dinámica de la clase por el eclipse solar anular

11:30 - 12:30

12:50 - 13:00

10:10 - 11:10

9:00 - 9:20 9:20 - 9:50

11:10 - 11:30

12:30 - 12:50

9:50 - 10:10

Gestionar volúmenes significativos de información de manera efectiva

Solver

KPI

Histograma

Solver es un complemento de optimización que está disponible en Microsoft Excel.

Key Performance Indicator

Análisis de datos en Excel

En Excel, puedes crear tablas de datos, gráficos y fórmulas para calcular y visualizar KPIs que son importantes para tu negocio.

Solver es una herramienta poderosa para la toma de decisiones cuantitativas y la resolución de problemas de optimización.

Solver

Activación

Componentes

Métodos de resolución

GNG Nonlineal

Simplex LP

Evolutionary

Problema

Proceso paso a paso:

Siguiente Curso :

Excel

Avanzado

Inicio: 4 de Noviembre 2023

¡GRACIAS!

Diseño de página

Tamaño de hoja.Se encuentra en "Disposición de hoja" Márgenes: Normal, Anchos, Estrechos, personalizado Ajustar al área de impresión En la pestaña "Disposición de página" se selecciona previamente el área y después se elige "Ajustar al área de impresión " Saltos Fuente,Color de la fuente, Color de la celda

Inmovilizar columnas y filasEn la pestaña "Vista" seleccionamos "Inmovilizar páneles"

Lorem ipsum dolor

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod.

Lorem ipsum dolor sit

• Lorem ipsum dolor sit amet.
• Consectetur adipiscing elit.
• Sed do eiusmod tempor incididunt ut.
• Labore et dolore magna aliqua.

Cálculo de porcentajes

Nota importante:

- Comando Power Pivot para grandes volumenes de datos -Análisis de datos de forma eficaz - Creación de modelos de datos sofisticados Se sustituye por Solver Que es una herramienta poderosa que permite a los usuarios resolver problemas complejos de optimización y tomar decisiones informadas basadas en los resultados obtenidos.

Tabla dinámica

Una tabla dinámica se compone de cuatro componentes fundamentales: campos de filas, campos de columnas, valores numéricos y campos de filtro. Agregar datos en cada campo Para agregar campos a la tabla dinámica, empleando la sección de categorías en la lista de elementos, activando la casilla junto a los nombres de categoría para ubicarlos en la región estándar de la lista de elementos. Partes de una tabla dinámica: Cuando se crea una tabla dinámica, se muestra una lista de campos disponibles y las áreas donde se pueden acomodar. Eso es lo que permite que se pueda comparar datos de la base.

Filtro de informe. Los campos que se añadan aquí crearán filtros para la tabla dinámica con el fin de que se pueda restringir la información que ve en pantalla.

Etiquetas de columna. Son los campos que se mostrarán como columnas de la tabla dinámica. Etiquetas de fila. Se trata de los campos que determinan las filas de la tabla dinámica. Valores. Son los campos que se colocarán como las celdas de la tabla dinámica y que serán totalizados para cada columna y fila.

Corte de filas y su recuperación

Después de presionar Ctrl + 9, las celdas que hayamos seleccionado previamente quedarán ocultas. En las filas se ve la numeración “cortada” y una linea más ancha. Si aumentamos el zoom en esa línea, veremos dos líneas. Para evitar que se oculten, selecciona las filas que se encuentran entre esa línea, luego haz clic en el botón derecho del ratón y selecciona la opción "Mostrar".

Selección de filas

Haz clic en una celda de la fila y luego presiona Shift (Mayúsculas) + Barra espaciadora.

Selección de Columnas

Haz click en cualquier celda de la columna, y luego presionaCrtl + Barra espaciadora La selección de filas y columnas se puede con el mouse

Subraya

Crt + S Subraya la celda que tiene escrito que estamos ubicados o a al conjunto de celdas que marcamos previamente Para deshabilitarla se vuelve a presionar CRTL + S

También CTRL + 4

Resumen por especie y tipo de hábitat:

Insertar -> Tabla dimámica (rango)

Tabla dinámica con las siguientes configuraciones: • Arrastra "Peso" a la fila de etiquetas de columna. • Arrastra "Altura" a la fila de etiquetas de fila. • Arrastra "Especie" a la sección de valores y configura la función para calcular el promedio de Especie. Es una tabla dinámica que muestra cómo se relaciona el peso con la altura para cada especie. Se puede utilizar esta tabla para identificar patrones o tendencias en la relación entre estas dos variables biológicas.

Inicializar Excel automáticamente

Instrucciones:

- En el buscador de Windows escribe EXCEL - Haz click botón derecho sobre el ejecutable de Excel - Elije “Abrir ubicación del archivo” - Sobre Excel después haz click el botón derecho - Elije PROPIEDADES - En la opción que dice “Tecla de método Abreviado” dice NINGUNO coloca el cursor y presiona la siguiente combinación de teclas*, “Crtl + Alt + E” *Puedes elegir otras después

• Darle click en "Aplicar"

• Darle click en "Continuar"
• Darle click en "Aceptar"
• Cerrar todo para probar si inicia con Crtl + Alt + E el Excel

Selección de datos

Seleccionar los datos de la fila superior con SHIFT, sin dejar de teclear SHIFT, para bajar hasta el último dato después se teclea CTRL + flecha hacia abajo ó CTRL + flecha hacia arriba

Ejemplo: Método GNG Nonlineal Optimización de la Producción con Restricciones de Recursos Una fábrica que produce dos tipos de productos A y B. Se requieren: mano de obra y materia prima. Cada unidad de A requiere 2 horas de mano de obra y 3 unidades de materia prima, mientras que para el producto B se requiere 3 horas de mano de obra y 2 unidades de materia prima.

Solver GNG Nonlineal: Es un enfoque iterativo que se utiliza para resolver problemas de optimización no lineal con restricciones. En cada iteración, el método ajusta las variables del problema para mejorar la función objetivo, teniendo en cuenta las restricciones no lineales.

Solver Elementos: Objetivo: Medida de desempeño de la decisión Parámetros: Valores fijos que no varían según las elecciones que hagamos. Variables: Valores numéricos que están sujetos a nuestras decisiones y que pueden ser influenciados por estas. Restricciones: Posibles limitaciones que presenta mi decisión.

Solver Elementos: Método de Resolución: Simplex LP, GRG Nonlinear, Evolutionary

Ejemplo: Método Evolutionary. Para encontrar la mejor ruta de entrega de paquetes Minimizar el tiempo considerando todas las restricciones

Solver Evolutionary Optimización Realiza varias iteraciones

MODA

Power Pivot

Power Pivot se presenta como una herramienta dentro de Excel diseñada para llevar a cabo análisis de datos efectivos y construir modelos de datos avanzados. Con Power Pivot, es posible integrar extensos conjuntos de datos provenientes de diversas fuentes, agilizar el proceso de análisis de información y compartir perspectivas con facilidad. Ya sea en Excel convencional o en Power Pivot, se tiene la capacidad de crear un modelo de datos, que es básicamente una colección de tablas interconectadas. El modelo de datos que se visualiza en una hoja de Excel es el mismo que se encuentra en la interfaz de Power Pivot.

Copiar hoja de cálculo en el mismo libro

• - Haz clic en la pestaña de la hoja de cálculo con el botón derecho del ratón
• - Elige la opción Mover o Copiar.
• - Activa la casilla que dice "Crear una copia".
• - En la sección que dice "Antes de la hoja", elige la ubicación donde deseas que se ubique la copia. Finalmente, selecciona el botón de "Aceptar".

Lorem ipsum dolor

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod.

Lorem ipsum dolor sit

• Lorem ipsum dolor sit amet.
• Consectetur adipiscing elit.
• Sed do eiusmod tempor incididunt ut.
• Labore et dolore magna aliqua.

2. ORDENAR DATOS

Borrar, Imprimir, Copiar, Pegar y Cortar

Teclas de mayor uso en Microsoft : Word, Excel, Power Point etc

Tabla dinámica

La característica de tabla dinámica en Microsoft Excel se destaca como una de las herramientas más robustas, ya que su fortaleza reside en su capacidad para resumir y estructurar grandes conjuntos de datos. En este curso, te guiaremos para que puedas crear una tabla dinámica en Excel de forma más accesible y con mayor claridad. Una tabla dinámica es una presentación condensada de información, reunida en una sola tabla, que te facilita la tarea de analizar y descubrir patrones en tus datos.

Resultan especialmente útiles cuando se trabaja con conjuntos de datos extensos y se requiere obtener rápidamente cantidades y realizar comparaciones entre elementos en filas o columnas largas.

Formato de celda

Crt + 1 Sale la ventana Formato de la celda que estamos ubicados o a un conjunto de celdas que marcamos previamente

Tema Nuevo

• Ejemplo de Tabla dinámica 1: Resumen de ventas mensuales por producto:

Crea una tabla dinámica con las siguientes configuraciones: Coloca "Mes" en la fila de etiquetas de fila. Coloca "Producto" en la columna de etiquetas de columna. Coloca "Ventas" en la sección de valores y configura la función para calcular la suma de ventas. Se obtiene una tabla dinámica que muestra las ventas totales de cada producto durante cada mes. También se puede ver cómo varían las ventas de productos a lo largo del tiempo y se determina cuál es el producto más vendido en cada mes.

En la pestaña "Herramientas de tabla dinámica", busca el grupo "Funciones" o "Resumen de valores" (el nombre exacto puede variar según la versión de Excel). Dentro del grupo "Funciones" o "Resumen de valores", verás una lista desplegable con diferentes funciones de resumen, como suma, promedio, máximo, mínimo, etc. Selecciona "Suma" para calcular la suma de las ventas. Una vez seleccionada la función de suma, Excel calculará automáticamente la suma de las ventas para cada elemento de la tabla dinámica, ya sea por mes, producto u otra categoría que hayas utilizado en la tabla.

Conclusiones de los temas vistos en clase

Lo importante es que todo se adecúe al tema.

Para ponerle promedio de la Edad se realiza lo siguiente: • Haz clic con el botón derecho del ratón en la columna "Edad" dentro de la tabla dinámica. • Se abrirá un menú contextual. Selecciona "Configuración de campo de valor" o una opción similar (la terminología puede variar según la versión de Excel). • Aparecerá una ventana emergente llamada "Configuración de campo de valor". Aquí puedes definir la función de resumen que deseas aplicar a los datos en la columna "Edad". En este caso, selecciona "Promedio" o "Promedio (edad)" en la lista de funciones de resumen. • Haz clic en "Aceptar" o "OK" para aplicar la función de promedio a la columna "Edad".

Resume por especie y tipo de hábitat:

Insertar -> Tabla dinámica (rango)

Crea una tabla dinámica con las siguientes configuraciones: • Arrastra "Especie" a la área de colocación de fila. • Arrastra "Tipo de Hábitat" a la área de colocación de fila debajo de "Especie". • Arrastra "Edad" a la área de colocación de valores y configura la función para calcular el promedio de edad. • Arrastra "Peso" a la área de colocación de valores y configura la función para calcular el promedio de peso. Ahora se tendrá una tabla dinámica que muestra el promedio de edad y peso de cada especie en cada tipo de hábitat. Se puede usar esta información para comparar cómo la edad y el peso promedio varían entre especies y hábitats.

Problema

Se requiere encontrar la cantidad de cada producto que se debe vender para maximizar los ingresos, pero se tiene la restricción de que solo se puede vender un máximo de 100 productos en total. Se puede usar Solver para resolver este problema de optimización.

Tema Nuevo

Tacha lo seleccionado

Crt + 5 Tacha la celda que tiene escrito que estamos ubicados o a al conjunto de celdas que marcamos previamente Para deshabilitarla se vuelve a presionar CRTL + 5

Letras en cursivas

Crt + K Pone en cursiva la celda que estamos ubicados o a al conjunto de celdas que marcamos previamente Para deshabilitarla se vuelve a presionar CRTL + K

También CRTL + 3

Repaso de la clase anterior y/o dudas

KPI "Key Performance Indicator" significa "Indicador Clave de Desempeño" o "Indicador Clave de Rendimiento". Es una métrica o medida que se utiliza para evaluar y monitorear el rendimiento de una empresa, un proyecto, un proceso o cualquier otra actividad. Estos indicadores ayudan a las organizaciones a medir su progreso hacia los objetivos y metas establecidos.

• Ejemplo 2: Análisis de la utilidad mensual:

Crea una tabla dinámica con las siguientes configuraciones: • Coloca "Mes" en la fila de etiquetas de fila. • En la sección de valores, coloca "Ventas," "Gastos," y "Utilidad" y configura la función para calcular la suma de cada una de estas columnas. Se genera una tabla dinámica que muestra la suma de las ventas, gastos y utilidad para cada mes. Se puede usar esta tabla para que se realice un seguimiento de la rentabilidad mensual y se analice cómo las ventas y los gastos afectan a la utilidad a lo largo del tiempo.

3) Filtrar y segmentar datos: Puedes filtrar y segmentar datos de diferentes maneras en una tabla dinámica para centrarte en información específica. Esto te ayuda a identificar tendencias, patrones y valor atípico en tus datos. 4) Crear informes y presentaciones: Las tablas dinámicas son ideales para crear informes y presentaciones de datos de manera profesional. Puedes diseñar informes personalizados y gráficos dinámicos que se actualizan automáticamente cuando cambian los datos subyacentes. 5) Agregar interactividad: Puedes agregar interactividad a tus informes y paneles utilizando segmentadores y controles de filtro en las tablas dinámicas. Esto permite a los usuarios seleccionar y explorar datos de manera más dinámica. En la hoja de Excel se realiza lo siguiente:

Creación de tablas dinámicas

Las tablas dinámicas en Excel son una poderosa herramienta que se utiliza para resumir, analizar y visualizar datos de manera eficiente. Tienen utilidad en: 1) Resumir datos: Permiten resumir grandes conjuntos de datos en una tabla más pequeña y manejable. Puedes agrupar datos, calcular sumas, promedios, contar valores y realizar otras operaciones de resumen fácilmente. 2) Analizar datos: Las tablas dinámicas facilitan la exploración de datos al permitirte reorganizar y recalcular rápidamente la información. Puedes cambiar la forma en que se muestran los datos y realizar análisis en profundidad de manera interactiva.

Insertar -> Tabla dimámica (rango)

Realizar una búsqueda y eliminar elementos duplicados

Implica seguir estos pasos: A) Elija las celdas que desea examinar. B) Vaya a la pestaña 'Inicio' y seleccione 'Formato condicional'. A partir de ahí, acceda a 'Resaltar reglas de celdas' y elija 'Valores duplicados'. C) En el cuadro que aparece al lado de 'valores con' D) Seleccione el formato que desea aplicar a los valores duplicados y, finalmente, haga clic en 'Aceptar'."

Tema Nuevo

Ejemplo: Método Simplex para resolver un problema de programación lineal en Excel.

Solver Simplex LP: Los problemas que implican restricciones lineales y enteras se resuelven mediante el Método Simplex, que establece límites en las variables, y también mediante el método de ramificación y límite, conocido como "branch and bound".

Procedimiento

Se requiere encontrar la cantidad de cada producto que se debe vender para maximizar tus ingresos, pero se tiene la restricción de que solo se puede vender un máximo de 100 productos en total. Se puede usar Solver para resolver este problema de optimización. Aquí está el Aquí está el proceso paso a paso: En una celda, calcula los ingresos totales para cada producto multiplicando la cantidad de ese producto por su precio. Por ejemplo, puedes colocar la siguiente fórmula en la celda C2 y arrastrarla hacia abajo:

• Lorem ipsum dolor sit amet.
• Consectetur adipiscing elit.
• Sed do eiusmod tempor incididunt ut.
• Labore et dolore magna aliqua.

Redondeo (Funciones)

• REDONDEAR REDONDEAR (nùmero, núm_decimales)
• REDONDEAR.MAS (nùmero, num_decimales)

• REDONDEAR.MENOS (nùmero, núm_decimales)

• REDOND.MULT (nùmero, múltiplo)

• REDONDEA.PAR ( número )
• REDONDEA.IMPAR ( número )
• ENTERO (nùmero)
• TRUNCAR (NÚMERO,(NUM_DECIMAL))

Guardar como...

Guardar

Repaso de la clase anterior y/o dudas

Ejemplo

Se lleva a cabo un estudio sobre las alturas de los árboles en un bosque. Utilizando un histograma, es posible observar la distribución de las alturas . Esta herramienta facilita la identificación rápida de si la mayoría de los áboles son altos, bajos o si hay una distribución uniforme en cuanto a las alturas.

Tema Nuevo

Buscar o Reemplazar

Sale la ventana de buscar o reemplazar Crt + B ó Crt + F

Letras en Negritas

Crt + NPone en negrita la celda que estamos ubicados o a al conjunto de celdas que marcamos previamente Para deshabilitarla se vuelve a presionar CRTL + N

También CRTL + 2

Solver Puedes utilizar Solver en Excel para una variedad de aplicaciones, como la programación lineal, la asignación de recursos, la planificación de la producción, la gestión de carteras de inversión y otros problemas de toma de decisiones que involucran la optimización de variables en una hoja de cálculo.

Para utilizar Solver, generalmente debes definir: La celda objetivo: Esta es la celda que deseas optimizar, como maximizar ganancias o minimizar costos. Las celdas variables: Estas son las celdas que puedes ajustar para lograr el objetivo. Restricciones: Estableces restricciones en las celdas variables para reflejar las limitaciones del problema.

Conclusiones de los temas vistos en clase

Revisor de ortografía

Abre Excel y Desde la pestaña "Archivo" (o "Inicio" en versiones anteriores) -> "Opciones" -> "Complementos" -> "Solver Add-In".