메인 컨텐츠로 가기

Excel에서 주어진 합계와 동일한 모든 조합을 쉽게 찾을 수 있습니다.

작가: 실루비아 최종 수정 날짜: 2020-08-24

Excel 용 Kutools

300+로 Excel 향상
강력한 기능

숫자 목록 (정수, 십진수 또는 음수)이 있다고 가정하고 이제 주어진 합계와 동일한 이러한 숫자에서 가능한 모든 조합을 찾으려고합니다. 일반적으로 공식, Solver Add-in 또는 사용자 정의 함수를 사용하여이를 달성 할 수 있지만 시간이 많이 걸리고 처리하기 쉽지 않습니다. 여기에 유용한 번호 만들기 ~의 유용성 Excel 용 Kutools, 주어진 합계와 동일한 모든 조합을 찾는 것이 Excel에서 그렇게 쉬운 적이 없었습니다.

주어진 합계와 같은 모든 조합 찾기


주어진 합계와 같은 모든 조합 찾기

아래 스크린 샷과 같이 B3 : B11 범위에서 합이 520까지 가능한 모든 숫자 조합을 찾으려면 다음과 같이 Make Up a Number 유틸리티를 사용해 볼 수 있습니다.

1. 클릭 쿠툴 > 내용 > 번호 만들기.

샷 메이크업 번호 03

2. 에서 번호 만들기 대화 상자에서 아래 설정을 수행하십시오.

  • . 데이터 소스 상자에서 숫자 목록을 선택하여 520까지 더한 숫자를 찾습니다.
  • . 옵션 섹션에 520을 입력하십시오. 합계 텍스트 상자;
  • 클릭 OK 단추. 스크린 샷보기 :

3. 다음 번호 만들기 대화 상자에서 조합을 출력 할 셀을 선택하고 OK 단추. 그 다음에 Excel 용 Kutools 생성 될 조합 수를 알려주는 대화 상자가 나타나면 OK.

다음과 같이 합계가 520 인 선택된 숫자의 가능한 모든 조합을 볼 수 있습니다.

노트:

1. 모든 조합을 생성하는 대신 각 조합의 조합 수와 요소 수를 지정하는 것은 선택 사항입니다. 아래 스크린 샷과 같이 조합 수를 4로 지정하고 각 조합의 요소 수를 1 ~ 4로 지정하면 다음과 같은 결과를 얻을 수 있습니다.

결과 :

2.이 강력한 기능은 십진수와 음수에도 적용 할 수 있습니다. 스크린 샷 참조 :

아래 스크린 샷과 같은 결과가 표시됩니다.

3. 당신은 확인할 수 있습니다 새 시트에 저장 상자를 사용하여 조합을 새 시트로 출력합니다.


주어진 합계와 같은 모든 조합 찾기

Excel 용 Kutools 300 개 이상의 편리한 Excel 도구가 포함되어 있습니다. 30 일 동안 제한없이 무료로 사용해보세요. 지금 무료 평가판을 다운로드하십시오!


생산성 도구 권장
다음 도구를 사용하면 시간과 비용을 크게 절약 할 수 있습니다. 적합한 도구는 무엇입니까?
사무실 탭: Office에서 편리한 탭 사용, Chrome, Firefox 및 New Internet Explorer의 방법입니다.
Excel 용 Kutools: 300 개 이상의 Excel 용 고급 기능 2021, 2019, 2016, 2013, 2010, 2007 및 Office 365.

Excel 용 Kutools

위에서 설명한 기능은 Excel 용 Kutools의 300 가지 강력한 기능 중 하나 일뿐입니다.

Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 및 Office 365용으로 설계되었습니다. 30일 동안 무료로 다운로드하여 사용할 수 있습니다.

Excel 용 Kutools 스크린 샷

btn 더 읽어보기      btn 다운로드     btn 구매

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.
This comment was minimized by the moderator on the site
This is so great, Thanks
This comment was minimized by the moderator on the site
Same here. The function gets hung-up when searing more than 1000 numbers. I do see a progress bar, but it continues to get stuck at 6%. I have been using their products for close to a decade; it would be nice if they fixed/improved this. I was told to install latest version. I wouldn't know how to install anything other than the latest version.
This comment was minimized by the moderator on the site
Hi Tony,
Sorry for the convenience and thanks for your support for close to a decade.
Our developers are working on upgrading this feature to have a better and fast way to deal with big data, and it will be avaliable in the next release.
By the way, do the 1000+ numbers you used include positive numbers, negative numbers and decimal numbers? Do you mind send us the sample file for testing via ?
This comment was minimized by the moderator on the site
Anyone able to get this to work? The Processing please wait is taking forever.. Don't want to cancel

This comment was minimized by the moderator on the site
I'm having this problem! Did you get it to work?
This comment was minimized by the moderator on the site
instead of "processing, please wait" it needs to have a counter which displays either how much time is estimated to be remaining to calculate or an actual counter of the calculations done and remaining to be counted. ie i have waited over 4 hours for the program to work out my 44C8 query (177m combinations) but i don't know whether the program has crashed or is still calculating.
This comment was minimized by the moderator on the site
Still going after another 6 hours (10hrs all up). i assume it has crashed and will force close the program.
This comment was minimized by the moderator on the site
ok just tried a 35C8 and it took just over 90mins, so maybe 44C8 takes 11.5hrs.
This comment was minimized by the moderator on the site
È possibile escludere delle combinazioni da quelle trovate ?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations