Объединение всего с помощью формулы

Объединение всего с помощью формулы
Содержимое страницы

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

Все примеры доступны в Таблице, см. раздел Ссылки .

Объединение всего в одну ячейку

Собираем данные из диапазона в одну ячейку. Пожалуй, оптимальным в этом случае является использование формулы TEXTJOIN со вторым параметром TRUE (игнорировать_пустые – логическое значение. Если установить значение TRUE, пустые ячейки в текстовых аргументах не будут включены в результат).

Для диапазона A2:E10, картинка из заголовка.

=TEXTJOIN(",";TRUE;A2:E10)

Объединение колонок в одну строку

Пример. Объединение колонок в одну строку

При таком легком решении, как выше, кажется, что решение будет простым. Однако, TEXTJOIN не работает с формулой массива, объединяя все на своем пути. Обходным решением может быть использование свойства формулы QUERY, которая объединяет ячейки колонок в заголовки, исходя из значения третьего параметра (заголовки – [ НЕОБЯЗАТЕЛЬНО ] – количество заголовочных строк в верхней части раздела данных. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных).

Например, при запросе =QUERY(A:B, "select *", 3) будут объеденены три первые строки через пробел, каждая колонка в свой заголовок.

Ключевая формула

Для начала объединим все данные. Получим одну строку со всеми объединенными данными

=QUERY(A2:E11;;ROWS(A2:E11))

Форматирование вывода

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

ARRAYFORMULA(REGEXREPLACE(QUERY(A2:E11&"|";;ROWS(A2:E11));"(\|\s*)+";", "))

Пример дополнен выводом с заголовком, для понимания, какие данные собираются

={
  A1:E1;
  ARRAYFORMULA(REGEXREPLACE(
    QUERY(A2:E11&"|";;ROWS(A2:E11));
    "(\|\s*)+";
    ", "
  ))
}

Замены

Пример. Объединение колонок в одну строку

Если изменить разделитель ", " на CHAR(10), то ячейки объединятся через пеервод строки

={
  A1:E1;
  ARRAYFORMULA(REGEXREPLACE(
    QUERY(A2:E11&"|";;ROWS(A2:E11));
    "(\|\s*)+";
    CHAR(10)
  ))
}

Объединение строк в одну колонку

Пример. Объединение колонок в одну строку

Судя по картинке - квест будет еще тот. Причиной тому является свойство QUERY, эта формула обрабатывает массивы только построчно.

Ключевая формула

Для начала необходимо развернуть исследуемый массив через TRANSPOSE, чтобы можно было использовать особенность QUERY. Стоит обратить внимание, что подсчет объединяемых строк теперь идет исходя из количества колонок, т.к. “развернули” массив.

=QUERY(TRANSPOSE(B1:F5);;COLUMNS(B1:F5))

Пример. Объединение колонок в одну строку

Для построения “построчного” массива необходимо развернуть данные еще раз

=TRANSPOSE(QUERY(TRANSPOSE(B1:F5);;COLUMNS(B1:F5)))

Форматирование вывода

Избавиться от большого числа пробелов и качественного отображения необходимо добавить обработку данных готового массива

=ARRAYFORMULA(REGEXREPLACE(
  TRANSPOSE(QUERY(TRANSPOSE(B1:F5&"|");;COLUMNS(B1:F5)));
  "(\|\s*)+";
  ", "
))

Выводы и рекомендации

Google Spreadsheets являются мощным и гибким инструментом для преобразования массива данных. Существуют способы объединения, сбора и форматирования данных в целях формирования соединенных колонок и/или строк.

Приведенные выше формулы, хорошо работают при больших объемах данныхи. Их использование оправдано.

Ссылки