curso excel andres

7/21/2019 Curso Excel Andres

1/130

EJERCICIO 1 DE EXCEL

1

EXCEL

EJERCICIO 1

FORMATO DE CELDAS; FRMULAS

Un ejemplo sencillo de la utilidad de la hoja de clculo puede ser un modelo defactura.

ACTIVIDAD A REALIZAR

En un nuevo libro (documento) de Excel, que guardars como 1exFactura , creaen la Hoja 1 (que llamars Modelo fac ) el impreso que se adjunta, con las frmulasnecesarias para realizar todas las operaciones.

PROCEDIMIENTO

Ensanchar filas y columnas

Todas las filas tienen, en principio, un alto de 13,20 ptos. y las columnas, un an-cho de 10,78 ptos.

Combinar celdas

Haciendo clic en la lnea de divi-sin entre columnas o filas yarrastrando el ratn puedesensancharlas o estrecharlas

1- Selecciona las celdas

que quieras combinar(convertir en una sola)2- Haz clic en el botn quese indica de la barra deherramientas


2/130


2

Nota: al combinar celdas, el contenido se centra automticamenteen la celda resultante .

Para descombinar celdas: selecciona la celda con el botn derecho, ve a For-mato de celdas, Alineacin y desactiva la casilla Combinar celdas (abajo, a la izquier-da) . Finalmente, pulsa Aceptar .

Poner bordes y sombreados (o color) a las celdas y ajustar texto

O bien, selecciona la celda o celdas en cuestin y utiliza los siguientes botonesde la barra de herramientas Formato :

Para establecer los bor-des, ve a la ficha Bordes .Para sombrear una celdao colorearla, la fichaTramas

Bordes Color de celdas

Color del textode las celdas

En general, para cambiar elformato de una o ms celdas,seleccinalas y haz clic sobre laseleccin con el botn dere-cho. Luego, elige la opcinFormato de celdas...


3/130


3

Para cambiar de lnea en la misma celda (p.ej, celda de PRECIO UNITARIO): se-lecciona la celda, ve a Formato de celdas, Alineacin y activa la casilla Ajustar texto.Luego, haz clic en Aceptar.

Alinear texto en celdasPara alinear el contenido de una celda a la izquierda, derecha o al centro se uti-

lizan los mismos botones que en el Word , si bien, antes de hacer clic en elbotn correspondiente hemos de seleccionar la celda o celdas que queramos alinear.

Para alinear el contenido de una celda arriba, abajo o en el medio, ve a Forma-to de celdas..., Alineacin, Vertical y establece la alineacin que corresponda.

Formato de celdas: moneda y porcentaje

Selecciona las celdas que contengan importes monetarios. Ve a Formato deceldas..., Nmero y, en el apartado Categora , selecciona Moneda . Deja las posicio-nes decimales en 2 (para los cntimos).

Selecciona la celda que contiene el tipo de IVA y, antes de escribir nada en ella,ve a Formato de celdas..., Nmero y, en el apartado Categora, selecciona Porcentaje .Baja las Posiciones decimales a cero 1.

Frmulas

Selecciona la celda en la que aparecer el resultado. Escribe el signo = y, a con-tinuacin, introduce la frmula, utilizando como operandos las referencias de celda.

EJEMPLO

Clculo del importe del producto B-45

- selecciona la celda D15- escribe el signo =- con el cursor, selecciona la celda A15- escribe el signo * (multiplicacin)- selecciona la celda C15- pulsa INTRO.

1 Si cambias el formato de un nmero ya introducido, el programa lo multiplicar por 100 (p.ej, 16pasar a ser 1600%)


4/130


4

En caso de que se repita la misma frmula en celdas contiguas, podemos co-piarla mediante el procedimiento de arrastre

Para calcular la base imponible en la factura, puede usarse el botn de Auto-

suma . Sita el cursor en la celda D23 y pulsa sobre dicho botn. Luego, seleccionael rango de celdas D15:D21 y pulsa INTRO.

Dar nombre a las hojas:

Los libros de Excel se componen, si no se establece otra cosa, de tres hojas:

Para especificar mejor el contenido de cada hoja es posible ponerle un nombreque haga referencia a dicho contenido. Para ello,

Haz doble clic sobre la pestaa de la Hoja1

Escribe Factura .Pulsa INTRO.

Eliminar hojas sobrantes:

Si no van a usarse todas las hojas de un libro, conviene eliminar las hojas vacascon el fin de que no aumenten intilmente el tamao del archivo (ms tarde, si lasnecesitamos, podemos aadir otras). En nuestro caso, eliminaremos las hojas 2 y 3:

Haz clic sobre la pestaa de la Hoja2 y, pulsando la tecla Shift (Maysculas),clic en la pestaa de la Hoja3.

Clic con el botn derecho sobre cualquiera de las dos pestaas (Hoja2 uHoja3) y selecciona Eliminar .

Configurar la hoja:

Con el fin de que la tabla creada quede centrada en la hoja (en vistas a la im-presin), ve a Archivo, Configurar pgina y, en la ficha Mrgenes, en el apartado

Centrar en la pgina , activa la casilla Horizontalmente . Fija el margen superior en 2cm.

Sita el cursor sobre la esquina inferiorderecha de la celda donde est la frmula y, cuando el cursor adopte la forma de unacruz negra, arrastra hacia abajo hasta laltima celda que deba contener la mismafrmula


5/130


5

Ajustar a una pgina. Vista preliminar

Con el fin de comprobar que toda la factura ocupa la misma pgina, haz clic so-

bre el botn Vista preliminar . Aparecer la factura tal como se imprimir.Luego, pulsa en el botn Cerrar y vuelve al documento. Una lnea intermitente

vertical y una horizontal indican los lmites de la pgina. Si la factura se sale de esoslmites se deben realizar los ajustes necesarios (estrechar columnas, filas, etc.)

Eliminar la cuadrcula:

Aunque las lneas grisesque marcan la cuadrcula decada hoja no aparezcan al im-primir el documento, la impre-sin visual es ms limpia si seeliminan. No obstante, esto sehar una vez se haya acabadoel trabajo en la hoja corres-pondiente. Para ello: ve aHerramientas, Opciones y en laficha Ver, aptdo. Opciones deventana, desactiva la casillaLneas de divisin .

Nota: los ltimos 5 apartados (dar nombre a las hojas, eliminarhojas sobrantes, configurar la hoja, ajustar a la pgina y eliminar la

cuadrcula) debern realizarse en todos los ejercicios de aqu enadelante

Otra opcin es ir a Archivo, Configurar pgina y en Escala fijarun porcentaje inferioral 100%


6/130

Destinatario:

Sant Antoni, 12507620 - LLUCMAJORTelfono: 971 - 723444FaxE-mail NIF o DNI:NIF:

FACTURA N FECHA:

CANTIDAD DESCRIPCIN PRECIOUNITARIO

IMPORTE

42 B-45 120,00 5.040,00

75 B-30 40,00 3.000,00

51 A-10 150,00 7.650,00

16 A-20 80,00 1.280,00

22 A-30 77,00 1.694,00

18 C-06 45,00 810,00

15 C-07 20,00 300,00

Base imponible 19.774,00

IVA

BASE IMPONIBLE

IMPORTE IVA

22.937,84

3.163,84

TOTAL FACTURA:

16%

19.774,00


7/130


1

EXCEL

EJERCICIO 2

FORMATO Y RELLENO DE SERIES II


Crea un Libro de Excel con dos hojas (elimina la que sobre) como las del modeloque se adjunta (consulta el original en el Google Docs):

La tabla de la primera hoja incluye la mayora de los formatos que puedenadoptar los datos introducidos en una celda.

Las tablas de la segunda hoja requieren utilizar la herramienta de rellenode series ya vista en parte en el ejercicio anterior

Llama 2ex Formatos y series al libro. La primera hoja se llamar Formatos y lasegunda, Series .

Recuerda configurar las hojas de forma que se centren en horizontal. Asimismo,la orientacin de las hojas tambin ha de ser horizontal (de lo contrario, las tablas sesaldrn de la pgina).

Si, an as, las tablas se salen de la pgina, reduce la escala de la misma; paraello, ve a Archivo, Configurar pgina, Pgina, Escala y ajstala al porcentaje del tama-o normal que sea preciso (aunque nunca menos del 80%)

Aade los bordes que hagan falta. Para las letras grandes utiliza el Wordart (seusa igual que en Word).


8/130


2

PROCEDIMIENTO

FORMATOS

Para dar un formato determina-do a la informacin introducida en unao ms celdas, selecciona la o las celdasy ve a Formato, Celdas . Selecciona laficha Nmero y, en el apartado Cate-gora escoge el formato correspon-diente. A continuacin, configralocomo convenga (nmero de decima-les, forma de fecha, etc.).

En la ltima de las fechas, la ca-tegora no es Fecha sino Personaliza-da .

SERIES

Para todas las series que aparecen en el ejercicio, con las excepciones que se in-dican ms abajo:

Introduce el primer dato de la serieSelecciona todas las celdas que deba ocupar la serie

Ve a Edicin, Rellenar, Series . Aparecer un cuadro de dilogo como el si-guiente (el apartado Unidad de tiempo slo estar activo cuando los da-tos seleccionados tengan formato de fecha):


9/130


3

Debes configurar este cuadro en cada caso de manera que la serie se llene co-rrectamente, teniendo en cuenta que lo que introduzcas en Incremento ser lo que seaadir de una celda a otra (sean unidades, das, aos, etc, segn el caso).

Ten en cuenta que el paso del 2% al 4% supone un incremento de 0,02 (usa lacoma del teclado alfanumrico).

Una vez configuradas las opciones correctas, pulsa Aceptar .

Excepciones a lo anterior:

Series predeterminadas: como hemos visto en el ejercicio 1, las series de mesesdel ao y das de la semana tienen un procedimiento especial de relleno (si no lo re-cuerdas, consulta el ejercicio 1).

Serie numrica lineal: la serie 1, 2, 3, 4, 5 se llena de la misma manera que lasde meses del ao y das de la semana, con la diferencia de que se ha de pulsar la teclactrl. antes de hacer clic y mientras se arrastra el ratn.

Serie de progresin aritmtica: introduce los dos primeros elementos de la serie(1 y 2). Luego, selecciona todas las celdas de la serie salvo la primera y especifica unincremento de 2.

Serie de progresin geomtrica: introduce los dos primeros elementos de la se-rie (1 y 2). Luego, selecciona todas las celdas de la serie (incluida la primera) y ve a Edi-cin, Rellenar, Series .Aqu tendrs que activar la casilla Tendencia del cuadro de di-logo indicado ms arriba y, lgicamente, seleccionar la opcin Geomtrica en el apar-

tado Tipo.


10/130

Fecha 1-2 2-2-2009 03-02-09 04-feb 05-feb-09 feb-09 febrero-09 febrero 8, 2009

Hora

Decimales

Miles sin punto deseparacin

Miles con puntode separacin

Nmerosnegativos

A la derecha

A la izquierda

Contabilidad Slo a la derecha

Moneda

6:00 PM18:004:30 AM4:00

1

3000 4000

-10 20,00 -30,00

2,0 4,0003,00

20,00

Fecha y hora

Formatos den

1.000

40,00

2.000 3.000 4.000

1000 2000

30,00 40,00

10,00 20,00 30,00 40,00

10,00 20,00 30,00

10,00


11/130

Das de febrero

Daslaborablesde febrero

Lunes de febrero y

marzo

Primer dade cada mes

MesesDas de lasemana

1-2-2009 1-2-2009 2-2-2009 1-1-2009 Enero Lunes

2-2-2009 2-2-2009 9-2-2009 1-2-2009 Febrero Martes

3-2-2009 3-2-2009 16-2-2009 1-3-2009 Marzo Mircoles

4-2-2009 4-2-2009 23-2-2009 1-4-2009 Abril Jueves

5-2-2009 5-2-2009 2-3-2009 1-5-2009 Mayo Viernes

6-2-2009 6-2-2009 9-3-2009 1-6-2009 Junio Sbado

7-2-2009 9-2-2009 16-3-2009 1-7-2009 Julio Domingo

8-2-2009 10-2-2009 23-3-2009 1-8-2009 Agosto

9-2-2009 11-2-2009 30-3-2009 1-9-2009 Septiembre

10-2-2009 12-2-2009 1-10-2009 Octubre

11-2-2009 13-2-2009 1-11-2009 Noviembre

12-2-2009 16-2-2009 1-12-2009 Diciembre

Series de porcentajes

Tipos deinters

2% 4% 6% 8% 10% 12% 14% 16% 18% 20%

Progresinlineal

1 2 3 4 5 6 7 8 9 10

Progresinaritmtica

1 2 4 6 8 10 12 14 16 18

Progresingeomtrica

1 2 4 8 16 32 64 128 256 512

Series denmeros

En horizontal

Series de textoSeries de fechas

En vertical


12/130


1

EXCEL


REFERENCIAS DE CELDA: RELATIVAS Y ABSOLUTAS

Todas las celdas de una hoja de clculo se identifican con una letra, relativa a lacolumna, y un nmero, referente a la fila en que se halla la celda. Cuando esas coor-denadas se utilizan en una frmula o una funcin se las llama referencia de celda .

El hecho de que la frmula de una celda pueda copiarse a otras obliga a distin-guir entre referencias de celda relativas y absolutas:

- Relativas: cambian al copiarse la frmula a otras celdas- Absolutas: no cambian al copiarse la frmula a otras celdas.

EJEMPLO

Clculo del importe de unas compras y del importe del IVA de las mismas.

Al copiar esta frmula de C3 a C6, lasreferencias cambian para ajustarse a lanueva situacin de la frmula (as, enC6, la frmula ser A6*B6). Son referen-cias relativas


13/130


2


Crea un nuevo libro de Excel, gurdalo con el nombre 3ex Inversin , y, en la

hoja 1, a la que llamars Rentabilidad , realiza las siguientes operaciones:

Dado un capital de 18000 invertido a un inters simple del 3% durante10 aos, calcular la rentabilidad anual y el capital final.

Hacer ese mismo clculo suponiendo que el inters es compuesto.

Confecciona, para ello dos cuadros como los que siguen:

Clculo de inters simple Clculo inters compuesto

CAPITALINICIAL

12000

INTERS 4%

AO CAPITAL INI-CIALRENTABILIDAD

ANUAL AOCAPITAL INI-

CIALRENTABILIDAD

ANUAL CAPITAL FINAL

2008 20082009 20092010 2010

2011 20112012 20122013 20132014 20142015 20152016 20162017 2017

RENTABILIDADTOTAL

CAPITAL FINAL

B8 es la celda donde se halla el tipode IVA. Dado que dicho tipo ha deser el mismo en las cuatro filas, nopuede cambiar al copiar la frmula.Por eso se le aade el signo $ a lacolumna y a la fila, convirtiendouna referencia relativa en referen-cia absoluta .


14/130


3

Utiliza el relleno de series para los aos y los formatos de celda que correspon-dan en cada caso (moneda, porcentaje...)

En este caso, sern referencias absolutas (y, por tanto, debers aadirles el sig-no $):

El capital inicial anual, en el inters simple El tipo de inters

Recuerda configurar la hoja, ajustar el contenido a la pgina, eliminar las hojassobrantes, la cuadrcula gris, etc. (ejercicio 1).


15/130


1

EXCEL

EJERCICIO 4

REFERENCIAS DE CELDA: MIXTAS

En ciertos casos puede ser necesario incluir en una frmula una referencia enparte relativa y en parte absoluta: una referencia mixta .

Esto es as cuando nos interesa que, al copiar la frmula, cambie la fila de la re-ferencia pero no la columna; o que cambie la columna pero no la fila:

A$1: en esta referencia, cambiar la columna pero la fila no $A1: aqu cambiar la fila, pero la columna no

EJEMPLO

Queremos calcular el IVA repercutido por la venta de tres productos sujetos adiferentes tipos a lo largo del primer trimestre de este ao. Los importes de las ven-tas de dichos productos en los tres primeros meses del ao son los siguientes:

A B C D1 Producto 1 Producto 2 Producto 32 Enero 1200 2400 6000 3 Febrero 1500 2100 6600 4 Marzo 1800 2700 5400 5 Tipos IVA 16% 7% 4%6 Enero =B2*B$57 Febrero8 Marzo

Para que la frmula que introduzcamos en B6 sirva para todo el rango B6:D8 esnecesario utilizar referencias mixtas para operar con los tipos de IVA ya que:

- En cada columna hay un tipo de IVA distinto: por tanto, al copiar lafrmula a C6 y D6, nos interesa que la columna de la celda del IVA vayacambiando


16/130


2

- Todos los tipos de IVA estn en la misma fila: por tanto, al copiar lafrmula a B7 y B8, nos interesa que la fila de la celda del IVA sea siem-pre la misma (la fila 5)

Por tanto, la frmula que introduzcamos en B6 tendr que ser: =B2*B$5. Dichafrmula vale para todo el rango B6:D8


1. En una fbrica se producen tres tipos de piezas de automvil. Tenemos lascifras de produccin de la semana y el coste que supone cada unidad. Queremoscalcular el coste diario total de cada pieza.

Abre un nuevo libro de Excel. Gurdalo en la memoria USB con el nombre 4ex

Referencias mixtas . En la Hoja 1, que llamars Piezas , confecciona el siguiente cua-dro.

Unidades producidasB-245 C-06 A-14

Lunes 2300 5320 650Martes 2150 4200 524Mircoles 2500 4850 563Jueves 1900 4972 627Viernes 2100 6000 450Sbado 2200 5340 300

Coste unita-rio

15,00 23,00 32,00

Coste totalB-245 C-06 A-14

LunesMartesMircolesJuevesViernes

Sbado

Introduce una frmula para calcular el coste total de la pieza B-245 el lunes, demanera que sirva tambin para los dems das y para las otras dos piezas. Copia di-cha frmula para calcular todos los costes.

Utiliza las referencias mixtas ah donde sean necesarias.

2. En la Hoja 2, que llamars Academia , realiza el siguiente cuadro:


17/130


3

N de matrculas1er trim 2 trim 3er trim

Curso A 30 21 25Curso B 12 15 10Curso C 16 14 17

Curso A 60,00

Curso B 120,00 Curso C 90,00

Recaudacin1er trim 2 trim 3er trim

Curso ACurso BCurso C

Se trata de calcular la recaudacin obtenida por una Academia de informticaen tres trimestres por la imparticin de 3 cursos (A, B y C), sabiendo el n de perso-nas matriculadas en cada uno cada trimestre y el precio de cada curso.

Introduce una frmula para calcular la recaudacin del curso A el primer tri-mestre, de forma que sirva tambin para todos los cursos y trimestres. Copia lafrmula para calcular todas las recaudaciones.

Utiliza las referencias mixtas ah donde sean necesarias.

3. En la Hoja 3, que llamars Tablas , establece el siguiente cuadro:

1 2 3 4 5 6 7 8 9

1

2

3

4

5

6

7

8

9

Introduce en esta celda una frmula que, copiada alas dems celdas vacas del cuadro sirva para realizartodas las tablas de multiplicacin. El resultado ser elque se indica en la pgina de atrs.


18/130


4

1 2 3 4 5 6 7 8 9

1 1 2 3 4 5 6 7 8 9

2 2 4 6 8 10 12 14 16 18

3 3 6 9 12 15 18 21 24 274 4 8 12 16 20 24 28 32 36

5 5 10 15 20 25 30 35 40 45

6 6 12 18 24 30 36 42 48 54

7 7 14 21 28 35 42 49 56 63

8 8 16 24 32 40 48 56 64 72

9 9 18 27 36 45 54 63 72 81


19/130


1

EXCEL

EJERCICIO 5

FUNCIONES Y RANGOS DE CELDAS

Con el fin de resumir frmulas complejas y/o muy largas, Excel (y cualquier

programa de Hoja de clculo) ofrece una serie de funciones predefinidas. Las funcio-nes son, por tanto, frmulas expresadas en un formato ms resumido P.ej, la funcin=SUMA(A1:A10) hace lo mismo que la frmula:

=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

Una funcin tiene aproximadamente la siguiente estructura

=NOMBRE DE LA FUNCIN(ARGUMENTO1;ARGUMENTO2...)

Los argumentos de la funcin son los datos que ha de introducir el usuario paraque la funcin se realice: p.ej, las celdas o rangos de celdas en que se encuentran lascantidades a sumar.

La principal ventaja de las funciones es que, a diferencia de las frmulas, per-miten operar con rangos de celdas y no slo con celdas individuales.

En este ejercicio vamos a ver las siguientes funciones:

SUMA (ya se ha visto en parte): suma todas las celdas de un rango PROMEDIO: calcula la media de las celdas de un rango MAX: devuelve la cantidad mxima de las celdas de un rango MIN: devuelve la cantidad mnima de las celdas de un rango CONTAR: cuenta el nmero de celdas llenas de un rango

Adems, veremos el Formato condicional de celdas: cmo hacer que una celdaadquiera un determinado formato slo si cumple una condicin dada.


Abre un nuevo libro de Excel y gurdalo en la memoria USB con el nombre 5exDatos examen .


20/130


2

Un profesor de Aplicaciones informticas quiere llevar un control de las faltasde sus alumnos (dos grupos, A y B) durante la primera evaluacin y, con ese fin, rea-liza en Excel un cuadro que le ayude a contabilizarlas. En dicho cuadro:

a) Se sumarn las faltas de septiembre a diciembre de cada alumno (funcinSUMA)

b) Se calcular el promedio de faltas de cada alumno durante esos meses (fun-cin PROMEDIO)

c) Se obtendr el nmero de faltas del alumno ms absentista de cada grupo yel del menos absentista: mes por mes y el total (funciones MAX y MIN).

d) Se calcular el total y la media de faltas de los dos grupos (funciones SUMA yPROMEDIO)

El cuadro es el de la pgina siguiente. Inclyelo en la Hoja 1, a la que llamarsFaltas .

A B C D E F G1

Grupo A FALTAS DE LA 1 EVALUACIN2 Septiembre Octubre Noviembre Diciembre TOTAL MEDIA3 Artigues, Rosa 2 4 3 64 Bonnn, Isabel 0 0 1 05 Cascos, Alberto 1 0 1 26 Dalmau, Gerardo 4 6 8 107 Torrens, Ana 0 1 2 08 N mximo de faltas9 N mnimo de faltas

1011

Grupo B FALTAS DE LA 1 EVALUACIN12 Septiembre Octubre Noviembre Diciembre TOTAL MEDIA13 lvarez, Sara 0 2 0 014 Armengol, Clara 0 2 4 815 Lendnez, Bartomeu 0 0 0 016 Santandreu, Vicente 1 2 3 117 Santos, Marisol 4 0 0 018 Viyuela, Agripina 1 1 0 219 N mximo de faltas

20 N mnimo de faltas212223 TOTAL FALTAS24 MEDIA GLOBAL


21/130


3

PROCEDIMIENTO

a) Para sumar las faltas de cada alumno, de septiembre a diciembre

Selecciona la celda en la que debe aparecer el total de faltas de Rosa Artigues

(grupo A). Luego, haz clic en el botn de Autosuma de la barra de herramientas ypulsa INTRO. A continuacin, copia la frmula a las celdas de TOTAL de los demsalumnos.

b) Para calcular el promedio de faltas de cada alumno durante estosmeses.

Selecciona la celda F3. Luego, haz clic en el botn de la barra de herramien-tas (a la derecha de Autosuma ). Aparecer un cuadro como el de la pgina siguiente:

A continuacin, copia la funcin en las celdas correspondientes a los demsalumnos.( F4:F7 y F13:F18 )

1- En Categora de la funcin seleccionaEstadsticas

2- En Nombre de la fun-cin desplzate haciaabajo hasta encontrar lafuncin PROMEDIO yseleccinala

3- Finalmente, pulsa Aceptar

Tras aparecer este cuadro, haz clic eneste recuadro y selecciona en la hoja elrango B13:E13. Luego, vuelve a hacer clicen el recuadro y pulsa en Aceptar


22/130


4

c) Nmero de faltas de los alumnos ms y menos absentistas

Selecciona la celda B8 . Haz clic en el botn y selecciona la categora Estads-ticas y la funcin MAX. Aparece ya seleccionado el rango que nos interesa ( B3:B7 ),situado justo encima de la celda donde estamos. Djalo tal cual y pulsa Aceptar . Lue-

go, copia la funcin hasta F8 .Copia la funcin correspondiente al mes de septiembre del grupo A a la celda

equivalente en el grupo B ( B19). A continuacin, copia la funcin hasta la celda F19.

Para obtener el nmero mnimo de faltas, se opera del mismo modo, pero conla funcin MIN. Adems, debers seleccionar como argumento de la funcin el rangoB3:B7 .

e) Total y media de faltas de los dos grupos:

Total:

Selecciona la celda B23 y pulsa el botn Autosuma . Selecciona el ran-go F3:F7 .

Sita el cursor en el segundo argumento de la funcin ( Nmero 2 ) y,manteniendo pulsado el botn Ctrl. del teclado, selecciona el rangoF13:F18 . Luego, pulsa INTRO.

Media:

Selecciona la celda B24 y pulsa el botn Escoge la funcin PRO-MEDIO y pulsa Aceptar .

En el primer argumento de la funcin ( Nmero 1 ) selecciona el rangoF3:F7. Haz clic en el segundo argumento ( Nmero 2 ) y selecciona elrango F13:F18 . Luego, pulsa Aceptar .


El mismo profesor de antes, con el fin de facilitar la correccin de un examen,realiza en Excel un cuadro en el que pretende que se calcule inmediatamente la notade cada alumno, una vez introducida la calificacin de cada ejercicio. Adems, seobtendrn otros datos:

La media de cada ejercicio y la nota media de los dos grupos (la del examen,la de los ejercicios de clase y la de evaluacin)

La media entre la nota del examen de cada alumno y la de los ejercicios declase (ser la nota de la evaluacin)

La nota mxima de cada ejercicio y del examen La nota mnima de cada ejercicio y del examen El nmero de alumnos presentados al examen (funcin CONTAR)


23/130


5

Confecciona este cuadro en la hoja 2, a la que llamars Datos examen .

A B C D E F G H I1 Notas

2 Ej. 1 Ej. 2 Ej. 3 Ej. 4 Notaex.Ejs.

claseNotaev.

3

G r u p o A

Artigues, Rosa 1 0,5 0,25 1,5 5,5

4 Bonnn, Isabel 2,5 1,75 0,75 2 75 Cascos, Alberto 86 Dalmau, Gerardo 2,5 2,3 2,5 2 8,57 Torrens, Ana 0,25 1 1 0,75 58

G r u p o B

lvarez, Sara 2 1 1 1,5 7,59 Armengol, Clara 2,5 1,5 1,25 0,75 8

10 Lendnez, Bartomeu 1,75 1,25 0,75 2,5 7,7511 Santandreu, Vicente 612 Santos, Marisol 0,75 0,25 0,5 0,75 413 Viyuela, Agripina 2,25 2,5 0,5 0,75 8,2514 MEDIAS15 MXIMAS16 MNIMAS

17 ALUMNOS PRESENTA-DOS

Adems, tanto las notas de examen como las de evaluacin que sean inferioresa 5 aparecern automticamente en rojo y en negrita ( Formato condicional de cel-

das).

PROCEDIMIENTO

Funcin CONTAR

Cuenta el nmero de celdas no vacas de un rango determinado.Selecciona la celda donde ha de aparecer el nmero de alumnos presentados

(C17 ). Luego, haz clic en el botn y, en la categora Estadsticas , escoge la funcin

CONTAR.En el primer argumento de la funcin ( Ref1), selecciona el rango C3:C13(las

celdas en blanco son de alumnos no presentados).Finalmente, pulsa Aceptar .

Formato condicional de celdas

Selecciona los rangos G3:G13 y I3:I13 (tras seleccionar el primer rango, pulsa latecla Ctrl. y, sin dejar de pulsarla, selecciona el segundo).


24/130


6

Ve a Formato, Formato condicional:

Nota: para las dems funciones, me remito a los procedimientos ex- plicados ms arriba, en este mismo ejercicio

Establece como condicinque el valor de la celda seamenor ue 5

Luego, ve a Formato,Fuente y seleccionaNegrita y color rojopara el texto

Finalmente, haz clicen Aceptar


25/130


26/130


ADMINISTRACIN Y FINANZAS

Tipo de IVA aplicable a todos los artculos: 16%

La hoja calcular:

El total de ordenadores e impresoras vendidos de cada modelo a lo largo dela semana,el importe de las ventas por cada modeloel importe total de las ventas de la semanael importe de IVA repercutido por cada modelo (de ordenador y de impreso-ra)el importe total de IVA repercutido (por todas las ventas)

Recuerda dar a las celdas el formato correspondiente segn el tipo de dato,

nombrar la hoja de trabajo ( Ventas ), eliminar las hojas sobrantes, centrar el conteni-do en horizontal, dar a la hoja la orientacin ms conveniente (vertical u horizontal,segn el caso) y eliminar la cuadrcula gris.

Importante: se valorar no slo el correcto funcionamiento de lahoja sino tambin la disposicin de los datos. Se trata de no duplicar in-formacin innecesariamente (p.ej, los dias de la semana) y de facilitar almximo el procedimiento de copiado de frmulas mediante arrastre.


27/130


1

EXCEL

EJERCICIO 7

FRMULAS Y FUNCIONES: REPASO 2

Los multicines Robledo mantienen un centro de 3 salas en Valencia y otro de 4

en Alicante. A continuacin, se proporcionan los datos relativos al nmero de espec-tadores de las diferentes salas y sesiones a lo largo del ltimo trimestre de 2008.

Valencia:

Octubre

Sala 1

Primera sesin: 30

Segunda sesin: 25 Tercera sesin: 50

Sala 2

Primera sesin: 52 Segunda sesin: 43 Tercera sesin: 85

Sala 3


Noviembre

Sala 1

Primera sesin: 50

Segunda sesin: 34Tercera sesin: 64


28/130


2

Sala 2


Sala 3


Diciembre:

Sala 1


Sala 2


Sala 3


Alicante

Sala 1


Sala 2

Primera sesin: 16Segunda sesin: 21

Tercera sesin: 32


29/130


3

Sala 3


Noviembre

Sala 1


Sala 2


Sala 3


Diciembre:

Sala 1


Sala 2


Sala 3

Primera sesin: 53 Segunda sesin: 58

Tercera sesin: 72


30/130


4

En un nuevo libro de Excel, 7 ex Multicines, en la hoja 1, que llamars Especta-dores , crea dos tablas (en las que puedes usar abreviaturas), una encima de otra.

la primera reflejar los datos proporcionados y calcular la suma de es-pectadores de cada sesin a lo largo del trimestre

en la segunda (con la misma estructura que la primera), se calcularnlos porcentajes que los espectadores de cada sesin y cada mes repre-sentan respecto al total de espectadores de cada sesin a lo largo deltrimestre 1. El clculo se realizar mediante una nica frmula (con refe-rencias mixtas), que se copiar a las celdas que sea necesario.

1

(P. ej, si en la primera sesin de la sala 1 del multicines de Valencia hubo, en octubre, 30 espectado-res y, en esa misma sesin, el total de espectadores del trimestre fue de 120, entonces, el porcentajede los espectadores de octubre respecto del total del trimestre ser del 25%)


31/130


1

EXCEL

EJERCICIO 8

GRFICOS

La informacin numrica introducida en una hoja de clculo puede ser analiza-

da de diferentes formas. Una de las ms tiles y conocidas es la realizacin de grfi-cos a partir de los datos de la hoja. Aqu veremos los tipos de grfico ms comn-mente utilizados, y otros no tan comunes.

El director de ventas del supermercado PRECAL, pasadas las navidades, se pro-pone hacer un estudio de las ventas de turrn normal y light durante las fiestas,comparndolas con las de aos pasados. Los datos de que dispone son los siguientes:

A B C D E F G H1

2 Alicante Jijona Yema TOTAL3 Light Normal Light Normal Light Normal4 2006-07 32 250 12 315 16 172 7975 2007-08 45 236 17 296 20 166 7806 2008-09 53 215 22 285 32 154 761

GRFICO DE LNEAS

til sobre todo para comprobar la evolucin de una serie de valores .

ACTIVIDAD

En un libro nuevo de Excel, que guardars como 8ex Grficos 1, representagrficamente la evolucin a lo largo de los 3 aos de las ventas de turrn de Alicantelight y normal.

PROCEDIMIENTO

En la Hoja 1, que llamarsDatos, crea la tabla de arriba (con la informacin arepresentar)

Selecciona el rango de celdas que contiene los datos a representar: A2:C6.


32/130


2

Ve a Insertar, Grfico... o haz clic en el botn de la barra de herramien-tas.

Haz clic enSiguiente . El cuadro 2 de este asistente nos permite indicar si losdatos cuya evolucin que queremos representar estn dispuestos en colum-nas o en filas. Normalmente, dejaremos la opcin predeterminada.

En el paso 3, escribiremos el ttulo del grfico y los nombres de los ejes:

Paso 4:

Selecciona Lneascomo tipo de grfico

Escoge este modelo(es el ms claro

Presionar aqu es unamanera de ver si elgrfico tiene buenapinta antes de seguiradelante

Establece, como ttulodel grfico y nombresde los ejes, los que seindican y haz clic enSiguiente

En el ltimo paso, orde-nars al programa queincluya el grfico en unahoja nueva llamadaLneas 1. Pulsa Finalizary se crear el grfico enuna hoja aparte


33/130


3

Una vez creado el grfico, se pueden introducir modificaciones en el mismo, yasea cambiando los datos de origen (si quieres, comprubalo: en la celda C4, introdu-ce 350 en lugar de 250 y observa cmo cambia el grfico), ya sea haciendo doble clicen alguno de los elementos del grfico (la lnea, los ejes, el rea delimitada por losejes, el rea del grfico...) y cambiando los valores correspondientes en los cuadrosde dilogo emergentes.

ACTIVIDAD

De acuerdo con lo que se acaba de decir, introduce en el grfico realizado lossiguientes cambios de formato:

El texto de los rtulos del eje X estar alineado en vertical.El tamao del texto de los dos ejes (X e Y) se cambiar a 8 ptos.La escala del eje Y variar de 20 en 20 y no de 50 en 50.Cambia el color de las lnea del grfico a rojo (turrn normal) y verde(light).Elimina el sombreado gris del rea de trazado (la delimitada por los dosejes)

ACTIVIDAD

A continuacin, representa, mediante grficos de lneas, la evolucin de lasventas de los turrones de Jijona y de Yema (light y normal), as como la de la ventatotal de turrones a lo largo de los 3 aos. Da a los grficos ttulos descriptivos de loque se representan e incluye cada uno en una hoja distinta ( Lneas 2, Lneas 3 yLneas4).

Para seleccionar rangos no contiguos, selecciona el primer rango yluego, manteniendo pulsada la tecla Ctrl., selecciona los dems.

GRFICOS DE COLUMNAS

tiles para comparar magnitudes .

ACTIVIDAD

Comparar grficamente la las ventas de turrn de Alicante, Jijona y Yema (lighty normal) en las navidades del 2006-07..


34/130


4

PROCEDIMIENTO

Selecciona el rango de celdas que contiene los datos a representar: A2:G4.

Ve a Insertar, Grfico... o haz clic en el botn de la barra de herramien-tas. Como tipo de grfico elige Columnas , y, como modelo, el primero de la iz-

quierda (fila de arriba)

Sigue el asistente. Como ttulo del grfico, escribe Venta de turrn en 2006-07. Como nombre del eje Y (el vertical), escribe N de tabletas .

Inserta el grfico en una hoja nueva, con el nombre Columnas 1.

ACTIVIDAD

A continuacin, compara grficamente las siguientes magnitudes:

Tabletas de turrn normal, de los 3 tipos, en las navidades de 2007-08. Inserta el grfico en una hoja nueva, con el nombre Columnas 2

Tabletas de turrn de Alicante y de yema (normal y light), en las navi-dades de 2008-09. Inserta el grfico en una hoja nueva, con el nombreColumnas 3

Nota: para estos dos grficos tambin puedes usar los tipos Ba-rras , Cilndrico , Cnico o Piramidal o alguno de los Tipos perso-nalizados siempre que sea equivalente y que quede clara la in-

formacin.

GRFICOS CIRCULARES

Sirve para mostrar el porcentaje que una serie de cantidades representan

respecto de un total. Slo permite representar una serie de valores cada vez.

ACTIVIDAD

Mostrar grficamente cmo se distribuyeron las ventas de turrn light, de los3 tipos, en las navidades de 2007-08.


35/130


5

PROCEDIMIENTO

Descombina las celdas en que aparecen los tipos de turrn (Alicante, Jijona yYema)

Selecciona las celdas que contienen los datos a representar: B2, B5, D2, D5,F2 y F5. (el rtulo Light no es necesario seleccionarlo, pues esa informacinya figurar en el ttulo del grfico)


Como tipo de grfico elige Circular , y, como modelo, el primero de la izquier-da (fila de arriba)

Sigue el asistente (en este caso, los datos seleccionados estn dispuestos en

fila). Como ttulo del grfico, escribeVenta de turrn light en 2007-08. En el paso 3 del asistente, selecciona la ficha Rtulos de datos:

En el paso 4, inserta el grfico como objeto en la hoja Datos y sitalo a la de-recha de la tabla (si es necesario, orienta la hoja en horizontal para que elgrfico quepa en la misma pgina que los datos).

ACTIVIDAD

Representa grficamente la siguiente informacin, insertando los grficos en lamisma hoja de los datos (debajo del Venta de turrn light en 2007-08 ):

Distribucin de las ventas totales de turrn en las 3 ltimas navidades

Distribucin de las ventas de turrn normal de Alicante, de Jijona y de Yemaen las dos ltimas navidades. En este caso, tendrs que utilizar un grfico de

Activa la casillaPor-centaje para que apa-rezca el % que cadaseccin representares ecto del total


36/130


6

anillos (equivalente al circular pero que permite representar ms de una se-rie de valores). El anillo interno muestra la serie ms antigua (2007-08) y elexterno, la ms reciente (2008-09).

GRFICOS DE DISPERSIN (XY)

Sirven para mostrar la relacin entre dos series de valores.

ACTIVIDAD

Representa grficamente la relacin entre la venta de turrn de Alicante nor-mal y light a lo largo de las 3 ltimas navidades.

PROCEDIMIENTO

En la hoja Datos, selecciona el rango de celdas que contiene los datos a re-presentar: B4:C6.

Ve a Insertar, Grfico... o haz clic en el botn de la barra de herramien-

tas. Como tipo de grfico elige XY (Dispersin) , y, como modelo, el primero de la

izquierda de la segunda fila.

Sigue el asistente. En el paso 3, como ttulo del grfico, escribe Ventas Alican-te light y normal . En la fichaLeyenda , desactiva la casilla Mostrar leyenda .

En el paso 4, inserta el grfico en la misma hoja, debajo de la tabla de datos. Con el fin de que la lnea comience desde el eje Y , cambia la escala del eje X .

Para ello, haz doble clic sobre el eje X y, en la ficha Escala, escribe 30 en elcuadro Mnimo (para que el valor inicial del eje sea 30).

La curva descendente muestra que existe una relacin inversa entre las ventasde turrn de Alicante normal y light: al aumentar unas, disminuyen las otras.

ACTIVIDAD:

A continuacin, en un libro nuevo de Excel, que guardars como 8ex Grficos2, realiza grficos xy (una hoja para cada grfico) que representen las siguientes se-ries de valores. Da a cada grfico un ttulo descriptivo de lo que representa y nombra


37/130


7

tambin los ejes. Los datos a representar inclyelos en la hoja 1, a la que llamarsGrficosxy :

Num. de viajes para transportar una mercanca:

N de ca-miones 1 2 3 5 10

N de viajes 150 75 50 30 15

Distancia de frenado en funcin de la velocidad:

Velocidad co-che(Km/h) 20 40 60 80 100

Distancia frena-do (mts) 7 20'5 39'5 64 95

Precios de un parking:

Tiempo (en mi-nutos) 30 60 70 140

Precio 1 1 2 3

GRFICOS DE COTIZACIONES

Sirven para representar la evolucin burstil de unos valores determinados a lolargo de una jornada.

ACTIVIDAD

A continuacin, se transcriben las cotizaciones mxima, mnima y final de unaserie de valores a lo largo de una jornada burstil:


38/130


8

Representa grficamente dichos valores mediante un grfico de cotizaciones .

PROCEDIMIENTO

Abre un libro nuevo de Excel y gurdalo con el nombre 8ex Grficos 3. En lahoja 1, que llamars Euro Stoxx 50,transcribe la tabla de arriba.

Selecciona el rango de celdas que contiene los datos a representar: A3:D16.


Como tipo de grfico elige Cotizaciones , y, como modelo, el primero de la iz-quierda (fila de arriba)

Sigue el asistente. Como ttulo del grfico, escribe Cotizaciones Euro Stoxx 50.Como nombre del eje Y, escribe Euros . En la fichaRtulos de datos, activa laopcin Mostrar valor.

Inserta el grfico en una hoja nueva, con el nombre Grfico Euro Stoxx 50. Haciendo doble clic sobre cada serie de valores, reduce el tamao de la fuen-

te a 8 ptos.

Alinea en vertical los rtulos del eje X (los nombres de las empresas)

A B C D1 EURO STOXX 5023 Mxima Mnima Cotiz final

4 Aegon 18,6 11,05 13,745 Ahold 8,5 3,2 6,286 Air Liquide 172 159 1597 Alcatel 14,3 8,32 10,598 Allianz 131,4 119,6 125,49 Axa UAP 36,5 22,3 26,54

10 Basf 71,02 59,12 63,111 Bayer 41,3 32,1 34,0612 BBVA 18,5 10,3 14,713 BNP Paribas 76,08 61,2 67,0514 Carrefour 47,3 31 38,0215 DaimlerChrysler 48,2 42,9 43,1816 Danone 98,14 85,3 89,45


39/130


1

EXCEL

EJERCICIO 9

GRFICOS: REPASO


A continuacin se exponen los resultados de un estudio realizado por el COCO-PUT(Comit de Control de la Publicidad en Televisin), en el que figura el tiempo (enminutos/da) dedicado a publicidad por distintas cadenas no de pago a lo largo delprimer semestre de 2008:

TV1 TV2 Tele5 Antena 3 TV3 Canal 33 Canal 9Enero 290 90 360 375 150 80 300Febrero 240 85 350 320 120 62 268Marzo 260 100 330 360 145 74 295Abril 250 70 290 310 95 65 140Mayo 220 72 340 330 100 70 198Junio 230 80 345 350 110 71 230

Transcribe este cuadro en un nuevo libro de Excel, que guardars como 9exPublicidad, en la Hoja 1 ( Publicidad ).

A partir de los datos de dicho cuadro, realiza grficos (del tipo que corres-ponda en cada caso) que muestren la siguiente informacin (incluye cada grfico en

una hoja nueva, que llamars Grfico1, Grfico2, etc. ) : La evolucin del tiempo dedicado a publicidad en las cadenas de TV pblica

(incluyendo autonmicas) durante el semestre La evolucin del tiempo dedicado a publicidad en las cadenas privadas duran-

te el semestre La evolucin del tiempo dedicado a publicidad en las cadenas autonmicas

durante el 2 trimestre

Comparacin del promedio de minutos/da dedicado a publicidad durante elsemestre por las cadenas TV1, TV2 y las privadas (se ha de calcular)

Comparacin del tiempo dedicado a publicidad durante el semestre por lascadenas TV1, TV2 y las autonmicas (barras apiladas)


40/130


2

Comparacin del tiempo dedicado a publicidad en marzo por las dos cadenasprivadas

Comparacin del tiempo dedicado a publicidad en mayo y junio por las cade-nas autonmicas

Distribucin del tiempo dedicado a publicidad por las cadenas pblicas en fe-brero

Distribucin del tiempo dedicado a publicidad por todas las cadenas en mayo Distribucin del tiempo dedicado a publicidad por las cadenas privadas en

enero y febrero (grfico de anillos)

Una vistosa opcin de formato, relativa sobre todo a los grficos de columnasy/o de barras, es la de sustituir las columnas o barras por imgenes (relacionadas conaquello que se representa).

Vamos a probarlo con el grfico de comparacin del tiempo dedicado a publi-cidad en marzo por las dos cadenas privadas (tercer grfico de columnas). Pero ante-

s, descarga en tu memoria USB el archivo imgenes ej 9 Excel.zip y descomprmeloah.

PROCEDIMIENTO

En el grfico mencionado, selecciona la columna correspondiente a la cadenaTele5. Para ello, haz un clic sobre ella, con lo que se selecciona la serie entera (lasdos columnas). Haz otro clic a continuacin sobre la misma columna y la seleccio-nars slo a ella.

Haz doble clic sobre la misma y, en el cuadro de dilogo Formato de punto dedatos haz clic en Efectos de relleno . Aparece este cuadro:


41/130


3

Busca en tu memoria USB el archivo logo Tele5.png y haz doble clic sobre l.

Repite la operacin con la columna de Antena 3, pero seleccionando la imagenlogo Antena3.gif .A continuacin, elimina el fondo gris del grfico, para que resalten mejor las

imgenes. Y, para acabar, convierte el grfico de uno de columnas en uno de barrasagrupadas y orienta en horizontal el rtulo del eje X. El aspecto final del grfico serel siguiente:

Haz clic en Seleccionarimagen

1. Selecciona la opcinGraduar tamao y escri-be 5 en el cuadro Uni-dades/imagen (cuantoms bajo el nmero,ms veces saldr repeti-da la ima en

2- Haz clic en Aceptar


42/130


4


Una agencia de viajes de Palma pretende realizar un anlisis grfico de las ven-tas de billetes de avin para diferentes destinos. Los datos son los siguientes:

Gran Bretaa Francia Alemania Rusia China

Londres Edimburgo Pars Lyon Berln Frankfurt MoscSan Peters-

burgoPekn Shangai

Enero 45 15 55 6 47 36 12 8 36 25Febrero 23 9 34 3 28 25 3 2 24 17Marzo 27 11 32 1 24 22 1 0 21 16Abril 42 16 49 8 51 38 15 11 39 28Mayo 31 12 38 4 32 19 5 3 28 12Junio 39 17 44 9 49 41 23 15 42 31

Abre un nuevo libro en Excel y gurdalo con el nombre 9ex Agencia. En la hoja1, que llamars Billetes, introduce la tabla anterior. A continuacin, representa me-diante grficos (del tipo que corresponda en cada caso) la informacin que se solicita(un grfico en cada pgina, numerados correlativamente),

Evolucin de la venta de billetes a Francia en el primer trimestre Evolucin de la venta de billetes a Londres y Berln en el 2 trimestre Evolucin de la venta de billetes Edimburgo, Lyon, Frankfurt entre febrero y

mayo


43/130


5

Comparacin de billetes vendidos para Londres, Pars y Berln en abril Comparacin de billetes vendidos para Lyon, Frankfurt, Mosc en el primer

trimestre (columnas apiladas) Comparacin de billetes vendidos para Pars y Pekn el segundo trimestre (co-

lumnas 3D). Sustituye las columnas por imgenes: para la serie de datos Pars elige la imagen Pars.wmf y para la serie Pekn , la imagen Pekn.wmf (ambasen tu memoria USB)

Distribucin (entre las distintas ciudades) de los billetes vendidos en marzo yabril (anillos)

Distribucin de los billetes vendidos para Londres entre los 6 meses Distribucin de los billetes vendidos para Mosc y Pekn en Enero

Por otro lado, se quiere mostrar la relacin existente (directa o inversa) entre elnmero de billetes que se venden para determinadas ciudades. Para eso se parte de

las cifras relativas a los ltimos 5 aos (n de billetes de avin vendidos):

Londres Pars Berln Tnez El Cairo Beijing

2003 565 348 430 230 165 982004 543 356 453 215 183 1122005 532 387 412 197 195 1282006 512 399 530 192 198 1402007 503 412 502 186 201 1452008 480 439 543 180 214 156

Inserta los datos en la hoja 2, que llamars Relaciones. Representa grficamen-te la relacin entre:

la venta de billetes a Londres y a Pars la venta de billetes a Tnez y a El Cairo la venta de billetes a Pars y a Beijing (destinos complementarios, a causa del

incremento de adopciones de nias chinas y la ruta seguida desde Espaa)

Incluye los grficos en la propia hoja de datos ( Relaciones), a la derecha de latabla. Indica debajo del grfico si la relacin representada es directa o inversa.

Como es natural, los grficos tambin pueden reflejar magnitudes negativas .


La empleada de la Agencia, a peticin de un cliente, extrae de internet una es-tadstica de temperaturas mnimas de Londres (ciudad en la que deber permanecer

durante 12 meses por razones de estudios) a lo largo del ao:


44/130


6

Inserta los datos en la hoja 3 y llmala Temperaturas Londres .

Representa grficamente esta relacin de temperaturas mediante un grfico decolumnas. Incluye en grfico en la propia hoja de datos, debajo de la tabla.

Temperaturas de Londres(en grados centgrados)

Enero -12Febrero -10Marzo -5Abril 0Mayo 5Junio 14Julio 17Agosto 16Septiembre 7Octubre 2Noviembre -7Diciembre -9


45/130


1

EXCEL

EJERCICIO 10

FUNCIONES LGICAS: SI, Y, O

Funcin SILa funcin SI es una funcin de tipo lgico que sirve para mostrar en una celda

un resultado u otro en funcin del contenido de otras celdas.

Un ejemplo claro es el de una ficha de entrada y salida de existencias de unproducto en un almacn. El total de unidades en existencia del artculo dependerde si ha habido una entrada o una salida. Para que el resultado aparezca slo conintroducir la cantidad que ha entrado o salido hace falta una funcin SI.

A B C1 Ficha de control2 Artculo: F-3243 Entradas Salidas Existencias4 10056789


Abre un nuevo libro de Excel y llmalo 10ex Funciones lgicas. En la hoja 1, quellamars Control almacn , crea la tabla de arriba y, en la celda C4, escribe 100 comocifra inicial de existencias.

Incluye en C5 una funcin que, copiada al rango C6:C9, d como resultado lacantidad total de unidades en existencias al introducir una entrada o una salida en lacolumna A o B.


46/130


2

PROCEDIMIENTO

En la celda C5 introducirs la funcin SI del siguiente modo:

Selecciona la celda.

Pulsa el botn que da inicio al asistente para funciones

Al pulsar Aceptar aparece el siguiente cuadro de dilogo:

Argumentos de la funcin:

2. Elige la funcinSI pulsa Aceptar

Breve descripcin de lafuncin

1. Selecciona lacategora Lgicas

1. Introduce la expresin A5= Si se cumpleesta condicin, la funcin mostrar un valor;si no, otro.

2. Introduce: C4-B5 Si lacondicin se cumple, semostrar este valor.

3. Introduce: C4+A5 Sila condicin no secumple, se mostrareste valor.

Descripcin de lafuncin

Aqu se explica el argumento de lafuncin en que se encuentra el cur-sor

4. Para acabar, haz clic en Aceptar


47/130


3

Prueba_lgica: A5= significa si A5 (la casilla de Entradas ) est vaca. Lasdobles comillas indican celda vaca . Por tanto, la condicin es que no haya habidouna entrada .

Valor_si_verdadero: si la condicin se cumple, es porque ha habido una salida;por tanto, la cantidad introducida en Salidas se restar de las existencias ( C4-B5).

Valor_si_falso: si la condicin no se cumple, significa que la celda de Entradas no est vaca y, por tanto, se ha producido una entrada de existencias, que ha desumarse a las que ya haba ( C4+A5)

Una vez introducida la funcin, en la celda C5 aparecer como resultado 100,que es la cantidad inicial de existencias (dado que todava no hemos introducido nientradas ni salidas)

A continuacin, copia la funcin de C5 en las celdas inferiores hasta C9. Ahora,en todo el rango C4:C9 aparece el mismo resultado: 100.

Introduce aleatoriamente cantidades (las que quieras) en Entradas o en Sali-das (o en uno o en otro, nunca en los dos) entre la fila 5 y la 9 y observa cmoel saldo final vara de acuerdo con las entradas.

Establece para el rango C4:C9 un formato condicional que muestre los nme-ros en rojo y negrita cuando la cantidad sea inferior a 30 (punto de pedido).

Recuerda aplicar las opciones de formato de ejercicios anteriores: centrar enhorizontal, eliminar la cuadrcula, quitar las hojas sobrantes, etc.

Funciones Y, O

Si queremos sujetar un resultado a que se cumpla ms de una condicin, nece-

sitamos combinar la funcin SI con la funcin Y.Si queremos condicionar el resultado a que se cumpla una (o ms) de entre va-

rias alternativas, tendremos que combinar la funcin SI con la funcin O.


A continuacin se presentan los resultados de los ejercicios terico y prcticode un examen. En la columna Apto / No apto aparecer la palabra APTOslo si deambos ejercicios se ha obtenido una nota igual o superior a 5. De lo contrario, se

mostrar NO APTO.


48/130


4

A B C D12 Teora Prctica Apto/Noapto5 Beltrn, Paula 4 73 Chvez, Alberto 7 3

6 Fernndez, Alejandro 3 69 Morn, Jorge 5,5 6,54 Quintana, Isabel 5 68 Torres, Margalida 7 87 Villalonga, Pascual 8 4

En caso de que el examinando sea apto, dicha palabra aparecer en negrita ycolor blanco, sobre fondo marrn oscuro (formato condicional)

En la hoja 2 del libro 10ex Funciones lgicas , que llamars Examen, introducela tabla de arriba, con las notas de las pruebas terica y prctica.

PROCEDIMIENTO

Establece para el rango D3:D9 el formato condicional indicado (proce-dimiento: ejercicio 5).

Selecciona la celda D3. Inicia el asistente para funciones y selecciona la funcin SI .

Copia la funcin hasta la celda D9.

1. Introduce la funcin Y(B3>=5;C3>=5) , quesignifica si la nota de teora (B3) y la deprctica (C3) son 5 o ms

2. Introduce: APTO. Slo aparecersi se cumple la doble condicin.

3. Introduce: NO AP-TO.Aparecer si no secumple alguna de lascondiciones.



49/130


5


En una tienda de productos informticos se lleva un registro de las ventas reali-zadas. Para celebrar el 10 aniversario del establecimiento, en la venta de ordenado-res e impresoras se concede un 10% de descuento y un 5% en los dems artculos. Enla columna Descuento aparecer el tipo de dto. aplicado en funcin del artculocomprado. Por tanto, para que el descuento sea del 10% se ha de cumplir una de dosposibles alternativas: que el artculo comprado sea un ordenador o que sea una im-presora:

A B C1

2 Clientes Artculo comprado Descuento

3 Gonzlez, Patricia Ordenador

4 Artigues, Ignacio Impresora5 Perell, Rosa Teclado6 Durn, Jos Antonio Memoria USB7 Dalmau, Aina M Impresora8 Fuster, Fulgencio Escner9 Garau, Luis Cartucho tinta

En la hoja 3 del libro 10ex Funciones lgicas , que llamars Ventas , introduce latabla anterior.

PROCEDIMIENTO Selecciona la celda C3. Inicia el asistente para funciones y selecciona la funcin SI .

Copia la funcin hasta la celda C9.

1. Introduce la funcinO(B3=Ordenador;B3=Impresora) , que signi-fica si el artculo comprado (B3) es un orden a-dor o una impresora

2. Introduce: 10%. Slo aparecer si secumple alguna de las 2 alternativas.

3. Introduce: 5%. Apa-recer si no se cumpleninguna de las condi-ciones.



50/130


1

EXCEL

EJERCICIO 11

FUNCIONES LGICAS: REPASO

La empresa Muebles La Mallorquina lleva el siguiente registro de sus empleados

Empleado Antigedad Sueldo baseCompl

antDepto

Complprod

Plus decompensacin

CargoPlus por

cargoCourel, Ana M 2 2.000,00 ContabArellano, lvaro 5 2.500,00 ComercialBelln, Evangelina 10 4.000,00 ContabCano, Miguel 1 2.200,00 ComercialGarca, Juan Ramn 1 3.200,00 ComprasGoya, Sonia 12 4.300,00 Compras

Vlchez, Jos Manuel 4 3.000,00 Comercial

Compl ant 3%Compl prod comercial 4%


En un nuevo libro de Excel, que guardars como 11ex Repaso func lgicas, creaen la Hoja 1, que llamars Empleados, el cuadro anterior, introduciendo en lascolumnas en blanco funciones lgicas de manera que:

En la columna Compl. ant (complemento de antigedad) se calculardicho complemento si la antigedad del empleado es superior a 4 aos.De lo contrario, aparecer 0.

En la columna Compl. prod se calcular dicho complemento para aquellosque pertenezcan al departamento comercial. En otro caso, aparecer 0.


51/130


2

En la columna Plus de compensacin aparecer S en caso de que elempleado/a no cobre ni complemento de antigedad ni de productividad;NO, en los dems casos.

En la columna Cargo, aparecer Jefe de ventas para aquellos

empleados/as que, perteneciendo al departamento comercial, cobren uncomplemento de productividad superior a 100 .

En la columna Plus por cargo aparecer 60 slo para los empleados queocupen algn cargo. De lo contrario, aparecer 0.

Nota: Los resultados de 0 pueden ser necesarios para realizarposteriores operaciones pero cabe que estropeen un poco el efecto finalde la hoja. Para ocultarlos sin eliminarlos, ve a Herramientas, Opciones, Very en el apartado Opciones de ventana , desactiva la casilla Valores cero .

Una academia de informtica incluye en un registro los cursos que vaofreciendo con una serie de datos relativos a los mismos.

Curso NivelDuracin(horas)

PrecioSoftwareincluido

DescuentoPresencial/A

distanciaInternet BsicoDiseo web Medio

Contab BsicoInternet AvanzadoLinux MedioOpenOffice AvanzadoWord MedioInternet MedioWord AvanzadoLinux Bsico

Bsico ( por hora) 10

Medio y avanzado (por hora) 15


En la hoja 2 (Cursos) del libro 11ex Repaso func lgicas incluye el cuadro anteriore introduce en las columnas en blanco funciones lgicas de manera que:


52/130


3

En la columnaDuracin (horas) aparezca 30 en caso de que el curso sea deLinux o su nivel sea bsico; 60, en otro caso.

En la columnaPrecio se calcule el precio del curso en funcin de su nivel(bsico, medio o avanzado).

En la columnaSoftware incluido aparecer S salvo que el curso sea decontabilidad o de Linux (en cuyo caso, aparecer NO).

En la columnaDescuento se calcular un 3% de descuento para aquellos cursoscuyo precio sea de 450 o superior.

En la columnaPresencial / A distancia aparecer A distancia slo para loscursos de Internet de 60 hs. de duracin. En los dems casos, aparecerPresencial .


En un videoclub van anotando los alquileres realizados, con indicacin de la fechade alquiler, de la devolucin y de si se ha de imponer o no un recargo al cliente porretraso. Para ello, se incluye en una celda una funcin que muestre la fecha del da,con el fin de poder calcular el retraso (ten en cuenta que con las fechas tambin sepueden hacer operaciones).

En la Hoja 3 (Videoclub) del libro 11ex Repaso func lgicas incluye el siguientecuadro:

A B C D12 Fecha de hoy34

5 Fecha alquiler Devuelta Recargo

6 Munar, Santiago no7 vila, Vanesa s

8 Ibez, Juan no9 Derrida, Alexandra no

10 Martos, Jos M s11 Aldecoa, Sara no

En la celda B2 introduce la fecha actual por medio de la funcin HOY() (elprocedimiento es muy sencillo, ya que es una funcin sin argumentos)

En la columna Fecha alquiler introduce fechas entre 1 y 20 das anteriores a laactual.

En la columna Recargo aparecer S slo en aquellos casos en que hayantranscurrido ms de 5 das desde el alquiler y an no se haya devuelto el video.


53/130


1

EXCEL

EJERCICIO 12

FUNCIONES SI ANIDADAS

Cuando el contenido de una celda depende tres o ms alternativas ya no bastacombinar la funcin SI y las funciones Y u O. Es preciso incluir funciones SI dentro deotra funcin Si. Las funciones incluidas dentro de otras funciones se llaman funcionesanidadas .

Puede parecer que esto es complicar demasiado las cosas pero no hace faltapensar mucho para encontrar posibles aplicaciones. Por ejemplo, una quiniela (el signoque aparezca depender de tres alternativas: victoria en casa, a domicilio o empate).

Nota: la quiniela no es de esta temporada.


Crea un nuevo libro de Excel y gurdalo con el nombre 12ex SI anidada . En laHoja1, que llamars Quiniela , confecciona la siguiente tabla.

Resultado Signo1. VALENCIA MALLORCA 2. BETIS ALAVS 3. DEPORTIVO ESPANYOL 4. BARCELONA CELTA 5. R. SOCIEDAD CDIZ 6. GETAFE SEVILLA 7. MLAGA VILLARREAL 8. R. MADRID RACING 9. OSASUNA AT. MADRID 10. CASTELLN RECREATIVO 11. NUMANCIA GIMNSTIC 12. XEREZ ALBACETE 13. HRCULES MURCIA 14. VALLADOLID LEVANTE

15. ZARAGOZA ATHLETIC CLUB


54/130


2

Incluye en las celdas de la columna Signo una funcin que muestre el signocorrespondiente (1, X , 2) en funcin del resultado.

Luego, escribe resultados al azar para comprobar el funcionamiento correcto dela funcin.

Recuerda eliminar las hojas sobrantes, etc.


Un ejemplo lmite de funcin SI anidada (lmite porque el mximo deanidamientos en Excel es de 7) es el siguiente:

En el hospital Son Dureta, seis doctores/as comparten una consulta. Cada da, delunes a sbado, atiende un doctor distinto, mientras que el domingo permanececerrada:

Lunes: Dra. Chaves Martes: Dr. Gomila Mircoles: Dra. Ansn Jueves: Dr. Antnez Viernes: Dr. Binimelis Sbado: Dra. Gonzlez Domingo: cerrada

Se trata de crear una hoja de clculo que pueda proporcionar, al que la consulte,

slo con introducir el da de la semana, la informacin de qu doctor/a atiende el daen cuestin. Intntalo. Hazlo en la Hoja 2 del libro 12ex SI anidada.xls

Por otro lado, tambin es posible hacerlo ms automtico y fcil para el usuario(y ms difcil para nosotros) ayudndonos de la funcin HOY combinada con la funcinDIASEM (da de la semana) y otra funcin SI del mismo estilo que la anterior paraobtener el da de la semana en letras.

Funcin HOY: =HOY()

No tiene argumentos. Devuelve la fecha del da en que se abre el documento.


55/130


3

Funcin DIASEM: =DIASEM(celda con la fecha;2)

El segundo argumento (2) es para indicar al programa que el sistema de semanaque nos interesa es el latino (Lunes: da 1; Domingo: da 7).

El formato de la hoja (puede ser la misma Hoja 2, modificada) ser algo parecidoa esto:

Hospital Son DuretaConsulta: Oftalmologa

Fecha de hoy Funcin HOY

N de da de la semana Funcin DIASEM

Da de la semana Funcin SI anidada

Consulta Funcin SI anidada

Esta fila se ocultar


56/130


1

EXCEL

EJERCICIO 13

FUNCIONES DE BSQUEDA: BUSCARV y BUSCARH.

Estas funciones buscan en una tabla y devuelven la correspondencia con un de-terminado valor.

EJEMPLO

Un opositor desea conocer la nota de su examen. Al introducir el DNI en una cel-da, aparecer abajo la nota correspondiente a ese DNI. Para eso es preciso contar conuna tabla previa en que figuren los DNI de todos los participantes en el examen y susnotas respectivas (se supone que la lista de notas es mucho ms larga que la que aquse incluye).

A B

1 DNI2 Nota3

4 DNI Nota5 40309345 36 42976450 7,77 72867009 4,58 42009384 8,39 43123456 5,7

En este caso tendramos que usar la funcin BUSCARV(Buscar en Vertical) ya quelos datos de la tabla, DNI y notas, estn dispuestos en vertical (en columnas)Dicha funcin tiene 4 argumentos o parmetros:

Valor_buscado: ser el DNI, es decir, B1 Matriz_buscar_en: ser la tabla de correspondencias, es decir A4:B9 Indicador_columnas: ser la columna de la tabla de correspondencias donde se

encuentra la nota, aunque expresada en nmero, no en letra. Es decir, 2.


57/130


2

Ordenado: dado que los DNI no aparecen por orden numrico, ser precisoponer FALSO (si la primera columna de la tabla est ordenada, este parmetrose deja en blanco).

De manera que, en este caso, la funcin a introducir en B2 sera:

=BUSCARV(B1;A4:B9;2;FALSO)

Funcin BUSCARV


El hotel Imperial lleva un registro de las reservas realizadas por sus clientes en el

que incluye: el nombre y apellidos del cliente, el tipo de habitacin reservada y el pre-cio por noche de la misma. La tarifa es la siguiente:

Tipo de habi-tacin

Julio y Agos-to

Resto delao

Individual 72 36 Doble 130 70 Suite 200 110 Imperial 350 190

Crea un libro nuevo de Excel y gurdalo como 13ex Bsqueda . En la hoja 1 (con elnombre Reservas verano ) elabora el registro de abajo e incluye en el rango A10:C14lalista de precios que se indica.

A B C

1 Registro de reservas: julio y agosto2 Cliente Tipo habitac Precio3 Teodora Antnez Individual4 Basilio Artigues Doble5 M Antonia Bastos Individual6 David Sintes Suite7 Ovidio Gonzlez Imperial8 Isabel Castillo Doble

En la columna correspondiente al precio, aparecer ste en cuanto se introduzcael tipo de habitacin reservada. Utiliza la funcin BUSCARV .

PROCEDIMIENTO

Selecciona la celda C3, en la que debe aparecer el precio correspondiente al pri-mer cliente. Haz clic sobre el botn Pegar funcin y selecciona la categoraBsqueda y referencia y la funcin BUSCARV .


58/130


3

Copia la funcin introducida en la celda C3 hasta la celda C8 .

Funcin BUSCARH

Funciona del mismo modo que BUSCARV pero se utiliza cuando los datos se dis-ponen en filas y no en columnas (BUSCARH significa buscar en horizontal ).


En el mismo libro 13ex Bsqueda, en la hoja 2 (a la que llamars Reservas restodel ao ), incluye la siguiente lista de reservas:

A B C

1 Registro de reservas: resto del ao2 Cliente Tipo habitac Precio3 Francisco Garca Suite4 Laura Burgos Individual5 Carlos Luis Parejo Suite6 Sandra Pertegaz Doble7 Daniel Alba Doble8 Hctor Gamund Imperial

A continuacin, copia la tabla de tarifas de la hoja 1 en la hoja 2 pero disponien-do los datos en horizontal de modo que ocupen el rango A10:E12.

En la columna correspondiente al precio, aparecer ste en cuanto se introduzcael tipo de habitacin reservada. Utiliza la funcin BUSCARH .

Es el tipo de habitacin. Esdecir, B3

Es la tabla con las tarifas. Esdecir, el rango $ A$10:$C$14.Aqu es preciso el signo $ yaque la funcin se copiar a lasceldas inferiores

Dado que se trata de lasreservas de julio y agos-to, la bsqueda se haren la columna 2

Los tipos de habitacin en latabla A10:C14 no estn enorden alfabtico, por lo queaqu pondremos FALSO


59/130


4

PROCEDIMIENTO

Para aprovechar el cuadro de tarifas de la hoja 1 ( Reservas verano ),

selecciona dicho cuadro haz clic en Copiar ve a la hoja 2 y haz clic con el botn derecho sobre la celda A10 elige Pegado especial del men contextual

Selecciona la celda C3 e inserta en ella una funcin BUSCARH:

El Valor_buscado ser el tipo de habitacin; por tanto, B3. En Matriz_buscar_en selecciona el rango A10:E12y aade el signo dlar a

las dos referencias. En Indicador_fila escribiremos 3, ya que los precios buscamos se encuen-

tran en esa fila En Ordenado pondremos FALSO (los tipos de habitacin no estn en or-

den en el cuadro de correspondencias)


En la hoja 3 ( Pedido) del libro 13ex Bsqueda crea el siguiente modelo de pedido(rango A2:D17 ):

Activa la casilla Tras- poner y luego haz clic

en Aceptar


60/130


5

CALZADOS GARCAC/ Romero, 9041042 SEVILLA

PEDIDO N FECHA:

Cd. destinatario Destinatario:

CONDICIONES

Forma envo Plazo entregaForma pago Lugar entrega

Cantidad Artculo Precio unit. Importe total

En la hoja 4 ( Tabla pedido ) crea la siguiente tabla de correspondencias:

Cdigo des-tinatario Destinatario

Forma env-o Forma pago

Plazo en-trega

Lugar en-trega

T32TalleresRamrez

Areo Al contado 24 hs. Fbrica

AK7MayoristasCentrales

CaminAplazado(30 d./vta.)

3 das Almacn

N12 El dedal, SL Tren Al contado 2 das Almacn

A continuacin, en las celdas del modelo de pedido correspondientes a los datosde Destinatario, Forma envo, Forma pago, Plazo entrega y Lugar entrega introducefunciones BUSCARV de forma que al escribir el cdigo del destinatario aparezcan au-tomticamente los datos correspondientes a dicho cdigo.

Ten en cuenta que:

- El cuadro de correspondencias est en una hoja distinta a aquella en quehas de incluir la funcin BUSCARV- En este caso, la funcin no se ha de copiar a ninguna celda contigua

Guarda los cambios realizados.


61/130


1

EXCEL

EJERCICIO 14

VALIDACIN DE DATOS

Una de las razones ms frecuentes por las que la funcin BUSCARV puede nofuncionar consiste en introducir en la celda del valor buscado un valor que no se co-rresponda exactamente con ninguno de los de la tabla de correspondencias.

P.ej, en la ltima actividad del ejercicio anterior, si, como cdigo del destinatario,introducimos T22 (en lugar de T32), la funcin nos dar un mensaje de error. A veces elerror es mucho ms difcil de detectar (un espacio en bl anco, una tilde) y ms fcil decometer (si el valor buscado es el nombre de la empresa, p.ej)

La validacin de datos permite que el programa nos impida introducir en unacelda un valor distinto de aqul o aquellos que le digamos.

P.ej. Si llevamos un registro de las facturas emitidas en el mes de marzo de 2009,podemos indicar en las celdas de fecha que no pueda introducirse un da que no sea deese mes.


En un nuevo libro de Excel, que llamars 14w Validacin de datos.xls , crea en laHoja 1 ( Facturas emitidas ) el siguiente modelo (haz las columnas ms anchas de lo queaparecen aqu; especialmente, la de Cliente, Nombre ):

REGISTRO DE FACTURAS EMITIDAS

Fecha N fra.Cliente Base im-

ponibleIVA Recargo equi-valencia TOTAL

Nombre NIF Tipo Cuota Tipo Cuota


62/130


2

Debajo de este cuadro, en la misma hoja, introduce la siguiente relacin de clien-tes y su NIF:

CLIENTES NIF

MARIA LLUISA MIRALLES ROIG 64669899FFOIXES, SL B17216202MAURICIO LOPEZ UTRILLAS 56137476HPEDRO RODRIGUEZ MARTINEZ 12788030YBILIASA, SL B12215209RAMON TEJEIRA ROLO 27124587L

ARFADELL, SL B25228546 ARRIBAS, SL B24247596CABAAS, SA A49216717VALLDEVID, SA A47225330PEALBA DE SAN PEDRO, SA A42220369

Utilizaremos la herramienta de validacin de datos para:

a) que no se introduzca ningn da que no sea del mes de marzo de 2009b) que el n de factura no sea mayor de 20 (supondremos que el n de facturas

mensuales es fijo, aunque es bastante suponer)c) que el cliente sea uno de los de la lista de arribad) que el tipo de IVA se introduzca correctamente (4%, 7% o 16%)

