Как разбить и распределить столбцы типа «A, B, C» и «X, Y, Z» на «A, X, B, Y, C, Z»?

Question

так что это кажется мне сложным, но у меня есть набор данных, которые нужно разделить на чередующиеся столбцы, вот что я имею в виду в приведенном ниже примере, я разделяю столбцы от A до CE и действую в зависимости от того, сколько наборов данных находится в ячейка разделяет мою запятую, а затем разделяет столбец B на чередующиеся столбцы DF и H ect ect

    | Column A | Column B | Column C | Column D | Column E | Column F | Column G |Column  H |
    | A, B, C  | X, Y, Z  | A        | X        | B        | Y        | C        | Z        |

что-то вроде выше, и моя конечная цель состоит в том, чтобы достичь

   |     Column J    |
   |A, X, B, Y, C, Z |

пожалуйста, если кто-то может помочь, это высоко ценится

Answer 1

Если вы готовы использовать VBA, это было бы относительно простым решением. Да, он использует VBA, но я бы посчитал, что ему легче следовать, чем обходной путь, который вам придется делать с именованными диапазонами. Код приведен ниже. Вы бы вызвали эту функцию на своем рабочем листе с помощью функции вроде =BlendCells(", ",A2,B2) . Первый параметр - это strDelimiter который позволяет вам вводить разделение текста. В вашем примере это будет запятая и пробел. Следующие два параметра - это две ячейки, которые вы хотите смешать.

Option Explicit

Public Function BlendCells(strDelimiter As String, Range1 As Range, Range2 As Range) As String

    'Declarations
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long

    'Setup each array
    arr1 = Split(Range1.Value, strDelimiter)
    arr2 = Split(Range2.Value, strDelimiter)

    'Blend the text
    For i = Application.Min(LBound(arr1), LBound(arr2)) To Application.Max(UBound(arr1), UBound(arr2))
        If i <= UBound(arr1) Then BlendCells = BlendCells & arr1(i) & strDelimiter
        If i <= UBound(arr2) Then BlendCells = BlendCells & arr2(i) & strDelimiter
    Next

    'Trim the results
    BlendCells = Left(BlendCells, Len(BlendCells) - Len(strDelimiter))

End Function

Answer 2

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

C1:  =INDEX(TRIM(MID(SUBSTITUTE(OFFSET($A$1,0,MOD(COLUMNS($A:A)+1,2)),",",REPT(" ",99)),{1,99,198},99)),MOD(COLUMNS($A:A)-1,3)+1)

и заполните слева до I1.

В качестве альтернативы, вы можете просто использовать инструмент Data/Text to Columns с запятой в качестве разделителя.

Чтобы получить окончательный результат, вы можете объединить отдельные столбцы:

=B1 & "," & C1 & "," & D1 & "," & E1 & "," & F1 & "," & G1 & "," & H1 & "," & I1

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

J1: =CONCATENATE(
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),1),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),1),",",
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),2),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),2),",",
INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),{1,99,198},99)),3),",",
INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),{1,99,198},99)),3))

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

Answer 3

Это непростая задача, если вы хотите реализовать ее без VBA.

Одним из возможных решений является использование нескольких формул массива внутри именованных диапазонов:

• выберите любую ячейку в 1-й строке
• откройте вкладку ленты « Formulas »> диалоговое окно « Name Manager » (Ctrl+F3)
• вставить New... именованный диапазон для каждой из следующих формул:
  • копия и вставить каждую строку в поле Name: в диалоговом окне New Name а затем вырезать формулу часть и вставить его в Refers to: поле

Name          Refers to

separator     =","
special       ="^"
enum          =ROW($1:$10)
each_left_A   =IFERROR(FIND(special, SUBSTITUTE($A1, separator, special, enum)), 999)
each_left_B   =IFERROR(FIND(special, SUBSTITUTE($B1, separator, special, enum)), 999)
each_right_A  =IFERROR(FIND(special, SUBSTITUTE($A1, separator, special, enum + 1)), 999)
each_right_B  =IFERROR(FIND(special, SUBSTITUTE($B1, separator, special, enum + 1)), 999)
nth_str       =MID($A1, each_left_A, each_right_A - each_left_A) & MID($B1, each_left_B, each_right_B - each_left_B)
space         =IF(MID(nth_str, 2, 1)=" ", " ", "")
first_str     =LEFT($A1, each_left_A) & space & LEFT($B1, each_left_B - 1)

• затем введите одну из следующих формул в выходной столбец (она сама по себе не является формулой массива, поэтому подтвердите только нажатием ENTER, вы можете повторять INDEX столько раз, сколько требуется - но в большинстве случаев enum , дополнительные индексы будут просто давать пустые строки , но если ваша таблица будет большой, будет снижение производительности):

  =first_str & INDEX(nth_str, 1) & INDEX(nth_str, 2)
  =first_str & INDEX(nth_str, 1) & INDEX(nth_str, 2) & INDEX(nth_str, 3) & INDEX(nth_str, 4) & INDEX(nth_str, 5) & INDEX(nth_str, 6) & INDEX(nth_str, 7) & INDEX(nth_str, 8) & INDEX(nth_str, 9) & INDEX(nth_str, 10)
  

  Вот так (экран печати из Excel 2010):

Пожалуйста, дайте мне знать, если требуется подробное объяснение всех / некоторых формул.
См. Также словарь Excel, чтобы быстрее находить решения.