Calcular calificaciones (SI anidados, SI con Y, SI.CONJUNTO, BUSCARV)
Pensemos por un momento que somos profesores de un centro escolar y tenemos que poner calificaciones de acuerdo con la siguiente tabla (imagen 1):
 |
| Imagen 1 |
En la celda A2 se encuentra el dato de la puntuación.
Intento 1
Podemos empezar con una fórmula como:
=SI(A2<=59;"F";SI(Y(A2>=60;A2<=62);"D-";SI(Y(A2>=63;A2<=66);"D";SI(Y(A2>=67;A2<=69);"D+";SI(Y(A2>=70;A2<=72);"C-";SI(Y(A2>=73;A2<=76);"C";SI(Y(A2>=77;A2<=79);"C+";SI(Y(A2>=80;A2<=82);"B-";SI(Y(A2>=83;A2<=86);"B";SI(Y(A2>=87;A2<=89);"B+";SI(Y(A2>=90;A2<=92);"A";SI(A2>=93;"A+"))))))))))))
Utilizando las siguientes funciones (siempre podemos recurrir a la Ayuda de Calc):
SI(comparación; caso-Verdadero; caso-Falso): Realiza una comparación lógica y si el resultado es VERDADERO se ejecutará o devolverá caso-Verdadero y si es FALSO se ejecutará o devolverá caso-Falso.
Y(comparación-1 [; comparación-2 [; ... [; comparación-255]]): Devuelve VERDADERO si todos los argumentos son VERDADEROS. Si uno de los elementos es FALSO, esta función devuelve el valor FALSO.
En la fórmula de arriba cada caso-Falso de la función SI es, a su vez, una función SI; lo que se denomina SI anidados. Tenemos doce funciones SI en una misma fórmula, una por cada rango de puntuación, más diez funciones Y. Debemos estar de acuerdo en que es una fórmula laboriosa y podemos equivocarnos fácilmente.
Intento 2
Pero como nos gusta mantener las neuronas ocupadas y sabemos que la función SI comienza a ejecutarse secuencialmente por la izquierda, llegamos a una nueva fórmula:
=SI(A2<60;"F";SI(A2<63;"D-";SI(A2<67;"D";SI(A2<70;"D+";SI(A2<73;"C-";SI(A2<77;"C";SI(A2<80;"C+";SI(A2<83;"B-";SI(A2<87;"B";SI(A2<90;"B+";SI(A2<93;"A";SI(A2<101;"A+"))))))))))))
Ya no es necesaria la función Y porque las comparaciones se realizan con el primera puntuación de cada rango. Va mejorando la situación pero seguimos teniendo doce SI anidados.
Intento 3
Continuamos pensando cómo eliminar los SI anidados y llegamos a:
=SI.CONJUNTO(A2<60;"F";A2<63;"D-";A2<67;"D";A2<70;"D+";A2<73;"C-";A2<77;"C";A2<80;"C+";A2<83;"B-";A2<87;"B";A2<90;"B+";A2<93;"A";A2<101;"A+")
SI.CONJUNTO(expresión1; resultado1 [; expresión2; resultado2 ][; ... ; [expresión127; resultado127 ]])
expresión1, expresión2, ... son valores booleanos o expresiones que pueden ser VERDADERO o FALSO.
resultado1, resultado2, ... son los valores que se devuelven si expresión1, expresión2, ... es VERDADERO.
SI.CONJUNTO(expresión1, resultado1, expresión2, resultado2, expresión3, resultado3) se ejecuta como:
SI expresión1 es VERDADERO ENTONCES resultado1
DE LO CONTRARIO SI expresión2 es VERDADERO ENTONCES resultado2
DE LO CONTRARIO SI expresión3 es VERDADERO ENTONCES reultado3
Para obtener un resultado predeterminado si ninguna expresión es VERDADERA añadiremos una última expresión que siempre sea VERDADERA, como VERDADERO o 1=1, seguida del resultado predeterminado.
Intento 4 (y último)
Y llegamos a la que yo utilizaría. Como tenemos los datos en una tabla o matriz podemos usar la función BUSCARV colocando en la primera columna de la tabla los valores a partir de los cuales se devolverá una calificación u otra como vemos en la imagen 2:
=BUSCARV(A2;$J$2:$L$13;3)
 |
| Imagen 2 |
BUSCARV(Búsqueda; Matriz; Índice [; Búsqueda-por-intervalos-ordenados])
Búsqueda es el valor de cualquier tipo buscado en la primera columna de la matriz.
Matriz es la referencia, la cual debe contener como mínimo la cantidad de columnas correspondiente al número pasado en el argumento Índice.
Índice es el número de la columna en la matriz que contiene el valor que se va a devolver. La primera columna tiene el número 1.
Búsqueda-por-intervalos-ordenados es un parámetro opcional que indica si la primera columna de la matriz contiene límites de intervalo en lugar de valores simples. En este modo, la búsqueda devuelve el valor de la fila cuya primera columna tiene un valor igual o menor que Búsqueda. Por ejemplo, podría contener fechas en las que se ha modificado algún valor fiscal, por lo que los valores representan fechas de inicio de un período en el que un valor fiscal específico estuvo vigente. Así, la búsqueda de una fecha ausente en la primera columna de la matriz, pero que cae entre algunas fechas límite existentes, daría la más baja de ellas, permitiendo encontrar los datos vigentes a la fecha buscada. Introduzca el valor booleano FALSO o cero si la primera columna no es una lista de límites del intervalo. Cuando este parámetro es VERDADERO o no se indica, la primera columna de la matriz debe estar ordenada de forma ascendente. Las columnas ordenadas se pueden buscar mucho más rápido y la función siempre devuelve un valor, aunque el valor de búsqueda no haya coincidido exactamente, si es mayor que el valor más bajo de la lista ordenada. En las listas no ordenadas, el valor de búsqueda debe coincidir exactamente. En caso contrario, la función devolverá #N/A con el mensaje Error: Valor no disponible.
Podéis ver otros ejemplos de la función BUSCARV en estos artículos de la revista EnRed@2.0 del Instituto Andaluz de Administración Pública:
Y, como podéis observar en el archivo de prácticas, los rangos B2:E101 y L2:L13 tienen un formato condicional de la forma (imagen 3):
 |
| Imagen 3 |
En la mima revista citada podéis leer sobre el formato condicional en estos artículos:
Desde aquí os animo a que hagáis pruebas con otros datos que uséis en vuestro trabajo diario.
Hay que guardar frecuentemente los archivos de trabajo y siempre realizar copias de seguridad, independientemente de la aplicación con la que estéis trabajando.
Muchas gracias por leer este artículo y saludos cordiales.
LibreOffice Community ver.7.6.7.2 (X86_64 es-ES) Windows 11
Se puede descargar el archivo de prácticas aquí.
Comentarios
Publicar un comentario