Разделить текст по столбцам в Google Sheets: Формулы vs Встроенное меню!
Довольно часто мы сталкиваемся с ситуацией, когда необходимые нам данные находятся в одной ячейке и помещены через пробел или прочий разделитель.
Допустим, в файл записываются данные скриптом (Google Apps Script) в виде данных, размещенных через двойное подчеркивание. И для того, чтобы обработать их, правильно ими воспользоваться, нам нужно их разделить:
Сделать это можно различными способами. Мы покажем Вам 2 способа - формулой и стандартным встроенным меню.
Разделение текста по столбцам в GoogleSheets через встроенное меню
На все про все нам понадобится 5 шагов:
- Копируем нужные нам данные из листа Data
- Вставляем их в заранее подготовленный лист с заголовками (для удобства)
- Нажимаем в меню "Данные" ➤ "Разделить на колонки..."
- В появимшемся окне выбираем разделить "Другой" (так как нашего нет в выпадающем списке)
- Вводим двойное подчеркивание и вуаля! Данные в нужном нам виде
Разделение текста по столбцам в GoogleSheets с использованием формул
Здесь нам понадобится написать хитроумную формулу, которая и сделает за нас всю работу:
Итак, разберем на части эту формулу:
"Незакрытый" диапазон: 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 запись:
Чтобы превратить эту запись в читаемую с помощью первого способа нам понадобится опять делать 5 шагов. А если данные добавляются постоянно, то это будет забирать нужное нам время на рутинную работу. В случае же с формулой теперь все автоматизировано. Как только данные были добавлены, на нашем листе с формулой сразу же добавился нужный нам результат:
Так что, чем пользоваться решать Вам в зависимости от ситуации.
И помните - в большинстве случаем рутинную работу можно автоматизировать и высвободить время для действительно важных вещей!
Как обычно - ссылка на пример в Google Sheets:
Ссылка на файл