Aprende a crear cuadros de mandos de Excel - Parte 4 - Técnicas de mandos

1. Parte 3 Introducción: hola y bienvenidos a tercera clase sobre la serie de clases sobre cómo crear paneles de Excel . En esta clase, vamos a combinar el formalismo, funciones y rangos dinámicos que hemos aprendido en la primera clase y las herramientas de interactividad que hemos aprendido en la segunda clase en técnicas de tablero de embarque que vamos a aprender en esta tercera clase. Y si no has visto salir las 1 ª 2 clases, Holly re comando, las vigilarías. Puedes inscribirte en las 1eras 2 clases usando los enlaces que aparecen a continuación, y con eso dicho, empecemos nuestra tercera clase sobre técnicas de tablero de embarque. 2. SkillShare Introducción hecho: gracias por inscribirse en esta clase antes de que empecemos la clase. Por favor, asegúrate de seguirme en la cuota de habilidad haciendo clic en el botón de seguir en la parte superior de la pantalla o en la esquina inferior derecha de la pantalla, para que puedas recibir actualizaciones sobre nuevas clases que libere o cualquier cambio a clases existentes. 3. Controlar las gráficos de pilotes con ComboBox: Hola chicos. Entonces en este video, vamos a discutir cómo podemos crear Ah, cuadro combinado que controla un gráfico pivot. Entonces puedes ver aquí que, um tengo un cuadro combinado aquí. Y cuando acabo de elegir un año diferente, sólo cambia los datos en el gráfico para trazar las ventas para ese año en particular. De acuerdo, s. entonces si un elige tu 2012 puedes ver aquí que son los datos los datos trazados. Ha cambiado a la para las ventas en 2012. Ahora, Ah, vamos a ver cómo podemos crear este tipo de efectos, porque aquí que tengo un cuadro combinado aquí y vamos a comprobar a qué está conectado, así que derecha, así que derecha, haga clic en él y haga clic en control formal. Y se puede ver aquí que el MPA entrena para el cuadro combinado está ah arreglado llamado la lista del año . Y también hay una venta aquí en vender em siete en la hoja de datos de fila de cuadro combinado. De acuerdo, Ahora, vamos a revisar la hoja de datos de la fila del cuadro combinado. Aquí se puede ver que tenemos una tabla que contiene algunos datos de ventas, y hay ah, ese colador está resaltado en amarillo. Se trata de una columna calculada que extrae el año de la fecha del pedido. De acuerdo, Y también tengo una mesa de pivote aquí, y esto es a lo que el pivote Short está conectado directamente. Entonces, ¿esto es lo que el qué? Este pivote corto, en realidad. Ah, parcelas. Está bien. Y puedes ver aquí que hay un filtro para el año y en realidad el cuadro combinado. Lo que hace es que cambia el año en este filtro, y te mostraré cómo es usando una macro, te mostraré cómo podemos crear una macro así, y puedes verla que hay actualizada otra. Um, he creado otra tabla pivotante aquí con el año solo con la columna Año en Lee, y solo está listando los años y se puede ver aquí esta ordenando envío de orden haciendo clic en ese botón y luego haciendo clic en ordenar, más pequeño a más grande. Y la razón por la que creé esta tabla pivotante es toe tener la lista de años para tener el rango de hamaca para la lista de años, y para que esta esta tabla pivote alimentara ese rango y sería porque el rango es dinámico, así que Si agrega datos para 2015 por ejemplo, esta tabla pivote se expandiría y el rango gastaría también ah para incluir el año 2015. Y así también se incluiría en la lista el año 2015. Por supuesto, ambas tablas pivote, se alimentan de la misma fila Fuente de datos a esta tabla aquí, tenemos a la izquierda. Genial. Ahora vamos a revisar nuestro rango dinámico aquí la lista de años. Es un rango sencillo que he creado. Usar la función de desplazamiento como hemos visto en el video para crear rango dinámico es usar las funciones de desplazamiento para referirse de nuevo a ese video. Si quieres Teoh, si necesitas un refresco y cómo crear rangos dinámicos Bien, Ahora vamos a cerrar el gestor de nombres aquí y puedes ver aquí que hay una celda aquí, que es la caja combinada vendiendo. Esta es la venta para el cuadro combinado. Para ello pone el número de índice del año seleccionado Así porque en esta lista, 2012 es el segundo año en la lista, por lo que sale 2012 es el segundo año en la lista, a y se puede ver aquí que Si elijo 2013 se abrirá 23 y así sucesivamente y así sucesivamente. Y esto es sólo ah, en esta celda de tenemos una función de índice que, um, um, saca esto el año mismo de, ah, ah, fuera de la lista de años grúas dinámicas. Entonces, en realidad, solo usamos la función de índice para extraer el año real del rango dinámico. Ah, tú Azaz tenemos Tendríamos el año extra, y luego construiremos una macro para realmente tomar este año real y ponerlo aquí en el filtro de mesa pivotante. De acuerdo, así es como podemos controlar. Ah, la tabla de pivote y el gráfico de pivote usando el cuadro combinado. De acuerdo, así que esta es la idea básica detrás de Ah, que controlar detrás de controlar un gráfico pivot usando un cuadro combinado. Entonces construyamos la macro. Está bien, chicos. Entonces ahora vamos a ver cómo podemos crear la macro que vamos a adjuntar al cuadro combinado con el fin de ah, cambiar el filtro encendido en esta tabla pivotante, el filtro del año. Entonces lo que tenemos que hacer, primer lugar, para grabar la macro, vamos a ir a nuestro desarrollador Tab en la cinta y luego dar clic en registro, Macro. Y luego voy a nombrar aquí mi macro, Cambiar año de guión bajo. Entonces voy a dar click, OK, para poder empezar a grabar. Y ahora lo que tenemos que hacer es sólo hacer un cambio de año aquí en los filtros de tabla dinámica. Voy a cambiar el filtro de tabla dinámica para que sea 2013 por ejemplo, y luego haga clic. OK, Y luego voy a dejar de grabar mi macro yendo a la tina del desarrollador en la cinta y luego haciendo clic en Dejar de grabar eso. Voy a ir a mi macro a editarlo. Eso voy a ir al botón macro adeudados aquí en la pestaña de desarrollador en la cinta, clic en él y luego solo voy a resaltar mi macro que he grabado y dar clic en ella. Por cierto, aquí hay otra macro, que es el cambio la macro del año que en realidad se adjunta ya al cuadro combinado. El que tengo ah ilustró la funcionalidad del cuadro combinado con. Entonces en realidad estoy creando un duplicado de la misma macro aquí. Voy a cómo me gusta mi macro de año de cambio y luego dar clic en ella. Y ahora, como se puede ver aquí en el código, la primera línea, lo que hace es que borre el filtro del año aquí en el filtro del año en la tabla pivotante. Por lo que borra el filtro del año y la segunda línea en la macro. Lo que hace es que mete el año 2013 en ese filtro. Por lo que necesitamos hacer dos cambios. En primer lugar, en las dos líneas. Se refiere a la hoja activa, pero no podemos referirnos a la hoja activa porque la macro se va a ejecutar desde esta hoja aquí la hoja de tablero de cuadro combinado, que es un tramposo diferente al que contiene la tabla pivotante, que es una hoja de datos cruda de cuadro combinado. Entonces lo que tenemos que hacer es referirnos al cuadro combinado Rhodey a hoja en lugar de la hoja activa debido a referida a la hoja activa. Entonces nos estamos refiriendo al tablero de mandos de cuadro combinado barato. Entonces solo voy a justo aquí en lugar de hoja activa, voy a escribir hojas de cálculo cuadro combinado de datos de fila cuadro combinado de datos datos en bruto, y no importa. El caso fuera de las letras, si son mayúsculas. En minúscula. Esto no marcará la diferencia el día. Quiero decir que en el cuadro combinado Rotator el nombre de la hoja. No importa si está escrito y policías de Olin o sólo en letras pequeñas. También voy a reemplazar la hoja activa en la segunda línea aquí con hojas de trabajo cuadro combinado datos brutos en la primera parte de la segunda línea y la segunda cosa que necesitamos hacerlo después de referirnos al cuadro combinado de hojas de trabajo rotar hacia fuera. El segundo que tenemos que hacer aquí es cambiar el año 2013 para ser, ah, ah, esta celda en el para ser la celda y siete Así que el valor en celda y siete en cuadro combinado hoja de datos raw porque necesitamos el valor del cuadro combinado, que está representado por celda, y siete bien, para ser puesto en este filtro ah en el año filtro aquí en la mesa de pivote. Entonces lo que tenemos que hacer es escribir hojas de trabajo,cuadro combinado, cuadro combinado rango de puntos de datos sin procesar y siete, porque ahora nos estamos refiriendo a esa celda y al valor de siete puntos, y es muy importante escribir aquí un puerto de valor para adultos. Y solo voy a guardar mi macro haciendo clic en control en s de mi teclado. Voy a cerrar el vb vb un editor, Ok. Y luego voy a ir a mi hoja de dashboard de cuadro combinado, y voy a hacer clic derecho en mi cuadro combinado porque necesito asignar la macro que acabo de grabar al cuadro combinado. Entonces como la macro a lanzar o a ser disparada cada vez que cambié el valor en el cuadro combinado , voy a hacer clic en una macro de signo y luego hacer clic en cambiar año de guión bajo y luego hacer clic. OK, entonces ahora mismo ¿qué? Lo que tenemos que hacer es probar nuestra macro. Entonces solo voy a poner aquí el año 2011 Y ustedes pueden ver aquí, chicos que el gráfico está interactuando con el cambio en el cuadro combinado El cambio de año en el cuadro combinado. Está bien. Y se puede ver aquí chicos también que tengo una celda aquí que en realidad escribe ventas y luego un guión, y luego el, uh el año que acabo de elegir y cómo hacer este efecto es usar una ecuación y idear en ating usando el n por ciento. Así que sólo estoy ideado en ating la palabra ventas y luego espacio y luego un guión y luego espacio aquí esta cuerda entre las dos comillas dobles y ken cortando incoct en un ting lo con la celda K cinco en cuadro combinado listo para escuchar. Entonces con la celda que contiene el filtro y luego le he asignado esta Ah, la caja de pilas aquí en el gráfico o este título del gráfico lo he asignado a esta celda. Normalmente, ocultaríamos esta celda aquí teniendo bus de ventas 2013 solo lo he mantenido visible para ilustración. Por lo que he asignado eso yendo Cuba haciendo clic en este título del gráfico y luego haciendo clic aquí dentro del para orificio medio y, ya sabes, normalmente no lo tendría No tendría nada en la barra de fórmulas. Hice clic en igual y luego acaba de hacer clic en que vender, impresionado, entrar. Por lo que ahora puedes ver aquí que el título de los charters interactuando con mi selección en el cuadro combinado. Entonces esto es chicos cómo puedes crear, um, cajas combo que pueden controlar gráficos perfectos. Entonces gracias chicos por ver este video y por favor síganme al siguiente video 4. Filtros dinámicos de fechas parte 1: Oigan, chicos, bienvenidos de nuevo. Por lo que este video es el primero de dos partes para filtros dinámicos de fecha. Entonces en este video, vamos a ver una técnica para filtrar tablas de datos usando rebanadoras. Entonces, como pueden ver aquí, estoy usando una rebanadora y seleccionando un mes determinado. Y entonces mis datos, como pueden ver aquí, están reaccionando a mi selección. Esta tabla de datos aquí está reaccionando a mi selección, y también podríamos usar nuestra línea de tiempo. Entonces, en realidad, como se puede ver aquí, estoy seleccionando entre diferentes trimestres. Y también la tabla de datos está reaccionando y también puede seleccionar de diferentes años. Pero en realidad este estatus , sólo contiene un año. Eso es 2014. Pero si hubiera sido el caso de que mi conjunto de datos tuviera varios años, entonces habría podido seleccionar de diferentes años. Pero puedes ver también puedo seleccionar de mes diferente, y en realidad puedo seleccionar un periodo de tiempo que abarca entre dos meses cualquiera también usando la línea de tiempo. Entonces este es en realidad nuestro producto final, y vamos a ver qué hay detrás de todo eso. Qué hay en el back stage que nos va a permitir hacer esta selección dinámica. De acuerdo, así que he ido aquí a mi hoja de trabajo de datos de fila, que contiene en realidad los datos de carretera detrás de esta tabla de datos. Entonces llegué aquí algún Camino ocho es tan Espersen el país orden 80 orden, fecha orden, monto, unidades vendidas. Y el mes El mes en realidad es una columna que he creado, que básicamente etiqueta cada estado con el primer día del mes para que pueda filtrar entre diferentes meses. Y estoy usando en realidad la función de fecha para hacer eso. Por lo que la función de fecha es una función en excel que puede generar una fecha cuando le das el año y el mes y un día. Y en realidad estoy excusando la función del año para extraer el año de la fecha. De acuerdo, Entonces en este caso, en caso de este estado, que es el 18 de abril, 2014 va a extraer 2014 y para el mes, va a extraer cuatro. Y en realidad, estoy poniendo el día para ser uno para todas las células porque sólo estoy tratando de etiquetar el mes con el primer día del mes, y en realidad lo estoy haciendo. Mira, esta manera cómo hacer que se vea como el 14 de abril de esta manera no se parece al primero del 14 de abril . Cómo hice que se viera como el 14 de abril es mediante el uso de formato numérica personalizado. Entonces si haces click derecho en tu celda, da click en formulario, se vende aquí y luego ir a la costumbre. Verás tener un número personalizado por asesinato, se aplica y se discute. Un formato numérica es Mm mm. Dash Y Y esto te dará esto. Busca la fecha. Hay otros para, missus. Bueno, entonces dee dee dee Dash Mm mm en realidad te dará el día y el mes, que en realidad no es lo que estamos requiriendo. Entonces estos aire diferente formance numérico que puedes solicitar fechas, Pero estoy usando este para que tuviera el mes teniendo este formato y recuerden, chicos es solo una forma no cambia el valor en la celda. Simplemente cambia cómo se ve la célula. Y lo estoy haciendo porque este formato en realidad se verá reflejado en mis rebanadoras. De acuerdo, entonces mis rebanadoras van a estar conectadas a este formato, así que las rebanadoras realmente van a ver como se ven estas células. Por lo que en realidad he optado por ese formato para que las rebanadoras tuvieran escrito el 14 de enero 14 de febrero 14 de marzo en ellos en lugar del primero de enero de 2014. Eso no es muy representativo, ¿verdad? Porque estamos tratando de filtrar para el mes, no para un día determinado. De acuerdo, entonces después de crear esta tabla de datos y crear esta columna Mes, lo que hice es que creé una tabla pivotante yendo a la pestaña de inserción en la cinta. Después de tener alguna celda en la tabla resaltada, vas a la pestaña de inserción en la cinta y luego haz clic en tabla pivote aquí, y luego insertas una nueva tabla pivotante He insertado la tabla PIB, muestra su ubicación en la hoja pivote aquí, para que pueda elegir cualquier ubicación y luego haga clic en Aceptar, y se va a insertar la tabla dinámica. Y aquí está mi mesa de pivote aquí. De acuerdo, entonces esta es la tabla pivotante que inserté en esta hoja. Entonces después de hacer eso, realidad puse el mes en el filtro aquí, y puse el país en la rosa y las ventas totales en la suma de valores para crear esto Busca la tabla pivotante y luego insertar una rebanadora, puedes resaltar cualquier celda de tu tabla pivotante e ir a la pestaña de inserción en la cinta aquí y luego hacer clic en rebanadora. Y luego necesitas insertar una rebanadora para el mes. Necesito hacer del mes el filtro para mi tabla pivotante. Por lo que seleccioné aquí mes y luego haga clic. OK, Como puedes ver aquí, aquí está el rebanador para los meses, y en realidad puedes usar este rebanador para filtrar tu tabla pivotante, como puedes ver aquí. Entonces solo voy a eliminar esta rebanadora porque ya tengo una aquí en esta hoja de trabajo y para crear una línea de tiempo, solo puedes atar luz cualquier celda de tu tabla y luego ir a la pestaña de inserción en la cinta haga clic en línea de tiempo. Y aquí voy a elegir la columna de mes porque la columna Mes es la que he seleccionado aquí en el filtro. Si desea elegir la fecha del pedido, deberá crear otra tabla pivotante y en realidad hacer de la fecha del pedido la columna de filtro aquí. Entonces voy a dar clic en Aceptar aquí. Después de seleccionar el mes y Aziz, se puede ver aquí. En realidad podemos seleccionar entre diferentes meses, y esto se reflejará en nuestra tabla pivotante. Y también podemos seleccionar entre diferentes trimestres en 2014. Pero en caso de los días también, los días en realidad están limitados a sólo el primer día del mes porque la columna del mes contiene en realidad el valor del primer día del mes en Lee. OK, pero si seleccionas el día del pedido para que sea la columna para tu cronología que en este caso, podrás seleccionar cualquier día del mes dependiendo, por supuesto, la disponibilidad de los datos. Entonces si no hay datos en un día determinado que tu tabla pivotante simplemente no mostrará resultados. Entonces, en realidad, si quieres usar la fecha del pedido, solo necesitarás crear otra tabla pivotante. Entonces si vuelves a la hoja de trabajo de datos de carretera aquí, ve a la inserción Tom aquí, haz clic en tabla pivotante, y luego solo lo voy a poner en la hoja de trabajo de pivote también. Aquí pulsa Aceptar, y solo necesitas poner las fechas del pedido para que sean la columna aquí en el filtro y luego país y cantidad del pedido así como la presentación del monto del pedido. Y luego puedes ir a la pestaña de inserción. Haga clic en Línea de tiempo. Por cierto, también, en otras versiones de Excel, podría ser el anal. Me detuve a crear una rebanadora o líneas de tiempo o año analizado Insertar rebanadora. Insertar línea de tiempo. Entonces, ¿en realidad vas a seleccionar fecha de pedido a partir de aquí? Da clic en Aceptar, y como puedes ver aquí ahora, si se utiliza la fecha del pedido, entonces vas a tener todas las opciones disponibles, como trimestres y días. Un mes aquí realmente va para 2000 y 15 pero 2015 no tiene ningún dato sobre entonces. Si seleccionamos 2014 entonces tenemos nuestros datos siendo reflejados aquí para el mes Sa's Bueno, puedes seleccionar un mes. Se puede seleccionar un periodo de tiempo que abarque entre diferentes meses. Se puede seleccionar un periodo de tiempo dedicado a gastar entre diferentes días así en un mes determinado si se desea utilizar la línea de tiempo. Entonces sólo voy a borrar eso. Y por cierto, lo que hice para mover la línea de tiempo o el rebanador es muy sencillo. Seleccionó el control de presión en X en tu teclado y muévelo aquí. Presione control y V para pegarlo. De acuerdo, entonces corta y pega. Muy sencillo. Entonces sólo voy a liderar esa aquí. Y también voy a eliminar esta tabla pivotante porque estoy basando la filtración en el columnista mes en la columna de fecha de pedido. De acuerdo, Así seleccionado. A mí me gusta despejar todo para quitarlo. No, voy a volver a Slicer en línea de tiempo aquí, y quiero mostrarles algo más tan bien como pueden ver aquí. Cuando estoy filtrando los datos usando mis rebanadoras en un selecto un mes determinado, se puede ver que esta tabla se está encogiendo y expandiendo. En base a los datos que tengo para que la veas El tamaño de la tabla podría cambiar, pero en realidad, el formato de la tabla no cambia. Y siempre tiene la columna del gran total en la parte inferior con cierto formato y los países en el medio con un formato diferente y los títulos para la tabla son también con un formato diferente. Entonces, ¿cómo logramos eso? Eso lo logramos realmente, a través del formato condicional y también a través de fórmulas en este establo. Por lo que esta tabla está leyendo, en realidad directamente desde la tabla pivotante. Entonces los cuatro minutos aquí en realidad son muy sencillos, dice. Si la celda de la hoja de cálculo de pivote es igual a Blank, entonces dame un espacio en blanco más. Dame el valor de la celda. Entonces es básicamente una fórmula igual con una sentencia if, modo que si la celda es manta solo mostrará un valor en blanco. Y después de hacer eso, en realidad hay un formato condicional que da un formato para la celda basado en su valor. Entonces si la célula es una de las del país, le da este color gris. Si es el gran Total Cell le da este relleno azul con un fondo atornillado blanco. Y en realidad la celda del título está fija en su lugar. De acuerdo, entonces el título así no tiene ninguna fórmula. Y sólo tiene este color aquí. Por lo que la celda del título no tiene ningún formato condicional. De acuerdo, entonces vamos a revisar el formato condicional aplicado para que pueda ir a la opción de formato condicional aquí en mi pestaña de inicio en la cinta haga clic en administrar reglas. Y como puedes ver aquí en la selección actual o también puedes elegir esta hoja de trabajo a Ciel, el formato condicional en la hoja de trabajo y empecemos primero por el formato condicional del país If I Just Destacado aquí este formato condicional que es gris haga clic en él un Truell. Aquí puedes ver que la fórmula en la que se basa el formato condicional es que el valor en la celda no es igual a dos grandes totales bien, y también el valor en la celda En la columna C no es igual a blanco y nota aquí también que he fijado la referencia de columna para columna vista en el formato condicional. Entonces este es el formato condicional que se aplica para las celdas que tienen los países y para las celdas que tienen el gran total o en realidad es uno venderlo en realidad solo pruebas. Si la celda de la columna C tiene un total general, entonces dame un color negro y ambos formato condicional Z se aplican a las celdas columna C y la columna D, y puedes ver aquí que lo he aplicado hasta la fila 23 en cada columna para que yo expansión y contracción fuera de la mesa y también la forma que es año realmente extendido incluso más allá del rango de la tabla para permitir la expansión y la contracción fuera de la mesa. Te recomendaría que abras este archivo de muestra y lo revises a fondo. Consulta las fórmulas y todo. De acuerdo, así que esto concluye la primera parte sobre cómo crear un filtro dinámico de fecha. Muchas gracias, chicos. Viendo este video, y te veré en el siguiente. 5. Filtros dinámicos de fechas parte 2: Hagos, bienvenido de nuevo. Entonces en este video, vamos a discutir en de la técnica para filtrar fechas usando cuadros combinados o usando listas de validación de datos. Y básicamente vamos a tener algunos rangos de fechas para que puedas seleccionar un mes de inicio y puedes seleccionar mes interminable. Y los datos de tu tabla van a reaccionar a tu selección. Entonces vas a tener los datos apareciendo para las ventas entre estos dos meses. El 14 de marzo por ejemplo, el 14 de octubre aquí. Esta es una forma de hacerlo. uso del cuadro combinado es al revés, que también realiza la misma función. Puedes hacerlo usando listas de validación de datos. Entonces vamos a ver cómo se construyen ambos caminos. Y quiero llamar su atención también sobre el hecho de que las ventas aquí se ordenan en orden descendente. De acuerdo, así que aquí, porque Canadá tiene las ventas más altas, está en la cima. Y luego nosotros un teniendo segundo lugar y así sucesivamente y así sucesivamente. Y este es el caso también en ambas mesas. Entonces vamos a discutir este ah, tablero de rango de fechas, y vamos a ver qué hay detrás de todo eso. Eso es alimentar estas mesas. OK, entonces vamos a ir a nuestra hoja de trabajo de fechas de validación de datos aquí, y vamos a discutir en realidad la lista de meses. El están alimentando los cuadros combinados y las listas de validación de datos. Entonces en realidad, esta lista de meses tengo dos formas de crearlo. El primer modo es crear una tabla pivotante, OK, OK, Y esta tablas pivote fuentes en realidad los datos de carretera y han creado una columna de mes aquí y esta columna de mes se crea usando la función de fecha, tal y como explicaron en el video anterior. Y básicamente, he creado mesa pivotante a partir de ese camino. Está sentado y acaba de poner el mes en la rosa aquí. Y esto creará esta tabla pivotante que se ve aquí y para básicamente garantizar que los meses se ordenen en orden ascendente. Simplemente haz clic aquí y haz clic en ordenar de más antiguo a más nuevo. Y cada vez que se actualice esta tabla pivotante, los meses siempre se van a ordenar en orden ascendente y luego se puede tener un rango dinámico en esa tabla que cubra esa tabla, y este rango dinámico cubrirá la lista y luego tú asignar ese rango dinámico a los cuadros combinados o a la fecha de las listas de elevación, y hemos aprendido a hacerlo Anteriormente, también podrías hacerlo de otra manera, que es básicamente escribir la lista de meses en una columna aquí, que es meses fríos. Y solo escribes la lista de meses simplemente escribiendo, por ejemplo, 14 de enero año y simplemente arrastras este mango así. Y Excel entenderá que estás tratando de hacer un no para llenar durante meses, y sólo va a llenar los meses en adelante. Entonces se crea un cuatro minutos aquí que comprueba si hay fechas entre este estado, que es aquí el primero de enero de 2014. Pero debido al formato numérico personalizado, parece que enero forzando así. Pero los valores el primero de enero. Y, um, esta fórmula realmente comprueba si hay fechas entre este estado y el fin de mes para este estado. Israel. A fin de mes para los Estados. La función de bajo mes realmente devuelve el último día del mes, por lo que va a comprobar si hay fechas entre el 1 de enero 14 y el 31 de enero 14 aquí en esta fila, datos sobre la columna de fecha de pedido, realidad, y si hay fechas que esto, vender aquí va a ser devuelto. Entonces básicamente, se va a comprobar si hay fechas entre el primer 14 junior y el 31 de enero. Y si hay fechas que va a devolver esta celda, Si no, entonces sólo te va a dar una celda en blanco. Esta es la fórmula de cuenta ifs. Hemos aprendido a usar la fórmula Condesa previamente. Por lo que te recomendaría que revises esta fórmula en el sencillo libro aquí para poder entenderlo e implementarlo también. Y esto se hace para todos y cada uno de los meses. Y si son mes existe, o si hay fechas, eso o entre el primero del mes y el final del mes, entonces los meses entonces el mes se van a mostrar aquí. Si no, entonces va a estar en blanco. Como se puede ver aquí, la fórmula se extiende, pero sólo si hay fechas que aparecerá el mes. Si no, entonces el mes simplemente no aparecerá. Será sólo un espacio en blanco. Personalmente prefiero este método porque es más fácil de implementar porque solo creas una tabla dinámica y te aseguras de ordenarla. Pero el inconveniente de este método aquí, el método de tabla dinámica para crear la lista de meses es que debes asegurarte de actualizar tus tablas dinámicas cada vez que actualices tus datos. OK, así que para presionar este botón de actualización aquí se refiere Todo. He creado este atajo en mi Excel, pero puedes ir a la parte superior de datos y dar clic en Actualizar viejo aquí. Pero recordar refrescar mis tablas pivote no es un problema para mí, porque siempre las refresco. Es sólo segunda naturaleza para mí cada vez que actualizo mi carretera ocho. Simplemente refresco mis tablas pivote para que no sea un problema para mí, pero podría plantear un problema para otras personas. Esta es más difícil de implementar, sin embargo, en realidad actualiza las listas de meses sin necesidad de actualizar las tablas pivote. Los meses solo se actualizarán instantáneamente cuando añadas algunos meses nuevos a tu rotador, pero requiere escribir una fórmula, por lo que eres el juez. De acuerdo, ahora vamos a nuestra fórmula de análisis puñalada, y esta hoja de trabajo contiene alguna forma de análisis. Es que usaríamos para controlar la clasificación en este establo y también para los cuadros combo porque sabemos que los cuadros combo necesitarían algunas celdas que en realidad, darían salida el número de índice para la selección en derecho, porque hay un número de índice de cuadro combinado. Entonces, en realidad, este es el índice de cuadro combinado número cuatro, la fecha del cuadro combinado. Y este es el número de índice de cuadro combinado para el cuadro de fecha a combo. Y después de que el cuadro combinado emita el número de índice aquí para la selección en esta celda, realidad hay una fórmula de índice que extrae el mes usando el número de índice, el número de índice de cuadro combinado. Se extrae el mes de la lista de meses. Pero aquí, en realidad, extraemos el mes de la lista de meses. Pero en realidad usamos la fórmula de fin de meses para obtener el último día del mes, y te voy a decir por qué solo en un segundo, Ok, Ok, entonces usamos la función de mes bajo para extraer el último día porque este es en realidad el fecha de finalización a la que necesitas tu filtración para extenderla. De acuerdo, entonces necesitamos que su filtración se extienda entre el primero del 14 de marzo y el 31 de octubre 14 y aquí realmente tenemos algunos If formula que axity resume las ventas entre esta fecha y esta fecha, que este estado es en realidad el 31 de octubre. Entonces si checas aquí en la hora de casa en la cinta, sus estados, si solo cambiamos su por ella, nos va a mostrar el 31 de octubre. Este estado es en realidad el 31 de octubre de 2014. Por lo que la forma de este verano lo resume en realidad las ventas entre estas dos fechas y han aprendido de las cumbres formativas previamente. Entonces por favor asegúrate de volver a esa lección si luchas con esa fórmula y esto se hace por todos los países, y entonces el gran viejo es simplemente la suma de las ventas para todos los países. Lo mismo se hace para las listas de validación de datos. OK, entonces básicamente estamos haciendo un poco de fórmula ish y luego una alguna función aquí para el gran total. Para resumir. Todas las ventas eran países más antiguos también estaban usando un rango para medios la fórmula clasificada e que que que es equivalente a la función de rango en Excel 2007. Por lo que esta función de rango básicamente asigna un rango para cada país en orden descendente. Entonces, en realidad, el país con las altas ventas tendrá el rango más alto y luego las ventas más bajas van a tener el rango más bajo. Entonces aquí, en realidad Canadá está teniendo las ventas más altas fue haber clasificado número dos porque en realidad, esta fórmula se aplica también al gran total también. Y el gran total está teniendo el puesto número uno. Porque, claro, va a ser más alto que cualquier país, verdad, porque la suma de las ventas para todos los países. Entonces en realidad el país más alto va a tener el número dos y luego el segundo más alto tres y así sucesivamente y así sucesivamente. Y luego en realidad hay una mesa de clasificación. Y este es el establo realmente alimentando nuestras mesas aquí en el tablero de escombros del tren de día. Entonces nuestras mesas aire básicamente teniendo un informante s. Así que si una celda contiene un valor, entonces dame el valor. Si no, entonces dame un espacio en blanco. Y luego hay una fórmula aquí que dice que si el valor está en blanco y dame un espacio en blanco más, dame el valor de la celda y esto se aplica para ambos establos e. Este establo está copiando sus valores de este establo y este estable está tomando sus valores de este establo, básicamente. Y aquí, en realidad, para asegurarnos de que obtenemos el rango correcto para los países en realidad hemos escrito un look view de formula con y, ah, si nuestro si hay un error que me des un espacio en blanco valor, básicamente. Y aquí la puedes ver que el look de valor es básicamente la función rosa. Y ya hemos aprendido de las carreteras funcionan antes, y estamos generando el número para básicamente buscar al país más alto porque vamos a usar este establo para el look up, ¿de acuerdo? Y vamos a mirar hacia arriba usando el rango. Entonces en realidad vamos a buscar el nombre del país usando su sonó. Y aquí la Rosa, por ejemplo, Rose F A a F nueve lo va a abrir a OK, así también va a ser nuestra mirada de valor en este establo. Y vamos a conseguir en realidad la segunda columna, que contiene al país. Entonces aquí este look de fórmula va a generar la lista de nuestro país, y luego nuestro gran total se escribe en realidad de forma manual aquí, y también está el total de ventas se genera así usando podrías o bien hacerlo usando el rango, o podrías hacerlo realmente usando el país. Entonces aquí, éste está mirando hacia arriba al país, y éste está mirando las ventas usando al país de este establo también. De acuerdo, así que asegúrate de revisar estas fórmulas, y eso es todo. Básicamente, así es como se genera la tabla de clasificación, y luego este tablero básicamente está obteniendo su valor de las tablas de clasificación. Y así es como te aseguras de que las ventas del país estén realmente ordenadas en orden descendente . De acuerdo, Entonces esta técnica aquí para la tabla de clasificación y la tabla de clasificación en realidad tiene un inconveniente , que es, si agrega un nuevo país en sus datos de fila, debe asegurarse de agregarlo a estos dos mesas. De acuerdo, pero lo que les voy a mostrar en realidad es un punto de control el que he creado, que nos permite comprobar si hay países desaparecidos sobre la marcha muy rápidamente para asegurarnos de que tenemos integridad de datos y que no nos faltan cualquier país en nuestros datos. En realidad voy a introducir dos formas en que eres capaz de hacerlo, y en realidad podrías descifrar tu propio camino también. Entonces aquí he escrito un aspecto V de fórmula, y esta fórmula busca al país desde la tabla pivotante de esta tabla aquí y lo devuelve . Pero no hay si condición así que dice que si la celda en la tabla pivotante está blanco y dame un valor en blanco más buscar el país desde la tabla pivotante en esta tabla y devolverlo básicamente devolvió el país en sí y en realidad han poner esto si el valor está en blanco y dame un espacio en blanco. Por lo que eso será capaz de extender la fórmula más allá de las tablas pivote como para permitir la expansión fuera de la tabla pivot para que si agregamos más países, tabla pivot se va a expandir, van a ser más asados. Dos espectáculos de la mesa pivotante van a ser más largos. Y esto si permite la expansión. Entonces, básicamente, esto realmente busca al país desde la tabla pivotante en esta tabla y lo devuelve. Y si el país no se devuelve y obtenemos un hash y un valor que el país no existe, y tenemos que agregarlo a la mesa También hay otra forma en que se podría hacerlo aquí. Esta fórmula básicamente genera valores verdaderos o falsos y han agregado algún formato condicional a también. Entonces si es cierto, va a ser verde. Si son tontos, entonces se va a leer. Y voy a tener una demostración aquí para ustedes agregando otro país. Entonces, por ejemplo, si tomo alguna fila aquí, solo, ah, ah, duplicado y solo agrego aquí un país diferente. Rusia, por ejemplo. No tenemos a este país en nuestros datos sobre refresco aquí mi mesa y vuelvo al puesto de control. Aquí se puede ver que Rusia se refleja aquí como tener un hash y un valor en esta columna y tener un valor falso en esta columna. De acuerdo, Por cierto, no tienes que crear ambas columnas. Es sólo aquí dos formas de hacer lo mismo. OK, entonces aquí estás Si revisas este punto de control cada vez que actualices tus datos, ya sabes, estoy tratando de introducir el concepto de tener puntos de control en tus reportes o dashboards para que cada vez que actualices los datos aquí en los datos de la carretera, se mira el puesto de control. Simplemente tienes unas líneas rápidas del punto de control para asegurarte de que tienes integridad de datos, que todo se está sumando correctamente y que no tienes ningún vacío en tus números. Básicamente, que todos tus números se suman entre sí. Porque si estás teniendo problemas con tus datos que el total de ventas en todos los países , aire no va a sumar al total general, por ejemplo. Entonces, básicamente, cuando vemos esto, cuando echamos un vistazo a nuestro puesto de control y lo vemos, sabemos que Rusia no existe. Entonces básicamente vamos a insertar en euro aquí y justo Rusia aquí, por ejemplo, y justo Rusia aquí está bien y sólo arrastra esta fórmula y dirige esta fórmula también y arrastramos esta fórmula de rango también. Está bien, y todo va a estar bien. Entonces como puedes ver aquí, el puesto de control vuelve a despejarse. Y sabes que has contabilizado todos los países en tus datos viales. De acuerdo, chicos, así que eso es todo para esta técnica. Espero que haya introducido algunas buenas ideas para ti que puedas implementar tal como está, o puedes agregar las tuyas también. Y espero haber simulado sus mentes para llegar a algunos paneles de gran aspecto y profesionales . Te recomendaría que revisaras a fondo todas las fórmulas de este cuaderno de muestras para poder entender mejor esta lección. Muchas gracias por ver este video y lo veré en el siguiente. 6. Elegir diferentes métodos de agregación: tega es bienvenido de nuevo. Entonces en este video, vamos a discutir otra técnica de dash boarding que sobre esa técnica, vamos a usar, Ah, Ah, una mesa y una mesa pivotante y una rebanadora y la función de elegir. De acuerdo, entonces el objetivo es crear diferentes métodos de agregación para que el usuario elija. Está bien. Para que como la puedes ver en la rebanadora, yo puedo elegir sacar el promedio de la cantidad del pedido. Y puedes ver aquí que el promedio aparece en la celda G dos o puede optar por obtener el máximo del monto del pedido, y aparece aquí y vender G dos o puede optar por obtener el mínimo, O puedo elegir obtener el total. Entonces eso es para tu usuario que no quiere jugar con las fórmulas y esas cosas. Simplemente quieren ver promedio, máximo, mínimo y total. Está bien. Y también puedes aplicar otras agravaciones para que puedas ampliar las opciones de este rebanador o simplemente darte Ah, un ejemplo. Ahora, tengo aquí algunos datos brutos bien para las ventas de la izquierda, y en realidad vamos a trabajar en la columna para los montos del pedido. Esto es lo que estamos agregando como han visto cuando estaba seleccionando los diferentes años de selecciones en mi rebanadora. De acuerdo, ahora, para aplicar esta técnica o a ah, para hacer lo que he hecho aquí con la rebanadora tuve que crear una mesa, una mini mesa aquí. Como se puede ver, eso tiene dos columnas, la agregación y una columna numérica que sólo asigna un número de referencia. Ah, un contador o así Ah, sólo una clave del tipo de agregación que se quiere aplicar. Por lo que promedio le han dado el número uno máximo, dándole el número dos mínimo. Yo le he dado el número tres y total. Le he dado un número cuatro. OK, es solo un número de referencia, ¿de acuerdo? Y he creado otra tabla pivotante aquí en la parte inferior que se basa en la tabla en la parte superior. OK, así que solo ah creé una tabla pivotante que se basa en la tabla en la parte superior. Y por cierto, si estás ah teniendo alguna pregunta con respecto a las mesas o si no estás familiarizado con las mesas son beber una menta que ves las dos lecciones perdidas aquí en el curso. Ah, eso explica tablas. Se trata de clases extra clases de bono bala que explican las tablas eso las tablas celulares explicativas en detalle. De acuerdo, Ahora, después de haber creado esta tabla pivotante aquí que se basa en la tabla en la parte superior, he eliminado la fila de totales generales de esa tabla pivotante. De acuerdo, así que lo he hecho yendo seleccionando mi tabla pivotante, yendo al tiempo de diseño en la cinta y luego yendo a grandes totales, haciendo clic en F encendido para filas y columnas. De acuerdo, así que he quitado la fila del gran total, ¿de acuerdo? Y he creado una función de zapatos aquí en la celda G dos. De acuerdo, Entonces esta función elegir está en su primer argumento el argumento de número de índice o la entrada de número de índice . Se hace referencia a la celda K 13 aquí. Eso tiene, um, estimación fuera del número, porque, ah, ah, el discutir la presentación del número, o en realidad el número Porque el número aquí sólo tiene, um, cada disco tiene sólo ah, cada número tiene sólo un registro aquí en la mesa, en la parte superior, en la mini mesa en la parte superior. Por lo que la suma va a ser la misma que el número en sí. Entonces el primer argumento aquí es referenciar a esta celda aquí conteniendo ah, la suma. De acuerdo, en realidad son el número de referencia en la mesa pivotante. Y entonces las opciones que he puesto son el promedio del monto del pedido. Esta es la primera opción en A Ponlo como primera opción porque el promedio aquí es tener el número uno A referente, el número de referencia uno aquí en esta mesa en la parte superior. De acuerdo, Y entonces tengo el máximo como tener el ser la segunda opción. Porque tiene el número dos aquí, y tengo mínimo como tener como tercera opción, porque tiene el número tres y algunos como ser la cuarta opción. Porque el total es el número cuatro aquí para aquí en la mesa en la parte superior. OK, y lo que pasa es que cuando elijo el tipo de agregación que quiero aplicarme un auto a elegir promedio. De acuerdo, esto se pone aquí en la mesa de pivote, ¿de acuerdo? Y consigo aquí el número de referencia fuera del tipo de agregación que quiero aplicar. Entonces la función elegir lee ese número de referencia. De acuerdo, Entonces que 13 va a ser uno, y así la función de escalofríos va a elegir aplicar. La primera opción aquí en la primera opción es el promedio. Este es el tipo de agregación que se va a aplicar. De acuerdo, en si elijo máximo, conseguiré el número dos aquí, ¿de acuerdo? Y se aplicará el segundo tipo de agregación, que es máximo. De acuerdo, así que consiguió los montos máximos de pedidos puestos aquí y vender G dos. Y esta va a ser la misma historia con mínimo y con algunos. De acuerdo, entonces básicamente, la función tregua hará referencia Ah, a esto o referencia, esta celda aquí obtendrá su elección Ah, número o el número de referencia de su elección de esta celda aquí desde auto k 13. Y entonces aplicará esa función de agregación. Ah, Teoh. Y conseguir que el resultado Y en realidad las funciones de agregación se están aplicando a la columna para los montos de orden aquí en la tabla. Y esta es una referencia estructurada para la tabla. Y si estás teniendo algún problema o no entiendes qué referencias estructuradas te recomendaría ver las dos lecciones perdidas. Ah, en el curso que son lecciones bonus sobre tablas sobre tablas de excel, podrás entender lo que son unas referencias estructuradas, ok. Y no tengo problema para entender eso. ¿ De acuerdo? Entonces, básicamente, siempre que elijas lo que elijas aquí en la rebanadora. Aquí se aplica la agregación en la celda G dos, y luego tenemos algún tipo de texto interactivo aquí. Entonces, en realidad, estoy agregando. Qué es Unseld J 13 aquí. Está bien. Con la cantidad de orden de palabra. Pero poner un espacio al lado delante de él para que hubiera un espacio entre el tipo de agregación y la palabra. Las dos palabras orden cantidad. Vale, esto es solo, um, el texto lider laborista etiqueta de textos económicos que he creado para solo ah, tener algo que cambiaría dinámicamente a medida que cambie mi elección El año es un elegir algo diferente en la rebanadora. De acuerdo, entonces Ah, así es como funciona la técnica, chicos. Um, entonces alguien preguntaría, ¿Y si no elijo aquí? A Ah, hago dos elecciones en la rebanadora. En realidad, Excel sólo ah, hacer la primera opción. O en realidad, conseguirás el resultado de la primera opción porque nos estamos refiriendo a la primera celda de la mesa pivotante aquí. La primera fila bajo la suma de número. Um, llámalo, y luego acaba de cambiar el ah, el encabezado de esa columna para hacer algo un número de referencia. De acuerdo, claro, Normalmente estás ocultando esta, um, tabla pivotante del usuario para que el usuario no viera que estarías poniendo Puede estar en otra hoja. Y estás escondiendo esa mesa de muchos también. Está bien. Y, uh, eso es todo. Podrías tal vez, um, tener como, um, una celda aquí que diría, um, que tendría una fórmula. De acuerdo, eso contaría Estas células de aquí, te dan el recuento de estas células. Y recuerda, chicos que viene función , cuenta. Sólo las celdas que tienen números. Está bien. Entonces, um, tal vez podrías poner un EF de cuatro minutos aquí si el recuento de esto es mayor que uno, entonces, um, um, por favor elige un tipo de agregación. De acuerdo, te voy a mostrar por qué he hecho eso. De acuerdo, Else, dame una celda en blanco. Está encendido. Yo lo estoy haciendo para que si el usuario opta por tipos de agregación, tuvieras esta celda aquí diciendo, Por favor, elige un tipo de agregación, y tal vez podrías dar esta advertencia, vender algún formato condicional, por lo que tal vez puedas ir a los reflejos de formato condicional. Entonces reglas y luego texto que contenga, por favor. Zapatos. Un tipo de agregación. Está bien. Ahora, para que si ah, el usuario hace más de una elección, esta celda aquí tendría un color rojo y algún tipo de advertencia que diga, por favor elija un tipo de agregación, y entonces el usuario lo haría, ah, sólo tienes que elegir un tipo de agregación en. Esta advertencia desaparecería. De acuerdo, para que puedas hacer eso. Ah, para que le diera al usuario algún tipo de advertencia si optan por armar o agregar tipos . Cero espectáculos en tres obras fue de tres. Y funciona con, ya sabes, cuatro tipos de agregación. Si quieres elegir para ello funciona también con cuatro tipos de agregación, y seleccionó un mismo tiempo. Eso el usuario recibirá una advertencia. Ya sabes, cuando seleccionan más de un tipo de agregación, pero es solo el selecto. Tan solo uno. Um, la advertencia desaparece aquí. De acuerdo, Entonces espero que eso fuera informativo para ustedes chicos. Muchas gracias por ver este video y por favor sígueme en el siguiente video. 7. Capturar selección de corteses en fórmulas: Oigan, chicos, bienvenidos de nuevo. Entonces en este video, vamos a discutir otra una técnica que implica poner nuestra selección de rebanadas en fórmula. Entonces como puedes ver, tengo una rebanadora. Y como estoy cambiando mi selección hasta la fecha en el gráfico cambia este gráfico realmente obtiene sus datos de esta tabla aquí, y este rebanador en realidad está vinculado a esta tabla pivotante. Esta tabla pivotante es una tabla pivotante muy simple que tiene el campo modelo puesto en la sección de fila aquí. Y esta tabla pivotante se basa en el devorador de carreteras de ventas reales y los datos brutos de ventas reales es uno de los conjuntos de datos de dos filas que tenemos obtendrían las ventas reales y las ventas de previsión . Por lo que esta axidad estable solo hace referencia al nombre del modelo de nuestra selección de rebanadoras. Entonces tenemos una fórmula de cumbres que calcula las ventas, real y el pronóstico de los respectivos conjuntos de datos basados en nuestra selección de rebanadas y ese mes en particular también, realidad hemos puesto este valor aquí en la celda p cinco, que es nuestra selección de rebanadoras. Lo hemos hecho un drenaje de nombre por lo que creó un nombre muy simple, drena una regaña rebanadas elección. Acabamos de hacer que sea igual a esa celda sólo para poder referirse a esa celda como selección de rebanadora . No es necesario, pero sólo facilita las cosas. Como se puede ver aquí, este es un texto de título de gráfico. Esta celda aquí contiene el texto del título del gráfico, y esta celda tiene una fórmula que prueba si la celda bajo nuestra celda de selección de cortador está vacía o no. Entonces si está en blanco, así que prueba si. ¿ Está en blanco o no? Porque si no está en blanco de lo que hemos seleccionado múltiples modelos, así que es sincero. Si selecciono varios modelos, no va a estar en blanco. Entonces dos o más va a hacer que la celda P seis no esté en blanco. Y esto es algo que no queremos hacer porque no queremos que el usuario seleccione múltiples modelos porque entonces las fórmulas de veranos no funcionaban correctamente porque solo pueden hacer referencia a un modelo. Por eso, en caso de que el usuario seleccione múltiples modelos, recibirá un mensaje de advertencia. Esto por favor elija un modelo sobre Lee. Entonces esto es en realidad una prueba que hacemos con el fin de asegurarnos de que el usuario seleccione un modelo en Lee y este boxier de título solo hace referencia a esta celda para que el título sea dinámico. Y básicamente el título dice la rebanada de la selección real frente a las ventas de enfoque. Por lo que ese modelo particular que seleccionamos ventas reales versus pronosticadas. Entonces eso es todo. Esta es una técnica muy sencilla para involucrar tu selección de cortador en una fórmula. Entonces muchas gracias, chicos, por ver este video y lo veré en el siguiente. 8. SkillShare Outro realizado: Gracias por ver esta clase. Espero que te haya sido informativo. De verdad lo aprecio. Si pudieras dejar una reseña haciendo clic en la reseña, detente en la parte inferior de la pantalla. Y por favor asegúrate de seguirme en la cuota de habilidad para que estés actualizado sobre todas mis nuevas clases. Gracias por ver. Ya veré en el siguiente.