Google Таблицы

ВПР / VLOOKUP со звездочкой

Друзья, хотим рассказать/напомнить про возможность применения символьных шаблонов в функции ВПР.

Мы неоднократно рассказывали про применение * (любое количество любых символов, в том числе и нулевое, то есть ничего) и ? (любой один обязательный символ) в функциях семейства СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН), СЧЁТЕСЛИ(МН).

Но их можно использовать и в ВПР. Если мы хотим найти не ячейку с точным совпадением с искомым значением, а ячейку, соответствующую определенной маске, и тут пригодятся звездочка и знак вопроса.

Например, такая функция ВПР будет искать ячейку, которая начинается со слова "Google" и вернет данные из второго столбца таблицы

=ВПР("Google*";Таблица;2;0)

А такая — ячейку, в которых будет встречаться слово iPad с любыми тремя знаками после этого слова через пробел и отделенных пробелом от дальнейшего текста (то есть iPad Pro и IPad Air подойдут, а iPad 10.2 не подойдет)

=ВПР("*ipad ??? *";Таблица;2;0)

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

P.S. А заодно напомним про РЕГИСТР — он в функции ВПР не учитывается. Даже если в функции будет ipad или iPad, а в таблице для поиска IPAD — все найдется.

Про символьные шаблоны:
Символьные шаблоны в функциях СЧЕТЕСЛИ, СУММЕСЛИ
Шпаргалка по символьным шаблонам

Еще посты про ВПР:
– ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
– ВПР (VLOOKUP) по нескольким условиям
– ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
– ВПР по нескольким диапазонам
– ВПР с интервальным просмотром = 1
– Перекрестный ВПР (ищем по строке и заголовку)
– Видео про функцию ВПР в Google Таблицах

15.5K views08:10

11.4K views06:48

Сравнение текстовых строк по символам и их кодам

Зачастую одни и те же текстовые данные воспринимаются Excel или Таблицами как разные, если они из разных источников (например: одно и то же название товара в выгрузке из 1С и в ваших таблицах будет разным из-за того, что там отличаются... пробелы! Пробелы бывают разными, увы).

Внешне это понять сложно - нужно смотреть на код символа. Его можно определить с помощью функции КОДСИМВ / CODE. Если у внешне одинаковых символов отличаются коды, значит, для Таблиц это разные символы и текстовые ячейки с ними будут тоже считаться разными.

Ну а чтобы достать определенный символ из текста, нужна ПСТР / MID.
Если мы хотим сразу разбить текст по символам одной формулой, сделаем формулу массива, и в качестве второго аргумента ПСТР (какой по порядку символ извлекать) укажем функцию SEQUENCE, которая создаст массив от единицы до числа, соответствующего количеству символов в ячейке (его находим с помощью ДЛСТР / LEN):

=ArrayFormula(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1))

Чтобы получить массив кодов, а не сами символы, добавим сверху КОДСИМВ:

=ArrayFormula(КОДСИМВ(ПСТР(A1;SEQUENCE(ДЛСТР(A1);1;1;1);1)))

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

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

14.8K views06:48

ОТПРАВЛЯТОР 2.0
Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию

Друзья, привет! Выпускаем вторую версию решения для отправки ваших данных в Телеграм.

В первой версии скрипт создавал простое PNG-изображение из диапазона данных, теперь же мы создаём полноценный PDF или JPG файл.

Таблица полностью готова к работе, просто заполните правила отправки и введите токен своего Телеграм бота.

Таблица со скриптом (инструкция – внутри!)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

19.2K viewsedited 07:49

0:20

FLATTEN2, что говорит лисичка и другие неописанные в справке функции Google Таблиц

Друзья, привет! Сегодня у нас прекрасная статья от Михаила Смирнова – он рассказывает, как нашел ряд неописанных в справке функций.

FLATTEN 2: другие недокументированные функции Google Sheets

Не про все функции понятно, что они делают, в справке же их нет :) поэтому пробуйте и предлагайте свои варианты в чат: @google_spreadsheets_chat

==
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

24.4K viewsedited 07:11

Отбираем три последних строки по имени и считаем среднее (ниже будет сложно!)

Представьте – вашим сотрудникам ставят оценки и вам нужно посчитать среднее по каждому сотруднику из его последних трёх оценок.

На скриншоте – разбор такой формулы.

В её основе (пункт 1 на скриншоте) – функция COUNTIFS / СЧЁТЕСЛИМН в массиве, она добавляет к диапазону виртуальный столбец с нумерацией по ключу, начиная от последнего элемента (у нас ключ – только имя, но при необходимости вы сможете использовать несколько ключей, просто добавив по аналогии условия в COUNTIFS). Подробнее про принцип работы формулы нумерации здесь.

Вторая часть (пункты 2-3 на скриншоте) – обработка получившего массива с новым столбцом внутри QUERY – оставляем последние три вхождения, группируем и считаем среднюю оценку.

Таблица поближе

Знаете, как решить по другому - пишите в комментариях :)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

12.9K viewsedited 08:36

Встраиваем видео в Google Таблицы

В Таблицах можно воспроизводить видео со звуком. Как это можно использовать? К примеру, вы сможете положить несколько видео-инструкций для своих коллег в Таблицу.

Чтобы видео заиграло – создаём скриптами модальное окно с emded-ссылкой на видео.

Код очень простой:
Код скрипта в pastebin
Таблица со скриптом

=
Как заставить решение работать:
1) копируете Таблицу
2) открываете редактор скриптов в ней > триггеры > создаёте триггер на редактирование Таблицы, выбираете функцию onEditTrig и сохраняете
3) готово – скрипт будет запускаться из Таблицы, после того, как вы активируете один из чекбоксов

За идею и реализацию спасибо @Lev_Andreevich 🦁

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление

13.5K viewsedited 07:25

10.9K views08:01

SEQUENCE — выводим заголовки с датами текущего месяца

И снова о прекрасной функции SEQUENCE, которая, кстати, появилась и в Excel — пока только для подписчиков Office 365 (на русском — ПОСЛЕД).

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

Аргументы функции:
число строк, число столбцов, первое значение и шаг.

Например, такая функция выведет столбец с числами от 1 до 10000:

=ПОСЛЕД(10000;1;1;1)

Так как даты — это числа, то можно выводить и их.
Допустим, нам нужны все даты текущего месяца в строку. Понадобится такая конструкция:

=ПОСЛЕД(1 строка; число дней в месяце; дата начала месяца; шаг=1)

Первый день текущего месяца можно получить так (текущий год + текущий месяц + первое число):

=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

А количество дней в месяце — так:

=КОНМЕСЯЦА(СЕГОДНЯ();0)

Остается все собрать в одну конструкцию. И как тут не напомнить про то, что в строке формул можно использовать пробелы и переход на следующую строку (Alt+Enter), чтобы делать длинные формулы проще для восприятия.

Конечно, внутри функции ДАТА можно подставить любой месяц вместо текущего, и тогда будут выведены даты этого месяца (см ГИФ)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

13.9K viewsedited 08:01

0:35

🧞‍♂️Превращатор Листа Таблицы в xlsx / pdf / csv
— файл мгновенно скачивается
— либо сохраняется на Google Диск

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

Если конвертируете в xlsx – скрипт вставит все формулы на листе как значения, чтобы ячейки, которые ссылаются на другие листы не потеряли своих значений.

Как установить:
Открываете вашу Таблицу, Инструменты > Редактор скриптов, вставляете код: pastebin.com/iuKKSFrM, сохраняете и перезагружаете Таблицу. После перезагрузки в Таблице появится меню "🧞‍♂️ – скрипты".

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

16.3K viewsedited 08:22

Извлекаем из массива фразы, даже если они написаны с ошибками

Привет! У нас есть чат, в него можно приходить с вопросами. Недавно туда пришла Марина и спросила:

Например, у меня есть 200 фраз. Среди них есть фразы со словами крем, глаза, ретинол. Мне их нужно найти в этом списке. При этом часть этих слов может быть написана с ошибками, типа крэм, ритинол и пр. И мне нужно вытащить все фразы с этими словами, в том числе и с ошибками.

Марине помог @vitalich, показав, как с помощью =FILTER и простых регулярных выражений можно вытащить всё, что нужно.

На скриншоте: формула в ячейке C2 берет регулярное выражение из ячейки C1 и выводит в C3 и ниже все, что ему соответствует (cre одна любая буква m).

Таблица

=
📗ОГЛАВЛЕНИЕ КАНАЛА, всё оглавление / категории
☺️ ДОНАТЫ: реквизиты

12.9K viewsedited 07:22

10.8K views09:33

Функция ВЫБОР и номер квартала
А еще про то, как организовать данные правильно

Допустим, в таблице есть данные за 12 месяцев, и на каждый из них приходится несколько столбцов (e.g. факт, прогноз, отклонение и т.д.)

Наша задача — суммировать данные за текущий квартал.
Сначала определим номер квартала по текущему месяцу:

ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3

Эта история станет первым аргументом функции ВЫБОР / CHOOSE.
А все последующие аргументы — что возвращать, если первый аргумент равен 1 (первый квартал), 2 и так далее:

=ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3; суммируем данные по 1 кварталу; суммируем по 2; ...; по 4)

В

качестве аргументов функции ВЫБОР могут быть другие функции — то есть мы выбираем их списка функций в зависимости от значения первого аргумента:

=ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3;0);СУММ(D3;G3;J3);СУММ(M3;P3;S3);СУММ(V3;Y3;AB3);СУММ(AE3;AH3;AK3))

Можно

было поступить и иначе — СУММ оставить снаружи, а внутри функции ВЫБОР собирать нужные ячейки в массивы.
=СУММ(ВЫБОР(ОКРУГЛВВЕРХ(МЕСЯЦ(СЕГОДНЯ())/3;0);{D3;G3;J3};{M3;P3;S3};...;...))

Эту задачу наверняка можно решить и другими способами (например, горизонтальный FILTER для отбора столбцов с фактом и потом нужных месяцев?) — предлагайте варианты в комментариях!

Таблица с формулой

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

Во-первых, в верхней строке месяцы введены без года в текстовом формате. Вспомните ли вы в следующем году, когда зайдете в Таблицу, какой это год? Что будете делать, когда появится январь следующего года? Будь это даты, их было бы проще обрабатывать формулами в будущем.

Во-вторых, одна объединенная ячейка с названием месяца на три столбца (факт, прогноз, откл). Такой подход усложнит работу с формулами. Придется выдумывать костыли, вроде разных диапазонов для функции FILTER или формулы, которая сначала соберет полную строку с месяцами, а потом с использованием этой строки вы сможете отфильтровать данные.

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

=
📗ОГЛАВЛЕНИЕ КАНАЛА, всё оглавление / категории
☺️ ДОНАТЫ: реквизиты

13.5K viewsedited 09:33

0:09

✔️☑️ ПРОСТЫЕ СОВЕТЫ ПРО ФЛАЖКИ (ОНИ ЖЕ ЧЕКБОКСЫ)

1) включаем / выключаем флажки массово:
выделяем мышкой диапазон и пробел

2) выключаем флажки через скрипты, clearContent():
const sheet = SpreadsheetApp
.getActive()

.getSheetByName('Лист1');

sheet.getRange("a1:a10").clearContent();

или вставляем в диапазон false или 'false':


sheet.getRange("a1:a10").setValue(false);

3) включаем флажки, вставляем в диапазон true или 'true':


sheet.getRange("a1:a10").setValue(true);

4) и классические варианты включения / отключения: check() / uncheck():

sheet.getRangeList(["c3:c9","c11:c20"]).uncheck();

P.S. пасхалка про чекбоксы от почётного модератора нашего чата @IT_sAdmin :)

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

14.5K viewsedited 09:02

0:09

Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц).

Недавно в нашем чате спросили – а как запускать скрипты, если у меня ведройд.

Автор вопроса имеет ввиду, что у него телефон на базе платформы Android c приложением Таблицы и там в Таблицах не работают скрипты, привязанные к кнопкам и не видны меню скриптов.

Но способ запускать скрипты есть:
1) создаём функцию starter() с параметром e (объектом события), эта функция сможет определить ячейку / лист, которые редактируются сейчас.

В функции – ЕСЛИ, проверка – редактируется ли ячейка на Листе1 ячейка А1. Если истина – запускаем нашу целевую функцию hellogirls().

function starter(e) {
  if (e.source.getActiveSheet().getName() == 'Лист1' && e.range.getA1Notation() == 'A1') {hellogirls();}

}

2) Ставим функцию starter() на триггер (редактор скриптов > триггеры > добавление триггера > starter > при редактировании Таблицы);

3) Профит! Теперь при изменение чекбокса на Лист1 запускается функция hellogirls(). В том числе с мобильного устройства!

14.1K viewsedited 06:48

СОБИРАТОР 4.0

В прошлом году мы выпустили свой СОБИРАТОР – интерфейс для сбора других Таблиц через sheets api (самый быстрый скриптовый способ).

Задаёте правила сбора Таблиц в другие Таблицы и собираете, к примеру, 50 Таблиц ваших продавцов в одну сводную Таблицу и 30 Таблиц в другую сводную Таблицу, на лету фильтруя и оставляя только нужные столбцы.

Сегодня выпускаем обновление:
— теперь можно указать частоту загрузки каждой таблицы в минутах;
— можно выбрать тип копирования данных (formula / formatted_value / unformatted_value);
— решена проблема с добавлением новых данных: раньше они (иногда) вставлялись со сдвигом;

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

СОБИРАТОР

ИНСТРУКЦИЯ

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

20.2K viewsedited 07:46

13.1K views09:01