universidad autónoma metropolitana -...

Universidad Autónoma Metropolitana

Unidad Iztapalapa

Paquete de “Prácticas para el Curso de Excel”

(40 hrs.)

Gerardo Gutiérrez Jiménez

Diciembre de 2007

Prácticas para el Curso de Excel

Gerardo Gutiérrez Jiménez 1

C o n t e n i d o :

Operadores matemáticos 2

Práctica No. 1 Preparar la zona de trabajo 3

Práctica No. 2 Aspectos básicos de la hoja de cálculo 4

Práctica No. 3 Preparar la hoja de cálculo 5

Práctica No. 4 iniciación a las fórmulas 6

Práctica No. 5 Utilizando Excel como calculadora 7

Práctica No. 6 Fórmulas básicas y de relleno 8

Práctica No. 7 Tablas de multiplicar 9

Práctica No. 8 Aspecto de las celdas, bordes y sombreado 12

Práctica No. 9 Formato de celdas: alineación del texto 13

Práctica No. 10 Formato de celdas y formato de los datos 15

Práctica No. 11 Referencias a los datos 19

Práctica No. 12 Distinción entre referencias absolutas y relativas 24

Práctica No. 13 Aplicación de fórmulas, referencias relativas y absolutas 25

Práctica No. 14 Formulas y funciones 27

Práctica No. 15 Funciones promedio, contar, truncar y formato de # 41

Práctica No. 16 Autoformato de tablas y bunciones de búsqueda 42

Práctica No. 17 Funciones SI, Y / O 43

Práctica No. 18 Función SI anidada 45

Práctica No. 19 Gráficos primera parte 46

Práctica No. 20 Gráficos segunda parte 54

Práctica No. 21 Gráficos avanzados 56

Práctica No. 22 Funciones de texto 58

Práctica No. 23 Formato condicional y de texto 59

Práctica No. 24 Propiedades de las potencias 62

Práctica No. 25 Tablas dinámicas 1ª. Parte 63

Práctica No. 26 Tablas dinámicas 2ª. Parte 67

Práctica No. 27 Grabadora de macros 69

Práctica No. 28 Macros en Visual Basic: autoarrancables 71

Práctica No. 29 TEST No. 1 73





Respuestas a los TEST 83



Operadores matemáticos

Operador aritmético Significado (Ejemplo) + (signo más) Suma (3+3) - (signo menos) Resta (3-1)

Negación (-1) * (asterisco) Multiplicación (3*3) / (barra oblicua) División (3/3) % (signo de porcentaje) Porcentaje (20%) ^ (acento circunflejo) Exponenciación (3^2) RAIZ( ) Raíz del valor ( ) PI() (genera el valor) Número π

Operador de comparación Significado (Ejemplo) = (signo igual) Igual a (A1=B1) >(signo mayor que) Mayor que (A1>B1) <(signo menor que) Menor que (A1<B1) >= (signo igual o mayor que) Igual o mayor que (A1>=B1) <= (signo igual o menor que) Igual o menor que (A1<=B1) <>(signo distinto de) Distinto de (A1<>B1)

Operador de texto Significado (Ejemplo) & ("y" comercial) Conecta o concatena dos valores para generar un valor de

texto continuo ("Viento"&"norte")

Operador de referencia

Significado (Ejemplo)

: (dos puntos) Operador de rango que genera una referencia a todas las celdas entre dos referencias, éstas incluidas (B5:B15)

, (coma) Operador de unión que combina varias referencias en una sola (SUMA(B5:B15,D5:D15))

(espacio) Operador de intersección que genera una referencia a celdas comunes a las dos referencias (B7:D7 C6:C8)



Práctica No. 1 “Preparar la zona de trabajo” Empezamos con un libro nuevo y vacío, deberás reconocer y cambiar los aspectos que a continuación se indican: Escribe el texto “Iniciación” en la casilla C19, con el tipo de letra Courier New, tamaño 13, cursiva. . Renombra la primera hoja a “Inicio” y elimina las restantes hojas. . Rellena la celda B3 con color rojo. . Remarca mediante un marco de color negro las celdas A9:A10. . Escribe tu nombre y apellidos en la celda A1, utiliza la combinación de celdas y centra el texto

entre las celdas combinadas. . Introduce la fórmula: =5*4 ;en la celda C10. Realiza los pasos anteriores para conseguir una tabla como esta:



Práctica No. 2 “Aspectos básicos de la hoja de cálculo” En este ejercicio deberás realizar las siguientes operaciones: Llama a la celda A1, “Mes” y además introduce el texto “Meses”, ponlo en rojo, negrita y de tamaño 18; Después introduce los meses de enero a diciembre, en las celdas inferiores.

Renombra la hoja1 a “Calendario”, elimina la Hoja2 y Hoja3, finalmente inserta una nueva hoja llamada “Clientes”. Pon la hoja en horizontal y pon 3 cm de márgenes en cada lado. Combina las celdas A1,B1,C1. Selecciona las celdas A1:C13 y establece este rango como área de impresión, desde el menú archivo / Área de impresión. Resultado final:

Realiza una vista previa para ver el resultado antes de imprimir.



Práctica No. 3 “Preparar la hoja de cálculo” Observa los siguientes datos:

Deberás introducir los datos tal y como se muestran y seguir los siguientes pasos para preparar la página y mostrar la información correctamente: Alto de la fila 1: 30 Alto del resto de filas: 12 Ancho de columna 1: 30 Ancho de columna 2: 8 Ancho de columna 3: 16 Ancho de columna 4: 22 Ancho de columna 5: 14 Márgenes de la página: Sup. e Inf. : 3cm Dcha. e Izq: 2cm Letra: Times New Roman ,10 No olvides que la 1ºfila tiene las celdas con un sombreado gris y letra color amarillo y establece la hoja de forma horizontal. Debes obtener lo siguiente:



Práctica No. 4 “iniciación a las fórmulas”

Vamos a practicar fórmulas sencillas y algunas características básicas. Copia los datos de la tabla: En otras celdas deberás: . 1º tienes que cambiar el formato a moneda de � . 2º Después realiza las operaciones que se indican . El cálculo del IVA será del 16%, con una cantidad de 100, estos datos los debes insertar tu mismo/a. Nota: el cálculo del IVA, deberás hallarlo primero, utilizando las celdas B25 y B26; después opera como si de una calculadora se tratase. El resultado debe ser como el que sigue:



Práctica No. 5 “Utilizando Excel como calculadora”

Desarrolla las siguientes expresiones y determina su resultado: R= 1.00129139 R= 1.126730084 El asterisco (*) es el signo de multiplicar y el acento circunflejo (^) se usa para indicar las potencias.

Excel primero calcula las potencias (282), luego las operaciones de multiplicar y dividir, y finalmente sumar y restar; los paréntesis nos permiten alterar este orden. Por tanto, en el denominador del ejemplo anterior no han sido necesarios los paréntesis indicados en rojo ( ) en esta nueva versión:

Ejemplo: =((1726-104)/(2,3*0,9)-1)/(28^2-(1,005/0,7))

2

1726 104 12.3*0.9

1.005280.7

X

−−

=−

log(2)cos(30) 3

X =



Práctica No. 6 “Fórmulas básicas y de relleno” En este caso deberás escribir las fórmulas correspondientes a: Fecha y hora Fecha Año, sacado de la fórmula de fecha y hora Año, sacado de la fórmula de fecha Después tendrás que completar las siguientes series usando la función de relleno

Tienes que obtener lo siguiente:

Nota: la función año necesita de otra función para mostrar el año, podemos utilizar dentro de la función año, la celda A32 ó A31, para que obtenga de esos datos el año correspondiente. =AÑO(A32), donde A32 contiene la fecha y, por lo tanto, el año.



Práctica No. 7 “Tablas de multiplicar”

El objetivo de este ejercicio es comprender la utilidad del uso de las referencias mixtas, es decir, aquellas en las que se fija la fila o la columna ($A1, A$1).

El ejercicio consiste en crear una tabla de multiplicar como la que se muestra a continuación en la que cada celda contiene el producto de la fila por la columna correspondiente.

A B C D E F G H I J

1 TABLAS DE MULTIPLICAR

2

3 1 2 3 4 5 6 7 8 9

4 1

5 2

6 3

7 4

8 5

9 6

10 7

11 8

12 9

1. Cree un nuevo libro de trabajo y copie la tabla anterior, en las celdas que se indican, en una hoja a la que le dará el nombre de “REFERENCIAS” (pulsar con el botón derecho sobre la ficha Hoja1 y seleccionar la opción Cambiar nombre, o haciendo doble clic directamente sobre la ficha Hoja1).

2. Elimine las dos hojas restantes del libro: Hoja2 y Hoja3 (pulsar con el botón derecho sobre la ficha Hoja1 y seleccionar la opción Eliminar).

3. Obtenga una tabla resultado donde el valor de cada celda es el producto de la fila por la columna, para ello debe crear una fórmula en la celda B4 utilizando el tipo de referencias adecuado y copiarla para el resto de la tabla.



Solución

La fórmula que hay que introducir en cada celda es el producto del valor contenido en la columna A y en su misma fila, por el valor contenido en la celda de su misma columna y fila 3 tal y como muestran las flechas en la tabla anterior donde el valor de la celda D5=A5*D3.

