Excel Básico Repaso
Click here to load reader
-
Upload
francisco-gonzalez -
Category
Documents
-
view
3.372 -
download
0
Transcript of Excel Básico Repaso
Calle Coromoto, Diagonal al C.C La Fuente Tlf: 0264-2411155-2417282
Repaso Excel Básico
Repaso Excel Básico.
CENIBIT Página 2
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).
Repaso Excel Básico.
CENIBIT Página 3
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:
Repaso Excel Básico.
CENIBIT Página 4
Base de comparación Relación Patrón de
Comparación
Valor de la Celda
Formula
Entre
No está entre
= (igual) <>(no igual a)
> (mayor) < (menor
>= (mayor igual) <= (menor igual)
Valor de Celdas que
definen el rango
Valor de la Celda
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:
Repaso Excel Básico.
CENIBIT Página 5
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
Prueba Lógica
Base de comparación Relación Patrón de Comparación
Valor de la Celda = (igual)
<>(no igual a) > (mayor)
< (menor >= (mayor igual)
<= (menor igual)
Valor de la Celda
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;…)
Repaso Excel Básico.
CENIBIT Página 6
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 Rango
Excelente 18-20
Distinguido 16-18
Bueno 14-16
Regular 10-16
Malo 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
E2>=18
Valor si verdader
o
“Excelente”
Valor si
falso:
SI(E2>=16;
“Distinguido”;SI(E2>=14
; “Bueno”;SI(E2>=10; “Regular”; “Malo”)))
Obteniendo los siguientes resultados:
Repaso Excel Básico.
CENIBIT Página 7
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 signo
Repaso Excel Básico.
CENIBIT Página 8
($) refiere que precede a un valor fijo (fija o columna) y se activa presionando
F4, tiene 4 formas básicas.
Significado Ejemplo
F4 Ambos fijos $A$1
F4 – F4 Fija la Fila A$1
F4 – F4 – F4 Fija la Columna $A1
F4 – F4 – F4 – F4 Ninguno fijo 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:
Mensaje de Entrada Mensaje de Error
Titulo Ingrese Datos Calificación no válida
Mensaje Notas del 0 al 20 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…
Repaso Excel Básico.
CENIBIT Página 9
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:
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 Mensaje de Error
Titulo Ingrese Datos Calificación no válida
Mensaje Seleccione el código
de la mercancía
No teclee el código
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
Base de Datos
Factura
Repaso Excel Básico.
CENIBIT Página 10
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
Significado Ejemplo
Valor Buscado
Refiere la celda con la información que sirve como índice de búsqueda
A9
Matriz Buscar en
Base de datos de los artículos A2:C7
Indicador de Columna
Respecto al índice de búsqueda cual es el
número de la columna que contiene la información que se busca
2
Ordenado
Valor lógico, Verdadero por defecto busca el valor más próximo por defecto, mientras
que Falso ubica el valor exacto que refiere el índice de búsqueda, no se requiere para
la mayoría de los casos.
Falso
Observaciones (Celdas de Referencia Fija – función F4):
Las operaciones con celdas que
involucran más de una 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.
Repaso Excel Básico.
CENIBIT Página 11
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:
Repaso Excel Básico.
CENIBIT Página 12
=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 Malba Tahan y
descubrirá muchas cosas
interesantes.