Excel все комбинации значений

Обновлено: 24.11.2024

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

Чтобы перечислить все возможные комбинации на листе Excel, выполните следующую процедуру.

Шаг 1. Откройте лист

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

Шаг 2. Выберите ячейку для результата

Теперь вам нужно выбрать ячейку, в которой вы хотите отобразить результат возможных комбинаций. Когда вы решили, где будет отображаться результат, вставьте формулу, как показано на рисунке 1 ниже;

Рисунок 1. Формула всех возможных комбинаций

На рисунке выше обратите внимание, что у нас указан каждый столбец. Это показывает, что мы перечислим возможные комбинации для каждого из столбцов.

Шаг 3. Перетащите формулу в другие ячейки

Чтобы сгенерировать возможную комбинацию для всех остальных ячеек, вам нужно перетащить формулу через все другие ячейки в столбце. Это показано на рисунке 2 ниже;

Рисунок 2. Возможные комбинации в ячейках

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Сегодня у меня есть две функции, которые я хотел бы продемонстрировать, они вычисляют все возможные комбинации из диапазона ячеек. Что такое комбинация? Чтобы объяснить комбинации, я должен объяснить разницу между комбинациями и перестановками.

Думайте о перестановках так, будто порядок важен, а о комбинациях — как будто порядок не важен. Если это сбивает с толку, посмотрите на следующие примеры.

Пример 1. Перестановки

Подумайте о номере телефона, каждая цифра может иметь от 0 до 9 или 10 различных значений. Пятизначный номер телефона содержит 100 000 перестановок (10x10x10x10x10 равно 100 000).

Теперь представьте себе номер телефона друга или коллеги. Если мы поменяем номера телефонов, возможно, вы позвоните незнакомцу. Порядок важен.

Прочитайте эту статью, чтобы узнать больше о перестановках:

Я обсуждал разницу между перестановками и комбинациями в своем последнем посте, сегодня я хочу поговорить о двух видах […]

Пример 2 — Комбинации

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

Пятизначный номер телефона имеет 100 000 возможных комбинаций, но пять из пяти ингредиентов пиццы имеют только одну комбинацию. Я думаю, что только математики используют слова перестановки, все остальные используют словосочетания, даже если они говорят о перестановках.

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

Вот полезная функция, которую я недавно нашел в поиске примеров решателя Excel. Я суммировал некоторые случайные значения из […]

Пользовательские функции

Следующая определяемая пользователем функция позволяет, например, увидеть все комбинации ингредиентов пиццы, которые вы можете выбрать, выбрав 5 из 7 ингредиентов.

Пользовательская формула массива в диапазоне ячеек E3:I23:

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

Посмотрите это видео, чтобы узнать больше о UDF

Как ввести формулу массива

Выберите диапазон ячеек E3:I23.
Нажмите левую кнопку мыши в строке формул
Введите пользовательскую функцию
Нажмите и удерживайте клавиши CTRL + SHIFT.
Нажмите клавишу ВВОД.

Если вы все сделали правильно, формула теперь начинается и заканчивается фигурными скобками, как здесь. Они появляются автоматически, не вводите фигурные скобки самостоятельно.

Формулы массива позволяют выполнять сложные вычисления, недоступные для обычных формул.

Код VBA

Эти функции имеют ограничение в 65532 строки, если вам нужно больше, прочитайте этот комментарий.

Куда скопировать код vba?

Перейдите в редактор VB (Alt + F11)
Нажмите левой кнопкой мыши на "Вставить" в меню.
Нажмите левой кнопкой мыши на "Модуль"
Вставить код в модуль кода
Вернуться в Excel

Совет! Знаете ли вы, что Excel может подсчитать количество комбинаций за вас? Используйте функцию COMBIN:

=COMBIN(7,5) равно 21 комбинации.

Получить файл Excel

Комбинации.xlsm

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

Крис спрашивает: Максимально допустимое число — 4 цифры, а числа — от 0 до 9. После того, как я заполню [...]

В этой статье демонстрируется макрос, который позволяет создать список перестановок, разрешенных повторений, на основе числа […]

Крис спрашивает: Максимально допустимое число — 4 цифры, а числа — от 0 до 9. После того, как я заполню [...]

Excelxor — отличный сайт для вдохновения, меня очень впечатлил этот пост Какие цифры в сумме дают […]

В этой статье демонстрируется формула Что находится на этой странице Формула вопроса Формула массива Объяснение формулы массива Получить файл Excel […]

Учитывая два отдельных списка элементов, как можно создать список всех возможных комбинаций из этих списков? В этом посте я покажу вам, как это можно сделать с помощью power query.

В этом примере я настроил два списка и создал из них таблицы с именами List1 и List2.

Создать запрос только на соединение для двух списков

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

Выберите ячейку в списке, затем перейдите на вкладку "Данные" и выберите команду "Из таблицы/диапазона" в разделе "Получить и преобразовать данные".

В редакторе запросов перейдите на вкладку "Добавить столбец" и выберите "Пользовательский столбец" в меню.

В диалоговом окне "Пользовательский столбец" добавьте постоянную формулу. Это может быть любой текст или число, просто убедитесь, что оно одинаково в обоих запросах списка.

Назовите новый столбец. Я назвал его «Присоединиться к столбцу».
Добавьте константу в формулу пользовательского столбца. Я выбрал 1 в качестве константы.
Нажмите кнопку ОК.

Перейдите на вкладку "Главная" в редакторе запросов и нажмите нижнюю часть кнопки "Закрыть и загрузить", затем выберите в меню "Закрыть и загрузить".

В диалоговом окне "Импорт данных" выберите "Только создать подключение" и нажмите кнопку "ОК".

Повторите эти шаги для обоих списков. У вас должно быть два запроса только на соединение с именами List1 и List2.

Объединить два запроса только на подключение с помощью запроса на слияние

Теперь мы готовы объединить эти два списка с помощью запроса на слияние.

Перейдите на вкладку "Данные" и выберите "Получить данные" в разделе "Получить и преобразовать данные". Выберите «Объединить запросы» в меню, а затем выберите «Объединить» в подменю.

Теперь мы можем настроить наш запрос на слияние для объединения двух списков.

Выберите List1 для первой таблицы.
Щелкните левой кнопкой мыши по столбцу соединения, созданному в запросах только на подключение.
Выберите List2 для второй таблицы.
Щелкните левой кнопкой мыши на столбце соединения в запросах только на соединение List2.
Выберите Тип соединения как Полное внешнее (все строки из обоих).
Нажмите кнопку ОК.

Сейчас мы можем удалить столбец соединения, так как он нам больше не нужен, и вывод будет менее загроможденным, если мы его удалим. Щелкните правой кнопкой мыши заголовок столбца и выберите Удалить.

Теперь мы можем расширить таблицу List2 в нашем запросе.

Щелкните левой кнопкой мыши значок фильтра справа от заголовка столбца.
Снимите галочку со столбца соединения, так как он нам не нужен в результатах запроса.
Снимите флажок Использовать исходное имя столбца в качестве префикса. Нам это тоже не нужно.
Нажмите кнопку ОК.

Теперь закройте и загрузите запрос на вкладке "Главная".

Наш список всех возможных комбинаций

Теперь у нас есть таблица, содержащая все возможные комбинации элементов из List1 и List2.

С небольшими таблицами, такими как этот пример, мы могли бы легко создать таблицу вручную. Однако для больших списков этот запрос существенно экономит время!

Мы также можем повторять этот процесс, если у нас есть более двух таблиц, чтобы найти все возможные комбинации элементов из 3 или более списков.

Чуть более быстрый метод

С тех пор, как я написал этот пост, я нашел более быстрый способ сделать это. В этом методе нам по-прежнему нужно загрузить обе таблицы в power query, но нам нужно будет создать только один настраиваемый столбец, и нам не нужно создавать запрос на слияние.

Создайте запрос только для подключения, который импортирует таблицу List1.

Теперь создайте запрос, который импортирует таблицу List2, и отредактируйте запрос. Добавьте пользовательский столбец и назовите его List1. Введите формулу =Список1 .

Разверните новый столбец List1, а затем закройте и загрузите запрос в таблицу.

В таблице будут все комбинации элементов из обоих списков, и мы сэкономили на создании пользовательского столбца в List1 и полностью отказались от использования запроса на слияние!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Представьте себе сценарий, в котором у вас есть два или более списков в Excel и вы хотите создать единую таблицу всех возможных комбинаций. Вопрос может быть задан в простой форме, например: «Как мы можем получить полный список всех комбинаций Главной книги и МВЗ?». Звучит просто, правда? В Excel или Power Query должна быть одна функция, которая будет генерировать это... правильно?

