Google Таблицы
Работа в Google Таблицах. Кейсы, решения и угар.
админы:
@namokonov
@r_shagabutdinov
@IT_sAdmin
оглавление: goo.gl/HdS2qn
заказ работы: teletype.in/@google_sheets/sheet_happens
купить рекламу: https://telega.in/c/google_sheets
РКН: clck.ru/3F3u9M
админы:
@namokonov
@r_shagabutdinov
@IT_sAdmin
оглавление: goo.gl/HdS2qn
заказ работы: teletype.in/@google_sheets/sheet_happens
купить рекламу: https://telega.in/c/google_sheets
РКН: clck.ru/3F3u9M
TGlist рейтингі
0
0
ТүріҚоғамдық
Растау
РасталмағанСенімділік
СенімсізОрналасқан жеріРосія
ТілБасқа
Канал құрылған күніApr 14, 2025
TGlist-ке қосылған күні
Sep 26, 2023Қосылған топ
Чат | Таблицы и GAS-скрипты
6.6K
Рекордтар
23.04.202523:59
62.4KЖазылушылар14.04.202523:59
400Дәйексөз индексі18.04.202523:59
4.1K1 жазбаның қамтуы30.04.202523:59
3.8KЖарнамалық жазбаның қамтуы19.04.202523:59
2.43%ER11.04.202513:17
6.63%ERR
10.04.202508:06
ЛЮБИМЫЕ СКРИПТЫ НАШЕГО КАНАЛА
Друзья, посмотрите, вдруг вы пропустили что-нибудь полезное:
— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →
— 🧞♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →
— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →
— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →
— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →
— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →
— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →
— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →
— Скриптами определяем, когда освободится домен →
— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →
— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →
— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →
— Защищаем скрипты от редактирования →
— Регулярный бэкап Таблиц в формате XLSX в телеграм! →
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →
— Важный скрипт. Связанные выпадающие списки из кэша →
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
Друзья, посмотрите, вдруг вы пропустили что-нибудь полезное:
— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →
— 🧞♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →
— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →
— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →
— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →
— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →
— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →
— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →
— Скриптами определяем, когда освободится домен →
— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →
— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →
— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →
— Защищаем скрипты от редактирования →
— Регулярный бэкап Таблиц в формате XLSX в телеграм! →
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →
— Важный скрипт. Связанные выпадающие списки из кэша →
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
04.04.202507:22
Связанные выпадающие списки на формулах
Вообще-то уже очень давно мы публиковали скрипт для создания связанных списков — вот ссылка.
Но для разнообразия предлагаем также слегка колхозное решение на формулах 🤠Если вы не любите скрипты.
По сути, мы создаем таблицу с таким же количеством строк, как основная. И в ней в каждой строке формируем данные для выпадающего списка нижнего уровня (сотрудники, например), куда отправляем значения исходя из выбранного значения в выпадающем списке верхнего уровня (отдел, например)
Алгоритм:
— Формируем в укромном месте список значений нижнего уровня для каждого выбранного значения в основной таблице. В нашем примере — для каждого выбранного отдела в основной таблице получаем список сотрудников этого отдела (то есть горизонтальный массив значений на каждое выбранное в основной таблице значение верхнего уровня)
Вариант формулы:
— Создаем проверку данных и в качестве диапазона ссылаемся на первый список (для первого значения), делаем ссылку относительной (то есть у номеров строк нет долларов) и получается, что в каждой строке забираем список для каждого очередного выпадающего списка.
Ссылка в проверке данных:
Куча лишних данных, но работает.
Таблица с примером
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
Вообще-то уже очень давно мы публиковали скрипт для создания связанных списков — вот ссылка.
Но для разнообразия предлагаем также слегка колхозное решение на формулах 🤠Если вы не любите скрипты.
По сути, мы создаем таблицу с таким же количеством строк, как основная. И в ней в каждой строке формируем данные для выпадающего списка нижнего уровня (сотрудники, например), куда отправляем значения исходя из выбранного значения в выпадающем списке верхнего уровня (отдел, например)
Алгоритм:
— Формируем в укромном месте список значений нижнего уровня для каждого выбранного значения в основной таблице. В нашем примере — для каждого выбранного отдела в основной таблице получаем список сотрудников этого отдела (то есть горизонтальный массив значений на каждое выбранное в основной таблице значение верхнего уровня)
Вариант формулы:
=MAP(столбец со значениями первого уровня ;LAMBDA(x; IFNA(ТРАНСП(ИНДЕКС(таблица со списками ;; XMATCH(x;названия списков первого уровня)));"")))
— Создаем проверку данных и в качестве диапазона ссылаемся на первый список (для первого значения), делаем ссылку относительной (то есть у номеров строк нет долларов) и получается, что в каждой строке забираем список для каждого очередного выпадающего списка.
Ссылка в проверке данных:
='Вспомогательный лист'!$A2:$Z2 (столбцы можно взять с запасом)
Куча лишних данных, но работает.
Таблица с примером
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
17.04.202512:05
Функции баз данных позволяют суммировать/усреднять/дисперсировать/минимизировать и т.д. по большому количеству условий, заданных в ячейках
В примере: суммируем все виды Ipad Pro и Ipad Air — в Москве только на основном складе, а во всех остальных городах на любых складах, кроме "Брака"
Первый аргумент функции DSUM — диапазон с данными, второй — заголовок столбца, в котором суммируемые числа, третий — диапазон (массив) с условиями.
И вот сегодня про второй аргумент. Как его можно задать?
1. Как текст — ссылаемся на ячейку с заголовком или вводим прямо в формуле в кавычках
2. Как порядковый номер. Тогда либо указываем его в аргументе (но такая формула слетит при изменении порядка столбцов), либо можем искать номер по слову функцией XMATCH, например, если нас интересует слово "остаток" в заголовке (третий аргумент, равный двойке, включает работу символов подстановки в XMATCH — в старой MATCH они работали по умолчанию, но там нужно явно включить точный поиск, так что там третий аргумент был бы равен нулю):
Последний вариант будет работать и при изменении заголовка (если там останется искомое слово, конечно), и при изменении порядка столбцов.
Все четыре варианта по ссылке:
Таблица с примером
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
В примере: суммируем все виды Ipad Pro и Ipad Air — в Москве только на основном складе, а во всех остальных городах на любых складах, кроме "Брака"
Первый аргумент функции DSUM — диапазон с данными, второй — заголовок столбца, в котором суммируемые числа, третий — диапазон (массив) с условиями.
И вот сегодня про второй аргумент. Как его можно задать?
1. Как текст — ссылаемся на ячейку с заголовком или вводим прямо в формуле в кавычках
2. Как порядковый номер. Тогда либо указываем его в аргументе (но такая формула слетит при изменении порядка столбцов), либо можем искать номер по слову функцией XMATCH, например, если нас интересует слово "остаток" в заголовке (третий аргумент, равный двойке, включает работу символов подстановки в XMATCH — в старой MATCH они работали по умолчанию, но там нужно явно включить точный поиск, так что там третий аргумент был бы равен нулю):
=DSUM(данные; XMATCH("*Остаток*"; 1:1;2); условия)
Последний вариант будет работать и при изменении заголовка (если там останется искомое слово, конечно), и при изменении порядка столбцов.
Все четыре варианта по ссылке:
Таблица с примером
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
04.04.202514:07
Проверяем, был ли у сотрудника хотя бы один 14-дневный отпуск
Интересная задача от участницы практикума "Магия формул", мы решили вынести решение на всех вас :)
Итак, в таблице с сотрудниками единицами отмечены дни отпуска. Надо понять, была ли у сотрудника приятная цепочка в 14 или более таких дней.
Алгоритм такой:
1. Убираем заголовки с месяцами (чтобы остались только дни; нам не нужно считать итог месяца отдельным днем, его надо пропустить) через функцию FILTER. Можно исключать конкретное слово "<>итого" или оставлять только числа ISNUMBER(...) )
2. Далее в полученном массиве идущих подряд дней с помощью SCAN считаем нарастающий итог — число идущих подряд единиц. Если есть единица, прибавляем к накопленному итогу ее, иначе обнуляем счетчик)
3. В результате получаем массив с накопленными днями отпусков — с помощью COUNTIF проверяем, есть ли там хоть одно число от 14 включительно.
4. Напоминаем вам, что логические значения TRUE и FALSE, если они выдаются формулами, можно показывать как флажки.
Вариант для одной строки (на скриншоте в строках с 9-й видно, что возвращает функция SCAN, то есть все, что внутри COUNTIF / СЧЁТЕСЛИ) — массив накопленных значений, в котором мы потом ищем числа от 14.
Еще вариант — одной формулой все сотрудники:
Ссылка на таблицу с вариантами формул
Не забывайте отдыхать, друзья! И предлагайте свои варианты решения задачи🤠
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
Интересная задача от участницы практикума "Магия формул", мы решили вынести решение на всех вас :)
Итак, в таблице с сотрудниками единицами отмечены дни отпуска. Надо понять, была ли у сотрудника приятная цепочка в 14 или более таких дней.
Алгоритм такой:
1. Убираем заголовки с месяцами (чтобы остались только дни; нам не нужно считать итог месяца отдельным днем, его надо пропустить) через функцию FILTER. Можно исключать конкретное слово "<>итого" или оставлять только числа ISNUMBER(...) )
2. Далее в полученном массиве идущих подряд дней с помощью SCAN считаем нарастающий итог — число идущих подряд единиц. Если есть единица, прибавляем к накопленному итогу ее, иначе обнуляем счетчик)
3. В результате получаем массив с накопленными днями отпусков — с помощью COUNTIF проверяем, есть ли там хоть одно число от 14 включительно.
4. Напоминаем вам, что логические значения TRUE и FALSE, если они выдаются формулами, можно показывать как флажки.
Вариант для одной строки (на скриншоте в строках с 9-й видно, что возвращает функция SCAN, то есть все, что внутри COUNTIF / СЧЁТЕСЛИ) — массив накопленных значений, в котором мы потом ищем числа от 14.
=COUNTIF(SCAN(0;FILTER(строка с днями сотрудника;строка заголовков<>"итого"); LAMBDA(acc;value;IF(value;acc+value;0)));">=14")<>0
Еще вариант — одной формулой все сотрудники:
Ссылка на таблицу с вариантами формул
Не забывайте отдыхать, друзья! И предлагайте свои варианты решения задачи🤠
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
02.04.202508:46
Получаем номер квартала из даты: разные варианты
К сожалению, для вычисления номера квартала функции нет ни в Excel любых версий, ни в Google Таблицах. Обычно номер квартала вычисляют составной формулой, опираясь на номер месяца. Например, такой.
Вариант 1, классический
Для января (месяц = 1) формула будет возвращать единицу:
(1 + 2)/3 = 1
А для августа (8):
(8 + 2)/3 = 3,(3)
Дробную часть мы убираем с помощью функции ЦЕЛОЕ / INT
и за счет этого получаем целое число 3 для августа.
Вариант 2, с извращениями
Функция ВЫБОР / CHOOSE возвращает один из своих аргументов по порядковому номеру, заданному в первом аргументе. То есть если МЕСЯЦ у нас будет равен единице, двойке или тройке, то ВЫБОР вернет второй, третий или четвертый аргумент — это у нас единицы.
Вариант третий, через LOOKUP
Здесь мы ищем ближайшее наименьшее число к номеру месяца в массиве функцией ПРОСМОТР. И получаем соответствующий номер квартала из другого массива.
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
К сожалению, для вычисления номера квартала функции нет ни в Excel любых версий, ни в Google Таблицах. Обычно номер квартала вычисляют составной формулой, опираясь на номер месяца. Например, такой.
Вариант 1, классический
=ЦЕЛОЕ ((МЕСЯЦ (ячейка с датой) + 2)/3)
Для января (месяц = 1) формула будет возвращать единицу:
(1 + 2)/3 = 1
А для августа (8):
(8 + 2)/3 = 3,(3)
Дробную часть мы убираем с помощью функции ЦЕЛОЕ / INT
и за счет этого получаем целое число 3 для августа.
ЦЕЛОЕ ((8 + 2)/3) = ЦЕЛОЕ (3,(3)) = 3
Вариант 2, с извращениями
=ВЫБОР (МЕСЯЦ (дата);1;1;1;2;2;2;3;3;3;4;4;4)
Функция ВЫБОР / CHOOSE возвращает один из своих аргументов по порядковому номеру, заданному в первом аргументе. То есть если МЕСЯЦ у нас будет равен единице, двойке или тройке, то ВЫБОР вернет второй, третий или четвертый аргумент — это у нас единицы.
Вариант третий, через LOOKUP
=ПРОСМОТР (МЕСЯЦ(A4); {1;4;7;10};{1;2;3;4})
Здесь мы ищем ближайшее наименьшее число к номеру месяца в массиве функцией ПРОСМОТР. И получаем соответствующий номер квартала из другого массива.
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
17.04.202507:03
Устали от бесконечных таблиц и ручной сверки данных?
Для финансовых директоров и бухгалтеров, которые ценят свое время, Софья Бурцева подготовила 5 проверенных инструментов для автоматизации УУ:
1. Практическое руководство «Связки ПО для автоматизации УУ»
2. Руководство: Как свести баланс в 1С:УНФ
3. Чек-лист "10 лайфхаков работы в 1С"
4. Запись закрытого эфира — "Архитектура базы и настройки 1С:УНФ"
5. Разбор 5 самых частых ошибок при автоматизации УУ
Результаты при освоении автоматизации:
✅ Обеспечите прозрачные и надежные данные.
✅ Освободите время для стратегических задач.
✅ Увеличите доверие со стороны собственника к финансовым отчетам.
✅ Ускорите работу, снижая время на рутинные задачи.
✅ Повысите стоимость вашего часа.
Скачать "5 инструментов для автоматизации УУ"
по ссылке ➡️ https://t.me/findir_pro/1687
Для финансовых директоров и бухгалтеров, которые ценят свое время, Софья Бурцева подготовила 5 проверенных инструментов для автоматизации УУ:
1. Практическое руководство «Связки ПО для автоматизации УУ»
2. Руководство: Как свести баланс в 1С:УНФ
3. Чек-лист "10 лайфхаков работы в 1С"
4. Запись закрытого эфира — "Архитектура базы и настройки 1С:УНФ"
5. Разбор 5 самых частых ошибок при автоматизации УУ
Результаты при освоении автоматизации:
✅ Обеспечите прозрачные и надежные данные.
✅ Освободите время для стратегических задач.
✅ Увеличите доверие со стороны собственника к финансовым отчетам.
✅ Ускорите работу, снижая время на рутинные задачи.
✅ Повысите стоимость вашего часа.
Скачать "5 инструментов для автоматизации УУ"
по ссылке ➡️ https://t.me/findir_pro/1687
Көбірек мүмкіндіктерді ашу үшін кіріңіз.
