En este post explicaré el uso de una Matriz de constantes; en concreto usaremos este tipo de Matriz para conseguir calcular el número de valores pares existente en un listado de datos algo peculiar...
Veamos este listado:
Observamos que nuestros datos se encuentran en el rango A2:A11 por filas, pero que existen hasta ocho valores en cada celda, separados por el caracter '/'. Nuestro trabajo será construir una única fórmula matricial que calcule el número total de valores pares existente entre todos esos valores.
Lo más importante será determinar una Matriz de constantes sobre la que trabajar para poder, y posteriormente, incorporar a nuestra definitiva matricial.
Una Matriz de constantes no es más que una matricial cuyos elementos son valores fijos. Esta matriz la podemos construir bien dándole una orientación horizontal, como será la de nuestro ejemplo, algo del tipo ={1;2;3;4}; o bien con una orientación vertical ={1\2\3\4}... por supuesto, combinando ambos signos de puntuación, tendríamos una matriz de constantes bidimensional ={1;2;3\4;5;6}.
Conviene, normalmente, ejecutarlas como cualquier otra matricial, esto es, presionando Ctrl+Mayusc+Enter.
Nuestra matriz de constantes será:
={4;8;12;16;20;24;28;32}
que son precisamente las posiciones de cada uno de los caracteres '/' que dividen cada celda en ocho valores.
De hecho, podríamos obtener con una función EXTRAE los ocho valores, cada uno en una celda empleando esas posiciones
=EXTRAE($A2;C$1-3;3):
También esta función EXTRAE es importante para nuestra función matricial, ya que realmente vamos a replicar este movimiento que acabamos de realizar.
Si combinamos y anidamos la función EXTRAE con nuestra matriz de constantes, con la función ES.PAR (que es lo que queremos saber para cada valor), tendríamos la siguiente matricial para cada celda de datos (Ctrl+Mayusc+Enter):
{=1*ES.PAR(EXTRAE($A2;{4;8;12;16;20;24;28;32}-3;3))}
Analicemos esta primera parte; con EXTRAE($A2;{4;8;12;16;20;24;28;32}-3;3) obtenemos un rango con los ocho valores (igual al que obtuvimos con la función directa =EXTRAE($A2;C$1-3;3)); anidando cada uno de los valores a la función ES.PAR determinamos si es VERDADERO o FALSO, es decir, si es o no par, para obtener un 1 ó un 0, multiplicamos el resultado por 1; de esta manera nos aseguramos que el siguiente paso funcione correctamente.
Si sumamos este conjunto de valores 0 y 1, tendríamos el número de valores pares en nuestro rango de estudio A2:A11.
Bien, pues todo esto, se puede realizar con una única fórmula matricial, que replicaría cada uno de los pasos:
{=SUMA(1*ES.PAR(EXTRAE($A$2:$A$11;{4;8;12;16;20;24;28;32}-3;3)))}
en la que analizamos con la función EXTRAE cada celda del rango A2:A11, obteniendo para cada celda un rango de ocho valores, que son evaluados por la función ES.PAR, y luego con una SUMA determinamos cuántos valores son igual a 1, es decir, cuantos valores son PARES; para nuestro ejemplo 37.
Veamos este listado:
Observamos que nuestros datos se encuentran en el rango A2:A11 por filas, pero que existen hasta ocho valores en cada celda, separados por el caracter '/'. Nuestro trabajo será construir una única fórmula matricial que calcule el número total de valores pares existente entre todos esos valores.
Lo más importante será determinar una Matriz de constantes sobre la que trabajar para poder, y posteriormente, incorporar a nuestra definitiva matricial.
Una Matriz de constantes no es más que una matricial cuyos elementos son valores fijos. Esta matriz la podemos construir bien dándole una orientación horizontal, como será la de nuestro ejemplo, algo del tipo ={1;2;3;4}; o bien con una orientación vertical ={1\2\3\4}... por supuesto, combinando ambos signos de puntuación, tendríamos una matriz de constantes bidimensional ={1;2;3\4;5;6}.
Conviene, normalmente, ejecutarlas como cualquier otra matricial, esto es, presionando Ctrl+Mayusc+Enter.
Nuestra matriz de constantes será:
={4;8;12;16;20;24;28;32}
que son precisamente las posiciones de cada uno de los caracteres '/' que dividen cada celda en ocho valores.
De hecho, podríamos obtener con una función EXTRAE los ocho valores, cada uno en una celda empleando esas posiciones
=EXTRAE($A2;C$1-3;3):
También esta función EXTRAE es importante para nuestra función matricial, ya que realmente vamos a replicar este movimiento que acabamos de realizar.
Si combinamos y anidamos la función EXTRAE con nuestra matriz de constantes, con la función ES.PAR (que es lo que queremos saber para cada valor), tendríamos la siguiente matricial para cada celda de datos (Ctrl+Mayusc+Enter):
{=1*ES.PAR(EXTRAE($A2;{4;8;12;16;20;24;28;32}-3;3))}
Analicemos esta primera parte; con EXTRAE($A2;{4;8;12;16;20;24;28;32}-3;3) obtenemos un rango con los ocho valores (igual al que obtuvimos con la función directa =EXTRAE($A2;C$1-3;3)); anidando cada uno de los valores a la función ES.PAR determinamos si es VERDADERO o FALSO, es decir, si es o no par, para obtener un 1 ó un 0, multiplicamos el resultado por 1; de esta manera nos aseguramos que el siguiente paso funcione correctamente.
Si sumamos este conjunto de valores 0 y 1, tendríamos el número de valores pares en nuestro rango de estudio A2:A11.
Bien, pues todo esto, se puede realizar con una única fórmula matricial, que replicaría cada uno de los pasos:
{=SUMA(1*ES.PAR(EXTRAE($A$2:$A$11;{4;8;12;16;20;24;28;32}-3;3)))}
en la que analizamos con la función EXTRAE cada celda del rango A2:A11, obteniendo para cada celda un rango de ocho valores, que son evaluados por la función ES.PAR, y luego con una SUMA determinamos cuántos valores son igual a 1, es decir, cuantos valores son PARES; para nuestro ejemplo 37.
Tengo un problema!me dieron un listado de 944 alumnos,necesito saber saber cuantos alumnos tengo en el "grupo 1", pero únicamente del turno"Matutino"
ResponderEliminarY lo mismo con distintos grupos, y también del turno vespertino,
me dijeron que usará formulas matriciales, pero no encuentro por ningún lado cono hacerlo! D:
Hola,
ResponderEliminarbueno, es cierto que parece que una función matricial podría solucionar tu problema, aunque existen algunas otras maneras que quizá te resulten más sencillas.
Puedes revisar este post:
http://excelforo.blogspot.com/2009/12/funcion-contarsiconjunto-en-excel-2007.html
en él se explican tres formas de conseguir contar registros que cumplna varias condiciones.
Espero te sirva.
Un saludo