Aulatecnic.es

La formación aumenta la autoestima y mejora el rendimiento

A.
Mejora tus hojas de cálculo incluyendo listas desplegables con validación de datos
B.
La función buscarV te dará múltiples soluciones para hacer más dinámicas tus hojas de cálculo
C.
Conocer las tablas dinámicas te permitirá realizar tantos informes como datos se recogen en una lista de datos.

Todo lo que debes saber de Excel:

Nivel inicial y medio.

 

1 Movimientos en hoja de Cálculo
2 Operadores Matemáticos
3 Referencias Relativas, Mixtas y Absolutas
4 La función Suma
5 La función Promedio, Max, Min y Contar
6 Formato Condicional
7 Ordenar y Filtrar datos
8 Validación de datos
9 Subtotales

 

 

Movimientos en hoja de Cálculo

Movimiento
Combinación de teclas
Celda Abajo
Celda Arriba
Celda Derecha
Celda Izquierda
Pantalla Abajo
Pantalla Arriba
Celda A1
Primera celda de la columna activa
Última celda de la columna activa
Primera celda de la fila activa
Última celda de la fila activa
Hoja Siguiente
Hoja Anterior
FLECHA ABAJO
FLECHA ARRIBA
FLECHA DERECHA
FLECHA IZQUIERDA
AVPAG
REPAG
CTRL+INICIO
FIN + FLECHA ARRIBA
FIN + FLECHA ABAJO
FIN + FLECHA IZQUIERDA o INICIO
FIN + FLECHA DERECHA
CTRL+AVPAG
CTRL+REPAG

 

Operadores Matemáticos

Operación

Tecla

Inicio de operación

Suma
Resta
Multiplicación
División

=

+
-
*
/

COMPARACIÓN

Igualdad =
Desigualdad <>
Menor que <
Mayor que >
Menor o igual que <=
Mayor o igual que >=

 

 

 

Referencias Relativas, Mixtas y Absolutas

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.

Existen 3 tipos de referencias:

Referencia Relativa: Las referencias de filas y columnas cambian si se copia la formula en otra celda, es decir se adapta a su entorno porque las referencias las hace con respecto a la distancia entre la formula y las celdas que forman parte de la formula. Esta es la opción que ofrece Excel por defecto. A2 =A1+2
Si ahora copiamos la celda A2 en B3, como la copiamos una columna hacia la derecha y en una fila hacia abajo, la fórmula cambiará por: =B2+2 . Lo que variará es la referencia a la celda A1 , al copiarla una columna hacia la derecha se incrementará el nombre de la columna en uno, es decir, en vez de A pondrá B y al copiarla una fila hacia abajo en vez de fila 1 pondrá 2 , resultado =B2+2 . Para mantener en la fórmula sumar 2 al contenido de la celda superior.

Referencia Absoluta: Las referencias de filas y columnas no cambian si se copia la formula a otra celda, las referencias a las celdas de la formula son fijas.
Supongamos el ejemplo: A2 =$A$1+2
Si ahora copiamos la celda A2 en B3 , aunque la copiemos una columna hacia la derecha y en una fila hacia abajo, como delante de la columna y delante de la fila encuentra en signo $ no variará la fórmula y en B3 pondrá =$A$1+2 .

Referencia Mixta: Podemos hacer una combinación de ambas referencias, podemos hacer que las filas sean relativas y las columnas absolutas o viceversa.
Ejemplo: A 2 =$A1+2
Si ahora copiamos la celda A2 en B3 , como hay un signo $ delante de la columna aunque se copie una columna más a la derecha ésta no variará, pero al no tener el signo $ delante de la fila, al copiarla una fila hacia abajo la fila cambiará por 2 en vez de 1 y el resultado será =$A2+2

 

La función Suma Resultado de imagen de icono suma excel

La sintaxis de cualquier función es:
nombre_función(argumento1;argumento2;...;argumentoN)
Siguen las siguientes reglas:
- Si la función va al comienzo de una fórmula 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.

=SUMA(A1:B3) esta función equivale a =A1+A2+A3+B1+B2+B3

Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresiones.
Por ejemplo, si tenemos la siguiente función =Suma((A1+A3);(A2-A4)) Excel resolverá primero las expresiones (A1+A3) y (A2-A4)
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.


La función Promedio, Max, Min y Contar
Promedio (calcula la media aritmética), Cuenta (cuenta valores), Máx (obtiene el valor máximo) o Mín (obtiene el valor mínimo)

Utilizar Promedio: si el intervalo A1:A20 contiene números, la fórmula =PROMEDIO(A1:A20) devuelve el promedio de dichos números.
Otro ejemplo:

=PROMEDIO(A2:A6;5)

Promedio de los números en las celdas A2 a A6 y el número 5.

Usar la función MAX para obtener la cantidad más alta de un rango o matriz de números.

=MAX(A2:A6)

El valor más alto del rango A2:A6.

 

=MAX(A2:A6;30)

El valor más alto del rango A2:A6 y el valor 30.

Usar la función MIN para obtener la cantidad más pequeña de un rango o matriz de números.

=MIN(A2:A6)

El valor menor de los números del rango A2:A6.

 

=MIN(A2:A6;0)

El valor menor de los números del rango A2:A6 y 0.

Usar la función CONTAR para obtener la cantidad de entradas en un campo de número de un rango o matriz de números.
Para contar los números en el rango A1:A20: =CONTAR(A1:A20).

 

Formato Condicional

El formato condicional en Excel nos permite dar color o poner un icono a una celda o a un conjunto de celdas según un criterio condicional. Este recurso facilita dar formato a celdas de una manera rápida y actualizando el formato según el criterio indicado.