Adems:

- En la columna NIFusa una funcin BUSCARV para que aparezca el NIF al intro-ducir el nombre del cliente.

- En la columna Recargo de equivalencia, Tipo usa una funcin SI (anidada) paraque aparezca el tipo de recargo (0,5%, 1% o 4%) segn cul sea el tipo de IVA(4%, 7% o 16%)

- En las columnas de Cuota y TOTALintroduce frmulas que calculen el conceptocorrespondiente.

PROCEDIMIENTO (PARA LA VALIDACIN DE DATOS)

a) que no se introduzca ningn da que no sea del mes de marzo de 2009

Selecciona las celdas correspondientes a la columna Fecha. Ve a Datos (men principal), Validacin


63/130


3

La pestaa Mensaje de error nos permite informar al que ha cometido unerror en la introduccin del dato. Configrala como aparece a continua-cin y, luego, haz clic en Aceptar :

b) que el n de factura est entre 1 y 20 (supondremos que el n de facturas men-suales es fijo, aunque sea bastante suponer)

Selecciona las celdas correspondientes a la columna N de factura . Ve a Datos (men principal), Validacin Configura el cuadro de dilogo Validacin de datos como sigue:

1- Haz clic aqu yselecciona Fecha

2- Establece comofecha inicial y comofecha final las que seindican

