Modulo Excel

TEXTO ESCOLAR DE COMPUTACIÓN E INFORMÁTICA

4

TO

AÑO

II nf@rD@t@ educación SECUNDARIA

INTERFAZ DE EXCEL 2007

1

Las fichas, los grupos y los comandos: 2

1

4

3

6 11

5

7

8

9

10

Coloca el nombre de las partes de la ventana de Microsoft Excel 2007

1 2 3 4 5 6 7 8 9 10 11

01

INTERFAZ DE EXCEL 2007

La ficha (Tab) y la cinta (Ribbon)

1 Hacer clic en las fichas: Inicio, Insertar, etc.

Procedimiento

2 Visualizar la cinta de cada ficha.

Ubica los siguientes comando en las cintas de opciones: Ficha Diseño de página Orientación de página Filtro Inmovilizar Opciones para el cálculo

Las filas y columnas Además de las características anteriores, el número de filas ha crecido a 10.048.576 y el número de columnas a 16384. Puede escribir fórmulas más largas en la nueva barra de fórmulas de tamaño variable.

Investigue el número de celdas en las versiones anteriores de Microsoft Excel.

1997 FILAS COLUMNAS

02

2000

2003

BARRA DE HERRAMIENTAS

2

Barra de herramientas de acceso rápido

Se encuentra sobre la cinta de opciones cuando se inicia Excel 2007, en dicha barra, los comandos están siempre visibles y cercanos para acceso inmediato.

Procedimiento

1

Hacer clic con el botón secundario del mouse en cualquier zona de la cinta de opciones.

2

Luego hacer clic en Agregar a la barra de herramientas de acceso rápido.

Indique cuáles son los iconos que aparecen en la cinta de acceso rápido.

03

BARRA DE HERRAMIENTAS

Ficha de Barra de Herramientas Contextual

Cuando se selecciona un objeto, por ejemplo un gráfico se activa la herramienta contextual para gráficos. La ficha Diseño se usa para cambiar el tipo de gráfico o moverlo a otra ubicación.

La ficha Presentación se usa para cambiar los títulos y otros elementos de gráfico.

La ficha Formato para agregar relleno de color y cambiar el estilo de las líneas.

Cuando termine el gráfica, haga clic fuera del área del gráfico. Desparecerán las Herramientas de gráficos. Para volver a verlas, haga clic dentro del gráfico y se volverán a mostrar.

04

CINTAS DE OPCIONES

3

Las galerías Las galerías simplifican muchas operaciones, al presentar un conjunto de formatos y opciones que los usuarios pueden elegir y hacer clic y así conseguir los resultados deseados de una manera más profesional. Existen galerías para aplicar formatos a gráficos, para seleccionar el diseño de una página, o cambiar la apariencia de un WordArt. Existen 2 tipos de galerías: las incrustadas y desplegables.

Procedimiento

1 2

Haga clic en la ficha Diseño de página. Haga clic en el comando Márgenes y luego en Tamaño. Estos ejemplos de galerías despegables.

05

CINTAS DE OPCIONES

La ventana cuadro de dialogo Excel 2007 conserva la ventana cuadro de dialogo de la versión anterior, por ejemplo la venta de formato de celdas del grupo de comando fuente.

Procedimiento

1

Haga clic en la flecha en la parte inferior de un grupo para obtener más opciones si las necesita.

2 3

Haga clic en la flecha en el grupo Fuente. Se abrirá el cuadro de dialogo Formato de celdas.

Observa las imágenes y escribe a qué ventana de diálogo pertenece.

06

CINTAS DE OPCIONES

Métodos abreviados de teclado Otra forma de acceder a los comandos es mediante el teclado, si se presiona la tecla ALT. Se mostrara las teclas de acceso a cada comando.

El botón OFFICE En la esquina superior izquierda de la ventana se encuentra el botón Office. Ahí se encuentran algunos comandos que ha usado para abrir y guardar libros.

Procedimiento 1

En el botón de Microsoft Office para abrir este menú.

2

En el menú, hacer clic en Abrir un libro existente.

3

O bien, haga clic Operaciones de Excel, en la parte inferior del menú para configurar las opciones del programa.

1 2

Modificar y dar formato a datos Para modificar y dar formato a datos se realiza mediante los comandos de grupos de la ficha Inicio.

Procedimiento 1

En el botón de Microsoft Office para abrir este menú.

2

En el menú, hacer clic en Abrir un libro existente. O bien, haga clic Operaciones de Excel, en la parte inferior del menú para configurar la opciones del programa.

3

07

CINTAS DE OPCIONES

Minimizar la cinta de opciones No hay forma de eliminar o reemplazar la cinta de opciones por las barras de herramientas y los menús de versiones anteriores de Microsoft Office. No obstante, puede minimizar la cinta de opciones para que el área de trabajo sea mayor.

Procedimiento

1 Hacer clic en Personalizar barra de herramientas

de acceso rápido. la lista, hacer clic en Minimizar la cinta de 2 En opciones.

También se puede activar y desactivar la cinta de opciones con las teclas CTRL+F1

Atajos de teclado. Escribe que acciones realizan las combinaciones de teclas. ·CTRL+F: _____________________________________________________ ·CTRL+7: _____________________________________________________ ·CTRL+RePág (ó PageUp): _______________________________________ ·CTRL+AvPág (ó PageDown): ____________________________________ ·CTRL+1: _____________________________________________________ ·CTRL+E: _____________________________________________________ ·CTRL+B: ____________________________________________________ ·CTRL+G: ____________________________________________________ ·TECLA F2: ____________________________________________________

08

LIBROS Y HOJAS DE CÁLCULO

4

Con respecto al libro de Excel 2007, ha tenido algunas modificaciones como el formato de extensión, al guardar el libro se graba con la extensión XLSX, cada libro está formado por hojas ubicados en la parte inferior.

1

Recuerda

El nombre del archivo aparece en la barra de título situada en la parte superior de la ventana cuando se guarda el libro con su propio título. Fichas de hojas llamadas etiquetas de hoja en

2 la parte inferior de la ventana, identifican las hojas de libro.

Crear un nuevo libro 1 Hacer clic en el botón de Office.

Procedimiento

2 Hacer clic Nuevo. En la ventana Nuevo libro, haga clic en Libro en blanco.

09


Columnas, filas y celdas A continuación de los primeros 26 títulos de columna (A a Z), están los 26 títulos de columna siguientes (AA a AZ). Los títulos de columna continúan hasta la columna XDF, haciendo un total de 16.384 columnas.

Los títulos de columna se indican mediante

Procedimiento

1 letras.

Los títulos de fila se indican mediante

2 números.

1

2

A continuación de AZ, las parejas de letras comienzan de nuevo con las columnas BA a BZ, y así sucesivamente hasta hacer que las 16384 columnas tengan su correspondiente titulo alfabético, finalizando en XFD.

10


Cada una de las filas también tiene un título. Los títulos de las filas son números, de 1 a 1048.576.

La celda Es el área almacenamiento está definido por la intersección de una columna y una fila, cuando abre un libro nuevo, la primera celda es la celda activa. Tiene un contorno de color negro.

11

5

RANGO DE CELDAS

Rango de celdas Un rango es una colección de celdas. El rango se define usando la celda inicial y final del rango. Escriba en cada recuadro el nombre de los rangos sombreados. Ejemplo: Rango B2:B5 Rango D2:F2 Rango D4:F5

El cuadro de Relleno El cuadro de relleno es símbolo + que se muestra al ubicar el Mouse en la parte inferior derecha de la celda activa.

Cuadro de relleno

12

RANGO DE CELDAS

Copiar, generar Series y Generar Listas Una tabla está conformada por espacios rectangulares que se agrupan en filas y columnas. La intersección de una fila y una columna se denomina celda.

Procedimiento 1 Haga clic en la celda. el puntero al cuadro 2 Lleve de relleno. vea el símbolo + 3 Cuando realice un arrastre.

Lista Personalizada Se puede crear listas para rellenar con el cuadro de relleno, por ejemplo para crear una lista de distritos.

1 Haga clic en el botón Office y elija Opciones de

Procedimiento

Excel. 2 En la ficha Más frecuente, haga clic en el botón Modificar Listas personalizadas.

3 En el cuadro Entrada de listas escriba 10 distritos.

Selección de rango de forma exacta: En hojas de cálculo complejas, muy grandes o con celdas muy pequeñas es difícil seleccionar un conjunto de ellas “arrastrando” con el mouse. Podemos hacerlo seleccionando la primer celda y pulsar la tecla MAYUS y luego al seleccionar la última celda, se seleccionará todo el rango comprendido en ese rectángulo imaginario.

13

RANGO DE CELDAS

Copiar celdas El cuadro de relleno también permite copiar el contenido de celdas. 1. Abre una hoja de cálculo y escribe en la celda A1 la palabra “Matemáticas” 2. Ir a la celda A1, arrastra el puntero para copiar texto, hasta la fila A11. 3. Selecciona la celda A8 hasta la A11 y copia el contenido en la calumna B7.

Actividad

Responde las preguntas con verdadero (V) o Falso (F). En Excel 2007 es posible seleccionar rangos múltiples.

El uso del botón relleno es correcto el resultado.

14

(

)

(

)

Se puede copiar rangos múltiples con el comando CTRL + C.

(

Se puede copiar rangos múltiples con el comando CTRL + C.

(

)

)

OPCIONES DE PEGADO, SUMA, FORMATO Y COMPATIBILIDAD

6

Opciones de pegado Las opciones de pegado cambian la forma que tiene Excel para rellenar celdas. 2. Observa que al soltar el mouse, 1. Activa la Hoja2. En la celda A4, las celdas se copian y se muestra arrastra el puntero hasta la celda al final un icono o etiqueta (vea la A12. imagen) Haz clic sobre este símbolo y elige Rellenar serie.

3. Ahora, llevar el puntero a la celda F4 y realiza un arrastre hasta la celda F12. En las opciones de relleno, elige Copiar celdas con los días de la semana laboral.

4. Completa las tablas de la Hoja2 (columna de Código). Hoja3 (las flechas hasta el 15 de Julio y los Enfermeros hasta el Enfermero5) y la Hoja4(Salas). 5. Genera series (usando las opciones de pegado) para loas Hoja5 (columna de la Guía) y Hoja6 (columna Factura).

6. Ir a la Hoja y partir de la celda A12, escribe la tabla de la derecha.

15


Función Suma Se pide calcular el total de casos atendidos por el laboratorio1. 1. En la Hoja1, ir a la celda B11 2. Ubica el botón AUTOSUMA y haz clic en la Función SUMA.

3. Presiona Enter 4. Arrastra el cuadro de relleno de la celda B6 hasta la celda E6. 5. Realice el mismo procedimiento para todos los trimestres.

Aplicando formatos de estilos de celdas Los estilos permiten aplicar un formato a las celdas seleccionados, este formato incluye fuentes, bordes, sombreados, etc.

1. Ir a la Hoja1 y selecciona las celdas A3:B3 2. En la ficha Inicio, en estilos, hacer clic en el comando Estilos de celda. 3. Selecciona titulo2 4. Repita los pasos 1 a 3 para cada rango.

Guardar y abrir libros, este se guarda con todas las hojas que usted ha trabajado. Puede usar cualquiera de estos procedimientos: 1. Use el botón Office y elige la opción Guardar como... 2. El nombre del archivo será Servicios.

16


Compatibilidad con versiones anteriores El libro que acaba de guardar esta formato 2007 el cual no se puede abrir en versiones anteriores (XP o 2003 por ejemplo). En este se guardara el archivo con un formato que asegurase que se pueda abrir en versiones anteriores.

Procedimiento

1. Haz clic en el botón Office. 2. Elige el comando Guardar como: Selecciona Libro Excel 97-2003. 2. Escribe el nombre Servicios 2003 3. Guardar. 4. Cierra el libro.

1

2

17

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________ 1. Es una de las nuevas carácteristicas de Excel 2007 a) Las opciones de auto relleno b) El cuadro de relleno c) El cuadro de nombre d) La barra de herramientas de acceso rápido. 2. El número de fichas que presenta Excel al inicio es: a) 5 b) 6 c) 7 d) 8 e) 9 3. La cinta o ribbon de Office está formado por: a) Cajas de controles b) Opciones c) Grupo de comandos d) Barra de herramientas e) Caja de herramientas 4.¿ Cuál no corresponde a un tipo de galería? a) Desplegable y Estirables b) Incrustada y estirada c) Pegada y desplegable d) Incrustada y desplegable e) Desplegable y estirada. 5. ¿Para mover el cursor a la última fila de la hoja de cálculo se presenta la combinación? a) Ctrl+Inicio b) Ctrl+Fin c) Fin+Ctrl d) Fin+tecla direccional abajo e) Inicio+tecla direccional abajo

18

6. ¿Cuál es la combinación de tecla para desactivar la cinta o ribbon? a) Ctrl+Inicio b) Ctrl+F1 c) Fin+F1 d) Ctrl+F4 e) Alt

7. En la siguiente figura indicar el rango a) B8:B9 b) B8:G8 c) B8:G9 d)B9:G9 e)G8:G9

8. Cuando se arrastra el cuadro de relleno, al final de la celda aparece un control ¿Cómo se llama dicho control? a) B8:B9 b) B8:G8 c) B8:G9 d)B9:G9 e)G8:G9 9. La opción que muestra solo los días laborales de Lunes a Viernes en el arrastre es: a) Las opciones de auto relleno b) El cuadro de relleno c) El cuadro de nombre d) La barra de herramientas de acceso rápido.

10. La extensión de los libros de Excel 2007 es : a) XLS b) XLT c) XLST d) XLSX e) XSLIX

19

7

Configurando EXCEL 2007

Antes de iniciar con el programa de Excel es necesario definir el tipo de idioma para establecer el símbolo monetario, símbolos decimales, de hora y fecha,

Procedimiento 1 Ir al panel de control del sistema operativo. Ingresar a configuración 2 regional y de idioma.

3

Seleccionar el país que desea configurar.

Ingreso de datos Para ingresar datos numéricos, es recomendables usar el teclado numérico, importante notar que las flechas y los datos numéricos se ubican ala derecha mientras que los textos se ubican a la izquierda.

Formatos 1. Formato de Alineación El contenido de una celda se puede alinear en 9 posiciones, puedes girar y cambiar de orientación.

Procedimiento 1 Seleccionar la celda o rango de celdas. 2 Ir a la ficha inicio. 3

20

Aplicar la alineación centrada


Orientación Este formato permite girar el contenido en varios ángulos.

Procedimiento

1 Seleccionar el rango de datos. 2 Ir a la ficha Inicio. 3 Seleccionar la opción de orientación.

Combinar y centrar Permite centrar un rango de celdas y centrar su contenido .

Procedimiento 1 Seleccionar el rango de celdas. 2 Ir a la ficha Inicio. 3 Seleccionar la opción de combinar y centrar.

Ajuste de texto Permite distribuir el contenido de una celda en varias líneas..

Procedimiento 1 Seleccionar el rango el rango 2 Ir a la ficha Inicio. 3 Aplicar el botón de ajuste de texto.

21


Formato de número 1. Formato moneda Los formatos numéricos presentan una gran variedad de modelos como por ejemplo los formato moneda, contabilidad, científica, etc.

Procedimiento 1 Seleccionar el rango de datos. 2 Ir a la ficha Inicio, y seleccionar el tipo de formato numérico

2. Formato personalizado El usuario puede prepara su propio formato, para mejorar la presentación de los valores numéricos por ejemplo los cheques deben tener un relleno en los espacios por seguridad.

Procedimiento 1 Seleccionar el rango de datos. 2 Ir a la ficha Inicio. 3 Seleccionar mas formatos de número.. 4

En personalizada, escribir S/. **#,##0.00; S/.**(#,##0.00)

Formato condicional El formato condicional permites aplicar formatos de acuerdo a que se cumpla una condición. En esta versión se cuenta con 3 tipos de formato a usar: Barra de datos, escala de color y conjunto de iconos aunque usted puede definir su propio tipo. 1. Condicional Barra de datos

Procedimiento

1 Seleccionar el rango de datos. Ir a la ficha Inicio, luego en el grupo de comandos 2 estilos, seleccione Formato condicional. 3 Aplique un formato condicional de tipo Barra de datos.

Ejemplo:

22


Borrar el formato condicional

Procedimiento

1 Seleccionar el rango. 2 Hacer clic en botón Formato Condicional. 3 Elija la opción Borrar reglas. Borrar reglas de las celdas seleccionadas.

3. Condicional Conjunto de iconos

Formato de tabla En la versión 2007 de Excel, es posible aplicar un formato de tablas a su celdas. Excel convierte un rango a una tabla, al cual se aplica filtros, subtotales y otras herramientas para su manejo.

Procedimiento 1 Seleccionar alguna celda de tabla. 2 Ahora, en la ficha Insertar-Tabla. 3 Haga clic en Aceptar.

23

ejercicios paso a paso

Antes de iniciar Copia la carpeta Archivos sesion12 a mis documentos.

Preparando el programa 1. Configurar el idioma a español Perú. 1. Ir al panel de control de Windows, seleccionar la opción configuración regional y de idioma. 2. Elige el idioma español Perú. 2. Ingreso de datos Abre un libro en blanco e ingrese la siguiente información.

Formatos Combinar y centrar 1. Selecciona el rango A1:D1 2. Ir a la ficha, aplicar el botón combinar y centrar. Formato de fecha 1. Selecciona el rango de fechas B4B12. 2. Ir a la fecha inicio y selecciona el formato de fecha larga. Formato moneda 1. Selecciona el rango D4:D12. 2. En la ficha Inicio seleccionar el formato moneda. Formato personalizado de fechas [Azul]S/.*.#,##0.00;[Rojos]S/.*.(#,##0.00) 1. Seleccionar el rango B4:B12 2. En la ficha Inicio, seleccionar mas formatos de números. 3. Selecciona personalizar y en tipo escribir. Formato personalizado de fechas 1. Seleccionar el rango B4:B12 2. En la ficha Inicio, seleccionar mas formatos de números. 3. Selecciona personalizar y en tipo escribir. 4. Estilo de formato 1. Selecciona el rango A4:D12. 2. En la ficha Inicio, selecciona estilos. 3. Selecciona énfasis 20% 4. Guarda el archivo con el nombre Reporte Terminado. 5. Cierra el Archivo

