Note: The other languages of the website are Google-translated. Back to English

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

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:

  • Anmärkningar:
  • I ovanstående långa formel: List1, List2 och Område1 är intervallnamnen du har skapat i tidigare steg, C2 är det specifika värde som du vill att siffrorna ska läggas till.
  • Om mer än en kombination av värden har en summa som är lika med det specifika värdet listas bara en kombination.

Hitta och lista alla kombinationer som motsvarar en viss summa snabbt och enkelt i Excel

Kutools för Excel's Gör upp ett nummer verktyget kan hjälpa dig att hitta och lista alla kombinationer och specifika kombinationer som motsvarar ett givet summanummer snabbt och enkelt. Klicka för att ladda ner Kutools för Excel!

Kutools för Excel: med mer än praktiska Excel-tillägg, gratis att prova utan begränsning på dagar. Ladda ner och testa gratis nu!


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:

Anmärkningar: Denna metod är också bara kan få en kombination celler om det finns mer än en kombination av värden har en summa lika med det specifika värdet.


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:

  • Anmärkningar:
  • I ovanstående formel, A2: A9 är nummerintervallet, och C2 innehåller det målvärde du vill vara lika med.
  • Om mer än en kombination av värden har en summa som är lika med det specifika värdet listas bara en kombination.

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.

Tips:Att tillämpa detta Gör upp ett nummer funktionen, först bör du ladda ner Kutools för Excel, och använd sedan funktionen snabbt och enkelt.

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.
kte-flik 201905

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!
officetab botten
Sortera kommentarer efter
Kommentarer (47)
Inga betyg än. Bli först med att betygsätta!
Denna kommentar minimerades av moderatoren på webbplatsen
Finns det något sätt att utöka utbudet så att det innehåller fler än 8 nummer? Dessutom är jag inte säker på hur den här funktionen fungerar: "=RAD(INDIREKT("1:"&2^RADER(Range1)))". Om jag försöker utöka "Range1" bortom 15 rader får jag ett #Ref-fel. Det fungerar utmärkt med bara de 8 siffrorna, men tänk om du vill inkludera, säg, 50 nummer eller till och med 100.
Denna kommentar minimerades av moderatoren på webbplatsen
Samma fråga
Denna kommentar minimerades av moderatoren på webbplatsen
Fantastisk man!!! Fantastisk man!!!
Denna kommentar minimerades av moderatoren på webbplatsen
Finns det något sätt att utöka intervallet som Thom säger, till att säga upp till 50 siffror, men att bara lägga ihop sex av siffrorna utanför intervallet som summerar till den angivna summan? För närvarande kommer det att tillhandahålla alla kombinationer som totalt uppgår till den angivna summan. tack
Denna kommentar minimerades av moderatoren på webbplatsen
Grymt bra. Kunde inte få den stora formeln att fungera men lösartillägget fungerade perfekt. Sparade mig så mycket arbete.
Denna kommentar minimerades av moderatoren på webbplatsen
men det tar för mycket tid
Denna kommentar minimerades av moderatoren på webbplatsen
Jag är i bästa fall en avancerad nybörjare på Excel. Jag provade allt och det fungerade inte. Vad kan jag göra för fel?
Denna kommentar minimerades av moderatoren på webbplatsen
hur om jag behöver mer än en kombination? tack
Denna kommentar minimerades av moderatoren på webbplatsen
Hej. Formelversionen fungerade inte heller för mig. Det känns som att det saknas ett steg. Jag ser inte var numret som anges i cell C2 kommer in i formeln.

Tack
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Dori,


Det finns ingen formel i C2, det är bara det specifika värdet som du vill lägga till tal.
Denna kommentar minimerades av moderatoren på webbplatsen
HEJ, jag laddade ner Kutools men kan inte få det att hitta alla kombinationer mindre än en specificerad summa.
Denna kommentar minimerades av moderatoren på webbplatsen
Jag kunde få exemplet med Range1 att fungera med mitt intervall i 12 rader, men när jag ändrade intervallet till 42 rader fungerade det inte. Jag startade till och med om hela processen med versionen med 42 rader och det fungerade inte heller. Några idéer?
Denna kommentar minimerades av moderatoren på webbplatsen
Lysande!!!
Denna kommentar minimerades av moderatoren på webbplatsen
Kan du ladda upp excel?
Denna kommentar minimerades av moderatoren på webbplatsen
Makrot fungerade inte om det finns mer än en lösning.
Dessutom fungerade jag inte om jag hittade "0"
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Lorena,
Innan du använder ovanstående VBA-kod måste du först sortera nummerlistan i fallande ordning.
För det andra fungerar koden inte korrekt för att få det totala antalet 0.
Hoppas det kan hjälpa dig, tack!
Denna kommentar minimerades av moderatoren på webbplatsen
Jag försöker bestämma den bästa blandningen av produkten och är osäker på om detta är det bästa sättet att göra det. Som mest använder jag tre produkter i en blandning med 5 specifikationer var. Alla specifikationer är linjära och kan beräknas i medeltal när de blandas. En blandning är vanligtvis 45,000 30,000 lbs och varje batch är 15 30 lbs. För det mesta är våra blandningar 2000k+XNUMXk men jag skulle vilja kunna beräkna de ovanliga blandningarna genom att använda stegen ända ner till XNUMXlbs.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej,

