Guía y  Segundo   Taller  de Excel Intermedio

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. Configurar de rojo las celdas con formato condicional las notas menores a 3,0

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. 

3) En la producción semanal configurar el relleno  rojo a la producción menor de  1200 Und

1) Calcular el total de la producción de Descartables

Guía y  Primer   Taller  de Excel Intermedio

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 los préstamos  entre $ 200.000 y $ 400.000  con texto color verde

 

Guía y Quinto  taller de Excel Básico

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])

Ejercicio con la fórmula anterior.

Digitar en Excel el siguiente ejercicio y Sumar las ventas de los autos desde el lunes hasta el viernes:

Ahora desarrollarlo  con la fórmula Suma(Numero1:Numero2)

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.

Es lo mismo que decir:  =(a1+a2+a3+a4)/4  (cantidad de celdas), con la fórmula promedio Quedaría:   =Promedio(a1:a4)  Nos da el mismo resultado, pero es la fórmula promedio.

Sintaxis:

=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

Ahora como queda con la fórmula promedio:

Da el mismo resultado

PRIMER TALLER

1) Encontrar el total vendido de los autos  de los primeros meses, (utilizando la fórmula suma), junto con su promedio de venta (Fórmula promedio).

2) Encontrar la nota final de cuatro notas:

N. FINAL Pueden utilizar la fórmula   =suma(primera celda:ultima celda)/cantidad de notas

 o con la fórmula promedio. 

El promedio de notas es con la Fórmula promedio.

Enviar al correo 

wfsuarez73@misena.edu.co

Segunda Guía y Taller  Virtual para los Aprendices de Excel Intermedio del Municipio de Lérida

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 2

1) Encontrar el sueldo a pagar de los empleados utilizando las fórmulas vistas en la guía:

-Valor a pagar: Multiplicar el sueldo diario por los días trabajados

-Total es con la fórmula suma

-Promedio es con la fórmula promedio

-Máximo con la fórmula Max

-Mínimo con la fórmula Min

-Cantidad  con la fórmula contar

2)  Encontrar las ventas de los viajes realizadas por los asesores  comerciales:
-Ventas es la suma de todos los destinos por cada asesor
-Promedio venta es con la fórmula promedio
-Mayor venta con la fórmula máxima
-Menor venta con la fórmula mínima
-Número de ventas con la fórmula contar.

 

Guía y cuarto  taller de Excel Básico

GRÁFICOS

Los gráficos se usan para presentar series de datos numéricos en formato gráfico y así facilitan el análisis de grandes cantidades de información.

Tipos:

Gráficos de columnas:
Muestra los cambios que han sufrido los datos en el transcurso de un período de tiempo determinado o ilustra las comparaciones entre elementos.

Gráficos de líneas:Muestran las tendencias en los datos a intervalos idénticos.

Gráficos Circulares:

Muestran el tamaño proporcional de los elementos que conforman una serie de datos, en función de la suma de los elementos. Siempre mostrará una única serie de datos y es útil cuando se desea destacar un elemento significativo.

Gráficos de Barras:
Ilustran comparaciones entre elementos individuales.

Gráficos de áreas:
Destacan la magnitud de los cambios con el transcurso del tiempo.

Gráficos Dispersión:
Muestran la relación entre los valores numéricos de varias serie de datos o trazan dos grupos de números como una única serie de coordenadas XY. Este tipo de gráfico se suele usar para datos científicos.

PASOS

- Digitar  el siguiente ejercicio

- Seleccionar los datos (toda la tabla)incluyendo los títulos

VENTAS 2020
EMPLEADO	VENTAS
Carlos Duarte	2000000
Jorge Gomez	3500000
Luis Pomar	1200000
Gloria oviedo	4560000
Karen Buitrago	7000000
Lola Mento	2340000
Lucia Suarez	3000000

- Clic en la ficha Insertar

- Seleccionar el gráfico, ejemplo columnas

SALE EL GRÁFICO (Ejemplo columnas)

Configuramos el gráfico a nuestro gusto con la ficha Diseño y Formato.

El gráfico se puede mover con clic sostenido desde la mitad, también se puede ampliar o reducir desde las esquinas.

Cuando esta seleccionado el gráfico se activan tres nuevas fichas o dos (Según la versión del Excel), que permiten configurar el color del gráfico, el tipo de gráfico y efectos (3d, sombra... ), las fichas son: Diseño, Presentación y Formato 

Gráfico configurado. A ustedes les queda mejor.

Taller

1)  Insertar  cinco gráficos diferentes con cinco  ejercicios. Configurar los gráficos

 

Guía y tercer  taller de Excel Básico
Entorno de Excel

Formato de celdas en Excel 

