Preskoči na glavno vsebino

V Excelu enostavno poiščite vse kombinacije, ki ustrezajo dani vsoti

Avtor: Siluvia Zadnja sprememba: 2020-08-24

Kutools za Excel

Izboljša Excel s 300+
Zmogljive lastnosti

Recimo, da obstaja seznam števil (cela števila, decimalna števila ali negativna števila), zdaj pa želite poiskati vse možne kombinacije teh števil, ki ustrezajo dani vsoti. Običajno lahko za doseganje uporabite formule, dodatek za rešitve ali uporabniško določeno funkcijo, vendar je to dolgotrajno in enostavno upravljanje. Tukaj s koristnim Sestavite si številko uporabnost Kutools za Excel, iskanje vseh kombinacij, ki ustrezajo dani vsoti, v Excelu še nikoli ni bilo tako enostavno.

Poiščite vse kombinacije, ki ustrezajo dani vsoti


Poiščite vse kombinacije, ki ustrezajo dani vsoti

Kot je prikazano na spodnji sliki zaslona, ​​lahko poiščete vse možne kombinacije številk v območju B3: B11, ki seštejejo do 520, s pripomočkom Make Up a Number, kot sledi.

1. klik Kutools > vsebina > Sestavite si številko.

strel sestavljajo številko 03

2. V Ljubljani Sestavite številko pogovorno okno, naredite spodnje nastavitve.

  • v Vir podatkov izberite seznam številk, da poiščete, katere številke seštevajo do 520;
  • v možnosti v razdelek vnesite 520 Seštevek besedilno polje;
  • Kliknite OK . Oglejte si posnetek zaslona:

3. V naslednjem Sestavite številko v pogovornem oknu izberite celico za izpis kombinacij in kliknite OK . Potem a Kutools za Excel Pojavi se pogovorno okno, ki vam pove, koliko kombinacij bo ustvarjenih, kliknite OK.

Ogledate si lahko vse možne kombinacije izbranih številk, ki seštejejo do 520, so navedene na naslednji način.

Opombe:

1. Neobvezno je določiti število kombinacij in število elementov v vsaki kombinaciji, namesto da bi ustvarili vse kombinacije. Kot je prikazano na spodnjem posnetku zaslona, ​​lahko določite število kombinacij kot 4 in določite število elementov v vsaki kombinaciji od 1 do 4, nato pa boste dobili naslednji rezultat:

Rezultat:

2. Ta zmogljiva funkcija se lahko uporablja tudi za decimalna in negativna števila, glejte posnetek zaslona:

In dobili boste rezultat, kot je prikazano na spodnji sliki zaslona:

3. Lahko preverite Shrani v nov list polje za izpis kombinacij na nov list.


Poiščite vse kombinacije, ki ustrezajo dani vsoti

Kutools za Excel vključuje več kot 300 priročnih orodij Excel. Brezplačno poskusite brez omejitev v 30 dneh. Prenesite brezplačno preskusno različico zdaj!


Priporočena orodja za produktivnost
Naslednja orodja vam lahko v veliki meri prihranijo čas in denar, katero je najbolj primerno za vas?
Zavihek Office: Uporaba priročnih zavihkov v pisarni, kot način Chrome, Firefox in New Internet Explorer.
Kutools za Excel: Več kot 300 naprednih funkcij za Excel 2021, 2019, 2016, 2013, 2010, 2007 in Office 365.

Kutools za Excel

Zgoraj opisana funkcionalnost je le ena izmed 300 zmogljivih funkcij Kutools za Excel.

Zasnovan za Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 in Office 365. Brezplačen prenos in uporaba 30 dni.

Posnetek zaslona Kutools za Excel

btn preberi več      btn prenos     btn nakup

Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is it possible to consider repetition of the same number as well?
For example:
numbers: 10 - 20 - 40
Given number: 90

Combination: 40 - 40 - 10

Thank you!
This comment was minimized by the moderator on the site
Hi Giuseppe,

Sorry for the inconvenience. If your original data doesn't contain any duplicates, then duplicate values cannot be generated from it. Creating duplicate values for the same number would result in numerous combinations, which is generally not advisable due to complexity.
This comment was minimized by the moderator on the site
I do not know about others, but I still have it.
This comment was minimized by the moderator on the site
There is no "Make up a number" under Content in my Kutools, has this feature been removed?
This comment was minimized by the moderator on the site
Hi Alex Ramaker,
I am sorry for the inconvenience. Can you tell me which Kutools version are you using? If you can't find it under the Content group, please press the Alt + S keys to open the Kutools search box and type in "Make up a number", and then the feature name will be listed under the search box. Just click on it to activate the feature.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/make_up_a_number.png
This comment was minimized by the moderator on the site
I am also missing features in the Content section including Make Up a Number and Data Association and using Alt + S does not give me an option to search functions. Version is 19.0. Kutools was downloaded to my work machine through my organization's catalog, is it possible certain features are blocked?
This comment was minimized by the moderator on the site
Hi Sylvia,

The Make Up a Number feature was released in version 23.00 and the Data Association feature was released in version 25.00.
You are using an old version of Kutools and It’s the lifetime license with the 2-year free update support.
After the 2-year free update support, you cannot update to any new versions but you can still use the old versions of the software.
After the 2-year free update support, you will need to purchase a new version to use the features in the new version.
This comment was minimized by the moderator on the site
The bug has not been fixed yet. Even though I select one output, and wait forever for the answer, I still get an error message stating that the tool can't handle more than 1,600+ answers. I did not ask for that many, I asked for *one*.
This comment was minimized by the moderator on the site
Hi Cathy,
Sorry for the convenience and thanks for your support.
Our developers are working on upgrading this feature to have a better and fast way to deal with big data. And the new version will be released soon.
Please stay tuned for our updates!
This comment was minimized by the moderator on the site
Was the bug fixed? What was great? Thank you.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations