Manual Excel I 2010.pdf

Gestión Comercial de Negocios

Excel I 2010

Técnico en Computación

Excel I 2010 Director del Programa

: Lic. Jaime Benavides Flores.

Coordinador General

: Lic. Daniel Sanchez Gamboa.

Coordinador Académico

: Ing. Eloy Sotelo Cruz.

Elaboración

: Prof. Edwin Chuquipul Pizarro.

Diagramación

: Rogger´s Publicidad - Sr. Javier Almora Oliva.

© Los derechos de edición, distribución y comercialización de esta obra son de exclusividad del Instituto Superior Tecnológico IDAT

2

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Presentación Teniendo en cuenta el constante avance de la tecnología y la necesidad de una permanente actualización, el instituto superior tecnológico IDAT, asume como preocupación primordial el desarrollo profesional de nuestros alumnos y participantes en general. Por este motivo, presenta el curso de Gestión Comercial de Negocios que tiene como objetivo fundamental capacitar a los participantes en la nueva versión del Libro Electrónico Excel 2010 a través de casos prácticos que le permita analizar la información para que tome las mejores decisiones en su empresa, oficina, estudios o centro de labores. La dirección del programa de Diseño, Publicidad y Extensión, felicita al alumnado y publico en general por confiar y formar parte de nuestra familia idatina, esperando que el esfuerzo para mejorar el servicio sea de su entera satisfacción y en provecho de ustedes.

Lic. Jaime Benavides Flores Director del Programa de Diseño, Publicidad y Extensión

Programa de Diseño, Publicidad y Extensión

3

4

Introducción

EXCEL I 2010

Sesión 1

Técnico en Computación

Qué es Excel Microsoft Excel 2010 permite analizar, administrar y compartir información de más formas que nunca, lo que le ayuda a tomar decisiones mejores y más inteligentes. Las nuevas herramientas de análisis y visualización le ayudan a realizar un seguimiento y resaltar importantes tendencias de datos. Obtenga acceso fácilmente a datos importantes dondequiera que vaya desde prácticamente cualquier explorador web o smartph. Incluso puede cargar sus archivos en Internet y trabajar simultáneamente con otras personas en línea. Tanto como si produce informes financieros o administra sus gastos personales, Excel 2010 le brinda más eficiencia y flexibilidad para lograr sus objetivos

Aplicaciones en Excel Realice comparaciones rápidas y efectivas Excel 2010 ofrece nuevas características y herramientas eficaces para ayudarle a descubrir patrones o tendencias que pueden llevar a decisiones más informadas y mejorar la capacidad de analizar grandes conjuntos de datos.

Obtenga análisis eficaces desde el escritorio Los refinamientos de Excel 2010 y las mejoras en su rendimiento le permiten llevar a cabo su trabajo con mayor rapidez y facilidad.

6

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Ahorre tiempo, simplifique el trabajo y aumente la productividad Crear y administrar los libros es mucho más fácil cuando puede trabajar como desea.

Rompa las barreras y trabaje en equipo de nuevas maneras Excel 2010 ofrece formas simples de permitir que las personas trabajen conjuntamente en libros, lo cual sirve para mejorar la calidad de su trabajo. Lo mejor de todo es que incluso las personas con versiones anteriores de Excel pueden participar sin problemas.

Programa de Diseño, Publicidad y Extensión

7

Técnico en Computación

Obtenga acceso a los libros en cualquier momento y lugar Obtenga la información que necesita, en el lugar y del modo en que la desea. Ahora puede obtener acceso fácilmente a los libros llevando la experiencia de Excel con usted y mantenerse al día dondequiera que esté.

Acceder a Excel 1. Seleccione el botón Inicio y luego haga clic en la opción Todos los programas. 2. Luego elija la carpeta Microsoft Office 20010 y selección Excel 2010. 3. De manera opcional, puede escribir en el control Buscar del botón inicio la palabra Excel, esta forma es mas rápida que la anterior.

8

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Descripción del entorno de Excel 2010

Mi primer libro Los documentos en Excel se denominan libros. Un libro está compuesto por varias hojas de cálculo que es almacenado en el disco duro como un archivo de extensión .xlsx. En la parte inferior izquierda de la ventana del libro existen unas etiquetas que permiten seleccionar la hoja de cálculo requerida. Cada vez que abrimos un nuevo libro de trabajo este se abrirá con tres hojas de cálculo.

Hojas de cálculo Una hoja de cálculo es una matriz bidimensional que está compuesta por columnas (en sentido vertical) y filas en sentido horizontal). Las columnas se encuentran etiquetadas con letras desde la “A” hasta la “XFD” (totalizan 16,384 columnas). Las Filas están etiquetadas con números desde el “1” hasta “1,048,576 filas). Una Celda es la intersección entre una columna y una fila, es donde se ingresa información. Toda celda tiene una dirección, conformada por el nombre de la columna y el número de la fila. Por ejemplo: B4 .También puede tener un nombre propio.

Programa de Diseño, Publicidad y Extensión

9

Técnico en Computación

Bloque o Rango de Celda está compuesto por una o varias celdas seleccionadas que tienen como característica principal su forma rectangular. Un rango tiene una dirección, conformada por la primera celda del lado izquierdo y la última celda del vértice opuesto. Por ejemplo: C8:J20 (los “:” denotan un rango). También puede tener un nombre propio. Puntero de celda es la celda especial de color inverso que se traslada por toda la matriz y permite acciones de desplazamiento, selección y relleno o copia de fórmulas.

Desplazamientos y selecciones Cambiar de celda activa es tan fácil como seleccionar otra celda de la hoja, ya sea con el mouse o con las teclas direccionales del teclado. La siguiente tabla muestra las teclas que puede utilizar para moverse cómodamente por las celdas de la hoja, utilizando el teclado:

Para

10

Pulse lo siguiente

Ir hasta el extremo de la región de datos actual

CTRL + Tecla de Dirección

Ir hasta el comienzo de una fila

Inicio

Ir hasta el comienzo de una hoja de cálculo

Ctrl + Inicio

Ir a la última celda de la hoja de cálculo

Ctrl + Fin

Desplazarse una pantalla hacia abajo

AvPág.

Desplazarse una pantalla hacia arriba

RePág.

Desplazarse una pantalla hacia la derecha

Alt + AvPág.

Desplazarse una pantalla hacia la izquierda

Alt + RePág.

Ir a la siguiente hoja del libro

Ctrl + AvPág.

Ir a la hoja anterior del libro

Ctrl + Re Pág.

Ir al siguiente libro o la siguiente ventana

Ctrl + F6 Ó Ctrl + Tab

Ir al libro a la ventana anterior

Ctrl + Shift + F6 Ó Ctrl + Shift + Tab

Ir al siguiente panel

F6

Ir al panel anterior

Shift + F6

Desplazarse para ver la celda activa

Ctrl + Retroceso

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Seleccionar (rangos) celdas continuas y discontinuas:

Seleccionar columnas y filas:

Tipos de datos Una celda en Excel acepta tipos de datos constantes o fórmulas.

Constantes Dentro de los datos contantes se agrupan los tipos de datos texto, numéricos y fecha/hora.

Programa de Diseño, Publicidad y Extensión

11

Técnico en Computación

1. Tipo de dato texto Un dato de tipo texto puede contener cualquier serie de caracteres (letras, números y símbolos), es decir, es de naturaleza alfanumérica y puede tener hasta un máximo de 32.000 caracteres.

Por ejemplo el número 10 equivale en formato fecha al día 10-Enero-1900, y el número 10,75 equivale en formato fecha al día 10-Enero-1900 a las 18:00 horas. De esta forma es posible realizar operaciones matemáticas con celdas que contengan fechas u horas.

Un dato de tipo número puede incluir dígitos del 0 al 9 y los signos especiales + – ( ) / %. Por defecto los números aparecen alineados a la derecha en la celda.

2. Tipos de datos fecha/hora Un dato tipo fecha/hora es tratado como un número, correspondiendo a cada fecha el número equivalente al de días transcurridos desde el 1 de Enero de 1.900 hasta la fecha en cuestión. A la hora le corresponde la fracción resultado de dividir el número de segundos transcurridos desde las 0 horas, por el número de segundos que tiene un día (86.400).

Fórmulas Una fórmula es una secuencia formada por: valores constantes, referencias a otras celdas, nombres, funciones, u operadores. La fórmula se escribe en la barra de fórmulas y debe empezar siempre por el signo =. Las fórmulas permiten realizar cálculos, más o menos complejos, con los datos introducidos en la hoja de cálculo.

Guardar libro Cuando se ingresa información en un libro por primera vez, es recomendable guardar o almacenar su información, para evitar pérdidas posteriores de datos.

Caso práctico 01 Crear un libro para registrar las ventas de electrodomésticos de una tienda comercial, durante la primera temporada de ventas. 1. Ubicado en Excel ingrese los siguientes datos considerando las operaciones de relleno, indicadas:

12

Programa de Diseño, Publicidad y Extensión

Excel I 2010

2. Luego de modificar el ancho de columnas, considerando lo siguiente:

3. Modificar el alto de la fila 2, según lo indicado:

4. Realizar la operación rápida de la Autosuma para el rango [D5:H15],

Programa de Diseño, Publicidad y Extensión

13

Técnico en Computación

Caso práctico 02 Guardar el libro para tener registradas las ventas de los productos, en la carpeta Excel-Inicial del USB, con el nombre Ventas_2011 1. Haga clic en el menú Archivo. 2. Luego seleccione Guardar. 3. Especifique la carpeta, Excel-Inicial, del USB. 4. Digite el nombre, en este caso: Ventas_2011. 5. Y luego haga clic en el botón Guardar.

Caso práctico 03 Ahora se requiere cerrar el libro, sin cerrar Ms-Excel 1. Haga clic en el menú Archivo. 2. Luego seleccione la opción Cerrar.

14

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 04 Ahora se requiere abrir el libro Ventas 2011, que se grabo en el USB 1. Haga clic en el menú Archivo. 2. Seleccione la opción Abrir. 3. Seleccione la carpeta Excel-Inicial, del USB. 4. Seleccione el archivo que requiera abrir, en este caso: Ventas_2011. 5. Y luego haga clic en el botón Abrir.

Caso práctico 05 Verifique la otra manera de abrir un archivo utilizando la opción Reciente 1. Seleccione el menú Archivo. 2. Luego elija la opción Reciente. 3. Seleccione de la lista el archivo que requiera activar.

Programa de Diseño, Publicidad y Extensión

15

Técnico en Computación

4. Para salir de la aplicación de Excel elija la opción Salir

Caso práctico 06 Verifique la otra manera de abrir un archivo utilizando la opción Reciente. 1. Seleccione el menú Archivo. 2. Luego elija la opción Reciente. 3.

16

Seleccione de la lista el archivo que requiera activar.

Programa de Diseño, Publicidad y Extensión

Manipulando hojas de cálculo

EXCEL I 2010

Sesión 2

Técnico en Computación

Manipulando hojas de cálculo Vamos a ver los diferentes métodos de selección de celdas para poder modificar el aspecto de éstas, así como diferenciar entre cada uno de los métodos y saber elegir el más adecuado según la operación a realizar.

Operaciones básicas con hojas y formatos La hoja de cálculo es una herramienta informática, que nos ahorra muchísimo trabajo diario a todos aquellos que trabajamos habitualmente con números, cifras, y datos. Una hoja de cálculo nos puede servir para elaborar un presupuesto, para hacer facturas, para calcular las cuotas de un préstamo, para hacer un balance y una cuenta de resultados, o para llevar el control de notas de unos alumnos, por poner unos cuantos ejemplos. Todo aquello que sea cuantificable, y valorable económicamente, puede ser tratado, medido, ordenado y controlado, mediante una hoja de cálculo.

Caso práctico 01 Active el libro Ventas 2011 en donde se requiere cambiar el nombre de la Hoja1 por Datos, así como también asignarle el color de etiqueta rojo a dicha hoja. La Hoja2 se llamara Informes y el color de etiqueta será Verde. 1. La manera convencional seria: En la barra de etiquetas haga clic derecho sobre una hoja, elegir Cambiar nombre y coloque el nuevo nombre 2. La manera directa de cambiar el nombre a una hoja es:

3. Realice un procedimiento similar para la Hoja2. Y cierre el libro actual.

Caso práctico 02 Ahora active el libro Ventas_Artefactos_2011 en donde están registradas las ventas por cada campaña. Además se desea insertar una hoja nueva para realizar los resúmenes, la nueva hoja se llamara Resumen.

18

Programa de Diseño, Publicidad y Extensión

Excel I 2010

1. En la ficha Inicio, ubique el grupo Celdas, habrá el botón Insertar y seleccione la opción Insertar hoja.

2. El modo rápido es: Haga clic en el control Insertar hoja de calculo

3. Ahora cambie el nombre de la Hoja1 por el de Resumen.

4. También asígnele un color de etiqueta a su criterio.

5. Luego grabe los cambios del libro y ciérrelo.

Caso práctico 03 Ahora active el libro Ventas_Ene_Abr se desean eliminar las hojas Mayo y Junio. Guarde los cambios. 1. En la ficha Inicio, ubique el grupo Celdas, habrá el botón Eliminar y seleccione la opción Eliminar hoja. 2. El modo rápido es: Haga clic derecho en la hoja de calculo: Mayo.

Programa de Diseño, Publicidad y Extensión

19

Técnico en Computación



Luego haga clic en el botón Aceptar de la caja de confirmación.