Mitt problem med den här formeln är att den ger mig ett värde tillräckligt många gånger för att få målvärdet.
I listan över olika värden finns det några värden som är lika med varandra.

Jag har t.ex. 0,16 för 3 gånger (de första värdena i listan) och formeln ger mig svaret att mitt målvärde är 593 av 0,16.

Varför kombinerar den inte olika värden för att få mitt målvärde? Den väljer bara ett värde och anger hur många gånger det ska vara målvärdet.

Någon hjälp eller idé?


Tack!
Denna kommentar minimerades av moderatoren på webbplatsen
vet någon om detta fungerar på google sheets
Denna kommentar minimerades av moderatoren på webbplatsen
Ja, det finns en tillägg som liknar excels lösare som heter "solver"
Denna kommentar minimerades av moderatoren på webbplatsen
Skulle någon veta hur man justerar VBA Getcombination-funktionen så att ingen upprepning ska tillåtas?

Till exempel, för nummer 1,2,3,4,5,13 om 14 ska uppnås är 1,13 en lösning och inte 14 av 1.
Denna kommentar minimerades av moderatoren på webbplatsen
Funktion GetCombination(CoinsRange As Range, SumCellId As Double) Som String
'uppdatering av Extendoffice 20160506
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
För varje xCell I CoinsRange
Om inte (xSum / xCell < 1) Då
xStr = xStr & "1 av " & xCell & " "
xSum = xSum - xCell
End If
Nästa
GetCombination = xStr
Änden Funktion
Denna kommentar minimerades av moderatoren på webbplatsen
hej det ger mig ett tvetydigt namnfel för vba-koden
någon hjälp för jag vet ingenting i VBA
Denna kommentar minimerades av moderatoren på webbplatsen
Hej Ram, detta fungerar bra men ger inte den faktiska summan.
EX: om jag har 23,34,25,28,10,17&12 och jag har summan 80 (vilket är summan av 23,28,17&12), behöver jag en vba-kod som kan hitta denna kombination (summan av 23,28,17 ,12&XNUMX) Kan du hjälpa mig med detta?
Denna kommentar minimerades av moderatoren på webbplatsen
Hallå,
Stort tack för informationen;
Hur kan man hitta de mest ungefärliga kombinationerna om det inte finns något exakt värde.
Många tack,
Denna kommentar minimerades av moderatoren på webbplatsen
Hallå,
Tack det här är väldigt bra,
Hur kan man hitta de mest ungefärliga kombinationerna om det inte finns något exakt värde.
Tack
Denna kommentar minimerades av moderatoren på webbplatsen
Fungerar inte solver-tillägget om det finns negativa tal i listan eller om värdet på nummer är 0? Jag försöker hitta en summa av tal i en lista som är lika med noll med några tal som är negativa och positiva, men lösaren fungerar inte. Jag ändrade ett par nummer på min lista för att testa för att se till att jag följde stegen korrekt och att det fungerade för testet. Vänligen meddela om det finns ett sätt att lösa med negativa och positiva tal för att hitta ett 0-värde.
L     a
Denna kommentar minimerades av moderatoren på webbplatsen
fick du något svar eller hittade du ett sätt att göra detta?
Denna kommentar minimerades av moderatoren på webbplatsen
Hallå,
Om det finns både positiva och negativa siffror i kolumnen rekommenderar jag att du tillämpar Kutools för Excels Make up a number-funktion, det kan lösa ditt problem snabbt och enkelt.

Du kan ladda ner Kutools för Excel och gratis provperiod 60 dagar. Snälla försök!
Denna kommentar minimerades av moderatoren på webbplatsen
Jag har 1162 celler för att hitta nummer x. Excel säger mig att det är för många variabla celler. Mycket liten datamängd! Några förslag? Tack!
Det finns inga kommentarer här ännu
Ladda fler
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser

Följ oss

Copyright © 2009 - www.extendoffice.com. | Alla rättigheter förbehållna. Drivs av ExtendOffice. | | Sitemap
Microsoft och Office-logotypen är varumärken eller registrerade varumärken som tillhör Microsoft Corporation i USA och / eller andra länder.
Skyddad av Sectigo SSL