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 Таблицах

Наше приложение: Эвотор + Google Таблицы

Друзья, привет! С середины прошлого года мы занимаемся созданием своего приложения для синхронизации онлайн-касс «Эвотор» и таблиц.

Судьба у приложения получилась непростой: сменилось 3 разработчика, но сейчас всё отлично — приложение готово и выложено в магазин Google.

Полезные функции:
– чеки вашего бизнеса загружаются в таблицу в «плоском формате» (одна позиция в чеке — одна строка)
– загрузка происходит автоматически каждый час
– чеки подставляются в отчёты, сейчас их три (общий отчёт по продажам / топ товаров / топ товаров по магазинам)
– есть модуль отправки данных в «Телеграм» — сможете настроить регулярную отправку диапазонов Таблиц по расписанию

Приложение: https://workspace.google.com/marketplace/app/%D0%BE%D0%BD%D0%BB%D0%B0%D0%B9%D0%BD_%D0%BA%D0%B0%D1%81%D1%81%D1%8B/978990150260

Для вопросов и покупки: @namokonov

ВЫГРУЖАТОР: СОЗДАЁМ РЕЕСТР ФАЙЛОВ ИЗ ПАПКИ, ВЛОЖЕННОСТЬ – УЧИТЫВАЕТСЯ

Выпускаем вторую версию скрипта, с помощью которого вы сможете выгрузить реестр файлов в Таблицу. Теперь поиск идёт и по вложенным папкам.

КАК РАБОТАЕТ:
– копируйте Таблицу с кодом;
– вводите ID или URL папки начала поиска в ячейку B1;
– запускайте скрипт из меню [СКРИПТЫ];

Если в B1 напишете root – то выгрузится весь диск, это сработает только для небольших дисков, < 10 000 файлов, из-за максимальной продолжительности выполнения скрипта в 6 минут.

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

ТАБЛИЦА С КОДОМ

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

Функция DETECTLANGUAGE в Google Таблицах: определяем язык текста в ячейке.

Кейс: автоматом формируем ссылку на нужный интернет-магазин

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

Функция ВЫБОР/CHOOSE в Excel и Google Таблицах: выбираем случайный элемент из набора

Функция ВЫБОР возвращает одно из значений, перечисленных в ее аргументах, по порядковому номеру. И этими аргументами могут быть как значения, так и ссылки на диапазоны. Так что с помощью нее можно и сгенерировать случайное значение из набора, и, например, "выбирать", из какой таблицы ВПР-ить данные. Пример здесь.

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

Достаём данные из ЮТУБ / ТЕЛЕГРАМ / VC / Т—Ж / ХАБР в Таблицу

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

Перед ним встала задача доставать просмотры, лайки и комментарии всех этих материалов.

Для этого он создал Таблицу "LynxReport: yчёт публикаций" и для каждого ресурса написал скрипт. Ютуб парсится с помощью API, все остальные сайты - fetch страницы и последующий match строки по регулярному выражению.

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

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

ИТОГИ 2020

оглавление с категориями для простого поиска

Друзья! За 2020 год мы написали на наш канал 90 постов, сумма их просмотров ~ 1 100 000.

Чтобы вам было проще с этим разобраться – мы разбили посты на категории и вывели их на лист "2020" в оглавлении нашего канала.

Категории такие:
– готовые решения
– телеграм боты
– формулы
– гостевые статьи
– видео
– форматирование

Посты внутри категорий отсортированы по просмотрам, от больших к меньшим.

Мы поздравляем вас с наступающими праздниками, берегите себя.

Прокачивайтесь в Таблицах, с помощью них вы сможете строить отличные системы отчётности и существенно упростите рутину в деле, которым занимаетесь 🎄

ОГЛАВЛЕНИЕ 2020

гифка к посту (для тех, кто не очень понял, как работает штука выше):

- вводим ссылки на таблицы и названия листов в интерфейс

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

- копируем собранную формулу и вставляем её на новый лист

🚜 Собиратыр-тыр-тыр 🚜

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

Каждая IMPORTRANGE дополнительно делится на несколько частей, по строкам (кажется, что так можно преодолеть / отсрочить ошибку импорта).

Инструмент подготовил активный участник нашего чата Михаил С. – спасибо ему 😎🤟

видео
статья про инструмент

САМА ТАБЛИЦА (файл - создать копию)

Новый онлайн-курс по Excel

Друзья, привет! На связи Ренат, рад сообщить, что закончил запись нового онлайн-курса по Excel для Skillbox.
Напомним, что и ранее бОльшая часть курса была записана нами: Google Таблицы + макросы в Excel (Ренат) и скрипты в Google Таблицах (Женя).

А теперь я обновил курс по Excel — с учетом всех новшеств, которые появились в Excel 2016, 2019 и в Office 365:
- Динамические массивы и соответствующие функции (УНИК, ФИЛЬТР, СОРТ, ПОСЛЕД (SEQUENCE по-нашему, гугло-табличному) и прекрасная СОРТПО — да, они теперь есть и в Excel! Правда, пока не у всех).
- Новые диаграммы - Парето, каскадная (теперь без ухищрений), картограмма, воронка, древовидная. И старые тоже. И (уже старые) спарклайны тоже.
- Power Query и Power Pivot — для чего нужны эти надстройки (которые уже и не надстройки, а часть Excel), что умеют, как импортировать данные из разных источников и связывать. 3D-карты в Power Map.
- Вся классика на месте: основы, форматы числовые и стилевые, пользовательские, условное форматирование простое и формулами, сводные, “умные” таблицы (термин принадлежит Николаю Павлову, официально это просто “таблицы”), как выводить таблицы на печать, даты и время, логика, суммирование и подсчет, расширенный фильтр и функции баз данных (мощная штука, о которых не все знают — мы писали о них однажды), поиск решения и многое другое.

Теперь названия всех функций в курсе дублируются на русском и английском, а горячие клавиши — называются и для Win, и для Mac. А также я называю, в каких версиях Excel появилась рассматриваемая функция/инструмент.

Всего — почти 80 уроков в 20 модулях (это только Excel без Google Таблиц и макросов) плюс текстовые материалы для тех, кто захочет повторить материал модуля и/или лучше воспринимает текст, файлы примеров, ДЗ и дополнительные текстовые материалы.

На момент поста на курс в Skillbox действует скидка 55% (скидка может меняться).
Программа и запись на курс

Подписка на регулярную поддержку канала (от 5$/ месяц)

Друзья, в январе будет четыре года, как мы рассказываем вам на нашем канале про Google Таблицы и про Скрипты.

Нашему чату около трёх лет, сегодня там почти 2500 участников и многие из них прошли путь от "не умею ничего в таблицу" к "специалист, к которому выстраивается офис в очередь"

Сегодня мы создали свою страницу в patreon – в ней вы сможете оформить ежемесячную поддержку нашего сообщества.

Внутри три уровня подписки (5, 10 и 20 долларов) и небольшие бонусы, которые доступны на каждом из них, включая "секретную опцию" 🙂

Подписка на регулярную поддержку канала: patreon.com/google_sheets

Поддержать канал можно по реквизитам

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

Я сразу влюбился в книгу на Франкфуртской книжной выставке, когда мне показали эту новинку. И в оформление, и в содержание. Чутье не подвело — впоследствии книга стала номером 1 по статистике в Великобритании (а на Амазоне у нее уже более 1000 оценок!).

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

Как мне кажется, книга занимает уникальную нишу: между учебниками и совсем популярными книгами (как замечательная и горячо любимая мной, но все же очень популярная “Голая статистика” Уилана). То есть это и очень интересно, и доступно, и в то же время достаточно, чтобы понять, научиться и начать применять.

Повествование - по основным темам и показателям. А строится оно через примеры вопросов, на которые помогает отвечать статистика. Вот лишь несколько из них:
- Каковы закономерности роста мирового населения за последние полвека?
- Уменьшают ли статины риск инфарктов и инсультов?
- Добавляет ли близость к супермаркету Waitrose 36 тысяч фунтов к стоимости вашего дома?
- Действительно ли в некоторых регионах Великобритании смертность от колоректального рака в три раза выше?
- Кто оказался самым везучим пассажиром на «Титанике»?

Ловите промокод для читателей нашего канала на 15% скидку до конца января (и на бумагу, и на электрическую книгу): STATART
https://www.mann-ivanov-ferber.ru/books/iskusstvo-statistiki/

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

одной формулой соединяем листы, добавляя название листа к каждой строке

ребята, привет! сегодня простой пример, вдохновлён утренним вопросом подписчика Сергея

представьте: несколько листов с данными, листы в одном формате, как всё это объединить?

поехали:
1) пишем формулу для одного листа
главный трюк – добавляем в select название листа и QUERY выведет его для каждой отобранной строки

QUERY('П1'!A2:D;"select 'П1', A, B, C, D where B is not null label 'П1' ''";0)

label 'П1' '' необязателен, используется, чтобы убрать строку заголовка

2) обрабатываем N/A
добавляем функцию IFERROR, чтобы вернуть правильное число пустых ячеек в случае, если условию отбору ничего не будет соответствовать:
=IFERROR(QUERY('П1'!A2:D;"select 'П1', A, B, C, D where E is not null label 'П1' ''";0);{""\""\""\""\""})

3) объединяем
пишем формулу для каждого листа и объединяем с помощью {}, подробнее про объединением диапазонов писали здесь

на скриншоте - получившаяся формула, а еще она в таблице

==
🔪 НАШ КУРС НА
SKILLBOX (Таблицы и скрипты, Excel и VBA)
📘 Канал: @google_sheets
📕 Оглавление канала
📗 Чат: @google_spreadsheets_chat

СКРИПТАМИ ОПРЕДЕЛЯЕМ, КОГДА ОСВОБОДИТСЯ ДОМЕН (кратко)

1) формируем ссылку: адрес сайта для проверки + наш домен
2) переходим на неё и с помощью регулярки достаём дату, по которую домен оплачен
3) получилось – переходим к следующей строке, не получилось – пробуем другой домен для проверки

ТАБЛИЦА СО СКРИПТОМ

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

вот такие возможности у бесплатных таблиц + скриптов от google :)

(этот же пост подробнее)

==
🔪 НАШ КУРС НА SKILLBOX (Таблицы и скрипты, Excel и VBA)
📘 Канал: @google_sheets
📕 Оглавление канала
📗 Чат: @google_spreadsheets_chat

СКРИПТАМИ ОПРЕДЕЛЯЕМ, КОГДА ОСВОБОДИТСЯ ДОМЕН (развернуто)

как определить, когда освободится домен? – идём на https://who.is/whois/, вставляем в окно адрес нашего сайта и нажимаем на лупу

вы переместитесь на страницу с адресом https://who.is/whois/+тот сайт, который вы ввели и на странице появится информация, из которой нас интересует строка с paid-till (дата, по которую оплачено доменное имя)

всё понятно и самое главное – это легко можно провернуть скриптами таблиц и сразу для сотен доменных имён

мы создали таблицу с решением для проверки, она работает так:

в таблице в столбце A - доменные имена, которые нужно проверить

в столбце B – флажки, флажок стоит - проверяем, не стоит - догадайтесь :)

первая строка - сайты, на которых доменные имена будут проверяться (помимо who.is есть еще несколько)

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

и друзья, сегодняшнего поста не было бы без Дениса – спасибо ему

ТАБЛИЦА С РЕШЕНИЕМ

крутейшая таблица с подсказками про query

слово автору:

Привет, я Паша Мрыкин, эксперт по сквозной аналитике в Calltouch и не смотря на мою любовь к коробочным решениям - я люблю работать с данными в Google Таблицах.

Что вы сможете сделать с помощью этого файла:
1. Найти примеры применения QUERY в своей работе.
2. Подсмотреть примеры формул.
3. По знаку "?" увидеть объяснение для каждого из шагов. Рекомендую заглянуть в каждую подсказку, т.к. там могут быть объяснения неочевидных моментов.

> ТАБЛИЦА ПРО QUERY

==
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat