Как найти все комбинации в эксель

Подсчитаем в MS EXCEL количество сочетаний из n элементов по k. С помощью формул выведем на лист все варианты сочетаний (английский перевод термина: Combinations without repetition).

Сочетаниями из n различных элементов по k элементов называются комбинации, которые отличаются хотя бы одним элементом. Например, ниже перечислены ВСЕ 3-х элементные сочетания, взятые из множества, состоящего из 5 элементов {1; 2; 3; 4; 5}:

(1; 2; 3); (1; 2; 4); (1; 2; 5); (1; 3; 4); (1; 3; 5); (1; 4; 5); (2; 3; 4); (2; 3; 5); (2; 4; 5); (3; 4; 5)

Примечание

: Это статья о подсчете количества сочетаний с использованием MS EXCEL. Теоретические основы советуем прочитать в специализированном учебнике. Изучать сочетания по этой статье – плохая идея.

Отличие Сочетаний от Размещений

В отличие от

Размещений

следующие 3-х элементные комбинации (1; 2; 3); (1; 3; 2); (2; 1; 3); (2; 1; 3); (3; 2; 1); (3; 1; 2) считаются одинаковыми, и в набор

Сочетаний

включается только одна из этих комбинаций. Очевидно, что для тех же n и k число

Сочетаний

всегда меньше чем число

Размещений

(так как при размещениях порядок важен, а для сочетаний – нет), причем в k! раз.

Подсчет количества Сочетаний

Число всех

Сочетаний

из n элементов по k можно вычислить по формуле:

Например, количество 4-х элементных комбинаций из 6 чисел {1; 2; 3; 4; 5; 6}  равно 15=6!/(4!(6-4)!)

Примечание

:  Для

Сочетаний

из n элементов по k также используется и другая запись:

В MS EXCEL для подсчета количества комбинаций без повторов существует специальная функция ЧИСЛКОМБ() , английское название функции – COMBIN(). Для предыдущего примера формула =ЧИСЛКОМБ(6;4) , разумеется, также вернет 15. Альтернативная формула для подсчета сочетаний =ФАКТР(6)/ФАКТР(6-4)/ФАКТР(4) .

Очевидно, что k меньше или равно n, т.к. нельзя выбрать из множества элементов n больше элементов, чем в нем содержится (предполагается, что элементы после выбора обратно не возвращаются). При k=n количество сочетаний всегда равно 1.

Примечание

: О Сочетаниях с повторениями (с возвращением элементов) можно прочитать в статье

Сочетания с повторениями: Комбинаторика в MS EXCEL

Вывод всех комбинаций Сочетаний

В файле примера созданы формулы для вывода всех Сочетаний для заданных n и k.

Задавая с помощью

элементов управления Счетчик

количество элементов множества (n) и количество элементов, которое мы из него выбираем (k), с помощью формул можно вывести все Сочетания.

В файле примера не забывайте увеличивать количество строк с формулами, чтобы поместились все ваши комбинации. Для этого выделите последние ячейки с формулами (сочетание №330) и скопируйте их вниз на нужно количество строк. При увеличении строк с формулами размер файла быстро растет, а скорости пересчета листа падает. Если строк 4 тысячи, то размер файла составляет около 2 Мб.

Задача

Автовоз может перевозить по 4 легковые машины. Необходимо перевезти 7 разных машин (LADA Granta, Hyundai Solaris, KIA Rio, Renault Duster, Lada Kalina, Volkswagen Polo, Lada Largus). Сколькими различными способами можно заполнить первый автовоз? Конкретное место машины в автовозе не важно.

Нам нужно определить число

Сочетаний

7 машин на 4-х местах автовоза. Т.е. n=7, а k=4. Оказывается, что таких вариантов =ЧИСЛКОМБ(7;4) равно 35.

Воспользуемся файлом примера (ссылка внизу статьи) , чтобы наглядно убедиться, что мы решили задачу правильно.

Произвольным образом сопоставим маркам машин числовые значения и сделаем сокращения названий марок: LADA Granta (LG=1), Hyundai Solaris (HS=2), …

Выставив в ячейках

В5

и

В6

значения 7 и 4 соответственно, определим все варианты размещений машин в автовозе (см. столбцы AJ:AM).

Примечание

: О Перестановках можно прочитать в статье

Перестановки без повторений: Комбинаторика в MS EXCEL

, а о Размещениях в статье

Размещения без повторений: Комбинаторика в MS EXCEL

.

In this guide, we’re going to show you how to find all combinations of two lists in Excel by using Power Query.

Download Workbook

In our example, we have two lists that contain Pokémon names and we want to see the combinations of matched Pokémon.

We can join these two tables to compare them by using Get & Transform Data. Get & Transform Data feature consists of tools, previously known as Power Query. You can get more information about this here: Power Query 101.

Creating queries from tables

First step is to convert the tables into Power Query structure.

1. Select one of your tables.
2. Open the Data tab and click the corresponding icon under the Get & Transform Data section to get data from your workbook.
   1. (Microsoft 365 / Excel 2019) From Sheet
      
      
   2. (Excel 2016) From Table/Range
   3. (Excel 2010 / Excel 2013) Use Power Pivot tab instead of Data
3. Clicking the button opens the Power Query Editor.
   
4. Once you are done with the changes, click Custom Column in Add Column. We will add a helper column which will be a common point between the two tables.
   
5. Enter a friendly name for your new column and enter =1 into the formula section.
   
6. Click OK to save.
   
7. After adding the helper column, return to the Home tab and click Close & Load To item in Close & Load list to see options.
   
8. Make sure to select Only Create Connection in the Import Data This option will save the table as a connection rather that populating the queried data in the worksheet.
   
9. Apply the same steps to the other table. At the end you should have two queries corresponding with your tables. You can see these tables in Queries & Connections pane in Excel.
   

Joining queries to find all combinations of two lists

Next step is to join (merge) the queries on a new table which will allow us to see any differences between the tables.

1. Once the queries from the tables are ready, go to Data > Get Data > Combine Queries > Merge to open the Merge dialog of Power Query.
   
2. Select each table in the drop downs.
3. Click on the column for each table to select them.
4. Finally select Full Outer option for Join Kind to join by all rows from both tables.
5. Click OK to apply selections.
   
6. You will see three columns after merging. The first one is the items of the first list. Right-click on the column in the middle and select Remove to delete the helper column.
   
7. Click the button next to the column title.
8. Make sure that the Expand option is selected in the popup menu.
9. Uncheck the helper column.
10. Uncheck Use original column as prefix.
11. Click OK to populate the data of the second table.
    
12. You will see the combinations in Power Query. When you are OK with the outcome, click Close & Load, this time to populate the entire data into your worksheet.
    

After populating the merged table, you can see all combinations of the two lists.

Например, у меня есть следующий список чисел, и теперь я хочу знать, какая комбинация чисел в списке дает в сумме 480, на следующем скриншоте вы можете увидеть, что есть пять групп возможных комбинаций, которые в сумме равны до 480, например, 300 + 60 + 120, 300 + 60 + 40 + 80 и т. д. В этой статье я расскажу о некоторых методах определения суммирования ячеек до определенного значения в Excel.

• Найдите комбинацию ячеек, равную заданной сумме, с помощью формул
• Найдите комбинацию ячеек, которая равна заданной сумме, с помощью надстройки Solver
• Найдите комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем
• Найдите все комбинации, которые равны заданной сумме, с удивительной функцией

---

Найдите комбинацию ячеек, равную заданной сумме, с помощью формул

Во-первых, вам нужно создать несколько имен диапазонов, а затем применить формулу массива, чтобы найти ячейки, которые суммируются с целевым значением, выполните следующие действия шаг за шагом:

1. Выберите список номеров и определите для этого списка имя диапазона – Range1 в Имя Box, и нажмите Enter ключ для завершения определения имени диапазона, см. снимок экрана:

2. После определения имени диапазона для списка номеров вам необходимо создать еще два имени диапазона в Менеджер имен коробка, пожалуйста, нажмите Формулы > Менеджер имен, В Менеджер имен диалоговое окно, нажмите Новинки кнопку см. скриншоты:

3. В выскочившем Новое имя диалоговое окно введите имя List1 в Имя и фамилия поле и введите эту формулу = СТРОКА (КОСВЕННАЯ (“1:” & СТРОКА (Диапазон1))) (Range1 это имя диапазона, которое вы создали на шаге 1) в Относится к поле, см. снимок экрана:

4. Нажмите OK вернуться к Менеджер имен диалоговое окно, затем продолжайте щелкать Новинки кнопку, чтобы создать другое имя диапазона, в Новое имя диалоговое окно введите имя List2 в Имя и фамилия поле и введите эту формулу = СТРОКА (КОСВЕННАЯ (“1:” & 2 ^ СТРОКА (Диапазон1))) (Range1 это имя диапазона, которое вы создали на шаге 1) в Относится к поле, см. снимок экрана:

5. После создания имен диапазонов примените следующую формулу массива к ячейке 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”,””), и нажмите Shift + Ctrl + Enter вместе, затем перетащите дескриптор заполнения вниз в ячейку B8, последнее число в списке, и вы увидите, что числа, общая сумма которых составляет 480, помечены как X в столбце B, см. снимок экрана:

---

---

Найдите комбинацию ячеек, которая равна заданной сумме, с помощью надстройки Solver

Если вы не уверены в приведенном выше методе, Excel содержит Надстройка Solver функция, используя эту надстройку, вы также можете определить числа, общая сумма которых равна заданному значению.

1. Во-первых, вам нужно активировать это решающее устройство добавить в, перейдите к Файл > Опции, В Параметры Excel диалоговое окно, нажмите Надстройки на левой панели, а затем щелкните Надстройка Solver из Неактивные надстройки приложений раздел, см. снимок экрана:

2. Затем нажмите Go для входа в Надстройки диалог, проверьте Надстройка Solver и нажмите OK для успешной установки этой надстройки.

3. После активации надстройки Solver вам необходимо ввести эту формулу в ячейку B9: = СУММПРОИЗВ (B2: B9; A2: A9)(B2: B9 – это пустые ячейки столбца рядом со списком номеров, и A2: A9 это список номеров, который вы используете. ) и нажмите Enter ключ, см. снимок экрана:

4. Затем нажмите Данные > решающее устройство , чтобы перейти к Параметр решателя диалоговое окно, в диалоговом окне выполните следующие операции:

(1.) Щелкните  кнопка для выбора ячейки B10 где ваша формула из Установить цель раздел;

(2.) Тогда в к раздел, выберите Значение, и введите целевое значение 480 как вам нужно;

(3.) Под Изменяя ячейки переменных раздел, нажмите кнопка для выбора диапазона ячеек B2: B9 где будут отмечены ваши соответствующие числа.

5. Затем нажмите Добавить кнопку, чтобы перейти к Добавить ограничение диалоговое окно, нажмите кнопка для выбора диапазона ячеек B2: B9И выберите бункер из выпадающего списка см. снимок экрана:

6. Нажмите OK вернуться в Параметр решателя диалоговое окно, затем щелкните Решить кнопку, через несколько минут Результаты решателя появится диалоговое окно, и вы увидите, что комбинация ячеек, равная заданной сумме 480, помечена как 1. В поле Результаты решателя диалог, пожалуйста, выберите Сохранить решение для решения и нажмите OK для выхода из диалога. Смотрите скриншот:

Внимание: Этот метод также позволяет получить только одну комбинацию ячеек, если существует более одной комбинации значений, сумма которой равна определенному значению.

---

Найдите комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем

Первые два метода являются сложными для большинства наших пользователей Excel, здесь я могу создать код VBA, чтобы быстро и легко решить эту задачу.

Чтобы получить правильный результат, вы должны сначала отсортировать список номеров в порядке убывания. А затем проделайте следующие шаги:

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: найти комбинацию ячеек, равную заданной сумме:

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. Затем сохраните и закройте это окно кода, а затем вернитесь на рабочий лист и введите эту формулу = getcombination (A2: A9, C2) в пустую ячейку и нажмите Enter key, вы получите следующий результат, который отображает комбинационные числа, равные заданной сумме, см. снимок экрана:

---

Найдите все комбинации, которые равны заданной сумме, с удивительной функцией

Возможно, все вышеперечисленные методы несколько сложны для вас, здесь я представлю мощный инструмент, Kutools for Excel, С его Составить номер функция, вы можете быстро получить все комбинации, равные заданной сумме.

Советы:Чтобы применить это Составить номер функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Кутулс > Содержание > Составить номер, см. снимок экрана:

2, Затем в Составьте число диалоговое окно, щелкните кнопку, чтобы выбрать список номеров, который вы хотите использовать, из Источник данных, а затем введите общее число в Сумма текстовое поле, см. снимок экрана:

3. А затем нажмите OK Кнопка, появится окно с подсказкой, чтобы напомнить вам выбрать ячейку, чтобы найти результат, см. снимок экрана:

4. Затем щелкните OK, и теперь все комбинации, которые равны данному номеру, были отображены, как показано на скриншоте ниже:

Нажмите, чтобы скачать Kutools for Excel и бесплатная пробная версия прямо сейчас!

---

Демонстрация: найдите комбинацию ячеек, которая равна заданной сумме в Excel

Например, у меня есть следующий список чисел, и теперь я хочу знать, какая комбинация чисел в списке дает в сумме 480, на следующем скриншоте вы можете видеть там пять групп возможных комбинаций, которые в сумме равны 480, например 300 + 60 + 120, 300 + 60 + 40 + 80 и т. д. В этой статье я расскажу о некоторых методах, чтобы найти, какие ячейки дают в сумме определенное значение. в Excel.

• Найти комбинацию ячеек, равную заданной сумме, с помощью формул
• Найти комбинацию ячеек, которая равна заданной сумме, с помощью Solver Add -in
• Найти комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем
• Найдите все комбинации, которые равны заданной сумме, с удивительной функцией

---

Найдите ячейки комбинация, равная заданной сумме с формулами

Во-первых, вам нужно создать так me, а затем примените формулу массива, чтобы найти ячейки, сумма которых соответствует целевому значению, выполните следующие действия шаг за шагом:

1 . Выберите список номеров и определите для этого списка имя диапазона – Range1 в поле Имя и нажмите клавишу Enter . чтобы завершить определение имени диапазона, см. снимок экрана:

2 . После определения имени диапазона для списка номеров вам необходимо создать еще два имени диапазона в поле Диспетчер имен , нажмите Формулы > Диспетчер имен , в диалоговом окне Диспетчер имен нажмите кнопку Создать , см. снимки экрана:

3 . В появившемся диалоговом окне Новое имя введите имя List1 в поле Имя и введите эту формулу = ROW (INDIRECT (“1:” & ROWS (Range1))) ( Range1 – это имя диапазона, созданное на шаге 1) в Относится к полю , см. Снимок экрана:

4 . Нажмите OK , чтобы вернуться в диалоговое окно Диспетчер имен , затем продолжайте нажимать кнопку New , чтобы создать другое имя диапазона в в диалоговом окне Новое имя введите имя List2 в поле Имя и введите эту формулу = ROW (INDIRECT (“1:” & 2 ^ ROWS (Range1))) ( Range1 – это имя диапазона, созданное на шаге 1) в Ссылается в поле , см. снимок экрана:

5 . После создания имен диапазонов примените следующую формулу массива к ячейке B1:

= 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”, “”) и нажмите Shift + Ctrl + Enter вместе, затем перетащите дескриптор заполнения вниз в ячейку B8, последнее число в списке, и вы увидите, что числа, общая сумма которых составляет 480, помечены как X в столбце B, см. снимок экрана:

---

---

Найдите комбинацию ячеек, равную заданной сумме, с помощью надстройки Solver

Если вы запутались с описанным выше методом, Excel содержит функцию Solver Add-in . С помощью этой надстройки вы также можете определить числа, общая сумма которых равна заданному значению.

1. Сначала вам нужно активировать эту надстройку Solver , перейдите в Файл > Параметры , в диалоговом окне Параметры Excel щелкните Надстройки на левой панели, а затем щелкните Надстройка Solver из раздела Неактивные надстройки приложений , см. Снимок экрана:

. Затем нажмите кнопку Go , чтобы войти в диалоговое окно Add-Ins , отметьте опцию Solver Add-in и нажмите ОК , чтобы успешно установить эту надстройку.

3 . После активации надстройки Solver вам необходимо ввести эту формулу в ячейку B9: = SUMPRODUCT (B2: B9, A2: A9) , ( B2: B9 – это пустые ячейки столбца рядом с вашим списком номеров, а A2: A9 – это список номеров, который вы используете.), и нажмите Enter . , см. снимок экрана:

4. Затем нажмите Data > Solver , чтобы перейти к диалоговому окну Solver Parameter , в диалоговом окне, пожалуйста выполните следующие операции:

(1.) Нажмите кнопку , чтобы выбрать ячейку B10 , где ваша формула из раздела Установить цель ;

(2.) Затем в To выберите Value Of и введите желаемое значение 480 , как вам нужно;

(3.) Под Изменением ячеек переменных раздел, нажмите кнопку , чтобы выбрать диапазон ячеек B2: B9 , в котором будут отмечены соответствующие числа.

5. И затем нажмите кнопку Добавить , чтобы перейти в диалоговое окно Добавить ограничение , нажмите , чтобы выбрать диапазон ячеек B2: B9 , и выберите bin из раскрывающегося списка, см. снимок экрана:

6 . Нажмите OK , чтобы вернуться в диалоговое окно Параметры решателя , затем нажмите кнопку Решить , через несколько минут появится Появится диалоговое окно Solver Results , и вы увидите, что комбинация ячеек, равная заданной сумме 480, помечена как 1. В диалоговом окне Solver Results выберите Сохранить решение и нажмите ОК , чтобы закрыть диалоговое окно. См. Снимок экрана:

Примечание : этот метод также может получить только одну комбинацию ячеек, если существует более одной комбинации значений, имеющая сумму равное определенному значению.

---

Найдите комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем

Первые два метода являются сложными для большинства наших пользователей Excel, здесь я могу создать код VBA для быстрого и простого решения этой задачи.

Чтобы получить правильный результат, вы должны сначала необходимо отсортировать список номеров в порядке убывания. Затем выполните следующие действия:

1 . Удерживайте ALT + F11 , чтобы открыть окно Microsoft Visual Basic для приложений .

2 . Нажмите Вставить > Module и вставьте следующий код в окно Module .

VBA код: найти комбинацию ячеек, равную заданной сумме:

3 . Затем сохраните и закройте это окно кода, а затем вернитесь к рабочему листу и введите эту формулу = getcombination (A2: A9, C2) в пустую ячейку и нажмите Введите , вы получите следующий результат, который отображает комбинацию чисел, равную заданной сумме, см. Снимок экрана:

---

Найдите все комбинации, которые равны заданной сумме, с удивительной особенностью

Может быть, все вышеперечисленные методы несколько сложны для я представлю вам мощный инструмент Kutools for Excel с его Make Up Функция Number позволяет быстро получить все комбинации, равные заданной сумме.

Советы: Чтобы применить это Составьте число . Во-первых, вы должны загрузить Kutools for Excel , а затем быстро и легко применить эту функцию.

После установки Kutools for Excel , сделайте следующее:

1 . Нажмите Kutools > Content > Составьте число , см. Снимок экрана:

2 . Затем в диалоговом окне Составьте число нажмите , чтобы выбрать список чисел, который вы хотите использовать, из источника данных , а затем введите общее число в текстовое поле Sum , см. снимок экрана. :

3 . А затем нажмите кнопку OK , появится окно с напоминанием о том, что нужно выбрать ячейку для поиска результата, см. Снимок экрана:

4. Затем нажмите OK , и теперь все комбинации, которые равны к этому заданному номеру были отображены, как показано на скриншоте ниже:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию прямо сейчас!

---

Демо: найдите комбинацию ячеек, которая равна заданной сумме в Excel

---

В этой статье описаны синтаксис формулы и использование функции ЧИСЛКОМБ в Microsoft Excel.

Описание

Возвращает количество комбинаций для заданного числа элементов. Функция ЧИСЛКОМБ используется для определения общего числа всех групп, которые можно составить из элементов данного множества.

Синтаксис

ЧИСЛКОМБ(число;число_выбранных)

Аргументы функции ЧИСЛКОМБ описаны ниже.

• Число    — обязательный аргумент. Количество элементов.

• Число_выбранных    — обязательный аргумент. Количество элементов в каждой комбинации.

Замечания

• Числовые аргументы усекаются до целых чисел.

• Если хотя бы один из аргументов не является числом, то #VALUE! значение ошибки #ЗНАЧ!.

• Если число < 0, number_chosen < 0 или < number_chosen, то #NUM! значение ошибки #ЗНАЧ!.

• Комбинацией считается любое множество или подмножество элементов независимо от их внутреннего порядка. Комбинации отличаются от перестановок, для которых порядок существен.

• Число комбинаций определяется по следующей формуле, где число = n, а число_выбранных = k:

  

  где

  

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы видеть все данные.

Формула	Описание	Результат
=ЧИСЛКОМБ(8;2)	Возможные команды по два человека, которые могут быть сформированы из восьми кандидатов.	28