Examinar autor: admin

EJERCICIO 2 DE EXCEL – 1

FÓRMULAS Y FUNCIONES II (continuación)

Poner nombres a referencias

Se pueden crear nombres para establecer referencias a una celda, un grupo de celdas, un valor o una fórmula. El uso de nombres proporciona las siguientes ventajas:

  • Se puede compartir el mismo conjunto de nombres en todas las hojas de un libro de trabajo.
  • Las fórmulas con nombres son más fáciles de leer y recordar. Por ejemplo, es más fácil de leer =Ingresos-Gastos que =B6-A6.
  • Si por algún motivo se cambia la estructura de la hoja de cálculo, se puede cambiar la referencia en un solo lugar y hacer que se actualicen automáticamente todas las fórmulas que utilizan ese nombre.
  • Se puede hacer que Excel cree automáticamente nombres para las celdas, basándose en los títulos de las filas o de las columnas, o bien escribiendo directamente los nombres.

Los nombres deben comenzar por una letra. Los demás caracteres pueden ser letras, números, puntos y caracteres de subrayado. No se permiten espacios. Los pasos a seguir para dar un nombre a una celda o grupos de celdas son:

  1. Se selecciona las celdas o rango de celdas.
  2. Se activa la pestaña “Fórmulas”, y se hace clic en “Asignar Nombre a un

Rango”.

  1. Aparece el cuadro de diálogo “Definir nombre

En Nombre se escribe el nombre que se asignará a las celdas seleccionadas.

En ámbito se puede indicar el alcance que tendrá esta referencia por nombre. Si se indica Libro quiere decir que se podrá utilizar una referencia a este nombre desde cualquier hoja del libro, de lo contrario se puede indicar alguna de las hojas del libro.

En comentario se puede incluir un texto explicativo de a que se está referenciado y en el cuadro Hace referencia a: se muestra la celda o conjunto de celda que se referencian.

Veamos un sencillo ejemplo:

En esta hoja vamos a calcular el Total añadiendole al subtotal el correspondiente Iva.

Para ello en primer lugar vamos a ponerle un nombre a la celda B1, que es donde se encuentra el porcentaje de iva que vamos a aplicar.

Seleccionamos la celda B1.

A continuación seleccionamos la ficha Fórmulas y hacemos clic en Asignar nombre a un rango, nos aparece el siguiente cuadro:

Excel nos propone el nombre que vemos en la figura, si deseamos otro nombre simplemente lo introducimos. En nuestro caso dejamos el mismo y pulsamos Aceptar.

Ahora nos situamos en la celda E4 donde vamos a introducir la fórmula. Escribimos =D4+D4*I , justo en el momento de escribir la I de I.V.A. nos aparecerá la siguiente lista:

En esta lista aparecen todos los nombres de rangos que hayamos creado y todas las funciones que comienzan por la i.

Hacemos doble clic en I.V.A. en este caso.

El resultado es el que se ve en la figura de arriba.

Redefinir un nombre

La gran ventaja de utilizar referencias con nombres es la de poder redefinir o cambiar las referencias sin la necesidad de reeditar las fórmulas que utilizan éstas referencias.

Se puede redefinir un nombre, editando su referencia, fórmula o valor constante, actualizando así todas las fórmulas en las que se usa ese nombre.

Para redefinir un nombre:

  • Se activa la etiqueta “Fórmulas”, se hace clic sobre “Administrar nombres”.

  • Aparece el cuadro de diálogo “Administrador de nombres” tal como se ve en la siguiente figura:

Mediante este cuadro de diálogo podemos:

  • crear un nuevo nombre
  • editar o redefinir uno existente
  • eliminar nombres.

 

  • Para redefinir un nombre se selecciona el nombre de la lista de nombres y se hace clic en “editar”. Aparecerá el siguiente cuadro de diálogo:

  • En este cuadro de diálogo podemos hacer las modificaciones que necesitemos y por último pulsamos el botón Aceptar.

 

Crear nombres automáticamente

Excel tiene la capacidad de definir nombres automáticamente, basándose en los rótulos de un rango seleccionado. Para lo cual:

  • Se selecciona el rango incluyendo los rótulos.
  • Se activa la etiqueta “Fórmulas”.
  • Se hace clic en “Crear desde la selección”.

Se abrirá el siguiente cuadro de diálogo:

En este cuadro de diálogo, se deben indicar las filas y/o columnas que se quiere que utilice Excel como nombres, en el ejemplo Excel ya las seleccionó por sí sólo y las propone como válidas.

  • Se pulsa Aceptar.

Excel habrá creado automáticamente los nombres Primer Semestre, Segundo Semestre, Ingresos, Gastos y Resultado. Esto se puede observar si se despliega el cuadro de nombres:

EJERCICIO 1 DE EXCEL – 2

Introducir referencias

Aunque se pueden introducir las referencias en las fórmulas escribiéndolas, la manera más sencilla es, después de escribir el signo igual o un operador (por ejemplo, +, -, <, ;), seleccionar la celda o el rango de celdas del que se desea introducir la referencia por medio del ratón. La selección aparecerá rodeada por una línea punteada llamada borde móvil.

Aunque lo normal será usar referencias a celdas que están en la misma hoja que la fórmula, también se puede hacer referencia a celdas que están en otra hoja del mismo libro o incluso a celdas de otros libros.

Para referenciar a una celda de otra hoja, se le antepone el nombre de la hoja al de la celda, separándolos con el signo !, por ejemplo, Hoja2!A1 hace referencia a la celda A1 de la hoja “Hoja 2”. Esto se puede hacer manualmente o bien simplemente se hace clic en la etiqueta de la hoja a la que se quiere referenciar y luego en la celda de dicha hoja donde se encuentra el valor que se quiere usar en la formula. Este procedimiento se puede apreciar en la siguiente secuencia de imágenes:

Tenemos un libro que en la hoja1 celda A1 tiene el valor 27.

En la hoja2 celda B1 tiene el valor 150.

Lo que queremos lograr es que Excel calcule la suma de estos dos valores y nos deje el resultado en la celda A1 pero de la hoja3. Para lo cual colocaremos el cursor en la celda A1 de la Hoja3. En dicha celda comenzaremos a introducir nuestra fórmula para lo cual escribiremos el signo =

A continuación tenemos que hacer referencia a la celda donde tenemos el primer número a sumar, en nuestro ejemplo es A1 de la hoja1. Para hacer referencia a dicha celda podemos escribir hoja1!A1 manualmente o bien podemos hacer clic en la etiqueta de la hoja1 y luego en la celda A1 de la hoja1:

Podemos observar que la celda A1 queda con un recuadro punteado, esto nos indica que aún estamos en proceso de creación de una fórmula y que nuestra posición real no es esta celda. Esto también es evidenciado por las siguientes señales: en donde deberíamos ver la celda activa en su lugar se muestra la palabra “SUMA”. En donde se debería mostrar el valor de la celda se puede ver como Excel va armando nuestra fórmula y la etiqueta de la Hoja3 se muestra iluminada indicando que la fórmula se encuentra en dicha hoja.

Ahora que ya indicamos la primer celda que compone nuestra fórmula tenemos que decirle a Excel que la queremos sumar, para lo cual escribimos el signo “+”.

Observamos que la línea punteada de la celda desaparece y se ve el signo + en nuestra fórmula. Ahora, siguiendo los pasos anteriores indicaremos la segunda celda a sumarse, hoja2!B1. Haga clic en la etiqueta de dicha hoja y luego en la celda indicada.

Y por último pulsamos la tecla Intro o Enter.

EJERCICIO 1 DE EXCEL – 1

 

FÓRMULAS Y FUNCIONES I

Una fórmula es esencialmente una secuencia de operadores, constantes, nombres, rangos de celdas, etc. que comienza con un signo (=) y produce un valor nuevo. Excel incluye varios cientos de fórmulas, denominadas Funciones, que se han diseñado para realizar muchos tipos de cálculos.

Algunos ejemplos:

=(B4/25)+100

=Ingresos-Gastos

=SUMA(A1:A5)

=SI(A1 > 100;A1+0,1*A1;A1+0,05*A1)

