Fuera de vacaciones? ¿Corriendo una maratón? ¿Construyendo el depósito de la casa de sus sueños? Mantenga un registro de su objetivo financiero 10 Plantillas de hoja de cálculo increíblemente útiles para organizar su vida 10 Plantillas de hoja de cálculo increíblemente útiles para organizar su vida ¿Es su vida un crisol de fechas incumplidas, compras olvidadas y compromisos incumplidos? Parece que tienes que organizarse. Lea más con una tabla de termómetros de Excel. Es una manera simple y efectiva de rastrear una sola variable, que puede compartir con su equipo. Visualice sus datos y haga que sus hojas de cálculo sean fáciles de usar con un tablero de Excel Visualice sus datos y haga que sus hojas de cálculo sean fáciles de usar con un panel de Excel A veces, un formato de hoja de cálculo simple no es lo suficientemente atractivo como para hacer que sus datos sean accesibles. Un tablero de instrumentos le permite presentar sus datos más importantes en un formato fácil de digerir. Lea más, compañero, amigos o simplemente manténgase alejado de usted. Deje que su propio progreso lo inspire a hacer más!
Construiremos en Excel 2013, pero debería poder seguir con las versiones anteriores sin problemas. Este tutorial explicará cómo hacer un gráfico de termómetro básico con un porcentaje general y un total general de la meta establecida. Luego veremos cómo usar las funciones SUMA y SUMARICIOS. Encuentre todo en su hoja de cálculo de Excel con funciones de búsqueda. Encuentre todo en su hoja de cálculo de Excel con funciones de búsqueda. En una hoja de cálculo gigante de Excel, CTRL + F solo lo llevará hasta el momento. Sé inteligente y deja que las fórmulas hagan el trabajo duro. Las fórmulas de búsqueda ahorran tiempo y son fáciles de aplicar. Lea más para seguir nuestro progreso durante un período específico.
Configuración de la hoja de cálculo
Antes de construir nuestro termómetro, debemos establecer nuestro objetivo. En este caso, estoy recolectando fondos de la oficina virtual MakeUseOf para nuestro tan esperado viaje de creación de equipos a Andorra, pero puede usar sus propios objetivos y marcadores financieros.
Abra Excel 2013 y abra una nueva hoja de trabajo. Para esta parte del tutorial, solo utilizaremos dos columnas: una para los meses y otra para la cantidad depositada. El mío se ve así:
Notará el objetivo, el total y las celdas de porcentaje debajo de nuestra tabla. Aquí es donde crearemos las fórmulas para nuestro termómetro.
En la celda B20 necesitarás escribir = suma (B5: B16), usando el equivalente para la posición de tu mesa. Esta fórmula nos da el total de los números en la columna B. Una vez que haya establecido su total actual, podemos calcular su progreso como un porcentaje usando otra fórmula muy básica.
En la celda B21 voy a escribir = B20 / B19 y presionar enter. Le dará una cantidad decimal. Podemos cambiar esto fácilmente a nuestro formato deseado, porcentaje, haciendo clic con el botón derecho en la celda en cuestión y seleccionando Formato de celdas . Esto debería abrir inmediatamente el cuadro de contexto Formato de celdas en la pestaña de números. Cambie la categoría a porcentaje y presione OK . Su hoja de trabajo ahora debería verse similar a esto:
Configuración del termómetro
Ahora cuidamos la tabla, el total y el porcentaje, y configuramos nuestras fórmulas Tutorial de Mini Excel: Uso del recuento avanzado y la adición de funciones en Excel Tutorial de Mini Excel: Uso del recuento avanzado y la adición de funciones en Excel Contar y agregar fórmulas puede parecer mundano en comparación con las fórmulas de Excel más avanzadas. Pero pueden ayudarlo a ahorrar mucho tiempo cuando necesite recopilar información sobre los datos en su hoja de cálculo. Lea más, podemos enfocarnos en el termómetro.
Diríjase a Insertar> Columna> Columna 2D> Columna de clúster . Esto abrirá un cuadro vacío al lado de nuestra mesa. Agregue datos a la tabla Cómo usar una tabla dinámica de Excel para el análisis de datos Cómo usar una tabla dinámica de Excel para el análisis de datos La tabla dinámica es una de las herramientas más potentes en el repertorio de Excel 2013. Se utiliza con frecuencia para el análisis de datos de gran tamaño. Siga nuestra demostración paso a paso para aprender todo al respecto. Lea más usando Seleccionar datos . Seleccione la celda que contiene el porcentaje de su total. Para mí, esta es la celda B21. Presione OK para completar el cuadro, y llegará a esta pantalla:
Ahora podemos quitar el gráfico de nuevo. Haga clic con el botón derecho en el título del gráfico y elimínelo. Haga lo mismo para el título de la columna y las líneas horizontales. Haga doble clic en el eje y (porcentajes) para abrir el cuadro de diálogo. Desde aquí puede cambiar los límites mínimo y máximo del gráfico a 0.0 y 1.0, respectivamente. Mientras estás aquí, desplázate hacia abajo y selecciona Numbers . Cambie los lugares decimales a 0.
Haga clic con el botón derecho en la columna y seleccione Dar formato a la serie de datos . Ajuste el ancho de la brecha a 0. Esto asegurará que su columna llene el área del gráfico, en lugar de tratar de esconderse en la esquina. Ahora puede reducir la tabla a un tamaño más similar al de un termómetro. Finalmente, regrese a la pestaña Insertar, seleccione formas y encuentre un bonito óvalo. Dibuje un óvalo y agréguelo al pie de la tabla del termómetro, luego cambie el tamaño del área del gráfico. Debe quedar bien alrededor de la campana del termómetro, así:
¡Lo hicimos! Puede cambiar su termómetro a rojo haciendo clic con el botón derecho y alterando el color de relleno.
Ampliando su termómetro
Si está realizando un seguimiento de una gran cantidad de dinero durante un período prolongado de tiempo, puede ser útil mirar hacia atrás en qué días ha recaudado la mayor cantidad de efectivo. Esto puede ser especialmente útil para campañas de caridad: puede analizar lo que su equipo hizo de manera diferente en esas ocasiones y vincularlo a su próximo evento de recaudación de fondos.
Primero, vamos a modificar nuestra tabla de datos Mini Tutorial de Excel: Use la lógica booleana para procesar datos complejos Tutorial de Mini Excel: Use la lógica booleana para procesar datos complejos Operadores lógicos IF, NOT, AND y O, pueden ayudarlo a obtener de Excel novato para potenciar al usuario. Explicamos los conceptos básicos de cada función y demostramos cómo puede usarlos para obtener los máximos resultados. Lee mas . Necesitamos una tabla más detallada, incluidas las fechas. También incluiremos nombres, porque el seguimiento de nuestros donantes es divertido. No voy a detallar la transformación de una mesa pequeña a una mesa grande, pero debes terminar con una mesa similar a la de abajo. Te mostraré cómo obtener este formato de tabla ordenado en un momento.
Recuerde: necesita columnas separadas para la fecha y el monto recibido. De esta manera, podemos monitorear cada variable. También necesitaremos establecer un rango con nombre dinámico. Los rangos con nombre son útiles para darnos el poder de un conjunto de celdas sin tener que actualizar constantemente nuestras fórmulas. ¿Necesita ayuda con Excel Formulas? 7 Recursos para consultar ¿Necesita ayuda con Excel Formulas? 7 Recursos para consultar Excel es el estándar de oro de la hoja de cálculo. Si tiene que usar Excel y aún tiene que familiarizarse con él, estos recursos le mostrarán rápidamente lo básico y más. Lee mas . Podemos solicitar automáticamente nuestra fórmula para dar cuenta de cualquier adición a nuestra tabla.
Rango nominal dinámico
Para facilitarnos las cosas más adelante, convertí nuestra mesa básica en una oficial. Haga esto seleccionando el área completa de su tabla. Seleccione la pestaña Insertar, e inmediatamente debe ver la Tabla . Seleccione OK. Has creado una tabla de búsqueda con encabezados. ¡Los encabezados serán útiles más tarde!
¿Recuerda nuestras celdas objetivo, total y porcentaje de antes? Ahora podemos vincular nuestra tabla con nuestro total. En su celda total, entrada = SUMA (Tabla 1 [Importe]) . Esta fórmula le pide a la celda que sume la columna Cantidad. La información del Porcentaje aún se puede encontrar dividiendo el total entre el objetivo, y todavía está vinculado a nuestro termómetro.
Seleccione el contenido de su columna Cantidad . Para mí, eso es C 26: C 38. Seleccione la pestaña Fórmulas y localice el Administrador de nombres . Haga clic en Nuevo . Tenga en cuenta el cuadro Se refiere a . Si tenía la columna Cantidad seleccionada , debería aparecer la Tabla 1 [Importe] . Necesitamos agregar a esta fórmula:
DESPLAZAMIENTO (Hoja1! $ C $ 1, 0, 0, CONTACTO (Hoja1! $ C: $ C), 1)
Cada vez que agregue un valor a la columna Importe, su total aumentará automáticamente.
Su fórmula debería ser muy similar a esto:
= Table1 [Amount], OFFSET (Sheet1! $ C $ 1, 0, 0, COUNTA (Sheet1! $ C: $ C), 1)
Puede que tenga que adaptar la letra de la columna; aquí está C
Agregar fechas usando SUMIDEROS
SUMIFS es una fórmula poderosa que nos permite correlacionar información de dos o más fuentes. Vamos a usar SUMIDEROS para averiguar cuántas donaciones hemos recibido dentro de un período de 14 días, entre dos fechas especificadas. Así es como se verá el producto final:
Ingrese su fecha de inicio requerida. Para mí, esta es la celda B10. En la celda B11, escriba = B10 + 14 . Excel insertará automáticamente la fecha y la seguirá actualizando en función de la celda B10. Ajuste el período de tiempo alterando +14 al número que necesite.
La celda B12 contendrá nuestra fórmula SUMARIOS. En la celda, escribe:
= SUMIDEROS ($ C $ 26: $ C $ 95, $ A $ 26: $ A $ 95, "> =" & $ B $ 10, $ A $ 26: $ A $ 95, "<=" y $ B $ 11)
Whoah: son muchos números, ¿qué hacen? ¡Te mostrare!
- $ C $ 26: $ C $ 95: el rango de celdas que queremos incluir. Le pedí que incluyera la celda C95, en caso de que nuestras donaciones se extendieran tan lejos.
- $ A $ 26: $ A $ 95, "> =" y $ B $ 10 : le dice a SUMMS que verifique la columna A para cualquier fecha en o después
- $ A $ 26: $ A $ 95, "<=" y $ B $ 11 : le dice a SUMMS que verifique la columna A para cualquier fecha en o antes
La celda B12 ahora debe expresar el valor de las donaciones recibidas entre las fechas especificadas.
Redondeo
Hemos logrado algunas cosas buenas hoy. Usó fórmulas básicas, editó el área y el eje del gráfico, creó tablas, aprendió acerca de rangos con nombre dinámicos y tuvo una probada del increíble poder de los SUMIDEROS.
Sin embargo, como mi amigo a menudo dice, SUMIFS es solo SUMAPRODUCTO de un pobre hombre; pero esa es otra historia de Excel, para otro día.
Seguimiento de su causa caritativa? Espero que esto haya ayudado? ¿Qué fórmula o función de Excel le gustaría que MakeUseOf explique por usted? Háganos saber a continuación!
Créditos de las imágenes: termómetro a través de Shutterstock