Hur hittar jag alla kombinationer som motsvarar en viss summa i Excel?

Till exempel har jag följande lista med siffror, och nu vill jag veta vilken kombination av siffror i listan som sammanfattar upp till 480, i följande skärmdump som visas kan du se att det finns fem grupper av möjliga kombinationer som lägger till lika till 480, till exempel 300 + 60 + 120, 300 + 60 + 40 + 80, etc. Den här artikeln kommer jag att prata om några metoder för att hitta vilka celler som summerar till ett specifikt värde i Excel.

• Hitta cellkombinationer som motsvarar en given summa med formler
• Hitta cellkombinationer som motsvarar en given summa med Solver-tillägget
• Hitta cellkombinationer som motsvarar en given summa med användardefinierad funktion
• Hitta alla kombinationer som motsvarar en viss summa med en fantastisk funktion

Hitta cellkombinationer som motsvarar en given summa med formler

Först måste du skapa några intervallnamn och sedan använda en matrisformel för att hitta cellerna som summerar till målvärdet, gör med följande steg för steg:

1. Välj nummerlista och definiera listan ett intervallnamn-- Område1 i Namn Boxoch tryck på ange för att avsluta det definierade intervallnamnet, se skärmdump:

2. När du har definierat ett intervallnamn för nummersidan måste du skapa ytterligare två intervallnamn i Namnhanterare rutan, klicka Formler > NamnhanterareI Namnhanterare dialogrutan, klicka Ny knappen, se skärmdumpar:

3. I poppade ut Nytt namn dialogrutan, ange ett namn List1 i Namn fält och skriv den här formeln = RAD (INDIRECT ("1:" & ROWS (Range1))) (Område1 är det intervallnamn du skapade i steg 1) i Refererar till fält, se skärmdump:

4. Klicka OK att återvända till Namnhanterare dialogrutan och fortsätt sedan att klicka Ny för att skapa ett annat intervallnamn i Nytt namn dialogrutan, ange ett namn List2 i Namn fält och skriv den här formeln = RAD (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Område1 är det intervallnamn du skapade i steg 1) i Refererar till fält, se skärmdump:

5. När du har skapat intervallnamnen, använd följande matrisformel i cell B2:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")och tryck på Skift + Ctrl + Enter tangenterna tillsammans, dra sedan påfyllningshandtaget ner till cell B8, det sista numret i listan, och du kan se siffrorna som totalt är 480 är markerade som X i kolumn B, se skärmdump:

Hitta cellkombinationer som motsvarar en given summa med Solver-tillägget

Om du är förvirrad med ovanstående metod innehåller Excel en Solver-tillägg med detta tillägg kan du också identifiera de siffror som totalt belopp motsvarar ett givet värde.

1. Först måste du aktivera detta Lösare tillägg, var snäll gå till Fil > TillbehörI Excel-alternativ dialogrutan, klicka Tillägg från den vänstra rutan och klicka sedan på Solver-tillägg från Inaktiva programtillägg avsnitt, se skärmdump:

2. Klicka sedan Go knappen för att komma in i Tillägg dialogrutan, kontrollera Solver-tillägg alternativet och klicka på OK för att installera detta tillägg framgångsrikt.

3. Efter att ha aktiverat Solver-tillägget måste du ange denna formel i cellen B9: = SUMPRODUKT (B2: B9, A2: A9), (B2: B9 är en tom kolumncell bredvid din nummerlista och A2: A9 är nummerlistan som du använder. ) och tryck på ange nyckel, se skärmdump:

4. Klicka sedan på Data > Lösare för att gå till Lösningsparameter dialogrutan, gör följande i dialogrutan:

(1.) Klicka på  för att välja cellen B10 där din formel kommer in från Ställ in mål sektion;

(2.) Sedan i Till avsnitt, välj Värdet avoch ange ditt målvärde 480 som du behöver;

(3.) Under Genom att ändra variabla celler avsnitt, klicka för att välja cellintervall B2: B9 där markerar dina motsvarande nummer.

5. Och klicka sedan på Lägg till knappen för att gå till Lägg till begränsning dialogrutan, klicka för att välja cellintervall B2: B9, och välj bin från rullgardinsmenyn, se skärmdump:

6. Klicka OK att gå tillbaka Lösningsparameter dialogrutan och klicka sedan på Lös knappen, några minuter senare, a Lösningsresultat dialogrutan visas och du kan se kombinationen av celler som motsvarar en given summa 480 är markerade som 1. I Lösningsresultat välj Behåll lösningsmedelslösningen alternativet och klicka på OK för att avsluta dialogen. Se skärmdump:

Hitta cellkombinationer som motsvarar en given summa med användardefinierad funktion

De två första metoderna är alla komplexa för de flesta av våra Excel-användare, här kan jag skapa en VBA-kod för att lösa detta jobb snabbt och enkelt.

För att få rätt resultat måste du först sortera nummerlistan i fallande ordning. Och gör sedan med följande steg:

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka Insert > Modulernaoch klistra in följande kod i Modulerna Fönster.

VBA-kod: Hitta cellkombination som motsvarar en given summa:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. Spara och stäng sedan kodfönstret och gå sedan tillbaka till kalkylbladet och ange den här formeln = getcombination (A2: A9, C2) in i en tom cell och tryck på ange nyckel får du följande resultat som visar kombinationsnummer som motsvarar en viss summa, se skärmdump:

Hitta alla kombinationer som motsvarar en viss summa med en fantastisk funktion

Kanske är alla ovanstående metoder lite svåra för dig, här kommer jag att introducera ett kraftfullt verktyg, Kutools för Excel, med dess Gör upp ett nummer funktionen kan du snabbt få alla kombinationer som motsvarar en viss summa.

När du har installerat Kutools för Excel, gör så här:

1. Klicka Kutools > Innehåll > Gör upp ett nummer, se skärmdump:

2. Då, i Gör upp ett nummer Klicka i dialogrutan för att välja den nummerlista som du vill använda från Datakällaoch ange sedan det totala antalet i Sum textruta, se skärmdump:

3. Och klicka sedan på OK -knappen kommer en snabbruta att dyka upp för att påminna dig om att välja en cell för att hitta resultatet, se skärmdump:

4. Klicka sedan på OK, och nu har alla kombinationer som motsvarar det angivna numret visats enligt nedanstående skärmdump:

Klicka för att ladda ner Kutools för Excel och gratis testversion nu!

Demo: Hitta cellkombination som motsvarar en viss summa i Excel

---

De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

• återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
• Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
• Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
• Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
• Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
• Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
• Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
• Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
• Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2021 och 365. Stöder alla språk. Enkel implementering i ditt företag eller organisation. Fullständiga funktioner 30 dagars gratis provperiod. 60 dagars pengarna tillbaka-garanti.

Läs mer... Gratis nedladdning... Köp... 

---

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

• Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
• Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
• Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!

Läs mer... Gratis nedladdning... Köp...