Google Таблицы, Google Spreadsheets

Sep 15 2019,   @googlesheets2

Парсим колонку с результатами игр и считаем отдельно сумму каждой из сторон. Функции: SPLIT, массив, INDEX и SUM. https://tglink.ru/google_sheets/73
Фильтруем ячейки по наличию в них формул https://tglink.ru/google_sheets/75
IMPORTRANGE и именованный диапазон https://tglink.ru/google_sheets/76
Условия для отбора пустых и непустых ячеек, ("", "=", "<>") https://tglink.ru/google_sheets/77

ВПР с ПОИСКПОЗ в массиве https://tglink.ru/google_sheets/78
Превращаем ИСТИНА/ЛОЖЬ в числа 1/0 и используем в СУММПРОИЗВ https://tglink.ru/google_sheets/79
Небольшое пояснение о том, как работает СУММПРОИЗВ https://tglink.ru/google_sheets/80
Объединяем значения из диапазонов, убирая пустые строки (JOIN, SPLIT) и используем для QUERY https://tglink.ru/google_sheets/81

Googlefinance, Парето и графики (#готовое решение) https://tglink.ru/google_sheets/82
Выбираем случайную пару команд из двух разных лиг, чтобы провести жеребьевку кубка. https://tglink.ru/google_sheets/85
Live дашборд в вебе, собирая данные из 1С https://tglink.ru/google_sheets/87
IMPORTXML: загружаем изображения с веб-страницы https://tglink.ru/google_sheets/88

IMPORTDATA: импорт данных из CSV-файлов https://tglink.ru/google_sheets/93
Фрагмент нашей книжки: "Динамические диапазоны с функцией СМЕЩ и динамический спарклайн". https://tglink.ru/google_sheets/97
Фрагмент нашей книжки: "СОЗДАЕМ ПРАВИЛО, КОТОРОЕ БУДЕТ ВЫДЕЛЯТЬ ЦВЕТОМ ОПОЗДАВШИХ НА РАБОТУ" https://tglink.ru/google_sheets/99

Фрагмент нашей книжки: "НЕСКОЛЬКО СЛОВ О ДИАГРАММАХ" https://tglink.ru/google_sheets/101
FILTER, условия выбираем диапазоном https://tglink.ru/google_sheets/102
SORTN и IMPORTHML - импортируем таблицу из вебсайта и выводим выбранное кол-во строк https://tglink.ru/google_sheets/104
REGEXEXTRACT: извлекаем фрагмент текста из ячейки по шаблону https://tglink.ru/google_sheets/106

REGEXMATCH: проверяем наличие в ячейке текста, отвечающего заданному шаблону https://tglink.ru/google_sheets/107
Простой скрипт для ежедневной рассылки из Google Таблицы https://tglink.ru/google_sheets/109
REGEXREPLACE. Находим и заменяем текст в ячейке, соответствующий регулярному выражению https://tglink.ru/google_sheets/110

Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) https://tglink.ru/google_sheets/115
Диаграмма "Дерево" для отображения организационной структуры или других иерархий. https://tglink.ru/google_sheets/116

Отбор данных по условию, объединение до одной ячейки, добавляем текст и составляем поздравительные телеграммы. И все с помощью только одной формулы FILTER и &. https://tglink.ru/google_sheets/119
Адовый кейс. Выводим последний чек по каждой пиццерии с помощью только одной введенной формулы. QUERY+FILTER https://tglink.ru/google_sheets/122

Как вывести финансовый месяц и год по нужной нам дате, если в таблице-справочнике только дата, когда он начинается? https://tglink.ru/google_sheets/126
Ссылки АПИ с помощью формулы, введенной только в одну ячейку https://tglink.ru/google_sheets/128
Символьные шаблоны в функциях СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ https://tglink.ru/google_sheets/129

Извлекаем из ячейки только определенное слово: функции SPLIT и ИНДЕКС https://tglink.ru/google_sheets/130
С помощью условного форматирования создаем интерактивные шкалы. Размер шкалы зависит от введенного балла. https://tglink.ru/google_sheets/134
Как открыть CSV (tsv) документ в Google Таблице? Два удобных способа. https://tglink.ru/google_sheets/136

Отбор по времени дня в QUERY. https://tglink.ru/google_sheets/137
★ Подборка ответов на вопросы: условное форматирование, FILTER \ QUERY, SUMIFs для разных листов одной формулой, IMPORTHTML или как постоянно загружать данные из веб-сайта, а также немного про работу с API, JSON, квоты машинного времени, скрипты и прочее https://tglink.ru/google_sheets/140

Как один столбец с данными развернуть в таблицу из 5-ти столбцов? ТРАСП(TRANSPOSE), СМЕЩ(OFFSET), ROW(СТРОКА) https://tglink.ru/google_sheets/142
ВПР по нескольким диапазонам https://tglink.ru/google_sheets/145

★ 2-ая подборка ответов: cчитаем галочки, cчитаем ячейки по цвету их заливки и сортируем, lобавляем пустые строки после результата формул, cвязываем IMPORTRANGE и QUERY, а также транспонируем формулы, вставляем в Google Документ обновляемую часть из Таблицы https://tglink.ru/google_sheets/148

Как создать "кнопку" для отправки письма нужному контрагенту в Google Таблицах https://tglink.ru/google_sheets/150
★ 3-ая подборка ответов на вопросы: формула IFS, как подсчитать незакрашенные цветом ячейки https://tglink.ru/google_sheets/151
"Условное форматирование" с иконками с помощью функции IMAGE https://tglink.ru/google_sheets/152

Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям. https://tglink.ru/google_sheets/153
Скрипт автоматически проставляет текущую дату и время при изменение статуса в ячейке https://tglink.ru/google_sheets/171

★ 4-ая подборка ответов: вводим в ячейку несколько символов, видим список из всех подходящих вариантов и выбираем нужный, с помощью QUERY уменьшаем таблицу до нескольких столбцов, как подставить в формулу номер строки или букву столбца из текущей ячейки, градиентное условное форматирование внутри групп и другие вопросы https://tglink.ru/google_sheets/158

Публикация в интернете - прекрасный инструмент для того, чтобы часть вкладок док-та сделать видимой для всех (read-only) https://tglink.ru/google_sheets/160
★ 5-ая подборка ответов на вопросы. Распределяем текст из одной ячейки по двум столбцам и наоборот, выборка по фрагменту слова в QUERY. Функции: JOIN, SPLIT, ISODD, ISEVEN (четное и нечетное), QUERY. https://tglink.ru/google_sheets/164

График с двумя осями и график типа "спарклайн". Совмещаем несовместимое на одном графике https://tglink.ru/google_sheets/166
Считаем процент выполнения плана, прирост и прогноз https://tglink.ru/google_sheets/168

★ 6-ая подборка ответов: IMPORTRANGE как аргумент для FILTER (или QUERY) или создаем документ с доступом только к части данных, убираем название вычисляемого столбца в QUERY двумя способами (LABEL, INDEX), создаем свой, уникальный порядок сортировки, часть столцба сортируем по нему, часть - [А-Яа-я] и все в одной формуле https://tglink.ru/google_sheets/169

Закрепление областей мышкой https://tglink.ru/google_sheets/172
Что нового в Таблицах - декабрь 2018 https://tglink.ru/google_sheets/174

★ 7-ая подборка ответов: О скрипте (функции), который отобразит названия всех листов в книге, о том, как собрать на одном листе Google Таблицы данные из 70-ти листов, о том, как объединить таблицы с разной структурой в QUERY, сгруппировать и посчитать средние значения https://tglink.ru/google_sheets/176

Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы. https://tglink.ru/google_sheets/180

★ 8-ая подборка ответов: о возможностях получения данных о форматировании ячейки с помощью функций, функции для проверки данных, выделяем ячейки с одинаковыми ссылками одним цветом, и решение про уникальный порядок сортировки от нашего читателя Ильи https://tglink.ru/google_sheets/181

Видео про функцию ВПР в Google Таблицах https://tglink.ru/google_sheets/182
Одной формулой FILTER создаем столбец, в котором переводим все даты из другого столбца на +2 часа. https://tglink.ru/google_sheets/184

"★ 9-ая подборка ответов:
— скрипт, запускающийся каждую ночь и защищащий от редактирования заполненный диапазон выбранного листа,
— как текст превратить в работающую ссылку на ячейки (и что делать, если название листа с пробелами),
— как объединить значения с одним названием на диаграмме,
— немного про cashflow, баланс и прочий p&l в Таблицах" https://tglink.ru/google_sheets/187

Новая опция в сводных: раскрытие данных любой ячейки https://tglink.ru/google_sheets/188
Видеоурок: Регулярные выражения в Google Таблицах https://tglink.ru/google_sheets/190
Автоматический формат данных в функции TEXT, убираем у целых чисел разряды, а дробные округляем до 4-го знака по запятой https://tglink.ru/google_sheets/191

"★ 10-ая подборка ответов:
— заменим в наименованиях товара кириллические значения на латинские (двумя способами!)
— составим для каждой даты в диапазоне текст вида ""01.01 - 07.01.18"" (первый день недели - последний)
— сгруппируем продажи по дням в месяца и сделаем из этого график
— и еще немного расскажем про FILTER" https://tglink.ru/google_sheets/194

С помощью условного форматирования закрасим все счета состязаний с победой хозяеев (INDEX, SPLIT) https://tglink.ru/google_sheets/195
Проверка данных: в качестве числового ограничения не фиксированное значение, а число из ячейки. https://tglink.ru/google_sheets/197

"★ 11-ая подборка ответов:
1) пишем пользовательскую функцию, которая выдергивает из =ГИПЕРССЫЛКА(ссылка;текст) - ссылку
2) функция FILTER, использующая данные выпадающего списка, получаем список всех тематик выбранного спикера
3) поговорим про парсинг с помощью IMPORTXML и квоты" https://tglink.ru/google_sheets/199

QUERY некорректно выводит данные, если в одном столбце соседствуют числовые и текстовые значения. Рассказываем, как обойти. https://tglink.ru/google_sheets/200
Разбираем Google скрипт (GAS) вставки функции - замены ее на значения. https://tglink.ru/google_sheets/206

"★ 12-ая подборка ответов:
— про чекбоксы (выпадающие списки)
— про логику формулы IF (Если)
— и как защитить лист, оставив только некоторые диапазоны доступными для редактирования" https://tglink.ru/google_sheets/208
Извлекаем из текстовой строки: дату, числа, артикул, используя функции массива и регулярные выражения (REGEXEXTRACT, SPLIT, INDEX) https://tglink.ru/google_sheets/209

Tags

No Tags