• Author / Uploaded
• Marianela Sibauste

Citation preview

Documentación elaborada por www.revistaformacion.com

Excel Avanzado Mayo 2010 www.revistaformacion.com

www.revistaformacion.com

1

Documentación elaborada por www.revistaformacion.com

2

CONTENIDOS DEL CURSO 1.- Conceptos generales de Microsoft Excel: ▫ Edición de celdas. Formatos. Funciones. Series 2.- Validación y Auditorias: ▫ Auditoria de la hoja de cálculo. Rastreo de errores. Restringir las entradas de celda ▫ Solucionar errores de fórmulas. Valores erróneos de Excel (#DIV/0! , #N/A , #NOMBRE? , #NULO! , #NUM! , #REF! , #VALOR )

3.- Funciones:

www.revistaformacion.com

▫ Funciones de texto: Trabajar con caracteres y códigos (Función CARACTER(), Función CODIGO() ) Convertir texto (Función MINUSC() , Función MAYUSC(), Función NOMPROPIO() Formatear texto (Función MONEDA(), Función DECIMAL(), Función TEXTO()

Documentación elaborada por www.revistaformacion.com

3

CONTENIDOS DEL CURSO ▫ Manipular texto: Eliminación de caracteres de una cadena: (Función ESPACIOS(), Función LIMPIAR()) Función REPETIR() Extraer una subcadena: Función IZQUIERDA() Función DERECHA() , Función EXTRAE() ▫ Buscar subcadenas: Funciones ENCONTRAR() y HALLAR() Sustituir una subcadena por otra: Función REEMPLAZAR() , Función SUSTITUIR() www.revistaformacion.com

▫ Funciones lógicas y de información: Uso de la función SI() Realizar diversas pruebas lógicas (Anidar funciones SI() , Función Y() , Función O() ) Funciones de búsqueda y referencia: Función ELEGIR() Búsqueda de valores en tablas: Función BUSCARV().

Documentación elaborada por www.revistaformacion.com

4

CONTENIDOS DEL CURSO • Función BUSCARH() Búsqueda de rangos Encontrar coincidencias exactas Operaciones de búsqueda avanzada (Funciones COINCIDIR() e INDICE() ) • Funciones de fecha y hora: Uso de las funciones de fecha de Excel: Devolver una fecha (HOY(), FECHA(): FECHANUMERO()) Devolver partes de una fecha (Función AÑO() , Función MES() , Función DIA(),

• Función DIASEM(), Función NUM.DE.SEMANA(), función DIA.LAB(), Función FECHA.MES(), • Función FIN.MES() • Calcular la diferencia entre dos fechas: (Función SIFECHA(), DIA.LAB.NETO(), DIAS360()) Uso de las funciones de hora de Excel

www.revistaformacion.com

Documentación elaborada por www.revistaformacion.com

5

CONTENIDOS DEL CURSO ▫ Funciones matemáticas: ▫ Función CONTAR.BLANCO (), CONTAR.SI (), PI (), POTENCIA (), PRODUCTO(), POTENCIA(), RAIZ() Utilización de funciones de redondeo de Excel: Función REDONDEAR(), Función REDOND.MULT(), Funciones REDONDEA.PAR() y REDONDEA.IMPAR() Funciones ENTERO() y TRUNCAR()

• Utilizar redondeo para prevenir errores de cálculo Establecer los decimales en precios Sumar valores: Función SUMAR(). Función SUMAR.SI() Calcular totales acumulados Sumar sólo los valores negativos o positivos en un rango Función RESIDUO()

www.revistaformacion.com

Documentación elaborada por www.revistaformacion.com

6

CONTENIDOS DEL CURSO ▫ Funciones estadísticas: Función CONTAR(), CONTARA() Calcular promedios: (Función PROMEDIO(), Función MEDIANA(), Función MODA() ) ▫ Calcular valores extremos. (Funciones MAX() y MIN() , Funciones K.ESIMO.MAYOR() y K.ESIMO.MENOR() ) Calcular medidas de variación: ( Función VAR(), funciones DESVESTP() y DESVEST() ) y VF()

▫ Trabajar con distribuciones de frecuencia (Función FRECUENCIA(), función DISTR.NORM() ) ▫ Funciones financieras: Función NPER(), PAGO(), TASA(), VA()

4.- Bases De Datos en Excel:

www.revistaformacion.com

Crear una lista. Ordenar una lista Filtros automáticos. Filtros avanzados

Documentación elaborada por www.revistaformacion.com

7

CONTENIDOS DEL CURSO ▫ Tablas y gráficos dinámicos ▫ Subtotales ▫ Funciones para bases de datos

5.- Operaciones avanzadas de análisis de datos: ▫ ▫ ▫ ▫

6.- Gráficos: ▫ Formato de gráfico. Tipos y subtipos de gráficos. Tendencia.

7.- Macros

Tabla de datos Escenarios Buscar Objetivos Solver

www.revistaformacion.com

Documentación elaborada por www.revistaformacion.com

Curso de Excel Avanzado

www.revistaformacion.com

8

Documentación elaborada por www.revistaformacion.com

ALGUNAS IDEAS SOBRE EXCEL • Excel no enseña matemáticas, solo podrá realizar cálculos que sepa realizar a mano. • En general, la parte más complicada del programa es el uso de fórmulas. • Para aprender a usar macros, hay que tener un fuerte conocimiento de Excel. • Todas las funciones tiene utilidad aunque no la veamos a simple vista. www.revistaformacion.com

9

Documentación elaborada por www.revistaformacion.com

INTRODUCCIÓN • Excel es un programa de hoja de cálculo. Los archivos que se generan en Excel, tienen extensión XLS y se denominan Libros. La información que contiene un libro está organizada en diferentes hojas. En las hojas se introduce y almacena información, se realizan cálculos con esos datos y se elaboran gráficos. • Cada hoja es una gran tabla de doble entrada formada por 256 columnas – A, B, C, ..., IV – y 65.536 filas – 1, 2, 3, ..., 65536 -. La intersección de una columna y una fila se define como celda. Es en la celda donde se introducen datos, se incluyen fórmulas, .... En general se identifican con la letra de la columna y el número de la fila – A1, A2, ..., IV65536 -. • Algunos de los usos de Excel pueden ser: ▫ Presupuestos ▫ Previsiones de ventas ▫ Estadísticas ... www.revistaformacion.com

10

Documentación elaborada por www.revistaformacion.com

11

CONCEPTOS INICIALES • Libro. Archivo que genera en Excel, donde registra, calcula y analiza datos. • Hoja de datos. Elemento del libro que se emplea para organizar y agrupar datos homogéneos. Tiene estructura de tabla de doble entrada formada por 256 columnas A, B, ..., IV y por 65.536 filas 1, 2, ..., 65536. • Celda. Unidad fundamental de trabajo en la hoja de datos. Es la intersección de una columna y una fila. • Rango. Conjunto de celdas agrupadas que forman un rectángulo. • Selección. Acción de marcar un rango para actuar sobre él.

• Gráfico. Representación gráfica de datos contenidos en el libro. • Base de datos. Parte de una hoja estructurada en columnas (campos) y en filas (registros), donde se almacena información para su posterior explotación, en Excel o en aplicaciones específicas de bases de datos. • Herramientas. Botones que ayudan a ejecutar determinadas acciones de forma más rápida y fácil. Son equivalentes a opciones del menú, pero ofrecen menos posibilidades que éste. • Objetos gráficos. Formas e imágenes gráficas que mejoran la apariencia de la hoja de cálculo.

www.revistaformacion.com

Documentación elaborada por www.revistaformacion.com

12

CONCEPTOS INICIALES • Macros. Instrucciones que se crean en Visual Basic para automatizar tareas repetitivas, generar funciones y/o elaborar aplicaciones personalizadas. • Importación y exportación. Intercambio de datos entre libros de Excel y archivos creados con otras aplicaciones. • Vinculación e incrustación. Intercambio dinámico de datos entre datos de Excel y archivos creados en otras aplicaciones (vinculación). Inserción de datos de otra aplicación como un objeto (incrustación).

• Plantillas. Archivos que se crean para usar como modelos básicos en otros archivos similares. • Llenado automático. Le permite crear en una hoja de cálculo una serie de valores que se incrementan o repiten arrastrando el cuadro de relleno. • Arrastrar y colocar. Le permite copiar, mover o eliminar en una hoja de cálculo el contenido de celdas, notas y formatos, arrastrando el ratón.

www.revistaformacion.com

Documentación elaborada por www.revistaformacion.com

LA PANTALLA DE EXCEL

www.revistaformacion.com

13

Documentación elaborada por www.revistaformacion.com

DESPLAZAMIENTOS DEL CURSOR Combinación de teclas

Desplazamiento



Mueve el cursor una celda a la derecha



Mueve el cursor una celda hacia abajo



Mueve el cursor una celda a la izquierda



Mueve el cursor una celda hacia arriba

AvPág

Mueve el cursor una celda unantalla hacia abajo

RePág

Mueve el cursor una celda unantalla hacia arriba

ALT + AvPág

Mueve el cursor unantalla hacia la derecha

ALT + RePág

Mueve el cursor unantalla hacia la izquierda

Edición  Ir a… o F5

Mueve el cursor a la celda que le indique

CONTROL + INICIO

Lleva el cursor a la celda A1

CONTROL + FIN

Lleva el cursor a la celda intersección de la última fila utilizada con la última columna utilizada.

INICIO

Lleva el cursor a la celda de primera columna, en la misma fila en la que está el cursor.

CONTROL + AvPág

Lleva el cursor a la hoja siguiente

CONTROL + RePág

Lleva el cursor a la hoja anterior

www.revistaformacion.com

14

Documentación elaborada por www.revistaformacion.com

SELECCIÓN Zona de selección

Cómo seleccionar

Una celda

Haga clic en la celda cuando el puntero del ratón tenga este aspecto Arrastre con el ratón desde una celda – vértice del rango – hasta la celda opuesta en diagonal de ese rango.

Un rango Otro método consiste en hacer clic sobre una celda y con la tecla MAYUS pulsada, hacer clic en otra celda. Seleccionará el rango comprendido entre esas celdas. Múltiples rangos

Seleccione un rango y pulse la tecla CONTROL para seleccionar el siguiente.

Una fila

Haga clic sobre el indicador de fila – número -

Filas contiguas

Haga clic sobre el indicador de una fila y arrastre hacia abajo.

Una columna

Haga clic sobre el indicador de columnas – letra -

Columnas contiguas

Haga clic sobre el indicador de una columna y arrastre hacia la derecha

Extender una selección

Pulse la tecla MAYUS junto a teclas de movimiento

Toda la hoja Múltiples hojas

Haga clic sobre el cuadro que está a la izquierda del indicador de columna A y por encima del indicador de fila 1. Haga clic sobre una etiqueta y con la tecla CONTROL pulsada haga clic sobre otras etiquetas.

Haga clic en una celda de una hoja (1) , pulse la tecla MAYUS y sin soltar pulse en la etiqueta Rango tridimensional

de otra hoja (n) del libro en una de sus celdas. Quedan seleccionadas las celdas de ese rango de las hojas comprendidas entre la hoja (1) hasta la hoja (n). www.revistaformacion.com

15

Documentación elaborada por www.revistaformacion.com

16

TIPO DE DATOS • En una celda puede introducir datos de diferente tipo: ▫ Texto, caracteres alfanuméricos. No se pueden realizar operaciones aritméticas con ellos. Puede introducir un máximo de 32.767 caracteres. La cantidad de texto que se muestra depende del ancho de columna y del formato aplicado a la celda. ▫ Número, se entiende por dato numérico, un dato con cuyo valor se pueden realizar operaciones matemáticas y aritméticas. Su formato puede ser muy variable: número, moneda, fecha, hora, ... ▫ Fórmulas, se clasifican en operaciones y en funciones de Excel. Las fórmulas se inician con el símbolo =. Se opera con las referencias de las celdas y muy pocas veces con números o valores fijos. Puede introducir un máximo de 1024 caracteres para la escritura de la fórmula. www.revistaformacion.com

Documentación elaborada por www.revistaformacion.com

17

REFERENCIAS

Relativa

Absoluta

Cambia la celda

Fija la celda

Fija la fila

Fija la columna

ColFila

$Col$Fila

Col$Fila

$ColFila

=A$1

=$A1





=B$1

=$A1

 =A$1



=A1  =B1  =A2

=$A$1  =$A$1  =$A$1

www.revistaformacion.com

Mixtas

=$A2

Documentación elaborada por www.revistaformacion.com

18

OPERADORES DE CÁLCULO

OTROS

COMPARACIÓN

ARITMÉTICOS

Operador

Significado +

Suma

-

Resta

*

Multiplicación

/

División

^

Exponente

%

Porcentaje

=

Igual a

>

Mayor que


=

Mayor o igual que