TEMA 3 FÓRMULAS Y FUNCIONES BÁSICAS EN EXCEL

Referencias relativas, absolutas y mixtas

 

 

La característica principal y dónde reside la potencia de Excel es en las fórmulas y las funciones, porque utilizan referencias. En una hoja de cálculo, con miles de registros, utilizar referencias en las fórmulas simplifica el trabajo.

Imaginad que tenemos facturas del mes anterior para hacer cálculos con ellas. Al importe neto de cada factura se le aplicará su tipo impositivo de IVA. Para cada factura utilizamos un celda donde multiplicamos el importe neto por el IVA pero utilizando valores absolutos.

 

Fórmulas con valores absolutos Excel

Fórmulas con valores absolutos

 

Pero, el Gobierno avisa de que el tipo de IVA actual para el producto que fabricamos se modifica. Del 21% pasa al 15%. Modificar una a una, cada factura, celda por celda que contenga un cálculo con el importe del IVA, del 21 al 15 será altamente costoso y pesado.

 

Fórmulas con referencias relativas Excel

Fórmulas con referencias relativas

 

Si utilizamos una celda dónde ponemos el tipo de IVA (En la imagen anetrior E1), y calculamos utilizando las referencias, simplemente cambiando en la hoja de cálculo el tipo de IVA (E1 en la siguiente imagen) se recalculan todas las fórmulas en las que tenga protagonismo el tipo de IVA automáticamente.

Os podéis fijar en las dos imágenes, la celda activa es E4 y en la barra de fórmulas muestra la fórmula. Para las dos es la misma fórmula. Solo hemos cambiado en E1 el IVA del 21 al 15%.

 

Fórmulas con referencias relativas y valor modificado Excel

Fórmulas con referencias relativas y valor modificado

 

Por defecto Excel utiliza referencias relativas. Pero también necesitaremos dominar las Referencias Absolutas y las Referencias Mixtas.

 

Referencias Relativas

 

 

 

La referencia Relativa es por defecto la que utiliza Excel. Se le dice Relativa porque el resultado del cálculo dependerá del dato que contenga dicha referencia.

Cuando copiamos y pegamos una fórmula que contiene referencias relativas, la fórmula calculará en su nueva ubicación utilizando las referencias relativas con respecto a las de origen.

 

Copiado de fórmula con referencias relativas Excel

Copiado de fórmula con referencias relativas

 

Al copiar D2 en D3, como multiplicaba B2 por C2, pega la fórmula aplicando B3 por C3.

 

Referencias Absolutas

 

 

 

Una referencia Absoluta es la referencia que participa en una fórmula, pero que al copiar dicha fórmula para que se aplique en otras celdas, necesitamos que siempre calcule de forma fija el valor que contiene la referencia Absoluta sobre el resto de la fórmula.

Digamos que bloqueamos o fijamos una referencia como constante en una fórmula o función.

Para convertir una referencia en absoluta, pondremos el símbolo del dólar ($) delante de la columna (letra) y delante del número (fila) cuando escribimos. Con esto bloqueamos toda la referencia. También podemos activarlos con la tecla F4.

 

Sintaxis de referencia Absoluta Excel

Sintaxis de referencia Absoluta

 

La referencia Absoluta es necesaria cuando debemos utilizar una referencia para copiar y pegar la fórmula vertical y horizontalmente.

Tal como vemos en la siguiente ilustración, en F2 multiplicamos C2 por J1. Como queremos copiar de F2 hasta F4, aplicaremos el símbolo del dólar tanto a la columna como a la fila en la fórmula para la referencia J1, quedando $J$1. Con esto conseguimos que cuando copiamos la fórmula el primer valor será relativo de dónde se pegue la fórmula, y el segundo valor será absoluto, siempre utilizara el dato introducido en J1.

 

Cálculo copiando fórmulas con referencias absolutas Excel

Cálculo copiando fórmulas con referencias Absolutas (Creación primera fórmula)

 

En la siguiente imagen observamos que utilizar el controlador de relleno para copiar y pegar hacia abajo, repite como referencia J1 con el símbolo del dólar ($) delante de la columna y de la fila.

 

Cálculo copiando fórmulas con referencias Absolutas Excel

Cálculo copiando fórmulas con referencias absolutas (copia hacia abajo)

 

De la misma forma al copiar y pegar hacia la derecha como vemos a continuación.

 

Cálculo copiando fórmulas con referencias absolutas Excel

Cálculo copiando fórmulas con referencias absolutas (Copiado hacia la derecha)

 
Referencias Mixtas

 

 

 

Con la referencia Mixta lo que deseamos conseguir es que al copiar hacia un lateral siempre utilice el dato de una misma columna, pero que al copiar en vertical (hacia arriba o hacia abajo) cambie de fila. Pondremos el símbolo del dólar ($) delante de la columna (Letra).

O deseamos copiar en vertical y que siempre utilice el dato de una fila pero que al copiar hacia los laterales (Izquierda o derecha) cambie de columna. Pondremos el símbolo del dólar ($) delante de la fila (Número).

 

Referencias Mixtas Excel

Referencias Mixtas

 

En la imagen siguiente vamos a calcular la comisión del 1%, 3% y 5% para cada uno de los representantes según su Facturación.

La mecánica de cálculo será: en C2 multiplicamos B2 por C1, en D2 multiplicamos B2 por D1, en E2 multiplicamos B2 por E1, y así con cada uno. Para ahorrar tiempo y ser eficientes creamos una fórmula y la copiamos y pegamos en el resto de celdas.

 

Hoja Excel para calcular Bonificación sobre Facturación por Representante

Hoja Excel para calcular Bonificación sobre Facturación por Representante

 

Creamos la fórmula =B2*C1 escribiéndola en C2, como en la ilustración siguiente. De C2 la copiaremos hacia abajo y hacia la derecha. Cuando copie hacia abajo siempre querré utilizar el 1% que está en la fila 1, pero cuando copie hacia la derecha querré utilizar D y E. Aquí bloqueo (pongo $) a la fila 1. En cambio, al copiar hacia abajo quiero que utilice B2, B3 y B4 pero que al copiar hacia la derecha siempre utilice la columna B. Tendré que poner el $ delante de la columna B.

 

Creación de la primera fórmula cálculo Bonificación Excel

Creación de la primera fórmula cálculo Bonificación

 

A continuación vemos el resultado de copiar hacia abajo.

 

Fórmula con referencias mixtas copiada hacia abajo Excel

Fórmula con referencias mixtas copiada hacia abajo

 

Y ahora vemos el resultado de copiar hacia la derecha.

 

Fórmula con referencias mixtas copiada hacia la derecha Excel

Fórmula con referencias mixtas copiada hacia la derecha

 

Digamos que hemos convertido en referencias mixtas a C1, D1, E1, B2, B3 y B4.

 

Cuarto artículo correspondiente al Tema 3 Fórmulas y funciones básicas en Excel de la unidad formativa UF0511 Tratamiento básico de datos y hojas de cálculo del módulo formativo MF0974 Tratamiento de datos, textos y documentación del certificado de profesionalidad ADGG0508 Operaciones de grabación y tratamiento de datos y documentos.

Puedes repasar el artículo anterior Copia de fórmulas en Excel o continuar con la siguiente capítulo Funciones matemáticas elementales predefinidas en la aplicación de hoja de cálculo.

Ahora, puedes valorar el artículo en el apartado Calificación.

También, si crees que otras personas pueden aprovecharlo no dudes en compartirlo.

Otra opción sería, en el caso de que tengas dudas o necesites aclaraciones ponerte en contacto conmigo o dejar un comentario.

Para terminar, próximamente publicaré el resto de capítulos y temas. Si no quieres perderte ninguno te recomiendo que te suscribas a la newsletter para recibir todos los artículos en tu mail.

¿Utilizas referencias mixtas y absolutas o escribes cada fórmula?. Comparte conmigo tu experiencia.

Te espero en el siguiente artículo…

¡COMPARTE!

Si crees que este artículo puede ayudar a otras personas no dudes en compartirlo. Gracias.