Las reglas para aplicar el formato condicional nos vienen dadas por el propio menú condicional donde sólo hay que seleccionar la regla, anotar el criterio y escoger el formato a aplicar.

En la pestaña Inicio, haga clic en Formato condicional > Nueva regla.

Comprobar formato condicional sobre fechas de vencimiento en Excel

En la pestaña Inicio, haga clic en Formato condicional >Barras de color

Comprobar formato condicional sobre fechas de vencimiento en Excel

En la pestaña Inicio, haga clic en Formato condicional >Escala de color

Comprobar formato condicional sobre fechas de vencimiento en Excel

En la pestaña Inicio, haga clic en Formato condicional >Conjunto de iconos

Comprobar formato condicional sobre fechas de vencimiento en Excel

.

Ordenar y filtrar datos

Para ordenar datos es necesario tener una lista dónde la primera fila debe contener los títulos de los datos de la misma.

No debe haber ninguna columna ni ninguna fila completamente vacía para hacer una selección automática. A continuación situar el cursor en cualquier lugar de la lista y seleccionar Datos en el menú Excel.

Para poder escoger varios niveles de ordenación es necesario escoger el botón Ordenar mostrará un cuadro de diálogo que le permite escoger la columna que queremos ordenar en primer nivel, para ello es necesario tener activado que " Mis datos tienen encabezado" . En Ordenar por se puede escoger la columna por la cual se quiere ordenar en primer lugar y en Orden se puede escoger si se quiere cambiar el orden alfabético de Z a A.

En el caso de querer ordenar la segunda columna en caso de que la primera se repita se ha de agregar un nivel.

Para ello sólo hay que hacer clic en Agregar Nivel y aparece otra línea por debajo de la anterior. Se ha escoger la columna por la que se quiere ordenar en segundo nivel y también el orden.

Ahora nuestra lista estará ordenada por producto de la A a la Z y en el caso que se repita producto la segunda columna se ordenrá alfabéticamente por cliente de la A a la Z

Otra manera rápida de ordenar una lista es posicionar el cursor en una celda de la columna que queremos que sea la que deseamos que quede ordenada junto con toda la lista.

El botón de menor a mayor nos devolverá la lista ordenada por la columna dónde estaba situado el cursor, si el contenido es de texto se habrá ordenado de la A a la Z y si el contenido es numérico se ordenará de menor a mayor.

El botón de mayor a menor nos devolverá la lista ordenada por la columna dónde estaba situado el cursor, si el contenido es de texto se habrá ordenado de la Z a la A y si el contenido es numérico se ordenará de mayor a menor.

Filtrar de datos

Para filtrar datos es necesario activar el filtro y de esta manera todas las columna reciben un icono con forma de flecha hacia abajo que indica que puede ser filtrada.

 

   
Los filtros permiten mostrar sólo aquellas filas de la lista que cumplen con los datos solicitados en el filtro.
Un ejemplo sería filtrar sólo los datos de la columna productos que su producto sea Café de Malasia

Otro ejemplo sería filtrar sólo los datos de la columna productos que su producto contenga Café.

Los datos de texto pueden ser filtrados por tantas condiciones como vemos en la imagen.

Es igual a..
No es igual a...
Comienza por..
Termina con..
Contiene...
No contiene..

Otro ejemplo sería filtrar sólo los datos de la columna trimestre 1 que el valor sea superior a 200 €.

Los datos numéricos pueden ser filtrados por tantas condiciones como vemos en la imagen

Es igual a...
No es igual a ..
Mayor que...
Mayor o igual que...
Menor que...
Menor o igual que..
Entre....
Diez mejores...
Superior del promedio...
Inferior del promedio...

 

Otro ejemplo sería filtrar por color de celda y también se puede filtrar por color de fuente

 

   
   

 

 

 

 

Validación de datos

La validación de datos nos va a permitir a asegurar que el contenido de la celda es aquel que queremos guardar en la misma. En el caso que el contenido sea texto podemos crear una regla de validación dónde limitemos el número de caracteres o bién el contenido

Datos / Validación de datos

 

   

Un ejemplo para validar que el texto no sea superior a 9 caracteres podría ser para guardar un NIF, en este caso nos aseguramos que al introducir los datos siempre haya los caracteres deseados.

Datos/Validación de datos / Longitud del Texto

Entre Mínimo 9 y Máximo 9

Un ejemplo para validar que el texto sea exactament el de una lista desplegable.

Datos/Validación de datos/Lista

Es necesario tener los datos de la lista en un lugar de la misma hoja o bién en otra hoja del libro. =$E$3:$E$6

Es mejor poner nombre a la lista

Fórmulas /nombre/ Crear nombre/

=provincias -->=$E$3:$E$6

Un ejemplo para validar que el nº de paquetes no supere los 50 y no sean menos de 1

Datos/Validación de datos/Número entero

Entre Mínimo 1 y máximo 50

 

Nº de paquetes  

Un ejemplo para validar que la fecha sea exclusivamente del mes de enero de 2020

Datos/Validación de datos/Fecha

Entre fecha inicia 1/1/2020

fecha final 31/1/2020

   
   
   
 
   

 

Subtotales

 

Se aplica a una lista de datos que tiene un contenido repetido en alguna de sus columnas y nos permite agruparlas para poder hacer cálculos con los contenidos de datos numéricos.

En primer lugar la lista debe tener una fila de encabezado y estar ordenado por la columna que deseamos agrupar.

Se coloca el cursor en cualquier lugar de la lista y se selecciona Datos/Subtotales

En el cuadro de diálogo se ha de seleccionar:

Para cada cambio en: la columna que se ha ordenado

Usar función: Escoger Suma

Agregar subtotal: Marcar las casillas con contenido numérico que deseamos sumar