Suponga que tiene una hoja de trabajo con tres columnas de datos. La primera columna tiene en orden secuencial cada letra del alfabeto, de la A a la Z.

La segunda columna contiene una serie de ocurrencias que se correlacionan con la letra del alfabeto. La tercera columna contiene una cantidad de horas que se correlaciona con la letra del alfabeto.

¿Qué sucede si desea distribuir, de la manera más uniforme posible, una combinación de las letras del alfabeto en cuatro grupos según la tercera columna (horas)?

Por ejemplo, si la suma de todas las horas para cada letra del alfabeto es 4.000 horas, querrá encontrar una combinación que separe el alfabeto de modo que cada uno de los cuatro grupos tenga alrededor de 1.000 horas por grupo.

En realidad, este es un problema bien conocido en el campo de las matemáticas discretas. Se han desarrollado una variedad de algoritmos para proporcionar soluciones y existen ciertos lenguajes de programación (como LISP)

que facilitan enormemente la creación de estructuras de árbol que puedan «buscar» soluciones óptimas.

En este caso, sin embargo, lo mejor es un enfoque simple, y eso implica el uso de una macro. Supongamos que tiene sus datos en las columnas A a C. La siguiente macro analizará el rango que especifique y devolverá una combinación de valores que cumplan con sus requisitos.

Function DoDist(sRaw As Range, _   iTCol As Integer, _   iBuckets As Integer, _   iWanted As Integer, _   iRetCol As Integer) As String

Dim lGTotal As Long     Dim lPerBucket As Long     Dim lCells() As Long     Dim sRet() As String     Dim lBk() As Long     Dim sBk() As String     Dim lTemp As Long     Dim sTemp As String     Dim J As Integer     Dim K As Integer     Dim L As Integer

Application.Volatile     ReDim lCells(sRaw.Rows.Count)

ReDim sRet(sRaw.Rows.Count)

ReDim lBk(iBuckets)

ReDim sBk(iBuckets)



lGTotal = 0     For J = 1 To sRaw.Rows.Count         lCells(J) = sRaw(J, iTCol)

lGTotal = lGTotal + lCells(J)

sRet(J) = sRaw(J, iRetCol)

Next J

For J = 1 To sRaw.Rows.Count - 1         For K = J + 1 To sRaw.Rows.Count             If lCells(J) < lCells(K) Then                 lTemp = lCells(J)

lCells(J) = lCells(K)

lCells(K) = lTemp                 sTemp = sRet(J)

sRet(J) = sRet(K)

sRet(K) = sTemp             End If         Next K     Next J

lPerBucket = lGTotal / iBuckets     For J = 1 To sRaw.Rows.Count         L = iBuckets         For K = iBuckets To 1 Step -1             If lBk(K) <= lBk(L) Then L = K         Next K         lBk(L) = lBk(L) + lCells(J)

sBk(L) = sBk(L) & sRet(J) & ", "

Next J

For J = 1 To iBuckets         If Right(sBk(J), 2) = ", " Then             sBk(J) = Left(sBk(J), Len(sBk(J)) - 2)

End If         sBk(J) = sBk(J) & " (" & lBk(J) & ")"

Next J

DoDist = sBk(iWanted)

End Function

Observe que a esta función se le pasan cinco parámetros. El primero es el rango que desea evaluar, el segundo es el desplazamiento de la columna dentro de ese rango que debe totalizarse, el tercero es el número de «cubos» que desea usar en la evaluación, el cuarto es el número de cubo que desea devolver, y el quinto es el desplazamiento de la columna (en el rango especificado) que contiene los valores que desea devolver.

Lo que hace la macro es tomar todos los valores de la columna que desea sumar y luego ordenarlos en orden descendente. Estos valores, de mayor a menor, se distribuyen entre muchos «cubos»

usted especificó que debería haber. El número siempre se agrega al depósito que contiene el total más pequeño. La cadena que devuelve la función representa los valores devueltos (lo que esté en cada celda de la columna especificada por el quinto parámetro) y el total del depósito.

Por ejemplo, si desea evaluar el rango A1: C: 26, desea que la distribución se base en los valores de la tercera columna del rango (columna C), desea que haya cuatro depósitos en el análisis, quería que se devolviera el tercer depósito, y quería que la función devolviera lo que esté en la columna A del rango, entonces usaría lo siguiente para llamar a la función:

=DoDist(A1:C26,3,4,3,1)

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (12234) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.

Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

`link: / excel-Determining_Combinations_to_Make_a_Total [Determining Combinations to Make a Total] ‘.