Note: The other languages of the website are Google-translated. Back to English

Как перечислить все возможные комбинации из одного столбца в Excel?

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

Перечислите все возможные комбинации из одного столбца с формулами

Перечислите все возможные комбинации из одного столбца с кодом VBA


Перечислите все возможные комбинации из одного столбца с формулами

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

1. Во-первых, вы должны создать две вспомогательные ячейки формулы. В ячейку C1 введите приведенную ниже формулу и нажмите Shift + Ctrl + Enter клавиши для получения результата:

=MAX(LEN(A2:A6))
Внимание: В этой формуле A2: A6 это список ячеек, которые вы хотите перечислить их комбинации.

2. В ячейке C2 введите следующую формулу и нажмите Shift + Ctrl + Enter клавиши вместе, чтобы получить второй результат, см. снимок экрана:

=CONCAT(A2:A6&REPT(" ",C2-LEN(A2:A6)))
Внимание: В этой формуле A2: A6 это список ячеек, которые вы хотите перечислить их комбинации, C2 содержит ли ячейка формулу, созданную на шаге 1.

3. Затем скопируйте и вставьте следующую формулу в ячейку D2 и нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, см. снимок экрана:

=IF(ROW()>2^(COUNTA(A$2:A$6)),"",TEXTJOIN(" + ",TRUE,IF(MID(TEXT(DEC2BIN(ROW()-1),REPT("0",COUNTA($A$2:$A$6))),ROW(INDIRECT("1:"&COUNTA($A$2:$A$6))),1)+0,TRIM(MID($C$3,(ROW(INDIRECT("1:"&COUNTA($A$2:$A$6)))-1)*$C$2+1,$C$2)),"")))
Внимание: В этой формуле A2: A6 это список ячеек, которые вы хотите перечислить их комбинации, C2 содержит ли ячейка формулу, созданную на шаге 1, C3 ячейка с формулой, которую вы создали на шаге 2, + символ является разделителем для разделения комбинаций, вы можете изменить их по своему усмотрению.

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

Внимание: эта формула доступна только в Office 2019, 365 и более поздних версиях.

Перечислите все возможные комбинации из одного столбца с кодом VBA

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

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

2, Затем нажмите Вставить > Модули, скопируйте и вставьте приведенный ниже код VBA в окно.

Код VBA: список всех возможных комбинаций из одного столбца

Sub ConnectArr()
'Updateby ExtendOffice
Dim xDValue As Variant
Dim xOutRg As Range
Dim xDictionary As Object
Dim xF As Long
Dim xChar As String
xDValue = Range("A2:A6").Value 'the data range
Set xOutRg = Range("C1") 'output range
xChar = "," 'separator
For xF = 1 To UBound(xDValue)
    Set xDictionary = CreateObject("Scripting.Dictionary")
    xDictionary(0) = "Sets of " & xF
    Call ConnectValue(xDValue, xDictionary, 0, xF, 0, "", xChar)
    xOutRg.Offset(0, xF - 1).Resize(xDictionary.Count).Value = WorksheetFunction.Transpose(xDictionary.Items)
    Set xDictionary = Nothing
Next
End Sub
Sub ConnectValue(ByRef pDValue, ByRef pDictionary, ByRef pLevel, ByVal pMaxLevel, ByVal pIndex, ByVal pValue, ByVal pChar)
Dim xF As Long
If pLevel = pMaxLevel Then
    pDictionary(pDictionary.Count + 1) = pValue
    Exit Sub
End If
For xF = pIndex + 1 To UBound(pDValue)
    If pValue = "" Then
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pDValue(xF, 1), pChar)
    Else
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pValue & pChar & pDValue(xF, 1), pChar)
    End If
Next
End Sub
Внимание: В приведенном выше коде:
  • A2: A6: это список данных, которые вы хотите использовать;
  • C1: — выходная ячейка;
  • ,: разделитель для разделения комбинаций.

3. Затем нажмите F5 ключ для выполнения этого кода. Все комбинации из одного столбца перечислены, как показано ниже:


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места