Cálculo de horas trabajadas en turno noche

Un tema recurrente en las consultas que recibo es cómo calcular horas trabajadas en turno noche. Supongamos la siguiente situación:

• un operario trabaja desde las 20:00 hasta las 04:00
• el turno noche, cuya tarifa es distinta del horario normal, corre de las 22:00 a las 06:00

¿Cómo calculamos con Excel cuántas horas de las 8 horas trabajadas pertenecen al turno noche? Veamos esta tabla con varios ejemplos

La fórmula en la celda E7, que calcula correctamente las horas trabajadas en turno noche tal como ésta definido en las celdas D3 y E3, es:

=RESIDUO(C7-B7,1)+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3)-(C7<B7)*($D$3-$E$3)

Esta fórmula se basa en la función MEDIANA y en la función RESIDUO
.
Para explicar esta fórmula vamos a dividirla en tres módulos:

RESIDUO(C7-B7,1)  - esta fórmula, que ya hemos explicado, calcula el total de horas trabajadas.

+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3) - Esta expresión nos permite calcular el total de horas trabajadas fuera del turno noche. Este resultado será siempre negativo o cero, de manera que al restarlo de las horas trabajadas (el primer "módulo" de nuestra fórmula), obtenemos las horas que caen dentro del horario nocturno.

(C7<B7)*($D$3-$E$3) - el objetivo de esta expresión es corregir el resultado cuando el horario trabajado pase de un día al otro. Si la hora de salida es mayor que la hora de entrada, todo el trabajo ha sido realizado dentro del mism día; en este caso la expresión dá cero (FALSO) y no varía el resultado. Si la hora de salida es menor que la de entrada, la jornada terminó al día siguiente; la expresión da VERDADERO (que equivale a multiplicar por 1), y el resultado es corregido de acuerdo.

Este cuadro interactivo permite ver como funciona cada módulo de la fórmula (los valores de entrada y salida y los del horario nocturno pueden ser modificados)





En este post pueden ver una solución para el caso de tres turnos (día, tarde y noche).

La función MEDIANA de Excel o como calcular si un valor se encuentra entre dos valores

Un lector me consultaba cómo calcular horas trabajadas en turno noche. El método tradicional para calcular horas de un turno, por ejemplo cuando el turno noche empieza a las 22:00 y concluye a las 06:00 y el trabajador trabajó de las 20:00 a las 04:00, es usando la función MEDIANA.

En este post mostraré cómo usar la función MEDIANA para calcular si un valor cae entre dos valores. En el próximo post mostraré cómo calcular las horas trabajadas en un turno.

Supongamos que queremos calcular con Excel si el valor en la celda B4 está comprendido entre los valores de las celdas B1 y B2. Podemos hacer usando la función Y

El problema con esta fórmula es que presupone que el valor de B2 es mayor que el de B1; en caso contrario el resultado es erróneo

El uso de la función MEDIANA evita este problema

La función MEDIANA calcula, obviamente, la mediana de un conjunto de números dado. La mediana, según la  ayuda de Excel es:  el número que se encuentra en medio de un conjunto de números.

Nuestra fórmula funciona también con valores no enteros, negativos y positivos. Un punto a tomar en cuenta es cómo definimos "entre". En nuestro ejemplo, ¿10 cae entre los valores del intervalo? Si usamos MEDIANA, la respuesta es si; si usamos la función Y

=Y(B4>=B1,B4<=B2), dará VERDADERO

=Y(B4>B1,B4<B2) dará FALSO

Resolver sistemas de ecuaciones lineales con Excel

Estábamos tomando un café a la mañana cuando Eduardo (si, el compañero de trabajo que siempre me viene a consultar sobre temas de Excel) me pregunta:

- Che, ¿te acordás algo del tema de sistemas de ecuaciones lineales?
- No mucho, ¿por qué?
- Porque mi hijo está estudiando el tema en el colegio y cada tanto me pide ayuda; y ¿qué querés que te diga?, no me acuerdo nada.
- Bueno, fijate en la Internet, seguro que hay cantidades industriales de sitios que tratan el tema como Wikipedia.
- Si, ya me fijé. Pero no tengo paciencia para ponerme a aprender todo de nuevo. Todo lo que necesito es ayudarlo a resolver las ecuaciones.
- Ah! bueno...Podés usar Excel para resolver sistemas de ecuaciones lineales.
- ¿Usar Excel para resolver sistemas de ecuaciones lineales? ¿Cómo?
- Te explico

Hay tres métodos para resolver sistemas de ecuaciones lineales:

• algebraicos
• gráficos
• matriciales

Con Excel podemos resolver sistemas de ecuaciones lineales usando el método matricial. Para ésto Excel nos ofrece dos funciones MMULT que nos permite multiplicar matrices y MINVERSA que calcula la inversa de una matriz.

Para resover de sistemas de ecuaciones lineales con el método matricial se pueden emplean dos procedimientos: el de la matriz inversa y el método de eliminación gaussiana. En esta nota vamos a mostrar como hacerlo con el método de la matriz inversa (sin detenernos en la consideraciones teóricas que dejamos libradas a la iniciativa del lector).

Supongamos que tenemos este sistema de ecuaciones lineales

5x+1y+8z=46

4x-2y=12

6x+7y+4z=50

Queremos encontrar los valores de las incógnitas "x", "y" y "z". El primer paso es crear en Excel una matriz con los valores de los coeficientes de las ecuaciones (el rango B2:D4) y una con los términos independientes (el rango F2:F4)

Para encontrar la solución del sistema, si existe, calculamos primero la inversa de la matriz B2:D4 y luego multiplicamos ésta por la matriz F2:F4.
Para obtener la matriz inversa usamos la función MINVERSA. Esta función, como otras funciones matriciales, dan resultados en un rango de celdas por lo que debemos primero seleccionar el rango donde obtendremos los resultados y luego introducir la función apretando simultáneamente las teclas Ctrl-Mayúsculas-Enter

El siguiente paso es multiplicar la matriz inversa (el rango B7:D9 en nuestro ejemplo) por la matriz F2:F4. Para realizar la multiplicacion de matrices usamos la función MMULT. Recordemos que para multiplicar dos matrices debe cumplirse la condición que el número de columnas de la primer matriz debe ser  igual al número de filas de la segunda. La dimensión de la matriz resultante es el número de filas de la primera (3) por el número de columnas de la segunda (1). Es decir, antes de introducir la función MMULT debemos seleccionar un rango de tres filas por una columna e introducir la función con Ctrl-Mayúsculas-Enter

Los valores de la matriz F7:F9 son las soluciones del sistema de ecuaciones,  x = 4; y = 2; z = 3.

Podemos solucionar el sistema en un único paso anidando la función MINVERSA dentro de la fórmula con MMULT

Funciones Excel raramente usadas

En un comentario sobre la función N() un lector me preguntaba sobre las funciones más usadas y las menos usadas. No creo que existan estadísticas al respecto, pero antes de argumentar sobre las funciones menos usadas en Excel, tema de esta nota, debemos descartar de la lista las funciones "profesionalmente específicas".
Con esto me refiero a funciones que realizan cálculos relevantes a un área específica de la actividad profesional. Por ejemplo, sólo profesionales en el área financiera utilizarán la función LETRA.DE.TEST.EQV.A.BONO()  ; o matemáticos interesados en soluciones canónicas y(x) de la ecuación diferencial de Bessel considerarán usar la función BESSELY().

Considerando, entonces, las funciones de "uso general", parece más que razonable que SUMA(), SI() y BUSCARV() se encuentren entre las más usadas.

¿Cuáles son las menos usadas? Algunas de las que integran mi colección de funciones Excel infrecuentes ya han sido mencionadas en este blog, como la función N() (ver el enlace al principio de esta nota). Otras funciones de uso poco frecuente pueden ser:

ELEGIR(): función poco apreciada que puede sustituir con ventaja a la función SI() en fórmulas con SI() anidada e inclusive permitir hacer búsquedas con BUSCARV() de derecha a izquierda.

REPETIR(): tras la inocua descripción "repite un texto un número determinada de veces" se esconde una herramienta que nos permite crear gráficos gráficos en celdas (minigráficos).

TIPO(): útil para crear fórmulas condicionales dependiente del tipo de dato de la celda evaluada.

SIGNO(): permite establecer si un número es positivo, negativo o cero.

RESIDUO(): calcula el residuo de una división; muy útil en cálculos de tiempos, para realizar cálculos tomando en cuenta cada n-ésimo valor de una serie o cálculos condicionales, entre otros usos.

SIFECHA(): la más indocumentada de las funciones Excel.


¿Qué funciones agregarían ustedes a la lista?

La función N de Excel

Pregunta: ¿cuándo fue la última vez que utilizaron la función N() de Excel? Supongo que las respuestas se podrán reducir a dos:

1. Nunca
2. ¿qué función?

No es casual. La ayuda en línea de Excel nos informa que por lo general no es necesario usar esta función y que existe solamente por cuestiones de compatibilidad con otros programas.

Sin embargo podemos señalar algunos usos más o menos prácticos de esta función. Veamos qué nos dice Excel al respecto

La función N() utiliza un único argumento (Valor) y los resultados posible son los siguientes:

• Si el valor es un número, el resultado será ese número.
• Si el valor es una fecha, la función N la convierte en su número de serie.
• Si el valor es VERDADERO, la función N dará 1.
• Si el valor es FALSO, la función N dará 0.
• Si el valor es un error, se devuelve el valor de error.
• Para todos los demás valores, la función N dará 0.

Uno de los posibles usos de esta función es agregar comentarios a nuestras fórmulas sin utilizar los comentarios de celda. Esto es posible ya que todo valor textual será convertido a 0, es decir que si lo sumamos a nuestra fórmula el resultado no se verá afectado. Por ejemplo

Como puede apreciarse, sumar la función N() en nuestra fórmula no afecta el resultado pero si nos permite ver el comentario en la barra de las fórmulas.

Usos poco comunes de la función ELEGIR

Si las funciones tuvieran sentimientos ELEGIR seguramente se sentiría subestimada; y con bastante razón.
La descripción de la función en la ayuda en línea de Excel reza literalmente:

Utiliza el argumento núm_índice para devolver un valor de una lista de argumentos de valores.

La cuestión se vuelve más interesante cuando seguimos leyendo y llegamos a las observaciones:

• Si núm_índice es una matriz, cada valor se evaluará cuando se evalúe ELEGIR. 

• Los argumentos de valor para ELEGIR pueden ser referencias de rango, así como valores individuales. 

Interesante! ELEGIR puede usar matrices como número de índice y las referencias pueden ser valores individuales y rangos.

Estas carcterísticas nos abren muchas más posibilidades de lo que podemos deducir de la escueta descripción en la ayuda.

Veamos algunos ejemplos:

Operaciones dinámicas con rangos.

Consideremos esta tabla de datos

Hemos definido los nombres "Plan", "Ventas" y "Diferencia" que se refieren a los respectivos rangos en la tabla. La fórmula

=SUMA(ELEGIR(1,Plan,Ventas,Diferencia))

da el total del plan de ventas de todos los productos. De la misma manera

=SUMA(ELEGIR(2,Plan,Ventas,Diferencia))

Para que la fórmula sea más dinámica podemos combinarla con la función COINCIDIR y usar vallidación de datos para crear una lista desplegable con las distintas opciones

=SUMA(ELEGIR(COINCIDIR(G2,B2:D2,0),Plan,Ventas,Diferencia))

Si bien podemos crear el mismo modelo usando INDICE, la ventaja de ELEGIR es que podemos usar rangos que este en distintas hojas.

BUSCARV de derecha a izquierda.

Uno de los usos de ELEGIR con matrices es hacer que BUSCARV (VLOOKUP) pueda trabajar de derecha a izquierda. Por ejemplo, en la tabla anterior si queremos encontrar a qué producto pertenece el importe 13,048 usamos

=BUSCARV(13048,ELEGIR({2,1},A3:A7,B3:B7),2,0)

Para entender como funciona esta fórmula podemos usar el evaluador de fórmulas (Fórmulas-Auditoría-Evaluar Fórmulas)

Podemos ver que la expresión ELEGIR({2,1},A3:A7,B3:B7) crea una matriz de dos columnas donde la primera es el importe de las ventas y la segunda los nombres de los productos.

En mi opinión el uso más importante de ELEGIR es reemplazar el sobrestimado uso de SI anidado. Como ya hemos señalado en una nota de este blog anidar funciones SI es una de las mejores maneras de cometer errores en una hoja de cálculo. Después de tres o cuatro niveles, la fórmula se vuelve practicamente incontrolable (y me disculparán todos esos cursos que insisten en enseñar a construir fórmulas SI anidadas como si fuera una gran proeza).

Para ejemplificarlo supongamos que queremos determinar el responsable de producción según el día de la semana. La fórmula  para seis días de la semana, donde en la celda B3 tenemos la fecha a considerarcon sería

con Si anidado

=SI(DIA(B3)=1,"Roberto",SI(DIA(B3)=2,"Juan",SI(DIA(B3)=3,"Carlos",SI(DIA(B3)=4,"Esteban",SI(DIA(B3)=5,"Raul","Jose")))))

Con ELEGIR

=ELEGIR(DIA(B3),"Roberto","Juan","Carlos","Esteban","Raul","Jose")

Como diría Gracián, "lo bueno si breve, dos veces bueno".

Que tengan un buen fin de semana

La función MONEDA de Excel

¿Alguien usó alguna vez la función MONEDA de Excel? La función MONEDA y sus dos "primas hermanas" MONEDA.FRAC y MONEDA.DEC pertenecen esa colección de funciones que ni siquiera sabíamos que existen.

Según la ayuda en línea de Excel el objetivo de la función MONEDA es convertir un número en texto usando formato de moneda

Introducimos un número (o referencia a una celda que contiene un número), definimos la cantidad de decimales y el resultado es el número precedido por el símbolo de la moneda (de acuerdo a las definiciones del sistema). Lejos de ser la función más sofisticada de Excel.

Las mencionadas "primas" tienen usos que pueden ser útiles en ciertas circunstancias. EL objetivo original de la función MONEDA.FRAC, siempre de acuerdo a la ayuda en línea de Excel, es:

Convierte una cotización de un valor bursátil, expresada en forma decimal, en fraccionaria. Use MONEDA.FRAC para convertir números decimales de moneda, como precios de valores bursátiles, en fracción.

En lugar de intentar explicar el significado de la definición, voy a mostrar un uso posible.

Por lo general los sistema de control de horarios usan la notación decimal. Por ejemplo, 2.5 horas representa 2 horas y 30 minutos; 2.75 representa 2 horas y 45 minutos. La forma decimal puede conducir a confusiones y ya hemos visto cómo convertir esta forma en formato horario:

1. dividimos 2.75 por 24
2. cambiamos el formato de la celda a [hh]:mm
3. el resultado será 02:45

En lugar del formato horario podemos usar MONEDA.FRAC de la siguiente manera

=MONEDA.FRAC(A2,60)

La parte decimal del número muestra ahora los minutos (2 horas, 45 minutos).

Funciones que dan #VALOR en referencias a cuadernos cerrados

Cada tanto recibo consultas en relación a fórmulas que dan el resultado #VALOR cuando contienen referencias a cuadernos cerrados.
Lo que intriga a estos usuarios de Excel es que este comportamiento no se da con todas las funciones. Ciertas funciones, como BUSCARV funcionan correctamente también si el cuaderno remoto está cerrado; otras, como CONTAR.SI dan #VALOR si el cuaderno remoto está cerrado, error que se corrige al abrir el cuaderno.

Veamos este ejemplo: tenemos dos cuadernos con las ventas anuales de dos sucursales resumidas por mes, Sucursal 1.xlsx y Sucursal 2.xlsx. En un tercer cuaderno queremos crear un reporte que muestre el total de ventas de cada sucursal  y el número de meses en que las ventas fueron superiores a los 200,000

Como puede apreciarse, la fórmula en la celda C3 contiene una referencia explícita al cuaderno de donde extraemos los datos

=CONTAR.SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13,">200000")

Al cerrar los cuadernos Sucursal 1.xlsx y Sucursal 2.xlsx la referencia incluye la dirección completa del archivo remoto.

Ahora cerramos el cuaderno con el reporte. Al volver a abrirlo veremos:

Como vemos, la función SUMA sigue mostrando los resultados, pero la función CONTAR.SI no puede resolver la referencia y da el resultado de error #¡VALOR!

Hay varios remedios para este problema. En el caso específico de la función CONTAR.SI podemos usar en su lugar una combinación de SUMAR y SI de esta manera:

=SUMA(SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000,1,0))

Esta fórmula es matricial y debe ser introducida en la celda apretando simultáneamente Ctrl-Mayúsculas-Enter

También podemos usar la función SUMAPRODUCTO, que no debe ser introducida matricialmente, de esta manera

