İpucu: Digər dillər Google-tərcümə olunur. Sizi ziyarət edə bilərsiniz English bu linkin versiyası.
Daxil ol
x
or
x
x
Qeydiyyatdan
x

or

Excel-də müəyyən bir məbləğə bərabər olan bütün kombinasiyaların necə tapılacaq?

Məsələn, aşağıdakı nömrələr siyahısına sahibəm və indi siyahıda göstərilən nömrələrin hansı birləşməsi 480-a yekunlaşdığını bilmək istərdim, aşağıdakı ekran şəklində göstərildiyinizi görə bilərsiniz ki, bərabər əlavə edə bilən mümkün birləşmələrin beş qrupu var 480 + 300 + 60, 120 + 300 + 60 + 40 kimi 80-ə və s. Bu məqalədə, hansı hüceyrələrin Excel-də xüsusi bir dəyəri toplamasını tapmaq üçün bəzi üsullardan danışacağam.

doc birləşmənin bərabər məbləği 1 tapın

Dəyərli bir məbləğə bərabər olan hüceyrələr birləşməsini tapın

Solver Add-in ilə müəyyən bir məbləğə bərabər olan hüceyrələrin birləşməsini tapın

İstifadəçi Tanımlı funksiyası ilə müəyyən bir məbləğə bərabər olan hüceyrələrin birləşməsini tapın


Bir neçə sütundan bütün mümkün kombinasiyalar siyahısı:

Excel üçün Kutools'S Bütün Kombinasiyalar siyahısı yardım proqramı, dəyərlərin birdən çox siyahısına əsaslanan bütün mümkün birləşmələri tez bir zamanda yaratmaq / yaratmaqda kömək edə bilər.

doc birləşmənin bərabər məbləği 2 tapın

Excel üçün Kutools: 200 gündə heç bir məhdudiyyət olmadan 60-dən çox lazımlı Excel eklentisi ilə pulsuzdur. İndi indir və pulsuz sınaq!


Dəyərli bir məbləğə bərabər olan hüceyrələr birləşməsini tapın


Birincisi, bəzi sıra adları yaratmaq və hədəf dəyərinə bərabər olan hüceyrələri tapmaq üçün bir sıra formulunu tətbiq etmək lazımdır, aşağıdakı adımla edin:

1. Rəqəmlər siyahısını seçin və bu siyahı bir sıra adını təyin edin - Range1 daxil Ad qutusuvə basın Daxil edin müəyyən edilmiş sıra adını bitirmək üçün açar, ekran görünüşünə baxın:

doc birləşmənin bərabər məbləği 2 tapın

2. Nömrə siyahısına görə bir sıra adını təyin etdikdən sonra, daha iki aralığın adını yaratmalısınız Adı meneceri qutusuna basın Formüller > Adı meneceri, Bu Adı meneceri informasiya qutusuna basın yeni düyməsinə baxın, ekran görüntüsünə baxın:

doc birləşmənin bərabər məbləği 3 tapın

3. Çölə atıldı Yeni ad dialoq, ad daxil edin List1 daxil ad alan və bu formula yazın = ROW (INDIRECT ("1:" & ROWS (Range1)))) (Range1 step1 da yaratdığınız sıra adıdır) Müraciət edir sahə, ekran bax:

doc birləşmənin bərabər məbləği 4 tapın

4. Basın OK qayıtmaq Adı meneceri sonra dialoqa davam edin yeni başqa bir sıra adını yaratmaq üçün düyməni basın Yeni ad dialoq, ad daxil edin List2 daxil ad alan və bu formula yazın = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Range1 step1 da yaratdığınız sıra adıdır) Müraciət edir sahə, ekran bax:

doc birləşmənin bərabər məbləği 5 tapın

5. Aralığın adlarını yaratdıqdan sonra, aşağıdakı B1 hücresine aşağıdakı array formula tətbiq edin:

=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","")və basın Shift + Ctrl + Enter düymələri birləşdirərək sonra doldurma tutamağını B8 hücresinə, sonuncu nömrəsinə sürükləyin və ümumi sətrin 480 olduğu sütun B'de X olaraq qeyd olunan nömrələri görə bilərsiniz, ekran bax:

doc birləşmənin bərabər məbləği 6 tapın

Qeydlər:

1. Yuxarıdakı uzun formada: List1, List2Range1 əvvəlki addımlarda yaradılan sıra adlarıdır, C2 nömrələrə əlavə olunmasını istədiyiniz xüsusi dəyərdir.

2. Bir neçə dəyərin birləşməsi xüsusi dəyərə bərabər olan bir məbləğə malikdirsə, yalnız bir kombinasiya siyahıya alınır.


Solver Add-in ilə müəyyən bir məbləğə bərabər olan hüceyrələrin birləşməsini tapın

Əgər yuxarıda göstərilən metodu ilə qarışdırırsınızsa, Excel bir var Solver Add-in xüsusilə, bu əlavə-in istifadə edərək, ümumi məbləğ müəyyən bir dəyərə bərabər olan nömrələri də təyin edə bilərsiniz.

1. Birincisi, bunu aktivləşdirməlisiniz Solver əlavə etmək, xahiş edirəm fayl > Nizamlamalar, Bu Excel Seçimləri informasiya qutusuna basın Add-Ins sol bölmədən seçin və sonra basın Solver Add-in dən Inaktiv olmayan proqram əlavə et bölmədə bax:

doc birləşmənin bərabər məbləği 7 tapın

2. Sonra basın Go düyməsini daxil etmək üçün Add-Ins dialoq yoxlayın Solver Add-in seçim et və basın OK bu add-inini uğurla quraşdırmaq.

doc birləşmənin bərabər məbləği 8 tapın

3. Çözücü eklentisini etkinleştirdikten sonra, bu formülü B9 hücresine daxil etmeniz lazımdır: = SUMPRODUCT (B1: B8, A1: A8)(B1: B8 sayı siyahınızın yanında boş sütun hüceyrələri və A1: A8 istifadə etdiyiniz nömrələr siyahısı. ) və basın Daxil edin açar, bax ekran görüntüsü:

doc birləşmənin bərabər məbləği 9 tapın

4. Sonra basın Tarix > Solver ilə getmək Solver Parametrləri dialoq qutusunda, aşağıdakı əməliyyatları edin:

(1.) Tıklayın doc düyməsini 1 hüceyrəni seçmək üçün düyməni basın B9 burada olan formuladan Məqsəd qur bölmə;

(2.) Daha sonra Kənar bölümünü seçin Dəyər, və hədəf dəyərinizi daxil edin 480 ehtiyacınız olduğu halda;

(3.) Altında Dəyişən hüceyrələri dəyişdirərək bölməsinə basın doc düyməsini 1 hüceyrə aralığını seçmək üçün düyməsini basın B1: B8 müvafiq nömrələrinizi qeyd edəcəyik.

doc birləşmənin bərabər məbləği 10 tapın

5. Və sonra basın əlavə etmək düyməsini getmək Əlavə et informasiya qutusuna basın doc düyməsini 1 hüceyrə aralığını seçmək üçün düyməsini basın B1: B8seçin və seçin bin açılan siyahıdan bax, ekran görünüşünə baxın:

doc birləşmənin bərabər məbləği 11 tapın

6. Basın OK geri dönmək Solver Parametrləri dialoq, sonra basın Həll etmək düyməsini bir neçə dəqiqədən sonra a Solver Nəticələri informasiya qutusu çıxarılıb və müəyyən bir məbləğdə 480 ədədinə bərabər olan hüceyrələrin birləşməsini 1 kimi qeyd edə bilərsiniz. İçində Solver Nəticələri dialoq seçin Çözücü Çözümünü saxlayın seçim et və basın OK dialoqdan çıxmaq. Ekran görüntüsünə baxın:

doc birləşmənin bərabər məbləği 12 tapın

Qeyd: Bu metod birdən çox kombinasiya hüceyrəsini ala bilər, əgər birdən çox birləşmə dəyərləri müəyyən bir dəyərə bərabərdirsə.


