Excel все комбинации значений
Вычисление числа комбинаций по формулам комбинаторики в Excel. Перебор всех вариантов перестановок, сочетаний и размещений для множеств без повторений и с повторениями.
Взаимосвязь некоторых распределений в MS EXCEL
Рассмотрим взаимосвязь Биномиального распределения, распределения Пуассона, Нормального распределения и Гипергеометрического распределения. Определим условия, когда возможна аппроксимация одного распределения другим, приведем примеры и графики.
Перестановки с повторениями: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество перестановок с повторениями из n элементов. С помощью формул выведем на лист все варианты таких перестановок (английский перевод термина: permutations of multisets).
Сочетания с повторениями: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество Сочетаний с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Сочетаний (английский перевод термина: combinations with …
Разрезка на мерные длины
Требуется разрезать провод на куски определенной длины, так чтобы количество отходов было минимально. Задачу решим методом перебора всех возможных комбинаций разрезки.
Размещения без повторений: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество Размещений из n по k и с помощью формул выведем на лист соответствующие варианты размещений (английский перевод термина: partial permutation или sequence without repetition).
Перестановки без повторений: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество перестановок из n элементов. С помощью формул выведем на лист все варианты перестановок (английский перевод термина: permutation).
Размещения c повторениями: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество Размещений с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Размещений (английский перевод термина: sequence with …
Комбинаторика в MS EXCEL
Обзорная статья, в которой приведены основные функции MS EXCEL для вычисления количества перестановок, сочетаний и размещений. Рассмотрены варианты комбинаций без повторений и с повторениями (выборка с возвращением).
Комбинации элементов из нескольких множеств: Комбинаторика в MS EXCEL
Пусть имеется несколько множеств —
Сочетания без повторений: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество сочетаний из n элементов по k. С помощью формул выведем на лист все варианты сочетаний (английский перевод термина: Combinations without repetition).
Как сгенерировать все комбинации из 3 или нескольких столбцов в Excel?
Предположим, у меня есть 3 столбца данных, теперь я хочу сгенерировать или перечислить все комбинации данных в этих 3 столбцах, как показано ниже. Есть ли у вас какие-нибудь хорошие методы решения этой задачи в Excel?
Сгенерируйте все комбинации на основе 3 столбцов данных с помощью формулы
Следующая длинная формула может помочь перечислить все комбинации из 3 столбцов, пожалуйста, сделайте следующее:
1. Пожалуйста, щелкните ячейку, в которой нужно вывести результат, а затем скопируйте и вставьте в нее следующую формулу:
Внимание: В этой формуле: A2: A4, B2: B6, C2: C5 - это диапазоны данных, которые вы хотите использовать.
2. Затем перетащите дескриптор заполнения вниз к ячейкам, пока не отобразятся пустые ячейки, что означает, что были перечислены все комбинации из 3 столбцов, см. Снимок экрана:
Создавайте все комбинации на основе 3 или нескольких столбцов данных с помощью кода VBA
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
Код VBA: генерировать все комбинации из 3 или нескольких столбцов
Внимание: В приведенном выше коде A2: A4, B2: B6, C2: C5 диапазон данных, который вы хотите использовать, E2 - это выходная ячейка, в которой вы хотите найти результаты. Если вы хотите получить все комбинации из большего количества столбцов, измените и добавьте другие параметры в код по мере необходимости.
3, Затем нажмите F5 ключ для запуска этого кода, и все комбинации из 3 или нескольких столбцов будут сгенерированы одновременно, см. снимок экрана:
Создавайте все комбинации на основе трех или нескольких столбцов данных с помощью потрясающей функции
Если у вас есть Kutools for Excel, с его мощным Список всех комбинаций вы можете быстро и легко перечислить все комбинации нескольких столбцов.
Советы: Чтобы применить это Список всех комбинаций функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс > Вставить > Список всех комбинаций, см. снимок экрана:
2. В Список всех комбинаций В диалоговом окне укажите данные столбцов и разделители для перечисления комбинаций, как показано на следующем снимке экрана:
3. После установки данных и разделителя нажмите Ok , в следующем окне запроса выберите ячейку, чтобы найти результат, см. снимок экрана:
4. А затем нажмите OK все комбинации были сгенерированы немедленно, как показано ниже:
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
- Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .
Вкладка Office - предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Чтобы сгенерировать любую случайную комбинацию, мы можем использовать
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))
КАК сделать ниже формулу для 5 столбцов? Пытаюсь разобраться, но выдает ошибку
Привет, Нихил, Для получения всех комбинаций из 5 столбцов может помочь приведенный ниже код VBA, измените ссылки на ячейки на свои данные.
ПодсписокВсеКомбинации()
'Обновить Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 Как диапазон
Dim xRg как диапазон
Dim xStr как строка
Dim xFN1, xFN2, xFN3, xFN4, xFN5 как целое число
Dim xSV1, xSV2, xSV3, xSV4, xSV5 как строка
Set xDRg1 = Range("A2:A7") 'Данные первого столбца
Установить xDRg2 = Range("B2:B7") 'Данные второго столбца
Установить xDRg3 = Range("C2:C7") 'Данные третьего столбца
Set xDRg4 = Range("D2:D7") 'Данные четвертого столбца
Set xDRg5 = Range("E2:E7") 'Данные пятого столбца
xStr = "-" 'Разделитель
Установить xRg = Range("H2") 'Выходная ячейка
Для xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Элемент(xFN1).Текст
Для xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Элемент(xFN2).Текст
Для xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Элемент(xFN3).Текст
Для xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Элемент(xFN4).Текст
Для xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Элемент(xFN5).Текст
xRg.Value = xSV1 и xStr и xSV2 и xStr и xSV3 и xStr и xSV4 и xStr и xSV5
Установите xRg = xRg.Offset (1, 0)
Далее
Далее
Далее
Далее
Далее
Конец сабвуфера Пожалуйста, попробуйте, надеюсь, это поможет вам!
Привет, Skyyang, я изменил код для 7 рулонов, но, поскольку в Excel всего 1,048,576 1 0 строк, код VBA не может дать все комбинации. У вас есть идея, как я могу продолжить работу с другими столбцами? Я думаю, что этот код - Установить xRg = xRg.Offset (XNUMX, XNUMX) необходимо изменить
Привет, Skyyang, я изменил код для 7 рулонов, но, поскольку в Excel всего 1,048,576 XNUMX XNUMX строк, код VBA не может дать все комбинации.
У вас есть идея, как я могу продолжить работу с другими столбцами?
Я думаю, что этот код - Установить xRg = xRg.Offset (1, 0)
необходимо изменить
Привет В коде VBA я использовал четыре столбца и диапазон столбцов: E2: E75, B2: B267, C2: C195 и D2: D267. Выходной диапазон J2. В этом случае выходной результат превышал лимит строк. Пожалуйста, помогите решить ошибку
Привет Итак, это код для 9 столбцов :')
ПодсписокВсеКомбинации()
'Обновить Extendoffice
Размер xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 Как диапазон
Dim xRg как диапазон
Dim xStr как строка
Размер xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 как целое число
Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 как строка
Set xDRg1 = Range("A2:A3") 'Данные первого столбца
Установить xDRg2 = Range("B2:B3") 'Данные второго столбца
Установите xDRg3 = Range("C2:C10") 'Данные третьего столбца
Set xDRg4 = Range("D2:D2") 'Данные третьего столбца
Set xDRg5 = Range("E2:E3") 'Данные третьего столбца
Set xDRg6 = Range("F2:F3") 'Данные третьего столбца
Установите xDRg7 = Range("G2:G4") 'Данные третьего столбца
Установите xDRg8 = Range("H2:H3") 'Данные третьего столбца
Set xDRg9 = Range("I2:I3") 'Данные третьего столбца
xStr = "-" 'Разделитель
Установить xRg = Range("K2") 'Выходная ячейка
Для xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Элемент(xFN1).Текст
Для xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Элемент(xFN2).Текст
Для xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Элемент(xFN3).Текст
Для xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Элемент(xFN4).Текст
Для xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Элемент(xFN5).Текст
Для xFN6 = 1 To xDRg6.Count
xSV6 = xDRg6.Элемент(xFN6).Текст
Для xFN7 = 1 To xDRg7.Count
xSV7 = xDRg7.Элемент(xFN7).Текст
Для xFN8 = 1 To xDRg8.Count
xSV8 = xDRg8.Элемент(xFN8).Текст
Для xFN9 = 1 To xDRg9.Count
xSV9 = xDRg9.Элемент(xFN9).Текст
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
Установите xRg = xRg.Offset (1, 0)
Далее
Далее
Далее
Далее
Далее
Далее
Далее
Далее
Далее
End Sub
Перечислите или сгенерируйте все возможные комбинации из двух списков с формулой
Следующая длинная формула поможет вам быстро составить список всех возможных комбинаций значений двух списков. Пожалуйста, сделайте следующее:
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в этом случае я введу ее в ячейку D2, а затем нажмите Enter ключ для получения результата, см. снимок экрана:
Внимание: В приведенной выше формуле $ A $ 2: $ A $ 5 - это диапазон значений первого столбца, а 2 млрд долларов: 4 млрд долларов - это диапазон значений второго списка, в котором вы хотите перечислить все их возможные комбинации, $ D $ 2 это ячейка, в которую вы поместили формулу, вы можете изменить ссылки на ячейки по своему усмотрению.
2. Затем выберите ячейку D2 и перетащите маркер заполнения вниз к ячейкам, пока не получите пустые ячейки, и все возможные комбинации будут перечислены на основе значений двух списков. Смотрите скриншот:
Перечислите или сгенерируйте все возможные комбинации из трех или более списков с кодом VBA
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: генерировать все комбинации из 3 или нескольких столбцов
Внимание: В приведенном выше коде A2: A5, B2: B4, C2: C4 диапазон данных, который вы хотите использовать, E2 - это выходная ячейка, в которой вы хотите найти результаты. Если вы хотите получить все комбинации из большего количества столбцов, измените и добавьте другие параметры в код по мере необходимости.
3. Затем нажмите F5 ключ для запуска этого кода, и все комбинации из 3 столбцов будут сгенерированы сразу, см. снимок экрана:
Составьте список или сгенерируйте все возможные комбинации из нескольких списков с помощью мощной функции
Если имеется несколько значений списков, необходимо указать возможные комбинации, возможно, вам будет сложно изменить код. Здесь я могу порекомендовать мощный инструмент - Kutools for Excel, он содержит удобную функцию Список всех комбинаций который может быстро перечислить все возможные комбинации на основе заданных списков данных.
Советы: Чтобы применить это Список всех комбинаций функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс > Вставить > Список всех комбинаций, см. снимок экрана:
2. В Список всех комбинаций В диалоговом окне выполните операции, как показано в демонстрации ниже:
3. Затем все указанные значения и разделители были перечислены в диалоговом окне, см. Снимок экрана:
4.А затем нажмите Ok Кнопка, и появится окно подсказки, напоминающее вам о выборе ячейки для вывода результата, см. снимок экрана:
5. Нажмите OK, все возможные комбинации на основе данных списков были сгенерированы на листе, как показано на следующем снимке экрана:
Сочетаниями из n различных элементов по k элементов называются комбинации, которые отличаются хотя бы одним элементом. Например, ниже перечислены ВСЕ 3-х элементные сочетания, взятые из множества, состоящего из 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 чисел равно 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 .
Пусть имеется несколько множеств — , , , количество элементов в которых может быть различно. Требуется составить все возможные комбинации элементов этих множеств таким образом, чтобы в комбинации присутствовал только один элемент из каждого множества: (A1, B1, C1), (A1, B2, C1), (A1, B2, C2), … Также подсчитаем в MS EXCEL количество таких комбинаций.
Начнем со множеств, состоящих из последовательностей целых чисел без повторов с шагом =1. Например, или . В файле примера создана форма для 4-х множеств, состоящих из 6 элементов (максимум).
В пределах одного множества все элементы должны быть разными, а элементы, принадлежащие разным множествам, могут повторяться. Количество элементов во множествах может быть различным.
Число различных комбинаций, содержащих по одному элементу из каждого множества, равно произведению количеств элементов каждого множества. Например, как следует из картинки выше, число комбинаций =6*4*3*2=144.
С помощью простых формул можно найти все эти 144 комбинации (см. файл примера лист Пример Числа )
Изменяя количество элементов во множествах, будут автоматически изменяться число комбинаций и сами комбинации (необходимо следить, чтобы количество строк с формулами было достаточным для отображения всех комбинаций).
При необходимости, в файле примера можно легко увеличить как количество множеств, так и количество элементов в каждом из множеств. Для этого нужно скопировать формулы в ячейки справа.
Примечание : Если все множества идентичны (состоят из одинаковых элементов), то задача сводится к Размещению с повторениями . Действительно, пусть в каждом из k множеств по n элементов. Произведение количеств элементов (n) k раз, равно n^k, что равно количеству Размещений с повторениями.
Множества, состоящие из букв (символов)
Если необходимо составить комбинации нечисловых элементов, например, взяв множества ; , то вышеуказанный подход также можно использовать. Для этого произвольным образом нужно сопоставить буквам последовательные числовые значения.
После того, как все комбинации для числовых эквивалентов будут построены, необходимо сделать обратное преобразование: каждому числу сопоставить соответствующую букву (см. лист Пример Буквы1 ).
Читайте также: