Referencias relativas y absolutas en Microsoft Excel

Hoy hablaremos de las referencias relativas y absolutas en Excel. Es muy probable que alguna vez nos hayamos encontrado en algunas fórmulas o funciones  con el símbolo $ en Excel. El símbolo $ dentro de una celda, se utiliza para "fijar" esa celda cuando utilizamos el método de arrastrar fórmula o copiamos una fórmula o función y nos interesa que siempre haga referencia a una celda en concreto.

 

Podemos "fijar" filas y columnas independientemente  o fijar una celda. Así, en el primer caso si hacemos una referencia así: =A1+$B1, estamos indicando que cuando arrastremos la fórmula hacia abajo, las filas aumenten en ambos casos (A2+$B2, A3+$B3, A3+$B3...) sin embargo, si arrastramos hacia la derecha, la segunda celda quedaría fija (B1+$B1, C1+$B1, D1+$B1...).

Si lo que nos encontramos es algo así =A1+B$1, nos pasará el caso inverso. Si arrastramos hacia abajo, aumentará el primer valor pero no el segundo (A2+B$1, A2+B$1, A2+B$1...) y al arrastrar hacia la derecha aumentarán ambos (B1+C$1, C1+D$1, D1+E$1...).

 

Estos dos casos se les conoce como referencias relativas. Si introducimos: = A1+$B$1, estamos fijando por completo la celda B1 y por tanto arrastremos hacia donde arrastremos la fórmula, siempre sumará por B1. Este caso se conoce como referencia absoluta.

Vamos a la práctica:

Partimos de ésta tabla que queremos formular, donde a partir del año 2007, cada año se prevee un incremento de un 5% respecto al año anterior:

En este caso tenemos los números de venta para cada producto y una celda (la tasa) que contiene un 5% y que utilizaremos para calcular el incremento (de ésta manera si luego queremos modificar ese valor se recalcularía toda la tabla automáticamente).  Para realizar el cálculo, debemos multiplicar el número de ventas por la tasa y volver a sumar las ventas. Para la gasolina sería: 100*5% + 100... para realizar un incremento del 5%. Así la fórmula quedaría:

Si validamos esta fórmula y la arrastramos, nos encontraremos con el problema de que la tasa, es decir el 5% se mueve a medida que movemos la fórmula, por tanto nos interesa dejarla fija para poder arrastrar la fórmula. Para ello, en la barra de fórmulas, marcaremos la celda B2 (un click entre la B y el 2) y pulsaremos la tecla F4, de manera que veremos que la celda queda como $B$2. Ahora ya podemos validar la fórmula con enter y arrastrar la fórmula.

En este segundo ejemplo, cada producto tiene su tasa:

La fórmula que deberíamos utilizar sería la siguiente:

En este caso, nos interesa que cuando arrastremos hacia la derecha, la columna de la tasa quede fija pero cuando arrastremos hacia abajo, vayan incrementando las filas. Por tanto en este caso la celda B2 debería ser $B2. Por tanto repetiremos el proceso anterior. En la barra de fórmulas haremos un click entre la B y el 2 e iremos apretando repetidas veces la tecla F4 hasta que nos aparezca $B2.

Ahora ya podemos validar la fórmula y arrastrarla.

Espero haberos ayudado y ya sabéis que podéis seguir enviando vuestros correos a info@elprofedeexcel.com

Os dejo el enlace del ejercicio: Relacionesrelativas y absolutas

Transponer datos de una Tabla de Excel

Muchas veces habremos oído eso de Transponer en Microsoft Excel pero se nos habrá quedado cara de no entender nada. El otro día en un curso, lo comento como anécdota,  un alumno me decía... Ya puedo dormir tranquilo, después de tantos años escuchando la frase, por fin he conseguido transponer celdas en Excel.

En el fondo transponer, no es más que cambiar la orientación de una tabla de Excel, de modo que los datos que tenemos en la columna izquierda, aparezcan como cabecera y viceversa.

Para ello, la manera más simple de realizarlo consiste en hacer una copia de los datos (tabla) que queremos transponer y pegarla mediante un "pegado especial" (un día de estos crearé un artículos sobre los diferentes métodos de Pegado Especial).

Para realizar el primer método, como ya hemos indicado antes, seleccionaremos la tabla que queremos transponer y le daremos a copiar (botón derecho --> copiar; ctrl+C; ... el método que más os guste)

Una vez copiado, seleccionaremos la celda de destino donde pegaremos los datos y marcaremos la flecha que se encuentra justo debajo del botón "Pegar" (Situado en la parte izquierda de la barra de herramientas del menú Inici.) y seleccionaremos la opción "Transponer"

El resultado, debería ser este, la tabla cambiada de orientación.

Como siempre, espero haberos ayudado.

PD.- Agradecer a Pablo García Santiago la consulta realizada.

PLANTILLAS DE EXCEL - PLANTILLA DE FACTURA

Hoy veremos como trabajar con una plantilla prediseñada en Excel y en este caso las utilizaremos para crear una Factura mediante Excel. Con las Plantillas podemos encontrar multitud de hojas de cálculo ya formateadas y formuladas que nos ahorraran mucho trabajo. Vamos a ver ya como trabajar con las plantillas.

Para empezar crearemos un nuevo documento de Excel. En este caso en lugar de seleccionar un documento en blanco, si nos fijamos en la barra lateral izquierda, podemos seleccionar entre nuestras plantillas (en el caso de que ya tengamos) o descargar plantillas de Microsoft Office Online. En nuestro caso, de todo el listado de Categorías que tenemos, hemos seleccionado la categoría Facturas:

A continuación veremos que en la parte central de la ventana nos aparecen todas las plantillas de Microsoft Office Online relacionadas con la categoría facturas. En nuestro caso para éste ejemplo hemos seleccionado "Factura de Servicios con cálculo de Impuestos". Si nos fijamos en la parte superior derecha, veremos el origen de la plantilla, en este caso pertenece a Microsoft y el número de votos y valoración que tiene dicha plantilla (eso significa que entrando en la web de Microsoft podemos valorar las plantillas de Excel). Por último le daremos al botón descargar que se encuentra en la parte inferior derecha de la ventana.

Como podéis comprobar, ya tendremos una plantilla de factura estándar ya creada y formulada, donde solo deberemos introducir nuestros datos, los datos del cliente, fecha.... A partir de ésta plantilla solo deberemos hacer las modificaciones que queramos para darle un toque más personal y que se adapte más a nuestras necesidades.

Hemos visto un pequeño ejemplo de cómo utilizar una plantilla en Excel, como ya hemos visto en la primera captura, tenemos infinidad de categorías y dentro de cada Categoría una gran variedad de plantillas que, como ya hemos comentado antes, nos agilizarán el trabajo.

Espero que el post de hoy os sea muy útil y ya sabeis, si teneis dudas, el profe de Excel está abierto a cualquier tipo de consulta.

Saludos.