viernes, 8 de febrero de 2013

Una matricial en Excel para sumar referencias cruzadas.

Nuevamente atacando con las matriciales. En esta ocasión en respuesta a la cuestión planteada:
...Se trata de una tabla en la que la cabecera de la primera fila figuran unos años, pongamos del 2010 al 2017.
En la cabecera de las columnas figuran los nombres de unos centros de trabajo (que pueden estar repetidos).

Y la matriz está compuesta por unos numeros que son las horas que cada centro de trabajo ha estado produciendo en cada uno de esos años.

Por otro lado tengo dos celdas que contienen un AÑO DE INICIO y un AÑO DE FIN. Y en otra celda tengo el nombre de uno de los centros de trabajo.

Necesito una fórmula -no valen tablas dinámicas-, tiene que ser en fórmula que me obtenga las horas de producción para ESE CENTRO DE TRABAJO ENTRE las fechas de INICIO y FIN que figuran en las celdas correspondientes...


Lo interesante de esta fórmula matricial en nuestro Excel es que trabajamos en un rango conjunto de datos, sobre parámetros en fila y en columnas... es decir, sobre una tabla de referencias cruzadas, en definitiva sobre un rango bidimensional!! (normalmente nuestros rangos son únicamente una columna o una fila).
Veamos en la imagen el planteamiento:

Una matricial en Excel para sumar referencias cruzadas.



El objetivo está claro, debo obtener en la celda O3 una sóla fórmula que acumule los valores para las filas con igual 'Centro de trabajo' que estén en el intervalo de Años dado.
Sin duda el asunto sería algo más sencillo si pudieramos trabajar con columnas auxiliares, pero el reto de hoy es conseguirlo en una sóla celda; y para ello deberemos recordar todo lo aprendido respecto al trabajo de las matriciales, y el comportamiento de los rangos 'virtuales' en ellas.


Empezaremos por el final, para los impacientes, mostrando la fórmula matricial deseada (recordemos que para ejecutarla debemos presionar a un tiempo Ctrl+Mayusc+Enter):
=SUMA(SI($B$3:$B$9=$L$3;SI(C$2:J$2>=$M$3;SI(C$2:J$2<=$N$3;C$3:J$9;0);0)))



Y ahora la explicación o desglose. La parte más profunda de la fórmula nos devolvería rangos en columna para aquellos Años que cumplan la condición de estar entre 'Inicio'(celda M3) y 'Fin'(celda N3).
Si ejecutamos la siguiente matricial para cada una de las columnas de 'Año', es decir, una matricial en C11:C17, otra en D11:D17, otra en E11:E17, etc conseguiríamos parte de la información necesaria, segregando, momentaneamente los Años de estudio. Esta es la fórmula matricial parcial que nos devuelve datos únicamente para los Años entre el intervalo de análisis:
=SI(Y(E$2>=$M$3;E$2<=$N$3);E$3:E$9;0)

Una matricial en Excel para sumar referencias cruzadas.



Pero claro está, con esta fórmula obtenemos valores para todos los 'Centros de trabajo'; así que ahora tendríamos que aplicar un nuevo criterio aplicado sobre estos Centros de trabajo, en horizontal (por filas); por lo que sobre los rango obtenidos, aplicaremos la última condición:
=SI($L11=$L$3;C11:J11;0)
una matricial horizontal sobre cada fila, esto es, una sobre M11:T11, otra sobre M12:T12, etc.

Una matricial en Excel para sumar referencias cruzadas.



Finalmente bastaría sumar el resultado obtenido de ambos cruces para conocer el Resultado final.

Para evitar trabajar con rangos auxiliares, aplicamos en una sóla celda O3, trabajando sobre el rango original absoluto, todas las condiciones de 'Centro de trabajp' y 'Años', con la fórmula matricial descrita inicialmente:
=SUMA(SI($B$3:$B$9=$L$3;SI(C$2:J$2>=$M$3;SI(C$2:J$2<=$N$3;C$3:J$9;0);0)))

