Проектная диаграмма с выделением выходных отдельным цветом

(или не выходных, а определенных дней недели — тут вы по аналогии справитесь😉)

Создаем два правила условного форматирования с формулами и выбираем разные цвета.

Для рабочих:

=AND(первая дата с закреплением строки>=начало первого этапа с закреплением столбца; первая дата<=конец первого этапа;WEEKDAY(дата;2)>5)

Для выходных (уже не в общем виде, а с ячейками):

=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)

Таблица

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Наш чат, в котором мы уже 6 лет отвечаем на ваши вопросы: @google_spreadsheets_chat

ПС Кто-то напишет в личку и предложит помочь - не отправляйте деньги, вас обманут

Оглавление нашего канала, где перечислены все наши посты: Оглавление канала

Полезные чатики по теме

Помимо нашего чата советуем чат наших друзей по скриптам: @googleappsscriptrc

И по Таблицам: @google_sheets_pro

👾 Делитесь в комментариях чатами по Таблицам, Excel, программированию, добавим в пост

Самое часто встречающееся текстовое значение

Самое частое число — это мода. Функция МОДА / MODE.

А как найти самое часто встречающееся текстовое значение?
Бен Коллинс в своей книге предлагает решение через MATCH (можно и XMATCH, которая просто по умолчанию ведет точный поиск без обязательного третьего аргумента = 0)

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

IFNA(XMATCH(B2:B;B2:B);)

Потом ищем самое частое число в этом диапазоне:

MODE(IFNA(XMATCH(B2:B;B2:B);))

И извлекаем по этому порядковому номеру текст ИНДЕКСом / INDEX:

=ArrayFormula(INDEX(B2:B; MODE(IFNA(XMATCH(B2:B;B2:B)))))

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

НОВОГОДНЯЯ АКЦИЯ!

Наша команда, в преддверии Нового года, решила порадовать наших подписчиков и сделать новогодный подарок!

Присылайте в наш бот свои задачи и вопросы по google скриптам, мы отберём три на наш взгляд самые интересные задачи и сделаем абсолютно бесплатное для вас решение!
Также эти решения будут опубликованы на нашем канале.

Bot: @vas_mnogo_a_ya_bot
Обязательно в сообщении указать:
1) тег #хочуподарок
2) подробное описание задачи
3) ссылку на Таблицу

Ждём ваши вопросы.
Кому-то точно повезёт!

С наступающим Новым годом! 🎄

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

Редактируем Google Форму прямо из Таблицы!

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

Как всё работает - на гифке, гифка сегодня со звуком.

Что происходит после запуска скрипта в Таблице:
1) в четвертой строке листа "🔥" Таблицы - вопросы, в пятой строке - тип каждого вопроса, под каждым вопросом и типом - варианты ответа, в ячейке "🔥!B2" - форма, которую будем редактировать
2) запускаем скрипт кликом на кнопку и код ищет каждый вопрос по названию в Google Форме
3) если вопрос найден - то скрипт обновляет варианты ответа
4) если вопрос не найден - скрипт добавляет этот вопрос в Google Форму с заданным типом и вариантами ответа

Типы вопросов, с которыми умеет работать скрипт:
CHECKBOX – выбираем ответ на вопрос чекбоксами, можно выбрать несколько вариантов
LIST – выбираем ответ из выпадающего списка
MULTIPLE_CHOICE – выбираем ответ в переключателе

Чтобы воспользоваться Таблицей - копируйте себе

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Друзья, с началом нового рабочего года вас!

Чтобы постепенно входить в рабочий режим, начнем с забавной формулы, вдохновленной поздравлением табличного гуру Бена Коллинса. Он там использовал похожую формулу, чтобы получить номер нового года текстом.

Чтобы получать число прописью, обычно используют пользовательские функции. Но если вам нужно без "рублей" и "копеек", можно воспользоваться функцией BAHTTEXT — она превращает число в текст на тайском с добавлением слова "бат", названия валюты (название тоже на тайском, что логично).

А мы это дело переведем на русский или другой нужный язык с помощью GOOGLETRANSLATE.

А потом останется убрать слово "бат" (бата, батов — смотря какое число будет). С помощью REGEXREPLACE.

Повторимся, для нормальной реализации (с рублями-копейками, для договоров) нужна пользовательская функция или надстройка с ней — про это мы тоже напишем

=REGEXREPLACE(GOOGLETRANSLATE(BAHTTEXT(A1);"th";"ru");"бата?\.|батов \.";"")

Диаграмма с выбором рядов (флажками)

Добавляем флажки для каждого ряда данных.

Дублируем данные для диаграммы, но формулой, которая будет возвращать значение из исходного диапазона только в том случае, если в соответствующем столбце флажок включен, то есть там значение TRUE / ИСТИНА. А если флажок выключен, то будет возвращать значение N/A с помощью одноименной функции. А такие значения на диаграммах не отображаются. Что нам и надо!

=IF(флажок;ячейка с данными;NA())

Строки с формулами можно скрыть, чтобы не любоваться всеми этими НД-шками, а только любоваться данными да диаграммой волшебной.

P.S. Так как периодически мы видим такое в чатах и таблицах, напомним: если вы проверяете статус флажка, не нужно отдельно прописывать равенство ячейки с флажком значению TRUE.
То есть формулы (где B$1 — ячейка с флажком)

=IF(B$1;...

и

=IF(B$1=TRUE;...

будут работать одинаково. Потому что включенный флажок — это TRUE. Так что в первом варианте будет TRUE. И во втором TRUE = TRUE вернет TRUE. Так что ничего страшного в избыточности нет, но и не нужно :)

P.P.S. В Excel все будет работать аналогично. Правда вот флажки в ячейках есть лишь в 365, а во всех старых версиях можно вставить флажки — элементы управления с вкладки ленты "Разработчик". Подробнее — в статье.

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

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Как выделить / найти все формулы на листе?

Можно просто нажать Ctrl + `
Или в меню: Вид - Показать - Формулы (View - Show - Formulae), или Alt+V + S + A.

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

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

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

Пожалуйста, поделитесь:
https://tttttt.me/google_sheets?boost

Как соберем бусты, то выложим вторую часть про бота выше - он будет работать как на каналах, так и в чатах.

Совсем немножко осталось, добьем, господа?

https://tttttt.me/google_sheets/1569

Чатно-канальный бот v2

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

В видео показал, как всё теперь работает.

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Показать защищенные диапазоны

У вас доступ на редактирование, но часть диапазонов защищена. В случае с защитой всего листа у его ярлыка появляется иконка, но не в ситуации, когда защищены отдельные диапазоны на листе. И вы хотите сразу видеть, что нельзя редактировать.

Вид — Показать — Защищенные диапазоны
View — Show — Protected ranges

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Чатно-канальный бот v3
(в видео - как это чудо работает)

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

В видео - как это работает.

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

🍏Хотите уметь делать таких ботов самостоятельно? 

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

Комбинированная диаграмма: показываем, где мы находимся на фоне распределения всех

Данные для этой диаграммы — результаты марафонцев и марафонесс в Москве в 2024 году (протокол с сайта организатора).

Как обрабатываем данные для диаграммы?
С помощью функции ЧАСТОТА/FREQUENCY получаем распределение по интервалам.

С помощью функции ТЕКСТ / TEXT делаем заголовки вида "До 05:00":

="До "&ТЕКСТ(H2;"hh:MM")

Функцией ЕСЛИ / IF проверяем, в какой интервал попадает наш результат — и для нужного интервала оставляем его значение, а для остальных — ошибку N/A с помощью одноименной функции НД / NA. Таким образом, будут данные для ряда на диаграмме, состоящие из одного значения, чтобы подсвечивать наш результат, и остальных значений N/A, которые не будут отображаться.

=ЕСЛИ(И(наш результат>граница интервала;наш результат<=граница след интервала);ЕСЛИ(наш пол="Женщины";число женщин в этом интервале;число мужчин);НД())

Как строим такую диаграмму?
Это комбинированная диаграмма. Распределение результатов (два ряда: женщины и мужчины) — это области. А ряд с нашим результатом — это столбик (гистограмма).

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


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Друзья, мы умеем делать приложения для магазинов

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

По этим приложениям у нас около 100 покупок каждый месяц.

☄️ Поэтому, если вам нужно свое приложение (необязательно для Эвотора) и если в этой системе есть его возможность сделать и выложить - напишите мне, обсудим @namokonov