Operaciones con celdas en hojas de calculo

1

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.

Javier Fernández Castelo 22 de marzo de 2012

2

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.


3

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.

En Excel 2007, todavía es más fácil: SOLO HAY QUE SELECCIONAR EL RANGO Y PULSAR EL BOTON DERECHO DEL RATON. El cuadro que aparece a continuación es similar al que aparece en Excel 2000/2003, pero permite escoger el ámbito del nombre creado.

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, no tenemos el valor “C”, obtenemos un error (REF!), pues nos hemos salido del ámbito de la hoja de cálculo por la izquierda.


4

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.


5

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.


6

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.


7

¿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.

Por último, vamos a terminar de ver esta interesante herramienta comprobando como podemos referencial celdas de otra hoja e incluso de otro libro.

Supongamos que necesitamos un documento que consta de varias hojas en el cual, dispondremos de una serie de datos que se han de repetir en todas las hojas pero que pueden ser modificados en cualquier momento. Esas modificaciones están involucradas en todas ellas.

Supongamos que tenemos tres empleados y que tenemos que confeccionar su nómina de forma que en una hoja seleccionamos el mes y año del pago, así como diferentes claves de nómina que ese mes van a percibir.

En las tres hojas restantes aparece el formato de cada una de las nóminas.


8

Abrimos un nuevo libro de Excel y en la primera hoja introducimos los datos que aparecen en la imagen adjunta.

Para este sencillo ejemplo, lo que hará esta hoja es almacenar los datos comunes a los tres operarios. Supongamos que todos ellos tienen la misma antigüedad, que todos los días están a turnos y que todo el mes, o bien están de día o de noche pero todos juntos.

En esta hoja solamente vamos a tener que determinar el año y el mes de pago. Además en la columna “claves del mes” tenemos que introducir las claves correspondientes. Para ello, utilizaremos las referencias a celdas.

Las otras tres hojas, en principio idénticas, captarán de la hoja “Datos” la mayoría de los datos necesarios.

Lo haremos en las tres hojas a la vez. Esto lo conseguimos seleccionando las tres pestañas de las tres hojas que queremos preparar.

Empecemos por la celda C1. Para llenar esta celda, escribimos en ella el signo “=” y a continuación, pulsando sobre la pestaña “Datos” de la primera Hoja, pulsamos sobre la celda D5. Aceptamos la fórmula y ya la tenemos vinculada. La celda está relacionada. Lo mismo haremos con el campo “del año”; en la celda E1 escribimos el signo “=” y seleccionando la hoja “Datos” de nuevo, seleccionamos la celda D4.

Para rellenar las celdas de concepto, utilizaremos la información proporcionada en la hoja “Datos”, rango E9:E16. Seleccionando la celda A6 escribimos el signo “=” en ella, vamos a la hoja “Datos” y una vez en ella, seleccionamos la celda E9. La fórmula que se ha creado es “=Datos!E9”. por último, seleccionando la celda E6, escribimos el signo “=” y en la hoja “Datos” seleccionamos la celda G9. A la fórmula resultante, le añadimos el resto de la función que multiplica el importe diario por el número de días, pero esta referenciaes absoluta: el dato siempre está en esta celda y no en otra.. Nos queda una función como esta: “=Datos!G9*$E$3”.

Vamos a probar la aplicación.

En la hoja datos, introducimos el mes: Abril, el año: 2012 y pasamos a seleccionar las claves que se abonarán este mes: para incluirlas en la nómina, pulsamos en la celda E9, escribimos el signo “=” y elegimos de la columna de la izquierda la clave correspondiente: 02 Sueldo. Nos aparece en la celda la fórmula siguiente: “=A9”.


9

Así vamos introduciendo las claves una por una en cada celda.

Escribimos los nombres de los operarios cada uno en una hoja. Pepe, Juan y Pedro, respectivamente. Introducimos el dato de los días de trabajo: 21, 20 y 21 respectivamente y la hoja se habrá calculado automáticamente.

Cambiando datos en la hoja “Datos”, vemos que dinámicamente se modifican los datos de las otras tres Hojas de forma automática.

Pero la referencia a celdas no se queda en el ámbito del libro. Podemos hacer referencia a celdas que se encuentren en cualquier otro libro, dotando así de una potencia al cálculo limitada solamente por la cantidad de memoria disponible.

Para ver ese tipo de relación no vamos a emplear un largo ejemplo. Solo veremos que podemos hacerlo con un sencillo y breve ejemplo.

Abrimos dos Libros de Excel y les nombramos Libro1.xls y Libro2.xls. En Libro2.xls escribimos los datos que aparecen en la imagen de la izquierda.

Pasamos a trabajar en el archivo Libro1.xls. en su Hoja1, en la celda A2

escribimos el signo “=” y volvemos a situarnos en Libro2.xls. Seleccionamos la celda A1 y aceptamos la fórmula. En la celda A2 de Libro1.xls aparece la fórmula “=[Libro2.xlsx]Hoja1!$A$1”. El significado de esta fórmula es el siguiente: apunta a la celda A1 de la Hoja1 del libro Libro2.xls. el nombre del libro o archivo se indica entre corchetes, a continuación se indica el nombre de la Hoja, seguida de un signo de admiración “!” y por último la celda a la que se referencia. El introducir la fórmula con ayuda del ratón nos da un resultado con referencia absoluta pero esto se puede modificar. Copiemos esta celda a las celdas A3 y A4 arrastrando la esquina inferior derecha de la celda.

Nos situamos en la celda A8 de la Hoja1 del libro Libro1.xls y escribimos el signo “=”. Pasamos a la hoja1 del libro Libro2.xls y seleccionamos la celda A3. Aceptamos la fórmula y en la celda A2 de Libro1.xls aparece la fórmula: “=[Libro2.xlsx]Hoja1!$A$3”. Editamos la fórmula en la barra de fórmulas situando el ratón sobre “$A$3”. Cuando el puntero se cambie a barra vertical, pulsamos F4 tres veces y la formula quedará “=[Libro2.xlsx]Hoja1!A3”; referencia relativa. Si copiamos esta

celda a las contiguas inferiores, nos quedará tal como aparece en la imagen.


10


Operaciones con celdas en hojas de calculo

Documents

Transcript of Operaciones con celdas en hojas de calculo