Разделить текст по столбцам в Google Sheets: Формулы vs Встроенное меню!

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

Допустим, в файл записываются данные скриптом (Google Apps Script) в виде данных, размещенных через двойное подчеркивание. И для того, чтобы обработать их, правильно ими воспользоваться, нам нужно их разделить:

Google Docs

Сделать это можно различными способами. Мы покажем Вам 2 способа - формулой и стандартным встроенным меню.

Разделение текста по столбцам в GoogleSheets через встроенное меню

На все про все нам понадобится 5 шагов:

  1. Копируем нужные нам данные из листа Data
  2. Вставляем их в заранее подготовленный лист с заголовками (для удобства)
  3. Нажимаем в меню "Данные" ➤ "Разделить на колонки..."
  4. В появимшемся окне выбираем разделить "Другой" (так как нашего нет в выпадающем списке)
  5. Вводим двойное подчеркивание и вуаля! Данные в нужном нам виде Google Docs

Разделение текста по столбцам в GoogleSheets с использованием формул

Здесь нам понадобится написать хитроумную формулу, которая и сделает за нас всю работу:

Google Docs

Итак, разберем на части эту формулу:

"Незакрытый" диапазон: Data!A2:A

В отличии от Excel, Google Sheets позволяют делать вот такие ссылки на диапазон. Вот эта запись ":A" означает "до последней строчки в документе". Дело в том, что Google Sheets отличаются от Excel представлением таблицы. В Excel 1 лист имеет 1 048 576 строк и 16 384 столбца. Притом, что если их не использовать - то файл это нагружать не будет. В Google Sheets все видимые ячейки нагружают таблицу. И чем больше их будет - тем тяжелее будет работать с файлом. Поэтому 1 рабочая книга ограничена 2 000 000 ячейками (на дату публикации блога). Однако, если вы в 1 листе сделаете максимальное количество - работать с файлом будет очень тяжело. Поэтому при создании новой книги, размер листа устанавливается 1 000 строк и 26 столбцов (A-Z). При желании можно удалять/добавлять строки/столбцы.

Итак, в нашем случае, у нас стандартные листы 1 000*26. Поэтому диапазон A2:A равен A2:A1000. Прелесть "незакрытого" диапазона в том, что при добавлении новых строчек он автоматически будет увеличиваться.

Часть формулы: FILTER(Data!A2:A;Data!A2:A<>"")

Такой формулы тоже нет в Excel. Она фильтрует диапазон по заданным условиям. В данном примере она возвратит все ячейки столбца A, которые не пустые. В нашем случае вернется диапазон A2:A5

Часть формулы: Split(FILTER(Data!A2:A;Data!A2:A<>"");"__")

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

Часть формулы: ARRAYFORMULA(Split(FILTER(Data!A2:A;Data!A2:A<>"");"__"))

А это - формула массива. Она работает немного по-другому, чем в Excel. Для нее не нужно выделять диапазон, на которые она распостраняется. Достаточно просто ввести ее при вводе основной формулы (или нажать CTRL+SHIFT+ENTER). Она позволяет обычным формулам, которые работают только с 1 значением, работать с диапазонами (SPLIT разбивает 1 значение по разделителю, но не диапазон значений).

Итак формула готова и все прекрасно работает. С одной стороны она показалась намного сложнее в использовании, чем стандартное встроенное меню. Однако представьте, что данные будут постоянно добавляться. Например, добавилась еще 1 запись:

Google Docs

Чтобы превратить эту запись в читаемую с помощью первого способа нам понадобится опять делать 5 шагов. А если данные добавляются постоянно, то это будет забирать нужное нам время на рутинную работу. В случае же с формулой теперь все автоматизировано. Как только данные были добавлены, на нашем листе с формулой сразу же добавился нужный нам результат:

Google Docs

Так что, чем пользоваться решать Вам в зависимости от ситуации.

И помните - в большинстве случаем рутинную работу можно автоматизировать и высвободить время для действительно важных вещей!

Как обычно - ссылка на пример в Google Sheets:

Google Sheets Ссылка на файл