13 Introducción a Excel

Excel es una herramienta útil para la gestión de datos ya que está presente en todas las empresas de alguna u otra forma.

En esta lección vamos a aprender lo siguiente:

Aprender los conceptos básicos sobre el uso de hojas de cálculo en Excel.
Familiarizarse con el formato de celdas y el uso de datos numéricos, de texto y fechas.
Realizar operaciones básicas como la introducción de fórmulas y el uso de funciones.
Crear gráficos y tablas dinámicas.
Usar herramientas avanzadas como la validación de datos y la eliminación de duplicados.

13.1 Conceptos Básicos de Excel

13.1.1 Estructura de Excel

Excel organiza los datos en hojas de cálculo que consisten en filas y columnas. Las intersecciones entre filas y columnas se denominan celdas y son donde se ingresan los datos. Las filas se identifican con números (1, 2, 3,…) y las columnas con letras (A, B, C,…).

13.1.2 Tipos de Datos en Excel

Las celdas de Excel pueden contener diferentes tipos de datos:

Texto: Cualquier combinación de letras y números.
Números: Valores numéricos que se pueden utilizar para realizar cálculos.
Fechas: Valores que representan fechas y horas específicas.

Para ingresar cualquier tipo de dato, basta con seleccionar la celda deseada y comenzar a escribir. Una vez terminado, presiona Enter para confirmar la entrada.

Ejemplo 13.1

Selecciona la celda A1 e ingresa el texto “Ventas del mes”.
Selecciona la celda B1 e ingresa el valor 1000.
Selecciona la celda C1 e ingresa la fecha 01/10/2024.

13.1.3 Formato de las Celdas

El formato de las celdas permite dar una presentación adecuada a los datos. Puedes modificar aspectos como:

Formato de número: Para mostrar datos como porcentajes, monedas o fechas.
Fuente: Cambiar el tipo, tamaño o estilo de la fuente (negrita, cursiva, subrayado).
Colores: Aplicar colores de fondo o de fuente para resaltar información.

13.1.3.1 Cómo Aplicar un Formato

Selecciona la celda o rango de celdas que deseas formatear.
Ve a la pestaña Inicio.
Utiliza las opciones en el grupo Fuente o Número para cambiar el formato según sea necesario.

Ejercicio 13.1

Selecciona las celdas B1:C1.
Cambia el formato del número en B1 a Moneda.
Cambia el formato de la fecha en C1 para mostrar el mes con letras completas.

13.2 Uso de Fórmulas Básicas

Excel permite realizar cálculos matemáticos mediante el uso de fórmulas. Todas las fórmulas comienzan con el signo igual (=).

13.2.1 Operadores Básicos

+: Suma
-: Resta
*: Multiplicación
/: División

Ejemplo 13.2 Para sumar dos números:

Selecciona la celda D1.
Escribe la fórmula =B1+500.
Presiona Enter.

La celda D1 mostrará el resultado de la suma del valor en B1 más 500.

13.3 Funciones en Excel

Las funciones son herramientas predefinidas que permiten realizar cálculos complejos de manera más sencilla.

13.3.1 Función SUMA

La función SUMA se usa para sumar un rango de celdas.

Sintaxis: =SUMA(rango)

Ejemplo 13.3

Ingresa valores en las celdas A2, A3, y A4.
Selecciona la celda A5 e ingresa la fórmula =SUMA(A2:A4).
Presiona Enter.

La celda A5 mostrará la suma de los valores ingresados en las celdas A2, A3 y A4.

Ejercicio 13.2 Con el archivo adjunto realiza las siguientes tareas:

Calcula el promedio, mediana, desviación estándar, máximo y mínimo de los datos en la columna de Notas.
Cuenta con COUNTA automáticamente cuantos registros tiene la tabla.
Usando la función AVERAGEIF, calcula el promedio de las notas solo de la carrera de Letras y Ciencias por separado.
Haz lo mismo para las clases A, B y C.
Utiliza la función VLOOKUP para encontrar la clase y la nota de Raúl.

Ejercicio 13.3 Con este archivo adjunto realiza las siguientes tareas:

Si Juan mide más de 180 quiero que me de como resultado la altura de Pablo, sino, la de Javier.
Si el pelo de Juan es Castaño entonces quiero que me devuelva “Castaño” y sino quiero que devuelva “Otro”.
Si Juan pesa más que Pablo entonces quiero saber el color de ojos de Juan, sino, los de Pablo.
Si Javier es mayor (en edad) que Juan, entonces quiero saber la suma de la edad de Javier y Juan, sino, la media de la edad.
Si Juan o Pablo son Rubios entonces quiero que devuelva “OK”, sino, “NO OK”.
Si Pablo tiene un pie más grande que la mano entonces quiero que me de su altura, sino que me de el color de sus ojos.
Si Juan y Pablo tienen los ojos verdes entonces que devuelva “Verde”, sino, que devuelva el color de los ojos de Javier.
Si Juan, Pablo o Javier pesan más de 100 kilos que ponga “Más de 100” sino, que ponga “Menos de 100”.
Si la altura de Juan es mayor de 180 y la de Pablo de 160 que ponga “Altos”, sino “No clasificados”.
Si la altura de Juan es mayor de 180 o la de Pablo menor de 180 entonces que ponga “Juan más alto”, sino que ponga “Juan es más bajo”.

Ejercicio 13.4 Con este archivo adjunto se nos pide rellenar las tablas con la función SUMIFS.

Estudiemos el primer caso

=SUMIFS($G:$G;$C:$C;$L4;$H:$H;M$3)

Y el resto ustedes lo completan.

Ejercicio 13.5 Una empresa de telas ha recibido durante los años 2012 y 2013 1000 incidencias. Se quiere analizar cómo se han gestionado estas incidencias, para saber si deben modificar el contrato con la empresa que les gestiona el servicio de atención al cliente.

Además, quieren saber cómo es la evolución de las incidencias a lo largo del tiempo así como el nivel de respuestas en tiempo que se han dado a las incidencias por meses. También quieren hacer un análisis por departamentos de la situación de cada uno de ellos.

Entonces se nos pide lo siguiente con la siguiente base de datos:

Número de incidencias mensuales.
Porcentaje de respuestas contestadas en tiempo mensualmente.
Tendencias de los indicadores anteriores.
Análisis segmentado por departamento.
Análisis segmentado por Tipo de Incidencia.
Media de retraso por tipo de incidencia.
Tiempo medio de respuesta por tipo de incidencia.
Tiempo medio de respuesta por departamento.
Desviación de los tiempos medios de respuesta respecto a los tipos de incidencia.

Busque la ayuda de las siguientes funciones LEFT, VLOOKUP, YEAR, MONTH, AVERAGEIF, COUNTIF.

13.4 Tablas dinámicas

Las tablas dinámicas funcionan para analizar tablas grandes en Excel. Con una serie de botones, puedes ordenar, contabilizar y resumir la tabla de datos.

Para crear una tabla dinámica en Excel, sigue los siguientes pasos:

Seleccionar los datos: Selecciona el rango de celdas que contiene la información. Recomiendo convertir este rango en una tabla de datos (Insertar > Tabla) para facilitar futuras actualizaciones.
Insertar la Tabla Dinámica: Selecciona la tabla creada y luego haz clic en Insertar > Tabla Dinámica. Esto abrirá una ventana para elegir el rango y la ubicación de la tabla dinámica.
Configurar la Tabla Dinámica: Una vez creada, aparecerá una nueva hoja con el área de la tabla dinámica y un panel a la derecha que permite organizar los campos de la tabla dinámica.

Al crear una tabla dinámica, verás una serie de áreas donde puedes organizar los datos:

Filtros de Reporte: Permite filtrar la tabla dinámica seleccionando uno o más elementos específicos.
Columnas: Los datos seleccionados aquí se organizarán por columnas.
Filas: Los datos seleccionados aquí se organizarán por filas.
Valores: Aquí se realizan los cálculos, como suma, promedio, contar, etc.

Ejercicio 13.6 Consideremos una tabla de ventas donde se registran las transacciones de varios comerciales en diferentes meses y productos.

Genera una tabla dinámica con las siguientes caracteríticas:

Valores: Suma de las ventas (precio) conseguido según los filtros seleccionados.
Filtro de reporte: Mes Enero.
Etiquetas de filas: Vendedores
Etiquetas de columnas: Productos.

Además se puede cambiar la forma en que se calcula la tabla dinámica.

Haz clic en la flecha al lado del campo de valor.
Selecciona Configuración de campo de valor.
Cambia la opción Mostrar valores como a % de Gran Total.

Excel ofrece dos pestañas adicionales en la cinta de opciones al trabajar con tablas dinámicas: Analizar y Diseño.

Pestaña Analizar: Permite actualizar los datos, cambiar la fuente de datos, y añadir campos calculados.
Pestaña Diseño: Permite cambiar el diseño visual de la tabla dinámica, incluyendo subtotales y totales generales.

Además, se puede incluir el Mes en las filas para un reporte más completo.

13.5 Gráficos en Excel

Ejercicio 13.7 En el ejemplo de las quejas teléfonicas, se nos pide que convirtamos todas los análisis realizados en gráficos.

Ejercicio 13.8 Con este archivo se nos pide que hagamos pero con interactividad.

13.6 Solver

Ejercicio 13.9 Se tiene el archivo adjunto donde una empresa vende 3 productos y tiene mapeado cuales son sus costes de producción, horas de producción y gastos de cada producto.

Ellos tienen como restricción el total de horas de producción no puede ser más que 200,000 y que el número máximo de unidades vendidas no puede ser más de 40,000. Además no queremos perder en la fabricación de cada producto.

Le gustaría hacer varios escenarios en caso de que la actividad incremente 1%, 3% y 5% y saber cuantos productos debería de cada clase para maximizar la ganancia.

Además, le gustaría saber como se vería afectada sus ventas si el tipo de cambio aumenta a 6, 7 y 8.

Haga un análisis de sensibilidad con el solver de Excel y compare los resultados.