CONSEJOS PROFESIONALES de Excel parte 6: análisis

1. Introducción a los consejos de análisis: De acuerdo, Si estás viendo este video, estás en una golosina. Esta siguiente sección del curso te presentará algunas de las sobresalientes. Herramientas analíticas más poderosas como tablas de datos, hojas de pronóstico, búsqueda de oro y gestor de escenarios. Trabajaremos en resolver la optimización compleja es con solucionador, explorando tablas de datos con funciones de cubo e incluso construyendo simulaciones de Monte Carlo utilizando un simulador de ruleta a escala completa. Espero que estés emocionado. Ahora empecemos. 2. Herramientas de análisis rápidos: todo bien para este pro tip. Quiero hablar de cómo podemos explorar instantáneamente nuestros datos usando algo llamado herramientas de análisis rápido . Este es un consejo de analítica de una estrella muy sencillo de usar, pero una herramienta realmente interesante para jugar. Por lo que las versiones más recientes de Excel creían en 2013 y en adelante, incluyen una opción llamada Herramientas de análisis rápido, y todo lo que necesitas hacer es seleccionar un rango de celdas, algo así como éste. Verás aparecer un pop up en la parte inferior, ¿verdad? Parece un rayo con un gráfico de barras debajo de él. Y cuando haga clic en esa opción, o presione control que, accederá a un menú de herramientas de análisis rápido. Y dentro de este menú encontrarás todo tipo de herramientas analíticas, incluyendo formatos condicionales, tipos de gráficos, filas calculadas y columnas. Se puede convertir este rango en una mesa no puede chispar líneas. Todo tipo de herramientas analíticas muy, muy interesantes y poderosas todo consolidado en este único menú aquí para un acceso rápido. Y lo que eso nos permite hacer es tomar una gama básica de células de roca como se ve en la parte superior de la diapositiva y convertirla en algo así. Usted sabe ya sea escala de color o agregar una columna calculada con los valores promedio o insertar un gráfico de columnas agrupadas con el clic de un botón. Ya sabes, cualquiera que me conozca y conozca mis cursos lo sabe. En términos generales, trato de evitar este tipo de herramientas atajadas como esta porque supongo que soy de vieja escuela. mí me gusta ir por los menús tradicionales, pero en realidad estoy bastante impresionado por lo suave y lo conveniente que es este menú de herramientas de análisis rápido , y como resultado, he empezado a usarlo un poco más yo mismo. Ahora, una nota aquí, no siempre verás todas estas opciones porque esas opciones y esas herramientas pueden requerir los datos estén en un formato determinado. Entonces, si estás seleccionado, los datos no son compatibles con cierto tipo de herramienta, como una línea de chispa o un pivote. Por ejemplo, es posible que no vea esa opción en su barra de herramientas de análisis rápido. Por lo que los casos de uso más comunes aquí, obviamente solo explora rápidamente una variedad de herramientas de análisis de datos populares sin tener que navegar manualmente a través de diferentes opciones de cinta. Y además, encontré que es una gran manera de agregar rápidamente filas o columnas calculadas para cosas como sumas conteos promedios totales sin tener que escribir realmente una sola fórmula. Entonces saltemos a excel. Consiguió una buena gama de datos que podemos utilizar para practicar algunas de estas herramientas de análisis rápido. Está bien, así que si quieres seguir adelante, adelante y abre tu cuaderno de trabajo de protesta tenía tu tabla de contenidos, y vamos a desplazarnos todo el camino hacia la derecha a nuestros consejos de análisis. Y en este caso, queremos las herramientas de análisis rápido Demo. Adelante y enlaza directamente a esa hoja. Y lo que verás es un sencillo rango de células aquí. Estamos viendo película Soy puntajes de DB promediados por año 2011 a 2015 y por género aquí en la columna A. Ahora esta es una gran muestra de datos para usar para practicar estas herramientas de análisis rápido porque es bidimensional. Tenemos un buen rango o tabla de valores aquí que podemos usar para el formato condicional basado en valores , cosas como escalas de color y conceptos altos. Tenemos tiempo, Siri o tendencia aquí con cinco años de datos para que podamos mostrar cosas como correr totales o gráficos de líneas o columnas. Tenemos oportunidades de agregar filas calculadas y Fila 13 o columnas calculadas o líneas de chispa aquí en la columna G. Así que es un conjunto de datos bueno y versátil para ver realmente una amplia gama de opciones para estas herramientas. Vamos a seguir adelante y seleccionar un a todo el camino hacia abajo hasta F 12. Tenga en cuenta que también estoy incluyendo los encabezados. Y como puedes ver, si pasas el cursor sobre ese rango que has seleccionado, obtendrás ese rápido análisis Pop up aquí mismo en la parte inferior derecha. Adelante y haga clic o presione control Que. Y aquí estamos en el menú de análisis rápido y tienes opciones de formato. Los totales de opciones de gráfico, que son filas y columnas calculadas, pueden tener tablas o pivotes líneas de chispa. Por lo que todas las herramientas de análisis realmente más populares y poderosas empaquetadas aquí en un único menú conveniente. Ahora, quizá mi característica favorita es el hecho de que ni siquiera tienes que comprometerte con ninguna opción para poder ver la salida. Por lo que obtienes este tipo de vista previa rápida en tu selección ya que simplemente pasas el cursor sobre estas diferentes opciones. Y si te gustaría hacer personalizadas son ations, que es algo que a menudo me gusta hacer Al hacer clic a través de estas herramientas, realidad accederás al cuadro de diálogo de formato donde puedes personalizar sabes exactamente lo que quieres aquí y luego presiona OK y al igual que cualquier otro cambio, presiona control Z deshacer, y puedes saltar de nuevo a esas herramientas para que las opciones de formato realmente hablen por sí mismas. Muy sencillo para gráficos. Básicamente, esto solo inserta y nuevo gráfico como objeto de libro de trabajo consiguió diferentes opciones comunes aquí al frente de nuestra lista. En la sección total, podemos agregar rosa calculada seguro nos mostró este tipo de fila sombreada azul aquí en el icono, o si seguimos hacia la derecha, podemos agregar columnas calculadas. Y este uso es tus modos de agregación o de ización de verano más populares, algunos un conteo promedio por ciento del total y un total corrido de nuevo, muy fácil de agregar sin escribir una sola fórmula. Ahí podemos convertir, arreglar a una mesa, insertar una mesa pivotante en blanco directamente desde aquí, o podemos agregar líneas de chispa y nariz de excel justo del bate Que el lugar más apropiado para soltar líneas de chispa en este caso estaría ahí mismo en la columna G Así que de nuevo, he estado muy, muy impresionado por estas herramientas y por lo suaves y convenientes que realmente son. Por lo que te animo a trabajar con los datos que se muestran aquí o a caer en una muestra propia y solo explora cómo estas herramientas de análisis rápido pueden ayudarte a analizar y entender tus datos. 3. El gerente de la cenario: Muy bien, tomemos unos minutos y hablemos de excels, escenario, escenario, gestor, herramienta y, específicamente, específicamente, cómo se puede utilizar esta herramienta para comparar rápidamente diferentes salidas de modelo. Por lo que el gestor de escenarios, que encontrarás en tu pestaña de datos bajo las opciones de análisis qué pasa si esencialmente te permite guardar y acceder rápidamente a combinaciones específicas de valores de celda preestablecidos. Entonces permítanme mostrarles un ejemplo aquí. Si perforamos esa opción de administrador de escenarios, veremos una lista de los escenarios predefinidos que hayamos creado. En este caso. Tengo cuatro. ¿ Y qué sucede cuando añades o creas un nuevo escenario desde cero? Básicamente se lo estás diciendo a Excel. Quería encontrar un escenario basado en este conjunto específico de celdas. En este caso, tengo tres de ellos han nombrado a una celda por ciento hacia abajo. He nombrado una tasa de interés de segunda celda y 1/3 de longitud de término celular. Ahora punta rápida. Aquí. No tienen que darse nombres. Esto podría decir sólo H siete h ocho h nueve. Encuentro que nombrar a esas células ayuda a que este proceso sea un poco más intuitivo y fácil de usar. Entonces esencialmente le estamos diciendo a Excel que estamos definiendo un escenario basado en valores particulares para cada una de estas tres celdas. Este caso, he nombrado al escenario 20% 30 años, y para ese escenario, quiero vender uno para cambiar a un valor de punto al 20%. No venderé a la tasa de interés para cambiar a un valor de 0.475 o 4.75% y quiero cambiar la Celda tres el plazo a 30 ahora. Una vez que haya definido y nombrado ese escenario, puede visualizarlo rápidamente y cambiar automáticamente esas celdas a los valores que haya configurado. Ahora. Esto se utiliza muy a menudo para ejercicios de modelado, que permiten fijar diferentes combinaciones de insumos como estas tres celdas aquí, el pago inicial, la tasa de interés y la duración del término con el fin de calcular el efecto sobre un determinado salida impulsada por fórmula en este caso, el efectivo para cerrar sobre el inmueble y los gastos mensuales estimados. Y si continuáramos con este proceso y creáramos tres escenarios adicionales, entonces con un clic de botón, podemos comparar rápidamente cuatro salidas diferentes en base a esas cuatro combinaciones de nuestras entradas. Por lo que como se puede ver una gran herramienta para evaluar rápidamente el impacto de diferentes combinaciones de valores de entrada. Ahora casos de uso común. Esto se utiliza a menudo para construir pronósticos que se basan en varias variables que pueden cambiar como estacionalidad, tasas de interés, etcétera, etcétera, o modelar varios resultados potenciales en los casos en que la incertidumbre o el riesgo es una factor. Por ejemplo, crear un escenario de riesgo bajo, riesgo medio o alto para predecir los rendimientos de su cartera de acciones. Entonces con eso, saltemos a Excel y creemos algunos de estos escenarios para nosotros mismos. Está bien, así que adelante y abre tu cuaderno de trabajo pro tip. Si estás siguiendo a lo largo, tenía que escenario manager en la sección Purple Analytics Tips y vamos a seguir adelante y enlazar directamente aquí. Tenemos una calculadora de costos de propiedad ahora, para aquellos de ustedes han estado siguiendo junto con todo el curso. Esto puede parecer familiar a partir de nuestro consejo de auditoría de fórmulas, pero básicamente estamos evaluando dos números clave aquí. Efectivo requerido para cerrar el inmueble y los gastos mensuales estimados, ambos están determinados por una serie de factores aquí en Collins, G y H precio de compra de la tasa del impuesto predial, diferentes condiciones de préstamo aquí, los que nos vamos a centrar en esta demo así como otros factores como utilidades, costos de seguros, honorarios y demás. Ahora notarás que he nombrado a cada una de estas celdas usando el cuadro de nombre justo aquí a la izquierda de la barra de fórmulas. Entonces por ciento a la baja, tasa de interés y duración del plazo y de nuevo eso lo hará un poco más legible, pero más intuitivo una vez que empecemos a crear algunos escenarios ahora, la idea aquí es esencialmente preestablecer una serie de combinaciones para estos tres valores en H siete a H nueve, con el fin de determinar rápida y fácilmente el impacto en nuestras salidas clave aquí. Y hay algunas maneras de hacerlo. Podríamos convertir estos en validación de datos, celdas desplegables, ya sabes que contienen una lista limitada de opciones y que los usuarios seleccionen manualmente esas combinaciones. Pero en este caso, realmente tenemos tres o cuatro combinaciones que son más probables y más realistas y para especie de presintonizar esas combinaciones. El gestor de escenarios será una gran herramienta para usar en este caso en particular, así que sigamos adelante y seleccionemos las tres celdas que formarán parte de nuestro escenario h siete h ocho h nueve. Vamos a entrar en nuestra ciudad de datos perforar en las herramientas de análisis qué pasa si en nuestra sección de pronósticos y haga clic en Gestor de escenarios. Entonces a partir de aquí, vamos a definir cuatro escenarios diferentes con el propósito de la demostración. El primero será 20% a la baja y una tasa de interés del 5% durante 30 años. Entonces sigamos adelante y sumamos éste. Ahora puedes darle el nombre que quieras. En este caso, no voy a ser muy inteligente. Simplemente voy a escribir en realidad lo que estamos usando para insumos. Por lo que 20% 5%. 30. Y como hemos seleccionado H 73 cada nueve Excel ya sabe que estas airen las tres celdas que estarán cambiando para definir este escenario. Y luego puedes agregar comentarios aquí personalizar la protección que este caso estaba impidiendo a los usuarios hacer cambios y presionar. OK, entonces aquí vamos. Está poblado con los valores actuales en esas celdas, y sólo podemos modificarlos para que se ajusten a nuestros criterios. Por lo que por ciento abajo su punto a lo que sea más fácil para ti puede escribir 0.2 o realmente puedes escribir 20% aquí. Yo soy como ese enfoque un poco mejor. Ha sido más legible. Um, tasa de interés en este caso es del 5% en el término. La longitud es de 30. Entonces presiona bien ahí, y se creó nuestro escenario. Y ahora mira lo que pasa cuando hago clic en este show button boom. Se ha cambiado esa tasa de interés a 5%. Por lo que ahora esas tres celdas toman los valores exactos a encontrar. Por ese primer escenario, acabamos de crear. Entonces el mismo proceso. Hagamos un crucero y sumamos tres más. El segundo va a estar 20% abajo al 4% a lo largo de 15 años. 20% 4% 15 Pulse OK, 20%. 4% 15 años. Muy bien, sigamos con esto. El próximo va a ser un menor pago inicial, sólo 10% a la baja al 5% a lo largo de 30 años. Y sé que me estoy moviendo rápido, así que siéntete libre de frenar las cosas para seguir adelante. Pero aquí tenemos 10 tiene 5%. Eso fue correcto. A lo largo de 30 años. Eso se ve bien. Y por último, vamos a sumar nuestro cuarto aquí, que es 10% tuvo tasa de interés del 4% más de 15 y presionarlo Vale, 10% 4% 15. Ahí vamos. Entonces ahora tenemos nuestros cuatro escenarios definidos aquí, y para determinar rápidamente el impacto de cada uno de esos escenarios en nuestras celdas de salida. Todo lo que tenemos que hacer es seleccionar el escenario y hacer clic en Mostrar. Entonces ahora echa un vistazo a eso. Cambió a 20%. 4% 15 click. El tercer ítem muestra 10% 5% 30 y luego la última opción. 10% 4% 15. Y cada vez que cambiamos estos artículos, se puede ver que nuestro efectivo para cerrar y o nuestros cálculos de gastos mensuales cambian en consecuencia. Por lo que ahí tienes una demostración rápida de usar el gestor de escenarios para definir combinaciones predefinidas de valores de entrada de celdas. 4. Optimización con el busco con objetivos: Muy bien, Este próximo pro tip es divertido. Vamos a hablar de cómo resolver para salidas individuales o modelos de optimización simples usando excels Gold seek tool now gold seek, que encontrarás en tu pestaña de datos bajo el qué si las opciones de análisis básicamente te permiten encontrar el resultado que deseas Al permitir excel toe cambie automáticamente el valor de una sola celda de entrada dada. El oro busca requiere entradas y salidas muy simples. Es necesario determinar una sola celda de entrada codificada y una sola celda de salida basada en fórmula . No podemos sumar otras condiciones aquí. No podemos probar combinaciones de entradas múltiples para esas optimización más compleja. Problemas estarán usando la herramienta Excels Solver en una demo más avanzada en el curso. Entonces considera un caso como este. Contamos con un insumo basado en valor, que es la cantidad de unidades vendidas ahí mismo y vendemos asiento a. Contamos con un conjunto de factores de cálculo como el precio unitario o precio de venta al por menor, el costo para producir cada unidad y un cierto costo fijo plano. Y con esos factores junto con el valor de la cantidad, podemos determinar la ganancia, que es nuestra salida basada en la fórmula en este caso, nuestra ganancia igualará la cantidad veces la diferencia entre el precio y el costo unitario menos el costo fijo plano. Por lo que este escenario en particular, este modelo sencillo comprueba todas las casillas para la herramienta de búsqueda de meta. Tenemos una sola cantidad de entrada codificada y una sola salida basada en fórmula. El profeta. Entonces veamos cómo se ve esto en realidad. Cuando perforamos en esa herramienta de búsqueda de objetivos, verás un cuadro de diálogo que se ve así, y lo que estamos haciendo aquí es que estamos configurando nuestra celda de salida C ocho, que es nuestra ganancia a un objetivo específico o valor objetivo en este caso, $1000 cambiando esa entrada basada en valor, ver a la cantidad. Entonces en llano inglés le estaban diciendo a Excel, quiero que cambies la celda C dos como sea necesario para aterrizar en $1000 de ganancia. ¿ Cuánta cantidad o cuántas unidades hacen para vender para hacer 1000 dólares? Y cuando configuras el oro, busca de esta manera y presiona OK , automáticamente produce estos valores. Fijaste tu beneficio $2000 tipo de trabajo al revés a la cantidad que equivale a 833 unidades vendidas ahora casos de uso común aquí, igual que estamos mostrando aquí, determinando el insumo ideal requerido para producir una meta u objetivo específico resultado. Ya sabes, por ejemplo, podríamos hacer una ganancia de 1000 dólares aquí. Podríamos hacer una ganancia de cero con el fin de averiguar cuánta cantidad necesitamos vender para romper parejo. Conoces cualquier combinación como esa, o realmente simplemente resolver cualquier problema de optimización simple que se base en una sola variable de entrada . Entonces llevemos este ejemplo a Excel y practiquemos la configuración de la herramienta de búsqueda de oro nosotros mismos. Muy bien, Así que si quieres seguirme conmigo, abre tu libro de trabajo pro tip y desde nuestra tabla de contenidos, vamos a perforar nuestro objetivo, buscar demo en nuestra sección de consejos de análisis y seguir adelante y enlazar. Y aquí, ya verás, son familiares. Modelo muy sencillo basado en esta única cantidad de entrada y este sale el profeta. Y como pueden ver, si selecciono la celda C dos, esto es simplemente un valor codificado duro. Aquí tenemos algunos factores de cálculo predefinidos desde el precio unitario, el costo unitario y el costo fijo en nuestra célula profeta. C ocho. Esta es nuestra salida impulsada por fórmula, que toma la cantidad, multiplica por el precio unitario menos el costo unitario y luego resta el costo fijo al final para determinar la ganancia real. Entonces, como puedes ver, puedes ajustar diferentes valores aquí. Diga bien, si vendiera 325 unidades, mi ganancia sería a 38. Si vendiera 23 mil unidades, mi ganancia sería de 34 mil 250 y así sucesivamente y demás. Entonces la forma en que muchos usuarios que no están familiarizados con el oro buscan usarán Excel y específicamente usarán un modelo como este es, ya sabes, digamos que queremos 10.000 dólares de ganancia. Ya sabes, podrías decir, OK, intentemos vender 8000. Bueno, eso son demasiados. Probemos que los 7500 siguen siendo demasiado altos. Tal vez 6000 a baja 65. Estás tratando de acostarte en ese número usando el enfoque de invitados y chequear, y esto es exactamente lo que busca oro está diseñado dedo del pie automatizar para ti y voluntad. También puedes tipo de volver a la fórmula para crear ese valor de cantidad óptima al pre enviar tu beneficio oro seek está diseñado para hacer eso para que pases por ese proceso y no te hagas tipo de revertir los cálculos para descifrar ese número y ver a. Entonces vamos a saltar a nuestra pestaña de datos. Vamos a ver nuestras herramientas de análisis y dar click en oro buscar o segunda opción aquí y todo lo que tenemos son tres condiciones aquí. Vamos a establecer una célula específica, que es nuestra ganancia puso a ese profeta en un valor específico. Y esto podría ser cualquier cosa. Empecemos realmente con $0. Yo quiero encontrar el punto de equilibrio. Y queremos hacer eso cambiando una celda dada, que es la cantidad aquí y ver, y todo lo que necesitamos hacer es presionar OK y comprobarla. Simplemente automatizó el proceso de resolver esto para nosotros. Por lo que el valor objetivo fue cero. Optimizado encontró valor actual de cero. De acuerdo, esto nos dice que Ok, necesitamos vender 167 unidades antes de romper una ganancia. Y para especie de confirmarlo si elegimos mil 66 bien, perdimos un dólar. Elige 1 68 hicimos $2 y puedes probar muy rápidamente diferentes escenarios aquí. Al igual que en este caso, queremos poner al profeta en 100 mil por ejemplo. Mismo proceso exacto. Las mismas entradas exactas. Presiona OK, Y ahí tienes. Encontró una solución y nos dijo que Ok. Por $100,000 de ganancia, tienes que vender 66,833 unidades. Por lo que hay un curso acelerado en busca de oro. Es una herramienta bastante increíble cuando necesitas optimizar modelos simples de entrada única y de salida única en excel. 5. Pronóstico básico: bien, hora de hablar de una de mis técnicas de analítica favoritas de pronósticos. Y específicamente, quiero mostrarles cómo podemos crear hojas de previsión utilizando datos históricos y excel. Ahora. Versiones recientes de Excel cree que a partir de 2016 y Office 3 65 incluyen una herramienta de trucos de previsión integrada que permite calcular pronósticos en función de un conjunto dado de valores históricos. Entonces, por ejemplo, tenemos datos básicos como esta temperatura promedio mensual, que en este caso en realidad continúan extendiéndose a través de unos seis años de datos. Lo que podemos hacer es seleccionar ambas columnas, incluyendo el mes o el indicador de fecha, así como los valores Perforar en la pestaña de datos y dar clic en la herramienta de trucos de previsión justo al lado de las opciones de análisis qué pasa si. Y cuando hagamos eso, veremos una vista previa básica del pronóstico predeterminado que Excel ha producido, así como opciones para personalizar cosas como la longitud del pronóstico, el intervalo de confianza, cualquier estacionalidad y también personalizado cómo manejar cosas como valores faltantes o duplicados. Y una vez que hayas configurado esos ajustes como te gustaría, Excel producirá una hoja que se vea algo así. Esta es la hoja de previsión real. Se trata de una nueva pestaña en su libro de trabajo, y contiene el gráfico de estacionalidad así como una nueva tabla de datos que contiene sus valores históricos existentes, así como un conjunto de valores pronosticados calculados. Te voy a mostrar un par de ejemplos de exactamente cómo se ve esto en Excel ahora una nota rápida . En ocasiones puedes usar herramientas como líneas de tendencia para ejercicios de previsión muy simples. Pero ten en cuenta que esos no darán cuenta de cosas como la confianza o la estacionalidad. Por lo que para casos más complejos o personalizados y definitivamente recomiendo usar esta opción de trampas de previsión en su lugar. Casos de uso tan comunes aquí para uno, prediciendo cualquier valor futuro como tasas de interés o rendimientos de acciones. Y dos si se quiere calcular un rango esperado de resultados futuros en base a un determinado nivel de confianza, esta hoja de previsión y las fórmulas que produce son muy, muy útiles para hacerlo. Entonces con esa plática suficiente, vayamos a excel en la práctica, construyendo nuestro propio pronóstico. Está bien, así que para aquellos de ustedes que siguen junto conmigo tuvieron su cuaderno de trabajo pro tip, estamos buscando la demo básica de previsión en nuestra sección de consejos de Analytics púrpura. Adelante y enlaza directamente, y aquí verás dos conjuntos diferentes de datos obtuvieron datos bursátiles aquí en columnas, un a través de E, un a través de E, mirando los precios de las acciones de Apple, ese precio de cierre y volumen a día. También tenemos algunas temperaturas medias mensuales desde Barcelona, España, que abarcan unos seis años de datos. Entonces dos tipos de conjuntos de datos muy, muy diferentes que vamos a utilizar para practicar pronósticos. Entonces, empecemos con nuestros datos bursátiles aquí. Y una cosa que quiero mostrarles es que si fuéramos a seleccionar la columna B y controlar, haga clic en los valores que queremos pronosticar, que en este caso será el precio de las acciones de cierre. Ahora, si vamos a nuestra hoja de datos y elegimos hoja de previsión junto a qué pasa si el análisis, verás este mensaje de error dice: Oye, Oye, no podemos crear un pronóstico porque tu línea de tiempo no está espaciada de manera uniforme. En otras palabras, tienes brechas inconsistentes entre algunos de estos puntos y la razón que es el caso, porque el mercado está cerrado los días festivos y fines de semana. Por lo que tenemos estos vacíos, ya sabes, como el 2 y el tercero de marzo, donde aquí no proporcionamos ningún dato. No hay precio de cierre ni volumen, así que lo que he hecho es en una nueva columna aquí. Columna C Eso es sólo una serie de valores secuenciales o un índice que básicamente sólo etiqueta el Día de los Días un día, dos día tres, independientemente de si se trata de un fin de semana o día laborable o festivo, sea lo que sea. Y ahora, si seleccionamos las columnas C y D y volvemos a nuestra hoja de previsiones ahora, somos capaces de producir un pronóstico real, así que vender tomará su mejor suposición en su configuración. Ya conoces tu pronóstico. Inicia tu pronóstico e intervalo de confianza estacionalidad, etcétera. Pero si quieres personalizar exactamente cómo se configura esto, adelante y perfora en las opciones en la parte inferior izquierda y aquí tenemos un montón de opciones adicionales para elegir, y una opción es cambiar qué tan lejos quieres un pronóstico. Entonces tal vez en lugar de 1574 días, tal vez sólo necesitamos pronosticar a 1400 días, lo que tipo de encoge la duración prevista del mismo. Otra opción es cambiar realmente una. Comienza el pronóstico. Entonces por defecto este pronóstico va a empezar. En el último día en que tenemos valores reales y la razón por la que sobresalieron los impagos de tomar ese enfoque es que le permite tipo de uso de todos estos puntos de datos. Todos estos actuales históricos dedo del pie ayudan a generar la predicción. Pero lo que podemos hacer es cambiar esto. No a algo así como 1000 días y forzar a excel a iniciar el pronóstico antes. Y básicamente nos da un periodo de superposición donde realmente podemos comparar los valores pronosticados con los reales. Ahora, el inconveniente del que estamos viendo los resultados de aquí es que al hacer eso acelerando el inicio del pronóstico, estamos alimentando menos información histórica para sobresalir y por lo tanto potencialmente impactando el exactitud del propio pronóstico. Entonces tal vez nos dividimos aquí la diferencia y decimos: Empezamos a los 1100 días. Eso se ve bastante bien. Todavía tenemos un buen trozo de solapamiento aquí, pero eso parece ser un pronóstico un poco más realista. Ahora, los otros ítems que debilitan personalizaron aquí el intervalo de confianza y que básicamente está creando estos dos límites los límites superior e inferior y diciéndonos que podemos estar 95% seguros de que los valores reales observados en el futuro caerá dentro de estos límites, así que mira lo que sucede cuando aumentemos la confianza a 99. Notarás que los límites se vuelven más parte, creando una ventana más amplia que potencialmente podría contener nuestros valores futuros reales. Y si reducimos esto a algo así como un 50% de confianza ahora, tenemos un límite mucho más estrecho y un objetivo mucho más difícil de golpear. Por lo tanto, estaban menos confiados en que los valores reales siempre estarán dentro de este rango. Entonces, en términos generales, vas a querer quedarte con algo así como 90 o 95 esos de aire tipo estándar. Y desde aquí también podemos lidiar con la estacionalidad. De forma predeterminada, Excel detectará la estacionalidad automáticamente. En este caso, si hubiera cierto número de días o cierto periodo donde vemos que un patrón se repite una y otra vez, podríamos establecer ese patrón manualmente, y te mostraré exactamente cómo podríamos hacer eso para los datos meteorológicos. Y luego cuando creemos la hoja, también podemos incluir algunas estadísticas de pronóstico como Solo marca esa casilla. Y por último pero no menos importante, tenemos nuestro rango de línea de tiempo y la columna C Valores de índice Día Columna viva D, y luego podemos elegir si teníamos puntos faltantes podríamos interpretar tardío temprano de aquellos en cero. Este caso, no tenemos puntos faltantes. Y si pasamos a tener duplicados, podríamos elegir cómo resumir o agregar. Esos duplicados en la mayoría de los casos promedia allí una elección apropiada. Entonces con eso, tengo confianza. Estoy contento con nuestras elecciones. De esa manera podemos figurar esto. Adelante y haga clic. Crear, y lo que hace Excel es insertar aquí una hoja totalmente nueva, nos da un poco de dedo pop up. Danos la bienvenida a nuestra nueva hoja, y dice, Genial. Hemos creado una tabla con una copia de nuestros datos Columnas A y B junto con los valores pronosticados y nuestra confianza. Límites en la columna C, D y e Así lo conseguí. Suena bien. Sigamos adelante y arrastremos esta hoja a la derecha de nuestra hoja de previsión y lo nombremos algo así como pronóstico de acciones. Echa un vistazo. Se incluye el gráfico para nosotros, igual que previsuamos. Se incluye estas estadísticas de pronóstico aquí en esta pequeña mini mesa en columnas G y H. No me meteré en esas ahora mismo y después conseguí nuestras copias reales de nuestros puntos de datos históricos aquí. En viene A y B y si nos desplazamos hacia abajo o incluso más eficientes, seleccionado venta vacía usada. El control son enfoque hacia abajo. Aquí vamos, el último día de nuestros valores observados o en este caso, el primer día que determinamos que queremos que nuestro pronóstico empiece ahora. Estas nuevas columnas aire se están poblando aquí también, y en realidad se están poblando con fórmulas de pronóstico que se están alimentando en insumos en base a cómo configuramos nuestros ajustes en el cuadro de diálogo y lo mismo con nuestros bajos y confianza superior. Los límites estaban usando fórmulas de intervalo de confianza confianza aquí. Entonces, si quisiéramos ajustar alguna de estas configuraciones, podríamos retroceder y crear un pronóstico totalmente nuevo desde cero usando el cuadro de diálogo. O podríamos seguir adelante y actualizar entradas individuales o argumentos de estas funciones y tener la tabla actualizada dinámicamente. tan realmente útil allí. Desplazemos por la parte superior. Y una cosa que quiero mostrarles aquí es que en este caso podemos editar este gráfico igual que cualquier otro. Y digamos que queríamos seguir un enfoque más simple para pronosticar estos valores bursátiles y simplemente ir a sus herramientas de gráficos. Agrega un elemento de gráfico y lanza una línea de tendencia lineal en su lugar que creó esta línea de tendencia punteada azul que, como puedes ver, sigue casi el pronóstico exacto de que se crea Excel. Entonces en este caso en particular, usar un pronóstico lineal como un primer paso muy rápido y sencillo podría ser un enfoque válido, y de hecho, va a producir casi exactamente los mismos valores que nuestro pronóstico aquí. Pero eso no siempre va a funcionar. Y un caso en el que muchas veces se queda corto es un caso en el que se tiene estacionalidad. Por lo tanto, volvamos a nuestra hoja básica de pronósticos, Select Columns G y H Esta vez, que son nuestros datos mensuales de temperatura. Vuelve a los datos y veamos qué pasa aquí y elegimos hoja de previsión. Así que echa un vistazo a este patrón muy, muy diferente aquí, y claramente hay estacionalidad que está sucediendo a lo largo de esos seis años de valores reales. Entonces la diferencia entre este ejemplo y el ejemplo de stock es que una no podemos simplemente abofetear una opción de línea de tendencia básica aquí para seguir este camino de previsión porque hay una tendencia de estacionalidad sucediendo porque, literalmente, estamos viendo el valor de los datos de la temporada. Eso Excel está diciendo bien hemos detectado estacionalidad automáticamente y si lo dijiste manualmente, básicamente estaban diciendo cada 12 puntos, que son meses, vemos el mismo patrón repetir, lo cual tiene sentido. Entonces en este caso, no voy a crear unas nuevas hojas. Se verá igual, tiene el otro. Pero ahí también lo tienes . Buenos ejemplos de cómo utilizar la hoja de previsión sobresale para proporcionar valores pronosticados o pronosticados utilizando dos tipos de datos muy diferentes. 6. Detección externa: todo bien para este pro tip, Nos estamos poniendo un poco más avanzados. Este es un consejo analítico de cuatro estrellas. Vamos a hablar de cómo encontrar o detectar valores atípicos, utilizando una combinación de estadísticas, funciones y formato condicional. Ahora, lo que vamos a hacer aquí es usar funciones como la mediana y el dedo del cuartil en realidad calcular valores atípicos estadísticos, y luego vamos a aplicar reglas de formato condicional para llamar la atención sobre ellos o resaltarlos dentro de nuestras columnas. Por lo que en este caso, estamos viendo datos de atletas con nombres y columna A. Tenemos alturas y columna B y pesos y columnas. Ver, Y lo que estará haciendo es crear los cálculos de valores atípicos. Usando esas funciones de estadísticas, calcularemos el valor de la mediana. El primer cuartil, tercer cuartil, el rango inter cuartil o como eres y algo llamado multiplicador de defensa. Y esa barda es esencialmente lo que determina a qué distancia de la norma de valor debe caer para ser etiquetada como un valor atípico. Y lo que terminará con es un rango de valores para encontrar por la cerca interior o inferior y la valla superior o externa, lo que significa que cualquier valor que caiga bien fuera de ese rango, técnicamente sea etiquetado como un valor atípico. Entonces, a partir de ahí, todo lo que necesitamos hacer es dirigirnos a nuestra pestaña de inicio, reglas de formato condicionales. Y vamos a utilizar una regla de resaltado personalizada para resaltar las celdas que no están entre esos valores de valla y vende F ocho F 11. Y cuando aplicamos esa regla de formato a en este caso la columna B reciben inmediatamente esos valores atípicos resaltados en amarillo Ahora. Una cosa a tener en cuenta. Si estás lidiando con cálculos o reglas de formato mucho más simples como los valores finales superiores o un porcentaje superior específico, solo tienes que usar formatos condicionales básicos. No hay que pasar por todo este proceso, pero lo que estamos haciendo es usar funciones estadísticas reales para definir esos valores atípicos de una manera un poco más científica. Y como les mostraré desde aquí, podemos como ajustar nuestra barda y ver cómo impacta eso, que vende aire siendo resaltado y etiquetado como valores atípicos. Casos de uso tan comunes aquí ante todo identificando cualquier anomalía estadística en el conjunto de datos en base a este criterio personalizado que defina y en segundo lugar, encontrar y eliminar valores que puedan haberse ingresado incorrectamente. Entonces si vimos altura aquí de 800 pulgadas. Ya sabes, eso se marcará muy rápidamente como un atípico y un mentor qué ajustes elegimos. Y eso sería una muy buena indicación de error de entrada del usuario y no ah, no sólo un ser humano muy, muy alto. Entonces con eso, saltemos a un cuaderno de trabajo pro tip. Vamos a echar un vistazo a este conjunto de datos de demostración exacta y correr a través de este proceso Paso a paso . Está bien, así que si te estás siguiendo, ya deberías conocer el simulacro, tenía tu tabla de contenidos. Y busca la demo de detección de valores atípicos en nuestra sección de consejos de Purple Analytics. Adelante y vincula directamente a eso. Ella y lo que encontrarás aquí son nombres de jugadores o atletas y columna A. Y al igual que describimos alturas y pesos y columnas B y C. Nuestro objetivo es poblar estos cálculos estadísticos aquí en la columna F para determinar exactamente cómo estamos tratando y definiendo valores atípicos. Entonces vamos a empezar con mediana y venderle F a. Esto en realidad no se usará en nuestro cálculo de valores atípicos, pero es una gran estadística de resumen para incluir Solo voy a usar la función mediana 0.2 columna B pulse OK. Eso me dice que la altura mediana en esta muestra de 74 pulgadas ahora que nos lleva a las funciones de azulejo del núcleo. Empieza por escribir una fórmula de cuartil, y lo que verás son tres opciones diferentes. Aquí tienes la versión E X, c o exclusiva I NC versión inclusiva y la versión original del cuartil, que es una función de compatibilidad compatible con versiones anteriores de Excel. Y lo que esto te dice es que originalmente, solo estaba la función cuartil y luego empezando y creo que 2010 Microsoft comenzó a introducir nuevas variaciones de esa fórmula. Ahora, en este caso en particular, veremos la misma respuesta sin importar la versión que utilicemos. Pero quiero mostrarte el i N C. La versión inclusiva ahora mismo, alguna pestaña que en las tres versiones usan los mismos argumentos, empezando por el array cominby y luego el azulejo de la corte, que puedes etiquetar con un 0123 o cuatro. Y en este caso, queremos el primer cuartil o el percentil 25 en una prensa 81 ahí y cerrarlo y presionar enter. Ahora lo que esto nos dice es que los jugadores que miden 72 pulgadas de alto son al menos tan altos como 25% de la otra rosa o jugadores de la muestra. Ahora un poco de matiz aquí. Pero la diferencia entre las versiones inclusivas y exclusivas es que la versión exclusiva básicamente determinaría el valor que es mayor al 25% de los valores en la matriz . La versión inclusiva determina el valor que es mayor o igual al 25% de los valores en la matriz. Entonces esa es la diferencia ahí. Ese es el matiz. Actos exclusivos como actos mayores a actos inclusivos como mayores o iguales a. Y otra nota aquí. Si quisieras usar esta función para devolver el valor mínimo o máximo con una etiqueta de cero o cuatro, tendrías que usar la versión inclusiva para hacerlo. Entonces se va y se cae. Son uno de vuelta en su prensa entrar, simplemente podemos copiar esa fórmula. Pega aquí por 1/3 cuartil simplemente cambió el 123 y obtenemos un 75. Ahora I Q R's aire Interter cuartil rango. Eso es un simple como el tercer cuarto voy en un cuatro menos el primer cuartil en F tres. Se trata de un rango de tres pulgadas, y a continuación, nuestro multiplicador de valla. Ahora lo que hace la barda es esencialmente determinar qué tan lejos fuera de nuestro rango inter cuartil debe caer un valor para ser etiquetado como un valor atípico estadístico. Por lo que cuanto mayor sea la barda o mayor sea el multiplicador de la valla, más lejos de nuestro rango esperado. Ese valor necesita caer para ser considerado un valor atípico, y este es un valor codificado duro. En muchos casos, 1.5 es una especie de buen punto de partida estándar. Adelante y empecemos ahí y luego por último pero no menos importante, necesitamos determinar nuestra cerca interior o barda inferior así como nuestra valla exterior o superior . Por lo que la barda inferior se define como el primer cuartil, menos paréntesis abiertas. Rango de cuartil Inter veces nuestra barda por lo que F tres menos cinco veces a las seis que devuelve un 67.5 . Eso significa que el valor más bajo que toma un Selcan antes de ser tratado como un valor atípico es de 67.5 pulgadas. Lógica muy similar aquí para la barda exterior o barda superior. Toma el tercer cuartil y vamos a sumar el I Q. R. veces el multiplicador de barda Ciérrelo. Presionar enter. Mismo proceso de pensamiento aquí. El valor más alto que toma un Selcan antes de ser tratado como un valor atípico es de 79.5 pulgadas. Ahora, desde aquí, hemos hecho la parte difícil. El último paso es realmente marcar o llamar la atención sobre los valores atípicos reales que hemos definido aquí en la columna B. Así que también es grabby . Antigua flecha de desplazamiento de control hacia abajo para agarrar toda la columna. Voy a entrar en nuestro menú de inicio formato condicional. Y vamos a usar, um, um, resaltar las reglas celulares aquí. Y lo que realmente queremos son células que tengan un valor que no esté dentro del rango de nuestras vallas superiores e inferiores. Entonces queremos un no entre opción aquí, que no tenemos es uno de nuestros estándares. Pero si entramos en más reglas aquí, tenemos esa opción donde podemos decir valores de celda de formato que no están entre. Y entonces simplemente podemos apuntar a las células que nos importan, que están todo el camino aquí arriba en la parte superior. Por lo que queremos resaltar las células que no caen en este rango. No están entre ocho o s 11 y nosotros cumplimos. Da formato a este depende de ti vas a usar tipo de Phil amarillo aquí y pulsa OK y echa un vistazo . Presionamos OK ahora. Ahora se resalta cualquier valor que sea superior a la barda superior mayor a 79.5. Y si saltamos al error de control inferior hacia abajo, tenemos a valores aquí que estaban por debajo de la barda inferior de 67.5. Por lo que esencialmente hemos marcado esos valores atípicos basados en nuestras condiciones estadísticas o en una configuración aquí. Y veo lo que pasa si cambiamos este multiplicador de barda de un 1.5 a uno. Ahora estamos incluyendo más valores que estaban etiquetando como valores atípicos aquí porque hemos creado esencialmente un rango más estrecho de valores normales. Cambiamos este multiplicador de barda a o ampliamos esa barda. Ahora estamos haciendo menos probable que un valor sea etiquetado como un valor atípico. Por lo que sólo vemos unos cuantos puntos de datos aquí en la columna B que quedan marcados en este caso, tres de los beisbolistas más altos del juego. Por lo que ahí tienes un gran pequeño curso crash sobre cómo usar algunas de estas funciones de estadísticas relativamente simples combinadas con el formateo condicional toe realmente detectan y marcan valores atípicos en tus datos 7. Mesas de datos automatizados: todo bien para este pro tip, vamos a hablar de un consejo analítico de cuatro estrellas, relativamente avanzado y vamos a discutir cómo evaluar las entradas variables usando tablas de datos sobresalientes. Ahora, las tablas de datos de las que estamos hablando aquí son diferentes a sus rangos de celdas estándar , formateadas como tablas. De hecho, ojalá Excel les diera un nombre diferente porque en realidad operan. manera bastante diferente estamos hablando son las tablas de datos que encontrarás en tu pestaña de datos a partir de las herramientas de análisis qué si Y lo que hacen estas tablas de datos es que te permiten calcular en matriz de resultados con el clic de un botón basado en todo un rango variedad de valores potenciales de entrada. Entonces para mostrarles lo que quiero decir con eso, vamos a ver nuestra herramienta calculadora de costos de propiedad, que hemos visto antes a lo largo del curso. Y la idea aquí es entender exactamente cuáles son nuestros costos hipotecarios. Los costos hipotecarios mensuales se verían en base a diferentes tasas de interés que van desde el 3% hasta el 10% en incrementos porcentuales a la mitad. Por lo que el tipo manual de tedioso enfoque de la vieja escuela sería enchufar esas diferentes tasas de interés a Celular H ocho y ver cuáles son los costos de la hipoteca escupir y vender h 14. Pero en lugar de hacer eso, vamos a ponernos un poco más sofisticados, y vamos a seleccionar esta tabla este rango en blanco en columnas, Jane K. Vamos a utilizar la herramienta de tabla de datos excels para producir una matriz de resultados con base en ese rango de tasas de interés. Entonces vamos a dirigirnos a nuestra ciudad de datos va a perforar en la opción de tabla de datos desde las herramientas de análisis qué pasa si. Y entonces aquí sólo tenemos dos entradas potenciales una celda de entrada de fila y una celda de entrada de columna. Y en esta demostración en particular, todo lo que tenemos es una columna que contiene esos valores de rango y la celda en la que queremos probar ese rango de valores vive en h ocho. Por lo que al tratar a los ocho años como la entrada de columna, Excel es capaz de escupir toda esta matriz de resultados ahí mismo en la columna K basado en el rango de las tasas de interés en la columna J. Así que en lugar de probar manualmente 15 o tan diferentes intereses valores de tasa y registrando los resultados o escribiendo una fórmula 15 veces, hemos configurado la herramienta de tabla de datos una vez y producido todos los resultados con el clic de un botón. Ahora, las tablas de datos se pueden usar para evaluar resultados como vemos aquí en base a cambios en una sola variable de entrada como lo hemos mostrado con tasa de interés o con múltiples variables, en cuyo caso también conectamos una celda de entrada de fila. Y te voy a mostrar un ejemplo de ambos en cuanto saltemos al excel. Pero casos de uso común rápidos reales calculando una matriz de resultados en base a combinaciones de valores de entrada como este. Pagos mensuales basados en cosas como tasas de interés y montos de pago inicial. O llevando eso incluso un paso más allá. Podría utilizar esta herramienta para identificar el resultado óptimo dadas múltiples combinaciones de entradas variables. Entonces sigamos adelante. Abre nuestro cuaderno de trabajo pro tip, enrolla nuestras mangas y practica la construcción de algunas de estas tablas de datos. Muy bien, así que desde tu tabla de contenidos, adelante y desplázate hasta los consejos de análisis. Vamos a ver la demo de la tabla de datos en este caso, adelante y enlazar directamente a esa pestaña púrpura. Y aquí verás nuestra calculadora de costos de propiedad, que, como hemos visto antes, toma un montón de insumos como el precio de compra, esa tasa impositiva y algunos términos del préstamo y escupe salidas como el monto del préstamo que hipotecan costos, impuesto predial, etcétera. Ahora lo que nos importa en este caso es el costo mensual de la hipoteca aquí y vender H 14. Y este costo hipotecario es una función de unos pocos factores diferentes para uno, el pago inicial y para a la tasa de interés, entre otros. Entonces si cambiamos tu pago inicial de 20% a 30% notarás que nuestros costos hipotecarios mensuales se reducen. En contraste, si cambiamos nuestra tasa de interés de 4% a 5% nuestros costos hipotecarios aumentan. Por lo que estos dos factores ambos impactan en el costo de la hipoteca. Entonces lo que vamos a hacer aquí es crear dos tablas de datos diferentes para probar o evaluar diferentes combinaciones de esos valores potenciales de entrada. Entonces en el primer caso aquí, todo lo que estamos probando son variaciones en la tasa de interés. Por lo que el Paso uno es crear una columna que contenga cada una de las variaciones en la tasa de interés que queremos probar con el fin de ver el impacto en nuestra fórmula de salida o en los costos de una hipoteca. Y solo tengo que mostrarte este consejo aquí, puedes teclear estos valores manualmente o qué puedes hacer y eliminarlos Puedes escribir tu primer valor aquí, el cursor sobre la esquina inferior derecha y escuchar esto. Sostén el botón derecho del clic derecho, arrastrado hacia abajo y retrocede hacia arriba, luego suelta para acceder a este tipo de menú oculto aquí. Y lo que vamos a hacer es caer en el de Siri al fondo. Vamos a llenar un Siris en una columna. Vamos a escalar o incrementar un 0.5%. Vamos a parar al 10%. Esa es nuestra prensa de valor máximo. OK, Booms llenó ese perfecto, incremental serio para nosotros con un solo clic del botón. Ahora, el objetivo aquí es poblar aquí estas celdas en blanco desde K cuatro hasta K 18. Y para poder hacer eso con tablas de datos, tenemos que configurar las cosas de una manera muy específica. El primer paso, que acabamos de lograr, es establecer el rango de valores de entrada. El segundo paso en realidad está vinculando a la fórmula que buscamos evaluar. Entonces solo estamos tomando la fórmula de H 14 y la estamos haciendo referencia aquí mismo en South K tres. Lo que eso nos va a permitir hacer es seleccionar todo este rango de celdas, que incluye tanto la fórmula misma como la variedad de valores de entrada. Y desde aquí podemos entrar en nuestra pestaña de datos. ¿ Y si análisis fechado tabla y otra vez, al igual que nuestra demo. No tenemos ninguna variable de entrada en una fila, pero sí tenemos entradas variables en una columna, y la celda de entrada en sí no es este rango que acabo de crear. Es la celda a la que realmente se hace referencia en la fórmula que contiene la tasa de interés, que en este caso es vender H ocho. Y eso es todo lo que necesitamos para configurar esta tabla de datos presione OK, y ahí vamos. Está poblado todas las salidas de costo mensual en base a este rango diferente de tasas de interés . Y como puede ver, aquí se genera una sola matriz o matriz de tabla en lugar de 15 fórmulas individuales. Por lo que pasamos a nuestra próxima demo, vamos a seguir ese mismo enfoque, excepto que estamos agregando un conjunto adicional de criterios aquí para diferentes pagos por adelantado así que lo hará aquí es crear una matriz real de valores que den cuenta de no sólo cambia a la tasa de interés pero también cambia al pago inicial ya sea 5 10 o 20% por lo que muy similar proceso aquí y seleccione todo el rango de celdas, incluyendo la fórmula y las celdas de entrada de fila y columna variables. Vamos a entrar en datos. ¿ Y si análisis fechado tabla? Y ahora sí tenemos una celda de entrada de fila. Y esa es la celda en la fórmula que contiene nuestras variaciones de entrada de fila, que son los pagos iniciales en vivo y venden H siete. Ahí vamos. Y de nuevo, nuestro Colin es la célula que contiene la tasa de interés. Apenas nuestro índice de columna y prensa. OK, y ahí lo tienes. Hemos producido una matriz bidimensional única esta vez que produce valores de salida basados en múltiples criterios. Entonces ahí tienes. Estas tablas de datos en excel podrían ser un poco complicadas, un poco incómodas para trabajar al principio. Pero una vez que realmente obtienes un control sobre ellos, podrían ser una gran herramienta para evaluar toda una matriz de resultados basada en entradasvariables entradas 8. Herramientas de consulta de poder: todo bien. Quiero tomarme unos minutos y guiarte por una rápida y breve introducción a una de las herramientas más poderosas de Excel llamada Power Query. Ahora, este es un consejo analítico avanzado de cuatro estrellas. Vamos a hablar de cómo conectar, dar forma, dar forma, transformar y cargar datos de fuentes externas en excel utilizando estas herramientas de consulta de energía. Por lo que en la moda clásica de Microsoft, es posible que veas estas herramientas nombradas todo tipo de cosas diferentes. Dependiendo de la versión de Excel que estés usando, posible que veas power query que podrías ver. Obtener datos que puede ver entrar en transformarse y todo significa lo mismo. Estoy usando office 3 65 pro plus. Y esto es lo que veo en mi pestaña de datos. Tengo un grupo de herramientas llamado Get and Transformation Data y ellos obtienen el comando de datos a la izquierda. Y lo que vamos a hacer en esta demo es conectarnos a una nota de archivo CSP plana que también puedes conectarte a fuentes de base de datos o web ap. Yo no es así y así sucesivamente. Este caso, vamos a mantenerlo sencillo. He puesto a disposición este archivo, es parte del curso. Básicamente, vamos a hacer un análisis rápido de algunos datos de campaña de Kickstarter. Por lo que una vez que apuntemos al archivo verá esta vista previa de consulta y luego haga clic en Editar remolque. Inicia nuestro editor de consultas, y esto se abrirá como un libro separado fuera de nuestro entorno de hojas de cálculo de Excel, y verás una vista previa de tus datos aquí y todo tipo de herramientas que tenemos para dar forma y filtrar y personalizar y transformar estos datos antes de cargarlos en excel. Entonces esto es como nuestro centro de comando para configurar esta consulta o conexión. Ahora, ten en cuenta, no hay manera de que pueda ni siquiera rayar la superficie de todas estas herramientas dentro del alcance de esta demostración en particular. Pero sí tengo un curso completo que va en profundidad en estas herramientas el Power Query, Power pivot y el curso Dax. Ahora, para los efectos de esta introducción, ten en cuenta que existen algunas secciones importantes de esta ventana de edición de consultas. Tienes tu barra de herramientas de edición de consultas en la parte superior con opciones para transformar o agregar nuevas columnas y una ventana en la parte inferior derecha llamada Pasos aplicados. Ahora estos pasos aplicados son cambiadores absolutos de juego porque esencialmente, ¿qué está pasando aquí? es que cada vez que realiza un ajuste o transformación, cualquier operación que aplique a su tabla se registra como un nuevo pasos aplicados exactamente igual que los pasos se graban en una V B una macro. Ahora, una vez que hayas transformado y dado forma a tus datos y estás listo para cargarlos en excel allí a lugares donde puedes cargarlos ya sea en una hoja de trabajo donde vivirá en filas y columnas y celdas, o a los datos modelo, donde se puede comprimir estos datos y almacenar mucha, mucha más información cientos de millones de rosa. Y también es donde realmente se pueden construir modelos de relaciones para crear relaciones de tabla y conectar o fusionar información de múltiples fuentes. Por lo que para resumir rápidamente los casos de uso común número uno conectándose a archivos planos o fuentes de base de datos y luego transformando o filtrando esos datos antes de cargarlos en excel. Para un mayor análisis, número dos Creando un proceso E T L totalmente automatizado, que significa extracto, transformación y carga que podría refrescarse con un solo clic a medida que vuelvan a estar disponibles nuevos datos . Detalles. Aire todo cubierto en mi consulta de poder, power pivot y curso Dax. Entonces con eso, saltemos a excel. Te vas a guiar a través de una demo rápida de cómo podemos usar power query para conectarnos y transformarnos en un archivo CSP externo. Está bien, así que si quieres seguir, encabeza tu tabla de contenidos y busca esta poderosa demo de Corey en nuestros consejos de análisis morados , y cuando te vincules, todo lo que verás aquí es una hoja en blanco. Por ahora. Ahora, para presentar este tema de consulta de poder, he puesto a su disposición un archivo de proyecto llamado Kickstarter projects dot CSP. Siéntete libre de descargar eso para seguir o simplemente siéntate y mira este show. Voy a dirigirme a mi pestaña de datos aquí un par de maneras en que puedo agarrar un archivo CSP ya sea de esto desde el botón de barra de texto CSP o en el menú desplegable get data, que me muestra algunas de las otras fuentes de datos también. Este caso al que voy a entrar desde archivo desde texto slash CSP. Voy a hacer doble clic en el archivo Kickstarter Project que he cargado en mi escritorio. Por lo que lo primero que veremos es una vista previa rápida basada en las 1 ª 200 filas de ese archivo consiguió proyecto I DS nombres, categorías, categorías principales, luego todo tipo de información sobre cuándo se lanzó el proyecto, cuando el plazo es cuántos respaldadores, cuánto dinero se comprometió y demás y demás. Ahora vamos a querer filtrar estos datos, recortarlos a justo lo que necesitamos. Entonces vamos a hacer clic en el dedo del pie del botón editar, realidad lanzar el editor de consultas y verás que se lanza en una nueva ventana fuera del entorno de hoja de cálculo de Excel. Y aquí es donde vamos a hacer todos esos datos modelando y filtrando y transformando antes de que se cargue en excel y te darás cuenta que ya se aplicó unos pasos para identificar la fuente desde mi solo parada aquí promovió los encabezados, y se detectan algunos de los tipos de datos aquí y se aplican esos tipos de datos automáticamente. Entonces, por el bien de la demostración, agreguemos aquí algunos filtros más. Um, tal vez no queremos que todos los datos del plazo aquí tienen proyectos que se remontan al 2009 así que vamos a aplicar una fecha, filtrar y decir proyectos Onley fuera de aquí. Adobe reader que van después, ¿Por qué no decimos 2014 para que podamos ir a diciembre 2014 Seleccionar 31 Pulse OK, hemos agregado un nuevo paso aplicado para filtrar esas rosas, y podemos ordenar esa columna ascendente igual que lo haríamos con cualquier tabla de Excel para ver eso. Ahora nos estamos ocupando de proyectos que sólo tienen plazos que se remontan al 1 de enero. 2015. Ahora la misma historia aquí podemos filtrar hacia abajo por país. Por ahora, sólo es mirar a los Proyectos de la U. S. S. Ahí vamos. Y ahora el estado si campaña fue exitosa o si falló o se canceló filtro de suministro ahí también. Y en Lee, mira los proyectos exitosos. Prensa. OK, ahora podríamos seguir adelante y seguir. Podríamos cambiar los tipos de datos aquí. Podríamos quitar columnas extra, conseguimos algunas redundantes en este caso. Pero por el bien de la demostración, sigamos adelante y pasemos a cargar los datos. Voy a elegir ropa y cargar a desde la parte superior izquierda, y esto me mostrará mis opciones de carga. Ahora bien, si no quisiera cargar estos datos en una hoja de trabajo y realmente almacenar esas rosas esas observaciones en filas y columnas pero podría hacer es Onley crear la conexión y dejarla caer en el modelo de datos. No, es posible que no tengas acceso al modelo de datos, dependiendo de la versión de Excel que estés usando. Y en este caso, quiero reservar la conversación del modelo de datos para un pro tip diferente. Entonces por ahora, vamos a usar la opción de tabla y en realidad cargar estos datos de filtro que creamos aquí mismo en nuestra hoja de trabajo existente, la hoja de trabajo de consulta de energía y vender uno y presionamos. De acuerdo, veremos que aparece nuestro dolor de consulta y dice, Muy bien, estamos cargando algunos datos de ese proyecto de Kickstarter CSP. Cargó 41,097 filas y dejó caer esos datos como una tabla justo aquí en mi poder. Corrie 10. Adelante y cerremos ese dolor desde aquí. Podemos trabajar con esta tabla igual que cualquier otra podemos insertar tabla Pivot, por ejemplo. Dejemos esto en una nueva hoja y tabulemos, y podemos llamar a este pivote Kickstarter, por ejemplo. Y aquí tenemos nuestros campos de esa mesa Kickstarter y aquí podemos trabajar igual que una tabla pivotante normal. A lo mejor quieren desglosar datos por categoría principal aquí en Rose. Y en este caso, veamos la cantidad prometida en Dólares de Estados los EstadosUnidos. Y al igual que cualquier otro pivot, podemos cambiar los formatos numéricos. Moneda podría haber hecho esto también en el editor de consultas. Y vamos a agarrar estos proyectos estatales estado como filtros. Y recuerda, lo arreglamos se filtran por el dedo del pie solo muestran proyectos exitosos. Esa es la única opción que tenemos aquí. Ahora eso es bastante guay. Ya podemos ver cuánto dinero se comprometió por categoría. Ver mucho dinero y diseño de juegos de tecnología, lo cual tiene sentido. Pero esto no es realmente lo que hace que la consulta de poder sea tan poderosa que podríamos haber cargado que CS siendo copiado y pegado en una pestaña. Lo que hace que la consulta de Power sea tan poderosa es que si ahora queremos cambiar algunas de las configuraciones o características de nuestra conexión, tal vez no queremos filtrar hacia abajo los proyectos exitosos de Onley. A lo mejor queremos mirar proyectos fallidos Ahora. En cambio, no tenemos que reinventar la rueda. No tenemos que filtrar manualmente mesa que se dejó caer en su poder. Corey Tam. En cambio, podemos entrar en consultas de datos y conexiones, y podemos hacer click derecho para editar esa consulta del proyecto Kickstarter y comprobarla. Este último paso, este paso Rose filtrado es donde habíamos filtrado hacia abajo a país es igual a U S y el estado del Proyecto igual a exitoso. Todo lo que tenemos que hacer es hacer clic en este pequeño engranaje. Vengo a editar ese paso, y la segunda línea aquí es thes state filter. Haga clic en esta elipse, elimine ese filtro y presione OK y se actualizará para volver a traer esas filas desde el archivo CSP original. Ahora, cuando cerremos y carguemos, verás en nuestra consulta dolor que Kickstarter Project Curry se está refrescando. Y lo que deberíamos ver es que aquí entran más datos en lugar de solo 41,000. Esto debería mostrar un total mayor porque ahora estamos incluyendo proyectos con diferentes estados de proyectos. Y eso es exactamente lo que vemos Ahora vemos 127 mil 875 filas cargadas en esta mesa y al igual que esperarías, podemos cerrar nuestro dolor, dirigirnos a nuestro pivote, saltar a herramientas de mesa pivotante y refrescar, y ahora échale un vistazo. Cuando nos fijamos en nuestro filtro estatal. Ahora bien, no sólo tenemos opciones exitosas. Hemos fallado, cancelado en vivo y suspendido también. Y ahí lo tienes. Tan muy, muy rápido, muy superficial demo de algunas de estas herramientas básicas de consulta de energía. Nuevamente, si quieres ese profundo conocimiento fundacional, echa un vistazo a la consulta de poder, Power pivot y Dax curso y ve a disfrutarlo realmente. Pero ojalá, por ahora que al menos te inspiró jugar con algunas de estas herramientas más avanzadas y ver cómo podrían ser capaces de revolucionar tu flujo de trabajo en excel. 9. Modelado de datos 101: todo bien para este pro tip. Me gustaría presentarles un concepto muy poderoso y excel. Vamos a hablar de cómo construir un analizar modelos de datos relacionales en Excel ahora sobresale. El modelo de datos se utiliza para una serie de propósitos diferentes. Por una parte, puedes comprimir cantidades extremadamente grandes de datos cientos de millones de rosa, si no más. En segundo lugar, se pueden crear relaciones de tabla, que es una forma mucho, mucho más eficiente, una forma elegante de combinar datos a través de tablas u fuentes sin coserlos manualmente junto con fórmulas auto. Y en tercer lugar, se pueden agregar columnas y medidas calculadas completamente nuevas utilizando un lenguaje de fórmulas denominado DAX o expresiones de análisis de datos. Por lo que hay algunas formas de conseguir datos en el modelo de datos. Las formas más comunes de crear una nueva conexión a una fuente de datos utilizando una herramienta como power query, también conocida como get data o get transform y carga directamente desde esa conexión al modelo. Una forma más sencilla de agregar fechado al modelo, especialmente por el bien de la demostración, es tomar una tabla de una hoja de trabajo, que generalmente es una tabla mucho más pequeña y hacer clic en el botón Añadir a modelo de datos dentro de la potencia tabulador de pivote. Ahora este es un buen punto para hacer una pausa y hacerte saber que algunas versiones de Excel no tendrán acceso a estas herramientas de modelado de datos. Entonces si no ves tu pestaña de pivote de potencia, el primer lugar para comprobar son las opciones de archivo. Adan y busca tu calma Adan's específicamente, Si aún no ves poder, pivote ahí. Yo recomendaría Google ¿Dónde está el pivote del poder? Y serás llevado al sitio web de soporte de oficina que se ve así. Y aquí en realidad verás los productos de oficina exactos que sí tienen acceso a estas herramientas de modelado de datos y alimentación de pivote. Ahora, suponiendo que sí tenga acceso al modelo de datos, una vez que haya agregado sus tablas al modelo, podrá administrar o editar su ventana de modelo de datos y ver algunas vistas diferentes como ve aquí a la izquierda, tenemos la vista de diagrama, en cuyo caso vemos que nuestras tablas aparecen como objetos individuales, y ahí es donde podemos crear las relaciones de tabla reales entre ellos, en base a cosas llamadas claves primarias y externas. A la derecha, tenemos nuestra vista de datos, que es un tipo de diseño tabular más tradicional. Pero la belleza del modelo de datos es que una vez definidas estas relaciones, una vez que hayas creado lo que llamamos un modelo de datos relacional, que, para efectos de esta conferencia estoy definiendo como un grupo de tablas relacionadas, entonces podemos insertar una tabla dinámica para explorar y analizar los datos a través de todas estas fuentes relacionadas en una sola vista. Y por eso llamamos a esto un pivote de poder. Se ve y se siente exactamente como una mesa pivotante regular, pero está sentado encima de un modelo de datos. Casos de uso tan comunes aquí, combinando información de múltiples fuentes sin realmente mezclarla combinando o usando funciones como Look Up o Index y dos. Esta es la base para construir soluciones de inteligencia empresarial robustas que puedan integrar y fusionar fuentes de datos de todo tipo de lugares, como los datos de ventas. Finanzas de RRHH, mercadotecnia, etcétera. Entonces con eso, saltemos a excel. Voy a correr por una demo extremadamente rápida, muy breve para mostrar algo de lo que llevan estos modelos de datos y herramientas de pivote de potencia. De acuerdo, así que si te sientes valiente y te gustaría seguir adelante, dirígete a tu tabla de contenidos en tu libro de trabajo de protesta buscando aquí los consejos de Purple Analytics y saltemos al modelado de datos Demo de cuatro estrellas demo. Seguiremos adelante y enlazaremos a esa hoja, y lo que estamos viendo aquí es una tabla llamada Transacciones. Tenemos la columna de fecha de transacción A consiguió el producto que se vendió y Colin ser alguna información sobre el cliente en base a un cliente i d. aquí en la columna C y luego los valores reales aquí, la cantidad vendida en la columna D Ahora en su propia, esta mesa no nos es muy útil. Podríamos enchufar esto en una mesa pivotante y podríamos enrollar la cantidad o las ventas. Ya sabes, para producto me ds o clientes o días. Pero de qué sirve realmente nos dice que vendimos producto número 76 seis veces, ¿ verdad? No tenemos contexto adicional sobre estos I ds. Entonces lo que realmente nos gustaría Aquí hay algunas mesas de búsqueda que potencialmente podrían mapear este producto yo d o este cliente i d dos dimensiones adicionales o piezas adicionales de información que podemos usar para realmente aprender más sobre estas ventas. Y de hecho, eso es exactamente lo que tenemos. Si ampliamos las columnas del grupo. Ya verás tenemos tres mesas más. Consiguió una mesa verde llamada productos. Tenemos una mesa naranja llamada clientes y una mesa amarilla llamada Calendario. Ahora, como cabría esperar, cada una de estas tablas incluye una columna I D o, en el caso del calendario, una columna de fecha, que puede mapear esta información de nuevo a nuestras transacciones. Datos en columnas Athor profundo. Es decir, si conocemos el producto I D, que hacemos, entonces creando una conexión o relación con la tabla de productos, entonces también conocemos la marca del producto y el nombre del producto, así como el precio y costo de venta al por menor. Lo mismo va con el cliente i D. Esa clave se puede usar para sacar información sobre esos clientes los nombres, ciudades, países, estado civil, género qué tipo de membresía tienen. Toda esa información se puede atar a esta tabla de transacciones porque tenemos las columnas clave que las pueden conectar. Por lo que podría estar tentado a seguir adelante y comenzar a escribir funciones como el look up o el índice match para empatar estos juntos en una sola mesa maestra. Porque en la vieja escuela, Excel estaba acostumbrado a necesitar una sola tabla o fuente única para tablas pivote. Esa fue siempre la única gran limitación de los pivotes. Entonces tal vez escribes, ya sabes, si miras aquí arriba, estamos mirando el producto que d dentro de nuestra tabla de productos y agarrando la segunda columna con una coincidencia exacta. Y boom, ahí vamos. Tenemos nuestra marca de producto ahora en la columna E. Así que atamos esa marca de producto a nuestra tabla de transacciones. Ahora escucha. Podríamos continuar con este proceso para tirar en nombre del producto, precio de venta al por menor así como toda la información del cliente y toda nuestra información del calendario también. Pero hay dos problemas con ese número uno. ¿ Y si estas mesas incluyeran cientos de columnas en lugar de sólo un puñado como lo tenemos aquí de repente? Este enfoque no es del todo un escalable, y a lo que hemos creado es una tabla realmente ineficiente con una tonelada de valores duplicados que simplemente no son necesarios. Entonces sigamos adelante y eliminemos esa columna aquí, y te voy a mostrar cuanto más elegante sea el enfoque más sofisticado para lograr lo mismo usando el modelo de datos. Por lo que ya he agregado estas tablas al modelo de datos, pero Si te gustaría practicar, seleccionarías una tabla individual, perforarías tu poder, perforarías tu poder, pivote el tiempo y haz clic en este anuncio a modelo de datos botón derecho aquí Ahora, porque ya los he agregado, podemos ir a nuestro botón de gestión que abrirá aquí la ventana del modelo de datos. Estamos en nuestra vista de datos. Por defecto, podemos ver estas cuatro tablas en formato tabular como pestañas separadas y vista de diagrama, que en realidad muestra cada una de nuestras tablas como un objeto en el lienzo. Ahora notarás estas líneas que conectan las tablas representan relaciones. Entonces en este caso, producto I D. Lo que se destaca, es lo que conecta los productos con las transacciones. Contamos con un cliente, i d, que conecta a los clientes con las transacciones. Y por último, tenemos nuestra fecha de transacción la cual conecta con el campo de fecha en nuestra tabla de calendario. Ahora, para mostrarte cómo crear estas relaciones desde cero, yendo adelante a la pestaña de diseño aquí, administrar relaciones, y todo lo que tenemos que hacer es seleccionar estas tres relaciones, presiona eliminar, está bien. Y cuando cerremos ese cuadro de diálogo ahora, veremos esas mesas un poco desconectadas de nuevo. Y todo lo que necesitamos hacer para simple es agarrar el campo que queremos y conectarlo a una clave primaria dentro de la tabla de búsqueda. Por lo que fecha de transacción a la fecha producto i D al producto i d. cliente i d al cliente 18. Y es un simple es que en cuestión de menos de 10 segundos, hemos replicado todo el esfuerzo de crear esas funciones de búsqueda o indexación para coser manualmente esos datos juntos. Y hemos logrado exactamente el mismo resultado. Y ahora aquí está la mejor parte. Vuelvo a la pestaña de inicio aquí, inserto una tabla pivotante en una nueva prensa de hoja de trabajo. OK, vamos a hacer doble clic para nombrar esta vez algo así como pivote modelo de datos. Aquí está la belleza de la misma. Ahora estamos lidiando con un pivote de potencia, que técnicamente es el mismo diseño de tabla de pivote viejo solo sentado en la parte superior de un modelo de datos. Y tenemos acceso a todas nuestras tablas de modelos de datos, calendario, clientes, productos y transacciones así como a otras tablas del libro de trabajo, que en este caso no serán de ayuda porque no hay relación con encontrar entre ellos. Entonces si perforamos en nuestra tabla de transacciones, verás aquí algunas medidas diferentes que he definido transacciones, cantidad total e ingresos Estas fueron diseñadas con un lenguaje llamado Dax, que está fuera del alcance de este curso, pero es algo que sí cubro en mi parapeto de consulta de poder y curso Dax. Entonces vamos a agarrar la cantidad total. Tira eso en nuestros valores. Aquí y ahora podemos acceder a cualquiera de nuestras otras tablas y desglosar esas cantidades de todas las formas diferentes, como Subproduct Brand on Rose, por ejemplo, aquí podemos ver que los productos americanos vendieron 1200 atómicas productos vendidos 695. Podemos agarrar un campo de nuestra mesa de clientes también, como la ciudad del cliente. Arrastre eso en y etiquetas de rol secundarias. Pope marca de producto fuera. Entonces como puedes ver, estoy manipulando este pivote exactamente como manipularía cualquier otra tabla pivotante en Excel . La única diferencia es que ahora soy capaz de acceder a campos a través de cuatro tablas diferentes que están conectadas solo con las relaciones que definimos dentro del modelo de datos. Cosas increíblemente poderosas 10. Funciones de CUBE: todo bien. Es hora de sumergirse en un consejo analítico de cinco estrellas, de nivel verdaderamente experto. Vamos a hablar de cómo explorar modelos de datos fuera de pivotes utilizando algo llamado funciones de cubo. Ahora, dos adversidades rápidas antes de sumergirnos. Número uno. Si no has visto el pro tip de modelado de datos o no has aprendido sobre modelos de datos en el pasado y te animas mucho a hacerlo Número dos, vas a necesitar una versión de Excel. Es compatible con el modelo de datos y con pivote de potencia. Si no estás seguro, ve a Google search for Where is power pivot y saltarás al sitio web de soporte de oficina que se ve así. Y aquí podrás ver las versiones exactas de Excel que incluyen acceso a estos modelos de datos y herramientas de pivote de potencia. Ahora volvamos a ello. Así funciones de cubo. La forma más fácil de explorar datos en un modelo de datos es a través de una tabla de pivote o un pivote de potencia. Pero lo que hacen las funciones de cubo es permitirle sacar un recuperar valores filtrados específicos fuera de su modelo de datos y tirarlos directamente en las celdas de la hoja de cálculo. Entonces, por ejemplo, si quieres crear una vista que se vea así. Puedes usar funciones de cubo para hacer esto, y en mi experiencia, hay cuatro tipos de funciones de cubo que uso casi exclusivamente. El primero se llama conjunto de cubos, y esencialmente un conjunto de cubos es una colección de elementos o miembros de cubo de su modelo. Es esencialmente equivalente a una columna completa de una tabla en su modelo. En este caso, hemos definido un conjunto de cubos que contiene información sobre las marcas de productos. Ahora, dentro de los conjuntos de cubos, tienes algo llamado miembros de Cube y estos aire sombreados en azul aquí en el miembro de Visual Cube es un solo elemento desde dentro de un conjunto de cubos. Entonces, en otras palabras, es un elemento fuera de una columna de tabla aquí. Estamos viendo diferentes valores dentro de la columna de membresía en nuestra tabla de clientes, dorado, plateado, bronce y normal. Y tenga en cuenta que también usamos miembros Q para definir medidas cuantitativas o medidas calculadas como transacciones o cantidad. A continuación, tenemos un tipo especial de miembro llamado miembro clasificado cubo. Estos están sombreados en verde aquí en lo visual y estos aire, igual que ustedes miembros, sus elementos individuales dentro de un conjunto de cubo. La única diferencia es que se basan en un rango de orden. Y eso es lo que nos permite hacer cosas como mostrar los cinco mejores productos por cantidad, por ejemplo, como se ve aquí. Y por último pero no menos importante, tenemos nuestro cuarto tipo primario de cubo, que es el valor del cubo, y esas son todas las celdas sombreadas en amarillo. Ahí, los valores numéricos reales que se estaban agregando en base a un conjunto de expresiones miembro. Y esas expresiones miembro nos ayudan a filtrar esos valores para recuperar los números adecuados . Ahora estos condenan finitamente ser complicados y un poco desconocidos para trabajar. Al principio te voy a guiar por exactamente cómo construir una vista igual a esta. Pero ten en cuenta que también hay muchos otros tipos de funciones de cubo, y he incluido un enlace para más información aquí. lo que este breve enlace te llevará es a la oficina Documentación de soporte Página cuatro funciones de Cubo , que se ve así, y aquí puedes desplazarte y aprender un poco más sobre los fundamentos de Cuba funciones y algunas de estas otras opciones que no estoy cubriendo. Casos de uso tan comunes aquí para un edificio basado en hojas de cálculo informes o tableros que se sientan en la parte superior de los datos en su modelo de datos sin tener que depender de tablas pivote y de funciones de cubo podría ser una gran manera de documentar todos los conjuntos y miembros dentro de su modelo de datos. Entonces con eso, saltemos a nuestro libro de trabajo pro tip y practiquemos la construcción de una vista de reporte como la que ves aquí. De acuerdo, así que si estás siguiendo junto con el curso,ya sabes, ya sabes, el taladro tenía a tu tabla de contenidos desplazarse a tus consejos de análisis morados aquí, vamos a perforar en esta demo de cinco funciones de Star Cube. Adelante y enlaza a esa hoja. Ahora, lo que estás viendo aquí es una plantilla básica en blanco que he creado de un reporte de muestra . Aquí no hay fórmulas ni nada. Es solo formato. Se vende. Y nuestro objetivo es convertir esto y conectarlo en un informe totalmente funcional que extrae datos del modelo de datos subyacente. Ahora, para empezar, necesitamos hacer una revisión rápida del modelo de datos con el que estamos trabajando. Entonces voy a saltar a la pestaña de pivote de potencia, haga clic en administrar el modelo de datos, y saltemos a la vista de diagrama aquí Así que este es el modelo con el que estamos trabajando en este cuaderno de trabajo. Tenemos datos de transacción con tres medidas calculadas transacciones, cantidad total e ingresos. Esas en las medidas son métricas que vamos a meter en nuestro informe. Y también tenemos una serie de campos de los que podemos sacar. Estas tres mesas de búsqueda consiguieron campos de calendario como el día, el mes, trimestre y año información de clientes como países y ciudades, género y estado de tarjeta de miembro y productos que incluyen cosas como la marca, el nombre, el precio de venta al por menor y el costo. Entonces sigamos adelante y en realidad insertemos una tabla pivotante de nuestro modelo de datos, y la voy a dejar caer en un libro de trabajo existente. Lo voy a poner justo aquí, um, en nuestra hoja de trabajo de funciones de cubo. Y dejémoslo aquí mismo y vendamos H dos y presionemos OK. Y lo que haremos es usar esta tabla dinámica para ayudar a detectar la cola de cheques y asegurarnos de que los números que se llenaban con funciones de cubo sean precisos. Y luego una vez que lo hayamos hecho, podemos seguir adelante y eliminar el pivote mismo y quedarnos solo con el informe personalizado que hemos construido para que como puedes ver aquí tenemos algunos breakouts diferentes de los datos. Estamos viendo tres métricas diferentes. Transacciones, cantidad e ingresos. En primer lugar, lo estamos desglosando por mes para los tres meses anteriores. Abril, mayo y junio. Entonces estamos haciendo un desglose por tipo miembro dorado, plateado y bronce. Y esta tercera tabla aquí está mostrando las ventas por producto para los cinco productos principales basados en transacciones, cantidad o ingresos. Entonces empecemos en la cima con ventas por mes. Y voy a perforar este pivote, y voy a tirar en algunos de estos campos transacciones, cantidad e ingresos. Y en este caso, queremos desglosarlo por mes desde mi tabla de calendario Agarra nombre del mes tirando en rosa . Y en este caso, todo lo que realmente nos importa es la prensa de abril, mayo y junio. OK, vamos a solucionarlo un mareado. Entonces esta es una pequeña vista previa de la vista que estamos tratando de lograr aquí mismo tirando esos mismos valores en las propias celdas de la hoja de trabajo. Entonces si recuerdas la función de valor del cubo era el tipo de cubo que puede tirar o agregar esos valores numéricos. El problema es, si empezamos por tan solo escribir el valor del cubo. Estamos abiertos el paréntesis. Nuestra conexión va a empezar con una cotización abierta y va a ser este modelo de datos de libro de trabajo. Se puede presionar tab toe lock que en cerrado La cita Ahora el segundo argumento y el tercero y el cuarto y el quinto para una función de valor de cubo básicamente te piden que apuntes a una expresión miembro. Y esa expresión miembro ayuda a decirle a Excel cómo filtrar los valores a devolver. Entonces en este caso, estamos filtrando hacia abajo en base a dos condiciones o criterios diferentes. El nombre del mes y ser cinco y coma al nombre de la medida en la Celda C cuatro. Entonces eso es realmente todo lo que necesito para esta función de valor de cubo. Cierro el paréntesis prensa enter, me meto en N A. Y la razón por la que obtengo este error es porque los valores Q no saben interpretar las celdas que solo contienen texto. Entonces ahora mismo esta célula no significa nada. Es sólo una cadena de texto que dice transacciones. Lo mismo con mis nombres de mes. Abril, mayo junio plata dorada, bronce. Entonces lo que vamos a necesitar hacer es convertir estas cadenas de texto en funciones de miembro de Cube reales . Entonces empecemos con nuestras métricas aquí, podemos escribir igual Cube member open Cotizar este cuaderno modelo de datos Comienza así cada vez que comin sobre la expresión miembro y este es el campo o el ítem de nuestro modelo de datos que nosotros quieren capturar en esta celda. Por lo que abre otra cotización. Esto le permitirá acceder a las tablas y campos de su modelo. Este caso, queremos un campo a partir de nuestras medidas. Y si entramos a un periodo, nos lleva al siguiente nivel en la jerarquía, que es la lista real de opciones de medida aquí. Y queremos transacciones. Entonces lo voy a tabular, cerrar la cotización, cerrar el paréntesis, presionar enter. Por lo que ahora todavía dice transacciones. Pero ahora se trata de un miembro que real, y vamos a pasar por el mismo proceso aquí para April es miembro Cube de este modelo de datos de libro de trabajo y la expresión miembro, recuerda, abrió de nuevo la cotización. Esta vez viene del periodo de la tabla de calendario a la lista de columnas queremos el nombre del mes Colin. Y ahora, porque queremos un mes específico dentro del nombre de mes Colin, vamos a agregar un periodo más y escribir el nombre del mes entre paréntesis. Por lo que queremos específicamente el mes de abril y luego cerrar la cotización. Cierra el paréntesis. Siéntete libre de hacer una pausa en la pantalla por un momento para asegurarte de que tienes la función correcta y voy a presionar, entrar y comprobarlo. Pasan dos cosas. Número uno. Mi celda miembro cue ahora toma un valor de abril, que no es sólo texto. Es un miembro que real, y nuestro valor de cubo ahora está devolviendo un número adecuado, que es 6588 el cual podemos mapear a nuestro pivote como Q. Y eso sí coincide. Por lo que vamos por buen camino. Y ahora que hemos definido algunos de estos, es muy fácil aplicarlos al resto de nosotros mismos para que podamos agarrar esta columna de transacciones , copiarla, pegarla dos veces más, y luego simplemente ajustar ese último argumento de las transacciones a la cantidad total y de las transacciones a la prensa de ingresos. Entra mismo cosa con abril, mayo y junio. Voy a acelerar esto dos veces más y sólo cambiar el abril en el argumento final. Dos principales y finalmente a junio. Y al igual que las funciones regulares de Excel. Aquí podemos tratar cuidadosamente nuestros tipos de referencia porque nuestros meses siempre viven en la columna B . Podemos arreglar esa referencia de columna. Y porque nuestros encabezados son medidas siempre viven en la Fila cuatro. Podemos arreglar esa referencia de fila y presionar enter y echar un vistazo a esto. Arrastrado hacia abajo, arrastra sobre y boom! Tenemos todos nuestros valores Q poblados. Podemos comprobar contra el pivote. Todo se ve bien. Y ahora estamos empezando a Cruz aquí. En realidad podemos agarrar a uno de estos miembros del cubo a partir del mes. Podemos pegarlo en la plantilla de tipo miembro aquí y ahora, en lugar de la tabla de calendario, vamos a abrir esto y queremos que se sienta de nuestra tabla de clientes. Y la columna que queremos es la columna de la tarjeta de miembro. Y en este caso, para el primer valor, los miembros específicamente que queremos es de oro, por lo que puedes escribirlo en cotización cerrada, cerrar el paréntesis, presionar enter, y luego copiar que dos veces más para poblar plata y bronce. Entonces plata ahí mismo y bronce justo aquí y ahora échale un vistazo. Copia este valor de cubo, pégalo hacia abajo en lee. Se están moviendo las referencias que queremos mover, las otras son fijas. Siguen señalando los encabezados correctos aquí arriba así como los miembros adecuados para el tipo de miembro aquí en la columna B. Así que sigamos adelante y demos. Fue mes pobre. Nombre fuera, tarjeta de miembro completa en, y podemos simplemente tipo de re ordenar esto un poco de oro en la parte superior y plata que el bronce. Usa los valores que deberíamos estar viendo. Y boom! Esos son los valores que sí vemos aquí mismo a partir de nuestros valores Q. Entonces somos dos por dos. Lo último que tenemos que hacer aquí es poblar estas ventas por matriz de producto, que va a tirar de los cinco primeros productos. Entonces aquí está la captura. No sabemos de antemano cuáles son los cinco productos principales, así que no podemos empezar a escribir miembros regulares de Cube desde cero como hicimos con mes o tipo de miembro. Ahora podríamos ordenar las diferentes marcas de productos en el pivote para averiguar cuáles serán los cinco primeros . Pero si queremos que esta vista cambie dinámicamente y siempre se ordene en base a los datos actuales, tenemos un mejor enfoque para hacerlo y lo que vamos a hacer aquí se introduce un cubo conjunto primero aquí en la columna B 16. Entonces sigamos adelante y tecleemos Cube set function esta vez como siempre, vamos a empezar con la conexión a este modelo de datos de libro de trabajo. Y ahora la expresión set va a ser la lista o la colección de marcas de productos, que vas a venir de nuestra tabla de productos. Por lo que abre la cotización productos dot marca de producto. Y ahora aquí está la llave. El último poquito opción después del punto no quiero seleccionar todos, y no quiero seleccionar ningún elemento específico. Entonces voy a escribir miembros, que va a obligar a este conjunto de cubo a almacenar todos los posibles miembros dentro de esa marca de producto Colin cerró la cotización, y ahora necesitamos actualizar o poblar los argumentos adicionales aquí. Entonces para el título, lo que queremos que muestre la celda, hagamos marcas. Y ahora el orden de clasificación. Es así como determinamos cómo se ordenan los miembros clasificados de cubo dentro de este conjunto. Entonces en este caso, vamos a ordenar los volúmenes altos descendentes en la parte superior. Ese es el número dos. ¿ Y por qué queremos ordenar? Bueno, podemos elegir cualquiera de estas medidas. Sigamos adelante y elijamos las transacciones. Y porque eso viene de nuestra tabla de medidas y abrir la cotización mide la transacción de punto cerrado La cotización. Cierra el paréntesis, pausa la pantalla por un momento. Asegúrate de que bajaste eso. Funcionamiento tan largo y vamos a entrar eso en. Por lo que siempre ver es la palabra marcas aquí. Pero esta celda contiene todo un conjunto de miembros, y eso nos va a permitir ahora usar algo llamado miembro clasificado cubo para sacar elementos individuales de ese conjunto. Por lo que echa un vistazo es igual a miembro clasificado Cube de este modelo de datos de libro de trabajo. Ahora la expresión set es una referencia a un conjunto de cubos, que acabamos de crear aquí en Selby 16 Press. Pagar un bloquearlo porque ese conjunto de cubos siempre vivirá ahí. Y ahora para el rango, notarás que he creado un pequeño índice del 1 al 5. Ahí en la columna A. Ese va a ser nuestro índice de rango. Entonces, solo señalemos ese número de rango, y podemos seguir adelante y cerrar la función presionar enter, y el primer elemento que vemos es todo. Si hacemos clic y arrastramos hacia abajo, vemos algunas marcas de productos diferentes apareciendo Karmanos, Telltale Ebony y High Top. Ahora los temas de que todo no es una marca individual. En realidad es el total a través de todas las marcas y eso siempre aparecerá en la parte superior, tener un conjunto de cubo. Entonces la forma más fácil de sólo por eso es simplemente comenzar con el rango dos, luego tres y cuatro que cinco, luego seis. Y porque hemos puesto una referencia es, bueno, copiar ese valor. Pégalo hacia abajo, arrástrelo hacia abajo, arrástrelo sobre, y se ve bien. Pero hagamos una final Que A y R pivote una tarjeta de miembro completa hacia fuera perforación a productos marca de producto en, y vamos a ordenar esas marcas de productos descendiendo por transacciones y prensa. OK, échale un vistazo. Karmanos Telltale Ébano Hi Top Tri Estado. mismos cinco exactos que poblamos usando miembros de rango de Cube y conjuntos de Cube, y parece que las transacciones, cantidad y valores de ingresos coinciden perfectamente. Por lo que el pivot ha hecho su trabajo y recomienda mantener uno solo para una exploración de datos rápida y fácil . Pero en este caso, podemos seguir adelante y seleccionar esas columnas, eliminar el pivote, y ahí vamos. Nos quedamos con el formato personalizado perfecto e informe que construimos en su totalidad usando fórmulasde cubo fórmulas 11. Simulación de Monte Carlo: está bien, quiero compartir una de mis técnicas favoritas de Analítica Avanzada en Excel. Se trata de un consejo de cinco estrellas, nivel experto. Lo que vamos a hacer es construir nuestro propio modelo de simulación de Monte Carlo enteramente dentro de Excel. No V B A. Sin macros, Sólo recto arriba vender fórmulas y tablas de datos. Ahora Simulación de Monte Carlo. Normalmente se usa para predecir la probabilidad de algún resultado futuro o resultados basados en muestreo aleatorio repetido. En otras palabras, simulamos aleatoriamente el mismo resultado una y otra vez y otra vez y luego analizamos la matriz resultante de resultados. Entonces, ¿qué mejor manera de demostrar la simulación de Monte Carlo que construir nuestro propio simulador de ruleta en Excel? Entonces la forma en que esto funciona es que puedes colocar cierto tipo de apuesta roja o negra, par o impar, números específicos, y cada apuesta está asociada a un pago dado. Por lo que después de colocar su apuesta, determinar una apuesta estaban usando corrió entre dos esencialmente aleatorizar o simular un giro de la rueda de la ruleta. En este caso en particular, hemos apostado $10 en rojo. El resultado es el número 23 rojo. Por lo que ganamos 10 dólares. Pero el objetivo no es solo sentarse aquí y dar vueltas aleatoriamente una y otra vez. Lo que queremos crear es algo así donde realmente estamos registrando los resultados de una serie de simulaciones en este caso, 10 giros para luego evaluar esos resultados y resumirlos con funciones básicas de estadísticas . Entonces aquí estamos calculando la ganancia o pérdida total, el número de giros, el número de victorias frente a pérdidas, el promedio de porcentaje de ganancias, retorno por giro y max básico y hombres. Y la herramienta que estamos usando toe realmente conducir estas simulaciones es sobresale tabla de datos. Entonces vamos a dirigirnos a nuestra pestaña de datos, caer en nuestras herramientas de análisis qué pasa si y usar la tabla de datos para crear y registrar tantas simulaciones como determinemos. Ahora. Obviamente, estamos usando esto para crear algo divertido e interesante. Pero hay una serie de casos de uso muy serios, muy realistas donde esto podría entrar en juego para uno simulando aleatoriamente un modelo de negocio miles de veces con el fin de entender la probabilidad de algo así como una ganancia versus una o para construir sobre esa creación de modelos predictivos que realmente puedan cuenta de un determinado grado de incertidumbre para uno o más insumos modelo como lo que harán las tasas de interés en el futuro o cómo cambiarán los costos de suministro. Por lo que esta es una herramienta increíblemente poderosa de ciencia de datos y análisis con la que podemos jugar y aprender aquí mismo en el familiar entorno excel. Entonces sigamos adelante y saltemos a su cuaderno de trabajo pro tip y practiquemos la construcción de algunas de estas simulaciones. Muy bien, Así que si quieres seguir adelante, dirígete a tu tabla de contenidos, desplázate a los consejos de análisis aquí en morado y vamos a saltar a nuestra demo de simulación de Monte Carlo. Adelante y enlaza directamente a esa pestaña, y aquí encontrará nuestro simulador de ruleta. Ahora, para aquellos de ustedes que no están familiarizados con el juego de Relent, así es como luce la mesa. Tienes 36 números, medio negro, medio leído, así como a números verdes cero y doble cero. Por lo que el distribuidor hará girar una rueda que contiene las 38 ranuras asociadas a cada uno de esos números. En cuanto a las apuestas faras, puedes realizar apuestas de varias maneras diferentes, que puedes ver aquí en esta celda desplegable. Puedes apostar por números individuales, que son los resultados más infrecuentes y por lo tanto el pago, el más alto en este caso, 35 dólares a 1 en tu apuesta. También puedes apostar en el 1er 3er de números. 1ra 12 2da 12 o 3ra 12 la 1ra 2ª o tercera columna de números. El primer medio segundo, la mitad par números. Números impares, rojos o negros. Entonces como ejemplo sencillo, digamos que apostamos por el negro. Podemos introducir cierta cantidad de apuesta, digamos $10. Este pago nos dice que apuesta por el negro paga 1 a 1. Entonces si ganamos, obtenemos 10 dólares. Perdemos, perdemos los $10 que apostamos. Entonces a partir de aquí comprimimos el botón F nueve calcular o dirigiéndonos a su pestaña de fórmula y rápidamente calculamos ahora botón para simular diferentes giros de la rueda. Entonces, como pueden ver, hemos apostado por el negro. Entonces cualquier resultado rojo significa que perdemos nuestra apuesta por debajo de $10. Cualquier resultado negro significa que ganamos $10 por culpa del oneto. Un pago. Ahora, lo que realmente nos importa es la información aquí abajo. Nuestros datos de simulación y nuestros resultados de simulación así aquí mismo. Aquí es donde vamos a utilizar un dedo de tabla de datos, en realidad girar la rueda 10 veces virtualmente y registrar los resultados de cada giro aquí en columna C y ya he poblado algunas funciones en Colin F. Para resumir. Esos resultados han creado un rango con nombre llamado Spins, y estamos usando un básico algunos para calcular la pérdida total de ganancia. Estamos tomando un máximo de la columna B para el número de giros usando contar si para calcular los vientos y así sucesivamente y así sucesivamente. Entonces sigamos adelante y empecemos con un pequeño número de simulaciones, solo 10 giros. Entonces lo que vamos a hacer aquí es seleccionar todo este rango desde B 15 hasta C 25 y recordar que cuando usamos tablas de datos, una de las celdas de la selección necesita contener la fórmula que estamos simulando, que en este caso, es el resultado de giro alojado y vendo yo nueve. Por lo que simplemente he hecho referencia a ese resultado aquí mismo en C 15 para que podamos seleccionar toda la referencia a medida que insertamos nuestra tabla de datos. Así que dirígete a tu pestaña de datos aquí. Vamos a ir al qué pasa si las opciones de análisis ponen tabla de datos en la parte inferior, y aquí tenemos nuestros dos criterios por fila. Entrada en nuestra entrada de columna. Ahora no tenemos ningún valor variable que estuviera probando en filas, así que eso obviamente va a estar en blanco. Pero aquí es donde las cosas se ponen un poco extrañas. Sí tenemos diferentes valores aquí en nuestra columna, por lo que necesitamos algún tipo de entrada de columna. Pero estos valores en realidad no se alimentan de ningún insumo que impacta nuestro resultado. En otras palabras, realidad no estamos enchufando el número uno, dos o tres en alguna entrada de fórmula. Simplemente queremos aleatorizar esa fórmula un cierto número de veces y hacer que Excel se dé cuenta que todo lo que tenemos que hacer es hacer referencia a una celda en blanco. Por lo que Selby, 14 está completamente en blanco. Podemos usar eso para una columna, ingresar y presionar OK, y ahí tienes. Una vez que hemos hecho eso, Excel ahora está iterando a través de esta tabla de datos, se podría ver que se creó una sola matriz que contiene todos los resultados, y se registra un Siris de resultados, victorias y pérdidas en este caso basado en 10 giros diferentes de la rueda. Entonces en este primer ejemplo, estamos apostando una apuesta de 10 dólares al negro. Lo giramos 10 veces. Terminamos ganando 40 dólares porque golpeamos a negro siete veces. Golpeamos rojo o verde, lo que significa que perdimos tres veces. Por lo que nuestra probabilidad de ganar fue del 70% y nuestro promedio de retorno por giro fue de $4. Entonces sigamos adelante y probemos otra simulación simplemente calculando ahora. De acuerdo, esta vez perdimos 20 cuando el porcentaje fue de sólo 40% otra vez. Ahora perdimos 60. Sólo ganamos dos veces de 10 giros. Entonces otra vez perdió 60. Perdidos, 40 quebrados. Incluso ese tiempo perdió 20. Entonces como puedes ver, hay bastante variabilidad en términos de esa probabilidad de ganar. Ahora conocemos las estadísticas reales. Aquí conocemos las probabilidades de rial. Las probabilidades de ganar una colocación roja o negra en la ruleta son iguales a 18 resultados ganadores de 38 resultados posibles, lo que equivale al 47.4%. Y la razón por la que esta probabilidad de ganar que vemos aquí y vendemos F 19 está rebotando por ahí tan salvajemente y ni siquiera realmente apuntando a esa verdadera probabilidad es que estamos lidiando con un tamaño de muestra muy, muy pequeño. Cualquier cosa puede pasar en 10 giros. Podrías ganar 10 de 10 podrías perder 10 de 10. Entonces lo que me gustaría hacer aquí es repetir el proceso no por 10 giros, sino por 10 mil giros y ver qué pasa ahora. Tenga en cuenta que no puede simplemente eliminar una celda individual de su tabla de datos, que es una matriz. Tienes que seleccionar todo el asunto, empezando con C 16 va a controlar la flecha de desplazamiento hacia abajo y luego eliminar. Y también voy a eliminar los números de índice de giro aquí y poco pro tip aquí. Voy a seleccionar el número uno, cursor sobre la esquina derecha de la celda, mantener el clic derecho en mi ratón mientras arrastraba hacia abajo y hacia arriba. Y cuando suelte, conseguiré este menú secreto aquí que tiene una opción de Siri en la parte inferior. Y lo que quiero hacer es llenar un Siris en esta columna. Yo quiero pisar enteros de uno y quiero parar en 10,000. Presiona OK, y ahí vamos. Si controlo nuestro abajo, verás que ha creado un Siri todo el camino bajando a 10,000 giros ahora mismo proceso que antes. Al seleccionar este control de rango, desplaza Flecha hacia abajo para agarrar todo el rango de celdas Datos . ¿ Qué pasa si análisis Tabla de datos, Sin fila Columna de entrada. Entrada igual a Selby 14 negro y prensa. OK, y ahora va a pensar por un rato porque está iterando 10,000 veces y grabando los resultados y vamos a ver qué pasa. Ahí vamos. Por lo que estamos haciendo la misma apuesta que antes, apostando $10 al Negro. La única diferencia es en lugar de hacer esa apuesta 10 veces, acabamos de hacer esa apuesta 10 mil veces. Fue una noche muy larga en el casino. Y el resultado que aquí vemos es una pérdida total de 4980 dólares. Se puede ver al encuestado 10,000 veces. Ganamos 4751 veces y echamos un vistazo a esa probabilidad de ganar. Recuerda lo que dijimos. La verdadera probabilidad real para esta apuesta es de 47.4%. Aquí vemos un resultado de 47.5, y si seguimos adelante y lo pasamos una vez más golpeando, calcule ahora, recuerde, va a tomar unos segundos, generalmente 10 segundos más o menos para correr realmente por la simulación y ahí lo tienes. Por lo que 47.6 esta vez perdimos 4900 dólares. Y sólo hagamos un cálculo más. Dale unos segundos para que se ejecute. Ahí vamos. Nuestro tercer resultado, sólo perdimos 3400 esa vez. Teníamos una probabilidad de viento superior a la normal, pero todavía muy, muy cerca del porcentaje verdadero o verdaderas probabilidades de ganar. Entonces lo que realmente voy a hacer aquí es seleccionar ese rango completo aquí y eliminar esa tabla de datos solo porque no quiero frenar las cosas aquí y sigamos adelante y solo eliminemos estos valores de índice hasta 10,000 y estamos como de vuelta donde empezamos. Entonces a partir de aquí, siéntete libre de jugar. Prueba diferentes tipos de apuestas, prueba diferentes cantidades de apuesta, simula diferentes números de giros. Depende totalmente de ti desde aquí. Pero ese es tu curso básico de crisis. En la creación de un modelo de simulación de Monte Carlo en Excel 12. Optimización avanzada con solver: todo bien. Estoy tan emocionado de compartir este próximo pro tip contigo. Este es un consejo analítico de cinco estrellas, verdaderamente experto nivel. Hablaremos de optimizar modelos complejos usando solver. Ahora es solucionadores diseñados para hacer es trabajar con problemas complejos de optimización del mundo real que requieren múltiples entradas o variables de decisión sujetas a un determinado conjunto de restricciones. Ahora considera un caso como este. Estabas mirando una matriz de transporte que nos dice el costo de enviar mercancías entre cuatro fábricas diferentes Boston, Nueva York, Chicago y Oakland, y cuatro centros de distribución diferentes Miami, Dallas, Seattle y Baltimore. Y vamos a estar caminando a través de esto en profundidad cuando saltemos al excel. Pero ¿la línea de fondo? El objetivo aquí es averiguar cuántas unidades de producto enviar de cada fábrica a cada centro de distribución con el fin de minimizar nuestros costos totales de envío. Ahora bien, no es tan sencillo, porque en el mundo real también tenemos limitaciones y limitaciones. Para una, necesitamos cumplir con todos los pedidos demandados en este caso, 6000 pedidos totales, y dos, no podemos exceder el inventario disponible en cada fábrica específica. Entonces para un problema como este. Realmente no podemos usar herramientas más simples como el oro Seek, que requirió una sola variable de entrada o decisión y un solo resultado concreto. En este caso, necesitamos usar solver porque solucionador nos permite ya sea minimizar, maximizar o apuntar a un valor objetivo. Nos permite cambiar múltiples celdas de entrada o variables de decisión, y nos permite determinar también restricciones específicas. Entonces vamos a hacer aquí la cabeza a nuestra pestaña de datos y dar clic en el botón Solver para iniciar el cuadro de parámetros de Solver. Ahora, si no ves solucionador en tu ciudad de datos, ten en cuenta que la mayoría de las versiones de Excel half solver como un plug in incorporado pero por defecto normalmente no está habilitado. Por lo que es posible que tengas que dirigirte a las opciones de archivo Adan's y luego perforar en tu Excel. Agrega ins para enchufar bateo. Ahora, mirando este cuadro de diálogo aquí, hay tres parámetros que necesitarán determinar el Número uno es nuestro objetivo. En este caso, nuestro objetivo vive en la celda E 23. Es nuestro costo de envío, y nuestro objetivo no es establecer ese costo de envío a ningún valor específico. Es para minimizar el número dos. Determinamos exactamente qué células variables o variables de entrada cambiar para cumplir con nuestros objetivos. Entonces aquí están las variables de decisión que viven en celdas D 16 30 19 Y por último pero no menos importante, necesitamos optimizar sujeto a un conjunto específico de restricciones. Entonces en este caso, no podemos enviar fracciones de unidades, entonces somos unidades. Necesidad de ser números enteros son enteros. No podemos exceder el inventario disponible en cada fábrica, y necesitamos que cada centro de distribución cumpla con toda su demanda. Ahora una nota muy rápida sobre los métodos de resolución, que verás justo debajo de esa ventana de restricción. Aquí hay tres opciones diferentes que puedes elegir. No voy a entrar en los detalles. Está bien fuera del alcance de este curso, pero regla de oro vas a usar LP simplex para cualquier optimización lineal simple es como problema con el que estamos tratando aquí y g r g o evolutivo para una optimización más compleja, no lineal. La gran diferencia entre esos dos es que G R. G es mucho más rápido, pero puede no proporcionar la solución óptima global. Es más probable que evolutivo proporcione esa solución global, pero tarda mucho más en calcular. Entonces ahora que hemos establecido nuestro objetivo nuestras variables de decisión y nuestras restricciones pueden seguir adelante y hacer clic en sal y comprobarlo. Excel producirá o poblará todos los valores en esas variables de decisión para minimizar nuestras líneas de fondo. Costo de envío. Entonces aquí vemos que estamos enviando 225 unidades de Boston a Miami, adorando 975 de la ciudad de Nueva York a Dallas, 500 de Oakland a Seattle. Y al final del día, estamos cumpliendo con la demanda adorando a todas las 6000 unidades no excedían nuestro inventario en cada fábrica, y nuestro costo total de envío entra en poco menos de $97,000. Entonces, como se puede ver, casos de uso común de herramientas increíblemente potentes al igual que vemos aquí determinando resultados óptimos sujetos a restricciones del mundo real, cosas como inventario limitado como pisos de precios como valores enteros, etcétera para que saltemos a un cuaderno de trabajo pro tip y resolvamos este problema de optimización. Paso a paso. De acuerdo, así que si lo estás siguiendo, dirige tu tabla de contenido ardilla todo el camino hacia tus consejos de análisis, y vamos a enlazar con nuestra demo de solucionador aquí y aquí, verás esa matriz de transporte que de lo que hablamos en la diapositiva. Pero antes de sumergirme, entra en tu pestaña de datos y busca ese botón de solucionador. Por lo general está todo el camino a la derecha. Si no lo ves, tenía que archivar opciones Adan's Y luego a partir de este desplegable, vas a querer administrar tu Excel, agregar ins y presionar Go, y deberías ver solucionador Adan en esta ventana aquí. Sólo tienes que poner una marca de verificación en esa casilla. Presiona OK, y deberías ver que aparece en tu intento de fecha. Ahora volviendo a nuestro problema aquí. De lo que tenemos nuestros costos de transporte, el costo real para enviar una sola unidad de nuestras fábricas a nuestros centros de distribución. Esto se basa en el kilometraje real, ¿verdad? Por lo que el envío de Boston a Baltimore es bastante barato, solo unos 4 dólares por unidad. Pero el envío de Boston a Seattle es mucho, mucho más caro, más de 30 dólares por unidad. Ahora también estamos lidiando con dos restricciones diferentes. Aquí tenemos restricciones de suministro, que es el límite al número de unidades que cada fábrica puede abastecer. Por lo que Boston actualmente solo tiene 1000 unidades en stock. La ciudad de Nueva York tiene 2000 El 2500 Oakland de Chicago solo tiene 500 por lo que no podemos enviar más unidades de las que una determinada fábrica tiene en inventario. Entonces eso es restricción número uno o restricción de suministro. También tenemos restricciones de la demanda. Estás en la lista aquí mismo en Rhode 10 que dice que Vale, Miami Onley necesita cumplir con 1250 pedidos para que no tenga sentido enviar más unidades a Miami. Y de hecho, no podemos enviar menos unidades a Miami porque necesitamos cumplir con estos pedidos. Tenemos que poner estos productos en manos de los clientes que los ordenaron. Entonces tenemos que llevar 975 a Dallas. Consiguió conseguir 3200 y 52 clientes en el área de Seattle y así sucesivamente y así sucesivamente. Entonces esas son nuestras limitaciones de demanda. Y en resumen es que hay 6000 unidades que buscamos enviar aquí como parte de este modelo. Ahora bien, si saltamos por debajo de la matriz de transporte, tenemos nuestro objetivo o nuestros resultados. Entonces el objetivo aquí de nuevo es poblar estos valores aquí mismo en D 16 a G 19. Esas son las variables de entrada o decisión que se cambiarán con el fin de evaluar o calcular el costo total de envío. Entonces mira lo que pasa si solo enchufamos algunos números. Digamos que enviamos 100 unidades de Boston a Miami, 50 de Nueva York a Dallas, 350 de Chicago a Seattle y 25 de Oakland a Baltimore. Como puedes ver, estamos calculando el número de unidades enviadas por fábrica. Nos vamos a asegurar que esos números no superen estas restricciones en el cinco al ocho. También estamos calculando el número de unidades recibidas por cada centro de distribución. Yo quiero asegurarme de que estos números no superen ni entren por debajo de los de aquí en D 10 a G 10. Estos tienen que ser exactamente iguales al final del día porque tenemos que cumplir con esos pedidos y luego durar pero no menos importante, nuestra celda total de gastos de envío. Este es el objetivo de fondo que nos importa es una sencilla función de algunos productos que básicamente toma todas las unidades vendidas o enviadas de cada centro de distribución de fábrica, las multiplica por sus respectivos costos y luego resume el total a escupir fuera de ese costo total de envío. En este caso para las 525 unidades que acabamos de enchufar es una demo que costaría poco más de $10,000 en enviar. Por lo que conocemos nuestro objetivo. Conocemos nuestras variables de decisión, y conocemos nuestras limitaciones. Es hora de resolver esta cosa. Adelante a los datos haga clic en Solver y aquí verás tus parámetros de solucionador. Entonces primero lo primero. ¿ Cuál es nuestro objetivo? Bueno, objetivo es gastos de envío vive aquí mismo y vender E 23. Y no queremos establecer ese costo de envío a ningún valor en particular, aunque podríamos si quisiéramos. Y ciertamente no queremos maximizar el costo del envío. Eso sería una locura. Queremos minimizar nuestros costos de envío y lo vamos a hacer cambiando un cierto conjunto de celdas variables o variables de decisión que viven justo aquí en Green de 16 30 19. Ahí vamos. Y lo único que tenemos que hacer ahora es establecer nuestras restricciones y van a ser algunas restricciones diferentes. Adelante y agrega el primero. Nuestra primera restricción, que es simple pero es importante agregar aquí es que no podemos enviar fracciones de productos, verdad. Tenemos que enviar unidades o cantidades enteras. Entonces sabemos que nuestras variables de decisión aquí de 16 30 19 tienen que ser yo anti, lo que indica que debe ser un entero así que presione. OK, ahí vamos. Esa es la restricción número uno. Vamos a sumar dos más. Primero va a ser nuestra restricción de suministro, lo que significa que nuestras unidades despachadas, que viven aquí en I 16 19 unidades por fábrica tienen que ser menores o iguales al número de unidades que cada fábrica tiene disponibles. Y tenga en cuenta que podría hacer esto en un solo paso seleccionando tipo de toda la matriz de cuatro celdas. El alternativo sería convertir esto en cuatro limitaciones diferentes. Ya sabes donde yo 16 es menor o igual a yo cinco i 17 esta menor o igual a yo seis. Pero podemos hacerlo en un solo paso seleccionando las cuatro celdas a la vez. Entonces otra vez, esto dice que no podemos enviar más unidades de las que una fábrica tiene Chris OK? Y entonces nuestra tercera restricción es nuestra restricción de demanda. Por lo que tenemos que cumplir con toda la demanda de cada centro de distribución. Por lo que las unidades recibidas por esos centros de distribución vive aquí mismo en D 21 3er G 21. Y en este caso, no podemos estar más o menos. Tenemos que ser exactamente iguales al número de órdenes demandadas o unidades demandadas para cada una de esas ubicaciones que viven justo aquí y vende D 10 a G 10 Pulse OK, y estamos bien para ir. El método de resolución va a ser la opción LP simplex. Ya que este es un caso de optimización lineal y échale un vistazo. Todo lo que tenemos que hacer ahora es click solucionado. Y casi al instante obtenemos este cuadro de resultados que dice que solucionador encontró una solución. Se satisfacen todas las restricciones y condiciones óptimas de alidad y señalan que enchufó los valores aquí mismo en nuestras variables de decisión y escupe esos costos mínimos totales de envío , que en este caso es de $96,687 y 75 centavos. Entonces sigamos adelante y mantengamos esa solución de solucionador. Tenga en cuenta que podemos guardar estos valores como escenario en nuestro escenario, manager o simplemente podemos presionar OK y ahí lo tienes. Hemos optimizado este reto logístico muy complejo al determinar exactamente cuántas unidades enviar desde cuatro fábricas diferentes a nuestros cuatro centros de distribución diferentes. Y ahí lo tienes demo rápida de cómo usar la herramienta Excel Solver para resolver este tipo de problemas de optimización del mundo real más complejos. 13. Herramienta de análisis de análisis Pak (prevista): bien, Este siguiente propina pro es un poco un teaser, una especie de vista previa a escondidas en algunas formas diferentes en que puedes explorar tus datos usando algo llamado el paquete de herramientas de análisis. Entonces, igual que solucionador, el paquete de herramientas de análisis es un enchufe de Excel integrado que está diseñado para admitir una gama de ciencia de datos más avanzada y compleja y herramientas y métodos estadísticos, cosas como análisis de varianza y sobre regresión de varianza co. Las pruebas T silbaban un gramos y así sucesivamente. Ahora, obviamente, no tengo tiempo para sumergirme en cada uno de esos métodos específicos. A su vez, cada uno merece una revisión mucho más profunda, mucho más exhaustiva. Pero lo que te mostraré es el alcance de opciones disponibles dentro de este pack de herramientas, así como algunas que podemos usar de inmediato del bate que son más simples pero no menos potentes. Entonces en esta demo, vamos a estar viendo una muestra de unos 5000 atletas olímpicos junto con sus edades, alturas y pesos. Y lo que podemos hacer es perforar nuestra pestaña de datos y buscar el botón de análisis de datos. Si no ves esto probable significa que solo necesitas activar eso. Agregar en. Así que adelante a las opciones de archivo. Adan's y administrar Tu Excel El dedo del pie de Adan. Activa esta herramienta en tu cinta. Y una vez que haga clic a través del análisis de datos, verá aquí una lista de desplazamiento de todas sus diferentes opciones, algunas de las cuales son simples. Algunos son bastante avanzados. Ahora, por el bien de la demostración, vamos a estar hablando de dos opciones aquí. En primer lugar, vamos a ver una matriz de correlación entre nuestros tres campos, edad, estatura y peso. Y luego a partir de ahí vamos a generar algunas estadísticas descriptivas para calcular cosas como el modo medio. Variantes rangaron el recuento de hombres sesgados nous y valores Max y así sucesivamente sin tener que escribir una sola fórmula. Por lo que esa será una gran manera de describir y entender realmente nuestros datos con el clic de un botón. Casos de uso tan comunes aquí, igual que te mostré, generando esas estadísticas descriptivas sin tener que usar fórmulas y para explorar o analizar datos utilizando estas ciencias de datos más avanzadas o métodos estadísticos como analizar variantes, construcción de modelos predictivos, etcétera. Entonces saltemos a excel y llevemos este paquete de herramientas de análisis para una prueba de manejo. Muy bien, así que adelante y abre tu libro de trabajo pro tip scroll en la tabla de contenidos a tus consejos de análisis Purple y vamos a saltar a la herramienta de análisis empaquetada demostración de vista previa aquí . Y cuando nos vinculemos, volveremos a ver nuestra lista de atletas olímpicos. Tenemos una muestra de unos 5000 atletas aquí. Estamos viendo edades, alturas y centímetros y pesos en kilogramos. Por lo que este sería un buen conjunto de datos para practicar algunas de estas opciones de paquete de herramientas de análisis. Entonces lo primero es lo primero, dirígete a tu pestaña de datos a la derecha. Aquí es donde debes ver tus enchufes activados. Si no ve el análisis de datos, adelante a las opciones de archivo. Adan's Vas a administrar la prensa de tu Excel Adan Ir. Deberías ver análisis Paquete de herramientas en esa lista. Adelante y marca la casilla y pulsa OK. Eso debería aparecer aquí mismo y sigamos adelante y veamos qué tenemos a nuestra disposición. Entonces, a medida que nos desplazamos, veremos una serie de opciones diferentes aquí. Análisis de varianza, correlación, co varianza, suavizado exponencial, números aleatorios promedio móviles , pruebas T, pruebas T y algunas otras. Por lo que obviamente tipo de una gama de nivel de dificultad y complejidad aquí. Algunas herramientas simples, algunas que son bastante avanzadas. En este caso. Nos vamos a apegar a algunas opciones básicas que puedes utilizar de inmediato al bate, empezando por las estadísticas descriptivas. Entonces sigamos adelante y presionemos OK. Aquí, veremos un cuadro de diálogo bastante intuitivo dice. Muy bien, ¿cuál es tu rango de entrada de valores? Bueno, me gustaría explorar o analizar las tres de estas columnas, así que vamos a seleccionar B A través de D. Estas están organizadas en columnas, así que eso es bueno. Sí tenemos encabezados o etiquetas en nuestra primera fila. ¿ Y dónde queremos bajar esta salida? Bueno, pongamos aquí la esquina superior izquierda y vendamos F uno. Y vamos a incluir también el caso más grande y más pequeño, un valor, básicamente una forma elegante de decir el máximo y los hombres. Y creo que eso sólo debería hacerlo. No necesitamos preocuparnos por el nivel de confianza para media. En este 0.95% por defecto está bien. Sigamos adelante y presionemos OK y miremos esto. Va a caer en todas estas estadísticas de resumen aquí mismo en columnas, F a K, literalmente en cuestión de segundos. Pienso en el tiempo que eso habría tardado en producir todos estos valores con fórmulas auto . Esto significa thes medians, variantes de desviación estándar habrían tomado bastante tiempo y fueron capaces de apenas producir todos esos valores, además de bastantes más en cuestión de segundos. Por lo que mirándola, Max y hombres parece que tenemos un atleta que tenía 65 años, es el más viejo . Nuestro atleta más joven tenía sólo 12 años en cuanto a estatura. Atleta más alto, 216 centímetros. Y para aquellos de ustedes que no están en el sistema métrico, eso es como siete pies una pulgada. Y podemos confirmar simplemente filtrando nuestros datos fuente aquí. Vamos a ordenar descendente por altura. Y aquí vemos y vistemos a Glina da Kiss, quien es una jugadora de baloncesto nacional griega, que no debería venir como una sorpresa dada lo alto que es y luego mirar pesos se ve como nuestro más grande. En realidad , eran 160 kilogramos. Nuestro más pequeño era de sólo 35 kilogramos y la misma historia aquí. Vamos a ordenar esos pesos ascendentes, y se puede ver que es como Oh Soo Ihara, que es gimnasta japonesa. Y de nuevo, para esas personas no métricas, 35 kilogramos es un cabello de más de 77 libras esterlinas. Entonces esos aires son estadísticas descriptivas realmente, realmente útiles, pero también bastante simples de producir. Sigamos adelante y hagamos un ejemplo más aquí, volvamos a nuestras opciones de análisis de datos. Es hora de que quiera mirar las correlaciones o relaciones entre estas tres columnas. Por lo que mismo rancho de entrada aquí. Ser 30. De acuerdo, las columnas de aire otra vez. Tenemos encabezados de etiquetas en la primera fila, y esta vez dejemos caer nuestra salida de correlación por debajo de sus estadísticas aquí mismo en F 20. Y es un simple ¿Está bien? Y ahí vamos. Adelante y formateemos estos para que sea un poco más legible como porcentajes. Y echa un vistazo a esto. Tenemos esta matriz de correlación que aquí se produce. Verás 100% tipo de en la diagonal porque cada campo está 100% correlacionado consigo mismo. Pero los que realmente nos importan aquí son estos dos valores así como este de aquí. Sólo voy a hacer que esos atrevidos. Y básicamente, lo que esto nos dice es que no hay mucha correlación o relación entre edades y alturas. sólo alrededor de 11% de correlación, lo que podría parecer un poco sorprendente al principio hasta que te das cuenta de que no estamos mirando a toda la población. No estamos mirando alturas todo el camino abajo dos recién nacidos o infantes. Todos estos están bien, en su mayor parte, atletas adultos. Entonces una vez que has llegado a la edad adulta, ya no estás creciendo realmente. Entonces no es de extrañar que allí veamos una correlación relativamente baja, y esa misma historia sostiene por edad y esperar las mismas razones. Ahora es interesante es que sí vemos una correlación muy clara, muy fuerte 78% entre estatura y peso, lo cual también tiene sentido porque los atletas más altos, más grandes tienden a pesar más. Entonces ahí lo tienes. Esa es una forma muy rápida de usar herramientas como correlación y estadísticas descriptivas para explorar y aprender más sobre tu conjunto de datos sin tener que usar herramientas o fórmulas de fantasía. Y de nuevo, no me meteré en el otro tipo de herramientas más complejas dentro de ese paquete de herramientas de análisis . Pero te animaría a explorar esas opciones e intentar jugar con esas herramientas por ti mismo. Por lo que ahí lo tenemos. Imprimación rápida, Vista previa rápida del paquete de herramientas de análisis