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

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

Î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:

  • note:
  • În formula lungă de mai sus: List1, List2 și Gama 1 sunt numele de gamă pe care le-ați creat în pașii anteriori, C2 este valoarea specifică la care doriți să se adauge numerele.
  • Dacă mai multe combinații de valori au o sumă egală cu valoarea specifică, este listată o singură combinație.

Găsiți și listați toate combinațiile care egalează o sumă dată rapid și ușor în Excel

Kutools pentru Excel's Alcătuiește un număr utilitarul vă poate ajuta să găsiți și să listați toate combinațiile și combinațiile specifice care sunt egale cu un anumit număr sumă rapid și ușor. Faceți clic pentru a descărca Kutools pentru Excel!

Kutools pentru Excel: cu suplimente Excel mai mult decât la îndemână, puteți încerca gratuit fără limitări în zile. Descărcați și proba gratuită acum!


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:

notițe: Această metodă este, de asemenea, poate obține o singură combinație de celule dacă există mai multe combinații de valori care au o sumă egală cu valoarea specifică.


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:

  • note:
  • În formula de mai sus, A2: A9 este intervalul numeric și C2 conține valoarea țintă cu care doriți să fie egală.
  • Dacă mai multe combinații de valori au o sumă egală cu valoarea specifică, este listată o singură combinație.

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ă.

Sfat:Pentru a aplica acest lucru Alcătuiește un număr caracteristică, în primul rând, ar trebui să descărcați Kutools pentru Excel, apoi aplicați caracteristica rapid și ușor.

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

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!
fundul officetab
Comentarii (47)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Există vreo modalitate de a extinde intervalul astfel încât să includă mai mult de 8 numere? De asemenea, nu sunt sigur cum funcționează această funcție: "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". Dacă încerc să extind „Range1” dincolo de 15 rânduri, primesc o eroare #Ref. Funcționează excelent doar cu cele 8 numere, dar dacă ați dori să includeți, să zicem, 50 de numere sau chiar 100.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Aceeași problemă
Acest comentariu a fost redus la minimum de moderatorul de pe site
Superb Om!!! Superb Om!!!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Există vreo modalitate de a extinde intervalul, așa cum spune Thom, de a spune până la 50 de numere, dar și de a totaliza doar șase numere din interval care însumează totalul specificat? În prezent, va furniza toate combinațiile care însumează totalul specificat. Mulțumiri
Acest comentariu a fost redus la minimum de moderatorul de pe site
Minunat. Nu am putut face ca formula mare să funcționeze, dar programul de completare pentru rezolvare a funcționat perfect. Mi-a scutit atât de multă muncă.
Acest comentariu a fost redus la minimum de moderatorul de pe site
dar este nevoie de prea mult timp
Acest comentariu a fost redus la minimum de moderatorul de pe site
Sunt în cel mai bun caz un începător avansat la Excel. Am incercat totul si nu a mers. Ce aș putea să fac greșit?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Cum dacă am nevoie de mai multe combinații? mulțumesc
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut. Nici varianta cu formula nu a mers la mine. Pare că lipsește un pas. Nu văd unde intră în formulă numărul specificat în celula C2.

mulțumesc
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna Dori,


Nu există nicio formulă în C2, este doar valoarea specifică cu care doriți să adăugați numerele.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, am descărcat Kutools, dar nu îl pot obține pentru a găsi toate combo-urile mai mici decât un total specificat.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Am reușit să fac ca exemplul cu Range1 să funcționeze cu intervalul meu în 12 rânduri, dar când am schimbat intervalul la 42 de rânduri, nu a funcționat. Am repornit chiar și întregul proces cu versiunea cu 42 de rânduri și nici asta nu a funcționat. Vreo idee?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Sclipitor!!!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Ai putea încărca excel-ul?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Macro-ul nu a funcționat dacă există mai multe soluții.
De asemenea, nu am funcționat dacă găsesc „0”
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Lorena,
Înainte de a aplica codul VBA de mai sus, trebuie să sortați mai întâi lista de numere în ordine descrescătoare.
În al doilea rând, codul nu funcționează corect pentru a obține numărul total 0.
Sper că te poate ajuta, mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Încerc să determin cel mai bun amestec de produse și nu sunt sigur dacă acesta este cel mai bun mod de a face acest lucru. Eu folosesc cel mult trei produse într-un amestec cu 5 specificații fiecare. Toate specificațiile sunt liniare și pot fi mediate atunci când sunt amestecate. Un amestec este de obicei de 45,000 lbs și fiecare lot este de 30,000 lbs. De cele mai multe ori amestecurile noastre sunt de 15k+30k, dar aș dori să pot calcula pentru amestecurile neobișnuite folosind incrementele până la 2000lbs.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,

Problema mea cu această formulă este că îmi oferă o singură valoare pentru suficient de multe ori pentru a obține valoarea țintă.
În lista de valori diferite există unele valori care sunt egale între ele.

De exemplu, am 0,16 de 3 ori (primele valori din listă) și formula îmi dă răspunsul că valoarea țintă este 593 din 0,16.

De ce nu combină valori diferite pentru a obține valoarea țintă? Alege doar o singură valoare și oferă de câte ori trebuie să fie valoarea țintă.

Ajutor sau idee?


Multumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
știe cineva dacă funcționează pe google sheets
Acest comentariu a fost redus la minimum de moderatorul de pe site
Da, există o extensie similară cu solutorul excel numită "solver"
Acest comentariu a fost redus la minimum de moderatorul de pe site
Ar ști cineva cum să ajusteze funcția VBA Getcombination astfel încât să nu fie permisă nicio repetiție?

De exemplu, pentru numerele 1,2,3,4,5,13 dacă se dorește obținerea 14, atunci 1,13 este o soluție și nu 14 din 1.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Funcția GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice 20160506
Dim xStr As String
Dim xSum ca dublu
Dim xCell As Range
xSum = SumCellId
Pentru fiecare xCell din CoinsRange
Dacă nu (xSum / xCell < 1) Atunci
xStr = xStr & "1 din " & xCell & " "
xSum = xSum - xCell
Final, dacă
Pagina Următoare →
GetCombination = xStr
Sfârşit Funcţia
Acest comentariu a fost redus la minimum de moderatorul de pe site
salut, îmi dă o eroare de nume ambioasă pentru codul vba
orice ajutor pentru că nu știu nimic în VBA
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună Ram, funcționează bine, dar nu oferă suma reală.
EX: dacă am 23,34,25,28,10,17&12 și am o sumă de 80 (care este suma de 23,28,17&12), am nevoie de un cod vba care poate găsi această combinație (suma de 23,28,17 ,12&XNUMX) Mă puteți ajuta vă rog cu asta?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna,
Multumesc mult pentru informatii;
Cum pot găsi cele mai aproximative combinații dacă nu există o valoare exactă.
Multe mulţumiri,
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna,
Mulțumesc, este foarte bun,
Cum pot găsi cele mai aproximative combinații dacă nu există o valoare exactă.
Mersi
Acest comentariu a fost redus la minimum de moderatorul de pe site
Nu va funcționa programul de completare pentru rezolvare dacă există numere negative în listă sau dacă valoarea numărului este 0? Încerc să găsesc o sumă de numere dintr-o listă care echivalează cu zero, unele numere fiind negative și pozitive, dar soluția nu funcționează. Am schimbat câteva numere din lista mea pentru a testa pentru a mă asigura că am urmat corect pașii și că a funcționat pentru test. Vă rugăm să spuneți dacă există o modalitate de a rezolva cu numere negative și pozitive pentru a găsi o valoare 0.
L     a
Acest comentariu a fost redus la minimum de moderatorul de pe site
ai primit vreodata un raspuns sau ai gasit o modalitate de a face asta?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna,
Dacă există atât numere pozitive, cât și negative în coloană, vă recomand să aplicați caracteristica Kutools for Excel Make up a number, vă poate rezolva problema rapid și ușor.

Puteți descărca Kutools pentru Excel și puteți încerca gratuit 60 de zile. Te rog incearca!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Am 1162 de celule pentru a găsi numărul x. Excel îmi spune că sunt prea multe celule variabile. Set de date foarte mic! Orice sugestii? Mulțumiri!
Nu există comentarii postate aici încă
Încărcați mai
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate

Urmărește-ne

Copyright © 2009 - www.extendoffice.com. | Toate drepturile rezervate. Cu sprijinul ExtendOffice. | Harta site-ului
Microsoft și sigla Office sunt mărci comerciale sau mărci comerciale înregistrate ale Microsoft Corporation în Statele Unite și / sau în alte țări.
Protejat de Sectigo SSL