3- Haz clic en la pestaaMensaje de error


64/130


4

c) que el cliente sea uno de los de la lista de clientes

Selecciona las celdas correspondientes a la columna N de factura . Ve a Datos (men principal), Validacin Configura el cuadro de dilogo Validacin de datos como sigue:


65/130


5

En este caso no es necesario configurar ningn mensaje, ya que en esta celdabastar con elegir un elemento de la lista (no es posible cometer errores).

d) que el tipo de IVA se introduzca correctamente (4%, 7% o 16%)

Crea, bajo el registro de facturas y al lado de la lista de clientes, una lista(en columna) con los tres tipos posibles de IVA (4%, 7% y 16%)

Selecciona las celdas de la columna IVA, Tipo Establece una validacin de datos del tipo Lista , en el que la lista sea la de

los tipos de IVA creada anteriormente.

Para acabar, a fin de comprobar que el ejercicio se ha realizado correctamente,haz pruebas introduciendo dos o tres facturas inventadas y tratando de colar datosequivocados en Fecha y en N de factura.

Nota: una vez introducidas todas las reglas de validacin y la funcinBUSCARV, no est de ms ocultar las filas que contienen la relacin declientes y los tipos de IVA.

Recuerda guardar los cambios realizados.

Haz clic aqu y selecciona lasceldas en las que hayas intro-ducido la lista de clientes (perosin la celda de encabezado)


