creaciÓn de fÓrmulas - grupoconforsa.com · como hemos visto en algunos ejemplos de anteriores...

Excel 2010 Creación de Fórmulas

8.1

UNIDAD

DIDÁCTICA

CREACIÓN DE FÓRMULAS Los objetivos de este tema son:

1. Comprender la utilidad de las fórmulas en Excel para resolver operaciones.

2. Entender los operadores que se utilizan en la confección de una fórmula y conocer la

prioridad de éstos.

3. Escribir fórmulas y editar fórmulas, tanto en la barra de fórmulas como en la celda.

4. Comprender la utilidad de las referencias relativas, absolutas y mixtas a la hora de escribir

una fórmula.

5. Utilizar referencias de datos de otras hojas del libro o de otros libros.

8.1. INTRODUCCIÓN

Una vez introducidos los datos en una hoja podemos crear una expresión o fórmula que realice

cálculos sobre éstos para obtener resultados. En este tema veremos cómo crear fórmulas

utilizando referencias a cedas, nombres de rango, operadores y constantes.

Una de las utilidades más importantes de las fórmulas dentro de Excel es evitar tener que

recalcular operaciones, es decir cuando se cambian los datos de una hoja, las fórmulas que

contengan referencias a esos datos pueden recalcularse automáticamente.

Por ejemplo si A3 contiene la suma de las celdas A1 y A2, al cambiar el valor de estas

automáticamente Excel realiza la suma con los nuevos datos.


8.2

PROFUNDIZA

Se puede configurar la posibilidad de ver la barra de fórmulas o no, para ello basta con

activar o desactivar la opción Barra de Fórmulas de la ficha Vista.

Con la combinación de teclas Alt + º se ven las fórmulas que contienen las celdas de

una hoja:

O también en Archivo Opciones Avanzadas Mostrar opciones para esta hoja.


8.3

8.2. OPERADORES

Los operadores son símbolos que se utilizan para realizar cálculos, y que se escriben dentro de

la fórmula, un ejemplo sencillo de un operador es el símbolo de suma +, en una fórmula se

podría utilizar escribiendo =A1+A2.

Se utilizan en Excel los operadores, para realizar operaciones aritméticas, comparar datos,

trabajar con texto, etc.

Como hemos visto en algunos ejemplos de anteriores temas, hemos utilizado los símbolos dos

puntos (:) y punto y coma (;), al hablar de rangos. También se utilizan estos símbolos dentro

de las fórmulas, y se llaman operadores de referencia.

El operador de referencia dos puntos (:), delimita un rango que hace referencia a todas las

celdas que están entre las dos escritas, por ejemplo: A1:A3 incluye las celdas A1, A2 y A3 de

tal forma que se puede utilizar una fórmula que sea =SUMA(A1:A3) y realice la suma de todas

esas celdas.

En cuanto al operador de referencia punto y coma (;), se utiliza para combinar varias

referencias en una sola, por ejemplo: =SUMA(A1:A3;C1:C3), es decir realizará la suma de las

celdas A1, A2, A3,C1, C2, C3, siendo rangos independientes A1A3 y C1C3.

También hay otros operadores que se pueden usar en las fórmulas, en la siguiente tabla se

pueden ver los operadores más utilizados.


8.4

Una vez estudiados los operados más utilizados, debes saber que en una fórmula pueden

utilizarse varios operadores y también combinar varios tipos de éstos.

Pero si se utilizan varios operadores, se debe tener en cuenta el orden de éstos, ya que Excel

ejecutará las operaciones en el orden de precedencia que se indica en la siguiente tabla.

Si una fórmula contiene operadores con la misma precedencia (por ejemplo, si una fórmula

contiene un operador de multiplicación y otro de división), Excel evaluará los operadores de

izquierda a derecha.


8.5

Por último para cambiar el orden de evaluación, se escribe entre paréntesis la parte de la

fórmula que se calculará en primer lugar. Por ejemplo, la fórmula =5+2*3 da un resultado de

11 porque Excel calcula la multiplicación antes que la suma. La fórmula multiplica 2 por 3 y, a

continuación, suma 5 al resultado.

Pero si escribimos =(5+2)*3 el resultado será 21.

Veamos otro ejemplo con varios operadores.

Siguiente paso >CAPTIVATE derecha CP100108-02-1

Por ejemplo, si en la celda A1 se introduce =C1+24, se realizaría una suma del valor de la celda

C1 más 24, como la celda C1 vale 10, el resultado de la fórmula sería 34.

Se pueden utilizar varios operadores, además debes tener en cuenta el orden de éstos y añadir

la utilización de paréntesis en las fórmulas en caso necesario.


8.6

8.3. ESCRITURA DE FÓRMULAS

Como hemos podido comprobar en los anteriores apartados, una fórmula es una ecuación que

efectúa cálculos con los valores de las celdas de una hoja de cálculo.

Una fórmula siempre comienza por un signo igual (=), por lo tanto para escribir una

fórmula, primero se selecciona la celda donde queremos obtener el resultado y escribimos el

signo igual.

Al escribirlo Excel interpreta que a continuación vamos a introducir valores, referencias a

otras celdas, operadores o funciones para obtener un resultado.

Una vez introducidos los datos pulsamos Intro.

En este apartado vamos a ver cómo crear fórmulas sencillas utilizando valores, referencias a

otras celdas y operadores, dejaremos para el siguiente tema la creación de fórmulas con

funciones.

Un primer ejemplo de fórmula con valores constantes sería escribiendo: =4+3, el resultado

es 7. Hemos utilizado dos valores constantes y un operador suma.

Otro ejemplo sería =4^2, el resultado es 16, 4 elevado a 2, dos valores constantes y el

operador exponenciación.

Hasta ahora hemos creado sólo formulas como si fuera una calculadora, pero la utilidad de Excel

está en utilizar referencias a celdas con valores.

Por ejemplo, si todos los días se tiene que rellenar una columna con las ventas de tres tiendas,

será necesario crear una fórmula que realice la suma total para no tener que repetir la

operación de sumar a diario.


8.7

En este ejemplo hemos realizado una suma con referencias a celdas utilizando el

operador suma.

Imagina que además se necesita calcular un porcentaje del total de las ventas, se podría

escribir la fórmula =C7*20%. En este caso tendríamos una referencia a una celda C7, un

operador * y un valor constante 20%.


8.8

8.4. MODIFICACIÓN DE FÓRMULAS

Una vez creadas las fórmulas puede que se necesite modificar datos de éstas, o se necesiten

eliminar.

Esto se puede hacer desde la misma celda que contiene la fórmula o desde la barra de

fórmulas.

Para modificar la fórmula desde la barra de fórmulas se hace clic en el lugar donde hay

que cambiar el dato y se escribe el nuevo, por ejemplo para cambiar el porcentaje de la fórmula

del ejemplo anterior del 20% al 30%, se hace clic delante de 20%, se pulsa la tecla SUPR para

borrar los datos, se escriben los nuevos 30% y se pulsa INTRO.

Para modificar la fórmula desde la celda, se hace doble clic en la celda y se selecciona en el

lugar donde hay que cambiar el dato, después se repiten los pasos anteriores. Ahora se cambia

el porcentaje al 40%.


8.9

Por último para eliminar la fórmula se selecciona la celda y se pulsa SUPR.

PROFUNDIZA

Otra forma de escribir fórmulas para trabajar con referencias a celdas, es utilizando el ratón

para seleccionar las celdas de donde se quiera tomar los datos. Es decir, en vez de escribir el

nombre de la celda en la fórmula sólo se debe seleccionar con el ratón.

El resultado de la celda C7, es la suma de C3+C4+C5, para ello se selecciona la celda C7, se

escribe el símbolo (=), con el ratón se selecciona la celda C3, se escribe el símbolo (+), se

selecciona la celda C4, se escribe (+), se selecciona C5 y se pulsa INTRO por último.


8.10

8.5. REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS

En el apartado anterior hemos aprendido a crear fórmulas utilizando referencias a otras celdas,

es decir el resultado de la fórmula se obtiene según los datos de otras celdas.

Además en el ejemplo anterior una fórmula con referencias era =C3+C4+C5, pero qué ocurre si

