Guía y Cuarto  Taller de Excel

Referencias relativas

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo,  la fórmula :

=C1*2

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

=C2*2

=C3*2

=C4*2

Cuando copiamos fórmulas Excel utiliza el tipo de referencia relativa que es el comportamiento predeterminado. Este tipo de referencia se adapta al contexto de la nueva celda cambiando las referencias de acuerdo a la nueva ubicación. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda específica sin importar que copiemos la fórmula a otra ubicación y para ello necesitamos utilizar las referencias absolutas.

 

Referencias mixtas

Las referencias mixtas son aquellas donde solamente se fija la columna ($A1) o solamente se fija la fila(A$1).

Atajo para crear referencias absolutas y mixtas

Una manera muy sencilla de crear una referencia absoluta es editar la fórmula y posicionar el cursor sobre la referencia relativa que deseamos convertir en referencia absoluta. Posteriormente debemos presionar la tecla F4 lo cual insertará el signo $ tanto para la columna como para el número de fila.

Si pulsas dos veces la tecla F4 observarás que Excel  solamente inserta el signo $ para la fila y si pulsas la tecla F4 tres veces se creará una referencia mixta con la columna fija. Al pulsar una cuarta vez la tecla F4 se removerán todos los signos $ y tendremos de nuevo una referencia relativa. 

Referencia	Ejemplo	Descripción
Relativa	=A1	La columna y la fila pueden cambiar al momento de copiar la fórmula.
Absoluta	=$A$1	Ni la columna ni la fila pueden cambiar.
Mixta	=$A1	La columna no cambia, solamente la fila puede cambiar.
Mixta	=A$1	La fila no cambia, solamente la columna puede cambiar.

REFERENCIA   ABSOLUTA

Cuando se trabaja algún tipo de informe en Excel encontramos que necesitamos hacer una formula que se repite en varias celdas, con el objetivo de ahorrar tiempo y precisión al momento de ejecutar un cálculo.

Excel nos ofrece la posibilidad de fijar celdas:

Para trabajar con referencias absolutas se debe especificar escribiendo el signo $ delante de la letra de la columna y del número de fila.

Por ejemplo $A$3 indica que siempre será la celda A3 y, al aplicar llenados -hacia abajo o hacia la derecha-, u operaciones de copiar y pegar, las referencias que tengan el signo $ delante no serán modificadas. 

Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Digitar el siguiente ejercicio

TALLER

Link

https://drive.google.com/drive/folders/1S79onS1wKhRaQpqwjAmAV3Y4ztsY54Yb?usp=drive_link

Ejercicios con  referencia relativa  absoluta y mixta

Encontrar la venta total de los siguientes productos:

2)

TALLER  2

Taller Ferias

4)

2)     Encontrar el valor  a cobrar de los intereses (7%) del monto préstamo y el valor total de la deuda. 

 

Guía y Segundo  Taller de Excel

Formato Condicional en Excel 

Se utiliza para que, según el valor que tenga una celda, Excel aplique un formato especial o no. Lo utilizaremos para resaltar celdas dependiendo del valor que contengan, para resaltar errores en valores que cumplan una condición determinada...

Mediante la aplicación del formato condicional, podremos identificar de una forma rápida, las variaciones producidas en un intervalo de valores.

Como aplicar el Formato Condicional

Primero tenemos que seleccionar las celdas a las cuales vamos a aplicar el formato condicional. Vamos a la ficha Inicio, al grupo Estilos, y pinchamos en Formato Condicional.

Tenemos diferentes opciones:

Resaltar reglas de celdas, Reglas superiores e inferiores, Barra de datos, Escalas de color y Conjunto de iconos, si queremos aplicar efectos a las celdas.

La opción Nueva regla, nos va a permitir crear una regla personalizada para aplicar un formato determinado a las celdas que cumplan unas condiciones. Pinchamos y se nos abre el cuadro Nueva regla de formato.

Tenemos que seleccionar el tipo de regla que queremos aplicar:

• Aplicar formato a todas las celdas según sus valores
• Aplicar formato únicamente a las celdas que contengan
• Aplicar formato únicamente a los valores con rango inferior o superior
• Aplicar formato únicamente a los valores que estén por encima o por debajo del promedio.
• Aplicar formato únicamente a los valores duplicados.
• Utilice una fórmula que determine las celdas para aplicar formato.

Después en el apartado Editar una descripción de regla, debemos indicar las condiciones que tiene que cumplir la celda y la forma en la que se va a marcar, que será diferente dependiendo del tipo de regla que hayamos elegido. Luego le damos a Aceptar y se creará la regla. Así, cada celda que cumpla las condiciones se marcará.

Si el valor de la celda, no cumple ninguna condición, entonces no se le aplicará ningún tipo de formato especial.

Ejercicio:

Digitar los siguientes datos en Excel

Ahora vamos a encontrar las ventas mayores a  $ 3.000.000 que las muestre con relleno color rojo claro.

- Seleccionar los números

- Clic en la ficha Inicio

-clic en formato condicional

-Reglas para resaltar las celdas

- Clic en Es mayor que...

sale una ventana

- Digitar los datos que nos solicitan 
- Aceptar
Muestra la información solicitada. Cualquier cambio que se le haga a las ventas se releja el formato.
Ahora vamos a realizar otro ejercicio.
- Borramos el formato de las celdas

Encontrar las ventas menores a  $2.500.000  con texto color rojo sin relleno en la celda.

Resultado

TALLER

1) Mostrar con formato texto rojo a los aprendices que sacaron menos de 3 en la nota final.

 La nota final le agregamos formulas:

=suma(primera celda:ultima celda)/cantidad de notas o la fórmula  =promedio(primera celda:ultima celda)

2) Mostrar las ventas mayores a a 40   con texto color verde y relleno azul. Hacer la fórmula del total. 

Guía y Tercer  Taller de Excel

Validación de datos en Excel

La validación de datos en Excel es una herramienta que no puede pasar desapercibida por los analistas de datos ya que nos ayudará a evitar la introducción de datos incorrectos en la hoja de cálculo de manera que podamos mantener la integridad de la información en nuestra base de datos.

Importancia de la validación de datos en Excel

De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea un texto, un número, una fecha o una hora. Sin embargo, los cálculos de nuestras fórmulas dependerán de los datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato correcto.

Por ejemplo, en la siguiente imagen puedes observar que la celda C5 muestra un error en el cálculo de la edad ya que el dato de la celda B5 no corresponde a una fecha válida.

Este tipo de error puede ser prevenido si utilizamos la validación de datos en Excel al indicar que la celda B5 solo aceptará fechas válidas. Una vez creada la validación de datos, al momento de intentar ingresar una cadena de texto, obtendremos un mensaje de advertencia como el siguiente:

Ejercicio

Digitamos los siguientes datos.
Los  usuarios tienen que ingresar edades menores de 18 años.
Pasos
-Seleccionamos las celdas donde van los datos que van a ingresar
-Clic en la ficha Datos
-Clic en validación de datos y de nuevo validación de datos

Sale una ventana y la configuramos:
- En permitir seleccionamos números enteros
-Seleccionar datos Entre
-Ingresamos el mínimo  (1) y máximo (17)

Ahora configuramos el mensaje de Entrada (título y mensaje)

      Configuramos el mensaje de Error (Estilo, título y mensaje)

Aceptamos
Digitamos datos correcto (de 1 a 17) Muestra el mensaje de entrada

Digitamos datos mayores a 17
Muestra el mensaje de error

TALLER

1) Agregamos validación de datos a cada una de las notas (Nota1,  nota2, nota3 y nota4) con mensajes de advertencia y de error.

2) En los días trabajados agregamos validación de datos (mensajes de advertencia y mensajes de error). La nómina es Máximo hasta treinta días.

 

Guía y Primer  Taller de Excel

FUNCIONES BÁSICAS

EN EXCEL EXISTEN GRAN VARIEDAD DE FUNCIONES QUE PUEDE APLICAR A SUS TABLAS O DATOS. UNA FUNCIÓN PUEDE SER UTILIZADA ÚNICAMENTE PARA OBTENER UN RESULTADO O JUNTARLO CON UNA FÓRMULA Y OBTENER MAYOR CAPACIDAD DE OPERACIÓN.

