martes, 14 de febrero de 2012

Matriz de constantes en Excel.

Daré otro ejemplo del uso para una Matriz de constantes. En concreto se trata de evaluar sobre una tabla de datos ciertos elementos de un campo para sumar, de manera condicionada a unos criterios dados. Concretamente deseamos acumular únicamente los movimientos de aquellos 'Comerciales' en cuyo concepto aparezca o bien el texto 'CONSIG' o bien el texto 'MY12-'.
Veamos nuestra tabla de partida con los textos remarcados:

Matriz de constantes en Excel.


Como siempre existen muchas formas de resolver este problema, pero en esta ocasión propondré una forma alternativa de trabajo mediante el empleo de una matriz de constantes. La fórmula matricial (ejecutada presionando Ctrl+Mayusc+Enter) que añadiremos en la columna H de nuestro ejemplo, y que resuelve la cuestión es:
{=SUMA(SI(O(CONTAR.SI($A3;"*CONSIG*")>=1;CONTAR.SI($A3;"*MY12-*")>=1);SUMAPRODUCTO(B3:G3;{1\1\1\1\1\1})))}

Lo primero que hacemos con esta función matricial es evaluar los elementos del campo concepto, mediante un CONTAR.SI, ya que esta función admite el uso de comodines (asterisco e interrogación) en sus argumentos de criterios(como otras), anidadas en una función lógica O, conseguimos averiguar si el elemento contiene bien el texto 'CONSIG' o bien el texto 'MY12-' en alguna parte de la celda.
Mediante un condicional SI efectuamos la operación en caso de que sea cierta o verdadera dicha condición. Y es aquí, cuando sea cierto, que empleamos la matriz de constantes, ya que aplicaremos la función:
SUMAPRODUCTO(B3:G3;{1\1\1\1\1\1})
que realiza el producto elemento a elemento de la cantidad de cada 'Comercial'(rango B3:G3) por el elemento correspondiente de nuestra matriz {1\1\1\1\1\1}, acumulando dichos productos.

Nuestra función finaliza anidando el acumulado con una SUMA, que devuelve el resultado esperado.
Aunque ciertamente es una fórmula algo 'retorcida', es interesante cómo podemos generar a nuestro antojo, matrices de constantes, para conseguir nuestros objetivos, permitiéndonos Excel su uso como argumentos en ciertas funciones estándar (siempre que admitan rangos de celdas como argumentos).

Matriz de constantes en Excel.

4 comentarios:

  1. Hola Excelforo, muy bueno tu sitio, muy ilustrativo.

    Te comento porque tengo un problema con el uso de constantes de matrices en excel07. El problema está cuando trato de introducir una constante tipo columna y fila, se sabe que para introducir una constante tipo fila, el procedimiento es primero seleccionar el rango donde se introduciran los valores, encerrar en llaves ({}) los elementos de la matriz, separados por comas (,) por ejemplo:

    ={1,2,3,4,5}, y luego presionar Ctrl+Shift+Enter, pero cuando hago esto me aparece un mensaje de error que me dice que la fórmula es incorrecta, y cuando lo hago con (;), si me devuelve la constante pero cuando uso (;) para insertar una constante columna me devuelve sólo el primer elemento de la matriz, por ejemplo, del ejemplo de arriba me devolvería solamente 1,¿cómo podría resolver ese problema?

    ResponderEliminar
    Respuestas
    1. Hola!
      la verdad es que el tema este de las constantes matriciales tiene bastante de prueba y error respecto al signo de separación, depende de la versión de Excel y en algunos casos de la configuración...
      Sólo te queda probar entre la , el ; y la \ para saber en tu caso qué opera para filas y qué para filas.
      No sé el motivo de esa disparidad.
      Un cordial saludo

      Eliminar
  2. Muchas gracias experto, me funcionó la iserción de una constante vertical utilizando el backslash, me ayudaste mucho, gracias por la rapidez de tu respuesta..muy bueno el blog.

    ResponderEliminar