"SEC | S20W2 | Data Analysis with Google Sheets: (Advanced Excel formulas, and pivot tables.)

graceleon (79)in #spreadsheet-s20w2 • last year

En este espacio nos sumergiremos en el amplio mundo de las fórmulas avanzadas de la hoja de cálculo y las tablas dinámicas.

Explique lo que entiende por fórmulas avanzadas de Excel y muéstrenos dónde se encuentran fórmulas avanzadas como la función de búsqueda y la función lógica en Excel con capturas de pantalla claras.

Las fórmulas avanzadas de Excel nos permiten procesar datos de una forma más precisas y eficaz; mucho más allá de las operaciones aritméticas con las cuales ya estamos familiarizados; una fórmula avanzada nos da la oportunidad de proporcionar información que será procesada y dará un resultado elaborado, a partir de los datos existentes, los cálculos integrados permiten que se pueda filtrar datos, duplicar, entre otras soluciones de información. Ellas abren el camino para realizar cálculos y operaciones complejas; generar reportes con datos bien procesados.
Entre las fórmulas avanzadas más conocidas están la de lógica y la de búsqueda.

Función Lógica: nos permite evaluar el estado de un dato; si es verdadero, falso, y a partir de el mismo proporcionar un resultado; ideal para hacer pruebas lógicas y es una de las funciones más potentes de Excel.

Les muestro a continuación el proceso:
- Abrir la hoja de cálculo de Excel
- En la barra de tareas, buscar la pestaña de Formulas
- Seleccionar la opción Lógica y allí se despliegan las posibles opciones a seleccionar.

Función Búsqueda: nos permite encontrar un dato a partir de la búsqueda por fila o columnas según sea el caso.

Les muestro a continuación el proceso:
- Abrir la hoja de cálculo de Excel
- En la barra de tareas, buscar la pestaña de Formulas
- Seleccionar la opción Búsqueda y referencias y allí se despliegan las posibles opciones a seleccionar. Entre las más populares están la BUSCARH que es la búsqueda de una fila, BUSCARV que es la búsqueda de una columna en particular.

Escriba la fórmula de la función SI para calcular el puntaje total, promedio y la calificación de los estudiantes que se muestran en la siguiente tabla.

En el siguiente ejercicio, aplicaremos la función SI para determinar si el alumno aprobó o no el curso y en que rango de calificación está. Pero antes vamos a calcular el total de sus calificaciones para determinar su promedio y seguidamente ver en que rango aprobación se encuentra.

Calculamos la suma total de las calificaciones obtenidas en cada materia por alumno.

Luego calculamos el promedio de calificaciones por alumno.

Y ahora usamos la función lógica para determinar el rango de aprobación por alumno:

Hacemos uso de la formula IF o "SI" explicada en la clase, y estos son los % establecidos para aprobar o reprobar:

90 -100 = A
89 - 70 = B
69 - 59 = C
58 - 49 = D
48 - 40 = E

Aplicamos la fórmula:

=SI(G2>=90;"A";SI(G2>=70;"B";SI(G2>=59;"C";SI(G2>=49;"D";SI(G2>=40;"E")))))

Y así obtenemos la ponderación de la calificación de cada estudiante. Arrastramos la fórmula para que todos tengan su grado

Al tener esto, podemos hacer un correcto y acertado análisis de datos; por ejemplo, podemos ver que el promedio de notas de Josepha es de 70,25 y por lo tanto su grade de calificación es: C por estar dentro del rango de mayor a 70 y menor a 80.

Analice brevemente cuatro operadores de función SI que haya aprendido y díganos cuáles son sus funciones y cuándo debemos usarlos.

Igual a (=): indica si un valor es igual al otro.
Mayor que (>): el símbolo** >** nos indica que el valor debe ser mayor que el número establecido. Ejemplo > 10.
Menos que (<): El símbolo < nos indica que debe ser menor al número establecido.
El mayor o igual a (>=): la combinación de >= indica que el valor debe ser mayor o igual que el número establecido.
No es igual a (<>): Indica que debe ser diferente al número o valor que se indica
El menor o igual a (<=): debe ser menor o igual que el número que se indica.

Un ejemplo práctico:

Para saber si el estudiante aprobó o no la materia English, debemos preguntar si su calificación es >= a 50.

=SI(C2>=50;"APROBADO";"REPROBADO")

Otro ejemplo práctico:

Para determinar si el promedio de un estudiante es bueno o excelente. Será Excelente si su promedio es superior a 70. Será Bueno si es inferior a 70.

=SI(G3<70;"BUENO";"EXCELENTE")

Con base en los datos que se proporcionan a continuación: cree una tabla dinámica que muestre (vea) las ventas totales por producto, arrastrando el producto al área de Filas, la Región al área de Columnas y las Ventas al área de Valores. Queremos ver los pasos que se siguen para agregar la tabla dinámica.

Al tener los datos suministrados en el ejercicio, procedemos a copiarlos en la matriz de la hoja de cálculo.

Seguidamente nos vamos a la barra de tareas y allí le damos click a Insertar; allí vamos a agregar la tabla dinámica.

Al definir la ubicación en la tabla pivote, coloqué que sería en la misma hoja de cálculo Hoja2 y en la matriz coloqué que sería en la columna H fila 5.

Luego aparece la tabla pivote que vamos a trabajar organizando los datos:

Elegí los datos que vamos a trabajar: producto, Región y Ventas
Y arrastre Producto a filas y Región a columnas.

Hago un acercamiento a nuestra nueva Tabla Pivote o tabla dinámica que hemos creado para este ejercicio:

Bien en esta lección hemos aprendido muchas cosas interesantes, el uso de tablas dinámicas, que nos permiten hacer un mejor análisis de datos y obtener conclusiones confiables; el uso de las fórmulas avanzadas lógicas y algunos ejercicios para reforzar conocimientos.

A la expectativa de lo que nos deparan las siguientes lecciones.

Quiero invitar a participar a @ciru2014, @noelisdc y @oneldoors en el "SEC | S20W2 | Análisis de datos con hojas de cálculo de Google: (Fórmulas avanzadas de Excel y tablas dinámicas).

Todas las imágenes son de mi autoría hechas con captures de pantalla de mi trabajo en Excel y editadas e Canva.