Así los resultados a obtener serían:

- B4=A4*B3 - C4=A4*C3

- D4=A4*D3

- B5=A5*B3 - B6=A6*B3

- Etc.

Si observamos las fórmulas anteriores nos daremos cuenta que las referencias a la columna A y a la fila 3 nunca cambian. Sin embargo si sólo se usan referencias relativas al copiar la fórmula de la celda B4=A4*B3, al resto obtendríamos los siguientes resultados:

- Al copiarla hacia la derecha: C4=B4*C3, D4=C4*D3, E4=D4*E3, etc. Es decir, la referencia a la columna A en el primer multiplicando va cambiando, la solución sería fijar esta columna.

- Al copiarla hacia abajo: B5=A5*B4, B6=A6*B5, B=A7*, etc. En este caso la referencia a la fila 3 en el segundo multiplicando va cambiando, la solución será fijar esta fila.

Por tanto la fórmula correcta a introducir en la celda B4 sería =$A4*B$3.

A B C D E

TABLAS DE MULTIPLICAR

2

3 1 2 3 4

4 1 =$A4*B$3 =$A4*C$3 =$A4*D$3 =$A4*E$3

5 2 =$A5*B$3 =$A5*C$3 =$A5*D$3 =$A5*E$3

6 3 =$A6*B$3 =$A6*C$3 =$A6*D$3 =$A6*E$3

5. Copiar la tabla del ejercicio anterior a partir de la fila 14 y multiplicar los valores de las celdas de las dos tablas.

- Para ello seleccionamos el rango A3:J12 pulsamos el botón Copiar, seleccionamos la celda A14 y pulsamos el botón Pegar.

- A continuación creamos la tabla producto tal y como aparece a continuación a partir de la celda L14.



A B C D E F G H I J K L M N O P Q R S T U

13 COPIA: PRODUCTO DE LAS DOS TABLAS:

14 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9

15 1 1 2 3 4 5 6 7 8 9 1

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

17 3 3 6 9 12 15 18 21 24 27 3

18 4 4 8 12 16 20 24 28 32 36 4

19 5 5 10 15 20 25 30 35 40 45 5

20 6 6 12 18 24 30 36 42 48 54 6

21 7 7 14 21 28 35 42 49 56 63 7

22 8 8 16 24 32 40 48 56 64 72 8

23 9 9 18 27 36 45 54 63 72 81 9

Solución

- En cada celda de esta nueva tabla habrá que introducir el producto del valor contenido en esa misma celda en la tabla de multiplicar y el valor contenido en esa misma celda en la tabla Copia. Es decir, la nueva tabla contendrá los mismos valores elevados al cuadrado.

Los resultados que queremos obtener serán: - M15=B4*B15

- N15=C4*C15

- O15=D4*D15

- M16=B5*B16 - N16=C5*C16

- Etc.

En la secuencia anterior se puede observar que en este caso todas las referencias en la fórmula va cambiando, por tanto, en este caso debemos utilizar referencias relativas en la fórmula y la fórmula correcta a introducir en la celda M15 es =B4*B15.

6. Guardar el libro con el nombre “TABLAS DE MULTIPLICAR”.



Práctica No. 8 “Aspecto de las celdas, bordes y sombreado” En este ejercicio aprenderás a darles un aspecto diferente a tus celdas. Mediante el formato de celdas, aplicando bordes, tramas y colores, trata de conseguir un aspecto como el que te proponemos. Con ello practicarás el uso de estas opciones.



Práctica No. 9 “Formato de celdas: alineación del texto” • Objetivo: Explicar las distintas alineaciones que puede tener un texto dentro de una celda. • Enunciado: El ejercicio consiste en escribir el texto que aparece a continuación y, aplicarle la alineación necesaria a las distintas celdas para obtener el mismo resultado.

Grabar el libro de trabajo como “Alineación.xls”, que deberá de constar de una única hoja de cálculo que contendrá el trabajo realizado llamada “datos”.



• Solución:

Este ejercicio, aunque tedioso, es bastante fácil, ya que consiste en repetir la misma operación varias veces, cambiando, eso sí, algunos parámetros. El modo de resolución es el siguiente.

Haciendo clic con el botón derecho del ratón en la celda a la que queramos aplicarle la alineación, y eligiendo formato de celdas podemos conseguir todos los resultados deseados.

A modo de ejemplo tenemos la siguiente imagen, en la que se recoge el cuadro de dialogo que hemos de utilizar en este ejercicio

Simplemente nos queda ver como se haría la fila 9, esto lo hemos conseguido pulsando el botón de combinar y centrar



Práctica No. 10 “Formato de celdas y formato de los datos”

• Objetivo: Practicar con los distintos formatos de números que Excel proporciona para dar un aspecto más legible a nuestro trabajo.

• Enunciado: El ejercicio consiste en escribir la columna “Valor” del ejemplo proporcionado (datos sombreados) y, posteriormente, realizar las siguientes operaciones:

- Copiar los datos de la columna “Valor”, a las distintas columnas de “Formato” existentes.

- Aplicarle a las columnas de “Formato” el formato adecuado para obtener el mismo

resultado que el proporcionado al alumno.

- La sección de Alumnos está basada en un formato condicional, de forma que si el alumno tiene una nota superior o igual a 5, la fila aparecerá de color verde, en caso contrario, aparecerá de color rojo.

El planteamiento es el siguiente:

Grabar el libro de trabajo como “Formatos.xls”, que deberá de constar de una única hoja de cálculo que contendrá el trabajo realizado llamada “datos”.



• Solución:

Para realizar este ejercicio vamos a ir describiendo los formatos de cada módulo, detallando en cada caso los aspectos que estimemos más relevantes. Lo primero que debemos saber es que para darle un formato determinado a una celda hemos de estar sobre ella y seguidamente hace clic con el botón derecho de ratón y elegir formato de celdas o bien elegir esto mismo en el menú Formato de la barra de herramientas.

En el bloque de salarios solo nos vamos a detener para comentar como se harían el formato-3 y el 4, los formatos que habría que darle son los siguientes:

Como vemos el formato será personalizado, y el tipo es #.##0. “miles de pesetas”, aquí las almohadillas le indican a Excel que escriba el número solo con sus cifras significativas en cambio el cero obliga a poner el número sin importarnos si la cifra es significativa o no. Los puntos son los separadores de miles, y como podemos observar después del cero hay un punto que hace que Excel omita las tres últimas cifras. Cuando tengamos que poner algún texto simplemente lo colocamos entre comillas. El formato-4 será #.##0.. “millones de pesetas” aquí cabe destacar los dos puntos consecutivos finales que hacen que se omitan las seis últimas cifras del número. Para el bloque de porcentajes sólo vamos a comentar el Formato-4, el cual sería 00000,0% que como dijimos en el apartado anterior hace que se pongan todas las cifras, incluso las no significativas. En los tres formatos anteriores solo hemos de jugar con las posiciones decimales que queremos poner.



Vamos ahora con el bloque de caja, que es quizás el más complejo. En Excel los formatos de celdas complejos los vamos a dividir en cuatro partes, todas ellas separadas por punto y coma. El primer bloque nos indica como serán los números positivos, el segundo los negativos, el tercero el cero y por último el formato de texto. Teniendo en cuenta esto y usando como referencia el Capitulo 3 del manual obtenemos los siguientes resultados:

Formato-1: [Azul]#.#00 “ptas.”;[Rojo]-#.#00 “ptas.”;[Verde] “----“ ;@ Formato-2: [Azul]#.#00 “€.”;[Rojo]-#.#00 “€”;[Verde] “----“ ;@ Formato-3: [Azul]”Entrada:” #.#00,00;[Rojo]”Salida:”#.#00,00;[Verde] “cero”;@ Para Totales es muy fácil, ya que solo hemos de jugar con la opción que nos da Excel de repetir un carácter de forma indefinida con la utilización del * de esta forma el formato-1, formato-2 y formato-3 quedarían de la siguiente forma:

Para los bloques de Teléfonos y Códigos postales, no vamos a dar ninguna explicación, ya que todo lo necesario se ha dicho antes, simplemente daremos el resultado final del formato.

Teléfonos

Formato-1: Especial, Número de teléfono

Formato-2 Personalizada, (000) 00 00 00 Formato-3 Personalizada, “Prefijo:” 000 – “Número:” 00 00 00

Códigos postales

Formato-1: Número, con separación de miles Formato-2: Personalizada, “provincia:” 00 “municipio” 000

Formato-3: Personalizada, 0 0 0 0 0

Formato-3: Personalizada, 0 0 . 0 0 0 En el bloque de fechas vamos hemos de tener en cuenta que tanto para los días como para los meses el formato variará de la siguiente forma:



d nos devuelve el día solo con cifras significativas.

dd nos devuelve el día con cifras no significativas. ddd nos devuelve el día de la semana de forma abreviada.

dddd nos devuelve el día de la semana sin abreviar.

Igualmente ocurre para los meses solo que en lugar de una d hemos de poner una m.

De esta forma los formatos quedarán de la siguiente manera:

Para el bloque de Horas los formatos a aplicar serán los siguientes:

Formato-1: Personalizada, h:mm AM/PM.

Formato-2: Personalizada, h:mm:ss. Formato-3: Personalizada, “hora – “ h “minutos – “ mm “segundos –“ ss.

