Guía práctica para el manejo de hojas de cálculo en Excel, Apuntes de Informática

¡Descarga Guía práctica para el manejo de hojas de cálculo en Excel y más Apuntes en PDF de Informática solo en Docsity!

CUADERNILLO DE

INFORMÁTICA

3º AÑO EES

Nombre del alumno:--------------------------

Microsoft Excel 3º año

La planilla de cálculo EXCEL

¿Qué es una hoja de cálculo?

Básicamente, una hoja de cálculo es un documento que ha sido dividido en filas y columnas. Excel y sus aplicaciones de hojas de cálculo derivadas, han sido diseñadas para facilitar el manejo de números y cálculos. Varios comandos y botones en un menú, facilitan arreglar y dar formato a las columnas y números y calcular los totales, promedios, porcentajes, presupuestos y complejas fórmulas científicas y financieras. El aspecto de una aplicación de hoja de cálculo, proviene de los libros de contabilidad que durante siglos se han estado usando para conservar registros. La mayor ventaja de una hoja de cálculo de computadora sobre esos libros de contabilidad, es su capacidad de actualizar automáticamente los totales y otros cálculos, a medida que se ingresan nuevos datos.

1. La pantalla inicial

Al iniciar Excel aparece una pantalla inicial como ésta. Indica sus nombres en los recuadros correspondientes

La barra de título

Contiene el nombre del documento sobre el que se está trabajando en ese momento. Cuando creamos un libro nuevo se le asigna el nombre provisional Libro1, hasta que lo guardemos y le

La barra de fórmulas

Nos muestra el contenido de la celda activa, es decir, la casilla donde estamos situados y nos permite modificarlo.

La barra de etiquetas

Permite movernos por las distintas hojas del libro de trabajo. Las etiquetas pueden personalizarse pulsando el botón derecho del Mouse sobre ellas y eligiendo la opción deseada (cambiar nombre, color, etc.)

2. Vocabulario:

Planilla de

cálculo

Documento que está totalmente construido con filas y columnas. Usado para armar listas y analizar datos.

libro de

trabajo

El documento básico para Excel. Sus archivos usan la extensión xls. Un libro de trabajo usualmente contiene varias hojas de trabajo. El libro de trabajo por defecto se llama Libro1. Contiene tres hojas de trabajo, llamadas Hoja1, Hoja2, Hoja 3.

hoja de

cálculo

Una sola hoja con datos. Una o más hojas de trabajo forman un libro de trabajo. Imaginen hojas de papel apiladas una encima de la otra, para formar el libro de trabajo. El libro de trabajo, puede tener hasta 255 hojas de trabajo. Una hoja de trabajo también llamada hoja de cálculo, puede tener hasta 256 columnas, encabezadas por letras y 65,536 filas encabezadas por números, con hasta 32000 caracteres dentro de una sola celda.

celda Intersección^ de^ una^ fila^ con una^ columna,^ en^ una^ hoja^ de^ trabajo.

etiqueta de la

hoja

Cada hoja de trabajo tiene abajo en la ventana del libro de trabajo, una etiqueta con el nombre de la hoja de trabajo escrito en la misma. Puede modificarse su nombre y su color de fondo pulsando el botón secundario del Mouse sobre ella.

hoja de

trabajo activa

La hoja de trabajo que recibe la acción de las teclas y comandos. Tiene una etiqueta blanca y su nombre escrito en negrita.

espacio de

trabajo

El espacio debajo de las barras de herramientas organizado en forma de tabla de doble entrada. Columnas Nombradas con letras según el patrón: A, B, C,…Z, AA, AB, AC,…AZ, BA, BB, BC,…BZ, CA,…IA, IB,…IV (es la última columna posible) Filas Nombradas con números desde 1 hasta 65,536. encabezados Los botones grises en el tope de las columnas y en el extremo izquierdo de las filas. celdas La intersección entre una columna y una fila Cuadro de Nombre En la hoja, arriba a la izquierda. Usado para mostrar los nombres de las celdas.

Barra de Fórmula Muestra los contenidos de una celda cuando es seleccionada, bien sea texto, números o una fórmula de cálculo. celda activa Tienen alrededor de ella un borde oscuro y los encabezados de las filas y las columnas se ven en relieve o en color. La celda activa recibe los datos que ingresemos. Seleccionando una celda, la convierto en activa rango Conjunto de celdas. En este ejemplo el rango de celdas es: A1 : B entrar datos Seleccionar la celda, escribir sus datos y pulsar la tecla ENTER.

3. El puntero del mouse en Excel

En Excel el puntero del mouse puede adoptar formas diferentes. La forma indica la acción a realizar.

Forma Usada para:

Seleccionar una celda o rango

o

Arrastrar la celda o el rango seleccionados. En Excel 2002/2003, se puede arrastrar una selección cuando el puntero se encuentra encima del borde de lo que se está seleccionando, con la forma Mover. Llenar (copiar valores dentro de las celdas a través de las que arrastra) o llenar series (copia un diseño de valores, como el llenado de los días de la semana) Ingresar o editar datos. El cursor (línea vertical dentro de la celda) titila. Redimensionar columna Redimensionar fila Copiar los formatos de celdas con la herramienta, Copiar Formato

EJERCICIO 3

En el archivo “Formatos-Manejo de filas y columnas1.xls” se encuentra en la hoja “Formatos” una planilla con datos del personal del colegio, sobre la misma, se solicitará a continuación realizar los cambios necesarios para lograr que dicha planilla se vea como el modelo que se muestra al final del documento.

1. Ubicarse en la celda A1 para agregar una fila arriba de la planilla. Pulsar para ello en el grupo Celdas de la pestaña Inicio , botón INSERTAR – FILA Ingresar en las celdas A1, B1, C1,D1 y E1 respectivamente los siguientes títulos: a. Apellido b. Nombre c. Fecha Ingreso d. Cargo e. Sueldo
2. Seleccionar el rango A1:E1 y aplicar los siguientes formatos: Fuente Comic Sans, tamaño 12, Estilos negrita, subrayado, color rojo, relleno verde claro.
3. Ajustar el ancho de la columna a los títulos ingresados. (doble clic para ancho exacto)
4. Alinear hacia la derecha los datos de la columna "Nombre".
5. Alinear al centro los datos de la columna "Fecha Ingreso" y asignarle formato Fecha tipo dd- mm-aa.
6. A la columna de sueldos asignarle formato Moneda, símbolo monetario "$" y con dos decimales.

EJERCICIO 4

El objetivo del presente ejercicio es crear una planilla en Excel, de las características del siguiente modelo:

1. Formato de la fila 1 a. Altura de 27,75 Pts. ( Inicio – Celdas – Formato – Alto de Fila ) b. Las celdas B1:C1 que contienen el dato “Buenos Aires”, tanto como las celdas D1:E1 que contienen el dato “Mar del Plata”, se encuentran combinadas. Seleccionarlas y pulsar para combinarlas. c. Estilo de la fuente: negrita. d. El contenido de las celdas se encuentra centrado horizontal y verticalmente( Inicio – Alineación). e. Relleno de las celdas: gris ( Formato de Celdas – Trama ).

Un rango es un

conjunto de celdas

Utilizar el controlador de relleno para rellenar datos Se puede utilizar el comando Rellenar para rellenar datos en las celdas de una hoja de cálculo. También se puede configurar Excel para que continúe automáticamente una serie de números, combinaciones de números y texto, fechas o períodos de tiempo, en función de un modelo establecido. Sin embargo, para rellenar rápidamente varios tipos de series de datos, selecciona las celdas y arrastra el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse sobre el controlador de relleno, el puntero cambia a una cruz negra.). Tras arrastrar el controlador de relleno, aparecerá el botón Opciones de autorrelleno para elegir el modo en que se rellenará la selección. Por ejemplo, para rellenar sólo los formatos de celda, haga clic en Rellenar formatos sólo y para rellenar sólo el contenido de la celda, haga clic en Rellenar sin formato.

2. Formato de la fila 2 a. Estilo de la fuente: negrita. b. Sombreado de las celdas: gris.
3. Formato de la columna 1 a. Estilo de la fuente: negrita. b. Sombreado de las celdas: gris. c. Para ingresar los datos de los meses, sugerimos utilizar la opción Rellenar Series.
4. Los datos de las temperaturas B2:E8 se deben mostrar en forma centrada.
5. Aplicar un borde a la planilla siguiendo las pautas del modelo.
6. Guardar el libro con el nombre “Temperaturas.xls”

