Función contar.si.conjunto en Excel 2002

11.04.17

Buenos dias,

Efectivamente, quienes llevamos años trabajando en esto, en algún(os) momento(s) necesitamos hacer lo que mencionas. Cierto es que casos donde fueran necesarias muchas de esas fórmulas indican que la solución es una tabla dinámica.

Pero si buscas una solución por fórmula similar a lo que ahora hacen CONTAR. SI. CONJUNTO() o SUMAR. SI. CONJUNTO() puedes usar "fórmula matriciales".

Es algo así como las fórmulas SUMAR. SI() o CONTAR. SI(), que suman o cuentan sobre la base de una condición, pero estas permiten agregar más condiciones y eventualmente modificar datos antes de usarlos para calcular "en el aire".

Estas fórmulas también son conocidas como CSE formulas, por Control+Shift+Enter que son las teclas que hay que presionar simultáneamente después de tipear la fórmula (no basta con Enter). Un par de llaves {} se agregarán automáticamente, encerrando la fórmula. Esto es FUNDAMENTAL para que funcione.

Observa el siguiente ejemplo para calcular cuántos alumnos hay en cada rango de calificaciones.

Imagina una planilla como la siguiente:

Hay una lista de alumnos y a la derecha construí una tabla donde están los segmentos de calificaciones a contar.

Luego en la celda G2, escribe la siguiente fórmula, pero NO des enter aún:

=SUMA(SI($B$2:$B$131>=E2;SI($B$2:$B$131<E3;1;0)))

[Considera si usas comas o punto y coma para separar argumentos de las funciones. Yo usé ";"]

Luego tienes que presionar Ctrl+Shift+Enter al entrarla en la celda.
Asígnale el formato deseado y, luego, copia esta celda y pégala en las restantes de esta misma columna.

Para el último segmento 9 a 10 será necesario que incluyas un 11 en la última celda para que considere el 10 dentro de ese tramo.

Básicamente, la fórmula armará una matriz de 1 (unos) para cada valor distinto de cero y luego los sumará (que equivale a su forma de CONTAR valores distintos de cero.

En otro ejemplo podrías usarla para calcular el mínimo de una serie de valores donde alguno pudiera llegar a ser cero y que el valor de la celda de referencia D1 coincida con los que están en la columna F

 =MIN(SI($H$4:$H$30<>0;SI($D$1=$F$4:$F$30;$H$4:$H$30)))

Recuerda presionar Ctrl+Shift+Enter al entrarla en la celda.

En este caso la fórmula guarda en una matriz en memoria sólo los valores de aquellas celdas que cumplan la condición de ser mayor que cero y dentro de esos casos, aquellos que coincidan con el valor de D1. Luego, sobre esa matriz virtual busca el valor mínimo.

Como verás, estas fórmulas te dan una flexibilidad importante una vez que las entiendes.

La ventaja es que puedes usarla en cualquier versión de MS Excel, aún en las actuales.

De hecho, yo las prefiero a las que mencionaste.

.

Buenas, de nuevo

Olvidé mencionar que si te resultara complicado armar la fórmula matricial para lo que necesitas, puedes comentarme cuáles son las condiciones y los rangos/celdas involucradas (una imagen ayudaría, también) y yo podría construirla.

Abrazo

Fer

.

Muy buenas tardes, Fernando.

Necesito controlar el número de clases a la semana de los alumnos. En la tabla de la derecha puedes ver una relación de alumnos con sus respectivos identificadores y el número de clases que tienen contratadas a la semana.

En la columna CLASE de la tabla de la izquierda (la que más campos tiene) quiero que aparezca el número de clases que lleva consumidas en una determinada semana, para que me avise y no pueda darle una clase de más. Como puedes observar hay una columna SEMANA (me he hecho una función que numera las semanas del año).

El funcionamiento es el siguiente: yo introduzco una fecha, una hora y un alumno. Automáticamente, tomando estos datos que he introducido, se rellena el número de semana que le corresponde a esa fecha, el nombre del día de la semana, el ID del alumno. Necesito que en el campo CLASE aparezca el número de clases que lleva esa semana, ya que contratan un número de clases por semana. También necesitaría comprobar que no puedan apuntarse a dos o más clases diarias, ya que sólo pueden hacer una clase diaria.

Las clases que tienen cada uno a la semana están en la tabla de la derecha (la pequeña).

Muchas gracias y un abrazo.

.

Buenas noches,

Efectivamente, se trata de una tarea para fórmulas condicionales.

Usaremos, para resolverlo, dos de ellas: Una para computar los días acumulados por cada semana y otra más para controlar las clases tomadas por cada día.

Luego una fórmula condicional evidenciará el estado de cada crossfiter.

Mira esta imagen que guarda alguna similitud con tu planilla:

Verás tres columnas nuevas:

1.- Clases Acumuladas por semana (celda G2 - verde)

Para contar, para cada persona, cuántos días lleva acumulados de esa semana al día donde se coloque la fórmula

Esta celda contendrá la siguiente fórmula matricial, por supuesto, ingresada con Ctrl + Shift + Enter:

=SI(ESBLANCO($E2);"";SUMA(SI($A$1:$A2=$A2;SI($E$1:$E2=$E2;1;0))))

Ya sabes, considera si usas comas o punto y coma para separar argumentos de las funciones. Yo usé ";". Asígnale el formato deseado y, luego, copia esta celda y pégala en las restantes de esta misma columna.

Nota que el extremo inferior del rango es relativo y llega sólo hasta la fila donde se pegó la fórmula, por ello no tiene el signo $. Al inicio tiene un control por si tuvieras las celdas con fórmulas y no hayas ingresado el nombre de la persona aún (registros en blanco para futuros ingresesos)

2.- Clases por dia (celda H2 - amarilla)

Similar a la anterior, controla cuando aparece más de una clase al dia.

La fórmula para ello es esta:

=SI(ESBLANCO($E2);"";SUMA(SI($C$1:$C2=$C2;SI($E$1:$E2=$E2;1;0))))

Recuerda ingresarla con Ctrl + Shift + Enter y luego extenderla al resto de las filas

Si te fijas, simplemente cambié la columna en dónde controlar los días.

3.- Status (celda I2 - Celeste)

Con los resultados de las dos columnas anteriores, la siguiente fórmula (NO matricial) dirá cuál es la situación de cada persona en cada día:

=SI(ESBLANCO($E2);"";SI(H2>1;"Repitió clase";"")&SI(G2>BUSCARV(E2;$L$3:$N$18;3;0);SI(H2>1;" y s";"S")&"uperó contrato";SI(H2>1;"";"OK")))

Esta fórmula primero controla que haya dato en la celda de nombre, luego la cantidad de clases tomadas en un dia y finalmente, la cantidad acumulada comparada (con un buscarv anidado) con los días contratados que le indicas en la tabla.

Bien, esto resuelve todo lo que solicitabas. Eventualmente podría anidar las fórmulas anteriores dentro de la última, pero la haría bastante más compleja.

Como te había mencionado antes, estas fórmulas te servirán en cualquier versión de MS Excel que tengas, aún aquellas posteriores a la que tienes actualmente.

Adicionalmente, dejé un ejemplo de una tabla dinámica que hace un resumen por persona y por semana del MAXimo de clases tomadas.

Espero haber cubierto tus espectativas.

Abrazo

Fer

.

¡Gracias! 

Muchísimas gracias, Fernando. Tu ayuda ha sido rapidísima y de una gran utilidad.

Un fuerte abrazo,

Miguel

Perdona, Fernando, pero la última fórmula tiene un & antes de un SI. Este operador, el &, se usa para concatenar texto. La fórmula da un error y sospecho que puede ser por esto.

.

Hola, Miguel

Efectivamente, ese concatenador permite informar en esa celda si la persona repite clase en un dia y/o superó el acumulado de clases contratado. Es correcto que esté delante de ese SI() y, de hecho, funciona correctamente como ves en la imagen que te había pasado.

En todo caso, controla que los rangos de la fórmula coincidan con los que tienes en tu planilla.

Hubiera sido de ayuda que indicaras qué tipo de error se produjo.

Si la fórmula pudo ingresarse en la celda, es posible que te aparezca un #N/A si no encuentra en la base de días contratados la persona indicada en la columna E.

Para evitar ese error, puedes usar esta variante que, además te avisaría que falta agregar esa persona a la base:

=SI(ESBLANCO($E2);"";SI(ESNOD(BUSCARV(E2;$L$3:$N$18;3;0));"Dar alta persona";SI(H2>1;"Repitió clase";"")&SI(G2>BUSCARV(E2;$L$3:$N$18;3;0);SI(H2>1;" y s";"S")&"uperó contrato";SI(H2>1;"";"OK"))))

Si aún así no funcionara, vuelve a escribir indicando el tipo de error que obtienes.

Abrazo

Fer

.

Buenas noches, Fernando.

El problema estaba en BUSCARV. Cambié todo para utilizar los id de alumno, pero no le dije a BUSCARV que su rango de búsqueda era ahoa de dos columnas y no de tres, y tampoco le indiqué cual era el rango de búsqueda correcto. Por un momento se me olvidó el funcionamiento de dicha función.

Tengo una duda transcendental: no quiero tener que arrastrar las fórmulas cada vez que la cosa se prolongue hacia abajo, pero arrastrarlas hacia el infinito tiene sus desventajas, que los càlculos se diparan de manera imnecesaria ¿qué puedo hacer para que, cuando inserte una nueva entrada al final de la tabla, no tenga que arrastrar ls respectivas fórmulas?

Estoy bastante impresionado y agradecido de tus conocimientos.

Un abrazo,

Miguel