=SUMAPRODUCTO(--('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000))

Nótese el doble símbolo "--"inmediatamente después del primer paréntesis. Su función es forzar a los valores VERDADERO y FALSO creados por la función SUMAPRODUCTO a tomar el valor 1 y 0 respectivamente.

Otras funciones que dan #¡VALOR! cuando se refieren a cuadernos cerrados son SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, CONTAR.BLANCO, PROMEDIO.SI, INDIRECTO (tema que trate en esta nota del año 2006), DESREF y varias de las funciones base de datos como BDCONTARA y BDPROMEDIO).

Operaciones con constantes matriciales

En alguna de mis prehistóricas notas he tocado el tema de las constantes matriciales. Las constantes matriciales nos permiten simplificar nuestras fórmulas, como en el ejemplo que describo a continuación.

Supongamos que queremos calcular el promedio de los tres menores valores de esta lista, (señalados con fuente roja)

Una posibilidad es hacerlo usando una columna auxiliar con la función JERARQUIA para obtener el número de orden y luego usar PROMEDIO.SI

Pero si por alguna razón queremos evitar le uso de columnas auxiliares (por ejemplo, para impresionar al jefe), podemos combinar PROMEDIO.SI con K.ESIMO.MENOR y una constante matricial:

=PROMEDIO(K.ESIMO.MENOR(B3:B12,{1,2,3}))

Como puede apreciarse la fórmula es compacta y a pesar de que estamos usando tres criterios a la vez, no es matricial (la introducimos como toda fórmula corriente).
Si queremos, por ejemplo, calcular la suma de los 5 mayores números en la lista (señalados con fuente verde) usamos

=SUMA(K.ESIMO.MAYOR(B3:B12,{1,2,3,4,5}))

Podemos crear una constante matricial usando la función FILA, de esta manera

=SUMA(K.ESIMO.MAYOR(B3:B12,FILA(1:5)))

pero en este caso debemos usar la fórmula en forma matricial, es decir, introducirla apretando simultáneamente Ctrl.-Mayúsculas-Enter.

Otra posibilidad interesante es el uso de Tablas o nombres definidos para crear una referencia dinámica a los criterios.

Por ejemplo, creamos una tabla de criterios como ésta:

Ahora podemos usar la tabla (tblCriterios) como argumento en nuestra fórmula:

=PROMEDIO(K.ESIMO.MENOR(B3:B12,tblCriterios[#Datos]))

La ventaja de esta técnica es que podemos cambiar dinámicamente los criterios en la fórmulas sin necesidad de editarla.

Por qué y cómo usar la nueva función AGREGAR

Estaba leyendo una discusión en uno de los foros de Excel sobre el uso de la combinación de funciones =SI(ESERROR(fórmula1),fórmula2,fórmula1) que opuesto a la nueva función (desde Excel 2007) SI.ERROR(fórmula1,fórmula2).

La primera forma es muy, pero muy, ineficiente ya que primero tiene que calcular fórmula1, luego evaluar si da error y si no da error volver a calcularla.
SI.ERROR resuelve este problema, pero sólo para quien haya avanzado a Excel 2007 posteriores.

Personalmente trato de evitar el uso de SI.ERROR ya que en ciertas ocasiones puede ocultar errores que si quisiéramos detectar (por ejemplo: #REF!).

Una situación similar es cuando queremos hacer operaciones con un rango de valores que contiene errores. En el pasado he mostrado algunas técnicas para enfrentarse con estas situaciones, pero a partir de Excel 2010 tenemos una nueva herramienta: la función AGREGAR.

Esta nueva función es una especie de SUBTOTALES potenciada. AGREGAR viene provista con 19 funciones (como puede verse en la ayuda en línea de Excel)

siete opciones (qué omitir y qué no)

Esta función viene en dos formas: forma de referencia y forma matricial. Un detalle a tener en cuenta es que esta función no está diseñada para agregar rangos horizontales.

Truco para consolidar datos de varias hojas Excel

Excel permite la creación de rangos tridimensionales, tal como mostré en el pasado (apretar el enlace para ver la nota). Un rango tridimensional es aquel que se extiende a más de una hoja. En este ejemplo tenemos un cuaderno con cinco hojas (ya he señalado que separa datos de esta manera es una mala práctica pero a los efectos del ejemplo ignoraré mis propias recomendaciones).

Las hojas "Ventas…" contienen los totales de ventas de cada sucursal para cada año; en la hoja "Totales" queremos consolidar el valor de la celda B6 de todas las hoja de ventas

En lugar de la técnica que mostré en la nota mencionada más arriba, podemos usar este truco:

En la celda C3 de la hoja Totales introducimos =SUMA('*'!B6)

Al cerrar el paréntesis y apretar Enter, Excel lo transforma en

=SUMA('Ventas 2009:Ventas 2012'!B6)

Como puede apreciarse, todas las hojas del cuaderno, excepto la activa, son incluidas en el rango tridimensional.

Ahora supongamos que el cuaderno contiene también hojas para los costos de ventas, con la misma estructura (los totales en la celda B6). Para que estas hojas no aparezcan en la fórmula podemos condicionar el comodín con un criterio, por ejemplo

=SUMA('Ventas*'!B6

Esta técnica no está limitada a una única celda. Podemos usar, por ejemplo,

=SUMA('Ventas*'!B2:B5

Tampoco está limitada a la función SUMA (la lista de funciones puede verse en la nota ya mencionada).

Matrices de valores únicos en funciones de Excel

El origen de esta nota es esta consulta:

"tengo una tabla de alumnos con las calificaciones de nueve asignaturas. Quiero extraer las tres mejores notas en orden decreciente, sin repetición".

Para ejemplificar veamos parte de esta tabla:

La función que nos permite extraer el valor de la lista de acuerdo a su jerarquía en la lista es K.ESIMO.MAYOR (orden decreciente) y K.ESIMO.MENOR (orden ascendente). La sintaxis de estas funciones es sencilla:

=K.ESIMO.MAYOR(matriz de valores, número de orden)

Así, para el alumno 1 (fila 2) la fórmula

=K.ESIMO.MAYOR($B2:$J2,1)

da como resultado 95.

El problema con esta función es que no ignora los valores repetidos. Si usamos ahora

=K.ESIMO.MAYOR($B2:$J2,2)

para obtener la segunda mejor notas en orden decreciente, el resultado será nuevamente 95, cuando el resultado que buscamos es 86.

Para obtener los resultados esperados necesitamos que el argumento "matriz de valores" de la función sea una matriz de valores únicos.

En esta nota mostraré dos soluciones posibles a este problema: 1) usar columnas auxiliares; 2) usar una FDU (función definida por el usuario).

Solución con columnas auxiliares.

A la izquierda de la matriz insertamos un número de columnas equivalente al número de columnas de la matriz

La celda A2 contiene la fórmula

 =SI(CONTAR.SI($L2:L2,L2)=1,L2,"#")

Esta fórmula evalúa si el valor de la celda L2 aparece por primera vez en la fila; en caso afirmativo da el valor de la celda L2; en caso negativo da como resultado el símbolo #.

EL valor 95 aparece por segunda vez en la celda N2 y por eso el valor resultante en la celda C2 es #.

En la función CONTAR.SI anclamos la columna en la referencia a la primer celda del rango ($L2) de manera que al copiar la fórmula a lo largo de las columnas el rango de evaluación se va extendiendo ($L2:M2 en la celda B2, $L2:N2 en la celda C2 y así sucesivamente).

Ahora podemos usar los rangos de las columnas A:I para nuestros cálculos

En la fórmula

=K.ESIMO.MAYOR($A2:$I2,V$1) 

usamos los valores de la fila 1 como el argumento de orden, fieles al principio de no evitar el uso de constantes en las fórmulas.

Podemos mejorar esta solución funcionalmente convirtiendo todo el rango (columnas auxiliares, matriz de datos y columnas con resultados) en una tabla (Insertar-Tablas-Tabla). De esta manera al agregar alumnos, todas las fórmulas se copian automáticamente

Solución FDU (función definida por el usuario – macro).

La función valUnicos crea una matriz de valores únicos que podemos usar dentro de las funciones de Excel, en nuestro caso en K.ESIMO.MAYOR.

Esta función usa la técnica que ya mostramos en la nota sobre cómo extraer valores únicos.

El código de la función es el siguiente:

Function valUnicos(rngValores As Range)
    Dim iX As Integer
    Dim arrTemp()
    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range
   
    On Error Resume Next
    For Each rngCell In rngValores
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0
       
    ReDim arrTemp(collUnicos.Count)
    For iX = 1 To collUnicos.Count
        arrTemp(iX - 1) = collUnicos.Item(iX)
    Next iX
   
    valUnicos = arrTemp
   
End Function

Como siempre recomiendo, guardamos el código en un módulo del cuaderno Personal.xls(b) de manera de poder usar la función en todo cuaderno abierto.

Esta función utiliza un único argumento: un rango de la hoja. En la función usamos el objeto Collection para descartar los valores repetidos en el rango; luego pasamos los ítems de la colección a una matriz (array).

En nuestro ejemplo, combinamos esta función con K.ESIMO.MAYOR para obtener los resultados deseados

De la misma manera podemos combinarla con otras funciones (K.ESIMO.MENOR, CONTAR, etc). Por ejemplo

=CONTAR(valUnicos(B2:J2))

da como resultado 7 ya que el 95 y el 86 se repiten.


El cuaderno con el código y los ejemplos se puede descargar aquí.

Encontrar el primer número positivo o negativo en el rango

En el pasado hemos tratado el tema de encontrar el último valor en un rango y también el último positivo o negativo.

Para encontrar el primer valor negativo en un rango podemos usar esta fórmula:

=INDICE($A$2:$A$15,COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0))

Para hallar el primer número positivo en el rango usamos

=INDICE($A$2:$A$15,COINCIDIR(VERDADERO,INDICE($A$2:$A$15>0,0,1),0))

Sencillamente invertimos el signo "<" a ">"

La fórmula funciona de esta manera:

La expresión INDICE($A$2:$A$15<0,0,1) genera un vector de valores VERDADERO o FALSO

Luego  COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0) nos da la ubicación de la primera aparición de VERDADERO en el vector

El resultado lo usamos como argumento en la función INDICE "externa"

obteniendo así el resultado

Podemos, también, obtener la dirección de la celda que contiene el valor combinando la función COINCIDIR con la función DIRECCION

=DIRECCION(COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0)+1,1)