1.- principio de excel

UNIDAD 04. UNIDAD 04. ELABORACIÓN DE ELABORACIÓN DE DOCUMENTOS Y DOCUMENTOS Y

PLANTILLAS DE HOJAS PLANTILLAS DE HOJAS DE CÁLCULO. DE CÁLCULO.

HOJA DE CÁLCULO. EXCEL

• Excel es un programa del tipo Hoja de Cálculo que permite realizar operaciones con números organizados en una cuadrícula.

• Es capaz de trabajar con números de forma sencilla e intuitiva.

• Excel vuelve a calcularlo todo automáticamente si aplicamos cambios.

HOJA DE CÁLCULO. EXCEL

• Entorno de Excel

HOJA DE CÁLCULO. EXCEL

• La barra de título

• La barra de menú

• La barra de herramientas

HOJA DE CÁLCULO. EXCEL

• La barra de fórmulas

• La barra de etiquetas

• Las barras de desplazamiento

HOJA DE CÁLCULO. EXCEL

• El panel de tareas.

HOJA DE CÁLCULO. EXCEL

• CONCEPTOS BÁSICOS• Un libro de trabajo es el archivo

que creamos con Excel • Un libro de trabajo está formado

por varias hojas, en principio de 3 hojas. Puede variar entre 1 y 255.

HOJA DE CÁLCULO. EXCEL

• La hoja de cálculo es uno de tipos de hojas que puede contener un libro de trabajo.

• Es como una gran hoja cuadriculada formada por 256 columnas y 65.536 filas

• Una columna es el conjunto de celdas seleccionadas verticalmente. Se nombra por letras.

HOJA DE CÁLCULO. EXCEL

• Una fila es el conjunto de celdas seleccionadas horizontalmente. Se nombra por números.

• La intersección de una columna y una fila se denomina Celda. Se nombra con el nombre de la columna a la que pertenece y a continuación el número de su fila

HOJA DE CÁLCULO. EXCEL

• Fila activa, fila donde se encuentra la celda activa

• Columna activa, columna de la celda activa

• Rango es un bloque rectangular de una o más celdas que Excel trata como una unidad.

HOJA DE CÁLCULO. EXCEL

FIN     FLECHA DERECHAÚltima celda de la fila activa

FIN     FLECHA IZQUIERDA o  INICIO

Primera celda de la fila activa

FIN     FLECHA ABAJO Última celda de la columna

activa

FIN     FLECHA ARRIBA Primera celda de la columna

activa

CTRL+INICIO Celda A1

REPAG Pantalla Arriba

AVPAG Pantalla Abajo

FLECHA IZQUIERDA Celda Izquierda

FLECHA DERECHA Celda Derecha

FLECHA ARRIBA Celda Arriba

FLECHA ABAJO Celda Abajo

TECLADOMOVIMIENTO

HOJA DE CÁLCULO. EXCEL

• TIPOS DE DATOS: • VALORES CONSTANTES: un dato

que se introduce directamente en una celda. Pueden ser letras o números.

• FÓRMULAS Se escribe en la barra de fórmulas y debe empezar siempre por el signo =.

HOJA DE CÁLCULO. EXCEL

• Para practicar hacemos juntos el ejercicio 1 y 1a.

• En este ejercicio veremos relleno de series y opciones de formato en las celdas.

HOJA DE CÁLCULO. EXCEL

• INTRODUCCIÓN DE FÓRMULAS Y FUNCIONES

• Una fórmula es una secuencia formada por valores constantes, referencias a otras celdas, nombres, funciones, u operadores.

• Debe empezar siempre por el signo =.

HOJA DE CÁLCULO. EXCEL

• Tipos de operadores:• Aritméticos:+ - * / % ^• Tipo texto: &• Relacionales: < > = <= >=

<>• De referencia:

– De rango: (:), Ejemplo: A1:G5– De unión:(,), Ejemplo: A1,G5

HOJA DE CÁLCULO. EXCEL

• Hacemos ahora el ejercicio 2 para practicar las fórmulas y los operadores.

HOJA DE CÁLCULO. EXCEL