3. El resultado será el siguiente:

Caso práctico 04 Trabajando en el libro Ventas_Ene_Abr se deben ordenar las hojas con los meses del año en forma ascendente. 1. Haga clic en la etiqueta de la hoja que desea mover y arrástrela hasta la posición requerida. 2. Arrastre la hoja Enero hasta antes de la hoja Febrero.

3. El resultado mostrará el siguiente resultado. (Guarde los cambios).

Manipular filas y columnas En el archivo Empleados_Taller.xlsx, se encuentran registrados los datos de los trabajadores del área de producción de la empresa textil Yhassir & Co.

Las operaciones de inserción dependen de las selecciones (filas, columnas, celdas) de referencia que haga el usuario. La manera formal de realzar inserciones implica utilizar de la Ficha Inicio, el Grupo Celdas y la orden Insertar, luego se elige la opción correspondiente:

20

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 05 En el libro Empleados_Taller.xlsx, en la hoja Datos, el encargado del área de Producción va a insertar dos columnas antes del turno para los apellidos paterno y materno respectivamente. 1. Seleccione las columnas D y E (columnas de referencia). 2. El modo convencional: Haga clic derecho en la selección. 3. Elija la opción Insertar.

4. El modo rápido: Con las columnas seleccionadas, pulsar:

5. Ingresamos los datos que faltan, como los encabezados para D y E, A-Paterno y A-Materno, respectivamente, luego los registros como se muestran:

Programa de Diseño, Publicidad y Extensión

21

Técnico en Computación

Caso práctico 06 En el libro Empleados_Taller.xlsx, en la hoja Datos, el encargado del área de Producción va a insertar tres nuevas filas, debajo del código TR-03. Para que se muestren de manera ordenada los registros 1. Seleccione las filas 7, 8 y 9 (filas de referencia). 2. El modo convencional: Haga clic derecho en la selección. 3. Elija la opción Insertar.

4. El modo rápido: Con las filas seleccionadas, pulsar:

5. Ingresamos los datos que faltan, en las filas 7,8 y 9 respectivamente, como se muestran. Guarde los cambios.

22

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Formatos de celda Muchas veces necesitaremos que lo mostrado en las celdas tengan determinado formato (por ejemplo, podemos necesitar que un número de una celda se muestre en color rojo si es negativo). Para modificar el formato de una celda o rango de celdas, luego de seleccionarlas, hacemos clic en un comando (característica) de los grupos: Fuente, Alineación, Numero y Estilos de la cinta de opciones correspondiente a la Ficha Inicio también es posible activar alguna caja de dialogo para elegir otras características.

Caso práctico 07 En el libro Ventas_Temporadas.xlsx, en la hoja 1ra Campaña, se deben realizar varias operaciones de cambio de formato de Fuente, Alineación y Número: 1. La hoja 1ra campaña mostrara originalmente lo siguiente:

2. La celda A1, tendrá las siguientes características: Fuente: Brush Script Std, 18, Neg, Curs, Subrayado doble.

Programa de Diseño, Publicidad y Extensión

23

Técnico en Computación

3. Seleccione el rango: [A2:H2], aplique lo siguiente: Combinar y centrar, Alinear en el medio, 16, Arial Narrow, Neg, Borde de cuadro grueso, Color de Relleno purpura claro, Color de Fuente purpura oscuro. 4. Los encabezados [A4:H4] deberán tener: Neg, Centrar, Alinear al medio, Ajustar texto, Todos los bordes, Color de Relleno azul claro, Color de Fuente azul oscuro. El ancho de la columna H será de 12.5 y en la intersección de la fila 4 y 5 haga doble clic. 5. El rango [A5:H14] junto con el rango [B15:H15] deberán tener: Todos los bordes. La celda A4 se le asigna la Orientación: Girar texto hacia arriba. Combinar y centrar el rango [B15:C15], asigne el mismo formato de los encabezados al rango combinado. Luego de los cambios de formato de fuente y alineación, se deberá obtener:

6. Los cambios de formatos de numero se aplicaran: el rango [D5:G14] tendrá el Estilo millares con un decimal, los rangos [H5:H15] y [D15:G15] se le asigna el Formato de numero de contabilidad Dólar (EE.UU) con un decimal, además de negrita y con un Color de relleno purpura claro. Como resultado se mostrará:

24

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Estilos Estilos de celda Aplica rápidamente un formato a una celda o rango de celdas con los estilos predefinidos. También se puede definir estilos propios para las celdas.

Caso práctico 08 En el libro Ventas_Temporadas.xlsx, en la hoja 2da Campaña, se deben realizar operaciones de asignación de Estilos de celda: 1. La hoja 2da Campaña mostrara lo siguiente:

2. Copiar los formatos de la A1 y A2 de la hoja 1ra Campaña hacia la hoja 2da Campaña, aplicar al rango [A4:H4] el Estilo de celda Énfasis 4, luego centrar y asignar Todos los bordes. 3. Ahora aplicar al rango [D5:G14] el Estilo de celda Moneda [0], luego centrar y asignar Todos los bordes. Los rangos [H5:H15] y [D15:G15] se le asigna el Estilo de celda 20% - Enfasis 1, Negr., el Estilo de celda Moneda, también todos los bordes. Programa de Diseño, Publicidad y Extensión

25

Técnico en Computación

4. Aplicar al rango [B5:B14] el Estilo de celda 20% - Enfasis 3, el rango [B15:C15] asigna el Estilo de celda Enfasis 4 y asigne Todos los bordes a los rangos. El resultado mostrará:

Formatos como tabla Permite aplicar rápidamente un formato a un conjunto de celdas y lo convierte en una tabla seleccionando un estilo predeterminado, esta herramienta es muy útil en el manejo de grandes volúmenes de información y también cuando se quieren manejar las tablas dinámicas.

Caso práctico 09 En el libro Ventas_Temporadas.xlsx, en la hoja 3ra Campaña, se deben realizar operaciones de asignación Dar formato como tabla: 1. La hoja 3ra Campaña mostrara lo siguiente:

26

Programa de Diseño, Publicidad y Extensión

Excel I 2010

2. Copiar los formatos de la A1 y A2 de la hoja 2da Campaña hacia la hoja 3ra Campaña, ubicarse dentro de la lista, aplicar Dar Formato como tabla: Estilo de tabla medio 5. A continuación haga clic en Aceptar de la caja de confirmación 3. En la Ficha Diseño de las Herramientas de tabla active el control Fila de totales (del grupo opciones de estilo de tabla). El rango [D5:H15] asígnele el Formato de numero de contabilidad S/. Español (Perú) 4. En las lista de la celda D15 elija Promedio, en la celda E15 elija Cuenta, en la celda F15 elija Max, en la celda G15 elija Min y en la celda H15 elija Suma.

5. Es importante considerar que cada campo se convierte una lista, donde se pueden mostrar solamente aquellos registros que cumplan con una determinada condición. (Guarde los cambios). 6. Cuando se convierte una lista en Dar formato de tabla, la lista automáticamente se nombre., también es posible reconfigurar la asignación de Formato como tabla, utilizando los controles de la Ficha Diseño con las Herramientas de tabla

Formato condicional Mediante la aplicación de formato condicional a los datos, es posible identificar rápidamente varianzas en un intervalo de valores con solo observar los formatos aplicados. Resalta celdas interesantes, enfatiza valores poco usuales y visualiza datos usando barras de datos, escalas de color y conjunto de iconos basados en criterios.

Caso práctico 10 En el libro Inventario_Almacen.xlsx, en la hoja Almacén Empleando Formato Condicional: Caso1) Poner en negrita, de color verde las Marca: Vouge. Caso 2) Poner en negrita, cursiva, rojo el tamaño Medium y de color azul, negrita el tamaño Large. Programa de Diseño, Publicidad y Extensión

27

Técnico en Computación

1. La secuencia para el Caso 1 es: a) Seleccione el rango [C2:C25], b) en el grupo Estilos active Formato condicional, c) seleccione Resaltar reglas de celdas y d) luego seleccione el evento Es igual a…

2. Luego: a) Escriba “Vouge” en el cuadro, b) Active la lista y elija Formato Personalizado, c) luego active Negrita y elija el color verde, d) Luego Aceptar y e) seleccione el botón Aceptar.

28

Programa de Diseño, Publicidad y Extensión

Excel I 2010

3. La secuencia para el Caso 2, para el formato Medium: a) Seleccione el rango [D2:D25], b) en el grupo Estilos active Formato condicional, c) seleccione Resaltar reglas de celdas, d) luego seleccione el evento Es igual a…, d1) Digite “MEDIUM”, d2) Elija Formato personalizado…, asigne negrita, cursiva y color rojo d3) Luego seleccione Aceptar.

Para el formato Large: a) Mantenga Seleccionado el rango [D2:D25], b) en el grupo Estilos active Formato condicional, c) seleccione Resaltar reglas de celdas, e) luego seleccione el evento Texto que contiene…, e1) Digite “LARGE”, e2) Elija Formato personalizado…, asigne negrita y color azul e3) Luego seleccione Aceptar.

Caso práctico 11 En el libro Inventario_Almacen.xlsx, en la hoja Almacen Empleando Formato Condicional: a). Poner el formato Barra de datos a los Stocks, b) Poner el formato Escala de color al Pre-Costo y c) Poner el formato Conjunto de iconos al Pre-Venta. 1. Para la Barra de datos: a) Seleccionado el rango [F2:F25], b) elija Formato condicional, c) señale Barras de datos y d) haga clic en Barra de datos verde.

Programa de Diseño, Publicidad y Extensión

29

Técnico en Computación

2. Para la Escala de color: a) Seleccionado el rango [G2:G25], b) elija Formato condicional, c) señale Escalas de color y d) haga clic en Escala de colores rojo, amarillo, verde.

3. Para el Conjunto de iconos: a) Seleccionado el rango [H2:H25], b) elija Formato condicional, c) señale Conjuntos de iconos y d) haga clic en 5 flechas (de color).

4. El resultado mostrara lo siguiente:

Caso Práctico 12 Aplicando un Formato condicional con Formula, en la hoja Almacen del libro Inventario_Almacen.xlsx, Poner en color azul y con relleno azul claro los nombres de los Productos donde el precio de venta sea superior a S/. 82.00. 1. La secuencia es: a) Seleccione el rango [B2:B25], b) en el grupo Estilos active Formato condicional, c) seleccione Nueva regla, d) luego seleccione el evento

30

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Utilice una fórmula que determine...., e) escriba la formula “=H2<82”, f) haga clic en el botón Formato, configure el color de texto azul, el color de relleno azul claro y Aceptar, finalmente clic en Aceptar.

Para administrar (crear, editar y eliminar) los formatos creados, solo debe ubicarse en una celda formateada e ingresar al comando Formato condicional y elegir la opción Administrar reglas.

Validación de datos La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un intervalo determinado de fechas, limitar las opciones con una lista o asegurarse de que sólo se escriben números enteros positivos.

Caso práctico 13 En el libro Inventario_Almacen.xlsx, en la hoja Personal Empleando Validación, realiza las siguientes restricciones:

CAMPO

VALORES PERMITIDOS

a

TURNO

Mañana, Tarde

b

HIJOS (tiene hijos?)

Si, No

c

FEC-NAC

Fechas menores de 1994

d

BASICO

Entre 700 y 1600

e

COMISION

No debe pasar del 8% del Básico

Programa de Diseño, Publicidad y Extensión

El tema Nombrar rangos se revisa con mayor detalle en la Unidad 3

31

Técnico en Computación

a. Para la Barra de datos: a) Ingresar datos a partir de la celda J2, b) seleccione rango [J3:J4] y c) en el control Cuadro de nombre digite “TURNO” y presione la tecla Enter.

b. Seleccione el rango a validar [C2:C8], d) En la ficha Datos, elija Validación de datos, e) Luego en el control Permitir elija Lista, f) Digite en el control Origen “Turno” y finalmente Aceptar.

c. Seleccione el rango a validar [D2:D8], active la Validación de datos y configure: en el control Permitir seleccione Lista, en el control Origen digite “Si,No” y Aceptar.

32

Programa de Diseño, Publicidad y Extensión

Excel I 2010

e. Seleccione el rango a validar [E2:E8], active la Validación de datos y configure: en el control Permitir seleccione Fecha, en el control Datos elija Menor que, en el control Fecha final digite “01/01/1994” y Aceptar.

f. Seleccione el rango a validar [F2:F8], active la Validación de datos y configure: en el control Permitir seleccione Decimal, en el control Datos elija entre, en el control Mínimo digite “700” , en el control Máximo digite “1600” y Aceptar.

f. Seleccione el rango a validar [G2:G8], active la Validación de datos y configure: en el control Permitir seleccione Personalizada, en el control Formula digite “=G2<8%*F2”, y Aceptar.

g. Toda información que no se encuentre validada no será aceptada. (Guarde los cambios). Programa de Diseño, Publicidad y Extensión

33

Técnico en Computación

34

Programa de Diseño, Publicidad y Extensión

Fórmulas y operadores

EXCEL I 2010

Sesión 3

Técnico en Computación

Fórmulas y operadores Una fórmula es una expresión que relaciona valores con operadores para producir un nuevo valor. Las fórmulas pueden tener varias formas, ya que pueden utilizar referencias, funciones, texto y nombres para realizar diferentes tareas. Para introducir fórmulas, deberá editar la celda o bien seleccionar la celda y después introducir datos. Una fórmula siempre debe comenzar con el signo igual ( = ) y puede producir diferentes resultados: Un valor concreto, un valor lógico (VERDADERO o FALSO) o un texto.