Ya hemos revisado cómo utilizar el formato de números en Excel para modificar la apariencia de valores numéricos. Ahora revisaremos las modificaciones que podemos realizar a la apariencia de la celda para cambiar sus bordes o su color de relleno entre otras cosas más.

El formato de celdas no es indispensable en el funcionamiento de las hojas de cálculo, pero puede ayudar a tener una mejor visualización y entendimiento de los datos presentados. Las herramientas de formato en Excel 2013 las podemos encontrar en tres ubicaciones:

1.   En la Cinta de opciones.

2.   En la mini barra que se muestra al hacer clic derecho sobre una celda.

3.   En el cuadro de diálogo Formato de celdas.

Ya que el formato de celdas es una funcionalidad muy utilizada en Excel, podremos acceder a una gran cantidad de comandos desde las tres ubicaciones antes mencionadas. Los comandos de la Cinta de opciones se encuentran en la ficha Inicio, repartidos en los grupos Fuente, Alineación y Estilos. También podemos encontrar varios de estos comandos en la mini barra mostrada al hacer clic derecho sobre una celda o rango.

A través de este cuadro de diálogo tendremos un mayor control sobre el formato aplicado a una celda de Excel. La sección Número nos muestra 12 categorías de formato de las cuales podemos elegir y además configurar ciertas propiedades particulares. A continuación describo brevemente cada una de las 12 categorías:

1. General: El formato General es el formato predeterminado de todas las celdas de una hoja y despliega los números tal como son ingresados excepto cuando el valor es muy grande para caber en la celda y lo convierte a notación científica.
2. Número: Este formato nos permite especificar la cantidad de posiciones decimales que deseamos mostrar y además indicar si deseamos incluir el separador de miles. Otra opción de configuración es la de indicar el formato de los números negativos ya sea solamente con el símbolo negativo (-), o de color rojo, o ambas opciones.
3. Moneda: El formato de Moneda nos permite elegir el símbolo a utilizar así como la cantidad de decimales a utilizar. Al igual que el formato anterior, podemos indicar la apariencia de los números negativos.
4. Contabilidad: El formato de Contabilidad es diferente del de Moneda en que los símbolos de moneda siempre están alineados de manera vertical.
5. Fecha: Esta opción nos permite elegir entre diferentes formatos para una fecha.
6. Hora: Nos permite elegir un formato para las horas ingresadas en una celda.
7. Porcentaje: Nos permite desplegar el símbolo de porcentaje e indicar la cantidad de decimales.
8. Fracción: Para las fracciones mostradas en la hoja de cálculo podemos elegir entre nueve diferentes formatos.
9. Científica: Para números expresados en notación científica podemos especificar la cantidad de decimales a mostrar.
10. Texto: Cuando este formato es aplicado a una celda, Excel tratará dicho valor como un texto, inclusive si es un número.
11. Especial: Esta opción contiene formatos especiales y dependen de la Configuración regional seleccionada. Algunas opciones de formatos especiales son los códigos postales, los números de teléfono o los números de seguridad social.
12. Personalizada: La opción de formato personalizado nos da la posibilidad de crear nuestros propios formatos de números. Esta es una opción que requiere una explicación más amplia por lo cual encontrarás un vínculo a otro artículo en la sección de material adicional.

Además de la Cinta de opciones y la mini barra, podemos encontrar estas opciones de configuración de formato dentro del cuadro de diálogo Formato de celdas. Podemos acceder a este cuadro de diálogo de diferentes maneras:

• Haciendo clic en el indicador de cuadro de diálogo de los grupos Fuente o Alineación de la ficha Inicio.
• Haciendo clic derecho sobre una celda y dentro del menú desplegable seleccionar la opción Formato de celdas.
• En la ficha Inicio, dentro del grupo Celdas, pulsar el botón Formato y seleccionar la opción Formato de celdas.

Además existen comandos de la Cinta de opciones que ofrecen la posibilidad de consultar más opciones y que nos llevarán a este mismo cuadro de diálogo. Por ejemplo, el comando Inicio > Alineación > Formato de alineación de celdas abrirá el cuadro de diálogo Formato de celdas en la sección correspondiente:

Resultado final

                                                       TALLER EN FORMACIÓN

1) Encontrar la comisión, suplemento, suma de comisiones y total  que le corresponde a cada vendedor

2) Encontrar el total de ventas por cada asesor y por cada viaje

 

 

Guía y segundo taller de Excel Básico
Entorno de Excel

1) Encontrar el costo total de la obra con fórmulas básicas.

Insertar un gráfico por la nómina y otro por  los materiales

2) Encontrar el total  ventas por cada asesor y por cada viaje.