Проектная диаграмма - Условное форматирование

Как сделать нечто вроде проектной диаграммы за счет заливки ячеек условным форматированием:
1. Сначала с помощью SEQUENCE формируем последовательность дат - от начала первого этапа до окончания последнего. Здесь в столбце B - даты начала этапов проекта, в C - окончания.
=SEQUENCE(1;МАКС(C:C)-МИН(B:B)+1;МИН(B:B);1)

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

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

Формула будет выглядеть так:
=AND(первая дата>=начало первого этапа;первая дата<=окончание первого этапа)

=AND(D$1>=$B2;D$1<=$C2)

Первая строка закреплена $, так как мы всегда смотрим на дату в этой строке (это заголовки). А у начала и окончания этапов закреплены столбцы, чтобы при смещении вправо мы в любом случае сравнивали каждую дату с началом и окончания этапов. Строки 2 не закреплены, так как в следующих строках мы уже смотрим на даты следующих этапов.

P.S. Если хочется исключить выходные дни, можно добавить условие (номер недели < 6, то есть не СБ или ВС) в условное форматировании:
=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)
=AND(D$1>=$B2;D$1<=$C2;WEEKDAY(D$1;2)<6)

Дальше - только полет вашей фантазии. Можно отдельным правилом красить выходные другим цветом. Каким-нибудь грустным, если все-таки предполагается там работать 😥

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

Еще про диаграмму Ганта: Диаграмма Ганта с помощью одной формулы

Два скрипта от нашего подписчика
– создаем список листов
– вставляем выпадающие списки по этому списку

Друзья, недавно в нашем чате Александр Кулешов предложил два скрипта для решения двух проблем:

1. первый скрипт создаёт список листов Таблицы и вставляет его на лист, помимо списка листов вставятся чекбоксы (выключенные);

2. второй скрипт проходится по этому списку листов и если пользователь напротив названия листа активировал чекбокс – скрипт вставляет в выбранный столбец этого листа выпадающие списки из листа "значения";

Таблица с примером (делайте копию, чтобы попробовать): тут

Код скриптов отдельно: pastebin.com/XZ70VzmU

Спасибо Александру, заходите в наш чат и изучайте скрипты и Таблицы, на нашем канале множество примеров – оглавление 😎

Отправлятор / Удалятор постов и сообщений Телеграм
(на гифке - отправляем сообщения, в т. ч. делаем реплай)

Отправлятор / Удалятор постов и сообщений Телеграм
(на гифке - удаление сообщений)

Друзья, вы часто спрашиваете про Телеграм ботов и поэтому принимайте.

С помощью нашей Таблицы вы сможете и отправлять и удалять ботом сообщения как в каналах (для этого бот должен быть админом канала с правами), так и в личных чатах с пользователями (для этого бот должен уже контактировать с пользователем в личном чате, по крайней мере кликнуть /start)

❗️ Полная инструкция
🚜 Таблица
⚙️ Код отдельно

Наш чат

Функция ОКРУГЛТ / MROUND - округление с заданной точностью

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

Допустим, вы хотите округлить цены, чтобы они всегда заканчивались на 0, тогда вторым аргументом функции будет 10.
А если надо, чтобы цены заканчивались на 0 или 5 - то 5 (число будет округляться до ближайшего кратного 5).

=MROUND(число;точность)

Таблица с примером

Каналы, которые мы читаем (иногда ведем) и рекомендуем

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

Но прежде всего приглашаем в наш табличный чат. Это как бы и не канал, зато можно зайти, спросить и получить помощь. И, наоборот, помочь другим:
https://t.me/google_spreadsheets_chat

От Жени
Орёл и кошка - этот канал ведут мои друзья, пара, которая релоцировалась в Турцию с котами. Про жилье, еду и местный колорит.

Например:
– Как улететь с котом в Армению: t.me/orel_i_koshka/20

– Влажный бургер! Семейный бизнес, превратившийся в туристический феномен Стамбула: t.me/orel_i_koshka/73

От Рената

CEO Readz - сооснователь, совладелец и в прошлом CEO издательства МИФ Артем Степанов про деловые книги.

Николай Павлов (Планета Excel) - главный маэстро Excel в России теперь есть в Телеграме. Ура! Николая, его статьи и книги категорически рекомендую.

Личный канал - это как моя личная страничка в соцсети. Ни слова про таблицы. Бег, книги, футбол и прочая жизнь. Заглядывайте в гости

ВПР-им с разных листов

Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.

Обычная ссылка на другой лист выглядит так:
='Москва'!A:B

Нам нужно подставлять внутри апострофов названия разных листов.

Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"

Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")

И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("'" & ячейка с названием листа & "'!диапазон"
) ; номер столбца ; 0)

Ссылка на таблицу с примером

Данные с разных листов, на которых разная структура

Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?

Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.

Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).

Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данные

MATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)

ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)

OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.

А логика формулы в общем виде такая:

=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))

Таблица с примером

Отправлятор / Удалятор постов и сообщений Телеграм, апдейт

Наш подписчик Михаил сделал новую версию Таблицы с Отправлятором.

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

Таблица

Всё крутится на библиотеке на GAS для Telegram Bot API: github.com/Guf-Hub/TGBot

@nosaev_m, спасибо! 😎

Сообщение в Телеграм чат при любом редактировании столбца.

Посмотрите на скриншот - такой вопрос пришел в наш чат.

Показываем минимальный код, с помощью которого задачку можно решить. Код вставляется в редактор скриптов Таблицы (Расширения > Apps script), после функцию send нужно положить на триггер изменения Таблицы (Расширения > Apps script > Триггеры > создать Триггер > при редактировании Таблицы > функция send).

В коде нужно заполнить botToken, ввести один или несколько chatIds, на которые будут отправляться сообщения (можете ввести и username канала). Чтобы узнать chatId - используйте @idBot в телеграм.

Бот сможет отправлять сообщение только если он в чате или на канале с правами отправлять сообщения либо если пользователь уже ему писал и после этого не блокировал.

Как искать ошибку, если код не работает: Расширения > Apps script > Количество выполнений > кликаем на неудачное выполнение, читаем ошибку и исправляем.

Код: pastebin.com/dBevx4L3

Чат: @google_spreadsheets_chat
Канал: @google_sheets

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

Функции:
— Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT →→
— Памятка по синтаксису QUERY / FILTER / SUMIFS →→
— Полный справочник по QUERY →→
— Функция FILTER. Список условий выбираем диапазоном прямо с листа →→
— Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы →→
— SUMIF для нескольких условий в формуле массива →→
— Формулой достаем изображения из поисковой выдачи Яндекса →→
— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) →→
— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой) →→
— ВПР-им с разных листов →→
— Автоматически создаем фразы по определенным шаблонам в Google Таблицах →→
— Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥 →→
— Создаём в QR-код прямо в ячейке →→
— Флаг вам в руки — суммируйте что хотите. SUMIFS с флажком →→

Проекты:
— Telegram bot + Google Sheets (используя вебхуки) →→
— Эмоджи форматирование выполнения плана (IMAGE, IFS) →→
— Создаем красивое расписание групповых занятий →→
— Создание оглавления в телеграм-канале: как автоматически загружать и сортировать публикации из вашего рабочего файла →→
— Google Форма + Google Таблица для проведения тестирования →→
— Применение IFTTT для отправки сообщений с данными из Таблиц →→
— Googlefinance, Парето и графики (#готовое решение) →→
— Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям →→
— Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) →→

Для новичков:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel" →→
— Советы по оптимизации Таблиц →→
— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц) →→
— Видеоурок по фильтрам и режиму фильтрации →→
— Видеоурок: Пользовательские числовые форматы в Google Таблицах →→
— Про виды доступа к документам →→
— Совместная работа с фильтрами →→
— Данные с другого листа в правиле условного форматирования →→
— Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела →→
— Схватка двух ёкодзун. Сравнение Google Таблиц и Excel →→

Полное оглавление нашего канала: http://goo.gl/HdS2qn
Наш чат: @google_spreadsheets_chat

Разрешаем доступ для IMPORTRANGE автоматически

Друзья, когда я начал заниматься Таблицами у меня был вопрос – как не кликать каждый раз на "allow access / разрешить доступ", как расшаривать доступ к Таблицам автоматически?

Тогда я не смог найти ответ, а сейчас мы умеем это делать — напоминаю про отличную статью от Михаила Смирнова 🔥

скрипт onOpen, который создаёт автофильтр

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

В фильтре применяем условие фильтрации формулой по первому столбцу > TODAY() - 3, чтобы остались только строки за сегодня, вчера и позавчера.

Код:
function onOpen() {
const sheetNames = [
'Касса 1',
'Касса 2',
'ИТОГО',
];

const ss = SpreadsheetApp.getActive();
const criteria = SpreadsheetApp.newFilterCriteria()
.whenFormulaSatisfied('=$A2 > TODAY() - 3')
.build();

for (let sheetName of sheetNames) {
let sheet = ss.getSheetByName(sheetName);

let filter = sheet.getFilter();

if (filter == null) {
filter = sheet.getRange('A1:F').createFilter();
}

filter.setColumnFilterCriteria(1, criteria);
}
};

Оглавление нашего канала: тут
Наш чат: @google_spreadsheets_chat

За сниппет спасибо Михаилу Смирнову! 😎

Шаблоны в Google Таблицах и Excel

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

Но можно просто сделать ссылку на копирование часто используемой таблицы и добавить в избранное.

В Excel можно сохранять книги как шаблоны, кроме того, можно изменить шаблон "новой" книги (создаваемой) - алгоритм по ссылке.

ИЗБРАННЫЕ СКРИПТЫ НАШЕГО КАНАЛА

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

— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →

— 🧞‍♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →

— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →

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

— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →

— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →

— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →

— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →

— Скриптами определяем, когда освободится домен →

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

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

— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →

— Защищаем скрипты от редактирования →

— Регулярный бэкап Таблиц в формате XLSX в телеграм! →

— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →

— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →

— Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →

— Важный скрипт. Связанные выпадающие списки из кэша →

Удаляем лишние пробелы

Для этого можно использовать функцию TRIM / СЖПРОБЕЛЫ - она удаляет пробелы в начале строки (все до первого слова), в конце и оставляет только по одному пробелу между словами.

А если нужно удалять переносы строк, воспользуйтесь CLEAN / ПЕЧСИМВ - эта функция удаляет непечатаемые символы (первый 31 символ ASCII, в том числе и перенос строки, с которым мы можем столкнуться в Таблицах).

Обе функции есть и в Excel.

А в Таблицах еще есть инструмент для удаления пробелов без формул, если вам нужно сделать это разово (без пересчета).

Данные - Очистка данных - Удалить пробелы
Data - Data cleanup - Trim whitespace

Или клавиши: (Alt+D) + U + I

ДЛЯ ПРОДАВЦОВ НА WILDBERRIES

Друзья, привет! Мы подготовили дополнение для продавцов на сайте WB.

Какие модули есть сейчас:
— выгрузка ваших данных из API WB: остатки, заказы, продажи, отчет по реализации (2000 рублей / месяц)

— выгрузка всех карточек продавца (можно выгрузить как свои, так и чужие карточки) в Таблицу + выгрузка остатков и размеров по выбранным карточкам (2000 рублей / месяц)

— редактирование карточек из Таблицы (2500 рублей / месяц)

Какие модули будут скоро: мы добавим более широкий парсинг сайта WB, инструмент для расчёта автозаказа, а также для изменения цен.

Про процесс: с каждым из первых клиентов мы создаём отдельный чат в Телеграм, в нём вы сможете задавать вопросы, прийти если что-то не работает и дать фидбек по развитию приложения.

Как купить: напишите мне, @namokonov, далее я вас сориентирую

Какие данные вы увидите: teletype.in/@google_sheets/wb_headers

PS. На ГИФ – выгрузка всех товаров с розничными ценами одного продавца 😎

Sheets Api, берём данные

*библиотека для работы с Таблицами, использовать её можно в том числе из скриптов этих самых Таблиц

Рома Игнатов написал материал про Sheets Api. Он берёт объемную Таблицу с данными о российских библиотеках и показывает примеры использования Sheets Api: забирает данные из Таблицы, вставляет, вставляет батчем (одновременно в несколько мест).

ignatov-script.blogspot.com/2022/06/sheets-api-1.html

Спасибо автору! 😎