Visualización de datos en Excel parte 2: demostraciones avanzadas

1. Intro: establecer expectativas: bien, así que por ahora probablemente seas maestro de casi todos los tipos de gráficos que Excel 2016 tiene para ofrecer. Entonces sigamos adelante y subamos las cosas una muesca. En esta siguiente sección, vamos a sumergirnos en alguna fecha realmente única, muy avanzada, muy avanzada, siguiente nivel de sus ejercicios. Pero antes de que lo hagamos, tomemos sólo un minuto para establecer algunas expectativas. Los gráficos y gráficos avanzados número uno suelen requerir el uso de fórmulas y funciones avanzadas . Al final del día, eso es lo que los hace avanzar. Entonces sé que esto no es una fórmulas y funciones. Curso del que viniste aquí para aprender fecha de es. Pero el hecho del asunto es las fórmulas y funciones son componentes críticos para habilitar alguna funcionalidad realmente, realmente cool con gráficos y gráficos avanzados. Por lo que durante las demos, hablaré de las fórmulas. Te daré un resumen rápido de las fórmulas, pero de ninguna manera será una revisión integral de fórmulas y funciones. Segundo, algunas de estas demos aire bastante fáciles. Tal vez encuentres algo realmente duro. Algunos podrían hacer que quieras acurrucarte y llorar. Ahora, eso está bien. Todo lo que pido es que proceda con una mente abierta y confíe en mí, surgirá. Una fecha de su mago último pero no menos importante, Número tres. Si sí quieres intensificar tu juego de fórmulas, aprende más sobre algunas de estas funciones que aquí tratamos. Algunos ifs. Index match offset Count Day array funciones, etcétera. Echa un vistazo a mi Excel Analytics, fórmulas y funciones avanzadas Curso y sí, sí, sé que soy sesgada como el autor del curso, pero confía en mí, es un muy bonito, verdad revisión integral de básicamente todas las fórmulas y funciones que podrías encontrar en Excel. Entonces con eso, sigamos adelante y empecemos. 2. Tabla personalizada de superposición de imagen: bien, así que vamos a empezar las cosas con algo que está un poco en el lado más fácil, pero aún así bastante único y creativo. Y eso son gráficos de superposición de imagen personalizados. Entonces en el cuaderno de ejercicios de visualización de datos cabeza sobre esa primera pestaña verde, la pestaña de gráficos de superposición de imágenes, y veremos con qué estamos trabajando. Y como pueden ver aquí arriba, sólo tenemos unos pocos puntos de datos con los que trabajar. Se trata de resultados básicos de recaudación de fondos. Tenemos un usuario ingresó valor por dólares recaudados y vendemos A para conseguir una meta de $10,000 los dólares recaudados y por ciento para ir en la columna C y D, ambos se calculan en base a A y B. Y la idea aquí será crear algo que es un poco más único y un poco más creativo. Entonces tu columna estándar 100% apilada. Y para ello, vamos a integrar nuestros propios gráficos personalizados. Así que he construido dos ejemplos aquí y los he insertado en la hoja de trabajo, tengo un termómetro y un icono de pulgar hacia arriba, y puedes usar cualquier cosa que elijas. Simplemente ten en cuenta que no importa qué tipo de gráfico estés usando. Asegúrate de que tenga un fondo sólido, opaco y algún área transparente donde quieras que el gráfico brille desde atrás. Entonces como puedes ver si hago click derecho y traigo mis pulgares hacia arriba icono al frente. Mismo caso aquí. Tiene un fondo blanco sólido, opaco, con el área transparente justo dentro del contorno de los pulgares arriba mismo. Entonces vamos a empezar y en realidad insertemos una columna apilada. Voy a seleccionar un control uno y dos Seleccionar C uno y C dos que le da en dólares recaudados y dólares restantes. Cómo insertar 100% apilados intercambiar su propia columna que tengo ambos mis Siri apilados juntos en la misma columna, y ahora hacemos un poco de formato así que no necesito el título del gráfico y en realidad no necesito el eje X. Esta cajas naranjas, mi caja restante de dólares. Esto en realidad no quiero con ningún tipo de relleno sólido así que puedo elegir No, Phil, Phil, no hay línea para mis dólares recaudados. Siri's Vamos a darle un relleno sólido con un bonitotipo de clásico rojo oscuro, tipo de clásico rojo oscuro, su mamá y su color, y en este punto, lo importante es que lo arrastre y una línea cero y 100% marca con el ícono del termómetro. Entonces solo lo haré de forma manual. Eso parece una alineación bastante buena. Y luego una vez que haya hecho eso, en realidad ni siquiera necesito esto. ¿ Por qué acceder más? Puedes optar por mantener las líneas de cuadrícula o perderlas si quieres. En este caso, sólo los quedaré. Lo último que quiero hacer aquí. Simplemente derecho Haga clic en Formato. El área del gráfico. No quiero ninguna zona Phil ni borde de gráfico. Y ahora, finalmente, si yo solo mis datos, los de Siri pueden reducir la brecha con sólo para asegurarse de que los de Siri sean tan amplios como todo el área del gráfico. Entonces he reducido esto realmente en solo un rectángulo que crece en base al valor y vendo ocho a, así que lo voy a reducir a lo que necesito, y luego simplemente cambiar y arrastrar sobre click derecho. Envíalo de vuelta, y así ahora está básicamente detrás están oscurecidos por mi gráfico de termómetro. El área Onley que brilla a través es la pieza de mi gráfica que es transparente, así que casi estoy ahí. Lo último que necesito hacer es simplemente llenar esta bombilla aquí, la parte inferior, que en la parte inferior, realidad está por debajo del 0%. Por lo que técnicamente no es parte de nuestra gráfica. Yo sólo voy a insertar forma, agarrar la herramienta ovalada. Si mantengo el turno mientras estoy arrastrando esto hacia fuera, puedo ajustar el tamaño. Voy a superponerlo justo en la bombilla de ahí. Vamos a darle ese mismo Phil rojo y sin línea y vamos a dar click derecho y mandar esto a la parte de atrás también. Y ahí lo tienes. Ahora, medida que el usuario cambia su valor en un dos, digamos que ahora han elevado 5800 son escaladas de área apiladas, y hace que parezca como si este termómetro estuviera aumentando en temperatura o creciendo hasta ese objetivo del 100%. Entonces aquí vamos. Tenemos 8123 50 súper simple, pero una especie de efecto creativo agradable que la mayoría de la gente no pensaría hacer en excel. Y ahora lo que puedo hacer es simplemente Es un poco difícil acceder a esto. Voy a arrastrar mi superposición fuera del camino solo para poder seleccionar mi control de prensa de gráficos. Ver para copiarlo, solo voy a hacer una versión duplicada de la misma. Y luego puedo arrastrar mi gráfico de vuelta hacia atrás, y voy a usar este como mi fuente para los pulgares hacia arriba, ya que utiliza exactamente los mismos datos. Um, ¿por qué no cambiamos el color del Phil a verde claro? Y en este caso, saquemos líneas de rejilla fluvial y lo extenderemos un poco. Y básicamente, lo que quiero hacer aquí es volver a escalarlo un poco de nuevo para que el 100% golpee solo la parte superior de un pulgar . Y 0% está justo aquí en la parte inferior así que puedo probarlo solo escribiendo 10,000. Sólo que está claro exactamente dónde está mi top. Parece que eso me va a llevar ahí mismo. fondo, como se puede ver, está alineado, por lo que se ve bien. Ahora solo es cuestión de estirar esto para llenar toda la forma. Da clic derecho enviar a la espalda Ahí lo tienes. Entonces intentemos enchufar algunos valores aquí en Sal A. ¿ A qué tal 2500? Ahí lo tienes. Es alrededor de 1/4 lleno 5000 obviamente consigue este medio camino. 66 82. Nos llevaremos un poco más lejos, y ahí vas a diferentes ejemplos de poner tu propio giro creativo en un gráfico de columnas apiladas relativamente simple para crear una superposición de imagen personalizada. 3. Agrega valores binarios para resaltar los rangos de fechas de fecha: para la próxima demo. Quiero hablar de agregar valores binarios para resaltar rangos de fechas dentro de un gráfico. Entonces dirígete a la segunda pestaña esa pestaña de rangos de fechas binarios y aquí ya verás. Sólo tenemos a columnas de datos. Estamos viendo ingresos diarios probándolo, y lo que queremos mostrar aquí es una circunstancia en la que podrías tener fechas o rangos de fechas que son significativas de alguna manera, fechas que quieres llamar la atención o resaltar dentro de un gráfico. En este caso, en la columna A. Verás algunos valores o algunas fechas formateadas con un Phil verde claro. Y digamos que estas fechas representan periodos de venta o promoción para este ejemplo en particular . manera realista, eso podría ser cualquier cosa. A lo mejor solo quieres exhibir los fines de semana en tus datos. O tal vez quieras destacar todos los miércoles o lunes. Sea lo que sea, el punto es que estás tratando de resaltar ciertas fechas o rangos de fechas en tu gráfico, lo que lo iniciaremos seleccionando A y B y simplemente insertando un gráfico de líneas básico, arrastrándolo hacia arriba y extendiéndolo un poco. Y por qué no seguimos adelante y formateamos ese feo eje X. Dale una unidad mayor de cuatro simplemente para que eso sea un poco más legible. Entonces ahí lo tienes. Tenemos ingresos de tendencia mostrados en forma de gráfico de líneas. Y ahora, cuando llegue el momento de presentar realmente esos rangos de fechas dentro de este gráfico, lo que voy a hacer es agregar una nueva columna en C llamada promo, y tendrá un valor predeterminado de cero. Por lo que podría simplemente hacer doble clic para aplicar ese cero todo el camino hacia abajo. Y ahora todo lo que necesito hacer es reemplazar el cero por uno para cualquier fecha que caiga dentro de un periodo promocional. Por lo que del 18 al 20 por ejemplo, puedo copiar ese. Aplícala aquí abajo para subir del 29 al 34 51 3 62 67 3 69 y finalmente del 81 al 85. De lo que me queda es de un binario Siri y columna ver que toma ya sea un valor de cero si la fecha no cae dentro de un periodo promocional o de uno. Si la fecha lo hace, en este punto, es solo cuestión de conseguir este binario. Siri está dentro de esta tabla de una manera que hace que algún tipo de sentido sea lo suficientemente simple. Puedo hacer clic derecho en seleccionar datos. Agrega un segundo, Siri's Vamos a llamarlo promo y para valores serios todos seleccionan Celda C para controlar Shift Arrow Down para llevarme todo el camino hacia abajo hasta el borde de esta matriz, que se ve 91 Pulse. Está bien dos veces. Desplázate hacia arriba, Y como puedes ver, no estamos del todo ahí. Todo lo que se hace es agregar una segunda línea en el mismo eje, que o bien toma un valor de cero o uno, y acaba de ahogarse por completo por los totales de ingresos. Por lo que claramente necesitamos usar un gráfico combinado. En este caso, voy a dirigirme a las herramientas de gráfico, cambiar mi tipo de gráfico select combo aquí en la parte inferior de la lista. Y como puedes ver, Excel trató de ponerse elegante y cambiar el tipo de gráfico en mí. En este caso, no quiero que los ingresos se agrupen. COLUMNA ¿Quieres volver al gráfico de líneas original que había seleccionado antes y para el periodo de promoción, que es mi binario Siri, este? Quiero ser columna de pila 100% en el eje secundario. Yo hago eso. Ya puedes ver que estamos empezando a acercarnos un poco más aquí. Entonces lo que quiero hacer es seleccionar ese formato de Siri y quiero darle un Phil muy bonito, ligero, ligero, sólido, porque quiero llamar la atención de esas fechas. Pero no quiero ahogar todo lo demás, y no quiero agregar demasiado ruido a esta visualización. Entonces si bajo a las opciones de Siri, lo último que haré con este Siri es simplemente eliminar la brecha con y con eso esencialmente lo hace es crear estas columnas de datos, cuyo ancho representa todas las fechas que caen dentro de un período promocional particular. Pero podrías notar que todavía tenemos el acceso secundario aquí, que a estas alturas no tiene sentido. Eso realmente no es nada que caiga a lo largo de este año a escala 100. Pero el problema es que si lo elimino, elimina los Siri con él, así que te controla para deshacer eso, y solo usaremos un pequeño atajo yendo al menú de inicio, y aquí podemos hacer el Phil blanco. Otra opción, si deshacemos eso, es usar el truco de formato condicional que usamos durante uno de los formatos de conferencias anteriores que el acceso bajan a las opciones numéricas, y vamos a usar una categoría personalizada, y el código será de tres semi puntos consecutivos que esencialmente hace que el texto del Eje sea completamente invisible. Entonces en este punto, es realmente justo el formato que te gustaría en este caso, lo lograré. Una línea azul oscuro sólida la suavizará sólo porque me gustan las líneas lisas. Y entonces, obviamente puedes agregar etiquetas de ejes, títulos de gráficos, lo que tu corazón desee. Pero ahí lo tienes. Bastante cool. Forma bastante interesante de destacar o resaltar rangos de fechas específicos dentro de un gráfico en Excel . 4. Automación con OFFSET, COUNTA y rangos señalados: El siguiente demo es uno de los ejercicios más importantes de todo el curso, y se trata de automatizar gráficos usando Offset, Count A y Named Rangos. Ahora la razón por la que esta conferencia es tan importante es porque funciones como Offset y Count Day son los bloques básicos para crear gráficos y gráficos verdaderamente dinámicos. Básicamente, te permiten creargráficos fluidos, dinámicos, gráficos fluidos, dinámicos datos de origen en lugar de matrices fijas tradicionales, lo que realmente te permite dar vida a tus visualizaciones e integrar algunas mucho más avanzadas funcionalidad. Así que dirígete a la pestaña de actualizaciones automáticas de gráficos. Y como puedes ver aquí, tenemos un sencillo conjunto de datos. Dos columnas, fecha y temperatura. Y digamos que mi trabajo es actualizar un gráfico de temperatura de tendencia con cada día que recojo nuevos datos. Lo que podrías hacer es seleccionar celda a uno B 24 e insertar un gráfico de líneas igual que normal. Y eso está totalmente bien. Los únicos retos que, medida que añades más días de datos e ingresas manualmente nuevo gráfico de temperaturas no sabe actualizar porque, como puedes ver, sus datos de origen se fijan a través de la Carretera 24 por lo que no ve ninguna nueva puntos de datos que se agregan debajo de esa opción y Excel es convertir o dar formato a este rango de datos como una tabla y luego tabular hacia abajo para agregar nuevo asado esa tabla. Pero les voy a mostrar un enfoque que es un poco más flexible y mucho más poderoso, y eso es usar esos offset y contar una funciones en forma de rangos nombrados. Entonces lo que tenemos que hacer aquí es dirigirnos a nuestra pestaña de fórmulas y hacer clic en el botón definir nombre . Vamos a hacer esto dos veces primero para valores X, que son fechas y segundo para por qué Valores Qué temperaturas del aire. Empecemos con las fechas. Entonces, en realidad, sólo nombre fechas debilitar alcance esta matriz llamada a una pestaña en particular o al libro de trabajo en su conjunto . Eso está bien por ahora. Y aquí es donde normalmente veríamos una matriz fija de celdas que sirven como los datos de origen para nuestro gráfico. Lo que queremos hacer es eliminar eso y escribir la función de desplazamiento, y lo que offset hace resumir demasiado rápido es que le dice a Excel. Quiero que empieces en esta celda en particular que definas queriendo bajar un cierto número de filas de izquierda a derecha. Cierto número de columnas y devuelven una matriz de celdas con una altura particular y con definido por el número de filas en el número de columnas. Y lo hermoso es que puedes anidar funciones dentro de su para que en lugar de codificar duro las dimensiones específicas de la matriz resultante, puedas hacerlo fluido y dinámico. Eso es exactamente lo que vamos a hacer, usando el conteo una función. Entonces con Offset. Es un poco complicado de ver, porque cuando estás escribiendo funciones aquí con el nombre Manager, Excel no te ayuda paso a paso. Por lo tanto, observa con cuidado. Por suerte, hemos escrito algunos de estos, así que conozco los pasos, pero básicamente vamos a abrir el paréntesis, y la primera pieza de nuestra función offset es el inicio. Entonces así para nuestra matriz de fechas, la celda de inicio será la primera fecha en la matriz, que es vender a a, y luego, como comas sobre se moverá a cada componente de la función. El siguiente pedazo de la función offset dice: ¿Quieres mover hacia abajo o hacia arriba alguna rosa desde ese punto de partida? Este caso no quiero mantener eso un cero. Ese es nuestro punto de partida y siempre será el siguiente comentario dice, ¿Quieres moverte a la izquierda o a la derecha de ese punto de partida otra vez? La respuesta es no. Lo que nos lleva al siguiente componente, que es la altura de nuestra matriz resultante. Y aquí es donde queremos al Conde A. Porque, bueno, podríamos decir OK, queremos una matriz con una altura de 25 filas y con un código duro 25 ahí dentro. Entonces, cuando agregamos nuevos datos, todavía va a leer las primeras 25 filas y servir el mismo propósito que una matriz fija tradicional , que realmente no nos lleva a ninguna parte. Entonces en lugar de codificar duro un número ahí, vamos a usar el recuento, una función que básicamente cuenta todas las celdas dentro de un rango dado que contiene algún valor. Entonces cualquier celda no en blanco y el rango en el que vamos a buscar en este caso abre el paréntesis y selecciona toda la columna A y cierra el paréntesis. Entonces ahora el recuento de una función está diciendo, Mira toda la columna. A. Dime cuántas celdas dentro de la columna pueden contener algo, Entonces en este caso, la función Contar Día miraría toda la columna A y contaría 24 celdas que contienen datos, que es casi lo que nosotros quieren excepto que no queremos contar el encabezado. Entonces solo necesita restar uno aquí y coma sobre el componente final de la función que es cuál es el ancho de la matriz resultante. , En cuanto al número de columnas, este caso puede entrar a una porque nuestros valores X están todos contenidos dentro de una matriz unidimensional que tiene una columna de ancho por lo que puedo cerrar ese paréntesis que envuelve nuestro día de offset y recuento función. Comprimir. Está bien. Y ahora vamos a hacer exactamente lo mismo para las temperaturas y la columna B. Así que para encontrar nombre, llámalo temperaturas ámbito cuaderno está bien. Y de nuevo, vamos a tipo de función offset aquí. Este caso, nuestro punto de partida. También es Selby . No queremos mover ninguna rosa. No queremos mover ninguna columna desde ese punto de partida. El alto de nuestra matriz resultante es Count A este caso de la columna B menos uno para la coma de cabecera Una columna de ancho cerrar el paréntesis Boom. Ahí lo tienes. Por lo que ahora hemos nombrado un raise para definir de una manera mucho más dinámica. Cuántos valores necesitamos trazar a partir de las columnas A R X valores y columnas B R Y. Valores. Por lo que el paso final es simplemente ajustar nuestra fuente de gráfico seleccionando los datos y comenzando con la temperatura, que es nuestro por qué los valores. Podemos editar ese Siri, como puedes ver aquí Serious está actualmente fijo en las celdas B dos a B 24. Por lo que ese rango nunca va a cambiar. Y nunca va a ver nuevos puntos de datos que se añadan debajo de él. Todo lo que necesito hacer es eliminar todo después de ese signo de exclamación y temperaturas de tipo, que es el llamado Array que acabamos de crear para nosotros. De acuerdo, misma cosa exacta para el eje X horizontal. Se fija en un dos a través de un 24. Voy a borrar todo después del signo de exclamación y escribir fechas. Presiona OK y quieres hacer eso. No verás que nada cambie inicialmente, pero a medida que añades más datos y escribes más temperaturas, puedes ver ahora que nuestro gráfico se actualiza automáticamente con esos nuevos puntos de datos. Si seleccionas la línea y seleccionas los Siri's, ahora verás que los Siri's reflejan todos los datos a través de la Carretera 27 ahora. Por lo que ahí tenemos una imprimación rápida y el uso de funciones de día offset y recuento dentro de rangos nombrados para crear automáticamente la actualización de gráficos con datos fuente fluidos y dinámicos. 5. Agregar la funcionalidad del Scroll y Zoom con el control de los formas: Este siguiente demo es uno de mis favoritos. Es uno realmente genial. Es algo que mucha gente no se da cuenta de que incluso podrías hacer en Excel. Todo se trata de crear gráficos que puedan desplazarse y hacer zoom dinámicamente. Para ello, vamos a usar la función de desplazamiento. Pero en lugar de combinarlo con Count Day como lo hemos hecho en el pasado, esta vez vamos a insertar algo llamado controles de formulario, que permitirá a los usuarios hacer clic y arrastrar y manipular barras de desplazamiento en el libro de trabajo para dinámicamente cambiar la vista que están viendo dentro del gráfico. Por lo que es una combinación realmente genial de herramientas para crear un efecto realmente interesante. Por lo que en esta pestaña de diagramas de desplazamiento y zoom, primero lo primero, asegúrate de tener la pestaña de desarrollador en tu cinta. De no ser así, es súper fácil de agregar. Simplemente dirígete a la opción de archivo yendo a opciones en la cinta personalizada inferior y aquí, en el lado derecho del dolor, deberías ver una casilla de verificación junto a la línea de desarrollador. Si eso no está marcado, adelante y dale un cheque y pulsa OK y deberías verlo aparecer ahí mismo y ahora el desarrollador Tab es donde puedes ir a editar visual basic o VB un código para escribir o editar macros. Y también incluye los controles de formulario que vamos a estar usando para esta demo en particular. Entonces en la opción de inserción aquí, adelante y seleccione el control de formulario de la barra de desplazamiento. Y estos son realmente geniales. En realidad puedes arrastrarlos hacia fuera hasta la forma exacta que quieras. Entonces algo como esto va a funcionar muy bien. Y con su todavía seleccionado, voy a golpear el control C seleccionar un control de celda vacía V para pegarlo. Y ahora tengo que desplazar barras una a scroll divino y otra para definir Zoom. Ahora, para realmente personalizar estos y asegurarse de que están haciendo lo que necesitamos que hagan, puede hacer clic derecho entrar en sus opciones de control de formato. Entonces en este caso, estamos mirando la barra de desplazamiento, que define la ventana de datos que un usuario está mirando ya sea desplazado hacia arriba o hacia abajo dentro esta matriz aquí. Por lo que el valor mínimo aquí será cero y el valor máximo será 11. Porque si están empezando en enero como desplazamiento predeterminado 11 meses abajo, te llevaremos justo al fondo de esta matriz en la fila 14. No necesito cambiar las opciones incrementales o de cambio de página. Lo importante es este enlace celular aquí. Entonces si selecciono el selector, voy a vincular este control de formulario para vender d 16 cuando presione OK y d seleccionarlo. Ahora, conforme manipulo esto, se puede ver el valor y vender D 16 actualizando en consecuencia. Y podríamos simplemente confirmar que tenemos un máximo de 11 y un mínimo de cero, igual que establecemos tan mismo trato con el control de formato de zoom. En este caso, zoom se define por el número de filas del visor que está mirando en un momento dado, y ese número de filas puede crecer o reducirse. Pero el fondo es que hay que estar buscando al menos un punto de datos o al menos una fila de datos, por lo que le daremos un mínimo de uno y un máximo de 12 que es la altura de nuestros datos brutos. Ray aquí y en este caso, mi enlace celular será así d 17 presionaré OK y confirmé que mi mínimo es uno. Mi máximo es de 12 así que sigamos adelante y solo pongamos estos en algún lugar del medio. Y quiero poner en marcha las cosas insertando un gráfico solo para poder ajustar y pulir y finalizar el formato antes de dar los pasos para que realmente sea dinámico. Entonces en este caso, voy a controlar Click Columnas B y C junto con la columna E. Porque quiero crear un gráfico combinado donde las impresiones sean mis columnas, haga clic. El precio es mi línea. Siéntete libre de usar cualquier combinación de estas tres métricas o sígueme totalmente hasta ti para que mis opciones de inserción entren en columnas. Más gráficos de columnas Combo el camino. Haga clic en la tarifa como línea en mi eje secundario y ahora de nuevo, siéntase libre de seguir junto con estos ajustes de formato que estoy haciendo o formatearlo como usted elija. Ya sabes, esta realmente no es la parte importante de la conferencia. Simplemente voy a formatear el área de parcela, deshacerme de mi relleno y frontera, hacer algunos cambios en mi línea. De Siri. Podría sólido, oscuro grand con el ancho de dos. Suavizalo y agrega una sombra rápida, preestablecida de 80% de transparencia y luego con mis columnas, dale a estos un Phil verde claro y ajusta el hueco con abajo. Y por último, sólo voy a añadir un elemento de gráfico con algunas líneas de cuadrícula horizontales secundarias menores. Entonces ahí lo tienes. Estoy contento con ese formateo otra vez. Personalízalo como más le parezca. Ahora es el momento de hacer realmente esta cosa dinámica porque en este momento se alimenta de estas matrices de datos fijos tradicionales, que necesitarán actualizar el enlace del dedo del pie a sus valores de desplazamiento y zoom. Entonces para hacer eso, voy a entrar en fórmulas, definir un nombre, y empecemos con nuestros valores X. Entonces llamaremos a este Scroll X, y en lugar de esta referencia de celda fija aquí, vamos a escribir en función offset. Mis ex valores son mis meses. Entonces mi punto de partida es mi primer mes, que vive en la celda B tres y el número de filas que quiero desplazar Ese punto de partida va a ser una función del índice de desplazamiento, que vive en la celda D 16. Porque recuerda, como un usuario desplaza esa barra de desplazamiento a la izquierda y a la derecha ahí esencialmente moviendo el punto de partida de esa ventana de datos que están mirando hacia arriba o hacia abajo. El siguiente pedazo es cuántas columnas sobre quiero desplazar ese punto de partida tipo cero causa no quiero desplazar el punto de partida en absoluto. Mis meses siempre viven en la columna B. La segunda a última pieza, recuerden, es la altura que en este caso está definida por el índice zoom. Ya que los usuarios estarán cambiando esa barra de zoom y esencialmente creciendo o encogiendo, la ventana que están mirando finalmente con en términos del número de columnas es de nuevo solo una tan encerrada que los paréntesis presionan bien y nuestros valores de scroll X son definido. Ahora voy a navegar por ese mismo proceso para cada uno de nuestros valores Y, comenzando con scroll, por qué o ardidamente Y de nuevo solo escribiremos offset aquí punto de partida para nuestros primeros valores Y que son impresiones vidas en C tres Gonna desplazarlo hacia abajo por el valor en d 16. No quiero mover ninguna columna. El alto es el índice de zoom y D 17. Ese ancho es uno cerrar el paréntesis pulse OK, y una vez más para scroll ancho. Para compensar este caso. Mi primer clic, el valor de tasa Celie tres número de filas. Estoy cambiando su d 16 número de columnas cero altura es D 17 con es uno tan mismo proceso esencialmente repetido tres veces Ahora el último paso es integrar en realidad esos nuevos campos nombrados que creamos en nuestro gráfico para que pueda hacer clic derecho en seleccionar datos. Y en cada una de las opciones de Siri, voy a hacer clic en editar y en lugar de la matriz fija de C 33 c 14. Esta es mi columna Impresiones, que es mi pergamino. Por qué columna Así que sólo puedo escribir el nombre de ese nuevo Array llamado justo ahí. Ardilla. Por qué y presione OK y repita el proceso para hacer clic a través de la velocidad, elimine todo después del signo de exclamación y simplemente escriba scroll. Por qué, también, presionar OK, y ahora una nota realmente, realmente importante es que necesito editar mi eje X también, así que entraré en editar, borrar todo después del signo de exclamación, scroll de esta X prensa. OK, pero aún no he terminado. A pesar de que parece que solo hay un valor que necesita ser ajustado para mi eje X, necesito cambiarlo dos veces una vez con la impresión seleccionada y otra vez con Click the rate selected. Es una cosa realmente, realmente fácil de perderse. Pero ahora, como se puede ver, si se selecciona haga clic en la tarifa. Si vuelvo a mis opciones de edición, ves esa referencia fija ahí otra vez. Por lo que necesito ingresar Scroll X dos veces y ahora el eje X asociado con Click the Rate y el eje X asociado a las impresiones, que, manera realista, es el mismo. El eje X está reflejando el nuevo Array llamado en todas las instancias, por lo que puedo presionar. De acuerdo, así que vamos a ver si eso realmente funcionó. Intenta probar. Nuestro pergamino parece funcionar muy bien. Todo el camino a la izquierda significa que estamos empezando en enero. Ahora mismo, estamos mirando una ventana de siete meses, lo cual tiene sentido. Ya que tenemos un índice de zoom de siete. Nosotros queremos de ustedes meses adicionales, puedo desplazarme a la derecha. Ahora estamos mirando la ventana completa, o puedes perforar hasta sólo dos o tres o cuatro meses a la vez. Y ahí tienes. ¿ Qué tan guay es eso? Es un gráfico realmente único, realmente interactivo que puedes construir con controles de forma bastante simples y nombrada a una raza en sí misma. Pero hay un problema aquí que tenemos que abordar, que es que si hacemos zoom hasta el final, realidad no tenemos rieles de protección en su lugar para mantener esta matriz dentro de los límites de nuestros datos fuente originales. Entonces, como se puede ver desplazando justo más allá de todos los datos en una matriz que simplemente ya no tiene ningún sentido y nada es incorrecto en esto, todavía está trazando los datos correctos. Sólo necesitamos encontrar la manera de volver a poner algunos rieles de guardia en su lugar para evitar que esto suceda . Por lo que si juegas un poco con esto, notarás que cualquier combinación de scroll y zoom en los siguientes valores que sean iguales o menores a 12 producen una vista válida. Entonces, en otras palabras, si me hacen zoom hasta los 12 meses y mi índice de desplazamiento se establece en cero, lo que significa que estoy empezando en enero, esta vista es legal. Pero en cuanto intento desplazarme más allá de diciembre, ahora estoy en problemas. Mismo caso. Si me acerco a ocho meses y me desplaza más allá de un valor de cuatro, puedes verlo una vez más en esa zona de peligro. Entonces todo lo que necesito hacer aquí es correcto, una fórmula para definir el valor máximo que quiero que tome el índice de desplazamiento, y así podría hacer eso, usando una sentencia if condicional aquí. Y diré, si mi índice zoom más mi índice de desplazamiento es menor o igual a 12 entonces acaba de regresar, cualquiera que sea el índice de ardilla hubiera sido, porque sabemos que si la combinación de esos índices es menor a 12 entonces tenemos un vista válida, lo que puedo seguir adelante y simplemente reflejar el índice de desplazamiento en ese caso, lo contrario el valor si es falso, si el total es mayor a 12 que puede evitar que la vista supere los límites solo tomando 12 menos el zoom en el siguiente valor presiono enter. Se puede ver que en cualquier momento el total supera los 12. My Max scroll fija su valor en cualquier momento. El total es menor de 12 o igual a 12 Micmac Scroll refleja el índice de desplazamiento original. Entonces para implementar este cambio y asegurarme de que los cambios reflejados en nuestro gráfico, puedo volver a la pestaña de mis fórmulas, dirigirme al gestor de nombres y ajustar esos tres rangos con nombre que creé, empezando por Scroll X y comprimirlo en él. Todo lo que necesito hacer aquí es cambiar mi referencia a D 16 que es mi índice de desplazamiento original a D 18 que es mi desplazamiento máximo. Por lo que podría simplemente agarrar los seis cambios a en 18 y presionar OK, hacer lo mismo con scroll. ¿ Por qué cambiar el 16? Dos en 18 y desplazamiento Miente a 16 dos en 18. Y ahora si nos desplazamos a la derecha 2 de octubre, noviembre, diciembre. Ahora, como pueden ver, no me permite desplazarse más allá. Entonces, esencialmente, lo que eso significa es que si me acercan todo el camino a la vista máxima de 12 ahora, la barra de desplazamiento es esencialmente inútil porque no me está permitiendo desplazarme fuera del reino o de los límites de mi original. Muy bien, así que ahí lo tienes usando controles de formulario y funciones de desplazamiento para crear gráficos dinámicos de desplazamiento y zoom en Excel. 6. Animación de cambios con el tiempo: Entonces el otro día estaba en línea y se encontró muy cool. Ejemplo de fecha de es que quiero compartir con ustedes chicos lo encontraron en el sitio del Pew Research Center, que es una organización que captura una gran cantidad de datos relacionados con la política y la política de Estados Unidos. Y básicamente tenían esta pieza realmente genial sobre la polarización política, y construyeron este visual animado que pensé que era lo más cool de la historia. Es un gran ejemplo de usar gráficos de área superpuestos y animarlos a lo largo del tiempo para contar una historia realmente clara. Y lo que estamos viendo aquí son básicamente puntos de vista demócratas y republicanos en términos de ser consistentemente liberales versus consistentemente conservadores. Y esencialmente, lo que está haciendo este gráfico de área es que casi actúa como un doble silbido un gramo, donde se está viendo la distribución de dos conjuntos de datos diferentes cambiando con el tiempo así como la superposición entre ellos. Y así tienen cinco años diferentes en la muestra, y al pasar la página, se puede ver cómo, tiempo estos puntos de vista se han desplazado y eventualmente, al llegar al 2014 se puede ver esta división muy clara o polarización teniendo lugar así que naturalmente vi esto y dije, solo tengo que replicar esta cosa y sobresalir porque es Está tan limpio. Es tan cool Cuenta una historia tan poderosa. Entonces pensé que este sería un gran ejemplo para usar como demo para pasearlos de principio a fin. Entonces vamos a entrar en excel y vamos a estar en los cambios de animación a lo largo del tiempo Tab. Básicamente, lo que hice fue volver a crear un nuevo conjunto de datos sin procesar, y tenemos los mismos cinco años, 94 a través de 2014. Y para cada año, tenemos 21 puntos de datos diferentes surgieron, lo que demuestra que puntúan desde liberal hasta conservador, donde el medio representa puntos de vista mixtos. Y entonces tenemos el número de demócratas y el número de republicanos que se alinean con cada uno de esos puntajes en cada uno de estos años. Entonces, lo que haremos es empezar por solo crear un gráfico de área para un año dado. Sólo que podamos conseguir el formato correcto y empezar a conseguir nuestros rodamientos. Por lo que voy a seleccionar ser ganado a través de d 22. No necesito el punto de datos del año real aquí, así que voy a insertar dentro de la línea, opciones a a área D y hacer un poco de formateo aquí. Entonces hagámoslo grande, porque es realmente genial. Deshaznos del título del gráfico aquí, y me voy a formar en el área del gráfico. Simplemente no quiero realmente una frontera a su alrededor. Y luego para los demócratas, republicanos en la leyenda, quiero que eso sea mucho más grande, audaz y simplemente extenderlo un poco. Por lo que a continuación quiero cambiar los colores reales y la transparencia de los dos datos. Siri está aquí dentro, y me gustaría alinearlos con los colores apropiados. Entonces para mis demócratas, Siri le voy a dar un sólido azul oscuro Phil. Y llamémoslo, llamémoslo 30% transparencia. Y para mi republicano, Siri le va a dar un Phil rojo oscuro con ese mismo 30% de transparencia. Ahí vamos. Ahora tenemos un demócrata Azul. Siri es un republicano rojo, de Siri. Está muy claro dónde radica la superposición, y ese es un ejemplo realmente bonito de una fecha de es si estuvieras mirando una instantánea en el tiempo desde 1994 por lo que ya hemos hecho un trabajo bastante bueno visualizando este conjunto de datos a la mano y en este caso en realidad no necesitan realmente las etiquetas del eje y porque realmente no suma mucho para ver los del Conde. En realidad nos interesan las diferencias relativas entre los dos conjuntos de datos. Entonces ese es el paso uno construyendo un ejemplo. Ahora, la clave es animar realmente esta cosa a lo largo del tiempo. Tenemos cinco años en el conjunto de datos. Yo quiero ver cómo han cambiado las cosas a lo largo de ese lapso de tiempo. Entonces lo voy a hacer es solo escribir año selecto y así f cinco lo hacen audaz y cursiva. Dale un alinear a la derecha y luego aquí y vendo g cinco. Voy a usar un poco de validación de datos. Y lo que quiero hacer es crear una celda donde los usuarios puedan bajar y seleccionar cualquiera de esos cinco años en la muestra igual que vimos en el sitio. Entonces para hacer eso, necesito una lista de fuentes para este desplegable, y así es uno. Yo sólo voy a entrar en los años. Es 94 99 ya sabes, supongo que lo haré La forma legítima podría seleccionar todos mis años aquí. Incluso con los duplicados, podría pasarlos aquí y luego en los datos solo presionar el botón eliminar duplicados. Y ahí tienes. Entonces eso me deja con mis cinco años únicos en el conjunto de datos y ahora de vuelta a G cinco. Voy a usar una opción de validación de datos, que está en mi pestaña de datos. Validación de datos. Y lo que quiero permitir en esta celda es una lista de valores y la lista fuente de esos valores vive justo aquí de s one. El resto cinco prensa. OK, ahí tienes. Tengo mi lista. Hagámoslo audaz y centrado y darle un poco de Phil solo para que los usuarios No. De acuerdo, esto es algo que puedo seleccionar. Entonces ahora tenemos nuestro desplegable, pero no está vinculado a nada. Todo lo que estás haciendo es simplemente cambiar el valor y G cinco en sí. Entonces lo que necesitamos hacer es crear un segundo conjunto de datos, que va a ser los nuevos datos fuente para este gráfico y que ese conjunto de datos sea dinámico en función del año que un usuario haya seleccionado. Entonces voy a empezar por agarrar los encabezados y pegarlos aquí abajo del gráfico. Este será mi nuevo conjunto de datos de fuente dinámica, y puedo agarrar todas las puntuaciones, ya que esas serán las mismas y pegarlas aquí y ahora, el año que va a igualar. Sea lo que sea que el usuario haya ingresado en G cinco, voy a presionar F cuatro para arreglar esa referencia. Si desea obtener más información sobre los tipos de referencia, definitivamente recomienda consultar Mi Excel Analytics, Fórmulas y funciones avanzadas. Por supuesto hay toda una sección sobre tipos de referencia, pero así básicamente, eso me permite arrastrar esto hacia abajo y tener la referencia permanecido fija. A G cinco en cada habitación. Entonces ahora si cambio el 94 a 99 mi 94 cambia a un 99 aquí está bien en mis datos fuente. Ahora la clave es poblar realmente los valores para demócratas y republicanos porque eso es lo que realmente va a impulsar el gráfico de área por encima. Por lo que hay algunas maneras de hacer eso. Algunas funciones diferentes que puedes usarla podrían usar coincidencia de índice. En este caso, voy a usar un poco su función. Entonces básicamente lo que hace algunos ifs es suma los valores en una columna en particular, dados ciertos criterios. Entonces en este caso, quiero algunos los valores en la columna demócratas y columna C y arreglar eso donde van los criterios. Llámalo a es igual al año y vendo G 33 puedo arreglar sólo la pieza G de eso. Y el segundo criterio es donde la puntuación y la columna B es igual a. El marcador acaba de reducirse porque es difícil ver el marcador en la h 33. Arreglaré el H ahí y dejaré que 33 cambie al 34 35 36 mientras aplico esta fórmula hacia abajo. Entonces sé que fue una corrida muy rápida a través de la función de cumbres otra vez. No quiero tomar demasiado tiempo cavando en fórmulas en este curso, pero hay toda una sección sobre funciones estadísticas en mi curso de fórmulas y funciones avanzadas . Así que definitivamente echa un vistazo a eso. Intenta replicar exactamente lo que he escrito aquí, y cuando presiono OK y arrastra eso hacia abajo, se rellena en mis valores demócratas, y podemos hacer una rápida Q A. Esto es datos de 1999, que tiene 9 2023 38 9 2023 38 se ve bien. Y ahora puedo copiar y pegar esa fórmula en la columna Republicanos, y lo único que necesito cambiar es la columna que estaban sumando valores de Así que ya no es columna C. Ahora lo llaman D, que es donde mis datos republicanos vive. Y mismo caso. Podría simplemente arrastrar eso hacia abajo y hacer una rápida Q A. Así que 234 16 19 234 16 19 se ve bien. Y ahora de nuevo, voy a cambiar este año 2004 cambios. Y debido a que estos aire ahora recubiertos como algunas funciones ifs, los valores para los demócratas y republicanos también cambian. Entonces ya casi estamos ahí. Todo lo que necesitamos hacer ahora es atar los datos de origen, que originalmente habíamos establecido sólo para 1994 a esta matriz dinámica que hemos creado. Y aquí hay un consejo realmente, realmente genial que en realidad no aprendí hasta hace poco, y ha sido un salvavidas, que es que creo que a partir de Excel 2013 o tal vez 2010 cambian las cosas para que si tú arrastra los datos de origen y solo intenta moverlo para un gráfico dado, que lo sepas, reformateó las cosas. Haré lo mismo con los datos republicanos establecidos aquí. Vamos a arrastrarlo hacia abajo. Mira, sólo se volvió a morar al azul y naranja, en cuyo caso tendría que pasar y decir:Está bien, Está bien, quiero llenarlo de rojo otra vez y darle ese 30% de transparencia. En realidad hay un trabajo alrededor para evitar que esto suceda, y así voy a deshacer esos dos pasos. Acabo de llevar la cabeza a la opción de archivo yendo a opciones aquí abajo. Si entras en Avanzado y Desplázate todo el camino hacia abajo para trazar esta última casilla de verificación que dice propiedades siguen el punto de datos del gráfico para el libro actual. Simplemente desmarca eso y presiona OK, no tengo idea de por qué lo predeterminado es despojar totalmente tu formato cuando mueves un origen datos Ray. Eso no tiene mucho sentido para mí, pero es lo que es. Y por suerte, una vez que conoces ese pequeño truco, es una solución fácil. Así que ahora, en lugar de hacer un reformato, puedo simplemente agarrar mi matriz de origen y cambiarme a su lugar para ambos mis Siri. Entonces hay demócratas y ver cómo se queda ese azul y aquí está mi republicano rojo en serio puede arrastrarlo justo ahí. También puedo cambiar los encabezados, pero de manera realista, es el mismo encabezado en cada caso, por lo que realmente no importa. Pero sigamos adelante y hagamos eso. Entonces ahora nuestras fuentes ligadas a nuestro array dinámico y lo que eso significa es ese auge. Se puede cambiar el menú desplegable, seleccionar un año nuevo y porque su estado de origen cambiando el gráfico en sí también está cambiando . Por lo que se puede ver que la distribución realmente genial está ocurriendo ahora. Entonces si quieres parar ahí, eso está totalmente bien. Creo que eso es impresionante. Te voy a mostrar otro tipo de paso de bonificación solo para que sea un poco más fácil la página a través de las animaciones en lugar de tener que hacer clic y seleccionar de una lista cada vez. Y para hacer eso, vamos a usar un tipo diferente de control de formulario de nuestro desarrollador Tab, y voy a insertar lo que se llama botón de giro, y básicamente eso es solo un botón superior abajo que cambia el valor en un dado. Entonces así que sigue aquí. Voy a hacer esto rápidamente y formar en el control, y le voy a dar un valor mínimo de un valor máximo de cinco, y lo voy a vincular para vender nuestra y presionar OK? Y lo que podría hacer. Aquí vamos a volver a mi pestaña de datos con G five seleccionado, y en realidad quiero eliminar la configuración de la lista de validación de datos que originalmente había hecho . Entonces voy a cambiar este dedo del pie cualquier valor, y eso básicamente restablece esta celda. Por lo que ahora, como puedes ver, este botón de giro cambia el valor en nuestro de 1 a 5. Y en realidad, lo que voy a hacer es querer cortar eso y pegarlo en Q en lugar de r. Y te mostraré por qué en solo un segundo sigue ligado al spinner. Y ahora, y llamarlo son es tipo 12345 Y esencialmente, eso es asignar un número de índice a cada uno de los años que tengo disponible. Por lo que un índice de uno corresponde con 94 a corresponde con 99 así sucesivamente y así sucesivamente. Entonces lo que eso me permite hacer en la celda G cinco es reemplazar el valor de este año con la función buscar arriba , y voy a decir, buscar el valor que mi spinner está produciendo actualmente dentro de esta pequeña matriz de nuestra uno a través de nosotros. Cinco. Arreglar ese rayo entero y luego los índices de columna a porque quiero tirar el valor a la segunda columna más de mi índice y mi rango. Buscar cero, porque quiero encontrar el valor exacto y presionar enter. Entonces otra vez, algo así como las cumbres. Ese fue un curso acelerado muy rápido en búsquedas v, pero esencialmente, la función de búsqueda le permite buscar un valor en particular. Este caso buscaban un número de índice del 1 al 5 y devuelven los datos de una columna correspondiente. Por lo que encontramos el siguiente número. Devolvemos el valor del año de la siguiente columna más. Eso es lo que significa ese índice de columna de dos. Entonces lo que eso me permite hacer es atar el valor del año a mi pequeño spinner aquí y hacer que sea un poco más fácil de usar de página a través de los cinco años en mi muestra. Por lo que ahí lo tenemos. Esta es una excelente manera de visualizar tendencias a lo largo del tiempo, utilizando gráficos de área superpuestos animados 7. Crear un tablero dinámico parte 1: las siguientes dos demos aire todo sobre la construcción de cuadros de mando dinámicos. Y ese es un tema realmente candente en Excel porque requiere que entres una serie de habilidades diferentes, tienes que usar fórmulas y funciones para jalar dinámicamente las piezas correctas de tus datos . Tienes que usar gráficos y gráficos para visualizar tus datos de una manera significativa, y tienes que combinar todo eso para transformar esencialmente un conjunto de datos sin procesar en una interfaz de usuario limpia e interactiva. Entonces en la pestaña de panel dinámico construyó un marco simple para dashboard que vamos a empezar a poblar Y si revisas este pequeño cuadro plus, aparece encima de la columna que en realidad revelará los datos brutos, que en este punto vive en columnas a a a P. Y solo si tienes curiosidad sobre cómo configurarlo, puedes simplemente seleccionar cualquier agrupación de columnas o filas. Entonces, en la pestaña de datos, aquí está esta opción de grupo, y eso solo los consolida para que puedas ocultarlos de la vista. En este caso, tengo mis datos brutos aquí mismo, lado a lado con mi tablero. Pero obviamente podrías poner tus datos crudos en una pestaña diferente. Si eliges, eso depende totalmente de ti. Entonces los datos que estamos viendo aquí son datos de béisbol por jugador. Tenemos año, equipo y liga, equipo y liga, seguido de una serie de métricas ofensivas, juegos a los murciélagos, carreras , hits, dobles, triples y así sucesivamente y así sucesivamente. Y luego aquí en el pequeño marco que he empezado a construir, tengo una celda de validación de datos donde el usuario puede seleccionar a cualquier jugador. Si quieres ver la lista de fuentes para esto, entra en validación de datos de datos. Y aquí está la lista de fuentes AI a través de un I 100 y, como se puede ver por aquí, acaba de ocultar la columna para mantenerla fuera de ti. Pero aquí está mi lista de jugadores. También tengo una lista de métricas únicas, que se usarán más adelante. Entonces, solo ocultemos eso de nuevo y empecemos a construir esto fuera. Entonces para sacar los datos, voy a usar una de mis funciones favoritas llamada algunas ifs. Y lo que hace algunos ifs es que extrae o suma datos de una columna dada bajo una serie de criterios específicos. Entonces en esta celda, aquí son cinco. Lo que quiero hacer es algunos datos de mi columna at bat, que está aquí mismo, y llamarlo F donde son ciertos dos criterios. El primero es que el jugador es igual al nombre que un usuario ha seleccionado y el año es igual cualquier valor que corresponda en columna son tan para hacer que voy a escribir igual a algunos ifs y mis algunos rangos Mi columna al bate. Entonces selecciona toda la columna F Voy a arreglarla presionando el atajo F cuatro. Y ahora los criterios Rango número uno. Usemos el nombre del jugador es nuestro primer criterio. Por lo que mis criterios rangos la columna de nombre y un fijo ese también. Y los criterios con el valor en esta columna necesita igualar es lo que esté en la celda s a ésta también se arreglará. Ahora, mi segundo rango de criterios va a ser mi columna del año, que es B a las cuatro para arreglarlo y los criterios que debe ser cierto Aquí. Observe cómo mi, um, mis fórmulas bloqueando celular son cinco años. acceso a Soto son cinco. Podría simplemente flecha hacia la izquierda, y eso poblará nuestros cinco en mi fórmula. Ahora, en lugar de arreglar todo el arte cinco. Quiero presionar F four para recorrer mis diferentes tipos de referencia, y solo quiero arreglar el Are. Y la razón por la que estoy haciendo eso es para que, al arrastrar esta fórmula hacia abajo, se lea de nuestros seis son siete R ocho o nueve y tirando de datos para el año correcto. Si arreglara el Son y los cinco, siempre leería datos de 2010 sin importar dónde viva esta fórmula. Y la razón que fijó la pieza de columna de eso es para que pueda arrastrar esta fórmula hacia la derecha y que siempre la lea los años en columna son en lugar de tener esa columna shift toe s o té o tú, el punto se hace referencia. tipos fijos y relativos son increíblemente importantes, sobre todo cuando estás replicando fórmulas en varios lugares. Y tener una comprensión realmente firme de los tipos de referencia te ayudará a trabajar de manera real, realmente eficiente. Por lo que esto algunos su función es buena para ir en una prensa enter y luego arrastró esto hacia abajo. Como se puede ver, se está poblando en datos murciélagos para Albert Pools para cada uno de estos seis años en la muestra. Y ahora, igual que un atajo rápido porque configuro mis tipos de referencia de manera inteligente. Podría simplemente copiar esos valores, pegarlos aquí y ahora la única pieza que necesito cambiar es el rango de suma. Entonces en lugar de F que era mi columna al bate Ahora quiero algo H, que es mi columna hits y los otros criterios el nombre del jugador y el año van a ser cierto para todas estas métricas. Entonces eso es lo único que necesito para cambiar esto con alguna prensa de rango entrar ahí, aplicar eso hacia abajo y voy a repetir este proceso para todas mis volumétricas. Así corre, duplica, triplica jonrones, R B I's robadas bases, pillado robando paseos En caso de que también veas que tengo tres métricas de tarifa aquí promedio de bateo , base robada porcentaje y ponches de dedo del pie camina. Esos se van a calcular usando funciones diferentes, más simples. Entonces ahora mismo, solo voy a pasar y seguir con mi hijo ifs para poner todas mis volumétricas en su lugar que pudiera simplemente pegar esto todo el camino y luego solo uno por uno, cambiar mi referencia de columna. Por lo que las carreras están en la columna G. Así que cambia el H dos g dobles no están llamando. Yo así cambio la edad a y triplica R y J H 20 J. Y en este caso, ¿te has dado cuenta de que tengo una serie de métricas en el mismo orden que duplica triplica jonrones, R B I robó base pillada robando son todos en el mismo orden aquí. Por lo que un pequeño atajo lleva para realmente quitar la referencia fija de mi columna y eso permitirá que j cambie a una k, l m y n. y como se puede ver, mirando la fórmula como página a la derecha, ya verás que eso está pasando. Y ahora los paseos están en o y ponches. Eso Samos k no es p. Así que ahí vas. Tengo todas mis métricas de volumen hechas. Arrastra todo esto hacia abajo, arrástralo hacia abajo, y aplicaré mis métricas de tarifa. Estos aire apenas calcularon los campos, por lo que los promedios solo pueden igualar golpes sobre los murciélagos. Y un consejo que es realmente útil aquí es porque estoy construyendo un tablero donde el usuario podría ser capaz de seleccionar un jugador que no tiene valores en cada celda. En lugar de permitir que esta columna calculada devuelva un N feo un error de referencia. Simplemente voy a envolver esta función en una declaración if error. Entonces si error abrir paréntesis T cinco dividido por s cinco y coma te lleva a este valor si error pieza y solo quiero devolver un pequeño guión rodeado de comillas. Entonces ahora si llego a un jugador que no tiene valores para un año determinado, esto sólo mostrará un pequeño guión, que es mucho más pulido que la tradicional función de error. Entonces lo mismo con tarifa base robada, va a igualar bases robadas divididas por paréntesis abiertas, bases robadas más robo atrapado, lo cual es total intenso. Y podría mantenerlo igual que ese o mismo caso lo puedo envolver justo después del signo igual dentro si error abierto, , guión y comillas de Prentice . Y ahí lo tienes y mismo trato exacto tachado dedo del pie cuestión andador. Va a ser una e dividida por una D, y usaré el romance ahí también. Entonces ahí lo tienes ahora para mis totales solo podría usar algún problema de funciones. Mis volúmenes y yo sólo podríamos pasar a estos abajo y va a traer a mis hijos y luego pequeña propina aquí. No. Ahora el tamaño de la fuente es más grande y audaz en esta fila. Si quiero aplicar esta función sin tener que reescribirla aquí, puedo golpear el control C y luego usarlo todo. Atajo de punta, Ault H v f Y eso sólo pega la fórmula mientras mantiene el formato de la celda en la que estás pegando. Tan bonito pequeño truco su control. Ver Ault, H B f y por último pero no menos importante, todo th VF. Y ahí va. Y tengo todos mis datos brutos poblados de tal manera que puedes seleccionar a cualquier jugador aquí y todos tus datos se actualizarán en consecuencia. Entonces note cómo por porcentaje base robado Billy Butler tiene un número de años donde no robó ni intentó ninguna base robada. Esos casos, solo consigues ese bonito pequeño guión, que está súper limpio en lugar de la declaración de aire. Entonces ahora que tenemos un dato crudo, quiero empezar a usar algunas técnicas para llamar la atención sobre campos que nos importan. Así que digamos que el promedio de R B I y la relación de tachado a caminar son una especie de nuestras métricas clave aquí. Ya sabes, algo fácil de hacer sería simplemente hacer que esos audaces llamaran más la atención de esos campos. Otra cosa que podemos hacer es agregar algún formato condicional con una bonita escala de colores aquí para llamar la atención sobre años en los que este jugador se desempeñó particularmente bien o mal. Y así puedes hacer es algo así con un dedo verde blanco. Lo bueno es que pueda manejar esa regla. Selecciona la regla que acabo de crear y edítala para que en lugar de pasar de verde a blanco hace de verde a este azul claro, que coincide con las celdas a su alrededor, eso hace que se mezcle bien. Aquí, solo puedo seleccionar esa matriz en la pestaña de inicio. Puedo hacer clic en el botón de formato pintor y tan solo hacer clic en C cinco, y eso aplicará el mismo formato. Sepa que también cambió el número. Entonces solo formatea las celdas y cambia el número a cero puntos decimales. Y ahí vas ahora por ponche a pie ratio. Yo quiero hacer algo muy parecido, pero en este caso, mayor número es peor porque significa que estás golpeando con más frecuencia de lo que estás caminando . Entonces quiero el mismo formato condicional solo invertido para que los números verdes de los números más bajos para que pudiera volver a entrar a mis escalas de color y sólo voy a hacer un blanco a Verde, que es lo contrario de dedo verde Blanco y luego mismo trato. Gestionar eso. Edita la regla y cambiaremos el máximo a este azul claro para que se mezcle realmente bien. Entonces insertemos algunos gráficos combinados. voy a construir desde cero, empezando por la columna. Vamos a redimensionarlo aquí abajo. Seleccionaré los datos donde mis primeros Siri's van a ser hits. Mis valores serios serán vende t cinco a t 10 y mis valores ex serán mis años 2010 a 2015. Añadamos un segundo Siris para promedio de bateo. Mis valores serán ustedes cinco ahí, ustedes 10. Mi eje x será el mismo seis años en la prensa muestral. OK, ir a las herramientas de gráfico cambió el tipo de gráfico dos combo. Y aquí quiero que mi promedio sea una línea en el eje secundario comprimir bien ahí. Y ahora sólo se reduce a formatear. Entonces vamos a agregar algunos elementos van a agregar título de eje vertical primario, que es hits. Voy a agregar un título de eje vertical secundario para promedio. Voy a encogerme un poco el tamaño de mis etiquetas de acceso. Hacer que mis años sean audaces y encojan esos hacia abajo. Ahora vamos a hacer algunos datos. El formateo serio encoge un poco la brecha. Qué es Phillies con un azul claro y vamos a formato están mintiendo para ser suavizado azul oscuro dos puntos con y luego en el último punto de la línea. Qué se agrega etiqueta de datos Y nos conformamos en esa etiqueta para que esté por encima del punto de datos que tiene un relleno de fondo de azul oscuro. Ahora el texto es blanco y audaz. El último artículo sobre esto. Podemos simplemente añadir un marcador aquí, que puedes acceder aquí mismo en las opciones de marcador. Hagámoslo construido en el diamante talla siete White Phil y una línea azul oscuro sólida que tiene 1.5 puntos de ancho y último, pero no menos importante, solo voy para él en el área del gráfico y deshazte de la frontera. Y ahí lo tienes. Tan bonita representación visual poco limpia de hits y promedio trazada a lo largo del tiempo. Y ahora, porque mis datos son dinámicos, eso significa que mi gráfico también es dinámico. Ahora todo lo que tenemos que hacer es copiar pegar eso y seleccionar diferentes datos. Siri's Así que en este caso, quiero mostrar a los murciélagos y r B me arrastre hits dos a los murciélagos. Arrastraré mi línea del promedio de Teoh R B I. Ahora todo lo que tenemos que hacer es hacer algunos ajustes al formato. Por lo que los hits se convierte en murciélagos El promedio se convierte en R B I. Y vamos a retocar un poco el formato. Haremos un verde claro aquí con una línea verde oscuro, y necesitamos editar el marcador. Linus. Bueno, entonces Opciones de marcador El borde será de color verde oscuro. Y luego lo mismo con el sello Phil. Detrás de la etiqueta habrá un verde oscuro. Entonces ahora hemos construido un segundo gráfico que cambia de nuevo para mostrar a nuevas métricas en murciélagos y R B I. Y solo repitamos ese proceso una vez más para mostrar bases robadas aquí en la columna A y tasa base robada. Buen uso del gráfico combinado porque tenemos dos métricas que están en escalas completamente diferentes y luego conoces el taladro. Cambiemos al bebé a un SB un r B i Teoh sp por ciento. Cambiaremos el color a en este caso, un naranja claro y la línea a naranja oscuro. Necesitamos cambiar tu marcador, y nuestro fondo de etiqueta de nuestro marcador sería otra vez una naranja oscura, y editaremos ese marcador con una línea sólida naranja oscuro. Ahora puedes jugar un poco con alineación aquí, pero esa es tu idea básica. Por lo que hemos construido un tablero muy bonito, muy limpio que muestra todos estos datos diferentes para cualquier jugador que elijas y hace un muy buen trabajo llamando la atención sobre las métricas clave y cómo lo han probado a lo largo tiempo. Entonces esa es la primera parte de construir dashboards dinámicos y excel. 8. Crear un tablero dinámico parte 2: De acuerdo, así que bienvenidos a la segunda parte de nuestra demo de tablero interactivo. El objetivo de la segunda parte es esencialmente agregar algo más de interactividad al tablero que construimos en la primera parte. Por lo que mirando nuestro tablero original, verás que elegimos visualizar tres pares diferentes de métricas. Estamos mostrando golpes un promedio en murciélagos, un R B I, y base robada en tarifa base robada. Pero, ¿y si un usuario prefiere ver carreras y atrapado robando o triples y ponches? ¿ Y si quieren ver alguna combinación diferente de los peines que hemos incluido aquí? Para permitir que eso suceda? Necesitamos crear una versión dinámica de uno de estos gráficos de tal manera que un usuario pueda seleccionar dos métricas cualquiera y esas métricas se visualicen. Entonces, empecemos por solo reorganizar un poco las cosas. Voy a cambiar click en estas dos gráficas, lo encogeré un poquito, y sólo voy a reposicionar una por encima de la otra. Y entonces este 3er 1 es el que voy a convertir en mi gráfico dinámico algo arrastrar esto hacia fuera y hacer este tipo de gráfico destacado en el tablero Y lo primero es lo primero, tenemos que decirle al usuario que aquí pueden seleccionar métricas. Por lo que es una métrica selecta justo aquí en X 14. negrita cursiva. Dale una escritura una línea, y solo llenaré la celda justo al lado. Por qué 14 con el amarillo claro Phil tipo de para que coincida con esta celda de validación de datos aparecen nous a Y entonces mismo trato aquí en la columna A Una métrica selecta para poder usar el pequeño formato. Atajo de pintor. Ahí fue el mismo Phil. Y ahora vamos a convertir estos en celdas de validación de datos con listas desplegable, igual que nuestro nombre de jugador. Si recuerdan, puedo simplemente antihigiénicas estas dos columnas y mi lista de métricas vive justo aquí y las columnas A . H Entonces en, por qué 14 voy a entrar en validación de datos. Permitimos una lista y nuestra lista de fuentes como H dos a H 15. Ahí va y luego selecciona métrica a la misma lista de validación de datos de proceso y la misma matriz de origen. Por lo que ahora un usuario puede seleccionar de cualquiera de las métricas y elegir cualquier par. Entonces haz un poco de formatear ahí, solo para centrar y atrevido de ese pintor y cuando escondemos nuestra lista de fuentes. Ya no necesitamos eso. De acuerdo, entonces ahora puedes seleccionar dos métricas cualquiera y queremos que esas métricas se visualicen aquí en este gráfico en lugar de tener la lectura del gráfico desde una matriz fija de la tabla anterior. Hay un par de formas de hacer esto. Y la primera forma es tomar un enfoque similar al que tomamos con nuestro gráfico de áreas superpuestas animado , que es crear una nueva matriz de datos de origen dinámico abajo del gráfico. Y si vamos a hacer eso, podríamos simplemente escribir nuestros años y arrastrarlo hacia abajo y llenar un Siri y crear encabezados basados en las selecciones de métricas uno y dos. Y ahora lo que podríamos hacer es usar una combinación de funciones como índice y match toe, en realidad poblar los datos correctos aquí indexando esta matriz y haciendo coincidir con la correcta un año en la columna son y métrica en la fila cuatro. Eso se vería algo como esto equivale a índice. Aquí está toda nuestra matriz de datos de origen. Arréglalo con ese cuatro. El número de carretera al que queremos bajar. Aquí vamos a igualar el año. Arregla la X. Vamos a igualar eso dentro del año. Array de origen, que es nuestro cinco. Hay 10 cuatro cero solo significa que quería una coincidencia exacta y luego una coma sobre qué columna quiero sacar de mi matriz. Esa es otra función de coincidencia, Remini que coincide con el encabezado de métrica. Arreglé la carretera 32 porque mis cabeceras siempre viven en la Carretera 32. Voy a buscar eso dentro de la matriz de cabecera métrica, que es s cuatro a través de una f para fijo con F cuatro cero común, su coincidencia exacta cercana a paréntesis. Golpear, entrar. Y luego porque he establecido bien mis tipos de referencia, puedo aplicarlo todo el camino a ambas métricas y esencialmente Lo que eso hace es jalar en los datos de algún lugar aquí arriba basado en las dos métricas seleccionadas así en murciélagos a 42 6 11 que partidos hits 50 a 1 85 hits, 50 a 1 85 Como se puede ver, cambio que cayó y los datos y fuentes cambian también. Entonces esa es la opción uno. Básicamente podría arrastrar mi referencia de fuente hacia abajo a esta matriz dinámica que he construido . Y si bien terminaría no quiero tomar ese enfoque. Quiero mostrarles una forma un poco más elegante de hacer esto sin tener que construir este rayo de datos de fuente flotante aleatorio debajo de mi gráfico. No es que esto sea lo peor del mundo. Ya sabes, Podrías simplemente tirarle una fuente blanca y nadie sabrá que está ahí. Ya sabes, el peligro de hacer eso es que alguien inevitablemente agrega una fila o columna o no la ve y borra algo. Y boom, ahí va todo tu arduo trabajo. Entonces esa es una opción. La opción que prefiero que te voy a mostrar en este momento es usar la función de coincidencia, pero usarla en el contexto de una función de desplazamiento que vamos a construir en un rango con nombre . Y lo que eso hará es permitirnos sacar datos dinámicamente de la columna correcta su gráfico sin tener que construir otra matriz de datos de origen en algún lugar. Entonces lleguemos a ello. Entraremos en nuestra pestaña de fórmulas, definiremos un nuevo nombre, y vamos a llamar a esta métrica uno. Repetiremos el proceso para métrica a eventualmente y en lugar de una referencia fija aquí. Vamos a empezar escribiendo una función de desplazamiento y dentro de la función de desplazamiento o punto de partida estará la celda s cinco y luego el número de filas que queremos desplazar. Ese punto de partida es cero, porque nuestro primer punto de datos siempre debe ser el punto del día dos 2010 justo ahí en Fila cinco. Pero entonces, si me comas sobre el desplazamiento de columna, Aquí es donde necesitamos ponernos creativos. Porque si lo piensas, cuando un usuario selecciona una B en el desplegable y vende, ¿por qué 14? Entonces no queremos cambiar nuestro punto de partida en absoluto porque a los murciélagos están en la columna s. Si, sin embargo, usuario seleccionó H o golpea aquí en EAU 14 entonces sí queremos desplazar nuestra columna inicial 140.1 a la derecha para que nosotros llegar a nuestra columna de éxitos. Por lo que sabemos que esta pieza de la función necesita ser dinámica. Y para hacer eso, vamos a insertar una función de coincidencia y todo servirá. Aquí intentamos igualar el valor que ingresó el usuario. Por qué 14 dentro de la matriz de encabezados en la Fila cuatro y tenga en cuenta que necesita una matriz unidimensional para que la función de coincidencia funcione. Y la última pieza del partido es el tipo de partido, que es cero para el partido exacto. Cierra el paréntesis y ya casi terminamos. Excepto que tenemos que hacer un ajuste final, que es restar uno. Y si lo piensas, es porque si el usuario seleccionó un ser la función de coincidencia dice Ok, voy a encontrar una B dentro de esta matriz y fila. Para ahí está en la primera celda de esa matriz. Por lo que la función de coincidencia devuelve el número uno y el número uno en el contexto de una función de desplazamiento dice a excel. Vale, Ahora mueve nuestra columna inicial 10.1 a la derecha, lo que te llevaría a la columna hits, donde realmente queríamos los valores en esos murciélagos. Por lo que menos uno sólo corrige para esa coma al segundo, el último componente de la función de desplazamiento, que es elhiperfijo de nuestra matriz resultante. En este caso, queremos seis puntos de datos, uno por cada año. Por lo que la altura siempre será de seis y el ancho siempre será de tan solo una columna de ancho cerca. El paréntesis presiona OK, y tenemos la métrica uno definida. Hagamos lo mismo para encontrar un nombre para métrica a. Y ya deberías ser un profesional en esto, vamos a volver a usar la función de desplazamiento. Y en realidad, lo único que va a cambiar es con qué métrica estamos emparejando. En este caso, va a ser un B 14 en lugar de Y 14. Por lo que nuestros puntos de partida ahí, no queremos desplazar ningún cambio de columna Rose es una función de igualar el valor y un B 14 dentro de nuestra fila de encabezado coincidencia exacta. Cerrar un paréntesis menos uno. La altura es de seis. Con éste, ciérralo. Vale, Ahora, eso nos lleva a nuestro último paso, que es sólo editar los Siri dentro de esta tabla para leer de nuestros nuevos rangos llamados en lugar de estos rangos fijos aquí. Entonces es correcto. Haga clic en seleccionar los datos y aquí donde dice bases robadas. Voy a editar esto y por valores todo. Después del signo de exclamación, voy a reemplazar por métrica uno solo el nombre de nuestro primero llamado Ray. Y para nuestro 2do 1 borrar todo después del signo de exclamación. Llámalo en métrica para prensa. OK, ambos casos mi eje X permanecerá fijo como apenas estos seis años. Eso está bien. Yo puedo presionar. Está bien. Ahora, una cosa que podrías haber notado es que los nombres de los Siri no cambiaron sus aún llamadas bases robadas porcentaje base robadas. Pero en este caso, realidad estoy visualizando en murciélagos y corridas por lo que Excel no podrá cambiar dinámicamente las etiquetas de sus ejes de la manera tradicional. Entonces vamos a hacer un poco de trabajo y eliminar las etiquetas aquí y despejar un poco de espacio para nosotros mismos. Y me voy a poner un poco de fantasía aquí e insertar un cuadro de texto. Y puedo establecer este cuadro de texto para que solo iguale el valor en alguna celda. Entonces voy a poner este cuadro de texto para igualar lo que sea en la vida 14 y lo centraré. Ahí vamos. Y rotamos esos 90 grados, hacemos un poco de formateo para deshacernos del Phil y de la frontera. Voy a arrastrar eso aquí como un título de acceso improvisado y luego copiarlo y pegarlo de nuevo, arrastrando por aquí. Voltearlo y ponerlo. Este es igual a un B 14 que es la segunda métrica que el usuario ha seleccionado. Por lo que he creado especie de mis propias versiones pirateadas de etiquetas de acceso. Pero lo que eso hará es permitir que esos cambien a medida que el usuario cambie el desplegable. Entonces como puedes ver aquí, estoy mirando a los murciélagos y R B. Solo es para reflejar esta tabla inferior, y parece que coincide muy bien. Intentemos hacer lo mismo con los éxitos de la tabla superior y el promedio. Ahí vas a 71 todos los partidos arriba y ahí vas. Por lo que acabamos de agregar una nueva capa fresca de interactividad para hacer un usuario verdaderamente dinámico frente al dashboard en Excel. 9. Formateo dinámico basado en el valor: bien. Nuestras próximas demos sobre el formato dinámico basado en valores, que esencialmente está tratando de formatear datos individuales, apuntan dentro de un Siri's con reglas de formato únicas basadas en su valor. Entonces, para darte un ejemplo, veamos nuestra pestaña de formato basada en valores, y aquí acabamos de obtener dos columnas de datos mirando volumen de clic por día. Entonces si fuera a seleccionar y ser e insertar tu gráfico básico de columnas ahora, como puedes ver si hago clic derecho en el Siri y el formato, cualquier cambio que haga aquí como Phil, por ejemplo, se aplica a todo el Siri. Pero, ¿y si quiero destacar puntos de datos individuales o columnas en función de sus valores? Entonces digamos que quiero resaltar cualquier punto donde el volumen de click sea mayor a 8000. Por lo que este punto, este, éste y demás y demás se podría seleccionar individualmente puntos de datos y cambiar manualmente . Son Phil, pero obviamente esa no es una solución escalable, y no te permite cambiar o ajustar tus umbrales, automatizar ese proceso. Voy a deshacer eso. Cambia lo que esencialmente necesitamos hacer aquí. Se divide o segmenta nuestros Siri's originales en múltiples sub series que caen en los cubos que queremos formatear con reglas únicas. Entonces, por ejemplo, en este caso, si quisiera aplicar ese umbral de 8000 del que acabamos de hablar, podría crear dos columnas nuevas en C y D, donde una columna llena el volumen de clics para cualquier fechas donde las camarillas son mayores 8000 y la segunda columna llena clics para cualquier fecha o clics o menores a 8000 y luego actualiza mi gráfica para leer de esas dos columnas en lugar de mi columna original. Sea así podría formarse en mi columna mayor de 8000, una persona que llama y mi columna de menos de 8000, otro color. Entonces vamos a hacer algo parecido pero ponernos un poco más fantasiosos aquí. Y lo que quiero hacer en este caso es crear cuatro sub segmentos de datos basados en mis volúmenes de clics . Es decir, quiero calcular el cuartil 123 y cuatro y luego formatear esas fichas de corte como datos individuales . De Siri. Entonces para hacer eso en la celda C uno, voy a ingresar un nuevo encabezado de columna llamado rango por ciento, y aquí voy a usar excels por ciento función de rango donde mi matriz está llamando a B y mi valor o mi ex es la celda derecha a la izquierda. Entonces en este caso, beat también, y puedo cerrar ese formato como porcentaje y aplicarlo hacia abajo. Y esencialmente, lo que esto está haciendo es decirme dónde cada valor y ser Colin cae a lo largo de una escala del 0 al 100% . Por lo que el volumen de clics más grande en este caso 9972 equivale a un rango por ciento de 100 y el más pequeño, que en este caso es de 5029 equivale a cero. Y voy a usar estos valores de rango por ciento para dividir este Siri original en cuatro sub segmentos basados en fichas de corte. Por lo que mi encabezado para D uno será 10.25 e uno será 10.5, f será 0.75 y G será uno, y solo podría agarrar al pintor de formatos, darles a esos el mismo tipo de formatos de encabezado. Simplemente expande los dos decimales allí, y ahora necesitamos escribir algunas funciones que esencialmente agrupan los números de clics en una de estas cuatro columnas en función de su valor, Así que necesitaremos usar algunas sentencias condicionales y lógicas operadores para que eso suceda . Entonces iniciando la celda D a diremos, si el rango por ciento en la columna C es menor o igual al valor en mi encabezado aquí d uno, entonces quiero devolver el volumen de click. De lo contrario, quiero dejar esto en blanco. Y yo podría hacer eso sólo grabando dos comillas consecutivas. Y antes de bloquear esto, quiero hacer algunos ajustes de tipo de referencia. Entonces ve a que solo quiero bloquear la columna porque eventualmente quiero aplicar estas fórmulas columnas cruzadas y filas abajo. Entonces estoy tratando de ser muy consciente sobre qué tipo de tipos de referencia establecí. Y luego para D uno, que es mi cabecera Encabezados siempre viven en la Fila uno así que solo puedo arreglar la fila y dejar a los familiares D el cambio diácono mientras muevo columna para llamarlo y luego similar al mar a aquí . Voy a bloquear sólo la columna para la B porque quiero que esta fórmula sólo se levante a medida que me muevo hacia abajo. Así que golpeó. ¿ De acuerdo? Y ahora si aplico esto a la baja notarás que las únicas ventas valores poblacionales son las celdas donde el rango por ciento cae por debajo de 25. Entonces hay un 2024 15 etcétera. Entonces ahora un caso similar aquí, sólo necesitamos escribir una nueva función porque aquí necesitamos valores que sean mayores a 0.25 pero menores o iguales a 2.5. Es un tipo de caso similar aquí, pero vamos a usar si con una declaración condicional final. Y nuestra primera lógica es si el rango por ciento es mayor a 0.25 y nuestra segunda lógica es muy por ciento. El rango es menor o igual a 0.5, y entonces podemos cerrar. El primer valor de paréntesis, si es verdadero, será nuestro valor de click, que no es beat al valor de false estará en blanco. Y de nuevo, voy a pasar y editar mis tipos de referencia usando el atajo F four. Entonces para C dos, solo quiero arreglar la columna. ¿ Quieres? Solo quiero arreglar la fila otra vez, ver solo la columna e uno solo la fila y luego ser solo la columna y luego presionar enter y debería poder aplicar esto una y otra vez y tener todo poblado correctamente. Entonces ahora si todo esto funcionó correctamente, cada punto de datos en Colin sea nuestro original. El de Siri ahora ha sido buck. Se convertiría en una de estas cuatro columnas D E. F o G. Así que en conjunto tenemos cada punto de datos que teníamos un Siri original y la columna B. Sólo los tenemos rotos por valor, y eso nos permitirá crear lo que sea reglas de formato que elegimos en cada uno de estos Siri individualmente. Entonces sigamos adelante e insertemos un nuevo gráfico de columnas y le agreguemos algo serio. Por lo que los datos selectos en la primera serie de un Siri serán nuestro primer cuartil en un turno de control. Flecha abajo para agarrar todos mis valores ahí. Presionar OK, mi eje X. Ahí está en mis citas, que son un dos a través de un 61. Agrega un segundo Siri's para mis 0.5. Todo lo que cae en mi segundo cuartil. Ahí vamos otra vez. Mi eje X en estados. Voy a repetir esto dos veces más para mi tercer cuartil. Ahí vamos, un poco manual, pero a veces tienes que hacer lo que tienes que hacer. Y luego finalmente, estamos sumando nuestro cuarto y último cuartil con nuestros valores de la columna G, y esto debería simplemente envolver las cosas. Está bien, así que ahora arrastremos esto que conformamos a los datos Siri reduce la brecha con y reduce el tipo de réplica de superposición. Estilo de gráfico de columnas original. Y ahora, medida que lo seleccione, notará que puede seleccionar cada uno de estos Siri de forma individual, y luego se le cae desde su panel de formato. También puedes seleccionar cada una de las fuerzas que esto ahora lo divide en Siri individuales que conformamos en cualquier forma que elijamos. Entonces en este caso por 0.25 ¿por qué no le damos un Phil sólido con una especie de gris claro? Entonces por 0.5, hagamos un relleno sólido con un verde claro 4.75 Haremos una especie de verde más oscuro. Y luego finalmente, por ejemplo, vamos a darle el sueño más oscuro todos ellos, y ahora presionas OK. Como puedes ver, este Siri es exactamente lo mismo. Ya sea que seleccionamos el compuesto original de Siri en la columna B o combinación de los cuatro sub Siri que construimos, que, cuando se agregan, también reflejan Colin ser y se puede ver que son los mismos valores. Excepto que ahora tengo reglas individuales de formato basadas en estas cuatro teselas centrales de valores, y luego lo último que podemos hacer solo para que esto sea un poco divertido es en lugar de codificar duro , la columna de clics. Yo solo quiero mostrarte porque es Es una buena manera visual de probar que Lo que hemos hecho realmente es dinámico. Por lo tanto, reemplaza estos por un generador de números aleatorios. Alguien usa rand entre 5000 y 10,000. Podrías hacer cualquier valor aquí si quieres, y cuando hago eso y lo aplico ahora abajo, estos números aleatorios aire que aleatorizarán cada vez que calculo la hoja de trabajo. Entonces entro en fórmulas y acabo de golpear. Calcula ahora, una y otra vez. Y como puedes ver, no importa el valor que tome una fecha o punto de datos en particular, siempre se formateará correctamente. En base a esos brotes de cuartil. Es una forma realmente genial de agregar una forma única de formato dinámico a tus visuales de excel 10. Resaltado dinámico de datos: nuestra siguiente demo es manos abajo uno de mis favoritos. Se trata de resaltar dinámicamente un dato específico, de Siri. Entonces en la pestaña electoral dinámica y seria, verás algunos datos familiares. Tenemos nuestras ventas de boletos de película por mes para cuatro géneros diferentes. Comedia, thriller, documental y romance y lo que quiero hacer aquí es construir un gráfico de área apilada que caracterice o resalta dinámicamente cualquier género que el usuario haya seleccionado en esta celda de validación de datos aquí y batir a. Entonces para hacer eso, vamos a necesitar crear dos versiones de cada una de estas cuatro. de Siri para definir el formato cuando ese género no está seleccionado y Selby dos y el segundo para definir el formato cuando se selecciona ese género. Entonces, en otras palabras, cada uno de estos cuatro géneros tendrá una versión llena de luz y una versión con relleno oscuro, y sólo la versión con relleno oscuro poblará datos y se visualizará en el gráfico cuando ese género sea seleccionado aquí en el desplegable . Entonces para poner en marcha las cosas, voy a agarrar cada uno de nuestros géneros y simplemente pegar dos versiones de cada uno hacia abajo en una nueva matriz de datos fuente a continuación y lo que tenemos que hacer para poblar estos datos fuente se utiliza algunas declaraciones condicionales que devolverán datos de esta matriz anterior, pero sólo en los casos en que este género ha sido seleccionado en Selby a lo contrario. Quiero devolver valores en blanco así que voy a empezar con comedia igual si el desplegable Selby demasiado arreglado con eso para igual a mi valor en un 28 y voy a arreglar justo la columna ahí, si eso es cierto. Entonces, en otras palabras, si el usuario ha seleccionado la comedia, entonces quiero tirar en el valor de mi fila de comedia en mis datos fuente Fijar el camino aquí. De lo contrario, quiero que esto esté en blanco así que acostumbrado a comillas y cerrarlo. Y luego si arrastré esto hacia arriba, parece que está formateado como moneda por alguna razón. Entonces vamos a seguir adelante y, uh, cambiar eso para jugar número. Entonces ahí lo tienes. Debido a que se selecciona la comedia, toda esta fila de datos se ha poblado. Si el usuario seleccionado thriller ver que los datos desaparecen. Entonces ahora solo necesito una fórmula más para definir básicamente lo contrario. Es decir, una copia y pega. Este básicamente dice, si b al desplegable no es igual a 8 29 entonces tire de mis datos de venta de boletos desde arriba, lo contrario en blanco. Por lo que todos estos quedarán en blanco ahora mismo porque la comedia está seleccionada arriba. Pero de nuevo, si solo eso bajó a Thriller, por ejemplo, se ven los datos recién movidos de la fila seleccionada a la fila no seleccionada. Ahora sólo necesitamos repetir el proceso para los otros tres géneros. Voy a pedir prestadas las fórmulas que acaba de escribir para la comedia y las pegaré en cada uno de los otros tres casos y todo lo que necesito hacer por Thriller. Por ejemplo, en la Fila 30 podría presionar F dos para perforar en la fórmula. Todo lo que necesito hacer es arrastrar esa referencia hacia abajo a la línea de thriller en ambos casos, y ahí tienes. Puedo agarrar el thriller Rose, arrastrarlo todo el camino hasta diciembre. Y como puedes ver ahora, ese thriller está seleccionado y Selby, también. La fila superior de datos de thriller está poblada en la fila inferior devuelve nada tan similar Caso con documental arrastrarlo hacia abajo a las ventas documentales en ambos casos y luego el romance será el valor en B 26. Ahora puedo secar estos últimos cuatro todo el camino. Y ahí va, han creado un nuevo array de datos fuente duplicando los datos para cada género y creando una regla donde los datos sólo se poblarán si ese género en particular es seleccionado por el usuario y Selby, también. Entonces un ejemplo más. Si vas al romance, verás que se está poblando la versión de seleccionada de cada uno de los otros géneros. Pero la versión seleccionada, la fila superior para el romance, ahora está poblando. Por lo que este es el conjunto de datos que ahora usará para construir son Área apilada, y se construirá en base a los ocho de estos datos. Siri a pesar de que sólo cuatro serán visibles a la vez. Entonces sigamos adelante e insertemos nuestra zona apilada de opciones de línea, y podemos seleccionar los datos y simplemente deshacernos de lo que Excel intentó agregar. Y ahora puede sumar mis primeros Siri's. Y en este caso, voy a dar nombres personalizados sólo para que quede claro cuando estamos formateando líder. Entonces esto va a ser mi primer Siri, que es la primera fila de mis datos de comedia así que voy a llamarla comedia con un s para valores seleccionados Será B 28 a M 28. De acuerdo, Mis etiquetas horizontales serán de enero a diciembre. Algo difícil de ver, pero pasa por la M 22 y déjame simplemente arrastrar esto fuera del camino para que esto sea un poco más fácil. Volver a seleccionar datos. De acuerdo, entonces tengo mi comedia Selected Row. Second Row es comedia D para D. Seleccionados esos valores viven aquí mismo. Presione OK, valores horizontales. Cada vez que vamos a ser los mismos meses ahí mismo en la fila 22. Voy a repetir este proceso para cada thriller seleccionado de Siri es mi top thriller Row Horizontal Labels o Mis meses y Thriller De Selected. Es mi segunda fila de thriller Cuando mis valores ex son mis meses Podrías verlo agregó que agregar 1/3 1 Doc seleccionado top documental Row Doc De selected. Es mi segunda fila documental y se puede ver lentamente estamos construyendo cada rebanada de nuestro diagrama de área aquí Aquí está el romance seleccionado y este es el que actualmente tiene datos porque eso lo que nuestra celda de validación de datos está reflejando actualmente y por último, romance de seleccionado, que es la última fila de nuestro rayo justo aquí. De acuerdo, así que ahí lo tienes. Construimos fuera son zona apilada, como se puede ver en Lee cuatro. El aire de Siri se muestra porque sólo cuatro de las ocho filas en esta matriz de fuente dinámica y cruda contenían datos actualmente. Y de hecho, sólo cuatro lo harán a la vez. Así que ahora vamos a personalizar las opciones de relleno para cada uno de los ocho posibles Siri's puedes hacer clic con el botón derecho en formato de datos Siri's, y aquí es donde este desplegable es increíblemente útil porque enumera los ocho aquí. Y si intentara hacer click derecho en cada uno de los individuales de Siri, no hay forma de aislar y dar click en cualquiera de los cuatro que actualmente están en blanco. Entonces voy a usar este es mi panel de control aquí. Entonces vamos a bajar la lista de arriba a abajo, empezando por la comedia y ¿por qué sólo escogemos un color? Esa comedia representará si está seleccionada o de seleccionada, así que le daré un Phil sólido. ¿ Por qué no decimos que la comedia es tonos de azul, así que cuando se selecciona la comedia, quiero darle un color azul brillante y luego sólo vamos a ir por la línea. Cuando la comedia sea de seleccionada, démosle un tono más claro de azul. Hagamos realmente la sombra más clara que podamos. Iremos a thriller cuando Thriller seleccionado cuando haga thriller Orange y We 'll thrillers de seleccionados muy, muy ligero Sombra de Naranja, pasando al documental seleccionado 70 Amarillo y cuando documentales de seleccionados más tarde Sombra de Amarillo y luego llegamos al romance, Hagamos un romance verde, y cuando sea de seleccionado, será un verde más claro. No es una prensa, OK, y como se puede ver, ya que tres de los cuatro Siri que se están mostrando aquí no están seleccionados y Selby a nosotros vemos la versión llena de luz de esos Siris en el Onley Siri que mostrando su versión más oscura es romance, que es la seleccionada. Entonces, si hemos hecho nuestro trabajo correctamente, significa que a medida que el usuario cambia el valor en este desplegable y ser también, debería resaltar dinámicamente sólo esa. Siri y revertir todos los otros tres. Siri vuelve a su versión más ligera llena, y ahí va. Ahí está el thriller de comedia documental y el romance. ¿ Qué tan guay es eso? Entonces, por último, sólo podemos hacer un poco de formateo aquí. Vamos a darle un título de gráfico por encima del gráfico lo llamará ventas de boletos por meses, y luego en este punto, es realmente fácil simplemente copiar y pegar esto pasando por encima de las herramientas de gráfico tal vez quieran cambiarlo a columna 100% apilado. En cambio, ya sabes, formato de reconocimiento de que Siri encoge la brecha hacia abajo algo así y luego cambió el título a porcentaje de ventas de boletos por mes. Desde ahora, estamos mirando al 100% apilados y ambos actuarán exactamente igual. Entonces realmente solo una elección estilística. Pero ahí la tienes regla de formato personalizado realmente única que puedes aplicar a tus gráficos y gráficos para ayudar realmente a dar vida a las cosas. 11. Crear un gráfico de marcado personalizado: ya para una próxima demo, te voy a mostrar cómo construir un gráfico de ritmo personalizado realmente genial, realmente único. Si abres la pestaña del gráfico de ritmo personalizado en nuestro libro de Excel, verás aquí un montón de columnas de datos que esencialmente muestran que los ingresos van hacia un objetivo. Por lo que en la columna B, estos totales de ingresos diarios aéreos que están codificados como valores la columna C básicamente sólo traduce eso en ingresos acumulados. Día a día parece que tenemos un gol de 150 mil para golpear a fin de mes. brecha es solo El déficit entre el porcentaje acumulado y objetivo del mes es un uso realmente genial de las funciones de tiempo de Dayton para calcular cada día es un porcentaje del mes, y luego Target básicamente dice, dado lo lejos que estás a través de la mes y dado cuál es nuestro objetivo, ¿cuántos ingresos deberíamos haber impulsado cada día para mantener el ritmo para que lleguemos a esos 150 k a fin de mes? Entonces lo que tenemos que hacer ahora es crear un visual que realmente cuente esa historia y deje claro cómo estamos recorriendo actualmente en este caso para el mes de abril. Entonces, ¿por qué no simplemente empezamos insertando un gráfico de líneas y voy a agregar datos manualmente ? Siri a esto. El primer Siri's que me gustaría agregar aquí es mi objetivo, y mis valores serán todos los valores de la Fila dos hasta la 31. Ahí vas. Y mis etiquetas del eje X son todas con mis fechas y columna A. Añadiré un segundo. Siri es para objetivo. Y ya conoces el simulacro. G 23 g 31 x eje, Las mismas fechas, y luego todo lo que tenía uno más por ahora, que va a ser mi ingreso acumulado real. Sólo voy a seleccionar abajo a través de Rhode 21. Pero cuando sí selecciono mis etiquetas del eje X, traeré todo el array don a la fila 31 porque quiero básicamente, quiero que esta línea o el estado de Siri termine en la fecha actual. Pero no quiero limitar el eje X a esa fecha. Yo quiero ver el eje X extendiéndose a lo largo de todo el mes. Entonces aquí está nuestro punto de partida, y podemos hacer un poco de formateo en este momento y dar formato a estos datos de oro. Siri ya sabes, tal vez quieras una línea verde sólida con un Phil más ligero. Y tal vez use algunos de estos tipos compuestos donde se puede hacer una doble línea o algo así , obviamente totalmente a ti para la línea objetivo, el ritmo diario que estoy tratando de golpear. Hagamos de este tipo de gris claro dos puntos con, y esta es una oportunidad agradable. Usa aquí un tipo de guión porque ayuda a indicar que esa es una especie de directriz más que cualquier otra cosa. Y luego, finalmente, puedo editar mi línea de ingresos acumulados reales. Yo quiero que este destaque. Dado que esto es una especie de métrica clave aquí, hagamos una línea agradable, azul oscuro , suave, y podemos seguir adelante y agregar marcadores a eso también. Ya sabes, soy fan de estos diamantes. Golpearlo hasta la talla seis. Dale un Phil sólido con blanco y un borde sólido, azul oscuro. Ahí vamos. De acuerdo, así que hemos tramado tres de estos Siri y ya hemos hecho un trabajo bastante bueno mostrando cómo un ingreso acumulado se está acelerando a lo largo de las primeras semanas del mes y cómo eso se relaciona con el ritmo objetivo que necesitamos para hacer un seguimiento de alcanzar nuestra meta de 150 k para finales de mes. Por lo que mucha gente se detendría aquí porque es un visual bastante sólido. Pero este es el siguiente ejercicio de nivel. Entonces obviamente tenemos que llevar las cosas un paso más allá, y lo que quiero hacer aquí es resaltar estas brechas. Entonces cuando los ingresos acumulados caigan por debajo del objetivo, quiero que este espacio entre las dos líneas sea formateado rojo oscuro y si los ingresos exceden el objetivo, quiero que este espacio se llene de verde, al igual que un adicional indicador visual de que las cosas pasan el aire en una dirección negativa o positiva. Lo primero que tenemos que hacer es realmente definir esas áreas en un sentido matemático. Y eso es lo que pretenden hacer estas cuatro columnas de marcador de posición. Vamos a arrastrar eso fuera del camino. Entonces para más baja esencialmente, lo que busco aquí es en cualquier fecha dada. ¿ Cuál es el punto más bajo al que le pegas? Es o un ingreso acumulado o la línea objetivo. Tiene que ser uno de los dos, así que todo lo que necesitamos hacer es tipo de función masculina o mínima, va a ser el mínimo de ingresos acumulados Columna C o Target y Colin G. Upper es solo el lado de la vuelta. Entonces, en cualquier fecha dada, ¿cuál es el valor más alto? ¿ Ese es el objetivo o son los ingresos acumulados? Entonces solo escribe una función max. Misma idea exacta aquí ahora, abajo y otra vez. Aquí es donde realmente vamos a calcular la diferencia entre estas dos líneas, dependiendo de cuáles más altas así están bajo Target. Columna es donde queremos poblar valores que reflejen la diferencia entre las dos líneas en los casos en que un ingreso acumulado está por debajo de la línea de destino Sobre objetivo es donde queremos calcular el espacio entre las líneas donde se termina el ingreso o superando la línea objetivo. Entonces y vender J para debilitar tipo igual. Si son acumulativos. Los ingresos están por debajo del objetivo en el valor que queremos devolver es la diferencia entre las líneas, que es sólo el límite superior, menos el valor límite inferior. Es falso es sólo un blanco, por lo que a las comillas y luego caso muy similar para sobre objetivo igual si nuestros ingresos son mayores o iguales al objetivo entonces nuevamente devuelven esa diferencia, que es superior menos menor, lo contrario en blanco. Entonces ahora si agarro los cuatro de esos campos, acabamos de calcular doble clic. Verás que los valores se poblan en la columna J, donde los ingresos acumulados caen por debajo del objetivo y los valores se poblan en la columna K En cualquier caso donde los ingresos acumulados superen al objetivo y estos son los dos Siri's que vamos a construir en nuestro gráfico que se puede formatear con esos rellenos verdes y rojos. Entonces, empecemos a sumar algo serio aquí. Voy a seleccionar un nuevo Siri's. El 1er 1 que voy a sumar es menor y ya verás por qué en tan solo un minuto valores para menor voy a ser de dos a 31 fechas del eje X. De acuerdo, ahora voy a sumar bajo objetivo arrastrado hacia abajo los valores Onley a través de la fecha en que tenemos datos para prensa OK, nuevo. Mismo caso. Quiero que mis etiquetas del eje X se extiendan durante todo el mes, a pesar de que no tengo datos para todos esos días. último pero no menos importante sobre Target arrastrado hacia abajo a la carretera 21 y luego agarraremos todas nuestras fechas en la columna A Y ahí vas para que las cosas no se vean del todo bien todavía. Y eso está bien, porque tenemos que hacer un poco de trabajo para convertir esto en lo que necesitamos. Entonces voy a ir en un gráfico. Herramientas cambian el tipo de gráfico yendo a combo. Entonces probablemente estés pensando, Oh, Oh, Dios mío, ¿Qué ha salido mal aquí? Esto es Esto es sólo un caos absoluto, pero somos más inteligentes que excel. Entonces vamos a decirle exactamente a Excel lo que estamos tratando de hacer aquí uno por uno. Vamos a llegar a nuestros Siri y decidir qué necesita ser visualizado. Al igual que nuestro objetivo. Verá, se cambió a una columna Agrupada. Sólo queremos que esa sea una línea de nuevo. Nuestro objetivo. Lo mismo, sólo una línea y nuestros ingresos acumulados. Hagamos una línea con marcadores. Esos fueron los tres que originalmente pusimos de nuevo a su forma original. Y ahora estos tres, los nuevos que agregamos más abajo, bajo y más. Todas estas van a ser áreas apiladas. Entonces haz lo mismo para los tres todos en el mismo eje y pulsa OK, y ahora estás viendo que nos estamos acercando un poco más aquí y de hecho, todo lo que tenemos que hacer. Es editar algunas de estas áreas de Siri, y deberíamos ser doradas. Entonces, ante todo, este es nuestro más bajo, que básicamente nos da el área hasta el punto mínimo, ya sea de ingresos o de objetivo. No hay necesidad de hacer es darle ni Phil y ni frontera, lo que realmente sólo nos deja con el bajo y sobre que hay que ajustar. Entonces aquí está el bajo, que podemos llenar con un rojo y algo de transparencia. Si quieres, se ve bien, y luego puedo seleccionar sobre Target, y le daremos a ese bonito verde oscuro con una transparencia similar. Entonces ahora cuando extendemos esto, tenemos estas áreas sombreadas personalizadas por encima y por debajo de nuestra línea de objetivo. En este punto, realidad es solo una función de hacer cualquier formato que quieras hacer. A lo mejor cambiamos las unidades a cada dos días, espaciamos un poco las cosas, probablemente útil para agregar una leyenda aquí también. Entonces pongamos una leyenda al fondo y espaciemos esto. No necesitamos una leyenda para causa inferior. En realidad es simplemente invisible. De todos modos, yo no lo pongo en negrita, ya sabes, y por supuesto, puedes agregar etiquetas de datos si quieres. A lo mejor solo quieres agregar una etiqueta de datos que conozcas a tu valor de ingresos de fecha actual. Y sabes que puedes darle un bonito Phil azul oscuro con fuente blanca audaz. Eso es una especie de efecto agradable, y puedes entrar en las opciones pero por encima de tu punto de datos. Y realmente, a partir de aquí, solo depende de ti cómo quieres ajustar y personalizar esto. Pero ahí lo tienes. Muy único, una forma efectiva de visualizar algo como ir contra un gol. 12. Diseño de un cuadro de calibre personalizado: Esta siguiente demo es divertida, y es un buen ejemplo de cómo combinar dos tipos de gráficos muy diferentes juntos para crear algo totalmente nuevo y único. Entonces en este caso, realidad vamos a estar combinando un gráfico de rosquillas con una gráfica de dispersión de todas las cosas para crear un efecto de calibre y aguja. Por lo que ese tipo de visualización de gráficos de gage puede ser una forma realmente agradable, realmente sencilla de mostrar dónde cae el valor dado a lo largo de una cierta escala. Por lo que en la pestaña de gráfico de indicadores podrías ver haber puesto algunos objetos en su lugar para empezar a crear un poco de plantilla. Las formas del's simplemente crearán casi como un efecto de fondo para gauge on. Estos cuadros de texto solo se alinearán con diferentes puntos a lo largo del borde del medidor. Entonces tengo unos pocos colocar todas sus columnas aquí, tengo valor gráfico y vendo A a esto. ¿ El usuario ingresó valor que estarás trazando en los gráficos? De verdad, Sólo un valor que se va a mostrar, y luego un valor máximo y Selby a En este caso, estamos mirando una escala de 0 a 10 podría ser de 0 a 100 año 2000 realmente podría ser cualquier cosa. Entonces lo primero que tenemos que hacer es crear un gráfico de rosquillas, que servirá como fondo de nuestro medidor. Y en este caso, quiero un donut con cinco segmentos a lo largo de la mitad superior del círculo. Entonces para hacer eso, voy a escribir 10% copiar y pegar ese total de cinco veces y luego en la celda C siete tipo 50%. El motivo por el que estoy haciendo eso es porque necesito que estos sumen hasta el 100% a pesar de que no voy a estar usando el fragmento del 50%. Lo que básicamente hará es simplemente formatear esta rebanada 50% sin, dijo Phil, se vuelve invisible. Y entonces tengo cinco segmentos de igual tamaño a lo largo de la mitad superior de mi semicírculo. Entonces tomemos estos valores de C dos a C siete e insertemos donut. No necesito un título de gráfico aquí o una leyenda, y todo lo que hacer es seleccionar esa pieza 50% formatear el punto de datos y darle no Phil ni línea. Y luego para el gráfico mismo uniforme en el área del gráfico. No, Phil, sin línea. Y eso es bueno por ahora. Vamos a hacer obviamente algunos ajustes de formato adicionales con colores y alineaciones momentáneamente. Pero la siguiente pieza que quiero hacer es empezar a preparar mis datos para los valores de la gráfica de dispersión que estarán sobre colocados encima de este gráfico de rosquillas. Es básicamente la idea aquí es crear un gráfico combinado que traza que rosquillas así como valores X y por qué los valores para alimentar mi diagrama de dispersión. Por lo que el Paso uno para generar esos valores es calcular el valor actual en términos de grados . Eso suena complicado, pero realmente no lo es. Primeras cosas. En primer lugar, pensamos en el valor actual como una porción o porcentaje del máximo. En este caso, Valor cinco devolvería 50.5 o 50%. Todo lo que necesitamos hacer es multiplicar eso por los 180 grados totales en nuestro semicírculo para traducirlo en grados en lugar de un porcentaje. Entonces ahí lo tienes. Valor de cinco de cada 10 significa que estaríamos buscando una línea vertical o ángulo recto de 90 grados ahora para averiguar los valores reales de X e Y que necesitamos trazar a lo largo de una gráfica de dispersión para definir la posición de la aguja necesitará desempolvar nuestra trigonometría libros y volver a lo básico. Encontré un sitio web que en realidad es muy útil para entender esto, Así que echemos un vistazo rápido. Entonces aquí estamos en www dot matemáticas es divertido dot com slash geometría slash unit circle dot html. Y esta es una gran visualización interactiva que muestra las relaciones entre ángulos, signos, co senos y tangentes y lo importante para ver años mientras me muevo por el arco de este círculo. El Línea Verde, que es la altura de este punto, o el valor Y, se define por la función sinusoidal o el seno del ángulo. La línea azul, que es el valor X de este punto, está definida por el co seno del ángulo. Entonces con eso en mente, sé que podemos usar las funciones de trigonometría excels, sign y co sign para definir el posicionamiento exacto de este punto, que, cuando lo piensas, servirá esencialmente como el punto final de mi aguja. Entonces démosle un tiro a esto en excel. Muy bien, entonces la aguja de mi medidor necesita dos puntos porque esos dos puntos estarán conectados por una línea que esencialmente crea la propia aguja. primer punto tiene que ser el origen o 00 y eso realmente refleja su punto aquí mismo. La base de la aguja que nunca se moverá. Siempre se arreglará a las 00 El segundo punto es el punto que necesito definir usando esas funciones de signo y signo co que acabo de mencionar. Entonces recuerda el por qué valores aire definido por signo para que solo pudiera escribir igual signo Y aquí está la captura. No puede simplemente hacer el signo de los grados porque Excel realmente está buscando este número en términos de resplandor. Pero por suerte, tienen una función para convertirlo simplemente escribiendo resplandor aquí. Por lo que firma resplandor D a ahí vas. Eso regresa a una, que en este caso, es exactamente lo que buscamos. Porque con un valor de cinco de cada 10 estaríamos buscando una aguja vertical con un valor Y de uno, que es el máximo para los valores X. Empecemos con sólo una función de signo co, así que co signo luminancia de cerrar con dos paréntesis, Y ahí vamos. Entonces ahora tenemos los puntos que necesitamos para una gráfica de dispersión, y simplemente se reduce a agregar eso como un nuevo Siri's para crear nuestro gráfico combinado, haga clic derecho en el donut Seleccionar datos. Agrega un nuevo Siri's que llamarán Needle y los valores de Siri. Por ahora, sólo voy a seleccionar una de estas ya sea la X o la Y. En este caso, seleccionaré los valores de mentira en F dos a F tres Prensa. OK, y como pueden ver, Excel no cumplió con sólo añadir un segundo anillo de mi donut. Entonces si vamos a graficar herramientas y cambiar el tipo de gráfico, estás en un combo y uno serio, que es mi donut seguirá siendo un donut, y mi aguja Siri es que, acabo de agregar, será un dispersar con líneas rectas y comprimir. De acuerdo, Y ahora que hemos definido nuestros segundos Siri como una trama de dispersión, podemos volver a entrar en datos selectos. Se pueden editar los datos de esa aguja, y ahora podemos introducir unos valores X e y adecuados. Entonces X valores van a ser e 233 ¿Y por qué va a estar tras el árbitro? Ahí vamos. Entonces ahora hemos hecho la parte difícil. Hemos conseguido los datos definidos de una manera que tiene sentido. Ahora necesitamos solo hacer algunos ajustes y ajustes a estos gráficos para que las cosas se alineen y se vean bien. Entonces primero lo primero en mi trama de dispersión, voy a hacer clic derecho y formar en el eje X. Quiero un mínimo de uno negativo y un máximo de un mismo trato con el por qué mínimo de uno negativo, un máximo de uno. Y en realidad puedo borrar el propio Eje porque no necesito los números. Puedo eliminar las líneas de cuadrícula, y me conformo en esta línea de todos modos, que elijo. Por lo que no necesito convertirlo en un negro sólido con un poco de efecto de sombra en él también . Ahí vamos ahora a nuestra rosquilla. Vamos a formar en el donut. Siri es lo que quiero hacer. Seo. Está girado de costado. Quiero voltearlo para que mis segmentos de igual tamaño estén encima para poder cambiar el ángulo de la primera rebanada todo el camino a 270 grados. Ahí vas. Aumenta un poco el tamaño del agujero de la donut. Eso se ve bien. Ahora arrastremos este gráfico combinado a la derecha en nuestra plantilla. Podría hacerlo un poco más grande solo para que encaje bien. Ahí vamos. Y por qué no coloreamos los segmentos para que los colores representen algo significativo . Por ejemplo, si este medidor refleja valores que progresan de bueno a malo, entonces tal vez quieras verde a la izquierda y rojo a la derecha, por lo que solo podrías darle un Phil sólido, tal vez un verde oscuro ahí, verde más claro para el segundo segmento, amarillo para el tercero naranja para el cuarto y finalmente un rojo oscuro para el quinto. Eso simplemente realmente ayuda a enfatizar visualmente esa progresión de lo bueno a lo malo o lo malo a lo bueno. Entonces vamos a probarlo y a ver si esta cosa está funcionando como esperamos que haga. Un valor de gráfico. 2.5 Bueno, eso se ve mal. Debería ser lo contrario. ¿ Y si hacemos valor de 10? Ves, eso es alinear cero, que parece un poco apagado. Si hacemos cero, está cambiando al máximo. Si volvemos a nuestra herramienta en línea, se puede ver que en este caso los ángulos van en aumento a medida que nos movemos en sentido contrario a las agujas del reloj a lo largo del arco de este círculo, mientras que en nuestro caso queremos que nuestro ángulo esté aumentando a medida que nos movemos en sentido horario a lo largo del del círculo. Entonces todo lo que necesitamos hacer es una solución muy fácil es simplemente invertir esta función costera agregando un negativo. Entonces estamos tomando el signo co negativo ahora, y ahí tienes. Por lo que ahora valora cinco hacia arriba y hacia abajo. Y a medida que aumentamos, nos movemos en sentido horario a lo largo de este calibre. Por lo que ojalá disfruté de que uno de los tutoriales de Excel más inusuales que probablemente veamos alguna vez. Pero creo que es un gran ejemplo de empujar realmente el concepto de gráfico combinado a un nivel completamente diferente. 13. Visualiza un porcentaje usando las funciones de Array: Entonces una de las herramientas más poderosas y excel que típicamente se malinterpreta es la teoría. Una función, y la razón por la que es tan mal entendida y tan misteriosa es que opera de una manera que es diferente a cualquier otra fórmula o función celular en Excel. Entonces por eso, podrían ser muy duros del dedo del pie aprender y muy duros de dominar. Pero una vez que te das cuenta de cómo funcionan realmente las funciones de Ray, pueden ser una herramienta increíblemente poderosa. Y les voy a mostrar un ejemplo de cómo eso también se puede utilizar en un contexto de construcción de visualizaciones de datos en excel. Por lo que en la pestaña Rejilla Porcentaje de matriz, tenemos un usuario para encontrar valor y estar a donde solo puedes escribir en cualquier porcentaje en particular . Y luego tenemos cuatro columnas con las funciones de rayos en Collins G a través de J. Ahora, lugar de escribir estas desde cero y realmente no hacerles justicia, me imaginé que las pondría aquí ahora para que puedas cavar tanto o como poco como eliges tipo de decodificar lo que las funciones del aire realmente haciendo. Pero en interés del tiempo y mantenerlo enfocado la visualización de datos. Simplemente les daré un rápido resumen de lo que estos cuatro realmente están tratando de lograr aquí. Por lo que la matriz, función y la columna G esencialmente solo utiliza funciones mod y fila para definir una matriz de 100 rosas de altura que se ve algo así. Básicamente, solo cuenta de 0 a 9 y luego repite el proceso de cero de nuevo. La segunda columna Función Array h utiliza fórmulas de enteros y de fila para producir la matriz que vemos aquí, que básicamente es contar grupos de 10. Entonces son 10 ceros, 10 unos 10 dos todo el camino hacia abajo a 10 nueves, y volvemos hacia arriba. Básicamente, lo que estas fórmulas y Collins, G y H están haciendo es que están creando pares de puntos que se pueden usar para poblar una cuadrícula de 10 por 10 utilizando el tipo de gráfico de diagrama de dispersión. Y con lo que terminas son 100 puntos individuales. Y así estas dos columnas esencialmente solo están creando una plantilla. Por eso los estamos llamando cajas vacías que podemos usar para visualizar cualquier porcentaje que se escriba y B dos y luego la segunda a las funciones de rayos. Básicamente, todo lo que están haciendo es que están usando este porcentaje para replicar los valores y columnas G y H hasta una fila en particular, por lo que en este caso han escrito 15%. Por lo que te darás cuenta de que las primeras 15 filas son solo duplicados de G y H. Y luego se pone a cero. Después de eso, podías ver si cambio esto a 12. Ahora sólo la 1ª 12 filas o duplicados lo cambian a solo la 1ª 2 Y lo que esto nos permitirá hacer es agregar un segundo Siri's a nuestra gráfica de dispersión, utilizando estas combinaciones de puntos para superponer nuestra cuadrícula con cajas llenas para indicar el porcentaje. Y Selby, también. Entonces vamos a darle una oportunidad a esta cosa. Voy a insertar una gráfica de dispersión, arrástrela aquí justo debajo de mi porcentaje Seleccionar datos a nombre de Siri. El primero, Siri está vacío y los valores X se debilitan. Simplemente selecciona G tres flecha de desplazamiento de control hacia abajo hasta G uno de dos. Y entonces los valores Y H tres turno de control bajan a H 102 It Ok, Ok. Haré un poco de formateo aquí. Sin título. No hay líneas de rejilla en realidad ni siquiera necesitan los propios ejes. Y si nos formamos en el área de parcela, no quiero ningún Phil ni ninguna frontera. Y por último pero no menos importante, editaré el propio Siri y en mis opciones de relleno y línea y entraré aquí a mis opciones de marcador . Usa marcadores construidos en con cuadrados y hagámoslos agradables y grandes. ¿ Qué tal 15 puntos cuadrados y por qué no llenamos esos marcadores con sólo un gris claro? Dado que esto es una especie de nuestra plantilla, esto refleja nuestras cajas vacías. No hagas frontera en esos, y ahí la tienes. Entonces a medida que tipo de cambio espaciando alrededor, esto se convierte en una cuadrícula 10 por 10 realmente limpia. Oops, Vamos a deshacernos de esa zona de gráfico de formato de frontera Sin frontera Aereo. Entonces tienes esta cuadrícula limpia 10 por 10 que está poblada con estas dos funciones de matriz aquí , y es posible que hayas notado en la pestaña de fórmulas. Si miras a tu gestor de nombres, tal vez hayas visto estas cuatro matrices con nombre, que corresponden con los rayos que acabo de atravesar en Collins G a través de J. Así que ya he construido estas para ti como rangos nombrados, lo que significa que tienes la opción. O crea tus datos de origen como acabo de hacer, o si prefieres puedes entrar en datos selectos y puedes editar los Siri's. Y en lugar de G 33 G uno o dos, podrías simplemente escribir array, subrayado X y eres sabio sería guión bajo matriz ¿por qué? Y eso hace exactamente lo mismo, sólo dos formas de lograr el mismo objetivo. Y ahora todo lo que queda por hacer es en un segundo Siri, y vamos a llamar a esto completo y los valores X serán la matriz producida en las celdas. Yo tres ahí quiero. O podría escribir después de la matriz de punto de exclamación X X, y el mismo trato aquí. Podría seleccionar todo este rango de J tres a J 102 o simplemente escribir array. ¿ Por qué, por qué? Y entrar y presionar. OK, Y veo estos dos pequeños puntitos porque sólo he seleccionado un porcentaje de a sólo dos de estas cajas se van a llenar. Entonces tal vez lo subamos a 25. Por lo que es un poco más legible. Y ahora nos conformamos en ese segundo dato de Siri y esto depende de ti. Podrías mantenerlos como puntos o diamantes o triángulos o lo que quieras. En este caso, voy a hacer, construidas en marcadores, los mismos marcadores de tamaño cuadrado que mis cajas vacías. Tan tamaño 15 sólo para que se superponen realmente bien en la parte superior. Le voy a dar un Phil azul marino, y no quiero una frontera en esos. Y ahí tienes. Entonces tengo mis dos sobre puestas 10 por 10 rejillas con marcadores cuadrados construidos de una manera que en cualquier momento cambie este porcentaje, el número correcto de cajas se llenará para reflejar que aquí en la cuadrícula una manera realmente interesante de visualizar algo que es bastante simple pero que se puede visualizar de una manera que es mucho más fresco de lo que la mayoría de la gente tiende a mostrarlo. Entonces ese es tu rápido curso de crash 101 en el uso de funciones de array para construir una cuadrícula de porcentaje . 14. Conclusión: Bueno, chicos, su oficial. Has completado Excel Analytics, gráficos y gráficos avanzados de Excel. Entonces, ante todo, felicitaciones y saber cuánto se necesita realmente sentarse y aprender y practicar estas cosas y ampliar tu conjunto de habilidades. Tan enormes apoyos para ti. La buena noticia es que lo hiciste tu fecha de su estrella de rock. Entonces felicidades otra vez antes de que te deje ir. Sólo quiero golpear algún recurso rápido es y próximos pasos primero, Algunos auto promoción más desvergonzada. Si te interesa sumergirte en el lado de fórmula y función de las cosas, echa un vistazo a mi fórmula avanzada en curso de funciones. Sé que golpeamos bastantes fórmulas y funciones, sobre todo durante la sección demo de este curso en la que realmente no pasamos tanto tiempo enfocándonos como pudimos tener. lo que ese curso incluye una tonelada de contenido alrededor de seis horas, y contiene todo lo que posiblemente podrías querer aprender sobre fórmulas o funciones. Entonces estadísticas, operadores lógicos, condición, ALS, funciones de texto , fórmulas de array, buscar referencia y mucho más. En segundo lugar, hay algunos sitios y blogs a los que tiendo acudir por apoyo. Me imaginé que los compartiría con ustedes. En primer lugar el soporte dot office dot com es realmente útil cuando se trata de los gráficos básicos o fórmulas. También echa un vistazo a Office 3 65 Para cualquier persona que no tenga de nuevo la última versión de Excel, 3 65 es un gran servicio, asequible, asequible,basado en suscripción para asegurarse de que siempre tiene los últimos productos de oficina. Y juro que no soy portavoz de pago. Promete un segundo desbordamiento de pila. Realmente, sitio realmente excelente para soportes de formulario. Si tienes preguntas específicas o necesidades basadas en proyectos, Stack desborda un gran lugar. Y luego encontré este sitio de Google E 90 e 50 gráficos que tiene algunas cosas insanamente avanzadas que realmente se sumerge en cosas como VB, un recubrimiento y matemáticas y estadísticas súper avanzadas. Entonces si de verdad quieres empujarte y te metes en ese tipo de cosas, adelante y comprueba que estás bien ahí. Por último, las calificaciones y las revisiones son lo que mantiene vivos cursos como este. Por lo que los animo a que por favor compartan comentarios si lo tienen para bien, para mal. Y quiero decir que la retroalimentación positiva, por supuesto, siempre es muy apreciada. Pero la retroalimentación constructiva es genial para cualquier retroalimentación que ustedes puedan compartir para asegurarme estoy produciendo el mejor contenido que posiblemente pueda va mucho camino. Por lo que por favor no duden en llegar con eso. Muchísimas gracias. De verdad, de verdad te aprecio. Tomar el tiempo para pasar por el curso realmente significa mucho para mí. Y espero verlos en algunos de mis otros cursos también. Entonces gracias de nuevo y buenas noches.