Miguel Ángel García López

Introducción a Excel Excel es una hoja de cálculo integrada dentro de la suite de Microsoft Office junto con Microsoft Word, PowerPoint, Access.. Una hoja de cálculo es una aplicación que permite introducir datos y realizar operaciones en hojas cuadriculadas con una estructura de filas y columnas. La principal cualidad de este programa es la rapidez con la que realiza los cálculos y la amplia variedad de funciones y aplicaciones que integra para realizarlos. Al fichero creado por la hoja de cálculo se le llama libro de trabajo que es simplemente un conjunto de hojas de cálculo agrupadas en un mismo fichero, que normalmente versarán sobre el mismo tema. Cuando abrimos Excel aparece por defecto un libro con tres hojas a las que se les podrá añadir y quitar hojas, vincular datos entre todas las hojas, realizar cálculos matemáticos, filtrar datos, realizar gráficos, imprimir los resultados…. La unión de una fila con una columna se llama celda, a la celda en la que escribimos datos o introducimos una fórmula o función la llamamos celda activa.

Cuando abrimos Microsoft Excel nos encontramos con esta pantalla, cuyos elementos son: Barra de Título Barra de menú Barra Herramientas estándar Barra formato Barra de fórmulas

Barra de etiquetas

1

Miguel Ángel García López

Desplazamiento en Excel Dentro de un libro de trabajo existen varias formas de desplazarse a través de las hojas del libro, y dentro de una hoja. Cuando abrimos Excel por defecto aparecen tres hojas, posteriormente se pueden añadir y quitar las hojas que consideres oportunas. Desplazamiento entre hojas. En la parte inferior izquierda de la pantalla está la barra de etiquetas. Allí aparecen las hojas de que consta el libro, en este caso tres. La hoja activa es la que está en negrita, en este caso la Hoja1.

Para cambiar de hoja simplemente hacemos clic sobre cualquier pestaña, si haces clic sobre la pestaña Hoja2, cambiará a esta hoja. Si el número de pestañas es muy alto y no se ven todas en la barra de etiquetas puedes moverte utilizando los botones de la izquierda Para ver a partir de la Hoja1. Para ver la hoja anterior a la que estamos viendo. Para ver la hoja siguiente a las que estamos viendo Para visualizar las últimas hojas. También se pueden utilizar combinaciones de teclas para realizar desplazamientos dentro del libro de trabajo, como pueden ser: MOVIMIENTO

TECLADO

Hoja Siguiente

CTRL+AVPAG

Hoja Anterior

CTRL+REPAG

Movimientos dentro de la hoja Cada hoja de trabajo en Excel consta de 65536 filas por 256 columnas, es evidente que no se puede ver una hoja en su totalidad. Para desplazarnos por la hoja existen varias combinaciones de teclas que nos permitirán movernos de forma rápida. Las combinaciones son las siguientes:.

MOVIMIENTO

TECLADO

Celda Abajo

FLECHA ABAJO

Celda Arriba

FLECHA ARRIBA

Celda Derecha

FLECHA DERECHA

Celda Izquierda

FLECHA IZQUIERDA

Pantalla Abajo

AVPAG

Pantalla Arriba

REPAG

Celda A1

CTRL+INICIO

Primera celda de la columna activa FIN

FLECHA ARRIBA

Última celda de la columna activa

FIN

FLECHA ABAJO

Primera celda de la fila activa

FIN

FLECHA IZQUIERDA o INICIO

Última celda de la fila activa

FIN

FLECHA DERECHA

2

Miguel Ángel García López

Tipos de datos. En una Hoja de Cálculo, los distintos TIPOS DE DATOS que podemos introducir son: VALORES CONSTANTES Es decir, un dato que se introduce directamente en una celda. Puede ser un número, una fecha u hora, o un texto. NÚMEROS Para introducir números puedes incluir los caracteres 0,1,2,3,4,5,6,7,8,9. Para escribir un número negativo éste tiene que ir precedido por el signo (-). El carácter E o e es interpretado como notación científica. Por ejemplo, 3E5 equivale a 300000 (3 por 10 elevado a 5). Se pueden incluir los puntos de miles en los números introducidos como constantes. Cuando un número tiene una sola coma se trata como una coma decimal. Si al finalizar un número se escribe €, Excel asigna formato Moneda al número. Si introducimos el símbolo % al final de un número, Excel lo considera como símbolo de porcentaje. Por defecto los números aparecen alineados a la derecha en la celda. FECHA U HORA Para introducir una fecha u hora, no tienes más que escribirla de la forma en que deseas que aparezca. Al igual que los números , las fechas y las horas también aparecen alineadas a la derecha en la celda. TEXTO Para introducir texto como una constante, selecciona una celda y escribe el texto. El texto puede contener letras, números y otros caracteres especiales. Si un texto no cabe en la celda Excel utiliza las celdas contiguas para mostrar el texto, pero solo está almacenado en la celda en la que se comenzó a escribir. En el momento en el que la celda contigua tenga algún tipo de contenido el texto aparecerá cortado. El texto aparece, por defecto, alineado a la izquierda en la celda. FÓRMULAS Está formada por valores constantes, referencias a otras celdas, nombres, funciones, u operadores. Se pueden realizar diversas operaciones como sumar, multiplicar, dividir, etc... La fórmula se escribe en la barra de fórmulas y debe empezar siempre por el signo =. FUNCIONES Una función es una fórmula especial que acepta un valor o valores, realiza unos cálculos con esos valores y devuelve un resultado. Todas las funciones tienen que seguir una sintaxis y si ésta no se respeta Excel nos mostrará un mensaje de error. 1. Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. 2. Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. 3. Los argumentos deben de separarse por un punto y coma ";". Ejemplo: =SUMA(A1:B3) esta función equivale a =A1+A2+A3+B1+B2+B3 Más adelante veremos cómo utilizar funciones.

3

Miguel Ángel García López

Configurar página Para configurar la página en Excel se hace de la siguiente manera, seleccionamos la siguiente secuencia: Menú Archivo > Configurar página Aparece una ventana con cuatro pestañas. Vamos a analizar cada una de las pestañas: Página: en esta pestaña destacamos como más interesantes. Orientación que queremos que tenga para luego imprimirla.(A), Ajuste de escala: Si la página es muy grande y no cabe en el papel que queremos imprimir podemos ajustar la escala por debajo del 100% de tal forma que al realizar la vista preliminar los datos se ven más pequeños pero caben más celdas en la hoja. Si ajustamos la escala por encima del 100% sucederá lo contrario. Ajustar a páginas por ancho y alto: cambiando el ajuste cambia la impresión podemos poner 2 páginas por ancho por 1 de alto, o cualquier otro ajuste para imprimir en un folio páginas de la hoja de cálculo.

Márgenes: Seleccionamos los distintos márgenes que queremos para la hoja, podemos definir también márgenes para el encabezado y pie de página, y centrar la página horizontal o verticalmente.

4

Miguel Ángel García López

Encabezado y pie de página: A través de esta pestaña se pueden personalizar tanto el encabezado como el pie de página: la siguiente ficha muestra más detalladamente las opciones.

Hoja En esta pestaña tenemos varias opciones destacamos: Área de impresión: que nos permite definir que zona dentro de la página se va a imprimir. Imprimir: donde podemos seleccionar que aparezcan las líneas de división entre celdas, los títulos de filas y columnas, imprimir en blanco y negro o calidad de borrador, así como el orden que queremos que imprima.

5

Miguel Ángel García López

Encabezado y pie de página Para configurar el encabezado y pie de página se hace de la siguiente manera, seleccionamos la siguiente secuencia: Menú Archivo > Configurar página Seleccionamos la pestaña Encabezado y pie de página de la ventana Configurar página

En esta pestaña podemos introducir un texto en el encabezado o pie entre las opciones que por defecto nos ofrece el programa, para ello hacemos clic sobre la lista despegable que proceda Aparecen tres botones: Imprimir: imprime la hoja activa. Vista preliminar: para ver como se imprimiría el documento Opciones: Abre la ventana de configuración de la impresión de acuerdo con la impresora que tengas instalada. Si queremos personalizar en encabezo o pie pulsamos sobre la opción personalizar encabezado o pie de página. Se abre una nueva ventana en la que podemos personalizar el encabezado o pie, según hayamos elegido. Aparecen tres espacios Sección izquierda, central o derecha, de tal forma que lo que escribamos en cada sección así aparecerá alineado al imprimir la hoja. Hay que tener en cuenta que el encabezo y pie solo se ven al imprimir o en la opción vista preliminar, cuando estés trabajando en la hoja no serán visibles

6

Miguel Ángel García López

Las opciones para personalizar son las siguientes: Puedes personalizar la fuente del encabezado Inserta el número de página Inserta el número de páginas de que consta la hoja Inserta la fecha Inserta la hora

Inserta la ruta donde está guardado el archivo y el nombre del mismo Inserta el nombre del archivo Inserta una etiqueta Inserta una imagen Si hemos insertado una imagen nos permite configurarla Solo tenemos que posicionarnos sobre la sección que queremos que aparezca el texto (izquierda, derecha o central) y escribir lo que queramos y darle el formato que queramos a través del botón para personalizar la fuente o insertar los elementos que consideremos oportunos. El proceso es el mismo para insertar un encabezado o pie de página. A continuación se muestra un ejemplo de configuración de página

7

Miguel Ángel García López

Formatear Celda Cada una de las hojas que forman un libro de Excel puede formatearse, es decir le podemos dar un formato distinto: Podemos personalizar cada celda, combinar celdas, aumentar o disminuir el ancho de las filas columnas….. Formato celda: para personalizar una celda pulsamos Menú formato > Celda. O bien Ctrl + 1 Aparece una ventana con seis pestañas: Número: elegimos el tipo de dato que queremos que tenga la celda, general, número, moneda… según el tipo de datos aparecerán diversas opciones de configuración. Alineación: Podemos alinear tanto vertical como horizontalmente y se presentan tres opciones: Ajustar texto: Hará el tamaño de la celda más grande o pequeño según la longitud del texto que se haya escrito. Reducir hasta ajustar: Reducirá el tamaño del texto lo necesario para que quepa en la celda que no cambiará de tamaño. Combinar celdas: Teniendo previamente seleccionadas dos celdas contiguas, seleccionando esta opción las convierte en una sola celda. Esto se puede hacer desde el botón combinar celdas de la barra formato

Fuente: Podemos personalizar el tipo de letra Bordes: los cuatro bordes de la celda se pueden personalizar con las opciones que aparecen en esta pestaña, cambiando el grosor, tipo y color. Se puede ver como quedaría la celda. Se puede realizar celda por celda si hemos seleccionado un grupo de celdas de una sola vez. Tramas: Permite personalizar el color de fondo de la celda, y también la trama, que es simplemente un segundo color de la celda. Proteger: Por defecto aparece la celda bloqueada, esto quiere decir que no se puede modificar el contenido de la celda, pero esto solo es posible si hemos protegido la hoja anteriormente. La opción ocultar, como su nombre indica hace desaparecer la celda. Por ejemplo: La siguiente celda está personalizada de la siguiente forma ≈ Número: moneda, símbolo €, con 2 decimales. ≈ Alineación: Horizontal y vertical centrada. ≈ Fuente: Comic Sans, tamaño 14, color rojo, negrita. ≈ Bordes: Color azul con un estilo distinto para cada uno de los cuatro bordes. ≈ Tramas: Color fondo amarillo, trama en franja vertical color verde claro

8

Miguel Ángel García López

Formatear filas y columnas Podemos personalizar las filas y columnas cambiando el alto y ancho, ocultar o mostrar. Personalizar fila: Nos colocamos sobre la fila que deseamos formatear y pulsamos Menú formato > Fila Se despliegan las siguientes opciones: Alto: se abrirá una pequeña ventana donde podemos poner el tamaño que deseamos de alto de fila. No se mide en centímetros sino en puntos, así que debes probar hasta que quede a tu gusto. Autoajustar: Excel ajusta las características de la fila por defecto. Ocultar: desaparece la fila que tienes seleccionada. Mostrar: Muestra la fila que has ocultado antes. Si has ocultado la fila 4 verás que tienes la fila 3 y 5, selecciona las dos filas y después selecciona mostrar y aparecerá de nuevo la fila. Para modificar el alto de varias filas realiza lo siguiente: 1. Selecciona varias filas 2. Coloca el cursos entre dos de ellas, da igual cuales. 3. El cursor cambia a una cruz y aparecerá un recuadro con el ancho actual. 4. Mueve el cursor para cambiar al alto deseado Formatear columna: Nos colocamos sobre la columna que queremos formatear y pulsamos Menú Formato >Columna Se despliegan las siguientes opciones Las opciones de personalización son iguales prácticamente que para la fila. En lugar de cambiar el alto podremos cambiar el ancho, y si has cambiando el ancho de la columna y quieres volver al ancho que se presenta por defecto se realiza seleccionando la opción ancho estándar. Si queremos modificar el ancho de varias columnas el proceso es el mismo que para las filas, seleccionando obviamente las columnas en lugar de las filas.

9

Miguel Ángel García López

Formato Condicional Excel nos ofrece la posibilidad de cambiar el color, estilo, subrayado y tamaño de fuente, así como los bordes de una celda cuando el valor que aparece cumple unas determinadas condiciones. Para ello debemos: 1º Seleccionar la celda o celdas donde queremos que aparezca el formato condicional. 2º. Ir al menú formato, opción formato condicional y aparecerá esta pantalla

3º Elegimos si el formato se lo queremos dar al valor de una celda o a una fórmula

4º Elegimos la condición que queremos que cumpla. En la imagen está seleccionado entre, solo tenemos que poner en las casillas en blanco de la derecha los valores que queramos. Si presionamos sobre la lista despegable tendremos las siguientes opciones: 5º. Pinchamos sobre el botón formato y nos sale una ventana con tres pestañas, en ellas podemos cambiar el formato a la fuente, el color de la misma y el diseño de los bordes de la celda.

6º Se pueden añadir más formatos condicionales a las celdas pulsando sobre el botón añadir.

10

Miguel Ángel García López

Introducción de fórmulas y funciones Las fórmulas son ecuaciones que realizan cálculos con los valores de una hoja de datos. Toda fórmula comienza por un signo igual (=) En la celda A1 quiero que muestre el resultado de sumar tres y cinco, debo poner lo siguiente: =3+5 Esta misma operación la puedo hacer haciendo referencia a las celdas donde están los datos: por ejemplo en A2, escribo 3 y en B2 escribo 5, en C2 escribo lo siguiente: A2+B2 Además puedo utilizar la función correspondiente, en este caso la suma. Escribo =SUMA(A2:B2) A 1

=3+5

2

3

B

C

D

5

=A2+B2

E

F

=SUMA(A2:B2)

3

Para realizar esta última acción es necesario conocer las funciones. Las partes de la consta una función son el signo igual, el nombre de la función y los argumentos: =SUMA(A2:B2) Para introducir una función con el asistente de funciones hacemos lo siguiente: 1. Nos colocamos sobre la celda que queremos que aparezca el resultado. 2. Pulsamos sobre el botón insertar función o bien a través del menú Insertar > función o de la barra de herramientas estándar.

3. Se abre la siguiente ventana en la que puedes hacer lo siguiente:

A B

11

a) Realizar una descripción de la operación que quiero realizar. b) Seleccionar una categoría, por ejemplo, lógicas, matemáticas, financieras…

Miguel Ángel García López

4. Seleccionamos la función que queremos usar. 5. Se abre el cuadro de dialogo para introducir los argumentos de la función

6. Una ver introducidos los argumentos se pulsa el botón Aceptar. Cada función tiene unos argumentos definidos, en este caso, para la función suma en el primer argumento número1 podemos escribir el rango que quiero que sume, no necesito ir introduciendo celda por celda, Al ser una función suma la cantidad de argumentos, Número1, Número2… es ilimitada. Por lo tanto puedo introducir en el primer argumento todo el rango.

Los operadores habituales para la introducción de fórmulas y funciones son: OPERADORES ARITMÉTICOS Suma +

* / % ^ : , ;

OPERADORES DE COMPARACIÓN = Igual

Resta

> < >= Nombre >Definir... Se abrirá el cuadro de diálogo Definir nombre como vemos en la imagen. En el recuadro Nombres en el libro: escribimos el nombre que le queremos dar a la celda. En Se refiere a: escribimos la referencia de la celda como vemos en la imagen. Es importante escribir el signo igual y utilizar referencias absolutas ($A$1). Excel rellena de forma automática el cuadro Se refiere a: con la referencia de la celda activa, por lo que es más cómodo colocarse primero en la celda a la cual queremos asignar un nombre y luego abrir el cuadro de diálogo Definir nombre, así ya tendremos el cuadro rellenado con la referencia correcta.

Aplicar cambios de nombre a referencias Cuando asignamos nombres a diversas celdas, las fórmulas que hagan referencia a estas celdas no se cambian automáticamente sustituyendo la nomenclatura de columna-fila por el nombre. Por ejemplo: Si tenemos la formula =A1+B1 y posteriormente asociamos la celda A1 con el nombre Gasto1 y la celda B1 con el nombre Gasto2. En la fórmula continuará saliendo =A1+B1. Para que los nombres se actualicen en todas las referencias debemos ir a la barra de menús, a Insertar> Nombre y Aplicar.... En el cuadro de diálogo Aplicar nombres que nos aparece debemos seleccionar los nombres que queramos aplicar y hacemos clic en Aceptar. Ahora en la fórmula veremos =Gasto1+Gasto2, que queda bastante más claro si sabemos a qué celdas hacen referencia los nombres Gasto1 y Gasto2.

16

Miguel Ángel García López

Fórmulas Sencillas Excel tiene un amplio abanico de funciones y fórmulas para introducir. Vamos a ver algunas sencillas SUMA: Tiene la siguiente sintaxis =Suma(número1,número2..) Podemos introducir en cada argumento una celda o un rango Ejemplo Tenemos en una hoja el número de personas que consiguen trabajo durante el primer semestre del año en la Región de Murcia En la celda B7 queremos que aparezca el total de personas empleadas. En este caso tenemos dos opciones: 1. Introducir la función suma 2. Al sumar celdas contiguas podemos pulsar el botón autosuma de la barra de herramientas Estándar

A

B

C

1 Enero

E

950

2 Febrero

1.500

1 trimestre

=Suma(B1:B3)

820

2 trimestre

=Suma(B4:B6)

3 Marzo 4 Abril

1.600

5 Mayo

1.400

6 Junio

2.500

Total

D

=Suma(B1:B6)

En la celda E2 queremos sumar los trabajadores contratados en el primer trimestre y en la celda E3 los del segundo trimestre. En este caso debemos escribir la función suma puesto que la celda donde se debe mostrar el resultado no está contigua a las celdas que contienen los datos. RESTA, MUTIPLICACIÓN Y DIVISIÓN. En estos casos no existe una función, se utilizan los operadores - * / para la realización de la fórmula precedido siempre del signo = Ejemplo: Se pretende: a) en la celda B1 que reste 10 menos 2 b) en la celda B2 que multiplique 10 por 2 c) En la celda B3 que divida 10 entre 2 A

B

1 10

=A1-A2

2 2

=A1*A2

3

=A1/A2

17

Miguel Ángel García López

Función máximo, mínimo, promedio Estas tres funciones son muy útiles para realizar estudios a nivel estadístico. La sintaxis es la siguiente: Máximo: selecciona el valor máximo de una serie de datos. =max(número1;número2;…) Mínimo: selecciona el valor mínimo de una serie de datos = min(número1;número2;…) Promedio: calcula la media aritmética de una serie de datos =promedio(número1; número2;) Las tres funciones tienen la sintaxis parecida así que basta con mostrar el cuadro de dialogo de la función MAX, para el resto es igual.

Ejemplo: Vamos a calcular es máximo, mínimo y promedio de las notas obtenidas por cinco alumnos en la materia de Historia del arte A

B

C

D

1

Alumno

Calificación

2

Juan

5

Máximo

=MAX(B2:B6)

3

Luisa

8

Mínimo

=MIN(B2:B6)

4

Marisa

7

Promedio

=PROMEDIO(B2:B6)

5

Federico

6

6

Martín

4

El resultado para cada caso sería: Máximo: 8 Mínimo: 4 Promedio: 6

18

Miguel Ángel García López

Objetivos Buscar objetivo: es el método para encontrar un valor determinado para una celda ajustando el valor de otra. Al escribir una fórmula en una celda, Excel varía el valor de la celda hasta que la fórmula devuelva el valor deseado. Para buscar objetivos pulsamos Menú herramientas > buscar objetivo Ejemplo

Tenemos una hoja en la que tenemos las ventas anuales de nuestra empresa. En la celda E2 hemos escrito la fórmula =Suma(A2:B2) A

B

C

D

1 Trimestre 1 Trimestre 2 Trimestre 3 Trimestre 4 2

25000

50000

35000

45000

E Total 155.000

¿Cuáles deben ser las ventas en el primer trimestre para que el total de ventas sea 180.000 € Debemos hacer lo siguiente: 1. Seleccionamos buscar objetivo del menú herramientas. 2. En el cuadro definir celda: seleccionamos la celda que contiene la fórmula en este caso E2. 3. En el cuadro Con el valor escribimos 180.000 que es el valor que queremos obtener. 4. En el cuadro Para cambiar la celda, escribimos A2 que es la celda que queremos cambiar. 5. Pulsamos aceptar y se abre el cuadro de diálogo Estado de la búsqueda de objetivo con la solución. Si queremos que sea permanente pulsamos aceptar.

19

Miguel Ángel García López

Escenarios Un escenario nos permite realizar análisis ante hipótesis diversas, pudiendo comprobar como el resultado final de la hoja ante cambios establecidos en algunas de las celdas. Excel permite guardar los escenarios y recuperarlos en el momento necesario. Para realizar un escenario pulsamos Menú herramientas > Escenarios Ejemplo.

Tenemos una hoja de cálculo donde vamos a calcular los costes de nuestra empresa. Tenemos agrupados los conceptos de coste. Queremos comprobar como variaría el coste total si cambiamos la cantidad comprada de cada materia prima. ¿A qué importe ascenderán los costes si las unidades vendidas de materia prima A son 3.000 y de materia prima B son 6.500 ? A

B

C

D

1 Concepto

Precio

Unidades

Total

2 Materia prima A

10,50 €

4.500

47.250 €

3 Materia Prima B

20,60 €

3.000

74.160 €

4 Gastos fabricación

150.000 €

5 Mano de obra

60.000 €

6 Otros gastos

100.000 € Total Gastos

7

431.410

Debemos hacer lo siguiente: 1. Seleccionamos escenario del menú herramientas. 2. En el cuadro de diálogo Administrador de escenarios, haz clic en el botón agregar. 3. Damos un nombre al escenario. En este caso Situación 1

4. Selecciona las celdas que quieres que cambien, este caso las celdas donde están las unidades de materias primas. Se hace en la misma ventana anterior en la opción celdas cambiantes.

20

Miguel Ángel García López

5. Pulsa aceptar, se abre el cuadro de diálogo Valores del Escenario, donde puedes cambiar las unidades de cada materia prima a las nuevas

6. En el cuadro de diálogo Administrador de escenarios, pulsa sobre la opción resumen

7. Comprueba que la celda cambiante en el cuadro resumen de escenario es la celda que contiene el total de gastos. En este caso D7

8. Si quisiéramos introducir un segundo escenario repetiríamos los pasos 1,2 y 3. 9. Pulsamos aceptar y se abre el cuadro de diálogo Estado de la búsqueda de objetivo con la solución. Si queremos que sea permanente pulsamos aceptar.

21

Miguel Ángel García López

Sumar.Si La función Sumar.SI nos permitirá sumar un rango de celdas, siempre que el contenido de estas celdas cumpla una determinada condición. Tiene la siguiente sintaxis SUMAR.SI(Rango;criterio;rango_suma) Rango: es el rango de celdas que se desea evaluar, puede ser también una celda. Criterio: condición que determina qué celdas deben sumarse, la condición que se establezca puede ser un número, el resultado de una fórmula, o un texto. Rango_suma: son las celdas que se van a sumar una vez que se cumpla la condición. Si se deja en blanco se sumará las celdas que aparezcan en el argumento rango. Al seleccionar la fórmula en la barra de fórmulas aparece la siguiente caja:

Ejemplo: en la siguiente hoja de cálculo aparecen las ventas realizadas por los vendedores de una empresa. En las celdas B5,C5,D5, aparecerá la suma de las ventas realizadas en cada mes, para este caso se introduce la función suma. En las celdas E2,E3,E4, debe aparecer la suma de las ventas de cada vendedor, pero solo cuando el importe de las mismas ha sido superior a 1.500 €. En este caso debemos introducir la función sumar.si en las celdas E2,E3,E4. A continuación se introduce la fórmula para la celda E2 A 1

B

C

D

E

Enero

Febrero

Marzo

Suma

F

2

Juan

1.000

2.000

1.520 Sumar.Si(B2:D2;”>1500”;B2:D2)

3

Pedro

1.500

2.100

2.210

4

Luis

1.300

2.000

3.220

5

Ventas

Rango: es el conjunto de celdas donde aparece el importe de las ventas. (B2:D2) Criterio: el criterio es que sea mayor DE 1.500; “> 1.500 “ Rango_suma: en este caso es el mismo. Se puede volver a escribir o dejar en blanco.

22

Miguel Ángel García López

Contar.Si La función Contar.SI nos permitirá contar las celdas que en un rango cumplen una determinada condición. Tiene la siguiente sintaxis CONTAR.SI(Rango;criterio) Rango: es el rango de celdas que se desea evaluar, omite las celdas en blanco Criterio: condición que determina qué celdas deben contarse, la condición que se establezca puede ser un número, el resultado de una fórmula, o un texto. Al seleccionar la fórmula en la barra de fórmulas aparece la siguiente caja:

Ejemplo: Un profesor tiene un listado con las calificaciones de los alumnos, desea saber cuántos alumnos han aprobado y cuantos han suspendido A

B

C

CALIFACIÓN

Alumnos aprobados

1

ALUMNO

2

Álvarez Pérez Juan

6

Contar.Si(B2:B6;”>=5”)

3

Bellido López María

4

Alumnos suspensos

4

Carrión Fernández Juana

7

Contar.Si(B2:B6;”1.500;D2*0,1;D2*0,05)

3

Manzanas

1.000

1,00

1.000

50

950

4

Plátanos

3.000

0,95

2.850

300

2.550

=D2-E2

En la celda E2, vamos a calcular el descuento. Para ello vamos a tener en cuenta una condición: Si la cantidad comprada es mayor de 1.500 Kg. se aplica un descuento del 10%, de lo contrario se aplicará un descuento del 5% Para ello en la celda E2, introducimos la función SI de la siguiente forma: Prueba_lógica: B2>1.500 Valor_si_verdadero: D2*0,1 Valor_si_falso:D2*0,05 Después podemos arrastrar la fórmula a las celdas E3 y E4 y de esta forma tenemos calculado el descuento para todos los productos, Podemos utilizar referencias absolutas o relativas en la función si. Una función Si puede tener a su vez varias condiciones Si, por ejemplo vamos a realizar una factura en la que el importe del descuento variará de la siguiente forma: Si el importe bruto es superior a 2.000 € se aplica un descuento del 10% Si el importe bruto superior a 1.000 e inferior a 2.000 € se aplica un descuento del 5% Si el importe bruto es igual o inferior a 1.000 € se aplica un descuento del 3%

24

Miguel Ángel García López

En este caso debemos introducir dos veces la función Si. Ten en cuenta que el número de funciones Si que debes introducir es siempre una menos que el número de condiciones que se establezcan. En este caso tenemos tres condiciones, por lo tanto se introducirán dos condiciones Si. Para escribir la fórmula correctamente pulsamos escribimos en la primera función los siguientes parámetros: Prueba_lógica y Valor_si_verdadero, cuando se escriba el argumento valor_si_falso se introduce nuevamente una función Si.

A 1

B

C

D

Precio

Imp. Bruto

Camisas

15 €

150 €

Unidades Producto 10

2 3

30

Pantalones

20 €

600 €

4

40

Cazadoras

40 €

800 €

6

Importe Bruto

1.550 €

7

Descuento

=SI(D6>2000;10%*D6;SI(D6>1000;D6*5%;D6*3%))

5

Las condiciones pueden establecerse como en este caso por intervalos. Al escribir la función Si debemos tener en cuenta que no es preciso escribir el intervalo en el argumento prueba lógica. En el caso del ejemplo se establecen tres intervalos. Que el importe bruto sea superior a 2000, que esté entre el intervalo de 1.000 y 2.000 o que sea inferior a 2.000. El primer intervalo no presenta problemas: D6>2.000 El segundo intervalo nos dice que el importe bruto debe estar entre 1.000 y 2.000. Lo primero que pensamos es escribir lo siguiente 1.00060000;C2>40000);10%;5%)

E

=Suma(B2:C2)*D2

Podemos calcular el importe de la comisión directamente, introduciendo la formula correspondiente en los argumentos de la función Si. =SI(Y(B2>60000;C2>40000);Suma(B2:C2)*10%; Suma(B2:C2)*5%)

26

Miguel Ángel García López

Función O Esta función, al igual que la función Y es muy útil cuando queremos introducir una función Si que además debe cumplir al menos una condición entre varias para que se considere que el valor es verdadero o falso. Esta función se utiliza cuando las condiciones a cumplir se refieren a conceptos distintos. Para introducir una función O se hace de la misma forma que la función Y. La diferencia radica que mientras que en la función Y es necesario que se cumplan todas las condiciones que se establezcan, en la función O solo es necesario que se cumpla una. Cuando introducimos la función O se abre la ventana de dicha función, en cada argumento, Valor_lógico1, Valor_lógico2… escribimos las condiciones, no olvidemos que solo es necesario que se cumpla una de ellas. Cuando hayamos introducido todas las condiciones, tenemos que volver a la función Si, lo hacemos igual que la función Y. Siguiendo el ejemplo de la función Y. Realizamos una hoja de cálculo en la que calculamos el importe de la comisión de los vendedores de una empresa. En la celda D2 debe aparecer el porcentaje de comisión que se aplicará sobre las ventas de un determinado vendedor. El porcentaje será del 10% si las ventas realizadas en la zona norte superan los 60.000 € o en la zona sur los 40.000, sin no se cumplen las dos condiciones la comisión a aplicar es el 5%. En la ceda E2 se calcula el importe de la comisión, que será la suma de las ventas por el valor obtenido en la celda D2 A 1 Vendedor Miguel 2 Ramírez Candel

B

C

D

Zona Norte

Zona Sur

Comisión

50.000€ 60.000€

=SI(O(B2>60000;C2>40000);10%;5%)

E

=Suma(B2:C2)*D2

Podemos calcular el importe de la comisión directamente, introduciendo la formula correspondiente en los argumentos de la función Si. =SI(O(B2>60000;C2>40000);Suma(B2:C2)*10%; Suma(B2:C2)*5%) Como se puede comprobar la función es prácticamente igual en ambos casos, la única diferencia está en discernir si se tienen que cumplir todas las condiciones que se establezcan o no.

27

Miguel Ángel García López

Gráficos Un gráfico es una representación de una serie de datos. Excel ofrece una gran tipología de ellos permitiendo personalizarlos de la forma que deseemos. Tipos de gráficos:
Líneas: para ver la evolución de una variable en el tiempo.
Sectores: Para comparar valores o porcentajes de varias variables en un momento del tiempo.
Barras: Para ver la evolución de una serie de datos, en este caso interesan más las cifras individuales en cada momento de la variable del gráfico.
Áreas: tiene la misma utilidad que el de líneas.
Dispersión: para comprobar la relación entre dos conjuntos de datos.
Anillos: tiene la misma utilidad que el de sectores.
Superficie: Gráficos en tres dimensiones Elementos de un gráfico
Título del gráfico.
Serie de datos: Son las variables de datos representadas en el gráfico, puede haber una o varias.
Eje de categorías: son los datos en el eje X
Eje de valores: son los datos en el eje Y
Líneas de división: líneas verticales y horizontales que permiten comprobar los valores de la serie.
Rótulos de datos: son los datos que aparecen dentro del gráfico sobre la serie de datos.
Tabla de datos: es la tabla de datos para crear el gráfico
Área de trazado: es el lugar donde se representan las series de datos. Eje valores

Título gráfico

Área de trazado

UNIDADES VENDIDAS 4º TRIMESTRE 500 450 400 350 300 250 200 150 100 50 0

Rótulo de datos

430

Leyenda 250 Unidades vendidas

180

Serie de datos

Octubre

Noviembre

Diciembre

Eje de categorías Líneas de división

Área del gráfico

28

Miguel Ángel García López

Crear gráfico con el asistente: 1. Seleccionar el área a representar. 2. Pulsar el botón asistente para gráficos de la barra de herramientas estándar

3. Selecciona el tipo de gráfico que deseas

4. En el cuadro de diálogo siguiente se muestra el rango de celdas que se mostrarán en el gráfico, podemos modificarlo si no estamos de acuerdo con las que ha seleccionado el asistente.

5. En el siguiente cuadro de diálogo se muestran varias pestañas donde se pueden modificar y personalizar el gráfico en sus elementos fundamentales, Títulos, ejes, líneas de división, leyenda, Rótulo de datos y tabla de datos.

29

Miguel Ángel García López

6. En el cuadro de diálogo siguiente debes elegir la forma de insertar el gráfico, bien en una hoja nueva o como un objeto que podrás mover en la hoja activa.

Crear gráfico manualmente: 1. Selecciona el rango de datos a representar. 2. Presiona F11, se crea automáticamente un gráfico de columnas en una hoja independiente que se puede modificar.