24


Formato condicional Abre el archivo formato condicional 1. Ajuste de texto 1. Aplicar formato de ajuste de texto a las celdas: A4, D4, G4 y B6. 2. Seleccionar celda. 3. Haz clic en el botón ajuste de texto del grupo alineación. 2. Formato de barras de datos 1. Selecciona el rango B7:B10 2. En la ficha Inicio, grupo estilo, hacer clic en Formato condicional. 3. Seleccionar barra de datos. 4. Repite los pasos del 1 al 3 para los rangos E7:E11 y H7:H8. 5. Aplicar formatos de estilo según corresponda.

Observa la figura 3. Formato de conjuntos de íconos 1. Seleccionar rango H7:H10. 2. En la ficha Inicio, grupo estilo, hacer clic en Formato condicional. 3. Seleccionar conjuntos de iconos.

Guarda el archivo con el nombre formato condicional terminado y ciérralo.

4. Aplicar reglas a un formato condicional En este caso necesitamos crear un cuadrado de puntajes mayor o igual a 14 se vera con icono verde, si es mayor o igual de 11 será color amarillo, de los contrario se verá con el icono de color rojo. 1. Abre el archivo calificación. 2. Selecciona el rango B3:B17 3. Aplicar el formato condicional conjunto de iconos de datos. 4.Hacer clic en administrar reglas, luego hacer clic en editar reglas. 5. Guardar y cerrar archivo.

25


Formato y Fórmulas 1. Abre el archivo Formato y Fórmulas. 2. Completar con el arrastre del cuadro de relleno y con formato personalizado para el número y flecha de cierre.

1. Fórmulas básicas Monto En la Hoja1, debe calcular el Monto de cada trabajador. El monto es el producto de las Horas con el Pado Hora. En la celda I4 escriba: = G4*H4 Luego, lleva el puntero al cuadro de relleno de la celda I4 y copia la fórmula a las demás celdas Retención. La Retención equivale al 10% del monto. En la celda J4, escriba: =14*10%. Copia la fórmula a las demás filas. Neto El Neto se calcula restando la retención del monto. En la celda K4, escriba: =I4-J4 Copia la fórmula a las demás filas. Aplicar los formatos necesarios para obtener los siguientes:

26


2. Fórmulas en porcentajes 1.En la hoja2 se pide calcular el porcentaje de atención de cada sala. 2. En la celda D4, escriba: =C4/B4 3. Luego, arrastra esta fórmula a las demás salas, selecciona el rango de celdas D4:D8 y uso el botón. El resultado se muestra como la imagen: 4. Aplicar los formatos necesarios para obtener el siguiente cuadro.

3. Más Fórmulas Completa las tablas de acuerdo a las siguientes especificaciones: Exceso 1. En la Hoja3, calcule el Exceso, sabiendo que el peso máximo es 50 Kg. 2. Además, calcula el total de peso y el total de exceso. IGV 3. En la Hoja4, calcula el IGV (19% del monto) 4. Calcula también el Neto (Monto más IGV) 5. Calcula también el total facturado (Suma de los montos) y el Pago Sunat (suma del IGV). BONIFICACIÓN 6. En la Hoja5, calcula la Bonificación por hijo ( se paga 120 soles por cada hijo). 7. Además, calcula el Total de Bonif (Suma de Bonificación base y la bonif por hijo). Guarda el archivo con el nombre Formatos y Fórmulas.

27


Más formatos Abre el archivo formatos, estas Acciones se realizarán en la hoja Datos, la hoja resultados para verificar si los pasos están correctos. 1. Combinar y Centrar 1. Seleccionar A1:Q1. 2. Haz clic en el botón combinar y centrar. 3. Realiza los pasos anteriores para el rango A2:Q2. 4. Realiza los pasos anteriores para el rango A3:Q3. 5. Selecciona el rango B5:E5. 6. Combinar haciendo clic en el botón. 7. Ahora aplica un color de fondo azul claro. 8. Aplica un color de fuente blanco a las letras. 2. Uso del cuadro de Relleno 1. Arrastra el cuadro de relleno de la celda combinada B5, hasta la celda Q5. 2. Realiza los pasos anteriores del 5 a 8 al rango B19:E19. 3. Rellenar los meses 1. Seleccionar la celda A72. Realiza un arrastre del cuadro de relleno hasta generar los 12 meses del año. 4. Formato de Fuente 1. Aplicar fondo amarillo y letras negras al rango B6:E6. 2. Realiza un arrastre del cuadro de relleno, hasta Q6. 5.Copiar rangos 1. Selecciona el rango B6:E6. 2. Ahora presiona las teclas CTRL+C 3.Ubicar el cursor en la celda B20. 4. Finalmente presiona las teclas CTRL+V 6. Arrastre del cuadro Relleno 1. Selecciona el rango B20:E20. 2. Realiza un arrastre del cuadro de relleno hasta Q20. 3. Ver hoja resultados.

28


Fórmulas y Expresiones 1. Cálculo de la diferencia (CAJA1) 1. Selecciona la celda D7 2. Ingresa la fórmula =B7-C7 3. Ahora realiza una copia, arrastrando hacia la celda D18. 4. Calcular fórmula de la caja2 en la celda H7. 5. Calcular fórmula de la caja3 en la celda L7. 2. Cálculo del total General Proyectado 1. Selecciona la celda N7 2. Ingresa la fórmula =B7+F7+J7 3. Calcula el total ejecutado (Celda O7). 4. Calcula la diferencia Total (celdaP7) 3. Cálculo del total Proyectado en la Caja1 1. Selecciona la celda B21. 2. Ingresa la fórmula con la función =SUMA(B7:B18) 3. Realiza un arrastre del cuadro de relleno hasta la celda E18. 4. Cálculo del Porcentaje Logrado de la Caja1 1. Selecciona la celda E7 2. Ingresa la siguiente fórmula =C7/B7 3. Realiza un arrastre del cuadro de relleno hasta la celda E18 Resolver 1. Calcula el porcentaje logrado de la caja2 I7 2. Calcula el porcentaje logrado de la caja3 M7. 3. Calcula el porcentaje logrado del Total Q7 1. Aplicar formatos Aplica los formatos necesarios para que la hoja Datos se muestre como la hoja Resultado. Resolver el cuadro estadístico:

29


30

RETROALIMENTACIÓN 1. Abrir el archivo utilidad y Resuelva el siguiente cuadro.

2. Abrir archivo cadena y resolver las fórmulas y formatos como se indica en el cuadro.

31

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________ 1. Marca verdadero (V), falso (F), las siguientes afirmaciones. ( ( ( (

) Las fechas son un tipo de dato especial y no son aceptadas como número. ) La tecla F2 permite modificar el contenido de una fórmula. ) La combinación ctrl+Enter genera un ajuste de celda. ) La combinación CTRL+F1 activa o desactiva la cinta superior de pantalla. En el siguiente cuadro indicar las fórmulas utilizadas.

2. Porcentaje de respuestas correctas en G3: a) =C3*D3/100 b) =D3/C3 c) =D3/(E3+D3) d) =(C3+D3)/D3 e)=C3/D3 3. Porcentaje de respuestas incorrectas en H3: a) =C3*D3/100 b) =D3/C3 c) =D3/(E3+D3) d) =(C3+E3)/E3 e)=E3/C3 4. Porcentaje de respuestas en blanco en I3: a) =F3/D3 b) =D3/F3 c) =D3/(E3+F3) d) =(C3+E3+E3)/E3 e)=C3/F3

32

5. Porcentaje total en J3: a) =suma(D3:F3) b) =suma(G3:F3) c) =suma(D3:F3)*C3/100 d) =suma(C3:F3) e) =suma(C3:F3)/suma(G3:I3) 6. ¿Cómo se llama el formato para que la respuesta tenga 3 digitos a) =suma(D3:F3) b) =suma(G3:F3) c) =suma(D3:F3)*C3/100 d) =suma(C3:F3) e) =suma(C3:F3)/suma(G3:I3) 7. ¿Cuál es el código de formato utilizado para que se vean con 3 digitos? a) 0,000 b) 0.000 c) 000,0 d) 000 e) ### 8. ¿Cuál es el código de formato utilizado en el total de preguntas (pts)? a) “Pts”000 b) “Pts”0 c) 0"Pts” d) # “Pts” e) 000 “Pts” 9. ¿Cómo se llama el formato condicional aplicado al siguientes rango? a) Resaltar colores b) Barra de datos c) Conjunto de Iconos d) Escala de colores e) Resaltar reglas de celdas

10. ¿ Cuál es la fórmula en el puntaje final si se considera que una respuesta correcta vale 8 puntos, una icorrecta -2 y una pregunta en blanco 0?

33