Э-э, на самом деле… Нет. Нам нужно принять более активное участие. Давайте углубимся в Power Query, чтобы посмотреть, как мы можем решить эту проблему.

В этом посте представлены два решения. Каждый из них рассказывает нам о Power Query по-разному, поэтому стоит изучить оба и понять различия.

Скачать файл примера

Я рекомендую вам загрузить файл примера для этого поста. Затем вы сможете работать с примерами и увидеть решение в действии, а файл будет полезен для дальнейшего использования.

Скачать файл: 0049 Список всех возможных комбинаций.zip

Посмотреть видео:

Загрузить данные в Power Query

Оба решения в этом сообщении требуют загрузки данных в Power Query. Итак, начнем с этого.

Повторите эти шаги для каждой таблицы.

После загрузки всех трех таблиц вернитесь в редактор Power Query, нажав Данные > Получить данные (раскрывающийся список) > Запустить редактор Power Query.

Повторите описанные выше шаги для каждого запроса. Убедитесь, что имя пользовательского столбца и формула пользовательского столбца одинаковы во всех запросах.

Теперь объедините все дополнительные таблицы в существующую объединенную таблицу.

Ваше окно Power Query будет выглядеть примерно так.

Нажмите кнопку "Развернуть" в заголовке столбца.
В подменю:
Выберите только столбец с данными, которые мы хотим сохранить (например, в нашем примере снимите флажок со столбца Temp)< br />Снимите флажок «Использовать исходное имя столбца в качестве префикса»
Нажмите «ОК»
Список возможных комбинаций теперь отображается в окне Power Query.

Если у вас больше двух запросов, расширьте и эти столбцы. Если вы работаете с данными примера, окно предварительного просмотра теперь выглядит так:

Выберите столбец Temp и нажмите клавишу Delete, так как он нам больше не нужен. Наконец, переименуйте столбцы в Product, Size и Color.

Теперь у нас есть список всех возможных комбинаций.

Наконец, нам просто нужно загрузить это в Excel, поэтому нажмите «Файл» > «Закрыть и загрузить» (раскрывающийся список) > «Закрыть и загрузить в…»

В диалоговом окне «Импорт данных» выберите «Таблица и новый лист», затем нажмите «ОК».

Есть более простой способ, который будет менее очевиден, если у вас нет опыта работы с таблицами и списками в Power Query.

В Excel откройте пустой запрос, нажав Данные > Получить данные > Из других источников > Пустой запрос.
В строке формул введите =Product. (Если у вас не открыта панель формул, нажмите «Вид» > «Панель формул» в редакторе Power Query).
Далее нажмите "Добавить столбец" > "Пользовательский столбец".
В диалоговом окне "Пользовательский столбец" введите следующее:
Имя нового столбца: Размер
Формула пользовательского столбца: =Размер
Затем нажмите "ОК".
Добавьте любые дополнительные запросы таким же образом.
Разверните таблицы тем же способом, что и в варианте 1.

Это снова даст нам таблицу, содержащую все возможные комбинации.

Теперь нам просто нужно загрузить это в Excel. Нажмите «Файл» > «Закрыть и загрузить» (раскрывающийся список) > «Закрыть и загрузить в…»

В окне "Импорт данных" выберите "Таблица и новый лист", затем нажмите "ОК".

После того как я начал использовать Power Query, прошло много времени, и я понял, что таблицы можно использовать таким образом. Надеюсь, вы узнали что-то новое.

Заключение

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

От меня только одно предупреждение. Из 3 списков, содержащих 3 элемента, мы создали 27 строк. Теперь представьте, что вы работаете с сотнями или тысячами строк. Это может привести к большому количеству данных. Предполагая, что все списки уникальны, он следует основным принципам умножения. 1000 строк в сочетании с еще 1000 строк дадут 1 000 000 строк. Так что это может стать слишком громоздким, если у нас много данных.
Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.

Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.

Не забывайте:

Если вы нашли этот пост полезным или у вас есть лучший подход, оставьте комментарий ниже.

Вам нужна помощь в адаптации этого к вашим потребностям?

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

Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.

Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

Читайте также: