PDF de programación - Manual Excel Avanzado BIOS

MANUAL 

 

EXCEL 

 

AVANZADO 

 

BIOS 

 

Manual Excel Avanzado 

 

BIOS 

 

Módulo Funciones Avanzadas 

 
Primer Ejemplo – Funciones simples 
 
Dada la siguiente planilla: 
 
 

 

 
Supongamos que la nota final se calculará basándonos en el promedio de notas que obtuvo cada 
estudiante en sus parciales. 
Por lo tanto aplicaremos la función promedio: 
 
=PROMEDIO (B2:D2) 
 
Recordemos que cualquier función que vayamos a aplicar debemos posicionarnos sobre la celda, y 
comenzar con el signo = o en su defecto el signo +. 
 
Por otro lado si hubiéramos querido tomar en cuenta solo dos de los parciales dentro del 
promedio tendríamos que: 
 
=PROMEDIO (B2; D2) 
 
En el caso de que queramos tomar rangos discontinuos esto se aplicaría de la siguiente forma: 
 
=PROMEDIO (B2:D2; H2:J2) 
 
La planilla hasta ahora quedaría así: 
 

 

 

Además en el caso de copiar esta fórmula, podríamos realizarlo mediante la opción clásica de dar 
sobre el botón derecho sobre la celda, seleccionar la opción copiar, y luego marcar el rango (en 
este caso de E3:E8) y darle pegar. 
 
Mas allá de esta clásica opción también se podría copiar la formula de forma simple dando DOBLE 
CLIC en el borde inferior derecho de la celda que contiene la formula a copiar. 
 
En la siguiente columna, se hará un breve repaso de la formula condicional SI. 
De acuerdo a esta formula deberá aplicarse: 
 
=SI (prueba _ lógica; valor verdadero; valor falso) 
 
En el caso de la planilla que manejamos anteriormente, esta quedaría de esta manera: 
 
=SI (E2<5; “Reprobado”; “Aprobado”) 
 
Si tuviéramos más de una función SI para establecer, recordar que siempre vamos a tener que 
evaluar el conjunto de opciones de nuestro problema (conviene comenzar desde el nivel mas bajo) 
y haremos una función menos que opciones de nuestro caso. 
 
=SI (E2<5; “Reprobado”; SI (E2<=7; “Parcial”; “Aprobado”) 
 

 

 
No debemos perder de vista que la función SI puede ser utilizada en este tipo de casos, y 
concatenar hasta 7 funciones SI (tenemos este límite). 
 
De la misma manera que anteriormente lo hicimos, podemos copiar la fórmula sin problemas. No 
olvidemos que hasta ahora hemos dejado de lado el tema de Referencias Relativas y Absolutas ya 
que no hizo falta fijar ninguna de las celdas a copiar. 
 
 
 
 
 
 
 
 
 
 
 

Vamos a armar un cuadro adicional anexo a la planilla para implementar otras funciones: 
 

 

 
En este cuadro calcularemos cuantos fueron los alumnos que obtuvieron como calificación 
Aprobado, Parcial y Reprobado. A su vez calcularemos con respecto al total de alumnos, el 
porcentaje que obtuvo cada calificación. 
 
Lo haremos aplicando la función CONTAR.SI, de la siguiente manera: 
 
=CONTAR.SI (F2:F8; “Aprobado”) 
 
También podríamos haber escrito: 
 
=CONTAR.SI (F2:F8; A12) 
 
Veamos a su vez que si pensáramos en copiar esta fórmula debería quedar: 
 
=CONTAR.SI($F$2:$F$8;A12) 
 
La razón del signo $ es el hecho de fijar el rango F2:F8 ya que las calificaciones son fijas, mientras 
que la celda A12 es relativa ya que cambiará de fila al copiar. 
 
En la función CONTAR.SI tenemos en cuenta el rango a evaluar, y como segundo argumento 
tenemos el criterio a tomar en cuenta para copiar. 
 
Si aplicamos la funcion CONTAR, o también CONTARA, podremos saber cuantos alumnos 
tendremos. 
 
=CONTAR (E2:E8) 
 
En este caso la función CONTAR aplica perfectamente para este caso puesto que las celdas que se 
evaluan tienen contenido numérico. 
 
En el caso de querer hacer un recuento por apellidos: 
 
=CONTARA(A2:A8) 
 
Aquí aplica perfectamente ya que esta función nos permite evaluar celdas no vacías. 

 

 
 
Pasando a otro tema veremos a continuación alguna aplicación adicional de la función SI. 
 
En la siguiente planilla se evaluará la calidad de los productos según su diámetro. Si el diámetro 
está comprendido entre 10 y 20 su calidad será A, y de lo contrario será B. 
 

 

 
Vemos que en este caso no solo se aplicó la función SI, sino que se combinó con la opción Y 
(también podriamos haber aplicado la opción O), de la siguiente manera: 
 
=SI(Y(opcion 1; opcion 2;…..); valor verdadero; valor falso) 
 
=SI(O(opcion 1; opcion 2;…..); valor verdadero; valor falso) 
 
En el caso de combinar con la opción Y, recordemos que estaríamos hablando de que si se 
cumplen UNA Y CADA UNA DE LAS OPCIONES EN FORMA SIMULTANEA se aplicaría la opción 
verdadera, y en el caso que no fuera así se aplica la opción falsa. 
 
Mientras que en el caso de las opciones que damos mediante el O, con que una de las opciones 
fuere verdadera, ya se aplicaría la opción del valor verdadero en la condición, y solo si no se 
cumple ninguna de las opciones se aplicaría el valor falso. 
 
 
 
 
 
 
 
 

Pasemos ahora a un nuevo ejemplo y continuamos en la aplicación de funciones: 
 

 

 
En este caso aplicamos una función de búsqueda, que se denomina BUSCARV. 
Recordemos que la forma de utilizar esta función de búsqueda es la que se demuestra a 
continuación: 
 
=BUSCARV(valor buscado; matriz; número de columna; [ordenamiento]) 
 
En el caso particular de la planilla 
 
=BUSCARV(A13; A2:C8; 3; FALSO) 
 
Vemos aquí que el valor buscado hace referencia a la celda donde el usuario cargará el dato en el 
que se basará la búsqueda. Este dato no debe ser parte de la matriz de búsqueda (A2:C8) si no, 
esto no tendría sentido en la búsqueda. Luego se carga la matriz, al hacer referencia a la matriz no 
cargamos los títulos de la misma, solo el contenido (registros de la misma), luego cargamos la 
referencia al número de columna que se va a extraer, en este caso la columna 3 ya que es la del 
stock (columna con dato a extraer) y por último como argumento OPCIONAL el orden dentro de la 
matriz, en este caso falso ya que la primera columna de la matriz no está ordenada en forma 
ascendente. 
 
Además en el caso del argumento opcional de ORDEN esto tambien es realmente significativo al 
momento de aplicar la coincidencia en la búsqueda. Si este argumento es FALSO y la busqueda no 
encuentra el dato preciso retorna un error. Por ejemplo: 
 

 

 

En este caso al no encontrar el Artículo A5565 devuelve un error del tipo #N/A, o dato NO 
DISPONIBLE (Not Available). 
 
En cambio veamos que pasa cuando el argumento de orden lo cambiamos a VERDADERO. 
 

 

 
En este caso, mas allá que el producto A5565 no se encontró de todas formas retornó el valor de 
stock del artículo cuya coincidencia no era exacta, sino aproximada. 
En muchos casos cuando la búsqueda no tiene porque ser exacta, este argumento VERDADERO 
puede ayudarnos a encontrar el dato más cercano que coincida con nuestra búsqueda. 
 
En cambio anteriormente cuando el argumento fue cambiado por FALSO, la búsqueda no nos 
retornaba ningún valor ya que el dato no lo encontraba (con coincidencia exacta) en la matriz de 
registros. 
 
En otro ambito también sería bueno en una planilla controlar los ingresos de información, y es por 
esto que vamos a introducir el tema VALIDACIÓN DE REGISTROS. 
 
VALIDACIÓN DE DATOS 
 
En el caso de la planilla que presentamos anteriormente, vamos a comenzar este tema de 
validación tratando de que el usuario se limite a agregar artículos con STOCK entre 0 y 300, y no 
pueda introducir un número menor a cero, ni mayor a 300. 
 
Veamos como operar: 
 

1. Pintamos las celdas de stock (celdas en las que vamos a dar la regla de validación) 
2. Luego vamos al menú de datos – validación 
3. Establecemos la regla de validación a operar 
4. Opcionalmente definimos mensaje entrante 
5. Opcionalmente definimos mensaje de error 

 
 
 
 
 
 
 

Veamos el paso en cada pantalla: 
En este primer paso marcamos las celdas de Stock y luego nos dirigimos al menú de Datos – 
Validación 
 

 
En el segundo paso definimos la regla de validación: 
En este caso, definimos que lo que vamos a permitir es un número entero en dichas celdas y este 
número deberá estar comprendido entre 0 y 300. 
 

 

 
Tenemos otras opciones al desplegar el cuadro Permitir, que las usaremos en algunos ejemplos 
posteriores. 
 

 

 

Poniendo otro ejemplo: 
 
En este caso nos posicionamos en la celda donde la persona va a realizar la búsqueda del stock de 
determinado artículo (A13), y damos una regla de validación para que solo pueda ingresar códigos 
de artículos existentes en la matriz de datos: 
 

 
Veamos que sucede cuando aplicamos también el uso de la solapa de Mensaje Entrante 
 

 

 

 

 
Esto resultará cuando la persona se posicione sobre la celda A13 (en la cual se determinó la regla 
de validación). 
 
Veamos como en la pantalla que se muestra a continuación, se muestra el mensaje entrante: 
 
 
 
 
 
 
 
 
 

Aquí vemos el mensaje entrante al posicionarnos sobre la celda  

 
Un comentario con respecto a esto, es el hecho de poder trabajar en las reglas de validación, tanto 
con rangos dinámicos como también con rangos con un nombre determinado. 
 
Veamos como funciona: 
 

 

 
En este caso, al dar como origen de la lista la selección: =$A:$A  (referencia a la columna A) 
 
La ventaja que se logra es el hecho de poder incorporar mas artículos con su respectivo stock a la 
matriz  de esa manera integrarlos a la regla de validación de forma automática. 
 
Aplicando lo que comentabamos: 
 

 

 
En este caso agregamos un artículo a la lista, y luego vemos como el artículo se integra en forma 
automática al cuadro combinado en la celda que tiene la validación. 
 
 
 
 
 
 
 

DEFINIR NOMBRES A RANGOS 
 
En el siguiente ejemplo veremos como aplicamos en lugar de una referencia a una lista formada 
por determinadas celdas, a un rango con un nombre preasignado 
 
En primer término asignaremos al rango, un nombre como por ejemplo ARTICULOS 
 
 
 
 
 
 
 
 
 
 
 
En el área señalada asignamos el nombre al rango de artículos. O sea primero, señalamos el rango 
de artículos (A2:A9) y luego le designamos un nombre a este rango. De ahora en mas cuando yo 
quiera hacer referencia a las celdas A2:A9 ya podremos escribir simplemente