Ищем слово из списка в ячейке

Если нужно проверить, встречается ли в ячейке одно из нескольких слов (например, один из четырех брендов), можно поступить так:
1 Cлепить из этих слов одну текстовую строку с регулярным выражением, объединив их через вертикальную черту.
Сделать это можно с помощью функции JOIN
=JOIN("|" ; список слов для поиска)

На выходе получится (с названиями брендов из нашего примера) : Acer|Lenovo|Dell|HP (это простое регулярное выражение - одно из перечисленных значений)

2 И этот результат можно использовать в функции REGEXMATCH, чтобы проверить, встречается ли хотя бы одно из слов в ячейке:
=REGEXMATCH(ячейка ; JOIN("|" ; список слов для поиска) )

А если нужно вывести список , а не проверять каждую строку - эту формулу нужно будет использовать внутри FILTER:
=FILTER(диапазон ячеек ; REGEXMATCH(ячейка ; JOIN("|" ; список слов для поиска) ) )

P.S. Чтобы сделать условие нечувствительным к регистру, добавьте в начале выражения (?i):
"(?i)" & JOIN("|" ; список слов для поиска)

Файл с примером
(Создать копию)

Смотрите также:
ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное

Регулярные выражения в диалоговом окне "Найти и заменить"

Таблица с базой примеров по регулярным выражениям

У онлайн-академии IMBA вышел новый курс про контекстной рекламе. Основная фишка - обучение всем инструментам на реальных проектах: неполные брифы, сгоревшие дедлайны и минусовой бюджет. Страшно? Зато вы станете практикующим специалистом уже в процессе обучения и не потратите свое время зря!

Вы научитесь не по видео-лекциям, а на практике работать в рекламных кабинетах, запускать, анализировать и повышать эффективность рекламных кампаний. Изучите изнутри ключевые сервисы аналитики: Яндекс. Метрики, Google.Analytics , Roistat, Calltouch, Spywords, CoMagic.

По этой ссылке - https://bit.ly/3hR7CUq до 20 августа на курс действует скидка 35%, есть рассрочка 0% без первого взноса, а первые полгода можно учиться бесплатно. Все условия тут, обязательно почитайте

#реклама

Простой пример использования fetchall

Коллеги, fetch в скриптах Таблиц - один запрос к веб-странице, fetchall - сразу пачка.

Сегодня мы для вас набросали простой пример использования этого инструмента.

Какую решали задачу: в Таблице список ссылок страницы одного типа. Нам нужно открыть каждую и выдернуть из кода страницы регуляркой ссылку на изображение и вставить результат в Таблицу.

Пара нюансов ниже
1) когда формируем массив запросов – обязательно добавляем к каждому muteHttpExceptions: true, чтобы из-за недоступности одного у нас не упала пачка запросов целиком
function create_rqsts(urls) {
 return urls.map(row => isURL(row[0]) ? { url: row[0], muteHttpExceptions: true } : '')
   .filter(t => t)
}

2) собственно запрашиваем всё
function fetch_all_all_all(req) {
 return response = UrlFetchApp.fetchAll(req);
}

3) каждый ответ обрабатываем регуляркой, достаём нужное
function match_all_all_all_all(array) {
 return array.map(page => {
   let m = page.toString().matchAll(d.regExp);
   return 'https://adv-map.com' + [...m]?.[1]?.[1] || '-'
 })
}

4) пересобираем исходный массив ссылок, на место валидных ссылок вставляем полученный из пункта 3 результат
function redesign(urls, data) {
 return urls.map((h, i) => [isURL(h[0]) ? data[urls.slice(0, i + 1).filter(f => f[0]).length - 1] : '-']);
}

Код в пастебин
Таблица с примером

Документация fetch, fetchall

Что важно знать о больших данных в бизнесе

Хотели бы эффективнее работать с клиентами, увеличить доход компании и отладить бизнес-стратегию? Присоединяйтесь к онлайн-лекции в Лектории Skillbox, как грамотно работать с большими данными.

💬 Эксперты на примере реальных кейсов расскажут, как эффективно интегрировать Big Data в бизнес.

🗂Узнаете, как монетизируются данные.
🗂Разберетесь, как развивать бизнес за счет использования Big Data.
🗂Поймете, как с их помощью управлять рисками работы с клиентами.
🗂Рассмотрите правовой аспект работы с данными.

Регистрируйтесь и узнайте больше об аналитике данных 👉  https://clc.to/UINZWg

#реклама

​​Проверка данных (Data validation) с формулами

Если вам не хватает готовых решений в проверке данных (проверка на числа, даты, вхождение символа/слова в текстовую строку, соответствие адресу электропочты) - пользуйтесь вариантом "Ваша формула" и создавайте свои собственные правила проверки.

Принцип здесь такой же, как и с использованием формул в условном форматировании: вы пишете формулу для первой ячейки диапазона, к которому применяете правила, и используете абсолютные и относительные ссылки ($). Представляйте, что вы вводите формулу прямо в первую ячейку диапазона проверки и потом будете ее "протягивать". Проверка данных будет запрещать ввод везде, где формула будет возвращать ЛОЖЬ / FALSE.

Примеры правил
Разрешить вводить значение только один раз:
=СЧЁТЕСЛИ($A$2:$A;A3)<=1
Проверка будет ругаться, если введенное значение уже встречалось в столбце A.

Разрешить вводить только рабочие дни:
=ДЕНЬНЕД(A2;2)<6
Проверка будет ругаться, если номер дня недели у введенной даты - 6 или 7, то есть это суббота или воскресенье. Напомним, что у ДЕНЬНЕД / WEEKDAY нужно указать второй аргумент = 2, именно такой тип нумерации соответствует привычному для нас (где понедельник = 1).

Разрешить вводить текст не длиннее 8 символов:
=ДЛСТР(A2)<=8
Здесь проверка будет ругаться, если вы ввели более 8 любых символов.

Таблица с примерами
(создать копию)

Уникализатор / пользовательская функция от нашего канала

Привет! Работает так: передаёте в функцию диапазон и указываете номер столбца с ключом, а функция:

1) возврат вам только по одной строке с каждым ключом;
2) эти строки будут "максимально обогащенными" - функция оставит в каждой ячейке строки последнее непустое значение по ключу строки (смотрите что получилось на скриншоте, ключи - Пушкин и Гумилёв);
3) на входе отфильтровываются все #N/A и #REF;

Функция текстом:
function unique_googlesheets(arr, key) {
 key = key - 1;
 let o = {};

 arr.map(row => row.map(cell => ['#REF!', '#N/A'].includes(cell) ? '' : cell))
   .forEach(y => {
     if (!(y[key] in o)) {
       o[y[key]] = y;
     } else {
       o[y[key]] = o[y[key]].map((h, i) => y[i] || h)
     }
   })
 return Object.values(o);
}

Таблица с примером

★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat
★ Избранные посты нашего канала: t.me/google_sheets/418

Интересных и востребованных специальностей много. Чтобы не ошибиться с выбором и найти свою, пройдите бесплатный тест на профориентацию 👉 https://clc.to/v5lWTQ.

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

Тест займёт всего 10 минут, в итоге вы получите список digital-профессий и курсов, которые помогут вам понять, в какой сфере сможете сделать карьеру.

Готовы узнать результаты? 😉

#реклама

Выдёргиваем аргументы из функций QUERY, IMPORTRANGE, HYPERLINK

Функция FORMULATEXT - извлекаем текстовую строку с формулой

Эта функция выдает вам формулу из ячейки в виде текста. Единственный аргумент – ссылка на ячейку с формулой.

Может пригодиться, чтобы вытащить что-нибудь из формулы. Когда у нас есть текст и нам нужно получить из него фрагмент (например, ссылку из функции ГИПЕРССЫЛКА / HYPERLINK или запрос из QUERY), можно дальше использовать и обычные текстовые функции типа ПСТР / MID, ПОИСК / SEARCH и ДЛСТР / LEN, чтобы с теми или иными костылями извлечь нужное, и регулярные выражения в функции REGEXEXTRACT.

Например, если нам  нужно вытащить ссылку из ГИПЕРССЫЛКИ:
=REGEXEXTRACT (FORMULATEXT(ссылка на формулу) ; """(.+?)""" )

FORMULATEXT выдаст текст с формулой из ячейки, а REGEXEXTRACT - извлечет текст из первых кавычек.

А если нужно вытащить из IMPORTRANGE ссылку на источник и превратить ее в кликабельную ссылку в отдельной ячейке:
=HYPERLINK(REGEXEXTRACT(FORMULATEXT(ссылка на формулу) ; """(.+?)""") ; "Ссылка")

Здесь достаем текст формулы из ячейки с IMPORTRANGE (это делает FORMULATEXT), затем из этой формулы достаем ссылку на исходную таблицу (это делает REGEXEXTRACT) и затем превращаем ее в кликабельную (HYPERLINK) и не показываем в ячейке саму ссылку, а только слово "Ссылка" (последний аргумент HYPERLINK).

Какие у вас идеи / примеры применения FORMULATEXT? Что бывает необходимо доставать из формул вам?

Ссылка на примеры
Сделать копию

P.S. В Excel эта функция называется Ф.ТЕКСТ на русском и FORMULATEXT на английском

Пользовательские списки в Excel

Сегодня — небольшой совет для пользователей Excel.

Скорее всего, вы знаете, что можно ввести, например, “Январь” в Excel, потянуть мышкой за маркер в правом нижнем углу ячейки — и список месяцев продолжится. Excel знает порядок месяцев на русском только потому, что есть такой встроенный список — но мы можем добавлять и другие, свои списки. Допустим, у вас в компании есть список из городов/филиалов/фамилий — да чего угодно, что нужно вводить списком и в определенном (не обязательно алфавитном) порядке:

Москва
Санкт-Петербург
Казань
Саратов
Екатеринбург

Следуем по адресу:
Файл - Параметры - Дополнительно - Изменить списки
File - Options - Advanced - Edit Custom Lists

Вводим свой список и нажимаем Добавить / Add.
Все: можно ввести в ячейку первое значение списка и потянуть мышкой.

Таких полезных мелочей в Excel очень и очень много. О десятке (а скорее всего и больше) поговорим уже 5 октября во вторник на открытом уроке в МИФе. Но об этом уже в следующем посте :)

Открытый бесплатный урок курса “Магия Excel” — 5 октября

Итак, друзья, 5 октября в 12:00 мск будет “нулевой” урок курса “Магия Excel” (это второй поток — первый был летом и средняя оценка учеников по итогам была 4.9 / 5).

Будет много пользы (темы вебинара — интерфейс Excel, инструменты для быстрого ввода данных, форматы чисел, включая пользовательские), возможность оценить формат курса, подачу материала, если думаете о том, чтобы пойти учиться, задать любые вопросы.

Записаться на открытый урок можно на странице курса (там же — программа и все детали)
https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Буду рад вас видеть!
Ренат (а также его ассистенты на этом курсе Штрудель и Лемур)
Если есть любые вопросы по курсу, хотите посмотреть больше примеров обратной связи по домашкам, уточнить что-то по программе — буду рад ответить, пишите @r_shagabutdinov.

Знание Excel и Google-таблиц нужно во многих профессиях. Вы бухгалтер или экономист — нужно делать расчеты и проектировать отчеты. Маркетолог — нужно планировать продажи и сравнивать эффективность рекламных кампаний. Также таблицы нужны менеджерам, предпринимателям и даже медсестрам! Так что в какую профессию ни ткни — везде пригодится. Научитесь работать с таблицами на базовом и продвинутом уровнях всего за 4 месяца с курсом от Skillbox!

Все подробности по ссылке: 👉 https://clc.to/lUQXVA

После обучения ваше резюме пополнится такими ключевыми навыками:
➕ создание диаграмм и спарклайнов;
➕ создание макросов для VBA;
➕ работа с функциями проверки данных;
➕ прогнозирование различных показателей;
➕ умение быстро фильтровать большие массивы;
➕ работа с функциями проверки данных;
➕ импорт и экспорт данных.

Скорее изучите все возможности Excel и Google-таблиц и увеличьте скорость своей работы: https://clc.to/lUQXVA

#реклама

Решим формулами задачу нашего подписчика Андрея

Дано:
На склад завезли 1000 шт. велосипедов. Стоимость хранения 1 шт - 3 рубля / день. Каждый день продается по 10 штук товара. Как посчитать итоговую стоимость хранения товара, к моменту, когда он кончится?

Решение 1
Если товар продаётся по 10 штук в день, то мы его продадим за 100 дней (1000 / 10), сформируем уменьшающийся склад (1000, 990, 980) за каждый день с помощью функции SEQUENCE:
SEQUENCE( 100 [количество строк] ; 1 [количество столбцов] ; 1000 [первое значение] ; -10 [шаг] )

Далее суммируем все строки и умножим на стоимость хранения товара — 3 рубля / шт / день. Итоговая формула:
=СУММ( SEQUENCE(1000/10;1;1000;-10) ) * 3

(на скриншоте a6:a и b6)

Решение 2
Вариант от Михаила Смирнова, без создания массива со складом за каждый день. Сумма арифметической прогрессии и остаток:
=3 * ((10 + MOD(1000; 10) + 1000) * INT(1000 / 10) / 2 + MOD(1000; 10))

(на скриншоте d5:d7)

Таблица с примером

Наш чат, не заходите: @google_spreadsheets_chat

Письмошная — выгрузка файлов и писем по фильтрам

Друзья, сегодняшний пост – коллаборация нашего канала и Дамира Х., передаём слово автору:

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

На листе настройки – прописываем свои фильтры в зелёных ячейках.

На листе "Отчет по выгрузке" будут сохраняться письма, ссылки на скачанные файлы и на папки к ним.

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

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

Таблица (делайте копию)

Скрипт отдельно в pastebin

==
★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat

Загружаем из своей почты 20 xlsx-файлов с помощью письмошной →

Итак, пример использования утреннего скрипта: в моей почте 20 писем с темой HP REPORT, в каждом письме – xlsx-файл.

Чтобы выгрузить текст писем в Таблицу, а все файлы на Google Диск —

1) делаем копию нашей Таблицы (если не скопировали еще);
2) заполняем только тему, все остальные фильтры не заполняем;
3) запускаем скрипт!

😮😎

★ Наш чат: @google_spreadsheets_chat

Вакансия Middle SEO Specialist для тех, кому надоело заниматься чем попало и хочется сосредоточиться на стратегических задачах одного бренда.

Что будет:

📍 задачи в рамках продвижения в Google сайта одного глобального бренда  — увидишь, как все устроено изнутри;
📍 бó‎льшие бюджеты и свобода действий, чем на обычных проектах;
📍 Outreach/Linkbuilding/Semanctic специалисты в команде;
📍 собственные продукты для SEO-специалистов;
📍 скрипты для автоматизации — тебе доступны наработки команды и отдела, а также полная свобода создавать свои решения под задачи проекта.
📍 стартовые условия $1000-1800 и welcome-бонус $2000.

Чего не будет: эмодзи ракеты в этом посте и рутины каждый день, потому что она передается Junior’ам.

Агентство Netpeak ищет Middle/Senior SEO Specialist для работы над одним проектом, который любит четкость в рабочих процессах, готов заниматься стратегией продвижения, искать точки роста и тестировать свои гипотезы.

Оставляй заявку в гугл-формев гугл-форме📱или на сайтеили на сайте

#реклама

ПЕРЕМЕЩАТОР — продолжаем показывать магию с Google Диском

Привет, недавно в наш чатик пришли с вопросом – есть список ссылок на файлы на Google Диске, как каждый из них скопировать в 10 папок?

Наш сегодняшний скрипт отвечает на этот вопрос, а еще он может не только копировать, но и перемещать файлы.

Инструкция, как пользоваться:
1) копируйте Таблицу со скриптом себе;
2) заполняйте URL файла и URL папки для перемещения (столбцы A и B);
3) выбирайте: копируем или перемещаем (столбец C);
4) запускайте скрипт из меню с 🔥 (при первом запуске скрипт попросит авторизацию – это нормально, этим вы разрешаете вашему скопированному скрипту обратиться к вашему диску – разрешаем);

Скрипт запустится и скопирует / переместит файлы исходя из настроек, попутно заполнит информацию в Таблице (название файла, название папки, результат и время), если вы запустите скрипт еще раз – скрипт обработает строки только без результата copy / move done.

Да и чтобы копировать файлы - у вас должен быть доступ и к файлу и к папке назначения 😉

Перемещатор (посмотреть на код в pastebin)

==
★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat

Как стать успешным бизнес-аналитиком и какие навыки для этого нужны? Зарегистрируйтесь на бесплатный интенсив по бизнес-аналитике: 👉 https://clc.to/yVO74g.

За 3 дня вы:
— познакомитесь с профессией бизнес-аналитика;
— изучите инструменты аналитика;
— изучите основы бизнес-аналитики;
— выполните конкурсное задание.

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

Всех участников ждут подарки — доступ в Telegram-чат с полезными материалами, электронная книга «Век живи — век учись» Кей Петерсон и Дэвида Колба и сертификат на 10 000 на любой курс Skillbox каждому, кто выполнит простое домашнее задание!

Подключайтесь к прямым эфирам 11–13 октября в 19:00 по московскому времени!

#реклама