Excel avanzado

Nivel Intermedio y Avanzado 5ta revisión

Ing. Wilson Cedillo P. MSc. 2010-2011 El autor otorga permiso para utilizar este documento bajo la licencia Creative Commons “Reconocimiento-NoComercial-SinObraDerivada 3.0 Genérica” Usted es libre de : Copiar, distribuir y comunicar públicamente ésta guía de Excel Avanzado Bajo las condiciones siguientes: Reconocimiento: Debe reconocer los créditos del documento al autor No comercial: No puede utilizar éste documento para fines comerciales Sin obras derivadas: No se puede alterar, transformar o generar una obra derivada a partir de éste documento Comentarios o sugerencias a los correos: ing.wilsoncedillo@msn.com ó wilsoncp@itss.edu.ec

Excel Avanzado para Colegio

Guía de Microsoft Excel

UNIDAD 1

Funciones desde un enfoque más avanzado

U UN NIID DA AD D1 1 Tema: Funciones desde un enfoque más avanzado 1. Referencias y fórmulas Las referencias a celdas se utilizan para referirse al contenido de una celda o grupo de celdas.

Una sola celda se referencia siempre por su identificador. Por ejemplo, la celda B3 es la que se encuentra en la intersección de la fila 3 con la columna B. La referencia de la celda activa se muestra en el cuadro de nombres situado a la izquierda de la barra de referencias.

Este es el CUADRO DE NOMBRES

Gráfico 1 La referencia a la celda se puede visualizar en el cuadro de nombres

El método más seguro y rápido para definir referencias a celdas consiste en utilizar el ratón para apuntar a la celda cuya referencia se desea insertar.

Cuando se desea introducir la referencia a una celda en una fórmula, basta con dar un clic en dicha celda.

Los rangos de celdas se expresan por medio del operador dos puntos (:), que separa la referencia a la primera celda del rango de la última celda. Por ejemplo, en la figura se muestra una selección rectangular cuya referencia es (B3:D4).

Los rangos se referencian por medio del operador dos puntos, por ejemplo éste es el rango B3:D4

Gráfico 2 Rango B3:D4

Las referencias múltiples consisten en referencias sencillas separadas por el caracter punto y coma (;). Utilizando el ratón, simplemente se mantiene presionado la tecla CONTROL y luego se selecciona los rangos deseados. En la Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

2

UNIDAD 1

Funciones desde un enfoque más avanzado

figura se muestra una selección doble, consistente en dos zonas rectangulares, cuya referencia es (B3:B6 ; D3:D6).

Para seleccionar rangos separados, se puede presionar la tecla CONTROL mientras se señala los rangos con el mouse.

Gráfico 3 Referencia Múltiple B3:B6 ; D3:D6

TRUCO: Si se desea ingresar varios datos similares, por ejemplo varios números 5 en diferentes rangos, señala el rango y escribe el texto que deseas luego presiona simultáneamente las teclas CONTROL+ENTER. Se pueden introducir también referencias a celdas de otra hoja de cálculo, introduciendo el nombre de esa hoja antes de la referencia a las celdas, y separándolos por el signo de admiración (!); por ejemplo si estamos en la hoja 2 y queremos hacer referencia a la celda B3 de la hoja 1 sería: Hoja1!B3.

Se puede hacer referencia a celdas de otras hojas puntos, por ejemplo éste es el rango B3:D4

Gráfico 4 Referencia a la celda B3 de la Hoja 1

IMPORTANTE: Excel permite no sólo referenciar celdas y rangos dentro del libro, sino también de otros libros. Por ejemplo, una fórmula que utilice una referencia a la celda A1 de la Hoja 2 del libro Factura.xls debe mantener la siguiente sintaxis:[Factura.xls]Hoja2!A1. El nombre del archivo externo debe ponerse entre corchetes.

Fórmulas sencillas Las fórmulas constituyen el núcleo de cualquier hoja de cálculo y por tanto de Excel. Las fórmulas con operaciones básicas que se puede utilizar en Excel son:

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

3

UNIDAD 1

Funciones desde un enfoque más avanzado

OPERACIÓN

SIMBOLO

EJEMPLO

Suma

+ (más)

=B5+A2

Resta

- (guión)

=B5-A2

División

/ (Slash)

=B5/A2

Multiplicación Potenciación

* (asterisco) ^ (tilde inglesa)

=B5*A2 =B5^A2

Tabla 1 Operaciones Básicas en Excel

8 metros

Por ejemplo suponiendo que se desea calcular el área de un terreno que mide 10 metros por 8 metros, se podría utilizar una operación básica como la multiplicación:

10 metros

Gráfico 5 Fórmula con una operación básica para calcular el área de un rectángulo

Referencias absolutas y relativas Referencias Relativas Cuando una fórmula se copia y las referencias cambian automáticamente se denominan referencias relativas. Por ejemplo si se desea calcular el total acumulado en el quinquemestre de un listado de estudiantes, solamente sería necesario realizar una fórmula para el primer estudiante y luego copiar para el resto. La fórmula original irá cambiando automáticamente a medida que se la copia.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

4

UNIDAD 1

Funciones desde un enfoque más avanzado

Formula Original

Las referencias relativas cambian cuando se copia la fórmula.

= C 4 + D4 = C 5 + D5 = C64 + D6

Gráfico 6 Formula con referencia relativa en las celdas C4 y D4

Referencias Absolutas En ciertos casos hay que evitar que las referencias a celdas cambien cuando se copia la fórmula a una nueva posición. Para ello hay que utilizar referencias absolutas.

Una referencia absoluta se puede insertar de dos formas diferentes: 1. Según se escribe la fórmula, se teclea el símbolo $ a cada lado de la letra de la celda que se desea que no cambie, por ejemplo $C$10.

2. Pulsando la tecla F4 cuando el punto de inserción en la barra de referencias esté dentro de la referencia a la celda.

En el ejemplo anterior vamos adicionar una columna con la cabecera TOTAL MAS PUNTOS POR ASISTENCIA y un dato en otra celda que indique los PUNTOS POR ASISTENCIA. En éste caso la celda que no queremos que cambie cuando se copie, es la celda que tiene los puntos por asistencia, por lo tanto es necesario anclarla antes de copiar la fórmula.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

5

UNIDAD 1

Funciones desde un enfoque más avanzado A las celdas con referencia absoluta, también se les conoce como celdas ancladas celda.

Gráfico 7 Fórmula con referencia relativa en la celda E4 y Referencia Absoluta en la celda C10

Práctica No. 1 En el CD Informática III puedes encontrar el archivo Práctica 1.xlsx con las instrucciones para que realices la práctica. La práctica tienes que desarrollarla durante las horas de clases y entregarla en el plazo indicado en la práctica. Recuerda que las COPIAS son calificadas con 0 Nombres de celdas y de conjuntos de celdas A veces resulta molesto tener que utilizar repetidamente referencias tales como B2:B4 ó B2:D3; C5:D6 en una hoja de cálculo, o seleccionar los mismos rangos una y otra vez. Excel resuelve este problema permitiendo definir nombres y asignarlos a una celda o a una selección. Es importante que a la hora de crear nombres se tenga en cuenta ciertas reglas: 1. Los nombres deben siempre empezar por una letra o por el caracter subrayado (_); tras este primer caracter, se puede usar cualquier conjunto de letras, números y caracteres especiales.

2. No se pueden utilizar espacios en blanco. Como alternativa a los espacios en blanco, se puede emplear un carácter de subrayado o un punto.

3. Aunque los nombres pueden tener hasta 255 caracteres, conviene que sean más cortos.

PASO A PASO Para una forma simple de definir nombres se pueden seguir los siguientes pasos: 1. Seleccionar la celda, el rango o el rango múltiple al que desee asignar el nombre.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

6

UNIDAD 1

Funciones desde un enfoque más avanzado

2. Elegir el comando: Ficha Fórmulas – Grupo Nombres definidos – Asignar nombre a un rango , con lo cual se abre un cuadro de diálogo tal como el mostrado en la figura.

El uso de nombres de rangos facilita y agilita el trabajo con funciones.

Gráfico 8 Asignar nombre a un rango

3. Teclear el nombre que desee en el cuadro de texto Nombre.

4. Hacer clic en Añadir o en Aceptar.

TRUCO: Otra posibilidad -más sencilla- es seleccionar la celda o rangos de celdas a las que se desea dar un nombre, y luego dar un clic sobre el cuadro de nombres de la barra de referencia. La referencia a la celda activa se sustituye por el nombre tecleado. Al pulsar ENTER las celdas seleccionadas quedan registradas con el nombre tecleado.

Para borrar o modificar un nombre se puede utilizar el Administrador de Nombres.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

7

UNIDAD 1

Funciones desde un enfoque más avanzado

Se puede cambiar las características de un nombre de rango

Gráfico 9 Cuadro de diálogo Administrar Nombres

2. Funciones Las funciones permiten hacer más fácil el uso de Excel e incrementar la velocidad de cálculo, en comparación con la tarea de escribir una fórmula. Por ejemplo, se puede crear la fórmula = (C4+C5+C6+C7+C8+C9+C10)/8 o usar la función PROMEDIO (C4:C10) para realizar la misma tarea.

Función Siempre que sea posible, es mejor utilizar funciones que escribir las propias fórmulas

Respuesta

Gráfico 10 Las funciones hacen más fácil el uso de Excel

Las funciones son más rápidas, ocupan menos espacio en la barra de fórmulas y reducen la posibilidad de errores tipográficos.

IMPORTANTE: Todas las funciones tienen que seguir una sintaxis y si ésta no se respeta Excel nos mostrará un mensaje de error.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

8

UNIDAD 1

Funciones desde un enfoque más avanzado

Estructura de una función La estructura de una función comienza por el signo igual (=), seguido por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por comas y un paréntesis de cierre.

1. Nombre de función.- Indica qué es lo que hace la función. Para obtener una lista de funciones disponibles, haga clic en una celda y presione MAYÚSC+F3.

2. Argumentos.- Indica qué datos necesita la función para dar la respuesta.  Los argumentos o valores de entrada van siempre entre paréntesis.

No dejes espacios antes o después de cada paréntesis.  Los argumentos pueden ser valores constantes (número o texto), referencias a celdas, referencias a rango, fórmulas sencillas o funciones.  Los argumentos deben de separarse por un punto y coma “;” (ó la coma [,] dependiendo la configuración regional de tu computador) 3. Información sobre herramientas de argumentos.- A partir de la versión Excel 2003 cuando se escribe la función, aparece una información sobre herramientas con su sintaxis y sus argumentos. Por ejemplo, escriba =SUMA (y aparecerá la información).

En el ejemplo la función SUMA tiene 4 argumentos: un valor constante, una referencia a celda, una fórmula sencilla y un rango

1

2 3

Gráfico 11 Estructura de una función

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

9

UNIDAD 1

Funciones desde un enfoque más avanzado

Errores comunes en fórmulas y funciones Enseguida se anota algunos mensajes de error que aparecen en Excel y que son de mucha utilidad cuando se trabaje con diferentes tipos de datos y fórmulas: Mensaje de Error

Significado

########

El dato escrito es mayor que la celda

#¿NOMBRE?

Se escribió un texto en una fórmula numérica o se escribió mal el nombre de una función

#¡NUM!

Se escribió un número incorrecto

#¡DIV 0!

Se intentó dividir por cero

#!VALOR¡

Se utilizó un comando incorrecto

#!REF¡

Se utilizó una referencia a una celda o un rango inválido

#N/A

Los datos son insuficientes para realizar el cálculo Tabla 2 Errores comunes en funciones de Excel

3. Funciones Matemáticas Función PI Devuelve el número 3,14159265358979, o la constante matemática pi, con una exactitud de 15 dígitos.

Sintaxis: =PI( ) Ejemplo: Supongamos que el dato del radio de un circulo está en la celda B2; para calcular el área del circulo bastaría con multiplicar pi por el radio elevado al cuadrado, es decir la fórmula: = PI () * B2^2 La función PI no tiene argumentos

Gráfico 12 Fórmula para calcular el área de un círculo utilizando la función PI()

Función ENTERO Saca la parte entera de un número decimal.

Sintaxis: =ENTERO(número) Número: es el número que desea sacar la parte entera.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

10

UNIDAD 1

Funciones desde un enfoque más avanzado

Ejemplo: Si se desea sacar la parte entera de 17,9 la fórmula sería =ENTERO(17,9) y el resultado sería 17.

La función ENTERO tiene sólo un argumento

Gráfico 13 Ejemplo de la función ENTERO

Función POTENCIA Devuelve el resultado de elevar un número a una potencia.

Sintaxis: = POTENCIA(número;potencia) Número

es el número base. Puede ser cualquier número real.

Potencia

es el exponente al que desea elevar el número base.

Ejemplo: Mejorando el ejemplo anterior del área del círculo, se puede utilizar la función POTENCIA en lugar de la tilde inglesa (^).

La función POTENCIA tiene dos argumentos

Gráfico 14 Fórmula para calcular el área del círculo con la función PI y la función POTENCIA

Función REDONDEAR Redondea una cantidad al número de decimales especificado.

Sintaxis: =REDONDEAR(número;núm_decimales) Número: es el número o cantidad que desea redondear.

Núm_decimales: especifica el número de dígitos al que desea redondear el argumento número.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

11

UNIDAD 1

Funciones desde un enfoque más avanzado

Ejemplo: Si se desea redondear 8,456 a dos posiciones decimales la fórmula sería = REDONDEAR(8,456;2) y el resultado que obtendríamos sería 8,46.

La función REDONDEAR tiene dos argumentos

Gráfico 15 Ejemplo de la función REDONDEAR

ACTIVIDAD NO.2 Utilizando la Ayuda de Excel investigar y copiar en el mismo archivo de la materia: la definición, la sintaxis, observaciones y ejemplos (con gráficos) de las siguientes funciones: PRODUCTO RAIZ CONTAR CONTARA (Recuerde que los deberes son parte de la lección de la siguiente clase)

4. Anidamiento de funciones Además de las funciones que incorpora Excel para su utilización directa, es posible utilizar varias funciones a la vez, y realizar así cálculos más complejos y específicos. Para ello las funciones deben estar anidadas.

Una función se encuentra anidada cuando una función actúa como argumento de otra. Para ello colocaremos correctamente unas funciones dentro de otras.

Para anidar funciones podemos utilizar dos métodos: Escribiendo directamente en la barra de fórmulas Se puede introducir en Excel una fórmula que contenga funciones anidadas escribiéndola directamente en una celda o bien en la Barra de fórmulas, sin más que establecer las funciones anidadas como si de argumentos sencillos se trataran, colocándolos entre los correspondientes paréntesis (abriremos tantos paréntesis como cerraremos porque van por parejas).

Esto supone el conocimiento de la sintaxis de todas las funciones a anidar y la atención continua para la colocación correcta de todos los paréntesis necesarios (se abrirán tantos como los que se cerrarán). Este método puede ser resuelto escribiendo de adentro hacia afuera. Por ejemplo si se pretende

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

12

UNIDAD 1

Funciones desde un enfoque más avanzado

calcular el perímetro de un paralelogramo cuyos lados están ingresados en las celdas B4 y B5 de una hoja de cálculo.

Perímetro=((2×a)+(2×b) )

a b Gráfico 16 Fórmula para calcular el perímetro de un paralelogramo

Empezamos escribiendo la operación principal, observe que el operador MAS está sumando a dos multiplicaciones, es decir, la suma es la operación principal y por lo tanto la función por la que debemos empezar: La función principal de ésta fórmula es la función SUMA

Gráfico 17 Para anidar empiece identificando a la función principal

Nos guiamos en la sintaxis de la función principal, que en el caso del ejemplo es una función SUMA cuya sintaxis nos pide ingresar el número 1 que se desea sumar, que en nuestro ejemplo es la multiplicación de 2 por el valor de lado a; pues bien, entonces en seguida escribimos la función que permita multiplicar que en Excel es la función PRODUCTO, no es necesario escribir otra vez el signo igual, pues cuando se anida solamente se pone una sola vez.

Es importante guiarse en la sintaxis para un correcto anidamiento

Gráfico 18 Al anidar la segunda función aparecerá su sintaxis

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

13

UNIDAD 1

Funciones desde un enfoque más avanzado

Podrá observar que inmediatamente después de escribir el paréntesis de apertura de la segunda función aparece su sintaxis que en nuestro caso es de la función PRODUCTO cuya sintaxis nos pide ingresar el número 1 que se va a multiplicar que en nuestro ejemplo es el valor constante 2 y el número 2 a multiplicar sería la referencia a la celda que contiene el valor del lado a: Todo paréntesis que se abra debe ser cerrado cuando así lo pida su sintaxis

Gráfico 19 El primer argumento de la función SUMA es la función PRODUCTO

Cerramos el paréntesis de la función anidada PRODUCTO (se puede observar también que cada grupo de paréntesis tiene un color diferente).

Inmediatamente aparecerá la sintaxis de la función principal SUMA que quedó pendiente, como ya se ha ingresó el número 1 ahora la sintaxis nos pide ingresar el número 2 a sumar, que en nuestro ejemplo es la multiplicación de 2 por el valor de lado b, es decir, debemos anidar otra función PRODUCTO. Resulta sencillo anidar funciones siguiendo las instrucciones

Gráfico 20 la función SUMA tiene anidado dos funciones PRODUCTO

Mediante el asistente de funciones Siguiendo este método de anidar funciones, los pasos a seguir son: Situarse en la celda en la que se va a introducir la fórmula.

Hacer clic sobre el botón Insertar función , de la ficha de Fórmulas. Del cuadro combinado O seleccionar una categoría, para nuestro ejemplo se debería seleccionar la categoría Matemáticas y trigonométricas.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

14

UNIDAD 1

Funciones desde un enfoque más avanzado

En el asistente las funciones están organizadas en categorías

Gráfico 21 Cuadro de diálogo Insertar Función

De la lista Seleccionar una función, seleccionaremos la función SUMA, y pulsaremos sobre el botón Aceptar.

De cada función que se seleccione nos muestra su sintaxis y una breve descripción

Gráfico 22 Listado de funciones de la categoría Matemáticas y trigonométricas

Aparecerá el siguiente cuadro de diálogo de Argumentos de función. Lo normal, sería marcar o indicar la celda de cuyo contenido se desea calcular la suma, pero en este caso vamos a hacer algo más complicado, vamos a anidar la función PRODUCTO dentro de la función SUMA:

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

15

UNIDAD 1

Funciones desde un enfoque más avanzado

En éste cuadro de diálogo ingresamos los argumentos de la función

Gráfico 23 Cuadro de diálogo Argumentos de función

En el Cuadro de nombres que aparece en la parte izquierda de la Barra de fórmulas, se sitúa en este momento, una lista desplegable de funciones.

Desplegaremos la misma haciendo clic en el botón desplegable de flecha a la derecha del cuadro y seleccionaremos la función PRODUCTO. Si no se encuentra la función en este listado genérico, seleccionaremos la opción Más funciones, para poder localizarla en el cuadro de diálogo INSERTAR FUNCIÓN, donde se encuentran todas las funciones existentes. De este modo, como argumento del primer panel, de la función SUMA estamos colocando otra función, en este caso la PRODUCTO, es decir, estamos anidando una función.

Para anidar una función dentro de un argumento escogemos la nueva función de la lista desplegable

Gráfico 24 La función anidar escogemos de la lista desplegable del cuadro de referencias

Aparece un nuevo cuadro de diálogo similar al anterior pero ahora referido a la función PRODUCTO, en el cual debemos introducir los argumentos necesarios para dicha función. Observar en la barra de fórmulas como la función anidada y, en consecuencia toda nuestra fórmula se va construyendo correctamente Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

16

UNIDAD 1

Funciones desde un enfoque más avanzado

(nosotros de momento no hemos tecleado, ni el signo igual, ni el nombre de ninguna función, ni ningún paréntesis...): A medida que ingresamos los argumentos se visualizan resultados parciales

Gráfico 25 Cuadro de dialogo Argumentos de la función anidada

Ahora introduciremos los argumentos necesarios para la función PRODUCTO directamente en el cuadro de texto o bien señalándolo a través del botón , como ya se ha indicado. Una vez introducidos los argumentos para la primera función PRODUCTO, hacer clic sobre el nombre de la función anterior en la barra de fórmulas (en este caso la función SUMA), volviendo y retrocediendo al cuadro de diálogo de la función principal, SUMA.

Para regresar a la función principal, en la barra de fórmulas hacemos clic en el nombre de dicha función

Gráfico 26 Regresamos al cuadro de diálogo de la función principal

En el segundo argumento de la función SUMA hacemos el mismo procedimiento, pues tenemos que anidar otra función PRODUCTO para multiplicar 2 por el lado b. Finalmente presionamos el botón Aceptar y listo.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

17

UNIDAD 1

Funciones desde un enfoque más avanzado

La formula está lista

Gráfico 27 En la barra de fórmulas se ha armado la función sin necesidad de haber escrito nosotros

IMPORTANTE: Si cuando, mediante el asistente de funciones se anida una función dentro de otra, y nos encontramos trabajando en una función de segundo nivel de anidamiento (la que está dentro de otra), si pulsamos el botón inferior de Aceptar, el asistente "se sale" y concluye el proceso, teniendo, por nuestra parte, que terminar editando la fórmula en la barra de fórmulas como si por el primer método de escribirla "a mano" estuviéramos trabajando.

Para evitar esto, deberemos ir retrocediendo en los diferentes niveles de anidación, haciendo clic en la barra de fórmulas, sobre cada una de las funciones precedentes. Esta es la forma de "navegar" en la edición de funciones anidadas desde la barra de fórmulas y mediante el asistente de funciones. Si nos hemos "salido", de querer retomar el asistente, nos situaremos en la barra de fórmulas sobre la función pertinente -clic en ella- y volveremos a pulsar sobre el botón de asistente de funciones

.

Otro ejemplo de anidamiento, si se desea redondear una suma, la siguiente fórmula utiliza una función anidada SUMA y su resultado es REDONDEADO Al resultado de la función ENTERO.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

18

UNIDAD 1

Funciones desde un enfoque más avanzado

Gráfico 28 Funciones SUMA y ENTERO anidadas dentro de la función REDONDEAR

5. Funciones de Texto Estas funciones nos permiten trabajar con el texto que contienen las celdas. Es importante hacer notar que en el momento que utilizamos estas funciones, en los argumentos podemos utilizar texto directamente entre comillas “ ”, o una referencia a una celda que contiene texto.

OBSERVACIÓN: Todos los ejemplos de las funciones de texto se los realizarán con tu nombre y apellido. Caso contrario será considerado como copia.

Función CONCATENAR Une diferentes elementos de texto en uno sólo. Esta función puede concatenar de 2 hasta 255 argumentos.

Sintaxis: =CONCATENAR(Texto1;Texto2;...)

Ejemplo: Si en la celda A1 tenemos el ingresado tu nombre, en la celda A2 tu apellido y queremos que en la celda B3 aparezca el nombre y apellido en la misma celda deberíamos escribir la siguiente función: =CONCATENAR(A1;" ";A2) La respuesta será Fabián Abril

Gráfico 29 La función CONCATENAR une textos

Observa que el segundo argumento es " " de esta forma hacemos que el nombre y el apellido aparezcan separados por un espacio.

Función HALLAR Nos da como respuesta la posición que ocupa un texto o un caracter dentro de una cadena de texto.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

19

UNIDAD 1

Funciones desde un enfoque más avanzado

Sintaxis: =HALLAR(Texto a buscar; Texto donde deseamos encontrar el texto; Posición inicial) Los dos primeros argumentos son obligatorios, en cambio la posición inicial no, ya que si no la ponemos empezará a contar desde el número 1.

Ejemplo: Si tenemos en la celda A1 ingresado tu nombre con tu apellido y deseásemos conocer qué posición ocupa dentro de este texto una letra en particular. En nuestro caso vamos a buscar la posición de la letra “n”, por lo que deberíamos escribir la siguiente función =HALLAR("n";A1), la función nos devolvería el resultado 6.

El resultado será 6, porque la n de Fabián está en esa posición

Gráfico 30 En la función HALLAR el argumento núm_inicial es opcional

Práctica No. 2 En el CD Informática III puedes encontrar el archivo Práctica 2.xlsx con las instrucciones para que realices la práctica. La práctica tienes que desarrollarla durante las horas de clases y en el plazo indicado en la práctica.

Recuerda que las COPIAS son calificadas con 0

Función REEMPLAZAR Esta función reemplaza parte de un texto, indicando desde que posición y cuantos caracteres se desea reemplazar y el texto que se desea poner.

Sintaxis: =REEMPLAZAR(Texto original; Posición inicial a partir de donde deseamos recortar; Número de caracteres a quitar; Nuevo texto a insertar) Ejemplo: Si en la celda A1 se escribe el texto "Fabián se llama Abril" (recuerda que debes hacer el ejemplo con tu nombre y apellido) y en la celda B3 la función: =REEMPLAZAR(A1;11;5;"apellida") el resultado será: Fabián se apellida Abril. La función lo que realiza es eliminar del texto que hay en la celda A1 desde la posición 11, 5 caracteres y estos los sustituye por el texto "apellida". Hay que observar que hemos eliminado 5 caracteres y hemos introducido 8 en su lugar.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

20

UNIDAD 1

Funciones desde un enfoque más avanzado El resultado será Fabián se apellida Abril

Gráfico 31 La función REEMPLAZAR tiene 4 argumentos

Función EXTRAE Saca una parte de texto a partir de una posición inicial de una cadena de texto.

Sintaxis: =EXTRAE(Texto; Posición inicial; Número de caracteres a extraer) Ejemplo: Si en la celda A1 tenemos el texto: "Fabián se apellida Abril" (recuerda que debes hacer el ejemplo con tu nombre y apellido) y en la celda B3 queremos obtener la palabra "se" deberíamos escribir la función =EXTRAE(A1;8;2)

El resultado será el texto se

Gráfico 32 Ejemplo de la función EXTRAE

Función IZQUIERDA Devuelve una cantidad de caracteres determinados empezando a contar desde la parte izquierda de la cadena de texto.

Estructura: =IZQUIERDA(Texto; Número de caracteres a extraer) Ejemplo: En la celda A1 escribimos el texto: "Fabián se apellida Abril" (recuerda que debes hacer el ejemplo con tu nombre y apellido). Si en la celda B3 escribimos la función =IZQUIERDA(A1;6), obtendremos como resultado "Fabián".

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

21

UNIDAD 1

Funciones desde un enfoque más avanzado

El resultado será el texto Fabián

Gráfico 33 Ejemplo de la función IZQUIERDA

Función DERECHA Devuelve una cantidad de caracteres determinados empezando a contar desde la parte derecha de la cadena de texto.

Sintaxis: =DERECHA(Texto; Número de caracteres a extraer) Ejemplo: En la celda A1 está el texto: "Fabián se apellida Abril" (recuerda que debes hacer el ejemplo con tu nombre y apellido), y en la celda B3 la función =DERECHA(A1;5), obtendremos como resultado "Abril".

El resultado será el texto Abril

Gráfico 34 Ejemplo de la función DERECHA

ACTIVIDAD NO.3 Utilizando la Ayuda de Excel investigar y copiar en el mismo archivo de la materia: la definición, la sintaxis, observaciones y ejemplos (con gráficos) de las siguientes funciones: ENCONTRAR MAYUSC MINUSC NOMPROPIO LARGO SUSTITUIR ESPACIOS (Recuerde que los deberes son parte de la lección de la siguiente clase

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

22

UNIDAD 1

Funciones desde un enfoque más avanzado

6. Recomendaciones para buscar complejos utilizando anidamiento

soluciones

a

ejercicios

En ocasiones también tendrá la necesidad de anidar funciones de texto, esto podría darse cuando la misma fórmula puede usarse para varios datos en lugar de hacerla de una en una. Por ejemplo si se tiene un listado de Nombres y apellidos de varios estudiantes y se necesita sacar en una nueva columna solamente los apellidos.

Para dar solución a éste tipo de ejercicios un poco más complejos se recomienda seguir los siguientes pasos: 1. Lee y entiende bien lo qué pide el ejercicio o caso de uso real planteado, pues si no entiendes qué es lo que se quiere no podrás dar una solución correcta.

2. Si todavía no has adquirido la habilidad para anidar directamente es recomendable que empieces realizando una fórmula que funcione para el primer dato, luego copiarla para el resto. Una vez que veas los resultados erróneos te podrás dar cuenta que ciertos argumentos no son iguales para todos los datos.

Esta solución funciona solamente para el primer apellido, o sea Arce

Gráfico 35 Solución no válida para varios datos

Se puede observar que la posición inicial (6) y el número de caracteres (4) funciona para el primer alumno, pero no para el resto.

3. Una vez que se haya identificado los argumentos que NO son iguales para todos los datos, buscamos funciones que les puedan sustituir, es decir, que hagan la misma tarea.

En el caso de éste ejercicio el argumento posición inicial se puede cambiar por una función que ayude a saber la posición de un carácter o una letra, puede ser la función hallar (o encontrar).

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

23

UNIDAD 1

Funciones desde un enfoque más avanzado

Note que en la función HALLAR el caracter que se necesita saber la posición será el espacio, pues es el que está en todos los datos justo antes que el apellido.

El texto buscado es el espacio

En lugar del número 6 se ha anidado la función HALLAR

Gráfico 36 Anidamiento de una primera función para buscar una solución correcta

El otro argumento que no coincide para el resto de datos es el 4, que corresponde al número de caracteres a extraer. La función que ayuda a saber el número de caracteres de una celda es la función LARGO, entonces ahora procedemos a anidar ésta función en lugar del número.

En lugar del número 4 se ha anidado la función LARGO

Gráfico 37 Anidamiento de una segunda función dentro de la función EXTRAE

Ahora si copiamos la fórmula para el resto de alumnos ya se podrá visualizar un resultado correcto.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

24

UNIDAD 1

Funciones desde un enfoque más avanzado

Pueden ser miles de alumnos, ahora es suficiente con copiar

Gráfico 38 Fórmula anidada correcta para varios datos

7. Funciones Lógicas Funciones que nos permiten "preguntar" sobre el valor de otras y actuar según la respuesta obtenida.

Función SI La función SI permite realizar una pregunta lógica (prueba lógica), la cual pueda tener dos posibles resultados VERDADERO o FALSO y actuar de una u otra forma según la respuesta obtenida.

Sintaxis: =SI(Prueba lógica; Acción en caso verdadero; Acción en caso falso).

Lo que escribamos dentro del segundo y tercer argumento serán las acciones que se realizarán en caso de que la respuesta a la pregunta lógica sea verdadera o sea falsa.

Para realizar la pregunta lógica podremos utilizar los siguientes operadores de comparación: = para preguntar si dos valores son iguales, > para saber si un valor es mayor que otro, < para preguntar por menor, >= con este podremos conocer si es mayor o igual, <= preguntamos por menor o igual, o si deseamos comprobar sin son diferente utilizaremos <>.

Ejemplo: Imagina que en la celda B2 escribimos la edad de una persona y en la celda B3 queremos que aparezca el texto "Fabián es Mayor de edad" (recuerda que debes hacer el ejemplo con tu nombre y apellido) en el caso que la edad sea igual o superior a 18, mientras que nos interesará aparezca "Fabián es Menor de edad" en caso que la edad sea menor de 18. La función que deberíamos escribir sería =SI(B2>=18;"Fabián es Mayor de edad";"Fabián es Menor de edad").

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

25

UNIDAD 1

Funciones desde un enfoque más avanzado El resultado será el texto Fabián es menor de edad

Gráfico 39 la función SI permite realizar una pregunta lógica

Práctica No. 3

En el CD Informática III puedes encontrar el archivo Práctica 3.xlsx con las instrucciones para que realices la práctica. La práctica tienes que desarrollarla durante las horas de clases y en el plazo indicado en la práctica.

Recuerda que las COPIAS son calificadas con 0

Función Y Esta función suele utilizarse conjuntamente con la función SI. Nos permite realizar en lugar de una pregunta, varias (hasta 255 en Excel 2007). Y sólo se realizará la tarea si es verdadero en el momento que todas las respuestas sean verdaderas.

Sintaxis: =Y(Pregunta 1; pregunta 2; pregunta 3;...)

Ejemplo: En la celda B1, introduciremos la edad y en la B2 la estatura de la persona medida en centímetros. En la celda A3 aparecerá el texto "Puede pasar" si la edad es mayor de 16 años y mide más de 150. En el caso que alguna de las dos condiciones no se cumplan, aparecerá el texto "NO puede pasar". =SI(Y(A1>16;B1>150);"Puede pasar";"NO puede pasar"). Observa que toda la función Y(...) se escribe dentro del primer argumento de la función Si. El resultado será el texto NO puede pasar, porque una de las condiciones no cumple

Gráfico 40 En la función Y basta que un argumento sea FALSO para que la respuesta sea FALSO

Función O Esta función también se suele utilizar conjuntamente con la función SI. Con ella también podremos realizar varias preguntas dentro del SI y la parte que está en el argumento reservado para cuando la pregunta es verdadera, sólo se Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

26

UNIDAD 1

Funciones desde un enfoque más avanzado

realizará en el caso que cualquiera de las respuestas a las preguntas dentro de la O sea verdadera.

Sintaxis: =O(Pregunta 1; pregunta 2; pregunta 3;...)

Ejemplo: Utilizaremos el mismo ejemplo anterior pero dejaremos pasar si la persona es mayor de 16 años o mide más de 150. De esta manera con que se cumpla una de las dos aparecerá el texto "Puede pasar". El único caso que aparecerá "NO puede pasar", será cuando las dos preguntas no se cumplan. =SI(O(A1>16;B1>150);"Puede pasar";"NO puede pasar El resultado será el texto Puede pasar, porque una de las condiciones SI cumple

Gráfico 41 En la función O, basta que un argumento sea VERDADERO para que la respuesta sea VERDADERO

SI anidados La función SI es una de las que más se usa para el anidamiento ya que su estructura es muy adecuada para esto, uno de los casos más utilizados es para sacar tablas de equivalencias. Por ejemplo se desea sacar la equivalencia de la nota sobre 20 de un estudiante de acuerdo a la siguiente tabla:

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

27

UNIDAD 1

Funciones desde un enfoque más avanzado

Si la nota es …

La equivalencia …

Menor o igual a 20 y mayor o igual a 18

SOBRESALIENTE

Menor o igual a 17 y mayor o igual a 16

MUY BUENA

Menor o igual a 15 y mayor o igual a 12

BUENA

Menor o igual a 11

REGULAR Tabla 3 Ejemplo de Equivalencias

Empezaremos resolviendo la primera equivalencia: para que una nota tenga equivalencia SOBRESALIENTE, se puede ver en la tabla que debe cumplir con dos condiciones (Menor o igual a 20 y mayor o igual a 18), por lo tanto se puede utilizar la función SI en conjunto con la función Y.

Los ejercicios de equivalencias se desarrollan poco a poco

Gráfico 42 USO de la función SI conjuntamente con la función Y para calcular equivalencias

Ahora en el argumento VALOR_SI_FALSO de la función SI que acabamos de escribir, ingresamos nuevamente una segunda función SI anidada con una función Y, solamente que ahora ponemos las condiciones basándonos en la tabla de equivalencias para la siguiente equivalencia, es decir, MUY BUENA. Esto se conoce como los SI ANIDADOS

Gráfico 43 Anidamiento de una función SI dentro de otra función SI

El mismo procedimiento se realiza para el resto de equivalencias, esto se conoce como SI anidados.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

28

UNIDAD 1

Funciones desde un enfoque más avanzado Ahora esta fórmula podemos copiarla a todos los estudiantes que quiera, pueden ser miles

Gráfico 44 SI anidados para calcular equivalencias

8. Otras Funciones importantes Función BUSCARV Esta función nos permite buscar un valor en la primera columna de una tabla, una vez localizado nos muestra dentro de la misma fila el valor que contiene la columna que deseamos obtener.

Sintaxis: =BUSCARV(Valor que se desea buscar en la tabla; Tabla, matriz o rango de datos donde buscar datos; Columna que se desea obtener un dato; Ordenado) Excel busca en la primera columna de la matriz, definida en el segundo argumento, de forma vertical el valor que ponemos en el primer argumento.

Valor que se desea buscar en la matriz: es el valor que se busca en la primera columna de la matriz y puede ser un valor, una referencia o una cadena de texto.

Matriz, tabla o rango de datos donde buscar datos: es la tabla de información donde se buscan los datos. Utilice una referencia a un rango o un nombre de rango.

Columna que se desea obtener un dato: es el número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si este argumento es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente.

Ordenado: es un valor lógico que especifica si BUSCARV debe localizar una coincidencia exacta o aproximada. Si se omite o es VERDADERO, devolverá una coincidencia aproximada. En otras palabras, si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior al valor

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

29

UNIDAD 1

Funciones desde un enfoque más avanzado

buscado. Si es FALSO, BUSCARV encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error # N/A. Ejemplo: Vamos a crear una pequeña hoja en la que según el código de un carro nos devuelva la descripción de este dependiendo de una lista. Para ello primero de todo necesitaremos una tabla de valores. Imaginemos que la introducimos a partir de la celda B4 donde escribiremos el primer código, por ejemplo X001. En la celda C4 la descripción: Automóvil. En la celda B5 escribiremos X002 y en la C5: Camioneta... y así todos los valores que queramos.

Lo que desearemos es que el usuario de esta hoja introduzca un código en la celda C1 y automáticamente en la celda C2 aparezca la descripción que depende del código. Para ello solamente tendremos que escribir la función siguiente en la celda A2: = BUSCARV(C1;B5:B11;2;FALSO) Explicaremos detenidamente los argumentos de esta función para terminar de entender el funcionamiento. En esta función buscamos el valor de la celda C1, dentro de la matriz B5:B11, recuerda que Excel busca primero por la primera columna. Como resultado nos mostrará lo que encuentre en la segunda columna de la fila del valor encontrado.

La respuesta es FURGONETA

Gráfico 45 La función BUSCARV busca en la primera columna de un rango dado

Función BUSCARH Esta función realiza lo mismo que la función anterior, pero con la diferencia que busca los valores en la primera fila de la matriz de forma horizontal y nos devuelve un valor que está dentro de la misma columna del valor encontrado.

Sintaxis: =BUSCARH(Valor que se desea buscar en la matriz; Matriz de datos donde buscar datos; Fila que se desea obtener dato; Ordenado) Ejemplo: Podemos utilizar el mismo caso que el anterior, simplemente que los códigos que vamos a utilizar deberán estar distribuidos en columnas y las Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

30

UNIDAD 1

Funciones desde un enfoque más avanzado

descripciones en la siguiente fila. Así de esta forma podemos utilizar la siguiente función: =BUSCARH(A1;A5:D6;2;FALSO) La respuesta es BLANCO

Gráfico 46 la función BUSCARH busca en la primera fila del rango seleccionado

ACTIVIDAD NO.4 Utilizando la Ayuda de Excel investigar y copiar en el mismo archivo de la materia: la definición, la sintaxis, observaciones y ejemplos (con gráficos) de las siguientes funciones: CONTAR.SI SUMAR.SI (Recuerde que los deberes son parte de la lección de la siguiente clase

Función PAGO Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.

Sintaxis: =PAGO(tasa;nper;va;vf;tipo) Tasa: es el tipo de interés del préstamo.

Nper: es el número total de pagos del préstamo.

Va: es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros, también se conoce como el principal.

Vf : es el valor futuro o un saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento vf se omite, se asume que el valor es 0 (es decir, el valor futuro de un préstamo es 0).

Tipo: es el número 0 (cero) ó 1 e indica el vencimiento de los pagos.

Defina tipo como …

Si los pagos vencen

0 u omitido

Al final del período

1

Al inicio del período Tabla 4 Valores para el argumento TIPO de la función PAGO

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

31

UNIDAD 1

Funciones desde un enfoque más avanzado

El pago devuelto por PAGO incluye el capital y el interés, pero no incluye impuestos, pagos en reserva ni los gastos que algunas veces se asocian con los préstamos.

Ejemplo: Supongamos que se desea saber cual sería el pago mensual de un préstamo de $1000 que se ha hecho al 8% de tasa de interés anual, y el préstamo se a convenido a pagar en 10 meses. La fórmula sería, si es que los pagos se hacen al inicio de cada mes: La función PAGO es muy útil

Gráfico 47 Ejemplo de la función PAGO

IMPORTANTE: La tasa de interés se divide por 12 para obtener una tasa mensual. El número de años de duración del préstamo se multiplica por 12 para obtener el número de pagos.

9. Conclusiones ACTIVIDAD NO.5 Tu opinión es muy importante, escribe por lo menos seis líneas de tu opinión personal acerca de éste capítulo, recuerda que será calificado como un deber más.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

32

UNIDAD 2

Base de Datos o listas en Excel

U UN NIID DA AD DN Noo.. 2 2 Tema: Base de Datos o listas en Excel ACTIVIDAD NO.6 Para los gráficos de este capítulo crear una base de datos de 15 registros, utilice una guía telefónica y ponga los quince primeros nombres, teléfonos y direcciones de la guía que empiecen con su APELLIDO, caso contrario será considerado como copia. La base de datos debe tener seis campos: #ESTUDIANTE, APELLIDOS, NOMBRES, DIRECCIÓN, TELÉFONO y NOTA DEL PRIMER APORTE. NOTA: el campo #ESTUDIANTE es un número secuencial del 101 al 115 y solo en el campo nota del primer aporte vamos a dejar sin datos (mas adelante lo llenaremos) .

La base de datos de éste capítulo debes sacar de la guía telefónica

Gráfico 48 Base de datos de ejemplo tomado de una guía telefónica

1. Introducción Una lista o base de datos simplemente son una cantidad de datos colocados uno debajo de otro.

Para trabajar de forma más efectiva, una base de datos (lista) debe tener las siguientes características: 1. Cada columna debería contener la misma clase de información. Por ejemplo, en una lista de estudiantes, se debería dejar una columna

UNIDAD 2

Base de Datos o listas en Excel

para el código del estudiante, otra para los nombres, otra para el curso, etc.

2. La primera o dos primeras filas deberían ser etiquetas, cada una de las cuales debiera describir el contenido de la columna que hay por debajo de ésta.

En la base de datos del ejemplo se tiene columnas para: #ESTUDIANTE, APELLIDOS, NOMBRES, DIRECCIÓN, TELÉFONO, NOTA DEL PRIMER APORTE. Estas cabeceras de columna definen campos, y cada una de las filas que contienen los datos se conoce como registros, es decir, un campo es un elemento específico de información de la lista de datos y un registro en cambio contiene toda la información de un apartado concreto de la lista. En nuestro ejemplo un registro contiene toda la información personal de un alumno, incluyendo el número de estudiante, apellidos, nombres, dirección y teléfono.

En definitiva, se denomina base de datos a una lista completa de datos organizada en campos y registros.

2. Ordenación de Datos Ordenación por un solo criterio Puede ordenar los datos por cualquier nombre de campo de la base de datos.

El nombre de campo o criterio que utilice para ordenar se denomina clave de ordenación. Para ordenar un rango siga los siguientes pasos: PASO A PASO Para una forma simple de ordenar campos de una base de datos se pueden seguir los siguientes pasos: 1. Seleccione una celda de la base de datos.

2. Elegir el comando: Ficha Datos – Grupo Ordenar y filtrar – Ordenar , con lo cual se abre un cuadro de diálogo tal como el mostrado en la figura.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

34

UNIDAD 2

Base de Datos o listas en Excel

Ordenar listas ayudará organizar mejor

A B

C

D

Gráfico 49 Cuadro de diálogo Ordenar

3. En la lista desplegable ORDENAR POR, pulse la flecha de la lista, y luego seleccione el campo por el que quiere ordenar, por ejemplo NOMBRES. El resto de campos serán ordenados según el campo de Primer Criterio, es la clave principal de la ordenación.

4. Si no tiene otro criterio adicional para ordenar escoja Aceptar.

IMPORTANTE: A diferencia del filtrado, la ordenación de la base de datos desplaza las filas a una nueva posición. Se puede cambiar el tipo de ordenación para ordenarlos en orden descendente, de la Z a la A, en lugar de hacerlo en orden ascendente de la A a la Z. Ordenación por más de una columna Cuando se ordena una lista, se nos puede plantear la necesidad de ordenar por varias columnas. Por ejemplo, una misma lista es posible que necesitemos ordenarla por Cursos, dentro de esos cursos podemos ordenar los clubes, dentro de cada club podremos ordenar los Alumnos por orden alfabético.

Si se quiere ordenar una lista por varias columnas (campos), debemos acceder al cuadro de diálogo Ordenar, seleccionando el comando: Ficha Datos – Grupo Ordenar y filtrar – Ordenar.

El primer criterio de ordenación lo seleccionamos en la lista desplegable ORDENAR POR, e indicaremos a Excel si la ordenación será ascendente o descendente. Por ejemplo podemos seleccionara NOMBRES.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

35

UNIDAD 2

Base de Datos o listas en Excel

Si deseamos un segundo criterio de ordenación simplemente presionamos el botón de comando AGREGAR NIVEL, y así sucesivamente cuantos niveles de ordenación necesite. En nuestro ejemplo el segundo criterio podemos seleccionar APELLIDOS.

Se puede agregar niveles de ordenación

Gráfico 50 Agregando niveles de ordenación

IMPORTANTE: Además se puede ordenar por colores de celda y fuente; en el caso de haberse equivocado también es posible eliminar un nivel.

ACTIVIDAD NO.7 Utilizando la ayuda de Excel investiga cuál es el valor máximo de columnas que se pueden ordenar en Excel 2007. .

3. Validación de datos Para asegurarnos que los nuevos datos de una lista satisfacen ciertos criterios, se puede optar por validar cierto rango de celdas con lo que se puede especificar el tipo de datos permitido (por ejemplo, números enteros, fechas, horas o texto); así como el rango de valores aceptables y hacer que Excel cree una lista desplegable con dichos valores. Por ejemplo en el campo NOTA DEL PRIMER APORTE no pueden ir notas mayores a 15.

Para validar datos primero se seleccionara el rango de celdas que se desea que cumplan con cierto criterio de validación y luego el comando: Ficha Datos – Grupo Herramientas de Datos – Validación de datos

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

.

36

UNIDAD 2

Base de Datos o listas en Excel

Validar consiste en poner ciertas reglas de ingreso

Gráfico 51 Cuadro de diálogo de Validación de Datos

En el cuadro de diálogo validación de datos, existen tres fichas: Configuración, Mensaje entrante y Mensaje de error.

Configuración En ésta ficha, se indica el tipo de datos y el rango de valores que desea permitir. Para esto, abra la lista desplegable Permitir y seleccione uno de los tipos que se presentan en la lista.

En la lista desplegable Datos, podremos seleccionar el operador de comparación que queremos aplicar a los datos de entrada. Por ejemplo si es que se desearía validar el campo NOTA DEL PRIMER APORTE, se elegiría Permitir Decimal y valores de Datos entre un Mínimo de 0 y un Máximo de 15

Gráfico 52 Ficha Configuración del Cuadro de diálogo Validación de datos

Cuando queramos introducir una fórmula para validar una entrada, deberemos seleccionar Personalizada en la lista desplegable Permitir, y especificar la Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

37

UNIDAD 2

Base de Datos o listas en Excel

fórmula en cuadro de texto Fórmula, recuerde que la fórmula que utilice debe usar adecuadamente referencias relativas y absolutas.

Mensaje Entrante Esta ficha permite crea un mensaje que indique al usuario las características de los datos permitidos, es como una ayuda para que el usuario sepa los datos correctos que debe ingresar. Tendrá la oportunidad de especificar el título y el contenido del mensaje, el cual debe ser con contenido cortés. El mensaje será presentado como un comentario junto a la celda validada.

El mensaje entrante debe explicar qué valores son válidos

Gráfico 53 Ficha Mensaje Entrante del cuadro de diálogo Validación de datos

Mensaje de Error Esta ficha permite especificar el mensaje presentado (contenido cortés) cuando se introduce un dato inválido. Además de introducir el título y el texto del mensaje, existe la alternativa de elegir un Estilo.

El mensaje de error se visualizará cuando se ingres un dato inválido

Gráfico 54 Ficha Mensaje de Error del cuadro de diálogo Validación de datos

Una vez validado el campo NOTA DEL PRIMER APORTE ya podemos ingresar todas las notas en nuestra base de datos, siempre y cuando cumplan con los criterios de validación que hemos puesto.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

38

UNIDAD 2

Base de Datos o listas en Excel

Existen otros tipos de mensaje de error

Gráfico 55 La validación no permite ingresar datos incorrectos

Práctica No. 4 En el CD Informática III puedes encontrar el archivo Práctica 4.xlsx con las instrucciones para que realices la práctica. La práctica tienes que desarrollarla durante las horas de clases y en el plazo indicado en la práctica.

Recuerda que las COPIAS son calificadas con 0

ACTIVIDAD NO.8 Utilizando la ayuda de Excel investiga y copia en el mismo archivo de la materia los diferentes estilos de mensaje de error para las validaciones. ¿En qué se diferencian cada uno?

4. Filtrado de Listas Filtros automáticos Cuando tenemos guardada en una Hoja de Cálculo demasiada información, la búsqueda de cualquier dato se puede convertir en una tarea complicada. Tal vez tenga una lista con números de teléfono de clientes o códigos de productos o descripciones, si necesita encontrar todos los números de teléfono en un curso o paralelo determinado, o todas las notas de los alumnos del paralelo A, probablemente no querrá examinar toda la lista y escoger los nombres manualmente.

Para buscar cualquier término dentro de la Hoja de Cálculo se puede utilizar el comando: Ficha Inicio – Grupo Buscar y Seleccionar – Buscar Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

.

39

UNIDAD 2

Base de Datos o listas en Excel Buscar datos no es complicado

Gráfico 56 Cuadro de diálogo Buscar y reemplazar

Pero no siempre resulta suficiente, y por este motivo existe el comando Filtro, el cual permite seleccionar solo aquellos elementos de una lista que cumpla con los criterios que nosotros indiquemos, ocultando el resto.

Por ejemplo supongamos que tenemos una lista de estudiantes de todo el colegio con los datos de NOMBRE, CURSO y PARALELO; se puede filtrar la información para visualizar solo los alumnos de Décimo “A”. Para ensayar podemos adicionar a nuestra Base de Datos los campos: CURSO y PARALELO, luego ponga cursos y paralelos al azar.

PASO A PASO 1.

Lo primero que tenemos que hacer es hacer clic en la celda el dato que queremos utilizar como criterio de búsqueda; en este caso CURSO. 2.

Después, selecciona el comando: Ficha Datos – Grupo Ordenar y filtrar – Filtro . A partir de este momento el primer elemento de cada una de las columnas seleccionadas se convierte en una lista desplegable indicando que el modo filtro está activo.

Gráfico 57 Filtro automático activado Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

40

UNIDAD 2

Base de Datos o listas en Excel

3. Ahora solo basta con desplegar la lista de CURSO y seleccionar Décimo, y en PARALELO seleccionar “A”. Se podrá comprobar, que han desaparecido los alumnos de la lista que no pertenezcan al Décimo “A”.

Gráfico 58 Base de datos filtrada los campos CURSO y PARALELO

4. Para volver a mostrar todos los elementos de la lista, se selecciona la opción (Todas) dentro de la lista desplegable asociada a la columna filtrada. Del mismo modo, para desactivar el modo filtro se sigue la siguiente secuencia: Ficha Datos – Grupo Ordenar y filtrar – Filtro.

Los 10 más de autofiltros Podemos utilizar Autofiltros para encontrar los n apartados superiores o inferiores de una lista, o aquellos apartados que conformen el porcentaje n superior o inferior de un total de columna. Para lo cual vamos a utilizar el comando: Ficha Datos – Grupo Ordenar y filtrar – Filtro.

Pulsamos la flecha de la lista desplegable para la columna en la que estamos interesados (Por ejemplo el campo NOTA DEL PRIMER APORTE), y luego seleccionamos Filtro de número, luego Diez mejores en la lista.

Automáticamente aparecerá el cuadro de diálogo Autofiltro de Diez Mejores

Gráfico 59 Cuadro de diálogo Autofiltro de las diez mejores

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

41

UNIDAD 2

Base de Datos o listas en Excel

En este cuadro de diálogo, se va a encontrar tres listas desplegables; en la primera se puede seleccionar Superiores o Inferiores; en la segunda se puede introducir cualquier número entre 1 y 500 y en la tercera se puede elegir entre elementos y por ciento.

Por ejemplo, si en la primera lista seleccionamos Superiores, en la segunda lista introducimos 50 y en la tercera seleccionamos Por ciento, estaremos indicando a Excel que nos muestre el 50% de las listas de mayor valor en esa columna, es decir, la mitad de las filas cuyo valor en esa columna es mayor que la otra mitad. Con esta herramienta se podría rápidamente saber las mejores notas de un curso.

5. Filtros Personalizados Ya sea que el campo contenga textos o números, entre las opciones que aparecen en la lista desplegable del modo Filtro, cabe destacar una denominada Filtro Personalizado. Al seleccionarla Excel muestra el cuadro de Diálogo Autofiltro Personalizado donde se puede utilizar criterios más complejos de selección.

Por ejemplo podemos filtrar nuestra base de datos para visualizar únicamente las notas entre 12 y 14.

PASO A PASO Para una forma simple de filtrar campos de una base de datos se pueden seguir los siguientes pasos: 1. Es

necesario

activar

el

autofiltro,

eligiendo

el

comando: Ficha Datos – Grupo Ordenar y filtrar – Filtro .

2. Vemos que todas las cabeceras de los campos de nuestra base de datos en Microsoft Excel, aparecen unas flechas en la zona de la derecha de la celda. Esto significa que tenemos activamos los Autofiltros.

3. En el campo “NOTA DEL PRIMER APORTE”, desplegamos la lista y seleccionamos filtros de número y luego Filtro Personalizado.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

42

UNIDAD 2

Base de Datos o listas en Excel

Gráfico 60 Opciones de un campo filtrado

4. Vamos a cumplimentar los dos apartados que tiene el cuadro de diálogo.

La condición “Y” obliga que se cumplan las dos condiciones, sino no muestra ambos resultados. En nuestro caso tiene que darse que simultáneamente los valores sean: mayores o iguales que 12 y menores o iguales que 14.

Gráfico 61 Cuadro de diálogo Autofiltro personalizado

5. Estos son los resultados. Vamos a fijarnos en pequeños embudos, en la columna donde hemos introducido el autofiltro y en la cabecera de las filas. Esto nos indica que hay registros que no se están visualizando por la aplicación del filtro.

Gráfico 62 Base de datos filtrada con filtros personalizados

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

43

UNIDAD 2

Base de Datos o listas en Excel

6. Para volver a visualizar todos los registros, solo tenemos que desplegar “Cantidad” y seleccionar “(Todas)”.

IMPORTANTE: Excel 2007 permite escoger directamente el criterio de filtrado escogiendo de la lista la opción Filtro de número o filtro de texto según sea el caso.

6. Filtros Avanzados Debido a que los autofiltros únicamente nos permiten poner hasta dos criterios para filtrar, tenemos los filtros avanzados que consisten en extraer de la base de datos un grupo de registros seleccionados, utilizando criterios más complejos de comparación.

Lo primero que se debe hacer es copiar las mismas cabeceras de campos de la base de datos que deseamos filtrar a otro lugar de nuestra hoja de cálculo.

Gráfico 63 El primer paso para realizar filtros avanzados es copiar las cabeceras

Luego debemos ingresar debajo las cabeceras las condiciones o criterios con los cuales deseamos filtrar nuestra base de datos, en nuestro ejemplo los criterios son NOTA PRIMER APORTE menores a 9 y el CURSO DECIMO.

Gráfico 64 Las condiciones se ingresan debajo las cabeceras copiadas

Luego hacer clic dentro de la base de datos y para filtrar se utiliza el comando Ficha Datos – Grupo Ordenar y filtrar – Avanzadas Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

.

44

UNIDAD 2

Base de Datos o listas en Excel

Gráfico 65 Cuadro de diálogo Filtro Avanzado

Cuando aparezca el cuadro de diálogo especifique:  El rango que contiene la lista que desea filtrar. Si selecciona una sola celda de la lista, el programa introducirá la referencia en toda la lista.  El rango que contiene los criterios. Asegúrese de incluir los rótulos de los criterios.  Si selecciona la Opción “Filtrar la lista sin moverla” la lista se filtrará en el mismo lugar y aquellos registros que no cumplan con los criterios serán ocultados. Para volver a mostrar todos los registros seleccione el comando “Mostrar todos” del submenú “filtros”.  Si selecciona “Copiar en otra ubicación” deberá indicar el rango donde desea visualizar los resultados presionando el botón de comando “copiar a” (contiene una flecha roja).

Si especifica una sola celda en blanco como el rango “Copiar a”, Excel copia todas las filas que cumplen los criterios en un rango que comienza con esa celda, incluyendo todos los rótulos de columna de la lista.

Gráfico 66 Resultado de un filtro avanzado, filtrado en la misma lista

Caracteres comodines Para encontrar valores de texto otros, se usa comodín * o ?.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

que compartan algunos caracteres pero no

45

UNIDAD 2

Base de Datos o listas en Excel

Utilice el comodín ? para reemplazar un único caracter. Por ejemplo Gr?cia buscará Gracia, Grecia.

Utilice el comodín * para reemplazar varios caracteres. Por ejemplo *este buscará Nordeste, Sudeste.

Una cantidad que desea comparar Para mostrar solo registros dentro de límites establecidos, escriba un operando de comparación: (>, <, >=, <=, <>, =) seguido de un valor en la celda debajo del rótulo de criterios.

Consejos cuando introduzca criterios de comparación Introduzca todos los criterios en la misma fila para buscar los datos que cumplan con todos los criterios en esa fila (similar a la función Y). Por ejemplo el siguiente rango de criterios encontrará todos los registros que contengan Cuota > 44 y fecha de pago = 10/10/2010 CUOTA

FECHA

>44

10/10/2010

Tabla 5 Criterios en una misma fila

Introduzca criterios en filas distintas para buscar los datos que cumplen todos los criterios en la primera o segunda fila (similar a la función O). Por ejemplo el siguiente rango de criterios encontrará todas las “filas que contengan el vendedor Fabián y los totales de ventas superiores a 5000 O las filas con vendedor Roxana y totales de ventas inferiores a 1000”.

VENDEDOR

TOTAL VENTA

JULIA

>5000

ROXANA

<1000

Tabla 6 Criterios en filas diferentes

Por ejemplo si deseamos filtrar nuestra base de datos utilizando Filtros Avanzados para visualizar únicamente los estudiantes en cuyo nombre la segunda letra tenga una i, O la tercera letra sea una e, Y además que en cualquiera de los dos casos que el #ESTUDIANTE sea mayor a 100, y la NOTA DEL PRIMER APORTE SEA mayor a 10, los criterios de filtrado serían:

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

46

UNIDAD 2

Base de Datos o listas en Excel

Gráfico 67 Caracteres comodín y varias condiciones en filtros avanzados

IMPORTANTE: Cuando seleccione la referencia del rango de criterios, asegúrese de incluir los rótulos de criterios. Para eliminar el filtro escoja el comando Ficha Datos – Grupo Ordenar y filtrar – Borrar .

ACTIVIDAD NO.9 Utilizando la ayuda de Excel investiga y copia en el mismo archivo de la materia cómo se puede buscar textos que contengan un signo de ? o un *, ya que dichos caracteres se usan como comodines. .

7. Inserción de Subtotales en una lista de datos No es necesario introducir fórmulas en una hoja de cálculo para introducir subtotales automáticos como resumen de una lista. Al utilizar los grupos de datos elegidos, Excel automáticamente calcula los valores del subtotal y del total general, inserta y rotula las filas totales y esquematiza la lista.

Pueden mostrarse subtotales para más de una clase de cálculos a la vez. Para mostrar subtotales automáticos los datos deben estar organizados en una lista.

Por ejemplo si tenemos la siguiente base de datos y se desea saber el total de UNIDADES que vendió cada VENDEDOR:

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

47

UNIDAD 2

Base de Datos o listas en Excel

Gráfico 68 Base de datos de vendedores

Antes de utilizar subtotales automáticos, agrupe las filas de los elementos cuyo subtotal va a calcular. Una manera fácil es ordenar la lista mediante el comando: Ficha Datos – Grupo Ordenar y filtrar – Ordenar. En nuestro ejemplo ordenaremos primero por VENDEDOR (debido a que se desea saber el total de CADA vendedor) y luego por TIPO: Mostrar filas de subtotales Una vez ordenado se debe seleccionar una sola celda de la lista y el comando: Ficha Datos – Grupo Esquema – Subtotal, Excel utiliza los rótulos de las columnas para ayudarle a identificar los elementos que desea agrupar y los valores que desea resumir.

Elegir elementos para hacer subtotales: Excel insertará una fila de subtotales para cada grupo de elementos en la columna seleccionada en la lista “Por cada cambio en”, aquí se escoge el mismo campo que se ordenó que en nuestro ejemplo es el campo VENDEDOR. Elegir una función resumen: Si la columna seleccionada contiene números, Excel propone la función SUMA para totalizar los datos. Si contiene texto propone la función CUENTA para proporcionar la cantidad de elementos. Sin embargo, puede seleccionar cualquier otra función de la lista. En nuestro ejemplo se desea saber el total de UNIDADES, por lo que escogemos SUMA. Elegir valores por resumir: Es posible mostrar los subtotales de más de una columna en la misma fila si selecciona las columnas de la lista “Agregar Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

48

UNIDAD 2

Base de Datos o listas en Excel

subtotales a”. Aquí seleccionamos el campo que se desea calcular, en nuestro caso el campo UNIDADES es el que se desea calcular.

Gráfico 69 Cuadro de diálogo para Subtotales

Daría la siguiente lista de resultado:

Gráfico 70 Subtotales de la base de datos de ejemplo Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

49

UNIDAD 2

Base de Datos o listas en Excel

Con los símbolos del esquema (Símbolos + y – que aparecen en la parte izquierda de los subtotales) puede ocultar los datos detallados para mostrar el resumen de datos solamente.

Creación de Subtotales Anidados Si se está creando un informe más complejo que utilice mucha información, se puede crear varios subtotales o subtotales anidados para suministrar más información. Siempre que desactive la casilla de verificación Reemplazar subtotales actuales del cuadro de dialogo Subtotales, podrá crear tantos subtotales como desee. Por ejemplo si a los subtotales anteriores le adicionamos el promedio de ventas de cada TIPO (recuerde que debe ordenar primero) de producto el resultado sería:

Gráfico 71 Subtotales anidados

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

50

UNIDAD 2

Base de Datos o listas en Excel

8. Conclusiones ACTIVIDAD NO.10 Tu opinión es muy importante, escribe por lo menos seis líneas de tu opinión personal acerca de éste capítulo, recuerda que será calificado como un deber más.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

51

UNIDAD 3

Tablas Dinámicas

U UN NIID DA AD DN Noo.. 3 3 Tema: Tablas Dinámicas 1. Introducción Cuando se crea un informe, a menudo necesita con frecuencia visualizar sus datos de formas diferentes.

En lugar de crear un informe con subtotales, se puede utilizar una tabla dinámica que agregue los datos de su lista y muestre únicamente las categorías que se escojan. Luego, puede decidir para cuales categorías desea mostrar resúmenes, y que funciones utilizará en los resúmenes.

En resumen, un informe de tabla dinámica es una tabla interactiva que combina y compara rápidamente grandes volúmenes de datos. Podrá girar las filas y las columnas para ver diferentes resúmenes de los datos de origen, y mostrar los detalles de determinadas áreas de interés.

2. Terminología de Tablas Dinámicas Para comprender algunos apartados de Tablas dinámicas es necesario conocer algunos términos: Eje.- Una dimensión de una tabla dinámica, como una columna, una fila o una página.

Datos Fuente.- Lista o Tabla de las que se deriva la tabla dinámica.

Campo.- Una categoría de información. Equivale a una columna de una lista.

Encabezado de Campo.- Rótulo que describe los contenidos de un campo.

Las tablas dinámicas se pueden reorganizar arrastrando los encabezados de los campos.

Elemento.- Miembro de un campo. Cada valor que aparece en ese campo.

Reorganización.- Reestructurar una tabla dinámica cambiando de posición uno o más campos.

Función de Resumen.- Función de la hoja de cálculo usada por Excel para calcular los valores del cuerpo de la tabla. La función de resumen por omisión es SUMA para los valores numéricos y CONTAR para los textos.

Sistema de calificación 3. Creación de Tablas Dinámicas Cuando queramos crear una tabla dinámica, seleccionaremos el comando: Ficha Insertar – Grupo Tablas – Tabla Dinámica proceso de creación de la tabla dinámica.

. Esta acción inicia el

Gráfico 72 Primer paso de la creación de una tabla dinámica

En el cuadro de diálogo Crear Tabla Dinámica deberemos especificar a Excel el tipo de fuente de datos sobre el que vamos a crear la tabla dinámica que puede ser de un rango o una fuente de datos externa. Además mediante dos botones de radio podemos elegir entre crear la tabla dinámica en la una hoja de cálculo nuevo o sino en la misma hoja que se está trabajando. Presionamos el botón de comando Aceptar.

Inmediatamente aparecerá en la parte derecha de la hoja de cálculo una ventana que en la parte superior tiene casillas de verificación con los nombres de los campos de su base de datos y en la parte inferior hay un diagrama de la estructura con las cuatro áreas de la tabla dinámica: Filtros de Informe, Rótulos de columna, Rótulos de Fila y valores.

En la parte izquierda de la hoja aparece el área total que delimitará su tabla dinámica.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

53

Sistema de calificación

Gráfico 73 Áreas y listas de campos de una tabla dinámica

Para estructurar la tabla dinámica, sólo tenemos que arrastrar botones a las diversas regiones del de la estructura. Para quitar un campo de su tabla dinámica, arrastre su botón fuera del diagrama de su estructura.

Arrastre al área de FILTRO DE INFORME (ó área de página) los campos para insertar subtotales para cada grupo de elementos de dicho campo. Recuerde que la palabra “cada” antes del nombre de un campo, nos indicará que dicho campo debe ir en el eje de página de la tabla dinámica.

Los ejes de RÓTULOS DE FILA y COLUMNA son similares, si se desea visualizar detalles de los subtotales, arrastre a éstas áreas los campos en los que están los detalles. Dependiendo de la cantidad de datos, escoja la una o la otra opción. Recuerde que en este caso la palabra “detalles” también nos ayudará a saber que campos van a estos ejes.

Arrastre al área de VALORES todos los campos para insertar subtotales para cada grupo de elementos de dicho campo, si desea sumar, promediar, etc.

Por omisión, el Asistente de tablas y gráficos dinámicos aplica la función Suma o los datos numéricos. Para los valores no numéricos, la función por omisión es Contar. Para usar una función de resumen distinta haga clic derecho sobre el botón de campo después de arrastrarlo al área de VALORES del diagrama. A continuación, seleccione una función en la lista presentada.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

54

Sistema de calificación Cuando terminemos de organizar lo tabla, pulsaremos Aceptar.

Gráfico 74 Tabla dinámica y su estructura

Trabajando con una lista de Excel, la misma lista de estudiantes de la Unidad 2, se tiene ocho campos de la base de datos, representados por rótulos a lo largo de la parte superior de la lista (#ESTUDIANTE, APELLIDOS, NOMBRES, DIRECCION, TELEFONO, NOTA DEL PRIMER APORTE, CURSO, PARALELO), y contiene varios registros Con la lista de ESTUDIANTES del ejemplo, podría crear una tabla dinámica que muestre cada curso con el detalle de cada paralelo y que calcule el promedio de la NOTA DEL PRIMER APORTE.

Gráfico 75 Tabla dinámica con base de datos de estudiantes

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

55

Sistema de calificación O su tabla dinámica podría mostrar las notas máximas o mínimas de cada curso y paralelo. Las posibilidades de una tabla dinámica sólo están limitadas por el tipo de datos con el que se esté trabajando.

4. Reorganización de tablas dinámicas.

Usted podrá reorganizar su tabla dinámica terminada arrastrando simplemente uno o más encabezados de campo al área que crea oportuna.

Además de trasponer filas y columnas, también se puede cambiar el orden en que aparecen los campos, tanto en el eje de las columnas como en el eje de las filas.

Si no nos convence la estructura de la tabla que tenemos creada, cambiaremos los campos al área que nos interese: Columna, Fila o Página. Esto lo haremos pulsando y arrastrando. Mientras arrastra los encabezados, el puntero del ratón cambia a tres páginas en cascada, si lo posiciona en el área de página; a varias filas, si está en el área de filas y a varias columnas, en el área de columnas.

Si está en un lugar donde no puede colocar el encabezado, el puntero se transforma en un botón tachado, indicando que si en ese momento suelta el botón del ratón, el campo que tiene seleccionado desaparecerá de la tabla dinámica.

Observe que, cuando colocamos un campo en un eje de filas o columnas, puede ver todos los elementos, de ese campo con sólo desplazarse a través de la tabla. Sin embargo, en el Eje de página sólo cada momento un elemento de cada campo. Para ver otros elementos del campo, hay que seleccionarlos de la lista desplegable del campo. Si seleccionamos la opción (Todas), obtendremos los totales de cada campo del eje de página.

Gráfico 76 Lista desplegable del área de FILTRO DE INFORME

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

56

Sistema de calificación Aunque tengamos un eje de página en una tabla dinámica, la tabla sólo se almacenará en una página del libro. Si queremos mostrar cada campo del eje de página en una hoja distinta, hacemos clic dentro de la tabla dinámica y seleccionamos el comando Ficha Opciones – Grupo Tabla Dinámica – Mostrar páginas de filtro de informes.

Gráfico 77 Opción para mostrar por páginas

En el cuadro de diálogo Mostrar páginas, seleccionamos el campo que vamos a usar para crear varias páginas y pulsaremos el botón de comando Aceptar.

ACTIVIDAD NO.11 Utilizando el texto de la materia lea, analice y desarrolle el tema ACTUALIZACION DE TABLAS DINAMICAS .

5. Actualización de tablas dinámicas Una tabla dinámica está vinculada a los datos fuente, pero la tabla no se actualiza automáticamente cada vez que se produce un cambio en los datos fuente.

Para actualizar una tabla es necesario que previamente haya sido creada con un nombre de rango y no un simple rango; para esto recuerde que debe utilizar el comando Ficha Fórmulas – Grupo Nombres Definidos – Administrador de Nombres, luego cada que ingrese nuevos datos es necesario editar el nombre del rango y cambiarlo.

Por último seleccione cualquier celda de la misma y escoja el comando Ficha Opciones – Grupo Datos – Actualizar, o simplemente en el menú contextual de la tabla dinámica escoja Actualizar.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

57

Sistema de calificación

Gráfico 78 Opción Actualizar de la Ficha Opciones

Si desea que Excel actualice su tabla dinámica cada vez que abra la hoja en que reside, debe seleccionar el comando Ficha Opciones – Grupo Tabla dinámica - Opciones – Datos – Actualizar al abrir archivo.

Gráfico 79 Opción Actualizar al abrir archivo

6. Conclusiones ACTIVIDAD NO.12 Tu opinión es muy importante, escribe por lo menos seis líneas de tu opinión personal acerca de éste capítulo, recuerda que será calificado como un deber más.

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

58

INDICES

IIN ND DIIC CE EG GE EN NE ER RA ALL UNIDAD 1 .................................................................................................................................................... 2 TEMA: FUNCIONES DESDE UN ENFOQUE MÁS AVANZADO .......................................................................... 2 1.

2.

3.

4.

5.

6.

7.

8.

9.

REFERENCIAS Y FÓRMULAS ........................................................................................................................... 2 Fórmulas sencillas ...................................................................................................................................... 3 Referencias absolutas y relativas ............................................................................................................... 4 Nombres de celdas y de conjuntos de celdas ............................................................................................. 6 FUNCIONES ............................................................................................................................................... 8 Estructura de una función .......................................................................................................................... 9 Errores comunes en fórmulas y funciones ............................................................................................... 10 FUNCIONES MATEMÁTICAS ........................................................................................................................ 10 Función PI................................................................................................................................................. 10 Función ENTERO ...................................................................................................................................... 10 Función POTENCIA ................................................................................................................................... 11 Función REDONDEAR ............................................................................................................................... 11 ANIDAMIENTO DE FUNCIONES ..................................................................................................................... 12 Escribiendo directamente en la barra de fórmulas .................................................................................. 12 Mediante el asistente de funciones ......................................................................................................... 14 FUNCIONES DE TEXTO ............................................................................................................................... 19 Función CONCATENAR ............................................................................................................................. 19 Función HALLAR ....................................................................................................................................... 19 Función REEMPLAZAR .............................................................................................................................. 20 Función EXTRAE ....................................................................................................................................... 21 Función IZQUIERDA .................................................................................................................................. 21 Función DERECHA .................................................................................................................................... 22 RECOMENDACIONES PARA BUSCAR SOLUCIONES A EJERCICIOS COMPLEJOS UTILIZANDO ANIDAMIENTO....................... 23 FUNCIONES LÓGICAS ................................................................................................................................. 25 Función SI ................................................................................................................................................. 25 Función Y .................................................................................................................................................. 26 Función O ................................................................................................................................................. 26 SI anidados............................................................................................................................................... 27 OTRAS FUNCIONES IMPORTANTES................................................................................................................ 29 Función BUSCARV .................................................................................................................................... 29 Función BUSCARH .................................................................................................................................... 30 Función PAGO .......................................................................................................................................... 31 CONCLUSIONES ........................................................................................................................................ 32

UNIDAD NO. 2............................................................................................................................................ 33 TEMA: BASE DE DATOS O LISTAS EN EXCEL ................................................................................................ 33 1.

2.

INTRODUCCIÓN ........................................................................................................................................ 33 ORDENACIÓN DE DATOS ............................................................................................................................ 34 Ordenación por un solo criterio ............................................................................................................... 34

INDICES

3.

4.

5.

6.

7.

8.

Ordenación por más de una columna ...................................................................................................... 35 VALIDACIÓN DE DATOS .............................................................................................................................. 36 Configuración ........................................................................................................................................... 37 Mensaje Entrante .................................................................................................................................... 38 Mensaje de Error...................................................................................................................................... 38 FILTRADO DE LISTAS .................................................................................................................................. 39 Filtros automáticos .................................................................................................................................. 39 Los 10 más de autofiltros ......................................................................................................................... 41 FILTROS PERSONALIZADOS ......................................................................................................................... 42 FILTROS AVANZADOS ................................................................................................................................ 44 Caracteres comodines .............................................................................................................................. 45 Una cantidad que desea comparar .......................................................................................................... 46 Consejos cuando introduzca criterios de comparación ............................................................................ 46 INSERCIÓN DE SUBTOTALES EN UNA LISTA DE DATOS ........................................................................................ 47 Mostrar filas de subtotales ...................................................................................................................... 48 Creación de Subtotales Anidados............................................................................................................. 50 CONCLUSIONES ........................................................................................................................................ 51

UNIDAD NO. 3............................................................................................................................................ 52 TEMA: TABLAS DINÁMICAS ........................................................................................................................ 52 1.

2.

3.

4.

5.

6.

INTRODUCCIÓN ........................................................................................................................................ 52 TERMINOLOGÍA DE TABLAS DINÁMICAS ........................................................................................................ 52 CREACIÓN DE TABLAS DINÁMICAS ............................................................................................................... 53 REORGANIZACIÓN DE TABLAS DINÁMICAS. ..................................................................................................... 56 ACTUALIZACIÓN DE TABLAS DINÁMICAS......................................................................................................... 57 CONCLUSIONES ........................................................................................................................................ 58

SISTEMA DE CALIFICACIÓN ..................................................................... ¡ERROR! MARCADOR NO DEFINIDO. PARÁMETRO ACTITUDINAL ............................................................................. ¡ERROR! MARCADOR NO DEFINIDO. PARÁMETRO PROCEDIMENTAL ....................................................................... ¡ERROR! MARCADOR NO DEFINIDO. PARÁMETRO COGNITIVO ................................................................................ ¡ERROR! MARCADOR NO DEFINIDO. REGLAS PARA EL USO DEL LABORATORIO ............................................... ¡ERROR! MARCADOR NO DEFINIDO. INDICE GENERAL ........................................................................................................................................ 59 INDICE DE GRÁFICOS .................................................................................................................................. 61

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

60

INDICES

IIN ND DIIC CE ED DE EG GR RÁ ÁFFIIC CO OS S GRÁFICO 1 LA REFERENCIA A LA CELDA SE PUEDE VISUALIZAR EN EL CUADRO DE NOMBRES ..................................................... 2 GRÁFICO 2 RANGO B3:D4 ........................................................................................................................................ 2 GRÁFICO 3 REFERENCIA MÚLTIPLE B3:B6 ; D3:D6 ....................................................................................................... 3 GRÁFICO 4 REFERENCIA A LA CELDA B3 DE LA HOJA 1 .................................................................................................... 3 GRÁFICO 5 FÓRMULA CON UNA OPERACIÓN BÁSICA PARA CALCULAR EL ÁREA DE UN RECTÁNGULO .......................................... 4 GRÁFICO 6 FORMULA CON REFERENCIA RELATIVA EN LAS CELDAS C4 Y D4 .......................................................................... 5 GRÁFICO 7 FÓRMULA CON REFERENCIA RELATIVA EN LA CELDA E4 Y REFERENCIA ABSOLUTA EN LA CELDA C10 .......................... 6 GRÁFICO 8 ASIGNAR NOMBRE A UN RANGO .................................................................................................................. 7 GRÁFICO 9 CUADRO DE DIÁLOGO ADMINISTRAR NOMBRES ............................................................................................. 8 GRÁFICO 10 LAS FUNCIONES HACEN MÁS FÁCIL EL USO DE EXCEL ...................................................................................... 8 GRÁFICO 11 ESTRUCTURA DE UNA FUNCIÓN ................................................................................................................. 9 GRÁFICO 12 FÓRMULA PARA CALCULAR EL ÁREA DE UN CÍRCULO UTILIZANDO LA FUNCIÓN PI()............................................. 10 GRÁFICO 13 EJEMPLO DE LA FUNCIÓN ENTERO ......................................................................................................... 11 GRÁFICO 14 FÓRMULA PARA CALCULAR EL ÁREA DEL CÍRCULO CON LA FUNCIÓN PI Y LA FUNCIÓN POTENCIA ........................ 11 GRÁFICO 15 EJEMPLO DE LA FUNCIÓN REDONDEAR .................................................................................................. 12 GRÁFICO 16 FÓRMULA PARA CALCULAR EL PERÍMETRO DE UN PARALELOGRAMO ............................................................... 13 GRÁFICO 17 PARA ANIDAR EMPIECE IDENTIFICANDO A LA FUNCIÓN PRINCIPAL ................................................................... 13 GRÁFICO 18 AL ANIDAR LA SEGUNDA FUNCIÓN APARECERÁ SU SINTAXIS........................................................................... 13 GRÁFICO 19 EL PRIMER ARGUMENTO DE LA FUNCIÓN SUMA ES LA FUNCIÓN PRODUCTO ................................................ 14 GRÁFICO 20 LA FUNCIÓN SUMA TIENE ANIDADO DOS FUNCIONES PRODUCTO............................................................... 14 GRÁFICO 21 CUADRO DE DIÁLOGO INSERTAR FUNCIÓN ................................................................................................ 15 GRÁFICO 22 LISTADO DE FUNCIONES DE LA CATEGORÍA MATEMÁTICAS Y TRIGONOMÉTRICAS ............................................... 15 GRÁFICO 23 CUADRO DE DIÁLOGO ARGUMENTOS DE FUNCIÓN ...................................................................................... 16 GRÁFICO 24 LA FUNCIÓN ANIDAR ESCOGEMOS DE LA LISTA DESPLEGABLE DEL CUADRO DE REFERENCIAS ................................. 16 GRÁFICO 25 CUADRO DE DIALOGO ARGUMENTOS DE LA FUNCIÓN ANIDADA ..................................................................... 17 GRÁFICO 26 REGRESAMOS AL CUADRO DE DIÁLOGO DE LA FUNCIÓN PRINCIPAL ................................................................. 17 GRÁFICO 27 EN LA BARRA DE FÓRMULAS SE HA ARMADO LA FUNCIÓN SIN NECESIDAD DE HABER ESCRITO NOSOTROS ................ 18 GRÁFICO 28 FUNCIONES SUMA Y ENTERO ANIDADAS DENTRO DE LA FUNCIÓN REDONDEAR ......................................... 19 GRÁFICO 29 LA FUNCIÓN CONCATENAR UNE TEXTOS................................................................................................ 19 GRÁFICO 30 EN LA FUNCIÓN HALLAR EL ARGUMENTO NÚM_INICIAL ES OPCIONAL ........................................................... 20 GRÁFICO 31 LA FUNCIÓN REEMPLAZAR TIENE 4 ARGUMENTOS................................................................................... 21 GRÁFICO 32 EJEMPLO DE LA FUNCIÓN EXTRAE .......................................................................................................... 21 GRÁFICO 33 EJEMPLO DE LA FUNCIÓN IZQUIERDA .................................................................................................... 22 GRÁFICO 34 EJEMPLO DE LA FUNCIÓN DERECHA ....................................................................................................... 22 GRÁFICO 35 SOLUCIÓN NO VÁLIDA PARA VARIOS DATOS ............................................................................................... 23 GRÁFICO 36 ANIDAMIENTO DE UNA PRIMERA FUNCIÓN PARA BUSCAR UNA SOLUCIÓN CORRECTA.......................................... 24 GRÁFICO 37 ANIDAMIENTO DE UNA SEGUNDA FUNCIÓN DENTRO DE LA FUNCIÓN EXTRAE ................................................. 24 GRÁFICO 38 FÓRMULA ANIDADA CORRECTA PARA VARIOS DATOS ................................................................................... 25 GRÁFICO 39 LA FUNCIÓN SI PERMITE REALIZAR UNA PREGUNTA LÓGICA .......................................................................... 26 GRÁFICO 40 EN LA FUNCIÓN Y BASTA QUE UN ARGUMENTO SEA FALSO PARA QUE LA RESPUESTA SEA FALSO ....................... 26 GRÁFICO 41 EN LA FUNCIÓN O, BASTA QUE UN ARGUMENTO SEA VERDADERO PARA QUE LA RESPUESTA SEA VERDADERO . 27 GRÁFICO 42 USO DE LA FUNCIÓN SI CONJUNTAMENTE CON LA FUNCIÓN Y PARA CALCULAR EQUIVALENCIAS........................... 28 GRÁFICO 43 ANIDAMIENTO DE UNA FUNCIÓN SI DENTRO DE OTRA FUNCIÓN SI ................................................................. 28

INDICES GRÁFICO 44 SI ANIDADOS PARA CALCULAR EQUIVALENCIAS ........................................................................................... 29 GRÁFICO 45 LA FUNCIÓN BUSCARV BUSCA EN LA PRIMERA COLUMNA DE UN RANGO DADO ............................................... 30 GRÁFICO 46 LA FUNCIÓN BUSCARH BUSCA EN LA PRIMERA FILA DEL RANGO SELECCIONADO .............................................. 31 GRÁFICO 47 EJEMPLO DE LA FUNCIÓN PAGO ............................................................................................................. 32 GRÁFICO 48 BASE DE DATOS DE EJEMPLO TOMADO DE UNA GUÍA TELEFÓNICA................................................................... 33 GRÁFICO 49 CUADRO DE DIÁLOGO ORDENAR ............................................................................................................. 35 GRÁFICO 50 AGREGANDO NIVELES DE ORDENACIÓN..................................................................................................... 36 GRÁFICO 51 CUADRO DE DIÁLOGO DE VALIDACIÓN DE DATOS ....................................................................................... 37 GRÁFICO 52 FICHA CONFIGURACIÓN DEL CUADRO DE DIÁLOGO VALIDACIÓN DE DATOS ...................................................... 37 GRÁFICO 53 FICHA MENSAJE ENTRANTE DEL CUADRO DE DIÁLOGO VALIDACIÓN DE DATOS.................................................. 38 GRÁFICO 54 FICHA MENSAJE DE ERROR DEL CUADRO DE DIÁLOGO VALIDACIÓN DE DATOS .................................................. 38 GRÁFICO 55 LA VALIDACIÓN NO PERMITE INGRESAR DATOS INCORRECTOS ........................................................................ 39 GRÁFICO 56 CUADRO DE DIÁLOGO BUSCAR Y REEMPLAZAR ........................................................................................... 40 GRÁFICO 57 FILTRO AUTOMÁTICO ACTIVADO.............................................................................................................. 40 GRÁFICO 58 BASE DE DATOS FILTRADA LOS CAMPOS CURSO Y PARALELO ..................................................................... 41 GRÁFICO 59 CUADRO DE DIÁLOGO AUTOFILTRO DE LAS DIEZ MEJORES ............................................................................. 41 GRÁFICO 60 OPCIONES DE UN CAMPO FILTRADO ......................................................................................................... 43 GRÁFICO 61 CUADRO DE DIÁLOGO AUTOFILTRO PERSONALIZADO ................................................................................... 43 GRÁFICO 62 BASE DE DATOS FILTRADA CON FILTROS PERSONALIZADOS ............................................................................ 43 GRÁFICO 63 EL PRIMER PASO PARA REALIZAR FILTROS AVANZADOS ES COPIAR LAS CABECERAS .............................................. 44 GRÁFICO 64 LAS CONDICIONES SE INGRESAN DEBAJO LAS CABECERAS COPIADAS ................................................................ 44 GRÁFICO 65 CUADRO DE DIÁLOGO FILTRO AVANZADO ................................................................................................. 45 GRÁFICO 66 RESULTADO DE UN FILTRO AVANZADO, FILTRADO EN LA MISMA LISTA ............................................................. 45 GRÁFICO 67 CARACTERES COMODÍN Y VARIAS CONDICIONES EN FILTROS AVANZADOS ......................................................... 47 GRÁFICO 68 BASE DE DATOS DE VENDEDORES ............................................................................................................. 48 GRÁFICO 69 CUADRO DE DIÁLOGO PARA SUBTOTALES .................................................................................................. 49 GRÁFICO 70 SUBTOTALES DE LA BASE DE DATOS DE EJEMPLO ......................................................................................... 49 GRÁFICO 71 SUBTOTALES ANIDADOS ........................................................................................................................ 50 GRÁFICO 72 PRIMER PASO DE LA CREACIÓN DE UNA TABLA DINÁMICA ............................................................................. 53 GRÁFICO 73 ÁREAS Y LISTAS DE CAMPOS DE UNA TABLA DINÁMICA ................................................................................. 54 GRÁFICO 74 TABLA DINÁMICA Y SU ESTRUCTURA ......................................................................................................... 55 GRÁFICO 75 TABLA DINÁMICA CON BASE DE DATOS DE ESTUDIANTES ............................................................................... 55 GRÁFICO 76 LISTA DESPLEGABLE DEL ÁREA DE FILTRO DE INFORME............................................................................ 56 GRÁFICO 77 OPCIÓN PARA MOSTRAR POR PÁGINAS ..................................................................................................... 57 GRÁFICO 78 OPCIÓN ACTUALIZAR DE LA FICHA OPCIONES ............................................................................................ 58 GRÁFICO 79 OPCIÓN ACTUALIZAR AL ABRIR ARCHIVO ................................................................................................... 58

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

62

INDICES

Colegio Técnico Salesiano Computación III Décimos de Educación General Básica

63