Видеоурок: Регулярные выражения в Google Таблицах

Всем привет. Делимся с вами видео, в котором рассмотрим функции REGEXEXTRACTи REGEXREPLACE для работы с регулярными выражениями в Google Таблицах.

С помощью них:
- Извлекаем числа из текстовой ячейки;
- Заменяем текст в ячейке по шаблону.

https://www.youtube.com/watch?v=Tw3_8LEIUlw

Файл с примером - по ссылке в описании видео.

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

Чтобы воспользоваться этим форматом в функции TEXT(ТЕКСТ), используйте шаблон "@":
=TEXT(A2;"@")

Если в ваших данных разное кол-во знаков после запятой, можно добавить округление:
=TEXT(ROUND(A2;3);"@") (до 3-го знака, например)

И по традиции добавим функцию FILTER, теперь для всего диапазона достаточно одной формулы:
=FILTER(TEXT(ROUND(A2:A;3);"@");A2:A<>"")

Посмотрите на скриншот, в колонках с формулами у целых чисел пропали разряды, а слишком длинные дробные округлены до 3-его знака.

Друзья, привет. Каждую субботу мы отвечаем на ваши вопросы (а задать их можно здесь: https://goo.gl/bRkn39) и эта суббота не станет исключением.

Сегодня мы покажем как:
— заменить в наименованиях товара кириллические значения на латинские (двумя способами!)
— составить для каждой даты в столбце текст вида "01.01 - 07.01.18" (первый день недели - последний)
— сгруппировать продажи по дням в месяца и сделать из этого график
— и еще немного расскажем про FILTER

Подборка: http://telegra.ph/10-yj-vypusk-subbotnih-otvetov-20-yanvarya-2018-01-20

Желаем всем прекрасных выходных!

P.S. Друзья, если среди вас есть владельцы каналов, либо вы можете посоветовать каналы, в которых мы можем прорекламировать свой канал (платно) - напишите, будем благодарны.

Друзья, сегодня с помощью условного форматирования закрасим все счета с победой хозяеев (1:0, 4:3, победа первой команды) в зеленый цвет, а с поражением - в красный.

Начнем:

0) в диапазоне A2:A5 счета спортивного состязания, первый:
3 : 2

1) c помощью функции SPLIT (разделитель ":") разделим текстовую строку со счетом на два элемента:
=SPLIT(a2;":")
если счет будет вида 3-1, то конечно разделителем будет "-" и т.д.

2) счет хозяеев, (счет первой команды), извлекаем с помощью функции INDEX, возвращая первый элемент из массива, который получился в рез-те действия функции SPLIT:
=INDEX(SPLIT(A2;":");1)

счет гостей - второй элемент:
=INDEX(SPLIT(A2;":");2)

3) теперь сравниваем результат первой команды и второй, и поместим получившуюся формулу в правило условного форматирования:
=INDEX(SPLIT(A2;":");1) > INDEX(SPLIT(A2;":");2)
если первая цифра в счете больше - формула вернет TRUE и правило сработает, чтобы выделить поражения хозяев используйте эту же формулу, заменив ">" на "<"

4) формула для правила условного форматирования создается для верхней ячейки диапазона (к которому вы применяете усл. форматирование), т.е. достаточно выделить необходимый диапазон (например, A2:A100), создать в нем правило условного форматирования и написать в него формулу для ячейки A2.

Кстати, календарь матчей, используя IMPORTHTML можно загрузить отсюда: https://www.sports.ru/amkar/calendar/, поставив вместо Амкара любую нужную команду (или с любого другого ресурса, где есть таблица с календарем матчей вашей любимой команды).

P.S. Напоминаем, что завтра мы проводим вебинар по супер-функциям FILTER и QUERY с многими кейсами.
Еще можно купить билет: https://google-tablitsy.timepad.ru/event/633908/
а если вы уже зарегистрировались и у вас есть какие-то вопросы - пишите нам, мы поможем.

В проверке данных можно использовать в качестве числового ограничения не фиксированное значение, а число из ячейки.

Например, запрещать ввод в диапазон чисел больше, чем указанное в определенной ячейке.
Пример - на GIF (открывайте по ссылке):

https://goo.gl/itQJqN

в ячейке A2: =ГИПЕРССЫЛКА("https://xn--r1a.website/google_sheets"; "Google Таблицы")

Друзья, привет, сегодня в субботней подборке ответов на ваши вопросы:

1) пишем пользовательскую функцию, которая выдергивает из =ГИПЕРССЫЛКА(ссылка;текст) - ссылку
2) функция FILTER, использующая данные выпадающего списка, получаем список всех тематик выбранного спикера
3) поговорим про парсинг с помощью IMPORTXML и квоты

Подборка здесь: http://telegra.ph/Subbotnie-otvety-27-yanvarya-2018-01-27
Вопросы сюда: https://goo.gl/bRkn39

Друзья, сегодня пост про проблему функции QUERY: если в ваших данных в одном столбце соседствуют числовые и текстовые значения, то текстовые данные могут исчезнуть при выводе.

Смотрите скриншот: зарплата Людмилы ("большая") просто не отображается.

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

Как это обойти: в таких случаях, а также, когда не получается работать с датами, пользуйтесь функцией FILTER.

Напомню: столбцы условия не обязательно должны быть в столбцах, предназначенных для вывода (см. формулу на скриншоте ниже)

P.S. А еще FILTER можно совместить с IMPORTRANGE, пробуйте

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

Давайте разбираться. Импортрейнджить будем оглавление нашего канала, каждая строка кода с комментарием: http://telegra.ph/Google-skript-vstavlyaem-formulu-i-zamenyaem-vse-na-znacheniya-01-31

P.S. Подобную модель мы используем для любых ситуаций с тяжелыми формулами (и однотипными, представьте столбец с 20к ВПР), если их постоянное обновление не требуется.

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

— про чекбоксы (выпадающие списки)
— про логику формулы IF (Если)
— и как защитить лист, оставив только некоторые диапазоны доступными для редактирования:
http://telegra.ph/Ezhenedelnye-Subbotnie-Moroznye-Otvety-na-vashi-voprosy-02-02

Свой вопрос можно задать здесь: https://goo.gl/bRkn39

P.S. Завтра материал посложней, посвященный текстовым функциям, из длинной строки будем вытаскивать и форматировать дату, стоимость, номера..

Всем привет.
Слушательница одного из потоков моего онлайн-курса обратилась за советом: как оптимальным образом решить задачу с обработкой выгрузки текстовых данных.

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

Иными словами, настраиваем формулы один раз, чтобы в дальнейшем превращать выгрузку в неудобном формате в данные нужного формата.
http://telegra.ph/Obrabatyvaem-vygruzku-v-vide-teksta-02-04