СУММЕСЛИ (разбор "сложных" условий, с <>= и символьными шаблонами *?) |
Советы по оптимизации таблиц |
Открытые диапазоны (А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) вставки функции - замены ее на значения. |