trucos de busqueda

[UNIDAD EDUCATIVA SALESIANA MARIA AUXILIADORA │

Módulo Excel | 1

1. ¿Qué es el Excel? ---------------------------------------------------------------------------------------------- 4

Rango de Datos ---------------------------------------------------------------------------------------------------- 4

1.2 Referencias absolutas y referencias relativas -------------------------------------------------------- 5

1.3 Ejercicio 1 ------------------------------------------------------------------------------------------------------ 5

1.4 Operadores ---------------------------------------------------------------------------------------------------- 6

1.4.1 Operadores aritméticos --------------------------------------------------------------------------------- 6

1.4.2 Operadores de Comparación -------------------------------------------------------------------------- 6

1.4.3 Operador de Texto --------------------------------------------------------------------------------------- 7

2. Series y Listas --------------------------------------------------------------------------------------------------- 7

3. Seleccionar una fila, columna u hoja de cálculo completa ----------------------------------------- 8

4. ¿Cómo variar las celdas de la hoja de cálculo? -------------------------------------------------------- 8

4.1 Variar el ancho de columna o el grosor de la fila --------------------------------------------------- 8

4.2 Insertar o eliminar una fila y una columna ----------------------------------------------------------- 8

5. Dar formato a la información ----------------------------------------------------------------------------- 8

5.1 Barra de formato --------------------------------------------------------------------------------------------- 8

5.2. Formato-------------------------------------------------------------------------------------------------------- 9

5.2.1 Celdas --------------------------------------------------------------------------------------------------------- 9

5.2.2 Número ---------------------------------------------------------------------------------------------------- 10

5.2.3 Fuentes ----------------------------------------------------------------------------------------------------- 10

5.2.4 Bordes y Diseño de celdas ---------------------------------------------------------------------------- 10

5.2.5 Alineación ------------------------------------------------------------------------------------------------- 11

5.2.6 Fila ----------------------------------------------------------------------------------------------------------- 11

5.2.7 Columna --------------------------------------------------------------------------------------------------- 11

5.2.8 Hoja --------------------------------------------------------------------------------------------------------- 12

PRACTICA 2.1. ---------------------------------------------------------------------------------------------------- 12

PRACTICA 2.2. ---------------------------------------------------------------------------------------------------- 12

6. Funciones ------------------------------------------------------------------------------------------------------ 12

6.1 Familia de Funciones -------------------------------------------------------------------------------------- 13

6.2 Mensajes de error más frecuentes -------------------------------------------------------------------- 13

7. Funciones de la familia de Fecha y Hora -------------------------------------------------------------- 14

7.1 Formato de Fechas ---------------------------------------------------------------------------------------- 14

7.1.1 Formatos Predefinidos --------------------------------------------------------------------------------- 14

7.1.2 Formatos personalizados ----------------------------------------------------------------------------- 15

8. Fórmulas con Fechas --------------------------------------------------------------------------------------- 15

8.1 Sumar días a una fecha ----------------------------------------------------------------------------------- 15

8.1.1 Función Fecha.Mes (Fecha_inicial;meses) -------------------------------------------------------- 16


Módulo Excel | 2

8.1.2 Función Fin.Mes (Fecha_inicial;meses) ----------------------------------------------------------- 17

8.2 Restar dos fechas ------------------------------------------------------------------------------------------ 18

8.2.1 Funciones Asociadas a Fechas ----------------------------------------------------------------------- 18

Practica 3.1 ------------------------------------------------------------------------------------------------------- 19

8.2.2 Aplicaciones de las funciones de fecha ------------------------------------------------------------ 19

Caso1. Calcular la Edad ---------------------------------------------------------------------------------------- 19

Caso 2 Calcular el primer día del mes de una fecha --------------------------------------------------- 19

Otras aplicaciones con funciones de fecha -------------------------------------------------------------- 20

8.2.3 Función DIAS.LAB --------------------------------------------------------------------------------------- 20

9. Funciones de Texto ----------------------------------------------------------------------------------------- 21

9.1.1 Largo(celda) ---------------------------------------------------------------------------------------------- 21

9.1.2 Mayusc(celda) -------------------------------------------------------------------------------------------- 21

9.1.3 Minusc(celda) -------------------------------------------------------------------------------------------- 22

9.1.4 Izquierda (cadena;caracteres) ----------------------------------------------------------------------- 22

9.1.5 Derecha (cadena;caracteres) ------------------------------------------------------------------------- 22

9.1.6 Extrae(cadena;posición_inicial;num_caracteres) ---------------------------------------------- 22

9.1.7 Hallar (texto_buscado;dentro_del_texto;[num_inicial]) ------------------------------------- 22

9.1.8 Encontrar(texto_buscado;dentro_del_texto;[num_inicial]) -------------------------------- 22

9.2 Aplicaciones de las funciones de Texto -------------------------------------------------------------- 22

9.2.1 Concatenar Textos -------------------------------------------------------------------------------------- 22

9.2.2 Creación de códigos autogenerados --------------------------------------------------------------- 22

9.2.3 Texto en Columnas ------------------------------------------------------------------------------------- 23

9.2.4 Práctica Texto1 ------------------------------------------------------------------------------------------- 24

10. Funciones de Información ------------------------------------------------------------------------------- 25

10.1 Función ES -------------------------------------------------------------------------------------------------- 25

10.1.1 ESBLANCO (celda) ------------------------------------------------------------------------------------- 25

10.1.2 ESERR (celda) ------------------------------------------------------------------------------------------- 25

10.1.3 ESERROR (celda) --------------------------------------------------------------------------------------- 25

10.1.4 ESLOGICO (celda) -------------------------------------------------------------------------------------- 25

10.1.5 ESNOD (celda) ------------------------------------------------------------------------------------------ 25

10.1.6 ESNOTEXTO (celda) ----------------------------------------------------------------------------------- 26

10.1.7 ESNUMERO (celda) ------------------------------------------------------------------------------------ 26

10.1.8 ESREF (celda) -------------------------------------------------------------------------------------------- 26

10.1.9 ESTEXTO (celda) ---------------------------------------------------------------------------------------- 26

10.2 Otras funciones de información ---------------------------------------------------------------------- 26

10.2.1 ES.PAR (numero) --------------------------------------------------------------------------------------- 26


Módulo Excel | 3

10.2.2 ES.IMPAR (numero) ----------------------------------------------------------------------------------- 26

10.2.3 CELDA (tipo;ref) ---------------------------------------------------------------------------------------- 26

11. Comparación de alternativas -------------------------------------------------------------------------- 27

Práctica Funciones ---------------------------------------------------------------------------------------------- 28

TRABAJO FINAL -------------------------------------------------------------------------------------------------- 28

12. INTRODUCCION -------------------------------------------------------------------------------------------- 30

EJERCICIO 4 ------------------------------------------------------------------------------------------------------- 31

12.1 Gráficos ---------------------------------------------------------------------------------------------------- 32

12.1.1 Asistente para Gráficos ------------------------------------------------------------------------------ 32

12.1.2 Modificación de los Gráficos ----------------------------------------------------------------------- 34

Práctica 5 ---------------------------------------------------------------------------------------------------------- 35

13. Bases de Datos --------------------------------------------------------------------------------------------- 35

13.1 Ordenar datos --------------------------------------------------------------------------------------------- 36

13.2 Filtrado de una Lista ------------------------------------------------------------------------------------- 37

14. Automatización de Tareas Repetitivas -------------------------------------------------------------- 38

14.1 Creación de Macros-------------------------------------------------------------------------------------- 38

14.2 Ejecución de macros ------------------------------------------------------------------------------------- 38

Práctica 8 ---------------------------------------------------------------------------------------------------------- 39


Módulo Excel | 4

1. ¿Qué es el Excel? Microsoft Excel es un potente programa de aplicación del tipo hoja de cálculo que se puede utilizar de forma eficaz para almacenar y trabajar con listas de datos, calcular números, crear informes y gráficos. Está diseñada para manipular datos y números, con base en el concepto de hoja tabular y se utiliza para resolver cálculos matemáticos en distintas disciplinas. Entre sus principales funciones incluye el manejo de:

Fórmulas y Funciones Bases de datos Macros Mapas Hipervínculos Gráficos Estadísticos

Excel organiza todos los datos en archivos llamados Libros de Trabajo, los cuales se componen de hojas de trabajo u hojas de cálculo, estos tienen una extensión xls. Dicha hoja está dividida en filas y columnas. La intersección de una fila y una columna se denomina celda. Cada celda tiene una dirección única, que también se denomina referencia de celda. Las líneas de división sirven para identificar rápidamente dónde está la celda. Las filas están identificadas por números en la barra vertical de la izquierda y las columnas por letras en la barra superior horizontal. Cada celda tiene una identificación única dada por la combinación de la letra que designa la columna y el número de fila. Por ejemplo, la celda creada por la intersección de la columna G y la fila 4 se denomina celda G4, es así que una hoja de trabajo tiene 16384 columnas y 1048576 filas. Con Microsoft Excel se pueden realizar diferentes aplicaciones como por ejemplo:

Elaboración de facturas Cálculos de Impuestos Control de Notas de Alumnos Elaboración de Roles de Pago, etc.

Rango de Datos Un conjunto rectangular de una o más celdas es un rango. Los rangos se determinan para aplicar un comando o una fórmula a todas las celdas que se agrupan. Un rango se designa por lo extremos de sus diagonales, por ejemplo, "B2:F6" indica que el rango inicia en la celda B2 y termina en F6.

Filas Columna

Celda Activa

B2 : F6 Desde B2 (: ) hasta F6


Módulo Excel | 5

1.2 Referencias absolutas y referencias relativas Cuando en Excel introduce una fórmula que hace referencia a otra u otras celdas hay varias formas de hacerlo según el uso que vaya a hacer posteriormente de ella. Una referencia relativa indica la posición relativa de una celda con respecto a la que contiene la fórmula y cuando se copia en otra posición modifica y actualiza las posiciones. Son referencias posiciónales, ejemplo: C4. Una referencia absoluta es una introducción explícita y única de otra celda en un cálculo, no de su posición relativa, ejemplo: $C$4. En la hoja de cálculo se trabaja normalmente con referencias relativas. Es el tipo de referencia que emplea Excel por defecto. Para trabajar con referencias absolutas se debe especificar escribiendo el signo $ delante de la letra de la columna y el número de fila. Por ejemplo $A$3 se refiere exclusivamente a la celda A3 y, al aplicar llenados –hacia abajo o hacia la derecha- u operaciones de copiar y pegar las referencias que tengan el signo $ delante no serán actualizadas. También puede incluirse referencias mixtas. Son referencias mixtas $C4 o C$4. en el primer caso la columna es absoluta y la fila relativa, y en el segundo caso la columna es relativa y la fila absoluta. 1.3 Ejercicio 1 a) Enlace lo correcto con una línea, puede haber más de una respuesta acertada: Microsoft Excel: Es un Programa Es un Sistema Operativo Es un Procesador de Palabras Es Software Es un Graficador Sirve para hacer cálculos Es de la empresa Microsoft Es Hardware Pertenece al paquete Office b) Señale lo correcto haciendo un círculo junto a la pregunta: Hablar de Excel es igual que hablar de Hoja de Cálculo F V Existe excel en diferentes versiones? F V Excel funciona sólo con Windows XP ? F V ¿Escriba para que sirve Excel? _______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________. d) Responda: Los archivos de Excel tienen la extensión: __________________________________. La letra que identifica a Excel es: __________________________________________.


Módulo Excel | 6

¿Qué es una hoja de cálculo? ______________________________________________________________________________________________________________________________________________________________________________________________________ ____________________________________________________________________________________________________________________________________________________________________________________________________. ¿Qué son celdas? _________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________. ¿Qué son referencias de celda? _______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________. ¿Qué es un Rango de celda? ______________________________________________________________________________________________________________________________________________________________________________________________________. 1.4 Operadores Los operadores son signos que se utilizan para estructurar determinada operación o fórmula; por ejemplo, la suma, si son aritméticos, o mayor que, si son de comparación. 1.4.1 Operadores aritméticos Los operadores aritméticos realizan operaciones matemáticas básicas con valores numéricos, recuerde siempre antes de introducir una fórmula/función coloque el signo igual (=) al comienzo por ejemplo =A1+B1. Excel ofrece los siguientes operadores aritméticos:

/ División - Resta o negación, resta dos valores o niega uno. * Multiplicación % Porcentaje ^ Función exponencial + Suma

1.4.2 Operadores de Comparación Son utilizados para obtener valores lógicos, es decir, FALSO o VERDADERO. También debe iniciarse antes de la fórmula con el signo igual (=). Excel ofrece los siguientes operadores de comparación:

= Igual > Mayor que < Menor que


Módulo Excel | 7

>= Mayor o igual <= Menor o igual <> Diferente de

Por ejemplo, si necesitamos compara los valores de las celdas A2 (75) y F5 (50) podemos utilizar la siguiente fórmula: = A2 > F5 Que, devolverá un valor VERDADERO en la celda activa, ya que el valor de A2 es mayor que F5.

1.4.3 Operador de Texto Existe un operador de texto que uno dos o más valores en uno solo, este operador es &.por ejemplo, si la celda C2 contiene el texto "Ingreso de alumnas" y la celda D2 contiene el texto "para el año 2002", la fórmula sería: = C2 & D2 y el resultado sería: "Ingreso de alumnas para el año 2002" 2. Series y Listas Excel tiene la posibilidad de llenar las casillas con datos automáticamente. Esto se puede realizar de dos maneras, cada una es para un tipo de datos distinto. Así: Seleccionando Edición, luego Llenar y finalmente Series. Aparecerá el cuadro de diálogo Series en donde se escogerán las diversas opciones que tiene el mismo. Otra manera de rellanar una serie en el caso de que la serie contenga datos numéricos es la siguiente: Escribir los dos primeros números de la serie con su intervalo correspondiente. Luego marcar las celdas que contienen los números.

Colocar el puntero del mouse en la parte inferior izquierda hasta que el cursor cambie a +.

A continuación arrastrar las celdas marcadas y la serie se generará automáticamente.


Módulo Excel | 8

3. Seleccionar una fila, columna u hoja de cálculo completa Para seleccionar una celda individual: Hacer clic en la celda deseada. Para seleccionar una fila o columna: Hacer clic en los encabezados de la fila o columna. Para seleccionar toda la hoja de cálculo: Hacer clic en el botón Seleccionar todo (cuadrado de la hoja de cálculo situado en la esquina superior izquierda debajo de las barras de herramientas). Para seleccionar un rango de celdas: Arrastrar desde la primera celda del rango a la última. Para celdas no adyacentes: Seleccionar la primera celda o rango que se desee, y con la tecla Ctrl, pulsada, seleccionar la celda o rango siguiente. 4. ¿Cómo variar las celdas de la hoja de cálculo? 4.1 Variar el ancho de columna o el grosor de la fila Para variar el ancho de la columna se coloca el puntero del mouse entre las celdas hasta que aparezca el icono de una flecha de doble punta, manteniendo pulsado el botón del ratón y se puede arrastrar la división hasta donde se desea. Para variar el ancho de una fila debe seguir los mismos pasos anteriormente explicados. 4.2 Insertar o eliminar una fila y una columna Se selecciona la fila sobre la que se desea que aparezca la nueva línea, es decir si se quiere que la fila nueva sea la 2, seleccione la fila 2 actual. Una vez hecha la selección elegir Insertar Fila, de forma automática aparece la nueva fila, y se desplaza toda la información hacia abajo. Cuando se desea insertar una columna la nueva desplaza a las anteriores a la derecha. Para eliminar una fila o una columna completa únicamente es necesario seleccionarla y elegir Edición Eliminar. 5. Dar formato a la información La forma en que la información aparece en la hoja de cálculo se puede cambiar si se cambia el tamaño, estilo y color de los datos dentro de esas celdas. El formato predeterminado que presenta Excel, diferencia entre valores numéricos y valores de texto: basta fijarse en que la alineación dentro de la celda no es la misma en los dos casos. Del mismo modo, los formatos aplicables a las celdas por el usuario, se establecen por separada según sean de aplicación sobre números, texto o ambas cosas. A una celda se le puede aplicar formato a través del menú Formato en la opción Celdas o a través de la barra de Formato. 5.1 Barra de formato

La forma más sencilla de variar el aspecto de las celdas es a través de esta barra. Todas las funciones de esta barra se pueden activar antes de escribir los datos, o bien cuando ya se ha escrito seleccionando lo que se quiera variar con el ratón.


Módulo Excel | 9

El primer recuadro es el tipo de letra, junto con el tamaño que se le quiera dar. La negrita, cursiva y subrayado ayuda a resaltar datos significativos.

También se puede alinear el texto dentro de las celdas, con los botones de la barra en forma de párrafo se puede alinear el texto o los números, a la izquierda, en el centro y a la derecha.

Este botón sirve para convertir varias celdas en una sola de modo que sea la cabecera de distintas columnas.

El botón con monedas, sirve para añadir el símbolo monetario que esté definido. El % sirve para los porcentajes, hay que introducir los datos en tanto por uno, el ordenador multiplicará por cien el dato que se le introduzca.

Los ceros con flechita hacia la izquierda o hacia la derecha sirven para añadir o quitar decimales.

El primer botón que es un recuadro sirve para añadir bordes a las celdas, pinchando sobre este botón se despliega un menú donde aparecen diferentes ventanitas con las diferentes formas de aplicar los bordes. Con el bote de pintura se puede seleccionar en color del relleno de la celda, y aplicarlo a la zona en la que se de un clic. Y con la letra se selecciona el color de la letra de la celda, y se aplica. En ambos casos hay que pinchar la lecha de la derecha para que se despliegue la paleta de colores. 5.2. Formato 5.2.1 Celdas En el menú Formato Celdas aparecerá un cuadro de diálogo donde se encuentran todas las posibilidades para formatear el contenido de las celdas, de esa forma conseguirá presentaciones de alta calidad. Todas las celdas en una hoja de cálculo nueva tienen formato estándar. Para cambiar el formato, hay que seleccionar el conjunto de celdas sobre las que queremos que se apliquen los cambios y elegir el menú Formato Celdas.