Formato-4: Personalizada, h . mm . ss.

Por último tenemos el bloque Alumnos, lo que hay que aplicarle será un formato condicional, que quedará de la siguiente forma:

Lo que hacemos es poner dos condiciones, la primera nos rellena de verde las celdas si el valor de la nota es igual o superior a cinco y la segunda de rojo si el valor es inferior a cinco.



Práctica No. 11 “Referencias a los datos”

• Objetivo: Practicar los distintos tipos de referencias a celdas y rangos existentes (relativas, absolutas y mixtas), las cuales permiten indicar a Excel en qué celdas buscar los datos a usar en una fórmula.

• Enunciado: Crear un nuevo libro de trabajo, que se llamará Referencias.xls, y realizar en distintas hojas de cálculo los siguientes planteamientos:

1. Referencias relativas (hoja: Relativas) Este tipo de referencias CAMBIAN al copiar la fórmula de una celda a otra.

Realizar la siguiente tabla utilizando referencias relativas para calcular los totales.

NOTA: El alumno debe escribir la línea de Producto, Precio y Unidades. Para calcular la línea de TOTAL debe escribir la fórmula apropiada en una de las columnas y copiarla al resto.

2. Referencias absolutas (hoja: Absolutas)

Este tipo de referencias NO CAMBIAN al copiar la fórmula de una celda a otra. Realizar la siguiente tabla utilizando referencias absolutas (o mixtas) para alcular los totales.

Nota: El alumno debe escribir la columna de Precio y Unidades. Para calcular la columna de TOTAL debe escribir la fórmula apropiada en la primea línea y copiarla al resto.

3. Utilización de Nombres (hoja: Nombres)



El dar un nombre a una celda o conjunto de celdas disminuye el error al trabajar con referencias relativas/absolutas.

NOTA: El alumno debe escribir la columna de Precio y Unidades. Posteriormente, debe asignar un nombre a la celda que contiene el valor del precio y, calcular la línea de T OTAL haciendo referencia a este nombre.

4. Referencias entre distintas Hojas de Cálculo Crear una nueva hoja de cálculo llamada Productos que contenga la siguiente información:

A continuación, crear una nueva hoja llamada Unidades como la siguiente:

NOTA: Para obtener el precio en cada uno de los meses, será necesario multiplicar el nº de unidades del producto por el precio de cada producto en el mes correspondiente que se encuentra en la hoja de Productos.

5. Referencias entre distintos Libros de Trabajo Crear un nuevo libro de trabajo que se llame Referencias2.xls, que contenga una hoja de cálculo con el siguiente aspecto:



NOTA: El precio de los productos se debe obtener automáticamente mediante referencias a la hoja Productos del libro de trabajo Referencias. Los totales de los productos para cada uno de los meses, así como el total de ventas, deberán calcularse mediante la fórmula correspondiente.



• Solución:

1. Este ejercicio es bastante simple si tenemos claro el concepto de referencias en

Excel, en el cual no vamos a entrar con detalles ya que se explico en el anterior curso,

simplemente vamos a recordar que para realizar una referencia relativa (cambian al

copiar la fórmula) solo hemos de colocar el nombre de la celda, en cambio cuando la

referencia es absoluta o mixta hemos de colocar el símbolo $ delante del indicador de

la fila, de la columna o de ambas, para que este tipo de referencia surta efecto.

Ahora con copiar y pegar esta celda en C8 y C8 conseguimos el resultado deseado usando referencias relativas.

Copiamos y pegamos hacia abajo y tendríamos el ejercicio resuelto con referencias

absolutas.

3. El uso de nombres de celdas para hacer referencias (son absolutas) es bastante útil, ya evita

errores. Para darle un nombre a una celda haremos lo siguiente:



Seguidamente daremos el nombre que deseemos a la celda. Una vez hecho esto, cuando queramos usar el valor de esa celda solo tendremos que dar su nombre.

4. Vamos ahora a comentar las referencias entre hojas y entre libros, este tipo de referencias son siempre absolutas, y las podemos realizar de dos formas

diferentes, la primera y más fácil será usando el ratón haciendo clic el la celda desde la cual queremos recuperar el valor (esto solo es posible cuando estamos introduciendo una fórmula).

La otra será haciendo uso de la sintaxis que nos ofrece Excel para identificar

la posición de cada celda, esta sería la siguiente:

‘[Nombre_Libro.xls]Hoja_nº’!Celda.

Si en esta fórmula omitimos el nombre del libro nos indicará que la hoja referenciada

es del mismo libro de trabajo. Veamos como sería en nuestro ejercicio

5. Estas tablas son las correspondientes a las referencias entre hojas del mismo libro, el

siguiente resultado será el correspondiente a las referencias entre libros distintos.

Hemos de hacer lo mismo para todos los precios de cada mes.



Práctica No. 12 “Distinción entre referencias absolutas y relativas” Observa la siguiente imagen:

Introduce los datos tal y como se muestran, aprenderás no solo a ver la diferencia entre relativa y absoluta, sino también a crear series de números y de fórmulas. Después señala las celdas como en la figura, pon el cursor del ratón encima del cuadrito pequeño de abajo a la derecha (de las celdas señaladas), presiona el botón izquierdo del ratón y sin soltarlo baja el ratón hasta la celda “A24”; verás cómo, tras soltar el botón, se rellenan las celdas con la cifra correspondiente a esa serie (1,2,3,4,5⁄). En la celda B15, deberás poner la fórmula “=A15*10”, sin las comillas. Si haces lo mismo de antes, marcar esa sola celda y arrastrando del cuadrito pequeño hacia abajo, verás cómo la fórmula cambia en cada celda, para multiplicar por 10 la celda correspondiente a su fila. Ahora introduce el valor “10” en E15, vuelve a arrastrar del cuadrito pequeño hacia abajo,;en este caso, al no detectar una serie numérica, de texto o una fórmula, copiará el mismo valor en las restantes. Las referencias absolutas son las que llevan el símbolo $ dentro del nombre de la celda, es decir, $A$1, $B$23⁄ Sirven para que al copiar y la fórmula a otra celda, esta no cambie y obtengamos el mismo resultado. Copia en F15, la fórmula “=$A$15*10”, puedes utilizar la tecla F4, con el cursor de escritura dentro del nombre de la celda, para que añada automáticamente los dos símbolos $ que contiene cada referencia absoluta. Ahora prueba a arrastrar del cuadrito pequeño hacia abajo, con la celda de la fórmula seleccionada; comprobarás que en este caso la fórmula no varía y en cada celda está la misma fórmula, obviamente con el mismo resultado. Debe resultarte como muestra la figura siguiente:



Práctica No. 13 “Aplicación de fórmulas, referencias relativas y absolutas” Ahora te planteamos que apliques y mezcles referencias relativas y absolutas en una misma fórmula, por ejemplo en un crédito de interés fijo y cuota variable. Empieza introduciendo estos datos:

En esta práctica te proponemos que realices un préstamo, que inicia con un capital de 30.000, en 12 pagos, con un 7% anual como interés y una cuota variable, que será la suma de dividir el pago total entre los 12 meses más el interés correspondiente a ese mes. El primer mes no se pagan intereses, en el resto de meses se pagarán de forma variable, cada vez menos, hasta el último mes. De forma que cada mes se pague el interés correspondiente, que no proporcional. Es decir, el 1er.mes no se pagan, el 2º se paga mucho y se prorratean las cantidades para que baje la cuantía del interés en cada mes. El ejercicio consiste en que copies las fórmulas dadas en las restantes celdas, y compruebes como, a un combinando las dos referencias (relativa y absoluta) en una misma fórmula, se comportan de forma distinta.



Para ello deberás usar referencias absolutas y relativas, apoyándote en las casillas que contienen los valores de capital e interés, incluyendo también los resultados que vayas obteniendo para hallar la amortización. En la fórmula de la fecha =B6+31, que copiarás en las celdas inferiores, le sumamos 31 a la celda superior para que sume 31 días sobre esa fecha y así nos contabilice mes a mes, obteniendo 12 pagos por año. El resultado final debería ser:

. Por último, prepara la página de forma horizontal, con márgenes superiores e inferiores a 2 cm. y los márgenes laterales con 2,5 cm. Centra la página horizontalmente y verticalmente.



Práctica No. 14 “Formulas y funciones” • Objetivo: Practicar el uso de: - Fórmulas: mediante éstas se llevan a cabo todos los cálculos en una hoja de cálculo. - Funciones: son fórmulas incorporadas de Excel que a partir de unos valores realizan una

operación devolviendo un resultado. • Enunciado: Crear un nuevo libro de trabajo, que se llamará Formulas y Funciones.xls, y realizar

en distintas hojas de cálculo los siguientes planteamientos: 1. Función SUMA (hoja: Suma)

Realizar la siguiente tabla:

NOTA: El primer total es necesario calcularlo utilizando una fórmula que sume las tres celdas, mientras que el segundo total debe calcularse utilizando la función SUMA.

2. Función PRODUCTO (hoja: Producto) Realizar la siguiente tabla:

NOTA: El primer total es necesario calcularlo utilizando una fórmula que multiplique las tres celdas, mientras que el segundo total debe calcularse utilizando la función PRODUCTO. 3. Función PROMEDIO (hoja: Promedio)



Realizar la siguiente tabla:

NOTA: El primer total es necesario calcularlo utilizando una fórmula que permite obtener la media aritmética de los tres valores, mientras que el segundo total debe calcularse utilizando la función PROMEDIO.

4. Función CONTAR (hoja: Contar) Realizar la siguiente tabla y utilizar la función CONTAR para determinar cuántos valores numéricos existen en el rango de datos considerado.

Utilizar también la función CONTARA y comprobar en qué se diferencia de la función CONTAR. Para contar el número de celdas que contienen letra A, se usará

la función CONTAR.SI que se verá a continuación.

5. Función SI (hoja: Si)

Realizar la siguiente tabla y determinar utilizando la función SI, si el valor de A es mayor, igual o menor que B:



Asignar ahora los siguientes valores: A = 200 y B = 100. ¿Qué ocurre? A continuación, asignar a A y a B el mismo valor, de forma que el resultado de la

función debe ser A es igual a B.

6. Función CONTAR.SI (hoja: Contar.Si) Realizar la tabla siguiente y utilizando la función CONTAR.SI determinar cuántas celdas tienen un valor mayor que cero:

Utilizando ahora los siguientes datos, determinar cuántas celdas tienen un valor mayor, igual o menor que cero.

7. Función BUSCAR (hoja: Buscar) Escribir la siguiente tabla: para determinar



A continuación utilizar la función BUSCAR para determinar: - Los nombres de los colores que tienen de valor “4,5” y “8,9”.

- Los colores con el valor más alto y con el valor más bajo (Estos valores se tendrán que calcular).

NOTA: El formato de la función BUSCAR es el siguiente:

BUSCAR (valor_buscado; vector_de_comparación; vector_resultado)

8. Función DIASEM (hoja: DiaSem)

Escribir la columna Fecha y, utilizando la función DIASEM calcular el día de la semana (en número) al que corresponde la fecha correspondiente.

Posteriormente determinar el día de de la semana en texto utilizando para ello la función SI o la función buscar, según se desee, tal y como aparece en la siguiente tabla:



9. Función PAGO (hoja: Pago)

Se va a realizar un estudio de un préstamo hipotecario, utilizando para ello la

función financiera PAGO.

El préstamo solicitado es de 90.000,00 € en 15 años. El banco nos ofrece un interés Anual del 11,5 %. Partiendo de estos datos y, suponiendo que el interés no va a cambiar, calcular: - La cuota mensual que deberemos de pagar a lo largo de toda la vida del préstamo. - El número de meses que debemos de estar pagando. - La cantidad total que habremos pagado una vez finalizado el préstamo. - La cantidad que se lleva el banco en cuestión de intereses.

El resultado que deberá de obtener el alumno será como muestra la siguiente tabla:

Entrada:

Préstamo solicitado 90,000.00 Tipo de interés anual 11.50% Período de amortización 15 años

Salida:

A continuación, utilizar lafunción PAGOINT para determinar la cantidad de intereses

que pagamos y la cantidad de capital que amortizamos en el primer año de la hipoteca



en cada uno de los pagosmensuales como muestra el siguiente desglose:

A continuación, hacer distintas pruebas variando la cantidad de préstamo solicitado, interés y período de amortización, para comprobar el ajuste de la función PAGO y PAGOINT (considerándose siempre que se trata de un interés constante en el período indicado).

10. Función TASA (hoja: Tasa) La función TASA devuelve la tasa de interés de un préstamo en un año.

Su formato básico es: TASA (nper; pago; va)

dónde: - nper, es el nº total de periodos de pago en una anualidad. - pago, es el pago efectuado en cada periodo. - va, es la cantidad a pagar. Así pues, utilizando la función TASA, realizar el siguiente ejercicio: hemos pedido en varias entidades bancarias un préstamo de 18.000,00. Cada banco ofrece unas condiciones de amortización distintas (periodo de amortización y cuota mensual a



pagar). Calcular la tasa de interés anual correspondiente a cada uno de los bancos.

A continuación, calcular cuál de las entidades bancarias nos propone el préstamo más ventajoso, así como cuál de ellas nos propone el peor. NOTA: Para ello, es necesario hacer uso de las funciones BUSCAR, MAX y MIN.

Recordad que para que la función BUSCAR trabaje correctamente es idóneo que el vector o matriz de búsqueda se encuentre ordenado.

Por último, calcular también el número de propuestas que superan la tasa media

anual.



• Solución:

Para las soluciones de esta práctica vamos a dar las funciones utilizadas en cada caso, sin

entrar a comentar su funcionamiento.

A5 = A2+A3+A4 B5 = SUMA(B2:B4)

B5 = D2*D3*D4 E5 = PRODUCTO(E2:E4)

A12 = (A9+A10+A11)/3 B12 = PROMEDIO(B9:B11)

B8 = CONTAR(B2:B7) B9 = CONTARA(B2:B7) B10 = CONTAR.SI(B2:B7;"=letraa")

Como se observa, la función CONTARA se diferencia de CONTAR en que

CONTARA cuenta todas las celdas que tengan algún carácter.



A4 = SI(B2>B3;"A es mayor que B"; SI(B3>B2;"B es mayor que A";"Son iguales"))

D13 = CONTAR.SI(C9:D12;">0") D21 = CONTAR.SI(C17:D20;"<=0") D22 = CONTAR.SI(C17:D20;"=0") D23 = CONTAR.SI(C17:D20;">0")

Para el ejercicio de BUSCAR, primero deberemos introducir la siguiente tabla:

Y el resultado será:



F2 = BUSCAR(E2;B2:B10;A2:A10) F3 = BUSCAR(E3;B2:B10;A2:A10) E4 = MAX(B2:B10) F4 = BUSCAR(E4;B2:B10;A2:A10) E5 = MIN(B2:B10) F5 = BUSCAR(E5;B2:B10;A2:A10)

El siguiente apartado es el uso de DIASEM y buscar el día de la semana. Hay que tener en cuenta, que según la configuración del sistema, 12/01/20 puede ser de 1920 o 2020, cambiando el resultado según se considere.

A2 = DIASEM(A2;2), y esta fórmula deberá copiarse al resto de celdas. .

DIASEM tiene dos argumentos:

• Núm_de_serie: es la fechaque buscamos. • Tipo: Es un parámetro que índica como se comporta la función. Esta función está preparada para distintas regiones en las que el lunes no es el primer día de la semana. Para que considere que el lunes es 1, tendremos que poner este valor a 2.

Para calcular el día de la semana que le corresponde, tenemos dos opciones, usando SI o usando BUSCAR.

• Usando SI: deberemos introducir una sentencia SI anidada, quedando el resultado:

C2 = SI(B2=1;"LUNES"; SI(B2=2;"MARTES";

SI(B2=3;"MIERCOLES"; SI(B2=4;"JUEVES";

SI(B2=5;"VIERNES"; SI(B2=6;"SÁBADO";

"DOMINGO")))))) Esta fórmula se copiará al resto de celdas.



• Usando BUSCAR: deberemos introducir una tabla auxiliar del siguiente modo:

Y a continuación usar la función buscar:

D2 = BUSCAR(B2;$E$2:$E$8;$F$2:$F$8)

Copiando la fórmula al resto de celdas.

Veamos ahora las funciones financieras.

Donde: D2 = PAGO(B3/12;B4*12;-B2) D2 = B4*12 D4 = D2*D3 D5 = D4-B2

La función PAGO que hemos usado tiene 3 argumentos:

• B3/12: Es el interés mensual. • B4*12: Son el número de plazos (de meses). • -B2: Lo que tenemos que pagar (se pone en negativo).



Para el primer mes tendremos:

F3 = $D$2 G3 = F3-H3 H3 = PAGOINT($B$3/12;E3;$D$3;-$B$2) I3 = SUMA($G$2:G3) J3 = $B$2-I3

Para el segundo:

F4 = $D$2 G4 = F4-H4 H3 = PAGOINT($B$3/12;E4;$D$3;-$B$2) I3 = SUMA($G$2:G4) J3 = $B$2-I4

Tenemos que observar como va cambiando la columna I para poder usar la suma y como se han usado las referencias relativas y absolutas para poder copiar las fórmulas a otras filas.

Podemos obtener información de la función PAGOINT en la ayuda de Excel.



Para el cálculo de la tasa anual vamos a utilizar la siguiente fórmula:

E2 = TASA(C2;-D2;B2)*12

Esta fórmula deberá copiarse al resto de celdas.

Ahora tenemos que calcular la mejor y la peor oferta:

Para E17 y E18 no hay ninguna complicación y usaremos la funciones MIN y MAX del siguiente modo:

E17 = MIN(E2:E12) E18 = MAX(E2:E12)

Ahora hay que buscar el nombre del banco, para ello podemos usar BUSCAR:

D17 = BUSCAR(E17;E2:E12;A2:A12) D18 = BUSCAR(E18;E2:E12;A2:A12)

Pero estas funciones no funcionarán ya que BUSCAR sólo trabaja con listas ordenadas. Para solucionar esto tenemos dos opciones.

1. Ordenar los datos desde el menú Datos Ordenar.



2. Utilizar una columna adicional y la función BUSCARV, del siguiente modo:

y ahora:

D17 = BUSCAR(BUSCARV(E17;$E$2:$G$12;3;FALSO);$G$2:$G$12;$A$2:$A$12) D18 = BUSCAR(BUSCARV(E18;$E$2:$G$12;3;FALSO);$G$2:$G$12;$A$2:$A$12)

La función BUSCARV se verá mas detenidamente en otros ejercicios. Por último debemos calcular la tasa media y el número de propuestas que

la superan.

H17 = PROMEDIO(E2:E12) H18 = CONTAR.SI(E2:E12;">10,83%")



Práctica No. 15 “Funciones promedio, contar, truncar y formato de #” Observa la siguiente hoja, introduce los datos en una hoja nueva y realiza las siguientes operaciones:

Te recomendamos que utilices la función =aleatorio(), para generar los cantidades de las cuales obtendrás el promedio.

Deberás obtener un resultado como el que te mostramos.



Práctica No. 16 “Autoformato de tablas y bunciones de búsqueda”

AUTOFORMATO DE TABLAS Y FUNCIONES DE BÚSQUEDA BÁSICA, BUSCARV, MÁXIMOS Y MÍNIMOS

Introduce en una hoja nueva los siguientes datos:

Realiza las siguientes operaciones:

• Introduce una función que nos muestre el producto más caro. • Introduce una función que nos muestre el producto más barato. • Para cada una de las funciones anteriores, introduce otra que nos muestre el nombre de dicho

producto. • Aplica finalmente un autoformato de tabla a los valores iniciales.

Como resultado debes obtener lo siguiente:



Práctica No. 17 “Funciones SI, Y / O” Empezaremos practicando los usos de la función SI, complementando con las características de las funciones Y/O. Copia estos datos y realiza los ejercicios:

En la columna “Media”, poner el promedio de las tres notas, en cada alumno, recuerda la fórmula “=promedio(B4:D4)”. Después aplica la función SI, en la columna resultado, para que si la media es mayor o igual a 5, nos muestre el texto “Aprobado” o “Suspenso” en caso contrario. El resultado será:

Donde en la columna Resultado hemos insertado la siguiente función, =SI(E4>=5;”Aprobado”,”Suspenso”). Después copia (señala la celda, y arrastra del cuadradito pequeño hacia abajo o arriba) la misma fórmula en el resto de celdas y comprueba el resultado. Ahora vamos a practicar las funciones lógicas Y / O , la función Y devuelve “verdadero” en el caso de que se cumplan todas sus condiciones; la función O devuelve “verdadero” si al menos una de unas condiciones se cumple. Completa la tabla con las fórmulas:



Deberás obtener el siguiente resultado:

Hemos utilizado las siguientes fórmulas: �=Y(A2=“x”;B2=“x”) =Y(condición1;condición2;⁄⁄) �=O(A2=“x”;B2=“x”) =O(condición1;condición2;⁄⁄) Estas fórmulas nos devuelven el texto “Verdadero” o “Falso” de forma automática, según se cumplan las condiciones o no.



Prácitca No. 18 “Función SI anidada” La función SI admite anidaciones, es decir, podemos incluir una función SI dentro de otra y así hasta conformar la fórmula que buscamos.

En las condiciones planteadas, para que se cumpla la 2º debe haberse cumplido la 1º, y para que se cumpla la 3º, la 2º condición tiene que existir. En este caso, buscamos como resultado que nos diga cuántas condiciones se cumplen para cada alumno, es decir, si se cumplen dos, nos debe devolver el texto “Dos”, y así correlativamente. Obtendrás un resultado como este:

Como fórmula inicial tienes (en nuestro caso estaría en la celda E2): =SI(B2="x";SI(C2="x";SI(D2="x";"Tres";"Dos");"Una");"Cero") Puedes modificar esta fórmula y adaptarla en otras ocasiones.



Práctica No. 19 “Gráficos primera parte”

• Objetivo: Practicar el uso de gráficos dentro de Microsoft Excel para que el alumno se habitúe a su utilización.

• Enunciado: El ejercicio consistirá en realizar una serie de gráficos de los alumnos matriculados en las 8 provincias andaluzas durante una serie de años.También, se calculará la media del número de alumnos en cada provincia.

Las operaciones a realizar son las siguientes: 1. Crear un libro de trabajo llamado Graficos.xls que contendrá una hoja de cálculo

llamada Alumnos. 2. Posteriormente, se debe introducir la siguiente tabla en la hoja de Alumnos, aplicándole el formato según se desee. No introducir el valor de la columna “Media”, pues esta se calculará en el siguiente punto.

3. Calcular el promedio o media de los alumnos en cada provincia, utilizando para ello la función PROMEDIO.

4. Crear distintos tipos de gráficos circulares en los que se muestren los alumnos de cada universidad en los diferentes cursos académicos. Se crearán, por tanto, cinco



gráficos diferentes, uno por curso. El resultado debe ser similar al que se presenta a continuación:

5. Crear distintos tipos de gráficos de columnas dónde se muestre la evolución de los alumnos en cada Universidad a lo largo de los cinco cursos. Así pues, será necesario crear 8 gráficos diferentes:



6. Por último es necesario crear un gráfico de líneas que refleje las medias del número de alumnos

en cada universidad, tal y como muestra la siguiente figura:



• Solución:

1. Creamos un nuevo libro y cambiamos el nombre a las hojas con lo indicado en el enunciado del ejercicio y borramos el resto de las hojas. Introducimos los datos indicados y le damos el formato que deseemos.

2. Para calcular la media utilizaremos la función PROMEDIO:

G2 = PROMEDIO(B2:F2)

Copiamos el valor de G2 al resto de la columna promedio. 3. Vamos a realizar un gráfico circular de los alumnos del año 99-00. Para ello deberemos seleccionar la columna A de A1 a A9 y la columna E de E1 a E9 (Primero seleccionamos A1:A9 y después, con la tecla Ctrl pulsada, seleccionamos E1:E9). A continuación pulsamos sobre el botón y seguimos los pasos del asistente que nos guiará para crear el gráfico. Los pasos son los siguientes: a) Seleccionamos el tipo de gráfico:



b) Comprobamos que los datos son los que hemos seleccionado:

c) Cambiamos el título y los rótulos de datos:

d) Creamos el gráfico en una hoja nueva que llamamos, por ejemplo, Circular



99-00:

El resultado final será:

Crearemos otros 4 tipos de gráficos circular para el resto de años. Podemos probar las combinaciones que queramos en las opciones y ver que resultados obtenemos. Por ejemplo, podemos conseguir los siguientes gráficos:

4. Vamos a realizar la gráfica con los datos de córdoba. Los pasos a seguir son iguales



que en el apartado 3, pero en este caso seleccionaremos filas y utilizaremos gráficos de columnas. Para Córdoba podemos tener:

Aplicando distintos estilos y herramientas de dibujo, podemos obtener, usando los datos de Cádiz, el siguiente gráfico:

5. Para este apartado, deberemos seleccionar A1:A9 y G1:G9, siguiendo los mismos pasos que en los puntos anteriores, eligiendo el tipo de gráfico lineal. El resultado puede ser:

Aplicando distintos estilos al gráfico, podemos obtener:



Práctica No. 20 “Gráficos segunda parte”

Introduce los siguientes datos:

En este caso realizarás estos cuatro gráficos a partir de esos datos: . Gráfico de columnas, en el paso 2 (Datos de origen) establece las series en columnas. . Gráfico de columnas, en el paso 2 (Datos de origen) establece las series en filas. . Gráfico circular, en el paso 2 (Datos de origen) establece las series en columnas. . Gráfico de líneas, en el paso 2 (Datos de origen) establece las series en columnas y/o filas (comprueba la diferencia). NOTA: Te recomiendo que selecciones el rango A6:E12 y después crees el gráfico, de esta forma detectará los datos y mediante el asistente podrás crear el gráfico; sin complicaciones.



90 80 70 60 50 40 30 20 10 0 Bares Revistas Ropa Comida Gasolina Extras



Práctica No. 21 “Gráficos avanzados”

Vamos a ver cómo crear gráficos con más detalle, introduce los siguientes valores para basar los gráficos en estos datos:

Ahora crea los siguientes gráficos: *Nota: Los valores de las gráficas puestas como ejemplo, es posible que no te coincidan con los valores de la tabla que te hemos puesto como práctica. Comprueba que tus gráficos resultantes, los que tu fabriques, sí deben coincidir con los valores de la tabla. . Con el rango A3:G7 seleccionado, crea un gráfico de columnas; en el 2º paso establece las series en filas, en el 3º paso pon “Gastos mensuales” como título del gráfico. Gastos mensuales 100 €, Luz 80 € 60 € Agua 40 € Gas Teléfono 20 € 0 € Enero Febrero Marzo Abril Mayo Junio

. Con el rango A3:G7 seleccionado, crea un gráfico de columnas, en el 2º paso establece las series en columnas, en el 3º paso pon “Gastos mensuales por conceptos” como título del gráfico.



Con el rango A4:B7 seleccionado, crea un gráfico circular, con las series en columnas, de titulo de gráfico pon “Gastos de Enero”, haz clic en la pestaña “Rótulos de datos” y marca las casillas “valor” y “porcentaje”, finalizando el gráfico.

. Selecciona el rango B3:G3 seleccionado, aprieta la tecla CTRL y sin soltarla selecciona el rango B8:G8, después inicia el asistente de gráficos, crea un gráfico circular, con las series en filas. En el 3º paso, establece el título “Gastos totales por mes” y en la pestaña Rótulos de datos marca las casillas de valor y porcentaje.



Práctica No. 22 “Funciones de texto” Deberás introducir estos datos y después aplicar la función de texto correspondiente:

Dados esos nombres y apellidos, deberás unir los textos en otra celda, de tal forma que leamos este formato ( 1ºapellido 2ºapellido, nombre), aplicando la siguiente fórmula, =CONCATENAR(B2;" ";C2;",";A2). Después mediante fórmulas pon el texto sugerido a mayúsculas, minúsculas y la función nombre propio (NOMPROPIO). Utiliza el asistente de fórmulas (fx) y visualiza la categoría texto, te resultará mas cómodo. Mayúsculas; =MAYUSC(A9) Minúsculas; =MINUSC(B9) Nombre propio; =NOMPROPIO(A9)

NOTA: prepara la página en horizontal, con márgenes de 3 cm en cada lado, adecua el tamaño de las columnas a su contenido.



Práctica No. 23 “Formato condicional y de texto” Introduce lo siguiente:

En este supuesto tienes que: �Cambiar el texto a negrita, a tamaño 12, con doble subrayado, alineación horizontal centrada y alineación vertical distribuido. Después combina las celdas A16 y B16. �Por otra parte, selecciona el rango A17:A20 y aplícales el siguiente formato Condicional (menú Formato): g) 1º Condición, formato de letra en rojo con un borde negro h) 2º Condición, formato de letra verde en negrita i) 3º Condición, formato de letra azul, con subrayado simple

Ahora te proponemos que practiques un poco, inserta en las celdas A17, A18, A19, A20 , valores numéricos diferentes y comprueba que cambia el formato de celda, según hayamos establecido en el formato condicional: Ej: si escribes “13” en A18, deberá obtener un color rojo y con borde negro. Si introduces “32” en A20, este será de color verde y en negrita. AUTOFILTROS Y FILTROS AVANZADOS



En esta ocasión vamos a repasar y poner en práctica los autofiltros y filtros avanzados, empecemos por los primeros; para ello introduce los siguientes valores:

El autofiltro nos ayudará a reducir una larga lista, mostrando solo aquellos datos que queremos, usando un filtro que le indicaremos. Para ello, seleccionaremos toda la lista, incluido el título, en nuestro caso seleccionamos A1:A13, y crearemos un autofiltro a la lista como se ve en la siguiente figura:

Si haces un clic en el desplegable que esta en A1, “Meses”, podrás elegir el mes para visualizarlo y el resto se ocultarán. Como habrás comprobado, al elegir uno de los 5 primeros meses, también nos aparece el día correspondiente, esto nos demuestra la relación entre el mes y el día, dado que pertenecen a la misma fila en la hoja de datos; por lo tanto al crear un autofiltro, también filtra esa fila según nuestra elección. Para quitar un autofiltro, sirve con seleccionar la primera celda e ir al menú Datos y “Desmarcar” la opción Autofiltro.



Ahora bien, si lo que buscamos es establecer un filtro con múltiples condiciones, tenemos que recurrir al filtro avanzado. Copia estos datos para poder practicar:

Nuestra condición será que sea un piso con estado en “Prox. Construcción” y que sea de 2 habitaciones. Para ello seleccionaremos toda la información inicial, de A2:D12. Después activaremos la opción “Filtro avanzado” del menú Datos/Filtro, en la ventana que nos aparece, estableceremos el rango B14:C15, como rango de criterios. Observa que también seleccionamos las cabeceras de las columnas, es decir, sus títulos “Estado”,”Habitaciones”, se hace para que Excel relacione los datos iniciales con el filtro que queremos hacer. Te mostramos el resultado final:

Recuerda que para quitar los filtros, podrás hacerlo desde el Menú Datos / Filtro / Mostrar Todo y/o desmarcando las opciones en ese mismo menú.



Práctica No. 24 “Propiedades de las potencias”

Utilice una celda para ingresar un número, una celda arriba y a la derecha para un valor exponencial de la forma siguiente: XY en la celda continua a “X” utilice =”=”, en la celda siguiente utilice =SI(C2>=1;B5;1); en la siguiente utilice =SI(C2>=2;"x";""); posteriormente, =SI(C2>=2;B5;""), luego, =SI($C$2>=3;"x";""); después, =SI($C$2>=3;$B$5;""). Ejemplo: Y X =”=” =SI(C2>=1;B5;1) =SI(C2>=2;"x";"") =SI(C2>=2;B5;"") =SI($C$2>=

3;"x";"") =SI($C$2>=3;$B$5;"")

1 =SI($C$2>=1;"1";"")

2 =SI($C$2>=2;"2";"")

3

Continua: =SI($C$2>=4;"x";"") =SI($C$2>=4;$B$5;"") =SI($C$2>=5;"x";"") =SI($C$2>=5;$B$5;"") . ="=" =POTENCIA(B5;C2) 4 5 Utilice el botón derecho del Mouse / formato de celda / proteger / desbloquear. Utilice para la cela “X” y “Y”, proteja la hoja y el libro, guarde y cierre, abra nuevamente e inserte valores para “X” y “Y”. Propiedades de los exponentes: Propiedad 1: Multiplicación de Potencias de Igual Base y Distinto Exponente. Propiedad 2: Multiplicación de Potencias de Distinta Base e Igual Exponente. Propiedad 3: División de Potencias de Igual Base y Distinto Exponente. Propiedad 4: División de Potencias de Distinta Base e Igual Exponente. Propiedad 5: Potencia Elevada a un Exponente. Propiedad 6: Potencia de Exponente Negativo. Ejemplo 2 Propiedad 1:

B C D E F G H I J K L 5 Y Z =C2&" +

"&G2 =SI(B6=E6;C2+G2;"Erro

r") 6 X =”X” X =”=” =B6 =”=” =SI(B6=E6;B6;"E") Ejemplo 3 Propiedad 2:

A B C D F G H I J K 5 Y Y =B5 =SI(C2=G2;C2;"Error") 6 X =”X” Z =”=

” ="( "&B6&" x "&D6&" )"

=”=” =SI(C2=G2;B6*F6;"Error")

Ejemplo 4, Propiedad 3:

A B C D E F G H 21 Y =B21&" - "&B23 =B21-B23 22 X =”=” =SI(A22=A24;A22;"E") =”=” =D22 23 Z 24 X No olvide poner el borde inferior de las celdas A22 y B22 para hacer visible el cociente.



Práctica No. 25 “Tablas dinámicas 1ª. Parte”

Este es un sencillo ejemplo utilizar una Tabla Dinámica en Excel. Esta nos sirve para ordenar y analizar mejor los datos de un listado o Tabla Excel.

El ejemplo se compone de una Tabla de pagos realizados a distintos proveedores, en distintos meses e imputables a distintas obras.

Realice la siguiente tabla:

Mes Obra Concepto Importe Marzo Mendoza 450 Albañileria $531,08 Febrero Mendoza 450 Albañileria $248,50 Febrero Rivadavia 6971 Yeso $261,28 Febrero J.B.Alberdi 1225 Carpinteria $491,32 Enero Mendoza 450 Yeso $303,88 Marzo Mendoza 450 Carpinteria $531,08 Marzo Rivadavia 6971 Carpinteria $168,98 Enero Rivadavia 6971 Albañileria $347,90 Febrero J.B.Alberdi 1225 Carpinteria $497,00 Enero J.B.Alberdi 1225 Albañileria $286,84 Marzo Rivadavia 6971 Pintura $362,10 Enero J.B.Alberdi 1225 Albañileria $332,28 Enero J.B.Alberdi 1225 Yeso $258,44 Marzo Mendoza 450 Pintura $156,20 Febrero Mendoza 450 Electricista $248,50 Marzo J.B.Alberdi 1225 Pintura $232,88 $5.258,26

Observando la tabla, seria interesante saber:

a) cuanto se le pago a cada proveedor, o por cada obra.

b) Que monto se le pago a un determinado proveedor en concepto de trabajos realizados en una obra específica, etc.

Las incógnitas mencionadas se obtienen mediante la ejecución de las Tablas dinámicas.



La creación de una tabla dinámica se hace en varias etapas con la ayuda de un asistente. Comienzo… 1) Colocar el cursor en cualquier celda de la tabla.

2) Ir al menú Datos > Asistente para tablas dinámicas. Elegimos el tipo de datos a utilizar…

3) Donde dice Donde están los datos que desea analizar, marcar la opción : marcar la opción Lista o base de datos de Microsoff Excel

4) Botón Siguiente. Elegimos el rango de los datos… 5) Seleccionar el Rango de la Tabla incluyendo la fila de títulos

6) Botón Siguiente Diseñamos la Tabla Dinámica… 7) Hacer clic en botón Diseño

8) A la derecha hay una lista de botones. Arrastramos el que dice "Obra" y lo soltamos donde dice "Columna". Luego arrastramos el que dice "Concepto" y lo soltamos donde dice "Fila". Finalmente arrastramos el que dice "Importe" y lo soltamos donde dice "Datos"

