This media is not supported in your browser
VIEW IN TELEGRAM
СКРИПТАМИ ОПРЕДЕЛЯЕМ, КОГДА ОСВОБОДИТСЯ ДОМЕН (кратко)
1) формируем ссылку: адрес сайта для проверки + наш домен
2) переходим на неё и с помощью регулярки достаём дату, по которую домен оплачен
3) получилось – переходим к следующей строке, не получилось – пробуем другой домен для проверки
ТАБЛИЦА СО СКРИПТОМ
p.s. магия в том, что ссылок может быть несколько тысяч и они обработаются за несколько запусков скрипта
вот такие возможности у бесплатных таблиц + скриптов от google :)
(этот же пост подробнее)
==
🔪 НАШ КУРС НА SKILLBOX (Таблицы и скрипты, Excel и VBA)
📘 Канал: @google_sheets
📕 Оглавление канала
📗 Чат: @google_spreadsheets_chat
1) формируем ссылку: адрес сайта для проверки + наш домен
2) переходим на неё и с помощью регулярки достаём дату, по которую домен оплачен
3) получилось – переходим к следующей строке, не получилось – пробуем другой домен для проверки
ТАБЛИЦА СО СКРИПТОМ
p.s. магия в том, что ссылок может быть несколько тысяч и они обработаются за несколько запусков скрипта
вот такие возможности у бесплатных таблиц + скриптов от google :)
(этот же пост подробнее)
==
🔪 НАШ КУРС НА SKILLBOX (Таблицы и скрипты, Excel и VBA)
📘 Канал: @google_sheets
📕 Оглавление канала
📗 Чат: @google_spreadsheets_chat
одной формулой соединяем листы, добавляя название листа к каждой строке
ребята, привет! сегодня простой пример, вдохновлён утренним вопросом подписчика Сергея
представьте: несколько листов с данными, листы в одном формате, как всё это объединить?
поехали:
1) пишем формулу для одного листа
главный трюк – добавляем в select название листа и
2) обрабатываем N/A
добавляем функцию
3) объединяем
пишем формулу для каждого листа и объединяем с помощью
на скриншоте - получившаяся формула, а еще она в таблице
==
🔪 НАШ КУРС НА
SKILLBOX (Таблицы и скрипты, Excel и VBA)
📘 Канал: @google_sheets
📕 Оглавление канала
📗 Чат: @google_spreadsheets_chat
ребята, привет! сегодня простой пример, вдохновлён утренним вопросом подписчика Сергея
представьте: несколько листов с данными, листы в одном формате, как всё это объединить?
поехали:
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 по статистике в Великобритании (а на Амазоне у нее уже более 1000 оценок!).
Ну а оформление мы сохранили оригинальное в российском издании, она лишь немного выросла в размерах (да, текст на русском обычно больше оригинала процентов на 15!). Так что эта книга может быть очень хорошим подарком аналитику, маркетологу, руководителю, журналисту, студенту, молодому ученому и всем-всем-всем, кто занимается анализом и интерпретацией данных.
Как мне кажется, книга занимает уникальную нишу: между учебниками и совсем популярными книгами (как замечательная и горячо любимая мной, но все же очень популярная “Голая статистика” Уилана). То есть это и очень интересно, и доступно, и в то же время достаточно, чтобы понять, научиться и начать применять.
Повествование - по основным темам и показателям. А строится оно через примеры вопросов, на которые помогает отвечать статистика. Вот лишь несколько из них:
- Каковы закономерности роста мирового населения за последние полвека?
- Уменьшают ли статины риск инфарктов и инсультов?
- Добавляет ли близость к супермаркету Waitrose 36 тысяч фунтов к стоимости вашего дома?
- Действительно ли в некоторых регионах Великобритании смертность от колоректального рака в три раза выше?
- Кто оказался самым везучим пассажиром на «Титанике»?
Ловите промокод для читателей нашего канала на 15% скидку до конца января (и на бумагу, и на электрическую книгу): STATART
https://www.mann-ivanov-ferber.ru/books/iskusstvo-statistiki/
PS Друзья, в связи с обсуждением в комментариях уточним — в магазине издательства максимальная скидка (промокод + акции + ваша личная скидка по программе лояльности) не может превышать 50%. Поэтому в дни новогодней акции промокод может не добавлять существенной скидки. Но это лишь означает, что в данный момент книга и так доступна вам по минимальной цене. В другое время после акции промокод будет давать бОльшую скидку.
Друзья, сегодня хотел бы порекомендовать вам популярную книгу по статистике — таких в принципе единицы, а уж настолько качественные появляются совсем редко.
Я сразу влюбился в книгу на Франкфуртской книжной выставке, когда мне показали эту новинку. И в оформление, и в содержание. Чутье не подвело — впоследствии книга стала номером 1 по статистике в Великобритании (а на Амазоне у нее уже более 1000 оценок!).
Ну а оформление мы сохранили оригинальное в российском издании, она лишь немного выросла в размерах (да, текст на русском обычно больше оригинала процентов на 15!). Так что эта книга может быть очень хорошим подарком аналитику, маркетологу, руководителю, журналисту, студенту, молодому ученому и всем-всем-всем, кто занимается анализом и интерпретацией данных.
Как мне кажется, книга занимает уникальную нишу: между учебниками и совсем популярными книгами (как замечательная и горячо любимая мной, но все же очень популярная “Голая статистика” Уилана). То есть это и очень интересно, и доступно, и в то же время достаточно, чтобы понять, научиться и начать применять.
Повествование - по основным темам и показателям. А строится оно через примеры вопросов, на которые помогает отвечать статистика. Вот лишь несколько из них:
- Каковы закономерности роста мирового населения за последние полвека?
- Уменьшают ли статины риск инфарктов и инсультов?
- Добавляет ли близость к супермаркету Waitrose 36 тысяч фунтов к стоимости вашего дома?
- Действительно ли в некоторых регионах Великобритании смертность от колоректального рака в три раза выше?
- Кто оказался самым везучим пассажиром на «Титанике»?
Ловите промокод для читателей нашего канала на 15% скидку до конца января (и на бумагу, и на электрическую книгу): STATART
https://www.mann-ivanov-ferber.ru/books/iskusstvo-statistiki/
PS Друзья, в связи с обсуждением в комментариях уточним — в магазине издательства максимальная скидка (промокод + акции + ваша личная скидка по программе лояльности) не может превышать 50%. Поэтому в дни новогодней акции промокод может не добавлять существенной скидки. Но это лишь означает, что в данный момент книга и так доступна вам по минимальной цене. В другое время после акции промокод будет давать бОльшую скидку.
Подписка на регулярную поддержку канала (от 5$/ месяц)
Друзья, в январе будет четыре года, как мы рассказываем вам на нашем канале про Google Таблицы и про Скрипты.
Нашему чату около трёх лет, сегодня там почти 2500 участников и многие из них прошли путь от "не умею ничего в таблицу" к "специалист, к которому выстраивается офис в очередь"
Сегодня мы создали свою страницу в patreon – в ней вы сможете оформить ежемесячную поддержку нашего сообщества.
Внутри три уровня подписки (5, 10 и 20 долларов) и небольшие бонусы, которые доступны на каждом из них, включая "секретную опцию" 🙂
Подписка на регулярную поддержку канала: patreon.com/google_sheets
Поддержать канал можно по реквизитам
Друзья, в январе будет четыре года, как мы рассказываем вам на нашем канале про Google Таблицы и про Скрипты.
Нашему чату около трёх лет, сегодня там почти 2500 участников и многие из них прошли путь от "не умею ничего в таблицу" к "специалист, к которому выстраивается офис в очередь"
Сегодня мы создали свою страницу в patreon – в ней вы сможете оформить ежемесячную поддержку нашего сообщества.
Внутри три уровня подписки (5, 10 и 20 долларов) и небольшие бонусы, которые доступны на каждом из них, включая "секретную опцию" 🙂
Подписка на регулярную поддержку канала: patreon.com/google_sheets
Поддержать канал можно по реквизитам
Новый онлайн-курс по 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% (скидка может меняться).
Программа и запись на курс
Друзья, привет! На связи Ренат, рад сообщить, что закончил запись нового онлайн-курса по 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% (скидка может меняться).
Программа и запись на курс
🚜 Собиратыр-тыр-тыр 🚜
Инструмент, который собирает массив из нескольких функций
Каждая
Инструмент подготовил активный участник нашего чата Михаил С. – спасибо ему 😎🤟
видео
статья про инструмент
САМА ТАБЛИЦА (файл - создать копию)
Инструмент, который собирает массив из нескольких функций
IMPORTRANGE
для загрузки данных из выбранных источников. Каждая
IMPORTRANGE
дополнительно делится на несколько частей, по строкам (кажется, что так можно преодолеть / отсрочить ошибку импорта).Инструмент подготовил активный участник нашего чата Михаил С. – спасибо ему 😎🤟
видео
статья про инструмент
САМА ТАБЛИЦА (файл - создать копию)
YouTube
🚜 Собиратыр-тыр-тыр 🚜
Статья про инструмент: https://telegra.ph/Sobiratyr-tyr-tyr-12-20
Канал Google Таблицы: https://t.me/google_sheets/
Чат канала: http://google_spreadsheets_chat/
Собиратор (через скрипты): https://t.me/google_sheets/536
Документация IMPORTRANGE: https:/…
Канал Google Таблицы: https://t.me/google_sheets/
Чат канала: http://google_spreadsheets_chat/
Собиратор (через скрипты): https://t.me/google_sheets/536
Документация IMPORTRANGE: https:/…
Media is too big
VIEW IN TELEGRAM
гифка к посту (для тех, кто не очень понял, как работает штука выше):
- вводим ссылки на таблицы и названия листов в интерфейс
- дожидаемся, чтобы прогрузилась формула, определяющая последнюю строку на каждом листе
- копируем собранную формулу и вставляем её на новый лист
- вводим ссылки на таблицы и названия листов в интерфейс
- дожидаемся, чтобы прогрузилась формула, определяющая последнюю строку на каждом листе
- копируем собранную формулу и вставляем её на новый лист
ИТОГИ 2020
оглавление с категориями для простого поиска
Друзья! За 2020 год мы написали на наш канал 90 постов, сумма их просмотров ~ 1 100 000.
Чтобы вам было проще с этим разобраться – мы разбили посты на категории и вывели их на лист "2020" в оглавлении нашего канала.
Категории такие:
Посты внутри категорий отсортированы по просмотрам, от больших к меньшим.
Мы поздравляем вас с наступающими праздниками, берегите себя.
Прокачивайтесь в Таблицах, с помощью них вы сможете строить отличные системы отчётности и существенно упростите рутину в деле, которым занимаетесь 🎄
ОГЛАВЛЕНИЕ 2020
оглавление с категориями для простого поиска
Друзья! За 2020 год мы написали на наш канал 90 постов, сумма их просмотров ~ 1 100 000.
Чтобы вам было проще с этим разобраться – мы разбили посты на категории и вывели их на лист "2020" в оглавлении нашего канала.
Категории такие:
– готовые решения
– телеграм боты
– формулы
– гостевые статьи
– видео
– форматирование
Посты внутри категорий отсортированы по просмотрам, от больших к меньшим.
Мы поздравляем вас с наступающими праздниками, берегите себя.
Прокачивайтесь в Таблицах, с помощью них вы сможете строить отличные системы отчётности и существенно упростите рутину в деле, которым занимаетесь 🎄
ОГЛАВЛЕНИЕ 2020
Достаём данные из ЮТУБ / ТЕЛЕГРАМ / VC / Т—Ж / ХАБР в Таблицу
Друзья, у нашего подписчика Михаила Шардина много материалов на ресурсах, которые перечислены в заголовке.
Перед ним встала задача доставать просмотры, лайки и комментарии всех этих материалов.
Для этого он создал Таблицу "LynxReport: yчёт публикаций" и для каждого ресурса написал скрипт. Ютуб парсится с помощью API, все остальные сайты - fetch страницы и последующий match строки по регулярному выражению.
инструкция по установке здесь
сама таблица здесь
код отдельно здесь
==
📗 ОГЛАВЛЕНИЕ КАНАЛА категории | всё оглавление
Друзья, у нашего подписчика Михаила Шардина много материалов на ресурсах, которые перечислены в заголовке.
Перед ним встала задача доставать просмотры, лайки и комментарии всех этих материалов.
Для этого он создал Таблицу "LynxReport: yчёт публикаций" и для каждого ресурса написал скрипт. Ютуб парсится с помощью API, все остальные сайты - fetch страницы и последующий match строки по регулярному выражению.
инструкция по установке здесь
сама таблица здесь
код отдельно здесь
==
📗 ОГЛАВЛЕНИЕ КАНАЛА категории | всё оглавление
Функция ВЫБОР/CHOOSE в Excel и Google Таблицах: выбираем случайный элемент из набора
Функция ВЫБОР возвращает одно из значений, перечисленных в ее аргументах, по порядковому номеру. И этими аргументами могут быть как значения, так и ссылки на диапазоны. Так что с помощью нее можно и сгенерировать случайное значение из набора, и, например, "выбирать", из какой таблицы ВПР-ить данные. Пример здесь.
=
🔥 НАШ КУРС НА SKILLBOX, Таблицы и скрипты, Excel и VBA
📗 ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Функция ВЫБОР возвращает одно из значений, перечисленных в ее аргументах, по порядковому номеру. И этими аргументами могут быть как значения, так и ссылки на диапазоны. Так что с помощью нее можно и сгенерировать случайное значение из набора, и, например, "выбирать", из какой таблицы ВПР-ить данные. Пример здесь.
=
🔥 НАШ КУРС НА SKILLBOX, Таблицы и скрипты, Excel и VBA
📗 ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Teletype
Функция ВЫБОР/CHOOSE в Excel и Google Таблицах: выбираем случайный элемент из набора
Функция ВЫБОР возвращает одно из значений, перечисленных в ее аргументах, по порядковому номеру (это первый аргумент):
Функция DETECTLANGUAGE в Google Таблицах: определяем язык текста в ячейке.
Кейс: автоматом формируем ссылку на нужный интернет-магазин
=
🔥 НАШ КУРС НА SKILLBOX, Таблицы и скрипты, Excel и VBA
📗 ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Кейс: автоматом формируем ссылку на нужный интернет-магазин
=
🔥 НАШ КУРС НА SKILLBOX, Таблицы и скрипты, Excel и VBA
📗 ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
This media is not supported in your browser
VIEW IN TELEGRAM
ВЫГРУЖАТОР: СОЗДАЁМ РЕЕСТР ФАЙЛОВ ИЗ ПАПКИ, ВЛОЖЕННОСТЬ – УЧИТЫВАЕТСЯ
Выпускаем вторую версию скрипта, с помощью которого вы сможете выгрузить реестр файлов в Таблицу. Теперь поиск идёт и по вложенным папкам.
КАК РАБОТАЕТ:
Если в B1 напишете root – то выгрузится весь диск, это сработает только для небольших дисков, < 10 000 файлов, из-за максимальной продолжительности выполнения скрипта в 6 минут.
Помимо остальных столбцов – в столбце E – вся информация по файлу. Из этой длинной строки вы сможете вытащить, к примеру, владельца файла или дату его создания.
ТАБЛИЦА С КОДОМ
=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Выпускаем вторую версию скрипта, с помощью которого вы сможете выгрузить реестр файлов в Таблицу. Теперь поиск идёт и по вложенным папкам.
КАК РАБОТАЕТ:
– копируйте Таблицу с кодом;
– вводите ID или URL папки начала поиска в ячейку B1;
– запускайте скрипт из меню [СКРИПТЫ];
Если в B1 напишете root – то выгрузится весь диск, это сработает только для небольших дисков, < 10 000 файлов, из-за максимальной продолжительности выполнения скрипта в 6 минут.
Помимо остальных столбцов – в столбце E – вся информация по файлу. Из этой длинной строки вы сможете вытащить, к примеру, владельца файла или дату его создания.
ТАБЛИЦА С КОДОМ
=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Наше приложение: Эвотор + 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
Друзья, привет! С середины прошлого года мы занимаемся созданием своего приложения для синхронизации онлайн-касс «Эвотор» и таблиц.
Судьба у приложения получилась непростой: сменилось 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
Google
ОНЛАЙН КАССЫ - Google Workspace Marketplace
Интеграция онлайн-касс ЭВОТОР и Google Таблиц
ВПР / VLOOKUP со звездочкой
Друзья, хотим рассказать/напомнить про возможность применения символьных шаблонов в функции ВПР.
Мы неоднократно рассказывали про применение
Но их можно использовать и в ВПР. Если мы хотим найти не ячейку с точным совпадением с искомым значением, а ячейку, соответствующую определенной маске, и тут пригодятся звездочка и знак вопроса.
Например, такая функция ВПР будет искать ячейку, которая начинается со слова "Google" и вернет данные из второго столбца таблицы
P.S. А заодно напомним про РЕГИСТР — он в функции ВПР не учитывается. Даже если в функции будет ipad или iPad, а в таблице для поиска IPAD — все найдется.
Про символьные шаблоны:
Символьные шаблоны в функциях СЧЕТЕСЛИ, СУММЕСЛИ
Шпаргалка по символьным шаблонам
Еще посты про ВПР:
– ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
– ВПР (VLOOKUP) по нескольким условиям
– ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
– ВПР по нескольким диапазонам
– ВПР с интервальным просмотром = 1
– Перекрестный ВПР (ищем по строке и заголовку)
– Видео про функцию ВПР в Google Таблицах
Друзья, хотим рассказать/напомнить про возможность применения символьных шаблонов в функции ВПР.
Мы неоднократно рассказывали про применение
*
(любое количество любых символов, в том числе и нулевое, то есть ничего) и ?
(любой один обязательный символ) в функциях семейства СУММЕСЛИ(МН), СРЗНАЧЕСЛИ(МН), СЧЁТЕСЛИ(МН).Но их можно использовать и в ВПР. Если мы хотим найти не ячейку с точным совпадением с искомым значением, а ячейку, соответствующую определенной маске, и тут пригодятся звездочка и знак вопроса.
Например, такая функция ВПР будет искать ячейку, которая начинается со слова "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 Таблицах
Сравнение текстовых строк по символам и их кодам
Зачастую одни и те же текстовые данные воспринимаются Excel или Таблицами как разные, если они из разных источников (например: одно и то же название товара в выгрузке из 1С и в ваших таблицах будет разным из-за того, что там отличаются... пробелы! Пробелы бывают разными, увы).
Внешне это понять сложно - нужно смотреть на код символа. Его можно определить с помощью функции КОДСИМВ / CODE. Если у внешне одинаковых символов отличаются коды, значит, для Таблиц это разные символы и текстовые ячейки с ними будут тоже считаться разными.
Ну а чтобы достать определенный символ из текста, нужна ПСТР / MID.
Если мы хотим сразу разбить текст по символам одной формулой, сделаем формулу массива, и в качестве второго аргумента ПСТР (какой по порядку символ извлекать) укажем функцию SEQUENCE, которая создаст массив от единицы до числа, соответствующего количеству символов в ячейке (его находим с помощью ДЛСТР / LEN):
Ячейки с формулами в таблице выделены оранжевым.
=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Зачастую одни и те же текстовые данные воспринимаются 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
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Media is too big
VIEW IN TELEGRAM
ОТПРАВЛЯТОР 2.0
Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию
Друзья, привет! Выпускаем вторую версию решения для отправки ваших данных в Телеграм.
В первой версии скрипт создавал простое PNG-изображение из диапазона данных, теперь же мы создаём полноценный PDF или JPG файл.
Таблица полностью готова к работе, просто заполните правила отправки и введите токен своего Телеграм бота.
Таблица со скриптом (инструкция – внутри!)
=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты
This media is not supported in your browser
VIEW IN TELEGRAM
FLATTEN2, что говорит лисичка и другие неописанные в справке функции Google Таблиц
Друзья, привет! Сегодня у нас прекрасная статья от Михаила Смирнова – он рассказывает, как нашел ряд неописанных в справке функций.
FLATTEN 2: другие недокументированные функции Google Sheets
Не про все функции понятно, что они делают, в справке же их нет :) поэтому пробуйте и предлагайте свои варианты в чат: @google_spreadsheets_chat
==
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
Друзья, привет! Сегодня у нас прекрасная статья от Михаила Смирнова – он рассказывает, как нашел ряд неописанных в справке функций.
FLATTEN 2: другие недокументированные функции Google Sheets
Не про все функции понятно, что они делают, в справке же их нет :) поэтому пробуйте и предлагайте свои варианты в чат: @google_spreadsheets_chat
==
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление