OPERACIONES CON CELDAS DE EXCEL. REFERENCIAS

Los atajos de teclado son combinaciones de teclas del teclado sirven para suplir los

movimientos y clicks de ratón y trabajar más rápidamente que con este, aumentando la

productividad. A continuación se presenta una pequeña lista a modo de ejemplo que muestra

alguno de estos “atajos”. Listas exhaustivas de estos atajos se pueden encontrar en cualquier

página web, por ello no profundizaremos en este tema.

Las teclas de dirección: nos permiten desplazarnos por la hoja o libro de Excel hacia arriba,

abajo, derecha o izquierda.

CTRL + tecla de dirección, situamos la selección en el extremo de la hoja que tiene datos. Si las

celdas con datos están intercaladas con celdas vacías, la selección se realiza sucesivamente de

celdas con datos hasta la siguiente, o hasta el final de la hoja.

CTRL + Inicio nos dirige a la primera celda de la hoja de cálculo (la celda A1).

CTRL + Fin nos lleva a la última celda de la hoja de cálculo, la celda más a la derecha y más

debajo de la hoja.

Av Pag Desplaza la celda seleccionada una pantalla hacia abajo.

Re Pag Desplaza la celda seleccionada una pantalla hacia arriba.

CTRL + Av Pag desplaza la celda selesccionada a la hoja siguiente.

CTRL +Pag Ant desplaza la celda seleccionada a la hoja anterior.

ALT + Av Pag desplaza la celda seleccionada una pantalla a la derecha.

ALT + Re Pag desplaza la celda selecicionada una pantalla a la izquierda.

CTRL + F6 ó CTRL +TAB desplaza la celda seleccionada al libro siguiente (otro fichero Excel

abierto).

Fin: activa o desactiva el Modo Fin.

Con Bloq Despl activado + inicio vamos a la primera celda de la hoja.

Con Bloq Despl activado + Fin vamos a la última celda que se ve entera de la ventana.

Con Bloq Despl activado + flechas de desplazamiento, desplaza la ventana cuando la

selección llega a los límites de esta.

REFERENCIA A LAS CELDAS DE UNA HOJA DE CALCULO.

Un capitulo olvidado por muchos usuarios es las referencias a las celdas. Estamos

acostumbrados a utilizar el ratón para movernos por la hoja de forma que ignoramos que

celdas estamos recorriendo. Todas ellas tienen un nombre que diferencia unas de otras solo

por su posición en la hoja. Esto es importante saberlo a la hora de utilizar características o

funcionalidades de Excel, como puede ser el empleo de fórmulas.

Las celdas toman su nombre justo de la posición que tienen dentro de la hoja de cálculo: la

primera celda de la hoja se llama A1 y su nombre se forma con la intersección de la columna y

de la fila donde se encuentra. A1 es la celda cuya columna es la “A” y cuya fila es la “1”.

Una de las fórmulas más sencillas que se pueden emplear en Excel es la que permite introducir

en una celda el valor de otra.

Si en la celda B5 escribimos “=A1” lo que estamos diciendo es que en la celda que ocupa la

intersección de la 2ª columna: “B”, y la 5ª fila: “5”, vamos a tener el valor que en ese momento

tenga la celda A1, que es la primera de la hoja.

Si cambia el valor de A1, automáticamente habrá cambiado el valor de B5.

Para introducir esta sencilla fórmula en la celda, es conveniente saber cómo se nombran las

celdas. Aunque en este caso se puede hacer lo mismo con el ratón, nos vamos a encontrar

fórmulas bastante más complejas que hace imposible hacerlo de otra forma que no sea

tecleando las referencias a las celdas con el teclado.

Un rango de celdas es un conjunto de celdas agrupadas que forman un rectángulo y que se

pueden referenciar nombrando en primer lugar la celda superior izquierda de la matriz

formada, seguido de dos puntos y por último, nombrando la última celda, la inferior derecha

de la matriz, en la imagen vemos la selección de 8

celdas que en este caso contienen datos, para hacer

referencia a este rango, compuesto por las 8 celdas,

nombramos primero la celda superior izquierda, esta

es la celda A1, seguida de dos puntos y por último la

celda inferior derecha de la selección, la celda B4 de

forma que el rango quedaría referenciado o nombrado

como sigue: “A1:B5”.

Es posible dar un nombre

personalizado a celdas o rangos.

Esto nos permitirá referenciarlas

en fórmulas sin tener que recordar

que “El importe total está en la

celda “AG130” o que nuestra lista

de colores esta en el rango

“C80:C87”. Podemos llamarles

directamente “Importe Total” y

“Colores”, respectivamente.

Para dar un nombre a una

celda o a un rango de celdas,

desplegamos, como muestra la

imagen anterior, el menú

Insertar, grupo Nombre y en el

el desplegable, pulsamos en

Definir. Automáticamente se

abre el cuadro de dialogo que nos

permite definir el nombre. La

información que nos reporta este cuadro, además, incluye el rango al que se refiere el nombre

que vamos a definir y la relación de nombres de rango definidos anteriormente en la hoja de

cálculo.

Le damos el nombre adecuado (elegimos para este ejemplo “LETRAS”) y pulsamos Aceptar. A

partir de este momento, podemos referenciar este grupo de celdas por el nombre dado:

LETRAS.

Podemos probar su funcionamiento moviéndonos lejos del rango, por ejemplo pulsando dos

veces Av Pag. La celda activa ahora está lejos del comienzo de la hoja y no vemos el grupo de

celdas del ejemplo. Si pulsamos en el teclado CTRL + I, aparece el

cuadro “Ir a…” y en él, la lista de selecciones definidas. En este caso

solo aparece “LETRAS” y la celda activa.

Seleccionando “LETRAS” y pulsando el

botón Aceptar, de nuevo vemos

seleccionado el rango del ejemplo.

Pero más fácil aún que lo que acabamos

de hacer es ir directamente a la selección

con nombre si buscamos el mismo en la barra de herramientas

eligiéndolo en el desplegable, tal como vemos en la imagen.

REFERENCIAS ABSOLUTAS Y RELATIVAS

De máxima importancia es la forma en la que podemos referenciar las celdas o grupos de ellas.

Si en el ejemplo visto más arriba, en el que a la celda B5 le

otorgamos el valor que tenga en cada momento la celda A1

y lo expresamos como la formula “=A1”, estamos hablando

de una REFERENCIA RELATIVA. Esto es así porque si

copiamos la celda B5 a la B6 (arrastrando la celda B5 hacia

abajo cuando cambia el cursor) y comprobamos qué formula

tiene ahora B6, veremos que lo que contiene no es “=A1”,

sino “=A2”. De la misma manera, si tratamos de copiar la

formula de la celda B6 a la A6, obtenemos un error (REF!), pues nos hemos salido del ámbito

de la hoja de cálculo por la izquierda.

Esta funcionalidad no sería nada útil para los casos en que una misma fórmula, por ejemplo,

tenga que ser copiada literalmente en varias celdas. Tal como hemos definido la fórmula, en

cada celda distinta que copiemos esta fórmula, el resultado será que apunta a una celda

diferente.

La solución está en la REFEENCIA ABSOLUTA. Mediante esta, es posible copiar formulas que

contengan “referencia a celdas” de forma que al copiarlas, hagan referencia a la misma celda

en la fórmula, independientemente de donde se copie. Esto que parece un galimatías, lo

veremos claramente en el siguiente ejemplo.

La referencia absoluta se forma a partir de la relativa, añadiendo a esta el símbolo dólar “$”

delante de la fila y/o columna que pretendamos quede fija: Que no varíe al copiarla.

Como ejemplo de lo expuesto, utilizaremos el valor anterior de la celda B6 y la copiaremos en

distintas celdas.

Para conseguir que una referencia relativa se convierta en absoluta, podemos escribir delante

de la fila y/o columna el símbolo de dólar “$” en la barra de formulas o expresiones pero es

mucho más fácil y rápido seleccionar para editar la referencia de celda y pulsar la tecla F4. Si

pulsamos una vez F4, el resultado es que una formula como “=A2” quedaría “=$A$2” y como

consecuencia, al coparla en “B7” el valor mostrado será el mismo que el que tenga B6, es

decir, el de A2. si ahora copiamos la formula en la celda “A6” como hicimos en el ejemplo

anterior, ya no nos da como resultado un error, nos da precisamente el valor de “A2” que si no

lo hemos modificado todavía será “C”

Hemos visto que el signo dólar hace que si lo escribimos delante de la columna, va a mantener

fija dicha columna y si lo escribimos delante de la fila deja esta fija. Podemos hacer que quede

fija la fila y la columna como en el ejemplo precedente. Pero también podemos hacer que

quede fija o bien la fila o bien la columna. Dependiendo de donde y cuantos signos

“$”coloquemos.

En el ejemplo que nos ocupa, tenemos la fórmula “=$A$2” en la celda B5 y la queremos copiar

en varias celdas de la hoja. La fórmula expresada “=$A$2” mantiene la celda A2 en cualquiera

de las celdas donde copiemos esta fórmula. “=$A2” mantiene fija la columna A y va

modificando la fila dependiendo de donde copiemos la fórmula. “=A$2” mantiene fija la fila 2

pero modifica la columna dependiendo de la celda donde estemos copiando la formula. Por

último, si la fórmula a copiar es “=A2” que está en B5, al copiarla en cualquier otra celda se

modificará tanto la fila como la columna a la que hacemos referencia.

Para pasar de una a otra forma de referencia, pulsamos con el ratón sobre la formula en la

barra de formulas o expresiones, el cursor habrá cambiado a modo edición (se ve como una

barra vertical, en vez de una flecha) y en este estado, pulsamos F4. la primera vez que

pulsemos, si la referencia era relativa, se transforma completamente en Absoluta. Una nueva

pulsación en F4 fija la fila y una nueva pulsación fija solamente las columnas. Si volvemos a

pulsar F4, de nuevo estaremos ante la referencia relativa.

Aunque el tema de las formulas se abordara más adelante y ahora no vamos a fijarnos

demasiado en su sintaxis, el siguiente ejemplo clarificará del todo esta cuestión.

Queremos conocer la diferencia a abonar por varios artículos dependiendo del descuento que

nos apliquen por ellos. En el establecimiento A nos aplican un descuento y en el B nos aplican

otro diferente. Al mismo tiempo, cada establecimiento puede modificar el descuento que

aplica en el momento que quiera.

Para observar la diferencia en el precio final de esa colección de artículos, empleamos una hoja

de cálculo en la que escribimos en forma de columna los nombres de los artículos a estudiar. A

la derecha de esta columna, otra columna nos indica el precio unitario del artículo que figura a

su izquierda. Este precio es único para los dos establecimientos.

La siguiente columna de la derecha presentará los precios finales del establecimiento A, una

vez aplicado su descuento.

Una última columna presenta los precios finales del establecimiento B, también con su

descuento aplicado.

Sobre estas cuatro columnas, dos celdas contienen el valor del tanto por ciento aplicado por

cada establecimiento. El valor de estas puede ser modificado en cualquier momento.

Para este ejemplo, creamos una tabla de datos en el que la primera y segunda columna

contiene datos literales mientras que las dos siguientes se calcularán mediante una sencilla

fórmula.

Para el establecimiento A para cada artículo):

Precio de A = Precio Artículo – (precio Artículo * %A)

Para el establecimiento B para cada artículo):

Precio de B = Precio Artículo – (precio Artículo * %B)

Este es el diseño que

hemos elegido para

este cálculo.

Las columnas C y D

correspondientes a

los cálculos de los

establecimientos los

estableceremos

mediante el uso de

formulas y

referencias.

Establecimiento A:

Para esta columna

podemos optar por

escribir una fórmula por cada artículo, con lo que teclearemos 11 fórmulas o bien teclear la de

la primera fila y copiar al resto. Esto es lo que se hace y lo que vamos a hacer ahora.

Seleccionamos la celda C6 y en la barra de

formulas tecleamos “=B6-(B6*D2)”. Aceptamos

y ya tenemos el primer valor.

Lo anterior lo leemos de la siguiente manera:

“toma el valor que contiene la celda B6 y le

restas el resultado de multiplicar el

mismo valor de B6 por el valor de la celda

D2”.

Si dejamos la fórmula tal como la hemos

tecleado y que podemos ver en la

imagen, al copiarla, el resultado de las

celdas B7, B8,.. y sucesivas no reflejarán

el cálculo que queremos realizar. Vemos

en la imagen que incluso nos ha

aparecido un error. ¿Qué es lo que ha pasado?.

Veamos; en la celda C7 lo que ha resultado de la copia

ha sido: “toma el valor de la celda B7 y le restas el

resultado de dividir ese mismo valor de B7 por el valor

de la celda D3”.

En principio, la fórmula es correcta pero, ya no estamos

calculando el descuento del 10% del establecimiento A,

sino el 15% del establecimiento B. Este no era el

propósito.

Pasemos al resultado de la celda C8: en esta

ocasión lo que indica la fórmula que

aparece es lo siguiente: “toma el valor de la

celda B8 y le restas el resultado de dividir

ese mismo valor de B8 por el valor de la

celda D4”.

La celda D4 está vacía y Excel lo toma como que el valor es cero. En este caso, el descuento

aplicado es cero.

Un último ejemplo lo tenemos en el

resultado de la celda C9: “toma el valor de

la celda B9 y le restas el resultado de

dividir ese mismo valor de B9 por el valor

de la celda D5”.

En esta ocasión la celda D5 contiene

caracteres alfanuméricos. El resultado que nos devuelve esta fórmula es “DEF!” es decir, un

error. Estamos tratando de multiplicar el valor de la celda B9 (40) por el valor de la celda D5

(“Establecimiento A”), es decir, un número por un texto.

Como vimos, este problema se soluciona con las referencias absolutas.

Si bien en este ejercicio nos viene bien evaluar las celdas del precio articulo dependiendo de la

“altura” en que se encuentren en la columna, no es el caso de la celda que contiene el valor

del descuento. Este descuento es el mismo para toda la columna de artículos en el

Establecimiento A por lo tanto, tendremos que multiplicar el valor de cada producto por el

valor exclusivo de la celda D2, que es la única que contiene el descuento deseado.

¿Cómo hacíamos esto?. Pues refiriéndonos desde un principio a la celda D2 como una celda en

referencia absoluta: en modo edición sobre la barra de fórmulas, colocamos el cursor vertical

sobre D2 y pulsamos F4.

La fórmula ahora

presenta este aspecto:

“=B6-(B6*$D$2)”. Al copiar esta fórmula al resto de celdas de la columna, el resultado ahora es

el correcto: siempre estamos multiplicando el valor del producto por el mismo tanto por

ciento.

Haciendo lo mismo para el Establecimiento B en la columna B, obtenemos el cálculo que

necesitábamos.