9) Clic en Aceptar Ahora ubicaremos la Tabla Dinámica… 10) Marcar la opción Hoja de Cálculo Existente 11) Indicar la celda donde armar la Tabla 12) Presionar Finalizar Ya tenemos armada la Tabla Dinámica. La misma ha ordenado todo los datos de la Tabla inicial. Además, la Tabla Dinámica tiene unas flechitas (filtros) que nos permiten mostrar u ocultar los datos.



Como actualizar la tabla dinámica… Los datos en la tabla conformada no se actualizan automáticamente ante cualquier cambio, producido en la tabla base. Si modificamos o introducimos nuevos registros en la Tabla original debemos actualizar la tabla dinámica. Esto se hace de la siguiente forma: 1) Nos ubicamos sobre la Tabla Dinámica, en cualquier celda.

2) Desde el menú Datos > Actualizar datos se actualizará la tabla automáticamente. (La opción actualizar datos se mantiene desactivada si NO te encuentras sobre la tabla dinámica).

Cambiando el diseño de la Tabla Dinámica…

Una vez terminada la tabla, existe la posibilidad de cambiar el Diseño, la estructura definida en el tercer paso del asistente. Simplemente tomamos los campos con el ratón y los llevamos a la nueva posición, según convenga.

Cambiando el tipo de operación de la Tabla Dinámica… En la tabla dinámica que armamos lo que hicimos fue totalizar los importes pagados en cada concepto y obra. En otras palabras, usamos la operación de suma. Por ejemplo, en la tabla dinámica siguiente en vez de sumar los importes hemos CONTADO los mismos, para ver cuantos pagos se han hecho.



Los pasos para cambiar el tipo de operación de la Tabla Dinámica son:

1) Activamos la Barra de Tablas Dinámicas (menú Ver > Barras de Herramientas > Tablas Dinámicas)

2) Nos ubicamos sobre la Tabla Dinámica.

3) Desde la barra de herramientas que habilitamos antes, clic en la lista Tabla Dinámica > Configuración de campo. 4) En la lista de Resumir por tenemos todas las opciones posibles (sumar, promedio, contar, etc…).



Práctica No. 26 “Tablas dinámicas 2ª. Parte” En esta ocasión deberás crear una tabla dinámica con los siguientes datos:

Selecciona las celdas como se muestra en la figura y crea una tabla dinámica a partir de ellos. Encontrarás la opción en Menú Datos / Informe de tablas y gráficos dinámicos. Obtendrás una hoja con lo siguiente:



Ahora deberás arrastrar los campos nombre, apellido1 y apellido2 a la zona de campos de fila (la columna de la izquierda), en el orden marcado, 1º el nombre y después el apellido1 a su izquierda (pero dentro de la misma zona) y finalmente el apellido2 en la zona de datos (la parte central más grande). El resultado deberá ser como el mostrado:

Observa que la tabla dinámica nos cuenta cuántas personas se llaman igual



Práctica No. 27 “Grabadora de macros”

MACROS

Para grabar una macro, Excel nos ofrece dos opciones: escribir la macro en una hoja de macros con el lenguaje de macros (Visual Basic), tarea para usuarios especializados, o bien realizar paso a paso las acciones a grabar en la macro, tal y como lo haría en la primera de las hojas de cálculo en la que necesitara ejecutar dichas acciones. Nos vamos a centrar en la segunda opción, que no exige ningún conocimiento previo de lenguaje de macros y, en cambio, podrá ejecutar cualquiera de los mandatos de Excel.

La creación de una macro, como podrá comprobar es muy simple mediante el comando Grabar nueva macro del menú Herramientas� Macro.

La práctica que realizaremos a continuación, consistirá en crear una macro sencilla que dé formato a determinadas celdas de la hoja de cálculo para que destaquen:

1. Pulse el botón Nuevo de la barra de herramientas. 2. Seleccione el menú Herramientas� Macro� Grabar nueva macro.

En su pantalla aparecerá el siguiente cuadro de diálogo con cuatro opciones:

* Nombre de la macro. Permite especificar el nombre que queremos dar a dicha macro.

* Método abreviado. Permite asignar una combinación de teclas de método

abreviado para ejecutar la macro después de su creación. Las letras minúsculas será Ctr+Letra minúscula, y las letras mayúsculas será Ctrl+Mayús+Letra mayúscula.

* Guardar macro en. Permite seleccionar dónde se desea almacenar la macro. Si

desea que la macro pueda utilizarse en cualquier libro de trabajo, elija Libro de macros personal (la macro se graba en una hoja de macros oculta que siempre se abre al ejecutar Excel y puede ejecutarse desde cualquier hoja de cálculo).



* Descripción. Para añadir o modificar la información descriptiva de la macro.

3. En el cuadro de texto Nombre de la macro teclee

Formato_especial. 4. Pulse la tecla Tabulador o haga clic en el cuadro Método abreviado. 5. Teclee f para la letra de Método abreviado. 6. Pulse el botón Aceptar para empezar la grabación.

Ahora Excel regresa al libro de trabajo, pero observe que en la barra de estado se muestra un mensaje que pone Grabando, y ha aparecido una nueva Barra de herramientas llamada Grabar macro con dos botones. Éstos son:

Referencia relativa. Activa o desactiva la grabación con referencias relativas o absolutas de celda

Detener grabación. Finaliza la grabación de la macro seleccionada.

En este momento todas las acciones que realicemos las grabará la macro, por lo tanto siga con atención los próximos pasos, ya que un error significará un error en la grabación.

Ejemplo 4:

7. Abra el menú Formato. 8. Seleccione la opción Celdas. 9. Haga clic en la pestaña fuente. 10. Elija la fuente Times New Roman con el tamaño de 11. 11. Haga clic en la pestaña Bordes. 12. Pulse sobre el botón Contorno. 13. Haga clic en la pestaña Tramas. 14. Seleccione el color Gris claro. 15. Pulse el botón Aceptar. 16. Por último pulse sobre el botón Detener grabación de la Barra de herramientas

Grabar macro. Con esto hemos finalizado los diferentes pasos que tiene que realizar la macro.



Práctica No. 28 “Macros en Visual Basic: autoarrancables” Autoarrancables: Macros Autoarrancables por evento Private Sub Workbook_Open() MsgBox ("Hola, bienvenido a las macros automáticas por eventos") sw = 1 End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox ("hasta luego, la hoja se cierra.... !!!") Cancel = False End Sub Sub Workbook_BeforePrint(Cancel As Boolean) If sw = 0 Then Cancel = False: GoTo salir Cancel = True MsgBox ("Este libro no está permitido que se imprima !!!") salir: End Sub Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) opcion = MsgBox("¿De verdad desea guardar el libro?", vbYesNo) If opcion = vbNo Then Cancel = True GoTo salida2 End If entrada: Mensaje = "Introduzca la clave para poder salvar el fichero:" Título = "INTRODUCCION DE LA CLAVE" ValorPred = "" clave = InputBox(Mensaje, Título, ValorPred) If clave <> "clave" Then Cancel = True veces = veces + 1 GoTo salida Else Cancel = False GoTo salida2 End If salida: If veces <> 3 Then GoTo entrada MsgBox ("Este libro no se guardará porque no está autorizado al no conocer la clave !!!") MsgBox ("SI NO LA SABE, POR QUE LO INTENTA ???!!!!")



salida2: End Sub Private Sub Workbook_Deactivate() MsgBox ("¡Hola!, hasta luego...") End Sub Private Sub Workbook_NewSheet(ByVal lahoja As Object) MsgBox ("Ha creado una nueva hoja, que NO ESTA PERMITIDO y se va a autodestruir") Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True End Sub



Práctica No. 29 “TEST No. 1”

1. Si queremos multiplicar los valores de las celdas A4 y C7 debemos escribir: a) Multiplica(A1:C7) b) Producto(A1:C7) c) =A1*C7 d) Ninguna es válida

2. ¿Qué función hay en B6, que suma todos los gastos de enero? a) Suma(B2+B3+B4+B5) b) Suma(B2:B5) c) B2+B3+B4+B5 d) B2+B5 3.¿Cuál es la celda activa? a) A2 b) B a) B17 a) C3 4. Partiendo del rango A8:A12, ¿cómo obtienes el rango B8:12? a) Introduciendo manualmente los valores. b) Multiplicando por 10 la celda contigua, =A8*10 y así sucesivamente. c) Añadiendo decimales en el formato de número. d) Ninguna es válida.



5. ¿Qué fórmula hay en C16? a) A16+B16 b) Promedio(A16:B16) c) A16-B16 d) Ninguna de las tres. 6. ¿Qué resultado nos daría la función =FILAS(A8:A12)? a) 5 b) 10 c) 2,5 d) 8,9,10,11,12

7. Si al rango B2:E2, lo hemos llamado Totagua, ¿qué fórmula emplearemos para calcular el 7% de la suma de sus valores? a) (B2+C2+D2+E3)*100/7 b) B2*7%+C2*7%+D2*7%+E2*7% c) Porcentaje(7;B2:E2) d) =SUMA(totagua)*7%

8. ¿Qué fórmula hay en E16, que nos da el total de beneficio en 3 meses? a) C16*D16 b) C16+D16 c) Suma(B2+E5) d) Ninguna de las anteriores.

9. ¿Cómo hemos unido las celdas D8, E8, F8, para que se comporten como una sola? a) Insertando el texto en E8 y alineando al centro. b) Combinando las celdas. c) Poniendo formato personalizado. d) En realidad no están unidas.

10. ¿Qué pasos hemos seguido para obtener una apariencia del texto como en E10:E11? a) Simplemente insertar texto. b) Combinando celdas. c) Poniendo formato personalizado. d) Combinando y usando una alineación vertical distribuida.




TEST 2

1. ¿Cuál es el nombre de la hoja activa? a) Clientes. b) Calendario. c) Libro3 d) C15. 2. ¿Cómo se llama el archivo abierto? a) Calendario. b) Excel. c) Meses. d) Libro3.



3. ¿Cuántas hojas posee el libro abierto? a) 3 b) 1. c) 5 d) Depende. 4. ¿Qué significa la esquina roja en la celda A2? a) Copia de formato. b) Que pertenece a una serie. c) Que tiene un comentario. d) Es una macro. 5. ¿Cómo se realiza un rótulo como el presentado? Nos referimos a “Test 2” a) Con letra grande y aplicando tramas. b) Es una imagen insertada. c) Con WordArt. d) Es una autoforma. 6. ¿Qué opciones de la barra de herramientas formato están aplicadas en la celda activa? a) Arial 18, negrita, color rojo, centrada, con combinación de celdas. b) Texto de alineación distribuida, roja. c) Es un WordArt. d) Ninguna de la anteriores. 7. ¿Cómo añadimos una imagen? a) Desde el menú Insertar / Imagen. b) La tenemos que dibujar con la barra de herramientas de dibujo. c) Desde el menú Formato. d) Desde el menú Archivo / Abrir. 8. ¿Qué indican las líneas discontinuas en el rango A1:C13? a) Nada, es un borde especial. b) Indican al área de impresión. c) Que está siendo copiado. d)Que contiene información recursiva. 9. ¿Qué nos puede indicar el desplegable de la celda B2? a) Que existe un autofiltro. b) Es una imagen. c) Hay una error en la copia. d) Error en la fórmula. 10. ¿Para qué sirve el botón Fx , de la barra de fórmulas? a) Nos ofrece un asistente para implementar cualquier fórmula. b) Nos abre la ayuda online de Excel. c) Comprueba la fórmula y nos da el resultado más óptimo d) No es un botón, indica que ahí se introducen valores.




1. ¿Qué tipo de gráfico se muestra y qué tipo de series tiene? a) De barras, con las series en columnas. b) De columnas, con las series en filas. c) Es un gráfico 3D d) Son cotizaciones ,con las series intermedias.

2. ¿Cómo conseguimos que aparezca el símbolo � y Excel lo interprete como un número? a) Es un tipo de letra. b) Es un formato de celda y número. c) Aparece sólo después de operar. d) En realidad es texto.



3. ¿Qué fórmula hay en H8? a) =Suma(B8:G8) b) =Suma(G4:G7) c) =Promedio(B4:G7) d) Cualquiera de las dos primeras. 4. El total del teléfono lleva un impuesto del 30%, ¿es correcta fórmula propuesta en D13? a) Sí. b) Sí, pero hay que cambiar la función “suma” por “multiplica”. c) No, en ningún caso. d) No, porque no se puede realizar en un solo paso.

5. ¿Cómo conseguimos el aspecto que tienen las celdas B3:G3 y A4:A7? a) Mediante el tipo de letra. b) Con bordes y sombreados. c) Es un WordArt. d) Combinando celdas. 6 Si queremos saber los gastos totales sin contar el teléfono, usaremos la fórmula: a) =Suma(B4:G6) b) =Suma(H4:H7) c) =Subtotales(B4:G7) – B7:G7 d) Las dos primeras son válidas. 7. ¿Qué formato de número hemos empleado para conseguir ese aspecto en la celda H8? a) Formato de número con dos decimales. b) Formato personalizado. c) Formato de moneda con dos decimales. d) Formato general. 8. Si queremos escribir una fórmula como si fuera texto, para que Excel no la ejecute, ¿qué símbolo deberemos poner antes de la fórmula tal y como se ve en D12 o D13? a) “=A1+A2 b) „=A1+A2 c) Usar formato de texto. d) Es un comentario. 9. ¿Qué rango de celdas hemos seleccionado para ese gráfico? a) A3:G7 b) B4:G7 c) A3:H8 d) B4:H8

10. Si deseamos saber el gasto medio de la luz a lo largo de los meses, ¿qué función emplearemos? a) Media(B4:G4) b) Promedio(B4:G4) c) Mediana(B4:G4) d) Promedio(B4:H4)




1. ¿Qué tipo de gráfico se muestra? a) Gráfico de líneas con las series en filas. b) Gráfico de líneas con las series en columnas. c) Gráfico de superficie con las series en filas. d) Gráfico de dispersión con las series en columnas. 2. Si llamamos al rango de celdas B5:B7, “Enero” y queremos sumar sus valores ¿cuál de estas fórmulas sería correcta? a) =SUMA(Enero) b) =Enero c) Promedio(Enero) d) Ninguna de la anteriores



3. ¿Cómo se llama la hoja activa? a) Hoja1 b) Hoja2 c) Hoja3 d) Dia5_gráficos 4. ¿Hasta qué fila se imprimirá la hoja? a) Fila H b) Fila J c) Fila 24 d) Fila 35

5. Si insertamos texto en una celda, esta se alinea automáticamente a: a) La derecha. b) El centro. c) La izquierda. d) Nos lo pregunta cada vez. 6. ¿Qué formato tiene la celda D6? a) Número b) General c) Personalizado. d) Moneda. 7. ¿Qué celda tiene una alineación diferente al resto? a) B4 b) H7 c) E5 d) A2 8. ¿Para qué sirve un criterio de validación? a) Suma los valores del rango indicado. b) Solo admite insertar una serie de valores, que definimos, en una celda determinada c) Abre la ayuda de Excel. d) Comprueba que las fórmulas no sean circulares.

9. ¿Qué significa el formato condicional? a) Aplica un formato de celda, dependiendo del valor que posea. b) Aplica una fórmula u otra dependiendo del valor de la celda. c) Es un formato de celda personalizado. d) Ninguna de las anteriores.

10. Si queremos quitar los decimales a la celda H5, mediante una función, emplearemos: a) Decimales(H5) b) Truncar(H5) c) Promedio(H5) d) Min.Decimal(H5)




1. ¿Qué fórmula hay en la celda E2? a) Suma(B2:D2) b) Promedio(B2:D2) c) Contar(B2:D2) d) Max(B2:D2) 2. ¿Qué fórmula empleamos para hallar la nota máxima, celda B13? a) =Promedio(E2:E11) b) =Promedio(B2:E11) c) =Max(E2:E11) d) =Min(E2:E11)



3. ¿Qué fórmula empleamos para hallar la nota mínima, celda B14? a) =Min(E2:E11) b) =Max(E2:E11) c) =nota.minimo(E2:E11) d) =Min(B2:E11)

4. ¿Qué fórmula empleamos para hallar la nota más repetida, celda B15? a) =Repetida(E2:E11) b) =Moda(E2:E11) c) =Promedio(E2:E11) d) Ninguna de estas.

5. ¿Qué fórmula empleamos para hallar la mediana de las notas, celda B16? a) =Media(E2:E11) b) =Mediana(E2:E11) c) =Mediana(B2:E11) d) =Promedio(E2:E11)

6. En la celda F17, hemos calculado cuantos alumnos tenemos, aplicando la fórmula⁄: a) =Contar(A2:A11) b) =Contar(A2:E11) c) =Promedio(A2:A11) d) =Suma(A2:A11)

7. ¿Cómo se llama la hoja activa? a) A12 b) Fórmulas c) Hoja de datos d) Notas

8. El botón que aparece con el texto “ver fórmulas”, al hacer clic sobre él nos abre una hoja diferente, esto es debido a: a) Una macro. b) Un formato de celda. c) Un hipervínculo. d) Un WordArt.

9. ¿Cuál es la columna más ancha? a) Columna F. b) Columna 13. c) Columna A. d) Columna A y F son iguales.

10. Para que nos aparezca el texto “insuficiente, aprobado⁄” en las celdas F2:F11, dependiendo del valor de la columna notas, se ha aplicado una función: a) Condicional, función SI. b) Lógica, función Y/O. c) No hay fórmula en esas celdas, es texto insertado a mano. d) Es la cifra de las notas, pero que al cambiarlo a formato de celdas de texto, nos muestra ese resultado en texto.



RESPUESTAS DE TODO EL TEST:

Supuesto 21

Supuesto 22

Supuesto 23

Supuesto 24

Supuesto 25

PREGUNTA/TEST TEST 1 TEST 2 TEST 3 TEST 4 TEST 5

PREGUNTA 1 C B B A B

PREGUNTA 2 B D B A C

PREGUNTA 3 D A D C A

PREGUNTA 4 B C A C B

PREGUNTA 5 C C B C B

PREGUNTA 6 A A D D A

PREGUNTA 7 D A C D D

PREGUNTA 8 A B A B A

PREGUNTA 9 B A A A C

PREGUNTA 10 D A B B A

universidad autónoma metropolitana -...

Documents