por ejemplo se quiere copiar esa fórmula en otra celda para utilizarla como suma.

Para ver qué ocurre, en la columna C escribimos las ventas del Lunes y en la columna D las

nuevas ventas del Martes.

Copiando la fórmula de la celda C7 y pegándola en la celda D7, se puede ver que

automáticamente se ha realizado la suma de la columna D, es porque al hacer referencia a las

celdas se ha hecho de forma relativa.


8.11

Según se quiera trabajar con las referencias de las celdas en Excel se puede utilizar:

1. Referencias Relativas

Se utilizan para denominar a la celda que contiene el dato, y se escriben como hemos visto

hasta ahora, la letra de la columna y el número de fila C4.

Se denominan relativas porque se basan en una posición relativa de la celda, en el ejemplo

anterior hemos visto que al copiar la fórmula, inmediatamente cambia la columna a sumar, es

decir Excel ajusta automáticamente las nuevas referencias de la fórmula.

Puede crear una día más de ventas, Miércoles en la columna E, y arrastrando la fórmula de C7

para copiarla en la celdas D7 E7, se podrá ver cómo realiza la suma.


8.12

2. Referencias Absolutas

Las referencias absolutas por el contrario, al copiar o mover la fórmula, hacen que

permanezcan fijas las celdas que hemos puesto como absolutas.

Para hacer una celda absoluta, basta con escribir el símbolo de ($) delante de la letra de la

columna y fila. Por ejemplo $C$4.

Siguiendo con el ejemplo anterior si se observa la celda C8, ahora hemos escrito =C7*$A$8, de

forma que multiplica el valor de las ventas de cada día por el porcentaje que contiene la celda

A8 que en este ejemplo es 30%, pero puede cambiar.

Para calcular el porcentaje del 30% de cada día, simplemente se tendrá que arrastrar la celda

C8 hacia la derecha. Se puedes ver cómo multiplica el total de cada día siempre por el

porcentaje escrito en la celda $A$8, porque está en absoluto.


8.13

3. Referencias Mixtas

Las referencias mixtas combinan una referencia relativa y otra absoluta, de manera que

permanece fija sólo la fila o columna, por ejemplo en C$4, la columna C es relativa y la fila $ es

absoluta.


8.14

PROFUNDIZA

Para modificar una fórmula con referencias absolutas o mixtas, se selecciona la

referencia y pulsamos F4 una vez para hacer la celda absoluta $C$4, o varias veces para

cambiar de fila a columna.

8.6. REFERENCIAS EXTERNAS Y VÍNCULOS

Hasta ahora hemos visto cómo crear fórmulas utilizando referencias a celdas en la misma hoja,

pero también se pueden utilizar valores de celdas de otras hojas del mismo libro.

Cuando se hace referencia a una celda de otra hoja del mismo libro, se debe especificar el

nombre de la hoja en la que se encuentra seguido del símbolo (!). Por ejemplo si a la Hoja1, le

llamas Enero, la referencia a esa hoja será Enero!, si además haces referencia a la celda C3,

tendrás que escribir Enero!C3.

A estas fórmulas que hacen referencia a otras hojas se les llama fórmulas tridimensionales o

fórmulas 3D, porque además de filas y columnas contienen referencias a las hojas.

Un ejemplo, imagina que tienes un libro que recoge las ventas de cada mes del primer

trimestre del año en una hoja por mes Enero, Febrero y Marzo.

En una hoja resumen quieres hacer la suma de las ventas de cada mes por tienda.

Para ello necesitarás sumar en la hoja resumen los datos de cada hoja mes, en la celda C3 de

cada hoja tienes las ventas del mes, la fórmula quedaría =Enero!C3+Febrero!C3+Marzo!C3,

para la primera tienda.

En la hoja resumen en C3 escribe (=) y con el ratón selecciona la celda con las ventas del mes

y de la tienda, de Enero, escribes el símbolo (+), y repites la acción con el resto de meses.


8.15

Excel también permite utilizar referencias en las fórmulas a otros libros de trabajo, en

este caso se llaman fórmulas de cuatro dimensiones.

En este caso para hacer referencia a otros libros se puede realizar de igual forma que el

ejemplo anterior, con el ratón señalando las celdas.

La forma de escribir la fórmula cuando hace referencia a otro libro es usando los corchetes ([])

en el nombre del libro, por ejemplo [Ventas] seguido de la hoja de referencia Enero! y de la

celda C4, quedando la fórmula =[Ventas]Enero!C4

A la hora de usar estas referencias se debe tener en cuenta si el libro del que se quiere coger

datos está guardado en la misma carpeta o en una diferente, en caso de estar en una

carpeta diferente el nombre del libro irá entre comillas simples (`´), por ejemplo

=`[Ventas.xlsx]Enero´!C4

PROFUNDIZA

Si se quiere conocer las referencias a hojas de otros libros en el libro que se tenga abierto

puedes verlo en Excel 2010 dentro de la ficha Datos grupo Conexiones botón Editar vínculos,

y aparecerá una ventana donde se ven los vínculos que tiene la hoja con otros libros (Origen).


8.16

Pasando el ratón por encima de los botones de la derecha puedes ver la utilidad de éstos.

Actualizar valores: actualiza los datos del libro abierto que contiene referencias, si han sido

cambiados en el libro de origen.

Cambiar origen: permite cambiar el libro de origen del que cogemos las referencias.

Abrir origen: abre el libro de origen que contiene los datos de referencia.

Romper vínculo: elimina la referencia a los datos del libro de origen de forma que la fórmula

pasará a ser un valor constante.

Comprobar estado: comprueba si el vínculo funciona de forma correcta, puede tardar tiempo

si los libros de origen están ubicados en una red por ejemplo.

8.7. RESOLUCIÓN DE ERRORES EN LAS FÓRMULAS

Puede ocurrir que al introducir fórmulas en la hoja de cálculo se obtenga como resultado un

error que empezará por el signo (#).

A veces puede ser un resultado correcto, como verás en los tipos de errores y otras veces se

tendrá que corregir la fórmula para que el resultado esté bien.


8.17

8.7.1. Tipos de errores

#¡N/A!

Error de valor no disponible. Este error se produce cuando una fórmula de búsqueda o

referencia no encuentra ninguna coincidencia exacta en la correspondiente matriz de búsqueda.

Significa que el valor buscado no existe en la matriz de búsqueda o que hay un valor no

disponible para la fórmula o función.

#¡REF!

Error de referencia de celda no válida. Al mover o eliminar celdas se produjo una referencia

de celda no válida, o bien una función está devolviendo un error de referencia, también se

produce cuando se tiene una referencia de celda inválida en la fórmula. Por ejemplo, en la

fórmula: =BUSCARV("art300",A3:B7,3,FALSO), obtenemos #¡REF! ya que no podemos buscar

en la tercera columna de una matriz que solo tiene dos columnas. En esta otra:

=DESREF(Hoja1!A2, -1,0,1,1) también obtenemos #¡REF! ya que no hay ninguna fila encima de

la celda A1. Siguiendo con esta fórmula, si eliminamos la primera fila de la hoja "Hoja1", o si

eliminamos la Hoja1, la fórmula mostrará #¡REF!, ya que se ha "perdido" la referencia a la

celda Hoja1!A2.

#¡NUMERO!

Este se produce cuando ingresamos algún valor no numérico como un argumento de función

que Excel espera que sea argumento numérico (o una referencia a un valor numérico). Otra

posibilidad es ingresar un número inválido, como uno negativo cuando se espera uno positivo, o

un 2 cuando el argumento solo admite 0 ó 1. La fórmula =COINCIDIR(123, B3:B13,3) devuelve

#NUM!, ya que el último argumento de COINCIDIR solo puede ser -1, 0 ó 1.

#¡NOMBRE!

Error de nombre no válido. La fórmula contiene texto no reconocido. Este error lo obtenemos

cuando se escribe mal el nombre de alguna función. También puede surgir cuando utilizamos

alguna función personalizada y tenemos deshabilitadas las macros o el complemento

correspondiente. Otra situación que dispara este error es el escribir mal el nombre de algún

rango nombrado. La fórmula =SUMARSI(A3:A11,"criterio",C3:C11) devuelve #¡NOMBRE!

porque la función SUMARSI no existe (más bien está mal escrita. Lo correcto es SUMAR.SI).

Finalmente puede suceder también que no utilizamos comillas al ingresar un argumento de

texto.


8.18

#¡DIV/0!

Error de división entre cero. La fórmula o función utilizada está dividiendo entre cero o entre

celdas vacías.

#¡VALOR!

Error en un valor. Un valor utilizado en la fórmula es de un tipo de datos erróneo. Similar a

#¡NUMERO!, lo obtenemos cuando el tipo de argumento solicitado por la función, es distinto al

ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función

requiere un rango, o un número cuando la función espera texto.

#¡NULO!

Este es muy poco frecuente. Una fórmula devolverá #¡NULO! cuando la celda de intersección de

dos rangos, no existe. En Excel, el operador de intersección es un espacio en blanco. Por tanto,

la fórmula =A2:D2 J1:J10, devuelve #¡NULO! ya que los rangos A2:D2 y J1:J10 no se

intersectan en ningún punto. En cambio, =A2:D2 C1:C10 devuelve C2, celda común a ambos

rangos.

#############

Otro error común es cuando la celda aparece llena de símbolos #. Esto se debe a que la celda

no es lo suficientemente ancha para mostrar el resultado o bien, cuando contiene una fecha

inválida.

8.7.2. Herramientas de ayuda en la resolución de errores

Excel ofrece la herramienta Auditoría de fórmulas que se puede encontrar en la ficha

Fórmulas.


8.19

En esta opción hay varios botones, vamos a ver algunos de ellos:

• Rastrear precedentes: muestra unas flechas que indican las celdas que afectan al valor

de la celda seleccionada actualmente. Por ejemplo el total de una factura tendría como

precedentes cada una de las partidas a sumar.

• Rastrear dependientes: muestra unas flechas que indican las celdas afectadas por el

valor de la celda seleccionada actualmente. En una factura, el IVA por ejemplo sería un

valor dependiente del total a pagar.

• El botón Comprobación de errores: permite comprobar los errores en las fórmulas que

hay en la hoja de cálculo, para ello elige en el desplegable de este botón la opción

Comprobación de errores... un cuadro de diálogo dará información sobre cada uno de

los errores de la hoja, uno por uno devuelve el error, además explica el tipo de error, en la

siguiente ventana se puede ver que el error es un Error de división entre cero.

• En el botón Ayuda sobre este error puedes obtener información de la ayuda de Excel,

Mostrar pasos de cálculo... te indica los pasos que la fórmula realiza para obtener el

resultado.

• Omitir error mantiene el resultado de la celda, con el error.

La opción Rastrear error marca con unas flechas los datos de celdas que afectan a la celda

contienen un error.


8.20

RESUMEN DEL TEMA

• Los operadores son símbolos que se utilizan para realizar cálculos, y que se

escriben dentro de la fórmula, un ejemplo sencillo de un operador es el símbolo

de suma +, en una fórmula se podría utilizar escribiendo =A1+A2.

• Una de las utilidades más importantes de las fórmulas dentro de Excel es evitar

tener que recalcular operaciones, es decir cuando se cambian los datos de una

hoja, las fórmulas que contengan referencias a esos datos pueden recalcularse

automáticamente.

• Las referencias relativas se utilizan para denominar a la celda que contiene el

dato, y se escribe la letra de la columna y el número de fila C4. Se basan en

una posición relativa de la celda,

• Las referencias absolutas por el contrario, al copiar o mover la fórmula, hacen

que permanezcan fijas las celdas que hemos puesto como absolutas.

• Las referencias mixtas combinan una referencia relativa y otra absoluta, de

manera que permanece fija sólo la fila o columna

• Cuando se hace referencia a una celda de otra hoja del mismo libro, se debe

especificar el nombre de la hoja en la que se encuentra seguido del símbolo

(!).A estas fórmulas que hacen referencia a otras hojas se les llama fórmulas

tridimensionales o fórmulas 3D.

creaciÓn de fÓrmulas - grupoconforsa.com · como hemos visto en algunos ejemplos de anteriores...

Documents