Supponi di avere un foglio di lavoro con tre colonne di dati. La prima colonna ha in ordine sequenziale ogni lettera dell’alfabeto, dalla A alla Z.

La seconda colonna contiene un numero di occorrenze correlate alla lettera dell’alfabeto. La terza colonna contiene un numero di ore correlato alla lettera dell’alfabeto.

E se volessi distribuire, nel modo più uniforme possibile, una combinazione delle lettere dell’alfabeto in quattro gruppi in base alla terza colonna (ore)?

Ad esempio, se la somma di tutte le ore per ogni lettera dell’alfabeto è di 4.000 ore, si desidera trovare una combinazione che segreghi l’alfabeto in modo che ciascuno dei quattro gruppi abbia circa 1.000 ore per gruppo.

Questo è in realtà un problema ben noto nel campo della matematica discreta. Sono stati sviluppati diversi algoritmi per fornire soluzioni e ci sono alcuni linguaggi di programmazione (come LISP)

che facilitano notevolmente la creazione di strutture ad albero in grado di “cercare” soluzioni ottimali.

In questo caso, tuttavia, è preferibile un approccio semplice e ciò implica l’utilizzo di una macro. Supponiamo di avere i dati nelle colonne dalla A alla C. La seguente macro analizzerà l’intervallo specificato e restituirà una combinazione di valori che soddisfano i requisiti.

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

Si noti che a questa funzione vengono passati cinque parametri. Il primo è l’intervallo che vuoi valutare, il secondo è l’offset della colonna all’interno di quell’intervallo che dovrebbe essere sommato, il terzo è il numero di “bucket” che vuoi utilizzare nella valutazione, il quarto è il numero del bucket che desideri restituire e il quinto è l’offset della colonna (nell’intervallo specificato) che contiene i valori che desideri vengano restituiti.

Quello che fa la macro è prendere tutti i valori nella colonna che vuoi totalizzare, e poi ordinarli in ordine decrescente. Questi valori, dal più grande al più piccolo, vengono poi distribuiti tra tanti “bucket”

hai specificato che dovrebbe esserci. Il numero viene sempre aggiunto al bucket che contiene il totale più piccolo. La stringa restituita dalla funzione rappresenta i valori restituiti (qualunque cosa si trovi in ​​ogni cella della colonna specificata dal quinto parametro) e il totale del bucket.

Ad esempio, se si desidera valutare l’intervallo A1: C: 26, si desidera che la distribuzione si basi sui valori nella terza colonna dell’intervallo (colonna C), si desidera che ci siano quattro intervalli nell’analisi, si volevi che il terzo bucket fosse restituito e volevi che la funzione restituisse tutto ciò che è nella colonna A dell’intervallo, quindi dovresti usare quanto segue per chiamare la funzione:

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

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (12234) si applica a Microsoft Excel 2007, 2010, 2013 e 2016.

Puoi trovare una versione di questo suggerimento per la vecchia interfaccia del menu di Excel qui:

Determinazione delle combinazioni per ottenere un totale.