TIPS EXCEL 2007 1) Poner la fecha del ordenador: Seguramente, a lo largo de una sesión de trabajo con Excel escribimos varias veces fechas, y en muchos casos se trata de la fecha del día corriente. En lugar de escribirla, simplemente estando posicionados en la celda donde queremos que aparezca la fecha de hoy, presionamos simultáneamente las teclas “CTRL” y “+”. 2) Poner la fecha del ordenador: De la misma manera que el truco numero 1 escribe la fecha actual (en realidad, la que tiene el ordenador), si presionamos “CTRL” y “:” aparecerá la hora en la celda seleccionada. 3) Repetir el contenido de la celda superior: Si queremos escribir dentro de una celda el mismo contenido que esta en la celda inmediatamente superior, la forma mas rápida seguramente es presionar “CTRL” y “ ' “ (apóstrofe). 4) Repetir del contenido de la celda inmediatamente a la izquierda: De la misma manera que en el truco numero 3, si presionamos “CTRL” y “D”, en la celda que se encuentre seleccionada se copiara el contenido de la celda inmediatamente a la izquierda. 5) Poner el contenido de la celda en Negrita: Si deseamos cambiar el formato de la fuente con la que esta escrito el contenido de una celda para que aparezca en negrita, la forma más eficiente es presionar “CTRL” y “N”. 6) Subrayar el contenido de una celda: Para subrayar, en lugar de usar la barra de herramientas es más rápido presionar “CTRL” y “S”. 7) Pasar a cursiva: De la misma manera que en los dos trucos anteriores, si queremos poner en cursiva (itálica) la fuente de la celda actual, basta con presionar simultáneamente “CTRL” y “K”. 8) Tachado del texto de una celda: Para cambiar el estilo del texto de la celda seleccionada a “tachado”, simplemente presionemos “CTRL” y “5”. 9) Menú Formato: Si el formato que queremos aplicar a una celda no es ninguno de los mencionados en los trucos 5, 6, 7 u 8, nos queda la posibilidad de entrar en el submenú “Formato de Celdas” (figura 1) presionando simultáneamente “CTRL” y “1” 10) Ocultar / Recuperar filas: Como ya sabemos, en Excel es posible tener filas o columnas ocultas, lo que evita tener “a la vista” celdas con resultados intermedios, o que no nos interesa ver. La forma más simple de ocultar una fila es presionar “CTRL” y “9” a la vez. La fila en la que esta la celda actual habrá “desaparecido”. Si la queremos recuperar, simplemente la combinación de teclas “CTRL”, “Mayús.” y “8” la harán aparecer nuevamente.

8

OPERADORES Y FUNCIONES Excel usa los operadores aritméticos con la jerarquía natural en las operaciones aritmética.

Operadores y jerarquía OPERADORES

EJEMPLO

RESULTADO

1

Paréntesis

2

Potenciación

= 2^2

4

3 4

Multiplicación

=15*15

225

=(2+8) - (18-9)

1

Sumas y Restas

= 2+ (3*5)

17

Fórmulas Para ingresar una fórmula usan las referencias de celda y los operadores, para iniciar una fórmula se ingresa el símbolo =. Ejemplo Resolver

Factor =

6x + 1+3x - 2+5x 2+ 4y 4

Procedimiento 1 Seleccionar la celda B5 2 Ingresar la fórmula

6*B2+ (1+3*B2(/2+4*B3)-(2+5*B2)/4

Expresiones con Operador de Potencia El operador de potencia en el símbolo circunflejo (^)

Procedimiento 1 Seleccionar la celda 2 Ingrese la fórmula: =4*B2^3

Z= 4x 3

35

OPERADORES Y FUNCIONES

Raíz cuadrada Para dar solución a expresiones con radicación cuadrática se hace uso de la función RAIZ.

Procedimiento 1 Selecciona la celda 2 Ingrese la fórmula: =RAIZ(B2+B3)

w=

x+y

Expresiones con Radicación Para expresar radicaciones es conveniente convertir la raíz en una potencia en fracción.

Procedimiento 1 2

Selecciona la celda

w=

x+y

Ingrese la fórmula: =(5*B2+2*B3)^(1/4)

Actividad Realiza el cálculo de las operaciones matemáticas en Excel 2007. Anota tu respuesta.

36

OPERADORES Y FUNCIONES

FUNCIONES Excel presenta una gran variedad de funciones y las clasifica mediante categorías. Entre la más usadas tenemos a:

Funciones Estadísticas

Función Cortar y ContarA La función contar (B3:D3) obtiene 12 porque solo cuenta números. La función contarA(B3:D3) obtiene 3 porque considera texto y número.

Función Promedio y PromedioA La función promedio (B3:D3) obtienes 12 porque solo cuenta números. La función promedio (B3:D3) obtiene 8 porque considera texto y número.

37


Preparando el programa Abre el archivo formulas y funciones. Fórmula y expresiones. Es necesario usar las referencias para el ingreso adecuado de una formulas

Área del triangulo 1 En la celda B5 2 Ingresa =B2*C3/2

Área= Base x Altura 2

Área del círculo 1 En la celda B5 2 Ingresa =B2*B3^2

Área= pi x R

2

Monto en dólares 1 En la celda B5 2 Ingresa =B2/B3

Dólares =

Soles tcambio

Monto en soles 1 En la celda E13 Soles = dolares x tcambio 2 Ingresa =E10*E11

38


Comisión de Ventas 1 En la celda B5 2 Ingresa =B2*B3 Comision= ventas x %comision

Descuento AFP 1 En la celda E19 2 Ingresa =B2*B3 Pago AFP=Suedo x%AFP

Nuevo Precio 1 En la celda B26 2 Ingresa =B2+B2*B3 Nprecio= Prec.Julio +Prec.Julio * %aumento

Descuento 1 En la celda E26 2 Ingresa =B2-B2*B3 Nprecio=Prec.Actual-Prec.Actual*%aumento

39

9

EXPRESIONES Y FÓRMULAS EXPRESIONES

Abre el archivo Fórmula y expresiones Resuelve cada una de las fórmulas.

1. Cálculo de I

1 Selecciona la celda F6 2 Ingresa =4*B3+5*B4

I=4x+5y

2. Cálculo de J

1 Selecciona la celda F9 2 Ingresa la fórmula = (B3+B4)/(B3-B4)

J= x + y x-y

3. Cálculo de K

1 En la celda F14 2 Ingresa =3+(5+B3)/B4+3)

K= x+ 5+x y+3

4. Cálculo de P

1 En la celda F21 2 Ingresa =(3*B3-2*B4)/(4*B4+7*B3)+ 1/(B3-B4)-(B3+B4)/3

40

P= 3x-2y + 1 x+y 4y+ 7x x-y 3

EXPRESIONES Y FÓRMULAS

Fórmulas y expresiones con potenciación El operador de potencia en el símbolo circunflejo (^)

Cálculo de la expresión Q

Procedimiento 1 Selecciona la celda F26 2 Ingresa la fórmula: =6*B3^4

Q= 6x

4

Cálculo de la expresión R

Procedimiento 1 Selecciona la celda F30 2 Ingresa la fórmula: =(4*B4^3-3*B3^4)/6*B3^5+2*B4^3)

R= 4y

3

?

6x 5

4

3x

2y 3

Raíz cuadrada Para dar solución a expresiones con radicación cuadrática se hace uso de la función RAIZ. 1. Cálculo de S

Procedimiento 1 Selecciona la celda F34 2 Ingresa la fórmula: =RAIZ(6*B4+B4*B3)

S=

6y + 4x

Cálculo de T

Procedimiento 1 Selecciona la celda F38 2 Ingresa la fórmula =RAIZ(2+3*B4)/RAIZ(95-2*B3)

T=

2+3y 95-2y

41

EXPRESIONES Y FÓRMULAS

n

X = X

Expresiones con Radicación

1 n

Cálculo de U 1 2

Selecciona la celda F42 Ingresa la fórmula =(B3+b4)^(1/3)

U=

3

x+y

Cálculo de V 1 2

Selecciona la celda F47 Ingresa la fórmula =(3*B4+(2*B3+1)^(1/4))/(1+4*B4)/(1/5)

U = 3y +

4

2x+1 5

1+ 4y

Actividad

Representar en Excel las siguientes fórmulas matemáticas

42

10

FUNCIONES

Actividad

Funciones Estadísticas Abre el archivo FUNCIONES ESTADÍSTICAS:

Calculo de total por mes

Cálculo del número de meses

Selecciona la celda B8

Selecciona la celda E11

Ingresa la fórmula: =Suma(B2:B7)

Ingresa la fórmula: =CONTARA(B1:G1)

Luego arrastra el cuadro de relleno

Luego arrastre

Cálculo del total por stand

Cálculo del número de meses

Selecciona la celda H2 Ingresa la fórmula: =Suma(B2:G2)


Luego arrastra el cuadro de relleno hacia abajo.

Luego arrastre

Ingresa la fórmula: =CONTARA(B1:G1)

43

Actividad

Funciones Estadísticas Abre el archivo FUNCIONES ESTADÍSTICAS:

Cálculo del total general

Cálculo de la mayor venta del stand3



Ingresa la fórmula: =h8

Ingresa la fórmula: =MAX(B4:G4)

Cálculo de la menos venta del stand

Cálculo del promedio del stand1



Ingresa la fórmula: =MIN(B6:G6)

Ingresa la fórmula: =PROMEDIO(B2:G2)

Cálculo del promedio general por stand Selecciona la celda E20 Ingresa la fórmula: =PROMEDIO(H2:H7)

44

Actividad

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________ Abrir Excel PREGUNTA 01: Mediante el uso de operadores resolver las fórmulas indicadas

PREGUNTA 02: Mediantes funciones estadísticas resolver

45

Actividad

PREGUNTA 03: Resolver mediante funciones

46

Actividad

PREGUNTA 04: Resolver las estadísticas de viaje mediante las funciones

47

11

FUNCIONES CONDICIONALES

Funciones Todas las funciones disponibles en Excel se muestran agrupadas en categorías dentro de una Biblioteca de funciones de la ficha de Fórmulas.

Pistas de función El autocompletar de funciones permite escribir rápidamente las funciones, así como conocer la sintaxis de la función a travéz de pistas.

2. Función Contar.Si Esta función permite contar celdas que cumplan un cierto criterio. Por ejemplo, si tuvieras una lista de pacientes, podríamos contar cuantos pacientes son de Trujillo o cuantos pacientes son menores de edad. La sintaxis es: =CONTAR.SI(rango, criterio) Rango

: Indica la referencia de rango o nombre de celda

Criterio

: Indica la condición que debe cumplir para contar en el rango.

Fórmula =CONTAR.SI(A2:A5,manzanas) =CONTAR.SI(B2:B5,>85) =CONTAR.SI(B1:B5,>55) =CONTAR.SI(B5:B5,>B4) =CONTAR.SI(B2:B5,>=32)

48

FUNCIONES CONDICIONALES Uso de caracteres comodín

Fórmula

Descripción

=CONTAR.SI(A2:A27, es)

Número de celdas que acaban con las letras es.

=CONTAR.SI(A2:A27,?????es)

Número de celdas que acaban con las letras es y tienes exactamente 7 letras.

=CONTAR.SI(A2:A27;*)

Número de celdas que contienen texto.

Función Contar.blanco Cuenta el número de celdas en blanco dentro de un rango. Ejemplo: =CONTAR.BLANCO(A2:B5) Contiene el valor 3 =CONTAR.BLANCO(A3:B5) Obtiene el valor 1 Resultado1

Contar.si.conjunto

Cuenta las celdas, dentro del rango, que cumplen los criterios especificados. Sintaxis: =CONTAR.SI.CONJUNTO(rango1;criterio1;rango2;criterio2)

Ejemplo: Número de clientes que cumplan con los tres requisitos (Si) =CONTAR.SI.CONJUNTO(B3:B6,si,C3:C6,si,D3:D6,si) Número de clientes que tengan auto y tarjeta =CONTAR.SI.CONJUNTO(B3:B6,si,S3:D6,si)

Resultado2

49


Sumar.si Suma las celdas que coinciden con el criterio indicado, es similar a la función contar.si con la inclusión del argumento rango_suma. Sintaxis

=SUMA.SI(rango,criterio,rango_suma)

Ejemplo: Total de vivienda de tipo B =SUMAR.SI(C2:C5,B,B2:B5) Resultado: 500 Total de vivienda en Lima: 100

Sumar.si.conjunto Suma las celdas de una rango que cumplen varios criterios Sintaxis: =SUMA.SI.CONJUNTO(RANGO_SUMA,rango1,criterio1,rango2,criterio2) Ejemplo Total de viviendas del Callao de tipoB =SUMAR.SI.CONJUNTO(C2:C7,B2:B7,callao,D2:D7,B) Resultado 700 Total de viviendas del tipo B de nivel1 =SUMAR.SI.CONJUNTO(C2:C7,B2:B7,callao,D2_D7,B,E2:E7,1) Resultado 200 Ejemplo =SUMAR.SI.CONJUNTO(Nº_vivienda,zona,callao,TipoB) =CONTAR.SI(Tipo,B)

50


Función Si Esta función permite evaluar una condición y de acuerdo a la condición se obtiene un resultado. Sintaxis = Si(prueba_logica_valor_verdadero,valor_falso) Estructura de la función:

V si la condición es verdadera =SI(condicion,V,F) F si la condición es falsa

Ejemplo1: Se desea obtener la observación de aprobado si el promedio es mayor o igual a 11, de lo contario será desaprobado. Solución = Si(C2>=11;”Aprob”;”Desap”)

Ejemplo 2: Debemos calcular la bonificación que representa el 20% del sueldo solo para los los estables E, para los contratados C no habrá bonificación. Solución =SI(C3=”E”;D3*20%:”0")

Ejemplo 3: Se desea hallar las horas extras semanales, teniendo como dato rotal de horas semanales trabajadas y que además las horas semanales son de 48. Solución =Si(C3>48,C3-48,0)

51

Actividad

Antes de Iniciar Abre el archivo EMPLEADOS

Creando Nombres

Caso1 1 Selecciona la hoja lista. 2 Ubica cualquier celda de la lista que contengan datos. 3 Presiona la combinación de tecla CTRL+*, esto permite seleccionar toda la lista.

4 ir a la ficha formulas, nombres definidos-crear desde la selección 5 Activa la casilla Fila Superior 6

52

Aceptar

Actividad

Contar.si y Sumar.si En la hoja estadística resuelve las fórmulas y funciones lógicas detalladas.

Personal por Cargo La función que obtiene el número de empleados de los Asistentes es=CONTAR.SI

1 En la celda B5, Ingresar =CONTAR.SI( 2 Presiona la tecla F3, y selecciona CARGO. 3 Luego del separador de lista(,), selecciona la celda A5 4 La función será: =CONTAR.SI(CARGO,A5) 5 Realiza una copia para los demás cargos mediante un arrastre.

Personal por categoría La función CONTAR.SI resolverá el número de personal por categoría. la celda E5, Ingresar= CONTAR.SI( Presiona la tecla 1 En F3, y selecciona CATEG. 2 Luego del separador de lista(,), selecciona la celda D5

3

La función será: =CONTAR.SI(CATEG,D5)

4

Realiza una copia para los demás cargos mediante un arrastre.

53

Actividad

Total de hijos por cargo La función SUMAR.SI resolverá el total de hijos por cargo. Para este cuadro se usara la función SUMAR.SI(CARGO,A13,HIJOS) CARGO es el rango donde se condiciona el criterio A13, y el nombre HIJOS es el rango donde se sumara cuando cumpla el criterio.

Total de hijos por categoría En la celda E13: =SUMAR.SI(CATEG,D13,HIJOS).

54

Actividad

Caso 2 Abre el archivo PROYECTOS. Calcularemos el número de proyectos de cada empresa. Solución Crear nombres de rango

1 Ubicar cualquier celda de las lista que contengan datos. 2 Presiona la combinación de teclas CTRL+* 3 Ir a la ficha formulas, nombres definido-crear desde la selección.. 4 Activa la casilla Fila Superior. 5 Aceptar Ingresa la función

1 Ir a la celda G4. 2 Ingresa la función: =CONTAR.SI(Empresa,F4).El rango Empresa= A4:A11. 3 Copia la formula

55

Actividad

Caso 3 Crea un archivo llamado ESTADO

Necesitamos resolver lo siguientes: 1. Número de clientes de la categoría A con hijos. 2. Número de clientes contratados de la gategoría B 3. Número de clientes sin hijos de sueldo menor a 200 y de la categoría A. Solución: En la hoja caso3 1. Selecciona la celda A2 =CONTAR.SI.CONJUNTO(CATEG,A,HIJOS, > 0) 2. Selecciona la celda A6 =CONTAR.SI.CONJUNTO(CONDICION,contratado,CATEG,b) 3. Selecciona la celda A6 CONTAR.SI.CONJUNTO(HIJOS,0,SUELDO,2000,CATEG,A)

Caso 4 Abre un archivo llamado LISTA, necesitamos resolver lo siguiente: 1. Total de saldos de clientes de la categoría A de la zona Norte. 2. Suma de saldos de los clientes de Brasil que tengan la mayoría de edad. 3. Promedio de edad de clientes de Francia de la categoría C =SUMAR.SI.CONJUNTO(Saldo,Categ,a,Zonal,Norte) =SUMAR.SI.CONJUNTO(Saldo,Edad>=18,Pais,Brasil) =PROMEDIO.SI.CONJUNTO(Edad,categ,C,Pais,Francia)

56

Actividad

Función SI

CASO 5 Abre el archivo FUNCIONES ANIDADAS HOJA F1 Se determina según el campo SEXO de la persona Si el SEXO es M mostrar MASCULINO, si el sexo es F, mostrar FEMENINO. Solución:

1. En la celda D3 2. =SI(C3=M,Masculino,Femenino)

HOJA F2 Se determina según el ESTADO CIVIL de la persona Si su E. CIVIL es S, mostrar SOLTERO Si su E,CIVIL es C, mostrar CASADO Si su E.CIVIL es V, mostrar VIUDO Si su E.CIVIL es D, mostrar DIVORCIADO Solución: 1. En la celda D3 2. =SI(C3=S,SOLTERO,SI(C3=C,CASADO,SI(C3,V,VIUDO,DIVORCIADO)) HOJA F3 Se determina según la CATEGORÍA de la persona CATEGORÍA Mostrará A 2500.00 B 2000.00 C 1500.00 D 1000.00 Solución:

1. En la celda D3 2. =SI(C4=A,2500,SI(C3=B,2000,SI(C3=C1500,1000)))

Hoja F4 Se determina la bonificación según la CATEGORÍA de la persona CATEGORÍA A B C D

Mostrará 20% del básico 15% del básico 12% del básico 10% del básico

1. En la celda E3 2. =SI(C3=A,D3*20%,SI(C3=B,D3*15%,SI(C3=C,D3*12%,D3*10%)))

57

Actividad

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________ Caso1: Resolver el siguiente cuadro de notas. Abre el archivo Práctica

Resolver el promedio considerado que la menor nota se elimina ¿Cuál es la formula ingresada? ___________________________________________________________________________ ___________________________________________________________________________ La observación será de Becado si el promedio es mayor o igual 14, de lo contrario no se mostrara nada. ¿Cuál es la formula ingresada? ___________________________________________________________________________

58

Actividad

Resuelva los casos Caso 2 En este caso resolveremos la hoja de notas mediante todo lo aprendido.

Abre el archivo Gastos Administrativos Equivalen al 2% de las ventas brutas

Ventas, ventas brutas. Gastos administrativo

Comisión fija será solo para los vendedores cuyo tipo se ESTABLECE (E). Equivale 345

Comisión Variables para los vendedores de la Línea VALVULAS (v) la Comisión será del 10% de las ventas, mientras que para el resto será del 7%

Bonificación será de 100 para los vendedores que excede de 10000 en sus ventas.

59

Actividad

Retención será de 10% de los ingresos (comsiones+ Bonificaciones)

Total Comisiones+ bonificación-retención

Observación para los vendedores que tengan Ventas BRUTAS menores a 5000 deben mostrar Baja, en caso contrario O.

Resolver el cuadro resumen de comisiones

Caso 3 En este caso resolvemos la hoja mediante la función lógica SI Abre el archivo Práctica 3.

60

REFERENCIAS RELATIVAS II

12

Referencias relativas Las referencias relativas están presentes en una fórmula. Si se mueve una formula, las referencias de celda no cambiaran, si se copia una fórmula, las referencias del celda cambiara. Calculo de total: Cant+Precio

Procedimiento 1 2

En la celda D2

3

Copiar la formula mediante un arrastre

Ingresar la formula: =B2*C2

Una referencia relativa también se aplica en las funciones.

Referencias Absolutas En el caso de las referencias absolutas, la dirección de esta no cambiara y se mantendrá fija. Para lograr que una celda sea absoluta, debe escribir la dirección de dicha celda con el formato de $Columna$Fila. Por ejemplo, $B$10 o $G$5 son consideradas referencias absolutas.

Procedimiento 1 2

Ingresar la formula: =A3*$B$1 Copiar la formula y realizar el arrastre.

Recuerde que la celda $B$1 es la celda absoluta.

61

REFERENCIAS RELATIVAS

Celda relativa y absoluta En la siguiente fórmula: =B2*$C$2 La celda B2 es una celda relativa y la celda $C$2 es una celda absoluta. La características de la celda absoluta esta en el hecho de que si se copia a la derecha siempre se mantiene la celda. Por ejemplo para calcular las ventas de febrero al mes de abril con un crecimiento de 10%.

Procedimiento

1 2

En la celda C6. Ingresar la fórmula: =B6+B6*$C$3

Referencias Mixtas Es importante observar que cuando se aplica referencias mixtas, la ubicación del símbolo $, define dos tipos de referencias mixtas. Columna$Fila Referencia Mixta (relativa en la columna y absoluta en la fila). Ejemplo B$1 Fórmula =A3*B$1

62


13

Columna$Fila Referencia Mixta (absoluta en la columna y relativa en la fila). Ejemplo Fórmula = B5*$B2

Se quiere calcular la proyección mensual de la producción para los meses de Febrero, Marzo y Abril, considerando los porcentajes de la tabla de aumentos.

Nombre de celda A una celda se le puede asignar un nombre para ser usada como referencia, lo importante está en que el usuario designe un nombre apropiado. Así las siguientes fórmulas podrían ser validas: 1. Definir Nombre de Celda La definición de celdas permite que el usuario designe un nombre que será usado en una fórmula.

Procedimiento

1 2 3 4

Selecciona la celda. En la fórmula, seleccionar nombres definidos. Luego asignar nombre a un rango. Ingresar el nombre:

63


Crear nombres de rango La creación de nombres se refiere a la posibilidad de tomar los títulos que se encuentran en la hoja de cálculo.

Procedimiento 1 Seleccionar rango de celdas. 2 En la ficha formula, selecciona nombres definidos. 3 Ingresar el nombre

Cuadro de Nombres de la barra de fórmulas En la barra de fórmula se encuentra el cuadro nombres de nombres, es posibles definir nombres en esta barra.

Procedimiento 1 Seleccionar la celda. 2 Ir al cuadro de nombres. 3 Escribir en nombre. 4 Presionar enter para finalizar.

64


Ver los nombres de rangos: Si desea conocer que nombres de rangos se han definido, puede usar algunas de las siguientes herramientas:.

Procedimiento 1 Seleccionar la haga clic en el Cuadro de nombres. Se desplegara una lista con todos los nombres de rangos asignados.

2 Fórmulas con nombres de celda y rango: Teniendo en cuenta el nombre de celda, es posible crear fórmulas con referencias a estos nombres. Por ejemplo: si la celda B2 se llama comisión. La fórmula seria: =B6+B6*Comisión.

65

Actividad

Celdas Absolutas Caso 1 Abrir el archivo caso01 Calcular la deuda en dólares

1 Ir a la celda C5 2 Ingresa la fórmula 3 =B=5*C3, luego presiona la tecla F4, se convertirá en =B5/$C$3, ahora presiona enter.

4

Copia la fórmula

Calcular el total 1 En B10 2 =suma(B5:B9) 3 Copia a la celda C10

Calcular el valor en dólares 1 Ir a la celda C4. Ingresar 2 =B4/B2 luego presiona la tecla F4 se convertirá en B4/$B$2 3

Presiona enter

4

Copia la fórmula hasta C6. Calcular el total En B7: ingresa =SUMA(B4:B6)

5 6

66

Copia hasta la celda D7

Actividad

2. Calculo del monto del libro. Calculo del monto total del libro

1 Ir a la celda C4. Ingresar 2 =B4*B1 luego presiona la tecla F4 se convertirá en =B4*$B$1 3 Presiona enter

4

Copia la fórmula hasta C10

Calcular el total

1 2

En B11 ingresar =suma(B4:B10) Copia hasta la celda D11

Calculo de porcentaje del libro

1 2

=B4*B1 luego presiona la tecla F4 se convertirá en =B4*$B$1

3

Presiona enter

4

Copia la fórmula hasta D10. Aplicar formatos.

5 6

Selecciona D4:D10

Ir a la celda D4. Ingresar

Haz clic en el botón %

67

Actividad

Caso 2 Abre archivo caso02

En este caso vamos a calcular la proyección de ventas para el año 2010, teniendo en cuenta la tabla de porcentaje de incremento para el producto. Proyección producto A 1. Ir a la celda C9 2. Ingresa la formula: =B9+B9*$B$4 3. Copia la fórmula a la celda M10. Proyección producto B 1. Ir a la celda C10 2. Ingresa la formula: =B10+B10*$B$5 3. Copia la fórmula a la celda M10. Proyección producto C 1. Ir a la celda C11 2. Ingresa la formula: =B11+B11*$B$6 3. Copia la fórmula a la celda M11 Total del mes de Enero 1. Ir a la celda B12 2. Ingresar =suma(B9:B11) Total del producto A 1. Ir a la celda N9 2. Ingresar =SUMA(B9:M9).

68

Actividad

Caso 3 Abre el archivo Caso03 Calcularemos los totales por región y por trimestre en soles y en dólares..

Calculo del total por región en soles 1. En la celda F5 2. Ingresa la formula: =SUMNA(B5:E5) 3. Copia la fórmula hasta la celda F7 Calculo de total por trimestre en soles 1. En la celda B8 2. Ingresa la formula: =SUMA(B5:B7) 3. Copia la fórmula hasta la celda G8 Calculo de total por región en dólares 1. En la celda G5 2. Ingresa la formula: =F5/$G$1 3. Copia la fórmula hasta la celda G7

69

Actividad

Calculo de total de trimestre en dólares 1. En la celda B9 2. Ingresar la formula: =B8/$G$1 3. Copiar la formula hasta la celda F8 Aplicar los formatos de soles y dólares.

Uso de celdas mixtas Abre el archivo Caso04 EN este caso calcularemos los precios para meses de febrero, marzo, abril y mayo, como datos tenemos los meses del mes de Enero y los porcentajes de aumento.

Usaremos una sola formula con celda mixta Calculo del precio del mes de febrero En la celda c3, ingresar la formula: =B3+B3*C$1 Copia la fórmula hacia la celda C7 y luego arrastre hacia la columna F.

70

Actividad

Caso 5 Abre el archivo Caso05 En este cuadro calcularemos las comisiones de TV, DVD, y VHS con una sola fórmula con celda mixta. Total de venta En la celda E5 Ingresa: =SUMA(B5:D5) Copiar fórmula Total de comisiones En la celda F5 Copiar fórmula Total de comisiones En la celda I5 Ingresar: =SUMA(F5:H5) Copiar formula

71

Actividad

Nombres de celda y rango Abre el archivo Caso06 Para resolver este caso utilizaremos nombres de celda. Definir nombre

1 2

Selecciona la celda B4 Ir a la ficha formulas, luego en nombres definidos asignar nombre a un rango.

3

Ingresa el nombre Alquiler

4

Aceptar

5

Repetir los pasos anteriores par definir los nombres anteriores, Los montos de alquiler, limpieza e impuestos son porcentajes de los ingresos.

Calcular el alquiler

1 2

Ingresa =C3+C3*limpieza

3

Copia hasta Junio

Selecciona la celda C4

Calculo de limpieza

72

1 2

Ingresa =C3+C3*limpieza

3

Copia hasta Junio


Calculo de impuesto

1 2

Ingresa =C3+C3*impuesto

3

Copia hasta Junio


Actividad

Caso7 Abre el archivo caso07 Para resolver este caso utilizaremos nombres de rango Creación de nombres

1 2

Selecciona el rango A3:G23.

3

Nombres definidos.

4

Crear desde la selección

Ir a la ficha formula

Cálculo del promedio final 1 2

Selecciona la celda G4

3

Copia la fórmula

Ingresa: =promedio(C4:F4)

Cálculo de la estadística del promedio final 1 2

Selecciona la hoja resolver

3

Ingresa: = promedio(prom)

4

Copia la fórmula

Selecciona la celda B4

73

Actividad

74

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________ Abre el archivo practica01. Calcular la fórmula para obtener los precios a plazo 3 meses, 6 meses, 9 meses y 12 meses respetivamente.

Usar celdas Absolutas

En la hoja de productos Usar celda Mixta Calcular la proyección 2011 mediante una sola formula

75

Actividad

En la hoja países Calcular la proyección 2009 mediante una sola fórmula Usar celda mixta Usar nombre de celda

Abrir el archivo practica03 Usar celda Mixta

76

Actividad

Usar nombre de celda Abrir el archivo practica04

Consumo mensual

Diferencia de lecturas

Monto Consumo

Consumo mensual*Costo de Consumo (usar nombre)

Mantenimiento

Monto de Consumo

Total

Suma de Monto Consumo+Mantenimiento

IGV

Total*IGV (usar nombre)

Neto

Total + IGV

77

14

GRÁFICOS E IMÁGENES

Gráficos

Gráficos estadísticas.

Gráficos polinomios

Gráficos trigonométricos

Se pueden realizar gráficos con representaciones de imágenes, esto hace mas representantes e ilustrativa las variaciones y comparaciones.

Lectura de un gráfico

78

GRÁFICOS E IMÁGENES Tipos de Gráficos

79


Crear Gráficos Para crear un gráfico, se debe seleccionar el rango de datos necesarios para graficar. Ejemplo: Graficar las ventas del primer semestre del año 2008

Procedimiento

80

1 2

Seleccionar los datos así como los título A4:G5

3 4

Seleccionar el tipo de gráfico Barra

Luego, en la ficha insertar, en el separador Gráficos.

Seleccionar el modelo(cilíndrico horizontal agrupado)


Cambiar el tipo de Gráfico

Procedimiento 1 Seleccionar gráfico. 2 Ir a la ficha herramientas de gráficos 3

Seleccionar la ficha diseño

4

En cambiar tipo de gráfico

5

Elegir una de las 70 combinaciones.

81


Diseño de gráficos La apariencia de los gráficos y sus elementos se deben controlar desde la ficha Diseño de las Herramientas para gráfico.

Procedimiento 1 Haga clic en el grafico 2 Luego en la barra Herramientas para gráfico haga clic en la ficha Diseño.

3 Ubique la galería Estilos de diseño. 4 Elija un nuevo estilo para gráfico. Observe los cambios aplicados.

Formato de gráficos Formato de elementos El formato de los elementos del gráfico, como título, leyendas, etiquetas, etc, se realiza a través de los botones de la ficha Inicio Herramientas Formato En el caso de los diagramas como barras, los planos, etc. Se realiza a través de la Ficha Formato de las Herramientas para gráficos.

Procedimiento 1 2 3

Haga clic en el gráfico. En la barra Herramientas para gráficos. Clic en la ficha formato. En la galería estilos de forma, estilos de WordArt

4

Elija la opción que desee. Por ejemplo el formato resplandor da relieve al texto

82


Gráfico Trigonométricos Los gráficos trigonométricos pueden ser graficados mediante la tabulación mediante la tabulación de la función seno y cos.

Procedimiento 1

Crear una tabulación con los datos Ángulo: grados sexagesimales Radianes función=radianes (ángulo) Seno función=Seno(radianes) Coseno función=cos(radianes) Seleccionar el rango Ángulo y Seno Graficar: tipo de gráfico dispersión

Imágenes Se puede crear gráficos con imágenes, para mostrar en forma más apropiada las imágenes.

Procedimiento 1

Crear un gráfico de columnas simple

2 3

Seleccionar imagen, clic en copiar.

4

Aplicar las opciones pegar en la serie

Seleccionar la serie

83

Actividad

Caso1: Proyección de Ingresos 1. Gráfico de Barras Abre el archivo Proyección Se pide crear un gráfico que muestre las ventas del 1er semestre del año 2008

1 2

Selecciona datos así como los títulos A4:G5.

3

Selecciona el tipo de gráfico Columna agrupada 3D

Luego en la ficha Insertar, en el separador Gráficos.

Diseño rápido

84

1 2

Haz clic en el gráfico

3

Selecciona diseño rápido, elegir diseños 10. Aplicar formato de relleno.

En la barra Herramientas para gráficos haz clic en la ficha diseño.

Actividad 3. Gráfico lineal

1 2

Gráfico de ventas y compras.

3

Diseño rápido, diseño 9

Selecciona los rangos: A4:G5 y A9:G9

4. Etiquetas de datos

1 2

Seleccionar los valores de etiqueta de datos.

3

Seleccionar por encima

Ir a la ficha presentación, seleccionar etiqueta de datos.

5. Gráfico Circular

1 2

Selecciona A4:B7

3

Selecciona circular 3D

Ir a la ficha Insertar-Gráficos

Más opciones

Separar sector

1 Haz clic en el sector 2 Realiza un arrastre

1 Haz clic en el En etiqueta de datos 2 Elegir más opciones de datos 3 Marca Valor, porcentaje, nombre

Etiqueta de datos

1 Ir a la ficha presentación 2 En la etiqueta de datos 3 Elige Extremo

85

Actividad

Caso2: Censo Poblacional Abrir el archivo censo poblacional 1.Censo de población total

86

1 2

Seleccionar el rango B3:B8

3

Hacer clic en seleccionar datos-

4

En etiquetas del eje horizontal, hacer clic en editar.

5

Seleccionar A3:A8

6

Eliminar el eje vertical y añadir etiqueta.

Ir a la ficha Insertar-columnas (agrupada en 3F)

Actividad

2. Censo 2005 Gráfico círcular

Efectos de forma

1 2

Seleccionar el rango D2:E2 y D8:E8

3

Seleccionar gráfico(círcular 3D).

4

Seleccionar etiqueta de datos, en opciones marcar nombre, valor y porcentaje.

Ir a la ficha Insertar

1 2

Seleccionar la serie (sector de gráfico)

3

Seleccionar gráfico(círcular 3D).

4

Aplicar formatos.

En la ficha Formato, ir a efectos de formas.

CENSO 2005

3. Censo Población encuestada Ejes en orden inverso

Ancho del intervalo

1 2

Ir a la ficha presentación( sector gráfico)

1 2

En el eje, eje primario, más opciones de eje.

3

Ir al botón aplicar formato a la selección.

3

Marcar la casilla categoría orden inverso.

4

En ancho del intervalo, elegir 15%

Seleccionar la serie de barras. En la ficha selección actual.

87

Actividad

4. Imágenes Población censada de MUJERES

1 Crear un gráfico de columnas 2D Agrupada. 2 Seleccionar serie de datos. 3 En la ficha formato, relleno de forma. 4 Seleccionar imagen, elegir varios. 5 Aplicar formato al título efecto resplandor.

Población censada de MUJERES. 13,061,026.00 POBLACIÓN CENSADA DE MUJERES

10,556,575.00 8,489,857.00 6,784,530.00

4,925,518.00 3,067,868.00

1940

1961

1972

1981

1993

2005

Mediante el paso anterior crear la sesión censada de varones.

88

Actividad

5. Gráficos polinomios Abrir el archivo gráfico XY

1 Seleccionar la celda B4 2 Ingresar la fórmula 3 Copiar la fórmula 4 Crear un gráfico 5 Aplicar formato a los ejes

6

Ingresa la fórmula de X

2

datos

89

Actividad

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________

Resolver

90

CONVERTIR RANGO A TABLA

15

Por defecto, los rangos de Excel son considerados como celdas comunes y corrientes. Todas las herramientas de tablas se verán solo cuando el rango sea convertido a tabla.

Procedimiento 1 2 3

4

5

Seleccionar rango de datos que desee convertir en una tabla.. En la ficha Insertar, haga clic en el botón tabla. Si el rango seleccionado incluye datos que desea mostrar como encabezados de tabla, active la casilla de verificación la tabla tiene encabezados. Al hacer clic en Aceptar, el rango se muestra con formato predeterminado y cada encabezado de columna se muestra con flechas de filtros.

Observe además, que al convertirse su rango a tabla, se muestra una herramienta contextual llamada Herramientas de tabla.

91


Aplicar un formato de tabla Una vez que el rango se ha convertido en una tabla, es posible aplicar un formato determinado.

Procedimiento 1 Hacer clic dentro de la tabla. 2 Luego, vaya a la ficha Diseño, y ubique, y ubique el grupo Estilos de tabla.

3 Use botón Mas para desplegar toda la galería y, a continuación, en el grupo Claro, Medio u Oscuro, haga clic en estilo de tabla que desea utilizar. Por ejemplo, en el grupo oscuro, ubique el estilo 10. Luego desplace el puntero del mouse al grupo Medio y señale el estilo 7. Finalmente, vaya al grupo Claro y señale el estilo 6.

4 Finalmente, aplique el estilo 4 del grupo Medio.

Quitar formato Toda tabla posee un estilo o un formato definido. En este ejercicio se verá la forma de eliminar dicho estilo.

Procedimiento 1 2

92

Hacer clic dentro de la tabla. Luego, ir a la ficha Diseño, y ubique el grupo Estilos de tabla.

Usar el botón Más para desplegar toda la galería.

1 2

Elija la opción Borrar de la parte inferior de la galería.

3

Aplique un formato del grupo Medio a su tabla.

4

Guardar los cambios


Mostrar y calcular totales Al convertir su rangos en tabla es posible obtener totales para una o más columnas de datos. Por ejemplo, podrá sumar una columna y obtener el promedio de otra, contar el número de registros de una columna y obtener la desviación estándar de otro, etc..

Procedimiento 1 2

Ubicar cursor sobre celda de la tabla. Activar el menú contextual, Tabla ya active la opción Fila de totales.

Cambiar la función 1. Sobre el total mostrado, hacer clic sobre la ficha y elija otra función, en nuestro caso Promedio

2. Desactive la fila de total realizado el mismo proceso que use para activarla.

Columnas calculadas Las fórmulas de una lista pueden usar las referencias normales con celdas o usar un nuevo tipo de referencias: estructuradas. En nuestro ejemplo, en la hoja Bebidas se va a crear una columna llamada transporte en el cual se cobra 1.5 por casa caja.

Procedimiento 1 Lleve el indicador de celda a la columna de la derecha(columna vacía)

2 En esta celda, escriba el texto Transporte. 3 Observe que el formato de la tabla se aplica a esta nueva columna.

95


Escribir fórmulas 1. En la celda F7 escriba la fórmula: E7*1.5

Escribir fórmulas con referencias Otra forma de escribir fórmulas, es usando referencias. Las referencias estructuradas permiten usar los títulos de cada columna (o campo) como parte de la fórmula.

Procedimiento 1 2

1 2

96

En la celda escriba = Observe la lista de campos que se despliega:

Seleccione el campo C guía y cierre el corchete (] ). Luego siga escribiendo la fórmula para que muestre como: = [ventas] * 1.5

CONVERTIR RANGO A TABLA Filtros Los filtros permiten listar registros que cumplan una determinada condición o criterio. Por ejemplo, podemos listar solo las licitaciones de la empresa Apse Srl o todas que se iniciaron en el año 2006. Los filtros se

Filtros

Para usar los filtros:

1 Haga clic sobre la flecha de la columna y Activa las casillas necesarias. 2 Haga clic en Aceptar y observese los cambios. Criterios personalizados Podemos usar criterios personalizados para los filtros. Por ejemplo Si deseamos ver las guías que tengan entre 10 y 15 cajas..

1 Haga clic en la flecha de filtros y 2

filtro de Número. Elegir la opción Entre y en el cuadro filtro personalizado.

3 Haga clic en Aceptar y observese los resultados. Filtro con texto Podemos usar porciones de textos como criterios. Por ejemplo, deseamos mostrar las guías que se despachen bebida que contengan la palabra litro en su tamaño.

Elija la opción Contiene y en el cuadro Filtro personalizado escriba. 2 Elegir la opción Entre y en el cuadro filtro personalizado.

1

97

16

PRACTICAMOS LO APRENDIDO

Convertir un rango en una tabla Por defecto, los rangos de Excel son considerados como celdas comunes y corrientes. Todas las herramientas de tablas se verán solo cuando el rango sea convertido a una tabla. * Abre el libro Pedidos. Ir a la hoja Bebidas. Para convertir el rango a una tabla

1

Selecciona el rango de datos que desee convertir en una tabla, en nuestro caso selecciona el rango de A6:E36.

2

En la ficha Insertar, en el grupo Tablas, haz clic en el botón Tabla, haz clic en el botón Tabla..

3

Si el rango seleccionado incluye datos que desea mostrar como encabezados de tabla, activa la casilla de verificación. La tabla tiene encabezados.

4

Al hacer clic en Aceptar.

5

Observa además, que al convertirse su rango a tabla, se muestra una herramienta contextual llamada herramientas de tabla.

6

Ahora, haz clic sobre su tabla y usando las flechas direccionales, e ir a la última celda. Observa que los encabezados de columnas que era letras.

Aplicar un formato a la tabla

1 2 3 4

Haz clic dentro de la tabla que acaba de crear. Luego, ir a la ficha Diseño y ubica el grupo. Estilos de tabla. Usa el botón Más para desplegar toda la galería y, a continuación, en el grupo Claro, medio u oscuro, haga clic en el estilo de tabla que desea utilizar. Por ejemplo, en el grupo Oscuro, ubica el estilo 10. Luego, desplace el puntero del mouse al grupo medio y señale el estilo 7. Finalmente vaya al grupo Claro y señale el estilo 6. Finalmente, aplica el estilo 4 del grupo Medio.

Quitar el formato de tabla

1 2

Haz clic dentro de la tabla, luego vaya a la ficha Diseño, y ubica el grupo Estilos de tabla. Usa el botón Más para desplegar toda la galería. Elige la opción Borrar de la parte inferior de la galería.

3

Aplica un formato del grupo Medio a su tabla, Guarda los cambios.

98

PRACTICAMOS LO APRENDIDO

Copiar, generar Series y Generar Listas Mostrar y calcular totales

1 Ir a la hoja bebidas. 2 Luego, sobre alguna celda de su tabla, usa el menú contextual. 3 4

Tabla y activa la opción Fila de totales. Sobre el total mostrado, haz clic sobre la flecha y elija otra función, en nuestro caso promedio. Desactiva la fila de total realizando el mismo proceso que use para activarla. Ir a la hoja Ventas, active la fila de Totales y obtenga totales de acuerdo a:

5 En la hoja Licitaciones, los totales se deben mostrar de acuerdo a:

Crear columnas calculadas

1 Ir a la hoja Bebidas. 2 Lleva el indicador de celda a la celda F6 3 En esta celda, escriba el texto transporte. 4

Observa que el formato de la tabla se aplica a esta nueva columna.

Fórmulas: 1. En la celda F7, escriba la fórmula =E7*1.5

99

PRACTICAMOS LO APRENDIDO Referencia estructurada

1 2

En la celda F7, escriba =[ Observa la lista de campos que se despliega:

1 Selecciona el campo guía y cierra el corchete (] ). Luego sigue escribiendo la fórmula para que se muestre como: = [ventas] * 1.5

2 Ahora, agrega una columnas llamada IGV y escribe la fórmula necesaria para que se calcule el IGV(19%) del campo de Transporte.

100

Actividad

PRACTICA CALIFICADA Nombre y Aplellidos: ______________________________ Grado y Sección: _________________________________ Pregunta1: Modificar las opciones de estilos

1. Haga clic sobre su tabla de la hoja BEBIDAS. 2. En la ficha Diseño, ubique el grupo Opciones de estilo. 3. Ahora, apague y prenda cada casilla y anote los resultados sobre su tabla.

Comando

Resultado

Fila de encabezado Fila de totales Fila con bandas Primera columna Última columna Color columnas con bandas

Pregunta2:

101

dadivitcA

Pregunta3

1. Vaya a las hojas Licitaciones y escriba la fórmulas necesarias para calcular: Fecha Final: se debe agregar la Duración al Mes Inicial Fórmula:_____________________________________________________________________ Garantía: 10% del monto Fórmula:_____________________________________________________________________

2. Luego, a la columna fecha se le debe aplicar un formato de la fecha tal como se muestra en la imagen. Al campo Garantía se le debe aplicar un formato de moneda sin decimales. El resultado debe ser como:

Pregunta4: Mostrar las ventas mayores a 10 cajas o las 5 guía mayor venta. Para aplicar criterios personalizados. 1. En la columna Ventas, haga clic en la flecha de filtros y elija Filtro de número. 2. Elija la opción Mayor que y en el cuadro Filtro personalizado escriba 20. 3. Haga clic en Aceptar y observe los resultado: ¿Qué registros se muestran?___________________________________ Si deseamos ver las guías que tengan entre 10 y 15 cajas.

Pregunta5: Abrir Archivo Filtros-Preguntas05 y resolver

102

Modulo Excel

Recommend Documents