Blog

Reemplazar formatos

Cuando tenemos una serie de datos como los que tenemos en pantalla y queremos reemplazar los formatos de algunos datos tenemos varias formas de afrontar esta situación.

Imaginemos que hemos señalado de amarillo, con letras verde a los trabajadores próximos a la jubilación. Queremos modificar el formato de todos ellos por otro, como el que aparece en la fila 6, con fondo naranja y letra roja.

Copiar formato

Una forma que podríamos pensar en un primer momento es utilizando la opción de Copiar formato.

Señalamos la celda con el formato que queremos copiar y le damos al icono de Copiar formato. Después señalamos una celda a la que queremos aplicar ese formato y veremos que cambia el formato, pero se desactiva la opción de copiar formato.

Para mantener la opción de copiar formato, y que no se desactive, tendríamos que hacer doble clic en la opción Copiar formato.

De esa forma podemos marcar una a una todas las celdas cuyo formato deseamos cambiar.

Esto presenta el problema de que tenemos que marcar las celdas una a una, lo que puede ser inviable en casos como el nuestro donde hay cientos de registros.

Debemos buscar la forma de modificar el formato de todas las celdas de una sola vez.

Reemplazar formatos

Para ello utilizaremos la opción Reemplazar formatos, que se encuentra dentro de la ficha Inicio, Buscar y seleccionar y Reemplazar.

En la ventana de Buscar y Reemplazar nos situamos en la pestaña Reemplazar, y a la derecha del todo tenemos dos botones de Formato…

El primero es para indicar el formato que deseamos que busque y el segundo el formato por el que queremos que lo reemplace.

Si desplegamos el botón Formato… se despliegan dos opciones: Formato… y Elegir formato de celda…

Si elegimos Formato… se abre la ventana de Formato para que le indique exactamente el formato que deseamos que busque.

Si tenemos alguna celda ya con el formato que queremos que busque es mejor la segunda opción: Elegir formato de celda…

Una vez seleccionada solamente debemos marcar una celda con el formato que deseamos que busque y se rellena la previsualización del formato.

De igual forma lo hacemos con el formato con el que queremos que se reemplace.

Ahora tenemos la opción de Reemplazar los datos uno a uno (opción Reemplazar) o todos a la vez (con la opción Reemplazar todos).

Para ver en detalle cómo reemplazar formatos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Activar Solver

El complemento Solver es un programa de Microsoft Office Excel que está disponible cuando instala Microsoft Office o Excel.

Sin embargo, para usar el complemento Solver, primero debemos hacer es cargarlo o activarlo en Excel.

A continuación vamos a ver un gráfico que incluye funcionalidades en ese sentido.

Pasos para activar Solver

Para cargar o activar Solver lo que tenemos que hacer es lo siguiente.

1.- Vamos a la pestaña Archivo y dentro de la misma a Opciones.

2.- En la ventana de Opciones de Excel, pulsamos en la opción Complementos.

3.- En la parte de abajo, aparece Administrar: Pulsamos sobre el botón Ir…

4.- Se abre la ventana de Complementos. Marcamos la casilla correspondiente a Solver y le damos a Aceptar.

De esta forma se habrá cargado el complemento y veremos que ya tenemos disponible esta herramienta.

Para ver en detalle cómo diseñar este tipo de gráfico interactivo lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Gráfico de barras interactivo para Dashboard 📊

Crear gráficos interactivos es una forma muy útil y dinámica de mostrar los datos en Excel. Utilizando las segmentaciones de datos y controles de formulario podemos darle una dinamismo a nuestros gráficos que nos facilitan mucho el análisis de los resultados.

A continuación vamos a ver un gráfico que incluye funcionalidades en ese sentido.

En este gráfico horizontal podemos ver las mayores ventas de distintos países.

Los datos de origen de los que parte tienen una forma tabular, como la siguiente.

Las funcionalidades incluidas en este gráfico y cuya implementación se explica en el vídeo son las siguientes.

Segmentación de datos

Para poder elegir el país hemos tenido que transformar los datos en una tabla y crear una segmentación de datos para el campo país.

De esta forma podremos seleccionar el país del cual deseemos mostrar los datos en el gráfico horizontal.

Control de número (Control de formulario)

Incluimos un control de número para indicarle el número de ciudades del país seleccionado que queremos resaltar como las ciudades con mayores ventas.

Serie resaltada en el gráfico

El color de las ciudades con más ventas, según el valor indicado en el control de número, se muestran con un color de barra más resaltado.

Título del gráfico

El título del gráfico no es un texto estático, sino que se adapta a los valores indicados en el control numérico.

Para ver en detalle cómo diseñar este tipo de gráfico interactivo lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Introducir datos con Formularios en Excel

Cuando estamos introduciendo datos en Excel, en forma de tabla, nos puede interesar hacerlo utilizando un formulario de entrada de datos. De esa forma se añaden los registros de una forma más visual y amigable.

Para ello, lo primero que tenemos que tener son los datos en forma tabular y transformarlos en una tabla de Excel, para lo cual nos situamos encima de cualquiera de los datos y vamos a la ficha Insertar, pulsando en Tabla.

El comando Formulario

En ninguna de las fichas que aparecen por defecto en Excel aparece el comando, por lo que la forma más rápida de acceder al comando Formulario es añadirlo a la barra de herramientas de acceso rápido.

Desplegamos la flecha que aparece al lado de la barra de herramientas de acceso rápido y pulsamos sobre la opción Más comandos…

En la ventana de Opciones de Excel desplegamos la ventana de «Comandos disponibles en:» y seleccionamos Todos los comandos.

Ahora buscamos el comando Formulario… y lo agregamos a la parte derecha. Después pulsamos Aceptar. Podemos comprobar que aparece a la derecha de la barra de herramientas de acceso rápido el icono del comando Formulario.

Utilización de los formularios

Para hacer uso de los formularios nos situamos encima de cualquier celda de la tabla que hemos creado y en la cual queremos insertar datos. Pulsamos sobre el comando Formulario y se abre la ventana correspondiente al formulario.

Pulsando en el botón Nuevo nos muestra el formulario en blanco para ingresar los datos en la tabla mediante el formulario.

También podemos eliminar el registro que queramos y desplazarnos al registro anterior o siguiente.

e la misma forma podemos movernos por los diferentes datos del formulario filtrando por criterios, incluyendo un valor a incluir (por ejemplo, zona: Norte), un importe (por ejemplo, más de 10000) o el uso de comodines (por ejemplo, Nombre: M* para indicar nombres que comiencen por la letra M.

Para cerrar el formulario pulsaríamos sobre el botón Cerrar.

Para ver en detalle cómo utilizar un formulario en Excel lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Calcular el cuadro de amortización de un préstamo de cuotas constantes

Vamos a ver la manera de calcular el cuadro de amortización de un préstamo de cuotas constantes.

Los préstamos de cuotas constantes son aquellos en los que siempre se paga el mismo importe a la hora de devolver un préstamo. La cuota está compuesta por la parte de amortización de capital y la parte de intereses.

Si bien la cuota es constante, las cantidades correspondientes a capital e intereses sí que van variando. Conforme pasa el tiempo el importe correspondiente a intereses va disminuyendo. En cambio la parte correspondiente a devolución de capital cada vez es mayor.

Este tipo de préstamos corresponde al denominado sistema francés.

Vamos a ver cómo se calcularía el cuadro de amortización de un préstamo de cuotas constantes en Excel.

Datos del préstamo

Los datos de partida del préstamo para este ejercicio serán los siguientes.

El total del préstamo asciende a 36.000 euros, con un tipo de interés anual del 6,5% a devolver mensualmente en 5 años.

Preparación del cuadro de amortización

Introduciremos los datos de partida en Excel.

Para crear el cuadro de amortización tendremos las columnas: cuota, fecha, cuota, interés, devolución de capital y capital pendiente.

Ya podemos rellenar el número de cuota, desde la cuota inicial (cero) a la última que sería la 60 (5 años corresponde a 60 meses).

En la columna de la fecha podemos indicar las 60 fechas.

Y en cuanto al capital pendiente en la primera fila (cuota 0) podemos indicar el total del préstamo, dado que debemos la devolución de la totalidad del préstamo.

Calcular la cuota del préstamo

Para calcular la cuota del préstamo utilizaremos la función PAGO. Los argumentos obligatorios de la función son:

Tasa: Es el tipo de interés. Dado que queremos calcular las cuotas mensuales y el tipo de interés es anual, debemos dividir este entre 12, para obtener el tipo de interés mensual.
Nper: Número de periodos. 5 años, que en meses son 60 periodos.
Va: El valor actual del préstamo. El importe total del préstamo.

Con todo ello obtenemos una cuota constante de 704,38 euros. Este importe aparece siempre en negativo, por lo que si queremos tener el dato en positivo, una posibilidad es que delante de la función incluyamos el signo negativo.

Para poder arrastrar hacia abajo esta fórmula tendremos que fijar las referencias como absolutas.

Y ya podemos arrastrar hacia abajo esta fórmula.

Calcular el interés

Para el cálculo del tipo de interés utilizaremos la función PAGOINT. Los argumentos obligatorios son:

Tasa: tipo de interés mensual.
Período: corresponde al número de cuota. Este valor no se puede incluir como referencia absoluta dado que debe variar con cada cuota.
Nper: Número de períodos.
Va: Importe del préstamo.

También en este caso escribiremos un signo negativo delante de la fórmula para que el importe aparezca en positivo. De la misma forma fijaremos todas las referencias menos el Período, para poder arrastrar la fórmula hacia abajo.

Cálculo del la devolución del capital

Dado que la cuota es constante y ya hemos calculado el interés, mediante una simple resta calculamos el importe correspondiente a amortización o devolución del capital.

Cálculo del capital pendiente

Para calcular el importe de capital pendiente, y partiendo del importe total del préstamo, únicamente tendremos que restar al importe de capital pendiente, la cantidad de devolución de capital de cada periodo.

Para comprobar que hemos calculado correctamente el cuadro de amortización recomiendo que observemos que el capital pendiente después del pago de la última cuota es cero.

Para ver en detalle cómo hemos seguido todos estos pasos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Conectar tablas dinámicas

Nos podemos encontrar con la situación de tener varias tablas dinámicas y que queramos incluir unos segmentadores de datos que filtren a la vez todas las tablas dinámicas.

Esto sería muy indicado, por ejemplo, si tenemos un Dashboard, que utilice varias tablas dinámicas y que queramos que con un mismo segmentador se filtren los datos de todas las tablas de la hoja.

En la siguiente imagen tenemos dos tablas dinámicas y dos segmentadores. Si pulsamos sobre una zona o un contrato, el filtro se aplicará únicamente a la tabla desde la que hemos generado la segmentación.

Para conectar las tablas dinámicas para que se filtren todas ellas a la vez al pulsar una opción del segmentador nos situamos sobre el segmentador y nos vamos a la pestaña Segmentación.

Después pulsamos sobre el comando Conexiones de informes.

Se abre una ventana de Conexión de informe para la Segmentación Zona. Vemos que tiene marcado un check para la tabla dinámica a la que afecta esa segmentación.

Como queremos que afecte a más tablas lo único que tenemos que hacer es marcar el nombre de las tablas dinámicas que queremos que se vean afectadas con ese segmentador..

En nuestro caso, como queremos que se filtren las dos tablas dinámicas, marcamos las dos. Incluso se podrían marcar tablas dinámicas de hojas diferentes.