Calculos con operadores Los operadores son símbolos que identifica Excel con operaciones aritméticas y es el enlace entre dos argumentos. Los cálculos con operadores empiezan con el signo “=”.

Tipos de operadores Los operadores especifican el tipo de cálculo que se desea realizar con los elementos de una fórmula. Microsoft Excel incluye cuatro tipos diferentes de operadores de cálculo: aritmético, comparación, texto y referencia.

Operadores aritmeticos Para ejecutar las operaciones matemáticas básicas como suma, resta o multiplicación, combinar números y generar resultados numéricos, utilice los siguientes operadores aritméticos. SIGNO

OPERACIÓN

EJEMPLO

^

Exponenciación

=8^3

% / *

Porcentaje Divide Multiplica

=850*15% =42/3 =14*7

+

Suma

=53+87

-

Resta

=62-18

Operadores de comparación Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores utilizando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO. SIGNO = > < >= <= <>

36

OPERACIÓN Igual Que Mayor Que Menor Que Mayor Igual Que Menor Igual Que Distinto

EJEMPLO 5=8 21>16 43<32 14>=14 50<=49 6<>8

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Operadores de texto Utilice el signo (&) para unir o concatenar una o varias cadenas de texto con el fin de generar un solo elemento de texto. SIGNO &

OPERACIÓN “Microsoft ” & “Office”

EJEMPLO Microsoft Office

Operadores de referencia Combinan rangos de celdas para los cálculos con los siguientes operadores. SIGNO

OPERACIÓN

EJEMPLO

:

Exponenciación

=8^3

,

Porcentaje

=850*15%

Divide

=42/3

Multiplica

=14*7

(espacio) (operación)

Caso práctico 01 La empresa “Yhassir Data””, distribuidora de productos de computo, requiere realizar el control de compras, proveedores y créditos del mes de junio del presente año. Para ello se debe activar el libro Ventas_Junio, y realice los siguientes cálculos: a. En la hoja Compras, obtener el resultado de los siguientes campos:

PREVENTA = PRECOSTO x %GANA + PRECOSTO.



IMPORTE = PREVENTA x CANT.



IGV = 19% x IMPORTE.



SUBTOTAL = IMPORTE + IGV.

1. Para calcular el P-VENTA, ubicarse en la celda E4, escriba la formula =C4*D4+C4 y presione ENTER. 2. Regrese a la celda E4, señale la esquina inferior derecha, cuando aparece el control de relleno (+), arrastre hacia abajo, hasta la celda E13, para copiar la fórmula.

Programa de Diseño, Publicidad y Extensión

37

Técnico en Computación

3. Para calcular el IMPORTE, ubicarse en la celda G4, ingrese la formula correspondiente y copie la formula hasta la celda G13. 4. Para calcular el IGV, ubicarse en la celda H4, ingrese la formula, presione ENTER, regrese a la celda H4 y cuando aparezca el control de relleno (+) haga Doble Clic para copiar la formula. 5. Realice un procedimiento similar al IGV para calcular el SUBTOTAL.

b. En la hoja Proveedores, obtener el IGV y el P-COSTO a partir de Montos donde están incluidos el Impuesto General a las Ventas:

IGV = MONTO – MONTO / 1.19.



P-COSTO = MONTO – IGV.

1. Considerando que el MONTO ya tiene incluido el IGV, ubíquese en la celda D4 para obtener el IGV y en la celda E4 el P-COSTO.

C. En la hoja Créditos, considerando las formulas estándar de Interés Ganado y monto Final, realice los siguientes cálculos:

38

Programa de Diseño, Publicidad y Extensión

Excel I 2010



P = MONTO INICIAL

I = INTERES GANADO

I=P[(1+i)^n-1]



i = INTERES MENSUAL

S = MONTO FINAL

S=P(1+i)^n



n = NUM. DE PERIODOS

1. Ubíquese en la celda F4 y G4 para obtener el Interés Ganado y el Monto Final respectivo:

Guarde los cambios.

Referencias de celdas Cuando hacemos usos de fórmulas y funciones casi es seguro que coloquemos referencias a celdas o conjunto de celdas que no son propiamente la misma celda donde tenemos la formula. Las referencias son enlaces a un lugar, es decir, cuando en una formula escribimos =SUMA(A2:A5) nos estamos refiriendo a que sume el contenido desde A1 hasta la celda A5. Es muy importante considerar que cuando se copia una Formula en sentido: -

Vertical (a nivel de columnas), cambian las direcciones de las filas,

-

Horizontal (a nivel de filas), cambian las direcciones de las columnas.

Los tipos de celdas durante una copia de fórmulas son: Relativas, Absolutas y Mixtas. - RELATIVAS.- Si copiamos una fórmula con referencias relativas, la fórmula cambiará dependiendo de donde vamos a copiarla. Las direcciones de las celdas son VARIABLES. Esta es la opción que ofrece Excel por defecto.

Ej.: H5

- ABSULTAS.- Si copiamos una fórmula con referencias absolutas, la fórmula NO variará. Las direcciones de las celdas son CONSTATES.

Para asignar el símbolo dólar ($), se pulsa la tecla F4 (sobre la celda), también se puede digitar dicho símbolo. Una celda es absoluta o mixta cuando se encuentra fuera de la lista de datos

Ej.: $H$5

Programa de Diseño, Publicidad y Extensión

39

Técnico en Computación

- MIXTAS.- Si copiamos una fórmula con referencias mixtas, la fórmula cambiará la columna o la fila que no tiene el signo $ delante dependiendo de donde vamos a copiarla y no variará la que sí tiene el signo $ delante. Combina celdas absolutas con celdas relativas.

Ej.: $H5 | H$5

Caso práctico 02 La empresa “Yhassir Import””, distribuidora de muebles de oficina de computo, requiere realizar el control de ventas, del presente mes. Para ello se debe activar el libro Importadora, en la hoja Ventas, realice los siguientes cálculos, considerando que las celdas F2 y H2 deben ser absolutas: IMPORTE IGV

= CANTIDAD * VALOR VTA. = IMPORTE * 19% (F2)

PRECIO VTA. S/. = IMPORTE + IGV PRECIO VTA. $

= PRECIO VTA. S/. / TIPO DE CAMBIO (H2)

1. Ubicado en la hoja Ventas: a) seleccione la celda E4, escriba la fórmula del IMPORTER indicada y luego cópiela, b) seleccione la celda F4, escriba la fórmula del IGV (la celda F2 será absoluta) indicada y luego cópiela, c) seleccione la celda G4, escriba la fórmula del PRECIO VTA S/. indicada y luego cópiela, d) seleccione la celda H4, escriba la fórmula del PRECIO VTA $. (la celda H2 será absoluta) indicada y luego cópiela.

2. Seleccione el rango [E4:H15] ubique el botón de Autosuma ( ∑ ) y haga clic.

Caso práctico 03 La empresa “Yhassir Import””, requiere realizar la proyeccion estimada para los siguientes tres meses (febrero, marzo, abril). Ubicado en la hoja Proyeccion, realice el cálculo:

40

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Febrero = MesAnterior * %GanMesActual + MesAnterior

Considerando que en la formula la celda C$2 (GanMesActual), solo la fila 2 debe ser absoluta:

1.

Ubicado en la celda D5, escriba la fórmula: =C5*C$2+C5

2. Regrese a la celda D5 y copia la formula (hacia la derecha) hasta la celda F5 3. Con el rango [D5:F5] seleccionada, ubique el control de copia y arrastre (hacia abajo) hasta la fila 15.

Rangos de celdas Podemos dar a un rango de celdas en Excel un nombre para hacer operaciones de cálculo o validaciones de datos. Un nombre de rango es reconocido en cualquier hoja de un libro, pero también se puede asignar un nombre solo para una determinada hoja. Seleccionamos todas las celdas (continuas o discontinuas) que le vamos asignar un nombre, luego en la Ficha Formulas, seleccionamos el botón Administrador de nombres, en donde podemos: asignar, editar y hasta eliminar un nombre de rango.

Caso práctico 04 En el libro Ventas_Tienda_01.xlsx, en la hoja X Trimestre, se debe seleccionar el rango: [B4:E10] y asignarle el nombre VENTAS, utilizando el método rápida de nombrar un rango de celdas. 1. Seleccione el rango: [B4:E10] 2. Luego hacer clic en el control: Cuadro de nombres, digite VENTAS 3. Luego presione la tecla ENTER.

Programa de Diseño, Publicidad y Extensión

41

Técnico en Computación

Caso práctico 05 En el libro Ventas_Tienda_01.xlsx, en la hoja X Semana, se deben seleccionar los rangos numéricos y asignarle el nombre SEMANAS. 1. Seleccione el rango: [C7:E12]

2. Luego presione la tecla CTRL, y seleccione los otros rangos 3. Hacer clic en el control: Cuadro de nombres, digite SEMANAS 4. Luego presione la tecla ENTER.

5. Una forma de comprobar que los rangos existen, es hacer clic al control desplegable Cuadro de nombres, seleccione un nombre y compruebe su dirección: (Guarde los cambios).

42

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 06 La empresa “Yhassir Import””, requiere realizar una bonificación extraordinaria del 20% a sus trabajadores. Para ello se debe activar el libro Importadora, en la hoja Trabajadores, deberá nombrar la celda E2, esta acción es equivalente a convertir una celda en celda absoluta. 1.

Ubicado en la celda E2, haga clic en el control Cuadro de nombres, digite BONIF y presione ENTER.

2. Luego ubíquese en la celda E4, escriba la fórmula: =D4*BONIF, presione ENTER y luego copie la formula hasta la celda E18. 3. Ubíquese en la celda F4, digite la fórmula: =D4+E4, presione ENTER y luego copie la formula hasta la celda F18.

4. Seleccione el rango [E4:F18] y realice la operación de Autosuma. 5. Finalmente guarde los cambios.

Programa de Diseño, Publicidad y Extensión

43

Técnico en Computación

44

Programa de Diseño, Publicidad y Extensión

Funciones

EXCEL I 2010

Sesión 4

Técnico en Computación

Funciones Una Función es una fórmula predefinida por Excel que opera sobre uno o más valores y te muestra un resultado que aparecerá directamente en la celda introducida. En otras palabras podemos decir que una función es una fórmula predefinida que se utiliza para realizar cálculos complejos de una manera sencilla. La sintaxis de cualquier función es:

nombre_función(argumento1,argumento2,...,argumentoN) Siguen las siguientes reglas: • Si la función va al comienzo de una fórmula debe empezar por el signo =. • Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. • Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. • Los argumentos deben de separarse por una coma , Para insertar una función:

1. Ubicado en la celda donde requiere obtener un resultado. 2. Ubíquese en la Ficha Formulas, en el grupo Biblioteca de funciones, Haga clic en la lista de la categoría de función y luego seleccione la función: 3. Configure la caja de dialogo: Argumentos de la función (seleccione rangos ó indique nombres).

46

Programa de Diseño, Publicidad y Extensión

Excel I 2010

4. El resultado se mostrará en la celda:

Otra manera, más usual, de insertar una función es: 1. Ubicado en la celda donde requiere obtener un resultado. 2. Coloque el signo =, luego empiece a escribir la función. 3. Excel le mostrara, mientras escribe, un filtro con una lista de funciones relacionadas, también muestra un breve concepto de la función elegida:

4. Puede elegir la función requerida de dos maneras: a. Hace doble clic sobre la función. b. Selecciona la función y presiona la tecla TAB (recomendada). 5. Luego completa los parámetros de la función elegida y presiona ENTER. 6. Mientras completa la función Excel le muestra en un recuadro gris, una ayuda con los parámetros o argumentos de la función. 7. El resultado se mostrara en la celda correspondiente:

8. Es importante observar la barra de formulas donde se muestra el contenido real de la celda (ósea la función insertada).

Programa de Diseño, Publicidad y Extensión

47

Técnico en Computación

Funciones mas frecuentes Función Suma Suma todos los números que se encuentren en sus argumentos, no toma en cuenta las celdas en blanco tampoco las celdas que contienen texto' y Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores. Sintaxis: Los argumentos pueden ser números. nombres de celdas. Rangos de celdas que contengan números. Si el argumento es un rango de celdas. sólo se utilizarán los números. Se pasarán por alto las celdas vacías, los valores lógicos o los valores de tipo texto. Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.



SUMA(Arg1,Arg2,Arg3,..,Argn) Donde n puede ser de ser de 1 a 255.

Función Max Devuelve el valor máximo de todos los valores que intervengan en sus argumentos. Sintaxis:

MAX(Arg1,Arg2,Arg3,....,Argn) Donde n puede ser de ser de 1 a 255.

Función Min Devuelve el valor mínimo de todos los valores numéricos que intervengan en sus argumentos. Sintaxis:

MIN(Arg1, Arg2, Arg3, ... , Argn) Donde n puede ser de ser de 1 a 255.

Función Promedio Devuelve el promedio (media aritmética) de los todos los valores que se encuentran como argumentos. Sintaxis:

PROMEDIO(Arg1,Arg2,Arg3,...,Argn) Donde n puede ser de ser de 1 a 255 argumentos.

Caso práctico 01 La empresa “Breshka s.a.”, distribuidora de productos, requiere realizar la estadística de compras de los meses octubre y noviembre respectivamente. Para ello se debe activar el libro Venta Oct-Nov, en la hoja Octubre realice los siguientes cálculos:

48

Programa de Diseño, Publicidad y Extensión

Excel I 2010

1. En la hoja Octubre, realice el siguiente manejo de funciones: 2. Considerando el manejo de direcciones de rangos: CELDA

RANGO DE CELDAS

F18:

=SUMA(F2:F17)

J3:

=SUMA(E2:E17)

J4:

=MAX(E2:E17)

J5:

=MIN(E2:E17)

J6:

=PROMEDIO(E2:E17)

E30

=Suma(F2:F17)

E31

=MAX(F2:F17)

E32

=MIN(F2:F17)

E33

=PROMEDIO(F2:F17)

3. Luego de las operaciones se mostrará el siguiente resultado:

4. Luego nos ubicamos en la hoja Noviembre y nombramos los siguientes rangos: CELDA

RANGO DE CELDAS

[F2:F17]

=SUMA(F2:F17)

J3:

=SUMA(E2:E17)

5. Considerando ahora que se utiliza los nombres de rango: CELDA

RANGO DE CELDAS

F18:

=SUMA(TVEN)

J3:

=SUMA(UVEN)

J4:

=MAX(UVEN)

J5:

=MIN(UVEN)

J6:

=PROMEDIO(UVEN)

E30

=SUMA(TVEN)

E31

=MAX(TVEN)

E32

=MIN(TVEN)

E33

=PROMEDIO(TVEN)

Programa de Diseño, Publicidad y Extensión

49

Técnico en Computación

6. Luego de las operaciones se mostrará el siguiente resultado:

7. Luego Guarde los cambios.

Funciones estadisticas Función Contar Cuenta el número de celdas que contienen números, además de los números incluidos dentro de la lista de argumentos. Sintaxis:

CONTAR(Arg1,Arg2,Arg3,...,Argn)

Donde n puede ser de ser de 1 a 255 argumentos que pueden contener o hacer referencia a distintos tipos de datos, pero sólo se cuentan los números, Fechas y horas. Se pasan por alto o no los toma en cuenta a las celdas vacías, valores lógicos, texto o valores de error.

Función Contara Cuenta el número de celdas que no están vacías en un rango y los valores que hay en la lista de argumentos. Sintaxis:

CONTARA(Arg1,Arg2,Arg3,..,Argn)

Donde n puede ser de ser de 1 a 255 argumentos que representan los valores que se desea contar. Los valores pueden ser de error, texto y vacío (“”). No cuenta las celdas vacías.

Función Contar.Blanco Cuenta el número de celdas vacías (en blanco) dentro de un rango de celdas.

50

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Sintaxis:

CONTAR.BLANCO(rango) Rango es el rango dentro del cual desea contar el número de celdas en blanco.

Función Contar.Si Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen los criterios especificados. Sintaxis:

CONTAR.SI(rango,criterio)

Rango son las celdas que se van a contar cuantas veces cumple el criterio que se especifique en el segundo argumento. Criterio es el criterio en forma de número, expresión, referencia a celda o texto, Por ejemplo, los criterios pueden expresarse como 75, “75”, “<2011”, “DISEÑO” o C4.

Funcion Contar.Si.Conjunto Cuenta las celdas, dentro del rango, que cumplen los criterios especificados. Sintaxis:

CONTAR.SI.CONJUNTO(Rang1,Crit1,Rang2,Crit2,....)

Rang1,Rang2,.... son de 1 a 127 rangos en los que se van a evaluar los criterios asociados. Crit1,Crit2,.... son de 1 a 127 criterios en forma de número, expresión, referencia de celda o texto que determinan las celdas ~ue se van a contar. Por ejemplo, los criterios pueden expresarse como 75, “75”, “>75”, “DISEÑADOR” o la dirección de una Celda como 84. Funcion K.Esimo.Mayor Devuelve el k-ésimo mayor valor de un conjunto de datos. Esta función puede usarse para seleccionar un valor basándose en su posición relativa. Sintaxis:

K.ESIMO.MAYOR(matriz;k) Matriz: es la matriz o rango de datos cuyo k-ésimo mayor valor desea determinar.

K: representa la posición (a partir del mayor valor), dentro de la matriz o rango de celdas, de los datos que se van a devolver. Por ejemplo, se puede utilizar K.ESIMO.MAYOR para devolver el mayor valor de un resultado, el segundo resultado o el tercero.

Programa de Diseño, Publicidad y Extensión

51

Técnico en Computación

Función K.Esimo.Menor Devuelve el k-ésimo menor valor de un conjunto de datos. Utilice esta función para devolver valores con una posición relativa específica dentro de un conjunto de datos. Sintaxis:

K.ESIMO.MENOR(matriz;k)

Matriz: es una matriz o un rango de datos numéricos cuyo k-ésimo menor valor desea determinar. K: es la posición, dentro de la matriz o del rango de datos, de los datos que se van a devolver, determinada a partir del menor de los valores.

Función Mediana Es el número intermedio de un grupo de números; es decir, la mitad de los números son superiores a la mediana y la mitad de los números tienen valores menores que la mediana. Por ejemplo, la mediana de 2, 3, 3, 5, 7 y 10 es 4. Sintaxis:

MEDIANA(número1,[número2], ...)

Número1,número2... Número1 es obligatorio, los números siguientes son opcionales. De 1 a 255 números cuya mediana desea obtener. Observaciones: •

Si la cantidad de números en el conjunto es par, MEDIANA calcula el promedio de los números centrales. Vea la segunda fórmula del ejemplo.

•

Los argumentos pueden ser números, o nombres, matrices o referencias que contengan números.

•

Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.

•

Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.

•

Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.

Función Moda.Uno Es el número que aparece más frecuentemente en un grupo de números. Por ejemplo, la moda de 2, 3, 3, 5, 7 y 10 es 3. Sintaxis:

52

MODA.UNO(número1,[número2],...]) Programa de Diseño, Publicidad y Extensión

Excel I 2010

Número1 Obligatorio. El primer argumento para el que desea calcular la moda. Número2, ... Observaciones: •

Los argumentos pueden ser números, o nombres, matrices o referencias que contengan números.

•

Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.

•

Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.

•

Si el conjunto de datos no contiene puntos de datos duplicados, MODA.UNO devuelve el valor de error #N/A.

Caso práctico 02 En la hoja de cálculo Personal del libro Datos del Personal Se registro los datos de los trabajadores de la empresa “Breshka s.a.”, los datos que no dieron o no se acordaban esta en blanco o con el signo interrogación “?” y los trabajadores que le falta algún dato está con asterisco “*”.

1. Ubicado en la hoja Personal, crear los siguientes nombres de rango: RANGO

NOMBRE

RANGO

NOMBRE

[B2:B28]

NOMBRE

[I2:I28]

FONO

[C2:C28]

FECHA

[J2:J28]

OCUPA

[D2:D28]

SEXO

[K2:K28]

CONDI

[E2:E28]

EDAD

[L2:L28]

AFP

[F2:F28]

ESTADO

[M2:M28]

FALTA

[G2:G28]

CATEG

[N2:N28]

SUELDO

[H2:H28]

HIJO

Programa de Diseño, Publicidad y Extensión

53

Técnico en Computación

2. Luego pase a la hoja Estadisticas, ubicarse el las celdas correspondientes y realice las siguientes operaciones:

54



Rpta. 1:



C2: =CONTARA(NOMBRE)



C3: =CONTAR(EDAD)



C4: =CONTAR.BLANCO(EDAD)



Rpta. 2:



C6: =CONTARA(HIJO)



C7: =CONTAR.BLANCO(HIJO)



Rpta. 3:



C9: =CONTARA(FONO)



C10: =CONTAR.BLANCO(FONO)



Rpta. 4:



C12: =CONTAR.SI(SEXO,"M")



C13: =CONTAR.SI(SEXO,"F")



Rpta. 5:



G2: =CONTAR.SI(ESTADO,"S")



G3: =CONTAR.SI(ESTADO,"C")



G4: =CONTAR.SI(CONDI,"ESTABLE")



G5: =CONTAR.SI(CONDI,"CONTRATADO")



Rpta. 6:



G9: =CONTAR.SI(OCUPA,"DISEÑADOR")



G10: hasta G13: el manejo es el mismo, solo debe cambiar el criterio.



Rpta. 7:



C16: =CONTAR.SI(AFP,"UNION VIDA")



C17: hasta C20: el manejo es el mismo, solo debe cambiar el criterio.



Rpta. 8:



G16: =CONTAR.SI(AFP,"<>?")



G17: =CONTAR.SI(AFP,"?")

Programa de Diseño, Publicidad y Extensión

Excel I 2010



Rpta. 9:



K3: =K.ESIMO.MAYOR(SUELDO,1)



K4: y K5: la matriz es la misma, solo debe cambiar la posición.



Rpta. 10:



K10: =K.ESIMO.MENOR(SUELDO,1)



K11: y K12: la matriz es la misma, solo debe cambiar la posición.



Rpta. 11:



L16: =CONTAR.SI(FECHA,"<01/01/2005")



L17:=CONTAR.SI.CONJUNTO(FECHA,">=01/01/2006",FECHA,"<=31/12/2008")



Rpta. 12:



L20: =CONTAR.SI.CONJUNTO(EDAD,"",FONO,"")

3. Luego de realizado los cálculos y con los formatos de moneda asignados, los resultados se mostraran de la siguiente manera:

Función Summar.Si Suma las celdas del rango suma, de las filas del rango que cumplan con el criterio que se especifique en el segundo argumento. Sintaxis:

SUMAR.SI(Rango,Criterio,Rango_suma)

Rango es el rango de celdas que se desea evaluar según los especificados.

criterios

Criterio es el criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 75, "75" u>75" o ''VENDEDORA''. Programa de Diseño, Publicidad y Extensión

55

Técnico en Computación

Rango_suma son las celdas reales que se deben sumar si las correspondientes celdas incluidas en el rango cumplen los criterios.

Caso práctico 03 En la hoja de cálculo Sueldos del libro Datos del Personal se requiere totalizar los sueldos por ocupación, categoría y condición. 1. En la hoja Sueldos considerando a SUELDO como rango de suma, ingrese las siguientes funciones: Total de sueldo por Ocupación:

C4: =SUMAR.SI(OCUPA,”DISEÑADOR”,SUELDO)



C5: hasta C8: el manejo es el mismo, solo debe cambiar el criterio.



C9: sume el rango [C4:C8]

Total de sueldo por Categoría:

F4: =SUMAR.SI(CATEG,”A”,SUELDO)



F5: hasta F7: el manejo es el mismo, solo debe cambiar el criterio.



F8: sume el rango [F4:F7]

Total de sueldo por Condición:

C12: =SUMAR.SI(CONDI,”ESTABLE”,SUELDO)



C13: =SUMAR.SI(CONDI,”CONTRATADO”,SUELDO)



C14: sume el rango [C12:C13]

2. El resultado del manejo de las operaciones será:

3. Finalmente guarde los cambios.

56

Programa de Diseño, Publicidad y Extensión

Resumenes estadisticos

EXCEL I 2010

Sesión 5

Técnico en Computación

Ordenar listas Los datos en las hojas de Excel se suelen introducir de una manera desordenada, y eso a veces hace complicado tener una visión general, o tener alguna respuesta completa con facilidad. “Ordenar”, de Excel, hace más fácil tener distintas visiones de una misma hoja de cálculo. Las hojas de cálculo de Excel permiten ordenar columnas por orden alfabético, si se trata de palabras, o de menor a mayor (o a la inversa) si se trata de números. La forma más sencilla de ordenar en orden alfabético normal y en orden inverso es mediante los botones “orden ascendente” y “orden descendente”. Se pueden ordenar las filas completas o sólo algunas celdas seleccionadas, según la primera columna de la selección. Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres de los clientes en orden alfabético, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar las decisiones más favorables para la empresa.

Caso práctico 01 La empresa textil “Breshka s.a.” tiene en cartera clientes nacionales e internacionales, es importante tener un registro ordenado y filtrado de los mismos. Inicialmente se requiere tener ordenado la lista de los clientes por País (en la hoja de cálculo ORDEN01 del Libro Listado_Clientes). 1. Seleccione toda (CTRL + E) la lista de la hoja ORDEN01. 2. En la Ficha Datos, en el grupo Ordenar y Filtrar seleccione el botón Ordenar.

3. Configure los controles: Ordenar por: seleccione el campo PAIS, Ordenar según: Valores, Criterio de ordenación: Ordenar de A a Z y finalmente Aceptar.

58

Programa de Diseño, Publicidad y Extensión

Excel I 2010

4. El resultado mostrará la lista ordenada por el campo PAIS:

Caso práctico 02 En la hoja ORDEN02 se desea tener ordenado la lista de clientes por Apellido Paterno, Apellido Materno y Nombre. 1. Seleccione toda (CTRL + E) la lista de la hoja de cálculo ORDEN02. 2. En la Ficha Datos, en el grupo Ordenar y Filtrar seleccione el botón Ordenar. 3. Luego configure los controles: a) seleccione el campo SEXO, en forma ascendente (A a Z), b) Agregar dos niveles, c) seleccione PAIS, en forma descendente (Z a A), seleccione DEPART./REG en forma ascendente (A a Z) y finalmente Aceptar.

Programa de Diseño, Publicidad y Extensión

59

Técnico en Computación

4. El resultado mostrará lo siguiente:

Caso práctico 03 Se desea tener ordenado del más joven al cliente de más edad en la hoja de cálculo ORDEN03. 1. Seleccione toda (CTRL + E) la lista de la hoja de cálculo ORDEN03. 2. En la Ficha Datos, en el grupo Ordenar y Filtrar seleccione el botón Ordenar. 3. Luego configure los controles: a) Orden Por: seleccione FECHA NACIM., Luego en el Criterio de ordenación seleccione De más recientes a más antiguos y finalmente Aceptar.

60

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 04 En la hoja de cálculo ORDEN04 se tiene la lista de datos, donde al PAIS se le aplicó un formato de relleno color de acuerdo a la frecuencia de compra que se indica en la siguiente tabla. TIPO DE CLIENTE

COLOR APLICADO

Compra Frecuentemente

Azul

Compra Habitual

Rojo

Compra Ocasional

Verde

1. Seleccione toda (CTRL + E) la lista. 2. En la Ficha Datos, en el grupo Ordenar y Filtrar seleccione el botón Ordenar. 3. Luego configure los controles: a) Orden Por: seleccione PAIS., b) Luego agregar dos niveles, también elija PAIS, c) en criterio de ordenación elija los colores Azul, Rojo y Verde, finalmente Aceptar.

Autofiltro de listas Los datos filtrados solamente muestran las filas que cumplen los criterios que haya especificado y ocultan las filas que no desea ver. Después de filtrar los datos, puede copiar, buscar, modificar, aplicar formato, representar mediante gráficos e imprimir el subconjunto de datos filtrados sin tener que volver a organizarlo ni moverlo. También puede filtrar por más de un columna. Los filtros son aditivos, lo que significa que cada filtro adicional se basa en el filtro actual y además reduce el subconjunto de datos.

Caso práctico 01 En la hoja de cálculo AFILTRO1 se desea que la lista muestre solo a los clientes de Argentina, Perú y que sean del sexo femenino, para ofrecerles un nuevo producto que es exclusivamente para damas. 1. Haga clic en cualquier celda de las cabeceras de la hoja AFILTRO1. 2. En la Ficha Datos, en el grupo Ordenar y Filtrar. 3. Luego haga clic en el comando Filtro. Programa de Diseño, Publicidad y Extensión

61

Técnico en Computación

4. Luego en el campo PAIS haga clic en la flecha del encabezado de columna. 5. En la lista de valores, desactive todo (Con Seleccionar todo), y luego active el check de Argentina y España. 6. Luego haga clic en Aceptar.

7. Luego seleccione el campo SEXO, solo marque la F (femenino), también seleccione la opción Ordenar de A a Z y luego Aceptar.

8. Seleccione nuevamente el filtro del campo PAIS, elija la opción Ordenar de A a Z y finalmente Aceptar.

62

Programa de Diseño, Publicidad y Extensión

Excel I 2010

9.

Como resultado tendremos una lista filtrada y ordenada por PAIS y que solo muestra los registros del sexo Femenino:

Caso práctico 02 En la hoja de cálculo AFILTRO2 se desea tener una lista de todos los clientes que en su nombre tenga como carácter la C y que las compras realizadas sean de color de relleno Rojo. 1. Indique la orden de FILTRO. En él un campo APELLIDO PATERNO haga clic en la flecha de filtro. 2. Luego seleccione Filtro de texto haga clic en el evento Contiene…

3. En la caja Autofiltro personalizado, escriba en el control Contiene la letra “C” y luego Aceptar.

Programa de Diseño, Publicidad y Extensión

63

Técnico en Computación

4. Luego active el filtro de COMPRAS, seleccione Filtrar por Color, luego elija el color ROJO y Aceptar.

5. El resultado será el siguiente:

Caso práctico 03 En la hoja AFILTRO3 se requiere filtrar los clientes cuya compra se encuentre en el intervalo de 1500 “a” 2000. Luego ordene el filtro de mayor a menor. Solución: 1. Haga clic en cualquier celda de la lista de la hoja AFILTRO3. 2. Indique la orden de FILTRO, active el filtro del campo COMPRAS, elija Filtro de número y luego la opción Entre…

64

Programa de Diseño, Publicidad y Extensión

Excel I 2010

3. Configure la caja de dialogo, digite (o seleccione) los valores 1500 y 2000 respectivamente, como se muestra y luego Aceptar.

4. Luego de ordenar de mayor a menor, el resultado se mostrara de la siguiente manera:

5. Finalmente guarde los cambios.

Manejo de subtotales Este comando se utiliza para calcular automáticamente subtotales y totales generales en una lista de una. Subtotales. Se calculan con un resumen, por ejemplo, Suma o Promedio, mediante la función SUBTOTALES. Puede mostrar más de un tipo de función de resumen para cada columna. Totales generales. Son los valores del total general se obtienen a partir de los datos de detalle y no de los valores de las filas de subtotales. El comando Subtotal también esquematiza la lista de modo que pueda mostrar u ocultar las filas de detalle de cada subtotal.

Programa de Diseño, Publicidad y Extensión

65

Técnico en Computación

Caso práctico 01 En la hoja de cálculo SUBTOT01 del libro Listado_Personal.xlsx se desea crear un informe detallado que muestre el total de trabajadores por sexo. 1. Ordenar la lista por el capo SEXO, en orden ascendente. 2. Luego en la Ficha Datos, en el grupo Esquema, haga clic en Subtotal. 3. Luego en el cuadro Subtotales en Para cada cambio en seleccione SEXO. 4. En función Elija Cuenta. 5. Y en agregar subtotales seleccione por ejemplo SEXO. Luego Aceptar.

6. Y como resultado tendríamos la siguiente lista.

7. Considerar que se crean tres (3) niveles de subtotales que se pueden manipular para contraer o expandir los subtotales según sea el caso.

66

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 02 Con los datos de la hoja de cálculo SUBTOT02, crear un informe que muestre el Mínimo, promedio y Máximo sueldo que tienen los trabajadores contratados y estables. 1. Primero ordene la lista por la columna CONDICIÓN. 2. Luego crear: a) un subtotal con el máximo sueldo para la CONDICIÓN, b) crear el subtotal para el mínimo sueldo, desactive el control Reemplazar subtotales actuales, y c) crear el subtotal para el promedio del sueldo, desactive el control Reemplazar subtotales actuales,

3. Como resultado obtendremos lo siguiente:

4. Considerar que ahora se tienen cuatro (4) niveles de subtotales. Programa de Diseño, Publicidad y Extensión

67

Técnico en Computación

Caso práctico 03 Con los datos de la hoja de cálculo SUBTOT03, crear un informe que muestre el número de trabajadores por SEXO y CONDICION 1. Ordenar La lista por SEXO y CONDICIÓN. 2. Luego aplique: a) un subtotal de la suma del BASICO por SEXO, b) luego un subtotal de la suma del BASICO por CONDICION pero al aplicar la cuenta a CONDICION, desactive el control Reemplazar subtotales actuales. Como se muestra en las siguientes imágenes:

3. Y el resultado activando el nivel 3 de los subtotales seria el siguiente:

4. Para anular un subtotal debe indicar la orden y en la caja de Subtotales, hacer clic en el botón Quitar todos.

68

Programa de Diseño, Publicidad y Extensión

Evaluación 01

Práctica calificada en Laboratorio

EXCEL I 2010

Sesión 6

Técnico en Computación

70

Programa de Diseño, Publicidad y Extensión

Funciones lógicas

EXCEL I 2010

Sesión 7

Técnico en Computación

Funciones lógicas Pueden utilizarse las funciones lógicas para ver si una condición es cierta o falsa o, para comprobar varias condiciones.

Función Si Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Sintaxis:

SI(prueba_lógica,valor_si_verdadero,valor_si_falso)

Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO. Observaciones: •

Es posible anidar hasta siete funciones SI como argumentos valor_si_ verdadero y valor_si_falso para construir pruebas más elaboradas.

•

Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.

•

Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI.

Funciones Si Anidadas Excel tiene la posibilidad de anidar las funciones SI(), es decir que en caso de ser necesario, podemos volver a plantear nuevamente un Si(), en caso de que se cumpla o no la condición planteada para el primero y así sucesivamente. Es posible anidar hasta siete funciones SI(), todo depende de la cantidad posible de salidas a contemplar. Dicho de otra forma un SI() permite obtener dos salidas posibles, una para el caso verdadero y otra para el caso falso; pero si tuviéramos que contemplar tres posibles salidas o resultados, no nos alcanzaría con un SI() y deberíamos utilizar otro en la misma fórmula.

Caso práctico 01 Abrir el libro Registro_Plantilla y Ventas, en la hoja de calculo Planilla deberá realizar los cálculos de remuneraciones, descuentos y totales, la hoja original se muestra de la siguiente manera:

72

Programa de Diseño, Publicidad y Extensión

Excel I 2010

-

Asignación Familiar: es el 8% del básico si tiene hijos.

-

Tiempo de Servicio: es el 20% del básico si tiene a partir de 10 años, en caso contrario será 10% del básico.

-

Incentivos: es el 15% del básico para todos.

-

Movilidad. Si tiene menos de 10 años recibirá 9%, de lo contrario, si es mas antiguo recibirá el 18% del básico.

-

Refrigerio: si tiene de 10 años a mas recibirá el 20%, sino recibirá el 14% del básico.

-

TOTAL REMUNERACION: Es la suma de todas las remuneraciones.

-

AFP: es el 13.8% para todos por estar en la misma AFP.

-

Adelanto: si el tiempo de servicio es mayor igual a 12 será del 20% del básico.

-

Tardanzas: si tiene mas de 99 minutos es el 15% del básico.

-

TOTAL DESCUENTO: es la suma de todos los descuentos.

-

NETO PAGAR: es el TOTAL REMUNERACION – TOTAL DESCUENTO.

1. Ubicado en la hoja Planilla, seleccione la celda correspondiente, 2. escriba las operaciones y luego copie la formula: - I3: =SI(F3>0,8%*H3,0) - J3: =SI(E3>=10,20%,10%)*H3 - K3: =15%*H3 - L3: =SI(E3<10,9%,18%)*H3

Programa de Diseño, Publicidad y Extensión

73

Técnico en Computación

- M3: =SI(E3>=10,20%,14%)*H3 - N3: =SUMA(H3:M3) - O3: =13.8%*H3 - P3: =SI(E3>=12,20%*H3,0) - Q3: =SI(G3>99,15%*H3,0) - R3: =SUMA(O3:Q3) - S3: =N3-R3 3. El resultado mostrará lo siguiente:

Caso práctico 02 En el mismo el libro Registro_Plantilla y Ventas, active la hoja de cálculo Ventas deberá realizar los siguientes cálculos: En el campo EVALUACION, se debe considerar: Si el PROMEDIO de ventas es mayor igual a 150 se deberá mostrar el mensaje FAVORABLE, en caso contrario se mostrará DESFAVORABLE En el campo OBSERVACION, se debe considerar: Si el PROMEDIO se encuentra entre:

74

- 90 – 120

INSUFICIENTE

- 121- 150

REGULAR

- 151 - 180

NOTABLE

- 181 - Mas

EXCELENTE

Programa de Diseño, Publicidad y Extensión

Excel I 2010

1. Seleccione la celda correspondiente, escriba las operaciones y luego copie la formula: - G3: =PROMEDIO(D3:F3) - H3: =SI(G3>=150,"FAVORABLE","DESFAVORABLE") - I3: =SI(G3<=120,"INSUFICIENTE",SI(G3<=150,"REGULAR",SI(G3<=180, "NOTABLE","EXCELENTE"))) 2. El resultado mostrará lo siguiente:

3. Finalmente guarde los cambios

Función Y Devuelve VERDADERO si todos los argumentos son VERDADERO, devuelve FALSO si uno o más argumentos son FALSO. Sintaxis:

Y(valor_lógico1,valor_lógico2, ...)

Valor_lógico1, Valor_lógico2, ... son entre 1 y 30 condiciones que se desea comprobar y que pueden ser VERDADERO o FALSO. Observaciones: •

Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o los argumentos deben ser matrices o referencias que contengan valores lógicos.

Programa de Diseño, Publicidad y Extensión

75

Técnico en Computación

•

Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto.

•

Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!.

Tabla Lógica Y P

Q

PYQ

V

V

V

V

F

F

F

V

F

F

F

F

Función O Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO. Sintaxis:

O(valor_lógico1;valor_lógico2; ...)

Valor_lógico1;valor_lógico2,... son entre 1 y 30 condiciones que desea comprobar y que pueden ser VERDADERO o FALSO. Observaciones: •

Los argumentos deben evaluarse como valores lógicos, como VERDADERO O FALSO, o en matrices o referencias que contengan valores lógicos.

•

Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto.

•

Si el rango especificado no contiene valores lógicos, O devolverá el valor de error #¡VALOR!

•

Puede utilizar la fórmula matricial O para comprobar si un valor aparece en una matriz. Para introducir una fórmula matricial, presione CTRL+MAYÚS+ENTRAR.

Tabla Lógica O

76

P

Q

POQ

V

V

V

V

F

V

F

V

V

F

F

F Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 01 Activar el libro Trabajadores, en la hoja de calculo Personal01, resolver la validación de acuerdo al Turno, tipo y Sexo: Validación-1: Mostrar Verdadero para los trabajadores del turno N (Noche) y de sexo F (Femenino) Validación-2: Mostrar Verdadero para los trabajadores del turno T (Tarde), de sexo M (Masculino) y de Tipo sea R 1. Ubicado en la celda F3, ingrese la formula:

=Y(C3="N",E3="F")

2. Luego copie la formula hasta la fila 22. 3. Ubicado en la celda G3, ingrese la formula:

=Y(C3="T",E3="M",D3="R")

4. Luego copie la formula hasta la fila 22 5. El resultado mostrara lo siguiente:

Programa de Diseño, Publicidad y Extensión

77

Técnico en Computación

Caso práctico 02 Ubicado en la hoja de calculo Personal02, resolver la validación de acuerdo al Turno, tipo y Sexo Validación-A: Mostrar Verdadero para los trabajadores del turno N o que son del sexo F Validación-B: Mostrar Verdadero para los trabajadores del turno N o del sexo F o el tipo sea C 1. Ubicado en la celda F3, ingrese la formula:

=O(C3="N",E3="F")

2. Luego copie la formula hasta la fila 22. 3. Ubicado en la celda G3, ingrese la formula:

=O(C3="N",E3="F",D3="C")

4. Luego copie la formula hasta la fila 22 5. El resultado mostrara lo siguiente:

78

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 03 Ubicado en la hoja de calculo Personal03, determinar el jornal de acuerdo a las siguientes indicaciones: Si el trabajador labora en el Turno "N" y es de Tipo "C" se le asigna un Jornal de S/. 20.00, en caso contrario su Jornal será de S/. 15.00 1. Ubicado en la celda E4, escriba la siguiente función, considerando a las celdas C2 y D2 como celdas absolutas

=SI(Y(C4="N",D4="C"),$C$2,$D$2)

2. Luego copie la formula hasta la fila 23. 3. El resultado mostrara lo siguiente:

Programa de Diseño, Publicidad y Extensión

79

Técnico en Computación

Caso práctico 04 Ubicado en la hoja de calculo Personal04, determinar las horas de trabajo de acuerdo a la siguiente tabla:

Cat

Turno

Hrs

1

T

4

Sino

 

6

1. Ubicado en la celda E5, escriba la siguiente función, considerando a las celdas E2 y E3 como celdas absolutas

=SI(Y(C5=1,D5="T"),$E$2,$E$3)

2. Luego copie la formula hasta la fila 24. 3. El resultado mostrara lo siguiente:

4. Finalmente guarde los cambios.

80

Programa de Diseño, Publicidad y Extensión

Funciones de busqueda

EXCEL I 2010

Sesión 8

Técnico en Computación

Funciones de busqueda Función Consultav Puede usar la función CONSULTAV para buscar un valor específico en la primera columna de un rango de celdas y, a continuación, se devuelve un valor de la columna de la misma fila que se especifique en su tercer argumento. Sintaxis:

CONSULTAV(Valor_buscado,Matriz_busqueda,Indice,[Ordenado])

•

Valor_buscar. Obligatorio. Valor que se va a buscar en la primera columna de la tabla o rango. El argumento valor_buscado puede ser un valor o una referencia.

•

matriz_busqueda. Obligatorio. El rango de celdas que contiene los datos. Los valores de la primera columna de matriz_busqueda son los valores que busca valor_buscado. Estos valores pueden ser texto, números o valores lógicos.

•

indicador_columnas. Obligatorio. Un número de columna del argumento matriz_busqueda desde la cual debe devolverse el valor coincidente.

•

ordenado. Opcional. Un valor lógico que especifica si CONSULTAV va a buscar una coincidencia exacta (VERDADERO / 1) o aproximada (FALSO / 0).

Si ordenado se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado. Importante. Si ordenado se omite o es VERDADERO, los valores de la primera columna de matriz_busqueda deben estar clasificados según un criterio de ordenación ascendente; en caso contrario, es posible que CONSULTAV no devuelva el valor correcto. Si el argumento ordenado es FALSO, CONSULTAV sólo buscará una coincidencia exacta. Si hay dos o más valores en la primera columna de matriz_buscar_en que coinciden con el argumento valor_búsqueda, se usará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

Función Consultah

La H de CONSULTAH significa “Horizontal”.

Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use CONSULTAH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se halle dentro de un número especificado de filas. Use CONSULTAV cuando los valores de comparación se encuentren en una columna a la izquierda de los datos que desee encontrar.

82

CONSULTAH(Valor_buscado,Matriz_busqueda,Indice,[Ordenado])

Programa de Diseño, Publicidad y Extensión

Excel I 2010

•

Valor_buscado Obligatorio. El valor que se busca en la primera fila de la tabla. Valor_buscado puede ser un valor, una referencia o una cadena de texto.

•

Matriz_buscar_en Obligatorio. Una tabla de información en la que se buscan los datos. Use una referencia a un rango o el nombre de un rango. Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos.



Si ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_ en deben colocarse en orden ascendente: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, CONSULTAH puede devolver un valor incorrecto. Si ordenado es FALSO, no es necesario ordenar matriz_buscar_en.

•

Indicador_filas Obligatorio. El número de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor que 1, CONSULTAH devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_ buscar_en, CONSULTAH devuelve el valor de error #¡REF!.

•

Ordenado Opcional. Un valor lógico que especifica si CONSULTAH debe localizar una coincidencia exacta o aproximada. Si es VERDADERO o se omite, devolverá una coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor mayor que sea inferior a valor_buscado. Si es FALSO, CONSULTAH encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error #N/A.

Funciones anidadas En algunos casos, puede que deba utilizar una función como uno de los argumentos (argumento: valores que utiliza una función para llevar a cabo operaciones o cálculos. El tipo de argumento que utiliza una función es específico de esa función. Los argumentos más comunes que se utilizan en las funciones son números, texto, referencias de celda y nombres.) de otra función. Por ejemplo, la siguiente fórmula utiliza una función anidada PROMEDIO y compara el resultado con el valor 50.

Resultados válidos. Cuando se utiliza una función anidada como argumento, deberá devolver el mismo tipo de valor que el que utilice el argumento. Por ejemplo, si el argumento devuelve un valor VERDADERO o FALSO, la función anidada deberá devolver VERDADERO o FALSO. Si éste no es el caso, Microsoft Excel mostrará el valor de error #¡VALOR! Límites del nivel de anidamiento. Una fórmula puede contener como máximo siete niveles de funciones anidadas. Si la Función B se utiliza como argumento de la Función A, la Función B es una función de segundo nivel. Por ejemplo, la función PROMEDIO y la función SUMA son ambas funciones de segundo nivel porque son argumentos de la función SI. Una función anidada dentro de la función PROMEDIO será una función de tercer nivel, etc. Programa de Diseño, Publicidad y Extensión

83

Técnico en Computación

Caso práctico 01 Abrir el libro Consultas, en la hoja de cálculo Productos deberá realizar la consulta de los productos según su código, además deberá validar el ingreso de códigos, también el rango de lista deberá convertirse en tabla. 1. Ubicado en la hoja Productos, seleccione una celda dentro del rango. 2. En el grupo Estilos, active la lista Dar Formato como tabla, luego seleccione Estilo de tabla Medio 2. Se recomienda realizar los Formatos de tabla cuando se requiere hacer Consultas de datos.

3. A continuación Aceptar el mensaje de confirmación, debe considerar que la lista se ha convertido en una tabla llamada Tabla1. A cada columna de la Tabla1 Excel le asigna (internamente) un índice correlativo.

84

Programa de Diseño, Publicidad y Extensión

Excel I 2010

4. Luego seleccione el rango [A3:A14] y asígnele el nombre CODIGO. 5. A continuación ubíquese en la celda C3 y realice la validación con el rango CODIGO, el resultado mostrara:

6. Luego ingrese las formulas en las celdas correspondientes: - H5: =CONSULTAV(G3,Tabla1,2,1) - H6: =CONSULTAV(G3,Tabla1,3,1) - H7: =CONSULTAV(G3,Tabla1,5,1) - K5: =CONSULTAV(G3,Tabla1,4,1) - K6: =K5*2.78 7. El resultado mostrara lo siguiente, por ejemplo, cuando seleccione el código PR-012.

8. Es importante validar la celda G3, para evitar un dato o código inexistente. 9. Comprobar que al ingresar uno o más registros nuevos a partir de la fila 15 de la hoja, estos se pueden consultar automáticamente. Debido a que la el rango o matriz de búsqueda en una tabla.

Caso práctico 02 Ahora seleccione la hoja de cálculo Marcas, donde deberá realizar la consulta de las Marcas, para obtener datos como ganancia, margen, etc. Debe validar la celda donde ingrese la Marca a consultar.

Programa de Diseño, Publicidad y Extensión

85

Técnico en Computación

1. Seleccione el rango [B1:E4] y asígnele el nombre: RESUMEN, considerar que ahora cada fila de la lista se convierte en un índice. Cuando los registros de una lista están en sentido vertical NO se puede utilizar Formato de tabla.

2. Luego seleccione el rango [B1:E1] y asígnele el nombre: MARCAS 3. A continuación validar la celda B7, que permita una Lista, con el contenido del rango MARCAS. 4.

Luego ingrese las formulas en las celdas correspondientes - B9: =CONSULTAH(B7,RESUMEN,2,0) - B10: =CONSULTAH(B7,RESUMEN,3,0) - D9: =B10*2.77 - D10: =CONSULTAH(B7,RESUMEN,4,0)

5. El resultado mostrara lo siguiente, por ejemplo, cuando seleccione la Marca Imaco:

6. Guarde los cambios y cierre el libro.

Caso práctico 03 La empresa Yhassir & Co. desea calcular los gastos de la planilla del mes actual y luego realizar las consultas de trabajadores por código. Para elle debe activar el libro Planilla, en la hoja planilla debe realizar las operaciones de calculo correspondiente y convertir la lista en Formato de tabla.

86

Programa de Diseño, Publicidad y Extensión

Excel I 2010

1. Ubicado en la hoja Planilla, realizar los cálculos correspondientes a Remuneraciones, Descuentos y Neto a pagar.

2. En la hoja Planilla, seleccione el rango [O4:P6] y crear el rango CONDICION, en este caso solo se nombra el rango de búsqueda para que posteriormente la función CONSULTAV reemplace a la función SI.

3. Ingrese las siguientes formulas y luego los deberá copiar: - Sueldo Básico = se obtiene según la CONDICION. - F3: =CONSULTAV(C3,CONDICION,2,0). - Asignación Familiar = 8% Solo para los que tienen Hijos. - G3: =SI(E3>0,8%*F3,0). - Tiempo De Servicio = 15% del básico para los que tienen de 10 a mas años de antigüedad, caso contrario 8% del básico. - H3: =SI(D3>=10,15%,8%)*F3. - TOTAL REMUNERACIÓN = Suma de Remuneraciones. - I3: =SUMA(F3:H3). - AFP = 5% para los estables, 4% para los contratados del Basico. - J3: =SI(C3="ESTABLE",5%,SI(C3="CONTRATADO",4%))*F3. - Adelanto = 10% del básico solo para los Estables.

Programa de Diseño, Publicidad y Extensión

87

Técnico en Computación

- K3: =SI(C3="ESTABLE",10%,0)*F3 - TOTAL DE DSCTO. = Suma de Descuentos - L3: =SUMA(J3:K3) - NETO PAGAR = TOTAL REMUN. – TOTAL DSCTO. - M3: =I3-L3 4. El resultado mostrara los siguientes resultados:

5. Seleccione el rango [A2:M17] y conviértalo en Formato de tabla, el estilo queda a su criterio, Aceptar la caja de confirmación.

6.

Seleccione el rango [A3:A17] y asígnele el nombre CODIGO.

7. Ahora ubíquese en la Hoja Consulta, Valide la celda D2 y conviértalo en una Lista 8. Luego ingrese las siguiente formulas: - B6: =CONSULTAV(D2,Tabla1,2,1) - B9: =CONSULTAV(D2,Tabla1,3,1) - D9: =CONSULTAV(D2,Tabla1,4,1)

88

Programa de Diseño, Publicidad y Extensión

Excel I 2010

- F9: =CONSULTAV(D2,Tabla1,5,1) - H9: =CONSULTAV(D2,Tabla1,6,1) - B12: =CONSULTAV(D2,Tabla1,7,1) - D12: =CONSULTAV(D2,Tabla1,8,1) - F12: =CONSULTAV(D2,Tabla1,9,1) - B15: =CONSULTAV(D2,Tabla1,10,1) - D15: =CONSULTAV(D2,Tabla1,11,1) - 15: =CONSULTAV(D2,Tabla1,12,1) - H15: =CONSULTAV(D2,Tabla1,13,1) 9. El resultado mostrara, al elegir el código 7, lo siguiente:

10. Puede comprobar que cuando ingresa más registros de trabajadores en la hoja Planilla, se actualiza la Consulta. 11. Guarde los cambios.

Programa de Diseño, Publicidad y Extensión

89

Técnico en Computación

90

Programa de Diseño, Publicidad y Extensión

Evaluación 02

EXCEL I 2010

Sesión 9

Técnico en Computación

92

Programa de Diseño, Publicidad y Extensión

Funciones

EXCEL I 2010

Sesión 10

Técnico en Computación

Funciones de Texto Las funciones de texto permitirán realizar operaciones con caracteres o con parte de un valor de tipo texto, en esta sesión veremos las funciones principales de Texto.

Función Izquierda Devuelve el primer carácter o caracteres de una cadena de texto, segun el número de caracteres que especifique en su segundo argumento. Sintaxis:

IZQUIERDA(texto,núm_de_caracteres) Texto Es la cadena de texto que contiene los caracteres que se desea extraer.

Núm_de_caracteres Aquí se indica el número de caracteres que se desea extraer desde el primer carácter izquierdo del texto. • Núm_de_caracteres debe ser mayor o igual a cero. • Si num_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolverá todo el texto. • Si num_de_caracteres se omite, se calculará como 1.

Función Derecha La función derecha devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres que se especifique en el segundo argumento. Sintaxis:

DERECHA(texto, núm_de_caracteres) Texto Es la cadena de texto, que contiene los caracteres que se desea extraer.

Núm_de_caracteres Aquí se especifica el número de caracteres de la derecha que se desea extraer. Observaciones:

94

•

Núm_de_caracteres debe ser mayor o igual a cero.

•

Si núm_de_caracteres es mayor que la longitud del texto, DERECHA devolverá todo el texto.

•

Si núm_de_caracteres se omite, se calculará como 1.

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Función Med Devuelve un número específico de caracteres de una cadena de texto, empezando en la posición que especifique en el segundo argumento, la cantidad de caracteres hacia la derecha indicado en el tercer argumento. Sintaxis:

MED(texto, posición_inicial, núm_de_caracteres) Texto Es la cadena de texto que contiene los caracteres que se desea extraer.

Posición_inicial Posición del primer carácter que se desea extraer del texto. La posición inicial del primer carácter de texto es 1, Y así sucesivamente, Núm_de_caracteres Aquí se indica el número de caracteres que se desea extraer desde la posición inicial hacia la derecha. Observaciones: •

Si posición_inicial es mayor que la longitud de texto, MED devuelve “” (texto vacío).

•

Si posición_inicial es menor que la longitud de texto, pero núm_de_caracteres excede la longitud de texto, MED devuelve los caracteres hasta el final de texto.

•

Si posición_inicial es menor que 1, MED devuelve el valor de error #¡VALOR!

•

Si núm_de_caracteres es negativo, MED devuelve el valor de error #iVALOR!

Función Hallar HALLAR busca una cadena de texto dentro de una segunda cadena de texto y devuelven el número de la posición inicial de la primera cadena de texto desde el primer carácter de la segunda cadena de texto. No distinguen mayúsculas y minúsculas. Es importante destacar que la función ENCONTRAR funciona de manera similar que la función HALLAR pero ENCONTRAR realiza el diferenciado entre mayúsculas y minúsculas. Sintaxis:

HALLAR(texto_buscado,dentro_de_texto,núm_inicial) Texto_buscado es el texto que desea encontrar. Dentro_del_texto es el texto en el que se requiere encontrar el texto buscado.

Núm_inicial es el número de carácter dentro deltexto donde desea iniciar la búsqueda. Observaciones: •

Utilice HALLAR para determinar la ubicación de un carácter o una cadena de texto dentro de otra cadena de texto.

Programa de Diseño, Publicidad y Extensión

95

Técnico en Computación

•

Si no se puede hallar el argumento texto_buscado, la función devuelve el valor de error #iVALOR!.

•

Si el argumento núm_inicial se omite, el valor predeterminado es 1.

•

Si el valor del argumento núm_inicial no es mayor que O (cero), o si es mayor que la longitud del argumento dentro_del_texto, se devuelve el valor de error #¡VALOR!.

Función largo Devuelve el número de caracteres de una cadena de texto. Sintaxis:

LARGO(texto)

Texto es el texto cuya longitud desea saber. Los espacios se cuentan como caracteres.

Función Valor Permite convierte una cadena de texto en un número, si los caracteres del texto se puede representar en un número. Sintaxis:

VALOR(texto)

Texto es el texto entre comillas o una referencia a una celda que contiene el texto que se desea convertir. Observación: El argumento texto puede tener cualquiera de los formatos de número constante, fecha u hora reconocidos por Microsoft Excel. Si no tiene uno de estos formatos, VALOR devuelve el valor de error #¡VALOR!

Función Concatenar Une varios elementos de texto en uno solo. Sintaxis:

CONCATENAR (texto1,texto2; ...)

Texto1, texto2, ... son de 1 a 30 elementos de texto que se unirán en un elemento de texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas únicas. Observaciones: Puede utilizar el operador “&” (ampersat) en lugar de CONCATENAR para unir elementos de texto.

96

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 01 1. Demostrar el manejo de las diferentes funciones de texto que se han revisado líneas arriba, para ello abrir el Libro Manejo_ de_Textos y en la hoja Funciones desarrollar las operaciones indicadas:

2. Ahora ingrese las siguientes fórmulas según las celdas indicadas: - A5:=IZQUIERDA(B2,8). - C5:=DERECHA(B2,11). - C7:=MED(B2,11,9). - D12:=VALOR(IZQUIERDA(B9,2))+2000. - D14:=C7&” “&C5. - D16:=HALLAR(“:”,B2). - D18:=LARGO(B2). 3. El resultado de la operaciones efectuadas mostrará lo siguiente:

4. Guardar los cambios realizados. Programa de Diseño, Publicidad y Extensión

97

Técnico en Computación

Caso práctico 02 La empresa Yhassir & Co. Requiere realizar un control de su personal dela Sección Taller de tal manera que a partir de un código generado se pueda obtener: a. Determinar el nombre del Area a la que pertenece el trabajador según la tabla de datos, teniendo como referencia el tercer carácter del código. b. Determinar el sueldo básico del trabajador según la tabla de datos, teniendo como referencia el tercer carácter del código. 1. Activar el libro Relacion_Personal, luego seleccione la hoja Taller, seleccione el rango [G2:I4] y asígnele el nombre DATOS.

2. Luego ubicado en la celda D2, ingrese la siguiente fórmula: =MED(A2,3,1) 3. Luego de copiar la fórmula, analice el resultado que mostrará lo siguiente:

4. Vuelva a activar la celda D2 y complete la fórmula, utilizando la función CONSULTAV, que en este caso reemplaza el uso de la función SI: =CONSULTAV(MED(A2,3,1),DATOS,2,1)

98

Programa de Diseño, Publicidad y Extensión

Excel I 2010

5. Luego copie la fórmula. 6. De manera similar se calcula el S-BASICO, en la celda E2, con la variación de la ubicación del índice en el rango de búsqueda. =CONSULTAV(MED(A2,3,1),DATOS,3,1) 7. Después de copiar la fórmula, este será el resultado final:

Caso práctico 03 La empresa Yhassir & Co, también requiere complementar la información del personal del área de Servicios, para ello se debe realizar: a. Generar el código: primer carácter del apellido paterno + los 2 primeros del apellido materno + categoría + “-0” + correlativo de números. Ejemplo el código para el primer empleado deberá ser: RSOA1-01. b. El Haber Básico se obtendrá según la tabla Básicos, teniendo en cuenta la Categoría del trabajador. 1. En el libro Relacion_Personal, seleccione la hoja Servicios, ubicado en la celda B3, ingresar:

=IZQUIERDA(C3,1)&MED(C3,HALLAR(“ “,C3,1)+1,2)&E3&”-0”&FILA(A1) Aquí una breve explicación de la fórmula: Luego copie la fórmula.

Programa de Diseño, Publicidad y Extensión

99

Técnico en Computación

2. Se mostrará el siguiente resultado:

OBS: Si utiliza la función CONCATENAR, lo haría de la siguiente manera:



=CONCATENAR(IZQUIERDA(C3,1),MED(C3,HALLAR(““,C3,1)+1,2) ,E3,”-0”,FILA(A1))

3. Para calcular el Haber Básico, deberá nombrar el rango [J3:K6] como BASICOS 4. Luego se debe ubicar en la celda F3 e ingresar la siguiente fórmula: =CONSULTAV(E3,BASICOS,2,0) 5. El resultado mostrará lo siguiente:

6. Guarde los cambios.

100

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Trabajo domiciliario Planteamiento de trabajo domiciliario Utilizando el libro Relacion_Personal, en la hoja ConsultaServicios realice la consulta por código de los trabajadores del área de Servicios de la siguiente forma: a. Debe ingresar el código y de manera automática deberá aparecer la información correspondiente al empleado, desglosado por Apellidos y Nombres. (La celda donde se ingresa el código deberá ser una lista). b. La Asignación Familiar (A.FAMILIAR) será el 5% del Haber Básico, solo para aquellos trabajadores que tienen hijos. c. El Sueldo Neto (NETO) es la suma del Haber Básico + la Asignación Familiar. 1. Convierta en Formato de tabla la lista de la hoja Servicios (el nombre de tabla generado será: Tabla1). 2. Seleccione el rango [B3:B19] y asígnele el nombre CODIGO. 3. Luego en la hoja ConsultaServicios, la celda B2 debe validarse para que muestre en forma de Lista el rango CODIGO. 4. Ingrese y analice las funciones en las celdas correspondientes:

5. Compruebe el manejo correcto de la consulta, agregando un nuevo trabajador en la tabla de la hoja Servicios. 6. Finalmente Guarde los cambios.

Programa de Diseño, Publicidad y Extensión

101

Técnico en Computación

102

Programa de Diseño, Publicidad y Extensión

Gráficos

EXCEL I 2010

Sesión 11

Técnico en Computación

Gráficos estadísticos Un gráfico es la representación gráfica de los datos de una hoja de cálculo y facilita su interpretación.

La utilización de gráficos hace más sencilla e inmediata la interpretación de los datos. Frecuentemente un gráfico nos dice mucho más que una serie de datos clasificados por filas y columnas. Cuando se crea un gráfico en Excel, podemos optar por crearlo: -

Como gráfico incrustado: Insertar el gráfico en una hoja normal como cualquier otro objeto.

-

Como hoja de gráfico: Crear el gráfico en una hoja exclusiva para el gráfico, en las hojas de gráfico no existen celdas ni ningún otro tipo de objeto.

Para crear un gráfico en Excel, lo primero que debe hacer es especificar en una hoja de los datos numéricos que presentará el gráfico. A continuación, sólo tiene que seleccionar el tipo de gráfico que desea usar en la pestaña Insertar del grupo Gráficos.

104

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Excel ofrece muchos tipos de gráficos que le ayudarán a mostrar los datos de forma comprensible ante una audiencia. Cuando cree un gráfico o modifique uno existente, puede elegir entre distintos tipos de gráfico (por ejemplo, gráficos de columnas o circulares) y subtipos (por ejemplo, gráficos de columnas apiladas o gráficos circulares 3D). También puede crear un gráfico combinado al utilizar varios tipos de gráficos en uno solo.

Elementos de los gráficos Un gráfico consta de numerosos elementos. Algunos de ellos se presentan de forma predeterminada y otros se pueden agregar según las necesidades. Para cambiar la presentación de los elementos del gráfico puede moverlos a otras ubicaciones dentro del gráfico o cambiar su tamaño o su formato. También puede eliminar los elementos del gráfico que no desee mostrar.

Caso práctico 1 La empresa Yhassir & Co. Requiere realizar un análisis estadístico de la producción de prendas de vestir durante el último quinquenio, para poder tomar decisiones en cuanto a la inversión para el siguiente año, así como también las estrategias para mantener una producción sostenible y ascendente.Los gráficos a crear serán de diversos tipos, además será necesario crear monográficos. 1. Activar el libro Gráficos, en la hoja Movimiento seleccione una celda cualquiera (C6). Programa de Diseño, Publicidad y Extensión

105

Técnico en Computación

2. Luego seleccione la Ficha Insertar, abra el tipo Grafico en Columna y seleccione: Columna agrupada 3D:

3. Se insertara automáticamente el gráfico, considerando que se puede configurar el grafico de diversas maneras utilizando las fichas Diseño, Presentación y Formato de la ficha Herramientas de gráficos.

4. En la ficha Diseñoy en los grupos de comando se pueden realizar operaciones como:

a. Tipo: Permite Cambiar el tipo de gráfico (modificar el tipo de grafico actual), Guardar como plantilla (graba el grafico en un archivo de plantilla para que sea utilizado más adelante).

106

Programa de Diseño, Publicidad y Extensión

Excel I 2010

b. Datos: Permite Cambiar entre filas y columnas (Intercambia los valores de datos y las series que se están representando), Seleccionar datos (Modifica el rango de datos, las series y la categoría que se está representando). c. Diseño de gráfico: Permite asignar diseños con formatos rápidos pre definidos. d. Estilos de diseño: Permite cambiar estilos generales del gráfico. Abra el botón Mas y Seleccione el Estilo 34. e. Ubicación: Permite elegir una ubicación distinta a la hoja actual. 5. En la ficha Presentación y en los grupos de comando se pueden ejecutar operaciones como:

a. Selección actual: Aplica formato a la selección (asigna características personalizados a los elementos del gráfico), Restablecer para hacer coincidir el estilo (borra los formatos personalizados del elemento para volver al estilo global). b. Insertar: Permite insertar imagen, formas y cuadro de texto dentro del grafico. c. Etiquetas: Permite configurar características como: Titulo, rótulos, leyenda, etiquetas, etc. Configurar: - Titulo Encima del grafico “MOVIMIENTO DE VENTAS”.

Programa de Diseño, Publicidad y Extensión

107

Técnico en Computación

- Rótulos del eje: Titulo del eje horizontal primario “MESES”, y en el Titulo del eje vertical primario “CANTIDAD”.

- Leyenda: Mostrar en la parte inferior.