• Una función es una fórmula predefinida por Excel2003 (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá en la celda o será utilizado para calcular la fórmula que la contiene.

HOJA DE CÁLCULO. EXCEL

• La sintaxis de cualquier función es: • nombre_función(arg1;arg2;...;argN)• Ejemplo: =SUMA(A1:C8) • El operador ":" nos identifica un

rango de celdas• Se puede escribir directamente en

la celda

HOJA DE CÁLCULO. EXCEL

• Situarse en la celda donde queremos introducir la función, desplegar el menú Insertar.

• Elegir la opción Función... • O bien, hacer clic sobre el botón

de la barra de fórmulas.

HOJA DE CÁLCULO. EXCEL

• Nos aparece este asistente

HOJA DE CÁLCULO. EXCEL

• Despues de elegir la fórmula, nos sale este cuadro:

HOJA DE CÁLCULO. EXCEL

• Hacemos ahora el ejercicio 3. Despues el 3-a y el 3-b.

HOJA DE CÁLCULO. EXCEL

• REFERENCIAS.• Las referencias son enlaces a un

lugar, es decir, cuando en una formula escribimos =SUMA(A1;B1) nos estamos refiriendo a que sume el contenido de A1 y el contenido de B1.

HOJA DE CÁLCULO. EXCEL

• Existen 3 tipos de referencias:– Referencias relativas– Referencias absolutas– Referencias mixtas.

HOJA DE CÁLCULO. EXCEL

• Referencia Relativa: Las referencias de filas y columnas cambian si se copia la formula en otra celda. Las que se usan por defecto.

• Referencia Absoluta:Las referencias de filas y columnas no cambian si se copia la formula a otra celda, las referencias a las celdas son fijas.

HOJA DE CÁLCULO. EXCEL

• Referencia Mixta: Podemos hacer una combinación de ambas referencias, podemos hacer que las filas sean relativas y las columnas absolutas o viceversa.

HOJA DE CÁLCULO. EXCEL

• Referencias relativas, referencias absolutas y referencias mixtas.

• Ejercicios de las carpetas EJERCICIO_04 y EJERCICIO_05.

HOJA DE CÁLCULO EXCEL

• 2º SESIÓN:• Listado de fórmulas sencillas que

se aplican como la de SUMA:• Suma, Promedio, Contar,

Contar.blanco, Max, Min, Mediana, Moda, Producto, Raiz.

HOJA DE CÁLCULO. EXCEL

• FUNCIÓN SI• Hacemos una pregunta, y según la

respuesta hace una cosa u otra.

HOJA DE CÁLCULO. EXCEL

• Al insertar la función, los datos que nos pide son:– Prueba lógica– Valor si verdadero– Valor si falso

HOJA DE CÁLCULO. EXCEL

• En prueba lógica siempre irá una pregunta, normalmente una comparación. Ejemplos– A1<B1, A1<>B1, A1<=B1, A1<5...

• Si la respuesta es verdadera, en Valor si verdadero irá lo que pondrá en la celda si la respuesta es que si.

HOJA DE CÁLCULO. EXCEL

• En valor si falso se pone lo que queremos que salga en la celda si la respuesta es falsa.

• Hacemos los ejercicios de la carpeta 6.

HOJA DE CÁLCULO EXCEL

• FORMATO CONDICIONAL• Podemos variar el formato de

nuestra hoja en función de los valores de la celda.

• Lo veremos con un ejemplo.

HOJA DE CÁLCULO EXCEL

• Modificaremos el formato de Ventas si el trabajador ha superado los objetivos.

HOJA DE CÁLCULO EXCEL

• Seleccionamos los datos y vamos a Formato – Formato condicional

HOJA DE CÁLCULO EXCEL

• Existe la posibilidad de modificar el formato de toda una fila completa en vez de solo una celda.

• También se hace a través de formato condicional

• Lo vemos con un ejemplo.

HOJA DE CÁLCULO EXCEL

• Destacaremos los trabajos que se han realizado posterior a una fecha

HOJA DE CÁLCULO EXCEL

• Vamos a formato condicional• En opciones cambiamos Celda por

Fórmula• Como condición ponemos

=$B2<HOY()• Seleccionamos el formato que

deseamos y aceptamos.

HOJA DE CÁLCULO EXCEL

• Para aplicar un formato condicional se puede:– Aplicar a una celda y luego arrastrar– Seleccionar todo el rango

directamente y aplicar el formato todas a la vez

• Tenemos la opción de poner varias condiciones con el botón de Agregar.

HOJA DE CÁLCULO EXCEL

• Para eliminar un formato condicional, se seleccionan las celdas afectadas, Formato – Formato condicional – Eliminar

• Te da la opción de eliminar una o varias condiciones.

HOJA DE CÁLCULO EXCEL

• Como ejercicio propio de formato condicional, solo aparece el 6B.

• Os animo a que en cada ejercicio practiquéis este formato, tanto en una celda como en una fila al finalizar cada ejercicio.

HOJA DE CÁLCULO. EXCEL

• 3º SESIÓN:• FUNCIÓN BUSCAR• Esta función busca el valor de un

campo de una tabla en otra tabla. Es útil que solo haya una correspondencia para cada valor.

HOJA DE CÁLCULO. EXCEL

• FUNCIÓN BUSCARV y BUSCARH• Son necesarias en aquellos casos en

que la matriz en la que realizamos la búsqueda tiene más de 2 columnas (o filas). En tales casos, se ha de indicar en qué columna (BUSCARV) o fila (BUSCARH) se ha de buscar la correspondencia que queremos.

HOJA DE CÁLCULO. EXCEL

• Ejemplo, en la carpeta Ejercicio 7, hacemos el ejercicio 7a juntos. Despues hacéis el resto de los de la carpeta.

HOJA DE CÁLCULO. EXCEL

• GRÁFICOS• Para la realización de los gráficos

hay que tener en cuenta una serie de conceptos.

• El primero de ellos, es que es lo que queremos representar y como lo vamos a representar.

HOJA DE CÁLCULO. EXCEL

• Cada columna o fila de datos que vamos a representar es una serie.

• Esto se cumple en todos los tipos de gráficos excepto en los X-Y.

• Los gráficos de sectores solo representan una serie.

• Los de líneas y columnas una o varias series.

HOJA DE CÁLCULO. EXCEL

• En la carpeta Ejercicio 8 tenéis unos cuantos ejercicios de Gráficos. El primero de ellos lo hacemos juntos.

HOJA DE CÁLCULO EXCEL

• 4º SESIÓN• ESQUEMAS Y VISTAS• A veces cuando trabajamos con

hojas de cálculo muy grandes o con varias hojas es un poco costoso organizar las diferentes vistas de las hojas.

HOJA DE CÁLCULO EXCEL

• Vamos a aprender a manejar los esquemas y las distintas vistas de una hoja de cálculo.

• Para los esquemas es una opción que está en el menú Datos – Agrupar y esquema.

• Sirve para ocultar o mostrar ciertas filas o columnas.

HOJA DE CALCULO EXCEL

• Al darle a agrupar a una serie de filas o columnas, nos ofrece la posibilidad de ocultarlas o no con el símbolo + que aparece en los márgenes.

• Al darle desagrupar, esa opción desaparece.

HOJA DE CÁLCULO EXCEL

• Vistas: otro modo de facilitarnos el uso de hojas de cálculo es con el uso de paneles o de organizar distintas hojas que tengamos abiertas.

• Todas las opciones se encuentran en el menú ventana.

HOJA DE CALCULO EXCEL

• Cuando fijamos paneles, una parte de la hoja permanece fija, y otra se puede mover con las barras de desplazamiento.

• Es útil para mantener a la vista encabezados de columnas o de filas.

HOJA DE CALCULO EXCEL

• LISTAS DE DATOS.• Una lista en Excel es un conjunto

de datos organizados en filas o registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados.

5º SESIÓN

HOJA DE CALCULO EXCEL

• Entre las operaciones más interesantes que podemos realizar con las listas tenemos:

• Ordenar la lista.• Filtrar el contenido de la lista por algún

criterio.• Utilizar fórmulas para la lista añadiendo

algún tipo de filtrado.• Crear un resumen de los datos

contenidos en la lista.

HOJA DE CALCULO EXCEL

• CREAR UNA LISTA– Datos - Lista - Crear Lista

HOJA DE CALCULO EXCEL

• Al crear la lista nos aparece esta barra de herramientas

• Además, las celdas de la lista aparecen remarcadas en Azul.

HOJA DE CALCULO EXCEL

• Podemos observar:• primera fila: cabeceras . Aparecen

con unas listas desplegables • Fila con el asterisco: sirve para

añadir más registros• Fila Total permite visualizar

resúmenes de las columnas.

HOJA DE CALCULO EXCEL

• Un formulario de datos es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos

• Nos posicionamos dentro de la lista. Vamos a Datos, Formulario.

HOJA DE CALCULO EXCEL

• En un formulario se pueden crear nuevas filas de datos, modificar las existentes o simplemente, consultar los datos.

HOJA DE CALCULO EXCEL

• Podemos ordenar los datos de una lista.

• Pulsamos en la lista, Datos - Ordenar.

• Se eligen los criterios y nos lo ordena.

HOJA DE CALCULO EXCEL

• Filtrar una lista es que de todos los registros almacenados en la lista, seleccionar aquellos que se correspondan con algún criterio fijado por nosotros.

• Excel nos ofrece dos formas de filtrar una lista. – Utilizando el Autofiltro.– Utilizando filtros avanzados.

HOJA DE CALCULO EXCEL

• Para utilizar el Autofiltro nos servimos de las listas desplegables asociadas a las cabeceras de campos (si por algún motivo no aparecieran, podemos hacerlas aparecer con el menú Datos -- Filtro --Autofiltro).

HOJA DE CALCULO EXCEL

• Para usar los filtros avanzados, lo primero es tener los criterios por los que vamos a filtrar.

• Para ello necesitamos una parte de la hoja de cálculo como zona de criterios.

• Hacemos una copia del encabezado de nuestra lista y la pegamos en otra parte de la hoja.

HOJA DE CALCULO EXCEL

• Si queremos que nos filtre, por ejemplo, de una lista los apellidos que sean Gómez, debajo de Apellido escribimos Gómez.

• Si ademas queremos que en edad sea mayor que 30, debajo de Edad escribimos >30

HOJA DE CALCULO EXCEL

• Si queremos que se cumpla una condición u otra, no las dos a la vez, una de las condiciones se escribe justo en la línea debajo de las cabeceras, y la otra debajo.

• Veamos algunos ejemplos:

HOJA DE CALCULO EXCEL

• Mostraría solo los de nombre Rosa

• Muestra los de nombre Rosa y código >3

• Muestra los de nombre Rosa o código >3

HOJA DE CALCULO EXCEL

• Una vez definidos los criterios, ya podemos empezar a usar los filtros.

• Vamos a Datos- Filtros- Filtros avanzados

HOJA DE CALCULO EXCEL

• Rango de la lista: Aquí especificamos los registros de la lista a los que queremos aplicar el filtro.

• Rango de criterios: Aquí seleccionamos la fila donde se encuentran los criterios de filtrado (la zona de criterios).

HOJA DE CALCULO EXCEL

• Podemos guardar el resultado del filtrado en otro lugar, seleccionando Copiar a otro lugar, en este caso rellenaremos el campo Copiar a: con el rango de celdas que recibirán el resultado del filtrado.

• Si marcamos la casilla Sólo registros únicos, las repeticiones de registros desaparecerán.

• Para volver a visualizar todos los registros de la lista, acceder al menú Datos - Filtro - Mostrar todo.

HOJA DE CALCULO EXCEL

• Hacemos los ejercicios denominados como Ejercicio 13 y siguientes.

HOJA DE CALCULO EXCEL

• TABLAS DINÁMICAS: Consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos.

• Permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...

5º SESIÓN

HOJA DE CALCULO EXCEL

• Para seguir la explicación crearemos en una hoja un listado de datos como el siguiente (unas 15 líneas de datos):

HOJA DE CALCULO EXCEL

• Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.

• Accedemos al menú Datos - Informe de tablas y gráficos dinámicos.

HOJA DE CALCULO EXCEL

HOJA DE CALCULO EXCEL

HOJA DE CALCULO EXCEL

PULSAMOS PULSAMOS EN DISEÑOEN DISEÑO

HOJA DE CALCULO EXCEL

HOJA DE CALCULO EXCEL

• En el campo Fila arrastramos REF, las referencias de nuestros artículos.

• En el campo Columna arrastramos MES.

• Y en Datos arrastramos IMPORTE• NOTA: Para ayudarnos, en los Datos

solo pueden ir columnas de datos con los que se pueda operar.

HOJA DE CALCULO EXCEL

HOJA DE CALCULO EXCEL

• Vamos a hacer ahora una tabla dinámica con la misma lista.

• Pondremos en las filas el campo REF

• En las columnas el campo MES• En los datos arrastramos los tres

campos restantes: Cantidad, Importe y Total.

HOJA DE CALCULO EXCEL

• Los campos principales vemos que están acompañados, en su parte derecha, de una flecha indicando una lista desplegable

• Permiten filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado

HOJA DE CALCULO EXCEL

• Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio .

• Puede interesarnos modificar esas fórmulas por otras: Máximos, Mínimos, Promedios, etc…

HOJA DE CALCULO EXCEL

• Con botón derecho en la zona a modificar, elegimos Configuración de campo...

HOJA DE CALCULO EXCEL

• GRAFICOS CON TABLAS DINÁMICAS• Clic con el botón derecho sobre cualquier

celda de la tabla, seleccionamos Gráfico dinámico, nos genera un gráfico de barras por defecto en una nueva hoja de gráficos.

• Para cambiar el formato del gráfico: clic con el botón derecho del ratón sobre el gráfico ; Tipo de gráfico... y escoger el gráfico que más nos convenga.

HOJA DE CALCULO EXCEL

• Hacemos los ejercicios de nombre Ejercicios 14

HOJA DE CALCULO EXCEL

• MACROS: automatizar varias tareas y fusionarlas en una sola.

• La forma más fácil de crear macros es crearlas mediante el grabador de macros del que dispone Excel.

• Se abre desde Herramientas - Macro y dentro de este submenu seleccionar la opción Grabar nueva macro...

HOJA DE CALCULO EXCEL

• Le damos un nombre• En Método abreviado

le damos una combinación de teclas

• Y guardamos la macro donde queremos que sea válida (Este libro)

HOJA DE CALCULO EXCEL

• Para comenzar la grabación de la macro pulsamos el botón Aceptar

• En la barra de estado nos informa de que está grabando.

• Realizamos las acciones a guardar• Presionamos sobre el botón

Detener

HOJA DE CALCULO EXCEL

• Antes de ejecutar una macro es necesario seleccionar las celdas donde queremos que se aplique la macro, si esta así lo requiere.

• Para ejecutar la macro debemos acceder al menú Herramientas - Macro - Macros..

• Seleccionar la macro deseada y pulsar sobre el botón Ejecutar

HOJA DE CALCULO EXCEL

• ASIGNAR UN BOTÓN A UNA MACRO

• Vamos a Ver- Barra de Herramientas- Formulario.

• Escogemos el botón y lo pondremos en la hoja.

• Nos aparece el cuadro de Asignar macro

HOJA DE CALCULO EXCEL

• Una vez escogida la macro correspondiente, le damos a Aceptar.

• Para cambiar el texto del botón, damos botón derecho sobre el y elegimos Modificar Texto.

HOJA DE CALCULO EXCEL

• ASIGNAR MACRO A BARRA DE HERRAMIENTAS.

• Herramientas- Personalizar, escogemos Comandos.

• En Categorías escogemos Macros, y en la derecha nos salen dos opciones, escogemos Personalizar Botón.

HOJA DE CALCULO EXCEL

• Pinchamos en Personalizar Botón y arrastramos a la barra de herramientas.

• Al darle botón derecho, le damos a la opción Asignar Macro.

• También tiene otras opciones para cambiar, como el icono del botón.

HOJA DE CALCULO EXCEL

• Hacemos los ejercicios 15