15 comentarios:

  1. Hola excelforo,
    Una consulta, simple curiosidad porque veo que la fórmula matricial cada día me sorprende más; haz sumado en forma horizontal y como resultado es 21. mi consulta es como sería la fórmula en sentido vertical, por ejemplo del ejercicio del blog celdas (E11:F17), que da como resultado 75.
    De antemano muchas gracias por tu tiempo.
    Atte:)
    Claudia.

    ResponderEliminar
    Respuestas
    1. Hola Claudia,
      sí sólo te interesa obtener el resultado aplicando el criterio de los años, el asunto es más sencillo, simplemente elimina la primera condición de 'Centro de trabajo', quedaría algo así:
      =SUMA(SI(C$2:J$2>=$M$3;SI(C$2:J$2<=$N$3;C$3:J$9;0);0))

      Pruébala y comentas.
      Slds

      Eliminar
    2. Gracias excelforo,
      por tu aporte eres un manita con excel, estuve dos horas intentando resolverlo sin éxito, y tú lo resuelve en un cerrar y abrir de ojo.
      muchas gracias
      Atte:)
      Claudia.

      Eliminar
  2. Hola Ismael,
    buenos días, una consulta en formato condicional como se haría la fórmula, en las celdas (C3:J9) del ejemplo en cuestión, formato de fondo rojo que sumado de el resultado de 21
    gracias
    María.

    ResponderEliminar
    Respuestas
    1. Hola María,
      no creo que tal cosa sea posible, ya que las combinaciones de celdas (2 a 2, 3 a 3, etc) que puden sumar esa cantidad (=21) serían demasiadas.
      En todo caso, se podría configurar el Formato condicional para que el fondo se ponga rojo siempre que la suma del conjunto sea 21.
      En el formato condicional con fórmula sobre C3:J9
      =SUMA(C3:J9)=21

      Realmente lo que estás pidiendo es una especie de conciliación pero con el formato condicional...
      Lo siento.
      Slds

      Eliminar
  3. Hola excelforo,
    Quisas no me supe explicar lo que quiero es que solo las celdas (E4:F4) = 6+4, y (E9:F9) = 10+1
    que sumado ambas celdas da 21, y si cambio el 'Centro de trabajo' L3 seria otras celdas coloreadas y otro el resultado ya no 21, osea dinámico.
    muchas gracias
    María

    ResponderEliminar
    Respuestas
    1. Ok,
      o sea sólo quieres marcar las celdas, independientemente de lo que sumen, que verifiquen el cruce de condiciones (Año y Centro de trabajo).
      En este caso la fórmula para el formato condicional sería:
      =Y(C$1>=$M$3;C$1<=$N$3;$B3=$L$3)
      Seleccionando el rango completo C3:J9 con la celda activa C3.
      Espero sea lo que buscabas.
      Slds

      Eliminar
  4. Reproduje el mismo ejemplo en una planilla pero me entrega como resultado "0". Me puedes ayudar?
    Gracias!

    ResponderEliminar
    Respuestas
    1. Hola Carla,
      es es un fallo habitual cuando al ejecutar la fórmula has presionado sólo Enter en lugar de Ctrl+Mayusc+Enter

      Recuerda que hablamos de funciones matriciales.
      Slds cordiales

      Eliminar
  5. Saludos,

    Gracias por el mensaje en otro foro (no logro encontrarlo) ...luego de analizar el mismo....favor pregunto ¿cómo se podría hacer "matricial" esta otra situación?...(Transcribo aqui..lo envie tambien por e-mail.)

    BÚSQUEDA SIN FORMULA MATRICIAL
    (Obtiene datos,.. pero formulas para cada fila)

    FORMULA EN A8 y A9, e igual en resto de filas....

    =K.ESIMO.MENOR((Codigo=$B$3)*(Fecha>=$B$4)*(Fecha<=$B$5)*(Numero)|1)
    =K.ESIMO.MENOR((Codigo=$B$3)*(Fecha>=$B$4)*(Fecha<=$B$5)*(Numero)|1)

    Cómo hacer para que sean "matriciales"? O para que arrojen los resultados esperados?


    Buscar CódigoNº 40 ESTA ES LA CELDA B3 (valor 40)
    Fecha Inicial 01/03/2015 esta es celda b4
    Fecha Final 31/03/2016 esta es celda b5

    A B C D
    Numero Fecha Valor1 Valor2
    8 12345 01/04/2015 10,1 0
    9 0 00/01/1900 0 0
    10 2345 15/05/2015 98,7 0
    11 0 00/01/1900 0 0
    12 0 00/01/1900 0 0
    13 0 00/01/1900 0 0


    DATOS (los datos son cientos...por eso preguntaba por una formula matricial, probe tabla dinamica pero preferiria formulas para el tratamiento y analisis)
    A B C D E
    Numero Fecha Codigo Valor1 Valor2
    8 12345 01/04/2015 40 10,10
    9 67890 01/04/2015 20 68,45
    10 2345 15/05/2015 40 98,70
    11 7890 15/05/2015 20 45,44
    12 245 25/03/2016 100 97,84
    13 678 25/03/2016 20 24,00

    saludos, JOSE LUIS

    Atte.,

    ResponderEliminar
    Respuestas
    1. ...Disculpas, salió el mensaje "desordenado"...pero envié el archivo por e-mail.....

      Eliminar
    2. Ya te he contestado por email ;-)
      La idea sería emplear la función FILA para recuperar los elementos ordenados:
      =K.ESIMO.MENOR((Codigo=$B$3)*(Fecha>=$B$4)*(Fecha<=$B$5)*(Numero);FILA($8:$13)-7)
      y ejecutarla sobre todo el rango A8:A13 a la vez.
      Slds

      Eliminar
  6. Muy buena tarde, ando en búsqueda de poder resolver lo siguiente:
    en una hoja1 tengo columna fecha y monto, en la otra hoja2 orden de compras y fecha inicio y fecha fin, quisiera con una formula lograr llevar el dato de la orden de compras hoja2 hacia la hoja1 teniendo en cuenta que la fecha de la hoja1 esta en el rango de fecha (fecha inicio y fecha fin) hoja2 y esta por supuesto coincide.

    de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      yo empezaría poniendo en la Hoja2 una nueva columna numerada como 1,2,3,4, etc que identifique de manera única cada fila (por ejemplo col A), y a su derecha (col B) Orden de compra, y (col C) Fecha Inicio y (col D) Fecha Fin

      Así en la hoja 1 a partir de la fecha podemos recuperar el dato con una fórmula similar a esta:
      =INDICE(Hoja2!$B$1:$B$5;SUMAR.SI.CONJUNTO(Hoja2!A1:A5;Hoja2!C1:C5;"<="&A2;Hoja2!D1:D5;">="&A2))

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.