martes, 3 de diciembre de 2013

Contando celdas: la combinación FILAS x COLUMNAS.

Hoy explicaré una forma de contar celdas sin macros, aplicado a un problema concreto planteado por un lector:

Requiero hacer una macro que busque un nombre (Hoja 1)en la columna A por ejemplo (Carlos) y que al mismo tiempo en la columna B tome el valor que tiene al lado, es decir:

Columna A Columna B
Carlos 8
Roberto 10
Carlos 4

y que sume el valor del numero las veces que se repita el nombre de Carlos.

Lo interesante es que la suma del valor se distribuya en unos como una matriz en una Hoja 2 en un rango de 5 filas a lo largo de todas las columnas es decir si tengo una suma de 12

Columna A B C D E F
1 1 1 1
2 1 1 1
3 1 1
4 1 1
5 1 1



Veremos la manera de emplear las funciones estándar de hoja de cálculo a nuestro alcance para conseguir nuestro objetivo (distribuir una cantidad por los elementos de una matriz) sin necesidad de implementar una macro.
Para ello veamos de qué origen de datos partimos:

Contando celdas: la combinación FILAS x COLUMNAS.


Hemos insertado en la celda D3 la función:
=SUMAR.SI(A1:A11;D2;B1:B11)
con el fin de tener el primer resultado solicitado, la suma de los valores de la columna B que coinciden con el valor 'carlos'.


El siguiente paso para conseguir nuestra matriz de cinco filas con 'unos' distribuidos será trabajar una 'matriz auxiliar'. Asi que no situaremos en la celda H2 y escribiremos:
=FILAS(H$2:H2)*COLUMNAS(H$2:H2)+MAX(G$2:G$6)
también podríamos haber optado por esta algo más sencilla:
=FILAS(H$2:H2)+MAX(G$2:G$6)
luego copiaremos cinco filas hacia abajo y tantas columnas a la derecha como necesitemos... esto resultará una numeración de arriba hacia abajo y de izquierda hacia derecha consecutiva:

Contando celdas: la combinación FILAS x COLUMNAS.


Fijémonos en que no haya datos en la columna G.
El funcionamiento de esa operación =FILAS(H$2:H2)*COLUMNAS(H$2:H2) devuelve una numeración de filas por cada columna (1,2,3,4 y 5 para nuestro ejemplo); y a continuación sumamos el valor máximo de la columna anterior con +MAX(G$2:G$6); asegurándonos entonces ese secuencia correlativa con saltos de 5 en 5 por cada columna.


El siguiente y último paso consite en emplear nuestros valores calculados en la matriz auxilar y aplicarlos con el fin de obtener la distribución pedida.
Como ya conocemos cuál es el valor a distribuir, en la celda D3, bastará aplicar un condicional del tipo y escribir en H9:
=SI(H2<=$D$3;1;"")
arrastrando al resto de elementos de la matriz final:

Contando celdas: la combinación FILAS x COLUMNAS.



Así en dos pasos tenemos lo que se solicitaba, y sin macros.

En el ejemplo he expuesto dos maneras de conseguir el mismo resultado, la primera consistía en multiplicar la función FILAS por la función COLUMNAS (aunque realmente para el ejemplo no era necesario); pero me parece interesante recordar cómo podemos contar celdas de un rango o matriz con funciones de Excel; y es que sería tan sencillo como calcular el producto del número total de filas por el número de columnas de dicha matriz:
=FILAS(H2:M6)*COLUMNAS(H2:M6)
por ejemplo, la matriz H2:M6 tiene 30 celdas (5 filas x 6 columnas).

2 comentarios:

  1. Hola, felicidades por el excelente Blog. Una consulta, no sé si sea posible ésto; en mi trabajo llegan diariamente cierto número de sobres que se reparten entre cierto número de trabajadores.

    Por ejemplo 100 sobres para 10 ejecutivos, a veces más, a veces menos. La idea es que de la lista en Excel de lo que llegó, que está en la Columna A, excel pueda repartir de forma automática mediante una fórmula en la columna B a quien le toca proporcionalmente, por ejemplo si hay 5 ejecutivos y 20 sobres que Excel pueda asignarlo así:
    Número de operaciones por ejecutivo = 4

    Columna A Columna B (fórmula)
    506010 Ejecutivo A
    506011 Ejecutivo A
    506012 Ejecutivo A
    506013 Ejecutivo A Aquí Excel debería de saber que 4 es lo que corresponde al Ejecutivo A
    506014 Ejecutivo B Aquí Excel debe saber que debe dar 4 a ejecutivo B.
    506015 Ejecutivo B
    506016 Ejecutivo B
    506017 Ejecutivo B
    506018 Ejecutivo C
    ... ...
    506029 Ejecutivo D

    Lo que he logrado es que Excel calcule cuantos sobres le tocan a cada ejecutivo, pero no he logrado que haga que la columna B de el resultado adecuado. Los nombres de los ejecutivos salen de otra lista donde apunto la gente disponible.

    Lo pongo muy simplificado, pero en la realidad son cientos de operaciones, que además se deben ponderar se acuerdoa un puntaje por tipo de operación y el número de ejecutivos es muy variable, pero si me pudieras orientar sobre como lograr que Excel reparta el trabajo de forma equitativa entre

    Gracias de antemano,

    ResponderEliminar
    Respuestas
    1. Hola,
      escribiré un post al respecto, pero para hacer el reparto te diría que emplearas la combinación
      =ENTERO(FILA()/proporcion)
      siendo proporción el número de operaciones por ejecutivo... luego solo vincula el número obtenido sobre tu rango de ejecutivos.

      Un saludo y espero te sirva.

      Eliminar