EXISTEN FUNCIONES BÁSICAS EN EXCEL PARA OBTENER RESULTADOS SIMPLES Y SENCILLOS Y SERÁ UN BUEN PUNTO DE PARTIDA PARA COMENZAR A ENTRAR AL MUNDO DE LAS FUNCIONES.

LA FUNCIÓN SUMA

COMO SU NOMBRE LO DICE, ESTA FUNCIÓN PERMITE SUMAR VALORES DE LAS CELDAS, SE UTILIZA MEJOR CUANDO SE SUMAN RANGOS. LA SINTAXIS DE LA FUNCIÓN SUMA ES LA SIGUIENTE:

1. =SUMA(NÚMERO1:[NÚMERO2])

CON LA NUEVA FÓRMULA  SE DIGITA LA PALABRA SUMA, SE ABRE EL PARÉNTESIS, SE DIGITA LA PRIMERA CELDA (ES B4), SE AGREGAN LOS DOS PUNTOS, SE DIGITA LA ÚLTIMA CELDA (F4   EXCEL AUTOMÁTICAMENTE SELECCIONA TODO EL RANGO)  Y SE CIERRA EL PARÉNTESIS. 

QUEDA DE ESTA FORMA:  =SUMA(B4:F4)

 EL RESULTADO ES EL MISMO, PERO LA FÓRMULA ES MAS CORTA.

LA FUNCIÓN PROMEDIO

LA FUNCIÓN PROMEDIO EN EXCEL SIRVE PARA OBTENER LA MEDIA ARITMÉTICA ENTRE UN GRUPO DE VALORES QUE LE PASEMOS COMO PARÁMETROS. BÁSICAMENTE, LO QUE HACE ES SUMAR TODOS LOS VALORES QUE LE PASEMOS Y LOS DIVIDE ENTRE EL CONTEO DE LOS MISMOS.

=PROMEDIO(NÚMERO1;[NÚMERO2])

RETOMANDO EL EJERCICIO VAMOS A ENCONTRAR EL PROMEDIO DE LA VENTA DE AUTOS DE CADA DÍA EN LA SEMANA. QUEDARÍA:

EJERCICIO SUMANDO LAS CELDAS Y LO DIVIDIMOS EN LA CANTIDAD DE CELDAS SUMADAS

 EL RESULTADO

DA EL MISMO RESULTADO

LA FUNCIÓN MÁXIMA

NOS PERMITE  OBTENER EL VALOR MÁXIMO DE UN CONJUNTO DE VALORES, EJEMPLO LA VENTA MÁXIMO, LA NOTA MÁXIMA, EL SUELDO MÁXIMO...

SINTAXIS:

1. =MAX(NÚMERO1;[NÚMERO2])

AHORA MOSTRAMOS LA MÁXIMA VENTA DE AUTOS EN EL DÍA Y LA SEMANA

RESULTADO

LA FUNCIÓN MÍNIMA

 LA FUNCIÓN MIN NOS DEVUELVE EL VALOR MÍNIMO DE UNA LISTA DE VALORES. ES LO CONTRARIO DE LA FUNCIÓN MÁXIMA.

SINTAXIS:

=MIN(NÚMERO1;[NÚMERO2])

AHORA MOSTRAMOS LA VENTA MÍNIMA DEL DÍA Y LA SEMANA.

RESULTADO MÍNIMO

LA FUNCIÓN CONTAR

LA FUNCIÓN CONTAR CUENTA LA CANTIDAD DE CELDAS SELECCIONADAS

SINTAXIS:

=CONTAR(NÚMERO1;[NÚMERO2])

AHORA VAMOS A CONTAR LA CANTIDAD DE VENTAS DEL SIGUIENTE EJERCICIO

RESULTADO

                 

                                           TALLER

Resolver los siguientes ejercicios en Excel

1) Encontrar la nota final de los siguientes aprendices, junto con el promedio de notas, nota máxima,  mínima y la cantidad de notas.

2) Encontrar el total de ventas de autos en el presente año

3) Encontrar el valor a pagar de la nomina  con fórmulas básicas.