Story Transcript
1. LA PANTALLA DE EXCEL
Ejemplo: Escribir nombre y apellidos en la casilla B2 Cambiar de HOJA , hoja 2, hoja 3 (comprobar que B2 está vacía) 1
2. ENTRADA DE DATOS
2
3. EL RATÓN
3
4. CÁLCULOS MATEMÁTICOS Con Excel podemos hacer operaciones matemáticas. Vamos a ver algunas formas de hacerlo. En primer lugar, podemos poner en una celda el valor de la operación escribiéndola directamente. Vamos a sumar 6+4 en la celda A1 y 6+5 en la C3. La forma de hacerlo es la siguiente (escribe sólo lo que está en negrita): En la celda A1 escribe: =6+4 (y pulsa Enter). En la celda C3 escribe: =6+5 (y pulsa Enter) Esta operación no será frecuente ni práctica; lo más seguro es que tengas que sumar los valores de una serie de celdas, o sea, de un rango. Ejemplo: Supongamos que es tu cumpleaños y que has recibido el dinero que figura en la siguiente tabla. Podrías ir a la celda B4 y escribir =20+10,5+7,35 pero, si se produce una modificación en alguno de los datos, tendrías que volver a escribir la fórmula. Vamos a considerar como si cada celda fuese una caja y lo que vamos a decirle al ordenador es que nos sume el contenido de la caja. De esta forma, si modificamos el valor de una celda, el ordenador nos dará el nuevo resultado. En B5 escribe = B1+B2+B3 (y pulsa Enter) También podemos hacerlo de la siguiente forma: En B5 escribe =SUMA(B1:B3) (y pulsa Enter) Si queremos que en esa columna nos aparezca el símbolo del Euro, seleccionamos las celdas y pulsamos sobre el icono en la Barra de herramientas de formato o clic en Formato / Celdas / Número / Moneda. Ejemplo: Probar otras operaciones SUMA RESTA
PRODUCTO
DIVISIÓN
PRÁCTICA 1. CÁLCULOS BÁSICOS. Abre EXCEL y rellena solamente las columnas C y D. En la columna C sólo hay casillas con TEXTO. Basta situar el cursor en la casilla correspondiente, C6, por ejemplo, y escribir "Dato 1:" En la columna D hay números y fórmulas. Selecciona D6 y escribe 15 y en D7 escribe 3. En la casilla D9 debes escribir una fórmula. Teclea el signo "=" . (Todas la fórmulas empiezan con este signo. Clica con el ratón en la casilla D6 en la que has escrito un 15, teclea acto seguido el signo "+" y clica con el ratón la casilla D7, donde hay un 3. En la barra de fórmulas clica en el icono de ACEPTAR (icono de Visto Bueno de color verde). En la casilla aparecerá un 18. Ya se ha escrito la fórmula. Procede con las siguientes del mismo modo teniendo en cuenta lo siguiente: En la casilla D10 debes escribir la fórmula =D6-D7 En la casilla D11 = D6*D7 En la casilla D12 = D6/D7 En la casilla D13 = D6^D7 En la casilla D15 =(D6+D7)*D7 En la casilla D20 = D18*D19/2
4
5. CONTROLADOR DE RELLENO. SERIES Al arrastrar el controlador de relleno de una celda o rango Excel lo copia si no sabe que es el comienzo de una serie y la rellena si lo sabe: Autorrelleno. Ejemplo: Escribir “lunes” en una celda y arrastrar (hacia abajo o derecha); probar con “enero”, “alumno 1”,... Si escribimos 1 en una celda y arrastramos, sólo lo copia porque no lo reconoce como serie; debemos escribir 2 en la inferior, seleccionar las 2 celdas y arrastrar. Podemos crear series nuevas que nos interesen en Herramientas / Opciones / Listas personalizadas / Nueva lista y en Entradas de lista escribimos la serie, por ejemplo: Bosco, Labor, Castelao, Marcote,... También podemos rellenar las series en Edición / Rellenar / Series / donde tenemos varias opciones: Lineal Geométrica (multiplica lo anterior por lo que se haya especificado en incremento) Cronológica (rellena según lo que pidamos: fecha, día laborable, mes, año) Ejemplo. Escribe un 1 en la casilla B3 y acepta. Vete a Edición, rellenar, series... y selecciona: Columnas, Lineal, Incremento 1, límite 15, Aceptar. En C3 pon un 0 (cero). Define: Columnas, Lineal, Incremento 5, Límite 75. En D3 pon un 1. Define la serie: Columnas, Geométrica, Incremento 2, Límite 500. En E3 escribe un 1. Antes de definir ninguna serie SELECCIONA las casillas E3:E20 (desde E3 hasta E20) y quedan seleccionadas en color malva ¿?. Ahora vete a Edición, Rellenar, Series.... fíjate que ya está seleccionada la opción Columnas. Selecciona Geométrica, Incremento 3. No pongas límite. Aceptar y analiza el resultado. Definiendo series cronológicas reproduce esta tabla:
Todas las columnas se han creado de la misma manera... Se ha puesto la fecha inicial en una casilla. Se han seleccionado las casillas que queremos rellenar. En Edición, Rellenar, Series se ha definido, Fecha e Incremento 1 para la 1ª columna; Días laborables para la 2ª; Fecha e Incremento 7 para la 3ª y Mes para la 4ª.
5
PRÁCTICA 2. PRESUPUESTO CONSTRUCCIÓN. Crea la siguiente tabla con las fórmulas necesarias para que calcule los costes de cada producto y el total.
PRÁCTICA 3. GASTOS DE UNA SEMANA. Crea la siguiente tabla con las fórmulas necesarias para que calcule los beneficios de cada día y los totales de ingresos, gastos y beneficios de la semana.
PRÁCTICA 4. GASTOS FAMILIARES. Crea la siguiente tabla con las fórmulas necesarias para que calcule los totales de cada mes y el total de cada tipo de gasto en el trimestre.
6
6. FUNCIONES. Las funciones nos permiten realizar operaciones más complejas. Para ello escribimos el signo = y el nombre de la función con los parámetros necesarios entre paréntesis. Al clickar en el icono fx se despliega un menú con las distintas funciones disponibles y al clickar en una de ellas nos aparecerá otro submenú en el que se nos piden los parámetros. Suma, producto,... son funciones que ya hemos practicado. Existen otras funciones entre las que podemos destacar: PROMEDIO: Calcula la media aritmética de los valores del rango. Las celdas que no tienen ningún valor no las considera, para ello deben tener un cero. VALOR MÁXIMO: Define el valor máximo de entre los del rango definido. SUMAR SI: Realiza la suma entre los valores del rango si cumplen una condición a definir por nosotros. CONTAR SI: Cuenta el nº de celdas que cumplen una condición que definimos nosotros. SI: comprueba si se cumple una condición a establecer por nosotros y devuelve un valor (establecido por nosotros) si se cumple y otro (establecido por nosotros) si no se cumple. Ejemplos:
Suma
Promedio
Valor máximo
Sumar si
Contar si
7
PRÁCTICA 5. NOTAS ASIGNATURA Crea la siguiente tabla con las fórmulas necesarias para que calcule las notas finales de los alumnos:
PRÁCTICA 6. GASTOS ANUALES Crea la siguiente tabla con las fórmulas necesarias para que calcule los siguientes datos: - Calcular el total de ingresos teniendo en cuenta que sólo se pueden sumar los que sean superiores a 1.000€ - Calcular el nº de meses en que los gastos han sido mayores de 500€.
8
7. FÓRMULAS RELATIVAS Las fórmulas por defecto son relativas, es decir, cambian automáticamente en función de la posición, o dicho de otra manera, si con el controlador de relleno copiamos una fórmula, ésta se adapta a la nueva situación, es decir es “relativa” a la situación. Ejemplo: Si tenemos varias columnas y queremos hacer una suma de cada una, sumamos la primera (1) y arrastramos el controlador de relleno para copiar la fórmula en las demás (2). Excel cambiará automáticamente las referencias de las celdas (3).
1. Suma en una columna
2. Insertamos datos en otra columna y arrastramos controlador
3. Actualiza referencias a la nueva columna
PRÁCTICA 7. Crea la siguiente tabla con las fórmulas necesarias para que calcule los beneficios de cada comercial, totales de ingresos, gastos y beneficios (hazlo arrastrando el controlador cuando tengas la fórmula hecha en la celda C9 desde la celda C9 a la D9):
En cualquier momento de la fase de trabajo se pueden insertar filas o columnas. Éstas se añadirán en la posición inmediatamente anterior a la que nos encontremos en el momento de insertar. Si queremos insertar más de una, seleccionamos celdas de tantas filas o columnas como queramos insertar. Dado que las fórmulas son relativas, Excel actualizará las posiciones de las celdas referenciadas a la nueva disposición de filas/columnas. Ejemplo: Si después de introducir unas cifras y hacer el cálculo que nos interese (1), nos diésemos cuenta de que necesitamos una fila más entre la cifra 300 y 610, nos situaríamos en la fila correspondiente a la cifra 610, o sea en la 5, y seleccionaríamos Insertar/Filas. Si comprobamos la fórmula, vemos que se ha actualizado incorporando una fila más (2), y si a continuación escribimos la cifra correspondiente se actualiza el total (3).
1. Tarea finalizada
2. Insertamos una fila
3. Incorporamos la cifra
9
PRÁCTICA 7 (continuación). A partir de la práctica 7 añade 3 comerciales más con los datos que figuran abajo y aplica a los números el formato “Moneda” sin decimales.
8. FÓRMULAS ABSOLUTAS Habrá casos en que no nos interesará que la fórmula se adapte al cambio de situación, que queramos que la fórmula no cambie aunque la movamos o copiemos a otra posición. Ejemplo: A una serie de precios le queremos aplicar el IVA que está en la celda C3. Si los componentes de la fórmula son relativos tal y como figura en la fórmula =PRODUCTO(C6/100;C3) (1), al arrastrar el controlador de relleno y situarnos en la celda D7 para ver cual es la fórmula aplicada en esa celda vemos que cambia la posición de la C a C4 (2), y en la siguiente será C5, C6,... Para indicar que una posición es absoluta utilizamos $, poniéndolo delante de la posición que queremos fijar. Entonces la fórmula en D6 debe ser =PRODUCTO(C6/100;$C$3) y ahora si arrastramos el controlador podemos comprobar que en las fórmulas de la D7, D8, D9 y D10 siempre hace referencia a la celda C3. Si tenemos claro que el controlador de relleno sólo lo vamos a arrastrar en la dirección vertical pondríamos =PRODUCTO(C6/100;C$3 (fijando así la posición de la fila 3), y si sólo lo vamos a arrastrar en la horizontal pondríamos =PRODUCTO(C6/100; $C3 (fijando así la posición de la columna C).
1
2
3
PRÁCTICA 8. Crear la siguiente tabla con las fórmulas necesarias para que al modificar la celda B1 de la hoja de cálculo, calcule la tabla de multiplicar correspondiente al número introducido en dicha celda. Cambia el nombre de la hoja por el nombre “Tabla”.
10
PRÁCTICA 9. Crea la siguiente tabla con las fórmulas necesarias para que calcule el IVA de cada producto, el TOTAL de cada producto y el total de IVA y de productos.
PRÁCTICA 10. Crea la siguiente tabla con las fórmulas necesarias para que calcule todos los datos que faltan, teniendo en cuenta lo siguiente: Importe = Cantidad x Precio Descuento = Importe x 25% Total = Importe – Descuento Totales = Totales de Importe, Descuento y Total IVA = Total de celda F16 x 16% Total con IVA = Total de celda F16 + IVA Precios máximo, mínimo y medio referidos a los precios unitarios de los artículos.
11
9. FORMATOS CONDICIONALES El formato condicional es el formato (por ejemplo, un sombreado de celda o un color de fuente) que Excel aplica automáticamente a las celdas si la condición que se especifica es cierta. Ejemplo: Si tenemos un listado de notas de alumnos puede interesarnos que los aprobados aparezcan en color azul y los suspensos en color rojo. Para ello supongamos que tenemos el listado de notas (para hacer el ejemplo lo tecleamos directamente, pero lo lógico sería que fuese el resultado de una operación previa de promedio, por ejemplo).
Nos situamos en la 1ª celda (C5) y seleccionamos Formato / Formato condicional, nos aparecerá un menú que nos pide las condiciones que queremos aplicar. En este caso serían 2 condiciones. Empezamos con la 1ª que sería que si el Valor de la celda es mayor o igual que 5 seleccionamos en Formato que la fuente sea de color azul. Ahora clickamos Agregar y creamos la condición 2: que si el Valor de la celda es menor que 5 seleccionamos en Formato que la fuente sea de color rojo.
Condición 1 en Formato / Formato condicional
Condición 2 en Formato / Formato condicional / Agregar
Aplicamos formato condicional a la 1ª celda y arrastramos controlador para aplicarlo a las demás celdas (C5 a C14)
Añadimos con la función SI el texto APROBADO/ SUSPENSO según la nota, y aplicamos también un formato condicional.
12
PRÁCTICA 11. Abre el archivo Práctica 5. Guárdalo con el nombre Práctica 11. Mediante formatos condicionales realiza lo siguiente: - Haz que las notas que sean mayores o iguales a 5 aparezcan en azul y las menores de 5 en rojo. - Al lado de cada nota (en las celdas I5 a I9) haz que aparezca automáticamente el texto APROBADO ó SUSPENSO según la nota final. - Calcula mediante la función adecuada el nº de aprobados y suspensos.
13
10. VINCULACIÓN DE DATOS EN DISTINTAS HOJAS Cuando queremos utilizar referencias a celdas de unas hojas a otras de un libro hay que escribir el nombre de la hoja seguido del signo de admiración y el nombre de la celda. Ejemplo: Si en un libro tenemos una hoja llamada “Movimientos” y por ejemplo en la celda B6 tenemos un cálculo hecho que queremos reflejar en la celda C4 de otra hoja llamada “Resumen”, vamos a la celda original B6 y clickamos Copiar. Luego vamos a la celda C4 de la hoja “Resumen” y clickamos Edición / Pegado especial / Pegar vínculos. De esta manera añadimos como referencia el nombre de la hoja de la que queremos el vínculo seguido del signo de admiración.
PRÁCTICA 12. Crea el siguiente libro con sus hojas Desglose y Resumen. En cada una de ellas copia lo que tienes debajo y crea las fórmulas necesarias en la hoja Desglose para que calcule los ahorros de cada mes (en los meses de enero y febrero que son de los únicos de los que tienes datos). En la hoja Resumen haz que en cada mes tenga la referencia de la celda correspondiente de la hoja Desglose.
14
11. GRÁFICOS Los gráficos sirven para que de un solo vistazo nos demos cuenta de detalles que en una hoja de cálculo tendríamos que analizar con detenimiento. Es fácil realizar gráficos partiendo de unos datos con el Asistente de gráficos. Partimos de los datos que se muestran en la primera imagen. Marcamos los datos que vamos a utilizar para el gráfico. En la Barra Estándar pulsamos sobre el botón Asistente de gráficos o en Insertar / Gráfico.
1.
Nos aparece el primer cuadro de una secuencia de cuatro. En el primero tenemos que marcar el Tipo de gráfico. Es decir de qué grupo queremos el gráfico y dentro del tipo de gráfico seleccionar a la derecha uno concreto. En nuestro ejemplo como Tipo hemos elegido Líneas y hemos seleccionado la primera opción dentro del grupo de gráficos disponibles.
2.
En el siguiente cuadro tenemos que definir de dónde se toman los datos para realizar el gráfico. En este caso hemos marcado desde el principio las celdas desde las que se tomarán los datos para dibujar el gráfico. Y aparece bien definido el rango de celdas desde el que se tomarán los datos. Si las celdas que aparecen a continuación de Rango de datos, no corresponden a las que contienen los datos de gráfico debemos escribir el rango correcto.
3.
En el siguiente cuadro tenemos la oportunidad de añadir un título. En este caso ya lo hemos tomado al marcar Ventas al comienzo. También podemos añadir un título a los ejes del gráfico. En nuestro caso añadimos Meses para la escala horizontal y Millones para la vertical como títulos explicativos de los ejes.
15
4.
El último cuadro nos permite seleccionar la ubicación del gráfico. Podemos colocar el gráfico insertado en una Hoja o podemos asignar una hoja para el gráfico.
Y el resultado se muestra en la imagen. Pulsando sobre los puntos del recuadro del gráfico y moviéndonos sin dejar de pulsar, podemos modificar el tamaño del gráfico.
Una cuestión muy interesante en relación a los gráficos en que se encuentran enlazados a los datos de los que procede. De tal forma que si cambiamos por ejemplo en la celda A4 el número 46 por 99 y pulsamos Enter, el gráfico refleja automáticamente el nuevo dato.
Seleccionando las distintas partes de que se compone el gráfico podemos modificar las propiedades, colores, fuentes, tamaños,... Si seleccionamos todo el gráfico y clickamos en el botón derecho del ratón podemos hacer modificaciones de carácter general en el gráfico.
16
PRÁCTICA 13. Crea la siguiente hoja de cálculo siendo los datos de partida las provincias y el nº de desempleados en cada provincia. El resto calcúlalo mediante las fórmulas correspondientes. Crea tres gráficos como los que se muestran (observa que en el 3º se ha eliminado el borde).
PRÁCTICA 14. Crea la siguiente hoja de cálculo siendo los datos de partida los sombreados en azul, todo lo demás debes hacerlo con fórmulas. Crea dos gráficos como los que se muestran.
17
12. PROTEGER CELDAS Podemos proteger determinadas celdas para evitar que otros usuarios accidentalmente puedan borrar o modificar los contenidos de dichas celdas. Para ello seguimos los siguientes pasos: 1. Seleccionamos las celdas que queremos proteger y clickamos Formato / Celdas / Proteger y seleccionamos Bloqueada. 2. Seleccionamos las celdas que no queremos proteger y clickamos Formato / Celdas / Proteger y deseleccionamos Bloqueada. 3. Clickamos Herramientas / Proteger / Proteger hoja y seleccionamos las opciones que nos interesen permitir a los usuarios.
De esta manera sólo se permite que los usuarios puedan seleccionar las celdas desbloqueadas y se impide el acceso a las demás. También podríamos poner una contraseña, de manera que el usuario que quisiera desproteger la hoja debería conocer dicha contraseña.
PRÁCTICA 15. Crea la siguiente hoja de cálculo siendo los datos de partida los de las celdas sombreadas en azul, todo lo demás debes hacerlo con fórmulas *. Crea tres gráficos como los que se muestran. Protege las celdas en las que insertaste las fórmulas (sin contraseña).
* En la columna G, las notas las letras de las calificaciones las ponemos con la función SI pero ahora no hay solo 2 posibilidades como ocurría siempre hasta ahora, sino que hay 5: insuficiente, suficiente, bien, notable y sobresaliente. Cuando hay más de 2 posibilidades hay que concatenar varias funciones SI, el 3er parámetro debe ser otro SI y así sucesivamente. En esta práctica la fórmula completa debe quedar así: =SI(E6