Поиск с учетом регистра

"Обычная" функция ВПР / VLOOKUP ищет значение без учета регистра. "ipad" и "IPAD" и "iPad" - ей все равно.
Как быть, если нужно искать значение с учетом регистра?

Функция СОВПАД / EXACT проверяет точное совпадение значений - своих аргументов - и возвращает ИСТИНА / TRUE, если они равны (то есть совпадают все символы и регистры всех символов).

Если ввести ее в массиве, то получим столбец со значениями ИСТИНА и ЛОЖЬ, и ИСТИНА будет в строке с нужным нам значением:
=ARRAYFORMULA(EXACT(искомое значение; просматриваемый столбец))

Ну а дальше дело техники: находим строку с ИСТИНОЙ через ПОИСКПОЗ / MATCH
=MATCH(TRUE; EXACT(искомое значение;просматриваемый столбец);0)

И ИНДЕКСом / INDEX вытаскиваем из столбца с нужными данными значение из найденной строки:
=INDEX(возвращаемый столбец; MATCH(TRUE; EXACT(искомое значение;просматриваемый столбец);0))

Можно воспользоваться и функцией FILTER - она будет возвращать несколько значений, если их будет больше одного, а не только первое:
=FILTER(возвращаемый столбец;EXACT(искомое значение;просматриваемый столбец))

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

P.S. Заодно мини-памятка. Где регистр учитывается, где - нет.
Учитывается:
- в регулярных выражениях
- в текстовых условиях кляузы WHERE функции QUERY
- в функции НАЙТИ / FIND
- в функции ПОДСТАВИТЬ / SUBSTITUTE

Не учитывается:
- в обычных логических выражениях, например =A1=B1
- в СУММЕСЛИМН / SUMIFS и других подобных функциях
- в функциях ВПР / VLOOKUP и ПОИСКПОЗ / MATCH
- в функции ПОИСК / SEARCH
- в текстовых условиях в функции FILTER

задачник в Таблице с оповещениями в Telegram чаты и каналы

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

Чтобы все заработало у вас, нужно:
1. скопировать Таблицу с кодом к себе;
2. создать своего Telegram бота через @botfather, скопировать его токен и вставить в ячейку I1;
3. добавить созданного бота в чаты / каналы, куда вы будете отправлять оповещения;
4. также добавить в эти чаты @myidbot, командой /getgroupid узнать chat_id чатов и внести в ячейку I2 (один или несколько через запятую), потом бота можно удалить;
5. создать триггер: открыть редактор скриптов → триггеры → добавление триггера → функция: onSender → тип события: при редактирование → создать;

★ Код отдельно: pastebin.com/Ew9Uj75F
★ Оглавление канала: goo.gl/HdS2qn

Специальная вставка: перенос только ширин столбцов

Допустим, нам нужно сделать в одной таблице ширину каждого столбца точно такой же, как в другой.
К счастью, такая опция в специальной вставке есть. Копируем ячейки в первой таблице (Ctrl+C), правой кнопкой щелкаем на первую ячейку во второй таблице, и в контекстном меню выбираем "Специальная вставка" (Paste special) --> "Только ширина столбца" (Column width only).

И в Excel тоже работает! Ctrl+Alt+V + ш(w)
Или правая кнопка мыши --> Специальная вставка (Paste Special) --> ширины столбцов (Column widths).

Топ-20 упоминаний постов нашего канала за полгода (1.05.21 - 31.10.21)

Друзья, @vitalich вытащил ссылки на самые упоминаемые посты из нашего чата. Присмотритесь – вполне возможно, найдете полезное для себя.

1. крутейшая таблица с подсказками про query (19)
www.tg-me.com/google_sheets/616

2. Народный Telegram бот❗️Отправляем сообщения прямо из Таблицы (13)
www.tg-me.com/google_sheets/556

3. СОБИРАТОР 4.0 (9)
www.tg-me.com/google_sheets/661

4. ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты (9)
www.tg-me.com/google_sheets/719

5. Считаем сумму по каждой строке / столбцу в формуле массиве (8)
www.tg-me.com/google_sheets/502

6. Избранные посты нашего канала (7)
www.tg-me.com/google_sheets/418

7. ускоряем работу Таблицы (7)
www.tg-me.com/google_sheets/143

8. ​​Условное форматирование. Выделяем дубликаты и не только (7)
www.tg-me.com/google_sheets/296

9. Как сделать, чтобы при выставлении определенного статуса в соседней колонке Таблицы автоматически появлялась дата и время? (7)
www.tg-me.com/google_sheets/171

10. ВЫГРУЖАТОР: СОЗДАЁМ РЕЕСТР ФАЙЛОВ ИЗ ПАПКИ, ВЛОЖЕННОСТЬ – УЧИТЫВАЕТСЯ (7)
www.tg-me.com/google_sheets/635

11. ДВИГАЕМ ВРЕМЯ ФОРМУЛАМИ (7)
www.tg-me.com/google_sheets/593

12. Важный скрипт. Связанные выпадающие списки из кэша (7)
www.tg-me.com/google_sheets/408

13. Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах) (6)
https://www.tg-me.com/google_sheets/283

14. ​​Как посчитать:
- Количество ячеек, начинающихся не на букву "Т"?
- Сумму всех товарных позиций из 7 и более символов?
- Количество записей не из 4 символов? (6)
www.tg-me.com/google_sheets/254

15. Повторяем диапазон N раз (6)
www.tg-me.com/google_sheets/587

16. Библиотека небольших скриптов onEdit (6)
www.tg-me.com/google_sheets/432

17. памятка по условиям, с помощью которых можно отобрать (для функции SUMIFs/СУММЕСЛИМН, например) пустые и непустые ячейки (5)
www.tg-me.com/google_sheets/77

18. Работаем со вшитыми (богатыми 😊) в ячейки ссылками (4)
www.tg-me.com/google_sheets/668

19. ОТПРАВЛЯТОР 2.0. Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию
www.tg-me.com/google_sheets/643

20. отправлятор таблиц в телеграм чаты
бесплатно и по расписанию (4)
www.tg-me.com/google_sheets/604

★ Чат: @google_spreadsheets_chat
★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn

P.S. @vitalich, спасибо за анализ чата и составление подборки 😎

IMPORTRANGE: от простого к сложному

Друзья! Большая статья от Михаила Смирнова:
— что такое IMPORTRANGE?;
— простой случай применения;
— добавляем к каждой строке информацию: из какой таблицы загружены данные (три способа);
— добавляем к каждой строке ссылку на строку в источнике;

telegra.ph/Google-Sheets--Nemnogo-pro-IMPORTRANGE-11-12

Выгружатор постов / просмотров / картинок из каналов Telegram

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

Как воспользоваться:
0. Посмотрите гифку :)
1. Копируете Таблицу с кодом к себе;
2. Заполняете лист настройки – вам нужно ввести ссылку на стартовый пост и количество постов от стартового, которое скрипт попробует спарсить;
3. Запускаете скрипт из меню с 🐧 (наверное, это скворец);
4. Скрипт вставит результат на лист "логи" и обновит дату / время на листе "настройки";

Как скрипт в принципе работает:
1. Берёт стартовый пост (например, www.tg-me.com/google_sheets/739), берёт количество постов, которое мы хотим выгрузить (например, 3);
2. Создаёт массив ссылок добавляя к стартовому посту 1,
[www.tg-me.com/google_sheets/739,
www.tg-me.com/google_sheets/740,
www.tg-me.com/google_sheets/741]

Поэтому, если на канале был пост, потом его удалили и эта ссылка попала в массив - по ссылке не вернется ничего❗️;

3. Далее весь массив ссылок запрашивается с помощью fetchAll();

Общая квота запросов на google-аккаунт – 20 000 запросов на бесплатном и 100 000 на google workspace, но если вы запросите сразу 1000 постов – то по части запросов сервер Telegram может не вернуть ничего, даже если посты были, подумав про вас и про ваш IP-адрес нехорошее, поэтому – запрашивайте умеренно ❗️;

4. Из результата по каждой ссылке с помощью регулярных выражений скрипт достаёт текст поста / просмотры / картинку;

Только код: pastebin.com/RbGjK7ju
Таблица с кодом: Таблица

Документация: fetchAll, квота запросов в день

PS Идеи что добавить к функционалу – напишите в комментариях.

Удалили нужный файл на Google Drivе?
Рассказываем, что делать.

Друзья, Александр Иванов написал про то, что делать, если вы случайно удалили нужный файл – категорически рекомендуем: gdriveru.blogspot.com/2021/11/restorefiles2.html

★ Чат: @google_spreadsheets_chat
★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn

DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы | @google_sheets

Друзья, привет! И у нас чёрная пятница – выкладываем для вас наш большой скрипт совершенно бесплатно.

— Хотите из Таблицы контролировать свои рабочие файлы и папки и следить за тем, кому они расшарены?

— Пришел новый сотрудник? Вы сможете добавить его к нужным файлам за несколько минут.

— Старый сотрудник перестал оправдывать доверие и вы решили с ним попрощаться? Удалим его из файлов, пока он ничего не удалил сам :)

🔥 Таблица со скриптом (делайте копию)
🔥 Инструкция

P.S. А без помощи Игоря из Японии это решение бы не состоялось, спасибо ему! 🎎

❗️❗️ Важный апдейт

Custom Functions: пользовательские функции в Google Apps Script

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

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

https://telegra.ph/Google-Apps-Script--Custom-Functions--Poleznye-Melochi-11-04

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк

Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
=SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...)

Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel (в Google к нему в сводных еще добавляется подсчет уникальных значений).

Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
1 и 101 - среднее
2 и 102 - количество чисел
3 и 103 - количество значений
4 и 104 - максимум
5 и 105 - минимум
6 и 106 - произведение
9 и 109 - сумма

Каждая функция бывает в двух вариантах - коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

Короткий вариант - подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.
Длинный вариант - подсчет только отфильтрованных строк, без скрытых вручную.

Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.

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

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

P.S. В Excel функция работает аналогично. Кроме того, если вы создаете "Таблицу" (Ctrl + T) и затем строку итогов (Total Row) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".

Астрологи объявили неделю видео: делимся с вами топ-10 уроков от дружественного канала STM Solution, и несколькими нашими видео, которые вы могли пропустить.

Топ-10 STM Solution:
1️⃣ Как создавать и делать базовое форматирование

2️⃣ Как делать автозаполнение данных, закрепление строк, добавление листов

3️⃣ Как создавать макросы и делать кнопки

4️⃣ Формулы, Ссылки, Выпадающие списки и Фильтрация

5️⃣ Что такое функция IF (Если)

6️⃣ Как делать Импорт/Экспорт Excel и защиту данных

7️⃣ Как собрать данные из множества листов Google таблиц в один не используя скрипты

8️⃣ Первое знакомство и создание своей функции

9️⃣ Как создавать,искать шаблоны и форматировать текст

1️⃣0️⃣ Продвинутое условное форматирование

Наши уроки:
Дата и время в Google Таблицах. Функции СЕГОДНЯ, ТДАТА, РАЗНДАТ, РАБДЕНЬ, ЧИСТРАБДНИ, НОМНЕДЕЛИ

Функции подсчета и суммирования в Google Таблицах (СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ)

Добавляем изображение товара в ячейку Google Таблиц по его названию

Спарклайны в Google Таблицах. Синтаксис и примеры

Фильтры и фильтрация в Google Таблицах. Совместная работа

Функция ВПР(VLOOKUP) в Google Таблицах + ВПР с поиском по заголовкам

★ Чат: @google_spreadsheets_chat
★ Оглавление нашего канала: goo.gl/HdS2qn

От ностальгирующих пользователей 😏

www.tg-me.com/google_sheets/851

Выгружаем все файлы / папки, обращаемся к GSUITE-дискам

Апдейт к нашему посту, разбираем комментарии.

1) Как выгрузить все файлы и папки?
Просто вводите вместо ссылки на папку:
https://drive.google.com/drive/u/0/folders/root и небольшие диски (скажем, до 10 000 файлов) успеют выгрузиться за 6 минут, на бОльшие – времени не хватит и скрипт закончится по таймауту.

2) Как выгружать корпоративные диски?
Добавьте в код в редакторе скриптов, в функцию "search":

optionalArgs.corpora = "allDrives"; //возможные варианты: user|drive|domain|allDrives
optionalArgs.includeItemsFromAllDrives = true;
optionalArgs.supportsAllDrives = true;

Помним, что у нас на выполнение скрипта не более 6 минут, поэтому, если вы включите все диски и попытаетесь выгрузить всё – скорее всего времени не хватит, поэтому при включении корпоративных дисков ограничивайтесь конкретными папками, которые вы укажете для выгрузки.

DRIVE COLUMBUS
ДОКУМЕНТАЦИЯ DRIVE API

Памятки 2.0

Друзья, привет! Составили Таблицу с ответами на многие типовые вопросы начинающих и продолжающих - с ссылками на посты в чате/канале и статьи:
https://docs.google.com/spreadsheets/d/183MPSD8cYfCiwwvJKL_vOpaSxG-X5SGvaevtjcJd6g8/edit

ВЫГРУЖАТОР: достаём скриптом количество подписчиков чатов и каналов в Телеграм

Друзья, ранее мы писали о том, как выгрузить из телеграм каналов посты и просмотры.

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

Теперь вы сможете отследить, насколько эффективную рекламную кампанию вы провели, как растут ваши конкуренты и другие каналы :)

Как это работает:
1) Делаем копию Таблицы;
2) Заполняем на листе "настройки" какие каналы и чаты парсим;
3) Запускаем скрипт из меню со "❄️", скрипт добавит строки с данными на лист "результат", каждый новый запуск будет добавлять новые строки;
4) Запускать каждый раз вручную необязательно – скрипт можно поставить в расписание, создав триггер для функции getChannelsDetails (кто не знает как – смотрите ГИФку).

★ Код отдельно: pastebin.com/QDg5UYRy
★ Оглавление нашего канала: goo.gl/HdS2qn

​​Пополняем обзор околотабличных книг еще тремя изданиями!
Книга из серии "для чайников" про Excel 2019, "Бизнес-анализ в Excel" и визуальное руководство по Google Workspace.
Эти и все остальные книги с обложками, подробными описаниями и ссылками - в обзоре:
https://teletype.in/@renat_shagabutdinov/excellent_books

Teach Yourself VISUALLY Google Workspace
Хорошее руководство по Google Workspace (бывший G Suite) для новичков и продолжающих.

Богато иллюстрированное (как полагается в этой серии, а это большая серия книг по разным приложениям) издание.

Excel 2019 для чайников
Как всегда в этой серии — подробно, по шагам, много таблиц, дополнительных разъяснений. Но выбор тем удивляет при этом. Очень мало про функции и формулы, практически ничего. Но есть небольшая вводная глава про макросы, например.

Бизнес-анализ в Excеl: Финансы, продажи, логистика
Все же скорее это не бизнес-анализ, а справочник по Excel. Вот, например, глава про текстовые функции — там нет бизнеса, только перечисление функций и описание их синтаксиса. Собственно бизнес-анализ — последние главы, они очень короткие и простые (теория очередей, складская логистика, инвестиционный портфель, точка безубыточности) — кажется, что эти темы нужно отдельно изучать, а Excel отдельно.

И такой скрипт у нас есть:

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

Другие вопросы можно задать в нашем чате: @google_spreadsheets_chat

И просто полистать оглавление нашего канала: goo.gl/HdS2qn

Привет, завтра выложим скрипт для автоматического превращения изображений в текст :)