Demo: Excel-də verilən məbləğə bərabər olan hüceyrələrin birləşməsini tapın


İstifadəçi Tanımlı funksiyası ilə müəyyən bir məbləğə bərabər olan hüceyrələrin birləşməsini tapın

İlk iki üsul bütün Excel istifadəçilərimizin çoxu üçün kompleksdir, burada, bu işi tez və asanlıqla həll etmək üçün VBA kodu yarada bilərəm.

Düzgün nəticəni almaq üçün ilk növbədə siyahıların siyahısını azaldılması lazımdır. Və sonra aşağıdakı addımları ilə:

1. Basıb saxlayın ALT + F11 düymələri açmaq üçün Proqramlar üçün Microsoft Visual Basic pəncərə.

2. Basın Taxmaq > Modules, və aşağıdakı kodu yapışdırın Modules Pəncərə.

VBA kodu: Verilən cəmi bərabər olan hüceyrələrin birləşməsini tapın:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice 20160506
    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. Sonra bu kod pəncərəsini saxlayıb bağlayın və sonra iş səhifəsinə geri qayıdın və bu formulu daxil edin = getcombination (A1: A8, C2) boş bir hüceyrəyə daxil edin və basın Daxil edin düyməsini basaraq, müəyyən bir məbləğə bərabər olan kombinasiya nömrələrini göstərən aşağıdakı nəticəni əldə edəcəksiniz:

doc birləşmənin bərabər məbləği 13 tapın

Qeydlər:

1. Yuxarıda göstərilən formada, A1: A8 sayı üçündür, və C2 bərabər olmaq istədiyiniz hədəf dəyərini ehtiva edir.

2. Bir neçə dəyərin birləşməsi xüsusi dəyərə bərabər olan bir məbləğə malikdirsə, yalnız bir kombinasiya siyahıya alınır.



Təklif olunan Məhsuldarlıq Vasitələri

Office Tab

qızıl star1 Chrome, Firefox və yeni Internet Explorer kimi Excel və digər Office proqramlarına lazımlı sekmeleri gətirin.

Excel üçün Kutools

qızıl star1 Amazing! 5 dəqiqədə məhsuldarlığınızı artırın. Heç bir xüsusi bacarıqa ehtiyac yoxdur, hər gün iki saat saxlaya bilərsiniz!

qızıl star1 300 Excel üçün yeni funksiyalar, Excel çox asan və güclü olun:

  • Veriyi ləğv etmədən Cell / Satır / Sütunları birləşdirin.
  • Bir neçə Çarşaf və İş Kitabını birləşdirin və birləşdirin.
  • Çaprazları müqayisə edin, birdən çox aralığa kopyalayın, mətni tarixə, vahidə və valyuta çevrilməsinə çevirin.
  • Rənglər, Paging Subtotals, Advanced Sort və Super Filter tərəfindən sayılan,
  • Daha çox seçin / Insert / Sil / Mətn / Biçim / Bağlantı / Yorum / Çalışma kitabları / Çalışma Qurğuları Tools ...

Excel üçün Kutools-un ekranı

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 12 days ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 1 months ago
    Thank you so much for the VBA coding, it has solved a major headache trying to find combinations to equal an exact amount.
  • To post as a guest, your comment is unpublished.
    Stephanie · 2 months ago
    I have 1162 cells to find number x. Excel tells me that is too many variable cells. Very small data set! Any suggestions? Thanks!
  • To post as a guest, your comment is unpublished.
    a · 5 months ago
    Will the solver add-in not work if there are negative numbers in the list or if the value of number is 0? I'm trying to find a sum of numbers in a list that equate to zero with some numbers being negative and positive, but the solver does not work. I changed a couple numbers on my list to test to make sure I followed the steps correctly and it did work for the test. Please advise if there is a way to solve with negative and positive numbers to find a 0 value.
  • To post as a guest, your comment is unpublished.
    Fattir · 9 months ago
    Hello,
    Thanks this is very good,
    How can find the most approximate combinations if there is no exact value.
    Many thanks