Excel Básico Repaso

Repaso Excel Básico

Calle Coromoto, Diagonal al C.C La Fuente Tlf: 0264-2411155-2417282

Repaso Excel Básico. Tabla de Contenido

o Formato Condicional (Formato > Formato Condicional). o Funciones lógicas (Si, Y, O). 

Anidación de funciones lógicas.



Celdas de Referencia Fija.

o Validación de Datos (Datos > Validación…) o Series de datos (Edición > Rellenar > Series) o Búsqueda de información (BUSCARV).

CENIBIT

Página 2

Repaso Excel Básico. Formato Condicional: El ejemplo a seguir es en una hoja de notas de un profesor lograr que esta indique los alumnos están o no reprobados, por ejemplo la que se anexa, ojo los resultados se obtiene con la función promedio:

Se selecciona el rango de datos a aplicar el formato (E2:E6), en la barra de Menú se selecciona la opción Formato > Formato Condicional.

Nótese que se basa en una opción que sigue las siguientes reglas:

CENIBIT

Página 3

Repaso Excel Básico. Base de comparación

Relación

Valor de la Celda Formula

Patrón de Comparación Valor de Celdas que definen el rango

Entre No está entre = (igual) <>(no igual a) Valor de la Celda > (mayor) < (menor >= (mayor igual) <= (menor igual) La idea acá es destacar a los reprobados, entonces debe cambiarse el aspecto con el botón formato, es posible agregar cambios a la fuente, tramas y bordes, en este caso específico los cambios son: Fuente Color Amarillo, Sin bordes y Trama Roja, obteniendo por resultado.

Es posible establecer únicamente y exclusivamente establecer hasta (tres) 3 formatos condicionales, a saber el semáforo (Verde = Ok, Amarillo = Alerta y Rojo = Malo).

Funciones Lógicas Estas sirven para que el libro de Excel realice una u otra función según sea el resultado de una comparación de factores, el mejor ejemplo es el formato condicional.

Existen varias funciones lógicas, sin embargo a los efectos de

este curso se utilizan las siguientes:

CENIBIT

Página 4

Repaso Excel Básico. SI Es una función que se basa en la verificación si un determinado valor ubicado en una celda cumple o no con cierta condición, según el siguiente la siguiente: =Si(prueba_lógica;valor_si_verdadero;valor_si_falso) Siendo Base de comparación

Prueba Lógica Relación

Valor de la Celda

Patrón de Comparación Valor de la Celda

= (igual) <>(no igual a) > (mayor) < (menor >= (mayor igual) <= (menor igual) Valor si Verdadero, puede ser un número, un texto o una ecuación. Valor si falso, puede ser un número, un texto o una ecuación.

Y Es una función cuyo resultado es verdadero si todas las condiciones a evaluar son verdaderas y falso si alguna de ellas es falsa, según el siguiente la siguiente: =Y(valor_lógico_1; valor_lógico_2; valor_lógico_3;…)

O Es una función cuyo resultado es verdadero si al menos alguna de las condiciones a evaluar es verdadera y falso si todas las condiciones a evaluar son falsas, según el siguiente la siguiente: =O(valor_lógico_1; valor_lógico_2; valor_lógico_3;…)

CENIBIT

Página 5

Repaso Excel Básico. Anidación de Funciones: Anidar se traduce en enlazar una función dentro de otra, por ejemplo en esa hoja de evaluación existe una columna de comentarios, en la cual debe aparecer alguno de los siguientes: Criterio Excelente Distinguido Bueno Regular Malo

Rango 18-20 16-18 14-16 10-16 0-10

El criterio para anidar funciones es comenzar desde el nivel más alto para barrer nivel por nivel cada uno de los rangos a evaluar, esto garantiza de antemano que cada vez que se elimina un rango este es el techo del que le sigue.

La razón por la cual no se verifica desde abajo hacia arriba obedece

a que una vez cumplido el criterio cesa la verificación, así de abajo hacia arriba un alumno con 18 puntos será un alumno regular, mientras que de arriba abajo será excelente, adicional a esto de arriba abajo, la anidación es igual a la cantidad de criterios -1, 5 criterios = 4 anidaciones, la máxima cantidad de funciones a anidar es siete (7), Al invocar la función se rellenaría con los siguientes valores: Prueba Lógica Valor si verdader o Valor si falso:

E2>=18 “Excelente” SI(E2>=16; “Distinguido”;SI(E2>=14 ; “Bueno”;SI(E2>=10; “Regular”; “Malo”)))

Obteniendo los siguientes resultados:

CENIBIT

Página 6

Repaso Excel Básico.

Celdas de Referencia Fija: El ejemplo anterior funciona muy bien hasta que se asigna pesos a cada uno de los exámenes:

La

definitiva

se

calcula

entonces

así:

=B11*B9+C11*C9+D11*D9,

sin

embargo, esta operación involucra a las celdas que contienen los valores en porcentaje,

así

que

la

expresión

correcta

es:

=B11*$B$9+C11*$C$9+D11*$D$9, resultando:

Nótese las variaciones de las definitivas y la utilidad de las funciones utilizadas al momento, pero que significan esos signos ($) y cual es su función. El

CENIBIT

signo

Página 7

Repaso Excel Básico. ($) refiere que precede a un valor fijo (fija o columna) y se activa presionando F4, tiene 4 formas básicas. Significado F4 Ambos fijos F4 – F4 Fija la Fila F4 – F4 – F4 Fija la Columna F4 – F4 – F4 – F4 Ninguno fijo

Ejemplo $A$1 A$1 $A1 A1

Validación de Datos: Ahora que pasa si un profesor ingresa notas negativas o calificaciones por encima de 20 pts, eso sin duda que afecta el promedio del alumno, por lo cual es posible establecer criterios para restringir el tipo de dato que se incorpora. Seleccione el rango de los datos (B2:D8), en la barra de menú Datos > Validación…, existen ocho (8) posibles valores (Cualquier valor, Número Entero, Decimal, Lista, Fecha, Hora, Longitud de Texto, Personalizada), a los efectos nos interesan dos (2) casos, el primero Número Entero (0, 1, 2…) ó Decimal (cualquier número positivo o negativo), la mas ventajosa es la opción decimal (con un rango entre 0 y 20).

El

sistema

permite

ingresar

información: Titulo Mensaje

Mensaje de Entrada

Mensaje de Error

Ingrese Datos Notas del 0 al 20

Calificación no válida Ingrese solo calificaciones entre 0 y 20

Presione ENTER e intente ingresar calificaciones negativas o superiores a 20 y observe.

Búsqueda de Información (Buscarv) Lo que se ha estudiado al momento lo vamos a utilizar para construir una factura totalmente funcional, para esto se utilizara Datos > Validación…

CENIBIT

Página 8

Repaso Excel Básico. tomando la opción Lista, esta opción requiere que la lista este ubicada en la misma hoja que los elementos de la factura.

A continuación los datos y la

factura:

Base de Datos

Factura

Seleccione el rango de datos (A9:A14), es el rango de los datos a validar, mientras que el origen de la lista es el rango (A2:A7).

A

continuación

ingrese los mensajes de entrada y de error, observe el resultado y vea la función del botón desplegable resultante. Mensaje de Entrada Titulo Mensaje

Mensaje de Error

Ingrese Datos Calificación no válida Seleccione el código No teclee el código de la mercancía despliegue el botón y selecciónelo

Ahora que es posible seleccionar un código es necesario que una vez este sea seleccionado de inmediato la información descripción y precio aparezcan de

CENIBIT

Página 9

Repaso Excel Básico. forma automática, para esto se inserta la función BUSCARV, los parámetros de esta función son: Opción del Cuadro de Dialogo Valor Buscado Matriz Buscar en

Significado

Ejemplo

Refiere la celda con la información que sirve como índice de búsqueda

A9

Base de datos de los artículos

A2:C7

Respecto al índice de búsqueda cual es el Indicador de número de la columna que contiene la Columna información que se busca Valor lógico, Verdadero por defecto busca el valor más próximo por defecto, mientras Ordenado que Falso ubica el valor exacto que refiere el índice de búsqueda, no se requiere para la mayoría de los casos.

2

Falso

Observaciones (Celdas de Referencia Fija – función F4): Las

operaciones

involucran

más

con de

celdas una

que

fila

y

columna por vez y las que refieren en varias columnas a una como referencia, implican por definición el uso de referencias fijas, entonces, A9 es $A9 y A2:C7 es $A$2:$C$7, de otra forma al intentar arrastrar las ecuaciones estas producirán variaciones en los campos asociado.

Una vez arrastrada la ecuación y verificado los indicadores de columna, debe proceder a la función subtotal =Precio*Cantidad, pero al arrastrar esta produce este resultado.

CENIBIT

Página 10

Repaso Excel Básico.

Antes de los cambios

Luego de los cambios

Resulta que la ausencia de código devuelve un mensaje de No Aplica y este no puede ser sumando, esto se resuelve con la siguiente función lógica: =SI($A9="";"";BUSCARV($A9;$A$2:$C$7;2;FALSO)), esto significa que si la celda en referencia es un espacio en blanco lo muestre como un espacio en blanco, sino que busque el valor del código.

Si se utiliza la función producto

se resuelve de forma automática el problema, (pero arroja resultados sin ingresar cantidades, observe los gráficos).

Si

uds.,

utiliza

=C9*D9,

entonces deberá utilizar la función lógica =SI(C9="";0;C9*D9), así si la celda esta en blanco el resultado que se muestra es un cero (o un guión) y en caso de haber una cantidad se muestra el número. Ahora la celda Total es una simple sumatoria en el rango (E9:E14), pero el total a pagar es una función que involucra un descuento que es normal que el comerciante ofrezca y el cargo de impuesto (IVA), el desarrollo de estas funciones involucra de nuevo funciones lógicas.

Para esto debe definirse ante todo los criterios: 30% descuento si compara más de Bs. 30.000, 25% si es más de Bs. 20.000, 20% más de Bs. 10.000 y 0% para los demás. El IVA tiene un valor de 15%.

Entonces la función que explica el total a pagar es:

CENIBIT

Página 11

Repaso Excel Básico. =SI(E15>=30000;E15*0,7*1,15;SI(E15>=20000;E15*0,8*1,15;SI(E15>=100 00;E15*0,9*1,15;E15*1,15))) Se obtiene como resultado: Así incluyendo el IVA siempre el descuento le dará la impresión que le salio mas barato, si tiene dudas lea el libro el hombre que calculaba

de

descubrirá

Malba muchas

Tahan

y

cosas

interesantes.

CENIBIT

Página 12