MANUAL CONTENIDO CURSO DE EXCEL AVANZADO

MANUAL CONTENIDO CURSO DE EXCEL AVANZADO

INDICE DE CONTENIDO

UNIDAD 1: FUNCIONES Y FORMULAS        

EXCEL Y USO DE FORMULAS        

FUNCIONES EN EXCEL        

ALGUNAS FUNCIONES Y SU USO:        

REFERENCIAS RELATIVAS Y ABSOLUTAS:        

UNIDAD 2: ORDEN, FILTROS, TOTALES Y SUBTOTALES        

ORDENAMIENTO        

AGREGAR NIVEL:        

FILTROS        

TOTALES Y SUBTOTALES:        

UNIDAD 3. MANEJO AVANZADO DE DATOS        

TABLA DINAMICAS:        

GRAFICOS DINAMICOS        

GRAFICOS        

FUNCIONES BD        

UNIDAD 4. MACROS        

MACROS        

El lenguaje VBA        

Habilitación de Macros        

GRABADORA DE MACROS        

UNIDAD 1: FUNCIONES Y FORMULAS

EXCEL Y USO DE FORMULAS

Antes de comenzar a explicar lo que es una formula, debemos entender primero lo que es una celda y un rango. Podemos observar en la figura que está a continuación una planilla Excel.

[pic 1]

Como podemos observar es una hoja con varios “recuadros” los cuales se encuentran clasificados por el lado izquierdo mediante números, y en la parte de arriba de forma horizontal por letras, cada recuadro lleva por nombre celda.

Los números nos indican una fila, y esta se extiende de izquierda a derecha.

Las letras en cambio nos indican columnas, y estas se extienden desde arriba hacia abajo.

Estas letras y números nos sirven para poder identificar la ubicación de una celda dentro de la hoja de cálculo, si vemos la celda que está seleccionada en la figura podemos identificar que su ubicación está en la “columna B” junto con la “fila 2”. Se escribe entonces la celda de la siguiente manera: “B2”.

Ahora que ya se explicó cómo se escribe una celda, se puede explicar lo que es un rango, un rango es en Excel una cantidad de celdas seleccionadas o celdas que a nosotros nos interesa ya sea por su información o por sus datos.

[pic 2]

Basados en la figura anterior el rango se escribiría de la siguiente manera, B2:B9

Si quisiéramos abarcar la columna C dentro del rango escribiríamos este de la siguiente forma, B2:C9 o sea lo separamos por dos puntos para un rango de datos

Si deseásemos elegir celdas con valores intercalados, saltados o específicos tendríamos que separarlos por un punto y coma “;”.

Otra consideración es que la planilla de cálculos en Excel hace la analogía a un libro, y este libro contiene hojas. Las hojas con en las que normalmente trabajamos, podemos tener Hoja1, Hoja2, Hoja3, según sea la necesidad.

Esto es lo más básico para poder entender una planilla de cálculo, ahora podremos entender una  fórmula. En términos prácticos, se puede entender como la “receta” para obtener un resultado de un patrón.

En la planilla de Excel las formulas se ingresan  anteponiendo un signo = y/o en la barra de formula[pic 3].

[pic 4]

Como sabemos Excel nos proporciona resultados matemáticos, por lo cual debemos hablar en el idioma matemático que usa Excel para crear nuestras formulas. A continuación se describe como ingresar las operaciones básicas en Excel:

Ejemplo 1: 

Si yo quiero siempre obtener el promedio de notas de un curso de 12 alumnos, lo que debo haces es sumar las 12 notas y luego ese resultado dividirlo por la cantidad de alumnos, la cual es 12. Si nos fijamos las notas del curso pueden variar, dependiendo del nivel del curso, pero la cantidad de alumnos es fija, o sea es de 12 alumnos, entonces podemos observar que nuestro patrón es 12 y de este, siempre se saca el promedio.

Para el ejemplo anterior, en Excel se debe escribir la fórmula de la siguiente manera:

=(B3+B4+B5+B6+B7+B8+B9+B10+B11+B12+B13+B14)/12

[pic 5]

Esta fórmula la podemos simplificar si usamos el rango de datos en el cual queremos sacar el promedio, si nos fijamos atentamente en la planilla anterior, podemos observar que el rango es desde la celda B3 hasta la celda B14, debemos reescribir la fórmula de la siguiente manera:

=SUMA(B3:B14)/12   La cual es mucho más práctica que escribirla como lo habíamos hecho anteriormente.

Ejemplo 2:

Tenemos productos de una tienda, pero estos están con valores Netos. Debemos realizar una planilla de cálculo en Excel, la cual esté actualizada la información del IVA (impuesto al valor agregado)

[pic 6]

Sabemos que el IVA corresponde al 19% del valor total de un producto, por lo que debemos confeccionar una formula la cual nos calcule de forma automática el valor del IVA.

Comencemos por que al valor debemos agregarle el 19%, o sea podemos escribir la fórmula de dos formas, tomando el valor 1,19 y multiplicándolo al valor a calcular (aumentamos el valor en un 19%). O bien multiplicamos el 0,19 del valor (extraemos el valor en 19%)  y luego el resultado se le suma al valor en cuestión.

Veámoslo expresado en la formula según la primera forma seria: =(B3*1,19)

Expresado de la segunda forma seria: =(B3*0.19)+B3

Como podemos observar es más sencilla de expresarla como =(B3*1,19)

FUNCIONES EN EXCEL

Como ya hemos repasado, podemos diseñar nuestras fórmulas para el desarrollo de nuestra planilla de cálculo, pero existen fórmulas ya preestablecidas, las cuales optimizan y nos ayudan a reducir el diseño de estas. Por ejemplo si queremos sumar varios valores lo haríamos tradicional de la siguiente manera: =A1+A2+A3+A4+A5+A6+A7, esto se puede simplificar notablemente llamando a la función SUMA lo que nos quedaría =SUMA(A1:A7) simplificando nuestro trabajo.

Las funciones las podemos visualizar haciendo clic en el símbolo ƒ× de la barra de fórmulas tal como se muestra en la figura:

[pic 7]

ALGUNAS FUNCIONES Y SU USO:

FUNCIONES MATEMATICAS

SUMA:        Esta función nos permite realizar la suma de un rango de valores dentro de la planilla, solo debemos escribir la palabra suma seguida de los paréntesis y el rango a calcular, su notación es =SUMA(rango a sumar)

ENTERO:        Esta función nos sirve para redondear un valor, el cual si se encuentra con decimales, este se aproxima al valor entero y no se escriben los valores después de la coma.

PRODUCTO:        Similar a la operación suma, pero su función consiste en multiplicar valores, su notación es =PRODUCTO(rango a multiplicar)

FUNCIONES ESTADISTICAS

PROMEDIO:        Esta función nos facilita el cálculo de promedios dentro de los valores deseado, solo debemos escribir la palabra PROMEDIO y luego colocar el rango deseado para los valores a calcular. Su notación es =PROMEDIO(rango de valores a promediar)

MAX:        Esta función nos permite identificar el valor máximo de un numero dentro de un rango de valores, por ejemplo, si tenemos mucha información de cotizaciones y deseamos saber cuál es la más cara, solo escribimos la palabra MAX seguida de los valores donde deseamos extraer el máximo, su notación es  =MAX(rango donde se desea extraer el máximo)

MIN:        La función mínimo tiene la misma idea que la anterior, pero sirve para extraer el dato de menor valor dentro de un rango, su notación es =MIN(rango donde se desea extraer el valor mínimo)

CONTAR:        Cuenta el número de celdas que contienen números, como aparece en la figura.

[pic 8]

CONTAR.SI:        Cuenta el número de celdas que contienen la restricción lógica establecida, por ejemplo dentro del rango de números se pide que se cuente solo los que son mayores a “2”

[pic 9]

FUNCION LOGICA

...