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
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
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
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
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
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 (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 (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 (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
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
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
A continuación vemos el resultado de copiar hacia abajo.
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
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.
Índice de contenidos
TEMA 3 - Fórmulas y funciones básicas en Excel
1. Operadores y prioridad en Excel
2. Escritura de fórmulas básicas en Excel
3. Copia de fórmulas en Excel
4. Referencias relativas, absolutas y mixtas
5. Funciones matemáticas elementales predefinidas en la aplicación de hoja de cálculo
6. Reglas para utilizar las funciones predefinidas en Excel
7. Utilización de las funciones elementales más usuales de Excel
8. Uso del asistente para funciones de Excel
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.
