Hoe vind ik alle combinaties die gelijk zijn aan een bepaalde som in Excel?

Ik heb bijvoorbeeld de volgende lijst met getallen, en nu wil ik weten welke combinatie van getallen in de lijst 480 is, in de volgende schermafbeelding zie je dat er vijf groepen mogelijke combinaties zijn die gelijk zijn tot 480, zoals 300 + 60 + 120, 300 + 60 + 40 + 80, enz. In dit artikel zal ik het hebben over enkele methoden om te achterhalen welke cellen een bepaalde waarde in Excel opleveren.

• Zoek een celcombinatie die gelijk is aan een bepaalde som met formules
• Zoek een celcombinatie die gelijk is aan een gegeven som met de Oplosser-invoegtoepassing
• Zoek een celcombinatie die gelijk is aan een bepaalde som met de door de gebruiker gedefinieerde functie
• Vind alle combinaties die gelijk zijn aan een bepaalde som met een geweldige functie

Zoek een celcombinatie die gelijk is aan een bepaalde som met formules

Eerst moet u een aantal bereiknamen maken en vervolgens een matrixformule toepassen om de cellen te vinden die optellen bij de doelwaarde. Voer de volgende stap voor stap uit:

1. Selecteer de nummerlijst en definieer voor deze lijst een bereiknaam-- Bereik 1 in de Naam Boxen druk op Enter toets om de gedefinieerde bereiknaam te voltooien, zie screenshot:

2. Nadat u een bereiknaam voor de nummerlijst hebt gedefinieerd, moet u nog twee bereiknamen maken in de Name Manager box, klik dan Formules > Name Manager, in de Name Manager dialoogvenster, klik Nieuw knop, zie screenshots:

3. In de pop-out Nieuwe naam voer een naam in List1 in de Naam veld en typ deze formule = RIJ (INDIRECT ("1:" & RIJEN (Bereik1))) (Bereik 1 is de bereiknaam die u in stap1) hebt gemaakt in de Verwijst naar veld, zie screenshot:

4. Klikken OK terugkeren naar de Name Manager dialoogvenster en blijf klikken Nieuw om een ​​andere bereiknaam te maken, in de Nieuwe naam voer een naam in List2 in de Naam veld en typ deze formule = RIJ (INDIRECT ("1:" & 2 ^ RIJEN (Bereik1))) (Bereik 1 is de bereiknaam die u in stap1) hebt gemaakt in de Verwijst naar veld, zie screenshot:

5. Pas na het maken van de bereiknamen de volgende matrixformule toe in cel B1:

=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","")en druk op Shift + Ctrl + Enter toetsen samen, sleep vervolgens de vulgreep naar cel B8, het laatste nummer van de lijst, en je kunt zien dat de nummers waarvan het totale aantal 480 is, zijn gemarkeerd als X in kolom B, zie screenshot:

Zoek een celcombinatie die gelijk is aan een gegeven som met de Oplosser-invoegtoepassing

Als u verward bent met de bovenstaande methode, bevat Excel een Oplosser-invoegtoepassing functie, door deze add-in te gebruiken, kunt u ook de getallen identificeren waarvan het totale bedrag gelijk is aan een bepaalde waarde.

1. U moet dit eerst activeren Oplosser add-in, Ga aub naar Dien in > Opties, in de Excel-opties dialoogvenster, klik Add-Ins in het linkerdeelvenster en klik vervolgens op Oplosser-invoegtoepassing van de Inactieve invoegtoepassingen voor apps sectie, zie screenshot:

2. Dan klikken Go om het te openen Add-Ins dialoogvenster, vink aan Oplosser-invoegtoepassing optie en klik op OK om deze invoegtoepassing met succes te installeren.

3. Nadat u de invoegtoepassing Oplosser hebt geactiveerd, moet u deze formule invoeren in cel B9: = SOMPRODUCT (B2: B9; A2: A9)(B2: B9 is een lege kolomcel naast uw nummerlijst, en A2: A9 is de nummerlijst die u gebruikt. ) en druk op Enter key, zie screenshot:

4. Dan klikken Data > Oplosser naar de Oplosserparameter dialoogvenster, voer in het dialoogvenster de volgende bewerkingen uit:

(1.) Klik  knop om de cel te selecteren B10 waar uw formule uit de Stel een doelstelling in sectie;

(2.) Vervolgens in de Naar sectie, selecteer Waarde vanen voer uw streefwaarde in 480 zoals je nodig hebt;

(3.) Onder de Door variabele cellen te veranderen sectie, klik dan om het celbereik te selecteren B2: B9 waar zal uw overeenkomstige nummers markeren.

5. En klik dan Toevoegen knop om naar de Beperking toevoegen dialoogvenster, klik om het celbereik te selecteren B2: B9En Select bak zie screenshot uit de vervolgkeuzelijst:

6. Klikken OK om terug te gaan naar het Oplosserparameter dialoogvenster en klik op Oplossen knop, enkele minuten later, een Oplosser resultaten dialoogvenster verschijnt en u kunt zien dat de combinatie van cellen die gelijk zijn aan een gegeven som 480 zijn gemarkeerd als 1. In het Oplosser resultaten dialoogvenster, selecteer alstublieft Houd Oplosser-oplossing optie en klik op OK om het dialoogvenster te verlaten. Zie screenshot:

Zoek een celcombinatie die gelijk is aan een bepaalde som met de door de gebruiker gedefinieerde functie

De eerste twee methoden zijn allemaal complex voor de meeste van onze Excel-gebruikers, hier kan ik een VBA-code maken om deze taak snel en gemakkelijk op te lossen.

Om het juiste resultaat te krijgen, moet u de nummerlijst eerst in aflopende volgorde sorteren. En doe dan met de volgende stappen:

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: zoek een celcombinatie die gelijk is aan een bepaalde som:

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. Sla vervolgens dit codevenster op en sluit het, ga terug naar het werkblad en voer deze formule in = getcombination (A2: A9, C2) in een lege cel en druk op Enter key, krijgt u het volgende resultaat dat de combinatienummers weergeeft die gelijk zijn aan een bepaalde som, zie screenshot:

Vind alle combinaties die gelijk zijn aan een bepaalde som met een geweldige functie

Misschien zijn alle bovenstaande methoden enigszins moeilijk voor u, hier zal ik een krachtig hulpmiddel introduceren, Kutools for Excel, Met Verzin een nummer functie, kunt u snel alle combinaties krijgen die gelijk zijn aan een bepaalde som.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Klikken Kutools > Content > Verzin een nummer, zie screenshot:

2. Vervolgens in de Verzin een nummer dialoogvenster, klik dan om de nummerlijst die u wilt gebruiken te selecteren uit de Databronen voer vervolgens het totale aantal in het Som textbox, zie screenshot:

3. En klik dan OK knop, verschijnt er een promptvenster om u eraan te herinneren een cel te selecteren om het resultaat te vinden, zie screenshot:

4. Klik vervolgens op OK, en nu zijn alle combinaties die gelijk zijn aan dat gegeven nummer weergegeven zoals onderstaand screenshot getoond:

Klik om Kutools voor Excel en nu gratis uit te proberen!

Demo: zoek een celcombinatie die gelijk is aan een bepaalde som in Excel

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

• visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
• Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
• Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
• Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
• Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
• Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
• Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
• Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
• Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.

Lees verder... Gratis download... Aankoop... 

---

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

• Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
• Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
• Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!

Lees verder... Gratis download... Aankoop...