Cum să găsiți toate combinațiile care egalează o anumită sumă în Excel?

De exemplu, am următoarea listă de numere și acum vreau să știu ce combinație de numere din listă însumează 480, în următoarea captură de ecran afișată, puteți vedea că există cinci grupuri de combinații posibile care se adună la fel până la 480, cum ar fi 300 + 60 + 120, 300 + 60 + 40 + 80 etc. Acest articol, voi vorbi despre câteva metode pentru a găsi ce celule însumează o anumită valoare în Excel.

• Găsiți o combinație de celule care egalează o sumă dată cu formule
• Găsiți o combinație de celule care egalează o sumă dată cu Solver Add-in
• Găsiți o combinație de celule care egalează o sumă dată cu funcția definită de utilizator
• Găsiți toate combinațiile care egalează o sumă dată cu o caracteristică uimitoare

Găsiți o combinație de celule care egalează o sumă dată cu formule

În primul rând, trebuie să creați câteva nume de intervale și apoi să aplicați o formulă matrice pentru a găsi celulele care se sumează la valoarea țintă, vă rugăm să faceți cu următorul pas cu pas:

1. Selectați lista numerelor și definiți această listă un nume de gamă - Gama 1 în Cutie de nume, și apăsați Intrați tasta pentru a termina numele domeniului definit, vezi captura de ecran:

2. După ce ați definit un nume de interval pentru lista numerelor, trebuie să creați două nume de domeniu în Manager nume caseta, vă rugăm să faceți clic Formule > Manager nume, În Manager nume casetă de dialog, faceți clic pe Nou buton, vezi capturi de ecran:

3. În pop-out Nume nou caseta de dialog, introduceți un nume List1 în Nume si Prenume și introduceți această formulă = ROW (INDIRECT ("1:" & ROWS (Range1))) (Gama 1 este numele intervalului pe care l-ați creat la pasul 1) în Se refera la câmp, vezi captura de ecran:

4. Clic OK pentru a reveni la Manager nume , apoi continuați să faceți clic Nou pentru a crea un alt nume de gamă, în Nume nou caseta de dialog, introduceți un nume List2 în Nume si Prenume și introduceți această formulă = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Gama 1 este numele intervalului pe care l-ați creat la pasul 1) în Se refera la câmp, vezi captura de ecran:

5. După crearea numelor intervalului, vă rugăm să aplicați următoarea formulă matrice în celula 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",""), și apăsați Shift + Ctrl + Enter tastele împreună, apoi trageți mânerul de umplere în jos până la celula B8, ultimul număr al listei, și puteți vedea numerele cu suma totală de 480 sunt marcate ca X în coloana B, vedeți captura de ecran:

Găsiți o combinație de celule care egalează o sumă dată cu Solver Add-in

Dacă sunteți confuz cu metoda de mai sus, Excel conține un Supliment de rezolvare caracteristică, utilizând acest program de completare, puteți identifica, de asemenea, numerele cu care suma totală este egală cu o anumită valoare.

1. Mai întâi, trebuie să activați acest lucru Rezolvator adauga la, te rog du-te la Fișier > Opţiuni, În Opțiuni Excel casetă de dialog, faceți clic pe Adăugați ins din panoul din stânga, apoi faceți clic pe Supliment de rezolvare de la Suplimente pentru aplicații inactive secțiune, vezi captura de ecran:

2. Apoi apasa Go pentru a introduce butonul Adăugați ins dialog, verificați Supliment de rezolvare și faceți clic pe OK pentru a instala acest supliment cu succes.

3. După activarea programului de completare Solver, trebuie să introduceți această formulă în celula B9: = SUMPRODUCT (B2: B9, A2: A9), (B2: B9 este o coloană necompletată alături de lista numerelor dvs. și A2: A9 este lista de numere pe care o utilizați. ) și apăsați Intrați tasta, vezi captura de ecran:

4. Apoi faceți clic pe Date > Rezolvator a merge la Parametru Solver casetă de dialog, în dialog, vă rugăm să efectuați următoarele operații:

(1.) Faceți clic pe  pentru a selecta celula B10 unde formula dvs. intră din Setați obiectivul secțiune;

(2.) Apoi în La secțiune, selectați Valoareași introduceți valoarea țintă 480 după cum aveți nevoie;

(3.) Sub Prin schimbarea celulelor variabile secțiune, faceți clic pe butonul pentru a selecta intervalul de celule B2: B9 unde va marca numerele corespunzătoare.

5. Apoi faceți clic pe Adăuga buton pentru a merge la Adăugați Constraint casetă de dialog, faceți clic pe butonul pentru a selecta intervalul de celule B2: B9Și selectați bin din lista derulantă, vezi captura de ecran:

6. Clic OK a reveni la Parametru Solver , apoi faceți clic pe Rezolva , câteva minute mai târziu, a Rezultate Solver caseta de dialog este afișată și puteți vedea combinația de celule care sunt egale cu o sumă dată 480 sunt marcate ca 1. În Rezultate Solver dialog, vă rugăm să selectați Păstrați soluția Solver și faceți clic pe OK pentru a ieși din dialog. Vedeți captura de ecran:

Găsiți o combinație de celule care egalează o sumă dată cu funcția definită de utilizator

Primele două metode sunt complexe pentru majoritatea utilizatorilor noștri Excel, aici pot crea un cod VBA pentru a rezolva acest lucru rapid și ușor.

Pentru a obține rezultatul corect, trebuie să sortați mai întâi lista numerelor în ordine descrescătoare. Și apoi efectuați următorii pași:

1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Module Fereastră.

Cod VBA: Găsiți o combinație de celule care este egală cu o sumă dată:

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. Apoi salvați și închideți această fereastră de cod, apoi reveniți la foaia de lucru și introduceți această formulă = getcombination (A2: A9, C2) într-o celulă goală și apăsați Intrați tasta, veți obține următorul rezultat care afișează numerele de combinație care egalează o sumă dată, a se vedea captura de ecran:

Găsiți toate combinațiile care egalează o sumă dată cu o caracteristică uimitoare

Poate că toate metodele de mai sus sunt oarecum dificile pentru dvs., aici, voi introduce un instrument puternic, Kutools pentru Excel, Cu său Alcătuiește un număr caracteristică, puteți obține rapid toate combinațiile care sunt egale cu o sumă dată.

După instalare Kutools pentru Excel, vă rugăm să faceți acest lucru:

1. Clic Kutools > Conţinut > Alcătuiește un număr, vezi captura de ecran:

2. Apoi, în Alcătuiește un număr casetă de dialog, faceți clic pe pentru a selecta lista de numere pe care doriți să o utilizați din Sursă de date, apoi introduceți numărul total în Sumă casetă text, vezi captura de ecran:

3. Apoi, faceți clic OK , o casetă de prompt va apărea pentru a vă reaminti să selectați o celulă pentru a localiza rezultatul, vedeți captura de ecran:

4. Apoi, faceți clic pe OK, și acum, toate combinațiile care sunt egale cu numărul dat au fost afișate după cum se arată în imaginea de mai jos:

Faceți clic pentru a descărca Kutools pentru Excel și încercare gratuită acum!

Demo: găsiți o combinație de celule care egalează o sumă dată în Excel

---

Cele mai bune instrumente de productivitate Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

• reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
• Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
• Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
• Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
• Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
• Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
• Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
• Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
• Peste 300 de funcții puternice. Suportă Office / Excel 2007-2021 și 365. Acceptă toate limbile. Implementare ușoară în întreprinderea sau organizația dvs. Funcții complete Probă gratuită de 30 de zile. Garanție de returnare a banilor de 60 de zile.

Citeşte mai mult ... Descărcare gratuită... Cumpărare... 

---

Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

• Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
• Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
• Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!

Citeşte mai mult ... Descărcare gratuită... Cumpărare...