66/130


1

EXCEL

EJERCICIO 15

GESTIN DE DATOS: LISTAS

Los programas de Hoja de clculo permiten, en general, la creacin de bases dedatos no demasiado complejas, formadas por:

Registros: cada uno de los elementos o entidades sobre los que la basemuestra informacin. En una base de datos sobre los empleados de unaempresa, cada empleado ocupar un registro; en una base de datos querecoja las facturas expedidas, cada factura ser un registro, etc.

En una base de datos confeccionada en Excel (o en cualquier progra-ma de hoja de clculo), los registros se disponen en filas contiguas: cada

registro en una fila diferente. Campos: cada uno de los datos o unidades de informacin que la base in-cluye en relacin con las entidades o elementos de que se trate. En el casode la base sobre empleados de una empresa, podran ser campos a incluir:nombre, apellidos, DNI, n de afiliacin a la S.S., etc.

En estas bases de datos, los campos se disponen en columnas. En la primera cel-da de cada columna se escribe el nombre del campo (DNI, n de afiliacin, etc.).

EJEMPLO

Nombre Apellidos DNI N de afiliacin Categora prof.Jorge Torres Garca 40.001.234 071234567 4Marisa Santos Alcal 42.213.450 075469817 2Eulalio Artigues Lpez 43.219.098 071793258 1

Las principales ventajas de realizar bases de datos en Excel y no en Access (o enotro programa de gestin de bases de datos) son:


67/130


2

Resulta ms fcil crear la base en Excel que en Access

A efectos de realizacin de clculos y de anlisis numrico de los datos Ex-cel dispone de ms herramientas.