Las fórmulas en Excel comparten algunas propiedades básicas:

  • Todas las fórmulas empiezan con un signo igual.
  • Una vez hemos introducido una fórmula, el valor resultado debe aparecer en una celda.
  • Cuando se selecciona una celda que contiene una fórmula, dicha fórmula aparece en la barra de fórmulas.

 Operadores

Un operador especifica el tipo de cálculo que se desea realizar con los elementos de una fórmula. Los hay de diferentes tipos: aritméticos, de comparación, de texto y referencia.

 

Operadores aritméticos

Los operadores aritméticos permiten realizar las operaciones matemáticas básicas.

Combinan valores numéricos y dan como resultado nuevos valores numéricos.

Los operadores son:

Operador Operación
+ Suma
Resta
* Multiplicación
/ División
% Porcentaje
^ Potenciación

 

Operadores de comparación

Permiten comparar dos valores, produciendo como resultado VERDADERO o FALSO.

Los operadores habituales son:

Operador Operación
= Igual
> Mayor
< Menor
>= Mayor o igual
<= Menor o igual
<> Distinto

 

Los operadores de comparación se pueden aplicar tanto a datos de tipo numérico como a datos de tipo texto y de fecha. Por ejemplo, podemos comparar dos cadenas de caracteres para ver cuál es mayor, o incluso comparar dos cadenas de caracteres para ver si son distintas.

Operador de texto

Se trata del operador &, que permite concatenar dos o más cadenas &, que concatenar dos o más cadenas de texto en una sola continua. Por ejemplo: =»Bienvenido» & «a» & «Excel» mostraría el texto Bienvenido a Excel.

 

Operadores de referencia

Hay tres operadores de referencia:

  • Rango (dos puntos). Hace mención a todas las celdas entre dos referencias, incluyendo éstas. Por ejemplo, B2:E2 incluye las celdas B2,C2,D2 y E2.
  • Unión (punto y coma). Produce una referencia única, resultado de unir otras dos. Por ejemplo, la suma de A1+A2+A3+B1+B2+B3 es equivalente a la suma de A1:A3;B1:B3.
  • Intersección (espacio). Hace referencia a las celdas comunes a dos referencias. Por ejemplo, B7:D7 C6:C8 hace referencia a C7, celda resultado de la intersección.

 

Orden de evaluación de los operadores

Cuando se combinan varios operadores en la misma fórmula, Excel realiza las operaciones en el siguiente orden de prioridades:

 

Operador Operación
: ; [espacio] Operadores de referencia.
Negación
% Porcentaje
^ Potenciación
* / Multiplicación y división
+ – Suma y resta
& Unión de texto
= < > <= >= <> Comparación

 

Los operadores con la misma prioridad se evalúan de izquierda a derecha. Hay que recordar que para indicar números negativos en una fórmula no se deben usar paréntesis, sino el signo menos delante del número.

Siguiendo este orden de preferencia, podemos ver que el operador de multiplicación se evalúa antes que el operador de suma; así, la fórmula: =1+2*5, da como resultado 11 ( 1 más el resultado de multiplicar 2 por 5) y no 15 (la suma de 1 y 2, que es igual a 3, multiplicada por 5).

Para cambiar el orden de evaluación de una fórmula utilizaremos los paréntesis agrupando las expresiones dentro de una fórmula. De hecho, una expresión que esté entre paréntesis se evalúa antes que todos los operadores aritméticos. Por ejemplo:

=1+2*3                  Da como resultado 7

=(1+2)*3               Da como resultado 9

De esta forma, se pueden agrupar paréntesis dentro de otros paréntesis donde la fórmula se evaluará desde los paréntesis interiores a los exteriores.

Para identificar cada paréntesis con su pareja, cuando se está escribiendo la fórmula, Excel le asignará un color a cada pareja de paréntesis.

Fórmulas que producen valores de error