Modificar un gráfico: Una vez realizado el gráfico bien con el asistente o manualmente se puede modificar. Simplemente pulsando doble clic sobre la zona del gráfico que deseemos cambiar, eje, serie de datos, se abre un cuadro de diálogo con las opciones de personalización para cada elemento. Podemos utilizar también la barra de herramientas gráfico, para ello pulsamos:

Menú ver > > Barra de herramientas > Gráfico

30

Miguel Ángel García López

BuscarV La función BuscarV nos permitirá encontrar datos y valores que están en distintas partes de nuestro libro, bien en la misma hoja o en otra distinta. La utilidad que tiene es encontrar valores en columnas a partir de la referencia establecida en una celda. La función BuscarV nos devuelve el valor buscado a partir del dato introducido en la primera columna tiene la siguiente sintaxis: BuscarV(valor_buscado;matriz_buscar_en;indicador_columnas:ordenado)

Valor_buscado: hacer referencia a la celda donde está el dato que queremos buscar. Matriz_buscar_en: es el rango en el que tenemos los datos que queremos la fórmula busque, debe ser un rango formado por varias filas y varias columnas. Indicador_columnas: es el número de columna dentro de la matriz a buscar que queremos que muestre el valor. Si el número es 1, la función devuelve el valor de la primera columna, si se pone el valor 2, devolverá el valor de la segunda columna… así sucesivamente. Ordenado: para que busque el valor exactamente se escribe “Falso”, si queremos que busque por aproximación escribiremos “Verdadero” A

B 2

1

C

D

E

F

IVA

total

=buscarV(B1;(A4:F5);2;falso)

2 Código

3

Cliente

Factura

Importe

4

1

Nallabe SA

34/08

1.000

160

1.160

5

2

Frumeca SA

35/08

5.000

800

5.800

En la celda B1 escribimos cualquier código de cliente, en la celda C1 podemos hacer que aparezca el nombre, para ello utilizamos la función BuscarV con los siguientes parámetros: Valor_buscado: B1 Matriz_buscar_en: (A4:F5), no se seleccionan las celdas que tienen los títulos. Indicador de columnas: 2 ( la primera columna sería el código) Ordenado: Falso.

31

Miguel Ángel García López

BuscarH La función BuscarH tiene la misma utilidad que la función BuscarV, sólo varía en una cosa. En lugar de buscar en la matriz a través de las columnas, lo hace a través de las filas BuscarH(valor_buscado;matriz_buscar_en;indicador_filas:ordenado)

Valor_buscado: hacer referencia a la celda donde está el dato que queremos buscar. Matriz_buscar_en: es el rango en el que tenemos los datos que queremos la fórmula busque, debe ser un rango formado por varias filas y varias columnas. Indicador_filas: es el número de fila dentro de la matriz a buscar que queremos que muestre el valor. Si el número es 1, la función devuelve el valor de la primera fila, si se pone el valor 2, devolverá el valor de la segunda fila… así sucesivamente. Ordenado: para que busque el valor exactamente se escribe “Falso”, si queremos que busque por aproximación escribiremos “Verdadero” Ejemplo tenemos una hoja donde registramos las faltas de asistencia de los alumnos, queremos que colocando el nombre del alumno en la celda B2, en la celda C2 nos muestre el total de faltas A

B Natalia

1

C

D

E

F

=buscarH(B1;(B4:F7);5;falso)

2 3

Nombre

Juan

Luisa

Natalia

Marina

Pedro

4

Enero

5

8

3

0

3

5

Febrero

7

3

9

2

1

6

Marzo

3

0

0

1

2

7

Total

15

11

12

3

6

32

Miguel Ángel García López

Funciones financieras (I): Valor futuro y valor actual Valor futuro (VF): Permite obtener el valor final que se recibe de una inversión, su sintaxis es: VF(tasa, nper, pago, valor actual, tipo)

Tasa: es el tipo de interés al que está la inversión. Nper: plazo de la inversión. Pago: abono periódico que se realiza Va (valor actual): valor actual que se desembolsa al principio de la operación Tipo: si se pone 1 se entiende que los pagos se realizan al principio del periodo, si el valor es cero o nada se entiende que los pagos se realizan al final. La tasa y nper deben estar el mismo periodo de tiempo, es decir si tomamos una tasa mensual el valor de nper debe ser mensual, si es anual los dos tienen que tener valor referente a anualidades. En excel la tasa mensual se calcula dividiendo la tasa anual entre 12. A

B

1

Tipo interés

2

Años

3

Pago

C

D

E

3%

Tipo interés

2,5%

5

Años

30

Pago

500

F

4

Valor inicial

2.000 €

Valor inicial

4.000 €

5

Valor final

=VF(B1;B2;-B3;-B4;0)

Valor final

=VF(E1;E2;-E3;-E4;0)

En la celda B5 vamos a calcular el valor final de una inversión, cuyo capital invertido es de 2.000 €, el tipo de interés anual es del 3%, los años de la inversión son 5. El valor inicial debe ponerse siempre con signo negativo, por eso aparece la celda –B4, en la celda pago no hay nada pero al introducir la fórmula también se pone, y además con signo negativo –B3. El resultado es 2.318,55 € En la celda E5 vamos a calcular el valor final de un plan de pensiones en el que abrió con un importe de 4.000 €, cada año se aportan 500 € y el plazo hasta rescatar dicho plan es de 30 años. El tipo de interés es del 2,5%. Como en el ejemplo anterior es necesario poner las celdas de pago y valor actual con signo negativo. El resultado es 30.341,62 €

33

Miguel Ángel García López

Valor actual(VA): Nos permite obtener el importe que debemos invertir si queremos obtener un valor final determinado. VA(tasa, nper,pago,vf, tipo)

Los datos significan lo mismo, el cálculo es distinto nos calcula el valor actual que debemos invertir si el valor futuro es igual a un importe concreto. El resultado es negativo porque es el dinero que debe salir de nuestra caja para ser invertido. El argumento pago se puede interpretar de dos formas, bien el dinero que debemos aportar cada periodo, por ejemplo en un plan de pensiones, o bien los intereses que nos dan en cada periodo, que será un pago, pero en este caso para la entidad bancaria. A

B

1

Tipo interés

2,5%

2

Años

20

3

Pago

500

4

Valor final

5

Valor inicial

C Vamos a calcular el valor inicial plan de pensiones a 20 años, cuyo importe final es 22.604,03 € y en el que aportamos 500 € en cada año.

22.604,03 € =VA(B1;B2;-B3;B4;0)

34

Miguel Ángel García López

Funciones financieras (II): Número de periodos, Potencia Número de periodos (Nper): Nos permite obtener el número de periodos que deben transcurrir para obtener un valor final determinado

Nper(tasa; pago; Va;Vf; tipo) Tasa: es el tipo de interés al que está la inversión. Pago: abono o pago que se realiza en cada periodo. Si la celda la colocamos con signo negativo significará pago Va (valor actual): valor inicial de la operación Vf (valor final): valor al finalizar la operación Tipo: si se pone 1 se entiende que los pagos se realizan al principio del periodo, si el valor es cero o nada se entiende que los pagos se realizan al final. A

B

1

Tipo interés

2 3

C

D

E

3%

Tipo interés

2,5%

Pago

600

Aportación

30

Valor inicial

3000

Valor inicial

500

4

Valor final

0

Valor final

4.000 €

5

Nº periodos

=Nper(B1,-B2;B3;B4)

Nº períodos

=Nper(E1;E2;E3;E4)

F

En la celda B5 vamos a calcular el número de periodos necesarios para devolver un préstamo de 3.000 € si en cada periodo devolvemos 600 €. En la celda E5 vamos a calcular el número de periodos necesarios para obtener un valor final de 4.000 € a un tipo de interés del 2,5% teniendo en cuenta que en cada periodo aportamos 300 €.

35

Miguel Ángel García López

Hay que tener en cuenta que el tipo de interés es anual, por lo tanto el número de periodos que nos dan son años, deberemos convertir el tipo de interés a mensual si queremos obtener el número de periodos reflejados en meses.

Potencia Su uso como queda claro sirve para elevar un número a una potencia determinada. Pero se puede usar también para cambiar el tipo de interés de anual a semestral, trimestral, mensual. Su sintaxis es la siguiente: Potencia(Número;Potencia)

Número: Para el uso que le vamos a dar será la celda donde esté el tipo de interés anual Potencia: En este caso será la fracción de año a la que vamos a cambiar el tipo de interés, ½ para interés semestral, ¼ para interés trimestral, 1/12 para interés mensual.

A

B

1

Interés Anual

5%

2

Interés mensual

=Potencia(1+B1;1/12)-1

36

Miguel Ángel García López

Funciones financieras (II): Amortización de un préstamo Las funciones a utilizar para el cálculo de un préstamo son las siguientes: Para saber la cuota anual a pagar de un préstamo utilizamos la función pago:

Pago pago(tasa;nper;va;vf;tipo)

Tasa: tipo de interés del préstamo Nper: número de años de duración del préstamo Va: valor inicial del préstamo. Vf: valor al final de la duración del préstamo, será cero y por lo tanto se puede omitir Tipo: si no se pone nada se entiende que las cuotas se pagan al inicio de cada periodo, si se pone uno, las cuotas se pagarían al final de cada periodo.. Si queremos calcular la cuota mensual utilizaremos la función potencia

Pagoprin pagoprin(tasa;período;nper;va;[vf];[tipo])

37

Miguel Ángel García López

Pagoint pagoint(tasa;período;nper;va;vf;tipo)

Los parámetros significan lo mismo que para la función pago. El único parámetro nuevo es período que hace referencia al momento en el que calculamos el interés. Estará entre 1 y nper. Vamos a realizar un cuadro de amortización de un préstamo de importe 25.000 € a doce años, al 5% de interés. En el rango (A1:B3), introducimos los datos tal y como están en el modelo. Como queremos calcular el cuadro de amortización completo, tenemos que introducir: En el rango (A7:A18) se introduce los periodos de pago del préstamo. En el rango (B7:B18) se introduce la cuota anual de pago, el valor del préstamo debe ponerse en negativo =PAGO($B$2;$B$3;-$B$1) En la celda C7 vamos a calcular el capital amortizado para el primer año, se introduce de la siguiente forma: =PAGOPRIN($B$2;A7;$B$3;-$B$1) En la celda D7 se introduce la función para calcular el interés pagado en el primer periodo =PAGOINT($B$2;A7;$B$3;-$B$1) En la celda E7 calcularemos el capital acumulado que para el primer año es igual a la celda C7. El capital pendiente para el primer año será igual al capital prestado menos el capital amortizado el primer año es decir B1-C7. Para la celda B7 la cuota es la misma, que en B6 Para la celda C7, arrastrando la función se calcula automáticamente el pago del capital Para la celda D7, que calcula el interés, se puede arrastrar igual que para el pago del capital, y se calcularán los intereses de cada periodo El capital acumulado en el periodo dos será el que esté en el periodo anterior más el de este periodo es decir =E7+C8. El capital pendiente de este periodo será el capital pendiente del periodo anterior menos el capital amortizado en el periodo actual, es decir: =F7-C8

38

Miguel Ángel García López

En las celdas B20, C20, y D20 calcula el total de las cuotas pagadas, capital amortizado e intereses pagados. El cuadro de amortización debe quedar tal y como aparece en la siguiente ilustración

De esta forma ya tienes el cuadro de amortización para de un préstamo por cuotas constantes o método francés.

39