Канал «Google Таблицы»: список всех публикаций

За время существования канала «Google Таблицы» в Телеграме мы с моим соавтором Евгением Намоконовым написали больше сотни полезных статей, поделились кейсами, ответили на вопросы слушателей и продолжаем это делать.

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

Оглавление канала в формате Google Таблицы можно найти по ссылке.

СУММЕСЛИ (разбор «сложных» условий, с <>= и символьными шаблонами *?)
Советы по оптимизации таблиц
Открытые диапазоны (А2:A, B5:B и т.д.)
Именованные диапазоны
СЧЁТЕСЛИ (разбор «сложных» условий, с <>= и символьными шаблонами *?)
Объединяем всякое, при помощи &
Вычисление фрагмента формулы прямо в строке.
Именованные диапазоны II
Статья в блоге изд-а МИФ (чередующиеся строки, ВПР (VLOOKUP) и вставка изображений в ячейки с помощью функции IMAGE)
ВПР с интервальным просмотром = 1
Диаграммы — карты в Google Таблицах
Выпадающий список
Добавляем к изображению ссылку
Надстройка CropSheet
ГПР
Транспонирование
Спарклайны
Горизонтальная сортировка
Текст по столбцам
FILTER (начало)
Networkdays
Фиксация
UNIQUE и COUNTUNIQUE
Выводим список, убирая элементы из него позиции другого списка
IMPORTHTML
Сравнение двух списков (ПОИСКПОЗ)
Конвертируем текст в числа — value+substitute
Условное форматирование — закрашиваем строку целиком
Исправляем регистр: предложение с заглавной буквы
IMPORTFEED (загрузка RSS-фида в Google таблицу)
Таблица со статусами, при смене которых меняется усл форматированием
Как сделать ссылку на конкретный диапазон и отправить ее коллеге
Sparkline
Видеоурок по функции IMPORTRANGE
Query (select и where)
Query (group by)
Query (из двух диапазонов)
Объединяем массивы для ПОИСКОЗ \ Выпадающий список зависит от другого вып. списка
Статья на Хабре «Гуглы, буби, крести, пики. Система отчетности для стартапа в Google Таблицах за пару часов»
5 дополнений к Таблицам
Сравнение QUERY и FILTER
VLOOKUP с массивом + статья про импорт/экспорт
Чередующаяся заливка строк с помощью условного форматирования
Выделяем ячейки с лишними пробелами с помощью условного форматирования
Отделяем от поля с датой отдельно дату и отдельно время.
Статья на Хабре: «Полезные функции Google Таблиц, которых нет в Excel»
Фильтр и режим фильтрации
Условное форматирование — выделяем 25% лучших в строке
Парсим колонку с результатами игр и считаем отдельно сумму каждой из сторон. Функции: SPLIT, массив, INDEX и SUM.
Фильтруем ячейки по наличию в них формул
IMPORTRANGE и именованный диапазон
Условия для отбора пустых и непустых ячеек, («», «=», «<>»)
ВПР с ПОИСКПОЗ в массиве
Превращаем ИСТИНА/ЛОЖЬ в числа 1/0 и используем в СУММПРОИЗВ
Небольшое пояснение о том, как работает СУММПРОИЗВ
Объединяем значения из диапазонов, убирая пустые строки (JOIN, SPLIT) и используем для QUERY
Googlefinance, Парето и графики (#готовое решение)
Выбираем случайную пару команд из двух разных лиг, чтобы провести жеребьевку кубка.
Live дашборд в вебе, собирая данные из 1С
IMPORTXML: загружаем изображения с веб-страницы
IMPORTDATA: импорт данных из CSV-файлов
Фрагмент нашей книжки: «Динамические диапазоны с функцией СМЕЩ и динамический спарклайн».
Фрагмент нашей книжки: «СОЗДАЕМ ПРАВИЛО, КОТОРОЕ БУДЕТ ВЫДЕЛЯТЬ ЦВЕТОМ ОПОЗДАВШИХ НА РАБОТУ»
Фрагмент нашей книжки: «НЕСКОЛЬКО СЛОВ О ДИАГРАММАХ»
FILTER, условия выбираем диапазоном
SORTN и IMPORTHML — импортируем таблицу из вебсайта и выводим выбранное кол-во строк
REGEXEXTRACT: извлекаем фрагмент текста из ячейки по шаблону
REGEXMATCH: проверяем наличие в ячейке текста, отвечающего заданному шаблону
Простой скрипт для ежедневной рассылки из Google Таблицы
REGEXREPLACE. Находим и заменяем текст в ячейке, соответствующий регулярному выражению
Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY)
Диаграмма «Дерево» для отображения организационной структуры или других иерархий.
Отбор данных по условию, объединение до одной ячейки, добавляем текст и составляем поздравительные телеграммы. И все с помощью только одной формулы FILTER и &.
Адовый кейс. Выводим последний чек по каждой пиццерии с помощью только одной введенной формулы. QUERY+FILTER
Как вывести финансовый месяц и год по нужной нам дате, если в таблице-справочнике только дата, когда он начинается?
Ссылки АПИ с помощью формулы, введенной только в одну ячейку
Символьные шаблоны в функциях СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ
Извлекаем из ячейки только определенное слово: функции SPLIT и ИНДЕКС
С помощью условного форматирования создаем интерактивные шкалы. Размер шкалы зависит от введенного балла.
Как открыть CSV (tsv) документ в Google Таблице? Два удобных способа.
Отбор по времени дня в QUERY.
★ Подборка ответов на вопросы: условное форматирование, FILTER \ QUERY, SUMIFs для разных листов одной формулой, IMPORTHTML или как постоянно загружать данные из веб-сайта, а также немного про работу с API, JSON, квоты машинного времени, скрипты и прочее
Как один столбец с данными развернуть в таблицу из 5-ти столбцов? ТРАСП(TRANSPOSE), СМЕЩ(OFFSET), ROW(СТРОКА)
ВПР по нескольким диапазонам
★ 2-ая подборка ответов: cчитаем галочки, cчитаем ячейки по цвету их заливки и сортируем, lобавляем пустые строки после результата формул, cвязываем IMPORTRANGE и QUERY, а также транспонируем формулы, вставляем в Google Документ обновляемую часть из Таблицы
Как создать «кнопку» для отправки письма нужному контрагенту в Google Таблицах
★ 3-ая подборка ответов на вопросы: формула IFS, как подсчитать незакрашенные цветом ячейки
«Условное форматирование» с иконками с помощью функции IMAGE
Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям.
Скрипт автоматически проставляет текущую дату и время при изменение статуса в ячейке
★ 4-ая подборка ответов: разделение проекта, как способ спрятать часть информации от сотрудника, вводим в ячейку несколько символов, видим список из всех подходящих вариантов и выбираем нужный, с помощью QUERY уменьшаем таблицу до нескольких столбцов, как подставить в формулу номер строки или букву столбца из текущей ячейки, градиентное условное форматирование внутри групп и другие вопросы
Публикация в интернете — прекрасный инструмент для того, чтобы часть вкладок док-та сделать видимой для всех (read-only)
★ 5-ая подборка ответов на вопросы. Распределяем текст из одной ячейки по двум столбцам и наоборот, выборка по фрагменту слова в QUERY. Функции: JOIN, SPLIT, ISODD, ISEVEN (четное и нечетное), QUERY.
График с двумя осями и график типа «спарклайн». Совмещаем несовместимое на одном графике
Считаем процент выполнения плана, прирост и прогноз
★ 6-ая подборка ответов: IMPORTRANGE как аргумент для FILTER (или QUERY) или создаем документ с доступом только к части данных, убираем название вычисляемого столбца в QUERY двумя способами (LABEL, INDEX), создаем свой, уникальный порядок сортировки, часть столцба сортируем по нему, часть — [А-Яа-я] и все в одной формуле
Закрепление областей мышкой
Что нового в Таблицах — декабрь 2018
★ 7-ая подборка ответов: О скрипте (функции), который отобразит названия всех листов в книге, о том, как собрать на одном листе Google Таблицы данные из 70-ти листов, о том, как объединить таблицы с разной структурой в QUERY, сгруппировать и посчитать средние значения
Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы.
★ 8-ая подборка ответов: о возможностях получения данных о форматировании ячейки с помощью функций, функции для проверки данных, выделяем ячейки с одинаковыми ссылками одним цветом, и решение про уникальный порядок сортировки от нашего читателя Ильи
Видео про функцию ВПР в Google Таблицах
Одной формулой FILTER создаем столбец, в котором переводим все даты из другого столбца на +2 часа.
★ 9-ая подборка ответов:
— скрипт, запускающийся каждую ночь и защищащий от редактирования заполненный диапазон выбранного листа,
— как текст превратить в работающую ссылку на ячейки (и что делать, если название листа с пробелами),
— как объединить значения с одним названием на диаграмме,
— немного про cashflow, баланс и прочий p&l в Таблицах
Новая опция в сводных: раскрытие данных любой ячейки
Видеоурок: Регулярные выражения в Google Таблицах
Автоматический формат данных в функции TEXT, убираем у целых чисел разряды, а дробные округляем до 4-го знака по запятой
★ 10-ая подборка ответов:
— заменим в наименованиях товара кириллические значения на латинские (двумя способами!)
— составим для каждой даты в диапазоне текст вида «01.01 — 07.01.18» (первый день недели — последний)
— сгруппируем продажи по дням в месяца и сделаем из этого график
— и еще немного расскажем про FILTER
С помощью условного форматирования закрасим все счета состязаний с победой хозяеев (INDEX, SPLIT)
Проверка данных: в качестве числового ограничения не фиксированное значение, а число из ячейки.
★ 11-ая подборка ответов:
1) пишем пользовательскую функцию, которая выдергивает из =ГИПЕРССЫЛКА(ссылка;текст) — ссылку
2) функция FILTER, использующая данные выпадающего списка, получаем список всех тематик выбранного спикера
3) поговорим про парсинг с помощью IMPORTXML и квоты
QUERY некорректно выводит данные, если в одном столбце соседствуют числовые и текстовые значения. Рассказываем, как обойти.
Разбираем Google скрипт (GAS) вставки функции — замены ее на значения.
Темы, на которые я пишу: