Todavía conmemoración la primera vez que intenté construir un tablero en Excel. Miré las infinitas filas de datos, busqué fórmulas y luché con la colocación del dibujo, solo para terminar con una hoja desordenada que casi nada era aprovechable. Lo que no me di cuenta era que algunas técnicas simples podrían haberme ahorrado innumerables horas e hicieron que todo el proceso fuera mucho menos frustrante.

Si alguna vez ha estado en la misma situación, probablemente le resulte útil memorizar algunos trucos de productividad de Excel que pueden alterar la forma en que construye paneles. No necesita ser un entendido o conocer todas las fórmulas para usarlas de guisa efectiva. Con solo un poco de praxis, puede advenir de datos sin procesar a un tablero desinteresado e interactivo en mucho menos tiempo del que cabría esperar.

Tablas de pivote

Resumir miles de filas en segundos sin fórmulas

Las tablas dinámicas pueden sonar intimidantes, pero son una de las herramientas más poderosas de Excel para dar sentido a grandes conjuntos de datos. En circunstancia de escribir fórmulas complejas para calcular totales, promedios o recuentos, simplemente puede deslizar y soltar los campos en su circunstancia.

Por ejemplo, tengo un conjunto de datos que contiene 1,500 filas de transacciones de ventas en regiones, productos y meses. Ayer de conocer sobre las tablas de pivote, confiaría en la función SUMIFS en Excel para replicar preguntas como “¿Cuáles eran nuestros productos más vendidos en el noreste?” Eso significaba establecer fórmulas separadas para cada producto, un proceso tedioso con solo un puñado de artículos y casi inverosímil con cientos. Con una mesa de pivote, puede obtener la misma respuesta en menos de un minuto.

Para construir uno, comience seleccionando su rango de datos. Entonces ve a Insertar > Mesas > Fundamable > De la mesa/rango. Cuando Excel pregunta dónde colocarlo, generalmente elijo un Nueva hoja de trabajo. En la nueva hoja, verá los encabezados de su columna en un panel a la derecha. Desde allí, simplemente arrástrelos al Hilera, Columnas, Títuloso Filtros áreas dependiendo de cómo desee analizar sus datos. Para encontrar el producto más vendido en el noreste, por ejemplo, tiro la región a las columnas, el producto a las filas y los ingresos a los títulos. ¡Ahí está tu respuesta!

Lo sorprendente de las tablas de pivote es que se actualizan automáticamente cuando cambia los datos de su origen. En circunstancia de recalcular o reescribir fórmulas, simplemente puede poner al día la tabla de pivote y siempre tener las últimas ideas a su trascendencia.

Gráficos de pivote

Visualice los datos de su tabla de pivote con un solo clic

Una vez que haya construido una mesa de pivote, convertirla en un dibujo es casi demasiado obvio. Simplemente haga clic en cualquier circunstancia interiormente de la mesa, vaya a Insertar > Pivotcharty seleccione su tipo de dibujo preferido. Incluso obtendrá una recomendación sobre el mejor cuadro para sus datos.

A posteriori de preferir uno, Excel crea instantáneamente un dibujo dinámico que se actualiza automáticamente cada vez que cambia la tabla de pivote. Encontrarás un icono de plus y cepillo al banda de la tabla. Haga clic en ellos y podrá ajustar los ejes, etiquetas, estilo, etc., para que coincida con su tablero.

Esta es una gran superioridad sobre los gráficos regulares, que requieren que seleccione rangos manualmente y comience de nuevo si la estructura de sus datos cambia. Con gráficos de pivote, todo se adapta para ti. En circunstancia de distraer gráficos posteriormente de hacer cambios en su conjunto de datos llamativo, simplemente actualice la tabla de pivote (haga clic derecho> Refrescar), y el dibujo se actualizaría instantáneamente con los últimos números.

Hojas de ayuda

Mantenga datos sin procesar y cálculos complejos fuera de la pinta del tablero principal

Cuando comencé a construir paneles, abarroté todo (datos de tiro, fórmulas, gráficos y formateo, en una sola hoja. El resultado fue lioso, confuso y casi inverosímil de solucionar los problemas cuando poco salió mal.

Ahí es donde entran las hojas de ayuda. Al separar el trabajo de fondo del tablero pulido, mantienes todo más organizado y mucho más obvio de ayudar. Las hojas de ayuda son diferentes de las columnas de ayuda, que son columnas adicionales que agrega interiormente de un conjunto de datos para favorecer las fórmulas.

Una configuración que funciona adecuadamente para mí es tener una hoja para datos sin procesar, otra para los cálculos y una hoja final para el tablero en sí, donde solo son visibles los gráficos, las métricas esencia y las cortes. Aquí hay un ejemplo concreto utilizando datos de ventas:

Comience con sus datos sin procesar

Primero, convertí mi rango de datos de 1,500 filas en una tabla de Excel (resaltado> Control+ +T). Luego, en la hoja de cálculos, exporté la columna de datos por columna con referencias como esta:

=Table1(Date)

Repetí esto para cada columna, agregando manualmente los encabezados ya que no se transfieren.

Añadir columnas auxiliares

Para favorecer las tablas de pivote, inserté columnas que dividieron las fechas en unidades de tiempo enseres:

=YEAR(A2:A1500)
=TEXT(A2:A1500,"yyyy-mm")
=ROUNDUP(MONTH(A2:A1500)/3, 0)

Creé otra mesa (Tabla2) Con objetivos de ingresos mensuales por región. Luego, en la hoja de cálculo, extraí los objetivos en mi conjunto de datos y calculé la varianza con los ingresos utilizando estas fórmulas:

=VLOOKUP(B2:B1500,Table2,2,FALSE)
=I2:I1500-E2:E1500

En este punto, tenía cinco nuevas columnas: año, mes, trimestre, objetivo y varianza.

Con estos cálculos en su circunstancia, puedo construir tablas y gráficos de pivote en una nueva hoja de tablero sin llenar los datos o fórmulas sin procesar. Por lo universal, configuraría las hojas de ayuda antiguamente de crear sus tablas de pivote, pero no siempre las necesitará. Depende de cuán complejos son sus datos.

Cuando comparto un ejemplar de trabajo, solo apunto a las personas a la hoja del tablero, mientras que todo el trabajo de preparación lioso se mantiene fuera de la pinta. Para hacer que las hojas de ayuda aún más efectivas, use tablas de Excel o rangos con nombre, para que sus fórmulas permanezcan estables a medida que sus datos crecen.

Cortadores y filtros

Centrarse rápidamente en registros relevantes

Las cortadoras son ordenanza de filtrado visual que le permiten explorar sus datos sin escribir una sola fórmula. Son especialmente poderosos cuando están vinculados a tablas de pivote y gráficos de pivote, porque puede filtrar y comparar los resultados al instante. Dicho esto, incluso trabajan con mesas de Excel regulares.

Para amplificar uno, haga clic en su tabla o mesa de pivote, vaya a Insertar > Cortadoray seleccione los campos que desea filtrar por (región, época, etc.). Excel creará un panel de ordenanza que actúan como filtros. Si desea ver solo los resultados del primer trimestre para la región oeste, simplemente haga clic en los ordenanza de la región y los cuartos. Para los datos basados ​​en el tiempo, incluso puede insertar una cortadora de secante de tiempo (Insertar > Dirección de tiempo) para moverse entre abriles, días, cuartos o meses con un solo clic.

Dicho esto, los filtros tradicionales todavía juegan un papel importante. Para un estudio rápido y temporal en tablas regulares, puede usar Datos > Filtrar Para amplificar desplegables a cada encabezado de columna. No son tan pulidos como las cortadoras, pero son simples, rápidas e invaluables cuando solo necesita compendiar un conjunto de datos sin construir una mesa de pivote completa.

Formato condicional

Resaltar datos con alertas visuales

Los números solos pueden ser aburridos. El formato condicional agrega color, iconos y barras de datos para que los patrones se destaquen al instante.

Por ejemplo, puede convertir una tabla simple en un plano de calor en segundos. Seleccione su tonalidad de celdas, vaya a Hogar > Formato condicional > Escalera de colory elige un gradiente. Los títulos altos pueden aparecer en títulos verdes, bajos en rojo, y todo lo demás en tonos que muestran exactamente dónde cae. En circunstancia de escanear a través de filas de números, sus fanales van directamente a los puntos calientes.

Además utilizo conjuntos de iconos para el seguimiento de estado: marcas de comprobación de vía para los objetivos MET, puntos de interjección amarilla para advertencias y X rojo para áreas problemáticas. Las barras de datos son otro preferido: actúan como pequeños gráficos de barras integradas en las celdas, lo que facilita la comparación de títulos de un vistazo.

Sin retención, es importante usar estas características con moderación. Demasiado formato crea ruido visual en circunstancia de claridad. Lo reservo para las métricas o excepciones más importantes, por lo que la información correcta aparece cuando importa.

No es tan difícil crear un tablero de tablero potente e interactivo en Excel

Mirando alrededor de antes, desearía activo sabido sobre estas cinco características mucho antiguamente. Me habrían ahorrado innumerables horas y me habrían evitado advertir que estaba peleando con Excel en circunstancia de usarlo como la poderosa aparejo que es.

No necesitas dominar todo a la vez. Comience con tablas de pivote para obtener el control de sus datos, luego agregue un dibujo de pivote para darle vida visualmente. Incluso con solo esos dos, estarás muy por delante de donde la mayoría de los principiantes se estancan. A medida que se vuelve más cómodo, cubra las hojas de ayuda, las cortinas y el formato condicional. En poco tiempo, tendrá paneles que se actualizarán en segundos, son fáciles de ayudar y facilitan sus datos.