Хотите с нуля освоить Excel и узнать все тонкости Google-таблиц? Тогда поспешите на курс от Skillbox, где вы получите все необходимые навыки всего за 4 месяца.
Занимайтесь в любое удобное время всего несколько часов в неделю

Чему вы научитесь в процессе обучения:
⚡️ строить прогнозы;
⚡️ правильно оформлять данные и наглядно их предоставлять;
⚡️ создавать макросы для языка VBA;
⚡️ работать с внешними источниками данных и инструментами фильтрации.


🔥Каждый, кто купит в ноябре курс, который участвует в ЧП, получит три скидки от партнеров:
     Asus - скидка 10% на ноутбуки.
     Литрес - -25% на все книги + 2 книги в подарок из подборки.
     Clinique - скидка 40% на все, кроме новогодних коллекций.

Переходите: https://clc.to/EHBy6g и приобретайте курс со скидкой до 60%

#реклама

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

"Обычная" функция ВПР / 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

​​Курс «Профессия BI-аналитик» поможет вам детально изучить профессию BI-аналитика и стать востребованным специалистом в сфере бизнес-анализа.

Вы научитесь:
🔹 работать в Power BI, SQL, Excel, Tableau;
🔹 обрабатывать данные из разных источников, таких как Excel, AmoCRM и другие;
🔹 наглядно презентовать данные с помощью дашбордов;
🔹 настраивать аналитику под задачи компании;
🔹 автоматизировать сбор данных при помощи Python.

Мы делаем фокус на практических заданиях, что позволяет закреплять полученные знания и навыки. Программа курса построена так, что уже в процессе обучения вы сможете устроиться на должность уровня Junior. И продолжите обучение параллельно работе, прокачивая свои навыки до уровня Middle.

Каждый, кто купит в ноябре курс, который участвует в ЧП, получит три скидки от партнеров:
Asus - скидка 10% на ноутбуки.
Литрес -25% на все книги + 2 книги в подарок из подборки.
Clinique - скидка 40% на все, кроме новогодних коллекций.
Переходите: https://clc.to/IsnhUQ и приобретайте курс со скидкой до 60%

#реклама

задачник в Таблице с оповещениями в 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)
t.me/google_sheets/616

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

3. СОБИРАТОР 4.0 (9)
t.me/google_sheets/661

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

20. отправлятор таблиц в телеграм чаты
бесплатно и по расписанию (4)
t.me/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. Берёт стартовый пост (например, t.me/google_sheets/739), берёт количество постов, которое мы хотим выгрузить (например, 3);
2. Создаёт массив ссылок добавляя к стартовому посту 1,
[t.me/google_sheets/739,
t.me/google_sheets/740,
t.me/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
@google_sheets

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

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

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

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

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

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

❗️❗️ Друзья, у кого не заработал скрипт – удалите в редакторе скриптов Drive Api и активируйте заново (не только в редакторе скриптов, но и в проекте);

Хотите освоить визуализацию данных на реальных задачах — попробуйте бесплатный курс-симулятор от Нетологии и Yandex.Cloud.

Ваш персонаж — аналитик в крупной ритейл-компании, а каждое занятие — история с прописанным сюжетом. Вы построите интерактивную карту клиентов в DataLens, соберете отчёт-конструктор расходов в Excel, сравните показатели в Power BI, а в конце наведёте красоту в Tableau. Узнаете много нового и познакомитесь с основными инструментами визуализации данных.

А ещё ваш дашборд может выиграть приз, подробнее → https://netolo.gy/hkO

#реклама

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

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

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

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

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

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


Новогодняя распродажа – скидки до 60% весь декабрь.
💥При покупке этого курса в декабре каждый получит скидки от партнёров:

Asus – 10% на ноутбуки.
Литрес – 25% на все книги + 2 книги в подарок.
Clinique – 40% на всё, кроме новогодних коллекций.
КЭСПА - 10% на занятия c преподавателем.

Переходите: clc.to/G9GM0Q и выбирайте подарок себе или близкому человеку со скидкой до 60%

#реклама

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / 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) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".

Не важно, кто вы, маркетолог, финансист или бизнес-аналитик.

Важны ваши навыки! И мы предлагаем их прокачать на онлайн-курсе по Power BI PRO.

Пройдите обучение, освойте весь инструментарий PBI, научитесь эффективно работать с данными, создавать метрики, графики, дашборды, а также быстро и эффективно решать бизнес-задачи.

Новогодняя распродажа — скидки до 60% весь декабрь.
💥При покупке этого курса в декабре каждый получит скидки от партнёров:
Литрес — 25% на все книги + 2 книги в подарок.
Clinique — 40% на всё, кроме новогодних коллекций.
КЭСПА — 10% на занятия c преподавателем.
Переходите: https://clc.to/X7ZOMg и выбирайте подарок себе или близкому человеку со скидкой до 60%

#реклама

Астрологи объявили неделю видео: делимся с вами топ-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

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

t.me/google_sheets/851