Módulo Excel | 10

5.2.2 Número Se selecciona la sección correspondiente a Número haciendo clic en la etiqueta de la carpeta. Puede emplearse uno de los formatos de número incorporados o crear formatos personalizados. En el cuadro de diálogo podrá elegir Categorías. Si elige todas verá todos los formatos posibles en cuadro Tipo, para un formato más concreto como Monedas, Fecha y Hora, Porcentaje, en el Tipo aparecerán las opciones más específicas. En el primer recuadro de Tipo muestra el formato seleccionado. Para agregar un formato personalizado, escriba ahí uno nuevo. Este se agregará al tipo de valor que esté seleccionado en ese momento. Hacer clic sobre Aceptar. Cuando se trabaja con el formato Porcentaje Excel multiplica por 100 el valor de la celda automáticamente. Hay que tenerlo en cuenta para introducir bien los números y también en las fórmulas de porcentajes: el dato debe estar en tanto por uno para que la aplicación del formato sea correcta. 5.2.3 Fuentes Algunos elementos de formato son aplicables a las celdas con independencia del tipo de valor que contenga. Por ejemplo, la fuente, el tamaño o el color. La opción Formato Celdas en la carpeta Fuentes es donde se podrá elegir el formato de los caracteres previamente seleccionados. Dentro del formato de carácter hay distintos componentes la fuente, que es el tipo de letra, el estilo: si el texto irá en cursiva, negrita, el subrayado, el color y los efectos de tachado, superíndice y subíndice. Todas estas modificaciones ayudan a poner títulos, destacar, subrayar y seleccionar color. Los cambios que se vayan efectuando se verán reflejados dentro del cuadro Muestra. 5.2.4 Bordes y Diseño de celdas Excel ofrece la posibilidad de crear fácilmente líneas y tramas para organizar y presentar de forma más clara la información de la hoja de cálculo. Con la sección Bordes del menú Formato Celdas se puede personalizar, elegir el borde que se desee para las celdas, el estilo y también su color.


Módulo Excel | 11

En la sección Diseño se elegirán las tramas y sombreados que permitirán modificar la presentación.

5.2.5 Alineación La información tipo texto en las celdas aparece alineada a la izquierda. Se diferencia de la numérica porque esta aparece alineada a la derecha. Por defecto la orientación Horizontal es la general que diferencia texto y números. Se puede cambiar y alinear números a la izquierda. Para hacerlo bastaría con seleccionar el rango de celdas a que se desea aplicar, elegir Formato Alineación y hacer un clic en el botón izquierda y Aceptar. La orientación Vertical se refiere a la situación del texto en la celda considerando de arriba hacia abajo. Tendrá importancia en celdas muy anchas o cuando se requiere buena presentación en tablas, etc. Podrá elegir superior, cerca del borde superior de la celda, media, centrada verticalmente, o inferior, cerca del borde inferior de la celda. El sentido de la información en la celda puede cambiarse. Dentro del cuadro de diálogo orientación verá reflejados los cambios que realice en la orientación del texto que haya seleccionado. La orientación del texto se puede cambiar tanto en una celda, como en un título de un gráfico, en un rótulo de un eje, etc. Para que el texto de una celda se divida en varias líneas se marca la opción Retorno Automático de este cuadro de diálogo. 5.2.6 Fila Al seleccionar Fila dentro del menú Formato se despliega un menú con diferentes opciones referentes al tamaño y a la vista o no de la fila. Con Alto se selecciona la altura de toda la fila. Con Ajustar a la selección la altura de toda la fila al tamaño de una de sus celdas. Con las opciones Ocultar y Mostrar. Se puede ocultar en pantalla una fila, o mostrarla. Estos datos no son eliminados de la hoja de cálculo, pueden ser recuperados en cualquier momento. Esto suele ser necesario cuando se trabaja con muchos datos, y se prefieres ocultar aquello que no sea necesario en ese momento. 5.2.7 Columna Al igual que en Fila, el menú que se despliega al pinchar sobre Columna sirve para ajustar el tamaño de la columna en la que se encuentre el cursor en ese momento. En el caso de la columna lo que se ajusta es el ancho. También se puede ocultar o mostrar la columna ocultas. Para esto último se debe seleccionar Edición Ir a _ dar la referencia de la columna o fila que queramos volver a mostrar. Y sin pulsar en ninguna celda seleccionar Formato Columna Mostrar.


Módulo Excel | 12

5.2.8 Hoja Esta opción se refiere a la hoja en la que estemos trabajando en ese momento, se le puede cambiar el nombre, ponerle un fondo distinto, y al igual que las columnas y las filas, se puede ocultar o mostrar. PRACTICA 2.1. Abra el archivo Practica 2.1. fórmulas” de Practicas de Excel en la carpeta de su curso. Utilice para la parte de Ingresos una fórmula con + para sumar los datos tanto de manera horizontal como vertical. Para la parte de Gastos utilice Autosuma Los resultados de las sumas deben ubicarse en las filas y columnas sombreadas. En la hoja “Costos” realice las fórmulas de suma, sin utilizar autosuma, para obtener los costos para los meses indicados En la hoja “Ventas” realice la fórmula para calcular el precio total para la lista de productos, haga la primera fórmula y copie para las filas siguientes En la hoja “Promedios” obtenga el precio promedio para los artículos (columna E) y el promedio de ventas anual (fila 12). Realice usted mismo la fórmula o utilice la función financiera promedio En la hoja “Sueldos” obtenga el total a pagar al empleado según las horas trabajadas En la hoja “Ganancia” realice las fórmulas necesarias para obtener la ganancia en los artículos En la hoja “Operadores” coloque en la columna D, una operación con el signo necesario para que la respuesta de VERDADERO. No repita ningún operador En la misma hoja en la parte inferior coloque los números necesarios en la fila 14, para que en todas las condiciones aparezca el valor VERDADERO PRACTICA 2.2. Abra el archivo Practica 2.2. Referencias Absolutas” de Practicas de Excel En la hoja “Series” Desde C5 haga una serie que comience en 4, con el intervalo de 3 hasta C25 Desde D5 haga una serie que comience en 1 con intervalo de 5 hasta C25 Desde F5 obtenga la suma de los elementos de las dos series de la misma fila Desde H5 obtenga el promedio de los elementos de las dos series de la misma fila Desde G5 sume cada elemento de la Serie 1 con el valor de A5 Desde I5 obtenga el promedio de cada elemento de la Serie 2 con el valor de A5 En la última hoja “Incremento Mensual”, siga las indicaciones dadas en las celdas 6. Funciones Las funciones son fórmulas programadas o previamente hechas en Excel que tienen un nombre que permite hacer gran cantidad de cálculos. Excel contiene cientos de funciones que puede utilizar en fórmulas. Una función puede requerir de uno o más valores y ofrecer como resultado uno o más valores. Entrada Función Resultado Por ejemplo la función RAIZ que permite calcular la raíz cuadrada de un número, recibe como entrada el número del que se desea calcular y devuelve como resultado la raíz cuadrada de ese número.


Módulo Excel | 13

Las entradas de una función reciben el nombre de argumentos Para escribir una función debe considerarse:

Utilizar el signo = antes del nombre de la función. Los argumentos o datos para la fórmula deben especificarse entre paréntesis,

luego del nombre de la función. Si hay varios argumentos deben separarse por puntos y comas (;), o comas (,)

según la configuración del computador. Por ejemplo para calcular 42 se puede utilizar la función POTENCIA y la fórmula sería: = POTENCIA (4;2) Para crear una función puede escribir directamente el nombre de la misma sobre una

celda, escoger la opción Insertar/función o puede selección el botón luego debe ingresar los argumentos que se le piden. 6.1 Familia de Funciones Excel provee gran cantidad de funciones, las cuales pueden ser agrupadas en las siguientes familias: Financieras: Para cálculos financieros, depreciaciones, cálculos de interés, etc. Matemáticas y Trigonométricas: Proporciona una variedad de funciones matemáticas y trigonométricas, exponenciales, radianes, logarítmicas, factoriales, para redondear, matrices, etc. Estadísticas: Funciones para trabajo estadístico como variaciones, desviaciones, probabilidades, distribuciones, etc. Búsqueda y referencia: Estas funciones permiten buscar uno o varios datos dentro de un grupo, de los valores que cumplen con cierto criterio de búsqueda. Base de Datos: Funciones para el trabajo con Datos grabados previamente en una Base, con la que se puede relacionar en Excel. Texto: Son funciones para trabajar con textos, cadenas de caracteres, por ejemplo, para convertir textos en mayúsculas, buscar letras o palabras en frases, etc. Lógicas: Funciones lógicas como Y, O, FALSO, VERDADERO Información: Estas funciones son para obtener información sobre datos, textos almacenados en las celdas. Fecha y Hora: Permite obtener información referente a la fecha, hora, hacer cálculos con fechas, etc. 6.2 Mensajes de error más frecuentes Cuando se usan fórmulas, es posible que estas no estén bien construidas, dependiendo de la situación, en las celdas cuyos valores no pueden determinarse aparece un mensaje que empieza con el símbolo #. Estos mensajes pueden ser los siguientes: #DIV/0 Aparece si la fórmula contiene una división para cero. #N/A Aparece si una fórmula hace referencia a una celda que no tiene valor. #¡NULO¡ Ocurre cuando se intersecan dos grupos de celdas y no hay ninguna celda

común a las dos áreas. #¡NUM¡ Indica que existe un problema relacionado con un número, esto ocurre

cuando se especifica un argumento numérico incorrecto para una función


Módulo Excel | 14

Por ejemplo: RAIZ(-2) o cuando el resultado de una fórmula es un número muy pequeño o uno muy grande POTENCIA (999,99).

# ¡VALOR¡ Aparece cuando no se usa el tipo de argumento que necesita la fórmula, ejemplo: RAIZ(“de”), en este caso se necesita de un número no de una palabra. 7. Funciones de la familia de Fecha y Hora Excel considera a las fechas como valores numéricos, Estos valores están entre 1 el cual es considerado como 1 de enero de 1900 al 2958465 correspondiente al 31 de diciembre de 9999, Así, el numero 5 corresponde al 5 de enero de 1900 y el 32 al 1 de febrero de 1900. Al contener números, las celdas pueden ser usadas para definir fórmulas. El ingreso de fechas debe ser muy preciso. En la hoja 1 de su libro de Excel, escriba:

5-dic 5-12 5/12 5/12/86

Si no se escribe el año, las fechas son consideradas del año actual. Si se escribe una fecha y solamente se escribe dos dígitos para el año, Microsoft Excel completa el año 2000 para fechas menores a 1930 y 1900 para los posteriores. Si usted escribe: 30-mar-10 Excel la considera como 30-mar-2010 Si usted escribe: 30-mar-72 Excel la considera como 30-mar-1972 7.1 Formato de Fechas Excel aplica un formato automático a las celdas que contiene fechas, el formato aplicado es dd-mmm dos dígitos para el día y 3 caracteres para el mes. 7.1.1 Formatos Predefinidos Para cambiar el formato de fechas aplicado a una celda o a un rango se realiza el siguiente procedimiento:

Seleccione las celdas a cambiar el formato.

Elija el menú Formato, Celdas, Número y finalmente escoja la categoría Fecha.

Seleccione uno de los formatos

integrados.

En la siguiente ilustración, se muestra algunos de los formatos de este cuadro de diálogo. Para probar estas opciones escriba en alguna celda la fecha 12-11-71 y aplique los formatos integrados que aparecen en el cuadro de diálogo antes mostrado.


Módulo Excel | 15

7.1.2 Formatos personalizados Al igual que con los números, Excel permite crear formatos personalizados para mostrar solo el mes de una fecha, quizás incluir el nombre del día a una fecha o incluso mostrar un texto que acompañe a una fecha: La fecha 30-3 se puede mostrar como : Marzo Martes 30 de marzo 30- Mar Cumpleaños de Doris Para realizar estas acciones es necesario conocer los códigos que tiene Excel para el tratamiento de fechas, los mismos que son mostrados en la siguiente tabla: CÓDIGO UTILIDAD d Muestra dígitos para el día. ddd Muestra la abreviatura del día. dddd Muestra el nombre del día m Muestra dígitos para el mes mmm Muestra la abreviatura del mes mmmm Muestra el nombre del mes aa Muestra dígitos para el año aaaa Muestra 4 dígitos para el año 8. Fórmulas con Fechas Las fechas son tratadas como números. Así usted podrá usar celdas que contiene fechas para usar funciones o definir fórmulas. Podrá sumar días a una fecha o restar dos fechas para hallar el intervalo de días entre ellas. 8.1 Sumar días a una fecha Al sumar números a una celda que contenga fechas, Excel considera que se suman días y el resultado se representa con formato de fecha. Observando un ejemplo práctico se pide calcular el Vencimiento. Para realizar esto, es necesario sumar a la Fecha de Préstamo el número de días de la columna D. La fórmula será: =C3+D3

Letras por Cobrar Nro. Monto Fecha de préstamo Días Préstamo Vencimiento

10101 2100 03-Ene 15 18-Ene

10102 3210 03-Ene 20 23-Ene

10103 5620 03-Ene 14 17-Ene

10104 4510 04-Ene 10 14-Ene

10105 8520 05-Ene 10 15-Ene Nota: en el caso de que la fórmula creada no muestra como resultado una fecha, es necesario aplicar un formato de fecha.


Módulo Excel | 16

Realizando otro ejemplo observamos que se desea incrementar a la Fecha de Préstamos con semanas, entonces la fórmula para el vencimiento será: =C3 + (D3*7) Al realizar ésta fórmula se multiplica al valor que se encuentra en la celda D3 el valor 7 para poder incrementar semanas a la Fecha de Préstamo.

Letras por Cobrar Nro. Monto Fecha de préstamo Semanas Vencimiento

10101 2100 03-Ene 5 07-Feb

10102 3210 03-Ene 2 17-Ene

10103 5620 03-Ene 5 10-Ene

10104 4510 04-Ene 2 25-Ene

10105 8520 05-Ene 1 05-Ene

10106 1450 06-Ene 3 06-Ene Realice una tabla similar a la de los ejemplos anteriores sustituyendo el campo Semanas con el campo Meses y utilice la fórmula que usted crea conveniente para agregar a la Fecha de Préstamo los días correspondientes.

Letras por Cobrar Nro. Monto Fecha de préstamo Meses Vencimiento

10101 2100 03-Ene 5 01-Jun

10102 3210 03-Ene 2 03-Mar

10103 5620 03-Ene 5 01-Jun

10104 4510 04-Ene 2 04-Mar

10105 8520 05-Ene 1 04-Feb

10106 1450 06-Ene 3 05-Abr

10107 3650 06-Ene 8 02-Sep

10108 5260 06-Ene 4 05-May 8.1.1 Función Fecha.Mes (Fecha_inicial;meses) Esta función nos permite sumar meses a una fecha inicial. Entonces la fórmula en la hoja será: =Fecha.mes(C3;D3) Realizar la tabla correspondiente con dicha función utilizando como base la tabla anterior y aumentando la columna Vencimiento Función.


Módulo Excel | 17

8.1.2 Función Fin.Mes (Fecha_inicial;meses) Esta función nos permite saber el ultimo d del mes siguiente indicado con el argumentos Meses a una fecha inicial. Entonces la formula en la hoja será: Caso 1 En el siguiente cuadro tenemos la fecha inicial y necesitamos la fecha de vencimiento a fin de mes después de n meses. =fin.mes(A4;B4)

Fecha de Vencimiento a Fin de Mes Fecha Inicial Meses Fecha de Vencimiento

16/03/2008 1 30/04/2008

17/03/2008 2 31/05/2008

18/03/2008 5 31/08/2008 Observando la tabla anterior se puede determinar que el resultado de la función utilizada es la fecha de fin de mes de vencimiento. Caso 2 En este caso se desea obtener las Fechas de Pago de las cuotas a fin de mes de acuerdo a la fecha de contrato. En el ejemplo la fecha de contrato es 4 de septiembre de 2008, por lo tanto las fechas de pagos serán: 31 de octubre, 30 de noviembre, 31 de diciembre, 31 de enero, etc. =fin.mes($B$4;A6)

Letras por Cobrar Nro. Monto Fecha de préstamo Meses Vencimiento Vencimiento Función

10101 2100 03-Ene 5 01-Jun 03-Jun

10102 3210 03-Ene 2 03-Mar 03-Mar

10103 5620 03-Ene 5 01-Jun 03-Jun

10104 4510 04-Ene 2 04-Mar 04-Mar

10105 8520 05-Ene 1 04-Feb 05-Feb

10106 1450 06-Ene 3 05-Abr 06-Abr

10107 3650 06-Ene 8 02-Sep 06-Sep

10108 5260 06-Ene 4 05-May 06-May


Módulo Excel | 18

Fecha Contrato 04/09/2008

CUOTAS FECHA PAGO

1 31/10/2008

2 30/11/2008

3 31/12/2008

4 31/01/2009

5 28/02/2009

6 31/03/2009 8.2 Restar dos fechas Excel permite restar celdas que contengan fechas, de esa forma se puede calcular el intervalo de días entre dos fechas. En el siguiente ejemplo se pide calcular los días de atraso para cada letra. Observe que en la celda F2 se tiene una fecha, entonces para calcular los días de atraso debemos restar la fecha de pago de la fecha de la celda F2. La función a utilizar será: =F1-E3

Letras por Cobrar 30-Ene

Nro. Monto Fecha de préstamo Días Préstamo Vencimiento Días de Atraso

10101 2100 03-Ene 15 18-Ene 12

10102 3210 03-Ene 20 23-Ene 7

10103 5620 03-Ene 14 17-Ene 13

10104 4510 04-Ene 15 19-Ene 11

10105 8520 05-Ene 12 17-Ene 13

10106 1450 06-Ene 19 25-Ene 5

10107 5260 06-Ene 12 18-Ene 12 8.2.1 Funciones Asociadas a Fechas Existen algunas funciones que el Excel provee para trabajar con fechas las cuales mencionaremos a continuación:

Hoy Ahora Dia Mes Año Dias360 Diasem Hora


Módulo Excel | 19

Practica 3.1

En un libro nuevo en la hoja1 Cambiar el nombre de la hoja por FUNCIONES DE FECHA, cambiar el color de

etiqueta al de su preferencia. Pruebe las funciones de fecha HOY (), AHORA (), DIA (), HORA (), DIA360 (),

DIASEM (), AÑO (), MES (). Guarde el archivo con el nombre de FUNCIONES DE FECHA, en la carpeta de su

curso. 8.2.2 Aplicaciones de las funciones de fecha Caso1. Calcular la Edad Una de las aplicaciones de las funciones podría ser la de calcular la edad de una persona. En la hoja se pide calcular la edad de los pacientes. Lo primero que debemos hacer es calcular el número de días de la fecha actual a la fecha de nacimiento. La función necesaria se deberá escribir así:

BUENA SALUD PACIENTES

Nombre Fecha de nacimiento Edad

Doris Nelly 30/03/1972 Andrea Marissa 23/06/1999 Diana Paola 10/03/2006 Andres Santiago 25/07/1978

=HOY() – B4 : tomando en cuenta que en B4 se encuentra el valor de la fecha de nacimiento. Esto dará como resultado días, los cuales deber ser convertidos a años, esto dividiendo entre 365 (días de un año). =(HOY()-B4) /365

Finalmente debemos quedarnos con la parte entera de este resultado: =ENTERO((HOY() –B4) /365) Otra forma de calcular la edad podría ser a través de la función Año. Por ejemplo la siguiente fórmula puede ser considerada por usted: =AÑO(HOY())-AÑO(B4) Caso 2 Calcular el primer día del mes de una fecha Si se tiene una fecha, podrá mostrar el primer día del mes correspondiente a esa fecha. Por ejemplo, para el 15 de febrero se mostrará 1 de febrero. En la celda B11 de la misma hoja, escriba: =FECHA(AÑO(A11); MES(A11);1)


Módulo Excel | 20

Se escribe 1 porque se desea mostrar el primer día de dicho mes. También se puede calcular el primer dia del mes siguiente de una fecha, es decir para el 15 de febrero se mostrará 1-marzo, para el 30-marzo se mostrará 1-abril, etc. En la celda C11 de la misma hoja, escriba: =FECHA(AÑO(A11);MES(A11)+1;1) Otras aplicaciones con funciones de fecha Por ejemplo en la siguiente hoja se pide calcular el ESTADO:”Refrigerar” para los productos que ingresaron antes de la quincena de Noviembre y “Almacén Tránsito” para los que ingresaron después de la quincena. Así la fórmula será =SI(DIA(B3)<15;”REFRIGERAR”;”ALMACÉN TRÁNSITO”) Además en la celda E3 podrá emplear fórmulas como: =SI(MES(D3)<7;”SEMESTRE1”;”SEMESTRE2”) Aquí la función identifica se corresponde al primer o segundo semestre del año.

Producto Ingreso Estado Pedido Semestre

A005 10-Nov

14-Jun A006 18-Nov

29-Jun

A007 12-Nov

18-Sep A008 16-Nov

02-Abr

A009 18-Nov

01-Oct A010 20-Nov

15-Mar

A011 10-Nov

14-Ago A012 26-Nov

28-Sep

A013 28-Nov

10-Mar

8.2.3 Función DIAS.LAB Devuelve el número de días laborables entre una fecha inicial y otra fecha final. Los días laborables no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento festivos. Al utilizar DIAS.LAB se puede calcular el incremento de los beneficios acumulados de los empleados basándose en el número de días trabajados durante un período específico. Si esta función no está disponible devuelve el error #NOMBRE?, se debería instalar algunos complementos al Excel.


Módulo Excel | 21

Sintaxis Días.lab(fecha_inicial; fecha_final;feriados) Donde: Fecha_inicial: Es una fecha que representa la fecha inicial. Fecha_final: Es una fecha que representa la fecha final.

Feriados: es una rango opcional de una o varias fechas que deven excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que contengan las fechas.

Ejemplo: Para hallar los días laborados durante el mes de septiembre se ingresa de la siguiente manera la función: =DIAS.LAB(A4;B4)

Pago por comisiones por días laborados

Fecha Ingreso Fecha Término Días Laborables

01/09/2004 30/09/2004 22

Si se dieran feriados durante las dos fechas estos pueden ser considerados en un rango y la expresión que resuelve los días laborados sería el siguiente:

=DIAS.LAB(A4;B4;C4:C7)

Fecha Ingreso Fecha Término Feriados Días Laborables

38231 38260 38233 18

38245

38254

38257

9. Funciones de Texto Así como también Excel manipula y define fórmulas con números, se tiene un conjunto de herramientas y opciones que permiten manipular texto y crear fórmulas con ellos. 9.1.1 Largo(celda) Nos devuelve como resultado la longitud de una celda. Cada carácter, incluido los espaciones en blanco, es considerado en esta función. 9.1.2 Mayusc(celda) Devuelve el texto de la celda en mayúsculas.


Módulo Excel | 22

9.1.3 Minusc(celda) Devuelve el texto de la celda en minúsculas 9.1.4 Izquierda (cadena;caracteres) Extrae caracteres desde el extremo izquierdo de una cadena de texto. 9.1.5 Derecha (cadena;caracteres) Extrae caracteres desde el extremo derecho de una cadena. 9.1.6 Extrae(cadena;posición_inicial;num_caracteres) Extrae n caracteres de una cadena de texto, empezando desde la posición inicial indicada en dicho argumento. 9.1.7 Hallar (texto_buscado;dentro_del_texto;[num_inicial]) Devuelve el numero de posición de la ocurrencia entre las cadenas de texto. Es decir busca el texto escrito en el primer argumento dentro de la cadena de texto escrita en el segundo argumento y muestra la posición en la que se encuentra. 9.1.8 Encontrar(texto_buscado;dentro_del_texto;[num_inicial]) A diferencia de la función Hallar, distingue las mayúsculas y minúsculas al momento de realizar la búsqueda. 9.2 Aplicaciones de las funciones de Texto Todas las fórmulas vistas son muy prácticas. Puede combinar estas funciones y podrá crear fórmulas más robustas y útiles. 9.2.1 Concatenar Textos Usando el símbolo & para concatenar (unir) textos. =texto1&texto2&texto3 Realice una tabla de datos en Excel con cinco elementos con los siguientes encabezados:

Ap Pat. Ap Mat. Sexo Area Apellidos Completos

9.2.2 Creación de códigos autogenerados Las funciones de texto nos permiten generar códigos basados en los datos de los usuarios. Así como en el Seguro social, donde se generan códigos basados en los apellidos y nombres de los afiliados (por ejemplo se toma el 4to carácter del apellidos, el 4to caracter del nombre, etc); o cualquier otra validación que se requiera. En nuestro cas, vamos a generar cógigos en la hoja de datos de acuerdo a los siguientes requerimientos:


Módulo Excel | 23

La inicial del área + 00 + Nro + Guión + 3 caracteres del nombre + 3 últimos caracteres del apellido paterno. En este caso la fórmual será algo larrga, por eso es preferible descomponerla para luego generar la fórmula final. Por ejemplo: =IZQUIERDA (f3;1) primer caracter del área “00” dos ceros A3 celda del Nro. “-“ guión IZQUIERDA(B3;2) 2 caracteres del nombre DERECHA(C3;3) 3 últimos caracteres del apellido paterno Entonces, sólo debemos concatenar estas expresiones. La fórmula nos dará como resultados:

Nro. Nombres Ap. Paterno Area Autogenerado

1 Doris Nelly Mayuri Ventas V001-Douri

2 Juan Carlos Castro Soporte S002-Jutro

3 Andrea Marissa Vega Ventas V003-Anega

4 Paola Danitza Díaz Ventas V004-Paíaz

5 Carlos Santiago Rodriguez Ventas V005-Cauez

6 Elsy Graciela Herrera Soporte S006-Elera

9.2.3 Texto en Columnas Una cadena de textos se puede convertir en columnas, así por ejemplo, en la hoja columna correspondiente a Nombres puede separarse en dos (una que contenga el primer nombre y otra que contenga el segundo).

Seleccione el rango desde B3:B8 Elija la opción Texto en Columnas del menú Datos El paso 1 del asistente le pedirá la confirmación de que sus datos son delimitados

(separados por algún caracter) o de ancho fijo (longitud fija para sus campos). Elija Delimitados y haga clic en Siguiente En el paso 2 debe elegir el separador de campos. Debe notar que en la parte

inferior Vista Previa. Elija como separador el espacio en blanco y haga clic en Terminar.


Módulo Excel | 24

Nro. Nombres Autogenerado 1er Nombre 2do Nombre

1 Doris Nelly V001-Douri Doris Nelly

2 Juan Carlos S002-Jutro Juan Carlos

3 Andrea Marissa V003-Anega Andrea Marissa

4 Paola Danitza V004-Paíaz Paola Danitza

5 Carlos Santiago V005-Cauez Carlos Santiago

6 Elsy Graciela S006-Elera Elsy Graciela

9.2.4 Práctica Texto1

1. Abra el archivo Lista de clientes.xls de la carpeta de su curso

CD LUZ

Relación de Clientes

Medidor Cliente Zona Categoría Fecha Autogenerado

S-10001 Vargas Tenorio, Luis 1 A 10-ago-1995

S-10002 Díaz Guerra, Alberto 2 B 15-ago-1995

S-10003 Torres Jimenez, Alfredo 2 B 17-ago-1995

S-10004 Osorio Sanchez, Miguel 3 B 04-sep-1995

S-10005 Zuloaga García, Pedro 1 A 15-sep-1995

S-10006 Lopez Lopez, Arturo 2 B 25-sep-1995

S-10007 Pereyra Lu, Hans 2 A 04-oct-1995

S-10008 Paricahua Ramirez, Luis 2 A 12-oct-1995

S-10009 Guerra Alvarez, Alberto 3 B 14-oct-1995

S-10010 Galvez Hinostroza, Miguel 3 B 12-nov-1995

2. Obtenga un código autogenerado con los siguientes requerimientos:

3 letras del apellido Paterno + 2 letras del apellido Materno + Guión + Categoría + Zona + Guión + 4 últimos caracteres del medido + Guión + 2 últimos dígitos del año

3. Abra el archivo Correo Generado.xls de la carpeta de su curso.

Nombre Apellido Fecha Nac Servidor Email

Katty Mendoza 28/12/1975 Hotmail

Graciela Herrera 03/03/1980 LatinMail

Zulema Camaque 03/02/1979 Terra

Gina Valladares 01/04/1975 Hotmail

Flor Moreano 05/10/1981 Uni

Miluska Vera 02/01/1985 LatinMail

Maritza Alvarez 10/10/1983 LatinMail

Natalia Flores 02/02/1984 Uni

4. Se pide obtener el código de acuerdo a lo siguiente:

Primera letra del nombre en minúsculas + apellido paterno en minúsculas+ 2 últimos dígitos de año+ símbolo @ + servidor + .com


Módulo Excel | 25

5. Abra el archivo Autogenerado.xls

AUTOGENERADO APELLIDOS Y NOMBRES FNAC DNI CONDICIÓN

RAMSEY CALLE, Paul William 01/01/1976 16674980 C

WOLL SAVASTANO, Hans Alexander 01/02/1970 23568987 P

SORIANO CASTILLO, Diego Martín 12/12/1972 23648214 P

AMES VILCHES, Jonathan Paul 23/10/1971 78921455 P

MALLA ALCALDE, Javier Lowis 22/05/1969 12354651 P

MITRI GIBA, Kamel Jorge 23/06/1969 89565223 C

BENTIN DE AMERO, Antonio Martín 12/11/1966 54545453 C

BATTIFORA DE NORIEGA, Eduardo Alfr 11/03/1968 26546635 C

GODOY URQUEAGA, Ximena 26/07/1970 32656654 C

HOYLE CROSBY, Manuel 24/07/1978 54546566 P

MIRO-QUESADA BOJANOVICH, Carolina 23/10/1971 45496464 P

TABOADA SAMANAMUD, Carmen Lourdes 25/08/1970 23524545 P

PINTO CORRALES, José Antonio 14/11/1972 98457832 C

PALACIOS RAMIREZ, Paola Bertha 22/05/1969 57785545 C

6. Obtener el AUTOGENERADO Condición + 2 primeros dígitos de D.Identidad + 2 primeros dígitos del nombre + “-“ + 2 primeros dígitos del apellido paterno + 2 últimos dígitos de la fecha de ingreso.

10. Funciones de Información 10.1 Función ES Dentro de esta función encontraremos que existen 9 funciones relacionadas donde es importante conocer las diferencias entre ellas. 10.1.1 ESBLANCO (celda) Devuelve el valor VERDADERO si se refiera a una celda vacía. 10.1.2 ESERR (celda) Devuelve el valor VERDADERO si se refiere a cualquier error con excepción de #N/A 10.1.3 ESERROR (celda) Devuelve el valor VERDADERO si se refiere a uno de los valores de error mencionados anteriormente en este módulo. 10.1.4 ESLOGICO (celda) Devuelve el valor VERDADERO si se refiere a un valor lógico. 10.1.5 ESNOD (celda) Devuelve el valor VERDADERO si se refiere al valor de error #N/A.


Módulo Excel | 26

10.1.6 ESNOTEXTO (celda) Devuelve el valor VERDADERO si se refiere a cualquier elemento que no sea texto. 10.1.7 ESNUMERO (celda) Devuelve el valor VERDADERO si se refiere a un número. 10.1.8 ESREF (celda) Devuelve el valor VERDADERO si se refiere a una referencia 10.1.9 ESTEXTO (celda) Devuelve el valor VERDADERO si se refiere a un texto. 10.2 Otras funciones de información 10.2.1 ES.PAR (numero) Devuelve VERDADERO si el número es par y FALSO si el número es impar. 10.2.2 ES.IMPAR (numero) Devuelve el valor VERDADERO si el número es impar y FALSO si no lo es. 10.2.3 CELDA (tipo;ref) Devuelve información acerca del formato, ubicación o contenido de la celda del extremo superior izquierda de una referencia. Tipo: es un valor de texto que especifica el tipo de información que se desea obtener acerca de la celda. La siguiente lista muestra los posibles calores de tipo_de_info y los correspondientes resultados: “ANCHO”: el ancho de la columna redondeado a un entero. “ARCHIVO”: el nombre del archivo incluyendo la ruta de acceso que contiene referencia al formato de texto. “COLOR”: 1 si la celda tiene formato de color para los calores negativos; en caso contrario devuelve 0. “COLUMNA”: El número de columna de la celda del argumento ref. “CONTENIDO”: Valor de la celda superior izquierda de la referencia, no es una fórmula. “DIRECCION”: La referencia, en forma de texto “FILA”: El numero de fila de la celda. “FORMATO”: Un valor de texto correspondiente al formato numérico de la celda. “PARENTESIS”: 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores, de lo contrario, devuelve 0. “PREFIJO”: Un valor de texto que corresponde al “prefijo rótulo” de la celda. “PROTEGER”: 0 si la celda no está bloqueada y 1 si la celda está bloqueada. “TIPO”: Un valor de texto que corresponde al tipo de datos de la celda.


Módulo Excel | 27

13. Funciones lógicas Estudio Especial

Existen principalmente 3 funciones lógicas: Y Para comparar si el primer valor Y el segundo valor es verdadero 1>0 Y 2>0 en Excel se coloca: Y(1>0;2>0) =VERDADERO 1>0 Y 2<0 en Excel se coloca: Y(1>0;2<0) =FALSO O Devuelve verdadero si el primer valor O el segundo valor es verdadero 1>0 Y 2>0 en Excel se coloca: O(1>0;2>0) =VERDADERO 1>0 Y 2<0 en Excel se coloca: O(1>0;2<0) =VERDADERO NO Devuelve el valor contrario al dado. Trabaja sólo con los valores lógicos de 0 y 1 NO (1) = 0 ó FALSO NO (0) =1 ó VERDADERO

11. Comparación de alternativas Cuando se está trabajando con datos tal vez puede preguntarse ¿Qué pasaría si ….? En Excel puede hacer estas preguntas, para comparar entre alternativas o para tomar decisiones. Por ejemplo se trabaja en una libreta de calificaciones y al ingresar la nota, se desea saber si pasa o no de año

Nota > 15 SI: Pasa de año NO: Pierde el año Si nota>15 Entonces Pasa de año Caso Contrario Pierde el año En Excel si suponemos que en la celda C2 está la nota, en una función se representa: =SI(C2>15, “Pasa de año”,”Pierde el año”)

11.1 Función SI anidada

En el caso de que el resultado Si o el resultado NO trae consigo otras preguntas, debe ocurrir un anidamiento de SI. Por ejm. Una alumna tiene al final del año tres posibilidades:

Si nota > =16 entonces “Pasa el año” Si nota >=12 y <16 entonces “Supletorio” Si nota <12 entonces “Pierde el año”

Entonces

Caso contrario

Condición


Módulo Excel | 28

11.2 Funciones de Búsqueda

En ocasiones puede necesitar buscar en una Base de Datos los datos referentes a cierto dato y colocarlos en otras celdas, para lo que se utiliza la función BUSCARV Por ejem. en la Base de Datos

A B C 1 Código Producto Precio 2 Co1 Sillas 30000 3 Co2 Mesas 50000

En otra celda deseamos obtener el nombre del producto automáticamente al colocar el código co2, la función a utilizar es BUSCARV, en donde los parámetros significan: BUSCARV ( dato a buscar, rango donde se buscará, el dato de que columna se sacará) BUSCAR (co2, A2:C3, 2) En la celda en la que se está ubicado aparecerá el dato “Mesas” Práctica Funciones

Abra el archivo Practica 4.1 Funciones En la primera hoja llamada Funciones Trigonométricas realice un cuadro para

obtener las funciones SENO, COSENO, TANGENTE para los ángulos comprendidos entre 0 y 90 grados con un incremento de 5 grados.

Para la hoja funciones de texto, escriba de forma separada los dos nombres, los dos apellidos para 10 alumnas y una todo el nombre con una función, luego obtenga el número de caracteres que tienen nombres y apellidos. Posteriormente utilizando alguna función, convierta su nombre completo a mayúsculas

En la hoja Fechas debe introducir la fecha de su nacimiento, obtener la fecha actual y con algunas funciones hallar el número de días vividos, el número de día de la semana en que nació.

En la hoja fechas, con la función SI debe traducir el número de día de la semana al nombre del día. Por ejem Si el número es 2 el día debe ser Lunes.

Grabe el archivo en la carpeta que corresponde al curso de Excel. TRABAJO FINAL

Haga en un nuevo libro una “Factura de Ventas” En una hoja llamada Productos, realice una lista de artículos de un almacén:

Código, Descripción, Valor Unitario En otra hoja llamada Clientes, realice una lista que contenga: Código, Nombre

Cliente, Dirección, Teléfono, Tipo de Cliente (A ó B) En una hoja llamada Factura haga una factura completa, que contenga un

encabezado: Nombre de la empresa, Logotipo de la empresa, Fecha, Factura No. El encabezado también debe tener una celda con el código del cliente y según esta

debe aparecer el nombre, dirección y tipo de Cliente El detalle de venta de 20 productos: Código, Descripción, Cantidad, Precio unitario,

Precio total. Al final obtenga el subtotal de la factura, IVA , Descuento, Recargo de Transporte,

Total El Descuento será de 10% para clientes A


Módulo Excel | 29

El Descuento será de 7% para clientes B El Impuesto será de 3% para todos los clientes Total= Subtotal+IVA-Descuento+Recargo Formatee las celdas según el contenido de cada una de ellas. Coloque bordes, sombreado Grabe con el nombre “Factura de Ventas”

Señale lo correcto haciendo un círculo junto a la pregunta: ¿Si una celda tiene formato monetario, se puede ponerle en formato fecha? F V ¿Puede cambiar ancho y alto de una celda sin utilizar menús? F V ¿Los argumentos son los datos de los rangos? F V ¿Puede desaparecer filas, columnas u hojas sin que se borren?

F V ¿Qué es formatear datos? ______________________________________________________________________________________________________________________________________________________________________________________________________________________. ¿Qué es un libro de trabajo? ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________. ¿Coloque 3 funciones y explique para que sirven? 1.____________________________________________________________________________________________________________________________________________________________________________________________________________________. 2.____________________________________________________________________________________________________________________________________________________________________________________________________________________. 3.____________________________________________________________________________________________________________________________________________________________________________________________________________________.


Módulo Excel | 30

0

50

100

1er

trim.

2do

trim.

3er

trim.

4to

trim.

12. INTRODUCCION Las hojas de cálculo como lo es Excel, permiten organizar y realizar cálculos de diversa índole, con datos ubicados en celdas y relacionados por fórmulas, que son recalculadas automáticamente al cambiar algún valor de la fórmula. Estas hojas de cálculo se aplican en una variedad de campos, pero no solamente se utilizan para fórmulas simples o para ordenar datos como si la hoja de cálculo fuese una gran tabla. Tienen otras utilidades como: GGGrrráááfffiiicccooosss

Las relaciones entre números son más fáciles de entender mediante gráficos, cuando se cambian los valores que representa el gráfico, este se actualiza automáticamente. Los gráficos son de amplia difusión, especialmente para estadísticas. En las elecciones se puede apreciar que la información de los candidatos se expresa más fácilmente en gráficos. No solamente se pueden realizar en barras, sino que pueden ser horizontales, verticales, circulares, por áreas, anillos, líneas, en 2 ó 3 dimensiones. Los gráficos pueden personalizarse según la necesidad, colocando los colores, tipos de letras para títulos, fondos, líneas divisorias, leyenda que sean necesarios para un gran impacto en el dibujo. BBBAAASSSEEESSS DDDEEE DDDAAATTTOOOSSS

En Excel también es muy frecuente el ordenar y trabajar con listas de datos. Se llama Base de Datos a una lista completa de datos organizados en columnas y filas. Cada fila posee los datos completos de una entidad, esa fila se llama Registro, Cada tipo de información de la entidad se llama Campo. Al tener una gran cantidad de datos puede necesitarse ordenar de alguna manera esos datos, Excel ayuda a que rápidamente se vea o se oculte solo los datos necesitados y ordenados de alguna manera. Las listas pueden ser por ejem: una guía telefónica, listas de clientes, lista de empleados de la empresa, lista de productos de una empresa, rutas de viajes de aviones, etc. MMMAAACCCRRROOOSSS

Algunas tareas realizadas en Excel pueden resultar repetitivas, por lo que en lugar de ejecutar la misma secuencia de tareas una y otra vez, se puede crear una macro para que al aplastar ciertas teclas o escoger cierto menú, se realice automáticamente esa tarea. La macro es una secuencia de acciones que se graban para luego solamente ejecutar la macro y se hará toda la secuencia que se grabó. Al grabar la macro la secuencia de acciones se transforma en instrucciones escritas en un lenguaje llamado Visual Basic. No se necesita conocer este lenguaje porque al grabar la macro Excel traduce automáticamente al lenguaje de programación. Si desea alterar directamente desde las instrucciones en Visual Basic necesita conocer el funcionamiento e instrucciones de este lenguaje.


Módulo Excel | 31

EJERCICIO 4 Escriba: Los gráficos se utilizan frecuentemente porque________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________. Cuatro ejemplos de tipos de gráficos que haya visto ________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________. Cuatro ejemplos de utilización de los gráficos _____________________________________________________. _____________________________________________________. _____________________________________________________. _____________________________________________________. Defina Una base de datos es ________________________________________________________________________________________________________________________________________________________________________________________________________________________. Los campos son ________________________________________________________________________________________________________________________________________________________________________________________________________________________. Los registros son ________________________________________________________________________________________________________________________________________________________________________________________________________________________. Realice un ejemplo de Base de Datos:

Complete: Una macro sirve para ________________________________________________________________________________________________________________________________________________________________________________________________________________________. El lenguaje de programación al que se traducen las macros se llama ________________________________________________________________________________________________________


Módulo Excel | 32

________________________________________________________________________________________________________________. 12.1 Gráficos

Se pueden mostrar los resultados en diversas formas, una de ellas es en forma gráfica. Al presentar los datos en forma gráfica nos permite compararlos, observar los cambios, verlos en forma reducida y conjunta es decir los datos pueden ser interpretados más fácilmente. Excel ofrece varios tipos de gráficos, como áreas, barra, columnas, líneas, circular, anillos, radar, dispersión x-y, piramidal, cotizaciones y en tres dimensiones. Se pueden crear gráficos de dos formas: en la misma hoja en la que están los datos o en una hoja aparte en el mismo libro de trabajo. Cuando se crea un gráfico en la misma hoja, se puede ver los datos y el gráfico al mismo tiempo, este gráfico se llama Gráfico incrustado, un gráfico en otra hoja se llama Hoja de gráfico.

12.1.1 Asistente para Gráficos El Asistente para gráficos es una serie de cuadros de diálogo que permiten fácilmente la creación de gráficos a partir de datos. Para ejecutar dichos asistente deberá seguir los siguientes pasos: Seleccione los datos en la hoja de cálculo a partir de los cuales generará los figuras. Haga clic en el botón Asistente para Gráficos. Suponga que tiene los datos de "Venta de Computadoras" y desea representarlos gráficamente: Seleccione los datos comprendidos en el rango B4:D6 Presione el botón Asistente para Gráficos Paso 1 de 4: Tipo de Gráfico: se visualizará la primer ventana de diálogo del Asistente. Seleccione en el cuadro correspondiente el Tipo de Gráfico Estándar (Columnas), haciendo


Módulo Excel | 33

clic sobre éste y escoja Subtipo de gráfico deseado y presione el botón Siguiente para continuar con el paso 2. Paso 2 de 4: Datos de Origen: se visualizará la segunda ventana, seleccione Serie en: la opción que corresponde a Columnas ya que nuestros datos se encuentran ordenados por columnas. Presione el botón Siguiente para continuar con el paso 3. Paso 3 de 4: Opciones de gráfico: se visualizará la tercer ventana, escriba en título del gráfico: el texto Venta de Computadoras, en Eje de categorías (x) Mes y en Eje de valores (Y) Unidades. Ingresar a las diversas opciones que ofrece éste cuadro de diálogo como son: Eje, Líneas de división, Leyenda, Rótulos de datos, Tabla de datos y por usted misma encontrar la utilidad que tiene cada una en la creación de gráficos, a continuación haga clic en el botón siguiente para continuar con el cuarto paso.


Módulo Excel | 34

Paso 4 de 4: Ubicación del gráfico: en la ventana Asistente para gráficos paso 4 de 4 aparece dónde situar el gráfico: en la misma hoja o en una hoja nueva, escoja la primera si desea que el gráfico sea considerada Como objeto en la hoja o escoja la opción en una hoja nueva si desea que el gráfico cree una Hoja Gráfica con el nombre predeterminado de Gráfico 1. Haga clic en Finalizar para cerrar el Asistente. Finalmente quedará el gráfico de la siguiente manera si usted escogió la opción Como objeto en.

12.1.2 Modificación de los Gráficos Si halla que la información del gráfico es demasiada, escasa o equivocada, puede cambiarlos. Antes de hacer cualquier cambio necesita activarlo con un clic y aparecerá una barra de herramientas y las órdenes para modificar el gráfico están disponibles al aparecer un nuevo menú Gráfico o al dar un clic con el botón derecho sobre un espacio en blanco del gráfico.


Módulo Excel | 35

Tota

l in

gre

sos

Coste

merc

an

cía

s v

en

did

as

Ben

eficio

bru

to

Gasto

s

Ingre

sos d

e e

xplo

tació

n

$ 0

$ 50,000

$ 100,000

$ 150,000

$ 200,000

$ 250,000

$ 300,000

$ 350,000

$ 400,000

$ 450,000 T

rin

1

Tri

m 2

Tri

m 3

Tri

m 4

Tota

les 1

99

6

2002

Para cambiar los datos del gráfico debe seleccionar la opción datos de origen, presionar del botón rango de datos, borrar la elección actual y seleccionar la nueva, para regresar a la edición del gráfico presione nuevamente el botón rango de datos. Para cambiar el tipo de gráfico, seleccione la opción Tipo de gráfico y escoja el nuevo tipo de dibujo. Si da un clic sobre cualquier sitio dentro del gráfico para seleccionar por ejemplo el título, los ejes de valores, las líneas divisorias y luego da doble clic sobre la selección aparecerán varias opciones para modificar el color, tipo de letra, tamaño, texto, etc. Práctica 5

Conteste: Un gráfico en la misma hoja en donde están los datos se llama _____________________ Un gráfico en una hoja diferente llama___________________________________ Abra el archivo Práctica 5 Gráficos desde la carpeta Prácticas de Excel Para todos los gráficos que realice debe cambiar los colores, tipo de letra. Busque una buena presentación del dibujo. En la hoja gastos haga un gráfico incrustado tipo columnas Para la hoja pasajes cree en una hoja de gráfico un dibujo de tipo barras 3D Para la hoja ventas haga un gráfico incrustado que exprese para cada persona cuanto vendió en cada zona. Ud. vea el gráfico, tamaño, tamaño de letra más conveniente, trate de que su gráfico se vea bien presentado. La leyenda debe estar presente Para la hoja meses haga un gráfico circular 3D incrustado En la hoja presupuesto cree un gráfico cilíndrico, el gráfico debe quedar en esta forma, los textos son a su voluntad 13. Bases de Datos En Excel es muy frecuente el utilizar listas de datos o bases de datos, estos se organizan con cabeceras de columnas que se llaman Campos y las filas son los registros. Ejem:

Clave Apellido Nombre Cargo 1020 Valdivieso Pedro Ingeniería 1875 Gonzales Estefanía Administración 1425 séller Samuel Diseño

Campos

Registro


Módulo Excel | 36

Aquí un registro contiene toda la información de una persona, mientras que cada columna o tipo de información es un campo Toda la lista completa de datos organizados en campos y registros se llama Base de Datos. Las bases de datos pueden ser listas de personas, lugares, productos, etc. Excel reconoce automáticamente que una lista de datos de cualquier tipo, es una Base de Datos siempre que está organizada en campos y automáticamente se activan las opciones del menú Datos que permiten trabajar con las listas.

13.1 Ordenar datos Algunas veces es necesario ordenar los registros de una lista de determinada manera, ejemplo: por NOMBRE. En los siguientes ejemplos se van a ordenar los registros por el campo NOMBRE en forma alfabética y ascendente. Seleccione cualquier celda de la tabla Elija Instrucción Ordenar del menú Datos. Se visualizará la ventana de diálogo Ordenar. Escoja el campo en el área Ordenar por y presione el círculo de selección correspondiente a Ascendente. Elija Sí en el área La lista tiene fila en encabezamiento, si aún no ha sido elegido. También se pueden ordenar directamente en forma ascendente o descenderte, con un solo criterio utilizando iconos de la barra de herramientas.


Módulo Excel | 37

13.2 Filtrado de una Lista

Una de las operaciones más comunes que se hacen con las bases de datos es la consulta. Cuando se efectúa una consulta, por lo general se está buscando registros que coincidan con algún valor o criterio. Existen varias formas de buscar la información, los filtros son una de ellas. El siguiente ejemplo muestra los pasos que se debe seguir si desea filtrar datos de una lista: Seleccione una celda cualquiera de una base de datos. Seleccione la orden Filtro del menú Datos y luego elija la opción Autofiltro. Como se puede observar, en cada una de las celdas del encabezado de la lista aparecen los botones de la flecha de filtro. Seleccione el botón de la flecha de filtro de la celda C1, que corresponde al campo EDAD y se despliega una ventana con una lista de criterios por escoger: Todas: Permite visualizar todos los registros de la lista de datos. Las diez más: visualiza ya sea las diez superiores o 10 inferiores Personalizar: Pueden verse todos los registros de la lista de datos con criterio


Módulo Excel | 38

Seleccione 28 en la lista de criterios, y quedará de la siguiente manera los registros filtrados. Importante: Para que aparezcan los demás datos que fueron filtrados anteriormente deberá escoger la opción Filtro del menú Datos y luego elegir Mostrar todo. 14. Automatización de Tareas Repetitivas Algunos ingresos de datos o tareas que realiza en Excel pueden ser repetitivos, en lugar se hacer la misma acción una y otra vez, se puede crear una MACRO, entonces en lugar de repetir la acción, solamente ejecuta la MACRO la cual hará sus tareas automáticamente. Una macro consta de una serie de instrucciones escritas en un lenguaje de programación llamado Visual Basic, para crear una macro no necesita comprender el lenguaje que se utiliza, sólo se necesita saber qué órdenes necesita que la macro haga. Se puede grabar las órdenes en secuencia y éstas serán traducidas al lenguaje Visual Basic. Si observa que ejecuta con frecuencia tareas como negrita, cursiva y un tamaño mayor de letra, puede ahorrar tiempo al automatizar estas tareas. Para crear una macro active la Grabadora de macros, luego debe ejecutar la secuencia de acciones que se desea grabar y luego desactive la grabadora de macros. Cuando se grabe la macro, las órdenes serán traducidas automáticamente al lenguaje Visual Basic. 14.1 Creación de Macros Antes de grabar una macro necesita planificar exactamente que desea que haga y en qué orden, después debe seleccionar Herramientas/Macro/Grabar Nueva Macro, luego se le pedirá un nombre que identifique a la macro que va a crear, una combinación de teclas CTRL+tecla, y una descripción de la nueva macro. Desde ese momento cada celda u opción que seleccione serán grabados bajo el nombre dado a la macro, los nombres de macro no deben incluir espacios o puntos. Una vez que termine de realizar las acciones que desea que grabe la macro, debe presionar del botón de Stop de la pequeña barra de herramientas que aparece sobremontada. Para ver la macro transformada al lenguaje de programación visual Basic seleccione: Herramientas/Macro/Editor de visual Basic 14.2 Ejecución de macros Cuando ejecuta una macro, ésta simplemente realiza todas las acciones que realizó mientras estaba grabando. Para ejecutarla puede seleccionar el nombre de la misma de una lista desde Herramientas/Macro/Macros ó puede presionar la combinación de teclas que la activarán, definidas al grabar la macro. La combinación de teclas siempre comenzará con la tecla CTRL.


Módulo Excel | 39

Práctica 8

En un documento nuevo que le llamará Practica 8 Macros haga: Realice 3 macros que le ayuden a ejecutar con rapidez 3 acciones que para usted

son repetitivas. A dos macros asígneles un botón de macro Cambie la configuración de uno de los botones con otro dibujo. Al otro botón asígnele un texto La tercera macro debe poseer una combinación de teclas para ejecutarla. Grabe el archivo en la carpeta que corresponde al curso de Excel.

Documents

trucos de busqueda