5. Formatos automáticos

Una forma fácil de dar una combinación de colores a la tabla que resulte elegante, es escogiendo uno de los estilos predefinidos, disponibles en la pestaña Diseño de la tabla. En Opciones de estilo de la tabla, podemos marcar o desmarcar otros aspectos, como que las columnas o filas aparezcan remarcadas con bandas, o se muestre un resaltado especial en la primera o última columna. Las bandas y resaltados dependerán del estilo de la tabla. Por lo demás, a cada celda se le podrán aplicar los colores de fuente y fondo, fondo condicional, etc. que a cualquier celda de la hoja de cálculo.

Barra de fórmulas =SUMA(RANGO) – Calcula la suma de los valores del rango. =PROMEDIO(RANGO) – Calcula el promedio de los valores del rango. =MAX(RANGO) – Calcula el valor mayor de todos los valores del rango. =MIN(RANGO) - Calcula el valor menor de todos los valores del rango. =CONTAR(RANGO) – Cuenta la cantidad de valores que hay en el rango. =CONTARA(RANGO) – Cuenta las celdas no vacías del rango. =CONTAR.BLANCO(RANGO) – Cuenta las celdas vacías del rango.  Prueba en tu computadora ¿Son correctas las siguientes relaciones? Fórmula Resultado o equivalencia V o F =6+10/2 El resultado es 8. =12-102 El resultado es - 8 =4+6^2 El resultado es 100 =25^2+10 Es equivalente a 60 =25*2-4+3 Es equivalente a 25 Una función es una fórmula predefinida por Excel que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda. Para que las funciones cumplan su cometido es necesario, en la mayoría de los casos, facilitarles unos datos. Estos datos son los que se conocen como argumentos de la función La sintaxis de cualquier función es: nombre_función(argumento1;argumento2;...;argumentoN) Respetan las siguientes reglas:

• 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 un punto y coma ; Ejemplo: =SUMA(A1;N2;C3)
• Los rangos se indican con el nombre de la primer celda, el signo : y el nombre de la última celda Ejemplo: =SUMA(A1:C8) Una función puede insertarse escribiendo directamente en la barra de fórmulas o haciendo clic sobre el botón de la misma barra. Las más usadas las encontrarás en el botón autosuma Algunas funciones importantes: Modificando cualquiera de las celdas a las que se hace referencia en una fórmula, automáticamente la fórmula se recalcula.

Cuando introducimos una fórmula en una celda puede ocurrir que se produzca un error. Dependiendo del tipo de error puede que Excel nos avise o no. Cuando nos avisa del error, aparece un cuadro de diálogo que nos permite aceptar o rechazar la sugerencia. En otras ocasiones, en lugar del resultado, aparecen en la celda símbolos como los que se muestran a continuación, expresando el origen del error.

se produce cuando el ancho de una columna no es suficiente o cuando se utiliza

una fecha o una hora negativa. #¡NUM! cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos. #¡DIV/0! cuando se divide un número por cero. #¿NOMBRE? cuando Excel no reconoce el texto de la fórmula. #N/A cuando un valor no está disponible para una función o fórmula. #¡REF! se produce cuando una referencia de celda no es válida. #¡NUM! cuando se escriben valores numéricos no válidos en una fórmula o función. #¡VALOR! similar a #¡NUMERO!, cuando el tipo de argumento solicitado por la función, es distinto al ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o un número cuando la función espera texto. #¡NULO! cuando se especifica una intersección de dos áreas que no se intersectan. EJERCICIO 7

1. Abrir el archivo “FORMULAS Y FUNCIONES.XLS” para completar el ejercicio de la Hoja1.

2. Aplicar formato número con separador de miles, sin decimales, números negativos en color rojo, sobre Ventas de Octubre (Columna D) y Ventas de Noviembre (Columna H)
3. Seleccionar el rango de celdas B5:B12 y aplicarle formato Reducir hasta ajustar. 🖊 Anota para qué sirve este formato que encontrarás en FORMATO de CELDAS – ALINEACIÓN
4. En el rango de celdas E5:E12 calcular el total de ventas realizado en el mes de octubre por cada artículo, = Precio Octubre (columna C) * Ventas Octubre (columna
5. En el rango de celdas G5:G12 calcular el precio actualizado de cada artículo en el mes de noviembre =Precio Octubre (columna C) * Porcentaje Aumento (columna F).
6. En las celdas I5:I12 calcular el total de ventas realizado de Noviembre por cada artículo, =Precio Noviembre (columna G) * Ventas Noviembre (columna H).

EJERCICIO 8

Este ejercicio se desarrollará utilizando la Hoja3.

2. En las celdas F10:F13, calcular el precio actualizado de las masas, teniendo en cuenta que sufren un 5% de aumento en sus precios, el mismo se encuentra en la celda A11.

3. A las celdas calculadas aplicarles formato moneda con dos decimales.

EJERCICIO 10

En esta planilla se necesita crear las tablas de multiplicar del 1 al 10, para ello debes ingresar los datos de la fila 1 y de la columna A. Todos los demás se calculan con una sola fórmula, de manera tal que podamos copiarla en toda la planilla, teniendo en cuenta que si copiamos la fórmula hacia abajo deberán quedar fijos los valores de la primera fila (Fila1) y si copiamos la fórmula hacia la derecha los valores que deberán quedar fijos son los que corresponden a la columna A.

1. Colocar en negrita los datos ingresados en la fila1 y en la columna A.
2. En la celda B2 ingresar el cálculo correspondiente, considerando que el resultado debe ser la multiplicación del valor de la celda B1 por el valor de la celda A2.

EJERCICIO 11

Utilizar las funciones que correspondan para completar la siguiente planilla.

Ventas del día 28 de agosto

1. Cantidad de empleados:
2. Total diario de ventas:
3. Promedio de venta por empleado:
4. Valor máximo de venta por empleado:
5. Valor mínimo de venta por empleado:

EJERCICIO 12

Empleado Ventas

Arregui $

Limerik $

Martorell $

Paredes $

Sánchez $

EUROPA AFRICA AMERICA TOTAL $ % $ % $ % $ OTOÑO 2234 9005 1053 INVIERNO 734 16555 9232 PRIMAVERA 5788 3378 6000 VERANO 12345 5454 8773 TOTAL EXCURSIONES " T I E M P O L I B R E " S.A. INFORME INGRESOS 1995

Calcular los

datos

faltantes en

la planilla y

el total anual

7. Ordenar una tabla de datos

A la hora de ordenar una tabla, Excel puede hacerlo de forma simple, es decir, ordenar por un único campo u ordenar la lista por diferentes campos a la vez. Para hacer una ordenación simple, por ejemplo ordenar la lista anterior por el primer apellido, debemos posicionarnos en la columna del primer apellido, después podemos acceder a la pestaña Datos y pulsar sobre Ordenar... y escoger el criterio de ordenación o bien pulsar sobre uno de los botones de la sección Ordenar y filtrar para que la ordenación sea ascendente o descendente respectivamente. Estos botones también están disponibles al desplegar la lista que aparece pulsando la pestaña junto al encabezado de la columna. Para ordenar la lista por más de un criterio de ordenación, por ejemplo ordenar la lista por el primer apellido más la fecha de nacimiento, en la pestaña Datos, pulsamos sobre Ordenar... nos aparece el cuadro de diálogo Ordenar donde podemos seleccionar los campos por los que queremos ordenar (pulsando Agregar Nivel para añadir un campo), si ordenamos según el valor de la celda, o por su color o icono (en Ordenar Según), y el Criterio de ordenación, donde elegimos si el orden es alfabético (A a Z o Z a A) o sigue el orden de una Lista personalizada. Por ejemplo, si en la columna de la tabla se guardan los nombres de días de la semana o meses, la ordenación alfabética no sería correcta, y podemos escoger una lista donde se guarden los valores posibles, ordenados de la forma que creamos conveniente, y así el criterio de ordenación seguirá el mismo patrón. Seleccionando un nivel, y pulsando las flechas hacia arriba o hacia abajo, aumentamos o disminuimos la prioridad de ordenación de este nivel. Los datos se ordenarán, primero, por el primer nivel de la lista, y sucesivamente por los demás niveles en orden descendente.

EJERCICIO 13

Reproduce esta tabla con los datos correspondientes a las ventas y sueldos base de un grupo de comerciantes, así como las comisiones establecidas en la empresa. Se desea elaborar un informe completo con los datos pendientes.  Ordena la tabla alfabéticamente, en forma ascendente.  La comisión es el 2% de las ventas.  El total corresponde a lo que cobra cada uno de comisión más el sueldo base.  El %vendedor es el porcentaje que se lleva cada vendedor del total de los sueldos. =total del vendedor / total de columna totales

EJERCICIO 14.

1. Dada la siguiente lista de personas determinar los años, días y minutos de vida. Para ello deberás ingresar la fecha actual y realizar los cálculos con las funciones correspondientes  Para calcular días =DIAS 360(F INICIAL; FECHA FINAL)  Para calcular horas =DIAS 360(F INICIAL; FECHA FINAL)*24  Para calcular años =DIAS 360(F INICIAL; FECHA FINAL)/360 2. Ordenar alfabéticamente la planilla. 3. Ordenar por fecha cuando encuentra nombres repetidos Nombre Fecha de nacimiento Años Días Minutos Juan 15/08/ Pedro 05/07/ Juan 25/07/ Magali 06/04/ Andrea 05/12/ Pedro 29/01/ Horacio 13/06/

EJERCICIO 14.

En el ejercicio anterior utilizar filtros para mostrar:  los 3 mayores  los 3 menores  los que tienen más de 30 años  los que tienen menos de 50 añoS Los filtros nos permiten visualizar sólo determinados datos en una planilla, aquellos que cumplen con un requisito especificado. Se aplican desde la pestaña DATOS en la opción FILTRO. Verás al activarlo que aparece una flecha a la derecha de cada título de columna. 🖊 Pulsa en esa flecha y anota las opciones disponibles.

EJERCICIO 15

1. Sitúate en la celda A1 y pulsa sobre , selecciona la categoria de fecha y hora y elige la función AHORA().Pulsa el botón Aceptar.
2. Aparece un cuadro de diálogo indicando que la función no tiene argumentos. Pulsa de nuevo sobre Aceptar.
3. Sitúate en la celda B2 y escribe el día de hoy en número. Ej. 5
4. Sitúate en la celda B3 y escribe el mes actual en número. Ej. 8
5. Sitúate en la celda B4 y escribe el año actual en número. Ej. 2007
6. Sitúate en la celda C5 y pulsa sobre , escoge la función FECHA() y pulsa el botón Aceptar.
7. Selecciona como argumentos las celdas B4 --> para año, B3 --> para mes y B2 --> para día, pulsa Aceptar.
8. Vamos a calcular nuestra edad.
9. Sitúate en la celda D1 y escribe tu fecha de nacimiento en formato (dia/mes/año)
10. En la celda E1 escribe =HOY()

11. En la celda E2 selecciona la función DIAS360, como fecha inicial la celda D1 (fecha nacimiento), como fecha final E1 (el día de hoy) y en método escribe Verdadero. 12. Como resultado nos aparece los días transcurridos desde la fecha D1 y la fecha E1.
12. Ahora en la celda F3 esc ribe =E2/360 para obtener los años.
13. El resultado aparece con decimales, para que nos salga solo la parte entera podemos utilizar la función =ENTERO(E2/360).
14. Hemos utilizado cuatro de las funciones más utilizadas y que ofrecen muchas posibilidades.

FUNCIONES LÓGICAS: Y – O

En esta ficha tenemos dos recuadros, donde se visualiza el encabezado y el pie en caso de que lo hubiera. Si no hay ninguno, se puede elegir entre las opciones prediseñadas que Excel ofrece o ir al botón Personalizar para indicar uno específico. d- En la ficha Hoja se pueden indicar especificaciones con respecto a la Hoja a imprimir, tales como si se desean o no las líneas de filas y columnas impresas, el orden de las páginas o el área de impresión.

3. Imprimir Al hacer clic sobre el botón Imprimir de la vista preliminar se inicia directamente la impresión. Si queremos cambiar alguna opción de impresión como puede ser la impresora donde queremos que imprima o el número de copias deseadas, lo podremos hacer desde el cuadro de diálogo Imprimir que se abre desde la opción Imprimir... del menú Archivo. En el recuadro Impresora, aparecerá el nombre de la impresora que tenemos instalada en nuestra computadora. En caso de desear cambiarla, hacer clic sobre la flecha derecha del Nombre: para elegir otra impresora de la lista desplegable que se abrirá. Con el botón Propiedades podremos cambiar algunas características de la impresora. En el recuadro Intervalo de páginas, tendremos que indicar si deseamos imprimir Todas las páginas, o bien sólo un rango de Páginas, especificándolo en Desde: y hasta En el recuadro Imprimir, podrás indicar si deseas imprimir Todo el libro, o sólo las Hojas activas (seleccionadas o nuestra hoja activa), o bien, Selección para imprimir sólo los objetos seleccionados de la hoja. Por último en Número de copias: podrás indicar el número de copias a realizar y si las quieres Intercalar.

EJERCICIO 16

Trabajar con la “Hoja7” del libro “FORMULAS Y FUNCIONES.xls” Realizar los siguientes cambios:

1. La orientación de la hoja será Horizontal.
2. Tamaño del papel, carta.
3. Ajustar la escala a 1 Pág. De ancho por 1pág. de alto.
4. Márgenes sup. e inf. de 2 cm. Izq. y der. De 1 cm.
5. Cambiar el orden de las páginas seleccionando la opción, hacia la derecha y luego hacia abajo.
6. Realizar una vista preliminar de la planilla
7. Imprimir 3 copias de todo el libro, intercalando las páginas.

IX- El manejo de los datos

Llenar series Otro proceso común en la hoja es llenar series de datos, como meses, años, etc. Estos procesos se pueden realizar automáticamente utilizando los procesos de autollenado de Excel. Si lo que copiamos es una fórmula o un valor cualquiera se realizará la copia normal, sin embargo existen varias series de fechas, horas o meses que modificará la operación normal de copiado.

Por ejemplo si en una copiamos una celda cuyo contenido en Enero en las celdas adyacentes, las celdas copiadas continuarán la secuencia de meses, Febrero, Marzo,... Si por lo contrario copiamos una celda cuyo contenido es una fecha como por ejemplo 22/07/68, las celdas copiadas seguirán la serie de fechas, 23/07/68, 24/07/68,... Lo mismo ocurre con horas, si copiamos una celda con por ejemplo la hora 10:00, las copiadas seguirán 11:00, 12:00,.... Cuando copiamos fechas las opciones de relleno pasan a ser las siguientes: Existen también otras series como podrían ser las numéricas. Por ejemplo, supongamos que en la celda A1 tenemos el valor 1 y en B1 el valor 2, ahora seleccionamos las dos celdas y con el controlador de relleno copiamos las celdas hacia la derecha, pues en las celdas adyacentes se seguirá la serie, 3, 4, 5,... Si en vez de 1 y 2 tenemos 2 y 4 y copiamos con este método la serie de las celdas adyacentes seguirá con números pares. Cuando copiamos series numéricas, las opciones de relleno incluyen Rellenar serie pero no Rellenar meses. Formato condicional: Es el formato que Excel aplica automáticamente a las celdas si la condición que se especifica es cierta. Para aplicarlo procedemos de la siguiente forma: Seleccionamos la celda a la que vamos a aplicar un formato condicional.

• Accedemos al menú Formato condicional de la pestaña Inicio. Elegimos la opción Nueva regla que permite crear una regla personalizada para aplicar un formato concreto a aquellas celdas que cumplan determinadas condiciones. Nos aparece un cuadro de diálogo Nueva regla de formato como el que vemos en la imagen. En este cuadro seleccionaremos que se aplique el formato únicamente a las celdas que contengan un valor, aunque puedes escoger otro diferente. En el marco Editar una descripción de regla deberemos indicar las condiciones que debe cumplir la celda y de qué forma se marcará.