Búsquedas, gráficos, estadísticas y tablas dinámicas
Habiendo revisado las funciones básicas, las referencias de celdas y las funciones de fecha y hora, ahora nos sumergimos en algunas de las características más avanzadas de Microsoft Excel. Presentamos métodos para resolver problemas clásicos en finanzas, informes de ventas, costos de envío y estadísticas..
NAVEGACION ESCOLAR- ¿Por qué necesitas fórmulas y funciones??
- Definiendo y creando una fórmula
- Referencia de celda relativa y absoluta, y formateo
- Funciones útiles que deberías conocer
- Búsquedas, gráficos, estadísticas y tablas dinámicas
Estas funciones son importantes para los negocios, los estudiantes y aquellos que solo desean aprender más.
VLOOKUP y HLOOKUP
Este es un ejemplo para ilustrar las funciones de búsqueda vertical (VLOOKUP) y búsqueda horizontal (HLOOKUP). Estas funciones se utilizan para traducir un número u otro valor en algo que es comprensible. Por ejemplo, puede usar VLOOKUP para tomar un número de pieza y devolver la descripción del artículo.
Para investigar esto, volvamos a la hoja de cálculo de "Decision Maker" en la Parte 4, donde Jane está tratando de decidir qué ponerse en la escuela. Ya no está interesada en lo que lleva puesto, ya que ha conseguido un nuevo novio, por lo que ahora llevará ropa y zapatos al azar..
En la hoja de cálculo de Jane, ella enumera conjuntos en columnas verticales y zapatos, columnas horizontales.
Abre la hoja de cálculo y la función RANDBETWEEN (1,3) genera un número entre o igual a uno y tres correspondientes a los tres tipos de atuendos que puede usar..
Ella usa la función ALEJADA (1,5) para elegir entre cinco tipos de zapatos.
Como Jane no puede usar un número, necesitamos convertirlo en un nombre, así que usamos funciones de búsqueda.
Usamos la función VLOOKUP para traducir el número del atuendo al nombre del atuendo. HLOOKUP se traduce del número de zapato a los diferentes tipos de zapatos en la fila.
La hoja de cálculo funciona así para equipos:
Excel elige un número aleatorio de uno a tres, ya que tiene tres opciones de vestimenta.
A continuación, la fórmula convierte el número a texto usando = VLOOKUP (B11, A2: B4,2) que usa un número aleatorio del valor de B11 para buscar en el rango A2: B4. Luego da el resultado (C11) de los datos enumerados en la segunda columna.
Usamos la misma técnica para elegir zapatos, excepto que esta vez usamos VOOKUP en lugar de HLOOKUP..
Ejemplo: Estadística básica
Casi todos conocen una fórmula de las estadísticas, el promedio, pero hay otra estadística que es importante para las empresas: la desviación estándar.
Por ejemplo, muchas personas que han asistido a la universidad han agonizado con su calificación SAT. Es posible que quieran saber cómo se clasifican en comparación con otros estudiantes. Las universidades también quieren saber esto porque muchas universidades, especialmente las prestigiosas, rechazan a los estudiantes con calificaciones bajas en el SAT.
Entonces, ¿cómo podríamos, o una universidad, medir e interpretar los resultados del SAT? A continuación se presentan los resultados del SAT para cinco estudiantes que van desde 1,870 a 2,230.
Los números importantes a entender son:
Promedio - La media también se conoce como la "media".
Desviación estándar (STD o σ) - Este número muestra cuán ampliamente dispersa está un conjunto de números. Si la desviación estándar es grande, entonces los números están muy separados y si es cero, todos los números son iguales. Se podría decir que la desviación estándar es la diferencia promedio entre el valor promedio y el valor observado, es decir, 1,998 y cada puntaje SAT. Tenga en cuenta que es común abreviar la desviación estándar utilizando el símbolo griego sigma "σ".
Rango percentil - Cuando un estudiante recibe una puntuación alta, puede presumir de estar en el percentil 99 superior o algo así. “Rango percentil” significa que el porcentaje de puntajes es inferior a un puntaje en particular.
La desviación estándar y la probabilidad están estrechamente vinculadas. Puede decir que para cada desviación estándar, la probabilidad o probabilidad de que ese número esté dentro de ese número de desviaciones estándar es:
| STD | Porcentaje de puntajes | Rango de puntajes SAT |
| 1 | 68% | 1,854-2,142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99.73% | 1,567-2,429 |
| 4 | 99.994% | 1,424-2,572 |
Como puede ver, la probabilidad de que cualquier puntuación del SAT esté fuera de las 3 STD es prácticamente cero, porque el 99.73 por ciento de las puntuaciones están dentro de las 3 STD.
Ahora veamos de nuevo la hoja de cálculo y expliquemos cómo funciona..
Ahora explicamos las fórmulas:
= MEDIA (B2: B6)
El promedio de todas las puntuaciones en el rango B2: B6. Específicamente, la suma de todas las puntuaciones dividida por el número de personas que tomaron la prueba.
= STDEV.P (B2: B6)
La desviación estándar sobre el rango B2: B6. La ".P" significa que STDEV.P se usa en todas las puntuaciones, es decir, en toda la población y no solo en un subconjunto.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Esto calcula el porcentaje acumulado en el rango B2: B6 en función de la puntuación del SAT, en este caso B2. Por ejemplo, el 83 por ciento de las puntuaciones están por debajo de la puntuación de Walker.
Graficando los resultados
Poner los resultados en un gráfico facilita la comprensión de los resultados, además, puede mostrarlos en una presentación para que su punto sea más claro..
Los estudiantes están en el eje horizontal y sus calificaciones en el SAT se muestran como una gráfica de barras azul en una escala (eje vertical) de 1,600 a 2,300.
La clasificación percentil es el eje vertical de la derecha del 0 al 90 por ciento, y está representada por la línea gris.
Cómo crear un gráfico
Crear un gráfico es un tema en sí mismo, sin embargo, explicaremos brevemente cómo se creó el gráfico anterior..
Primero, seleccione el rango de celdas para estar en el gráfico. En este caso A2 a C6 porque queremos los números, así como los nombres de los estudiantes.
Desde el menú "Insertar", seleccione "Gráficos" -> "Gráficos recomendados":
La computadora recomienda un cuadro de "Eje secundario, columna agrupada". La parte "Eje secundario" significa que dibuja dos ejes verticales. En este caso, esta tabla es la que queremos. No tenemos que hacer nada más..
Puede mover el gráfico y redimensionarlo hasta que lo tenga como tamaño y en la posición que desee. Una vez que esté satisfecho, puede guardar el gráfico en la hoja de cálculo.
Si hace clic con el botón derecho en el gráfico, a continuación, "Seleccionar datos", le muestra qué datos se seleccionan para el rango.
La función "Gráficos recomendados" generalmente lo libera de tener que lidiar con detalles tan complicados como determinar qué datos incluir, cómo asignar etiquetas y cómo asignar los ejes verticales izquierdo y derecho.
En el cuadro de diálogo "Seleccionar origen de datos", haga clic en "puntuación" en "Entradas de leyenda (Series)" y presione "Editar", y cámbielo para que diga "Puntuación".
Luego cambie la serie 2 ("percentil") a "Percentil".
Regrese a su cuadro y haga clic en "Título del cuadro" y cámbielo a "Puntuaciones del SAT". Ahora tenemos un cuadro completo. Tiene dos ejes horizontales: uno para la puntuación SAT (azul) y otro para el porcentaje acumulativo (naranja).
Ejemplo: El problema del transporte
El problema del transporte es un ejemplo clásico de un tipo de matemáticas llamado "programación lineal". Esto le permite maximizar o minimizar un valor sujeto a ciertas restricciones. Tiene muchas aplicaciones para una amplia gama de problemas comerciales, por lo que es útil aprender cómo funciona..
Antes de comenzar con este ejemplo, tenemos que habilitar el "Solucionador de Excel".
Habilitar complemento de Solver
Seleccione "Archivo" -> "Opciones" -> "Complementos". En la parte inferior de las opciones de complementos, haga clic en el botón "Ir" junto a "Administrar: Complementos de Excel".
En el menú resultante, haga clic en la casilla de verificación para habilitar "Complemento de resolución" y haga clic en "Aceptar".
Ejemplo: Calcule los costos de envío más bajos del iPad
Supongamos que estamos enviando iPads y estamos tratando de llenar nuestros centros de distribución utilizando los costos de transporte más bajos posibles. Tenemos un acuerdo con una empresa de transporte por carretera y aerolínea para enviar iPads desde Shanghai, Pekín y Hong Kong a los centros de distribución que se muestran a continuación..
El precio para enviar cada iPad es la distancia desde la fábrica hasta el centro de distribución hasta la planta dividida por 20,000 kilómetros. Por ejemplo, es de 8,024 km de Shanghai a Melbourne, que es de 8,024 / 20,000 o $ .40 por iPad.
La pregunta es cómo enviamos todos estos iPads de estas tres plantas a estos cuatro destinos al menor costo posible.?
Como puedes imaginar, descubrir esto podría ser muy difícil sin una fórmula y herramienta. En este caso, tenemos que enviar un total de 462,000 (F12) iPads. Las plantas tienen una capacidad limitada de 500,250 unidades (G12)..
En la hoja de cálculo, para que pueda ver cómo funciona, hemos escrito 1 en la celda B10, lo que significa que queremos enviar 1 iPad desde Shanghai a Melbourne. Dado que los costos de transporte a lo largo de esa ruta son de $ 0.40 por iPad, el costo total (B17) es de $ 0.40.
El número se calculó utilizando la función = SUMPRODUCTO (costos, envío) "costos" son los rangos B3: E5.
Y "enviados" son el rango B9: E11:
SUMPRODUCT multiplica los “costos” por el rango “enviado” (B14). Eso se llama "multiplicación de matrices".
Para que SUMPRODUCT funcione correctamente, las dos matrices (costos y envío) deben ser del mismo tamaño. Puede evitar esta limitación haciendo costos adicionales y enviando columnas y filas con valor cero para que las matrices sean del mismo tamaño y no haya impacto en los costos totales.
Usando el Solver
Si todo lo que tuviéramos que hacer fuera multiplicar los "costos" de las matrices por los tiempos "enviados", no sería demasiado complicado, pero también tenemos que lidiar con las restricciones.
Tenemos que enviar lo que requiere cada centro de distribución. Ponemos esa constante en el solucionador de esta manera: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Esto significa que la suma de lo que se envía, es decir, los totales en las celdas $ B $ 12: $ E $ 12, debe ser mayor o igual a lo que requiere cada centro de distribución ($ B $ 13: $ E $ 13).
No podemos enviar más de lo que producimos. Escribimos esas restricciones así: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Ahora ve al menú "Datos" y presiona el botón "Solver". Si el botón "Solver" no está allí, debe habilitar el complemento Solver.
Escriba las dos restricciones detalladas anteriormente y seleccione el rango de "Envíos", que es el rango de números que queremos que Excel calcule. También elija el algoritmo predeterminado "Simplex LP" e indique que queremos "minimizar" la celda B15 ("costos de envío totales"), donde dice "Establecer objetivo".
Presione "Resolver" y Excel guarda los resultados en la hoja de cálculo, que es lo que queremos. También puedes guardar esto para jugar con otros escenarios..
Si la computadora dice que no puede encontrar una solución, entonces ha hecho algo que no es lógico, por ejemplo, es posible que haya solicitado más iPads de los que pueden producir las plantas..
Aquí Excel está diciendo que encontró una solución. Presione "OK" para mantener la solución y volver a la hoja de cálculo.
Ejemplo: Valor actual neto
¿Cómo decide una empresa si invertir en un nuevo proyecto? Si el “valor presente neto” (VAN) es positivo, invertirán en él. Este es un enfoque estándar adoptado por la mayoría de los analistas financieros.
Por ejemplo, supongamos que la empresa minera Codelco quiere expandir la mina de cobre Andinas. El enfoque estándar para determinar si seguir adelante con un proyecto es calcular el valor actual neto. Si el VPN es mayor que cero, entonces el proyecto será rentable dado dos insumos (1) tiempo y (2) costo de capital.
En términos simples, el costo del capital significa cuánto ganaría ese dinero si lo dejaran en el banco. Usted usa el costo de capital para descontar los valores en efectivo al valor presente, en otras palabras, $ 100 en cinco años podría ser $ 80 hoy.
En el primer año, $ 45 millones se reservan como capital para financiar el proyecto. Los contadores han determinado que su costo de capital es del seis por ciento..
A medida que comienzan a explotar, el efectivo comienza a llegar a medida que la compañía encuentra y vende el cobre que producen. Obviamente, cuanto más explotan, más dinero ganan, y su pronóstico muestra que su flujo de efectivo aumenta hasta alcanzar los $ 9 millones por año.
Después de 13 años, el VAN es de $ 3,945,074 USD, por lo que el proyecto será rentable. Según los analistas financieros, el “período de amortización” es de 13 años..
Creación de una tabla dinámica
Una "tabla dinámica" es básicamente un informe. Los llamamos tablas dinámicas porque puede cambiarlos fácilmente de un tipo de informe a otro sin tener que hacer un nuevo informe completo. Entonces ellos pivote en su lugar. Vamos a mostrar un ejemplo básico que enseña los conceptos básicos..
Ejemplo: Informes de ventas
El personal de ventas es muy competitivo (eso es parte de ser un vendedor), por lo que, naturalmente, quieren saber cómo se enfrentan entre sí al final del trimestre y al final del año, además de cuánto serán sus comisiones..
Supongamos que tenemos tres vendedores, Carlos, Fred y Julie, todos que venden petróleo. Sus ventas en dólares por trimestre fiscal para el año 2014 se muestran en la hoja de cálculo a continuación..
Para generar estos informes, creamos una tabla dinámica:
Seleccione "Insertar -> Tabla de pivote, que se encuentra en el lado izquierdo de la barra de herramientas:
Seleccione todas las filas y columnas (incluido el nombre del vendedor) como se muestra a continuación:
El cuadro de diálogo de la tabla dinámica aparece en el lado derecho de la hoja de cálculo..
Si hacemos clic en los cuatro campos del cuadro de diálogo de la tabla dinámica (Trimestre, Año, Ventas y Vendedor), Excel agrega un informe a la hoja de cálculo que no tiene sentido, pero ¿por qué??
Como puede ver, hemos seleccionado los cuatro campos para agregar al informe. El comportamiento predeterminado de Excel es agrupar filas por campos de texto y luego sumar el resto de las filas.
Aquí nos da la suma del año 2014 + 2014 + 2014 + 2014 = 24,168, lo cual es una tontería. También se incluye la suma de los trimestres 1 + 2 + 3 + 4 = 10 * 3 = 3 0. No necesitamos esta información, por lo que deseleccionamos estos campos para eliminarlos de nuestra tabla dinámica.
Sin embargo, la "suma de ventas" (ventas totales) es pertinente, así que lo arreglaremos.
Ejemplo: Ventas por vendedor
Puede editar "Suma de ventas" para decir "Ventas totales", que es más claro. Además, puede formatear las celdas como moneda al igual que lo haría con cualquier otra celda. Primero haga clic en "Suma de ventas" y seleccione "Configuración del campo de valor".
En el cuadro de diálogo resultante, cambiamos el nombre a "Ventas totales", luego hacemos clic en "Formato de número" y lo cambiamos a "Moneda".
A continuación, puede ver su obra en la tabla dinámica:
Ejemplo: Ventas por vendedor y trimestre.
Ahora vamos a agregar subtotales para cada trimestre. Para agregar subtotales, simplemente haga clic izquierdo en el campo "Trimestre" y manténgalo presionado y arrástrelo a la sección "filas". Puedes ver el resultado en la siguiente captura de pantalla:
Mientras estamos en ello, eliminemos los valores de "Suma de trimestre". Simplemente haga clic en la flecha y haga clic en "Eliminar campo". En la captura de pantalla, ahora puede ver que hemos agregado las filas "Trimestre", que desglosan las ventas de cada vendedor por trimestre..
Con estas habilidades nuevas en mente, ahora puede crear tablas dinámicas a partir de sus propios datos!
Conclusión
En resumen, le mostramos algunas de las características de las fórmulas y funciones de Microsoft Excel que puede aplicar Microsoft Excel a sus necesidades empresariales, académicas u otras.
Como ha visto, Microsoft Excel es un producto enorme con tantas funciones que la mayoría de las personas, incluso los usuarios avanzados, no las conocen todas. Algunas personas podrían decir que eso lo hace complicado; sentimos que es más completo.
Con suerte, al presentarle muchos ejemplos de la vida real, hemos demostrado no solo las funciones disponibles en Microsoft Excel sino que también le enseñamos algo sobre estadísticas, programación lineal, creación de gráficos, números aleatorios y otras ideas que ahora puede adoptar Úsalo en tu escuela o donde trabajas..
Recuerde, si desea volver y tomar la clase nuevamente, puede comenzar de nuevo con la Lección 1!