Excel, según el tipo de operadores que haya en una fórmula, espera un cierto tipo de valores. Si se introduce un valor de un tipo diferente al esperado, Excel intentará convertir el valor. En caso de que no lo logre, aparecerá un mensaje de error. Cuando Excel no es capaz de calcular correctamente el valor de una fórmula para una celda, presenta un valor de error en dicha celda. Los valores de error comienzan con el signo de número (#), y pueden ser:

#¡DIV/0!

#N/A

#¿NOMBRE?

#¡NULO!

#NUM!

#¡REF!

#¡VALOR!

######

Estos tipos de errores ya los hemos visto anteriormente.

Tenemos que tener en cuenta que si una fórmula incluye una referencia a una celda que contiene un valor de error dicha fórmula producirá también un valor de error.

Usar referencias en las fórmulas

Las referencias se basan en los encabezados de las columnas y filas de la hoja de cálculo. Permiten identificar celdas o grupos de celdas, y se pueden incluir en fórmulas.

La hoja de cálculo se encuentra dividida por columnas rotuladas con letras de la A a la XFD y 1.048.576 filas rotuladas con números del 1 al 1.048.576. Una celda vendrá especificada por la intersección de una fila con una columna. Esto se conoce como estilo de referencia A1. La referencia de la celda activa aparece en el cuadro de nombres en el extremo izquierdo de la barra de fórmulas, como se muestra en la siguiente figura:

Hay tres tipos de referencias: referencias relativas, referencias absolutas y referencias mixtas.

Referencias relativas

Cuando se utiliza una referencia en una fórmula, ésta puede ser relativa, es decir, indica a Excel la posición que la celda referenciada ocupa respecto a la celda donde se introduce la fórmula. Si se copia la fórmula en otra celda, las referencias relativas que contiene son actualizadas automáticamente respecto a la nueva posición. Por ejemplo, una fórmula situada en la celda A3 que sea =A1+A2, al copiarse en la celda B3 se convertirá en =B1+B2.

Referencias absolutas

Otra posibilidad para indicar en una fórmula la posición de una celda, es hacerlo de modo absoluto. Una referencia absoluta, como $A$1, indica a Excel cómo encontrar otra celda según su posición exacta en la hoja de cálculo. Estas referencias se designan añadiendo el signo del dólar ($) antes de la letra de la columna y del número de la fila, o bien pulsando la tecla F4 después de escribir la referencia en la fórmula.

De este modo, una fórmula situada en la celda A3 que sea =$A$1+$A$2, al copiarse en la celda B3 seguirá siendo =$A$1+$A$2.

Referencias mixtas

En una misma fórmula puede haber direcciones de celdas absolutas y relativas. En este caso, al copiarlas de una posición a otra, sólo variarán las direcciones relativas. Por ejemplo, si en la celda B5 tenemos la fórmula =$B$1*B3 y la copiamos a la celda C5, la fórmula cambiará a la siguiente =$B$1*C3, ya que la columna de datos B está en forma relativa.

 

También podemos dejar como dirección absoluta una fila o una columna de una determinada celda si escribimos el signo dólar sólo a uno de los dos datos que hacen referencia a una celda. Por ejemplo, si en la columna B escribimos $B5, hará referencia absoluta a la columna B, sin embargo la fila 5, variará en el momento en que copiemos en otro lugar el contenido de esta celda.

Podemos convertir una dirección de celda relativa en absoluta mediante la tecla F4. Al pulsarla, colocará los signos de dólar necesarios a la dirección de la celda sobre la que está el cursor. Es decir, si tenemos la fórmula =B1/B3+B2 y queremos que, al copiarla, la celda B1 no cambie, colocaremos el cursor sobre la B de B1, y pulsaremos la tecla F4. De este modo la fórmula quedaría =$B$1/B3+B2.

Si pulsamos sucesivamente la tecla F4 sobre una celda que contenga una fórmula, aparecerán los tres tipos de referencia. Es decir, si sobre B1 pulsamos F4, aparecerá $B$1 (referencia absoluta), si volvemos a pulsar F4 aparecerá B$1 (referencia mixta), si volvemos a pulsar F4 aparecerá $B1 (referencia mixta) y si por último volvemos a pulsar F4 aparecerá B1 (referencia relativa).

Para poder utilizar la tecla F4, lógicamente, es necesario tener la fórmula ya escrita.

Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!