No obstante, cuando la base de datos a crear es ms compleja es preferible utili-zar el Access (u otro gestor de bases de datos) e importar sus datos desde Excel cuan-do se quieran analizar.

ACTIVIDAD A REALIZAR:

La empresa QUMICAS, S.A. ha llevado a cabo tres proyectos de investigacin enlos cuales han trabajado 10 empleados.

Los empleados que participan en el Proyecto A cobran un sueldo de 12 /hora,los del Proyecto B , de 10,81 /hora; y los del Proyecto C , de 9,01 /hora.

Cada trabajador ha realizado gastos de diferente cuanta en la realizacin delproyecto (o proyectos) en que participa, en dos conceptos diferentes: material y des-plazamientos.

Los datos concretos aparecen en la tabla siguiente:

A B C D E F G

1

2 EMPLEADOS PROY. HORAS /H. SUELDOTOTAL

MAT. DESPLAZTOS.

3Gutierrez Hermoso,M Isabel

B 320 50000 297,62 0

4Cebolla Ramos, Anto-nio

C 210 85000 505,95 35,71

5Medina Esteban,Pedro

B 150 0 0 23,81

6Muoz Muoz, Ernes-to

B 320 90000 535,71 59,52

7 Casanueva Bermejo,Laura A 350 10000 59,52 0

8Garca Jimnez, JoseLuis

A 400 50000 297,62 29,76

9Guzmn Cansado,Francisco

A 350 0 0 0

10Hinojosa Ceballos,Lourdes

C 240 0 0 26,79

11Montero Pinzn,Rosario

C 100 7000 41,67 59,52

12Ortega Romero, Vir-ginia

B 50 10000 59,52 0


68/130


3

Abre un nuevo libro en Excel y gurdalo como15ex Proyectos.En la Hoja 1 (Proyectos), en el rango A2:G12, introduce la tabla de arriba. En la

Hoja 2 (Sueldo por proyecto ), rango A2:B5 , introduce la siguiente:

A B

12 PROYECTO SUELDO POR HORA3 A 12 4 B 10,81 5 C 9,01

En la celda D3 (hoja Proyectos) introduce la funcin necesaria (funcin BUS-CARV) para que aparezca automticamente el sueldo por hora de cada empleado alteclear el proyecto al que ha sido asignado.

En la celda E3 (hoja Proyectos) introduce la frmula necesaria para calcular elsueldo total a percibir por cada empleado (que deber incluir los gastos realizados porcada empleado en material y desplazamientos).

Una vez introducidos los datos, queremos:

A.- Ordenar la lista alfabticamente, atendiendo a los apellidos y nombres delos empleados.

B.- Establecer algn sistema por el que rpida y fcilmente podamos consultar,por separado, los datos de la lista referentes a cada proyecto.

PROCEDIMIENTO:

A.- Selecciona el rango A2:G12 de la hoja 1. A continuacin, ve a Datos, Orde-nar... y configura el cuadro de dilogo de manera que los datos se ordenen alfabti-camente segn los apellidos y nombre de los empleados.


69/130


4

B.- Para ver cada vez slo los datos correspondientes a un proyecto vamos a in-troducir en la lista un filtro automtico o autofiltro .

Para ello, selecciona primero el rango de celdas B2:B12. A continuacin, ve a Da-tos , Filtros y selecciona Autofiltro .

Observa cmo en la celda B2, a la derecha, aparece una flecha hacia abajo. Alhacer clic en esa flecha aparece una lista de elementos; seleccionando uno u otro po-dremos visualizar:

Todos los elementos de la columna seleccionada (seleccionando Todas )

Tratndose de datos numricos, los 10 registros (o el nmero que le indi-quemos) ms altos o ms bajos de la lista (seleccionando Los 10 ms... )

Slo las celdas en que aparezca el Proyecto A (con lo que se visualizarn slolos datos referentes a este proyecto); o slo las del Proyecto B o las del Pro-yecto C (seleccionando las letras A, B o C)

Tambin podemos filtrar datos segn cualquier criterio que se nos ocurra,dentro de las posibilidades ofrecidas por el cuadro de dilogo que aparece alseleccionar Personalizar... (las posibilidades son mayores cuando los datosson numricos)

ACTIVIDAD A REALIZAR:

Inserta 3 nuevas hojas en el Libro 15ex Proyectos. Copia en ellas el rango A2:G12 de la hoja Proyectos. Para ello:

Con el cursor en cualquier celda de la hoja Proyectos, pulsa la tecla Shift(maysculas) y, sin dejar de pulsarla, haz clic con el botn derecho sobre lasolapa de la Hoja 3. En el men emergente elige la opcin Insertar . Haz dobleclic en Hoja de clculo .


70/130


5

Con el cursor en la hoja Proyectos, selecciona el rango A2:G12 y activa la op-cin Copiar . A continuacin, haz clic en la solapa de la Hoja 3 y, pulsando latecla Shift , clic en Hoja 5. Seguidamente, selecciona la celda A2 de la Hoja 5 yactiva la opcin Pegar : el rango A2:G12 se pegar en las hojas 3, 4 y 5 almismo tiempo.

Finalmente, haz clic con el botn derecho sobre la solapa de cualquiera delas tres hojas seleccionadas y elige Desagrupar hojas . Si no lo hicieras, todo loque escribieras en cualquiera de estas 3 hojas, se escribira tambin en lasotras.

Renombra las 3 hojas nuevas como Proyectos 2, Proyectos 3 y Proyectos 4.

Tras copiar el contenido en las dems hojas, establece en la hoja Proyectos un filtro para que slo aparezcan los empleados del proyecto A.

En la hojaProyectos 2:

Ordena los datos en funcin de la cantidad de horas dedicadas al mismo porcada empleado, de ms a menos (orden descendente)

Inserta un filtro en el rango E2:E12 y configralo de modo que se muestrenslo los datos de los empleados cuyo sueldo to tal sea superior a 2000 . Usa laopcin Personalizar... explicada antes (pg. 4).


Ordena los datos en funcin del sueldo total de los empleados, de ms a me-nos.

Filtra los datos de manera que se muestren nicamente los 5 sueldos ms bajos. Usa la opcinLas 10 ms... .


71/130


6


Ordena los datos en funcin del sueldo por hora cobrado por cada empleado,de menos a ms.

Filtra los datos de manera que se muestren slo los datos de los empleados cu-yo apellido empieza por M.

Inserta tres hojas ms, de la manera ya vista, y nmbralas como Proyectos 5,Proyectos 6 y Proyectos 7. Copia en ellas los datos del rango A2:G12 de la hoja Proyec-tos y establece las ordenaciones y filtros siguientes (atrs):

Ordenacin Filtro

Proyectos 5Segn la cantidad inverti-da en desplazamientos,de ms a menos

Slo los datos del Proyecto B

Proyectos 6Segn la cantidad inverti-da en material, de menosa ms

Slo los datos de los 3 emplea-dos que cobren ms euros porhora

Proyectos 7 Segn el proyecto, ensentido descendente

Slo los datos de los empleadosque hayan gastado ms de 15euros en material


72/130

EJERCICIO 16

1

EXCEL

EJERCICIO 16

GESTIN DE LISTAS: AUTOFILTROS MLTIPLES Y FILTROS AVANZADOS

Autofiltros mltiples

En el ejercicio anterior, la herramienta Autofiltro se ha utilizado limitando los cri-terios de filtrado a una sola columna.

Evidentemente, es posible extender esos criterios a ms de una columna si apli-camos el autofiltro sin seleccionar ninguna columna en concreto.


Sobre la lista de responsables de proyectos del ejercicio anterior, aplica filtros mlti-ples que muestren slo los registros que cumplan las siguientes condiciones:

- Empleados del proyecto B que hayan dedicado ms de 300 horas.- Empleados cuyo sueldo est entre 2000 y 4000 euros y que cobren algo en con-

cepto de desplazamientos .- Empleados cuyo apellido empiece por M o por G y que hayan participado en el

proyecto B o en el C.

PROCEDIMIENTO

Copia la hoja Proyectos del libro 15ex Proyectos a otro libro y llama a ste 16ex Autofiltros mltiples . Para copiar toda la hoja rpidamente y conservar los formatos yla configuracin de la hoja, haz clic con el botn derecho sobre la pestaa de la hoja yselecciona Mover o copiar Luego, configura el cuadro de dilogo como sigue:


73/130

EJERCICIO 16

2

Ya en el nuevo libro, elimina el autofiltro de la columna Proyectos y, seleccionan-do cualquier celda del encabezado de la tabla, aplcalo de nuevo, esta vez a todas lascolumnas ( Datos, Filtro, Autofiltro ).

Haz dos copias de la hoja Proyectos en el mismo libro y llmalas Proyectos 2 yProyectos 3 .

- En la hoja Proyectos configura los filtros que haga falta para que aparezcan slolos empleados del proyecto B que hayan dedicado ms de 300 horas.

- En la hoja Proyectos 2 haz lo mismo para que aparezcan los empleados slo cu-yo sueldo est entre 2000 y 4000 euros y que cobren algo en concepto de desplazamientos

- Finalmente, en la hoja Proyectos 3 los filtros se configurarn para que mues-tren nicamente a los empleados cuyo apellido empiece por M o por G y que

hayan participado en el proyecto B o en el C

Filtros avanzados

Incluso los autofiltros mltiples tienen sus limitaciones.P.ej, en el caso anterior, permiten mostrar a los empleados del proyecto B que

hayan dedicado ms de 300 horas pero no permiten mostrar a los empleados quehayan participado en el proyecto B o hayan dedicado ms de 300 horas. La diferenciaes sutil pero muy importante.

Otro ejemplo lo mostrar ms claramente. Dada la siguiente lista de proveedores(hay que imaginarse que es mucho ms larga):

Proveedor Pedidos conretrasoPedidos

devueltosA 5 3B 2 0C 12 1

D 1 0E 8 2

No olvides activaresta casilla!


74/130

EJERCICIO 16

3

Nuestra empresa quiere cortar relaciones con todos los proveedores que sehayan retrasado en ms de 5 pedidos (el C y el E) o hayan servido ms de dos defec-tuosos (el A). Te encargan que hagas una lista. Si aqu aplicas los autofiltros mltiples,la lista que obtengas incluir o slo al proveedor A o slo al C y al E pero nunca a lostres (que es lo que se pide).

Los filtros avanzados sirven para este fin y tienen, adems, otras ventajas quevamos a ver.


Para empezar con algo sencillo, haremos lo del ejemplo antes citado.Guarda en tu memoria USB un nuevo libro de Excel con el nombre 16ex Filtros

avanzados y crea en la hoja 1 (que llamars Proveedores ), en el rango A6:C11 la listaanterior de proveedores, pedidos con retraso y devueltos.

A continuacin, aplica un filtro avanzado que permita elaborar una lista de losproveedores con ms de 5 pedidos retrasados o ms de 2 defectuosos.

PROCEDIMIENTO

Selecciona las celdas A6:C6 y copia su contenido en el rango A1:C1.En la celda B2, escribe, como criterio de filtrado, la expresin >5.En la celda C3, escribe, como criterio

curso excel andres

Documents