d. Ejes: Cambia el formato y diseño de cada eje. Activa y desactiva las líneas de cuadricula. e. Fondo: configura Área de trazado (solo para gráficos 2D). Da formato al cuadro de grafico ((solo para gráficos 3D). Da formato al plano inferior del grafico ((solo para gráficos 3D) y Cambia el punto de vista 3D del gráfico. f. Análisis: Asigna herramientas de análisis de línea de tendencia y barras (solo para gráficos 2D). g. Propiedades: Permite nombrar el grafico para poder referenciarlo al momento de programar en VBA (Visual Basic para Aplicaciones).

108

Programa de Diseño, Publicidad y Extensión

Excel I 2010

El resultado hasta el momento mostrará lo siguiente:

6. En la ficha Formato y en los grupos de comando se pueden ejecutar operaciones como: a. Selección actual: Aplica formatos específicos al elemento seleccionado del gráfico. También restablece al formato del estilo original. Por ejemplo:

Seleccione el eje vertical (números), luego seleccione Aplicar formato a la selección, en la caja configure la Unida mayor en Fija: 150 luego cerrar.

b. Estilos de forma: Asigna Estilos automáticos a la selección, además aplica efectos de Relleno, Contorno y Efectos de forma. c. Estilos de WordArt: Configura estilos artísticos a los textos del grafico. d. Organizar: Reordena los elementos, alinea, agrupa, etc. e. Tamaño: cambia el tamaño del gráfico.

Programa de Diseño, Publicidad y Extensión

109

Técnico en Computación

7. Considerando que los elementos del gráfico son independientes, luego de los cambios de formato, se puede obtener lo siguiente:

Caso práctico 2 La empresa necesita realizar un estudio de producción de prendas durante el intervalo de años 2006 al 2010, para verificar el aumento de dicha producción. Para esto en la hoja Producción se requiere crear un gráfico de tipo Barras, de estilo Barras 3D apiladas. 1. Ubicado en la hoja Produccion, seleccione el rango [C3:F8]. 2. En la ficha Insertar grupo Grfico, seleccione Barra y luego Barra 3D Apilada.

110

Programa de Diseño, Publicidad y Extensión

Excel I 2010

3. Luego seleccione de la ficha Diseño y del grupo Datos la opción Seleccionar datos. 4. En la caja Seleccionar origen de datos, de la sección Etiquetas del eje horizontal (categoría) haga clic en el botón Editar.

Luego en el cuadro Rango de rótulos del eje, seleccione el rango [B4:B8] luego Aceptar, finalmente vuelva a Aceptar.

5. El resultado mostrará lo siguiente:

6. Luego de modificar las opciones de Presentación (Etiquetas, Ejes, etc.), Formato (Selección actual, estilos, etc.). Podremos obtener el siguiente resultado:

Programa de Diseño, Publicidad y Extensión

111

Técnico en Computación

Caso práctico 3 El siguiente gráfico se debe trabajar en la hoja Reporte, la empresa solo requiere representar las ventas anuales en los últimos tres años y para esto se va a crear un gráfico en Líneas. También se debe mostrar la Línea de tendencia lineal correspondiente al año 2010. 1. Ubicado en la hoja Reporte, Seleccione los rangos: [B5:B16], [E5:G16]. 2. Luego de la ficha Insertar, elegir el gráfico de Líneas y el subtipo Línea con marcadores.

3. El resultado inicial mostrará lo siguiente:

112

Programa de Diseño, Publicidad y Extensión

Excel I 2010

4. Luego seleccione de la ficha Diseño y del grupo Datos la opción Seleccionar datos. 5. En la caja Seleccionar origen de datos, de la sección Entrada de leyenda (Series), elijala opción Serie1 y haga clic en el botón Editar.

Luego en el cuadro Modificar serie, seleccione Nombre de la serie y haga clic en la celda[B3] luego Aceptar, a la Serie2 se le asigna la celda [C3] ] luego Aceptary a la Serie3 asignarle la celda [D3] luego Aceptar, finalmente vuelva a Aceptar.

6. Luego seleccione la serie del eje vertical, luego seleccione el botón Aplicar formato a la selección, en la caja Dar formato a eje elija el botón Número y cambien a la Categoría Moneda asigne 1 decimal finalmente clic en Cerrar.

Programa de Diseño, Publicidad y Extensión

113

Técnico en Computación

7. Luego de realizar los cambios de formato se puede obtener:

8. Luego seleccione el área de trazado, de la ficha Presentación realice la asignación de Análisis siguientes:

114

Programa de Diseño, Publicidad y Extensión

Excel I 2010

9. El resultado se mostrará de la siguiente manera:

Caso práctico 4 En la hoja Ventas se va a crear un gráfico circular en 3D, ahora la empresa requiere mostrar las ventas mensuales representadas en porcentajes durante el último año. 1. Ubíquese en la hoja ventas y seleccione la celda [B8], luego en la ficha Insertar del grupo Gráficos active el Tipo Circular y seleccione el sub tipo Grafico circular seccionado 3D.

2. Se muestra automáticamente un gráfico circular en 3D, luego en la ficha Presentación escriba el Titulo “VENTAS 2010” y luego desactive la Leyenda.

Programa de Diseño, Publicidad y Extensión

115

Técnico en Computación

3. También en la ficha Presentación active la opción Etiqueta de datos, luego elija Más opciones de la etiqueta de datos.. 4. En la caja de diálogo configure la categoría Opciones de etiqueta: Active las casillas Nombre de categoría y Porcentaje, luego en Posición de etiqueta: seleccione Extremo externo y en la lista Separador seleccione Nueva línea.

No olvide que en la categoría Número puede elegir la Categoría Porcentaje con 1 posición decimal. El resultado mostrará lo siguiente:

Caso práctico 5 La empresa necesita crear un mini gráfico en barras en la hoja Producción, para mostrar la producción de prendas durante los 4 trimestres en los años 2006 hasta el 2010. 1. Ubíquese en la hoja Producción seleccione el rango [C4:F4], luego de la ficha Insertar, en el grupo Minigráficos seleccione la opción Columna 2. Luego en la caja Crear grupo Minigrafico, en el control Ubicación seleccione la celda G4 y finalmente Aceptar.

116

Programa de Diseño, Publicidad y Extensión

Excel I 2010

3. El resultado mostrará el minigrafico en la celda G4, luego proceda a copiar hasta la celda G8.

4. En la ficha Herramienta para minigraficos se encuentra la sub ficha Diseño con sus diferentes características. 5. En el grupo Mostrar: active Punto alto, Punto bajo, Primer punto, Último punto, en el grupo Estilo elija un estilo preestablecido a su criterio, cambie el Color de minigrafico y cambie el Color de marcador. El resultado se mostrará de la siguiente manera:

Programa de Diseño, Publicidad y Extensión

117

Técnico en Computación

118

Programa de Diseño, Publicidad y Extensión

Evaluación 03

Práctica calificada en Laboratorio

EXCEL I 2010

Sesión 12

120

Excel I 2010

Contenido Introduccion........................................................................................................... 5 Qué es Excel............................................................................................................ 6 Aplicaciones en Excel.............................................................................................. 6 Realice comparaciones rápidas y efectivas ............................................................ 6 Obtenga análisis eficaces desde el escritorio......................................................... 6 Ahorre tiempo, simplifique el trabajo y aumente la productividad........................... 7 Rompa las barreras y trabaje en equipo de nuevas maneras................................ 7 Obtenga acceso a los libros en cualquier momento y lugar................................... 8 Acceder a Excel....................................................................................................... 8 Descripción del entorno de Excel 2010................................................................... 9 Mi primer libro....................................................................................................... 9 Hojas de cálculo...................................................................................................... 9 Desplazamientos y selecciones............................................................................. 10 Tipos de datos...................................................................................................... 11 Constantes............................................................................................................ 11 1. Tipo de dato texto............................................................................................. 12 2. Tipos de datos fecha/hora................................................................................ 12 Fórmulas............................................................................................................... 12 Guardar libro........................................................................................................ 12 Caso práctico 01................................................................................................... 12 Caso práctico 02................................................................................................... 14 Caso práctico 03................................................................................................... 14 Caso práctico 04................................................................................................... 15 Caso práctico 05................................................................................................... 15 Caso práctico 06................................................................................................... 16 Manipulando hojas de cálculo.............................................................................. 17 Operaciones básicas con hojas y formatos........................................................... 18 Caso práctico 01................................................................................................... 18 Caso práctico 02................................................................................................... 18 Caso práctico 03................................................................................................... 19 Caso práctico 04................................................................................................... 20 Manipular filas y columnas.................................................................................. 20 Caso práctico 05................................................................................................... 21 Caso práctico 06................................................................................................... 22 Formatos de celda................................................................................................ 23 Caso práctico 07................................................................................................... 23 Estilos................................................................................................................... 25 Estilos de celda..................................................................................................... 25 Caso práctico 08................................................................................................... 25 Formatos como tabla........................................................................................... 26 Caso práctico 09................................................................................................... 26 Formato condicional ............................................................................................ 27 Caso práctico 10................................................................................................... 27 Caso práctico 11................................................................................................... 29 Caso Práctico 12................................................................................................... 30 Validación de datos.............................................................................................. 31 Caso práctico 13................................................................................................... 31

Programa de Diseño, Publicidad y Extensión

121

Técnico en Computación

Fórmulas y operadores......................................................................................... 35 Calculos con operadores....................................................................................... 36 Tipos de operadores............................................................................................. 36 Operadores aritmeticos........................................................................................ 36 Operadores de comparación................................................................................ 36 Operadores de texto............................................................................................. 37 Operadores de referencia..................................................................................... 37 Caso práctico 01................................................................................................... 37 Referencias de celdas .......................................................................................... 39 Caso práctico 03................................................................................................... 40 Caso práctico 02................................................................................................... 40 Rangos de celdas.................................................................................................. 41 Caso práctico 04................................................................................................... 41 Caso práctico 05................................................................................................... 42 Caso práctico 06................................................................................................... 43 Funciones.............................................................................................................. 45 Funciones mas frecuentes.................................................................................... 48 Función Suma....................................................................................................... 48 Función Max......................................................................................................... 48 Función Min.......................................................................................................... 48 Función Promedio................................................................................................. 48 Caso práctico 01................................................................................................... 48 Funciones estadisticas.......................................................................................... 50 Función Contar..................................................................................................... 50 Función Contara................................................................................................... 50 Función Contar.Blanco.......................................................................................... 50 Función Contar.Si.................................................................................................. 51 Funcion Contar.Si.Conjunto................................................................................... 51 Función K.Esimo.Menor........................................................................................ 52 Función Mediana.................................................................................................. 52 Función Moda.Uno............................................................................................... 52 Caso práctico 02................................................................................................... 53 Función Summar.Si............................................................................................... 55 Caso práctico 03................................................................................................... 56 Resumenes estadisticos ....................................................................................... 57 Ordenar listas....................................................................................................... 58 Caso práctico 01................................................................................................... 58 Caso práctico 02................................................................................................... 59 Caso práctico 03................................................................................................... 60 Caso práctico 04................................................................................................... 61 Autofiltro de listas................................................................................................ 61 Caso práctico 01................................................................................................... 61 Caso práctico 02................................................................................................... 63 Caso práctico 03................................................................................................... 64 Manejo de subtotales........................................................................................... 65 Caso práctico 01................................................................................................... 66 Caso práctico 02.................................................................................................. 67 Caso práctico 03................................................................................................... 68 Práctica calificada en Laboratorio........................................................................ 69 Evaluación 01....................................................................................................... 69 Funciones lógicas.................................................................................................. 71 Función Si............................................................................................................. 72 Funciones Si Anidadas.......................................................................................... 72 Caso práctico 01................................................................................................... 72

122

Programa de Diseño, Publicidad y Extensión

Excel I 2010

Caso práctico 02................................................................................................... 74 Función Y ............................................................................................................. 75 Función O.............................................................................................................. 76 Tabla Lógica O...................................................................................................... 76 Tabla Lógica Y....................................................................................................... 76 Caso práctico 01................................................................................................... 77 Caso práctico 02................................................................................................... 78 Caso práctico 03................................................................................................... 79 Caso práctico 04................................................................................................... 80 Funciones de busqueda........................................................................................ 81 Función Consultav................................................................................................ 82 Función Consultah................................................................................................ 82 Funciones anidadas.............................................................................................. 83 Caso práctico 01................................................................................................... 84 Caso práctico 02................................................................................................... 85 Caso práctico 03................................................................................................... 86 Evaluación 02....................................................................................................... 91 Funciones.............................................................................................................. 93 Funciones de Texto............................................................................................... 94 Función Izquierda................................................................................................. 94 Función Derecha .................................................................................................. 94 Función Med......................................................................................................... 95 Función Hallar ...................................................................................................... 95 Función largo ....................................................................................................... 96 Función Valor........................................................................................................ 96 Función Concatenar.............................................................................................. 96 Caso práctico 01................................................................................................... 97 Caso práctico 02................................................................................................... 98 Caso práctico 03................................................................................................... 99 Trabajo domiciliario............................................................................................ 101 Planteamiento de trabajo domiciliario............................................................... 101 Gráficos.............................................................................................................. 103 Gráficos estadísticos........................................................................................... 104 Elementos de los gráficos................................................................................... 105 Caso práctico 1................................................................................................... 105 Caso práctico 2................................................................................................... 110 Caso práctico 3................................................................................................... 112 Caso práctico 4................................................................................................... 115 Caso práctico 5................................................................................................... 116 Práctica calificada en Laboratorio...................................................................... 119 Evaluación 03..................................................................................................... 119 Contenido........................................................................................................... 121

Programa de Diseño, Publicidad y Extensión

123